-- Migration: 029_staff_mfa.sql
-- Purpose:
--   Add mandatory TOTP MFA support for staff accounts.
--
-- Security:
--   TOTP secrets are stored using AES-256-GCM encrypted fields.
--   Recovery codes are stored only as keyed hashes.
--
-- Compatibility:
--   Existing accounts default to mfa_enabled = false and must enroll
--   during their next successful password login.

BEGIN;

ALTER TABLE staff_users
  ADD COLUMN IF NOT EXISTS mfa_secret_ciphertext TEXT,
  ADD COLUMN IF NOT EXISTS mfa_secret_iv TEXT,
  ADD COLUMN IF NOT EXISTS mfa_secret_tag TEXT,
  ADD COLUMN IF NOT EXISTS mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS mfa_enrolled_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS mfa_last_used_step BIGINT;

CREATE TABLE IF NOT EXISTS staff_recovery_codes (
  recovery_code_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  staff_id UUID NOT NULL
    REFERENCES staff_users(staff_id)
    ON DELETE CASCADE,
  code_hash TEXT NOT NULL UNIQUE,
  used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_staff_recovery_codes_staff
  ON staff_recovery_codes(staff_id);

CREATE INDEX IF NOT EXISTS idx_staff_recovery_codes_unused
  ON staff_recovery_codes(staff_id)
  WHERE used_at IS NULL;

COMMENT ON COLUMN staff_users.mfa_secret_ciphertext IS
  'AES-256-GCM encrypted Base32 TOTP secret.';

COMMENT ON COLUMN staff_users.mfa_secret_iv IS
  'Base64 AES-GCM initialization vector for the encrypted TOTP secret.';

COMMENT ON COLUMN staff_users.mfa_secret_tag IS
  'Base64 AES-GCM authentication tag for the encrypted TOTP secret.';

COMMENT ON COLUMN staff_users.mfa_enabled IS
  'True only after the staff member verifies the first authenticator code.';

COMMENT ON COLUMN staff_users.mfa_last_used_step IS
  'Most recently accepted TOTP time step, used to prevent replay.';

COMMIT;
