<?php
// ── Parameters ────────────────────────────────────────────────────────────────
$report_year  = isset($_GET['year'])  ? (int)$_GET['year']  : (int)date('Y');
$report_month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');

$days_in_month  = cal_days_in_month(CAL_GREGORIAN, $report_month, $report_year);
$month_name     = date('M', mktime(0, 0, 0, $report_month, 1, $report_year));
$month_name_full= date('F', mktime(0, 0, 0, $report_month, 1, $report_year));

// ── Highlighted employees (orange row) — customise as needed ──────────────────
$highlighted_ids = [160, 356, 161, 103, 216]; // employee codes to highlight

// ── Fetch data ────────────────────────────────────────────────────────────────
$employees  = [];
$daily_data = []; // [employee_id][day] = hours

if (!$use_mock) {
    // Real query: adjust table/column names to match your schema
    $date_from = sprintf('%04d-%02d-01', $report_year, $report_month);
    $date_to   = sprintf('%04d-%02d-%02d', $report_year, $report_month, $days_in_month);

    // Employees — pull from admin, only those who appear in shifts for this period
    $emp_sql = "
        SELECT DISTINCT a.id AS employee_code, a.uname AS employee_name
        FROM admin a
        INNER JOIN shifts s ON s.user_id = a.id
        WHERE DATE(s.start_time) BETWEEN '$date_from' AND '$date_to'
        ORDER BY a.uname
    ";
    $emp_res = $conn->query($emp_sql);
    if ($emp_res) {
        while ($row = $emp_res->fetch_assoc()) {
            $employees[$row['employee_code']] = $row['employee_name'];
        }
    }
    // Fallback: if no employees found for period, show all
    if (empty($employees)) {
        $emp_res2 = $conn->query("SELECT id AS employee_code, uname AS employee_name FROM admin WHERE clock_no IS NOT NULL AND clock_no != '' ORDER BY uname");
        if ($emp_res2) {
            while ($row = $emp_res2->fetch_assoc()) {
                $employees[$row['employee_code']] = $row['employee_name'];
            }
        }
    }

    // Daily hours from shifts table — calculated from timestamps
    // Uses TIMESTAMPDIFF for accuracy; ignores still-active (NULL end_time) sessions
    $hrs_sql = "
SELECT 
    user_id,
    work_date,
    SUM(hours) AS hours
FROM
(
    /* First Day */
    SELECT
        s.user_id,
        DATE(s.start_time) AS work_date,

        CASE
            WHEN DATE(s.start_time) = DATE(s.end_time)
            THEN TIMESTAMPDIFF(SECOND, s.start_time, s.end_time) / 3600

            ELSE TIMESTAMPDIFF(
                    SECOND,
                    s.start_time,
                    CONCAT(DATE(s.start_time), ' 23:59:59')
                 ) / 3600
        END AS hours

    FROM shifts s
    WHERE s.end_time IS NOT NULL

    UNION ALL

    /* Last Day */
    SELECT
        s.user_id,
        DATE(s.end_time) AS work_date,

        CASE
            WHEN DATE(s.start_time) != DATE(s.end_time)
            THEN TIMESTAMPDIFF(
                    SECOND,
                    CONCAT(DATE(s.end_time), ' 00:00:00'),
                    s.end_time
                 ) / 3600
            ELSE 0
        END AS hours

    FROM shifts s
    WHERE s.end_time IS NOT NULL
) x

WHERE work_date BETWEEN '$date_from' AND '$date_to'

GROUP BY user_id, work_date
";
    $hrs_res = $conn->query($hrs_sql);
    while ($row = $hrs_res->fetch_assoc()) {

    $day_num = (int)date('d', strtotime($row['work_date']));

    $daily_data[$row['user_id']][$day_num] =
        ($daily_data[$row['user_id']][$day_num] ?? 0)
        + (float)$row['hours'];
}
}

