<?php
/**
 * Data cleanup — SysAdmin-only test-data deletion across every vertical.  data-cleanup-v1
 *
 * Lets a SysAdmin find and DELETE test records (coding charts, AR accounts/visits, billing rows) by id / MRN /
 * chart-number / visit-id / patient name, WITHOUT touching the database directly. Every delete runs in a
 * transaction, removes all dependent child rows in the correct order, and writes an audit_log entry.
 *
 * THIS IS DESTRUCTIVE AND PERMANENT. It is gated to SysAdmin both by the route (auth.php) and in-page.
 */
require_once __DIR__ . '/../includes/config.php';
$me  = require_login('internal');
$pdo = db();
$uid = (int) $me['id'];

if (!is_sysadmin()) { http_response_code(403); exit('Only a SysAdmin can use data cleanup.'); }

$type = in_array($_GET['type'] ?? $_POST['type'] ?? 'coding', ['coding', 'ar', 'billing'], true) ? ($_GET['type'] ?? $_POST['type'] ?? 'coding') : 'coding';
$q    = trim((string) ($_GET['q'] ?? ''));
$selfUrl = fn(array $o = []) => '/admin/data-cleanup.php?' . http_build_query(array_filter(array_merge(['type' => $type, 'q' => $q], $o), fn($v) => $v !== '' && $v !== null));

/* Optional-table guard: billing/AR tables may be absent on a fresh install — skip their deletes gracefully. */
$tableExists = function (string $t) use ($pdo): bool {
    try { $pdo->query("SELECT 1 FROM `" . preg_replace('/[^a-z_0-9]/i', '', $t) . "` LIMIT 1"); return true; }
    catch (Throwable $e) { return false; }
};

