<?php
/**
 * GoodsScrub — the "Projects Coordinator scrubs the client PO" step.
 *
 * Between project provisioning and Procurement sourcing, the PC verifies each item on
 * the auto-created client PO: fix descriptions, tighten quantities, set the sell/cog
 * split so a margin per line is explicit. Once done the PO is "SCRUBBED" and ready
 * for RFQ.
 *
 * Rollup: line.line_total_sell = qty * unit_price_sell
 *          line.line_total_cog  = qty * unit_price_cog
 *          line.margin_pct       = ((sell - cog) / sell) * 100
 *          po.subtotal_sell/subtotal_cog rolled up + VAT recomputed.
 */
class GoodsScrub extends Model
{
    /** Client POs belonging to a project. Used by the project-scoped landing page. */
    public function posForProject(int $projectId): array
    {
        if ($projectId <= 0) return [];
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, client_po_no, customer_name, po_date, currency, vat_rate,
                    subtotal_sell, subtotal_cog, vat_amount, total,
                    status, scrubbed_at, scrubbed_by_name, notes
               FROM goods_client_pos
              WHERE project_id = :p AND deleted = 0
              ORDER BY c0d DESC",
            [':p' => $projectId]
        ) ?: [];
    }

    /** Assert scrub is still allowed (status DRAFT / CONFIRMED / SOURCING). */
    public function assertEditable(array $po): void
    {
        if (!in_array((string)$po['status'], ['DRAFT', 'CONFIRMED', 'SOURCING'], true)) {
            throw new RuntimeException('Client PO status "' . $po['status'] . '" is past the scrub window.');
        }
    }

    /**
     * Update one item's scrub fields (description, qty, unit, sell, cog, notes).
     * Derives line totals + margin, then rolls up the header.
     */
    public function saveItem(int $itemId, array $d, int $actorId): void
    {
        $db = new DB();
        $row = $db->mySelect("SELECT client_po_id FROM goods_client_po_items WHERE c0d = :id", [':id' => $itemId]);
        if (empty($row)) throw new RuntimeException('Item not found.');
        $poId = (int)$row[0]['client_po_id'];
        $po   = $db->mySelect("SELECT * FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po)) throw new RuntimeException('Parent Client PO not found.');
        $this->assertEditable($po[0]);

        $qty  = (float)($d['qty'] ?? 0);
        $sell = (float)($d['unit_price_sell'] ?? 0);
        $cog  = (float)($d['unit_price_cog'] ?? 0);
        $margin = ($sell > 0) ? round((($sell - $cog) / $sell) * 100, 2) : 0.0;
        $ltSell = round($qty * $sell, 2);
        $ltCog  = round($qty * $cog, 2);

        $db->insertUpdate(
            "UPDATE goods_client_po_items
                SET description = :desc, qty = :q, unit = :u,
                    unit_price_sell = :s, unit_price_cog = :c, margin_pct = :m,
                    line_total_sell = :lts, line_total_cog = :ltc,
                    notes = :nt, scrubbed_at = NOW()
              WHERE c0d = :id",
            [
                ':desc' => (string)($d['description'] ?? ''),
                ':q'    => $qty, ':u' => (string)($d['unit'] ?? ''),
                ':s'    => $sell, ':c' => $cog, ':m' => $margin,
                ':lts'  => $ltSell, ':ltc' => $ltCog,
                ':nt'   => (string)($d['notes'] ?? ''),
                ':id'   => $itemId,
            ]
        );
        $this->rollupHeader($poId, $actorId);
    }

    /** Sum active items → subtotal_sell/cog, vat_amount, total. */
    public function rollupHeader(int $poId, int $actorId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE goods_client_pos p
                SET subtotal_sell = IFNULL((SELECT SUM(line_total_sell) FROM goods_client_po_items WHERE client_po_id = p.c0d), 0),
                    subtotal_cog  = IFNULL((SELECT SUM(line_total_cog)  FROM goods_client_po_items WHERE client_po_id = p.c0d), 0),
                    edited_by = :u, edited_on = NOW()
              WHERE p.c0d = :id",
            [':u' => $actorId, ':id' => $poId]
        );
        $db->insertUpdate(
            "UPDATE goods_client_pos
                SET vat_amount = ROUND(subtotal_sell * vat_rate / 100, 2),
                    total      = ROUND(subtotal_sell + (subtotal_sell * vat_rate / 100), 2)
              WHERE c0d = :id",
            [':id' => $poId]
        );
    }

    /**
     * Mark scrub complete. Requires at least one item scrubbed and all sell prices > 0.
     * Bumps PO status from DRAFT → CONFIRMED so downstream (RFQ) can begin.
     */
    public function markScrubbed(int $poId, int $actorId, string $actorName, string $scrubNotes = ''): void
    {
        $db = new DB();
        $po = $db->mySelect("SELECT * FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po)) throw new RuntimeException('Client PO not found.');
        $this->assertEditable($po[0]);

        $items = $db->mySelect("SELECT COUNT(*) n, SUM(CASE WHEN unit_price_sell > 0 THEN 1 ELSE 0 END) priced FROM goods_client_po_items WHERE client_po_id = :id", [':id' => $poId]);
        $n      = (int)($items[0]['n'] ?? 0);
        $priced = (int)($items[0]['priced'] ?? 0);
        if ($n === 0)      throw new RuntimeException('Client PO has no items to scrub.');
        if ($priced < $n)  throw new RuntimeException($n - $priced . ' item(s) have zero sell price — set unit_price_sell before completing scrub.');

        $newStatus = $po[0]['status'] === 'DRAFT' ? 'CONFIRMED' : (string)$po[0]['status'];
        $db->insertUpdate(
            "UPDATE goods_client_pos
                SET scrubbed_at = NOW(), scrubbed_by_id = :a, scrubbed_by_name = :an,
                    scrub_notes = :nt, status = :s,
                    edited_by = :a, edited_on = NOW()
              WHERE c0d = :id",
            [':a' => $actorId, ':an' => $actorName, ':nt' => $scrubNotes, ':s' => $newStatus, ':id' => $poId]
        );
        $this->rollupHeader($poId, $actorId);
    }

    /** Undo scrub (back to DRAFT) — allowed only if no RFQ has started against this PO. */
    public function unscrub(int $poId, int $actorId): void
    {
        $db = new DB();
        $rfq = $db->mySelect("SELECT COUNT(*) n FROM goods_rfqs WHERE client_po_id = :id AND deleted = 0", [':id' => $poId]);
        if ((int)($rfq[0]['n'] ?? 0) > 0) throw new RuntimeException('Cannot unscrub — an RFQ already exists for this PO.');
        $db->insertUpdate(
            "UPDATE goods_client_pos
                SET scrubbed_at = NULL, scrubbed_by_id = 0, scrubbed_by_name = '', scrub_notes = '',
                    status = 'DRAFT', edited_by = :a, edited_on = NOW()
              WHERE c0d = :id",
            [':a' => $actorId, ':id' => $poId]
        );
    }
}
