CREATE TABLE IF NOT EXISTS password_reset_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 selector CHAR(24) NOT NULL UNIQUE,
 token_hash CHAR(64) NOT NULL,
 otp_hash VARCHAR(255) NOT NULL,
 expires_at DATETIME NOT NULL,
 attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 max_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 5,
 consumed_at DATETIME NULL,
 requested_ip VARCHAR(64) NULL,
 requested_user_agent VARCHAR(255) NULL,
 created_at DATETIME NOT NULL,
 INDEX(user_id,expires_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS password_reset_rate_limits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 rate_key CHAR(64) NOT NULL,
 window_started_at DATETIME NOT NULL,
 attempts SMALLINT UNSIGNED NOT NULL DEFAULT 1,
 UNIQUE KEY uq_password_reset_rate(rate_key)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 session_hash CHAR(64) NOT NULL UNIQUE,
 ip_address VARCHAR(64) NULL,
 user_agent VARCHAR(255) NULL,
 created_at DATETIME NOT NULL,
 last_seen_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 INDEX(user_id,revoked_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS translation_overrides (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 page_scope VARCHAR(190) NOT NULL DEFAULT '*',
 source_locale VARCHAR(10) NOT NULL,
 target_locale VARCHAR(10) NOT NULL,
 source_text VARCHAR(500) NOT NULL,
 source_hash CHAR(64) NOT NULL,
 translated_text VARCHAR(500) NOT NULL,
 status ENUM('draft','published','archived') NOT NULL DEFAULT 'published',
 created_by BIGINT UNSIGNED NULL,
 updated_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_translation_override(page_scope,target_locale,source_hash),
 INDEX(target_locale,status),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS translation_editor_permissions (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 can_translate TINYINT(1) NOT NULL DEFAULT 0,
 can_publish TINYINT(1) NOT NULL DEFAULT 0,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;