<?php
/**
 * WorksAlerts — cross-project exception surfacing for the Works module.
 *
 * Reads from the report/retention/IPC models to answer "what needs someone's attention
 * across every live works project" in one page. Each alert row has:
 *   severity   HIGH | MEDIUM | LOW
 *   category   short kind label used for filtering + chip colour
 *   project_id / project_no / project_name
 *   summary    one-line human description
 *   value      the number that matters (loss amount, pool balance, days over, …)
 *   action_url where clicking should send the user
 */
class WorksAlerts extends Model
{
    /** Days a DRAFT IPC is allowed to sit before it becomes stale. */
    private const STALE_DRAFT_DAYS = 14;

    /** Runs every alerts probe and returns a flat, sorted list (HIGH → MEDIUM → LOW). */
    public function allAlerts(): array
    {
        $all = array_merge(
            $this->lossMaking(),
            $this->overCommitted(),
            $this->staleDraftIpcs(),
            $this->customerRetentionPastDlp(),
            $this->supplierRetentionPastDlp(),
            $this->phasesOverdue(),
            $this->directAwardUnjustified()
        );
        $rank = ['HIGH' => 0, 'MEDIUM' => 1, 'LOW' => 2];
        usort($all, function ($a, $b) use ($rank) {
            $ra = $rank[$a['severity']] ?? 9;
            $rb = $rank[$b['severity']] ?? 9;
            if ($ra !== $rb) return $ra <=> $rb;
            return strcmp((string)$a['project_no'], (string)$b['project_no']);
        });
        return $all;
    }

    /** Aggregate counts per severity for the sidebar badge / top nav pill. */
    public function counts(): array
    {
        $out = ['HIGH' => 0, 'MEDIUM' => 0, 'LOW' => 0, 'total' => 0];
        foreach ($this->allAlerts() as $a) {
            $out[$a['severity']]++;
            $out['total']++;
        }
        return $out;
    }

    /**
     * All alerts touching a single project — used to render an in-context strip on
     * the project report page so users don't have to click over to /WorksAlerts.
     */
    public function alertsFor(int $projectId): array
    {
        $out = [];
        foreach ($this->allAlerts() as $a) {
            if ((int)$a['project_id'] === $projectId) $out[] = $a;
        }
        return $out;
    }

    /**
     * Session-cached counts — the sidebar renders this on EVERY page load, and the
     * probes are chatty. TTL is short so freshly-triggered alerts still show up
     * without a hard refresh.
     */
    public static function cachedCounts(int $ttl = 60): array
    {
        $cached = $_SESSION['works_alerts_counts'] ?? null;
        $atSec  = (int)($_SESSION['works_alerts_counts_at'] ?? 0);
        if ($cached && (time() - $atSec) < $ttl) return $cached;
        $wa = new self();
        $counts = $wa->counts();
        $_SESSION['works_alerts_counts']    = $counts;
        $_SESSION['works_alerts_counts_at'] = time();
        return $counts;
    }

    /** Force the sidebar badge to refresh next page load — call after any write. */
    public static function invalidateCounts(): void
    {
        unset($_SESSION['works_alerts_counts'], $_SESSION['works_alerts_counts_at']);
    }

    /* ── Probes ────────────────────────────────────────────────────── */

    /** Projects where margin_to_date < 0 (paid more than we've certified as revenue). */
    public function lossMaking(): array
    {
        if (!class_exists('WorksProjectReport')) require_once ROOT . '/app/models/WorksProjectReport.php';
        $wpr = new WorksProjectReport();
        $out = [];
        foreach ($wpr->projectsWithReport() as $r) {
            $pid = (int)$r['c0d'];
            $rev = $wpr->revenueSummary($pid);
            // Only flag once there's actual activity — a project with 0 revenue AND 0 cost is noise.
            if ((float)$rev['cost_paid'] <= 0 && (float)$rev['revenue_certified'] <= 0) continue;
            if ((float)$rev['margin_to_date'] >= 0) continue;
            $loss = -1 * (float)$rev['margin_to_date'];
            $out[] = [
                'severity'   => 'HIGH',
                'category'   => 'LOSS_MAKING',
                'project_id' => $pid,
                'project_no' => $r['project_no'],
                'project_name' => $r['project_name'],
                'summary'    => 'Loss ' . number_format($loss) . ' — paid ' . number_format((float)$rev['cost_paid']) . ' vs certified ' . number_format((float)$rev['revenue_certified']),
                'value'      => $loss,
                'action_url' => PROOT . 'WorksProjectReport/view/' . $pid,
            ];
        }
        return $out;
    }

