<?php
/**
 * Slice 8c-Retention · Subcontractor retention pool · end-to-end.
 *
 * 1) Set retention_pct = 5% + dlp_days = 30 on anchor project's BOQ (isolated: back it up
 *    and restore at the end so we don't disturb other verifies).
 * 2) Create an isolated fresh phase + CR + LPO on the anchor project.
 * 3) Drive the payment via PaymentVouchers::recordPayment. Verify a
 *    works_retention_holdings row was auto-created with retention = 5% of gross.
 * 4) poolFor() splits correctly. Past-DLP amount is 0 (DLP not expired).
 * 5) Guard: release() refuses while in DLP; forceUnlock=true releases it.
 * 6) Second release fails (already released).
 * 7) Non-Works LPO payment (source_cr_id=0) leaves no holding (skip semantics).
 * 8) Perms + view surface + hook still wired.
 */
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 (['WorksCrs.php','WorksPhases.php','WorksCrFulfilment.php','WorksRetention.php','PaymentVouchers.php','Lpos.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();
$wr  = new WorksRetention();
$wc  = new WorksCrs();
$wp  = new WorksPhases();
$wf  = new WorksCrFulfilment();
$pv  = new PaymentVouchers();

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

/* ── 0. Anchor + backup existing retention terms ─────────────────────── */
h('0. Pick anchor works project + backup existing terms');
$anchor = $db->mySelect(
    "SELECT c0d, project_no FROM c_projects
      WHERE project_type='works' AND deleted=0 ORDER BY c0d DESC LIMIT 1",
    []
);
if (empty($anchor)) boom('No works project. Run slice8cSpine_e2e_verify.php first.');
$prjId = (int)$anchor[0]['c0d'];
$prevTerms = $wr->termsFor($prjId) ?: ['pct' => 0.0, 'dlp_days' => 365];
p("Project #$prjId · prev retention_pct={$prevTerms['pct']}% · dlp_days={$prevTerms['dlp_days']}");

/* ── 0b. Purge prior retention-verify artefacts ─────────────────────── */
h('0b. Purge prior retention-verify artefacts');
$oldPhases = $db->mySelect(
    "SELECT c0d FROM works_phases WHERE project_id = :p AND title LIKE 'RetentionVerify %'",
    [':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'];
            // Cascade: retention holdings, allocations, payments, PVs, items
            $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]);
}
p("Purged " . count($oldPhases) . " prior retention-verify phases");

/* ── 1. Set terms 5% / DLP 30 days ────────────────────────────────── */
h('1. Set retention_pct=5%, dlp_days=30');
$setRes = $wr->setTerms($prjId, 5.00, 30);
ok($setRes['ok'], 'setTerms(5%, 30) accepted');
$t = $wr->termsFor($prjId);
ok(abs($t['pct'] - 5.00) < 0.01 && $t['dlp_days'] === 30, "termsFor returns pct=5%, dlp=30");

/* ── 2. Create isolated phase + CR + LPO + APPROVE LPO ─────────────── */
h('2. Fresh phase + CR ACTIVE + LPO APPROVED');
$phId = $wp->create($prjId, [
    'code' => 'RTV-' . date('His'),
    'title' => 'RetentionVerify ' . date('YmdHis'),
    'planned_start' => date('Y-m-d'),
    'planned_end'   => date('Y-m-d', strtotime('+30 days')),
], $pcId);
$crId = $wc->draft($prjId, $phId, ['title' => 'RetentionVerify 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' => 2500] ], // 10M
    $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]);
p("LPO c0d=$lpoId · 10M · APPROVED");

/* ── 3. Full PV flow: prep → schedule → recordPayment → auto-hold ─── */
h('3. Payment pipeline auto-creates a retention holding');
$prep = $pv->prepFromLpo($lpoId, $fmId, $fmNm, 'Retention verify pay');
if (!$prep['ok']) boom('prepFromLpo failed: ' . ($prep['msg'] ?? '?'));
$pvId = (int)$prep['id'];
$sch  = $pv->schedule($pvId, date('Y-m-d', strtotime('+1 day')), 1, $fmId, $fmNm, '');
if (!$sch['ok']) boom('schedule failed: ' . ($sch['msg'] ?? '?'));
$pay  = $pv->recordPayment($pvId, [
    'payment_method'  => 'BANK_TRANSFER',
    'reference_no'    => 'RET-VERIFY-' . date('His'),
    'payment_date'    => date('Y-m-d'),
    'bank_account_id' => 1,
], $fmId);
if (!$pay['ok']) boom('recordPayment failed: ' . ($pay['msg'] ?? '?'));
$paymentId = (int)$pay['payment_id'];
p("Payment {$pay['payment_no']} · c0d=$paymentId");

