USE fortunes_quotation;

ALTER TABLE users ADD COLUMN IF NOT EXISTS must_change_password TINYINT(1) NOT NULL DEFAULT 0 AFTER status;

ALTER TABLE activity_logs ADD COLUMN IF NOT EXISTS ip_address VARCHAR(64) NULL AFTER description;
ALTER TABLE activity_logs ADD COLUMN IF NOT EXISTS user_agent VARCHAR(255) NULL AFTER ip_address;

CREATE TABLE IF NOT EXISTS login_attempts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  ip_address VARCHAR(64) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at DATETIME NOT NULL,
  INDEX idx_login_attempts_email_time (email, attempted_at),
  INDEX idx_login_attempts_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UPDATE users SET must_change_password = 1 WHERE email = 'admin@fortunes.local';
