<?php
/**
 * Exports for a report.
 *   format=xlsx_legacy    multi-sheet workbook matching Sample_Report.xlsx shape
 *   format=csv_dashboard  monthly summary CSV
 *   format=csv            generic per-output CSV (legacy mode if output=0)
 *   format=print          print-friendly per-output (Save as PDF)
 *   format=print_legacy   print-friendly Dashboard layout
 */
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/xlsx-writer.php';
$me = require_login('internal');

$pdo = db();
$reportId = (int) ($_GET['id'] ?? 0);
$outputId = (int) ($_GET['output'] ?? 0);
$format   = $_GET['format'] ?? 'csv';

$rep = $pdo->prepare('SELECT r.*, c.name AS cname FROM reports r
    JOIN processes p ON p.id = r.process_id JOIN clients c ON c.id = p.client_id
    WHERE r.id=? AND r.deleted_at IS NULL');
$rep->execute([$reportId]);
$report = $rep->fetch();
if (!$report) { http_response_code(404); exit('Report not found.'); }

function load_active_rows(PDO $pdo, int $reportId): array
{
    $stmt = $pdo->prepare('SELECT data_json, period_label FROM report_data WHERE report_id=? AND is_active=1');
    $stmt->execute([$reportId]);
    $rows = [];
    while ($r = $stmt->fetch()) {
        $row = json_decode($r['data_json'], true) ?: [];
        $row['_period'] = $r['period_label'];
        $rows[] = $row;
    }
    return $rows;
}

function aggregate_monthly(array $rows): array
{
    $monthBuckets = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
    $by = [];
    foreach ($rows as $r) {
        $dos = $r['dos'] ?? null;
        if (!$dos) continue;
        $ts = strtotime($dos); if ($ts === false) continue;
        $m = date('M', $ts);
        if (!isset($by[$m])) $by[$m] = ['claims'=>0,'paid'=>0,'unpaid'=>0,'charge'=>0,'pri'=>0,'sec'=>0,'ter'=>0,'pat_paid'=>0,'bal'=>0,'pat_bal'=>0,'exp'=>0];
        $by[$m]['claims']++;
        $by[$m]['charge']   += (float) ($r['charge_amount'] ?? 0);
        $by[$m]['pri']      += (float) ($r['primary_insurance_paid'] ?? 0);
        $by[$m]['sec']      += (float) ($r['secondary_insurance_paid'] ?? 0);
        $by[$m]['ter']      += (float) ($r['tertiary_insurance_paid'] ?? 0);
        $by[$m]['pat_paid'] += (float) ($r['patient_payment_amount'] ?? 0);
        $by[$m]['bal']      += (float) ($r['insurance_balance'] ?? 0);
        $by[$m]['pat_bal']  += (float) ($r['patient_balance'] ?? 0);
        $by[$m]['exp']      += (float) ($r['amount_allowed'] ?? 0);
        if ((float)($r['primary_insurance_paid'] ?? 0) > 0) $by[$m]['paid']++; else $by[$m]['unpaid']++;
    }
    uksort($by, fn($a,$b) => array_search($a, $monthBuckets) <=> array_search($b, $monthBuckets));
    return $by;
}

function totals_of(array $byMonth): array
{
    $t = ['claims'=>0,'paid'=>0,'unpaid'=>0,'charge'=>0,'pri'=>0,'sec'=>0,'ter'=>0,'pat_paid'=>0,'bal'=>0,'pat_bal'=>0,'exp'=>0];
    foreach ($byMonth as $m) foreach ($t as $k=>$v) $t[$k] += $m[$k];
    return $t;
}

$baseFilename = preg_replace('/[^A-Za-z0-9_-]/', '_',
    $report['code'] . '_' . date('Ymd_His'));

/* ---------------- legacy multi-sheet xlsx ---------------- */
if ($format === 'xlsx_legacy') {
    $rows = load_active_rows($pdo, $reportId);
    $byMonth = aggregate_monthly($rows);
    $tot = totals_of($byMonth);

    $w = new XlsxWriter();

    // Sheet 1: Dashboard (matches legacy)
    $dash = [];
    $dash[] = [strtoupper($report['cname']) . ' OVERALL REVENUE SUMMARY REPORT'];
    $dash[] = [];
    $dash[] = ['Month','Total Claims Billed','Total Charge Amount','Primary Insurance Paid','Secondary Insurance Paid','Insurance Balance','Patient Balance','Total Expected Payment'];
    foreach ($byMonth as $m => $v) {
        $dash[] = [$m, $v['claims'], round($v['charge'],2), round($v['pri'],2), round($v['sec'],2), round($v['bal'],2), round($v['pat_bal'],2), round($v['exp'],2)];
    }
    $dash[] = ['TOTAL', $tot['claims'], round($tot['charge'],2), round($tot['pri'],2), round($tot['sec'],2), round($tot['bal'],2), round($tot['pat_bal'],2), round($tot['exp'],2)];
    $w->addSheet('Dashboard', $dash, false, false);

    // Sheet 2: Summary Report (wider table)
    $sum = [];
    $sum[] = [strtoupper($report['cname']) . ' ALL FACILITIES REVENUE AND BALANCES SUMMARY'];
    $sum[] = [];
    $sum[] = ['Month','Total Claims Billed','Total Paid Claims','Total Unpaid Claims',
              'Total Charge Amount','Primary Insurance Paid','Secondary Insurance Paid',
              'Total Paid','$ per Paid Claim','$ per Claim (Overall)','Insurance Balance','Patient Balance','Total Expected Payment'];
    foreach ($byMonth as $m => $v) {
        $totPaid    = ($v['pri'] ?? 0)+($v['sec'] ?? 0)+($v['ter'] ?? 0)+($v['pat_paid'] ?? 0);
        $perPaid    = $v['paid']   > 0 ? round($totPaid / $v['paid'],   2) : 0;
        $perOverall = $v['claims'] > 0 ? round($totPaid / $v['claims'], 2) : 0;
        $sum[] = [$m, $v['claims'], $v['paid'], $v['unpaid'],
                  round($v['charge'],2), round($v['pri'],2), round($v['sec'],2),
                  round($totPaid,2), $perPaid, $perOverall, round($v['bal'],2), round($v['pat_bal'],2), round($v['exp'],2)];
    }
    $tPaid = ($tot['pri'] ?? 0) + ($tot['sec'] ?? 0) + ($tot['ter'] ?? 0) + ($tot['pat_paid'] ?? 0);
    $sum[] = ['TOTAL', $tot['claims'], $tot['paid'], $tot['unpaid'],
              round($tot['charge'],2), round($tot['pri'],2), round($tot['sec'],2),
              round($tPaid,2),
              $tot['paid']   > 0 ? round($tPaid/$tot['paid'],   2) : 0,
              $tot['claims'] > 0 ? round($tPaid/$tot['claims'], 2) : 0,
              round($tot['bal'],2), round($tot['pat_bal'],2), round($tot['exp'],2)];
    $w->addSheet('Summary Report', $sum, false, false);

    // Sheet 3: Look Up Data (the pivot)
    $lk = [['Row Labels','Count of DOS','Sum of Charge Amount','Sum of Primary Insurance Paid','Sum of Secondary Insurance Paid','Sum of Insurance Balance']];
    $pivot = [];
    foreach ($rows as $r) {
        $f = $r['facility'] ?? '(unknown)'; $dos = $r['dos'] ?? null; if (!$dos) continue;
        $ts = strtotime($dos); $m = date('M', $ts); $d = date('j-M', $ts);
        $pivot[$f][$m][$d][] = $r;
    }
    ksort($pivot);
    foreach ($pivot as $facility => $months) {
        $fT = ['v'=>0,'c'=>0,'p'=>0,'s'=>0,'b'=>0];
        foreach ($months as $mr) foreach ($mr as $dr) foreach ($dr as $r) {
            $fT['v']++; $fT['c']+=(float)($r['charge_amount']??0); $fT['p']+=(float)($r['primary_insurance_paid']??0);
            $fT['s']+=(float)($r['secondary_insurance_paid']??0); $fT['b']+=(float)($r['insurance_balance']??0);
        }
        $lk[] = [$facility, $fT['v'], round($fT['c'],2), round($fT['p'],2), round($fT['s'],2), round($fT['b'],2)];
        $monthBuckets = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
        uksort($months, fn($a,$b)=>array_search($a, $monthBuckets) <=> array_search($b, $monthBuckets));
        foreach ($months as $m => $days) {
            $mT = ['v'=>0,'c'=>0,'p'=>0,'s'=>0,'b'=>0];
            foreach ($days as $dr) foreach ($dr as $r) {
                $mT['v']++; $mT['c']+=(float)($r['charge_amount']??0); $mT['p']+=(float)($r['primary_insurance_paid']??0);
                $mT['s']+=(float)($r['secondary_insurance_paid']??0); $mT['b']+=(float)($r['insurance_balance']??0);
            }
            $lk[] = ['  ' . $m, $mT['v'], round($mT['c'],2), round($mT['p'],2), round($mT['s'],2), round($mT['b'],2)];
            uksort($days, fn($a,$b)=>(int)$a<=>(int)$b);
            foreach ($days as $d => $rs) {
                $dT = ['v'=>0,'c'=>0,'p'=>0,'s'=>0,'b'=>0];
                foreach ($rs as $r) {
                    $dT['v']++; $dT['c']+=(float)($r['charge_amount']??0); $dT['p']+=(float)($r['primary_insurance_paid']??0);
                    $dT['s']+=(float)($r['secondary_insurance_paid']??0); $dT['b']+=(float)($r['insurance_balance']??0);
                }
                $lk[] = ['    ' . $d, $dT['v'], round($dT['c'],2), round($dT['p'],2), round($dT['s'],2), round($dT['b'],2)];
            }
        }
    }
    $w->addSheet('Look Up Data', $lk);

    // Sheet 4: Overall Raw Data
    $raw = [];
    $cols = $pdo->prepare('SELECT col_name, col_label FROM report_columns WHERE report_id=? ORDER BY col_order');
    $cols->execute([$reportId]); $cols = $cols->fetchAll();
    $raw[] = array_column($cols, 'col_label');
    foreach ($rows as $r) {
        $line = [];
        foreach ($cols as $c) $line[] = $r[$c['col_name']] ?? '';
        $raw[] = $line;
    }
    $w->addSheet('Overall Raw Data', $raw);

    $tmp = sys_get_temp_dir() . '/' . $baseFilename . '.xlsx';
    $w->save($tmp);
    download_log($me['id'], $reportId, null, "$baseFilename.xlsx", 'xlsx');
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment; filename=\"$baseFilename.xlsx\"");
    header('Content-Length: ' . filesize($tmp));
    readfile($tmp);
    @unlink($tmp);
    exit;
}

