<?php
/**
 * LpoRetirements — Slice 8Z-d · Batch 2
 *
 * Retirement is the "close-out" of a cash advance. The requester enters spend lines with receipts
 * (soft-uploaded to attachments/retirements/{lpo_id}/), adjusts cash_returned, submits. Accountant
 * reviews, approves or rejects. On approval → LPO marked RETIRED + journal posted best-effort.
 *
 * One retirement per LPO (uq_lpo constraint). Lines are additive; requester can edit while DRAFT
 * or REJECTED, locked once SUBMITTED (unless bounced back to draft explicitly).
 */
class LpoRetirements extends Model
{
    private const MAX_BYTES = 10 * 1024 * 1024; // 10 MB per receipt

    /* ── Reads ─────────────────────────────────────────────────────────── */

    public function findByLpo(int $lpoId): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM lpo_retirements WHERE lpo_id = :l AND deleted = 0 LIMIT 1", [':l' => $lpoId]);
        return $r[0] ?? null;
    }

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

    public function linesFor(int $retirementId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM lpo_retirement_lines WHERE retirement_id = :r ORDER BY line_no, c0d",
            [':r' => $retirementId]
        ) ?: [];
    }

    /** SUBMITTED retirements awaiting Accountant review — the review queue. */
    public function pendingReviewQueue(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT r.*, l.lpo_no, l.lpo_type, l.amount AS lpo_amount, l.currency, l.purpose,
                    l.requested_by_name, l.payee_name, p.project_no, p.project_name
               FROM lpo_retirements r
               JOIN lpos l           ON l.c0d = r.lpo_id
          LEFT JOIN c_projects p     ON p.c0d = l.project_id
              WHERE r.status = 'SUBMITTED' AND r.deleted = 0
              ORDER BY r.submitted_on ASC",
            []
        ) ?: [];
    }

    /** Requester's own retirements (any status). */
    public function forUser(int $userId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT r.*, l.lpo_no, l.lpo_type, l.amount AS lpo_amount, l.currency, l.purpose,
                    p.project_no
               FROM lpo_retirements r
               JOIN lpos l           ON l.c0d = r.lpo_id
          LEFT JOIN c_projects p     ON p.c0d = l.project_id
              WHERE l.requested_by = :u AND r.deleted = 0
              ORDER BY r.c0d DESC",
            [':u' => $userId]
        ) ?: [];
    }

    /* ── Write ─────────────────────────────────────────────────────────── */

    /** Idempotent: ensure a DRAFT retirement exists on the LPO. Returns retirement id. */
    public function ensureDraft(int $lpoId, int $userId): int
    {
        $existing = $this->findByLpo($lpoId);
        if ($existing) return (int)$existing['c0d'];
        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO lpo_retirements
                (lpo_id, status, total_spent, cash_returned, variance, created_by, created_on)
             VALUES (:l, 'DRAFT', 0, 0, 0, :u, NOW())",
            [':l' => $lpoId, ':u' => $userId]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    /** Update header fields (cash_returned + reviewer_notes on requester side). DRAFT only. */
    public function saveHeader(int $retirementId, array $d, int $userId): void
    {
        $r = $this->find($retirementId);
        if (!$r) throw new RuntimeException('Retirement not found.');
        if ($r['status'] !== 'DRAFT' && $r['status'] !== 'REJECTED') {
            throw new RuntimeException('Retirement is locked (' . $r['status'] . ').');
        }
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpo_retirements SET
                cash_returned = :cr, edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':cr' => (float)($d['cash_returned'] ?? 0), ':u' => $userId, ':id' => $retirementId]
        );
        $this->recomputeTotals($retirementId);
    }

    public function addLine(int $retirementId, array $d, ?array $file, int $userId): int
    {
        $r = $this->find($retirementId);
        if (!$r) throw new RuntimeException('Retirement not found.');
        if (!in_array($r['status'], ['DRAFT','REJECTED'], true)) {
            throw new RuntimeException('Retirement is locked (' . $r['status'] . ').');
        }
        $desc = trim((string)($d['description'] ?? ''));
        if ($desc === '') throw new InvalidArgumentException('Description required.');
        $amt = (float)($d['amount'] ?? 0);
        if ($amt <= 0) throw new InvalidArgumentException('Amount must be > 0.');

        // Optional receipt upload
        $relPath = null; $origName = null; $mime = null; $size = 0;
        if ($file && !empty($file['tmp_name']) && is_uploaded_file($file['tmp_name'])) {
            [$relPath, $origName, $mime, $size] = $this->stashReceipt($file, (int)$r['lpo_id']);
        }

        $db = new DB();
        $nextNo = ((int)($db->mySelect("SELECT COALESCE(MAX(line_no),0) n FROM lpo_retirement_lines WHERE retirement_id=:r", [':r'=>$retirementId])[0]['n'] ?? 0)) + 1;
        $fb = $db->insertUpdate(
            "INSERT INTO lpo_retirement_lines
                (retirement_id, line_no, description, amount, coa_account_id,
                 receipt_attachment_path, receipt_original_name, receipt_mime, receipt_size_bytes, notes, created_on)
             VALUES (:r, :n, :d, :a, :coa, :fp, :on, :mm, :sz, :nt, NOW())",
            [
                ':r'=>$retirementId, ':n'=>$nextNo, ':d'=>$desc, ':a'=>$amt,
                ':coa'=>!empty($d['coa_account_id']) ? (int)$d['coa_account_id'] : null,
                ':fp'=>$relPath, ':on'=>$origName, ':mm'=>$mime, ':sz'=>$size,
                ':nt'=>(string)($d['notes'] ?? ''),
            ]
        );
        $this->recomputeTotals($retirementId);
        return (int)($fb['last_id'] ?? 0);
    }

    public function updateLine(int $lineId, array $d, int $userId): void
    {
        $db = new DB();
        $ln = $db->mySelect("SELECT * FROM lpo_retirement_lines WHERE c0d = :id", [':id' => $lineId]);
        if (empty($ln)) throw new RuntimeException('Line not found.');
        $r = $this->find((int)$ln[0]['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        $db->insertUpdate(
            "UPDATE lpo_retirement_lines SET
                description = :d, amount = :a, coa_account_id = :coa, notes = :nt
              WHERE c0d = :id",
            [
                ':d'=>trim((string)($d['description'] ?? $ln[0]['description'])),
                ':a'=>(float)($d['amount'] ?? $ln[0]['amount']),
                ':coa'=>!empty($d['coa_account_id']) ? (int)$d['coa_account_id'] : null,
                ':nt'=>(string)($d['notes'] ?? $ln[0]['notes']),
                ':id'=>$lineId,
            ]
        );
        $this->recomputeTotals((int)$ln[0]['retirement_id']);
    }

    public function removeLine(int $lineId): void
    {
        $db = new DB();
        $ln = $db->mySelect("SELECT retirement_id FROM lpo_retirement_lines WHERE c0d = :id", [':id' => $lineId]);
        if (empty($ln)) return;
        $r = $this->find((int)$ln[0]['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        $db->insertUpdate("DELETE FROM lpo_retirement_lines WHERE c0d = :id", [':id' => $lineId]);
        $this->recomputeTotals((int)$ln[0]['retirement_id']);
    }

    /** Flip DRAFT → SUBMITTED (also allowed from REJECTED for a re-submit). Notify Accountant. */
    public function submit(int $retirementId, int $userId, string $userName): void
    {
        $r = $this->find($retirementId);
        if (!$r) throw new RuntimeException('Retirement not found.');
        if (!in_array($r['status'], ['DRAFT','REJECTED'], true)) {
            throw new RuntimeException('Cannot submit — status is ' . $r['status']);
        }
        // Gate: at least one line, totals balanced, and (Slice 8Z-q) receipt confirmed on LPO.
        $this->recomputeTotals($retirementId);
        $r  = $this->find($retirementId);
        $ls = $this->linesFor($retirementId);
        if (empty($ls))                    throw new RuntimeException('Add at least one spend line before submitting.');
        if (abs((float)$r['variance']) > 0.005) {
            throw new RuntimeException('Retirement is out of balance by ' . number_format((float)$r['variance'], 2) . '. Adjust lines or cash returned.');
        }
        // Slice 8Z-q · receipt must be confirmed on the LPO first (procurement acknowledged money arrived).
        $dbg = new DB();
        $lc  = $dbg->mySelect("SELECT receipt_confirmed_at, lpo_no FROM lpos WHERE c0d = :l", [':l' => (int)$r['lpo_id']]);
        if (empty($lc[0]['receipt_confirmed_at'])) {
            throw new RuntimeException('Cash receipt not yet confirmed on LPO ' . (string)($lc[0]['lpo_no'] ?? '') . '. Confirm receipt first.');
        }
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpo_retirements SET status = 'SUBMITTED', submitted_by = :u, submitted_by_name = :un, submitted_on = NOW(),
                    edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':u' => $userId, ':un' => $userName, ':id' => $retirementId]
        );
        // Notify Accountant group
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $db2 = new DB();
            $lpo = $db2->mySelect("SELECT lpo_no, currency, amount FROM lpos WHERE c0d = :id", [':id' => (int)$r['lpo_id']]);
            $ref = (string)($lpo[0]['lpo_no'] ?? '#' . $r['lpo_id']);
            $body = 'Total spent ' . number_format((float)$r['total_spent']) . ' · Cash returned ' . number_format((float)$r['cash_returned'])
                  . ' · Lines ' . count($ls);
            (new Notifications())->notifyGroup(
                'Accountant', 'RETIREMENT_PENDING_ACCT',
                'Retirement to review: ' . $ref, $body,
                'Retirements/view/' . $retirementId, 'lpo_retirement', $retirementId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /**
     * Accountant approves. Marks LPO as RETIRED, best-effort posts journal.
     * Returns the created journal_entry_id (0 if journal skipped).
     */
    public function approve(int $retirementId, int $reviewerId, string $reviewerName, string $reviewerNotes): int
    {
        $r = $this->find($retirementId);
        if (!$r)                                throw new RuntimeException('Retirement not found.');
        if ($r['status'] !== 'SUBMITTED')       throw new RuntimeException('Cannot approve — status is ' . $r['status']);

        $db = new DB();
        // Mark retirement approved first
        $db->insertUpdate(
            "UPDATE lpo_retirements SET status='APPROVED', reviewer_id=:r, reviewer_name=:rn, reviewer_notes=:rnote, reviewed_on=NOW()
              WHERE c0d = :id",
            [':r'=>$reviewerId, ':rn'=>$reviewerName, ':rnote'=>trim($reviewerNotes), ':id'=>$retirementId]
        );

        // Update lpos.retired_amount + retired_on + return_to_float_amount
        $db->insertUpdate(
            "UPDATE lpos SET retired_amount = :ra, return_to_float_amount = :rf, retired_on = NOW()
              WHERE c0d = :l",
            [':ra' => (float)$r['total_spent'], ':rf' => (float)$r['cash_returned'], ':l' => (int)$r['lpo_id']]
        );

        // Best-effort journal posting (skip silently if COA setup incomplete)
        $jeId = $this->postJournalForRetirement($retirementId, $reviewerId, $reviewerName);
        if ($jeId > 0) {
            $db->insertUpdate("UPDATE lpo_retirements SET journal_entry_id = :j WHERE c0d = :id", [':j'=>$jeId, ':id'=>$retirementId]);
        }

        // Batch 3 · if the original disbursement came from petty cash AND the requester returned
        // unused cash, push it back into the tin. PettyCashBox::recordReturn is a no-op if the
        // LPO was disbursed via the bank/PV path (checks for a prior DISBURSE movement).
        $cashReturned = (float)$r['cash_returned'];
        if ($cashReturned > 0) {
            try {
                if (!class_exists('PettyCashBox')) require_once ROOT . '/app/models/PettyCashBox.php';
                (new PettyCashBox())->recordReturn((int)$r['lpo_id'], $retirementId, $cashReturned, $reviewerId, $reviewerName);
            } catch (\Throwable $e) { /* best-effort */ }
        }

        // Notify requester
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $lpo = $db->mySelect("SELECT requested_by, lpo_no FROM lpos WHERE c0d = :l", [':l' => (int)$r['lpo_id']]);
            $requesterId = (int)($lpo[0]['requested_by'] ?? 0);
            if ($requesterId > 0) {
                (new Notifications())->notify(
                    $requesterId, 'RETIREMENT_APPROVED',
                    'Retirement approved: ' . (string)$lpo[0]['lpo_no'],
                    'Accountant approved your retirement — your advance is closed.',
                    'Lpos/view/' . (int)$r['lpo_id'], 'lpo_retirement', $retirementId, $reviewerId, $reviewerName
                );
            }
        } catch (\Throwable $e) { /* best-effort */ }

        return $jeId;
    }

    /** Accountant rejects and sends back to requester for edits. Reason mandatory. */
    public function reject(int $retirementId, int $reviewerId, string $reviewerName, string $reason): void
    {
        if (trim($reason) === '') throw new RuntimeException('A reason is required when rejecting a retirement.');
        $r = $this->find($retirementId);
        if (!$r)                          throw new RuntimeException('Retirement not found.');
        if ($r['status'] !== 'SUBMITTED') throw new RuntimeException('Cannot reject — status is ' . $r['status']);

        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpo_retirements SET status='REJECTED', reviewer_id=:r, reviewer_name=:rn, reviewer_notes=:rnote, reviewed_on=NOW()
              WHERE c0d = :id",
            [':r'=>$reviewerId, ':rn'=>$reviewerName, ':rnote'=>trim($reason), ':id'=>$retirementId]
        );

        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $lpo = $db->mySelect("SELECT requested_by, lpo_no FROM lpos WHERE c0d = :l", [':l' => (int)$r['lpo_id']]);
            $requesterId = (int)($lpo[0]['requested_by'] ?? 0);
            if ($requesterId > 0) {
                (new Notifications())->notify(
                    $requesterId, 'RETIREMENT_REJECTED',
                    'Retirement bounced back: ' . (string)$lpo[0]['lpo_no'],
                    'Accountant asked for changes — see notes: ' . mb_strimwidth(trim($reason), 0, 200, '…'),
                    'Retirements/view/' . $retirementId, 'lpo_retirement', $retirementId, $reviewerId, $reviewerName
                );
            }
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /* ── Internals ─────────────────────────────────────────────────────── */

    private function recomputeTotals(int $retirementId): void
    {
        $db = new DB();
        $rows = $db->mySelect("SELECT COALESCE(SUM(amount),0) s FROM lpo_retirement_lines WHERE retirement_id = :r", [':r' => $retirementId]);
        $totalSpent = (float)($rows[0]['s'] ?? 0);
        $header = $this->find($retirementId);
        // Slice 8Z-m · use amount_paid (if partial) as the expected advance total; fall back to
        // full amount for the fully-paid case (where amount_paid == amount anyway).
        $lpo = $db->mySelect("SELECT amount, amount_paid FROM lpos WHERE c0d = :l", [':l' => (int)$header['lpo_id']]);
        $lpoTotal = (float)($lpo[0]['amount'] ?? 0);
        $lpoPaid  = (float)($lpo[0]['amount_paid'] ?? 0);
        $expectedAdvance = ($lpoPaid > 0) ? $lpoPaid : $lpoTotal;
        $cashReturned = (float)$header['cash_returned'];
        $variance = round($expectedAdvance - $totalSpent - $cashReturned, 2);
        $db->insertUpdate(
            "UPDATE lpo_retirements SET total_spent = :ts, variance = :v, edited_on = NOW() WHERE c0d = :id",
            [':ts' => $totalSpent, ':v' => $variance, ':id' => $retirementId]
        );
    }

    /** Move an uploaded file to attachments/retirements/{lpo_id}/. Returns [rel, orig, mime, size]. */
    private function stashReceipt(array $file, int $lpoId): array
    {
        $err = (int)($file['error'] ?? 0);
        if ($err !== UPLOAD_ERR_OK)              throw new RuntimeException('Upload error code ' . $err);
        $size = (int)($file['size'] ?? 0);
        if ($size <= 0 || $size > self::MAX_BYTES) throw new RuntimeException('Receipt file size invalid.');
        $mime = @mime_content_type($file['tmp_name']) ?: (string)($file['type'] ?? '');
        $okMimes = ['application/pdf','image/jpeg','image/png','image/webp','image/gif','text/plain','text/csv','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet','application/vnd.ms-excel'];
        if (!in_array($mime, $okMimes, true))    throw new RuntimeException('Unsupported receipt file type: ' . $mime);

        $orig = (string)($file['name'] ?? 'receipt');
        $safe = preg_replace('/[^A-Za-z0-9._\-]+/', '_', $orig);
        $ext  = pathinfo($safe, PATHINFO_EXTENSION);
        $base = pathinfo($safe, PATHINFO_FILENAME) . '_' . substr(bin2hex(random_bytes(4)), 0, 8) . ($ext ? '.' . $ext : '');
        $dir  = ROOT . '/attachments/retirements/' . $lpoId;
        if (!is_dir($dir) && !@mkdir($dir, 0777, true) && !is_dir($dir)) throw new RuntimeException('Could not create attachment folder.');
        $abs  = $dir . '/' . $base;
        if (!@move_uploaded_file($file['tmp_name'], $abs))               throw new RuntimeException('Failed to save receipt.');
        return ['attachments/retirements/' . $lpoId . '/' . $base, $orig, $mime, $size];
    }

    /**
     * Best-effort double-entry: Dr each line's coa_account_id (or default expense 6290) · Cr LPO's payee balance
     * (approximated as generic Cash Advance suspense — real payment posting is a separate slice).
     * Returns journal_entry_id, or 0 if COA setup incomplete.
     */
    private function postJournalForRetirement(int $retirementId, int $userId, string $userName): int
    {
        try {
            if (!class_exists('JournalPosting')) require_once ROOT . '/app/models/JournalPosting.php';
            $r = $this->find($retirementId);
            if (!$r) return 0;
            $lines = $this->linesFor($retirementId);
            if (empty($lines)) return 0;

            $db = new DB();
            $lpo = $db->mySelect("SELECT lpo_no, currency FROM lpos WHERE c0d = :l", [':l' => (int)$r['lpo_id']])[0] ?? null;
            if (!$lpo) return 0;

            $defaultExpense  = $this->resolveAccountCode('6290'); // Other Operating Expenses
            $advanceSuspense = $this->resolveAccountCode('1121'); // Trade Receivables — used as advance suspense in MVP
            $inputVat        = $this->resolveAccountCode('1220'); // Input VAT (recoverable) — for EFD_VAT lines
            if (!$defaultExpense || !$advanceSuspense) return 0;

            // Slice 8Z-q · per-line VAT allocation. For lines linked to an EFD_VAT receipt,
            // allocate a proportional share of the receipt's vat_amount and post it as
            // Dr Input VAT instead of Dr Expense. Falls back to whole-line expense when no
            // linked receipt or receipt is NORMAL / EFD_NON_VAT.
            $receiptCache = [];
            $receiptLineSum = []; // receipt_id => sum of line amounts pointing at it
            foreach ($lines as $ln) {
                $rid = (int)($ln['receipt_id'] ?? 0);
                if ($rid > 0) {
                    if (!isset($receiptCache[$rid])) $receiptCache[$rid] = $this->findReceipt($rid);
                    $receiptLineSum[$rid] = ($receiptLineSum[$rid] ?? 0) + (float)$ln['amount'];
                }
            }

            $jeLines  = [];
            $totSpent = 0.0;
            foreach ($lines as $ln) {
                $amt        = (float)$ln['amount'];
                $totSpent  += $amt;
                $accountId  = !empty($ln['coa_account_id']) ? (int)$ln['coa_account_id'] : $defaultExpense;
                $rid        = (int)($ln['receipt_id'] ?? 0);
                $rec        = $receiptCache[$rid] ?? null;
                $lineVat    = 0.0;
                if ($inputVat && $rec && (string)$rec['receipt_type'] === 'EFD_VAT' && (float)$rec['vat_amount'] > 0 && !empty($receiptLineSum[$rid])) {
                    $share   = $amt / $receiptLineSum[$rid];
                    $lineVat = round((float)$rec['vat_amount'] * $share, 2);
                    if ($lineVat > $amt) $lineVat = $amt;
                }
                $expensePortion = $amt - $lineVat;
                if ($expensePortion > 0) {
                    $jeLines[] = [
                        'account_id' => $accountId,
                        'debit'      => $expensePortion,
                        'credit'     => 0,
                        'narrative'  => 'Retirement: ' . (string)$ln['description'],
                        'lpo_id'     => (int)$r['lpo_id'],
                    ];
                }
                if ($lineVat > 0) {
                    $jeLines[] = [
                        'account_id' => $inputVat,
                        'debit'      => $lineVat,
                        'credit'     => 0,
                        'narrative'  => 'Input VAT · EFD ' . (string)($rec['efd_receipt_no'] ?? ''),
                        'lpo_id'     => (int)$r['lpo_id'],
                    ];
                }
            }
            $jeLines[] = [
                'account_id' => $advanceSuspense,
                'debit'      => 0,
                'credit'     => $totSpent,
                'narrative'  => 'Advance retired ' . (string)$lpo['lpo_no'],
                'lpo_id'     => (int)$r['lpo_id'],
            ];

            $post = (new JournalPosting())->post([
                'entry_date'  => date('Y-m-d'),
                'source_type' => 'RETIREMENT',
                'source_id'   => $retirementId,
                'source_ref'  => (string)$lpo['lpo_no'],
                'narrative'   => 'Cash advance retirement · ' . (string)$lpo['lpo_no'],
                'currency'    => (string)($lpo['currency'] ?? 'TZS'),
                'posted_by'   => $userId,
                'posted_by_name' => $userName,
                'lines'       => $jeLines,
            ]);
            return !empty($post['ok']) ? (int)($post['id'] ?? 0) : 0;
        } catch (\Throwable $e) { return 0; }
    }

    private function resolveAccountCode(string $code): int
    {
        $db = new DB();
        $r = $db->mySelect("SELECT c0d FROM chart_of_accounts WHERE account_code = :c AND is_active = 1 LIMIT 1", [':c' => $code]);
        return (int)($r[0]['c0d'] ?? 0);
    }

    /* ── Slice 8Z-q · Receipts (cross-cutting, one receipt may back many lines) ── */

    public function receiptsFor(int $retirementId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM lpo_retirement_receipts WHERE retirement_id = :r ORDER BY c0d",
            [':r' => $retirementId]
        ) ?: [];
    }

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

    /** Add a receipt (with optional attachment file). Guarded to DRAFT/REJECTED. */
    public function addReceipt(int $retirementId, array $d, ?array $file, int $userId, string $userName): int
    {
        $r = $this->find($retirementId);
        if (!$r)                                            throw new RuntimeException('Retirement not found.');
        if (!in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        $type = strtoupper((string)($d['receipt_type'] ?? 'NORMAL'));
        if (!in_array($type, ['NORMAL','EFD_VAT','EFD_NON_VAT'], true)) $type = 'NORMAL';
        $subject = trim((string)($d['subject'] ?? ''));
        if ($subject === '') throw new InvalidArgumentException('Subject required — describe what this receipt covers.');
        if ($type !== 'NORMAL' && trim((string)($d['efd_receipt_no'] ?? '')) === '') {
            throw new InvalidArgumentException('EFD receipt number required for ' . $type . '.');
        }
        $gross = (float)($d['gross_amount'] ?? 0);
        $vat   = ($type === 'EFD_VAT') ? (float)($d['vat_amount'] ?? 0) : null;
        if ($vat !== null && $vat > $gross + 0.005) throw new InvalidArgumentException('VAT amount cannot exceed gross.');

        $attach = [null,null,null,null];
        if ($file && !empty($file['tmp_name']) && is_uploaded_file($file['tmp_name'])) {
            $attach = $this->stashReceipt($file, (int)$r['lpo_id']);
        }
        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO lpo_retirement_receipts
                (retirement_id, receipt_type, subject, supplier_name, efd_receipt_no, efd_date, supplier_tin,
                 gross_amount, vat_amount,
                 attachment_path, attachment_original_name, attachment_mime, attachment_size_bytes,
                 notes, created_by, created_by_name)
             VALUES
                (:r, :t, :sub, :sn, :en, :ed, :tin,
                 :g, :v,
                 :ap, :ao, :am, :as,
                 :nt, :u, :un)",
            [
                ':r'=>$retirementId, ':t'=>$type, ':sub'=>$subject,
                ':sn'=>trim((string)($d['supplier_name'] ?? '')) ?: null,
                ':en'=>trim((string)($d['efd_receipt_no'] ?? '')) ?: null,
                ':ed'=>trim((string)($d['efd_date'] ?? '')) ?: null,
                ':tin'=>trim((string)($d['supplier_tin'] ?? '')) ?: null,
                ':g'=>$gross, ':v'=>$vat,
                ':ap'=>$attach[0], ':ao'=>$attach[1], ':am'=>$attach[2], ':as'=>$attach[3],
                ':nt'=>trim((string)($d['notes'] ?? '')) ?: null,
                ':u'=>$userId, ':un'=>$userName,
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function updateReceipt(int $receiptId, array $d): void
    {
        $rec = $this->findReceipt($receiptId);
        if (!$rec) throw new RuntimeException('Receipt not found.');
        $r = $this->find((int)$rec['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        $type = strtoupper((string)($d['receipt_type'] ?? $rec['receipt_type']));
        if (!in_array($type, ['NORMAL','EFD_VAT','EFD_NON_VAT'], true)) $type = 'NORMAL';
        $subject = trim((string)($d['subject'] ?? $rec['subject']));
        if ($subject === '') throw new InvalidArgumentException('Subject required.');
        $gross = isset($d['gross_amount']) ? (float)$d['gross_amount'] : (float)$rec['gross_amount'];
        $vat   = ($type === 'EFD_VAT') ? (isset($d['vat_amount']) ? (float)$d['vat_amount'] : (float)($rec['vat_amount'] ?? 0)) : null;

        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpo_retirement_receipts SET
                receipt_type=:t, subject=:sub, supplier_name=:sn, efd_receipt_no=:en, efd_date=:ed,
                supplier_tin=:tin, gross_amount=:g, vat_amount=:v, notes=:nt, edited_on=NOW()
              WHERE c0d = :id",
            [
                ':t'=>$type, ':sub'=>$subject,
                ':sn'=>trim((string)($d['supplier_name'] ?? $rec['supplier_name'] ?? '')) ?: null,
                ':en'=>trim((string)($d['efd_receipt_no'] ?? $rec['efd_receipt_no'] ?? '')) ?: null,
                ':ed'=>trim((string)($d['efd_date'] ?? $rec['efd_date'] ?? '')) ?: null,
                ':tin'=>trim((string)($d['supplier_tin'] ?? $rec['supplier_tin'] ?? '')) ?: null,
                ':g'=>$gross, ':v'=>$vat,
                ':nt'=>trim((string)($d['notes'] ?? $rec['notes'] ?? '')) ?: null,
                ':id'=>$receiptId,
            ]
        );
    }

    public function removeReceipt(int $receiptId): void
    {
        $rec = $this->findReceipt($receiptId);
        if (!$rec) return;
        $r = $this->find((int)$rec['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        $db = new DB();
        // Unlink any lines that pointed here
        $db->insertUpdate("UPDATE lpo_retirement_lines SET receipt_id = NULL WHERE receipt_id = :id", [':id' => $receiptId]);
        $db->insertUpdate("DELETE FROM lpo_retirement_receipts WHERE c0d = :id", [':id' => $receiptId]);
    }

    /* ── Slice 8Z-q · Line attachments (photos, extra proofs — max 4 per line in UI) ── */

    public function lineAttachmentsFor(int $lineId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM lpo_retirement_line_attachments WHERE line_id = :l ORDER BY c0d",
            [':l' => $lineId]
        ) ?: [];
    }

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

    public function addLineAttachment(int $lineId, array $file, int $userId, string $userName): int
    {
        $db = new DB();
        $ln = $db->mySelect("SELECT retirement_id FROM lpo_retirement_lines WHERE c0d = :id", [':id' => $lineId]);
        if (empty($ln)) throw new RuntimeException('Line not found.');
        $r = $this->find((int)$ln[0]['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        // Cap at 4 attachments per line (UI ask)
        $count = (int)($db->mySelect("SELECT COUNT(*) n FROM lpo_retirement_line_attachments WHERE line_id = :l", [':l' => $lineId])[0]['n'] ?? 0);
        if ($count >= 4) throw new RuntimeException('Maximum 4 attachments per line. Remove one first.');

        [$rel, $orig, $mime, $size] = $this->stashReceipt($file, (int)$r['lpo_id']);
        $fb = $db->insertUpdate(
            "INSERT INTO lpo_retirement_line_attachments (line_id, file_path, original_name, mime, size_bytes, kind, uploaded_by, uploaded_by_name)
             VALUES (:l, :fp, :on, :mm, :sz, 'PHOTO', :u, :un)",
            [':l'=>$lineId, ':fp'=>$rel, ':on'=>$orig, ':mm'=>$mime, ':sz'=>$size, ':u'=>$userId, ':un'=>$userName]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function removeLineAttachment(int $attId): void
    {
        $db = new DB();
        $att = $this->findLineAttachment($attId);
        if (empty($att)) return;
        $ln = $db->mySelect("SELECT retirement_id FROM lpo_retirement_lines WHERE c0d = :id", [':id' => (int)$att['line_id']]);
        if (empty($ln)) return;
        $r = $this->find((int)$ln[0]['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        $db->insertUpdate("DELETE FROM lpo_retirement_line_attachments WHERE c0d = :id", [':id' => $attId]);
    }

    /** Update a line's receipt_id (link/unlink to a receipt). Also handles other line fields via existing updateLine. */
    public function setLineReceipt(int $lineId, ?int $receiptId): void
    {
        $db = new DB();
        $ln = $db->mySelect("SELECT retirement_id FROM lpo_retirement_lines WHERE c0d = :id", [':id' => $lineId]);
        if (empty($ln)) throw new RuntimeException('Line not found.');
        $r = $this->find((int)$ln[0]['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Retirement is locked.');
        $db->insertUpdate(
            "UPDATE lpo_retirement_lines SET receipt_id = :r WHERE c0d = :id",
            [':r' => $receiptId ?: null, ':id' => $lineId]
        );
    }

    /* ── Batch 4 · Product Discovery findings ──────────────────────────── */

    public function findingsFor(int $retirementId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM lpo_retirement_findings WHERE retirement_id = :r ORDER BY recommendation, supplier_name",
            [':r' => $retirementId]
        ) ?: [];
    }

    public function addFinding(int $retirementId, array $d, int $userId): int
    {
        $r = $this->find($retirementId);
        if (!$r) throw new RuntimeException('Retirement not found.');
        if (!in_array($r['status'], ['DRAFT','REJECTED'], true)) {
            throw new RuntimeException('Findings are locked once the retirement is submitted.');
        }
        $sName = trim((string)($d['supplier_name'] ?? ''));
        if ($sName === '') throw new InvalidArgumentException('Supplier name is required.');
        $rec = (string)($d['recommendation'] ?? 'MAYBE');
        if (!in_array($rec, ['SHORTLIST','MAYBE','REJECT'], true)) $rec = 'MAYBE';

        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO lpo_retirement_findings
                (retirement_id, supplier_name, contact_name, phone, email, tin, country,
                 capability_notes, quote_received, quote_amount, quote_currency, quote_lead_time_days,
                 sample_received, recommendation, rationale, created_by, created_on)
             VALUES
                (:r, :sn, :cn, :ph, :em, :tin, :co,
                 :cap, :qr, :qa, :qc, :qlt,
                 :sr, :rec, :rat, :u, NOW())",
            [
                ':r'=>$retirementId, ':sn'=>$sName,
                ':cn'=>(string)($d['contact_name'] ?? ''), ':ph'=>(string)($d['phone'] ?? ''),
                ':em'=>(string)($d['email'] ?? ''), ':tin'=>(string)($d['tin'] ?? ''),
                ':co'=>(string)($d['country'] ?? ''), ':cap'=>(string)($d['capability_notes'] ?? ''),
                ':qr'=>!empty($d['quote_received']) ? 1 : 0,
                ':qa'=>!empty($d['quote_amount']) ? (float)$d['quote_amount'] : null,
                ':qc'=>!empty($d['quote_currency']) ? (string)$d['quote_currency'] : null,
                ':qlt'=>!empty($d['quote_lead_time_days']) ? (int)$d['quote_lead_time_days'] : null,
                ':sr'=>!empty($d['sample_received']) ? 1 : 0,
                ':rec'=>$rec, ':rat'=>(string)($d['rationale'] ?? ''),
                ':u'=>$userId,
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function updateFinding(int $findingId, array $d): void
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT retirement_id FROM lpo_retirement_findings WHERE c0d = :id", [':id' => $findingId]);
        if (empty($rs)) throw new RuntimeException('Finding not found.');
        $r = $this->find((int)$rs[0]['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Findings are locked.');
        $rec = (string)($d['recommendation'] ?? 'MAYBE');
        if (!in_array($rec, ['SHORTLIST','MAYBE','REJECT'], true)) $rec = 'MAYBE';
        $db->insertUpdate(
            "UPDATE lpo_retirement_findings SET
                supplier_name=:sn, contact_name=:cn, phone=:ph, email=:em, tin=:tin, country=:co,
                capability_notes=:cap, quote_received=:qr, quote_amount=:qa, quote_currency=:qc, quote_lead_time_days=:qlt,
                sample_received=:sr, recommendation=:rec, rationale=:rat, edited_on=NOW()
              WHERE c0d = :id",
            [
                ':id'=>$findingId,
                ':sn'=>trim((string)($d['supplier_name'] ?? '')),
                ':cn'=>(string)($d['contact_name'] ?? ''), ':ph'=>(string)($d['phone'] ?? ''),
                ':em'=>(string)($d['email'] ?? ''), ':tin'=>(string)($d['tin'] ?? ''),
                ':co'=>(string)($d['country'] ?? ''), ':cap'=>(string)($d['capability_notes'] ?? ''),
                ':qr'=>!empty($d['quote_received']) ? 1 : 0,
                ':qa'=>!empty($d['quote_amount']) ? (float)$d['quote_amount'] : null,
                ':qc'=>!empty($d['quote_currency']) ? (string)$d['quote_currency'] : null,
                ':qlt'=>!empty($d['quote_lead_time_days']) ? (int)$d['quote_lead_time_days'] : null,
                ':sr'=>!empty($d['sample_received']) ? 1 : 0,
                ':rec'=>$rec, ':rat'=>(string)($d['rationale'] ?? ''),
            ]
        );
    }

    public function removeFinding(int $findingId): void
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT retirement_id FROM lpo_retirement_findings WHERE c0d = :id", [':id' => $findingId]);
        if (empty($rs)) return;
        $r = $this->find((int)$rs[0]['retirement_id']);
        if (!$r || !in_array($r['status'], ['DRAFT','REJECTED'], true)) throw new RuntimeException('Findings are locked.');
        $db->insertUpdate("DELETE FROM lpo_retirement_findings WHERE c0d = :id", [':id' => $findingId]);
    }

    /**
     * Batch 4 · seed for RFQ shortlist.
     * Returns SHORTLIST-recommended supplier profiles from any APPROVED Product Discovery retirement
     * whose LPO is `sourced_from_client_po_id = :poId`. Used by the RFQ workspace to pre-populate
     * invited suppliers without Procurement re-typing everything they just captured.
     */
    public function shortlistForPo(int $poId): array
    {
        if ($poId <= 0) return [];
        $db = new DB();
        return $db->mySelect(
            "SELECT f.*, l.lpo_no, r.reviewed_on
               FROM lpo_retirement_findings f
               JOIN lpo_retirements r ON r.c0d = f.retirement_id AND r.deleted = 0 AND r.status = 'APPROVED'
               JOIN lpos l           ON l.c0d = r.lpo_id AND l.deleted = 0
              WHERE l.lpo_type = 'PRODUCT_DISCOVERY'
                AND l.sourced_from_client_po_id = :p
                AND f.recommendation = 'SHORTLIST'
              ORDER BY f.supplier_name",
            [':p' => $poId]
        ) ?: [];
    }
}
