<?php
 
$sum = static function (int $days): array {
    $o = ['impression' => 0, 'click' => 0];
    foreach (Database::fetchAll("SELECT event_type, SUM(cnt) AS n FROM ad_events WHERE event_date >= CURDATE() - INTERVAL ? DAY GROUP BY event_type", [$days]) as $r) {
        $o[$r['event_type']] = (int) $r['n'];
    }
    return $o;
};
$d7 = $sum(7);
$d30 = $sum(30);
$ctr = static fn(int $i, int $c): string => $i > 0 ? number_format($c / $i * 100, 2) . '%' : '—';
$byZone = Database::fetchAll("SELECT z.name AS name,
        SUM(CASE WHEN e.event_type='impression' THEN e.cnt ELSE 0 END) AS imp,
        SUM(CASE WHEN e.event_type='click' THEN e.cnt ELSE 0 END) AS clk
    FROM ad_events e JOIN ad_zones z ON z.id = e.zone_id
    WHERE e.event_date >= CURDATE() - INTERVAL 30 DAY
    GROUP BY z.id, z.name ORDER BY imp DESC");
$bySpot = Database::fetchAll("SELECT s.name AS name, z.name AS zone,
        SUM(CASE WHEN e.event_type='impression' THEN e.cnt ELSE 0 END) AS imp,
        SUM(CASE WHEN e.event_type='click' THEN e.cnt ELSE 0 END) AS clk
    FROM ad_events e JOIN ad_spots s ON s.id = e.spot_id JOIN ad_zones z ON z.id = e.zone_id
    WHERE e.event_date >= CURDATE() - INTERVAL 30 DAY
    GROUP BY s.id, s.name, z.name ORDER BY imp DESC LIMIT 25");
?>
<div class="space-y-6">
    <div class="grid grid-cols-2 md:grid-cols-4 gap-4">
        <div class="bg-white rounded-lg border border-gray-200 p-4"><p class="text-[11px] text-gray-400 uppercase tracking-wider">Impressions · 7d</p><p class="text-2xl font-bold text-gray-900 mt-1"><?= number_format($d7['impression']) ?></p></div>
        <div class="bg-white rounded-lg border border-gray-200 p-4"><p class="text-[11px] text-gray-400 uppercase tracking-wider">Clicks · 7d</p><p class="text-2xl font-bold text-gray-900 mt-1"><?= number_format($d7['click']) ?></p></div>
        <div class="bg-white rounded-lg border border-gray-200 p-4"><p class="text-[11px] text-gray-400 uppercase tracking-wider">CTR · 7d</p><p class="text-2xl font-bold text-gray-900 mt-1"><?= $ctr($d7['impression'], $d7['click']) ?></p></div>
        <div class="bg-white rounded-lg border border-gray-200 p-4"><p class="text-[11px] text-gray-400 uppercase tracking-wider">CTR · 30d</p><p class="text-2xl font-bold text-gray-900 mt-1"><?= $ctr($d30['impression'], $d30['click']) ?></p></div>
    </div>

    <div class="bg-white rounded-lg border border-gray-200 overflow-hidden">
        <div class="px-5 py-3 border-b border-gray-200"><h3 class="text-sm font-semibold text-gray-900">By zone &mdash; last 30 days</h3></div>
        <table class="w-full text-sm">
            <thead><tr class="text-left text-[11px] text-gray-400 uppercase tracking-wider border-b border-gray-100"><th class="px-5 py-2">Zone</th><th class="px-5 py-2 text-right">Impressions</th><th class="px-5 py-2 text-right">Clicks</th><th class="px-5 py-2 text-right">CTR</th></tr></thead>
            <tbody>
            <?php if (empty($byZone)): ?><tr><td colspan="4" class="px-5 py-8 text-center text-gray-400">No data yet &mdash; stats appear as visitors view &amp; click your ads.</td></tr><?php endif; ?>
            <?php foreach ($byZone as $r): ?>
                <tr class="border-b border-gray-50 last:border-0"><td class="px-5 py-2 text-gray-700"><?= htmlspecialchars($r['name']) ?></td><td class="px-5 py-2 text-right tabular-nums"><?= number_format((int) $r['imp']) ?></td><td class="px-5 py-2 text-right tabular-nums"><?= number_format((int) $r['clk']) ?></td><td class="px-5 py-2 text-right tabular-nums font-medium"><?= $ctr((int) $r['imp'], (int) $r['clk']) ?></td></tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <div class="bg-white rounded-lg border border-gray-200 overflow-hidden">
        <div class="px-5 py-3 border-b border-gray-200"><h3 class="text-sm font-semibold text-gray-900">Top ads &mdash; last 30 days</h3></div>
        <table class="w-full text-sm">
            <thead><tr class="text-left text-[11px] text-gray-400 uppercase tracking-wider border-b border-gray-100"><th class="px-5 py-2">Ad</th><th class="px-5 py-2">Zone</th><th class="px-5 py-2 text-right">Impressions</th><th class="px-5 py-2 text-right">Clicks</th><th class="px-5 py-2 text-right">CTR</th></tr></thead>
            <tbody>
            <?php if (empty($bySpot)): ?><tr><td colspan="5" class="px-5 py-8 text-center text-gray-400">No data yet.</td></tr><?php endif; ?>
            <?php foreach ($bySpot as $r): ?>
                <tr class="border-b border-gray-50 last:border-0"><td class="px-5 py-2 text-gray-700"><?= htmlspecialchars($r['name'] ?: '—') ?></td><td class="px-5 py-2 text-gray-400"><?= htmlspecialchars($r['zone']) ?></td><td class="px-5 py-2 text-right tabular-nums"><?= number_format((int) $r['imp']) ?></td><td class="px-5 py-2 text-right tabular-nums"><?= number_format((int) $r['clk']) ?></td><td class="px-5 py-2 text-right tabular-nums font-medium"><?= $ctr((int) $r['imp'], (int) $r['clk']) ?></td></tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <p class="text-[11px] text-gray-400">Impressions are counted on <strong>viewability</strong> (the ad actually appeared on screen), not on page render. Clicks include banner clicks and popunder fires.</p>
</div>
