<?php
/**
 * Ensure required tables exist
 */

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once __DIR__ . '/config/database.php';

echo "<h2>Ensuring Required Tables Exist</h2>";

try {
    // Check if login_attempts table exists
    echo "<h3>1. Checking login_attempts table...</h3>";
    $result = $pdo->query("SHOW TABLES LIKE 'login_attempts'");
    if ($result->rowCount() == 0) {
        echo "<p>Creating login_attempts table...</p>";
        $pdo->exec("
            CREATE TABLE login_attempts (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50),
                ip_address VARCHAR(45) NOT NULL,
                user_agent VARCHAR(255),
                attempt_status ENUM('success', 'failed') NOT NULL,
                failure_reason VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_ip_time (ip_address, created_at),
                INDEX idx_username (username)
            )
        ");
        echo "<p style='color: green;'>✅ login_attempts table created successfully!</p>";
    } else {
        echo "<p style='color: green;'>✅ login_attempts table already exists</p>";
    }
    
    // Check if parents table exists
    echo "<h3>2. Checking parents table...</h3>";
    $result = $pdo->query("SHOW TABLES LIKE 'parents'");
    if ($result->rowCount() == 0) {
        echo "<p>Creating parents table...</p>";
        $pdo->exec("
            CREATE TABLE parents (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT UNIQUE,
                father_name VARCHAR(100) NOT NULL,
                father_phone VARCHAR(20),
                father_email VARCHAR(100),
                father_occupation VARCHAR(100),
                mother_name VARCHAR(100),
                mother_phone VARCHAR(20),
                mother_email VARCHAR(100),
                mother_occupation VARCHAR(100),
                guardian_name VARCHAR(100),
                guardian_phone VARCHAR(20),
                guardian_email VARCHAR(100),
                guardian_relation VARCHAR(50),
                address TEXT,
                emergency_contact VARCHAR(20),
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
            )
        ");
        echo "<p style='color: green;'>✅ parents table created successfully!</p>";
    } else {
        echo "<p style='color: green;'>✅ parents table already exists</p>";
    }
    
    echo "<h3>3. Testing database connection...</h3>";
    $test = $pdo->query("SELECT 1 as test")->fetch();
    if ($test && $test['test'] == 1) {
        echo "<p style='color: green;'>✅ Database connection working properly</p>";
    } else {
        echo "<p style='color: red;'>❌ Database connection test failed</p>";
    }
    
    echo "<h3>4. All checks completed!</h3>";
    echo "<p>You can now try accessing the parent login and registration pages.</p>";
    echo "<ul>";
    echo "<li><a href='parent_login.php'>Parent Login</a></li>";
    echo "<li><a href='parent_register.php'>Parent Registration</a></li>";
    echo "</ul>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}
?>