-- =====================================================================
-- AI Front Desk Platform — Migration 014
--
-- TOTP-based two-factor authentication. Mandatory for role=superadmin
-- (enforced in application code — Auth::requireLogin() redirects an
-- unenrolled superadmin to the enrollment page before anything else),
-- opt-in for every other role.
--
-- totp_secret is only meaningful once totp_confirmed_at is set — a
-- secret can exist mid-enrollment (generated, shown to the user, not
-- yet confirmed with a live code) without totp_enabled ever flipping
-- on, so an abandoned enrollment never silently locks the account out.
--
-- totp_recovery_codes stores each code already hashed with
-- password_hash(), same as the login password itself — a DB leak alone
-- is never enough to produce a usable code.
-- =====================================================================

SET NAMES utf8mb4;

ALTER TABLE users
    ADD COLUMN totp_secret VARCHAR(64) NULL
        COMMENT 'Base32-encoded TOTP shared secret. Meaningless until totp_confirmed_at is set.'
        AFTER password_hash,
    ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0
        COMMENT '1 only after enrollment was confirmed with a live code — never set directly.'
        AFTER totp_secret,
    ADD COLUMN totp_recovery_codes TEXT NULL
        COMMENT 'JSON array of password_hash()-hashed one-time recovery codes.'
        AFTER totp_enabled,
    ADD COLUMN totp_confirmed_at DATETIME NULL
        COMMENT 'When enrollment was confirmed with a live code.'
        AFTER totp_recovery_codes;