/* ============================================================================ POST — delete */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    csrf_check_or_die();
    if (!is_sysadmin()) { http_response_code(403); exit('Forbidden'); }
    $act = (string) ($_POST['action'] ?? '');

    /* ---- Coding chart: removes the chart + all coding children + its billing footprint ---- */
    if ($act === 'del_coding') {
        $id = (int) ($_POST['id'] ?? 0);
        $snap = $pdo->prepare("SELECT id, patient_name, patient_mrn, dos, status FROM coding_inventory WHERE id=?");
        $snap->execute([$id]); $snap = $snap->fetch();
        if (!$snap) { flash('error', 'Coding chart not found.'); redirect($selfUrl()); }
        try {
            $pdo->beginTransaction();
            if ($tableExists('billing_chart_cpt'))  $pdo->prepare("DELETE FROM billing_chart_cpt WHERE inventory_id=?")->execute([$id]);
            if ($tableExists('billing_allocation')) {
                if ($tableExists('billing_allocation_event')) {
                    $pdo->prepare("DELETE e FROM billing_allocation_event e JOIN billing_allocation a ON a.id=e.allocation_id WHERE a.inventory_id=?")->execute([$id]);
                    // Also purge events that carry the chart's inventory_id but a NULL/stale allocation_id (e.g. rfi_to_coding).
                    try { $pdo->prepare("DELETE FROM billing_allocation_event WHERE inventory_id=?")->execute([$id]); } catch (Throwable $e) {}
                }
                $pdo->prepare("DELETE FROM billing_allocation WHERE inventory_id=?")->execute([$id]);
            }
            $pdo->prepare("DELETE FROM coding_audit_codes WHERE inventory_id=?")->execute([$id]);
            $pdo->prepare("DELETE FROM coding_chart_events WHERE inventory_id=?")->execute([$id]);
            $pdo->prepare("DELETE FROM coding_chart_cpt_icds WHERE chart_cpt_id IN (SELECT id FROM coding_chart_cpts WHERE inventory_id=?)")->execute([$id]);
            $pdo->prepare("DELETE FROM coding_chart_cpt_modifiers WHERE chart_cpt_id IN (SELECT id FROM coding_chart_cpts WHERE inventory_id=?)")->execute([$id]);
            $pdo->prepare("DELETE FROM coding_chart_cpts WHERE inventory_id=?")->execute([$id]);
            $pdo->prepare("DELETE FROM coding_inventory WHERE id=?")->execute([$id]);
            $pdo->commit();
            audit_log('admin.record_delete.coding', 'coding_inventory', $id, $snap, null);
            flash('success', 'Deleted coding chart #' . $id . ' (' . e($snap['patient_name'] ?: '—') . ') and all its coding + billing rows.');
        } catch (Throwable $ex) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            flash('error', 'Delete failed: ' . $ex->getMessage());
        }
        redirect($selfUrl());
    }

    /* ---- AR account: removes an account's visit ledger + audits + allocation + its collection_raw rows ---- */
    if ($act === 'del_ar') {
        $pid   = (int) ($_POST['process_id'] ?? 0);
        $chart = (string) ($_POST['chart_number'] ?? '');
        if (!$pid || $chart === '') { flash('error', 'Pick an AR account to delete.'); redirect($selfUrl()); }
        try {
            $pdo->beginTransaction();
            if (function_exists('ar_drop_account_audits')) ar_drop_account_audits($pdo, $pid, $chart);
            if ($tableExists('ar_visit_work'))  $pdo->prepare("DELETE FROM ar_visit_work WHERE process_id=? AND chart_number=?")->execute([$pid, $chart]);
            if ($tableExists('ar_allocation'))  $pdo->prepare("DELETE FROM ar_allocation WHERE process_id=? AND chart_number=?")->execute([$pid, $chart]);
            if ($tableExists('ar_allocation_event')) {
                try { $pdo->prepare("DELETE FROM ar_allocation_event WHERE process_id=? AND chart_number=?")->execute([$pid, $chart]); } catch (Throwable $e) {}
            }
            $repIds = function_exists('ar_process_report_ids') ? ar_process_report_ids($pdo, $pid) : [];
            $crN = 0;
            if ($repIds) {
                $in = implode(',', array_map('intval', $repIds));
                $d = $pdo->prepare("DELETE FROM collection_raw WHERE report_id IN ($in) AND chart_number=?");
                $d->execute([$chart]); $crN = $d->rowCount();
            }
            $pdo->commit();
            audit_log('admin.record_delete.ar', 'ar_account', $pid, ['process_id' => $pid, 'chart_number' => $chart, 'collection_rows' => $crN], null);
            flash('success', 'Deleted AR account ' . e($chart) . ' (' . $crN . ' collection row(s) + visit ledger, audits, allocation).');
        } catch (Throwable $ex) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            flash('error', 'Delete failed: ' . $ex->getMessage());
        }
        redirect($selfUrl());
    }

    /* ---- Billing footprint only: removes a chart from billing and returns it to coding deliverables ---- */
    if ($act === 'del_billing') {
        $id = (int) ($_POST['inventory_id'] ?? 0);
        if (!$id) { flash('error', 'Pick a billing record.'); redirect($selfUrl()); }
        try {
            $pdo->beginTransaction();
            if ($tableExists('billing_chart_cpt'))  $pdo->prepare("DELETE FROM billing_chart_cpt WHERE inventory_id=?")->execute([$id]);
            if ($tableExists('billing_allocation')) {
                if ($tableExists('billing_allocation_event')) {
                    $pdo->prepare("DELETE e FROM billing_allocation_event e JOIN billing_allocation a ON a.id=e.allocation_id WHERE a.inventory_id=?")->execute([$id]);
                    try { $pdo->prepare("DELETE FROM billing_allocation_event WHERE inventory_id=?")->execute([$id]); } catch (Throwable $e) {}
                }
                $pdo->prepare("DELETE FROM billing_allocation WHERE inventory_id=?")->execute([$id]);
            }
            // Un-release the coding chart so it returns to Coding → Deliverables (no longer "in billing").
            $pdo->prepare("UPDATE coding_inventory SET released_to_billing_at=NULL, released_by=NULL, billing_status=NULL, release_destination=NULL, updated_at=NOW() WHERE id=?")->execute([$id]);
            $pdo->commit();
            audit_log('admin.record_delete.billing', 'coding_inventory', $id, ['inventory_id' => $id], null);
            flash('success', 'Removed chart #' . $id . ' from billing (coding chart kept; it returns to Deliverables).');
        } catch (Throwable $ex) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            flash('error', 'Delete failed: ' . $ex->getMessage());
        }
        redirect($selfUrl());
    }
    redirect($selfUrl());
}

