<?php
/**
 * WorksCrs — Consolidated Request per phase.
 *
 * A CR is the phase's execution package: activities + budget across 7 pillars
 * (Preliminaries, Manpower, Materials, Equipment, Tools, Sub-contracts, Overheads).
 * Submitted for approval via WORKS_CR_APPROVAL chain (PC → PM → FM → Exec).
 *
 * Lifecycle:
 *   DRAFT → SUBMITTED → APPROVED → ACTIVE (on chain-terminal APPROVED)
 *   A newer revision drafted while an ACTIVE exists → both live until the new one goes
 *   ACTIVE, at which point the old one is auto-SUPERSEDED.
 */
class WorksCrs extends Model
{
    /* Pillar → line-table mapping. Table-driven so we can add/rename pillars later. */
    public const PILLARS = [
        'PRELIMINARIES' => ['table' => 'works_cr_preliminaries', 'total_col' => 'total_preliminaries'],
        'MANPOWER'      => ['table' => 'works_cr_manpower',      'total_col' => 'total_manpower'],
        'MATERIALS'     => ['table' => 'works_cr_materials',     'total_col' => 'total_materials'],
        'EQUIPMENT'     => ['table' => 'works_cr_equipment',     'total_col' => 'total_equipment'],
        'TOOLS'         => ['table' => 'works_cr_tools',         'total_col' => 'total_tools'],
        'SUBCONTRACTS'  => ['table' => 'works_cr_subcontracts',  'total_col' => 'total_subcontracts'],
        'OVERHEADS'     => ['table' => 'works_cr_overheads',     'total_col' => 'total_overheads'],
    ];

    /* ── CR header CRUD ─────────────────────────────────────────────── */

    /**
     * Draft a new CR. If a CR is already ACTIVE on this phase, the new one is a revision
     * that will supersede it once approved. If a prior CR is DRAFT/SUBMITTED, refuse — the
     * originator must finish or cancel the in-flight one first.
     */
    public function draft(int $projectId, int $phaseId, array $d, int $userId, string $userName): int
    {
        if ($projectId <= 0 || $phaseId <= 0) throw new RuntimeException('project_id and phase_id required.');
        $title = trim((string)($d['title'] ?? ''));
        if ($title === '') throw new RuntimeException('CR title required.');

        $db = new DB();
        // Guard: only one non-terminal CR (DRAFT / SUBMITTED / APPROVED-not-yet-active) per phase.
        $blocking = $db->mySelect(
            "SELECT c0d, status FROM works_crs
              WHERE phase_id = :p AND status IN ('DRAFT','SUBMITTED','APPROVED') AND deleted = 0",
            [':p' => $phaseId]
        );
        if (!empty($blocking)) throw new RuntimeException('Another CR (#' . $blocking[0]['c0d'] . ') on this phase is ' . $blocking[0]['status'] . ' — finish or cancel it first.');

        // Version = 1 + max existing (including SUPERSEDED and ACTIVE — revisions carry version+1)
        $vNext = (int)($db->mySelect(
            "SELECT COALESCE(MAX(version),0)+1 n FROM works_crs WHERE phase_id = :p AND deleted = 0",
            [':p' => $phaseId]
        )[0]['n'] ?? 1);

        // What ACTIVE CR are we superseding (if any)? Recorded now as intent; the actual flip happens on activate().
        $supersedes = 0;
        $activeCr = $db->mySelect(
            "SELECT c0d FROM works_crs WHERE phase_id = :p AND status = 'ACTIVE' AND deleted = 0 LIMIT 1",
            [':p' => $phaseId]
        );
        if (!empty($activeCr)) $supersedes = (int)$activeCr[0]['c0d'];

        // Slice 8i · central atomic doc numbering
        if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
        $crNo = (new DocNumberSettings())->allocate('CR');

        $fb = $db->insertUpdate(
            "INSERT INTO works_crs
                (project_id, phase_id, cr_no, title, version, supersedes_id, status,
                 notes, created_by, created_by_name, created_on)
             VALUES (:p, :ph, :no, :t, :v, :sup, 'DRAFT', :nt, :u, :un, NOW())",
            [
                ':p' => $projectId, ':ph' => $phaseId, ':no' => $crNo,
                ':t' => $title, ':v' => $vNext, ':sup' => $supersedes,
                ':nt' => (string)($d['notes'] ?? ''),
                ':u' => $userId, ':un' => $userName,
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function findById(int $id): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM works_crs WHERE c0d = :id AND deleted = 0", [':id' => $id]);
        return $r[0] ?? null;
    }

    public function activeForPhase(int $phaseId): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT * FROM works_crs WHERE phase_id = :p AND status = 'ACTIVE' AND deleted = 0 ORDER BY version DESC LIMIT 1",
            [':p' => $phaseId]
        );
        return $r[0] ?? null;
    }