$holding = $db->mySelect("SELECT * FROM works_retention_holdings WHERE payment_id = :p", [':p' => $paymentId]);
ok(count($holding) === 1,                                       'exactly 1 works_retention_holdings row auto-created');
$h1 = $holding[0];
ok((int)$h1['project_id'] === $prjId,                            'holding.project_id');
ok((int)$h1['payment_id'] === $paymentId,                        'holding.payment_id');
ok((int)$h1['lpo_id']     === $lpoId,                            'holding.lpo_id');
ok((int)$h1['cr_id']      === $crId,                             'holding.cr_id');
ok(abs((float)$h1['gross_amount']    - 10000000) < 0.01,         'holding.gross_amount = 10M');
ok(abs((float)$h1['retention_pct']   - 5.00)     < 0.01,         'holding.retention_pct = 5%');
ok(abs((float)$h1['retention_amount']- 500000)   < 0.01,         'holding.retention_amount = 500k');
ok((string)$h1['status'] === 'HELD',                             'holding.status = HELD');
ok((string)$h1['held_on'] === date('Y-m-d'),                     'holding.held_on = today');
ok((string)$h1['dlp_end_on'] === date('Y-m-d', strtotime('+30 days')), 'holding.dlp_end_on = today + 30d');

/* ── 4. poolFor split ─────────────────────────────────────────────── */
h('4. poolFor totals');
$pool = $wr->poolFor($prjId);
p(sprintf("total_held=%s past_dlp_held=%s total_released=%s (held=%d, released=%d)",
    number_format($pool['total_held']), number_format($pool['past_dlp_held']),
    number_format($pool['total_released']), $pool['count_held'], $pool['count_released']));
ok((float)$pool['total_held']     >= 500000, 'total_held includes our 500k');
ok((float)$pool['past_dlp_held']  === 0.0 || (float)$pool['past_dlp_held'] === 0, 'past_dlp_held = 0 (DLP not expired)');
ok((int)$pool['count_held']       >= 1,       'count_held >= 1');

/* ── 5. Guard: release() refuses in DLP; forceUnlock releases ────── */
h('5. Guard · release in DLP');
$rej = $wr->release((int)$h1['c0d'], $fmId, $fmNm, 'Try early release');
ok(!$rej['ok'] && stripos($rej['msg'], 'DLP has not expired') !== false, 'release rejected while in DLP');
$rel = $wr->release((int)$h1['c0d'], $fmId, $fmNm, 'Sign-off on file · force', true);
ok($rel['ok'], 'release with forceUnlock=true succeeds');

$after = $db->mySelect("SELECT * FROM works_retention_holdings WHERE c0d = :id", [':id' => (int)$h1['c0d']]);
ok((string)$after[0]['status']         === 'RELEASED',                 'holding flipped to RELEASED');
ok((int)$after[0]['released_by']       === $fmId,                      'released_by stamped');
ok((string)$after[0]['released_by_name'] === $fmNm,                    'released_by_name stamped');
ok(!empty($after[0]['released_on']),                                    'released_on stamped');
ok(stripos((string)$after[0]['released_notes'], 'Sign-off') !== false, 'released_notes persisted');

/* ── 6. Second release fails (already released) ──────────────────── */
h('6. Double-release guard');
$again = $wr->release((int)$h1['c0d'], $fmId, $fmNm, '');
ok(!$again['ok'] && stripos($again['msg'], 'already released') !== false, 'already-released cannot re-release');

/* ── 7. Pool split moves from held → released ───────────────────── */
h('7. poolFor after release');
$pool2 = $wr->poolFor($prjId);
ok((float)$pool2['total_released'] >= 500000, 'total_released includes our 500k');
ok((float)$pool2['total_held']     < (float)$pool['total_held'] + 0.01, 'total_held decreased by 500k');
p(sprintf("held=%s released=%s", number_format($pool2['total_held']), number_format($pool2['total_released'])));

