<?php
/**
 * GoodsScrubPack — per-Client-PO 7-pillar cost scrub done by PC + Procurement after
 * CRM onboarding is approved.
 *
 * (Distinct from the legacy `GoodsScrub` class — that one handles per-item margin/cog
 * scrub on `goods_client_po_items` for pre-8g `migrated_flag=1` projects.)
 *
 * Design mirror: `WorksCrs` + `works_cr_*`. Same 7 pillars, same line shapes. Users
 * who know the Works CR editor are immediately productive here.
 *
 * MATERIALS pillar is special:
 *   - Auto-seeded from `goods_client_po_items` at scrub creation (the items CRM captured).
 *   - PC + Procurement enrich each seeded line with an estimated `unit_cost` (cost of
 *     goods) which drives sourcing decisions downstream.
 *   - Additional ad-hoc materials lines (client_po_item_id=0) can be added at scrub time.
 *
 * Auxiliary pillars (Preliminaries, Manpower, Equipment, Tools, Subcontracts, Overheads)
 * capture the "hidden costs" of delivering goods — loading, transport, insurance, HSE.
 *
 * Lifecycle: DRAFT → COMPLETE. On complete, Procurement can begin RFQs / LPO drafting
 * against the materials pillar.
 */
class GoodsScrubPack extends Model
{
    /** Pillar → line-table + total-column + sum-column mapping (works_cr pattern). */
    public const PILLARS = [
        'PRELIMINARIES' => ['table' => 'goods_scrub_preliminaries', 'total_col' => 'total_preliminaries', 'sum_col' => 'amount'],
        'MANPOWER'      => ['table' => 'goods_scrub_manpower',      'total_col' => 'total_manpower',      'sum_col' => 'total'],
        'MATERIALS'     => ['table' => 'goods_scrub_materials',     'total_col' => 'total_materials',     'sum_col' => 'total'],
        'EQUIPMENT'     => ['table' => 'goods_scrub_equipment',     'total_col' => 'total_equipment',     'sum_col' => 'total'],
        'TOOLS'         => ['table' => 'goods_scrub_tools',         'total_col' => 'total_tools',         'sum_col' => 'total'],
        'SUBCONTRACTS'  => ['table' => 'goods_scrub_subcontracts',  'total_col' => 'total_subcontracts',  'sum_col' => 'lump_sum'],
        'OVERHEADS'     => ['table' => 'goods_scrub_overheads',     'total_col' => 'total_overheads',     'sum_col' => 'amount'],
    ];

    /* ── Header ──────────────────────────────────────────────────────── */

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

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

    /**
     * Get the scrub for a Client PO, or create a fresh DRAFT one — auto-seeding the
     * MATERIALS pillar from the PO's items so PC starts with a real baseline.
     * Guard: PO must be CONFIRMED (i.e., CRM chain approved) or SOURCING.
     */
    public function getOrCreateDraft(int $poId, int $userId, string $userName): array
    {
        if ($poId <= 0) return ['ok' => false, 'msg' => 'client_po_id required.', 'id' => 0];
        $db = new DB();
        $po = $db->mySelect(
            "SELECT c0d, project_id, status FROM goods_client_pos WHERE c0d = :id AND deleted = 0",
            [':id' => $poId]
        );
        if (empty($po))                                    return ['ok' => false, 'msg' => 'Client PO not found.', 'id' => 0];
        if (!in_array($po[0]['status'], ['CONFIRMED','SOURCING'], true)) {
            return ['ok' => false, 'msg' => 'Client PO must be CONFIRMED to begin the scrub (current: ' . $po[0]['status'] . ').', 'id' => 0];
        }

        $ex = $this->findByClientPo($poId);
        if ($ex) return ['ok' => true, 'id' => (int)$ex['c0d'], 'created' => false];

        $projectId = (int)$po[0]['project_id'];
        // Slice 8i · central atomic doc numbering
        if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
        $scrubNo = (new DocNumberSettings())->allocate('SCRUB');

        $fb = $db->insertUpdate(
            "INSERT INTO goods_scrubs
                (client_po_id, project_id, scrub_no, status, created_by, created_by_name, created_on)
             VALUES (:p, :pj, :no, 'DRAFT', :u, :un, NOW())",
            [':p' => $poId, ':pj' => $projectId, ':no' => $scrubNo, ':u' => $userId, ':un' => $userName]
        );
        $scrubId = (int)($fb['last_id'] ?? 0);
        if ($scrubId <= 0) return ['ok' => false, 'msg' => 'Save failed on scrub header.', 'id' => 0];

        // Auto-seed MATERIALS pillar from Client PO items (idempotent per client_po_item_id)
        $this->seedMaterialsFromPo($scrubId, $poId);
        $this->recomputeTotals($scrubId);

        return ['ok' => true, 'id' => $scrubId, 'created' => true, 'scrub_no' => $scrubNo];
    }

