<?php
/**
 * WorksBoq — the contract Bill of Quantities on a Works project.
 *
 * Snapshotted from the approved tender pricing (Slice 4) at project provisioning.
 * Variations append new rows with origin='VARIATION' after WorksVariations approval.
 *
 * IPC (Slice 8c-later) will update cumulative_qty_done / cumulative_value_done as work
 * is certified. Contract quantities are immutable — variations only add new lines.
 */
class WorksBoq extends Model
{
    /* ── BOQ header ──────────────────────────────────────────────────── */

    public function findByProject(int $projectId): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM works_boqs WHERE project_id = :p AND deleted = 0 LIMIT 1", [':p' => $projectId]);
        return $r[0] ?? null;
    }

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

    /**
     * Snapshot the latest APPROVED tender pricing version into a fresh BOQ for this project.
     * Called from Tenders::convertToProject when project_type = 'works'.
     * Idempotent — if a BOQ already exists for the project, returns the existing id.
     */
    public function snapshotFromTenderPricing(int $tenderId, int $projectId, int $userId, string $userName): int
    {
        if ($projectId <= 0) return 0;
        $existing = $this->findByProject($projectId);
        if ($existing) return (int)$existing['c0d'];

        if (!class_exists('TenderPricing')) require_once ROOT . '/app/models/TenderPricing.php';
        $tp = new TenderPricing();
        $version = $tp->latestApproved($tenderId);
        if (!$version) return 0; // No approved pricing → no BOQ (edge: direct-award project)

        $db = new DB();
        // Project title for the BOQ header
        $prj = $db->mySelect("SELECT project_no, project_name, currency FROM c_projects WHERE c0d = :p", [':p' => $projectId]);
        $title    = ($prj[0]['project_no'] ?? '') . ' · ' . ($prj[0]['project_name'] ?? '') . ' — Contract BOQ';
        $currency = (string)($prj[0]['currency'] ?? 'TZS');

        $fb = $db->insertUpdate(
            "INSERT INTO works_boqs
                (project_id, title, currency, contract_value, snapshot_source, snapshot_pricing_version_id,
                 status, created_by, created_by_name, created_on)
             VALUES (:p, :t, :c, :cv, 'TENDER_PRICING', :v, 'ACTIVE', :u, :un, NOW())",
            [
                ':p' => $projectId, ':t' => $title, ':c' => $currency,
                ':cv' => (float)($version['sum_price'] ?? 0),
                ':v' => (int)$version['c0d'],
                ':u' => $userId, ':un' => $userName,
            ]
        );
        $boqId = (int)($fb['last_id'] ?? 0);
        if ($boqId <= 0) return 0;

        // Copy the priced lines from tender pricing to BOQ items.
        // Each pricing line → one BOQ item.
        $rows = $db->mySelect(
            "SELECT l.*, s.section_name FROM tender_pricing_lines l
               JOIN tender_pricing_sections s ON s.c0d = l.section_id
              WHERE l.version_id = :v AND l.deleted = 0
              ORDER BY s.section_no, l.line_no",
            [':v' => (int)$version['c0d']]
        ) ?: [];

        $itemNo = 0;
        foreach ($rows as $ln) {
            $itemNo++;
            $qty  = (float)$ln['qty'];
            $rate = (float)$ln['unit_price'];
            $amt  = round($qty * $rate, 2);
            $db->insertUpdate(
                "INSERT INTO works_boq_items
                    (boq_id, project_id, item_no, section, description, unit,
                     contract_qty, contract_rate, contract_amount, remeasurable, origin, notes, created_on)
                 VALUES (:b, :p, :no, :sec, :desc, :u,
                         :q, :r, :a, 1, 'CONTRACT', :nt, NOW())",
                [
                    ':b' => $boqId, ':p' => $projectId,
                    ':no' => (string)($itemNo),
                    ':sec' => (string)$ln['section_name'],
                    ':desc' => ($ln['item_ref'] ? '[' . $ln['item_ref'] . '] ' : '') . (string)$ln['description'],
                    ':u' => (string)$ln['uom'],
                    ':q' => $qty, ':r' => $rate, ':a' => $amt,
                    ':nt' => (string)$ln['notes'],
                ]
            );
        }
        return $boqId;
    }

    /* ── BOQ items ────────────────────────────────────────────────────── */

    public function itemsFor(int $boqId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM works_boq_items WHERE boq_id = :b AND deleted = 0 ORDER BY origin, c0d",
            [':b' => $boqId]
        ) ?: [];
    }

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

    /**
     * Append rows from an approved Variation into this BOQ.
     * Called from the WorksVariations::applyToBoq() step.
     */
    public function appendFromVariation(int $boqId, int $variationId): int
    {
        $boq = $this->findById($boqId);
        if (!$boq) throw new RuntimeException('BOQ not found.');
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT * FROM works_variation_boq_items WHERE variation_id = :v AND deleted = 0 ORDER BY c0d",
            [':v' => $variationId]
        ) ?: [];

        $inserted = 0;
        $totalDelta = 0.0;
        foreach ($rows as $r) {
            $qty = (float)$r['qty'];
            $rate = (float)$r['rate'];
            $amt = round($qty * $rate, 2);
            $db->insertUpdate(
                "INSERT INTO works_boq_items
                    (boq_id, project_id, item_no, section, description, unit,
                     contract_qty, contract_rate, contract_amount, remeasurable,
                     origin, source_variation_id, notes, created_on)
                 VALUES (:b, :p, :no, :sec, :desc, :u,
                         :q, :r, :a, :rem,
                         'VARIATION', :sv, :nt, NOW())",
                [
                    ':b' => $boqId, ':p' => (int)$boq['project_id'],
                    ':no' => (string)$r['item_no'],
                    ':sec' => (string)$r['section'],
                    ':desc' => (string)$r['description'],
                    ':u' => (string)$r['unit'],
                    ':q' => $qty, ':r' => $rate, ':a' => $amt,
                    ':rem' => (int)$r['remeasurable'],
                    ':sv' => $variationId,
                    ':nt' => (string)($r['notes'] ?? ''),
                ]
            );
            $inserted++;
            $totalDelta += $amt;
        }
        // Bump the BOQ's tracked variation_value + contract_value.
        if ($inserted > 0) {
            $db->insertUpdate(
                "UPDATE works_boqs
                    SET variation_value = variation_value + :d,
                        contract_value  = contract_value  + :d,
                        edited_by = 0, edited_on = NOW()
                  WHERE c0d = :id",
                [':d' => $totalDelta, ':id' => $boqId]
            );
        }
        return $inserted;
    }

    /** Sum of contract_amount across items — matches the header value. Useful for verification. */
    public function totals(int $boqId): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                SUM(CASE WHEN origin = 'CONTRACT'  THEN contract_amount ELSE 0 END) contract_total,
                SUM(CASE WHEN origin = 'VARIATION' THEN contract_amount ELSE 0 END) variation_total,
                SUM(contract_amount) grand_total,
                COUNT(*) item_count
             FROM works_boq_items WHERE boq_id = :b AND deleted = 0",
            [':b' => $boqId]
        );
        return $r[0] ?? [];
    }
}
