<?php
/**
 * Deductible Mapping upload + browser.
 *
 * A per-report list of "deductible" Visit Numbers. Replaces atomically: the old
 * list stays live until the new file is fully validated and committed. After
 * replacement, the engine re-flags collection_raw.deductible for the whole report
 * (full replace — a visit dropped from the file is un-flagged). Charge amounts are
 * read from the Collection data; the upload carries ONLY Visit Numbers.
 */
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/collection-engine.php';

$me = require_login('internal');
$pdo = db();

$reportId = (int) ($_GET['id'] ?? $_POST['id'] ?? 0);
if (!$reportId) {
    try {
        $st = $pdo->query("SELECT id FROM reports WHERE code='COLL-DASH' AND deleted_at IS NULL LIMIT 1");
        $reportId = $st ? (int) $st->fetchColumn() : 0;
    } catch (Throwable $e) { /* tables not yet created */ }
}
if ($reportId) require_report_access($reportId);   // can only map data for a report they're granted
$backUrl = $reportId ? url('/admin/collection.php?id=' . $reportId) : url('/admin/reports.php');

/* Pre-flight: required tables */
try {
    $pdo->query('SELECT 1 FROM deductible_mappings LIMIT 1');
    $pdo->query('SELECT 1 FROM collection_uploads LIMIT 1');
} catch (Throwable $e) {
    $pageTitle = 'Deductible Mapping';
    require __DIR__ . '/../includes/header.php';
    echo '<div class="container-xl"><div class="alert alert-danger mt-4">'
       . '<h3><i class="ti ti-alert-octagon me-1"></i>Deductible schema not applied yet</h3>'
       . '<p>The <code>deductible_mappings</code> table provisions automatically on the next authenticated page load. Refresh and try again.</p>'
       . '<p class="small mb-0"><strong>Detail:</strong> ' . e($e->getMessage()) . '</p></div></div>';
    require __DIR__ . '/../includes/footer.php';
    exit;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $rp = $reportId ? '?id='.$reportId : '';

    // POST larger than post_max_size → PHP drops it ($_POST/$_FILES empty). Explain clearly.
    if (empty($_POST) && empty($_FILES) && (int) ($_SERVER['CONTENT_LENGTH'] ?? 0) > 0) {
        flash('error', 'That file is larger than the server upload limit (post_max_size). Raise the PHP limits in cPanel, OR upload the CSV with File Manager into storage/uploads/deductible-mapping/ and use “Ingest a file already on the server” below.');
        redirect('/admin/deductible-mapping.php' . $rp);
    }

    csrf_check_or_die();

    /* Clear ALL deductible-mapping data for this report (no file). Confirmed by typing CLEAR. */
    if (($_POST['action'] ?? '') === 'clear') {
        if (!$reportId) { flash('error', 'A report context is required.'); redirect('/admin/deductible-mapping.php'); }
        if (strtoupper(trim((string) ($_POST['confirm'] ?? ''))) !== 'CLEAR') {
            flash('error', 'Clear cancelled — confirmation text did not match.'); redirect('/admin/deductible-mapping.php' . $rp);
        }
        try {
            $eng = new CollectionEngine();
            $res = $eng->clearDeductibleMapping($reportId);
            audit_log('deductible_mapping.clear', 'deductible_mapping', $reportId, null, $res);
            flash('success', sprintf('Deductible list cleared — %s row(s) removed and all claims un-flagged.', number_format((int) ($res['deleted'] ?? 0))));
        } catch (Throwable $e) {
            flash('error', 'Clear failed: ' . (defined('APP_DEBUG') && APP_DEBUG ? $e->getMessage() : 'error'));
        }
        redirect('/admin/deductible-mapping.php' . $rp);
    }

    $dir = __DIR__ . '/../storage/uploads/deductible-mapping';
    if (!is_dir($dir)) mkdir($dir, 0755, true);

    // Source = (a) a file already on the server (for large files), or (b) an HTTP upload.
    $serverFile = trim((string) ($_POST['server_file'] ?? ''));
    if ($serverFile !== '') {
        $dest = $dir . '/' . basename($serverFile);          // basename() blocks path traversal
        if (!is_file($dest)) {
            flash('error', 'Server file not found in storage/uploads/deductible-mapping/: ' . basename($serverFile));
            redirect('/admin/deductible-mapping.php' . $rp);
        }
        $ext = strtolower(pathinfo($dest, PATHINFO_EXTENSION));
        if (!in_array($ext, ['csv','tsv'], true)) {
            flash('error', "Only .csv / .tsv accepted (got .$ext).");
            redirect('/admin/deductible-mapping.php' . $rp);
        }
    } else {
        if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
            $err = (int) ($_FILES['file']['error'] ?? -1);
            $msg = ($err === UPLOAD_ERR_INI_SIZE || $err === UPLOAD_ERR_FORM_SIZE)
                 ? 'The file exceeds the server upload limit. Raise PHP upload_max_filesize / post_max_size in cPanel, or use “Ingest a file already on the server” below.'
                 : 'No file uploaded or upload error.';
            flash('error', $msg);
            redirect('/admin/deductible-mapping.php' . $rp);
        }
        $ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION));
        if (!in_array($ext, ['csv','tsv'], true)) {
            flash('error', "Only .csv / .tsv accepted for the deductible file (got .$ext).");
            redirect('/admin/deductible-mapping.php' . $rp);
        }
        $dest = $dir . '/' . date('YmdHis') . '_' . bin2hex(random_bytes(4)) . '.' . $ext;
        if (!move_uploaded_file($_FILES['file']['tmp_name'], $dest)) {
            flash('error', 'Failed to save uploaded file.');
            redirect('/admin/deductible-mapping.php' . $rp);
        }
    }

    try {
        set_time_limit(0);                 // large lists — don't let the web 120s limit kill the ingest
        ini_set('memory_limit', '1G');
        $eng = new CollectionEngine();
        if (!$reportId) {
            flash('error', 'A report context is required.  Open the Collection Dashboard for a report and use its Deductible button.');
            redirect($backUrl);
        }
        $res = $eng->ingestDeductibleMapping($dest, (int)$me['id'], $reportId);
        audit_log('deductible_mapping.upload','deductible_mapping', null, null, $res);
        flash('success', sprintf('Deductible list replaced: %d visit(s) ingested, %d errors. Collection claims re-flagged.',
            $res['valid'], $res['errors']));
    } catch (Throwable $t) {
        flash('error', 'Ingest failed: ' . $t->getMessage());
    }
    redirect('/admin/deductible-mapping.php' . ($reportId ? '?id='.$reportId : ''));
}