    /** Copy PO items into MATERIALS lines. Idempotent — skips items already seeded. */
    public function seedMaterialsFromPo(int $scrubId, int $poId): int
    {
        $db = new DB();
        $items = $db->mySelect(
            "SELECT c0d, description, brand, source_pref, qty, unit, unit_price_cog
               FROM goods_client_po_items WHERE client_po_id = :p ORDER BY item_no, c0d",
            [':p' => $poId]
        ) ?: [];
        $n = 0;
        foreach ($items as $it) {
            $ex = $db->mySelect(
                "SELECT c0d FROM goods_scrub_materials WHERE scrub_id = :s AND client_po_item_id = :i AND deleted = 0 LIMIT 1",
                [':s' => $scrubId, ':i' => (int)$it['c0d']]
            );
            if (!empty($ex)) continue;

            $qty     = (float)$it['qty'];
            $unitCog = (float)$it['unit_price_cog'];
            $total   = round($qty * $unitCog, 2);
            $db->insertUpdate(
                "INSERT INTO goods_scrub_materials
                    (scrub_id, client_po_item_id, line_no, description, brand, source_pref,
                     qty, unit, unit_cost, total, notes, created_on)
                 VALUES (:s, :i, :no, :d, :br, :sp,
                         :q, :u, :uc, :t, :nt, NOW())",
                [
                    ':s' => $scrubId, ':i' => (int)$it['c0d'],
                    ':no' => $n + 1,
                    ':d' => (string)$it['description'], ':br' => (string)$it['brand'],
                    ':sp' => (string)($it['source_pref'] ?: 'ANY'),
                    ':q' => $qty, ':u' => (string)$it['unit'],
                    ':uc' => $unitCog, ':t' => $total,
                    ':nt' => 'Seeded from Client PO item ' . (int)$it['c0d'],
                ]
            );
            $n++;
        }
        return $n;
    }

    /* ── Lines: add / remove / setMaterialsUnitCost ──────────────────── */

    private function assertDraft(int $scrubId): array
    {
        $s = $this->findById($scrubId);
        if (!$s)                                    return ['ok' => false, 'msg' => 'Scrub not found.'];
        if ($s['status'] !== 'DRAFT')                return ['ok' => false, 'msg' => 'Scrub is ' . $s['status'] . ' — editing locked.'];
        return ['ok' => true, 'scrub' => $s];
    }

