-- ====================================================================
-- Admin 2FA login challenges (2026-09-23) — see src/lib/auth/loginChallenge.ts
--
-- Replaces the old flow where /api/auth/2fa/verify and /resend trusted a
-- bare `employeeId` from the request body (anyone who knew an employee's id
-- could spam codes or brute-force them, and resend reset the attempt count).
-- A challenge is created only after a correct password, is identified by a
-- random token held in an HttpOnly cookie (only its hash is stored), is bound
-- to the browser that passed the password step, and has hard caps on wrong
-- codes and resends that a resend does NOT reset.
-- Safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `login_challenges` (
  `id`            VARCHAR(36)  NOT NULL DEFAULT (UUID()),
  `employee_id`   VARCHAR(36)  NOT NULL,
  `token_hash`    CHAR(64)     NOT NULL COMMENT 'SHA-256 of the challenge cookie token',
  `code_hash`     CHAR(64)     NOT NULL COMMENT 'HMAC of the current 6-digit code',
  `device_hash`   CHAR(64)     NOT NULL COMMENT 'Browser that passed the password step',
  `attempts`      TINYINT      NOT NULL DEFAULT 0 COMMENT 'Wrong codes entered (never reset by resend)',
  `resend_count`  TINYINT      NOT NULL DEFAULT 0,
  `last_sent_at`  DATETIME     NOT NULL,
  `expires_at`    DATETIME     NOT NULL,
  `consumed_at`   DATETIME     NULL DEFAULT NULL,
  `ip_address`    VARCHAR(45)  NULL DEFAULT NULL,
  `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_login_challenges_token` (`token_hash`),
  INDEX `idx_login_challenges_employee` (`employee_id`),
  INDEX `idx_login_challenges_expires` (`expires_at`),
  CONSTRAINT `fk_login_challenges_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
