<?php
/**
 * Attendance analytics – trends, breakdowns, comparisons
 */

try {

$filterType = isset($_GET['type']) ? (int)$_GET['type'] : 0;
$filterYear = isset($_GET['year'])  && preg_match('/^\d{4}$/', $_GET['year']) ? (int)$_GET['year'] : (int)date('Y');

$serviceTypes = $pdo->query("SELECT id, name FROM service_types WHERE is_active=1 ORDER BY name")->fetchAll();
$serviceTypeNameById = [];
foreach ($serviceTypes as $t) {
    $serviceTypeNameById[(int)$t['id']] = (string)$t['name'];
}
$selectedTypeName = ($filterType && isset($serviceTypeNameById[$filterType])) ? $serviceTypeNameById[$filterType] : '';
$selectedTarget = null;
if ($filterType) {
    $stmt = $pdo->prepare("SELECT target_total FROM service_types WHERE id=?");
    $stmt->execute([$filterType]);
    $selectedTarget = $stmt->fetchColumn();
    $selectedTarget = ($selectedTarget === null) ? null : (int)$selectedTarget;
}

// Last 20 sessions for bar chart (filtered by type if chosen)
$chartSql = "SELECT r.date, t.name AS service_type,
                    r.male_count, r.female_count, r.children_count, r.visitors_count,
                    (r.male_count + r.female_count + r.children_count + r.visitors_count) AS total
             FROM attendance_records r
             JOIN service_types t ON t.id = r.service_type_id
             WHERE 1";
$chartParams = [];
if ($filterType) { $chartSql .= " AND r.service_type_id = ?"; $chartParams[] = $filterType; }
$chartSql .= " ORDER BY r.date DESC LIMIT 20";
$stmt = $pdo->prepare($chartSql);
$stmt->execute($chartParams);
$chartData = $stmt->fetchAll();
$chartData = array_reverse($chartData);

// Monthly totals for the selected year
$monthlySql = "SELECT MONTH(r.date) AS m,
                      DATE_FORMAT(MIN(r.date),'%b') AS label,
                      COUNT(*) AS sessions,
                      SUM(r.male_count + r.female_count + r.children_count + r.visitors_count) AS total,
                      ROUND(AVG(r.male_count + r.female_count + r.children_count + r.visitors_count)) AS avg_total,
                      SUM(r.male_count) AS male, SUM(r.female_count) AS female,
                      SUM(r.children_count) AS children, SUM(r.visitors_count) AS visitors
               FROM attendance_records r
               WHERE YEAR(r.date) = ?";
$monthlyParams = [$filterYear];
if ($filterType) { $monthlySql .= " AND r.service_type_id = ?"; $monthlyParams[] = $filterType; }
$monthlySql .= " GROUP BY MONTH(r.date) ORDER BY m";
$stmt = $pdo->prepare($monthlySql);
$stmt->execute($monthlyParams);
$monthlyRows = $stmt->fetchAll();
$monthly = [];
foreach ($monthlyRows as $row) {
    $monthly[(int)$row['m']] = $row;
}

// Year-on-year summary
$yearSummary = $pdo->query(
    "SELECT YEAR(r.date) AS yr,
            COUNT(*) AS sessions,
            SUM(r.male_count + r.female_count + r.children_count + r.visitors_count) AS total,
            ROUND(AVG(r.male_count + r.female_count + r.children_count + r.visitors_count)) AS avg_total
     FROM attendance_records r
     GROUP BY yr ORDER BY yr DESC LIMIT 5"
)->fetchAll();

// Overall all-time stats
$allTime = $pdo->query(
    "SELECT COUNT(*) AS sessions,
            SUM(male_count+female_count+children_count+visitors_count) AS grand_total,
            ROUND(AVG(male_count+female_count+children_count+visitors_count)) AS avg_total,
            MAX(male_count+female_count+children_count+visitors_count) AS best,
            SUM(male_count) AS total_male, SUM(female_count) AS total_female,
            SUM(children_count) AS total_children, SUM(visitors_count) AS total_visitors
     FROM attendance_records"
)->fetch();
$allTime = $allTime ?: [
    'sessions' => 0,
    'grand_total' => 0,
    'avg_total' => 0,
    'best' => 0,
    'total_male' => 0,
    'total_female' => 0,
    'total_children' => 0,
    'total_visitors' => 0,
];

// Breakdown by service type
$byType = $pdo->query(
    "SELECT t.id, t.name, t.target_total, COUNT(r.id) AS sessions,
            ROUND(AVG(r.male_count+r.female_count+r.children_count+r.visitors_count)) AS avg_total,
            MAX(r.male_count+r.female_count+r.children_count+r.visitors_count) AS best
     FROM service_types t
     JOIN attendance_records r ON r.service_type_id = t.id
     GROUP BY t.id, t.name, t.target_total
     ORDER BY avg_total DESC"
)->fetchAll();

// If a type is selected and it has a target, compute avg % to target (for the filter + year)
$avgPctToTarget = null;
if ($filterType && $selectedTarget) {
    $stmt = $pdo->prepare(
        "SELECT ROUND(AVG((r.male_count+r.female_count+r.children_count+r.visitors_count) / ? * 100)) AS pct
         FROM attendance_records r
         WHERE YEAR(r.date) = ? AND r.service_type_id = ?"
    );
    $stmt->execute([$selectedTarget, $filterYear, $filterType]);
    $avgPctToTarget = $stmt->fetchColumn();
    $avgPctToTarget = ($avgPctToTarget === null) ? null : (int)$avgPctToTarget;
}

// Rolling averages + health score (last 8 sessions % to target)
$rolling = [
    'avg4' => null,
    'avg8' => null,
    'health_pct' => null,
];
if (!empty($chartData)) {
    $totals = array_map(fn($r) => (int)($r['total'] ?? 0), $chartData);
    $last4 = array_slice($totals, -4);
    $last8 = array_slice($totals, -8);
    if (count($last4) > 0) $rolling['avg4'] = (int) round(array_sum($last4) / count($last4));
    if (count($last8) > 0) $rolling['avg8'] = (int) round(array_sum($last8) / count($last8));
    if ($filterType && $selectedTarget && count($last8) > 0) {
        $rolling['health_pct'] = (int) round((array_sum($last8) / count($last8)) / $selectedTarget * 100);
    }
}

$pageTitle = 'Attendance Analytics';
require_once __DIR__ . '/../../includes/header.php';
?>

<style>
.ana-stats { display:grid; grid-template-columns:repeat(auto-fit,minmax(150px,1fr)); gap:1rem; margin-bottom:1.5rem; }
.ana-stat  { background:#fff; border:1px solid var(--border); border-radius:8px; padding:1.125rem 1.25rem; }
.ana-stat-label { font-size:0.72rem; text-transform:uppercase; letter-spacing:0.05em; color:var(--text-muted); margin:0 0 0.3rem; }
.ana-stat-value { font-size:1.75rem; font-weight:700; color:var(--text); line-height:1; }
.ana-stat-sub   { font-size:0.75rem; color:var(--text-muted); margin-top:0.25rem; }

.ana-grid { display:grid; grid-template-columns:1fr 1fr; gap:1.5rem; }
@media(max-width:700px){ .ana-grid { grid-template-columns:1fr; } }
.ana-card { background:#fff; border:1px solid var(--border); border-radius:8px; padding:1.25rem; }
.ana-card h2 { font-size:0.72rem; font-weight:600; text-transform:uppercase; letter-spacing:0.07em; color:var(--text-muted); margin:0 0 1.25rem; }

/* Bar chart */
.bar-chart { display:flex; align-items:flex-end; gap:3px; height:140px; }
.bar-col   { flex:1; display:flex; flex-direction:column; align-items:center; gap:2px; min-width:0; }
.bar-val   { font-size:0.58rem; color:var(--text-muted); }
.bar-body  { width:100%; border-radius:3px 3px 0 0; transition:height 0.3s; min-height:2px; }
.bar-date  { font-size:0.55rem; color:var(--text-muted); writing-mode:vertical-rl; transform:rotate(180deg); white-space:nowrap; overflow:hidden; max-height:44px; }

/* Stacked mini-bar for male/female split */
.split-bar { display:flex; height:10px; border-radius:4px; overflow:hidden; gap:1px; width:100%; }
.split-m { background:#3b82f6; }
.split-f { background:#ec4899; }
.split-c { background:#10b981; }
.split-v { background:#f59e0b; }

/* Table */
.ana-table { width:100%; border-collapse:collapse; font-size:0.875rem; }
.ana-table th { text-align:left; padding:0.45rem 0; font-size:0.72rem; text-transform:uppercase; letter-spacing:0.05em; color:var(--text-muted); border-bottom:1px solid var(--border); }
.ana-table th.r, .ana-table td.r { text-align:right; }
.ana-table td { padding:0.6rem 0; border-bottom:1px solid var(--border); vertical-align:middle; }
.ana-table tr:last-child td { border-bottom:none; }

.ana-filter { display:flex; gap:0.5rem; margin-bottom:1.25rem; align-items:center; flex-wrap:wrap; }
.ana-filter select, .ana-filter input {
    padding:0.375rem 0.75rem; border:1px solid var(--border); border-radius:6px;
    font-size:0.875rem; color:var(--text); background:#fff;
}
.trend-up   { color:#16a34a; font-weight:600; }
.trend-down { color:#dc2626; font-weight:600; }
.trend-flat { color:var(--text-muted); }

.health-pill {
    display:inline-block; padding:0.15rem 0.55rem; border-radius:999px;
    font-size:0.75rem; font-weight:600;
}
.health-good { background:#dcfce7; color:#166534; }
.health-near { background:#fef3c7; color:#92400e; }
.health-low  { background:#fee2e2; color:#991b1b; }
</style>

<div class="header">
    <h1>Attendance Analytics</h1>
    <div class="header-actions">
        <a href="<?= BASE_PATH ?>/attendance" class="btn btn-secondary">← Sessions</a>
        <a href="<?= BASE_PATH ?>/attendance/record" class="btn">Record Attendance</a>
        <a href="<?= BASE_PATH ?>/logout">Logout</a>
    </div>
</div>

<!-- All-time summary -->
<div class="ana-stats">
    <div class="ana-stat">
        <p class="ana-stat-label">Total Sessions</p>
        <div class="ana-stat-value"><?= (int)$allTime['sessions'] ?></div>
    </div>
    <div class="ana-stat">
        <p class="ana-stat-label">Avg Attendance</p>
        <div class="ana-stat-value"><?= (int)$allTime['avg_total'] ?></div>
        <div class="ana-stat-sub">per session</div>
    </div>
    <div class="ana-stat">
        <p class="ana-stat-label">Best Session</p>
        <div class="ana-stat-value"><?= (int)$allTime['best'] ?></div>
    </div>
    <div class="ana-stat">
        <p class="ana-stat-label">Total Headcount</p>
        <div class="ana-stat-value"><?= number_format((int)$allTime['grand_total']) ?></div>
        <div class="ana-stat-sub">all time</div>
    </div>
    <?php if ($filterType): ?>
    <div class="ana-stat">
        <p class="ana-stat-label">Target</p>
        <div class="ana-stat-value"><?= $selectedTarget ? (int)$selectedTarget : '—' ?></div>
        <div class="ana-stat-sub">
            <?php if ($selectedTarget && $avgPctToTarget !== null): ?>
                <?= $avgPctToTarget ?>% avg (<?= (int)$filterYear ?>)
            <?php else: ?>
                set in “Manage Services”
            <?php endif; ?>
        </div>
    </div>
    <?php if ($selectedTarget): ?>
    <div class="ana-stat">
        <p class="ana-stat-label">Health score</p>
        <div class="ana-stat-value"><?= $rolling['health_pct'] !== null ? ($rolling['health_pct'] . '%') : '—' ?></div>
        <div class="ana-stat-sub">
            <?php if ($rolling['health_pct'] !== null): ?>
                <?php
                    $hp = (int)$rolling['health_pct'];
                    $cls = $hp >= 100 ? 'health-good' : ($hp >= 80 ? 'health-near' : 'health-low');
                    $txt = $hp >= 100 ? 'Doing well' : ($hp >= 80 ? 'Almost there' : 'Needs improvement');
                ?>
                <span class="health-pill <?= $cls ?>"><?= $txt ?></span>
                <span style="margin-left:0.4rem; color:var(--text-muted);">last 8 sessions</span>
            <?php else: ?>
                <span class="text-muted">Set a target to calculate health</span>
            <?php endif; ?>
        </div>
    </div>
    <?php endif; ?>
    <?php endif; ?>
</div>

<!-- Filters -->
<form method="get" class="ana-filter">
    <select name="type" onchange="this.form.submit()">
        <option value="">All service types</option>
        <?php foreach ($serviceTypes as $t): ?>
            <option value="<?= $t['id'] ?>" <?= $filterType === (int)$t['id'] ? 'selected' : '' ?>><?= htmlspecialchars($t['name']) ?></option>
        <?php endforeach; ?>
    </select>
    <input type="number" name="year" value="<?= $filterYear ?>" min="2000" max="2099" style="width:90px;" onchange="this.form.submit()" title="Year">
    <?php if ($filterType): ?>
        <a href="<?= BASE_PATH ?>/attendance/analytics?year=<?= $filterYear ?>" class="btn btn-secondary">Clear filter</a>
    <?php endif; ?>
</form>

<!-- Bar chart + breakdown grid -->
<div class="ana-grid" style="margin-bottom:1.5rem;">
    <!-- Recent sessions bar chart -->
    <div class="ana-card" style="grid-column:1/-1;">
        <h2>Attendance – Last <?= count($chartData) ?> Sessions <?= $selectedTypeName ? '(' . htmlspecialchars($selectedTypeName) . ')' : '' ?></h2>
        <?php if (empty($chartData)): ?>
            <p style="color:var(--text-muted); font-size:0.875rem;">No data yet.</p>
        <?php else:
            $maxVal = max(array_column($chartData, 'total')) ?: 1;
        ?>
        <div class="bar-chart">
            <?php foreach ($chartData as $r):
                $pct   = round($r['total'] / $maxVal * 100);
                $total = (int)$r['total'];
                // Color based on target if available, else default
                $color = '#6366f1';
                if ($filterType && $selectedTarget) {
                    $p = round($total / $selectedTarget * 100);
                    if ($p >= 100) $color = '#22c55e';
                    elseif ($p >= 80) $color = '#f59e0b';
                    else $color = '#ef4444';
                }
            ?>
            <div class="bar-col" title="<?= date('M j, Y', strtotime($r['date'])) ?> · <?= $r['service_type'] ?>&#10;Total: <?= $total ?> | M:<?= $r['male_count'] ?> F:<?= $r['female_count'] ?> C:<?= $r['children_count'] ?> V:<?= $r['visitors_count'] ?>">
                <span class="bar-val"><?= $total ?></span>
                <div class="bar-body" style="height:<?= max($pct,2) ?>%; background:<?= $color ?>;"></div>
                <span class="bar-date"><?= date('M j', strtotime($r['date'])) ?></span>
            </div>
            <?php endforeach; ?>
        </div>
        <?php endif; ?>
    </div>

    <!-- Monthly breakdown for selected year -->
    <div class="ana-card">
        <h2>Monthly — <?= $filterYear ?></h2>
        <?php
        $allMonths = range(1, 12);
        $monthNames = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
        $hasMonthly = !empty($monthly);
        ?>
        <?php if (!$hasMonthly): ?>
            <p style="color:var(--text-muted); font-size:0.875rem;">No data for <?= $filterYear ?>.</p>
        <?php else: ?>
        <table class="ana-table">
            <thead>
                <tr>
                    <th>Month</th>
                    <th class="r">Sessions</th>
                    <th class="r">Avg Total</th>
                    <th>Split</th>
                </tr>
            </thead>
            <tbody>
            <?php foreach ($allMonths as $m):
                $row = $monthly[$m] ?? null;
                if (!$row) continue;
                $t = (int)$row['male'] + (int)$row['female'] + (int)$row['children'] + (int)$row['visitors'];
                $mp = $t ? round($row['male']/$t*100) : 0;
                $fp = $t ? round($row['female']/$t*100) : 0;
                $cp = $t ? round($row['children']/$t*100) : 0;
                $vp = $t ? 100 - $mp - $fp - $cp : 0;
            ?>
            <tr>
                <td><?= $monthNames[$m-1] ?></td>
                <td class="r" style="color:var(--text-muted);"><?= (int)$row['sessions'] ?></td>
                <td class="r" style="font-weight:600;"><?= (int)$row['avg_total'] ?></td>
                <td>
                    <div style="display:flex; align-items:center; gap:0.4rem;">
                        <div class="split-bar" style="width:70px;">
                            <div class="split-m" style="flex:<?= $mp ?>"></div>
                            <div class="split-f" style="flex:<?= $fp ?>"></div>
                            <div class="split-c" style="flex:<?= $cp ?>"></div>
                            <div class="split-v" style="flex:<?= $vp ?>"></div>
                        </div>
                        <span style="font-size:0.7rem; color:var(--text-muted);"><?= $mp ?>%M</span>
                    </div>
                </td>
            </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
    </div>

    <!-- Monthly target vs actual (only when a service type with target is selected) -->
    <?php if ($filterType && $selectedTarget): ?>
    <div class="ana-card">
        <h2>Monthly target vs actual — <?= $filterYear ?></h2>
        <?php if (!$hasMonthly): ?>
            <p style="color:var(--text-muted); font-size:0.875rem;">No data for <?= $filterYear ?>.</p>
        <?php else: ?>
        <table class="ana-table">
            <thead>
                <tr>
                    <th>Month</th>
                    <th class="r">Sessions</th>
                    <th class="r">Actual</th>
                    <th class="r">Target</th>
                    <th class="r">% to target</th>
                </tr>
            </thead>
            <tbody>
            <?php foreach ($allMonths as $m):
                $row = $monthly[$m] ?? null;
                if (!$row) continue;
                $sessions = (int)$row['sessions'];
                $actual = (int)$row['total'];
                $target = (int)$selectedTarget * $sessions;
                $pct = $target > 0 ? round($actual / $target * 100) : 0;
                $cls = $pct >= 100 ? 'health-good' : ($pct >= 80 ? 'health-near' : 'health-low');
            ?>
                <tr>
                    <td><?= $monthNames[$m-1] ?></td>
                    <td class="r" style="color:var(--text-muted);"><?= $sessions ?></td>
                    <td class="r" style="font-weight:600;"><?= number_format($actual) ?></td>
                    <td class="r" style="color:var(--text-muted);"><?= number_format($target) ?></td>
                    <td class="r"><span class="health-pill <?= $cls ?>"><?= $pct ?>%</span></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
        <p style="margin:0.75rem 0 0; font-size:0.8rem; color:var(--text-muted);">
            Target per month is calculated as <strong>target attendance</strong> × <strong>sessions recorded</strong>.
        </p>
    </div>
    <?php endif; ?>

    <!-- By service type -->
    <div class="ana-card">
        <h2>By Service Type</h2>
        <?php if (empty($byType)): ?>
            <p style="color:var(--text-muted); font-size:0.875rem;">No data yet.</p>
        <?php else: ?>
        <table class="ana-table">
            <thead><tr><th>Service</th><th class="r">Sessions</th><th class="r">Target</th><th class="r">% to target</th><th class="r">Avg</th><th class="r">Best</th></tr></thead>
            <tbody>
            <?php foreach ($byType as $row): ?>
            <?php
                $tgt = $row['target_total'] === null ? null : (int)$row['target_total'];
                $pctTo = ($tgt && (int)$row['avg_total'] > 0) ? round((int)$row['avg_total'] / $tgt * 100) : null;
                $badge = '';
                if ($pctTo !== null) {
                    if ($pctTo >= 100) $badge = "<span style='color:#16a34a; font-weight:600;'>Good</span>";
                    elseif ($pctTo >= 80) $badge = "<span style='color:#f59e0b; font-weight:600;'>Near</span>";
                    else $badge = "<span style='color:#dc2626; font-weight:600;'>Low</span>";
                }
            ?>
            <tr>
                <td><?= htmlspecialchars($row['name']) ?></td>
                <td class="r" style="color:var(--text-muted);"><?= (int)$row['sessions'] ?></td>
                <td class="r" style="color:var(--text-muted);"><?= $tgt ? $tgt : '—' ?></td>
                <td class="r"><?= $pctTo !== null ? ($pctTo . '% ' . $badge) : '—' ?></td>
                <td class="r" style="font-weight:600;"><?= (int)$row['avg_total'] ?></td>
                <td class="r" style="color:var(--text-muted);"><?= (int)$row['best'] ?></td>
            </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
    </div>

    <!-- Year-on-year -->
    <div class="ana-card">
        <h2>Year-on-Year</h2>
        <?php if (empty($yearSummary)): ?>
            <p style="color:var(--text-muted); font-size:0.875rem;">No data yet.</p>
        <?php else: ?>
        <table class="ana-table">
            <thead><tr><th>Year</th><th class="r">Sessions</th><th class="r">Avg</th><th class="r">Trend</th></tr></thead>
            <tbody>
            <?php
            $prevAvg = null;
            foreach (array_reverse($yearSummary) as $i => $row):
                $trend = '';
                if ($prevAvg !== null) {
                    $diff = (int)$row['avg_total'] - $prevAvg;
                    if ($diff > 0)      $trend = "<span class='trend-up'>▲ $diff</span>";
                    elseif ($diff < 0)  $trend = "<span class='trend-down'>▼ " . abs($diff) . "</span>";
                    else                $trend = "<span class='trend-flat'>—</span>";
                }
                $prevAvg = (int)$row['avg_total'];
            ?>
            <tr>
                <td style="font-weight:500;"><?= (int)$row['yr'] ?></td>
                <td class="r" style="color:var(--text-muted);"><?= (int)$row['sessions'] ?></td>
                <td class="r" style="font-weight:600;"><?= (int)$row['avg_total'] ?></td>
                <td class="r"><?= $trend ?: '—' ?></td>
            </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
    </div>

    <!-- Gender/group breakdown all-time -->
    <div class="ana-card">
        <h2>Overall Breakdown (all time)</h2>
        <?php
        $grand = max(1, (int)$allTime['grand_total']);
        $groups = [
            ['label' => 'Male',     'value' => (int)$allTime['total_male'],     'color' => '#3b82f6'],
            ['label' => 'Female',   'value' => (int)$allTime['total_female'],   'color' => '#ec4899'],
            ['label' => 'Children', 'value' => (int)$allTime['total_children'], 'color' => '#10b981'],
            ['label' => 'Visitors', 'value' => (int)$allTime['total_visitors'], 'color' => '#f59e0b'],
        ];
        ?>
        <?php if ((int)$allTime['sessions'] === 0): ?>
            <p style="color:var(--text-muted); font-size:0.875rem;">No data yet.</p>
        <?php else: ?>
        <div style="display:flex; height:12px; border-radius:6px; overflow:hidden; margin-bottom:1rem; gap:2px;">
            <?php foreach ($groups as $g): ?>
                <div style="flex:<?= $g['value'] ?>; background:<?= $g['color'] ?>; min-width:<?= $g['value'] ? '2px' : '0' ?>;"
                     title="<?= $g['label'] ?>: <?= number_format($g['value']) ?>"></div>
            <?php endforeach; ?>
        </div>
        <table class="ana-table">
            <thead><tr><th></th><th>Group</th><th class="r">Total</th><th class="r">Share</th></tr></thead>
            <tbody>
            <?php foreach ($groups as $g): ?>
            <tr>
                <td style="width:14px;"><span style="display:inline-block; width:10px; height:10px; border-radius:50%; background:<?= $g['color'] ?>;"></span></td>
                <td><?= $g['label'] ?></td>
                <td class="r" style="font-weight:600;"><?= number_format($g['value']) ?></td>
                <td class="r" style="color:var(--text-muted);"><?= $grand ? round($g['value'] / $grand * 100) : 0 ?>%</td>
            </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
    </div>
</div>

<?php
require_once __DIR__ . '/../../includes/footer.php';
} catch (Throwable $e) {
    http_response_code(500);
    // Production-friendly failure; keep generic message.
    echo "<div style='padding:24px;'>Attendance analytics is temporarily unavailable.</div>";
    exit;
}
?>
