-- ============================================================
-- Phase B — Auth Hardening — Users Table Migration (TEST DB)
-- Date: 2026-07-21
-- File: /migrations/2026_07_21_phase_b_users_auth_hardening.sql
-- Target DB: jewellersbd_test  (⚠️ live jewellersbd_erp এ চালাবেন না)
-- ============================================================
-- Idempotent — বারবার চালালেও error দিবে না, "already exists" আসবে
-- ============================================================

-- ⚠️ SAFETY CHECK: phpMyAdmin এ database dropdown দেখে নিশ্চিত হন
--    "jewellersbd_test" select করা আছে, তারপরে চালান
-- USE jewellersbd_test;  -- phpMyAdmin এ USE লাগে না (dropdown থেকেই select)

START TRANSACTION;

-- failed_login_attempts
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'failed_login_attempts');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `failed_login_attempts` INT NOT NULL DEFAULT 0 COMMENT "Consecutive failed login count" AFTER `is_deleted`',
  'SELECT "failed_login_attempts already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- locked_until
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'locked_until');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `locked_until` DATETIME NULL DEFAULT NULL COMMENT "Account locked until this time" AFTER `failed_login_attempts`',
  'SELECT "locked_until already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- last_login_ip
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'last_login_ip');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `last_login_ip` VARCHAR(45) NULL DEFAULT NULL COMMENT "IPv4/IPv6 of last successful login" AFTER `last_login_at`',
  'SELECT "last_login_ip already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- last_login_ua_hash
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'last_login_ua_hash');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `last_login_ua_hash` VARCHAR(64) NULL DEFAULT NULL COMMENT "SHA256 hash of user agent" AFTER `last_login_ip`',
  'SELECT "last_login_ua_hash already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- password_changed_at
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'password_changed_at');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `password_changed_at` DATETIME NULL DEFAULT NULL COMMENT "Last password change time" AFTER `last_login_ua_hash`',
  'SELECT "password_changed_at already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Index on locked_until
SET @idx_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
    AND INDEX_NAME = 'idx_users_locked_until');
SET @sql = IF(@idx_exists = 0,
  'ALTER TABLE `users` ADD INDEX `idx_users_locked_until` (`locked_until`)',
  'SELECT "idx_users_locked_until already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Backfill
UPDATE `users`
SET `password_changed_at` = COALESCE(`created_at`, NOW())
WHERE `password_changed_at` IS NULL;

COMMIT;

-- Verification
-- SHOW COLUMNS FROM `users`;
-- SELECT id, name, email, failed_login_attempts, locked_until,
--        last_login_ip, password_changed_at FROM `users`;

-- ============================================================
-- ROLLBACK (uncomment to run)
-- ============================================================
-- START TRANSACTION;
-- ALTER TABLE `users` DROP INDEX `idx_users_locked_until`;
-- ALTER TABLE `users` DROP COLUMN `password_changed_at`;
-- ALTER TABLE `users` DROP COLUMN `last_login_ua_hash`;
-- ALTER TABLE `users` DROP COLUMN `last_login_ip`;
-- ALTER TABLE `users` DROP COLUMN `locked_until`;
-- ALTER TABLE `users` DROP COLUMN `failed_login_attempts`;
-- COMMIT;
