-- ====================================================================
-- Session hardening (2026-09-23) — admin (user_sessions) + customer
-- (customer_sessions). See src/lib/auth/sessionCore.ts.
--   token_hash       SHA-256 of the cookie token. The raw token is never
--                    stored again, so a DB/backup leak can't be replayed.
--   prev_token_hash  the token before the last rotation (short grace window
--                    for in-flight requests; reuse after that = stolen copy).
--   rotated_at       when the token was last rotated.
--   device_hash      fingerprint of the browser the session belongs to.
--   is_persistent    0 = browser-session cookie (customer without "remember me"),
--                    so a rotated cookie keeps the same lifetime type.
--
-- Existing sessions keep working: their raw token (the old `id`) is hashed
-- into token_hash and the id is replaced by a random UUID, so users are not
-- logged out and the raw token disappears from the DB.
-- Plain statements only (no DELIMITER/procedures), safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

-- ── user_sessions ────────────────────────────────────────────────────
SET @has = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_sessions' AND COLUMN_NAME = 'token_hash');
SET @sql = IF(@has = 0,
  'ALTER TABLE `user_sessions`
     ADD COLUMN `token_hash` CHAR(64) NULL DEFAULT NULL AFTER `id`,
     ADD COLUMN `prev_token_hash` CHAR(64) NULL DEFAULT NULL AFTER `token_hash`,
     ADD COLUMN `rotated_at` DATETIME NULL DEFAULT NULL AFTER `prev_token_hash`,
     ADD COLUMN `device_hash` CHAR(64) NULL DEFAULT NULL AFTER `rotated_at`,
     ADD COLUMN `is_persistent` TINYINT(1) NOT NULL DEFAULT 1 AFTER `device_hash`',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

UPDATE `user_sessions`
   SET token_hash = SHA2(id, 256), rotated_at = NOW(), id = UUID()
 WHERE token_hash IS NULL AND CHAR_LENGTH(id) = 64;

UPDATE `user_sessions`
   SET revoked_at = NOW(), revoke_reason = 'forced'
 WHERE token_hash IS NULL AND revoked_at IS NULL;

SET @has = (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_sessions' AND INDEX_NAME = 'uk_token_hash');
SET @sql = IF(@has = 0,
  'ALTER TABLE `user_sessions` ADD UNIQUE KEY `uk_token_hash` (`token_hash`), ADD INDEX `idx_prev_token_hash` (`prev_token_hash`)',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- ── customer_sessions ────────────────────────────────────────────────
SET @has = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customer_sessions' AND COLUMN_NAME = 'token_hash');
SET @sql = IF(@has = 0,
  'ALTER TABLE `customer_sessions`
     ADD COLUMN `token_hash` CHAR(64) NULL DEFAULT NULL AFTER `id`,
     ADD COLUMN `prev_token_hash` CHAR(64) NULL DEFAULT NULL AFTER `token_hash`,
     ADD COLUMN `rotated_at` DATETIME NULL DEFAULT NULL AFTER `prev_token_hash`,
     ADD COLUMN `device_hash` CHAR(64) NULL DEFAULT NULL AFTER `rotated_at`,
     ADD COLUMN `is_persistent` TINYINT(1) NOT NULL DEFAULT 1 AFTER `device_hash`',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

UPDATE `customer_sessions`
   SET token_hash = SHA2(id, 256), rotated_at = NOW(), id = UUID()
 WHERE token_hash IS NULL AND CHAR_LENGTH(id) = 64;

UPDATE `customer_sessions`
   SET revoked_at = NOW(), revoke_reason = 'forced'
 WHERE token_hash IS NULL AND revoked_at IS NULL;

SET @has = (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customer_sessions' AND INDEX_NAME = 'uk_token_hash');
SET @sql = IF(@has = 0,
  'ALTER TABLE `customer_sessions` ADD UNIQUE KEY `uk_token_hash` (`token_hash`), ADD INDEX `idx_prev_token_hash` (`prev_token_hash`)',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