// ── Compute MTD totals ────────────────────────────────────────────────────────
$mtd = [];
foreach ($employees as $emp_id => $_) {
    $mtd[$emp_id] = 0;
    for ($d = 1; $d <= $days_in_month; $d++) {
        $mtd[$emp_id] += $daily_data[$emp_id][$d] ?? 0;
    }
}
$max_mtd = max(array_values($mtd)) ?: 1;

// ── Build column list (only days that have any data, but show full month) ──────
// We show all 30 days but for demo match the screenshot structure
$all_days = range(1, $days_in_month);

// ── Color thresholds for cell highlighting ────────────────────────────────────
function cell_color(float $val): string {
    if ($val <= 0) return '';
    if ($val >= 20) return '#f48fb1'; // deep pink
    if ($val >= 10) return '#f8bbd0'; // medium pink
    return '#fce4ec';                 // light pink
}
?>
<link href="https://fonts.googleapis.com/css2?family=IBM+Plex+Mono:wght@400;600&family=DM+Sans:wght@400;500;600;700&display=swap" rel="stylesheet">
<style>
:root {
    --pink-deep:   #e91e8c;
    --pink-mid:    #f48fb1;
    --pink-light:  #fce4ec;
    --orange-bg:   #ffe0cc;
    --orange-border: #f4a261;
    --header-bg:   #9c4893;
    --header-text: #ffffff;
    --col-head-bg: #2d2d44;
    --border:      #e0e0e0;
    --row-hover:   #fdf0f8;
    --text-main:   #1a1a2e;
    --text-muted:  #6b7280;
    --bar-bg:      #f3e8ff;
}

* { margin: 0; padding: 0; box-sizing: border-box; }



/* ── Report Card ── */
.report-card {
    background: white;
    border-radius: 14px;
    box-shadow: 0 4px 24px rgba(0,0,0,0.08);
    overflow: hidden;
}

/* ── Header ── */
.report-header {
    background: var(--header-bg);
    padding: 20px 28px;
    display: flex;
    align-items: center;
    justify-content: space-between;
    gap: 16px;
    flex-wrap: wrap;
}

.report-header h1 {
    font-size: 20px;
    font-weight: 700;
    color: white;
    letter-spacing: -0.3px;
}

.report-header .subtitle {
    font-size: 13px;
    color: rgba(255,255,255,0.55);
    margin-top: 3px;
}

.month-nav {
    display: flex;
    align-items: center;
    gap: 10px;
}

.month-nav a {
    display: inline-flex;
    align-items: center;
    justify-content: center;
    width: 32px; height: 32px;
    background: rgba(255,255,255,0.1);
    color: white;
    border-radius: 8px;
    text-decoration: none;
    font-size: 16px;
    transition: background 0.2s;
}

.month-nav a:hover { background: rgba(255,255,255,0.22); }

.month-nav .current-month {
    font-family: 'IBM Plex Mono', monospace;
    font-size: 14px;
    font-weight: 600;
    color: white;
    min-width: 120px;
    text-align: center;
}

/* ── Legend ── */
.legend-bar {
    padding: 10px 28px;
    background: #f9fafb;
    border-bottom: 1px solid var(--border);
    display: flex;
    gap: 18px;
    align-items: center;
    flex-wrap: wrap;
}

.legend-item {
    display: flex;
    align-items: center;
    gap: 7px;
    font-size: 12px;
    color: var(--text-muted);
    font-weight: 500;
}

.legend-swatch {
    width: 16px; height: 16px;
    border-radius: 4px;
    flex-shrink: 0;
}

/* ── Table Wrapper ── */
.table-scroll {
    overflow-x: auto;
    overflow-y: visible;
}

/* ── Table ── */
table {
    border-collapse: collapse;
    width: 100%;
    min-width: 900px;
    font-size: 12.5px;
}

/* Sticky columns */
th, td {
    border: 1px solid var(--border);
    padding: 0;
    white-space: nowrap;
}

