<?php
/**
 * WorksProjectReport — read-only rollup of a Works project's execution health.
 *
 * The lifecycle numbers already live per-CR (works_crs.total_*) and per-CR-pillar
 * (works_cr_closure_variance for CLOSED CRs; live LPOs + payments for ACTIVE ones).
 * This model aggregates them at project + phase + pillar granularities so leadership
 * gets one page instead of clicking through 3–4 CRs per phase.
 *
 * "Committed" = sum of CR pillar totals where CR status ∈ (ACTIVE, CLOSED). SUPERSEDED
 * CRs are excluded — those were the plan at one point but the successor CR is the
 * authoritative one. DRAFT / SUBMITTED / APPROVED are excluded until they go ACTIVE.
 */
class WorksProjectReport extends Model
{
    /** CR statuses that count into the plan (the live/executed plan, not the graveyard). */
    private const IN_PLAY = "('ACTIVE','CLOSED')";

    /* ── Project-level headline ────────────────────────────────────────── */

    /**
     * Returns:
     *   contract_value      — from works_boqs.contract_value (INCLUDES approved variations)
     *   variation_value     — from works_boqs.variation_value
     *   committed_budget    — SUM(works_crs.grand_total) for in-play CRs on the project
     *   paid                — SUM(payment_allocations.amount) for LPOs sourced from in-play CRs
     *   remaining_budget    — committed_budget − paid
     *   margin_vs_boq       — contract_value − committed_budget (contract headroom left)
     *   cr_active / cr_closed / cr_draft — counts by status
     *   phase_planned / phase_active / phase_closed — counts by status
     */
    public function projectSummary(int $projectId): array
    {
        $db = new DB();
        $boq = $db->mySelect(
            "SELECT contract_value, variation_value, currency
               FROM works_boqs WHERE project_id = :p AND deleted = 0 LIMIT 1",
            [':p' => $projectId]
        );
        $contract    = (float)($boq[0]['contract_value']  ?? 0);
        $variations  = (float)($boq[0]['variation_value'] ?? 0);
        $currency    = (string)($boq[0]['currency']       ?? 'TZS');

        $committed = (float)($db->mySelect(
            "SELECT COALESCE(SUM(grand_total),0) s FROM works_crs
              WHERE project_id = :p AND status IN " . self::IN_PLAY . " AND deleted = 0",
            [':p' => $projectId]
        )[0]['s'] ?? 0);

        $paid = (float)($db->mySelect(
            "SELECT COALESCE(SUM(pa.amount),0) s
               FROM payment_allocations pa
               JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0
               JOIN lpos     l ON l.c0d = pa.lpo_id     AND l.deleted = 0
               JOIN works_crs c ON c.c0d = l.source_cr_id AND c.deleted = 0
              WHERE c.project_id = :p AND c.status IN " . self::IN_PLAY,
            [':p' => $projectId]
        )[0]['s'] ?? 0);

        $crCounts = $db->mySelect(
            "SELECT status, COUNT(*) n FROM works_crs
              WHERE project_id = :p AND deleted = 0 GROUP BY status",
            [':p' => $projectId]
        ) ?: [];
        $crMap = [];
        foreach ($crCounts as $r) $crMap[$r['status']] = (int)$r['n'];

        $phCounts = $db->mySelect(
            "SELECT status, COUNT(*) n FROM works_phases
              WHERE project_id = :p AND deleted = 0 GROUP BY status",
            [':p' => $projectId]
        ) ?: [];
        $phMap = [];
        foreach ($phCounts as $r) $phMap[$r['status']] = (int)$r['n'];

        return [
            'currency'          => $currency,
            'contract_value'    => $contract,
            'variation_value'   => $variations,
            'committed_budget'  => $committed,
            'paid'              => $paid,
            'remaining_budget'  => max(0, $committed - $paid),
            'margin_vs_boq'     => $contract - $committed,
            'contract_utilisation_pct' => $contract > 0 ? round($committed / $contract * 100, 1) : 0.0,
            'paid_pct'          => $committed > 0 ? round($paid / $committed * 100, 1) : 0.0,
            'cr_active'  => (int)($crMap['ACTIVE']  ?? 0),
            'cr_closed'  => (int)($crMap['CLOSED']  ?? 0),
            'cr_draft'   => (int)($crMap['DRAFT']   ?? 0) + (int)($crMap['SUBMITTED'] ?? 0) + (int)($crMap['APPROVED'] ?? 0),
            'phase_planned' => (int)($phMap['PLANNED'] ?? 0),
            'phase_active'  => (int)($phMap['ACTIVE']  ?? 0),
            'phase_closed'  => (int)($phMap['CLOSED']  ?? 0),
        ];
    }

