<?php
include 'db_connection.php';
include 'header.php';
?>

<!DOCTYPE html>
<html>
<head>
    <title>Conflict Dashboard</title>

    <style>
        body {
            font-family: Arial;
            background: #f4f6f9;
        }

        .container {
            width: 90%;
            margin: auto;
        }

        .alert-box {
            padding: 15px;
            margin: 10px 0;
            border-radius: 8px;
        }

        .danger {
            background: #f8d7da;
            color: #721c24;
            border-left: 5px solid #dc3545;
        }

        .warning {
            background: #fff3cd;
            color: #856404;
            border-left: 5px solid #ffc107;
        }

        .success {
            background: #d4edda;
            color: #155724;
            border-left: 5px solid #28a745;
        }

        h2 {
            margin-top: 20px;
        }
    </style>
</head>
<body>

<div class="container">

<h2>🚨 Timetable Conflict Dashboard</h2>

<?php
/* =========================
   1. TEACHER DOUBLE BOOKING
========================= */

$sql1 = "
SELECT teacher_id, day, period, COUNT(*) as total
FROM timetable
GROUP BY teacher_id, day, period
HAVING total > 1
";

$result1 = $conn->query($sql1);

if ($result1->num_rows > 0) {
    echo "<div class='alert-box danger'>";
    echo "<h3>❌ Teacher Double Booking Detected</h3>";

    while ($row = $result1->fetch_assoc()) {

        $teacher = $conn->query("SELECT name FROM teachers WHERE teacher_id=".$row['teacher_id'])
                         ->fetch_assoc();

        echo "Teacher: <b>{$teacher['name']}</b> |
              Day: {$row['day']} |
              Period: {$row['period']} <br>";
    }

    echo "</div>";
} else {
    echo "<div class='alert-box success'>✅ No teacher conflicts found</div>";
}

/* =========================
   2. CLASS DOUBLE BOOKING
========================= */

$sql2 = "
SELECT class_id, day, period, COUNT(*) as total
FROM timetable
GROUP BY class_id, day, period
HAVING total > 1
";

$result2 = $conn->query($sql2);

if ($result2->num_rows > 0) {
    echo "<div class='alert-box danger'>";
    echo "<h3>❌ Class Schedule Conflicts</h3>";

    while ($row = $result2->fetch_assoc()) {

        $class = $conn->query("SELECT class_name FROM classes WHERE class_id=".$row['class_id'])
                      ->fetch_assoc();

        echo "Class: <b>{$class['class_name']}</b> |
              Day: {$row['day']} |
              Period: {$row['period']} <br>";
    }

    echo "</div>";
} else {
    echo "<div class='alert-box success'>✅ No class conflicts found</div>";
}

/* =========================
   3. MISSING TEACHERS ALERT
========================= */

$sql3 = "
SELECT * FROM timetable WHERE teacher_id IS NULL
";

$result3 = $conn->query($sql3);

if ($result3->num_rows > 0) {
    echo "<div class='alert-box warning'>";
    echo "<h3>⚠ Missing Teacher Assignments</h3>";

    while ($row = $result3->fetch_assoc()) {
        echo "Class ID: {$row['class_id']} |
              Day: {$row['day']} |
              Period: {$row['period']} <br>";
    }

    echo "</div>";
}

?>

</div>

</body>
</html>