<?php
/**
 * Slice 8c-Report+ · Revenue vs Cost live margin rollup · end-to-end.
 *
 * Sets up an isolated per-run scenario on the anchor works project:
 *   - inject a phase + CR (MATERIALS 12M)
 *   - source + pay an LPO for 8M   → cost_paid grows by 8M
 *   - hold supplier retention 5% (400k)
 *   - draft + approve an IPC certifying 10M gross → customer retention 5% (500k)
 *   - record a 200k customer-retention release
 * Then asserts revenueSummary() numbers add up correctly and margin_to_date reflects
 * the delta from baseline. Finally verifies view + controller wiring.
 */
define('DS', DIRECTORY_SEPARATOR);
define('ROOT', dirname(__DIR__));
require_once ROOT . DS . 'config' . DS . 'config.php';
require_once ROOT . DS . 'core' . DS . 'Application.php';
require_once ROOT . DS . 'core' . DS . 'Model.php';
require_once ROOT . DS . 'core' . DS . 'DB.php';
$_SESSION = $_SESSION ?? [];
foreach ([
    'WorksProjectReport.php','WorksIpcs.php','WorksRetention.php','WorksCustomerRetention.php',
    'WorksCrs.php','WorksPhases.php','WorksCrFulfilment.php','WorksBoq.php',
    'PaymentVouchers.php','Lpos.php','Sales.php',
] as $m) {
    require_once ROOT . DS . 'app' . DS . 'models' . DS . $m;
}

function h(string $t): void { echo "\n==== $t ====\n"; }
function p(string $t): void { echo "  $t\n"; }
function ok(bool $c, string $m): void { echo ($c ? "  ✓ " : "  ✗ ") . $m . "\n"; }
function boom(string $why): void { echo "\nFATAL: $why\n"; exit(1); }

$db   = new DB();
$wpr  = new WorksProjectReport();
$wi   = new WorksIpcs();
$wcr  = new WorksCustomerRetention();
$wr   = new WorksRetention();
$wc   = new WorksCrs();
$wp   = new WorksPhases();
$wf   = new WorksCrFulfilment();
$pvs  = new PaymentVouchers();

$pcId = 100; $pcNm = 'Verify PC'; $pcG = 'Projects Coordinator';
$fmId = 3;   $fmNm = 'Verify FM';

/* ── 0. Anchor + backup ────────────────────────────────────────────── */
h('0. Pick anchor + backup state');
$anchor = $db->mySelect(
    "SELECT p.c0d, p.project_no, p.project_customer_id, p.crm_status
       FROM c_projects p JOIN works_boqs b ON b.project_id = p.c0d AND b.deleted = 0
      WHERE p.project_type='works' AND p.deleted=0 ORDER BY p.c0d ASC LIMIT 1",
    []
);
if (empty($anchor)) boom('No works project with a BOQ. Run slice8cSpine_e2e_verify.php first.');
$prjId       = (int)$anchor[0]['c0d'];
$prevCustId  = (int)$anchor[0]['project_customer_id'];
$prevCrm     = (string)$anchor[0]['crm_status'];
$prevTerms   = $wr->termsFor($prjId) ?: ['pct' => 0.0, 'dlp_days' => 365];
p("Project #$prjId · customer_id=$prevCustId · crm=$prevCrm · retention_pct={$prevTerms['pct']}");

/* ── 0b. Prime + purge prior artefacts ──────────────────────────────── */
h('0b. Prime + purge');
$db->insertUpdate("UPDATE c_projects SET project_customer_id = 1, crm_status = 'APPROVED' WHERE c0d = :p", [':p' => $prjId]);
$wr->setTerms($prjId, 5.00, 90);

