<?php
/**
 * Slice 8c-Alerts · Cross-project exception dashboard · end-to-end.
 *
 * The alerts model reads from live state, so this verify:
 *   1) captures a baseline of alert-ids
 *   2) creates controlled scenarios that SHOULD trip each probe
 *   3) asserts the new alert appears with the right project, category, severity
 *   4) tears the scenario down and confirms the alert disappears
 *   5) checks perms + view + sidebar 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 ([
    'WorksAlerts.php','WorksProjectReport.php','WorksIpcs.php',
    'WorksRetention.php','WorksCustomerRetention.php','WorksBoq.php',
    'WorksCrs.php','WorksPhases.php','WorksCrFulfilment.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();
$wa  = new WorksAlerts();
$wpr = new WorksProjectReport();
$wi  = new WorksIpcs();
$wr  = new WorksRetention();
$wcr = new WorksCustomerRetention();
$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';

/** Grab a signature key for an alert: "category|project_id" so we can detect deltas. */
function alertKey(array $a): string { return $a['category'] . '|' . $a['project_id']; }
function alertSet(array $alerts): array {
    $s = [];
    foreach ($alerts as $a) $s[alertKey($a)] = $a;
    return $s;
}

$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];
$prjNo       = $anchor[0]['project_no'];

/* ── Purge prior AlertsVerify artefacts ────────────────────────────── */
h('0. Purge prior AlertsVerify artefacts');
$oldPhases = $db->mySelect("SELECT c0d FROM works_phases WHERE project_id = :p AND title LIKE 'AlertsVerify %'", [':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]);
        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]);
}
$oldIpcs = $db->mySelect("SELECT c0d, sale_id FROM works_ipcs WHERE project_id = :p AND notes LIKE 'AlertsVerify %'", [':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]);
    }
}
$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) . " phases + " . count($oldIpcs) . " IPCs + " . count($stray) . " stray drafts");

$db->insertUpdate("UPDATE c_projects SET project_customer_id = 1, crm_status = 'APPROVED' WHERE c0d = :p", [':p' => $prjId]);
$wr->setTerms($prjId, 5.00, 30);

/* ── 1. Baseline alert set ─────────────────────────────────────────── */
h('1. Baseline alert set');
$base = alertSet($wa->allAlerts());
p("baseline alerts: " . count($base));

/* ── 2. Trigger STALE_DRAFT_IPC ────────────────────────────────────── */
h('2. Trigger STALE_DRAFT_IPC (backdate a DRAFT IPC 20 days)');
$dr = $wi->draft($prjId, ['notes' => 'AlertsVerify ' . date('YmdHis')], $pcId, $pcNm);
if (!$dr['ok']) boom('IPC draft failed: ' . ($dr['msg'] ?? '?'));
$staleIpcId = (int)$dr['id'];
$db->insertUpdate("UPDATE works_ipcs SET created_on = DATE_SUB(NOW(), INTERVAL 20 DAY) WHERE c0d = :id", [':id' => $staleIpcId]);
$now = alertSet($wa->allAlerts());
$k = 'STALE_DRAFT_IPC|' . $prjId;
ok(isset($now[$k]),                                'STALE_DRAFT_IPC alert appears');
ok(isset($now[$k]) && $now[$k]['severity'] === 'MEDIUM', 'severity MEDIUM (20 days < 30)');
ok(isset($now[$k]) && (int)$now[$k]['value'] >= 20, 'value = age_days >= 20');

/* ── 3. Trigger PHASE_OVERDUE ──────────────────────────────────────── */
h('3. Trigger PHASE_OVERDUE (backdate planned_end 45 days)');
$phId = $wp->create($prjId, [
    'code' => 'AV-' . date('His'),
    'title' => 'AlertsVerify overdue ' . date('YmdHis'),
    'planned_start' => date('Y-m-d', strtotime('-60 days')),
    'planned_end'   => date('Y-m-d', strtotime('-45 days')),
], $pcId);
// Force phase to ACTIVE (probe requires ACTIVE)
$db->insertUpdate("UPDATE works_phases SET status = 'ACTIVE' WHERE c0d = :id", [':id' => $phId]);
$now = alertSet($wa->allAlerts());
$k = 'PHASE_OVERDUE|' . $prjId;
ok(isset($now[$k]),                                'PHASE_OVERDUE alert appears');
ok(isset($now[$k]) && (int)$now[$k]['value'] >= 45, 'value = days_over >= 45');
ok(isset($now[$k]) && $now[$k]['severity'] === 'MEDIUM', 'severity MEDIUM (45 < 60)');

