<?php
/**
 * Setup OTP Email Verification
 * Creates necessary database tables and updates users table
 */

require_once __DIR__ . '/config/config.php';
require_once __DIR__ . '/classes/Database.php';

$db = Database::getInstance();
$pdo = $db->getConnection();

try {
    echo "Setting up OTP Email Verification System...\n\n";
    
    // 1. Create email_otps table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS email_otps (
            id INTEGER PRIMARY KEY AUTO_INCREMENT,
            email VARCHAR(255) NOT NULL,
            otp VARCHAR(6) NOT NULL,
            attempts INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            expires_at DATETIME NOT NULL,
            UNIQUE(email)
        )
    ");
    echo "✅ Created email_otps table\n";
    
    // 2. Check and add columns to users table if needed
    $result = $pdo->query("SHOW COLUMNS FROM users")->fetchAll(PDO::FETCH_ASSOC);
    $columnNames = array_column($result, 'Field');
    
    $updates = [];
    
    if (!in_array('email_verified', $columnNames)) {
        try {
            $pdo->exec("ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT 0");
            $updates[] = "Added email_verified column";
        } catch (Exception $e) {}
    }
    
    if (!in_array('email_verified_at', $columnNames)) {
        try {
            $pdo->exec("ALTER TABLE users ADD COLUMN email_verified_at DATETIME NULL");
            $updates[] = "Added email_verified_at column";
        } catch (Exception $e) {}
    }
    
    if (!in_array('otp_bypass_by_admin', $columnNames)) {
        try {
            $pdo->exec("ALTER TABLE users ADD COLUMN otp_bypass_by_admin BOOLEAN DEFAULT 0");
            $updates[] = "Added otp_bypass_by_admin column";
        } catch (Exception $e) {}
    }
    
    if (count($updates) > 0) {
        foreach ($updates as $update) {
            echo "✅ $update\n";
        }
    } else {
        echo "✓ All users table columns already exist\n";
    }
    
    echo "\n✅ OTP Email Verification Setup Complete!\n";
    echo "\n📋 Summary:\n";
    echo "  • email_otps table created for OTP storage\n";
    echo "  • Users can now register with OTP verification\n";
    echo "  • Admin can bypass OTP verification if needed\n";
    echo "  • OTP expires in 5 minutes (configurable)\n";
    echo "\n✨ System is ready to use!\n";
    
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'already exists') !== false || 
        strpos($e->getMessage(), 'duplicate') !== false) {
        echo "✓ Tables already exist\n";
    } else {
        echo "Error: " . $e->getMessage() . "\n";
        error_log($e->getMessage());
    }
}
?>
