<?php
/**
 * SpareRequests · Slice 8Z-z-D.
 *
 * Site-scoped spare-part change requests. Walks a 3-step approval chain
 * (POC_CONFIRMATION → PM_APPROVAL → PROCUREMENT_TRIAGE) via ApprovalChain.
 * Emergency-urgency requests get fast-tracked: POC step is optional and can
 * be captured post-facto.
 *
 * Downstream branches after Procurement triage:
 *   FROM_STOCK      → status STOCK_ISSUED   (issue slip in sp_issue_requests, later slice)
 *   PROCURE         → status SOURCING       (goods LPO auto-drafted, later slice)
 *   EMERGENCY_CASH  → status SOURCING       (IMPREST LPO via CalloutsController::raiseCashRequestAj)
 */
class SpareRequests extends Model
{
    public const URGENCY  = ['EMERGENCY','URGENT','ROUTINE'];
    public const WHO_PAYS = ['CLIENT','INTERNAL','MIXED','TBD'];
    public const STATUS   = [
        'DRAFT','AWAITING_POC','POC_APPROVED','POC_DECLINED',
        'AWAITING_PM','PM_APPROVED','PM_DECLINED',
        'WITH_PROCUREMENT','STOCK_ISSUED','SOURCING','ORDERED',
        'DELIVERED','INSTALLED','CLOSED','CANCELLED'
    ];
    public const POC_METHODS = ['EMAIL','WHATSAPP','PHONE','ONSITE_SIGNATURE','OTHER'];

    /** Header rows for one contract, with computed line count + sum estimate. */
    public function listForContract(int $contractId): array
    {
        return (new DB())->mySelect(
            "SELECT r.*,
                    s.site_code, s.name AS site_name,
                    (SELECT COUNT(*) FROM spare_request_items i WHERE i.request_id = r.c0d AND i.deleted = 0) AS item_count,
                    (SELECT COALESCE(SUM(i.qty * COALESCE(i.unit_cost_estimate,0)),0)
                       FROM spare_request_items i WHERE i.request_id = r.c0d AND i.deleted = 0) AS est_total
               FROM spare_requests r
          LEFT JOIN service_contract_sites s ON s.c0d = r.site_id
              WHERE r.contract_id = :c AND r.deleted = 0
              ORDER BY r.created_on DESC, r.c0d DESC",
            [':c' => $contractId]
        ) ?: [];
    }

    /** Header rows for one site — used by the site page. */
    public function listForSite(int $siteId): array
    {
        return (new DB())->mySelect(
            "SELECT r.*,
                    (SELECT COUNT(*) FROM spare_request_items i WHERE i.request_id = r.c0d AND i.deleted = 0) AS item_count,
                    (SELECT COALESCE(SUM(i.qty * COALESCE(i.unit_cost_estimate,0)),0)
                       FROM spare_request_items i WHERE i.request_id = r.c0d AND i.deleted = 0) AS est_total
               FROM spare_requests r
              WHERE r.site_id = :s AND r.deleted = 0
              ORDER BY r.created_on DESC, r.c0d DESC",
            [':s' => $siteId]
        ) ?: [];
    }

    /** Items for one request. */
    public function itemsFor(int $requestId): array
    {
        return (new DB())->mySelect(
            "SELECT i.*, sp.sku, sp.name AS part_name_catalog, sp.uom AS part_uom_catalog
               FROM spare_request_items i
          LEFT JOIN spare_parts sp ON sp.c0d = i.part_id
              WHERE i.request_id = :r AND i.deleted = 0
              ORDER BY i.c0d",
            [':r' => $requestId]
        ) ?: [];
    }

    /** Single header + items. */
    public function find(int $requestId): ?array
    {
        $r = (new DB())->mySelect("SELECT * FROM spare_requests WHERE c0d = :id AND deleted = 0", [':id' => $requestId]);
        if (empty($r)) return null;
        $row = $r[0];
        $row['items'] = $this->itemsFor($requestId);
        return $row;
    }