    public function addLine(int $scrubId, string $pillar, array $d): array
    {
        $pillar = strtoupper($pillar);
        if (!isset(self::PILLARS[$pillar]))          return ['ok' => false, 'msg' => 'Unknown pillar: ' . $pillar];
        $chk = $this->assertDraft($scrubId);
        if (!$chk['ok']) return $chk;

        $table = self::PILLARS[$pillar]['table'];
        $db = new DB();
        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(line_no),0)+1 n FROM `$table` WHERE scrub_id = :s AND deleted = 0",
            [':s' => $scrubId]
        )[0]['n'] ?? 1);

        // Per-pillar insert (shape-dependent).
        switch ($pillar) {
            case 'PRELIMINARIES':
                $db->insertUpdate(
                    "INSERT INTO goods_scrub_preliminaries (scrub_id, line_no, description, amount, notes, created_on)
                     VALUES (:s, :n, :d, :a, :nt, NOW())",
                    [':s' => $scrubId, ':n' => $nextNo,
                     ':d' => (string)($d['description'] ?? ''),
                     ':a' => (float)($d['amount'] ?? 0),
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                break;
            case 'MANPOWER':
                $persons = (int)($d['persons'] ?? 1);
                $days    = (float)($d['days'] ?? 1);
                $rate    = (float)($d['rate'] ?? 0);
                $total   = round($persons * $days * $rate, 2);
                $db->insertUpdate(
                    "INSERT INTO goods_scrub_manpower (scrub_id, line_no, role, persons, days, rate, total, notes, created_on)
                     VALUES (:s, :n, :r, :p, :d, :ra, :t, :nt, NOW())",
                    [':s' => $scrubId, ':n' => $nextNo,
                     ':r' => (string)($d['role'] ?? ''),
                     ':p' => $persons, ':d' => $days, ':ra' => $rate, ':t' => $total,
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                break;
            case 'MATERIALS':
                $qty     = (float)($d['qty'] ?? 0);
                $unitCog = (float)($d['unit_cost'] ?? 0);
                $total   = round($qty * $unitCog, 2);
                $db->insertUpdate(
                    "INSERT INTO goods_scrub_materials
                        (scrub_id, client_po_item_id, line_no, description, brand, source_pref,
                         qty, unit, unit_cost, total, notes, created_on)
                     VALUES (:s, 0, :n, :d, :br, :sp,
                             :q, :u, :uc, :t, :nt, NOW())",
                    [':s' => $scrubId, ':n' => $nextNo,
                     ':d' => (string)($d['description'] ?? ''),
                     ':br' => (string)($d['brand'] ?? ''),
                     ':sp' => strtoupper((string)($d['source_pref'] ?? 'ANY')),
                     ':q' => $qty, ':u' => (string)($d['unit'] ?? ''),
                     ':uc' => $unitCog, ':t' => $total,
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                break;
            case 'EQUIPMENT':
                $hours = (float)($d['hours'] ?? 0);
                $rate  = (float)($d['rate'] ?? 0);
                $total = round($hours * $rate, 2);
                $db->insertUpdate(
                    "INSERT INTO goods_scrub_equipment
                        (scrub_id, line_no, description, asset_id, hours, rate, total, in_house_flag, notes, created_on)
                     VALUES (:s, :n, :d, :a, :h, :r, :t, :fh, :nt, NOW())",
                    [':s' => $scrubId, ':n' => $nextNo,
                     ':d' => (string)($d['description'] ?? ''),
                     ':a' => (int)($d['asset_id'] ?? 0),
                     ':h' => $hours, ':r' => $rate, ':t' => $total,
                     ':fh' => (int)($d['in_house_flag'] ?? 1),
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                break;
            case 'TOOLS':
                $qty = (float)($d['qty'] ?? 0);
                $uc  = (float)($d['unit_cost'] ?? 0);
                $t   = round($qty * $uc, 2);
                $db->insertUpdate(
                    "INSERT INTO goods_scrub_tools (scrub_id, line_no, description, qty, unit, unit_cost, total, notes, created_on)
                     VALUES (:s, :n, :d, :q, :u, :uc, :t, :nt, NOW())",
                    [':s' => $scrubId, ':n' => $nextNo,
                     ':d' => (string)($d['description'] ?? ''),
                     ':q' => $qty, ':u' => (string)($d['unit'] ?? ''),
                     ':uc' => $uc, ':t' => $t,
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                break;
            case 'SUBCONTRACTS':
                $db->insertUpdate(
                    "INSERT INTO goods_scrub_subcontracts (scrub_id, line_no, subcontractor_name, scope, lump_sum, notes, created_on)
                     VALUES (:s, :n, :sc, :sp, :ls, :nt, NOW())",
                    [':s' => $scrubId, ':n' => $nextNo,
                     ':sc' => (string)($d['subcontractor_name'] ?? ''),
                     ':sp' => (string)($d['scope'] ?? ''),
                     ':ls' => (float)($d['lump_sum'] ?? 0),
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                break;
            case 'OVERHEADS':
                $db->insertUpdate(
                    "INSERT INTO goods_scrub_overheads (scrub_id, line_no, category, description, amount, notes, created_on)
                     VALUES (:s, :n, :c, :d, :a, :nt, NOW())",
                    [':s' => $scrubId, ':n' => $nextNo,
                     ':c' => strtoupper((string)($d['category'] ?? 'OTHER')),
                     ':d' => (string)($d['description'] ?? ''),
                     ':a' => (float)($d['amount'] ?? 0),
                     ':nt' => (string)($d['notes'] ?? '')]
                );
                break;
        }
        $this->recomputeTotals($scrubId);
        return ['ok' => true];
    }

    public function removeLine(int $scrubId, string $pillar, int $lineId): array
    {
        $pillar = strtoupper($pillar);
        if (!isset(self::PILLARS[$pillar]))          return ['ok' => false, 'msg' => 'Unknown pillar: ' . $pillar];
        $chk = $this->assertDraft($scrubId);
        if (!$chk['ok']) return $chk;
        $table = self::PILLARS[$pillar]['table'];
        $db = new DB();
        $db->insertUpdate(
            "UPDATE `$table` SET deleted = 1 WHERE c0d = :id AND scrub_id = :s",
            [':id' => $lineId, ':s' => $scrubId]
        );
        $this->recomputeTotals($scrubId);
        return ['ok' => true];
    }

    /** MATERIALS pillar helper — PC/Procurement enrich unit_cost on a seeded line. */
    public function setMaterialsUnitCost(int $scrubId, int $lineId, float $unitCost): array
    {
        $chk = $this->assertDraft($scrubId);
        if (!$chk['ok']) return $chk;
        if ($unitCost < 0) return ['ok' => false, 'msg' => 'unit_cost must be >= 0.'];
        $db = new DB();
        $line = $db->mySelect("SELECT qty FROM goods_scrub_materials WHERE c0d = :id AND scrub_id = :s AND deleted = 0", [':id' => $lineId, ':s' => $scrubId]);
        if (empty($line)) return ['ok' => false, 'msg' => 'MATERIALS line not found.'];
        $total = round((float)$line[0]['qty'] * $unitCost, 2);
        $db->insertUpdate(
            "UPDATE goods_scrub_materials SET unit_cost = :uc, total = :t WHERE c0d = :id",
            [':uc' => $unitCost, ':t' => $total, ':id' => $lineId]
        );
        $this->recomputeTotals($scrubId);
        return ['ok' => true];
    }

    /* ── Read helpers ────────────────────────────────────────────────── */

    public function linesForPillar(int $scrubId, 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 scrub_id = :s AND deleted = 0 ORDER BY line_no, c0d",
            [':s' => $scrubId]
        ) ?: [];
    }

    public function allLines(int $scrubId): array
    {
        $out = [];
        foreach (self::PILLARS as $pillar => $meta) $out[$pillar] = $this->linesForPillar($scrubId, $pillar);
        return $out;
    }

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

    public function recomputeTotals(int $scrubId): void
    {
        $db = new DB();
        $grand = 0.0;
        $setSql = [];
        $bind   = [':id' => $scrubId];
        foreach (self::PILLARS as $pillar => $meta) {
            $t = (float)($db->mySelect(
                "SELECT COALESCE(SUM(`{$meta['sum_col']}`),0) t FROM `{$meta['table']}` WHERE scrub_id = :s AND deleted = 0",
                [':s' => $scrubId]
            )[0]['t'] ?? 0);
            $setSql[] = "`{$meta['total_col']}` = :" . strtolower($pillar);
            $bind[':' . strtolower($pillar)] = $t;
            $grand += $t;
        }
        $setSql[] = "grand_total = :gt";
        $bind[':gt'] = round($grand, 2);
        $db->insertUpdate(
            "UPDATE goods_scrubs SET " . implode(', ', $setSql) . ", edited_on = NOW() WHERE c0d = :id",
            $bind
        );
    }

    /* ── Complete ────────────────────────────────────────────────────── */

    public function markComplete(int $scrubId, int $userId, string $userName, string $userGroup): array
    {
        $scrub = $this->findById($scrubId);
        if (!$scrub)                                 return ['ok' => false, 'msg' => 'Scrub not found.'];
        if ($scrub['status'] !== 'DRAFT')            return ['ok' => false, 'msg' => 'Scrub is ' . $scrub['status'] . ' — already complete.'];
        if (!in_array($userGroup, ['Projects Coordinator','Project Manager','Procurement','Administrator'], true)) {
            return ['ok' => false, 'msg' => 'Only PC / PM / Procurement / Admin can mark scrub complete.'];
        }
        $db = new DB();
        // Guard: MATERIALS must have every line with unit_cost > 0
        $missingRow = $db->mySelect(
            "SELECT COUNT(*) n FROM goods_scrub_materials WHERE scrub_id = :s AND deleted = 0 AND unit_cost <= 0",
            [':s' => $scrubId]
        );
        $missing = (int)($missingRow[0]['n'] ?? 0);
        if ($missing > 0) return ['ok' => false, 'msg' => $missing . ' MATERIALS line(s) still have unit_cost = 0 — Procurement must estimate before completing.'];

        $db->insertUpdate(
            "UPDATE goods_scrubs
                SET status = 'COMPLETE',
                    completed_by = :u, completed_by_name = :un, completed_on = NOW(),
                    edited_by = :u, edited_on = NOW()
              WHERE c0d = :id AND status = 'DRAFT'",
            [':u' => $userId, ':un' => $userName, ':id' => $scrubId]
        );
        return ['ok' => true, 'msg' => 'Scrub complete · sourcing can begin.'];
    }
}