    /** Projects where committed_budget > contract_value (CRs approved above tender). */
    public function overCommitted(): array
    {
        if (!class_exists('WorksProjectReport')) require_once ROOT . '/app/models/WorksProjectReport.php';
        $wpr = new WorksProjectReport();
        $out = [];
        foreach ($wpr->projectsWithReport() as $r) {
            $pid = (int)$r['c0d'];
            $sum = $wpr->projectSummary($pid);
            if ((float)$sum['margin_vs_boq'] >= 0) continue;   // headroom still exists
            if ((float)$sum['contract_value'] <= 0)   continue;   // no BOQ / test noise
            $over = -1 * (float)$sum['margin_vs_boq'];
            $out[] = [
                'severity'   => 'HIGH',
                'category'   => 'OVER_COMMITTED',
                'project_id' => $pid,
                'project_no' => $r['project_no'],
                'project_name' => $r['project_name'],
                'summary'    => 'Over-committed ' . number_format($over) . ' — committed ' . number_format((float)$sum['committed_budget']) . ' vs contract ' . number_format((float)$sum['contract_value']),
                'value'      => $over,
                'action_url' => PROOT . 'WorksProjectReport/view/' . $pid,
            ];
        }
        return $out;
    }

    /** DRAFT IPCs older than STALE_DRAFT_DAYS — someone started a certificate and abandoned it. */
    public function staleDraftIpcs(): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT i.c0d, i.ipc_no, i.created_on, i.certified_gross,
                    p.c0d project_id, p.project_no, p.project_name,
                    DATEDIFF(CURDATE(), DATE(i.created_on)) age_days
               FROM works_ipcs i
               JOIN c_projects p ON p.c0d = i.project_id AND p.deleted = 0
              WHERE i.status IN ('DRAFT','SUBMITTED') AND i.deleted = 0
                AND DATEDIFF(CURDATE(), DATE(i.created_on)) >= :thr",
            [':thr' => self::STALE_DRAFT_DAYS]
        ) ?: [];
        $out = [];
        foreach ($rows as $r) {
            $age = (int)$r['age_days'];
            $out[] = [
                'severity'   => $age > 30 ? 'HIGH' : 'MEDIUM',
                'category'   => 'STALE_DRAFT_IPC',
                'project_id' => (int)$r['project_id'],
                'project_no' => $r['project_no'],
                'project_name' => $r['project_name'],
                'summary'    => 'IPC ' . $r['ipc_no'] . ' has been DRAFT for ' . $age . ' days · certified ' . number_format((float)$r['certified_gross']),
                'value'      => $age,
                'action_url' => PROOT . 'WorksIpcs/view/' . (int)$r['c0d'],
            ];
        }
        return $out;
    }

    /** Customer-side retention past DLP with a positive in-pool balance — chase the customer. */
    public function customerRetentionPastDlp(): array
    {
        if (!class_exists('WorksProjectReport'))    require_once ROOT . '/app/models/WorksProjectReport.php';
        if (!class_exists('WorksCustomerRetention')) require_once ROOT . '/app/models/WorksCustomerRetention.php';
        $wpr = new WorksProjectReport();
        $wcr = new WorksCustomerRetention();
        $out = [];
        foreach ($wpr->projectsWithReport() as $r) {
            $pid = (int)$r['c0d'];
            $pool = $wcr->poolFor($pid);
            if ((float)$pool['in_pool'] <= 0)     continue;
            if ($pool['days_to_dlp'] === null)     continue;   // no IPCs yet — no DLP anchor
            if ((int)$pool['days_to_dlp'] > 0)     continue;   // still within DLP
            $daysOver = -1 * (int)$pool['days_to_dlp'];
            $out[] = [
                'severity'   => $daysOver > 60 ? 'HIGH' : 'MEDIUM',
                'category'   => 'CUST_RETENTION_PAST_DLP',
                'project_id' => $pid,
                'project_no' => $r['project_no'],
                'project_name' => $r['project_name'],
                'summary'    => 'Customer holds ' . number_format((float)$pool['in_pool']) . ' · DLP ended ' . $daysOver . ' days ago — chase',
                'value'      => (float)$pool['in_pool'],
                'action_url' => PROOT . 'WorksCustomerRetention/pool/' . $pid,
            ];
        }
        return $out;
    }

    /** Supplier-side retention past DLP still HELD — we owe the vendor; release or budget flag. */
    public function supplierRetentionPastDlp(): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT h.project_id, h.retention_amount, h.dlp_end_on,
                    p.project_no, p.project_name,
                    DATEDIFF(CURDATE(), h.dlp_end_on) days_over
               FROM works_retention_holdings h
               JOIN c_projects p ON p.c0d = h.project_id AND p.deleted = 0
              WHERE h.status = 'HELD' AND h.dlp_end_on <= CURDATE()",
            []
        ) ?: [];
        // Group by project so we return one aggregate row per project.
        $byProject = [];
        foreach ($rows as $r) {
            $pid = (int)$r['project_id'];
            if (!isset($byProject[$pid])) {
                $byProject[$pid] = [
                    'project_id'  => $pid,
                    'project_no'  => $r['project_no'],
                    'project_name'=> $r['project_name'],
                    'total_amt'   => 0.0,
                    'count'       => 0,
                    'max_days'    => 0,
                ];
            }
            $byProject[$pid]['total_amt'] += (float)$r['retention_amount'];
            $byProject[$pid]['count']     += 1;
            $byProject[$pid]['max_days']   = max($byProject[$pid]['max_days'], (int)$r['days_over']);
        }
        $out = [];
        foreach ($byProject as $pid => $agg) {
            $out[] = [
                'severity'   => $agg['max_days'] > 60 ? 'HIGH' : 'MEDIUM',
                'category'   => 'SUPP_RETENTION_PAST_DLP',
                'project_id' => $pid,
                'project_no' => $agg['project_no'],
                'project_name' => $agg['project_name'],
                'summary'    => $agg['count'] . ' holding' . ($agg['count'] === 1 ? '' : 's') . ' past DLP (up to ' . $agg['max_days'] . ' days over) · ' . number_format($agg['total_amt']) . ' owed to suppliers',
                'value'      => $agg['total_amt'],
                'action_url' => PROOT . 'WorksRetention/pool/' . $pid,
            ];
        }
        return $out;
    }

    /**
     * Direct-award projects with missing / thin / placeholder justification.
     * Slice 8j-DirectAwardHardening — legacy or pre-guard rows can slip through, and
     * audit needs a chase list. New rows are blocked at Projects::create; this catches
     * anything already in the DB that doesn't meet the standard.
     */
    public function directAwardUnjustified(): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT c0d, project_no, project_name, direct_award_justification
               FROM c_projects
              WHERE origin = 'DIRECT_AWARD' AND deleted = 0",
            []
        ) ?: [];
        $placeholders = ['test','tbc','tba','todo','asdf','n/a','na','-','.','?','none','xxx','pending','later','tbd'];
        $out = [];
        foreach ($rows as $r) {
            $daj = trim((string)$r['direct_award_justification']);
            $norm = strtolower(preg_replace('/\s+/', ' ', $daj));
            $len = mb_strlen($daj);
            $reason = null;
            if ($daj === '')                              $reason = 'empty';
            elseif (in_array($norm, $placeholders, true)) $reason = 'placeholder ("' . $daj . '")';
            elseif ($len < 20)                            $reason = 'too short (' . $len . ' chars)';
            if ($reason === null) continue;
            $out[] = [
                'severity'   => 'HIGH',
                'category'   => 'DIRECT_AWARD_UNJUSTIFIED',
                'project_id' => (int)$r['c0d'],
                'project_no' => (string)$r['project_no'],
                'project_name' => (string)$r['project_name'],
                'summary'    => 'Direct award justification ' . $reason . ' — write a real audit-worthy explanation',
                'value'      => (float)$len,
                'action_url' => PROOT . 'Projects/view/' . (int)$r['c0d'],
            ];
        }
        return $out;
    }

    /** Phases still ACTIVE past their planned_end date — schedule slippage. */
    public function phasesOverdue(): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT ph.c0d phase_id, ph.phase_no, ph.title, ph.planned_end,
                    p.c0d project_id, p.project_no, p.project_name,
                    DATEDIFF(CURDATE(), ph.planned_end) days_over
               FROM works_phases ph
               JOIN c_projects p ON p.c0d = ph.project_id AND p.deleted = 0
              WHERE ph.status = 'ACTIVE' AND ph.deleted = 0
                AND ph.planned_end IS NOT NULL
                AND ph.planned_end < CURDATE()",
            []
        ) ?: [];
        $out = [];
        foreach ($rows as $r) {
            $days = (int)$r['days_over'];
            $out[] = [
                'severity'   => $days > 60 ? 'HIGH' : ($days > 14 ? 'MEDIUM' : 'LOW'),
                'category'   => 'PHASE_OVERDUE',
                'project_id' => (int)$r['project_id'],
                'project_no' => $r['project_no'],
                'project_name' => $r['project_name'],
                'summary'    => 'Phase ' . (int)$r['phase_no'] . ' · ' . $r['title'] . ' planned to end ' . $r['planned_end'] . ' (' . $days . ' days overdue)',
                'value'      => $days,
                'action_url' => PROOT . 'WorksPhases/view/' . (int)$r['phase_id'],
            ];
        }
        return $out;
    }
}