/* ---------------- CSV: monthly dashboard ---------------- */
if ($format === 'csv_dashboard') {
    $rows = load_active_rows($pdo, $reportId);
    $byMonth = aggregate_monthly($rows);
    $tot = totals_of($byMonth);
    download_log($me['id'], $reportId, null, "$baseFilename.csv", 'csv');
    header('Content-Type: text/csv; charset=utf-8');
    header("Content-Disposition: attachment; filename=\"$baseFilename.csv\"");
    $fh = fopen('php://output','w');
    fwrite($fh, "\xEF\xBB\xBF");
    fputcsv($fh, ['Month','Total Claims','Total Charge','Primary Paid','Secondary Paid','Insurance Balance','Patient Balance','Total Expected Payment']);
    foreach ($byMonth as $m => $v) fputcsv($fh, [$m, $v['claims'], $v['charge'], $v['pri'], $v['sec'], $v['bal'], $v['pat_bal'], $v['exp']]);
    fputcsv($fh, ['TOTAL', $tot['claims'], $tot['charge'], $tot['pri'], $tot['sec'], $tot['bal'], $tot['pat_bal'], $tot['exp']]);
    fclose($fh); exit;
}

/* ---------------- Print legacy ---------------- */
if ($format === 'print_legacy') {
    $rows = load_active_rows($pdo, $reportId);
    $byMonth = aggregate_monthly($rows);
    $tot = totals_of($byMonth);
    download_log($me['id'], $reportId, null, "$baseFilename.pdf", 'pdf');
?><!doctype html><html><head><meta charset="utf-8"><title><?= e($report['name']) ?></title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; color: #222; }
h1 { color: #1f4570; margin-bottom: 4px; }
.meta { color: #777; font-size: 11px; margin-bottom: 14px; }
table { width:100%; border-collapse:collapse; font-size:12px; margin-bottom:16px; }
th, td { border: 1px solid #888; padding: 6px 8px; }
th { background: #1f4570; color: #fff; text-align: center; }
td.num { text-align: right; font-variant-numeric: tabular-nums; }
tr.tot { background: #fff8e1; font-weight: 700; }
@media print { .noprint { display:none; } }
</style></head><body>
<button class="noprint" onclick="window.print()" style="float:right;padding:8px 14px;background:#1f4570;color:#fff;border:0;cursor:pointer">Print or save as PDF</button>
<h1><?= e(strtoupper($report['cname'])) ?> &mdash; OVERALL REVENUE SUMMARY REPORT</h1>
<div class="meta">Generated <?= date('Y-m-d H:i') ?> by <?= e($me['name']) ?></div>
<table>
<tr><th>Month</th><th>Total Claims Billed</th><th>Total Charge Amount</th>
    <th>Primary Insurance Paid</th><th>Secondary Insurance Paid</th>
    <th>Insurance Balance</th><th>Patient Balance</th><th>Total Expected Payment</th></tr>
<?php foreach ($byMonth as $m => $v): ?>
<tr><td><strong><?= e($m) ?></strong></td>
    <td class="num"><?= number_format($v['claims']) ?></td>
    <td class="num">$<?= number_format($v['charge'],2) ?></td>
    <td class="num">$<?= number_format($v['pri'],2) ?></td>
    <td class="num">$<?= number_format($v['sec'],2) ?></td>
    <td class="num">$<?= number_format($v['bal'],2) ?></td>
    <td class="num">$<?= number_format($v['pat_bal'],2) ?></td>
    <td class="num">$<?= number_format($v['exp'],2) ?></td>
</tr>
<?php endforeach; ?>
<tr class="tot"><td>TOTAL</td>
    <td class="num"><?= number_format($tot['claims']) ?></td>
    <td class="num">$<?= number_format($tot['charge'],2) ?></td>
    <td class="num">$<?= number_format($tot['pri'],2) ?></td>
    <td class="num">$<?= number_format($tot['sec'],2) ?></td>
    <td class="num">$<?= number_format($tot['bal'],2) ?></td>
    <td class="num">$<?= number_format($tot['pat_bal'],2) ?></td>
    <td class="num">$<?= number_format($tot['exp'],2) ?></td>
</tr>
</table>
</body></html><?php
    exit;
}

/* ---------------- Generic per-output CSV / print (existing modes) ---------------- */
$out = $pdo->prepare('SELECT * FROM report_outputs WHERE id=? AND report_id=?');
$out->execute([$outputId, $reportId]);
$output = $out->fetch();
if (!$output && $outputId !== 0) { http_response_code(404); exit('Output not found.'); }

if ($outputId === 0) { flash('error','Pick a format.'); redirect('/admin/report-view.php?id=' . $reportId); }

$gen = $pdo->prepare('SELECT data_json FROM generated_outputs WHERE report_id=? AND output_id=? ORDER BY generated_at DESC LIMIT 1');
$gen->execute([$reportId, $outputId]);
$row = $gen->fetch();
if (!$row) { http_response_code(404); exit('No data to export.'); }
$data = json_decode($row['data_json'], true);

if ($format === 'csv') {
    download_log($me['id'], $reportId, $outputId, "$baseFilename.csv", 'csv');
    header('Content-Type: text/csv; charset=utf-8');
    header("Content-Disposition: attachment; filename=\"$baseFilename.csv\"");
    $fh = fopen('php://output','w'); fwrite($fh, "\xEF\xBB\xBF");
    if (($output['output_type'] ?? '') === 'detail') {
        $rs = $data['rows'] ?? [];
        if ($rs) { fputcsv($fh, array_keys($rs[0])); foreach ($rs as $r) fputcsv($fh, array_values(array_map(fn($v)=>is_scalar($v)?$v:json_encode($v), $r))); }
    } else {
        $rs = $data['rows'] ?? []; $gb = $data['group_by'] ?? []; $ac = $data['agg_cols'] ?? [];
        $hdr = array_merge($gb, array_map(fn($a)=>$a['label']??$a['col'], $ac));
        fputcsv($fh, $hdr);
        foreach ($rs as $r) {
            $line = []; foreach ($gb as $g) $line[] = $r[$g] ?? '';
            foreach ($ac as $a) { $alias = $a['label'] ?? $a['col']; $line[] = $r[$alias] ?? ''; }
            fputcsv($fh, $line);
        }
    }
    fclose($fh); exit;
}

http_response_code(400); exit('Unsupported format.');
