<?php 
session_start();

$year = date("Y");
$month = date("m");

// Generate all dates of the current month
$dates = [];
for ($day = 1; $day <= date("t"); $day++) {
    $dates[] = sprintf("%02d-%s", $day, date("M", strtotime("$year-$month-01")));
}

// Fetch shifts data for the current month
$query = "SELECT 
            admin.id AS employee_id, 
            admin.uname AS employee_name, 
            DATE_FORMAT(shifts.date, '%d-%b') AS formatted_date, 
            SUM(shifts.total_hours) AS total_hours
        FROM shifts 
        JOIN admin ON shifts.user_id = admin.id 
        WHERE DATE_FORMAT(shifts.date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m') 
        GROUP BY admin.id, DATE_FORMAT(shifts.date, '%Y-%m-%d') 
        ORDER BY shifts.date ASC, admin.id ASC";

$sql = mysqli_query($conn, $query);
$result = mysqli_num_rows($sql);

$employees = [];
if ($result > 0) {
    while ($row = mysqli_fetch_assoc($sql)) {
        $id = $row["employee_id"];
        $employees[$id]["uname"] = $row["employee_name"];
        $employees[$id]["hours"][$row["formatted_date"]] = $row["total_hours"];
    }
}

// Fetch assigned shift hours per employee
$shift_hours = [];
$shift_query = "SELECT sa.employee_name_id, wh.shift_name, wh.hours 
                FROM shift_assignment sa
                JOIN working_hours wh ON FIND_IN_SET(wh.id, sa.shift_id)";
$shift_result = mysqli_query($conn, $shift_query);

if (mysqli_num_rows($shift_result) > 0) {
    while ($row = mysqli_fetch_assoc($shift_result)) {
        $shift_hours[$row["employee_name_id"]][$row["shift_name"]] = strtotime($row["hours"]) / 3600; // Convert HH:MM:SS to hours
    }
}

// Map weekdays to database format (e.g., Monday -> M1)
$weekdays = [
    "Mon" => "M1", "Tue" => "T1", "Wed" => "W1", "Thu" => "TH1",
    "Fri" => "F1", "Sat" => "S1", "Sun" => "SU1"
];

?>

<style>
    .highlight { background-color: rgba(255, 0, 0, 0.3); }
    table {
        width: 100%;
        border-collapse: collapse;
        table-layout: fixed;
    }
    th, td {
        border: 1px solid #ddd;
        padding: 8px;
        text-align: center;
        white-space: nowrap;
        overflow: hidden;
        text-overflow: ellipsis;
    }
    th:first-child, td:first-child { width: 130px; }
    th:nth-child(2), td:nth-child(2) { width: 130px; }
    th:nth-child(3), td:nth-child(3) { width: 130px; }
    th, td { width: 70px; }
</style>

<div class="content-header">
    <div class="container-fluid">
        <div class="row mb-2">
            <div class="col-sm-12">
                <h2>Employee Hours - <?php echo date("F Y"); ?></h2>
            </div>
        </div>
    </div>
</div>

<section class="content">
    <div class="container-fluid">
        <div class="row">
            <div class="col-lg-12">
                <div class="card">
                    <div class="card-body">
                        Total No. of Records: <?= count($employees); ?>
                        <div class="table-responsive">
                            <?php if ($result > 0) { ?>
                                <table id="example1" class="table table-bordered table-striped" style='font-size:12px;'>
                                    <thead>
                                        <tr>
                                            <th>Employee ID</th>
                                            <th>Employee Name</th>
                                            <th>MTD Contribution</th> 
                                            <?php foreach ($dates as $date) { echo "<th>$date</th>"; } ?>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($employees as $id => $data) { 
                                            $totalHours = array_sum($data["hours"]); 
                                        ?>
                                            <tr>
                                                <td><?= $id ?></td>
                                                <td><?= $data["uname"] ?></td>
                                                <td><strong><?= $totalHours ?></strong></td> 
                                                <?php foreach ($dates as $date) {
                                                    $day_of_week = date("D", strtotime("$date-$year"));
                                                    $shift_day = $weekdays[$day_of_week] ?? "";
                                                    $hours = isset($data["hours"][$date]) ? $data["hours"][$date] : "";
                                                    $expected_hours = isset($shift_hours[$id][$shift_day]) ? $shift_hours[$id][$shift_day] : null;

                                                    // Highlight only if shift is assigned for that day and worked hours are less than expected
                                                    $highlight = ($expected_hours !== null && $hours !== "" && $hours < 8) ? "highlight" : "";
                                                    echo "<td class='$highlight'>$hours </td>";
                                                } ?>
                                            </tr>
                                        <?php } ?>
                                    </tbody>
                                </table>
                            <?php } else { ?>
                                <p>No Items Found...</p>
                            <?php } ?>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>  