/* ── 4. Trigger LOSS_MAKING by paying more than certified ───────── */
h('4. Trigger LOSS_MAKING (pay 20M cost, certify only 10M revenue)');
$crId = $wc->draft($prjId, $phId, ['title' => 'AlertsVerify CR ' . date('His')], $pcId, $pcNm);
$wc->addLine($crId, 'MATERIALS', ['description' => 'AlertsVerify Diesel', 'qty' => 4000, 'unit' => 'L', 'unit_cost' => 5000]); // 20M
$wc->addActivity($crId, ['title' => 'Access', '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' => 5000] ],
    $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 = $pv->prepFromLpo($lpoId, $fmId, $fmNm, 'AlertsVerify loss');
$sch  = $pv->schedule((int)$prep['id'], date('Y-m-d', strtotime('+1 day')), 1, $fmId, $fmNm, '');
$pay  = $pv->recordPayment((int)$prep['id'], [
    'payment_method' => 'BANK_TRANSFER', 'reference_no' => 'AV', 'payment_date' => date('Y-m-d'), 'bank_account_id' => 1,
], $fmId);
if (!$pay['ok']) boom('recordPayment failed: ' . ($pay['msg'] ?? '?'));

// Now certify 10M via an IPC (approve it, but not the stale one)
$dr2 = $wi->draft($prjId, ['notes' => 'AlertsVerify 10M ' . date('YmdHis')], $pcId, $pcNm);
if (!$dr2['ok']) {
    // stale DRAFT blocked us — flip the stale one to CANCELLED as a workaround
    $db->insertUpdate("UPDATE works_ipcs SET status = 'CANCELLED' WHERE c0d = :id", [':id' => $staleIpcId]);
    $dr2 = $wi->draft($prjId, ['notes' => 'AlertsVerify 10M ' . date('YmdHis')], $pcId, $pcNm);
    if (!$dr2['ok']) boom('IPC draft2 failed: ' . ($dr2['msg'] ?? '?'));
    // Re-DRAFT the stale one to restore the stale alert
    $db->insertUpdate("UPDATE works_ipcs SET status = 'DRAFT' WHERE c0d = :id", [':id' => $staleIpcId]);
}
$ipcId2 = (int)$dr2['id'];
$lines2 = $wi->linesFor($ipcId2);
$targetLine = null; $targetQty = 0;
foreach ($lines2 as $l) {
    $r = (float)$l['contract_rate'];
    if ($r > 0 && (10000000 / $r) <= (float)$l['contract_qty']) {
        $targetLine = $l; $targetQty = round(10000000 / $r, 4); break;
    }
}
if (!$targetLine) boom('no BOQ line has capacity for 10M IPC');
$wi->setLineQty($ipcId2, (int)$targetLine['c0d'], $targetQty);
$wi->approve($ipcId2, $pcId, $pcNm, $pcG);

$now = alertSet($wa->allAlerts());
$k = 'LOSS_MAKING|' . $prjId;
ok(isset($now[$k]),                                'LOSS_MAKING alert appears');
ok(isset($now[$k]) && $now[$k]['severity'] === 'HIGH', 'severity HIGH');
ok(isset($now[$k]) && (float)$now[$k]['value'] > 0, 'value = positive loss amount');

