<?php
// Create database and tables automatically
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h2>Database Setup</h2>";

// Try to connect without database
try {
    $pdo = new PDO("mysql:host=localhost;charset=utf8mb4", "root", "root", [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
    echo "<p style='color: green;'>✓ Connected to MySQL server</p>";
    
    // Create database
    try {
        $pdo->exec("CREATE DATABASE IF NOT EXISTS bidda CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        echo "<p style='color: green;'>✓ Database 'bidda' created successfully!</p>";
        
        // Select the database
        $pdo->exec("USE bidda");
        
        // Create basic tables
        $tables = [
            "CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                role ENUM('admin', 'teacher', 'student', 'parent') NOT NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS classes (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                section VARCHAR(10) NOT NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS subjects (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                code VARCHAR(20) UNIQUE NOT NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )"
        ];
        
        foreach ($tables as $sql) {
            try {
                $pdo->exec($sql);
                echo "<p style='color: green;'>✓ Table created</p>";
            } catch(Exception $e) {
                echo "<p style='color: red;'>✗ Table error: " . $e->getMessage() . "</p>";
            }
        }
        
        echo "<hr>";
        echo "<h3 style='color: green;'>✓ Setup Complete!</h3>";
        echo "<p>You can now visit: <a href='index.php'>http://localhost/bidda/</a></p>";
        
    } catch(Exception $e) {
        echo "<p style='color: red;'>✗ Error creating database: " . $e->getMessage() . "</p>";
    }
    
} catch(PDOException $e) {
    echo "<p style='color: red;'>✗ Cannot connect to MySQL: " . $e->getMessage() . "</p>";
    echo "<p>Make sure MAMP is running and MySQL is started.</p>";
}
