-- Email registration + 2FA tables (MariaDB). Run as DB root once; SQLite dev auto-migrates via AuthEmailSchema.

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS email_normalized VARCHAR(254) NULL,
    ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS status ENUM('pending','active','locked','disabled') NOT NULL DEFAULT 'active',
    ADD COLUMN IF NOT EXISTS recovery_email_normalized VARCHAR(254) NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_users_email_normalized ON users (email_normalized);

CREATE TABLE IF NOT EXISTS auth_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    purpose ENUM('verify_email','reset_password','login_magic') NOT NULL,
    token_hash CHAR(64) NOT NULL,
    email_normalized VARCHAR(254) NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_auth_tokens_hash (token_hash),
    KEY idx_auth_tokens_email (email_normalized)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_factors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    type ENUM('totp','webauthn','backup_code') NOT NULL,
    secret_encrypted TEXT NULL,
    label VARCHAR(64) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_auth_factors_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip_hash CHAR(64) NOT NULL,
    username_hash CHAR(64) NULL,
    outcome VARCHAR(32) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_auth_attempts_ip (ip_hash, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
