<?php
/**
 * System Reference Index — modern, informative, attractive.
 *
 *   Tab 1: Overview          (health cards + activity timeline)
 *   Tab 2: Error Log         (filters · grouping · stack traces · CSV export · resolve)
 *   Tab 3: Pages → Tables    (live scan of every PHP file with SQL operations)
 *   Tab 4: Database Schema   (card grid with column metadata)
 *   Tab 5: Dependency Matrix (table → pages that touch it)
 *   Tab 6: Changelog         (version history with file & table impact)
 *
 * Every PHP error / exception in the application is auto-captured by the
 * global handler in includes/config.php and stored in system_reference_errors
 * with the URL, file, line, stack trace, user and IP that triggered it.
 */
require_once __DIR__ . '/../includes/config.php';
$me = require_login('internal');
if (!user_has_permission((int)$me['id'], 'reference.view')) {
    flash('error','No permission.'); redirect('/dashboard.php');
}

$pdo = db();
$tab = $_GET['tab'] ?? 'overview';

function qAll(PDO $pdo, string $sql, array $params = []): array {
    $st = $pdo->prepare($sql); $st->execute($params); return $st->fetchAll();
}
function qRow(PDO $pdo, string $sql, array $params = []): ?array {
    $st = $pdo->prepare($sql); $st->execute($params); $r = $st->fetch(); return $r ?: null;
}

/* ---------------- POST actions ---------------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    csrf_check_or_die();
    $a = $_POST['action'] ?? '';
    if ($a === 'clear_errors') {
        $pdo->exec('DELETE FROM system_reference_errors');
        audit_log('reference.errors.clear', 'system_reference_errors');
        flash('success','Error log cleared.');
        redirect('/admin/reference.php?tab=errors');
    }
    if ($a === 'resolve_one') {
        $id = (int)($_POST['id'] ?? 0);
        $pdo->prepare('UPDATE system_reference_errors SET resolved = 1 - resolved WHERE id=?')->execute([$id]);
        redirect('/admin/reference.php?tab=errors' . (isset($_POST['return']) ? '&' . $_POST['return'] : ''));
    }
    if ($a === 'resolve_all_filtered') {
        // Same WHERE as the list — passed in hidden inputs
        $level   = $_POST['filter_level']  ?? '';
        $status  = $_POST['filter_status'] ?? '';
        $search  = $_POST['filter_search'] ?? '';
        $where = ['1=1']; $params = [];
        if ($level)  { $where[] = 'error_level=?'; $params[] = $level; }
        if ($status === 'open')     $where[] = 'resolved=0';
        if ($status === 'resolved') $where[] = 'resolved=1';
        if ($search !== '') { $where[] = '(message LIKE ? OR file LIKE ?)'; $params[] = "%$search%"; $params[] = "%$search%"; }
        $sql = "UPDATE system_reference_errors SET resolved=1 WHERE " . implode(' AND ', $where);
        $st = $pdo->prepare($sql); $st->execute($params);
        audit_log('reference.errors.resolve_bulk','system_reference_errors',null,null,['filter'=>$_POST]);
        flash('success', $st->rowCount() . ' error(s) marked resolved.');
        redirect('/admin/reference.php?tab=errors');
    }
    if ($a === 'rescan') {
        try {
            if (is_file(__DIR__ . '/../bin/reference-scan-runner.php')) {
                require __DIR__ . '/../bin/reference-scan-runner.php';
            }
            audit_log('reference.scan', 'system_reference_pages');
            flash('success','System Reference rescanned.');
        } catch (Throwable $t) {
            flash('error','Rescan failed: ' . $t->getMessage());
        }
        redirect('/admin/reference.php?tab=' . urlencode($tab));
    }
}

/* ---------------- CSV export ---------------- */
if ($tab === 'errors_csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="error_log_'.date('Ymd_His').'.csv"');
    $out = fopen('php://output', 'w');
    fwrite($out, "\xEF\xBB\xBF");
    fputcsv($out, ['Occurred', 'Level', 'Message', 'File', 'Line', 'URL', 'Method', 'User ID', 'IP', 'Resolved']);
    $rs = $pdo->query('SELECT occurred_at, error_level, message, file, line, url, method, user_id, ip, resolved FROM system_reference_errors ORDER BY occurred_at DESC');
    while ($r = $rs->fetch()) fputcsv($out, $r);
    fclose($out); exit;
}

/* ---------------- File scan (used by 3 tabs) ---------------- */
function srx_scan(string $base): array
{
    $out = [];
    $dirs = ['' => 'root', 'admin' => 'admin', 'client' => 'client', 'includes' => 'includes', 'bin' => 'bin'];
    foreach ($dirs as $sub => $module) {
        $dir = rtrim($base . '/' . $sub, '/');
        if (!is_dir($dir)) continue;
        foreach (scandir($dir) as $f) {
            if (!str_ends_with($f, '.php')) continue;
            $rel = ltrim("$sub/$f", '/');
            $body = (string) @file_get_contents("$dir/$f");
            if ($body === '') continue;
            $tables = [];
            if (preg_match_all('/\b(?:UPDATE|INSERT\s+INTO|FROM|JOIN|DELETE\s+FROM)\s+`?([a-z_][a-z0-9_]*)`?/i', $body, $m)) {
                $tables = array_values(array_unique(array_map('strtolower', $m[1])));
            }
            $ops = [];
            if (preg_match('/\bSELECT\b/i', $body)) $ops[] = 'SELECT';
            if (preg_match('/\bINSERT\b/i', $body)) $ops[] = 'INSERT';
            if (preg_match('/\bUPDATE\b/i', $body)) $ops[] = 'UPDATE';
            if (preg_match('/\bDELETE\b/i', $body)) $ops[] = 'DELETE';
            $roles = [];
            if (preg_match('/require_login\([\'"](\w+)[\'"]/', $body, $m)) $roles[] = $m[1];
            $desc = '';
            if (preg_match('#/\*\*(.*?)\*/#s', $body, $m)) {
                if (preg_match('/^\s*\*\s+(\S.*)$/m', $m[1], $mm)) $desc = trim($mm[1]);
            }
            $out[] = compact('module','rel','tables','ops','roles','desc')
                   + ['lines' => substr_count($body, "\n") + 1];
        }
    }
    return $out;
}