    /* ── Per-phase rollup: one row per phase, pillar totals summed across in-play CRs ─ */

    public function phaseRollup(int $projectId): array
    {
        if (!class_exists('WorksCrs')) require_once ROOT . '/app/models/WorksCrs.php';
        $db = new DB();
        $phases = $db->mySelect(
            "SELECT c0d, phase_no, code, title, status, planned_start, planned_end, actual_end
               FROM works_phases WHERE project_id = :p AND deleted = 0
              ORDER BY phase_no",
            [':p' => $projectId]
        ) ?: [];

        $out = [];
        foreach ($phases as $ph) {
            $phId = (int)$ph['c0d'];
            $tot = $db->mySelect(
                "SELECT COALESCE(SUM(total_preliminaries),0) prel,
                        COALESCE(SUM(total_manpower),0)      man,
                        COALESCE(SUM(total_materials),0)     mat,
                        COALESCE(SUM(total_equipment),0)     eq,
                        COALESCE(SUM(total_tools),0)         tls,
                        COALESCE(SUM(total_subcontracts),0)  sub,
                        COALESCE(SUM(total_overheads),0)     ovh,
                        COALESCE(SUM(grand_total),0)         gt,
                        COUNT(*) cr_count
                   FROM works_crs
                  WHERE phase_id = :ph AND status IN " . self::IN_PLAY . " AND deleted = 0",
                [':ph' => $phId]
            );
            $paid = (float)($db->mySelect(
                "SELECT COALESCE(SUM(pa.amount),0) s
                   FROM payment_allocations pa
                   JOIN payments p  ON p.c0d = pa.payment_id AND p.deleted = 0
                   JOIN lpos     l  ON l.c0d = pa.lpo_id     AND l.deleted = 0
                   JOIN works_crs c ON c.c0d = l.source_cr_id AND c.deleted = 0
                  WHERE c.phase_id = :ph AND c.status IN " . self::IN_PLAY,
                [':ph' => $phId]
            )[0]['s'] ?? 0);

            $t = $tot[0] ?? [];
            $budget = (float)($t['gt'] ?? 0);
            $out[] = [
                'phase_id'      => $phId,
                'phase_no'      => (int)$ph['phase_no'],
                'code'          => (string)$ph['code'],
                'title'         => (string)$ph['title'],
                'status'        => (string)$ph['status'],
                'planned_start' => $ph['planned_start'],
                'planned_end'   => $ph['planned_end'],
                'actual_end'    => $ph['actual_end'],
                'cr_count'      => (int)($t['cr_count'] ?? 0),
                'pillars'       => [
                    'PRELIMINARIES' => (float)($t['prel'] ?? 0),
                    'MANPOWER'      => (float)($t['man']  ?? 0),
                    'MATERIALS'     => (float)($t['mat']  ?? 0),
                    'EQUIPMENT'     => (float)($t['eq']   ?? 0),
                    'TOOLS'         => (float)($t['tls']  ?? 0),
                    'SUBCONTRACTS'  => (float)($t['sub']  ?? 0),
                    'OVERHEADS'     => (float)($t['ovh']  ?? 0),
                ],
                'budget'        => $budget,
                'paid'          => $paid,
                'remaining'     => max(0, $budget - $paid),
                'paid_pct'      => $budget > 0 ? round($paid / $budget * 100, 1) : 0.0,
            ];
        }
        return $out;
    }

    /* ── Per-pillar project-wide rollup ────────────────────────────────── */

