-- ====================================================================
-- Auth hardening Phase 3 (2026-09-23)
--   login_attempts            failed-password tracking per (account, client IP).
--                             Replaces the account-wide lock that let anyone
--                             lock any account (incl. the super admin) by
--                             typing 5 wrong passwords. See loginThrottle.ts.
--   employee_password_resets  admin "forgot password" links (hashed tokens).
--   users.created_via         'guest_checkout' marks accounts auto-created at
--                             checkout, so the real owner of that email can
--                             claim it (email squatting fix).
-- Plain statements only, safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `scope`          ENUM('admin','customer') NOT NULL,
  `subject_hash`   CHAR(64)    NOT NULL COMMENT 'SHA-256 of the lower-cased email typed (existing account or not)',
  `ip_address`     VARCHAR(45) NOT NULL,
  `fail_count`     INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Failures since the last lock',
  `lock_count`     INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Locks so far; each doubles the next lock',
  `locked_until`   DATETIME    NULL DEFAULT NULL,
  `last_failed_at` DATETIME    NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_login_attempts` (`scope`, `subject_hash`, `ip_address`),
  KEY `idx_login_attempts_last_failed` (`last_failed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_password_resets` (
  `id`          VARCHAR(36) NOT NULL DEFAULT (UUID()),
  `employee_id` VARCHAR(36) NOT NULL,
  `token_hash`  CHAR(64)    NOT NULL COMMENT 'SHA-256 of the token in the emailed link',
  `expires_at`  DATETIME    NOT NULL,
  `used_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_employee_password_resets_token` (`token_hash`),
  KEY `idx_employee_password_resets_employee` (`employee_id`),
  CONSTRAINT `fk_employee_password_resets_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @has = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'created_via');
SET @sql = IF(@has = 0,
  'ALTER TABLE `users` ADD COLUMN `created_via` ENUM(''register'',''guest_checkout'') NOT NULL DEFAULT ''register'' AFTER `referred_by`',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- Browsers each account has signed in from (device fingerprint only), so a
-- sign-in from a new one can trigger an email alert. See securityAlerts.ts.
CREATE TABLE IF NOT EXISTS `known_devices` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `scope`        ENUM('admin','customer') NOT NULL,
  `owner_id`     VARCHAR(100) NOT NULL,
  `device_hash`  CHAR(64)     NOT NULL,
  `first_seen`   DATETIME     NOT NULL,
  `last_seen`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_known_devices` (`scope`, `owner_id`, `device_hash`),
  KEY `idx_known_devices_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Best-effort backfill: accounts that checkout auto-created before this
-- column existed (unverified, never logged in, created together with their
-- first order). A wrong guess is harmless — registering with that email then
-- emails a "set your password" link instead of saying "already exists".
UPDATE `users` u
   SET u.created_via = 'guest_checkout'
 WHERE u.created_via = 'register'
   AND u.email_verified = 0
   AND u.last_login_at IS NULL
   AND EXISTS (SELECT 1 FROM `orders` o
                WHERE o.user_id = u.id
                  AND ABS(TIMESTAMPDIFF(SECOND, u.created_at, o.created_at)) <= 10);

-- rotation_confirmed: 0 right after a token rotation, 1 once the browser has
-- presented the NEW token. Until then the previous token is not treated as
-- "stolen" (the Set-Cookie may simply never have reached the browser — an
-- aborted prefetch/navigation), which caused false "token reuse" sign-outs.
SET @has = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_sessions' AND COLUMN_NAME = 'rotation_confirmed');
SET @sql = IF(@has = 0,
  'ALTER TABLE `user_sessions` ADD COLUMN `rotation_confirmed` TINYINT(1) NOT NULL DEFAULT 1 AFTER `rotated_at`',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @has = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customer_sessions' AND COLUMN_NAME = 'rotation_confirmed');
SET @sql = IF(@has = 0,
  'ALTER TABLE `customer_sessions` ADD COLUMN `rotation_confirmed` TINYINT(1) NOT NULL DEFAULT 1 AFTER `rotated_at`',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