thead tr:first-child th {
    background: var(--col-head-bg);
    color: white;
    font-family: 'IBM Plex Mono', monospace;
    font-size: 11px;
    font-weight: 600;
    letter-spacing: 0.4px;
    text-align: center;
    padding: 9px 8px;
    position: sticky;
    top: 0;
    z-index: 20;
}

/* Sticky left columns */
thead tr:first-child th:nth-child(1),
thead tr:first-child th:nth-child(2),
thead tr:first-child th:nth-child(3) {
    position: sticky;
    left: 0;
    z-index: 30;
}

thead tr:first-child th:nth-child(2) { left: 70px; }
thead tr:first-child th:nth-child(3) { left: 200px; }

tbody td:nth-child(1),
tbody td:nth-child(2),
tbody td:nth-child(3) {
    position: sticky;
    background: white;
    z-index: 10;
}

tbody td:nth-child(1) { left: 0; }
tbody td:nth-child(2) { left: 70px; }
tbody td:nth-child(3) { left: 200px; }

/* Highlighted rows */
tbody tr.highlighted td { background: var(--orange-bg) !important; }
tbody tr.highlighted td:nth-child(1),
tbody tr.highlighted td:nth-child(2),
tbody tr.highlighted td:nth-child(3) {
    background: var(--orange-bg) !important;
}

