<?php
session_start();
require_once __DIR__ . '/config/db.php';

echo "<h2>Setting Up Advanced Authentication Features</h2>";

// 1. Alter users table to add new fields
echo "<h3>1. Adding new fields to users table...</h3>";

$fields_to_add = [
    "email_verified" => "ALTER TABLE users ADD COLUMN email_verified TINYINT(1) DEFAULT 0 AFTER email",
    "email_verified_at" => "ALTER TABLE users ADD COLUMN email_verified_at TIMESTAMP NULL AFTER email_verified",
    "two_factor_enabled" => "ALTER TABLE users ADD COLUMN two_factor_enabled TINYINT(1) DEFAULT 0 AFTER status",
    "two_factor_secret" => "ALTER TABLE users ADD COLUMN two_factor_secret VARCHAR(255) NULL AFTER two_factor_enabled",
    "password_changed_at" => "ALTER TABLE users ADD COLUMN password_changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER password"
];

foreach ($fields_to_add as $field_name => $alter_sql) {
    $check = $conn->query("SHOW COLUMNS FROM users LIKE '$field_name'");
    if ($check->num_rows === 0) {
        if ($conn->query($alter_sql)) {
            echo "<p style='color: green;'>✓ Added column: <strong>$field_name</strong></p>";
        } else {
            echo "<p style='color: orange;'>⚠ Column $field_name might already exist or error: " . $conn->error . "</p>";
        }
    } else {
        echo "<p style='color: blue;'>✓ Column <strong>$field_name</strong> already exists</p>";
    }
}

// 2. Create OTP table
echo "<h3>2. Creating OTP verification table...</h3>";

$check_otp = $conn->query("SHOW TABLES LIKE 'otp_verification'");
if ($check_otp->num_rows === 0) {
    $otp_sql = "CREATE TABLE otp_verification (
        id INT PRIMARY KEY AUTO_INCREMENT,
        email VARCHAR(100) NOT NULL,
        otp_code VARCHAR(10) NOT NULL,
        purpose ENUM('email_verification', 'password_reset', '2fa') DEFAULT 'email_verification',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP NULL DEFAULT NULL,
        verified TINYINT(1) DEFAULT 0,
        INDEX idx_email (email),
        INDEX idx_otp (otp_code)
    )";
    
    if ($conn->query($otp_sql)) {
        echo "<p style='color: green;'>✓ OTP verification table created</p>";
    } else {
        echo "<p style='color: red;'>✗ Error: " . $conn->error . "</p>";
    }
} else {
    echo "<p style='color: blue;'>✓ OTP table already exists</p>";
}

// 3. Create password reset tokens table
echo "<h3>3. Creating password reset table...</h3>";

$check_reset = $conn->query("SHOW TABLES LIKE 'password_resets'");
if ($check_reset->num_rows === 0) {
    $reset_sql = "CREATE TABLE password_resets (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        token VARCHAR(255) UNIQUE NOT NULL,
        email VARCHAR(100) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP NULL DEFAULT NULL,
        used TINYINT(1) DEFAULT 0,
        INDEX idx_user (user_id),
        INDEX idx_token (token),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )";
    
    if ($conn->query($reset_sql)) {
        echo "<p style='color: green;'>✓ Password reset table created</p>";
    } else {
        echo "<p style='color: red;'>✗ Error: " . $conn->error . "</p>";
    }
} else {
    echo "<p style='color: blue;'>✓ Password reset table already exists</p>";
}

// 4. Create audit log table
echo "<h3>4. Creating audit log table...</h3>";

$check_audit = $conn->query("SHOW TABLES LIKE 'audit_logs'");
if ($check_audit->num_rows === 0) {
    $audit_sql = "CREATE TABLE audit_logs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT,
        action VARCHAR(100) NOT NULL,
        details TEXT,
        ip_address VARCHAR(45),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user (user_id),
        INDEX idx_action (action)
    )";
    
    if ($conn->query($audit_sql)) {
        echo "<p style='color: green;'>✓ Audit log table created</p>";
    } else {
        echo "<p style='color: red;'>✗ Error: " . $conn->error . "</p>";
    }
} else {
    echo "<p style='color: blue;'>✓ Audit log table already exists</p>";
}

echo "<hr>";
echo "<h3>✅ Database Setup Complete!</h3>";
echo "<p><a href='login.php' style='background: #D4A574; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;'>← Back to Login</a></p>";

$conn->close();
?>