/* ── 8. Non-Works payment: no holding (safety) ──────────────────── */
h('8. Non-Works CR-sourced payment → no holding');
// Simulate a payment on an LPO with source_cr_id = 0 (e.g. petty cash)
// Insert directly then call the hook. Should skip.
$stubLpo = $db->insertUpdate(
    "INSERT INTO lpos (lpo_no, project_id, source_module, lpo_type, payee_type, payee_id, payee_name,
                       amount, currency, requested_by, source_cr_id, cash_out_status, created_by, edited_by)
     VALUES (:no, :pj, 'central', 'PETTY_CASH', 'employee', 0, 'Verify Stub', 999999, 'TZS', :u, 0, 'APPROVED', :u, :u)",
    [':no' => 'STUB-' . date('His'), ':pj' => $prjId, ':u' => $pcId]
);
$stubLpoId = (int)$stubLpo['last_id'];
$stubPay = $db->insertUpdate(
    "INSERT INTO payments (payment_no, payment_voucher_id, bank_account_id, amount, currency, payment_method, reference_no, payment_date, received_by, created_by, edited_by)
     VALUES (:pn, 0, 1, 999999, 'TZS', 'CASH', 'STUB', CURDATE(), 'Employee', :u, :u)",
    [':pn' => 'PAY-STUB-' . date('His'), ':u' => $pcId]
);
$stubPayId = (int)$stubPay['last_id'];
$db->insertUpdate(
    "INSERT INTO payment_allocations (payment_id, lpo_id, amount, allocation_type) VALUES (:p, :l, 999999, 'FULL')",
    [':p' => $stubPayId, ':l' => $stubLpoId]
);
$stubRes = $wr->recordHolding($stubPayId);
ok($stubRes['ok'] && (int)$stubRes['id'] === 0, 'recordHolding skips non-Works CR LPO');
$stubHold = $db->mySelect("SELECT COUNT(*) n FROM works_retention_holdings WHERE payment_id = :p", [':p' => $stubPayId]);
ok((int)$stubHold[0]['n'] === 0, 'no holding row for petty-cash payment');
// Cleanup stub
$db->insertUpdate("DELETE FROM payment_allocations WHERE payment_id = :p", [':p' => $stubPayId]);
$db->insertUpdate("DELETE FROM payments WHERE c0d = :p", [':p' => $stubPayId]);
$db->insertUpdate("DELETE FROM lpos WHERE c0d = :l", [':l' => $stubLpoId]);

/* ── 9. Perms + view surface + hook wiring ───────────────────────── */
h('9. Perms + view surface');
foreach (['Projects Coordinator','Project Manager','Field Supervisor','Procurement','Finance Manager','Accountant','Executive','Administrator'] as $role) {
    $rr = $db->mySelect("SELECT c0d FROM user_groups WHERE user_group_name = :n", [':n' => $role]);
    $gid = (int)($rr[0]['c0d'] ?? 0);
    $r = $db->mySelect(
        "SELECT 1 FROM user_groups_perms WHERE user_group_id = :g AND controller_allowed = 'WorksRetention' AND action_allowed = 'pool' LIMIT 1",
        [':g' => $gid]
    );
    ok(!empty($r), "$role · WorksRetention/pool");
}
foreach (['Finance 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 (['releaseAj','setTermsAj'] as $act) {
        $r = $db->mySelect(
            "SELECT 1 FROM user_groups_perms WHERE user_group_id = :g AND controller_allowed = 'WorksRetention' AND action_allowed = :a LIMIT 1",
            [':g' => $gid, ':a' => $act]
        );
        ok(!empty($r), "$role · WorksRetention/$act");
    }
}
ok(is_file(ROOT . DS . 'app' . DS . 'views' . DS . 'WorksRetention' . DS . 'pool.php'), 'pool view file exists');
$pvBody = (string)file_get_contents(ROOT . DS . 'app' . DS . 'models' . DS . 'PaymentVouchers.php');
ok(strpos($pvBody, 'WorksRetention') !== false && strpos($pvBody, 'recordHolding') !== false, 'PaymentVouchers hook wires to WorksRetention::recordHolding');
$rptBody = (string)file_get_contents(ROOT . DS . 'app' . DS . 'views' . DS . 'WorksProjectReport' . DS . 'view.php');
ok(strpos($rptBody, 'WorksRetention/pool/') !== false, 'project report links to WorksRetention/pool');

/* ── Z. Restore prior retention terms so anchor state is untouched ─── */
h('Z. Restore prior retention terms on the BOQ');
$wr->setTerms($prjId, (float)$prevTerms['pct'], (int)$prevTerms['dlp_days']);
p("Restored to pct={$prevTerms['pct']} · dlp_days={$prevTerms['dlp_days']}");

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