    /** Header upsert — DRAFT state only. Post-submit changes go through approval log. */
    public function save(array $d, int $userId): array
    {
        $cid = (int)($d['contract_id'] ?? 0);
        $sid = (int)($d['site_id']     ?? 0);
        if ($cid <= 0 || $sid <= 0) return ['ok' => false, 'msg' => 'contract_id + site_id required.'];

        $urgency = strtoupper((string)($d['urgency'] ?? 'ROUTINE'));
        if (!in_array($urgency, self::URGENCY, true)) $urgency = 'ROUTINE';
        $whoPays = strtoupper((string)($d['who_pays'] ?? 'TBD'));
        if (!in_array($whoPays, self::WHO_PAYS, true)) $whoPays = 'TBD';

        $db = new DB();
        $id = (int)($d['c0d'] ?? 0);
        $params = [
            ':c'  => $cid, ':s' => $sid,
            ':co' => isset($d['callout_id']) && $d['callout_id'] !== '' ? (int)$d['callout_id'] : null,
            ':u'  => $urgency,
            ':w'  => $whoPays,
            ':r'  => trim((string)($d['reason'] ?? '')) ?: null,
            ':n'  => trim((string)($d['notes']  ?? '')) ?: null,
        ];

        if ($id > 0) {
            // Only DRAFT + AWAITING_POC statuses are editable inline.
            $cur = $db->mySelect("SELECT status FROM spare_requests WHERE c0d = :id", [':id' => $id]);
            $curStatus = strtoupper((string)($cur[0]['status'] ?? ''));
            if (!in_array($curStatus, ['DRAFT','AWAITING_POC','AWAITING_PM','WITH_PROCUREMENT'], true)) {
                return ['ok' => false, 'msg' => "Cannot edit — request is $curStatus."];
            }
            $u = [
                ':co' => $params[':co'], ':u' => $params[':u'], ':w' => $params[':w'],
                ':r'  => $params[':r'],  ':n' => $params[':n'],
                ':eb' => $userId, ':id' => $id,
            ];
            $db->insertUpdate(
                "UPDATE spare_requests
                    SET callout_id=:co, urgency=:u, who_pays=:w, reason=:r, notes=:n,
                        edited_by=:eb, edited_on=NOW()
                  WHERE c0d=:id",
                $u
            );
            return ['ok' => true, 'id' => $id];
        }
        $params[':rn'] = $this->nextRequestNo();
        $params[':cb'] = $userId;
        $r = $db->insertUpdate(
            "INSERT INTO spare_requests
                (request_no, contract_id, site_id, callout_id, urgency, who_pays,
                 status, reason, notes, created_by, created_on)
             VALUES (:rn, :c, :s, :co, :u, :w, 'DRAFT', :r, :n, :cb, NOW())",
            $params
        );
        return ['ok' => true, 'id' => (int)($r['last_id'] ?? 0), 'request_no' => $params[':rn']];
    }