/* ── 5. Trigger SUPP_RETENTION_PAST_DLP ────────────────────────────── */
h('5. Trigger SUPP_RETENTION_PAST_DLP (backdate the holding\'s DLP)');
$db->insertUpdate(
    "UPDATE works_retention_holdings SET dlp_end_on = DATE_SUB(CURDATE(), INTERVAL 45 DAY)
      WHERE lpo_id = :l AND status = 'HELD'",
    [':l' => $lpoId]
);
$now = alertSet($wa->allAlerts());
$k = 'SUPP_RETENTION_PAST_DLP|' . $prjId;
ok(isset($now[$k]),                                'SUPP_RETENTION_PAST_DLP alert appears');
ok(isset($now[$k]) && (float)$now[$k]['value'] > 0, 'value = amount owed to suppliers');

/* ── 6. Trigger CUST_RETENTION_PAST_DLP ────────────────────────────── */
h('6. Trigger CUST_RETENTION_PAST_DLP (backdate every APPROVED IPC beyond DLP)');
// DLP end anchors on MAX(ipc_date) across all APPROVED IPCs, so we must backdate them
// ALL (not just ours) — otherwise a co-existing IPC dated today keeps DLP in the future.
$prevIpcDates = $db->mySelect("SELECT c0d, ipc_date FROM works_ipcs WHERE project_id = :p AND status='APPROVED' AND deleted=0", [':p' => $prjId]) ?: [];
$db->insertUpdate(
    "UPDATE works_ipcs SET ipc_date = DATE_SUB(CURDATE(), INTERVAL 45 DAY) WHERE project_id = :p AND status='APPROVED' AND deleted=0",
    [':p' => $prjId]
);
$now = alertSet($wa->allAlerts());
$k = 'CUST_RETENTION_PAST_DLP|' . $prjId;
ok(isset($now[$k]),                                'CUST_RETENTION_PAST_DLP alert appears');
ok(isset($now[$k]) && (float)$now[$k]['value'] > 0, 'value = amount customer holds');

/* ── 7. counts() aggregate is coherent ─────────────────────────────── */
h('7. counts() aggregate is coherent');
$c = $wa->counts();
$reAll = $wa->allAlerts();
ok($c['total']  === count($reAll),                         'counts.total = allAlerts count');
$high=0;$med=0;$low=0;
foreach ($reAll as $a) { if ($a['severity']==='HIGH') $high++; elseif ($a['severity']==='MEDIUM') $med++; else $low++; }
ok($c['HIGH']   === $high,                                 'counts.HIGH matches');
ok($c['MEDIUM'] === $med,                                  'counts.MEDIUM matches');
ok($c['LOW']    === $low,                                  'counts.LOW matches');

/* ── 8. Tear down triggers → alerts disappear ──────────────────────── */
h('8. Tear down triggers → alerts disappear');
$db->insertUpdate("UPDATE works_ipcs SET status = 'CANCELLED' WHERE c0d = :id", [':id' => $staleIpcId]);
$db->insertUpdate("UPDATE works_phases SET status = 'CLOSED' WHERE c0d = :id", [':id' => $phId]);
$db->insertUpdate("UPDATE works_retention_holdings SET status = 'RELEASED', released_on = NOW(), released_by = :u WHERE lpo_id = :l", [':u' => $fmId, ':l' => $lpoId]);
// LOSS goes away when we certify enough revenue to overtake cost — record another
// large customer retention release + a huge fake IPC? Easier: cancel the IPC + delete the payment.
$db->insertUpdate("UPDATE works_ipcs SET status = 'CANCELLED', ipc_date = CURDATE() WHERE c0d = :id", [':id' => $ipcId2]);
// Also clear the payment so cost_paid goes back to baseline (avoids permanent loss on anchor).
$db->insertUpdate("DELETE FROM payment_allocations WHERE payment_id = :p", [':p' => (int)$pay['payment_id']]);
$db->insertUpdate("DELETE FROM works_retention_holdings WHERE payment_id = :p", [':p' => (int)$pay['payment_id']]);
$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['payment_id']]);
$db->insertUpdate("DELETE FROM journal_entries WHERE source_type='PAYMENT' AND source_id = :p", [':p' => (int)$pay['payment_id']]);
$db->insertUpdate("DELETE FROM payments WHERE c0d = :p", [':p' => (int)$pay['payment_id']]);

