-- ===========================================================================
-- Migration: Two-factor authentication (authenticator app / TOTP)
-- Date: 2026-07-29
--
-- Adds an OPTIONAL second login factor on top of email + password: a 6-digit
-- code from an authenticator app (Google Authenticator, Authy, Microsoft
-- Authenticator, 1Password…). A user turns it on for their own account from
-- their Profile. Password login is untouched for anyone who doesn't enable it.
--
--   users.totp_secret        — base32 shared secret (set at setup; NULL when off)
--   users.totp_enabled       — whether the second step is enforced at login
--   totp_recovery_codes      — one-time codes to get in if the phone is lost
--   two_factor_challenges    — short-lived tokens bridging the two login steps
-- Idempotent.
-- ===========================================================================

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS totp_secret  VARCHAR(64)      NULL AFTER platform_perms,
  ADD COLUMN IF NOT EXISTS totp_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER totp_secret;

CREATE TABLE IF NOT EXISTS totp_recovery_codes (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  -- bcrypt hash of the code; the plaintext is shown to the user exactly once.
  code_hash  VARCHAR(255) NOT NULL,
  used_at    TIMESTAMP    NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_totp_recovery_user (user_id),
  CONSTRAINT fk_totp_recovery_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS two_factor_challenges (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  -- opaque token issued after a correct password, exchanged (with a code) for a
  -- session at /auth/login/2fa. Only exists once the password is verified.
  token      CHAR(64)     NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  -- wrong-code counter; the row self-destructs past a small cap so the 6-digit
  -- space can't be brute-forced within one challenge.
  attempts   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at TIMESTAMP    NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_2fa_token (token),
  KEY idx_2fa_expires (expires_at),
  CONSTRAINT fk_2fa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;