    public function listForProject(int $projectId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c.*, p.title AS phase_title, p.phase_no
               FROM works_crs c
               JOIN works_phases p ON p.c0d = c.phase_id
              WHERE c.project_id = :p AND c.deleted = 0
              ORDER BY p.phase_no, c.version DESC",
            [':p' => $projectId]
        ) ?: [];
    }

    /** Assert the CR is still in a state where the PC can add/remove lines. */
    public function assertDraftEditable(array $cr): void
    {
        if ($cr['status'] !== 'DRAFT') {
            throw new RuntimeException('CR is ' . $cr['status'] . ' — only DRAFT CRs accept line edits.');
        }
    }

    /* ── Line CRUD (generic across the 7 pillars) ───────────────────── */

    /**
     * Add a line to a specific pillar. Payload shape varies per pillar — see PILLAR_LINE_SHAPES.
     * Returns new line c0d. Recomputes CR pillar total + grand_total.
     */
    public function addLine(int $crId, string $pillar, array $d): int
    {
        $cr = $this->findById($crId);
        if (!$cr) throw new RuntimeException('CR not found.');
        $this->assertDraftEditable($cr);
        $pillar = strtoupper($pillar);
        if (!isset(self::PILLARS[$pillar])) throw new RuntimeException('Unknown pillar: ' . $pillar);
        $table = self::PILLARS[$pillar]['table'];

        $db = new DB();
        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(line_no),0)+1 n FROM `$table` WHERE cr_id = :c AND deleted = 0",
            [':c' => $crId]
        )[0]['n'] ?? 1);

        $newId = 0;
        switch ($pillar) {
            case 'PRELIMINARIES':
                $fb = $db->insertUpdate(
                    "INSERT INTO works_cr_preliminaries (cr_id, line_no, description, amount, notes, created_on)
                     VALUES (:c, :n, :d, :a, :nt, NOW())",
                    [':c' => $crId, ':n' => $nextNo, ':d' => (string)$d['description'],
                     ':a' => (float)($d['amount'] ?? 0), ':nt' => (string)($d['notes'] ?? '')]
                );
                $newId = (int)($fb['last_id'] ?? 0);
                break;

            case 'MANPOWER':
                $persons = (int)($d['persons'] ?? 1);
                $days    = (float)($d['days'] ?? 0);
                $rate    = (float)($d['rate'] ?? 0);
                $total   = round($persons * $days * $rate, 2);
                $fb = $db->insertUpdate(
                    "INSERT INTO works_cr_manpower (cr_id, line_no, role, persons, days, rate, total, notes, created_on)
                     VALUES (:c, :n, :r, :p, :d, :rt, :tot, :nt, NOW())",
                    [':c' => $crId, ':n' => $nextNo, ':r' => (string)$d['role'],
                     ':p' => $persons, ':d' => $days, ':rt' => $rate, ':tot' => $total,
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                $newId = (int)($fb['last_id'] ?? 0);
                break;

            case 'MATERIALS':
                $qty  = (float)($d['qty'] ?? 0);
                $uc   = (float)($d['unit_cost'] ?? 0);
                $total = round($qty * $uc, 2);
                $fb = $db->insertUpdate(
                    "INSERT INTO works_cr_materials (cr_id, line_no, description, qty, unit, unit_cost, total,
                                                     stores_item_id, notes, created_on)
                     VALUES (:c, :n, :d, :q, :u, :uc, :tot, :si, :nt, NOW())",
                    [':c' => $crId, ':n' => $nextNo, ':d' => (string)$d['description'],
                     ':q' => $qty, ':u' => (string)($d['unit'] ?? ''), ':uc' => $uc, ':tot' => $total,
                     ':si' => (int)($d['stores_item_id'] ?? 0), ':nt' => (string)($d['notes'] ?? '')]
                );
                $newId = (int)($fb['last_id'] ?? 0);
                break;

            case 'EQUIPMENT':
                $hrs = (float)($d['hours'] ?? 0);
                $rate = (float)($d['rate'] ?? 0);
                $total = round($hrs * $rate, 2);
                $fb = $db->insertUpdate(
                    "INSERT INTO works_cr_equipment (cr_id, line_no, description, asset_id, hours, rate, total,
                                                    in_house_flag, notes, created_on)
                     VALUES (:c, :n, :d, :a, :h, :rt, :tot, :ih, :nt, NOW())",
                    [':c' => $crId, ':n' => $nextNo, ':d' => (string)$d['description'],
                     ':a' => (int)($d['asset_id'] ?? 0), ':h' => $hrs, ':rt' => $rate, ':tot' => $total,
                     ':ih' => !empty($d['in_house_flag']) ? 1 : 0,
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                $newId = (int)($fb['last_id'] ?? 0);
                break;

            case 'TOOLS':
                $qty = (float)($d['qty'] ?? 0);
                $uc  = (float)($d['unit_cost'] ?? 0);
                $total = round($qty * $uc, 2);
                $fb = $db->insertUpdate(
                    "INSERT INTO works_cr_tools (cr_id, line_no, description, qty, unit, unit_cost, total, notes, created_on)
                     VALUES (:c, :n, :d, :q, :u, :uc, :tot, :nt, NOW())",
                    [':c' => $crId, ':n' => $nextNo, ':d' => (string)$d['description'],
                     ':q' => $qty, ':u' => (string)($d['unit'] ?? ''), ':uc' => $uc, ':tot' => $total,
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                $newId = (int)($fb['last_id'] ?? 0);
                break;

            case 'SUBCONTRACTS':
                $fb = $db->insertUpdate(
                    "INSERT INTO works_cr_subcontracts (cr_id, line_no, subcontractor_name, scope, lump_sum, notes, created_on)
                     VALUES (:c, :n, :s, :sc, :l, :nt, NOW())",
                    [':c' => $crId, ':n' => $nextNo,
                     ':s' => (string)$d['subcontractor_name'],
                     ':sc' => (string)($d['scope'] ?? ''),
                     ':l' => (float)($d['lump_sum'] ?? 0),
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                $newId = (int)($fb['last_id'] ?? 0);
                break;

            case 'OVERHEADS':
                $fb = $db->insertUpdate(
                    "INSERT INTO works_cr_overheads (cr_id, line_no, category, description, amount, notes, created_on)
                     VALUES (:c, :n, :cat, :d, :a, :nt, NOW())",
                    [':c' => $crId, ':n' => $nextNo,
                     ':cat' => (string)($d['category'] ?? 'OTHER'),
                     ':d' => (string)$d['description'],
                     ':a' => (float)($d['amount'] ?? 0),
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                $newId = (int)($fb['last_id'] ?? 0);
                break;
        }

        $this->recomputeTotals($crId);
        return $newId;
    }

    public function deleteLine(int $crId, string $pillar, int $lineId): void
    {
        $cr = $this->findById($crId);
        if (!$cr) throw new RuntimeException('CR not found.');
        $this->assertDraftEditable($cr);
        $pillar = strtoupper($pillar);
        if (!isset(self::PILLARS[$pillar])) throw new RuntimeException('Unknown pillar: ' . $pillar);
        $table = self::PILLARS[$pillar]['table'];
        $db = new DB();
        $db->insertUpdate("UPDATE `$table` SET deleted = 1 WHERE c0d = :id AND cr_id = :c", [':id' => $lineId, ':c' => $crId]);
        $this->recomputeTotals($crId);
    }

    public function linesFor(int $crId, string $pillar): array
    {
        $pillar = strtoupper($pillar);
        if (!isset(self::PILLARS[$pillar])) return [];
        $table = self::PILLARS[$pillar]['table'];
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM `$table` WHERE cr_id = :c AND deleted = 0 ORDER BY line_no, c0d",
            [':c' => $crId]
        ) ?: [];
    }

    /** Return all lines across pillars keyed by pillar name — useful for e2e + views. */
    public function allLines(int $crId): array
    {
        $out = [];
        foreach (self::PILLARS as $p => $meta) $out[$p] = $this->linesFor($crId, $p);
        return $out;
    }

    /* ── CR activities ─────────────────────────────────────────────── */

    public function addActivity(int $crId, array $d): int
    {
        $cr = $this->findById($crId);
        if (!$cr) throw new RuntimeException('CR not found.');
        $this->assertDraftEditable($cr);
        $db = new DB();
        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(activity_no),0)+1 n FROM works_cr_activities WHERE cr_id = :c AND deleted = 0",
            [':c' => $crId]
        )[0]['n'] ?? 1);
        $fb = $db->insertUpdate(
            "INSERT INTO works_cr_activities
                (cr_id, activity_no, linked_phase_activity_id, linked_boq_item_id,
                 title, description, unit, planned_qty, created_on)
             VALUES (:c, :n, :pa, :bi, :t, :d, :u, :q, NOW())",
            [
                ':c' => $crId, ':n' => $nextNo,
                ':pa' => (int)($d['linked_phase_activity_id'] ?? 0),
                ':bi' => (int)($d['linked_boq_item_id'] ?? 0),
                ':t' => (string)$d['title'],
                ':d' => (string)($d['description'] ?? ''),
                ':u' => (string)($d['unit'] ?? ''),
                ':q' => (float)($d['planned_qty'] ?? 0),
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function activitiesFor(int $crId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM works_cr_activities WHERE cr_id = :c AND deleted = 0 ORDER BY activity_no",
            [':c' => $crId]
        ) ?: [];
    }

    /* ── Rollup ─────────────────────────────────────────────────────── */

    public function recomputeTotals(int $crId): void
    {
        $db = new DB();
        $set = [];
        $grand = 0.0;
        foreach (self::PILLARS as $pillar => $meta) {
            $table = $meta['table'];
            $col = $meta['total_col'];
            $sumCol = match ($pillar) {
                'PRELIMINARIES', 'OVERHEADS' => 'amount',
                'SUBCONTRACTS' => 'lump_sum',
                default => 'total',
            };
            $t = (float)($db->mySelect(
                "SELECT COALESCE(SUM(`$sumCol`),0) t FROM `$table` WHERE cr_id = :c AND deleted = 0",
                [':c' => $crId]
            )[0]['t'] ?? 0);
            $set[] = "$col = " . number_format($t, 2, '.', '');
            $grand += $t;
        }
        $set[] = 'grand_total = ' . number_format($grand, 2, '.', '');
        $sql = "UPDATE works_crs SET " . implode(', ', $set) . ", edited_on = NOW() WHERE c0d = :id";
        $db->insertUpdate($sql, [':id' => $crId]);
    }

    /* ── Chain lifecycle ────────────────────────────────────────────── */

    /**
     * Submit the CR into the WORKS_CR_APPROVAL chain.
     * Requires: at least one activity + grand_total > 0.
     */
    public function submitForApproval(int $crId, int $userId, string $userName, string $userGroup): array
    {
        $cr = $this->findById($crId);
        if (!$cr) return ['ok' => false, 'msg' => 'CR not found.'];
        if ($cr['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'CR is ' . $cr['status'] . '.'];
        if (empty($this->activitiesFor($crId))) return ['ok' => false, 'msg' => 'Add at least one activity before submitting.'];
        if ((float)$cr['grand_total'] <= 0)     return ['ok' => false, 'msg' => 'CR budget is zero — add lines before submitting.'];

        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $ac = new ApprovalChain();
        $ref = 'CR ' . $cr['cr_no'] . ' · ' . $cr['title'] . ' · TZS ' . number_format((float)$cr['grand_total']);
        $res = $ac->submit([
            'chain_key' => 'WORKS_CR_APPROVAL',
            'entity_type' => 'WORKS_CR',
            'entity_id' => $crId,
            'ref_label' => $ref,
            'return_url' => 'WorksCrs/view/' . $crId,
            'user_id' => $userId, 'user_name' => $userName, 'user_group' => $userGroup,
        ]);
        if (!empty($res['ok'])) {
            $db = new DB();
            $db->insertUpdate(
                "UPDATE works_crs SET status = 'SUBMITTED', approval_id = :aid, submitted_on = NOW(), edited_by = :u, edited_on = NOW() WHERE c0d = :id",
                [':aid' => (int)$res['approval_id'], ':u' => $userId, ':id' => $crId]
            );
        }
        return $res;
    }

    /**
     * Called from FinChainsController::postDecisionHook when WORKS_CR_APPROVAL terminates.
     * On APPROVED: mark APPROVED, then activate (which supersedes any prior ACTIVE on the phase).
     * On DECLINED: mark DECLINED.
     */
    public function onChainDecision(int $crId, string $finalStatus): void
    {
        $cr = $this->findById($crId);
        if (!$cr) return;
        $db = new DB();
        if ($finalStatus === 'APPROVED') {
            $db->insertUpdate(
                "UPDATE works_crs SET status = 'APPROVED', approved_on = NOW(), edited_on = NOW() WHERE c0d = :id",
                [':id' => $crId]
            );
            $this->activate($crId);
        } elseif ($finalStatus === 'DECLINED') {
            $db->insertUpdate(
                "UPDATE works_crs SET status = 'DECLINED', edited_on = NOW() WHERE c0d = :id",
                [':id' => $crId]
            );
        }
    }

    /**
     * Flip a APPROVED CR to ACTIVE, superseding any prior ACTIVE on the same phase.
     * Also marks the parent phase ACTIVE.
     */
    public function activate(int $crId): void
    {
        $cr = $this->findById($crId);
        if (!$cr) throw new RuntimeException('CR not found.');
        if ($cr['status'] !== 'APPROVED') throw new RuntimeException('Only APPROVED CRs can be activated (current: ' . $cr['status'] . ').');

        $db = new DB();
        // Supersede any prior ACTIVE CR on the same phase.
        $db->insertUpdate(
            "UPDATE works_crs SET status = 'SUPERSEDED', superseded_on = NOW(), edited_on = NOW()
              WHERE phase_id = :ph AND status = 'ACTIVE' AND c0d <> :id AND deleted = 0",
            [':ph' => (int)$cr['phase_id'], ':id' => $crId]
        );
        $db->insertUpdate(
            "UPDATE works_crs SET status = 'ACTIVE', activated_on = NOW(), edited_on = NOW() WHERE c0d = :id",
            [':id' => $crId]
        );

        // Mark phase ACTIVE
        if (!class_exists('WorksPhases')) require_once ROOT . '/app/models/WorksPhases.php';
        (new WorksPhases())->markActive((int)$cr['phase_id']);
    }

    /* ── Closure (Slice 8c-Close) ─────────────────────────────────────── */

    /**
     * Whether a CR can be closed by a given role. Read-only — the actual close() enforces again.
     * Returns [ok=>bool, msg=>string]. Roles: PC / PM / Admin.
     */
    public function canClose(int $crId, string $userGroup): array
    {
        $cr = $this->findById($crId);
        if (!$cr)                                 return ['ok' => false, 'msg' => 'CR not found.'];
        if ($cr['status'] !== 'ACTIVE')            return ['ok' => false, 'msg' => 'Only ACTIVE CRs can be closed (current: ' . $cr['status'] . ').'];
        $canRole = in_array($userGroup, ['Projects Coordinator','Project Manager','Administrator'], true);
        if (!$canRole)                             return ['ok' => false, 'msg' => 'Only PC / PM / Admin can close a CR.'];

        // Any LPO sourced from this CR that isn't PAID / CANCELLED blocks close (paid trail must be clean).
        if (!class_exists('WorksCrFulfilment')) require_once ROOT . '/app/models/WorksCrFulfilment.php';
        $db = new DB();
        $open = $db->mySelect(
            "SELECT c0d, lpo_no, cash_out_status FROM lpos
              WHERE source_cr_id = :c AND deleted = 0
                AND cash_out_status NOT IN ('PAID','CANCELLED','DECLINED')",
            [':c' => $crId]
        ) ?: [];
        if (!empty($open)) {
            $refs = array_map(fn($r) => $r['lpo_no'] . ' (' . $r['cash_out_status'] . ')', array_slice($open, 0, 3));
            return ['ok' => false, 'msg' => 'Unpaid LPOs blocking close: ' . implode(', ', $refs) . (count($open) > 3 ? ' …' : '')];
        }
        return ['ok' => true, 'msg' => ''];
    }

    /**
     * Close an ACTIVE CR — snapshots per-pillar variance to works_cr_closure_variance,
     * stamps closed_by/on + close_reason, flips status → CLOSED.
     * Idempotent on the variance snapshot (unique(cr_id,pillar), upserted).
     */
    public function close(int $crId, string $reason, int $userId, string $userName, string $userGroup): array
    {
        $chk = $this->canClose($crId, $userGroup);
        if (!$chk['ok']) return $chk;

        if (!class_exists('WorksCrFulfilment')) require_once ROOT . '/app/models/WorksCrFulfilment.php';
        $wf   = new WorksCrFulfilment();
        $prog = $wf->progressPerPillar($crId);

        $db = new DB();
        foreach ($prog as $pillar => $ppr) {
            $variance = round((float)$ppr['paid'] - (float)$ppr['budget'], 2);
            $vPct     = (float)$ppr['budget'] > 0 ? round($variance / (float)$ppr['budget'] * 100, 2) : 0.0;
            $db->insertUpdate(
                "INSERT INTO works_cr_closure_variance
                    (cr_id, pillar, budget, sourced, delivered, paid, variance_amount, variance_pct)
                 VALUES (:c, :p, :b, :s, :d, :pd, :v, :vp)
                 ON DUPLICATE KEY UPDATE
                    budget=VALUES(budget), sourced=VALUES(sourced), delivered=VALUES(delivered),
                    paid=VALUES(paid), variance_amount=VALUES(variance_amount), variance_pct=VALUES(variance_pct)",
                [
                    ':c' => $crId, ':p' => $pillar,
                    ':b' => $ppr['budget'], ':s' => $ppr['sourced'],
                    ':d' => $ppr['delivered'], ':pd' => $ppr['paid'],
                    ':v' => $variance, ':vp' => $vPct,
                ]
            );
        }

        $db->insertUpdate(
            "UPDATE works_crs
                SET status = 'CLOSED',
                    closed_by = :u, closed_by_name = :un, closed_on = NOW(),
                    close_reason = :r, edited_by = :u, edited_on = NOW()
              WHERE c0d = :id AND status = 'ACTIVE'",
            [':u' => $userId, ':un' => $userName, ':r' => $reason, ':id' => $crId]
        );
        return ['ok' => true, 'msg' => 'CR closed. Variance snapshot written.'];
    }

    /** Read the frozen variance snapshot for a CLOSED CR (or a live preview when still ACTIVE). */
    public function closureVarianceFor(int $crId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT pillar, budget, sourced, delivered, paid, variance_amount, variance_pct
               FROM works_cr_closure_variance WHERE cr_id = :c ORDER BY c0d",
            [':c' => $crId]
        ) ?: [];
    }
}