tbody tr:hover td { background: var(--row-hover); }
tbody tr:hover td:nth-child(1),
tbody tr:hover td:nth-child(2),
tbody tr:hover td:nth-child(3) { background: var(--row-hover); }
tbody tr.highlighted:hover td { background: #ffd5b8 !important; }

/* ── Cell types ── */
.cell-code {
    font-family: 'IBM Plex Mono', monospace;
    font-size: 11.5px;
    font-weight: 600;
    color: var(--text-muted);
    text-align: center;
    padding: 7px 8px;
    min-width: 60px;
}

.cell-name {
    font-weight: 600;
    font-size: 12.5px;
    padding: 7px 12px;
    min-width: 130px;
    color: var(--text-main);
}

/* ── MTD Bar Cell ── */
.cell-mtd {
    min-width: 120px;
    padding: 5px 8px;
    text-align: right;
    vertical-align: middle;
}

.mtd-inner {
    display: flex;
    align-items: center;
    gap: 6px;
    justify-content: flex-end;
}

.mtd-bar-wrap {
    flex: 1;
    height: 12px;
    background: #f0f0f0;
    border-radius: 6px;
    overflow: hidden;
    min-width: 50px;
}

.mtd-bar-fill {
    height: 100%;
    background: linear-gradient(90deg, var(--pink-deep), #ff6ec7);
    border-radius: 6px;
    transition: width 0.3s;
}

.mtd-val {
    font-family: 'IBM Plex Mono', monospace;
    font-size: 11px;
    font-weight: 600;
    color: var(--text-main);
    min-width: 32px;
    text-align: right;
}

/* ── Daily hour cell ── */
.cell-day {
    text-align: center;
    padding: 7px 5px;
    min-width: 46px;
    font-family: 'IBM Plex Mono', monospace;
    font-size: 11.5px;
    font-weight: 500;
    transition: background 0.15s;
}

.cell-day.has-value {
    font-weight: 600;
    color: #880e4f;
}

.cell-day.weekend {
    background: #fafafa;
    color: #d0d0d0;
}

/* ── Summary row ── */
tfoot td {
    background: #f0f0f5 !important;
    font-weight: 700;
    font-family: 'IBM Plex Mono', monospace;
    font-size: 11.5px;
    text-align: center;
    padding: 8px 5px;
    border-top: 2px solid #c0c0d0;
    color: var(--text-main);
    position: sticky;
    bottom: 0;
    z-index: 10;
}

tfoot td:nth-child(1),
tfoot td:nth-child(2),
tfoot td:nth-child(3) {
    position: sticky;
    left: 0;
    z-index: 20;
}
tfoot td:nth-child(2) { left: 70px; }
tfoot td:nth-child(3) { left: 200px; }

/* Day-of-week sub-header */
thead tr.dow-row th {
    background: #3a3a55;
    color: rgba(255,255,255,0.6);
    font-size: 10px;
    font-weight: 500;
    text-align: center;
    padding: 4px 0;
    position: sticky;
    top: 34px;
    z-index: 18;
    letter-spacing: 0.2px;
}

thead tr.dow-row th:nth-child(1),
thead tr.dow-row th:nth-child(2),
thead tr.dow-row th:nth-child(3) {
    position: sticky;
    z-index: 28;
}
thead tr.dow-row th:nth-child(2) { left: 70px; }
thead tr.dow-row th:nth-child(3) { left: 200px; }

.weekend-head { color: #f48fb1 !important; }

/* ── Footer ── */
.report-footer {
    padding: 12px 28px;
    background: #f9fafb;
    border-top: 1px solid var(--border);
    font-size: 12px;
    color: var(--text-muted);
    display: flex;
    justify-content: space-between;
    flex-wrap: wrap;
    gap: 8px;
}

@media print {
    body { padding: 0; background: white; }
    .report-card { box-shadow: none; border-radius: 0; }
    .month-nav a { display: none; }
}
</style>

<div class="report-card">

    <!-- Header -->
    <div class="report-header">
        <div>
            <h1>📊 Employee Hours Report</h1>
            <div class="subtitle">Daily clock-in / clock-out totals · <?= $month_name_full ?> <?= $report_year ?></div>
        </div>
        <div class="month-nav">
            <?php
            $prev_m = $report_month - 1; $prev_y = $report_year;
            if ($prev_m < 1) { $prev_m = 12; $prev_y--; }
            $next_m = $report_month + 1; $next_y = $report_year;
            if ($next_m > 12) { $next_m = 1; $next_y++; }
            ?>
            <a href="?month=<?= $prev_m ?>&year=<?= $prev_y ?>">‹</a>
            <span class="current-month"><?= $month_name_full ?> <?= $report_year ?></span>
            <a href="?month=<?= $next_m ?>&year=<?= $next_y ?>">›</a>
        </div>
    </div>

    <!-- Legend -->
    <div class="legend-bar">
        <span style="font-size:12px;font-weight:700;color:#374151;">Legend:</span>
        <div class="legend-item">
            <div class="legend-swatch" style="background:#fce4ec;border:1px solid #f48fb1;"></div>
            &lt; 10 hrs
        </div>
        <div class="legend-item">
            <div class="legend-swatch" style="background:#f8bbd0;"></div>
            10 – 19 hrs
        </div>
        <div class="legend-item">
            <div class="legend-swatch" style="background:#f48fb1;"></div>
            ≥ 20 hrs
        </div>
        <div class="legend-item">
            <div class="legend-swatch" style="background:var(--orange-bg);border:1px solid var(--orange-border);"></div>
            Highlighted employees
        </div>
        <div style="margin-left:auto;font-size:12px;color:var(--text-muted);">
            <?= count($employees) ?> employees &nbsp;·&nbsp; <?= $days_in_month ?> days
        </div>
    </div>

    <!-- Table -->
    <div class="table-scroll">
        <table>
            <thead>
                <!-- Main header row -->
                <tr>
                    <th style="left:0;position:sticky;">Code</th>
                    <th style="left:70px;position:sticky;">Employee Name</th>
                    <th style="left:200px;position:sticky;">MTD Contribution</th>
                    <?php for ($d = 1; $d <= $days_in_month; $d++): 
                        $dow = date('N', mktime(0,0,0,$report_month,$d,$report_year));
                        $is_weekend = ($dow >= 6);
                    ?>
                        <th class="<?= $is_weekend ? 'weekend-head' : '' ?>" title="<?= date('l', mktime(0,0,0,$report_month,$d,$report_year)) ?>">
                            <?= sprintf('%02d', $d) ?>-<?= $month_name ?>
                        </th>
                    <?php endfor; ?>
                </tr>
                <!-- Day of week row -->
                <tr class="dow-row">
                    <th></th><th></th><th></th>
                    <?php for ($d = 1; $d <= $days_in_month; $d++):
                        $dow     = date('N', mktime(0,0,0,$report_month,$d,$report_year));
                        $is_wknd = ($dow >= 6);
                        $day_lbl = date('D', mktime(0,0,0,$report_month,$d,$report_year));
                    ?>
                        <th class="<?= $is_wknd ? 'weekend-head' : '' ?>"><?= $day_lbl[0] ?></th>
                    <?php endfor; ?>
                </tr>
            </thead>

            <tbody>
            <?php
            // Day-level column totals
            $col_totals = array_fill(1, $days_in_month, 0);

            foreach ($employees as $emp_id => $emp_name):
                $is_highlighted = in_array($emp_id, $highlighted_ids);
                $mtd_val  = $mtd[$emp_id];
                $bar_pct  = $max_mtd > 0 ? ($mtd_val / $max_mtd * 100) : 0;

                foreach ($all_days as $d) {
                    $col_totals[$d] += $daily_data[$emp_id][$d] ?? 0;
                }
            ?>
                <tr<?= $is_highlighted ? ' class="highlighted"' : '' ?>>
                    <!-- Code -->
                    <td class="cell-code"><?= $emp_id ?></td>

                    <!-- Name -->
                    <td class="cell-name"><?= htmlspecialchars($emp_name) ?></td>

                    <!-- MTD bar -->
                    <td class="cell-mtd">
                        <div class="mtd-inner">
                            <div class="mtd-bar-wrap">
                                <div class="mtd-bar-fill" style="width:<?= round($bar_pct) ?>%;"></div>
                            </div>
                            <span class="mtd-val"><?= $mtd_val > 0 ? number_format($mtd_val, 1) : '—' ?></span>
                        </div>
                    </td>

                    <!-- Daily cells -->
                    <?php for ($d = 1; $d <= $days_in_month; $d++):
                        $val     = $daily_data[$emp_id][$d] ?? 0;
                        $dow     = date('N', mktime(0,0,0,$report_month,$d,$report_year));
                        $is_wknd = ($dow >= 6);
                        $bg      = $val > 0 ? cell_color($val) : ($is_wknd ? '#fafafa' : '');
                        $cls     = 'cell-day' . ($val > 0 ? ' has-value' : '') . ($is_wknd ? ' weekend' : '');
                    ?>
                        <td class="<?= $cls ?>">
                            <?= $val > 0 ? number_format($val, 1) : '' ?>
                        </td>
                    <?php endfor; ?>
                </tr>
            <?php endforeach; ?>
            </tbody>

            <!-- Totals footer -->
            <tfoot>
                <tr>
                    <td colspan="2" style="text-align:right;padding:8px 12px;font-size:12px;font-weight:700;">
                        Daily Totals
                    </td>
                    <td class="cell-mtd" style="text-align:right;padding:8px;">
                        <span style="font-weight:700;"><?= number_format(array_sum($mtd), 1) ?></span>
                    </td>
                    <?php for ($d = 1; $d <= $days_in_month; $d++): ?>
                        <td style="text-align:center;font-size:11px;<?= $col_totals[$d] > 0 ? 'color:#880e4f;' : 'color:#bbb;' ?>">
                            <?= $col_totals[$d] > 0 ? number_format($col_totals[$d], 1) : '—' ?>
                        </td>
                    <?php endfor; ?>
                </tr>
            </tfoot>
        </table>
    </div>

    <!-- Footer -->
    <div class="report-footer">
        <span>Generated: <?= date('d M Y, H:i') ?></span>
        <span>
            Total employees: <strong><?= count($employees) ?></strong>
            &nbsp;·&nbsp; Total MTD hours: <strong><?= number_format(array_sum($mtd), 1) ?></strong>
        </span>
    </div>

</div><!-- /report-card -->