<?php
/**
 * ServiceRequisitions — the head of the Services float/imprest ladder.
 *
 * This slice (8b-4a) ships the FOUNDATION: DRAFT → SUBMITTED → APPROVED via the
 * SERVICE_REQUISITION_APPROVAL chain (PC → PM → FM → Exec).
 *
 * Later slices layer the cash lifecycle on top:
 *   APPROVED → ISSUED (Accountant releases cash)
 *   ISSUED  → RETIRING (PC records expenses against categories)
 *   RETIRING → RETIRED (Accountant approves retirement)
 *   RETIRED → CLOSED (posted to GL)
 *
 * Categories are a fixed enum so retirement can enforce spend-vs-plan by category later.
 */
class ServiceRequisitions extends Model
{
    public const CATEGORIES = ['FUEL','TRANSPORT','MEALS','ACCOMMODATION','MATERIALS','LABOUR','MISC'];

    /* ── Requisition header ─────────────────────────────────────────── */

    public function draft(int $projectId, array $d, int $userId, string $userName): int
    {
        if ($projectId <= 0) throw new RuntimeException('project_id required.');
        $title = trim((string)($d['title'] ?? ''));
        if ($title === '') throw new RuntimeException('Requisition title required.');

        $db = new DB();
        $seq  = ((int)$db->mySelect("SELECT COUNT(*) n FROM service_requisitions", [])[0]['n']) + 1;
        $reqNo = 'SR-' . date('Y') . '-' . str_pad((string)$seq, 4, '0', STR_PAD_LEFT);

        $fb = $db->insertUpdate(
            "INSERT INTO service_requisitions
                (project_id, req_no, title, purpose, status, currency, context_ref,
                 created_by, created_by_name, created_on)
             VALUES (:p, :no, :t, :pu, 'DRAFT', :cur, :ctx, :u, :un, NOW())",
            [
                ':p' => $projectId, ':no' => $reqNo,
                ':t' => $title,
                ':pu' => (string)($d['purpose'] ?? ''),
                ':cur' => (string)($d['currency'] ?? 'TZS'),
                ':ctx' => (string)($d['context_ref'] ?? ''),
                ':u' => $userId, ':un' => $userName,
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

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

    public function listForProject(int $projectId, string $status = ''): array
    {
        $db = new DB();
        $sql = "SELECT * FROM service_requisitions WHERE project_id = :p AND deleted = 0";
        $params = [':p' => $projectId];
        if ($status !== '') { $sql .= " AND status = :s"; $params[':s'] = $status; }
        $sql .= " ORDER BY c0d DESC";
        return $db->mySelect($sql, $params) ?: [];
    }

    /** Cancel a DRAFT (originator only, or Admin). No side effects. */
    public function cancel(int $reqId, int $userId): void
    {
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT requisitions can be cancelled directly (submitted → decline via chain).');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_requisitions SET status = 'CANCELLED', edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':u' => $userId, ':id' => $reqId]
        );
    }

    /* ── Line CRUD (category-aware) ─────────────────────────────────── */

    public function addLine(int $reqId, string $category, array $d): int
    {
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT requisitions accept line edits.');

        $category = strtoupper($category);
        if (!in_array($category, self::CATEGORIES, true)) throw new RuntimeException('Invalid category: ' . $category);
        $desc = trim((string)($d['description'] ?? ''));
        if ($desc === '') throw new RuntimeException('Line description required.');

        $qty = (float)($d['qty'] ?? 1);
        $uc  = (float)($d['unit_cost'] ?? 0);
        if ($qty <= 0 || $uc < 0) throw new RuntimeException('qty must be > 0 and unit_cost ≥ 0.');
        $total = round($qty * $uc, 2);

        $db = new DB();
        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(line_no),0)+1 n FROM service_requisition_lines WHERE req_id = :r AND deleted = 0",
            [':r' => $reqId]
        )[0]['n'] ?? 1);

