<?php
// Database Migration for OTP and Email Verification
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'granny_restaurant');

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

if ($conn->connect_error) {
    die(json_encode(['success' => false, 'message' => 'Connection failed: ' . $conn->connect_error]));
}

$conn->set_charset("utf8");

// Add new columns to users table if they don't exist
$alterUsersSql = "ALTER TABLE users 
    ADD COLUMN IF NOT EXISTS is_email_verified BOOLEAN DEFAULT 0,
    ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS otp_reset_token VARCHAR(100) DEFAULT NULL";

if (!$conn->query($alterUsersSql)) {
    echo "Error altering users table: " . $conn->error . "<br>";
}

// Create OTP table for email verification
$otpTableSql = "CREATE TABLE IF NOT EXISTS email_otps (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    otp VARCHAR(6) NOT NULL,
    purpose VARCHAR(50) NOT NULL DEFAULT 'signup',
    attempts INT DEFAULT 0,
    is_verified BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP DEFAULT (DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 15 MINUTE)),
    verified_at TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_otp (otp)
)";

if ($conn->query($otpTableSql)) {
    echo "✓ OTP table created/verified successfully<br>";
} else {
    echo "Error creating OTP table: " . $conn->error . "<br>";
}

// Create password reset table
$passwordResetTableSql = "CREATE TABLE IF NOT EXISTS password_resets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    token VARCHAR(100) NOT NULL UNIQUE,
    otp VARCHAR(6) NOT NULL,
    attempts INT DEFAULT 0,
    is_verified BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP DEFAULT (DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 30 MINUTE)),
    verified_at TIMESTAMP NULL,
    reset_at TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_token (token),
    INDEX idx_otp (otp)
)";

if ($conn->query($passwordResetTableSql)) {
    echo "✓ Password reset table created/verified successfully<br>";
} else {
    echo "Error creating password reset table: " . $conn->error . "<br>";
}

// Create pending registrations table to store temporary signup data
$pendingRegistrationsTableSql = "CREATE TABLE IF NOT EXISTS pending_registrations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    otp_code VARCHAR(6),
    attempts INT DEFAULT 0,
    is_verified BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP DEFAULT (DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)),
    verified_at TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_otp (otp_code)
)";

if ($conn->query($pendingRegistrationsTableSql)) {
    echo "✓ Pending registrations table created/verified successfully<br>";
} else {
    echo "Error creating pending registrations table: " . $conn->error . "<br>";
}

$conn->close();
echo "<br><strong>OTP and Email Verification Database Setup Complete!</strong>";
?>
