<?php
include('db_connection.php');
?>

<!DOCTYPE html>
<html>
<head>
<title>Manage Teachers | School ERP</title>

<style>
body {
    font-family: Arial;
    background: #eef2f7;
    margin: 0;
}

.header {
    background: #1e293b;
    color: white;
    padding: 20px;
    text-align: center;
}

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

.card {
    background: white;
    padding: 20px;
    border-radius: 10px;
}

table {
    width: 100%;
    border-collapse: collapse;
}

th {
    background: #1e293b;
    color: white;
    padding: 10px;
}

td {
    padding: 10px;
    border-bottom: 1px solid #ddd;
}

a {
    text-decoration: none;
    padding: 5px 8px;
    border-radius: 5px;
    font-size: 13px;
}

.edit { background: #2563eb; color: white; }
.delete { background: #dc2626; color: white; }
.view { background: #16a34a; color: white; }
.payroll { background: #f59e0b; color: white; }

a:hover { opacity: 0.8; }
</style>
</head>

<body>

<div class="header">
    <h2>AGID EDUCATIONAL INSTITUTE</h2>
    <p>Teacher Management Module</p>
</div>

<div class="container">
<div class="card">

<h3>All Teachers</h3>

<a class="payroll" href="add_payroll.php">Add Payroll</a>

<table>
<tr>
    <th>Name</th>
    <th>Email</th>
    <th>Subject</th>
    <th>Actions</th>
</tr>

<?php
$query = "
SELECT t.teacher_id, t.name, t.subject, u.email
FROM teachers t
JOIN users u ON t.user_id = u.user_id
ORDER BY t.name ASC
";

$result = $conn->query($query);

while ($row = $result->fetch_assoc()):

    /* =========================
       GET LATEST PAYROLL ID
    ========================= */
    $payroll_stmt = $conn->prepare("
        SELECT id 
        FROM payrolls 
        WHERE teacher_id = ? 
        ORDER BY pay_year DESC, pay_month DESC 
        LIMIT 1
    ");
    $payroll_stmt->bind_param("i", $row['teacher_id']);
    $payroll_stmt->execute();
    $payroll_res = $payroll_stmt->get_result();
    $payroll = $payroll_res->fetch_assoc();
    $payroll_id = $payroll['id'] ?? null;
?>

<tr>
    <td><?= htmlspecialchars($row['name']) ?></td>
    <td><?= htmlspecialchars($row['email']) ?></td>
    <td><?= htmlspecialchars($row['subject']) ?></td>

    <td>

        <a class="view" href="view_teacher.php?id=<?= $row['teacher_id'] ?>">View</a>

        <a class="edit" href="edit_teacher.php?id=<?= $row['teacher_id'] ?>">Edit</a>

        <a class="delete" href="delete_teacher.php?id=<?= $row['teacher_id'] ?>" onclick="return confirm('Delete this teacher?')">Delete</a>

        <a class="payroll" href="add_payroll.php?teacher_id=<?= $row['teacher_id'] ?>">
            Add Payroll
        </a>

        <?php if ($payroll_id): ?>
            <a class="edit" href="edit_payroll.php?id=<?= $payroll_id ?>">
                Edit Payroll
            </a>
        <?php else: ?>
            <span style="color:#999;font-size:12px;">No payroll</span>
        <?php endif; ?>

    </td>
</tr>

<?php endwhile; ?>

</table>

</div>
</div>

</body>
</html>