<?php
/**
 * Slice 8c-Certificate · Interim Payment Certificates · end-to-end.
 *
 * 1) Isolated setup on the anchor works project:
 *    - back up + set project_customer_id, crm_status, works_boqs.retention_pct
 *    - purge prior IPC-verify artefacts
 * 2) Draft IPC → seedLines auto-populates every BOQ item
 * 3) setLineQty on one line → recomputeTotals fires (certified_gross, retention_amount, net_amount, vat_amount, invoice_total)
 * 4) setAdvanceRecovery → totals recompute again
 * 5) Guards: over-certify blocked, DRAFT-blocking blocks a second IPC
 * 6) approve() → sales invoice created via Sales::draft; sale_id stamped on IPC
 * 7) Second IPC on same project: previous_qty pre-populated from prior APPROVED IPC
 * 8) totalCertifiedFor rollup correct
 * 9) Perms + view + wiring surface checks
 * Z) Restore project state (customer_id, crm_status, retention_pct)
 */
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 (['WorksIpcs.php','WorksBoq.php','WorksRetention.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();
$wi = new WorksIpcs();
$wb = new WorksBoq();
$wr = new WorksRetention();

$pcId = 100; $pcNm = 'Verify PC'; $pcG = 'Projects Coordinator';

/* ── 0. Anchor + backup ─────────────────────────────────────────────── */
h('0. Pick anchor works project + backup state');
$anchor = $db->mySelect(
    "SELECT p.c0d, p.project_no, p.project_customer_id, p.crm_status, p.migrated_flag
       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'];
$prevMigrate = (int)$anchor[0]['migrated_flag'];
$prevTerms   = $wr->termsFor($prjId) ?: ['pct' => 0.0, 'dlp_days' => 365];
p("Project #$prjId · prev customer_id=$prevCustId crm_status=$prevCrm migrated=$prevMigrate retention_pct={$prevTerms['pct']}");

/* ── 0b. Force project to invoice-ready + 5% retention ─────────────── */
h('0b. Prime project for invoicing');
$db->insertUpdate(
    "UPDATE c_projects SET project_customer_id = 1, crm_status = 'APPROVED' WHERE c0d = :p",
    [':p' => $prjId]
);
$wr->setTerms($prjId, 5.00, 365);
p('customer_id=1 · crm_status=APPROVED · retention_pct=5%');

/* ── 0c. Purge prior IPC-verify artefacts + orphaned sales ─────────── */
h('0c. Purge prior IPC-verify artefacts');
$oldIpcs = $db->mySelect(
    "SELECT c0d, sale_id FROM works_ipcs WHERE project_id = :p AND notes LIKE 'CertVerify %'",
    [':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]);
    }
}
// Also nuke any leftover DRAFT IPCs on the project (from crashed runs)
$otherDraft = $db->mySelect(
    "SELECT c0d FROM works_ipcs WHERE project_id = :p AND status IN ('DRAFT','SUBMITTED')",
    [':p' => $prjId]
) ?: [];
foreach ($otherDraft as $od) {
    $db->insertUpdate("DELETE FROM works_ipc_lines WHERE ipc_id = :i", [':i' => (int)$od['c0d']]);
    $db->insertUpdate("DELETE FROM works_ipcs WHERE c0d = :i", [':i' => (int)$od['c0d']]);
}
p("Purged " . count($oldIpcs) . " prior verify IPCs + " . count($otherDraft) . " stray DRAFT IPCs");

/* ── 1. Draft IPC → auto-seed lines from BOQ ────────────────────────── */
h('1. Draft IPC and auto-seed BOQ lines');
$boq = $wb->findByProject($prjId);
$boqItems = $wb->itemsFor((int)$boq['c0d']);
$boqCount = count($boqItems);
p("BOQ has $boqCount items");

$res = $wi->draft($prjId, [
    'ipc_date'    => date('Y-m-d'),
    'period_from' => date('Y-m-01'),
    'period_to'   => date('Y-m-d'),
    'vat_pct'     => 18.00,
    'notes'       => 'CertVerify ' . date('YmdHis'),
], $pcId, $pcNm);
if (!$res['ok']) boom('draft failed: ' . ($res['msg'] ?? '?'));
$ipcId1 = (int)$res['id'];
p("IPC {$res['ipc_no']} · c0d=$ipcId1");
$ipc1 = $wi->findById($ipcId1);
ok($ipc1['status'] === 'DRAFT',                                     'IPC status DRAFT');
ok(abs((float)$ipc1['retention_pct'] - 5.00) < 0.01,                'retention_pct copied from BOQ (5%)');
ok((int)$ipc1['ipc_seq'] >= 1,                                       'ipc_seq assigned');
ok(!empty($ipc1['ipc_no']) && strpos($ipc1['ipc_no'], 'IPC-') === 0, 'ipc_no formatted IPC-YYYY-P####-##');

$lines1 = $wi->linesFor($ipcId1);
ok(count($lines1) === $boqCount, "seeded $boqCount lines (matches BOQ item count)");
foreach ($lines1 as $l) {
    if ((float)$l['previous_qty'] !== 0.0 && (float)$l['previous_qty'] !== 0) { ok(false, 'line ' . $l['item_no'] . ' previous_qty=' . $l['previous_qty']); return; }
}
ok(true, 'all seeded lines start with previous_qty = 0');

/* ── 2. setLineQty on one line → recompute totals ───────────────────── */
h('2. setLineQty triggers recomputeTotals');
$firstLine = $lines1[0];
$rate      = (float)$firstLine['contract_rate'];
$thisQty   = min(100, (float)$firstLine['contract_qty']); // certify some of the line
$expectedGross = round($thisQty * $rate, 2);
$expectedRet   = round($expectedGross * 5 / 100, 2);
$expectedNet   = round($expectedGross - $expectedRet, 2);
$expectedVat   = round($expectedNet * 18 / 100, 2);
$expectedTotal = round($expectedNet + $expectedVat, 2);
p("Certify qty=$thisQty · rate=" . number_format($rate) . " → gross=" . number_format($expectedGross));

$setRes = $wi->setLineQty($ipcId1, (int)$firstLine['c0d'], $thisQty);
ok($setRes['ok'], 'setLineQty accepted');
$ipc1a = $wi->findById($ipcId1);
p(sprintf("gross=%s ret=%s net=%s vat=%s total=%s",
    number_format((float)$ipc1a['certified_gross']),
    number_format((float)$ipc1a['retention_amount']),
    number_format((float)$ipc1a['net_amount']),
    number_format((float)$ipc1a['vat_amount']),
    number_format((float)$ipc1a['invoice_total'])
));
ok(abs((float)$ipc1a['certified_gross']  - $expectedGross) < 0.01, 'certified_gross correct');
ok(abs((float)$ipc1a['retention_amount'] - $expectedRet)   < 0.01, 'retention_amount = gross * 5%');
ok(abs((float)$ipc1a['net_amount']       - $expectedNet)   < 0.01, 'net_amount = gross - retention');
ok(abs((float)$ipc1a['vat_amount']       - $expectedVat)   < 0.01, 'vat_amount = net * 18%');
ok(abs((float)$ipc1a['invoice_total']    - $expectedTotal) < 0.01, 'invoice_total = net + vat');

/* ── 3. setAdvanceRecovery adjusts net ─────────────────────────────── */
h('3. setAdvanceRecovery adjusts net downward');
$adv = min(100000, $expectedNet / 4);
$advRes = $wi->setAdvanceRecovery($ipcId1, $adv);
ok($advRes['ok'], 'setAdvanceRecovery accepted');
$ipc1b = $wi->findById($ipcId1);
$expectedNet2 = round($expectedGross - $expectedRet - $adv, 2);
ok(abs((float)$ipc1b['net_amount'] - $expectedNet2) < 0.01, 'net_amount adjusted for advance recovery');

/* ── 4. Over-certify guard ──────────────────────────────────────────── */
h('4. Over-certify guard');
$overQty = (float)$firstLine['contract_qty'] + 1;
$over = $wi->setLineQty($ipcId1, (int)$firstLine['c0d'], $overQty);
ok(!$over['ok'] && stripos($over['msg'], 'Over-certify') !== false, 'over-certify blocked');

/* ── 5. Duplicate DRAFT guard ──────────────────────────────────────── */
h('5. Duplicate DRAFT guard');
$dup = $wi->draft($prjId, ['notes' => 'CertVerify dup ' . date('His')], $pcId, $pcNm);
ok(!$dup['ok'] && stripos($dup['msg'], 'still') !== false, 'blocks second draft while first still DRAFT');

/* ── 6. Approve IPC → sales invoice raised ─────────────────────────── */
h('6. approve() creates sales invoice');
$appRes = $wi->approve($ipcId1, $pcId, $pcNm, $pcG);
ok($appRes['ok'], 'approve accepted: ' . $appRes['msg']);
$saleId = (int)$appRes['sale_id'];
ok($saleId > 0, "sale_id populated: $saleId");

$ipc1c = $wi->findById($ipcId1);
ok($ipc1c['status'] === 'APPROVED',                    'IPC flipped to APPROVED');
ok((int)$ipc1c['sale_id'] === $saleId,                 'ipc.sale_id stamped');
ok((int)$ipc1c['approved_by'] === $pcId,               'approved_by stamped');

$sale = $db->mySelect("SELECT * FROM sales WHERE c0d = :s", [':s' => $saleId]);
ok(!empty($sale),                                       'sales row exists');
ok((int)$sale[0]['project_id']  === $prjId,             'sale.project_id');
ok((int)$sale[0]['customer_id'] === 1,                  'sale.customer_id = 1');
ok((string)$sale[0]['source_module'] === 'works',       'sale.source_module = works');
ok(abs((float)$sale[0]['subtotal'] - $expectedNet2) < 0.01, 'sale.subtotal = IPC.net_amount');
$saleItems = $db->mySelect("SELECT * FROM sales_items WHERE sale_id = :s", [':s' => $saleId]);
ok(count($saleItems) === 1,                             'exactly 1 sales_items row (single-line IPC)');
ok(abs((float)$saleItems[0]['line_total'] - $expectedNet2) < 0.01, 'sales_items line_total = net');

/* ── 7. Idempotent approve ─────────────────────────────────────────── */
h('7. Idempotent approve');
$again = $wi->approve($ipcId1, $pcId, $pcNm, $pcG);
ok($again['ok'] && (int)$again['sale_id'] === $saleId, 'second approve returns same sale_id');

/* ── 8. Second IPC on same project: previous_qty pre-populated ─────── */
h('8. Second IPC: previous_qty carried forward');
$res2 = $wi->draft($prjId, ['notes' => 'CertVerify ' . date('YmdHis') . ' 2'], $pcId, $pcNm);
if (!$res2['ok']) boom('second IPC draft failed: ' . ($res2['msg'] ?? '?'));
$ipcId2 = (int)$res2['id'];
$lines2 = $wi->linesFor($ipcId2);
// Find the same BOQ line we certified
$matchedLine = null;
foreach ($lines2 as $l) if ((int)$l['boq_item_id'] === (int)$firstLine['boq_item_id']) { $matchedLine = $l; break; }
ok($matchedLine !== null, 'found the same BOQ item on second IPC');
ok(abs((float)$matchedLine['previous_qty'] - $thisQty) < 0.0001, "previous_qty = $thisQty (carried from IPC #1)");
p("previous_qty on IPC#2 line = " . rtrim(rtrim(number_format((float)$matchedLine['previous_qty'], 4), '0'), '.'));

/* ── 9. totalCertifiedFor rollup ───────────────────────────────────── */
h('9. totalCertifiedFor rollup');
$tot = $wi->totalCertifiedFor($prjId);
p(sprintf("gross=%s held=%s net=%s invoiced=%s (n=%d)",
    number_format($tot['gross']), number_format($tot['held']),
    number_format($tot['net']),   number_format($tot['invoiced']), $tot['count']));
ok($tot['count']    >= 1,                                    'count >= 1 APPROVED');
ok(abs($tot['gross']    - $expectedGross) < 0.01,            'rollup.gross = 1 IPC gross');
ok(abs($tot['held']     - $expectedRet)   < 0.01,            'rollup.held  = 1 IPC retention');
ok(abs($tot['net']      - $expectedNet2)  < 0.01,            'rollup.net   = 1 IPC net (after advance)');
ok(abs($tot['invoiced'] - $expectedTotal - ($expectedNet2 - $expectedNet) * 1.18) < 0.02
    || abs($tot['invoiced'] - round($expectedNet2 * 1.18, 2)) < 0.02,
    'rollup.invoiced = net + VAT');

/* ── 10. Perms + view + wiring surface ─────────────────────────────── */
h('10. Perms + view + wiring surface');
foreach (['Projects Coordinator','Project Manager','Administrator'] as $role) {
    $rr = $db->mySelect("SELECT c0d FROM user_groups WHERE user_group_name = :n", [':n' => $role]);
    $gid = (int)($rr[0]['c0d'] ?? 0);
    foreach (['index','list','create','view','createAj','setLineQtyAj','setAdvanceAj','approveAj'] as $act) {
        $r = $db->mySelect(
            "SELECT 1 FROM user_groups_perms WHERE user_group_id = :g AND controller_allowed = 'WorksIpcs' AND action_allowed = :a LIMIT 1",
            [':g' => $gid, ':a' => $act]
        );
        ok(!empty($r), "$role · WorksIpcs/$act");
    }
}
foreach (['Field Supervisor','Procurement','Finance Manager','Accountant','Executive'] as $role) {
    $rr = $db->mySelect("SELECT c0d FROM user_groups WHERE user_group_name = :n", [':n' => $role]);
    $gid = (int)($rr[0]['c0d'] ?? 0);
    foreach (['index','list','view'] as $act) {
        $r = $db->mySelect(
            "SELECT 1 FROM user_groups_perms WHERE user_group_id = :g AND controller_allowed = 'WorksIpcs' AND action_allowed = :a LIMIT 1",
            [':g' => $gid, ':a' => $act]
        );
        ok(!empty($r), "$role · WorksIpcs/$act (read)");
    }
}
foreach (['index','list','create','view'] as $v) {
    ok(is_file(ROOT . DS . 'app' . DS . 'views' . DS . 'WorksIpcs' . DS . "$v.php"), "view file: WorksIpcs/$v.php");
}
$boqBody = (string)file_get_contents(ROOT . DS . 'app' . DS . 'views' . DS . 'WorksBoq' . DS . 'view.php');
ok(strpos($boqBody, 'WorksIpcs/list/') !== false, 'BOQ view links to WorksIpcs/list');
$rptBody = (string)file_get_contents(ROOT . DS . 'app' . DS . 'views' . DS . 'WorksProjectReport' . DS . 'view.php');
ok(strpos($rptBody, 'WorksIpcs/list/') !== false, 'project report links to WorksIpcs/list');
foreach (['projects_coordinator','project_manager','field_supervisor','administrator','executive'] as $role) {
    $body = (string)file_get_contents(ROOT . DS . 'app' . DS . 'views' . DS . 'layouts' . DS . 'partials' . DS . "_sidebar_$role.php");
    ok(strpos($body, "PROOT; ?>WorksIpcs") !== false, "_sidebar_$role has WorksIpcs link");
}

/* ── Z. Restore project state ──────────────────────────────────────── */
h('Z. Restore anchor project state');
$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_status=$prevCrm · retention_pct={$prevTerms['pct']}");

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