<?php
/**
 * Multi-level drill-down endpoint (JSON) for the Collection Dashboard.
 * Expands an aggregated row one level at a time, e.g. Carrier ▸ Year ▸ Month ▸ Claim.
 *
 * GET: id(report), level (year|month|claim), + accumulated constraints
 *      (carrier|cpt|provider|facility|year|month) + page filters (f_facility|f_provider|f_year|f_month).
 *
 * Returns aggregated rows {bucket,label,claims,charge,allowed,paid,ins_balance,pat_balance}
 * for year/month levels, or claim rows {visit,dos,facility,provider,charge,...} for the leaf.
 */
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/collection-engine.php';
header('Content-Type: application/json; charset=utf-8');

// Allow internal staff AND client-portal users (the collection dashboard's drill-downs run in client mode too).
$cu         = current_user();
$clientMode = $cu && (($cu['user_type'] ?? '') === 'client');
$me = require_login($clientMode ? 'client' : 'internal');
$pdo = db();

$reportId = (int) ($_GET['id'] ?? 0);
$level    = (string) ($_GET['level'] ?? 'claim');
if ($reportId <= 0)                                    { echo json_encode(['ok'=>false,'error'=>'No report.']); exit; }
// A client may only drill a report that belongs to their OWN client (client-assignment-based fence).
// Internal users keep the per-report report_access grant check.
if ($clientMode) {
    $own = $pdo->prepare("SELECT p.client_id FROM reports r JOIN processes p ON p.id=r.process_id WHERE r.id=?");
    $own->execute([$reportId]); $rc = $own->fetchColumn();
    if ($rc === false || ((int)$rc !== (int)($me['client_id'] ?? 0) && !user_can_view_report((int)$me['id'], $reportId))) { echo json_encode(['ok'=>false,'error'=>'No access to this report.']); exit; }
} elseif (!user_can_view_report((int)$me['id'], $reportId)) { echo json_encode(['ok'=>false,'error'=>'No access to this report.']); exit; }
if (!in_array($level, ['year','month','facility','claim'], true)) { echo json_encode(['ok'=>false,'error'=>'Bad level.']); exit; }

/**
 * Virtual rollup scope — same shape as admin/collection.php's helper.  Returns
 * ['sql' => '(report_id = ? OR (report_id = ? AND facility IN (...)) ...)', 'params' => [...]]
 * so a rollup report (e.g. 12 Blue Central) sees its own rows PLUS the (source, facility)
 * pairs registered in `report_facility_mirror`.  No physical row copy — the rules are
 * applied at query time.  Duplicated here (and in admin/collection.php) to avoid a new
 * shared include; the function body is small and changes rarely.
 */