/* ============================================================================ GET — search results */
$rows = [];
$like = '%' . $q . '%';
if ($q !== '') {
    if ($type === 'coding') {
        $st = $pdo->prepare("SELECT ci.id, ci.patient_name, ci.patient_mrn, ci.dos, ci.status, ci.released_to_billing_at,
                c.name cn, p.name pn, f.name fn
            FROM coding_inventory ci
            JOIN clients c ON c.id=ci.client_id JOIN processes p ON p.id=ci.process_id JOIN coding_facilities f ON f.id=ci.facility_id
            WHERE ci.patient_mrn LIKE ? OR ci.patient_name LIKE ? OR ci.id = ?
            ORDER BY ci.id DESC LIMIT 200");
        $st->execute([$like, $like, ctype_digit($q) ? (int) $q : 0]);
        $rows = $st->fetchAll();
    } elseif ($type === 'ar') {
        $st = $pdo->prepare("SELECT p.id process_id, p.name pn, c.name cn, cr.chart_number,
                MAX(TRIM(CONCAT(COALESCE(cr.patient_last_name,''),', ',COALESCE(cr.patient_first_name,'')))) patient,
                COUNT(DISTINCT cr.visit_id) visits, COUNT(*) rows_n
            FROM collection_raw cr
            JOIN reports r ON r.id=cr.report_id JOIN processes p ON p.id=r.process_id JOIN clients c ON c.id=p.client_id
            WHERE cr.chart_number LIKE ? OR cr.visit_id LIKE ?
               OR CONCAT(COALESCE(cr.patient_last_name,''),' ',COALESCE(cr.patient_first_name,'')) LIKE ?
            GROUP BY p.id, cr.chart_number
            ORDER BY p.name, cr.chart_number LIMIT 200");
        $st->execute([$like, $like, $like]);
        $rows = $st->fetchAll();
    } else { // billing
        $st = $pdo->prepare("SELECT a.inventory_id, ci.patient_name, ci.patient_mrn, ci.dos, a.inventory_status, a.billing_status,
                c.name cn, u.name biller
            FROM billing_allocation a
            JOIN coding_inventory ci ON ci.id=a.inventory_id
            LEFT JOIN clients c ON c.id=ci.client_id LEFT JOIN users u ON u.id=a.allocated_biller
            WHERE ci.patient_mrn LIKE ? OR ci.patient_name LIKE ? OR a.inventory_id = ?
            ORDER BY a.id DESC LIMIT 200");
        $st->execute([$like, $like, ctype_digit($q) ? (int) $q : 0]);
        $rows = $st->fetchAll();
    }
}

$pageTitle = 'Data cleanup';
require __DIR__ . '/../includes/header.php';
$tabs = ['coding' => 'Coding charts', 'ar' => 'AR accounts', 'billing' => 'Billing records'];
?>
<div class="page-header"><div class="container-xl"><div class="row align-items-center">
    <div class="col"><h2 class="page-title"><i class="ti ti-trash-x me-2 text-danger"></i>Data cleanup <span class="badge bg-red-lt ms-1">SysAdmin</span></h2>
        <div class="text-secondary small">Find and permanently delete test records across any vertical — without touching the database. Every delete is logged.</div>
    </div>
</div></div></div>

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

    <div class="alert alert-danger d-flex align-items-center"><i class="ti ti-alert-triangle me-2"></i>
        <div><strong>Permanent deletion.</strong> This removes the record and all its dependent rows (coding lines/audits/events, billing allocation, AR visit ledger/audits, collection rows). There is no undo — use only for test cleanup.</div>
    </div>

    <ul class="nav nav-tabs mb-3">
        <?php foreach ($tabs as $k => $t): ?>
            <li class="nav-item"><a class="nav-link <?= $type===$k?'active':'' ?>" href="<?= e(url('/admin/data-cleanup.php?type='.$k)) ?>"><?= e($t) ?></a></li>
        <?php endforeach; ?>
    </ul>

    <form method="GET" class="card mb-3"><div class="card-body py-2 d-flex flex-wrap gap-2 align-items-center">
        <input type="hidden" name="type" value="<?= e($type) ?>">
        <input type="search" name="q" class="form-control form-control-sm" style="max-width:360px" value="<?= e($q) ?>"
               placeholder="<?= $type==='ar' ? 'Chart #, visit id, or patient name…' : 'Chart id, patient MRN, or patient name…' ?>" autocomplete="off" autofocus>
        <button class="btn btn-sm btn-primary"><i class="ti ti-search me-1"></i>Search</button>
        <span class="text-secondary small ms-1"><?= e($tabs[$type]) ?></span>
    </div></form>

    <?php if ($q === ''): ?>
        <div class="card"><div class="card-body text-secondary text-center py-5"><i class="ti ti-search" style="font-size:2rem;opacity:.4"></i><div class="mt-2">Search above to find <?= e(strtolower($tabs[$type])) ?> to delete.</div></div></div>
    <?php elseif (!$rows): ?>
        <div class="card"><div class="card-body text-secondary text-center py-5">No matching <?= e(strtolower($tabs[$type])) ?> for “<?= e($q) ?>”.</div></div>
    <?php else: ?>
        <div class="card"><div class="card-header py-2"><h3 class="card-title"><?= count($rows) ?> match<?= count($rows)===1?'':'es' ?></h3></div>
        <div class="table-responsive"><table class="table table-vcenter card-table table-sm">
            <?php if ($type === 'coding'): ?>
                <thead><tr><th>Chart #</th><th>Patient</th><th>MRN</th><th>DOS</th><th>Client » Process » Facility</th><th>Status</th><th class="w-1"></th></tr></thead>
                <tbody><?php foreach ($rows as $r): ?>
                    <tr>
                        <td class="font-mono">#<?= (int)$r['id'] ?></td>
                        <td><strong><?= e($r['patient_name'] ?: '—') ?></strong></td>
                        <td><?= e($r['patient_mrn'] ?: '—') ?></td>
                        <td class="text-nowrap"><?= e(fmt_date($r['dos'])) ?></td>
                        <td class="small text-secondary"><?= e($r['cn']) ?> » <?= e($r['pn']) ?> » <?= e($r['fn']) ?></td>
                        <td><span class="badge bg-secondary-lt"><?= e(str_replace('_',' ',$r['status'])) ?></span><?php if ($r['released_to_billing_at']): ?> <span class="badge bg-azure-lt" title="Released to billing — its billing rows are deleted too">in billing</span><?php endif; ?></td>
                        <td><form method="POST" onsubmit="return confirm('Permanently delete coding chart #<?= (int)$r['id'] ?> (<?= e(addslashes($r['patient_name'] ?: '')) ?>) and ALL its coding + billing rows?');">
                            <?= csrf_field() ?><input type="hidden" name="action" value="del_coding"><input type="hidden" name="type" value="coding"><input type="hidden" name="id" value="<?= (int)$r['id'] ?>">
                            <button class="btn btn-sm btn-danger"><i class="ti ti-trash me-1"></i>Delete</button>
                        </form></td>
                    </tr>
                <?php endforeach; ?></tbody>
            <?php elseif ($type === 'ar'): ?>
                <thead><tr><th>Account (chart #)</th><th>Patient</th><th>Client » Process</th><th class="text-center">Visits</th><th class="text-center">Rows</th><th class="w-1"></th></tr></thead>
                <tbody><?php foreach ($rows as $r): ?>
                    <tr>
                        <td class="font-mono"><?= e($r['chart_number']) ?></td>
                        <td><strong><?= e(trim($r['patient'], ', ') ?: '—') ?></strong></td>
                        <td class="small text-secondary"><?= e($r['cn']) ?> » <?= e($r['pn']) ?></td>
                        <td class="text-center"><?= (int)$r['visits'] ?></td>
                        <td class="text-center text-secondary"><?= (int)$r['rows_n'] ?></td>
                        <td><form method="POST" onsubmit="return confirm('Permanently delete AR account <?= e(addslashes($r['chart_number'])) ?> — its visit ledger, audits, allocation, and <?= (int)$r['rows_n'] ?> collection row(s)?');">
                            <?= csrf_field() ?><input type="hidden" name="action" value="del_ar"><input type="hidden" name="type" value="ar">
                            <input type="hidden" name="process_id" value="<?= (int)$r['process_id'] ?>"><input type="hidden" name="chart_number" value="<?= e($r['chart_number']) ?>">
                            <button class="btn btn-sm btn-danger"><i class="ti ti-trash me-1"></i>Delete</button>
                        </form></td>
                    </tr>
                <?php endforeach; ?></tbody>
            <?php else: ?>
                <thead><tr><th>Chart #</th><th>Patient</th><th>MRN</th><th>DOS</th><th>Client</th><th>Biller</th><th>Status</th><th class="w-1"></th></tr></thead>
                <tbody><?php foreach ($rows as $r): ?>
                    <tr>
                        <td class="font-mono">#<?= (int)$r['inventory_id'] ?></td>
                        <td><strong><?= e($r['patient_name'] ?: '—') ?></strong></td>
                        <td><?= e($r['patient_mrn'] ?: '—') ?></td>
                        <td class="text-nowrap"><?= e(fmt_date($r['dos'])) ?></td>
                        <td class="small text-secondary"><?= e($r['cn']) ?></td>
                        <td class="small"><?= e($r['biller'] ?: '—') ?></td>
                        <td><span class="badge bg-secondary-lt"><?= e($r['inventory_status'] ?: '—') ?></span> <?= $r['billing_status'] ? '<span class="badge bg-blue-lt">'.e($r['billing_status']).'</span>' : '' ?></td>
                        <td><form method="POST" onsubmit="return confirm('Remove chart #<?= (int)$r['inventory_id'] ?> from billing? (The coding chart is kept and returns to Deliverables.)');">
                            <?= csrf_field() ?><input type="hidden" name="action" value="del_billing"><input type="hidden" name="type" value="billing"><input type="hidden" name="inventory_id" value="<?= (int)$r['inventory_id'] ?>">
                            <button class="btn btn-sm btn-outline-danger"><i class="ti ti-trash me-1"></i>Remove from billing</button>
                        </form></td>
                    </tr>
                <?php endforeach; ?></tbody>
            <?php endif; ?>
        </table></div></div>
    <?php endif; ?>

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