    public function pillarRollup(int $projectId): array
    {
        if (!class_exists('WorksCrs')) require_once ROOT . '/app/models/WorksCrs.php';
        $db = new DB();
        // Budget = sum of works_crs.total_<pillar> across in-play CRs
        $totRow = $db->mySelect(
            "SELECT COALESCE(SUM(total_preliminaries),0) prel,
                    COALESCE(SUM(total_manpower),0)      man,
                    COALESCE(SUM(total_materials),0)     mat,
                    COALESCE(SUM(total_equipment),0)     eq,
                    COALESCE(SUM(total_tools),0)         tls,
                    COALESCE(SUM(total_subcontracts),0)  sub,
                    COALESCE(SUM(total_overheads),0)     ovh
               FROM works_crs
              WHERE project_id = :p AND status IN " . self::IN_PLAY . " AND deleted = 0",
            [':p' => $projectId]
        );
        $t = $totRow[0] ?? [];
        $budgets = [
            'PRELIMINARIES' => (float)($t['prel'] ?? 0),
            'MANPOWER'      => (float)($t['man']  ?? 0),
            'MATERIALS'     => (float)($t['mat']  ?? 0),
            'EQUIPMENT'     => (float)($t['eq']   ?? 0),
            'TOOLS'         => (float)($t['tls']  ?? 0),
            'SUBCONTRACTS'  => (float)($t['sub']  ?? 0),
            'OVERHEADS'     => (float)($t['ovh']  ?? 0),
        ];

        // Sourced (LPO amounts) + Paid (allocations) by pillar
        $sourcedRows = $db->mySelect(
            "SELECT l.source_cr_pillar pillar, COALESCE(SUM(l.amount),0) s
               FROM lpos l
               JOIN works_crs c ON c.c0d = l.source_cr_id AND c.deleted = 0
              WHERE c.project_id = :p AND c.status IN " . self::IN_PLAY . " AND l.deleted = 0
              GROUP BY l.source_cr_pillar",
            [':p' => $projectId]
        ) ?: [];
        $sourcedMap = [];
        foreach ($sourcedRows as $r) $sourcedMap[$r['pillar']] = (float)$r['s'];

        $deliveredRows = $db->mySelect(
            "SELECT i.cr_pillar pillar, COALESCE(SUM(i.total),0) s
               FROM works_phase_items_issued i
               JOIN works_crs c ON c.c0d = i.cr_id AND c.deleted = 0
              WHERE c.project_id = :p AND c.status IN " . self::IN_PLAY . " AND i.deleted = 0
              GROUP BY i.cr_pillar",
            [':p' => $projectId]
        ) ?: [];
        $deliveredMap = [];
        foreach ($deliveredRows as $r) $deliveredMap[$r['pillar']] = (float)$r['s'];

        $paidRows = $db->mySelect(
            "SELECT l.source_cr_pillar pillar, COALESCE(SUM(pa.amount),0) s
               FROM payment_allocations pa
               JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0
               JOIN lpos     l ON l.c0d = pa.lpo_id     AND l.deleted = 0
               JOIN works_crs c ON c.c0d = l.source_cr_id AND c.deleted = 0
              WHERE c.project_id = :p AND c.status IN " . self::IN_PLAY . "
              GROUP BY l.source_cr_pillar",
            [':p' => $projectId]
        ) ?: [];
        $paidMap = [];
        foreach ($paidRows as $r) $paidMap[$r['pillar']] = (float)$r['s'];

        $out = [];
        foreach (WorksCrs::PILLARS as $pillar => $meta) {
            $budget    = (float)($budgets[$pillar]    ?? 0);
            $sourced   = (float)($sourcedMap[$pillar]   ?? 0);
            $delivered = (float)($deliveredMap[$pillar] ?? 0);
            $paid      = (float)($paidMap[$pillar]      ?? 0);
            $out[$pillar] = [
                'budget'        => $budget,
                'sourced'       => $sourced,
                'delivered'     => $delivered,
                'paid'          => $paid,
                'remaining'     => max(0, $budget - $paid),
                'variance'      => round($paid - $budget, 2),  // negative = under-spend
                'paid_pct'      => $budget > 0 ? round($paid      / $budget * 100, 1) : 0.0,
                'delivered_pct' => $budget > 0 ? round($delivered / $budget * 100, 1) : 0.0,
                'sourced_pct'   => $budget > 0 ? round($sourced   / $budget * 100, 1) : 0.0,
            ];
        }
        return $out;
    }

    /* ── Revenue vs Cost — the job's live margin picture ─────────────── */