    private function nextRequestNo(): string
    {
        $year = (int)date('Y');
        $r = (new DB())->mySelect(
            "SELECT request_no FROM spare_requests
              WHERE request_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'SR-' . $year . '-%']
        );
        $seq = 1;
        if (!empty($r) && preg_match('/SR-\d{4}-(\d+)$/', $r[0]['request_no'], $m)) $seq = ((int)$m[1]) + 1;
        return 'SR-' . $year . '-' . str_pad((string)$seq, 4, '0', STR_PAD_LEFT);
    }

    public function remove(int $requestId): array
    {
        if ($requestId <= 0) return ['ok' => false, 'msg' => 'id required.'];
        $r = (new DB())->mySelect("SELECT status FROM spare_requests WHERE c0d = :id", [':id' => $requestId]);
        if (empty($r)) return ['ok' => false, 'msg' => 'Not found.'];
        if (strtoupper((string)$r[0]['status']) !== 'DRAFT') {
            return ['ok' => false, 'msg' => 'Only DRAFT requests can be removed. Cancel instead.'];
        }
        (new DB())->insertUpdate(
            "UPDATE spare_requests SET deleted = 1, edited_on = NOW() WHERE c0d = :id",
            [':id' => $requestId]
        );
        return ['ok' => true];
    }

    /** Line-item upsert. */
    public function itemSave(int $requestId, array $d): array
    {
        if ($requestId <= 0) return ['ok' => false, 'msg' => 'request_id required.'];
        $qty = isset($d['qty']) && $d['qty'] !== '' ? (float)$d['qty'] : 1.0;
        $partId = isset($d['part_id']) && $d['part_id'] !== '' ? (int)$d['part_id'] : null;
        $freeName = trim((string)($d['part_name_free'] ?? ''));
        if (!$partId && $freeName === '') return ['ok' => false, 'msg' => 'part_id or part_name_free required.'];

        $whoPaysLine = strtoupper((string)($d['who_pays_line'] ?? ''));
        if (!in_array($whoPaysLine, ['CLIENT','INTERNAL'], true)) $whoPaysLine = null;

        $params = [
            ':r'  => $requestId,
            ':p'  => $partId,
            ':fn' => $freeName !== '' ? $freeName : null,
            ':q'  => $qty,
            ':u'  => trim((string)($d['uom'] ?? '')) ?: null,
            ':uc' => isset($d['unit_cost_estimate']) && $d['unit_cost_estimate'] !== '' ? (float)$d['unit_cost_estimate'] : null,
            ':w'  => $whoPaysLine,
            ':nt' => trim((string)($d['notes'] ?? '')) ?: null,
        ];

        $db = new DB();
        $id = (int)($d['c0d'] ?? 0);
        if ($id > 0) {
            $u = $params; unset($u[':r']); $u[':id'] = $id;
            $db->insertUpdate(
                "UPDATE spare_request_items
                    SET part_id=:p, part_name_free=:fn, qty=:q, uom=:u,
                        unit_cost_estimate=:uc, who_pays_line=:w, notes=:nt
                  WHERE c0d=:id",
                $u
            );
            return ['ok' => true, 'id' => $id];
        }
        $r = $db->insertUpdate(
            "INSERT INTO spare_request_items
                (request_id, part_id, part_name_free, qty, uom, unit_cost_estimate, who_pays_line, notes)
             VALUES (:r, :p, :fn, :q, :u, :uc, :w, :nt)",
            $params
        );
        return ['ok' => true, 'id' => (int)($r['last_id'] ?? 0)];
    }

    public function itemRemove(int $itemId): array
    {
        if ($itemId <= 0) return ['ok' => false, 'msg' => 'item_id required.'];
        (new DB())->insertUpdate(
            "UPDATE spare_request_items SET deleted = 1 WHERE c0d = :id",
            [':id' => $itemId]
        );
        return ['ok' => true];
    }

    /**
     * Submit for approval. Flips status DRAFT → AWAITING_POC and creates a
     * fin_approvals row for SPARE_REQUEST_APPROVAL. Emergency-urgency requests
     * still start at POC step; PC captures the POC evidence after the fact
     * before actioning downstream — auditability first, speed second.
     */
    public function submitForApproval(int $requestId, int $userId, string $userName, string $userGroup): array
    {
        if ($requestId <= 0) return ['ok' => false, 'msg' => 'id required.'];
        $req = $this->find($requestId);
        if (!$req) return ['ok' => false, 'msg' => 'Not found.'];
        if (strtoupper((string)$req['status']) !== 'DRAFT') {
            return ['ok' => false, 'msg' => 'Only DRAFT requests can be submitted.'];
        }
        if (empty($req['items'])) return ['ok' => false, 'msg' => 'Add at least one item first.'];

        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $AC = new ApprovalChain();
        $summary = [
            'request_no' => $req['request_no'],
            'urgency'    => $req['urgency'],
            'who_pays'   => $req['who_pays'],
            'items'      => count($req['items']),
        ];
        $r = $AC->submit([
            'chain_key'    => 'SPARE_REQUEST_APPROVAL',
            'entity_type'  => 'SPARE_REQUEST',
            'entity_id'    => $requestId,
            'ref_label'    => (string)$req['request_no'],
            'summary_json' => json_encode($summary, JSON_UNESCAPED_SLASHES),
            'return_url'   => 'Projects/spareRequest/' . $requestId,
            'user_id'      => $userId,
            'user_name'    => $userName,
            'user_group'   => $userGroup,
            'comment'      => 'Submitted — awaiting POC confirmation.',
        ]);
        if (!empty($r['ok'])) {
            (new DB())->insertUpdate(
                "UPDATE spare_requests SET status = 'AWAITING_POC', edited_by = :u, edited_on = NOW() WHERE c0d = :id",
                [':u' => $userId, ':id' => $requestId]
            );
        }
        return $r;
    }

    /**
     * PC captures POC confirmation for the pending step-1 approval.
     * Records POC id/method/attachment then advances the approval chain.
     */
    public function recordPocConfirmation(int $requestId, array $d, int $userId, string $userName, string $userGroup): array
    {
        if ($requestId <= 0) return ['ok' => false, 'msg' => 'id required.'];
        $req = $this->find($requestId);
        if (!$req) return ['ok' => false, 'msg' => 'Not found.'];
        $decision = strtoupper((string)($d['decision'] ?? 'APPROVE'));
        if (!in_array($decision, ['APPROVE','DECLINE'], true)) return ['ok' => false, 'msg' => 'decision must be APPROVE or DECLINE.'];

        $pocId  = isset($d['poc_id']) && $d['poc_id'] !== '' ? (int)$d['poc_id'] : null;
        $method = strtoupper((string)($d['method'] ?? 'OTHER'));
        if (!in_array($method, self::POC_METHODS, true)) $method = 'OTHER';
        $attach = trim((string)($d['attachment_path'] ?? '')) ?: null;
        $comment = trim((string)($d['comment'] ?? '')) ?: null;

        $db = new DB();
        $db->insertUpdate(
            "UPDATE spare_requests
                SET poc_id = :p, poc_approved_on = NOW(),
                    poc_confirmation_method = :m, poc_attachment_path = :a,
                    status = :st, edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':p' => $pocId, ':m' => $method, ':a' => $attach,
                ':st' => $decision === 'APPROVE' ? 'AWAITING_PM' : 'POC_DECLINED',
                ':u' => $userId, ':id' => $requestId,
            ]
        );

        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $AC = new ApprovalChain();
        $approval = $AC->approvalFor('SPARE_REQUEST', $requestId);
        if (empty($approval)) return ['ok' => true, 'msg' => 'POC recorded but no approval row found.'];

        $ac = $decision === 'APPROVE'
            ? $AC->approve(['approval_id' => (int)$approval['c0d'], 'user_id' => $userId, 'user_name' => $userName, 'user_group' => $userGroup, 'comment' => 'POC confirmed via ' . strtolower($method) . ($comment ? ' · ' . $comment : '')])
            : $AC->decline(['approval_id' => (int)$approval['c0d'], 'user_id' => $userId, 'user_name' => $userName, 'user_group' => $userGroup, 'comment' => 'POC declined via ' . strtolower($method) . ($comment ? ' · ' . $comment : '')]);
        return $ac;
    }

    /**
     * Procurement records the triage decision (from_stock / procure / emergency_cash)
     * and advances/terminates the approval accordingly. This is the terminal step
     * of SPARE_REQUEST_APPROVAL.
     */
    public function recordProcurementDecision(int $requestId, string $decision, string $notes, int $userId, string $userName, string $userGroup): array
    {
        $decision = strtoupper($decision);
        if (!in_array($decision, ['FROM_STOCK','PROCURE','EMERGENCY_CASH'], true)) {
            return ['ok' => false, 'msg' => 'decision must be FROM_STOCK / PROCURE / EMERGENCY_CASH.'];
        }
        $newStatus = ['FROM_STOCK' => 'STOCK_ISSUED', 'PROCURE' => 'SOURCING', 'EMERGENCY_CASH' => 'SOURCING'][$decision];
        $db = new DB();
        $db->insertUpdate(
            "UPDATE spare_requests
                SET procurement_decision = :d, status = :s, notes = COALESCE(:n, notes),
                    edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':d' => $decision, ':s' => $newStatus, ':n' => $notes !== '' ? $notes : null, ':u' => $userId, ':id' => $requestId]
        );

        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $AC = new ApprovalChain();
        $approval = $AC->approvalFor('SPARE_REQUEST', $requestId);
        if (!empty($approval)) {
            $AC->approve([
                'approval_id' => (int)$approval['c0d'],
                'user_id'     => $userId,
                'user_name'   => $userName,
                'user_group'  => $userGroup,
                'comment'     => 'Triage: ' . $decision . ($notes ? ' · ' . $notes : ''),
            ]);
        }
        return ['ok' => true, 'decision' => $decision, 'status' => $newStatus];
    }
}