// Purge prior Report+ verify phases + their CRs/LPOs/payments/IPCs/releases
$oldPhases = $db->mySelect("SELECT c0d FROM works_phases WHERE project_id = :p AND title LIKE 'ReportPlusVerify %'", [':p' => $prjId]) ?: [];
foreach ($oldPhases as $op) {
    $phId = (int)$op['c0d'];
    $crs = $db->mySelect("SELECT c0d FROM works_crs WHERE phase_id = :p", [':p' => $phId]) ?: [];
    foreach ($crs as $cr) {
        $cid = (int)$cr['c0d'];
        $lpos = $db->mySelect("SELECT c0d FROM lpos WHERE source_cr_id = :c", [':c' => $cid]) ?: [];
        foreach ($lpos as $lp) {
            $lid = (int)$lp['c0d'];
            $pays = $db->mySelect("SELECT c0d FROM payments p WHERE EXISTS (SELECT 1 FROM payment_allocations a WHERE a.payment_id=p.c0d AND a.lpo_id=:l)", [':l' => $lid]) ?: [];
            foreach ($pays as $pay) {
                $db->insertUpdate("DELETE FROM works_retention_holdings WHERE payment_id = :p", [':p' => (int)$pay['c0d']]);
                $db->insertUpdate("DELETE FROM payment_allocations WHERE payment_id = :p", [':p' => (int)$pay['c0d']]);
                $db->insertUpdate("DELETE FROM journal_lines WHERE journal_entry_id IN (SELECT c0d FROM journal_entries WHERE source_type='PAYMENT' AND source_id=:p)", [':p' => (int)$pay['c0d']]);
                $db->insertUpdate("DELETE FROM journal_entries WHERE source_type='PAYMENT' AND source_id = :p", [':p' => (int)$pay['c0d']]);
                $db->insertUpdate("DELETE FROM payments WHERE c0d = :p", [':p' => (int)$pay['c0d']]);
            }
            $db->insertUpdate("DELETE FROM payment_vouchers WHERE lpo_id = :l", [':l' => $lid]);
            $db->insertUpdate("DELETE FROM lpo_items WHERE lpo_id = :l", [':l' => $lid]);
            $db->insertUpdate("DELETE FROM lpos      WHERE c0d   = :l", [':l' => $lid]);
        }
        $db->insertUpdate("DELETE FROM works_phase_items_issued WHERE cr_id = :c", [':c' => $cid]);
        $db->insertUpdate("DELETE FROM works_cr_closure_variance WHERE cr_id = :c", [':c' => $cid]);
        foreach (WorksCrs::PILLARS as $pil => $meta) {
            $db->insertUpdate("DELETE FROM `{$meta['table']}` WHERE cr_id = :c", [':c' => $cid]);
        }
        $db->insertUpdate("DELETE FROM works_cr_activities WHERE cr_id = :c", [':c' => $cid]);
        $db->insertUpdate("DELETE FROM works_crs WHERE c0d = :c", [':c' => $cid]);
    }
    $db->insertUpdate("DELETE FROM works_phase_activities WHERE phase_id = :p", [':p' => $phId]);
    $db->insertUpdate("DELETE FROM works_phase_boq_items  WHERE phase_id = :p", [':p' => $phId]);
    $db->insertUpdate("DELETE FROM works_phases WHERE c0d = :p", [':p' => $phId]);
}
// Purge prior Report+ IPCs + their sales
$oldIpcs = $db->mySelect("SELECT c0d, sale_id FROM works_ipcs WHERE project_id = :p AND notes LIKE 'ReportPlusVerify %'", [':p' => $prjId]) ?: [];
foreach ($oldIpcs as $oi) {
    $id  = (int)$oi['c0d']; $sid = (int)$oi['sale_id'];
    $db->insertUpdate("DELETE FROM works_ipc_lines WHERE ipc_id = :i", [':i' => $id]);
    $db->insertUpdate("DELETE FROM works_ipcs WHERE c0d = :i", [':i' => $id]);
    if ($sid > 0) {
        $db->insertUpdate("DELETE FROM sales_items WHERE sale_id = :s", [':s' => $sid]);
        $db->insertUpdate("DELETE FROM sales      WHERE c0d     = :s", [':s' => $sid]);
    }
}
$db->insertUpdate("DELETE FROM works_customer_retention_releases WHERE project_id = :p AND notes LIKE 'ReportPlusVerify %'", [':p' => $prjId]);
// Stray drafts
$stray = $db->mySelect("SELECT c0d FROM works_ipcs WHERE project_id = :p AND status IN ('DRAFT','SUBMITTED')", [':p' => $prjId]) ?: [];
foreach ($stray as $s) {
    $db->insertUpdate("DELETE FROM works_ipc_lines WHERE ipc_id = :i", [':i' => (int)$s['c0d']]);
    $db->insertUpdate("DELETE FROM works_ipcs WHERE c0d = :i", [':i' => (int)$s['c0d']]);
}
p("Purged " . count($oldPhases) . " prior phases, " . count($oldIpcs) . " prior IPCs, " . count($stray) . " stray drafts");

