CREATE TABLE IF NOT EXISTS auth_rate_limits (
    scope ENUM('ip','account','mfa') NOT NULL,
    identifier BINARY(32) NOT NULL,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    first_failed_at DATETIME(6) NOT NULL,
    last_failed_at DATETIME(6) NOT NULL,
    locked_until DATETIME(6) NULL,
    PRIMARY KEY (scope, identifier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_mfa (
    user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    secret_ciphertext TEXT NOT NULL,
    enabled_at DATETIME(6) NOT NULL,
    last_used_step BIGINT UNSIGNED NULL,
    CONSTRAINT fk_user_mfa_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mfa_recovery_codes (
    user_id BIGINT UNSIGNED NOT NULL,
    code_hash BINARY(32) NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    used_at DATETIME(6) NULL,
    PRIMARY KEY (user_id, code_hash),
    CONSTRAINT fk_mfa_recovery_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;