<?php
/**
 * ServicePacks — Slice 8S-1 · Services Master Data
 *
 * Spares BOM per (asset_type × size range × service interval). Each generator/AC/etc. asset
 * auto-matches to a pack via size falling inside pack.size_min..size_max range.
 *
 * When a PM is scheduled, the system resolves the applicable pack and auto-generates a
 * spare parts requisition. In-stock parts get issued; missing parts trigger a Goods LPO.
 */
class ServicePacks extends Model
{
    public function list(bool $activeOnly = false, ?int $assetTypeId = null): array
    {
        $db = new DB();
        $params = [];
        $where = "WHERE 1=1";
        if ($activeOnly)                { $where .= " AND p.is_active = 1"; }
        if ($assetTypeId !== null)      { $where .= " AND p.asset_type_id = :a"; $params[':a'] = $assetTypeId; }
        return $db->mySelect(
            "SELECT p.c0d, p.code, p.name, p.description, p.size_min, p.size_max, p.size_unit, p.is_active,
                    a.code AS asset_type_code, a.name AS asset_type_name, a.icon AS asset_icon,
                    (SELECT COUNT(*) FROM service_pack_items WHERE pack_id = p.c0d) AS item_count,
                    (SELECT COALESCE(SUM(pi.qty * sp.unit_cost),0)
                       FROM service_pack_items pi
                       JOIN spare_parts sp ON sp.c0d = pi.part_id
                      WHERE pi.pack_id = p.c0d) AS pack_cost_total
               FROM service_packs p
          LEFT JOIN asset_types a ON a.c0d = p.asset_type_id
               $where
              ORDER BY a.code, p.size_min, p.code",
            $params
        ) ?: [];
    }

    public function find(int $id): array
    {
        $db = new DB();
        $r  = $db->mySelect(
            "SELECT p.*, a.code AS asset_type_code, a.name AS asset_type_name, a.icon AS asset_icon
               FROM service_packs p
          LEFT JOIN asset_types a ON a.c0d = p.asset_type_id
              WHERE p.c0d = :id",
            [':id' => $id]
        );
        return $r[0] ?? [];
    }

    public function itemsFor(int $packId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT i.c0d, i.interval_hrs, i.interval_months, i.qty, i.notes,
                    p.c0d AS part_id, p.sku, p.name AS part_name, p.uom, p.unit_cost, p.currency,
                    ROUND(i.qty * IFNULL(p.unit_cost, 0), 2) AS line_cost
               FROM service_pack_items i
          LEFT JOIN spare_parts p ON p.c0d = i.part_id
              WHERE i.pack_id = :p
              ORDER BY COALESCE(i.interval_hrs, i.interval_months), p.sku",
            [':p' => $packId]
        ) ?: [];
    }

    public function saveHeader(array $d, int $uid): int
    {
        $code = strtoupper(trim((string)($d['code'] ?? '')));
        $name = trim((string)($d['name'] ?? ''));
        $atId = !empty($d['asset_type_id']) ? (int)$d['asset_type_id'] : 0;
        if ($code === '' || $name === '' || $atId <= 0) throw new InvalidArgumentException('Code + name + asset_type_id required.');

        $db = new DB();
        $id = (int)($d['c0d'] ?? 0);
        $data = [
            ':c'    => $code,
            ':n'    => $name,
            ':d'    => trim((string)($d['description'] ?? '')) ?: null,
            ':at'   => $atId,
            ':smn'  => isset($d['size_min']) && $d['size_min'] !== '' ? (int)$d['size_min'] : null,
            ':smx'  => isset($d['size_max']) && $d['size_max'] !== '' ? (int)$d['size_max'] : null,
            ':su'   => trim((string)($d['size_unit'] ?? 'kVA')) ?: 'kVA',
            ':a'    => !empty($d['is_active']) ? 1 : 0,
        ];
        if ($id > 0) {
            $data[':id'] = $id;
            $db->insertUpdate(
                "UPDATE service_packs SET code=:c, name=:n, description=:d, asset_type_id=:at,
                    size_min=:smn, size_max=:smx, size_unit=:su, is_active=:a, edited_on=NOW()
                  WHERE c0d=:id",
                $data
            );
            return $id;
        }
        $data[':ub'] = $uid;
        $fb = $db->insertUpdate(
            "INSERT INTO service_packs (code, name, description, asset_type_id, size_min, size_max, size_unit, is_active, created_by)
             VALUES (:c, :n, :d, :at, :smn, :smx, :su, :a, :ub)",
            $data
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function saveItem(array $d): int
    {
        $pid   = (int)($d['pack_id'] ?? 0);
        $part  = (int)($d['part_id'] ?? 0);
        $qty   = (float)($d['qty'] ?? 0);
        if ($pid <= 0 || $part <= 0 || $qty <= 0) throw new InvalidArgumentException('pack_id + part_id + qty required.');
        $ihr   = !empty($d['interval_hrs']) ? (int)$d['interval_hrs'] : null;
        $imo   = !empty($d['interval_months']) ? (int)$d['interval_months'] : null;
        if ($ihr === null && $imo === null) throw new InvalidArgumentException('Provide interval_hrs OR interval_months.');

        $db = new DB();
        $id = (int)($d['c0d'] ?? 0);
        if ($id > 0) {
            $db->insertUpdate(
                "UPDATE service_pack_items SET interval_hrs=:ih, interval_months=:im, part_id=:pt, qty=:q, notes=:nt WHERE c0d=:id",
                [':ih'=>$ihr, ':im'=>$imo, ':pt'=>$part, ':q'=>$qty,
                 ':nt'=>trim((string)($d['notes'] ?? '')) ?: null, ':id'=>$id]
            );
            return $id;
        }
        $fb = $db->insertUpdate(
            "INSERT INTO service_pack_items (pack_id, interval_hrs, interval_months, part_id, qty, notes)
             VALUES (:p, :ih, :im, :pt, :q, :nt)",
            [':p'=>$pid, ':ih'=>$ihr, ':im'=>$imo, ':pt'=>$part, ':q'=>$qty,
             ':nt'=>trim((string)($d['notes'] ?? '')) ?: null]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function deleteItem(int $id): void
    {
        $db = new DB();
        $db->insertUpdate("DELETE FROM service_pack_items WHERE c0d = :id", [':id' => $id]);
    }

    /**
     * Auto-match: find the pack that fits an asset of given asset_type + size (e.g. kVA).
     * Prefers the tightest range (smallest max-min gap).
     */
    public function findForAsset(int $assetTypeId, int $size = 0): array
    {
        if ($assetTypeId <= 0) return [];
        $db = new DB();
        $r  = $db->mySelect(
            "SELECT c0d FROM service_packs
              WHERE asset_type_id = :a AND is_active = 1
                AND (size_min IS NULL OR size_min <= :s1)
                AND (size_max IS NULL OR size_max >= :s2)
              ORDER BY (COALESCE(size_max, 99999) - COALESCE(size_min, 0)) ASC
              LIMIT 1",
            [':a' => $assetTypeId, ':s1' => $size, ':s2' => $size]
        );
        return !empty($r) ? $this->find((int)$r[0]['c0d']) : [];
    }
}