/* ── 0c. Baselines ─────────────────────────────────────────────────── */
h('0c. Snapshot baselines');
$baseRev = $wpr->revenueSummary($prjId);
p(sprintf("baseline cert=%s paid=%s cust_held=%s supp_held=%s margin=%s",
    number_format($baseRev['revenue_certified']), number_format($baseRev['cost_paid']),
    number_format($baseRev['customer_retention_held']), number_format($baseRev['supplier_retention_held']),
    number_format($baseRev['margin_to_date'])));

/* ── 1. Cost side: create phase + CR + LPO paid for 8M ───────────── */
h('1. Cost side: pay an 8M LPO (5% supplier retention → 400k held)');
$phId = $wp->create($prjId, [
    'code' => 'RP-' . date('His'),
    'title' => 'ReportPlusVerify ' . date('YmdHis'),
    'planned_start' => date('Y-m-d'),
    'planned_end'   => date('Y-m-d', strtotime('+30 days')),
], $pcId);
$crId = $wc->draft($prjId, $phId, ['title' => 'ReportPlusVerify CR ' . date('His')], $pcId, $pcNm);
$wc->addLine($crId, 'MATERIALS', ['description' => 'Diesel', 'qty' => 4000, 'unit' => 'L', 'unit_cost' => 3000]);
$wc->addActivity($crId, ['title' => 'Access track', 'unit' => 'm²', 'planned_qty' => 5000]);
$db->insertUpdate("UPDATE works_crs SET status='APPROVED' WHERE c0d=:id", [':id'=>$crId]);
$wc->activate($crId);
$lpoRes = $wf->draftLpoFromCrLines(
    $crId, 'MATERIALS',
    ['vendor_id' => 1, 'vendor_name' => 'Furniture Direct Ltd', 'currency' => 'TZS'],
    [ ['line_id' => (int)$wf->sourceableLines($crId)['MATERIALS'][0]['line_id'], 'qty' => 4000, 'unit_price' => 2000] ], // 8M
    $pcId, $pcNm
);
if (!$lpoRes['ok']) boom('LPO draft failed: ' . ($lpoRes['msg'] ?? '?'));
$lpoId = (int)$lpoRes['id'];
$db->insertUpdate("UPDATE lpos SET cash_out_status='APPROVED' WHERE c0d=:id", [':id'=>$lpoId]);
$prep = $pvs->prepFromLpo($lpoId, $fmId, $fmNm, 'ReportPlus verify');
if (!$prep['ok']) boom('prepFromLpo failed: ' . ($prep['msg'] ?? '?'));
$sch = $pvs->schedule((int)$prep['id'], date('Y-m-d', strtotime('+1 day')), 1, $fmId, $fmNm, '');
if (!$sch['ok']) boom('schedule failed: ' . ($sch['msg'] ?? '?'));
$pay = $pvs->recordPayment((int)$prep['id'], [
    'payment_method' => 'BANK_TRANSFER', 'reference_no' => 'RP-' . date('His'),
    'payment_date' => date('Y-m-d'), 'bank_account_id' => 1,
], $fmId);
if (!$pay['ok']) boom('recordPayment failed: ' . ($pay['msg'] ?? '?'));
p("Payment {$pay['payment_no']} · 8M · supplier retention auto-held 5% = 400k");

