<?php
/**
 * Reports browser. Modern card / table dual view with freshness, search, filters.
 */
require_once __DIR__ . '/../includes/config.php';
$user = require_login('internal');

$pdo  = db();
$id   = (int) ($_GET['id'] ?? 0);
$view = $_GET['view'] ?? 'list';
if ($view === 'detail' && $id) require_report_access($id);   // gate before any output

$pageTitle = 'Reports';
require __DIR__ . '/../includes/header.php';

if ($view === 'detail' && $id) {
    $stmt = $pdo->prepare("
        SELECT r.*, p.name AS process_name, p.code AS process_code,
               c.name AS client_name, c.code AS client_code,
               v.name AS vertical_name
        FROM reports r
        JOIN processes p ON p.id = r.process_id
        JOIN clients   c ON c.id = p.client_id
        JOIN verticals v ON v.id = c.vertical_id
        WHERE r.id = ? AND r.deleted_at IS NULL
    ");
    $stmt->execute([$id]);
    $report = $stmt->fetch();
    if (!$report) { flash('error','Report not found.'); redirect('/admin/reports.php'); }

    $cols = $pdo->prepare('SELECT * FROM report_columns WHERE report_id = ? ORDER BY col_order');
    $cols->execute([$id]);
    $columns = $cols->fetchAll();

    $outs = $pdo->prepare('SELECT * FROM report_outputs WHERE report_id = ? ORDER BY display_order, id');
    $outs->execute([$id]);
    $outputs = $outs->fetchAll();
?>
<div class="page-header"><div class="container-xl">
    <div class="row align-items-center">
        <div class="col">
            <a href="<?= e(url('/admin/reports.php')) ?>" class="text-secondary text-decoration-none"><i class="ti ti-chevron-left"></i> All reports</a>
            <h2 class="page-title mt-1"><?= e($report['name']) ?> <code class="ms-2 text-secondary fw-normal" style="font-size:.65em"><?= e($report['code']) ?></code></h2>
            <div class="text-secondary">
                <i class="ti ti-building me-1"></i><?= e($report['vertical_name']) ?>
                <span class="mx-1 opacity-50">›</span>
                <?= e($report['client_name']) ?>
                <span class="mx-1 opacity-50">›</span>
                <?= e($report['process_name']) ?>
            </div>
        </div>
        <div class="col-auto">
            <a href="<?= e(url(($report['handler']==='collection_dashboard' ? '/admin/collection.php?id=' : '/admin/report-view.php?id=').$id)) ?>" class="btn btn-primary btn-lg">
                <i class="ti ti-chart-pie-2 me-2"></i>Open Dashboard
            </a>
            <a href="<?= e(url('/admin/upload.php?report_id='.$id)) ?>" class="btn btn-outline-primary btn-lg">
                <i class="ti ti-upload me-2"></i>Upload data
            </a>
        </div>
    </div>
</div></div>
<div class="page-body"><div class="container-xl">
    <div class="row row-deck row-cards">
        <div class="col-md-6">
            <div class="card"><div class="card-header"><h3 class="card-title">Definition</h3></div>
                <div class="card-body">
                    <div class="datagrid">
                        <div class="datagrid-item"><div class="datagrid-title">Type</div><div class="datagrid-content"><?= e(ucfirst($report['report_type'])) ?></div></div>
                        <div class="datagrid-item"><div class="datagrid-title">Upload mode</div><div class="datagrid-content"><span class="badge bg-blue-lt"><?= e($report['upload_strategy']) ?></span></div></div>
                        <div class="datagrid-item"><div class="datagrid-title">Period column</div><div class="datagrid-content"><?= e($report['period_column'] ?? '--') ?></div></div>
                        <div class="datagrid-item"><div class="datagrid-title">Status</div><div class="datagrid-content"><span class="badge bg-success-lt"><?= e($report['status']) ?></span></div></div>
                        <div class="datagrid-item"><div class="datagrid-title">Unique key</div><div class="datagrid-content"><code><?= e($report['unique_key_columns'] ?? '[]') ?></code></div></div>
                    </div>
                    <?php if ($report['description']): ?>
                        <p class="mt-3 text-secondary"><?= e($report['description']) ?></p>
                    <?php endif; ?>
                </div>
            </div>
        </div>
        <div class="col-md-6">
            <div class="card"><div class="card-header"><h3 class="card-title">Outputs (<?= count($outputs) ?>)</h3></div>
                <table class="table table-vcenter card-table">
                    <thead><tr><th>Name</th><th>Type</th></tr></thead>
                    <tbody>
                        <?php foreach ($outputs as $o): ?>
                            <tr>
                                <td><strong><?= e($o['output_name']) ?></strong></td>
                                <td><span class="badge bg-blue-lt"><?= e($o['output_type']) ?></span></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
        <div class="col-12">
            <div class="card"><div class="card-header"><h3 class="card-title">Input columns (<?= count($columns) ?>)</h3></div>
                <div class="table-responsive">
                    <table class="table table-vcenter card-table">
                        <thead><tr>
                            <th>#</th><th>Field</th><th>Label</th><th>Type</th><th>Flags</th>
                        </tr></thead>
                        <tbody>
                            <?php foreach ($columns as $c): ?>
                                <tr>
                                    <td><?= (int) $c['col_order'] ?></td>
                                    <td><code><?= e($c['col_name']) ?></code></td>
                                    <td><?= e($c['col_label']) ?></td>
                                    <td><span class="badge bg-secondary-lt"><?= e($c['data_type']) ?></span></td>
                                    <td>
                                        <?php
                                        $flags = [];
                                        if ($c['is_required'])     $flags[] = '<span class="badge bg-red-lt">required</span>';
                                        if ($c['is_period_col'])   $flags[] = '<span class="badge bg-yellow-lt">period</span>';
                                        if ($c['is_key_col'])      $flags[] = '<span class="badge bg-purple-lt">key</span>';
                                        if ($c['is_groupable'])    $flags[] = '<span class="badge bg-blue-lt">groupable</span>';
                                        if ($c['is_aggregatable']) $flags[] = '<span class="badge bg-green-lt">' . e($c['default_agg']) . '</span>';
                                        echo implode(' ', $flags);
                                        ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div></div>
<?php
} else {
    $q = trim((string) input('q'));
    $vFilter = (int) input('vertical_id');
    $cFilter = (int) input('client_id');
    $sFilter = trim((string) input('status'));
    $modeView = $_GET['mode'] ?? 'cards';

    $params = [];
    $where = 'WHERE r.deleted_at IS NULL';
    if ($q !== '') { $where .= ' AND (r.name LIKE ? OR r.code LIKE ?)'; $params[]="%$q%"; $params[]="%$q%"; }
    if ($vFilter) { $where .= ' AND v.id = ?'; $params[] = $vFilter; }
    if ($cFilter) { $where .= ' AND c.id = ?'; $params[] = $cFilter; }
    if ($sFilter !== '') { $where .= ' AND r.status = ?'; $params[] = $sFilter; }
    $where .= report_scope_sql('r.id');   // only reports SysAdmin granted this user (SysAdmin = all)

    $stmt = $pdo->prepare("
        SELECT r.id, r.name, r.code, r.handler, r.report_type, r.upload_strategy, r.status, r.updated_at,
               p.name AS process_name, c.name AS client_name, v.name AS vertical_name,
               (SELECT COUNT(*) FROM report_columns rc WHERE rc.report_id = r.id) AS col_count,
               (SELECT COUNT(*) FROM report_outputs ro WHERE ro.report_id = r.id) AS out_count,
               (SELECT COUNT(*) FROM report_uploads ru WHERE ru.report_id = r.id) AS upload_count,
               (SELECT MAX(created_at) FROM report_uploads ru WHERE ru.report_id = r.id AND ru.status='completed') AS last_upload,
               (SELECT COUNT(*) FROM report_data rd WHERE rd.report_id = r.id AND rd.is_active=1) AS row_count
        FROM reports r
        JOIN processes p ON p.id = r.process_id
        JOIN clients   c ON c.id = p.client_id
        JOIN verticals v ON v.id = c.vertical_id
        $where
        ORDER BY v.name, c.name, p.name, r.name
        LIMIT 500");
    $stmt->execute($params);
    $rows = $stmt->fetchAll();

    /* Override stats for every Collection Dashboard — its data lives in collection_raw / collection_uploads
       (multi-tenant: each Collection report has its own report_id-scoped rows). */
    $collDashIds = [];
    foreach ($rows as $r) { if ($r['handler'] === 'collection_dashboard') $collDashIds[] = (int)$r['id']; }
    if ($collDashIds) {
        try {
            $cColCount = (int) $pdo->query("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='collection_raw'")->fetchColumn();
            $inList = implode(',', $collDashIds);
            /* Per-report live row count = valid_rows of the latest COMPLETED upload,
               read from the small collection_uploads table.  Avoids a
               GROUP BY COUNT(*) over millions of collection_raw rows that would
               scan every client's data on each Reports-browser load. */
            $stRows = $pdo->prepare(
                "SELECT u.report_id, u.valid_rows c
                   FROM collection_uploads u
                   JOIN (SELECT report_id, MAX(id) max_id
                           FROM collection_uploads
                          WHERE kind='collection' AND status='completed' AND report_id IN ($inList)
                          GROUP BY report_id) m
                     ON m.report_id = u.report_id AND m.max_id = u.id");
            $stUploads = $pdo->prepare("SELECT report_id, COUNT(*) c, MAX(completed_at) last_at FROM collection_uploads WHERE kind='collection' AND report_id IN ($inList) GROUP BY report_id");
            $stRows->execute();    $rowCountByRep   = $stRows->fetchAll(PDO::FETCH_KEY_PAIR);
            $stUploads->execute(); $uploadByRep     = [];
            foreach ($stUploads->fetchAll(PDO::FETCH_ASSOC) as $u) {
                $uploadByRep[(int)$u['report_id']] = ['count'=>(int)$u['c'], 'last'=>$u['last_at']];
            }
            foreach ($rows as &$r) {
                if ($r['handler'] === 'collection_dashboard') {
                    $rid = (int)$r['id'];
                    $r['row_count']    = (int) ($rowCountByRep[$rid] ?? 0);
                    $r['upload_count'] = $uploadByRep[$rid]['count'] ?? 0;
                    $r['last_upload']  = $uploadByRep[$rid]['last'] ?? null;
                    $r['col_count']    = $cColCount;
                    $r['out_count']    = 10; // Dashboard / Summary / Pivot / Unique Patients / Aging / Carriers / CPT / Providers / Detail / History
                }
            }
            unset($r);
        } catch (Throwable $e) { /* collection tables not yet created — keep zeros */ }
    }

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

    /* Quick stats */
    $totalActive = 0; $freshCount = 0; $idleCount = 0; $totalRows = 0;
    foreach ($rows as $r) {
        if ($r['status'] === 'active') $totalActive++;
        if ($r['last_upload'] && strtotime($r['last_upload']) > strtotime('-7 days')) $freshCount++;
        if (!$r['last_upload'] || strtotime($r['last_upload']) < strtotime('-30 days')) $idleCount++;
        $totalRows += (int) $r['row_count'];
    }
?>
<style>
.rep-card {
    background: var(--m-surface); border: 1px solid var(--m-border); border-radius: 12px;
    padding: 16px; cursor: pointer; transition: all .15s;
    position: relative; overflow: hidden; height: 100%; display: flex; flex-direction: column;
}
.rep-card:hover { border-color: var(--tblr-primary); transform: translateY(-3px); box-shadow: var(--m-shadow-md); }
.rep-card .rep-ico {
    width: 38px; height: 38px; border-radius: 10px; display: inline-flex; align-items: center; justify-content: center;
    background: rgba(32,107,196,.12); color: var(--tblr-primary); margin-bottom: 10px; font-size: 1.2rem;
}
.rep-card h3 { font-size: .98rem; font-weight: 600; margin: 0 0 4px; line-height: 1.3; color: var(--m-text); }
.rep-card .sub { font-size: .8rem; color: var(--m-text-soft); margin-bottom: 4px; }
.rep-card .scope { font-size: .72rem; color: var(--m-text-soft); margin-bottom: 12px; }
.rep-card .meta { margin-top: auto; padding-top: 10px; border-top: 1px solid var(--m-border); display: grid; grid-template-columns: 1fr 1fr; gap: 6px 12px; font-size: .78rem; }
.rep-card .meta .label { color: var(--m-text-soft); font-size: .65rem; text-transform: uppercase; letter-spacing: .04em; }
.rep-card .meta .value { font-weight: 600; color: var(--m-text); font-variant-numeric: tabular-nums; }
.rep-card .badge-fresh, .rep-card .badge-idle, .rep-card .badge-draft {
    position: absolute; top: 12px; right: 12px;
    font-size: .68rem; padding: 2px 8px; border-radius: 99px;
    display:inline-flex; align-items:center; gap:4px;
}
.rep-card .badge-fresh { background:#dcfce7; color:#166534; }
.rep-card .badge-fresh::before { content:''; width:6px; height:6px; background:#16a34a; border-radius:50%; box-shadow: 0 0 0 3px rgba(22,163,74,.2); }
.rep-card .badge-idle  { background:#fef3c7; color:#92400e; }
.rep-card .badge-draft { background:#e0e7ff; color:#3730a3; }
html[data-bs-theme="dark"] .rep-card .badge-fresh { background:#0c4a32; color:#86efac; }
html[data-bs-theme="dark"] .rep-card .badge-idle  { background:#3a2f0c; color:#fde68a; }

.rb-stat-strip .stat {
    background: var(--m-surface); border: 1px solid var(--m-border); border-radius: 10px; padding: 12px 14px;
}
.rb-stat-strip .stat .lbl { font-size:.65rem; text-transform:uppercase; color: var(--m-text-soft); letter-spacing:.04em; }
.rb-stat-strip .stat .val { font-size: 1.4rem; font-weight: 700; color: var(--m-text); line-height: 1.1; margin-top: 2px; font-variant-numeric: tabular-nums; }
</style>

<div class="page-header"><div class="container-xl">
    <div class="row align-items-center">
        <div class="col">
            <h2 class="page-title">Reports browser</h2>
            <div class="text-secondary">Each report has its own input schema and output definitions.</div>
        </div>
        <div class="col-auto">
            <div class="btn-group btn-group-sm me-2" role="group">
                <a href="?<?= http_build_query(array_merge($_GET, ['mode'=>'cards'])) ?>" class="btn btn<?= $modeView === 'cards' ? '-primary' : '-outline-secondary' ?>"><i class="ti ti-layout-grid"></i></a>
                <a href="?<?= http_build_query(array_merge($_GET, ['mode'=>'table'])) ?>" class="btn btn<?= $modeView === 'table' ? '-primary' : '-outline-secondary' ?>"><i class="ti ti-list"></i></a>
            </div>
            <?php if (is_sysadmin()): ?>
                <a href="<?= e(url('/admin/report-builder.php')) ?>" class="btn btn-primary"><i class="ti ti-template me-1"></i>Builder</a>
            <?php endif; ?>
        </div>
    </div>
</div></div>

<div class="page-body"><div class="container-xl">

    <!-- Quick stats -->
    <div class="row rb-stat-strip mb-3 g-2">
        <div class="col-md-3 col-6"><div class="stat"><div class="lbl">Total reports</div><div class="val"><?= count($rows) ?></div></div></div>
        <div class="col-md-3 col-6"><div class="stat"><div class="lbl">Active</div><div class="val text-success"><?= $totalActive ?></div></div></div>
        <div class="col-md-3 col-6"><div class="stat"><div class="lbl">Fresh (7d)</div><div class="val text-primary"><?= $freshCount ?></div></div></div>
        <div class="col-md-3 col-6"><div class="stat"><div class="lbl">Idle (30d+)</div><div class="val text-warning"><?= $idleCount ?></div></div></div>
    </div>

    <!-- Filter bar -->
    <div class="card mb-3">
        <div class="card-body py-2">
            <form method="GET" class="row g-2 align-items-center">
                <input type="hidden" name="mode" value="<?= e($modeView) ?>">
                <div class="col-md-4">
                    <div class="input-icon">
                        <span class="input-icon-addon"><i class="ti ti-search"></i></span>
                        <input type="text" name="q" class="form-control" placeholder="Search by name or code…" value="<?= e($q) ?>">
                    </div>
                </div>
                <div class="col-md-2">
                    <select name="vertical_id" class="form-select" onchange="this.form.submit()">
                        <option value="">All verticals</option>
                        <?php foreach ($verticals as $v): ?>
                            <option value="<?= $v['id'] ?>" <?= $vFilter == $v['id'] ? 'selected' : '' ?>><?= e($v['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <select name="client_id" class="form-select" onchange="this.form.submit()">
                        <option value="">All clients</option>
                        <?php foreach ($clients as $c): ?>
                            <option value="<?= $c['id'] ?>" <?= $cFilter == $c['id'] ? 'selected' : '' ?>><?= e($c['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-2">
                    <select name="status" class="form-select" onchange="this.form.submit()">
                        <option value="">Any status</option>
                        <?php foreach (['active','draft','inactive'] as $s): ?>
                            <option value="<?= $s ?>" <?= $sFilter === $s ? 'selected' : '' ?>><?= ucfirst($s) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-1">
                    <button class="btn btn-primary w-100"><i class="ti ti-search"></i></button>
                </div>
            </form>
        </div>
    </div>

    <?php if (!$rows): ?>
        <div class="card text-center p-5">
            <div style="font-size:3rem;opacity:.3">📊</div>
            <h3 class="mt-3">No reports found</h3>
            <p class="text-secondary">Try changing your filters or <a href="<?= e(url('/admin/report-builder.php')) ?>">build a new report</a>.</p>
        </div>
    <?php elseif ($modeView === 'cards'): ?>
        <div class="row row-cards">
            <?php foreach ($rows as $r):
                $isFresh = $r['last_upload'] && strtotime($r['last_upload']) > strtotime('-7 days');
                $isIdle  = !$r['last_upload'] || strtotime($r['last_upload']) < strtotime('-30 days');
                $statusClass = ['active'=>'success','draft'=>'warning','inactive'=>'secondary'][$r['status']] ?? 'secondary';
            ?>
                <div class="col-md-6 col-lg-4">
                    <a href="<?= e(url(($r['handler'] === 'collection_dashboard' ? '/admin/collection.php?id=' : '/admin/report-view.php?id=') . $r['id'])) ?>" class="text-decoration-none">
                        <div class="rep-card">
                            <?php if ($r['status'] === 'draft'): ?>
                                <span class="badge-draft">DRAFT</span>
                            <?php elseif ($isFresh): ?>
                                <span class="badge-fresh">FRESH</span>
                            <?php elseif ($isIdle && $r['last_upload']): ?>
                                <span class="badge-idle">IDLE</span>
                            <?php endif; ?>
                            <div class="rep-ico"><i class="ti ti-chart-pie-2"></i></div>
                            <h3><?= e($r['name']) ?></h3>
                            <div class="sub"><code style="font-size:.78em"><?= e($r['code']) ?></code></div>
                            <div class="scope"><i class="ti ti-building me-1"></i><?= e($r['vertical_name']) ?> › <?= e($r['client_name']) ?></div>
                            <div class="meta">
                                <div><div class="label">Rows</div><div class="value"><?= number_format((int)$r['row_count']) ?></div></div>
                                <div><div class="label">Uploads</div><div class="value"><?= (int)$r['upload_count'] ?></div></div>
                                <div><div class="label">Columns</div><div class="value"><?= (int)$r['col_count'] ?></div></div>
                                <div>
                                    <div class="label">Last upload</div>
                                    <div class="value" style="font-weight:500;font-size:.78rem">
                                        <?= e(fmt_date($r['last_upload'], '—')) ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </a>
                </div>
            <?php endforeach; ?>
        </div>
    <?php else: ?>
        <div class="card">
            <div class="table-responsive">
                <table class="table table-vcenter card-table">
                    <thead><tr>
                        <th>Report</th><th>Scope</th><th>Type</th><th>Mode</th>
                        <th class="text-end">Rows</th><th class="text-end">Uploads</th><th>Last upload</th><th>Status</th><th class="w-1"></th>
                    </tr></thead>
                    <tbody>
                    <?php foreach ($rows as $r):
                        $isFresh = $r['last_upload'] && strtotime($r['last_upload']) > strtotime('-7 days');
                        $statusClass = ['active'=>'success','draft'=>'warning','inactive'=>'secondary'][$r['status']] ?? 'secondary';
                    ?>
                        <tr>
                            <td>
                                <strong><?= e($r['name']) ?></strong>
                                <div class="small text-secondary"><code><?= e($r['code']) ?></code></div>
                            </td>
                            <td class="text-secondary small">
                                <?= e($r['vertical_name']) ?> › <?= e($r['client_name']) ?> › <?= e($r['process_name']) ?>
                            </td>
                            <td><span class="badge bg-blue-lt"><?= e($r['report_type']) ?></span></td>
                            <td><span class="badge bg-secondary-lt"><?= e($r['upload_strategy']) ?></span></td>
                            <td class="text-end"><?= number_format((int)$r['row_count']) ?></td>
                            <td class="text-end"><?= (int)$r['upload_count'] ?></td>
                            <td class="small <?= $isFresh ? 'text-success' : 'text-secondary' ?>">
                                <?php if ($r['last_upload']): ?>
                                    <?= e(fmt_dt($r['last_upload'])) ?>
                                <?php else: ?>—<?php endif; ?>
                            </td>
                            <td><span class="badge bg-<?= $statusClass ?>-lt"><?= e($r['status']) ?></span></td>
                            <td>
                                <div class="btn-list flex-nowrap">
                                    <a href="<?= e(url(($r['handler'] === 'collection_dashboard' ? '/admin/collection.php?id=' : '/admin/report-view.php?id=') . $r['id'])) ?>" class="btn btn-sm btn-primary">
                                        <i class="ti ti-chart-pie-2"></i>
                                    </a>
                                    <a href="<?= e(url('/admin/reports.php?view=detail&id=' . $r['id'])) ?>" class="btn btn-sm btn-outline-secondary">
                                        <i class="ti ti-eye"></i>
                                    </a>
                                    <a href="<?= e(url('/admin/upload.php?report_id=' . $r['id'])) ?>" class="btn btn-sm btn-outline-primary">
                                        <i class="ti ti-upload"></i>
                                    </a>
                                </div>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    <?php endif; ?>

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