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

// Set session for testing
$_SESSION['user_id'] = 1;
$_SESSION['user_role'] = 'admin';

echo "<h2>Database Fix - Add Missing Columns</h2>";

try {
    // Check current students table structure
    $stmt = $pdo->query("DESCRIBE students");
    $columns = $stmt->fetchAll();
    $existing_columns = array_column($columns, 'Field');
    
    echo "<h3>Current Columns:</h3>";
    echo "<ul>";
    foreach ($existing_columns as $col) {
        echo "<li>$col</li>";
    }
    echo "</ul>";
    
    // Check for missing columns and add them
    $missing_columns = [];
    
    if (!in_array('session', $existing_columns)) {
        $missing_columns[] = 'session';
        $pdo->exec("ALTER TABLE students ADD COLUMN session VARCHAR(10) DEFAULT '2024'");
        echo "<p style='color: green;'>✅ Added 'session' column</p>";
    }
    
    if (empty($missing_columns)) {
        echo "<p style='color: green;'>✅ All required columns exist!</p>";
    }
    
    // Update existing students with session if they don't have it
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM students WHERE session IS NULL OR session = ''");
    $null_session_count = $stmt->fetch()['count'];
    
    if ($null_session_count > 0) {
        $pdo->exec("UPDATE students SET session = '2024' WHERE session IS NULL OR session = ''");
        echo "<p style='color: blue;'>📝 Updated $null_session_count students with default session '2024'</p>";
    }
    
    // Show final structure
    $stmt = $pdo->query("DESCRIBE students");
    $columns = $stmt->fetchAll();
    
    echo "<h3>Final Table Structure:</h3>";
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Column</th><th>Type</th><th>Null</th><th>Default</th></tr>";
    foreach ($columns as $col) {
        echo "<tr>";
        echo "<td>{$col['Field']}</td>";
        echo "<td>{$col['Type']}</td>";
        echo "<td>{$col['Null']}</td>";
        echo "<td>{$col['Default']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    // Test a simple query
    echo "<h3>Test Query:</h3>";
    $stmt = $pdo->query("SELECT id, first_name, last_name, roll_number, session FROM students WHERE status = 'active' LIMIT 3");
    $test_results = $stmt->fetchAll();
    
    if ($test_results) {
        echo "<table border='1' style='border-collapse: collapse;'>";
        echo "<tr><th>ID</th><th>Name</th><th>Roll</th><th>Session</th></tr>";
        foreach ($test_results as $row) {
            echo "<tr>";
            echo "<td>{$row['id']}</td>";
            echo "<td>{$row['first_name']} {$row['last_name']}</td>";
            echo "<td>{$row['roll_number']}</td>";
            echo "<td>{$row['session']}</td>";
            echo "</tr>";
        }
        echo "</table>";
        echo "<p style='color: green;'>✅ Database is ready!</p>";
    } else {
        echo "<p style='color: orange;'>⚠️ No student data found. <a href='create_demo_students.php'>Create demo data</a></p>";
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}
?>

<div style="margin-top: 20px;">
    <a href="test_api_simple.php" style="background: #007bff; color: white; padding: 10px; text-decoration: none; border-radius: 5px;">Test API</a>
    <a href="create_demo_students.php" style="background: #28a745; color: white; padding: 10px; text-decoration: none; border-radius: 5px;">Create Demo Data</a>
    <a href="invoice.php" style="background: #17a2b8; color: white; padding: 10px; text-decoration: none; border-radius: 5px;">Go to Invoice</a>
</div>