// Restore backdated IPC dates so we don't corrupt the anchor for downstream verifies
foreach ($prevIpcDates as $pd) {
    $db->insertUpdate("UPDATE works_ipcs SET ipc_date = :d WHERE c0d = :id", [':d' => $pd['ipc_date'], ':id' => (int)$pd['c0d']]);
}
$torn = alertSet($wa->allAlerts());
// For each category, "disappears after teardown" only means the alert either matches baseline
// or is absent — pre-existing baseline conditions (e.g., another project already loss-making) stay.
foreach (['STALE_DRAFT_IPC','PHASE_OVERDUE','SUPP_RETENTION_PAST_DLP','CUST_RETENTION_PAST_DLP','LOSS_MAKING'] as $cat) {
    $k = $cat . '|' . $prjId;
    $wasBaseline = isset($base[$k]);
    if ($wasBaseline) {
        // Pre-existing — value should be back to baseline (± 1 TZS for float drift)
        $baseVal = (float)$base[$k]['value'];
        $tornVal = isset($torn[$k]) ? (float)$torn[$k]['value'] : 0.0;
        ok(abs($baseVal - $tornVal) < 1.5, "$cat alert value restored to baseline (was $baseVal, now $tornVal)");
    } else {
        ok(!isset($torn[$k]), "$cat alert disappears after teardown");
    }
}

/* ── 9. Perms + view + sidebars ────────────────────────────────────── */
h('9. Perms + view + sidebars');
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 = 'WorksAlerts' AND action_allowed = 'index' LIMIT 1",
        [':g' => $gid]
    );
    ok(!empty($r), "$role · WorksAlerts/index");
}
ok(is_file(ROOT . DS . 'app' . DS . 'views' . DS . 'WorksAlerts' . DS . 'index.php'), 'view file exists');
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; ?>WorksAlerts") !== false, "_sidebar_$role has WorksAlerts link");
}

/* ── Z. Restore anchor state ──────────────────────────────────────── */
h('Z. Restore anchor state');
// Purge everything AlertsVerify created (including LPOs/PVs/IPCs)
$db->insertUpdate("DELETE FROM works_ipc_lines WHERE ipc_id IN (SELECT c0d FROM works_ipcs WHERE project_id = :p AND notes LIKE 'AlertsVerify %')", [':p' => $prjId]);
$saleIds = $db->mySelect("SELECT sale_id FROM works_ipcs WHERE project_id = :p AND notes LIKE 'AlertsVerify %' AND sale_id > 0", [':p' => $prjId]) ?: [];
$db->insertUpdate("DELETE FROM works_ipcs WHERE project_id = :p AND notes LIKE 'AlertsVerify %'", [':p' => $prjId]);
foreach ($saleIds as $s) {
    $db->insertUpdate("DELETE FROM sales_items WHERE sale_id = :s", [':s' => (int)$s['sale_id']]);
    $db->insertUpdate("DELETE FROM sales      WHERE c0d     = :s", [':s' => (int)$s['sale_id']]);
}
// LPO chain: allocations already cleared above; delete the lpo itself
$db->insertUpdate("DELETE FROM payment_vouchers WHERE lpo_id = :l", [':l' => $lpoId]);
$db->insertUpdate("DELETE FROM lpo_items WHERE lpo_id = :l", [':l' => $lpoId]);
$db->insertUpdate("DELETE FROM lpos      WHERE c0d   = :l", [':l' => $lpoId]);
// Phase + CR + activities
$db->insertUpdate("DELETE FROM works_cr_materials WHERE cr_id = :c", [':c' => $crId]);
$db->insertUpdate("DELETE FROM works_cr_activities WHERE cr_id = :c", [':c' => $crId]);
$db->insertUpdate("DELETE FROM works_crs WHERE c0d = :c", [':c' => $crId]);
$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]);

$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 anchor project state · customer_id=$prevCustId · crm=$prevCrm · retention_pct={$prevTerms['pct']}");

$final = alertSet($wa->allAlerts());
$delta = count($final) - count($base);
ok($delta === 0, "final alert count === baseline (delta=$delta)");

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