    /**
     * Reads from the IPC pipeline + both retention pools to give leadership one
     * side-by-side view of "money in" vs "money out" to date.
     *
     *   revenue_certified   — SUM(works_ipcs.certified_gross) APPROVED
     *   customer_retention  — SUM(works_ipcs.retention_amount) APPROVED (customer holds)
     *   customer_released   — SUM(works_customer_retention_releases.amount)
     *   revenue_invoiced    — SUM(works_ipcs.net_amount) APPROVED (what we billed)
     *   revenue_with_vat    — SUM(works_ipcs.invoice_total) APPROVED
     *   cost_paid           — SUM(payment_allocations.amount) for in-play CRs
     *   supplier_retention_held      — SUM(works_retention_holdings.retention_amount HELD)
     *   supplier_retention_released  — SUM(...) RELEASED
     *   margin_to_date      — revenue_certified − cost_paid  (before retention flows)
     *   effective_cash      — revenue_invoiced (billed) − cost_paid + supplier_retention_held − customer_retention_still_held
     *                         (positive = we're up on cash; negative = we're financing)
     */
    public function revenueSummary(int $projectId): array
    {
        if (!class_exists('WorksIpcs'))              require_once ROOT . '/app/models/WorksIpcs.php';
        if (!class_exists('WorksRetention'))         require_once ROOT . '/app/models/WorksRetention.php';
        if (!class_exists('WorksCustomerRetention')) require_once ROOT . '/app/models/WorksCustomerRetention.php';

        $ipcModel  = new WorksIpcs();
        $ipcTotals = $ipcModel->totalCertifiedFor($projectId);
        $ipcRcvd   = $ipcModel->totalReceivedFor($projectId);
        $custPool  = (new WorksCustomerRetention())->poolFor($projectId);
        $suppPool  = (new WorksRetention())->poolFor($projectId);

        $db = new DB();
        $paid = (float)($db->mySelect(
            "SELECT COALESCE(SUM(pa.amount),0) s
               FROM payment_allocations pa
               JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0
               JOIN lpos     l ON l.c0d = pa.lpo_id     AND l.deleted = 0
               JOIN works_crs c ON c.c0d = l.source_cr_id AND c.deleted = 0
              WHERE c.project_id = :p AND c.status IN " . self::IN_PLAY,
            [':p' => $projectId]
        )[0]['s'] ?? 0);

        $revenueCertified = (float)$ipcTotals['gross'];
        $revenueInvoiced  = (float)$ipcTotals['net'];        // net of retention + advance
        $revenueWithVat   = (float)$ipcTotals['invoiced'];
        $custHeld         = (float)$custPool['total_held'];
        $custReleased     = (float)$custPool['total_released'];
        $custStillHeld    = (float)$custPool['in_pool'];
        $suppHeld         = (float)$suppPool['total_held'];
        $suppReleased     = (float)$suppPool['total_released'];

        $marginToDate  = round($revenueCertified - $paid, 2);
        // Effective cash position: what we've billed (customer will pay these) minus what
        // we've paid, PLUS supplier retention still in our hand, MINUS customer retention
        // still in customer's hand.
        $effectiveCash = round($revenueInvoiced - $paid + $suppHeld - $custStillHeld, 2);

        // Customer cash actually received against IPC-sourced sales
        $received      = (float)$ipcRcvd['received'];
        $receivedCount = (int)$ipcRcvd['count'];
        // Working-capital gap: what customer owes us (invoiced but not received)
        $arGap = round($revenueInvoiced - $received, 2);

        return [
            'revenue_certified'          => $revenueCertified,
            'revenue_invoiced'           => $revenueInvoiced,
            'revenue_with_vat'           => $revenueWithVat,
            'customer_retention_held'    => $custHeld,
            'customer_retention_released'=> $custReleased,
            'customer_retention_pool'    => $custStillHeld,
            'cost_paid'                  => $paid,
            'supplier_retention_held'    => $suppHeld,
            'supplier_retention_released'=> $suppReleased,
            'customer_received'          => $received,
            'customer_ar_gap'            => max(0, $arGap),
            'receipts_count'             => $receivedCount,
            'margin_to_date'             => $marginToDate,
            'margin_pct_to_date'         => $revenueCertified > 0 ? round($marginToDate / $revenueCertified * 100, 1) : 0.0,
            'effective_cash'             => $effectiveCash,
            'ipcs_count'                 => (int)$ipcTotals['count'],
        ];
    }

    /* ── Cross-project index — every works project's headline row ─────── */

    public function projectsWithReport(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT p.c0d, p.project_no, p.project_name, p.project_customer, p.currency,
                    b.c0d boq_id, b.contract_value, b.variation_value,
                    (SELECT COUNT(*) FROM works_phases ph WHERE ph.project_id = p.c0d AND ph.deleted = 0) phase_count,
                    (SELECT COUNT(*) FROM works_crs c   WHERE c.project_id  = p.c0d AND c.deleted  = 0 AND c.status IN ('ACTIVE','CLOSED')) cr_count
               FROM c_projects p
               JOIN works_boqs b ON b.project_id = p.c0d AND b.deleted = 0
              WHERE p.deleted = 0 AND p.project_type = 'works'
              ORDER BY p.project_no DESC",
            []
        ) ?: [];
    }
}