$base  = realpath(__DIR__ . '/..');
$pages = srx_scan($base);

/* Pull every table + columns */
$dbTables = $pdo->query('SELECT table_name, table_rows
                         FROM information_schema.tables
                         WHERE table_schema = "' . DB_NAME . '" AND table_type="BASE TABLE"
                         ORDER BY table_name')->fetchAll();
$colMap = [];
$colsRs = $pdo->prepare('SELECT table_name, column_name, data_type, column_key, is_nullable
                         FROM information_schema.columns WHERE table_schema = ? ORDER BY ordinal_position');
$colsRs->execute([DB_NAME]);
while ($c = $colsRs->fetch()) $colMap[$c['table_name']][] = $c;

$totalPages    = count($pages);
$modules       = count(array_unique(array_column($pages, 'module')));
$tablesTouched = count(array_unique(array_merge(...(array_column($pages, 'tables') ?: [[]]))));

/* ---------------- Error log stats (for hero KPIs) ---------------- */
$errStats = $pdo->query("
    SELECT
        COUNT(*) AS total,
        SUM(CASE WHEN occurred_at >= NOW() - INTERVAL 1 DAY  THEN 1 ELSE 0 END) AS last_24h,
        SUM(CASE WHEN occurred_at >= NOW() - INTERVAL 7 DAY  THEN 1 ELSE 0 END) AS last_7d,
        SUM(CASE WHEN resolved = 0 THEN 1 ELSE 0 END)        AS open,
        SUM(CASE WHEN resolved = 0 AND error_level IN ('critical','error','alert','emergency') THEN 1 ELSE 0 END) AS open_critical,
        SUM(CASE WHEN resolved = 1 THEN 1 ELSE 0 END)        AS resolved
    FROM system_reference_errors
")->fetch();
$errStats = array_map('intval', $errStats);

$pageTitle = 'System Reference Index';
require __DIR__ . '/../includes/header.php';
?>
<style>
.rv-title-bar { padding:14px 0 10px; border-bottom: 1px solid var(--m-border); margin-bottom:14px; }
.rv-tabs { border-bottom: 2px solid var(--m-border); margin-bottom:14px; }
.rv-tabs .nav-link { padding: 9px 16px; font-size:.88rem; color: var(--m-text-soft); border-radius:6px 6px 0 0; }
.rv-tabs .nav-link.active { color:var(--tblr-primary); border-bottom: 2px solid var(--tblr-primary); margin-bottom:-2px; background:transparent; font-weight: 600; }
.rv-tabs .nav-link:hover { color: var(--m-text); }
.rv-tabs .nav-link .badge { font-size: .65rem; margin-left: 4px; }

.health-card { background: var(--m-surface); border: 1px solid var(--m-border); border-radius: 10px; padding: 14px 16px; transition: all .15s; }
.health-card:hover { border-color: var(--m-border-strong); }
.health-card .lbl { font-size:.7rem; text-transform: uppercase; letter-spacing: .05em; color: var(--m-text-soft); }
.health-card .val { font-size: 1.7rem; font-weight: 700; line-height: 1.1; color: var(--m-text); margin-top: 4px; font-variant-numeric: tabular-nums; }
.health-card .ico { width: 36px; height: 36px; border-radius: 9px; display: inline-flex; align-items: center; justify-content: center; font-size: 1.1rem; }

.err-row.lvl-critical, .err-row.lvl-error, .err-row.lvl-alert, .err-row.lvl-emergency { border-left: 3px solid #dc2626; }
.err-row.lvl-warning  { border-left: 3px solid #f59e0b; }
.err-row.lvl-notice, .err-row.lvl-info { border-left: 3px solid #0ea5e9; }
.err-row.lvl-debug    { border-left: 3px solid #94a3b8; }
.err-row.resolved     { opacity: .5; }
.err-row .msg-link { cursor: pointer; color: var(--m-text); text-decoration: none; }
.err-row .msg-link:hover { color: var(--tblr-primary); }
.err-row .file-meta { font-family: 'JetBrains Mono', monospace; font-size: .72rem; color: var(--m-text-soft); }
.err-row .trace-panel { display: none; background: var(--m-surface-2); padding: 10px 14px; border-top: 1px solid var(--m-border); font-family: 'JetBrains Mono', monospace; font-size: .75rem; max-height: 320px; overflow: auto; white-space: pre-wrap; word-break: break-all; }
.err-row.expanded .trace-panel { display: block; }

.err-grp .grp-count { display: inline-block; background: #1e3a8a; color: #fff; padding: 1px 8px; border-radius: 999px; font-weight: 600; font-size: .72rem; }
.err-grp .grp-count.high { background: #dc2626; }

.lvl-pill { display:inline-block; padding: 2px 8px; border-radius: 4px; font-size: .68rem; font-weight: 600; text-transform: uppercase; letter-spacing: .04em; }
.lvl-critical, .lvl-error, .lvl-alert, .lvl-emergency { background: #fee2e2; color: #991b1b; }
.lvl-warning  { background: #fef3c7; color: #92400e; }
.lvl-notice, .lvl-info { background: #dbeafe; color: #1e40af; }
.lvl-debug    { background: #f1f5f9; color: #475569; }
html[data-bs-theme="dark"] .lvl-critical, html[data-bs-theme="dark"] .lvl-error { background: #4a1a1a; color: #fca5a5; }
html[data-bs-theme="dark"] .lvl-warning { background: #3a2f0c; color: #fde68a; }
html[data-bs-theme="dark"] .lvl-notice, html[data-bs-theme="dark"] .lvl-info { background: #1e3a5a; color: #93c5fd; }

.tab-counter { display:inline-block; min-width:18px; padding: 1px 6px; border-radius:99px; font-size: .65rem; font-weight: 600; background: var(--m-surface-2); color: var(--m-text-soft); }
.tab-counter.warn { background: #fee2e2; color: #991b1b; }

.schema-card-wrap .schema-card { transition: all .12s; }
.schema-card-wrap .schema-card:hover .card { border-color: var(--tblr-primary); box-shadow: var(--m-shadow-md); }

.op-pill { display:inline-block; padding: 1px 6px; border-radius:3px; font-size:.65rem; font-weight: 600; margin-right: 3px; }
.op-pill.SELECT { background:#dcfce7; color:#166534; }
.op-pill.INSERT { background:#dbeafe; color:#1e40af; }
.op-pill.UPDATE { background:#fef3c7; color:#92400e; }
.op-pill.DELETE { background:#fee2e2; color:#991b1b; }
</style>

<div class="container-xl">

<!-- ============= TITLE BAR ============= -->
<div class="rv-title-bar">
    <div class="row align-items-center g-3">
        <div class="col">
            <h2 class="page-title mb-0" style="font-size:1.5rem;font-weight:700"><i class="ti ti-binary-tree me-2 text-primary"></i>System Reference Index</h2>
            <div class="small text-secondary mt-1">Self-documenting admin reference. Every PHP error and exception is auto-captured into the Error Log.</div>
        </div>
        <div class="col-auto">
            <form method="POST" style="display:inline">
                <?= csrf_field() ?><input type="hidden" name="action" value="rescan">
                <button class="btn btn-primary btn-sm"><i class="ti ti-refresh me-1"></i>Re-scan codebase</button>
            </form>
        </div>
    </div>
</div>

<!-- ============= HEALTH KPIS ============= -->
<div class="row row-deck row-cards mb-3 g-2">
    <div class="col-md-2 col-6"><div class="health-card">
        <div class="d-flex align-items-center mb-2"><span class="ico" style="background:rgba(32,107,196,.12);color:#206bc4"><i class="ti ti-file-code"></i></span></div>
        <div class="lbl">Total Pages</div><div class="val"><?= $totalPages ?></div>
    </div></div>
    <div class="col-md-2 col-6"><div class="health-card">
        <div class="d-flex align-items-center mb-2"><span class="ico" style="background:rgba(22,163,74,.12);color:#16a34a"><i class="ti ti-stack-2"></i></span></div>
        <div class="lbl">Modules</div><div class="val"><?= $modules ?></div>
    </div></div>
    <div class="col-md-2 col-6"><div class="health-card">
        <div class="d-flex align-items-center mb-2"><span class="ico" style="background:rgba(124,58,237,.12);color:#7c3aed"><i class="ti ti-database"></i></span></div>
        <div class="lbl">Database Tables</div><div class="val"><?= count($dbTables) ?></div>
    </div></div>
    <div class="col-md-2 col-6"><div class="health-card">
        <div class="d-flex align-items-center mb-2"><span class="ico" style="background:rgba(247,103,7,.12);color:#f76707"><i class="ti ti-link"></i></span></div>
        <div class="lbl">Tables Referenced</div><div class="val"><?= $tablesTouched ?></div>
    </div></div>
    <div class="col-md-2 col-6"><div class="health-card" style="border-left: 3px solid <?= $errStats['last_24h']>0 ? '#f59e0b' : '#16a34a' ?>;">
        <div class="d-flex align-items-center mb-2"><span class="ico" style="background:rgba(245,158,11,.12);color:#f59e0b"><i class="ti ti-clock-exclamation"></i></span></div>
        <div class="lbl">Errors (24h)</div><div class="val"><?= number_format($errStats['last_24h']) ?></div>
    </div></div>
    <div class="col-md-2 col-6"><div class="health-card" style="border-left: 3px solid <?= $errStats['open_critical']>0 ? '#dc2626' : '#16a34a' ?>;">
        <div class="d-flex align-items-center mb-2"><span class="ico" style="background:rgba(220,38,38,.12);color:#dc2626"><i class="ti ti-alert-octagon"></i></span></div>
        <div class="lbl">Open Critical</div><div class="val"><?= number_format($errStats['open_critical']) ?></div>
    </div></div>
</div>

<!-- ============= TABS ============= -->
<ul class="nav rv-tabs">
    <?php foreach ([
        ['key'=>'overview',  'label'=>'Overview',          'icon'=>'home'],
        ['key'=>'errors',    'label'=>'Error Log',         'icon'=>'alert-triangle', 'count' => $errStats['open']],
        ['key'=>'pages',     'label'=>'Pages → Tables',    'icon'=>'list'],
        ['key'=>'schema',    'label'=>'Database Schema',   'icon'=>'table'],
        ['key'=>'matrix',    'label'=>'Dependency Matrix', 'icon'=>'arrows-shuffle'],
        ['key'=>'changelog', 'label'=>'Changelog',         'icon'=>'history'],
    ] as $t): ?>
        <li class="nav-item">
            <a class="nav-link <?= $tab===$t['key']?'active':'' ?>" href="?tab=<?= $t['key'] ?>">
                <i class="ti ti-<?= $t['icon'] ?> me-1"></i><?= $t['label'] ?>
                <?php if (!empty($t['count'])): ?><span class="tab-counter <?= $t['count']>0?'warn':'' ?>"><?= $t['count'] ?></span><?php endif; ?>
            </a>
        </li>
    <?php endforeach; ?>
</ul>

<?php if ($tab === 'overview'): ?>

    <?php
    // Recent errors trend (last 14 days)
    $trend = array_fill_keys(array_map(fn($i)=>date('Y-m-d', strtotime("-$i days")), range(13,0,-1)), 0);
    foreach ($pdo->query("SELECT DATE(occurred_at) d, COUNT(*) c FROM system_reference_errors WHERE occurred_at > NOW() - INTERVAL 14 DAY GROUP BY DATE(occurred_at)")->fetchAll() as $r) {
        if (isset($trend[$r['d']])) $trend[$r['d']] = (int)$r['c'];
    }
    // Top offending files
    $offenders = $pdo->query("
        SELECT file, COUNT(*) c,
               SUM(CASE WHEN resolved=0 THEN 1 ELSE 0 END) AS open_n,
               MAX(occurred_at) AS last_seen
        FROM system_reference_errors
        WHERE file IS NOT NULL
        GROUP BY file ORDER BY c DESC LIMIT 6
    ")->fetchAll();
    // Module breakdown
    $moduleStats = [];
    foreach ($pages as $p) {
        $m = $p['module'];
        if (!isset($moduleStats[$m])) $moduleStats[$m] = ['pages'=>0, 'tables'=>[], 'lines'=>0];
        $moduleStats[$m]['pages']++;
        $moduleStats[$m]['lines'] += $p['lines'];
        foreach ($p['tables'] as $t) $moduleStats[$m]['tables'][$t] = true;
    }
    ?>
    <div class="row row-cards">
        <div class="col-md-7">
            <div class="card">
                <div class="card-header py-2"><h3 class="card-title small mb-0"><i class="ti ti-chart-line me-1 text-primary"></i>Errors per day (last 14 days)</h3></div>
                <div class="card-body p-2"><div id="chart_err_trend" style="min-height:220px"></div></div>
            </div>
        </div>
        <div class="col-md-5">
            <div class="card">
                <div class="card-header py-2"><h3 class="card-title small mb-0"><i class="ti ti-flame me-1 text-warning"></i>Top offending files</h3></div>
                <div class="list-group list-group-flush">
                    <?php if (!$offenders): ?>
                        <div class="list-group-item text-center text-secondary py-4 small">No errors recorded yet 🎉</div>
                    <?php else: foreach ($offenders as $o): ?>
                        <div class="list-group-item py-2">
                            <div class="d-flex align-items-center">
                                <div class="flex-grow-1 text-truncate">
                                    <code class="small"><?= e(basename((string)$o['file'])) ?></code>
                                    <div class="small text-secondary"><?= e(fmt_dt($o['last_seen'])) ?></div>
                                </div>
                                <div class="text-end">
                                    <span class="badge bg-red-lt"><?= (int)$o['c'] ?> errors</span>
                                    <?php if ((int)$o['open_n'] > 0): ?>
                                        <div class="small text-danger mt-1"><?= (int)$o['open_n'] ?> open</div>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>
                    <?php endforeach; endif; ?>
                </div>
            </div>
        </div>
        <div class="col-12">
            <div class="card">
                <div class="card-header py-2"><h3 class="card-title small mb-0"><i class="ti ti-layers-intersect me-1 text-info"></i>Modules at a glance</h3></div>
                <div class="card-body">
                    <div class="row row-cards">
                    <?php foreach ($moduleStats as $m => $s): ?>
                        <div class="col-md-2 col-4">
                            <div class="border rounded p-3 text-center" style="background: var(--m-surface-2)">
                                <div style="font-size:.65rem;text-transform:uppercase;letter-spacing:.05em;color:var(--m-text-soft)"><?= e($m) ?></div>
                                <div style="font-size:1.4rem;font-weight:700;color:var(--m-text);margin-top:4px"><?= $s['pages'] ?></div>
                                <div class="small text-secondary"><?= count($s['tables']) ?> tables · <?= number_format($s['lines']) ?> LOC</div>
                            </div>
                        </div>
                    <?php endforeach; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script>
    (function(){
        const dark = document.documentElement.getAttribute('data-bs-theme') === 'dark';
        new ApexCharts(document.querySelector('#chart_err_trend'), {
            chart: { type:'area', height:220, toolbar:{show:false}, fontFamily:'inherit', background:'transparent' },
            theme: { mode: dark ? 'dark' : 'light' },
            series: [{ name:'Errors', data: <?= json_encode(array_values($trend)) ?> }],
            xaxis: { categories: <?= json_encode(array_map(fn($d)=>date('M j', strtotime($d)), array_keys($trend))) ?>,
                     labels: { style: { colors: dark ? '#97a2b6' : '#6c7a89' } } },
            yaxis: { labels: { style: { colors: dark ? '#97a2b6' : '#6c7a89' }, formatter: v => Math.round(v) } },
            stroke: { curve:'smooth', width:2 },
            colors: ['#dc2626'],
            fill: { type:'gradient', gradient:{ shadeIntensity:1, opacityFrom:.4, opacityTo:.05 } },
            grid: { borderColor: dark ? '#1e2a44' : '#eef0f3' },
            tooltip: { y: { formatter: v => v + ' error' + (v===1?'':'s') } },
        }).render();
    })();
    </script>

<?php elseif ($tab === 'errors'):
    // Filter state
    $fLevel  = $_GET['level']  ?? '';
    $fStatus = $_GET['status'] ?? 'open';
    $fSearch = trim((string)($_GET['q']     ?? ''));
    $fFile   = trim((string)($_GET['file']  ?? ''));
    $fSince  = trim((string)($_GET['since'] ?? ''));
    $mode    = $_GET['mode']   ?? 'list';   // list | grouped
    $page    = max(1, (int)($_GET['p'] ?? 1));
    $perPage = 50;

    $where = ['1=1']; $params = [];
    if ($fLevel  !== '') { $where[] = 'error_level = ?'; $params[] = $fLevel; }
    if ($fStatus === 'open')     $where[] = 'resolved = 0';
    if ($fStatus === 'resolved') $where[] = 'resolved = 1';
    if ($fSearch !== '') { $where[] = '(message LIKE ? OR file LIKE ? OR url LIKE ?)';
                          $params[] = "%$fSearch%"; $params[] = "%$fSearch%"; $params[] = "%$fSearch%"; }
    if ($fFile   !== '') { $where[] = 'file LIKE ?'; $params[] = "%$fFile%"; }
    if ($fSince  !== '') { $where[] = 'occurred_at >= ?'; $params[] = $fSince; }
    $whereSql = implode(' AND ', $where);

    if ($mode === 'grouped') {
        $rows = qAll($pdo, "
            SELECT MIN(id) AS id, MAX(occurred_at) AS last_seen, MIN(occurred_at) AS first_seen,
                   error_level, message,
                   MAX(file) AS file, MAX(line) AS line, MAX(url) AS url,
                   MAX(trace) AS trace,
                   SUM(CASE WHEN resolved=0 THEN 1 ELSE 0 END) AS open_n,
                   SUM(CASE WHEN resolved=1 THEN 1 ELSE 0 END) AS resolved_n,
                   COUNT(*) AS total_n
            FROM system_reference_errors
            WHERE $whereSql
            GROUP BY error_level, LEFT(message, 200)
            ORDER BY last_seen DESC
            LIMIT $perPage OFFSET " . (($page-1)*$perPage),
            $params);
        $totalCount = (int) qRow($pdo, "
            SELECT COUNT(*) c FROM (SELECT 1 FROM system_reference_errors WHERE $whereSql GROUP BY error_level, LEFT(message, 200)) t",
            $params)['c'];
    } else {
        $rows = qAll($pdo, "SELECT * FROM system_reference_errors WHERE $whereSql
                            ORDER BY occurred_at DESC LIMIT $perPage OFFSET " . (($page-1)*$perPage),
                    $params);
        $totalCount = (int) qRow($pdo, "SELECT COUNT(*) c FROM system_reference_errors WHERE $whereSql", $params)['c'];
    }
    $totalPages = max(1, (int) ceil($totalCount / $perPage));

    function err_qs(array $extra = []): string {
        global $fLevel, $fStatus, $fSearch, $fFile, $fSince, $mode;
        $p = ['tab'=>'errors', 'level'=>$fLevel, 'status'=>$fStatus, 'q'=>$fSearch, 'file'=>$fFile, 'since'=>$fSince, 'mode'=>$mode];
        foreach ($extra as $k=>$v) $p[$k] = $v;
        $p = array_filter($p, fn($v) => $v !== '' && $v !== null);
        return '?' . http_build_query($p);
    }
?>
    <!-- Filter bar -->
    <form method="GET" class="card mb-3">
        <input type="hidden" name="tab" value="errors">
        <input type="hidden" name="mode" value="<?= e($mode) ?>">
        <div class="card-body py-2">
            <div class="row g-2 align-items-center">
                <div class="col-md-3">
                    <div class="input-icon">
                        <span class="input-icon-addon"><i class="ti ti-search"></i></span>
                        <input type="text" name="q" class="form-control form-control-sm" placeholder="Search message / file / URL..." value="<?= e($fSearch) ?>">
                    </div>
                </div>
                <div class="col-md-2">
                    <select name="level" class="form-select form-select-sm" onchange="this.form.submit()">
                        <option value="">All levels</option>
                        <?php foreach (['critical','error','warning','notice','info','debug'] as $lv): ?>
                            <option value="<?= $lv ?>" <?= $fLevel===$lv?'selected':'' ?>><?= ucfirst($lv) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-2">
                    <select name="status" class="form-select form-select-sm" onchange="this.form.submit()">
                        <option value="all"      <?= $fStatus==='all'?'selected':'' ?>>All status</option>
                        <option value="open"     <?= $fStatus==='open'?'selected':'' ?>>Open only</option>
                        <option value="resolved" <?= $fStatus==='resolved'?'selected':'' ?>>Resolved only</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <input type="date" name="since" class="form-control form-control-sm" value="<?= e($fSince) ?>" onchange="this.form.submit()">
                </div>
                <div class="col-md-2">
                    <input type="text" name="file" class="form-control form-control-sm" placeholder="File name..." value="<?= e($fFile) ?>">
                </div>
                <div class="col-md-1">
                    <button class="btn btn-sm btn-primary w-100"><i class="ti ti-filter"></i></button>
                </div>
            </div>
        </div>
    </form>

    <!-- Toolbar -->
    <div class="d-flex align-items-center mb-2 flex-wrap gap-2">
        <div class="btn-group btn-group-sm" role="group">
            <a href="<?= err_qs(['mode'=>'list']) ?>"    class="btn btn<?= $mode==='list'   ?'-primary':'-outline-secondary' ?>"><i class="ti ti-list me-1"></i>Timeline</a>
            <a href="<?= err_qs(['mode'=>'grouped']) ?>" class="btn btn<?= $mode==='grouped'?'-primary':'-outline-secondary' ?>"><i class="ti ti-stack-2 me-1"></i>Group by message</a>
        </div>
        <div class="text-secondary small ms-2">
            <?= number_format($totalCount) ?> match<?= $totalCount===1?'':'es' ?> ·
            <?= number_format($errStats['open']) ?> open · <?= number_format($errStats['resolved']) ?> resolved
        </div>
        <div class="ms-auto d-flex gap-2 align-items-center">
            <label class="form-check form-switch mb-0 small text-secondary">
                <input type="checkbox" class="form-check-input" id="auto_refresh">
                <span>Auto-refresh 30s</span>
            </label>
            <a href="?tab=errors_csv" class="btn btn-sm btn-outline-success"><i class="ti ti-download me-1"></i>Export CSV</a>
            <form method="POST" style="display:inline">
                <?= csrf_field() ?>
                <input type="hidden" name="action" value="resolve_all_filtered">
                <input type="hidden" name="filter_level"  value="<?= e($fLevel) ?>">
                <input type="hidden" name="filter_status" value="<?= e($fStatus) ?>">
                <input type="hidden" name="filter_search" value="<?= e($fSearch) ?>">
                <button class="btn btn-sm btn-outline-primary" onclick="return confirm('Mark all <?= number_format($totalCount) ?> matching errors as resolved?')"><i class="ti ti-check-all me-1"></i>Resolve filtered</button>
            </form>
            <form method="POST" style="display:inline">
                <?= csrf_field() ?><input type="hidden" name="action" value="clear_errors">
                <button class="btn btn-sm btn-outline-danger" onclick="return confirm('Permanently delete ALL errors?')"><i class="ti ti-trash me-1"></i>Clear all</button>
            </form>
        </div>
    </div>

    <!-- Error list -->
    <div class="card">
        <?php if (!$rows): ?>
            <div class="card-body text-center py-5 text-secondary">
                <div style="font-size:3rem">🎉</div>
                <h3 class="mt-3">No errors match the current filters</h3>
                <p>System is healthy. Try clearing the filters above to see resolved entries.</p>
            </div>
        <?php else: ?>
            <div class="list-group list-group-flush">
            <?php foreach ($rows as $r):
                $lv = $r['error_level'];
                $isResolved = ($mode === 'grouped') ? ((int)$r['open_n'] === 0) : (int)$r['resolved'] === 1;
                $shortMsg = mb_substr($r['message'], 0, 240);
            ?>
                <div class="list-group-item err-row lvl-<?= $lv ?> <?= $isResolved ? 'resolved' : '' ?>" id="err-<?= (int)$r['id'] ?>">
                    <div class="d-flex align-items-start gap-3">
                        <div class="flex-grow-1" style="min-width:0">
                            <div class="d-flex align-items-center gap-2 flex-wrap">
                                <span class="lvl-pill lvl-<?= $lv ?>"><?= e($lv) ?></span>
                                <?php if ($mode === 'grouped'): ?>
                                    <span class="grp-count <?= (int)$r['total_n']>10?'high':'' ?>" title="Total occurrences"><?= (int)$r['total_n'] ?>×</span>
                                <?php endif; ?>
                                <a href="#" class="msg-link fw-medium" onclick="toggleTrace(<?= (int)$r['id'] ?>); return false;" style="font-size:.92rem">
                                    <?= e($shortMsg) ?><?= mb_strlen($r['message']) > 240 ? '…' : '' ?>
                                </a>
                                <?php if ($isResolved): ?><span class="badge bg-success-lt ms-auto">✓ Resolved</span><?php endif; ?>
                            </div>
                            <div class="file-meta mt-1">
                                <?php if ($r['file']): ?>
                                    <i class="ti ti-file-code"></i> <?= e($r['file']) ?>:<?= (int)$r['line'] ?>
                                <?php endif; ?>
                                <?php if (!empty($r['url'])): ?>
                                    &nbsp;·&nbsp;<i class="ti ti-link"></i> <?= e(mb_substr($r['url'], 0, 80)) ?>
                                <?php endif; ?>
                                <?php if ($mode === 'grouped'): ?>
                                    &nbsp;·&nbsp;<i class="ti ti-clock"></i> first <?= e(fmt_dt($r['first_seen'])) ?> · last <?= e(fmt_dt($r['last_seen'])) ?>
                                <?php else: ?>
                                    &nbsp;·&nbsp;<i class="ti ti-clock"></i> <?= e(fmt_dt($r['occurred_at'])) ?>
                                    <?php if (!empty($r['ip'])): ?>&nbsp;·&nbsp;<i class="ti ti-server"></i> <?= e($r['ip']) ?><?php endif; ?>
                                    <?php if (!empty($r['user_id'])): ?>&nbsp;·&nbsp;<i class="ti ti-user"></i> #<?= (int)$r['user_id'] ?><?php endif; ?>
                                <?php endif; ?>
                            </div>
                        </div>
                        <?php if ($mode === 'list'): ?>
                            <form method="POST" class="d-inline">
                                <?= csrf_field() ?>
                                <input type="hidden" name="action" value="resolve_one">
                                <input type="hidden" name="id" value="<?= (int)$r['id'] ?>">
                                <input type="hidden" name="return" value="<?= e(http_build_query(['level'=>$fLevel,'status'=>$fStatus,'q'=>$fSearch,'mode'=>$mode,'p'=>$page])) ?>">
                                <button type="submit" class="btn btn-sm <?= $isResolved ? 'btn-outline-secondary' : 'btn-outline-success' ?>" title="<?= $isResolved ? 'Re-open this error' : 'Mark as resolved' ?>">
                                    <i class="ti ti-<?= $isResolved ? 'arrow-back-up' : 'check' ?>"></i>
                                </button>
                            </form>
                        <?php endif; ?>
                    </div>
                    <?php if (!empty($r['trace']) || mb_strlen($r['message']) > 240): ?>
                        <div class="trace-panel" id="trace-<?= (int)$r['id'] ?>">
<?php if (mb_strlen($r['message']) > 240): ?><strong>Message:</strong>
<?= e($r['message']) ?>

<?php endif; ?>
<?php if (!empty($r['trace'])): ?><strong>Stack trace:</strong>
<?= e($r['trace']) ?>
<?php endif; ?>
                        </div>
                    <?php endif; ?>
                </div>
            <?php endforeach; ?>
            </div>
        <?php endif; ?>
    </div>

    <?php if ($totalPages > 1): ?>
    <div class="d-flex justify-content-between align-items-center mt-3">
        <div class="small text-secondary">Page <?= $page ?> of <?= $totalPages ?></div>
        <div class="btn-group btn-group-sm">
            <a href="<?= err_qs(['p'=>max(1,$page-1)]) ?>" class="btn btn-outline-secondary <?= $page<=1?'disabled':'' ?>"><i class="ti ti-chevron-left"></i> Prev</a>
            <a href="<?= err_qs(['p'=>min($totalPages,$page+1)]) ?>" class="btn btn-outline-secondary <?= $page>=$totalPages?'disabled':'' ?>">Next <i class="ti ti-chevron-right"></i></a>
        </div>
    </div>
    <?php endif; ?>

    <script>
    function toggleTrace(id) {
        const row = document.getElementById('err-' + id);
        if (row) row.classList.toggle('expanded');
    }
    document.getElementById('auto_refresh')?.addEventListener('change', function(){
        if (this.checked) {
            window._errTimer = setInterval(() => { window.location.reload(); }, 30000);
        } else if (window._errTimer) {
            clearInterval(window._errTimer); window._errTimer = null;
        }
    });
    </script>

<?php elseif ($tab === 'pages'): ?>
    <div class="card"><div class="card-header">
        <h3 class="card-title">Every PHP page in the application</h3>
        <div class="card-actions">
            <input type="text" class="form-control form-control-sm" id="srch_pages" placeholder="Filter by name, module, table..." style="max-width:300px">
        </div>
    </div>
    <div class="table-responsive"><table id="t_pages" class="table table-vcenter card-table table-sm">
        <thead><tr>
            <th>Module</th><th>Page</th><th>Description</th>
            <th>Tables touched <small class="text-secondary">(hover)</small></th>
            <th>Operations</th><th>Roles</th><th class="text-end">Lines</th>
        </tr></thead><tbody>
        <?php foreach ($pages as $p): ?>
            <tr>
                <td><span class="badge bg-secondary-lt"><?= e($p['module']) ?></span></td>
                <td><code><?= e($p['rel']) ?></code></td>
                <td class="small text-secondary text-truncate" style="max-width:280px"><?= e($p['desc'] ?: '--') ?></td>
                <td>
                    <?php foreach ($p['tables'] as $t):
                        $cols = $colMap[$t] ?? [];
                        $tooltip = $cols ? implode(' | ', array_map(fn($c) => $c['column_name'].':'.$c['data_type'], array_slice($cols, 0, 12)))
                                         : '(no metadata)';
                        if (count($cols) > 12) $tooltip .= ' ... +' . (count($cols)-12) . ' more';
                    ?>
                        <span class="badge bg-blue-lt me-1" data-bs-toggle="tooltip" data-bs-html="true" title="<?= e($tooltip) ?>"><?= e($t) ?></span>
                    <?php endforeach; ?>
                </td>
                <td class="small">
                    <?php foreach ($p['ops'] as $op): ?>
                        <span class="op-pill <?= $op ?>"><?= e($op) ?></span>
                    <?php endforeach; ?>
                </td>
                <td class="small"><?= implode(', ', array_map('e', $p['roles'])) ?: '--' ?></td>
                <td class="text-end small text-secondary"><?= (int)$p['lines'] ?></td>
            </tr>
        <?php endforeach; ?>
        </tbody></table></div></div>
    <script>
    document.querySelectorAll('[data-bs-toggle="tooltip"]').forEach(el => new bootstrap.Tooltip(el));
    document.getElementById('srch_pages').addEventListener('input', function(){
        const q = this.value.toLowerCase();
        document.querySelectorAll('#t_pages tbody tr').forEach(tr => {
            tr.style.display = tr.textContent.toLowerCase().includes(q) ? '' : 'none';
        });
    });
    </script>

<?php elseif ($tab === 'schema'): ?>
    <div class="d-flex justify-content-between align-items-center mb-2">
        <input type="text" id="srch_schema" class="form-control form-control-sm" placeholder="Search tables or columns..." style="max-width:340px">
        <span class="small text-secondary"><?= count($dbTables) ?> tables · <?= array_sum(array_map('count', $colMap)) ?> columns</span>
    </div>
    <div class="row row-cards schema-card-wrap" id="schema_grid">
    <?php foreach ($dbTables as $t):
        $name = $t['table_name']; $cols = $colMap[$name] ?? []; $rowCount = (int)$t['table_rows']; ?>
        <div class="col-md-6 col-lg-4 schema-card" data-name="<?= e($name) ?>">
            <div class="card h-100">
                <div class="card-header py-2 d-flex justify-content-between align-items-center">
                    <strong style="font-family:monospace; font-size:13px"><?= e($name) ?></strong>
                    <span class="badge bg-secondary-lt"><?= number_format($rowCount) ?> rows · <?= count($cols) ?> cols</span>
                </div>
                <div class="card-body p-0" style="max-height:240px;overflow:auto">
                    <table class="table table-sm card-table mb-0" style="font-size:12px">
                        <tbody>
                        <?php foreach ($cols as $c):
                            $kcls = ['PRI'=>'primary','UNI'=>'success','MUL'=>'info'][$c['column_key']] ?? ''; ?>
                            <tr class="schema-col" data-name="<?= e($c['column_name']) ?>">
                                <td style="width:60%"><code><?= e($c['column_name']) ?></code>
                                    <?php if ($c['is_nullable']==='NO' && !$c['column_key']): ?><sup class="text-danger" title="NOT NULL">*</sup><?php endif; ?>
                                </td>
                                <td><span class="badge bg-secondary-lt"><?= e($c['data_type']) ?></span></td>
                                <td class="text-end" style="width:50px">
                                    <?php if ($c['column_key']): ?>
                                        <span class="badge bg-<?= $kcls ?>-lt" title="<?= $c['column_key'] ?>"><?= e(substr($c['column_key'],0,3)) ?></span>
                                    <?php endif; ?>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    <?php endforeach; ?>
    </div>
    <script>
    document.getElementById('srch_schema').addEventListener('input', function(){
        const q = this.value.toLowerCase();
        document.querySelectorAll('#schema_grid .schema-card').forEach(card => {
            const tname = card.dataset.name.toLowerCase();
            const cols = Array.from(card.querySelectorAll('.schema-col')).map(r => r.dataset.name.toLowerCase());
            const matchTable = tname.includes(q);
            const matchCol = cols.some(n => n.includes(q));
            card.style.display = (matchTable || matchCol) ? '' : 'none';
            card.querySelectorAll('.schema-col').forEach(r => {
                r.style.background = (q && r.dataset.name.toLowerCase().includes(q)) ? 'rgba(254,240,138,.5)' : '';
            });
        });
    });
    </script>

<?php elseif ($tab === 'matrix'):
    $byTable = [];
    foreach ($pages as $p) {
        foreach ($p['tables'] as $t) {
            $byTable[$t][] = ['rel' => $p['rel'], 'module' => $p['module'], 'ops' => $p['ops']];
        }
    }
    ksort($byTable);
?>
    <div class="card"><div class="card-header">
        <h3 class="card-title">Reverse map — <?= count($byTable) ?> tables ↔ pages</h3>
        <div class="card-actions"><input type="text" id="srch_mx" class="form-control form-control-sm" placeholder="Filter..." style="max-width:280px"></div>
    </div>
    <div class="table-responsive"><table id="t_mx" class="table table-vcenter card-table table-sm">
        <thead><tr><th>Table</th><th>Pages that touch it</th><th>Operations (combined)</th></tr></thead>
        <tbody>
        <?php foreach ($byTable as $t => $ps):
            $allOps = array_unique(array_merge(...array_column($ps, 'ops'))); ?>
            <tr>
                <td><code><?= e($t) ?></code> <span class="badge bg-secondary-lt ms-1"><?= count($ps) ?></span></td>
                <td>
                    <?php foreach ($ps as $p): ?>
                        <span class="badge bg-blue-lt me-1" title="<?= e($p['module']) ?>"><?= e($p['rel']) ?></span>
                    <?php endforeach; ?>
                </td>
                <td>
                    <?php foreach ($allOps as $op): ?>
                        <span class="op-pill <?= $op ?>"><?= e($op) ?></span>
                    <?php endforeach; ?>
                </td>
            </tr>
        <?php endforeach; ?>
        </tbody></table></div></div>
    <script>
    document.getElementById('srch_mx').addEventListener('input', function(){
        const q = this.value.toLowerCase();
        document.querySelectorAll('#t_mx tbody tr').forEach(tr => {
            tr.style.display = tr.textContent.toLowerCase().includes(q) ? '' : 'none';
        });
    });
    </script>

<?php elseif ($tab === 'changelog'):
    $log = $pdo->query("SELECT c.*, u.name AS user_name FROM system_reference_changelog c
                        LEFT JOIN users u ON u.id = c.changed_by
                        ORDER BY c.changed_at DESC")->fetchAll();
?>
    <div class="card"><div class="table-responsive"><table class="table card-table">
        <thead><tr><th>When</th><th>Version</th><th>Type</th><th>Title</th><th>By</th><th>Files / Tables</th></tr></thead>
        <tbody>
        <?php if (!$log): ?>
            <tr><td colspan="6" class="text-center text-secondary py-4">No changelog entries.</td></tr>
        <?php else: foreach ($log as $c): ?>
            <tr>
                <td class="small text-secondary"><?= e($c['changed_at']) ?></td>
                <td><code><?= e($c['version']) ?></code></td>
                <td>
                    <?php $cls=['FIX'=>'warning','FEATURE'=>'success','UPDATE'=>'info','BREAKING'=>'danger'][$c['change_type']] ?? 'secondary'; ?>
                    <span class="badge bg-<?= $cls ?>-lt"><?= e($c['change_type']) ?></span>
                </td>
                <td><strong><?= e($c['title']) ?></strong>
                    <?php if ($c['description']): ?><div class="small text-secondary"><?= e($c['description']) ?></div><?php endif; ?>
                </td>
                <td class="small"><?= e($c['user_name'] ?? '--') ?></td>
                <td class="small text-secondary">
                    <?php
                    foreach (json_decode($c['files_affected'] ?: '[]', true) ?: [] as $f) echo '<code class="me-1">' . e($f) . '</code>';
                    echo '<br>';
                    foreach (json_decode($c['tables_affected'] ?: '[]', true) ?: [] as $t) echo '<span class="badge bg-blue-lt me-1">' . e($t) . '</span>';
                    ?>
                </td>
            </tr>
        <?php endforeach; endif; ?>
        </tbody></table></div></div>

<?php endif; ?>

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