-- =====================================================
-- OTP Verification System Migration
-- Run this SQL in phpMyAdmin on your production database
-- =====================================================

-- Step 1: Add OTP columns to clients table
ALTER TABLE clients
  ADD COLUMN otp_code VARCHAR(255) NULL AFTER email_type,
  ADD COLUMN otp_expiry DATETIME NULL AFTER otp_code,
  ADD COLUMN otp_attempts INT DEFAULT 0 AFTER otp_expiry,
  ADD COLUMN email_verified TINYINT(1) DEFAULT 0 AFTER otp_attempts,
  ADD COLUMN otp_status ENUM('pending','verified','expired') DEFAULT NULL AFTER email_verified,
  ADD COLUMN otp_resend_count INT DEFAULT 0 AFTER otp_status,
  ADD COLUMN otp_resend_reset_at DATETIME NULL AFTER otp_resend_count;

-- Step 2: Migrate existing users
-- In the old system: email_type=1 means UNVERIFIED (guards redirect), email_type=2 means VERIFIED/default
UPDATE clients SET email_verified = 1 WHERE email_type = 2;
UPDATE clients SET email_verified = 0 WHERE email_type = 1;

-- Step 3: Add OTP admin settings to settings table
ALTER TABLE settings
  ADD COLUMN otp_expiry_minutes INT DEFAULT 10,
  ADD COLUMN otp_max_attempts INT DEFAULT 5,
  ADD COLUMN otp_resend_cooldown INT DEFAULT 60,
  ADD COLUMN otp_max_resend_per_hour INT DEFAULT 3,
  ADD COLUMN otp_auto_delete_hours INT DEFAULT 48,
  ADD COLUMN otp_auto_delete_enabled TINYINT(1) DEFAULT 0;

-- Step 4: Add indexes for performance
ALTER TABLE clients ADD INDEX idx_email_verified (email_verified);
ALTER TABLE clients ADD INDEX idx_otp_status (otp_status);