        $fb = $db->insertUpdate(
            "INSERT INTO service_requisition_lines
                (req_id, line_no, category, description, qty, unit, unit_cost, total, notes, created_on)
             VALUES (:r, :n, :cat, :d, :q, :u, :uc, :tot, :nt, NOW())",
            [
                ':r' => $reqId, ':n' => $nextNo, ':cat' => $category,
                ':d' => $desc, ':q' => $qty, ':u' => (string)($d['unit'] ?? ''),
                ':uc' => $uc, ':tot' => $total,
                ':nt' => (string)($d['notes'] ?? ''),
            ]
        );
        $this->recomputeTotal($reqId);
        return (int)($fb['last_id'] ?? 0);
    }

    public function deleteLine(int $lineId): void
    {
        $db = new DB();
        $line = $db->mySelect("SELECT req_id FROM service_requisition_lines WHERE c0d = :id", [':id' => $lineId]);
        if (empty($line)) return;
        $reqId = (int)$line[0]['req_id'];
        $req = $this->findById($reqId);
        if (!$req) return;
        if ($req['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT requisitions accept line edits.');
        $db->insertUpdate("UPDATE service_requisition_lines SET deleted = 1 WHERE c0d = :id", [':id' => $lineId]);
        $this->recomputeTotal($reqId);
    }

    public function linesFor(int $reqId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM service_requisition_lines WHERE req_id = :r AND deleted = 0 ORDER BY line_no, c0d",
            [':r' => $reqId]
        ) ?: [];
    }

    /** Return lines grouped by category with per-category totals. Useful for retirement later. */
    public function linesByCategory(int $reqId): array
    {
        $out = [];
        foreach (self::CATEGORIES as $c) $out[$c] = ['lines' => [], 'total' => 0.0];
        foreach ($this->linesFor($reqId) as $ln) {
            $cat = (string)$ln['category'];
            if (!isset($out[$cat])) $out[$cat] = ['lines' => [], 'total' => 0.0];
            $out[$cat]['lines'][] = $ln;
            $out[$cat]['total']  += (float)$ln['total'];
        }
        return $out;
    }

    public function recomputeTotal(int $reqId): void
    {
        $db = new DB();
        $t = (float)($db->mySelect(
            "SELECT COALESCE(SUM(total),0) t FROM service_requisition_lines WHERE req_id = :r AND deleted = 0",
            [':r' => $reqId]
        )[0]['t'] ?? 0);
        $db->insertUpdate(
            "UPDATE service_requisitions SET total_amount = :t, edited_on = NOW() WHERE c0d = :id",
            [':t' => $t, ':id' => $reqId]
        );
    }

    /* ── Chain lifecycle ────────────────────────────────────────────── */

    public function submitForApproval(int $reqId, int $userId, string $userName, string $userGroup): array
    {
        $req = $this->findById($reqId);
        if (!$req) return ['ok' => false, 'msg' => 'Requisition not found.'];
        if ($req['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'Requisition is ' . $req['status'] . '.'];
        if ((float)$req['total_amount'] <= 0) return ['ok' => false, 'msg' => 'Add at least one priced line before submitting.'];
        if (empty($this->linesFor($reqId))) return ['ok' => false, 'msg' => 'No lines to submit.'];

        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $ac = new ApprovalChain();
        $ref = $req['req_no'] . ' · ' . $req['title'] . ' · TZS ' . number_format((float)$req['total_amount']);
        $res = $ac->submit([
            'chain_key'   => 'SERVICE_REQUISITION_APPROVAL',
            'entity_type' => 'SERVICE_REQUISITION',
            'entity_id'   => $reqId,
            'ref_label'   => $ref,
            'return_url'  => 'ServiceRequisitions/view/' . $reqId,
            'user_id'     => $userId, 'user_name' => $userName, 'user_group' => $userGroup,
        ]);
        if (!empty($res['ok'])) {
            $db = new DB();
            $db->insertUpdate(
                "UPDATE service_requisitions SET status = 'SUBMITTED', approval_id = :aid, submitted_on = NOW(), edited_by = :u, edited_on = NOW() WHERE c0d = :id",
                [':aid' => (int)$res['approval_id'], ':u' => $userId, ':id' => $reqId]
            );
        }
        return $res;
    }

    /**
     * Called from FinChainsController::postDecisionHook when SERVICE_REQUISITION_APPROVAL terminates.
     * On APPROVED: mark APPROVED (awaits Accountant issue — later slice).
     * On DECLINED: mark DECLINED.
     */
    public function onChainDecision(int $reqId, string $finalStatus): void
    {
        $req = $this->findById($reqId);
        if (!$req) return;
        $db = new DB();
        if ($finalStatus === 'APPROVED') {
            $db->insertUpdate(
                "UPDATE service_requisitions SET status = 'APPROVED', approved_on = NOW(), edited_on = NOW() WHERE c0d = :id",
                [':id' => $reqId]
            );
            // Slice 8b-4b: notify Accountant(s) that a requisition is ready to issue.
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                (new Notifications())->notifyGroup(
                    'Accountant', 'REQUISITION_APPROVED',
                    'Ready to issue: ' . (string)$req['req_no'],
                    (string)$req['title'] . ' — TZS ' . number_format((float)$req['total_amount']),
                    'ServiceRequisitions/view/' . $reqId,
                    'service_requisition', $reqId, 0, ''
                );
            } catch (\Throwable $e) { /* best-effort */ }
        } elseif ($finalStatus === 'DECLINED') {
            $db->insertUpdate(
                "UPDATE service_requisitions SET status = 'DECLINED', edited_on = NOW() WHERE c0d = :id",
                [':id' => $reqId]
            );
        }
    }

    /* ── Slice 8b-4b · Issue (Accountant releases cash) ─────────────── */

    /** Roles that may issue a requisition. */
    public static function canIssue(string $userGroup): bool
    {
        return in_array(strtoupper(trim($userGroup)), ['ACCOUNTANT','ADMINISTRATOR','FINANCE MANAGER'], true);
    }

    /**
     * Accountant issues the cash. Requires APPROVED status, issue_reference (voucher/cheque no),
     * and an issue_amount that defaults to total_amount but can be reduced (partial issue).
     * Emits a notification to the requisitioner (PC) that cash is available.
     */
    public function issue(int $reqId, array $d, int $userId, string $userName, string $userGroup): void
    {
        if (!self::canIssue($userGroup)) throw new RuntimeException('Only Accountant/FM/Admin may issue a requisition.');
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'APPROVED') throw new RuntimeException('Requisition is ' . $req['status'] . ' — only APPROVED requisitions can be issued.');

        $ref = trim((string)($d['issue_reference'] ?? ''));
        if ($ref === '') throw new RuntimeException('Issue reference required (voucher no / cheque no / transfer ref).');

        $amt = isset($d['issue_amount']) && $d['issue_amount'] !== '' ? (float)$d['issue_amount'] : (float)$req['total_amount'];
        if ($amt <= 0) throw new RuntimeException('Issue amount must be > 0.');
        if ($amt > (float)$req['total_amount']) throw new RuntimeException('Issue amount cannot exceed approved total (' . number_format((float)$req['total_amount']) . ').');

        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_requisitions
                SET status = 'ISSUED', issued_on = NOW(),
                    issued_by = :u, issued_by_name = :un,
                    issue_amount = :a, issue_reference = :r, issue_notes = :nt,
                    edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':u' => $userId, ':un' => $userName,
                ':a' => $amt, ':r' => $ref,
                ':nt' => (string)($d['issue_notes'] ?? ''),
                ':id' => $reqId,
            ]
        );

        // Notify the requisitioner (PC) that cash is available to collect.
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notify(
                (int)$req['created_by'], 'REQUISITION_ISSUED',
                'Cash issued for ' . (string)$req['req_no'] . ' · TZS ' . number_format($amt),
                'Ref ' . $ref . ' · collect from Accountant then start recording expenses.',
                'ServiceRequisitions/view/' . $reqId,
                'service_requisition', $reqId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /* ── Query helpers for controllers ──────────────────────────────── */

    /** Accountant's queue — APPROVED requisitions awaiting issue. */
    public function openForAccountant(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT r.*, p.project_no, p.project_name
               FROM service_requisitions r
               JOIN c_projects p ON p.c0d = r.project_id
              WHERE r.status = 'APPROVED' AND r.deleted = 0
              ORDER BY r.approved_on ASC, r.c0d ASC",
            []
        ) ?: [];
    }

    /** PC's queue — my originated requisitions (any status). */
    public function myRequisitions(int $userId, string $status = ''): array
    {
        $db = new DB();
        $sql = "SELECT r.*, p.project_no, p.project_name
                  FROM service_requisitions r
                  JOIN c_projects p ON p.c0d = r.project_id
                 WHERE r.created_by = :u AND r.deleted = 0";
        $params = [':u' => $userId];
        if ($status !== '') { $sql .= " AND r.status = :s"; $params[':s'] = $status; }
        $sql .= " ORDER BY r.c0d DESC";
        return $db->mySelect($sql, $params) ?: [];
    }

    /** Full listing for admin/exec dashboards. */
    public function listAll(string $status = '', int $limit = 200): array
    {
        $db = new DB();
        $sql = "SELECT r.*, p.project_no, p.project_name
                  FROM service_requisitions r
                  JOIN c_projects p ON p.c0d = r.project_id
                 WHERE r.deleted = 0";
        $params = [];
        if ($status !== '') { $sql .= " AND r.status = :s"; $params[':s'] = $status; }
        $limit = max(1, min(1000, $limit));
        $sql .= " ORDER BY r.c0d DESC LIMIT $limit";
        return $db->mySelect($sql, $params) ?: [];
    }

    /* ── Slice 8b-4c · Retirement lifecycle ─────────────────────────── */

    /** Roles that may approve/send-back a retirement. Mirrors canIssue. */
    public static function canApproveRetirement(string $userGroup): bool
    {
        return self::canIssue($userGroup);
    }

    /**
     * PC starts retirement: ISSUED → RETIRING. From here the PC can add/remove
     * expense lines until they submit. Only the originator (or Admin) can start.
     */
    public function startRetirement(int $reqId, int $userId, string $userGroup): void
    {
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'ISSUED') throw new RuntimeException('Requisition is ' . $req['status'] . ' — only ISSUED requisitions can start retirement.');
        $isCreator = (int)$req['created_by'] === $userId;
        $isAdmin = strtoupper(trim($userGroup)) === 'ADMINISTRATOR';
        if (!$isCreator && !$isAdmin) throw new RuntimeException('Only the requisitioner (or an Administrator) may start retirement.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_requisitions SET status = 'RETIRING', edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':u' => $userId, ':id' => $reqId]
        );
    }

    /**
     * Add an expense line. Only allowed while RETIRING and before submission
     * (retirement_submitted_on IS NULL).
     */
    public function addExpense(int $reqId, string $category, array $d, int $userId, string $userName): int
    {
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'RETIRING') throw new RuntimeException('Requisition is ' . $req['status'] . ' — expenses only recordable while RETIRING.');
        if (!empty($req['retirement_submitted_on'])) throw new RuntimeException('Retirement already submitted — send back to edit further.');

        $isCreator = (int)$req['created_by'] === $userId;
        if (!$isCreator) throw new RuntimeException('Only the requisitioner may record expenses.');

        $category = strtoupper($category);
        if (!in_array($category, self::CATEGORIES, true)) throw new RuntimeException('Invalid category.');
        $desc = trim((string)($d['description'] ?? ''));
        if ($desc === '') throw new RuntimeException('Expense description required.');
        $amt = (float)($d['amount'] ?? 0);
        if ($amt <= 0) throw new RuntimeException('Amount must be > 0.');

        $db = new DB();
        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(line_no),0)+1 n FROM service_requisition_expenses WHERE req_id = :r AND deleted = 0",
            [':r' => $reqId]
        )[0]['n'] ?? 1);

        $isReceipted = !empty($d['receipt_ref']) || !empty($d['receipt_date']) ? 1 : 0;

        $fb = $db->insertUpdate(
            "INSERT INTO service_requisition_expenses
                (req_id, line_no, category, description, amount, receipt_ref, receipt_date,
                 is_receipted, notes, created_by, created_by_name, created_on)
             VALUES (:r, :n, :cat, :d, :a, :rr, :rd, :ir, :nt, :u, :un, NOW())",
            [
                ':r' => $reqId, ':n' => $nextNo, ':cat' => $category,
                ':d' => $desc, ':a' => $amt,
                ':rr' => (string)($d['receipt_ref'] ?? ''),
                ':rd' => !empty($d['receipt_date']) ? (string)$d['receipt_date'] : null,
                ':ir' => $isReceipted,
                ':nt' => (string)($d['notes'] ?? ''),
                ':u' => $userId, ':un' => $userName,
            ]
        );
        $this->recomputeRetiredAmount($reqId);
        return (int)($fb['last_id'] ?? 0);
    }

    public function deleteExpense(int $expenseId, int $userId): void
    {
        $db = new DB();
        $row = $db->mySelect("SELECT req_id FROM service_requisition_expenses WHERE c0d = :id AND deleted = 0", [':id' => $expenseId]);
        if (empty($row)) return;
        $reqId = (int)$row[0]['req_id'];
        $req = $this->findById($reqId);
        if (!$req) return;
        if ($req['status'] !== 'RETIRING') throw new RuntimeException('Expenses only editable while RETIRING.');
        if (!empty($req['retirement_submitted_on'])) throw new RuntimeException('Retirement already submitted.');
        if ((int)$req['created_by'] !== $userId) throw new RuntimeException('Only the requisitioner may delete expenses.');
        $db->insertUpdate("UPDATE service_requisition_expenses SET deleted = 1 WHERE c0d = :id", [':id' => $expenseId]);
        $this->recomputeRetiredAmount($reqId);
    }

    public function expensesFor(int $reqId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM service_requisition_expenses WHERE req_id = :r AND deleted = 0 ORDER BY line_no, c0d",
            [':r' => $reqId]
        ) ?: [];
    }

    /** Expenses grouped by category, with per-category totals + variance vs planned. */
    public function expensesByCategory(int $reqId): array
    {
        $planned = $this->linesByCategory($reqId);
        $out = [];
        foreach (self::CATEGORIES as $c) {
            $out[$c] = [
                'expenses' => [],
                'spent'    => 0.0,
                'planned'  => (float)($planned[$c]['total'] ?? 0.0),
                'variance' => 0.0,
            ];
        }
        foreach ($this->expensesFor($reqId) as $exp) {
            $cat = (string)$exp['category'];
            if (!isset($out[$cat])) $out[$cat] = ['expenses'=>[], 'spent'=>0.0, 'planned'=>0.0, 'variance'=>0.0];
            $out[$cat]['expenses'][] = $exp;
            $out[$cat]['spent'] += (float)$exp['amount'];
        }
        foreach ($out as $c => &$row) {
            // planned - spent: positive = under-spent, negative = overspent
            $row['variance'] = round($row['planned'] - $row['spent'], 2);
        }
        return $out;
    }

    /** Recompute retired_amount + variance_amount on the header. */
    public function recomputeRetiredAmount(int $reqId): void
    {
        $db = new DB();
        $t = (float)($db->mySelect(
            "SELECT COALESCE(SUM(amount),0) t FROM service_requisition_expenses WHERE req_id = :r AND deleted = 0",
            [':r' => $reqId]
        )[0]['t'] ?? 0);
        $db->insertUpdate(
            "UPDATE service_requisitions
                SET retired_amount = :t,
                    variance_amount = ROUND(issue_amount - :t, 2),
                    edited_on = NOW()
              WHERE c0d = :id",
            [':t' => $t, ':id' => $reqId]
        );
    }

    /**
     * PC submits retirement for Accountant review.
     * Requires: at least one expense line + status RETIRING + not already submitted.
     * Sends notification to Accountant group.
     */
    public function submitRetirement(int $reqId, int $userId, string $userName): void
    {
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'RETIRING') throw new RuntimeException('Requisition is ' . $req['status'] . '.');
        if (!empty($req['retirement_submitted_on'])) throw new RuntimeException('Retirement already submitted.');
        if ((int)$req['created_by'] !== $userId) throw new RuntimeException('Only the requisitioner may submit retirement.');
        if (empty($this->expensesFor($reqId))) throw new RuntimeException('Add at least one expense before submitting retirement.');

        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_requisitions SET retirement_submitted_on = NOW(), edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':u' => $userId, ':id' => $reqId]
        );

        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $after = $this->findById($reqId);
            (new Notifications())->notifyGroup(
                'Accountant', 'REQUISITION_RETIREMENT_SUBMITTED',
                'Retirement submitted: ' . $req['req_no'],
                'Retired ' . number_format((float)$after['retired_amount']) . ' of ' . number_format((float)$req['issue_amount']) . ' issued — review and approve.',
                'ServiceRequisitions/view/' . $reqId,
                'service_requisition', $reqId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /**
     * Accountant approves the retirement. RETIRING → RETIRED. Variance is frozen.
     * Notes describe how any variance was reconciled. Notifies the requisitioner.
     */
    public function approveRetirement(int $reqId, int $userId, string $userName, string $userGroup, string $notes = ''): void
    {
        if (!self::canApproveRetirement($userGroup)) throw new RuntimeException('Only Accountant/FM/Admin may approve retirement.');
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'RETIRING') throw new RuntimeException('Requisition is ' . $req['status'] . '.');
        if (empty($req['retirement_submitted_on'])) throw new RuntimeException('Retirement not yet submitted by requisitioner.');

        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_requisitions
                SET status = 'RETIRED', retired_on = NOW(),
                    retirement_approved_by = :u, retirement_approved_by_name = :un,
                    retirement_notes = :nt, edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':u' => $userId, ':un' => $userName, ':nt' => $notes, ':id' => $reqId]
        );

        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $after = $this->findById($reqId);
            $var = (float)$after['variance_amount'];
            $tag = ($var > 0) ? 'unspent ' . number_format($var) . ' returned' : (($var < 0) ? 'overspend ' . number_format(-$var) : 'exact');
            (new Notifications())->notify(
                (int)$req['created_by'], 'REQUISITION_RETIRED',
                'Retirement approved · ' . $req['req_no'],
                $userName . ' approved retirement · variance ' . $tag . '.' . ($notes ? ' Notes: ' . $notes : ''),
                'ServiceRequisitions/view/' . $reqId,
                'service_requisition', $reqId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /**
     * Accountant sends the retirement back to the PC for corrections. Clears
     * retirement_submitted_on so the PC can edit expenses again. Status stays RETIRING.
     * Requires a reason. Notifies the requisitioner.
     */
    public function sendBackRetirement(int $reqId, int $userId, string $userName, string $userGroup, string $reason): void
    {
        if (!self::canApproveRetirement($userGroup)) throw new RuntimeException('Only Accountant/FM/Admin may send back retirement.');
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'RETIRING') throw new RuntimeException('Requisition is ' . $req['status'] . '.');
        if (empty($req['retirement_submitted_on'])) throw new RuntimeException('Retirement not yet submitted.');
        if (trim($reason) === '') throw new RuntimeException('Reason required when sending back.');

        $db = new DB();
        $db->insertUpdate(
            "UPDATE service_requisitions
                SET retirement_submitted_on = NULL,
                    retirement_notes = :nt,
                    edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':u' => $userId, ':nt' => $reason, ':id' => $reqId]
        );

        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notify(
                (int)$req['created_by'], 'REQUISITION_RETIREMENT_SENT_BACK',
                'Retirement sent back · ' . $req['req_no'],
                $userName . ' returned the retirement — reason: ' . $reason,
                'ServiceRequisitions/view/' . $reqId,
                'service_requisition', $reqId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /** Accountant's retirement review queue — RETIRING requisitions with retirement_submitted_on set. */
    public function openRetirementForAccountant(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT r.*, p.project_no, p.project_name
               FROM service_requisitions r
               JOIN c_projects p ON p.c0d = r.project_id
              WHERE r.status = 'RETIRING' AND r.retirement_submitted_on IS NOT NULL AND r.deleted = 0
              ORDER BY r.retirement_submitted_on ASC, r.c0d ASC",
            []
        ) ?: [];
    }

    /** Accountant's close queue — RETIRED requisitions awaiting GL post. */
    public function openCloseForAccountant(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT r.*, p.project_no, p.project_name
               FROM service_requisitions r
               JOIN c_projects p ON p.c0d = r.project_id
              WHERE r.status = 'RETIRED' AND r.journal_entry_id = 0 AND r.deleted = 0
              ORDER BY r.retired_on ASC, r.c0d ASC",
            []
        ) ?: [];
    }

    /* ── Slice 8b-4d · GL Close ─────────────────────────────────────── */

    /**
     * Requisition expense category → GL account code (from chart_of_accounts).
     * Fallback for any unmapped category is 6290 "Other Operating Expenses".
     */
    public const CATEGORY_TO_ACCOUNT = [
        'FUEL'          => '6210', // Transport & Fuel
        'TRANSPORT'     => '6210', // Transport & Fuel
        'LABOUR'        => '6220', // Manpower & Site Labour
        'MATERIALS'     => '6230', // Materials & Consumables
        'MEALS'         => '6290', // Other Operating Expenses
        'ACCOMMODATION' => '6290', // Other Operating Expenses
        'MISC'          => '6290', // Other Operating Expenses
    ];
    public const DEFAULT_EXPENSE_ACCOUNT = '6290';
    public const DEFAULT_BANK_ACCOUNT_CODE = '1200'; // Bank & Cash

    /** Roles that may close (post GL + move to CLOSED). Mirrors canApproveRetirement. */
    public static function canClose(string $userGroup): bool
    {
        return self::canIssue($userGroup);
    }

    /**
     * Close the requisition: post the retirement JE to GL, then move RETIRED → CLOSED.
     *
     * Journal structure (single balanced entry):
     *   Debit  · one line per category with actual spend (mapped via CATEGORY_TO_ACCOUNT)
     *   Credit · Bank & Cash (via bank_accounts.coa_id or 1200 fallback) for retired_amount
     *
     * Variance handling: unspent/overspend are treated as separately reconciled at retirement
     * approval time (Accountant's approval notes describe the physical reconciliation). This
     * slice posts what was actually spent — clean books, no suspense accounts.
     *
     * Idempotent — if already CLOSED or journal_entry_id already set, throws.
     *
     * @return int  posted journal_entry_id
     */
    public function close(int $reqId, int $bankAccountId, int $userId, string $userName, string $userGroup): int
    {
        if (!self::canClose($userGroup)) throw new RuntimeException('Only Accountant/FM/Admin may close a requisition.');
        $req = $this->findById($reqId);
        if (!$req) throw new RuntimeException('Requisition not found.');
        if ($req['status'] !== 'RETIRED') throw new RuntimeException('Requisition is ' . $req['status'] . ' — only RETIRED requisitions can be closed.');
        if ((int)$req['journal_entry_id'] > 0) throw new RuntimeException('Already posted (JE #' . $req['journal_entry_id'] . ').');

        $retired = (float)$req['retired_amount'];
        if ($retired <= 0) throw new RuntimeException('retired_amount is zero — nothing to post.');

        $expenses = $this->expensesByCategory($reqId);
        // Build debit lines: one per category with spent > 0
        $debits = [];
        $sumDr = 0.0;
        foreach ($expenses as $cat => $bucket) {
            $spent = (float)$bucket['spent'];
            if ($spent <= 0) continue;
            $acCode = self::CATEGORY_TO_ACCOUNT[$cat] ?? self::DEFAULT_EXPENSE_ACCOUNT;
            $debits[] = [
                'account_code' => $acCode,
                'debit'        => $spent,
                'credit'       => 0,
                'narrative'    => 'Retirement · ' . $req['req_no'] . ' · ' . $cat . ' (' . count($bucket['expenses']) . ' line' . (count($bucket['expenses']) === 1 ? '' : 's') . ')',
                'project_id'   => (int)$req['project_id'],
            ];
            $sumDr += $spent;
        }
        if (empty($debits)) throw new RuntimeException('No expense categories with spend > 0.');

        // Credit line: bank/cash side. Prefer coa_id from bank_accounts; fall back to 1200 CoA code.
        $db = new DB();
        $bankCoaId = 0;
        if ($bankAccountId > 0) {
            $ba = $db->mySelect("SELECT c0d, coa_id, account_name FROM bank_accounts WHERE c0d = :id AND is_active = 1", [':id' => $bankAccountId]);
            if (empty($ba)) throw new RuntimeException('Bank account not found.');
            $bankCoaId = (int)$ba[0]['coa_id'];
        } else {
            $ba = $db->mySelect("SELECT c0d, coa_id FROM bank_accounts WHERE is_active = 1 ORDER BY c0d LIMIT 1", []);
            if (!empty($ba)) {
                $bankAccountId = (int)$ba[0]['c0d'];
                $bankCoaId     = (int)$ba[0]['coa_id'];
            }
        }
        $creditLine = [
            'debit'           => 0,
            'credit'          => $sumDr,
            'narrative'       => 'Retirement close · ' . $req['req_no'] . ' · TZS ' . number_format($sumDr),
            'project_id'      => (int)$req['project_id'],
            'bank_account_id' => $bankAccountId,
        ];
        if ($bankCoaId > 0) $creditLine['account_id']   = $bankCoaId;
        else                $creditLine['account_code'] = self::DEFAULT_BANK_ACCOUNT_CODE;

        // Post via JournalPosting
        if (!class_exists('JournalPosting')) require_once ROOT . '/app/models/JournalPosting.php';
        $jp  = new JournalPosting();
        $res = $jp->post([
            'entry_date'     => date('Y-m-d'),
            'source_type'    => 'SERVICE_REQUISITION',
            'source_id'      => $reqId,
            'source_ref'     => (string)$req['req_no'],
            'narrative'      => 'Close of service requisition ' . $req['req_no'] . ' · ' . $req['title']
                                . ' · retired ' . number_format($sumDr)
                                . (abs((float)$req['variance_amount']) > 0.01 ? ' · variance ' . number_format((float)$req['variance_amount']) . ' reconciled at approval' : ''),
            'currency'       => (string)$req['currency'],
            'posted_by'      => $userId,
            'posted_by_name' => $userName,
            'lines'          => array_merge($debits, [$creditLine]),
        ]);
        if (empty($res['ok'])) throw new RuntimeException('GL post failed: ' . ($res['msg'] ?? 'unknown'));
        $jeId = (int)$res['id'];

        // Flip the header to CLOSED
        $db->insertUpdate(
            "UPDATE service_requisitions
                SET status = 'CLOSED', closed_on = NOW(),
                    closed_by = :u, closed_by_name = :un,
                    journal_entry_id = :je, edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':u' => $userId, ':un' => $userName, ':je' => $jeId, ':id' => $reqId]
        );

        // Notify originator
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notify(
                (int)$req['created_by'], 'REQUISITION_CLOSED',
                'Closed · ' . $req['req_no'],
                'Requisition closed and posted to GL (' . $res['je_no'] . '). This file is now read-only.',
                'ServiceRequisitions/view/' . $reqId,
                'service_requisition', $reqId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }

        return $jeId;
    }
}
