<?php
require_once __DIR__ . '/header.php';
perms_require('view_analytics');

$user    = current_user();
$isSuper = perms_is_super($user);

$formIdF  = v_int($_GET['form_id'] ?? 0, 0);
$dateFrom = v_clean_string($_GET['date_from'] ?? '', 20);
$dateTo   = v_clean_string($_GET['date_to']   ?? '', 20);

$forms = $pdo->query('SELECT id, name FROM forms WHERE deleted_at IS NULL ORDER BY name')->fetchAll();

$where  = ['a.deleted_at IS NULL'];
$params = [];
if (!$isSuper && $user['facility_id']) {
    $where[]  = 'c.facility_id = ?';
    $params[] = $user['facility_id'];
}
if ($formIdF > 0)                                                                           { $where[] = 'a.form_id = ?';            $params[] = $formIdF; }
if ($dateFrom !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateFrom))                     { $where[] = 'DATE(a.created_at) >= ?';  $params[] = $dateFrom; }
if ($dateTo   !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo))                       { $where[] = 'DATE(a.created_at) <= ?';  $params[] = $dateTo; }

$whereSql = ' WHERE ' . implode(' AND ', $where);

// Headline KPIs
$kpi = $pdo->prepare("
    SELECT
        COUNT(*) AS n,
        COALESCE(ROUND(AVG(a.percentage), 2), 0) AS mean_p,
        COALESCE(ROUND(STDDEV_SAMP(a.percentage), 2), 0) AS sd_p,
        SUM(CASE WHEN a.percentage < 50 THEN 1 ELSE 0 END) AS fail_count
    FROM attempts a
    JOIN candidates c ON c.id = a.candidate_id
    $whereSql
");
$kpi->execute($params);
$kpi = $kpi->fetch();

// Per-form summary
$perForm = $pdo->prepare("
    SELECT f.id, f.name,
           COUNT(a.id) AS n,
           ROUND(AVG(a.percentage), 2) AS mean_p,
           ROUND(STDDEV_SAMP(a.percentage), 2) AS sd_p,
           SUM(CASE WHEN a.percentage < 50 THEN 1 ELSE 0 END) AS fails,
           MIN(a.percentage) AS min_p, MAX(a.percentage) AS max_p
    FROM forms f
    LEFT JOIN attempts a ON a.form_id = f.id AND a.deleted_at IS NULL
    LEFT JOIN candidates c ON c.id = a.candidate_id
    WHERE f.deleted_at IS NULL
    GROUP BY f.id, f.name
    ORDER BY f.name
");
$perForm->execute();
$perForm = $perForm->fetchAll();

// Grade-band distribution for chosen form
$dist = [];
if ($formIdF > 0) {
    $q = $pdo->prepare("
        SELECT final_grade, COUNT(*) AS n
        FROM attempts a
        JOIN candidates c ON c.id = a.candidate_id
        $whereSql
        GROUP BY final_grade
        ORDER BY n DESC
    ");
    $q->execute($params);
    $dist = $q->fetchAll();
}
?>
<h2>Analytics</h2>

<form method="get" class="mb-2">
    <div class="form-row">
        <div class="field"><label>Form</label>
            <select name="form_id">
                <option value="0">All forms</option>
                <?php foreach ($forms as $f): ?>
                    <option value="<?php echo (int)$f['id']; ?>" <?php if ($formIdF==(int)$f['id']) echo 'selected'; ?>><?php echo h($f['name']); ?></option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="field"><label>Date from</label><input type="date" name="date_from" value="<?php echo h($dateFrom); ?>"></div>
        <div class="field"><label>Date to</label><input type="date" name="date_to" value="<?php echo h($dateTo); ?>"></div>
    </div>
    <button type="submit" class="btn btn-primary">Apply</button>
    <a href="analytics.php" class="btn">Clear</a>
</form>

<div class="kpi-grid">
    <div class="kpi"><div class="label">Total Attempts</div><div class="value"><?php echo (int)$kpi['n']; ?></div></div>
    <div class="kpi"><div class="label">Mean %</div><div class="value"><?php echo h($kpi['mean_p']); ?></div></div>
    <div class="kpi"><div class="label">Std Dev</div><div class="value"><?php echo h($kpi['sd_p']); ?></div></div>
    <div class="kpi"><div class="label">Failed (<50%)</div><div class="value"><?php echo (int)$kpi['fail_count']; ?></div><div class="sub">of <?php echo (int)$kpi['n']; ?></div></div>
</div>

<h3>Per-Form Summary</h3>
<div class="table-wrap">
<table class="data">
    <thead><tr><th>Form</th><th class="num">N</th><th class="num">Mean %</th><th class="num">SD</th><th class="num">Min</th><th class="num">Max</th><th class="num">Fails</th><th class="num">Fail %</th></tr></thead>
    <tbody>
    <?php foreach ($perForm as $r): ?>
        <tr>
            <td data-label="Form"><?php echo h($r['name']); ?></td>
            <td data-label="N"   class="num"><?php echo (int)$r['n']; ?></td>
            <td data-label="Mean %" class="num"><?php echo $r['n'] ? h($r['mean_p']) : '—'; ?></td>
            <td data-label="SD" class="num"><?php echo $r['n'] ? h($r['sd_p']) : '—'; ?></td>
            <td data-label="Min" class="num"><?php echo $r['n'] ? h($r['min_p']) : '—'; ?></td>
            <td data-label="Max" class="num"><?php echo $r['n'] ? h($r['max_p']) : '—'; ?></td>
            <td data-label="Fails" class="num"><?php echo (int)$r['fails']; ?></td>
            <td data-label="Fail %" class="num">
                <?php echo $r['n'] > 0 ? round(($r['fails'] / $r['n']) * 100, 1) . '%' : '—'; ?>
            </td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</div>

<?php if ($formIdF > 0 && $dist): ?>
    <h3 class="mt-3">Grade Distribution (selected form)</h3>
    <div class="table-wrap">
    <table class="data">
        <thead><tr><th>Grade</th><th class="num">Count</th><th class="num">Share</th><th>Bar</th></tr></thead>
        <tbody>
        <?php
        $tot = array_sum(array_column($dist, 'n'));
        foreach ($dist as $d):
            $pct = $tot > 0 ? round(($d['n']/$tot)*100, 1) : 0;
        ?>
            <tr>
                <td data-label="Grade"><?php echo h($d['final_grade']); ?></td>
                <td data-label="Count" class="num"><?php echo (int)$d['n']; ?></td>
                <td data-label="Share" class="num"><?php echo $pct; ?>%</td>
                <td data-label="Bar">
                    <div style="background:#dbeafe; height:14px; border-radius:4px; max-width:300px;">
                        <div style="background:var(--c-primary); height:14px; border-radius:4px; width:<?php echo $pct; ?>%;"></div>
                    </div>
                </td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
    </div>
<?php endif; ?>

<?php require __DIR__ . '/footer.php'; ?>
