<?php
/**
 * admin/report.php — visitor / search / availability analytics.
 * Reads the analytics_events table populated by app/analytics.php.
 * Place in admin/. Admin login required. Uses the site header/footer.
 */
require __DIR__ . '/../app/config.php';   // adjust if admin/ sits elsewhere
require_admin();

$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS analytics_events (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, ts INT UNSIGNED NOT NULL, day DATE NOT NULL,
    type VARCHAR(16) NOT NULL, sid CHAR(32) NOT NULL DEFAULT '', path VARCHAR(160) NOT NULL DEFAULT '',
    ref_host VARCHAR(120) NOT NULL DEFAULT '', src VARCHAR(40) NOT NULL DEFAULT '', device VARCHAR(8) NOT NULL DEFAULT '',
    bot TINYINT NOT NULL DEFAULT 0, from_code VARCHAR(10) NOT NULL DEFAULT '', to_code VARCHAR(10) NOT NULL DEFAULT '',
    train_no VARCHAR(10) NOT NULL DEFAULT '', jdate DATE NULL, n INT NOT NULL DEFAULT 0,
    note VARCHAR(60) NOT NULL DEFAULT '', ip_hash CHAR(16) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

/* ---------------- filters ---------------- */
$today = new DateTime('now', new DateTimeZone('Asia/Kolkata'));
$preset = $_GET['preset'] ?? '30';
$to   = $_GET['to']   ?? $today->format('Y-m-d');
$from = $_GET['from'] ?? (clone $today)->modify('-29 day')->format('Y-m-d');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) $from = (clone $today)->modify('-29 day')->format('Y-m-d');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to))   $to   = $today->format('Y-m-d');
if (isset($_GET['preset']) && !isset($_GET['from'])) {
    $to = $today->format('Y-m-d');
    if ($preset === 'today')   $from = $to;
    elseif ($preset === '7')   $from = (clone $today)->modify('-6 day')->format('Y-m-d');
    elseif ($preset === '30')  $from = (clone $today)->modify('-29 day')->format('Y-m-d');
    elseif ($preset === 'all') { $from = '2026-01-01'; }
}
$incbots = !empty($_GET['incbots']) ? 1 : 0;
$dev     = in_array($_GET['dev'] ?? '', ['mobile', 'desktop', 'bot'], true) ? $_GET['dev'] : '';
$srcF    = isset($_GET['src']) ? substr(preg_replace('/[^0-9A-Za-z_\-]/', '', $_GET['src']), 0, 40) : '';

$w = ["day BETWEEN ? AND ?"];
$p = [$from, $to];
if (!$incbots) { $w[] = "bot = 0"; }
if ($dev !== '') { $w[] = "device = ?"; $p[] = $dev; }
if ($srcF !== '') { $w[] = "src = ?"; $p[] = $srcF; }
$W = implode(' AND ', $w);

function q(PDO $pdo, string $sql, array $p): array {
    $st = $pdo->prepare($sql); $st->execute($p); return $st->fetchAll(PDO::FETCH_ASSOC);
}
function names(PDO $pdo, string $table, string $keyCol, string $nameCol, array $keys): array {
    $keys = array_values(array_unique(array_filter($keys)));
    if (!$keys) return [];
    $in = implode(',', array_fill(0, count($keys), '?'));
    $st = $pdo->prepare("SELECT $keyCol k, $nameCol nm FROM $table WHERE $keyCol IN ($in)");
    $st->execute($keys);
    $m = []; foreach ($st as $r) $m[$r['k']] = $r['nm']; return $m;
}

