<?php
require_once 'config/database.php';

echo "<h2>Database Table Check</h2>";

try {
    // Check academic_sessions table
    $stmt = $pdo->query("SHOW TABLES LIKE 'academic_sessions'");
    if ($stmt->rowCount() > 0) {
        echo "<p style='color: green;'>✓ academic_sessions table exists</p>";

        // Check table structure
        $stmt = $pdo->query("DESCRIBE academic_sessions");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "<h3>academic_sessions columns:</h3><ul>";
        foreach ($columns as $column) {
            echo "<li>{$column['Field']} ({$column['Type']})</li>";
        }
        echo "</ul>";

        // Check data
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM academic_sessions");
        $count = $stmt->fetch()['count'];
        echo "<p>Total sessions: $count</p>";

        // Show active sessions
        $stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY start_date DESC LIMIT 5");
        $sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
        echo "<p>Active sessions: " . implode(', ', $sessions) . "</p>";

    } else {
        echo "<p style='color: red;'>✗ academic_sessions table does not exist</p>";
    }

    // Check teacher_leaves table
    $stmt = $pdo->query("SHOW TABLES LIKE 'teacher_leaves'");
    if ($stmt->rowCount() > 0) {
        echo "<p style='color: green;'>✓ teacher_leaves table exists</p>";

        // Check table structure
        $stmt = $pdo->query("DESCRIBE teacher_leaves");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "<h3>teacher_leaves columns:</h3><ul>";
        foreach ($columns as $column) {
            echo "<li>{$column['Field']} ({$column['Type']})</li>";
        }
        echo "</ul>";

        // Check data
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM teacher_leaves");
        $count = $stmt->fetch()['count'];
        echo "<p>Total leave applications: $count</p>";

    } else {
        echo "<p style='color: red;'>✗ teacher_leaves table does not exist</p>";
    }

    // Check teachers table
    $stmt = $pdo->query("SHOW TABLES LIKE 'teachers'");
    if ($stmt->rowCount() > 0) {
        echo "<p style='color: green;'>✓ teachers table exists</p>";

        // Check data
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM teachers");
        $count = $stmt->fetch()['count'];
        echo "<p>Total teachers: $count</p>";

    } else {
        echo "<p style='color: red;'>✗ teachers table does not exist</p>";
    }

} catch (Exception $e) {
    echo "<p style='color: red;'>Database error: " . htmlspecialchars($e->getMessage()) . "</p>";
}
?>
