<?php
class WorkRequestItems extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function m($v): float  { return (float)str_replace(',', '', (string)$v); }

    public function forRequest(int $wrId): array
    {
        return (new DB())->mySelect(
            "SELECT i.c0d, i.seq_no, i.item_type, i.description, i.category,
                    i.qty, i.uom, i.unit_cost_est, i.total_est,
                    i.vendor_id, i.actual_cost, i.sourcing_status, i.notes,
                    v.vendor_name
               FROM work_request_items i
          LEFT JOIN vendors v ON v.c0d = i.vendor_id
              WHERE i.work_request_id = :id AND i.deleted = 0
           ORDER BY i.seq_no, i.c0d",
            [':id' => $wrId]
        ) ?: [];
    }

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect("SELECT * FROM work_request_items WHERE c0d = :id AND deleted = 0 LIMIT 1", [':id' => $id]);
        return $rs[0] ?? [];
    }

    public function sumEstForRequest(int $wrId): float
    {
        $rs = (new DB())->mySelect(
            "SELECT COALESCE(SUM(total_est),0) AS s FROM work_request_items WHERE work_request_id = :id AND deleted = 0",
            [':id' => $wrId]
        );
        return (float)($rs[0]['s'] ?? 0);
    }

    public function create(int $wrId, array $d, int $uid): array
    {
        $desc = $this->s($d['description'] ?? '');
        if ($desc === '') return ['ok' => false, 'msg' => 'Description is required.', 'id' => 0];
        $qty       = $this->m($d['qty'] ?? 1) ?: 1;
        $unitEst   = $this->m($d['unit_cost_est'] ?? 0);
        $totalEst  = round($qty * $unitEst, 2);

        $rs = (new DB())->mySelect(
            "SELECT COALESCE(MAX(seq_no),0) + 1 AS n FROM work_request_items WHERE work_request_id = :id",
            [':id' => $wrId]
        );
        $seq = (int)($rs[0]['n'] ?? 1);

        $r = (new DB())->insertUpdate(
            "INSERT INTO work_request_items
                (work_request_id, seq_no, item_type, description, category,
                 qty, uom, unit_cost_est, total_est, notes, created_by, created_on)
             VALUES (:wr, :sq, :it, :desc, :cat, :qty, :uom, :uc, :tc, :notes, :uid, NOW())",
            [
                ':wr'    => $wrId,
                ':sq'    => $seq,
                ':it'    => $this->s(($d['item_type'] ?? '') ?: 'part'),
                ':desc'  => $desc,
                ':cat'   => $this->s(($d['category'] ?? '') ?: 'spare_parts'),
                ':qty'   => round($qty, 3),
                ':uom'   => $this->s(($d['uom'] ?? '') ?: 'unit'),
                ':uc'    => round($unitEst, 2),
                ':tc'    => $totalEst,
                ':notes' => $this->s($d['notes'] ?? ''),
                ':uid'   => $uid,
            ]
        );

        // Recompute WR estimated_cost
        (new DB())->insertUpdate(
            "UPDATE work_requests
                SET estimated_cost = (SELECT COALESCE(SUM(total_est),0) FROM work_request_items WHERE work_request_id = :id AND deleted = 0),
                    edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':id' => $wrId, ':uid' => $uid]
        );

        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Item added.' : 'Save failed.', 'id' => (int)$r['last_id']];
    }

    public function softDelete(int $id, int $uid): array
    {
        $item = $this->byId($id);
        if (!$item) return ['ok' => false, 'msg' => 'Not found.'];
        $wrId = (int)$item['work_request_id'];

        (new DB())->insertUpdate(
            "UPDATE work_request_items SET deleted = 1 WHERE c0d = :id",
            [':id' => $id]
        );
        (new DB())->insertUpdate(
            "UPDATE work_requests
                SET estimated_cost = (SELECT COALESCE(SUM(total_est),0) FROM work_request_items WHERE work_request_id = :id AND deleted = 0),
                    edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':id' => $wrId, ':uid' => $uid]
        );
        return ['ok' => true, 'msg' => 'Item removed.'];
    }
}