/* ---------------- metrics ---------------- */
$sum = q($pdo, "SELECT
        COALESCE(SUM(type='pageview'),0) pv,
        COUNT(DISTINCT CASE WHEN type='pageview' THEN sid END) uv,
        COALESCE(SUM(type='search'),0) s,
        COALESCE(SUM(type='availability'),0) a,
        COALESCE(SUM(type='pnr'),0) pnr
    FROM analytics_events WHERE $W", $p)[0] ?? ['pv'=>0,'uv'=>0,'s'=>0,'a'=>0,'pnr'=>0];

$daily = q($pdo, "SELECT day,
        COALESCE(SUM(type='pageview'),0) pv,
        COUNT(DISTINCT CASE WHEN type='pageview' THEN sid END) uv,
        COALESCE(SUM(type='search'),0) s,
        COALESCE(SUM(type='availability'),0) a,
        COALESCE(SUM(type='pnr'),0) pnr
    FROM analytics_events WHERE $W GROUP BY day ORDER BY day", $p);

$routes = q($pdo, "SELECT from_code, to_code, COUNT(*) c
    FROM analytics_events WHERE $W AND type='search' AND from_code<>'' AND to_code<>''
    GROUP BY from_code, to_code ORDER BY c DESC LIMIT 25", $p);

$origins = q($pdo, "SELECT from_code k, COUNT(*) c FROM analytics_events
    WHERE $W AND type='search' AND from_code<>'' GROUP BY from_code ORDER BY c DESC LIMIT 12", $p);
$dests   = q($pdo, "SELECT to_code k, COUNT(*) c FROM analytics_events
    WHERE $W AND type='search' AND to_code<>'' GROUP BY to_code ORDER BY c DESC LIMIT 12", $p);

$trains = q($pdo, "SELECT train_no k, COUNT(*) c FROM analytics_events
    WHERE $W AND type='availability' AND train_no<>'' GROUP BY train_no ORDER BY c DESC LIMIT 15", $p);

$sources = q($pdo, "SELECT CASE WHEN src='' THEN '(direct / app)' ELSE src END src,
        COUNT(DISTINCT sid) uv, COUNT(*) hits
    FROM analytics_events WHERE $W AND type='pageview' GROUP BY src ORDER BY uv DESC LIMIT 15", $p);

$referrers = q($pdo, "SELECT ref_host, COUNT(DISTINCT sid) uv FROM analytics_events
    WHERE $W AND type='pageview' AND ref_host<>'' GROUP BY ref_host ORDER BY uv DESC LIMIT 12", $p);

$devices = q($pdo, "SELECT device, COUNT(DISTINCT sid) uv, COUNT(*) hits FROM analytics_events
    WHERE $W AND type='pageview' GROUP BY device ORDER BY uv DESC", $p);

$stCodes = []; foreach ($routes as $r) { $stCodes[] = $r['from_code']; $stCodes[] = $r['to_code']; }
foreach ($origins as $r) $stCodes[] = $r['k']; foreach ($dests as $r) $stCodes[] = $r['k'];
$stName = names($pdo, 'stations', 'code', 'name', $stCodes);
$trName = names($pdo, 'trains', 'train_no', 'train_name', array_column($trains, 'k'));
function sn(array $m, string $c): string { return ($m[$c] ?? '') !== '' ? $m[$c] . " ($c)" : $c; }

$maxPv = 1; foreach ($daily as $d) $maxPv = max($maxPv, (int) $d['pv']);
$pvN = (int) $sum['pv']; $uvN = (int) $sum['uv']; $sN = (int) $sum['s']; $aN = (int) $sum['a']; $pnrN = (int) $sum['pnr'];
$spv = $uvN ? round($sN / $uvN, 2) : 0;
$aps = $sN ? round($aN / $sN * 100) : 0;

function h($v): string { return htmlspecialchars((string) $v, ENT_QUOTES, 'UTF-8'); }
function qs(array $over): string {
    $base = ['from' => $_GET['from'] ?? null, 'to' => $_GET['to'] ?? null, 'preset' => $_GET['preset'] ?? null,
             'incbots' => $_GET['incbots'] ?? null, 'dev' => $_GET['dev'] ?? null, 'src' => $_GET['src'] ?? null];
    $m = array_merge($base, $over);
    $m = array_filter($m, fn($v) => $v !== null && $v !== '');
    return '?' . http_build_query($m);
}

$page_title = 'Analytics';
require APP_DIR . '/header.php';
?>
<style>
.rpt{--mut:#5b7373;--line:#e3ecec;--brand:#0d6e6e}
.rpt h2{font-size:1rem;margin:1.6rem 0 .5rem;color:var(--brand)}
.rpt .r-bar{background:#fff;border:1px solid var(--line);border-radius:12px;padding:12px;margin-top:12px}
.rpt .r-filters{display:flex;flex-wrap:wrap;gap:10px;align-items:end;margin-top:10px}
.rpt .r-filters label{font-size:.78rem;color:var(--mut);display:block;margin-bottom:2px}
.rpt .r-filters input,.rpt .r-filters select{font:inherit;padding:6px 8px;border:1px solid var(--line);border-radius:8px;background:#fff}
.rpt .r-presets a{display:inline-block;margin-right:6px;font-size:.85rem;padding:3px 9px;border:1px solid var(--line);border-radius:999px;text-decoration:none;background:#fff;color:var(--brand)}
.rpt .r-cards{display:grid;grid-template-columns:repeat(auto-fit,minmax(150px,1fr));gap:10px;margin-top:12px}
.rpt .r-card{background:#fff;border:1px solid var(--line);border-radius:12px;padding:14px}
.rpt .r-card .k{font-size:.75rem;color:var(--mut);text-transform:uppercase;letter-spacing:.4px}
.rpt .r-card .v{font-size:1.7rem;font-weight:700;margin-top:3px}
.rpt .r-card .sub{font-size:.78rem;color:var(--mut);margin-top:2px}
.rpt table{width:100%;border-collapse:collapse;background:#fff;border:1px solid var(--line);border-radius:12px;overflow:hidden}
.rpt th,.rpt td{padding:8px 10px;text-align:left;border-bottom:1px solid var(--line);font-size:.9rem}
.rpt th{background:#eef5f5;color:var(--mut);font-weight:600;font-size:.78rem;text-transform:uppercase;letter-spacing:.3px}
.rpt td.n,.rpt th.n{text-align:right;font-variant-numeric:tabular-nums}
.rpt tr:last-child td{border-bottom:0}
.rpt .r-grid2{display:grid;grid-template-columns:1fr 1fr;gap:16px}
.rpt .r-chart{display:flex;align-items:flex-end;gap:3px;height:150px;padding-top:8px;overflow-x:auto}
.rpt .r-chart .col{flex:1 0 14px;display:flex;flex-direction:column;justify-content:flex-end;align-items:center;min-width:14px}
.rpt .r-chart .col i{display:block;width:70%;background:var(--brand);border-radius:3px 3px 0 0;min-height:2px}
.rpt .r-chart .col span{font-size:.6rem;color:var(--mut);margin-top:3px;white-space:nowrap;transform:rotate(-60deg);transform-origin:top left;height:34px}
.rpt .empty{color:var(--mut);padding:14px;text-align:center}
@media(max-width:720px){.rpt .r-cards{grid-template-columns:repeat(2,1fr)}.rpt .r-grid2{grid-template-columns:1fr}}
</style>

<div class="rpt">
  <h1>Analytics</h1>

  <div class="r-bar">
    <div class="r-presets">
      <a href="<?= h(qs(['preset'=>'today','from'=>null,'to'=>null])) ?>">Today</a>
      <a href="<?= h(qs(['preset'=>'7','from'=>null,'to'=>null])) ?>">7 days</a>
      <a href="<?= h(qs(['preset'=>'30','from'=>null,'to'=>null])) ?>">30 days</a>
      <a href="<?= h(qs(['preset'=>'all','from'=>null,'to'=>null])) ?>">All</a>
    </div>
    <form method="get" class="r-filters">
      <div><label>From</label><input type="date" name="from" value="<?= h($from) ?>"></div>
      <div><label>To</label><input type="date" name="to" value="<?= h($to) ?>"></div>
      <div><label>Device</label><select name="dev">
        <option value="">All</option>
        <?php foreach (['mobile','desktop','bot'] as $o): ?><option value="<?= $o ?>" <?= $dev===$o?'selected':'' ?>><?= ucfirst($o) ?></option><?php endforeach; ?>
      </select></div>
      <div><label>Source</label><input type="text" name="src" value="<?= h($srcF) ?>" placeholder="e.g. wa" size="8"></div>
      <div><label><input type="checkbox" name="incbots" value="1" <?= $incbots?'checked':'' ?>> include bots</label></div>
      <div><button class="btn btn-sm">Apply</button></div>
    </form>
    <div class="muted" style="font-size:.8rem;margin-top:8px"><?= h($from) ?> &rarr; <?= h($to) ?><?= $incbots?'':' · bots excluded' ?><?= $dev?' · '.h($dev):'' ?><?= $srcF?' · src='.h($srcF):'' ?></div>
  </div>

  <div class="r-cards">
    <div class="r-card"><div class="k">Visitors</div><div class="v"><?= number_format($uvN) ?></div><div class="sub">unique sessions</div></div>
    <div class="r-card"><div class="k">Pageviews</div><div class="v"><?= number_format($pvN) ?></div><div class="sub"><?= $uvN?round($pvN/$uvN,1):0 ?> per visitor</div></div>
    <div class="r-card"><div class="k">Searches</div><div class="v"><?= number_format($sN) ?></div><div class="sub"><?= $spv ?> per visitor</div></div>
    <div class="r-card"><div class="k">Availability checks</div><div class="v"><?= number_format($aN) ?></div><div class="sub"><?= $aps ?> per 100 searches</div></div>
    <div class="r-card"><div class="k">PNR checks</div><div class="v"><?= number_format($pnrN) ?></div><div class="sub">this period</div></div>
  </div>

  <h2>Daily trend — pageviews</h2>
  <div class="r-bar">
    <?php if ($daily): ?>
    <div class="r-chart">
      <?php foreach ($daily as $d): $hgt = round((int)$d['pv'] / $maxPv * 120); ?>
        <div class="col" title="<?= h($d['day']) ?> — <?= (int)$d['pv'] ?> views, <?= (int)$d['uv'] ?> visitors, <?= (int)$d['s'] ?> searches, <?= (int)$d['a'] ?> avail">
          <i style="height:<?= $hgt ?>px"></i><span><?= h(substr($d['day'],5)) ?></span>
        </div>
      <?php endforeach; ?>
    </div>
    <?php else: ?><div class="empty">No data in this range yet.</div><?php endif; ?>
  </div>

  <h2>Day-by-day</h2>
  <table>
    <tr><th>Date</th><th class="n">Visitors</th><th class="n">Pageviews</th><th class="n">Searches</th><th class="n">Avail.</th><th class="n">PNR</th></tr>
    <?php if (!$daily): ?><tr><td colspan="6" class="empty">No data.</td></tr><?php endif; ?>
    <?php foreach (array_reverse($daily) as $d): ?>
      <tr><td><?= h($d['day']) ?></td><td class="n"><?= (int)$d['uv'] ?></td><td class="n"><?= (int)$d['pv'] ?></td><td class="n"><?= (int)$d['s'] ?></td><td class="n"><?= (int)$d['a'] ?></td><td class="n"><?= (int)$d['pnr'] ?></td></tr>
    <?php endforeach; ?>
  </table>

  <h2>Top routes searched</h2>
  <table>
    <tr><th>From</th><th>To</th><th class="n">Searches</th></tr>
    <?php if (!$routes): ?><tr><td colspan="3" class="empty">No searches yet.</td></tr><?php endif; ?>
    <?php foreach ($routes as $r): ?>
      <tr><td><?= h(sn($stName,$r['from_code'])) ?></td><td><?= h(sn($stName,$r['to_code'])) ?></td><td class="n"><?= (int)$r['c'] ?></td></tr>
    <?php endforeach; ?>
  </table>

  <div class="r-grid2">
    <div>
      <h2>Top origin stations</h2>
      <table><tr><th>Station</th><th class="n">Searches</th></tr>
        <?php foreach ($origins as $r): ?><tr><td><?= h(sn($stName,$r['k'])) ?></td><td class="n"><?= (int)$r['c'] ?></td></tr><?php endforeach; ?>
        <?php if (!$origins): ?><tr><td colspan="2" class="empty">—</td></tr><?php endif; ?>
      </table>
    </div>
    <div>
      <h2>Top destination stations</h2>
      <table><tr><th>Station</th><th class="n">Searches</th></tr>
        <?php foreach ($dests as $r): ?><tr><td><?= h(sn($stName,$r['k'])) ?></td><td class="n"><?= (int)$r['c'] ?></td></tr><?php endforeach; ?>
        <?php if (!$dests): ?><tr><td colspan="2" class="empty">—</td></tr><?php endif; ?>
      </table>
    </div>
  </div>

  <h2>Most-checked trains (availability)</h2>
  <table>
    <tr><th>Train</th><th class="n">Checks</th></tr>
    <?php if (!$trains): ?><tr><td colspan="2" class="empty">No availability checks yet.</td></tr><?php endif; ?>
    <?php foreach ($trains as $r): ?>
      <tr><td><?= h($r['k']) ?><?= ($trName[$r['k']] ?? '')!=='' ? ' — '.h($trName[$r['k']]) : '' ?></td><td class="n"><?= (int)$r['c'] ?></td></tr>
    <?php endforeach; ?>
  </table>

  <div class="r-grid2">
    <div>
      <h2>Traffic sources</h2>
      <table><tr><th>Source</th><th class="n">Visitors</th><th class="n">Views</th></tr>
        <?php foreach ($sources as $r): ?><tr><td><?= h($r['src']) ?></td><td class="n"><?= (int)$r['uv'] ?></td><td class="n"><?= (int)$r['hits'] ?></td></tr><?php endforeach; ?>
        <?php if (!$sources): ?><tr><td colspan="3" class="empty">—</td></tr><?php endif; ?>
      </table>
      <p class="muted" style="font-size:.78rem">Tip: add <code>?src=wa</code> to the URL in your WhatsApp message to see that campaign here.</p>
    </div>
    <div>
      <h2>Referrers &amp; devices</h2>
      <table><tr><th>Referrer</th><th class="n">Visitors</th></tr>
        <?php foreach ($referrers as $r): ?><tr><td><?= h($r['ref_host']) ?></td><td class="n"><?= (int)$r['uv'] ?></td></tr><?php endforeach; ?>
        <?php if (!$referrers): ?><tr><td colspan="2" class="empty">— (direct/app)</td></tr><?php endif; ?>
      </table>
      <table style="margin-top:10px"><tr><th>Device</th><th class="n">Visitors</th><th class="n">Views</th></tr>
        <?php foreach ($devices as $r): ?><tr><td><?= h($r['device']?:'(unknown)') ?></td><td class="n"><?= (int)$r['uv'] ?></td><td class="n"><?= (int)$r['hits'] ?></td></tr><?php endforeach; ?>
        <?php if (!$devices): ?><tr><td colspan="3" class="empty">—</td></tr><?php endif; ?>
      </table>
    </div>
  </div>
</div>
<?php require APP_DIR . '/footer.php'; ?>