/* ── 2. Revenue side: certify + approve IPC for 10M ────────────────── */
h('2. Revenue side: approve IPC 10M (5% customer retention → 500k held)');
$boq = (new WorksBoq())->findByProject($prjId);
$boqItems = (new WorksBoq())->itemsFor((int)$boq['c0d']);
$ipcDr = $wi->draft($prjId, ['notes' => 'ReportPlusVerify ' . date('YmdHis')], $pcId, $pcNm);
if (!$ipcDr['ok']) boom('IPC draft failed: ' . ($ipcDr['msg'] ?? '?'));
$ipcId = (int)$ipcDr['id'];
// Set qty on first line to certify exactly 10M gross
$lines = $wi->linesFor($ipcId);
$targetLine = null;
$targetQty  = 0;
foreach ($lines as $l) {
    $r = (float)$l['contract_rate'];
    if ($r > 0) {
        $needed = round(10000000 / $r, 4);
        if ($needed <= (float)$l['contract_qty']) {
            $targetLine = $l;
            $targetQty  = $needed;
            break;
        }
    }
}
if (!$targetLine) boom('No BOQ line has capacity for 10M certification');
$setR = $wi->setLineQty($ipcId, (int)$targetLine['c0d'], $targetQty);
if (!$setR['ok']) boom('setLineQty failed: ' . ($setR['msg'] ?? '?'));
$ipc = $wi->findById($ipcId);
p("IPC {$ipcDr['ipc_no']} · certified=" . number_format((float)$ipc['certified_gross']));
$appR = $wi->approve($ipcId, $pcId, $pcNm, $pcG);
if (!$appR['ok']) boom('IPC approve failed: ' . ($appR['msg'] ?? '?'));

/* ── 3. Customer retention release: 200k ─────────────────────────── */
h('3. Customer retention release 200k');
$relR = $wcr->recordRelease($prjId, 200000, date('Y-m-d'), 'ReportPlusVerify partial', 0, $fmId, $fmNm);
ok($relR['ok'], 'partial release recorded');

/* ── 4. revenueSummary numbers ─────────────────────────────────── */
h('4. revenueSummary numbers');
$rev = $wpr->revenueSummary($prjId);
p(sprintf("cert=%s inv=%s cost_paid=%s supp_held=%s cust_held=%s margin=%s eff_cash=%s",
    number_format($rev['revenue_certified']), number_format($rev['revenue_invoiced']),
    number_format($rev['cost_paid']), number_format($rev['supplier_retention_held']),
    number_format($rev['customer_retention_pool']),
    number_format($rev['margin_to_date']), number_format($rev['effective_cash'])));

// Deltas from baseline
$dCert  = $rev['revenue_certified']   - $baseRev['revenue_certified'];
$dInv   = $rev['revenue_invoiced']    - $baseRev['revenue_invoiced'];
$dPaid  = $rev['cost_paid']           - $baseRev['cost_paid'];
$dCust  = $rev['customer_retention_held']    - $baseRev['customer_retention_held'];
$dSupp  = $rev['supplier_retention_held']    - $baseRev['supplier_retention_held'];
$dRel   = $rev['customer_retention_released']- $baseRev['customer_retention_released'];
$dMargin= $rev['margin_to_date']      - $baseRev['margin_to_date'];