/* Download the CURRENT deductible list as an uploadable CSV — SAME single column as the blank template. */
if (($_GET['export'] ?? '') === 'csv') {
    if (!$reportId) { flash('error', 'A report context is required.'); redirect('/admin/deductible-mapping.php'); }
    audit_log('deductible_mapping.export', 'deductible_mapping', $reportId, null, null);
    set_time_limit(0);
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="deductible-visits-' . $reportId . '-' . date('Ymd') . '.csv"');
    $out = fopen('php://output', 'w'); fwrite($out, "\xEF\xBB\xBF");
    fputcsv($out, ['Visit Number']);
    try { $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); } catch (Throwable $e) {}
    $rows = $pdo->prepare("SELECT visit_id FROM deductible_mappings WHERE report_id = ? ORDER BY id");
    $rows->execute([$reportId]);
    while ($r = $rows->fetch(PDO::FETCH_ASSOC)) fputcsv($out, [$r['visit_id']]);
    fclose($out);
    exit;
}

$st = $pdo->prepare("SELECT * FROM collection_uploads WHERE report_id = ? AND kind='deductible_mapping' ORDER BY created_at DESC LIMIT 1");  $st->execute([$reportId]); $lastUpload = $st->fetch();
$st = $pdo->prepare("SELECT * FROM collection_uploads WHERE report_id = ? AND kind='deductible_mapping' ORDER BY created_at DESC LIMIT 12"); $st->execute([$reportId]); $history    = $st->fetchAll();

/* KPI tiles: unique-visit count from the cached upload meta (O(1)); deductible charge summed live
   from collection_raw (the flag baked at ingest — a simple SUM, no GROUP BY → temp-safe). */
