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

echo "<h2>Database Schema Verification</h2>";

$tables = ['exams', 'exam_subjects', 'results', 'exam_attendance', 'exam_room_duties', 'exam_rooms'];

foreach ($tables as $table) {
    try {
        $stmt = $pdo->prepare("DESCRIBE `$table`");
        $stmt->execute();
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<h3>Table: $table</h3>";
        echo "<ul>";
        foreach ($columns as $column) {
            echo "<li>{$column['Field']} ({$column['Type']})";
            if ($column['Key'] == 'PRI') echo " - PRIMARY KEY";
            if ($column['Key'] == 'MUL') echo " - INDEX";
            echo "</li>";
        }
        echo "</ul>";
    } catch (Exception $e) {
        echo "<p style='color: red;'>Error describing table $table: " . $e->getMessage() . "</p>";
    }
}

// Check foreign key relationships
echo "<h3>Foreign Key Relationships</h3>";
try {
    $stmt = $pdo->query("
        SELECT 
            TABLE_NAME,
            COLUMN_NAME,
            CONSTRAINT_NAME,
            REFERENCED_TABLE_NAME,
            REFERENCED_COLUMN_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE REFERENCED_TABLE_NAME IS NOT NULL
        AND TABLE_SCHEMA = DATABASE()
        ORDER BY TABLE_NAME
    ");
    
    $fks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($fks)) {
        echo "<p>No foreign key relationships found.</p>";
    } else {
        echo "<table border='1' cellpadding='5' cellspacing='0'>";
        echo "<tr><th>Table</th><th>Column</th><th>References</th><th>Referenced Column</th></tr>";
        foreach ($fks as $fk) {
            echo "<tr>";
            echo "<td>{$fk['TABLE_NAME']}</td>";
            echo "<td>{$fk['COLUMN_NAME']}</td>";
            echo "<td>{$fk['REFERENCED_TABLE_NAME']}</td>";
            echo "<td>{$fk['REFERENCED_COLUMN_NAME']}</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "<p style='color: red;'>Error checking foreign keys: " . $e->getMessage() . "</p>";
}

echo "<p><a href='routine.php'>Back to Routine Page</a></p>";
?>