function collection_scope_for_report(PDO $pdo, int $reportId): array
{
    static $cache = [];
    if (isset($cache[$reportId])) return $cache[$reportId];
    try {
        // SECURITY (multi-tenant): only mirror SOURCE reports of the SAME CLIENT as the target are included,
        // so a cross-client rollup rule can never leak another client's PHI to a client-portal user. (Mirrors
        // the same-client fence in admin/collection.php.)
        $st = $pdo->prepare("SELECT m.source_report_id, m.facility
            FROM report_facility_mirror m
            JOIN reports tr ON tr.id = m.target_report_id JOIN processes tp ON tp.id = tr.process_id
            JOIN reports sr ON sr.id = m.source_report_id JOIN processes sp ON sp.id = sr.process_id
            WHERE m.target_report_id = ? AND sp.client_id = tp.client_id
            ORDER BY m.source_report_id");
        $st->execute([$reportId]);
        $rules = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    } catch (Throwable $t) {
        $rules = [];
    }
    if (!$rules) {
        return $cache[$reportId] = ['sql' => 'report_id = ?', 'params' => [$reportId]];
    }
    $bySource = [];
    foreach ($rules as $r) $bySource[(int) $r['source_report_id']][] = (string) $r['facility'];
    $clauses = ['report_id = ?'];
    $params  = [$reportId];
    foreach ($bySource as $srcId => $facs) {
        $ph = implode(',', array_fill(0, count($facs), '?'));
        $clauses[] = "(report_id = ? AND facility IN ($ph))";
        $params[]  = $srcId;
        foreach ($facs as $f) $params[] = $f;
    }
    return $cache[$reportId] = [
        'sql'    => '(' . implode(' OR ', $clauses) . ')',
        'params' => $params,
    ];
}

$scope  = collection_scope_for_report($pdo, $reportId);
$where  = [$scope['sql']];
$params = $scope['params'];

/* Whitelisted accumulated constraints → real columns (parameterised). */
$colMap = [
    'carrier'  => 'carrier1_name',
    'cpt'      => 'charge_code',
    'provider' => 'provider_name',
    'facility' => 'facility',
    'year'     => 'dos_year',
    'month'    => 'dos_month',
];
foreach ($colMap as $k => $col) {
    if (!isset($_GET[$k]) || $_GET[$k] === '') continue;
    $val = (string) $_GET[$k];
    if (in_array($val, ['(unmapped)','(no provider)','(no carrier)'], true)) {
        $where[] = "($col IS NULL OR $col = '')";
    } else {
        $where[] = "$col = ?";
        $params[] = ($k === 'year' || $k === 'month') ? (int) $val : $val;
    }
}
/* Aging bucket constraint (days since DOS, on claims with any open balance). */
$agingBuckets = ['0-30'=>[0,30], '31-60'=>[31,60], '61-90'=>[61,90], '91-120'=>[91,120], '121+'=>[121,100000]];
if (isset($_GET['bucket']) && isset($agingBuckets[$_GET['bucket']])) {
    [$lo, $hi] = $agingBuckets[$_GET['bucket']];
    // dos BETWEEN (today - hi) AND (today - lo) == DATEDIFF(today,dos) BETWEEN lo AND hi,
    // but written on dos directly so the (report_id, dos, ...) index can be used.
    $where[] = '(insurance_balance > 0 OR patient_balance > 0) AND dos IS NOT NULL AND dos BETWEEN DATE_SUB(CURDATE(), INTERVAL ? DAY) AND DATE_SUB(CURDATE(), INTERVAL ? DAY)';
    $params[] = $hi; $params[] = $lo;
}

/* Claim-status filter (recently billed / unpaid / primary|secondary pending / …) —
   shared definition with the dashboard. Accepts multi-value status[] OR legacy single status. */
$cleanArr = function ($raw): array {
    if ($raw === null) return [];
    $arr = is_array($raw) ? $raw : [$raw];
    $arr = array_map(fn($v) => trim((string) $v), $arr);
    $arr = array_filter($arr, fn($v) => $v !== '');
    return array_values(array_unique($arr));
};
$drillStatuses = $cleanArr($_GET['status'] ?? null);
if (($_GET['recent'] ?? '') === '1' && !in_array('recent', $drillStatuses, true)) $drillStatuses[] = 'recent';
if ($drillStatuses) {
    $sFrags = [];
    foreach ($drillStatuses as $s) {
        [$sSql, $sParams] = collection_status_sql($s, $reportId);
        if ($sSql) { $sFrags[] = "($sSql)"; foreach ($sParams as $sp) $params[] = $sp; }
    }
    if ($sFrags) $where[] = '(' . implode(' OR ', $sFrags) . ')';
}

/* Active page filters carry through — accept both array form (f_facility[]) and
   legacy single-value form so old bookmarks/links still work. */
$inClause = function (string $col, array $values, bool $intCol = false) use (&$params): string {
    if (!$values) return '';
    $ph = implode(',', array_fill(0, count($values), '?'));
    foreach ($values as $v) $params[] = $intCol ? (int) $v : (string) $v;
    return "$col IN ($ph)";
};
$dfFac = $cleanArr($_GET['f_facility'] ?? null);
$dfPro = $cleanArr($_GET['f_provider'] ?? null);
$dfYr  = array_values(array_filter(array_map('intval', $cleanArr($_GET['f_year']  ?? null)), fn($n) => $n > 0));
$dfMo  = array_values(array_filter(array_map('intval', $cleanArr($_GET['f_month'] ?? null)), fn($n) => $n >= 1 && $n <= 12));
if ($f = $inClause('facility',      $dfFac))       $where[] = $f;
if ($f = $inClause('provider_name', $dfPro))       $where[] = $f;
if ($f = $inClause('dos_year',      $dfYr, true))  $where[] = $f;
if ($f = $inClause('dos_month',     $dfMo, true))  $where[] = $f;

/* Specialty page filter carries through to the drill too (scopes charge_code to the
   specialty's CPT set). Match relies on charge_code being stored uppercase (see
   collection.php note). Guarded so a missing table simply drops the filter. */
$dfSpec = trim((string)($_GET['f_specialty'] ?? ''));
if ($dfSpec !== '') {
    try {
        $pdo->query("SELECT 1 FROM ar_cpt_specialty LIMIT 1");
        $where[]  = "charge_code IN (SELECT cpt_code FROM ar_cpt_specialty WHERE specialty = ?)";
        $params[] = $dfSpec;
    } catch (Throwable $t) { /* table absent → ignore */ }
}

/* Deductible page filter carries through to the drill (matches collection.php). Guarded so a
   report whose `deductible` column isn't provisioned yet simply drops the filter. */
$dfDed = (string)($_GET['f_deductible'] ?? '');
if ($dfDed === '0' || $dfDed === '1') {
    try {
        if (function_exists('app_col_exists') && app_col_exists($pdo, 'collection_raw', 'deductible')) {
            $where[]  = 'deductible = ?';
            $params[] = (int)$dfDed;
        }
    } catch (Throwable $t) { /* column absent → ignore */ }
}

$whereSql = implode(' AND ', $where);

try {
    if (in_array($level, ['year', 'month', 'facility'], true)) {
        $isFac = ($level === 'facility');
        $col   = $isFac ? "COALESCE(facility,'(unmapped)')" : ($level === 'year' ? 'dos_year' : 'dos_month');
        $tab   = (string) ($_GET['tab'] ?? '');
        // Each tab shows the same metric set as its top-level scorecard row, so the
        // drill reconciles to it. The covering index per dimension keeps this index-only.
        if ($tab === 'cpt') {
            $sel = "COUNT(DISTINCT visit_id) AS claims,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 THEN visit_id END) AS unpaid_claims_n,
                    COUNT(DISTINCT chart_number) AS patients,
                    SUM(charge_amount) AS charge,
                    SUM(expected_payment) AS expected,
                    SUM(primary_ins_paid) AS pri,
                    SUM(secondary_ins_paid) AS sec,
                    SUM(tertiary_ins_paid) AS ter,
                    SUM(patient_payment_amount) AS pat_paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                    SUM(insurance_balance) AS ins_balance,
                    SUM(patient_balance) AS pat_balance,
                    SUM(CASE WHEN primary_ins_paid = 0 THEN 1 ELSE 0 END) AS unpaid_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n,
                    COUNT(DISTINCT CASE WHEN (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS outst_n,
                    SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS prim_outst,
                    SUM(CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS sec_outst";
        } elseif ($tab === 'providers') {
            $sel = "COUNT(DISTINCT visit_id) AS claims,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 THEN visit_id END) AS unpaid_n,
                    COUNT(DISTINCT chart_number) AS patients,
                    COUNT(DISTINCT facility) AS facilities,
                    COUNT(DISTINCT charge_code) AS cpts,
                    SUM(charge_amount) AS charge,
                    SUM(expected_payment) AS expected,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
                    SUM(primary_ins_paid) AS pri,
                    SUM(secondary_ins_paid) AS sec,
                    SUM(tertiary_ins_paid) AS ter,
                    SUM(patient_payment_amount) AS pat_paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                    SUM(insurance_balance) AS ins_balance,
                    SUM(patient_balance) AS pat_balance,
                    AVG(days_to_first_payment) AS avg_days,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n";
        } elseif ($tab === 'summary') {
            $sel = "COUNT(DISTINCT visit_id) AS claims,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                    SUM(charge_amount) AS charge,
                    SUM(primary_ins_paid) AS pri,
                    SUM(secondary_ins_paid) AS sec,
                    SUM(tertiary_ins_paid) AS ter,
                    SUM(patient_payment_amount) AS pat_paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS ins_paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                    SUM(expected_payment) AS expected,
                    SUM(insurance_balance) AS ins_balance,
                    SUM(patient_balance) AS pat_balance,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n,
                    COUNT(DISTINCT CASE WHEN (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS outst_n,
                    SUM(CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS prim_outst,
                    SUM(CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN insurance_balance + patient_balance ELSE 0 END) AS sec_outst";
        } elseif ($tab === 'aging') {
            $sel = "COUNT(DISTINCT visit_id) AS claims,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                    SUM(charge_amount) AS charge,
                    SUM(expected_payment) AS expected,
                    SUM(primary_ins_paid) AS pri,
                    SUM(secondary_ins_paid) AS sec,
                    SUM(tertiary_ins_paid) AS ter,
                    SUM(patient_payment_amount) AS pat_paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                    SUM(insurance_balance) AS ins_balance,
                    SUM(patient_balance) AS pat_balance";
        } else { // carriers (default)
            $sel = "COUNT(DISTINCT visit_id) AS claims,
                    COUNT(DISTINCT chart_number) AS patients,
                    SUM(charge_amount) AS charge,
                    SUM(primary_ins_paid) AS pri,
                    SUM(secondary_ins_paid) AS sec,
                    SUM(tertiary_ins_paid) AS ter,
                    SUM(patient_payment_amount) AS pat_paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS ins_paid,
                    SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid + patient_payment_amount) AS total_paid,
                    SUM(expected_payment) AS expected,
                    SUM(insurance_balance) AS ins_balance,
                    SUM(patient_balance) AS pat_balance,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 THEN visit_id END) AS paid_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS unpaid_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS prim_pend_n,
                    COUNT(DISTINCT CASE WHEN primary_ins_paid > 0 AND secondary_ins_paid = 0 AND (insurance_balance > 0 OR patient_balance > 0) THEN visit_id END) AS sec_pend_n,
                    AVG(days_to_first_payment) AS avg_days";
        }
        $notNull = $isFac ? '1=1' : "$col IS NOT NULL";
        $order   = $isFac ? 'charge DESC' : $col;
        $st = $pdo->prepare("SELECT $col AS bucket, $sel
            FROM collection_raw
            WHERE $whereSql AND $notNull
            GROUP BY $col ORDER BY $order");
        $st->execute($params);
        $out = [];
        foreach ($st->fetchAll() as $r) {
            $o = ['bucket' => $isFac ? (string) $r['bucket'] : (int) $r['bucket']];
            foreach ($r as $k => $v) {
                if ($k === 'bucket') continue;
                $o[$k] = ($v === null) ? null : (is_numeric($v) ? (float) $v : $v);
            }
            $out[] = $o;
        }
        echo json_encode(['ok' => true, 'level' => $level, 'rows' => $out]);
        exit;
    }

    /* Claim (visit) leaf — a small, fast subset (a single month/year, or the
       "recently billed" window); show the highest-total-balance claims first. */
    $LIMIT = (($_GET['recent'] ?? '') === '1' || in_array('recent', $drillStatuses, true)) ? 2000 : 300;
    $cst = $pdo->prepare("SELECT COUNT(DISTINCT visit_id) FROM collection_raw WHERE $whereSql");
    $cst->execute($params);
    $total = (int) $cst->fetchColumn();

    $st = $pdo->prepare("
        SELECT visit_id,
               COALESCE(MAX(facility),'') AS facility,
               COALESCE(MAX(provider_name),'') AS provider,
               MAX(dos) AS dos,
               MAX(entry_date) AS entry_date,
               SUM(charge_amount)  AS charge,
               SUM(amount_allowed) AS allowed,
               SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS paid,
               SUM(insurance_balance) AS ins_balance,
               SUM(patient_balance)   AS pat_balance
        FROM collection_raw
        WHERE $whereSql
        GROUP BY visit_id
        ORDER BY (SUM(insurance_balance) + SUM(patient_balance)) DESC, charge DESC
        LIMIT $LIMIT");
    $st->execute($params);
    $out = [];
    foreach ($st->fetchAll() as $r) {
        $out[] = [
            'visit'       => (string) $r['visit_id'],
            'facility'    => (string) $r['facility'],
            'provider'    => (string) $r['provider'],
            'dos'         => (string) ($r['dos'] ?? ''),
            'entry_date'  => (string) ($r['entry_date'] ?? ''),
            'charge'      => (float) $r['charge'],
            'allowed'     => (float) $r['allowed'],
            'paid'        => (float) $r['paid'],
            'ins_balance' => (float) $r['ins_balance'],
            'pat_balance' => (float) $r['pat_balance'],
        ];
    }
    echo json_encode(['ok' => true, 'level' => 'claim', 'rows' => $out, 'total' => $total, 'shown' => count($out)]);
} catch (Throwable $t) {
    echo json_encode(['ok' => false, 'error' => $t->getMessage()]);
}