$liveCount = 0; $uniqVisits = 0; $countsFromCache = false;
$st = $pdo->prepare("SELECT valid_rows, meta_json FROM collection_uploads
                      WHERE report_id = ? AND kind='deductible_mapping' AND status='completed'
                      ORDER BY completed_at DESC LIMIT 1");
$st->execute([$reportId]);
$cacheRow = $st->fetch();
if ($cacheRow && !empty($cacheRow['meta_json'])) {
    $meta       = json_decode((string) $cacheRow['meta_json'], true) ?: [];
    $liveCount  = (int) $cacheRow['valid_rows'];
    $uniqVisits = (int) ($meta['uniq_visits'] ?? 0);
    $countsFromCache = isset($meta['uniq_visits']);
}
if (!$countsFromCache) {
    $st = $pdo->prepare('SELECT COUNT(*) FROM deductible_mappings WHERE report_id = ?');
    $st->execute([$reportId]); $liveCount = (int) $st->fetchColumn();
    $st = $pdo->prepare('SELECT COUNT(DISTINCT visit_id) FROM deductible_mappings USE INDEX(idx_ded_map_report_visit) WHERE report_id = ?');
    $st->execute([$reportId]); $uniqVisits = (int) $st->fetchColumn();
}
/* Deductible charge + flagged-claim count, read from the Collection data. */
$dedCharge = 0.0; $dedClaims = 0;
try {
    $st = $pdo->prepare("SELECT COUNT(DISTINCT visit_id) AS claims, COALESCE(SUM(charge_amount),0) AS charge
                           FROM collection_raw WHERE report_id = ? AND deductible = 1");
    $st->execute([$reportId]); $dr = $st->fetch();
    $dedClaims = (int) ($dr['claims'] ?? 0);
    $dedCharge = (float) ($dr['charge'] ?? 0);
} catch (Throwable $e) { /* deductible column not provisioned yet */ }

$q = trim((string) ($_GET['q'] ?? ''));
$browseParams = [$reportId];
$browseWhere  = 'WHERE report_id = ?';
if ($q !== '') {
    $browseWhere .= " AND visit_id LIKE ?";
    $browseParams[] = "%$q%";
}
$stmt = $pdo->prepare("SELECT visit_id, created_at
    FROM deductible_mappings $browseWhere ORDER BY id DESC LIMIT 200");
$stmt->execute($browseParams);
$browse = $stmt->fetchAll();

$pageTitle = 'Deductible Mapping';
require __DIR__ . '/../includes/header.php';
?>
<div class="page-header"><div class="container-xl">
    <div class="row align-items-center">
        <div class="col">
            <a href="<?= e($backUrl) ?>" class="text-secondary small text-decoration-none"><i class="ti ti-chevron-left"></i> Collection Dashboard</a>
            <h2 class="page-title mt-1"><i class="ti ti-receipt-tax me-2 text-primary"></i>Deductible Mapping</h2>
            <div class="text-secondary">Visit IDs that are deductible — flagged automatically on the Collection data. Charge amounts come from the Collection data.</div>
        </div>
        <div class="col-auto">
            <a href="<?= e(url('/admin/template.php?kind=deductible-mapping')) ?>" class="btn btn-outline-primary" download>
                <i class="ti ti-download me-1"></i>Blank template
            </a>
        </div>
        <?php if ($reportId): ?>
        <div class="col-auto">
            <a href="<?= e(url('/admin/deductible-mapping.php?id=' . $reportId . '&export=csv')) ?>" class="btn btn-outline-success" title="Download the CURRENT deductible visit list as CSV — add new entries, Clear all data, then re-upload">
                <i class="ti ti-file-download me-1"></i>Download current data
            </a>
        </div>
        <div class="col-auto">
            <form method="POST" action="<?= e(url('/admin/deductible-mapping.php?id=' . $reportId)) ?>" id="clearForm" class="d-inline">
                <?= csrf_field() ?>
                <input type="hidden" name="action" value="clear">
                <input type="hidden" name="confirm" id="clearConfirm" value="">
                <button type="button" id="clearBtn" class="btn btn-outline-danger" title="Wipe all deductible visit IDs for this report">
                    <i class="ti ti-trash me-1"></i>Clear all data
                </button>
            </form>
        </div>
        <script>
        (function(){
            var btn=document.getElementById('clearBtn'); if(!btn) return;
            btn.addEventListener('click', function(){
                var typed = prompt('⚠ This WIPES all deductible visit IDs for this report and un-flags the Collection claims.\n\nType CLEAR (exactly) to confirm:');
                if(typed === null) return;
                if((typed||'').trim().toUpperCase() !== 'CLEAR'){ alert('Cancelled — confirmation text did not match.'); return; }
                document.getElementById('clearConfirm').value='CLEAR';
                btn.disabled=true; btn.innerHTML='<i class="ti ti-loader-2"></i> Clearing…';
                document.getElementById('clearForm').submit();
            });
        })();
        </script>
        <?php endif; ?>
        <div class="col-auto small text-secondary">
            <?php if ($lastUpload && $lastUpload['status']==='completed'): ?>
                <i class="ti ti-clock me-1"></i>Last replaced <?= e(fmt_dt($lastUpload['completed_at'])) ?>
            <?php else: ?>
                <span class="text-warning"><i class="ti ti-alert-triangle me-1"></i>No deductible list uploaded yet</span>
            <?php endif; ?>
        </div>
    </div>
</div></div>

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

    <div class="row row-cards mb-3">
        <div class="col-md-4"><div class="card kpi-tile"><div class="card-body py-3">
            <div class="d-flex align-items-center mb-2"><span class="bg-blue-lt text-blue avatar avatar-sm me-2"><i class="ti ti-database"></i></span><div class="stat-label">Total list rows</div></div>
            <div class="stat-value"><?= number_format($liveCount) ?></div>
        </div></div></div>
        <div class="col-md-4"><div class="card kpi-tile"><div class="card-body py-3">
            <div class="d-flex align-items-center mb-2"><span class="bg-green-lt text-green avatar avatar-sm me-2"><i class="ti ti-id"></i></span><div class="stat-label">Unique deductible visits</div></div>
            <div class="stat-value"><?= number_format($uniqVisits) ?></div>
        </div></div></div>
        <div class="col-md-4"><div class="card kpi-tile"><div class="card-body py-3">
            <div class="d-flex align-items-center mb-2"><span class="bg-orange-lt text-orange avatar avatar-sm me-2"><i class="ti ti-cash"></i></span><div class="stat-label">Deductible charge <span class="text-secondary">(from Collection)</span></div></div>
            <div class="stat-value">$<?= number_format((float)$dedCharge, 2) ?></div>
            <div class="form-hint mt-1"><?= number_format($dedClaims) ?> flagged claim(s) matched in the Collection data</div>
        </div></div></div>
    </div>

    <div class="row row-cards">
        <div class="col-md-5">
            <div class="card">
                <div class="card-header"><h3 class="card-title"><i class="ti ti-upload me-1 text-primary"></i>Replace deductible list</h3></div>
                <div class="card-body">
                    <div class="alert alert-info py-2 small mb-3">
                        <i class="ti ti-info-circle me-1"></i><strong>Atomic replace:</strong> the old list stays active until the new upload completes successfully. Re-uploading fully replaces the list — a visit dropped from the file is un-flagged.
                    </div>
                    <form id="mapForm">
                        <div class="d-flex align-items-center justify-content-between mb-1">
                            <label class="form-label mb-0">Deductible visit IDs (CSV)</label>
                            <a href="<?= e(url('/admin/template.php?kind=deductible-mapping')) ?>" class="small text-decoration-none" download><i class="ti ti-download me-1"></i>Download blank template</a>
                        </div>
                        <input type="file" id="mapFile" accept=".csv,.tsv" required class="form-control mb-3">
                        <div class="small text-secondary mb-3">
                            <strong>Column:</strong> <code>Visit Number</code> — one deductible visit per row. Charge amounts come from the Collection data; any other columns are ignored.
                        </div>
                        <button type="submit" id="mapBtn" class="btn btn-primary w-100"><i class="ti ti-cloud-upload me-1"></i>Upload and replace</button>

                        <div id="mapProgWrap" class="mt-3 d-none">
                            <div class="d-flex justify-content-between small mb-1">
                                <span id="mapStatus" class="text-secondary">Uploading…</span>
                                <span id="mapPct" class="fw-bold">0%</span>
                            </div>
                            <div class="progress" style="height:1.1rem">
                                <div id="mapBar" class="progress-bar progress-bar-striped progress-bar-animated" style="width:0%"></div>
                            </div>
                            <div class="form-hint mt-1"><i class="ti ti-alert-triangle me-1"></i>Please keep this page open until it finishes.</div>
                        </div>
                        <div id="mapErr" class="alert alert-danger mt-3 d-none"></div>
                    </form>
                    <div class="small text-secondary mt-2"><i class="ti ti-info-circle me-1"></i>Large files upload in pieces with a live progress bar — no size limit, no cPanel needed.</div>
                </div>
            </div>

            <?php if ($history): ?>
            <div class="card mt-3">
                <div class="card-header"><h3 class="card-title small mb-0"><i class="ti ti-history me-1"></i>Upload history</h3></div>
                <div class="table-responsive">
                    <table class="table table-sm card-table">
                        <thead><tr><th>When</th><th>File</th><th class="text-end">Rows</th><th>Status</th></tr></thead>
                        <tbody>
                        <?php foreach ($history as $h): ?>
                            <tr>
                                <td class="small text-secondary text-nowrap"><?= e(fmt_dt($h['created_at'])) ?></td>
                                <td class="small text-truncate" style="max-width:170px" title="<?= e($h['file_name']) ?>"><?= e($h['file_name']) ?></td>
                                <td class="text-end"><?= number_format((int)$h['valid_rows']) ?></td>
                                <td><span class="badge bg-<?= $h['status']==='completed'?'success':($h['status']==='failed'?'danger':'info') ?>-lt"><?= e($h['status']) ?></span></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
            <?php endif; ?>
        </div>

        <div class="col-md-7">
            <div class="card">
                <div class="card-header">
                    <h3 class="card-title"><i class="ti ti-list me-1 text-primary"></i>Browse deductible visits <span class="text-secondary small">(sample 200)</span></h3>
                    <form method="GET" class="card-actions">
                        <?php if ($reportId): ?><input type="hidden" name="id" value="<?= $reportId ?>"><?php endif; ?>
                        <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 visit_id…" value="<?= e($q) ?>">
                        </div>
                    </form>
                </div>
                <div class="table-responsive" style="max-height: 600px; overflow:auto">
                    <table class="table table-sm card-table" style="font-size:.82rem">
                        <thead style="position:sticky;top:0;background:var(--m-surface)">
                            <tr><th style="width:50%">Visit Number</th><th class="text-nowrap">Added</th></tr>
                        </thead>
                        <tbody>
                        <?php if (!$browse): ?>
                            <tr><td colspan="2" class="text-secondary text-center py-4">No deductible visits yet. Upload the file to start.</td></tr>
                        <?php else: foreach ($browse as $r): ?>
                            <tr>
                                <td><code><?= e($r['visit_id']) ?></code></td>
                                <td class="text-nowrap text-secondary small"><?= e(fmt_dt($r['created_at'] ?? null)) ?></td>
                            </tr>
                        <?php endforeach; endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

</div></div>
<?php require __DIR__ . '/../includes/chunk-upload-js.php'; ?>
<script>
(function(){
    var form=document.getElementById('mapForm'); if(!form) return;
    var fileEl=document.getElementById('mapFile'), btn=document.getElementById('mapBtn'),
        wrap=document.getElementById('mapProgWrap'), bar=document.getElementById('mapBar'),
        pct=document.getElementById('mapPct'), statusEl=document.getElementById('mapStatus'),
        errBox=document.getElementById('mapErr');
    form.addEventListener('submit', function(e){
        e.preventDefault();
        errBox.classList.add('d-none'); errBox.textContent='';
        if(!fileEl.files||!fileEl.files[0]){ errBox.textContent='Please choose a CSV/TSV file first.'; errBox.classList.remove('d-none'); return; }
        btn.disabled=true; fileEl.disabled=true; wrap.classList.remove('d-none');
        mChunkUpload({
            file:fileEl.files[0], kind:'deductible-mapping', reportId:<?= (int)$reportId ?>,
            csrf:<?= json_encode(csrf_token()) ?>,
            endpoint:<?= json_encode(url('/admin/upload-chunk.php')) ?>,
            onProgress:function(p,phase,secs){
                bar.style.width=p+'%'; pct.textContent=p+'%';
                if(phase==='process'){
                    var t=Math.max(0,secs||0), el=(t>=60?(Math.floor(t/60)+'m '+(t%60)+'s'):(t+'s'));
                    statusEl.textContent='Re-flagging Collection data — '+el+' elapsed. Please wait, large files can take a few minutes.';
                    bar.classList.add('bg-warning');
                } else { statusEl.textContent='Uploading '+p+'%…'; }
            },
            onDone:function(j){
                bar.classList.remove('bg-warning'); bar.classList.add('bg-success'); pct.textContent='✓';
                statusEl.textContent='Done — '+Number(j.valid).toLocaleString()+' visits flagged. Reloading…';
                setTimeout(function(){ window.location.reload(); }, 1100);
            },
            onError:function(msg){
                errBox.textContent=msg; errBox.classList.remove('d-none');
                bar.classList.add('bg-danger'); statusEl.textContent='Failed';
                btn.disabled=false; fileEl.disabled=false;
            }
        });
    });
})();
</script>
<?php require __DIR__ . '/../includes/footer.php'; ?>