// ± 1 TZS tolerance — the target-qty computation for the IPC picks a fractional qty
// (10M / contract_rate) that can drift up to a whole shilling on round-trip.
ok(abs($dCert  - 10000000) < 1.0, "delta revenue_certified ≈ 10M");
ok(abs($dPaid  -  8000000) < 1.0, "delta cost_paid           = 8M");
ok(abs($dCust  -   500000) < 1.0, "delta customer_retention_held ≈ 500k");
ok(abs($dSupp  -   400000) < 1.0, "delta supplier_retention_held = 400k");
ok(abs($dRel   -   200000) < 1.0, "delta customer_retention_released = 200k");
ok(abs($dInv   -  9500000) < 1.0, "delta revenue_invoiced ≈ 9.5M (10M − 500k retention)");
ok(abs($dMargin -  2000000) < 1.0, "delta margin_to_date ≈ 2M (10M − 8M)");
ok(($rev['ipcs_count'] - $baseRev['ipcs_count']) === 1, 'IPCs count +1');

// Effective cash = revenue_invoiced - cost_paid + supplier_held - customer_still_held
$expEffCash = (float)$rev['revenue_invoiced'] - (float)$rev['cost_paid']
            + (float)$rev['supplier_retention_held'] - (float)$rev['customer_retention_pool'];
ok(abs((float)$rev['effective_cash'] - $expEffCash) < 0.01, 'effective_cash formula holds');

/* ── 5. Margin pct math ─────────────────────────────────────────── */
h('5. margin_pct_to_date correctness');
$expPct = $rev['revenue_certified'] > 0
    ? round($rev['margin_to_date'] / $rev['revenue_certified'] * 100, 1)
    : 0.0;
ok(abs($rev['margin_pct_to_date'] - $expPct) < 0.1, "margin_pct = margin / certified * 100 = $expPct%");

/* ── 6. Wiring / view surface ───────────────────────────────────── */
h('6. Wiring / view surface');
$viewBody = (string)file_get_contents(ROOT . DS . 'app' . DS . 'views' . DS . 'WorksProjectReport' . DS . 'view.php');
ok(strpos($viewBody, "\$this->data['revenue']")   !== false, 'view pulls revenue from controller data');
ok(strpos($viewBody, 'Revenue vs Cost · live margin') !== false, 'view has Revenue vs Cost card');
ok(strpos($viewBody, 'margin_to_date')             !== false, 'view references margin_to_date');
ok(strpos($viewBody, 'effective_cash')             !== false, 'view references effective_cash');
$ctrlBody = (string)file_get_contents(ROOT . DS . 'app' . DS . 'controllers' . DS . 'WorksProjectReportController.php');
ok(strpos($ctrlBody, "'revenue'")                  !== false, 'controller passes revenue');
ok(strpos($ctrlBody, 'revenueSummary($projectId)') !== false, 'controller calls revenueSummary');

/* ── Z. Restore anchor state ────────────────────────────────────── */
h('Z. Restore anchor state');
$db->insertUpdate("DELETE FROM works_customer_retention_releases WHERE project_id = :p AND notes LIKE 'ReportPlusVerify %'", [':p' => $prjId]);
// Delete this run's IPC + its sales so downstream verifies see a clean anchor.
$saleIdZ = (int)($wi->findById($ipcId)['sale_id'] ?? 0);
$db->insertUpdate("DELETE FROM works_ipc_lines WHERE ipc_id = :i", [':i' => $ipcId]);
$db->insertUpdate("DELETE FROM works_ipcs      WHERE c0d   = :i", [':i' => $ipcId]);
if ($saleIdZ > 0) {
    $db->insertUpdate("DELETE FROM sales_items WHERE sale_id = :s", [':s' => $saleIdZ]);
    $db->insertUpdate("DELETE FROM sales      WHERE c0d     = :s", [':s' => $saleIdZ]);
}
$db->insertUpdate("UPDATE c_projects SET project_customer_id = :cid, crm_status = :cs WHERE c0d = :p",
    [':cid' => $prevCustId, ':cs' => $prevCrm, ':p' => $prjId]);
$wr->setTerms($prjId, (float)$prevTerms['pct'], (int)$prevTerms['dlp_days']);
p("Restored: customer_id=$prevCustId · crm=$prevCrm · retention_pct={$prevTerms['pct']} · deleted IPC+sale");

echo "\n=== END ===\n";
