<?php
/**
 * PaymentSchedules — Slice 8Z-l · batched payables.
 *
 * BOOKED LPOs (Accountant Prepared, awaiting funds approval) get assembled into a Payment
 * Schedule. The schedule (not the individual LPO) walks the FM → Exec chain. On terminal
 * APPROVED, every LPO in the batch flips to APPROVED (ready for disburse).
 *
 * Segregation-of-duties: when FM creates a schedule, their FM_APPROVE step auto-approves
 * with a "Self-approved by creator" note — chain jumps straight to Exec.
 */
class PaymentSchedules extends Model
{
    /** LPOs that are BOOKED and NOT yet on any schedule — the source pool for building. */
    public function bookedLposFor(array $f = []): array
    {
        $db = new DB();
        $where = "WHERE l.deleted = 0 AND l.cash_out_status = 'BOOKED'
                  AND NOT EXISTS (SELECT 1 FROM payment_schedule_items i WHERE i.lpo_id = l.c0d)";
        $params = [];
        if (!empty($f['currency'])) { $where .= " AND l.currency = :c"; $params[':c'] = $f['currency']; }
        if (!empty($f['method']))   { $where .= " AND l.disburse_method = :m"; $params[':m'] = $f['method']; }
        return $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.lpo_type, l.amount, l.currency, l.purpose,
                    l.requested_by, l.requested_by_name, l.scheduled_disburse_date,
                    l.disburse_method, l.disburse_account_id, l.override_reason,
                    l.accountant_prep_notes, l.approval_chain_key,
                    p.project_no, p.project_name,
                    a.provider_name AS dest_provider, a.account_number AS dest_account, a.account_holder_name AS dest_holder, a.status AS dest_status
               FROM lpos l
          LEFT JOIN c_projects p     ON p.c0d = l.project_id
          LEFT JOIN payee_accounts a ON a.c0d = l.disburse_account_id
               $where
              ORDER BY l.disburse_method, l.requested_by_name, l.c0d",
            $params
        ) ?: [];
    }

    public function list(string $status = ''): array
    {
        $db = new DB();
        $where = "WHERE s.deleted = 0";
        $p = [];
        if ($status !== '') { $where .= " AND s.status = :s"; $p[':s'] = $status; }
        return $db->mySelect(
            "SELECT s.*, (SELECT COUNT(*) FROM payment_schedule_items i WHERE i.schedule_id = s.c0d) AS calc_item_count,
                    (SELECT COALESCE(SUM(i.amount),0) FROM payment_schedule_items i WHERE i.schedule_id = s.c0d) AS calc_total
               FROM payment_schedules s $where ORDER BY s.c0d DESC",
            $p
        ) ?: [];
    }

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

    /** Items with LPO + payee + destination account joined for the detail view. */
    public function itemsFor(int $scheduleId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT i.c0d AS item_id, i.amount, i.method, i.account_id,
                    i.payment_request_id, pr.pr_no,
                    l.c0d AS lpo_id, l.lpo_no, l.lpo_type, l.purpose, l.currency,
                    l.requested_by, l.requested_by_name, l.override_reason, l.accountant_prep_notes,
                    p.project_no,
                    a.provider_name AS dest_provider, a.account_number AS dest_account, a.account_holder_name AS dest_holder, a.status AS dest_status
               FROM payment_schedule_items i
               JOIN lpos l               ON l.c0d = i.lpo_id
          LEFT JOIN payment_requests pr  ON pr.c0d = i.payment_request_id
          LEFT JOIN c_projects p         ON p.c0d = l.project_id
          LEFT JOIN payee_accounts a     ON a.c0d = i.account_id
              WHERE i.schedule_id = :s
              ORDER BY i.method, l.requested_by_name, i.c0d",
            [':s' => $scheduleId]
        ) ?: [];
    }

    private function nextScheduleNo(): string
    {
        $db = new DB();
        $year = date('Y');
        $r = $db->mySelect(
            "SELECT schedule_no FROM payment_schedules WHERE schedule_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'PS-' . $year . '-%']
        );
        $seq = 1;
        if (!empty($r) && preg_match('/-(\d+)$/', $r[0]['schedule_no'], $m)) $seq = (int)$m[1] + 1;
        return sprintf('PS-%s-%04d', $year, $seq);
    }

    /**
     * Create a DRAFT schedule from a bunch of BOOKED LPO IDs. Enforces same-currency,
     * skips any LPO that isn't actually BOOKED or is already on a schedule. Returns the
     * new schedule id + counts.
     */
    public function createFromLpos(array $lpoIds, string $scheduledDate, string $notes, int $userId, string $userName, string $userGroup): array
    {
        $lpoIds = array_values(array_unique(array_map('intval', $lpoIds)));
        if (empty($lpoIds)) return ['ok' => false, 'msg' => 'No LPOs selected.'];

        $db = new DB();
        // int-cast list is safe to interpolate (no user string content); DB::mySelect requires
        // a params array as second arg even when empty.
        $inList = implode(',', array_map('intval', $lpoIds));
        $rows = $db->mySelect(
            "SELECT l.c0d, l.amount, l.currency, l.disburse_method, l.disburse_account_id, l.cash_out_status
               FROM lpos l
              WHERE l.c0d IN ($inList)
                AND l.deleted = 0",
            []
        );
        if (empty($rows)) return ['ok' => false, 'msg' => 'None of the selected LPOs found.'];

        // Filter: must be BOOKED + not on another schedule
        $onSch = $db->mySelect(
            "SELECT lpo_id FROM payment_schedule_items WHERE lpo_id IN ($inList)",
            []
        );
        $blocked = array_column($onSch, 'lpo_id');
        $eligible = [];
        $currency = null;
        foreach ($rows as $r) {
            if ($r['cash_out_status'] !== 'BOOKED')             continue;
            if (in_array((int)$r['c0d'], array_map('intval', $blocked), true)) continue;
            if ($currency === null) $currency = $r['currency'];
            elseif ($currency !== $r['currency'])              continue; // skip mismatched currencies
            $eligible[] = $r;
        }
        if (empty($eligible)) return ['ok' => false, 'msg' => 'No eligible BOOKED LPOs in the selection.'];

        $total = 0.0;
        foreach ($eligible as $r) $total += (float)$r['amount'];

        $no = $this->nextScheduleNo();
        $ins = $db->insertUpdate(
            "INSERT INTO payment_schedules
                (schedule_no, currency, status, total_amount, item_count, scheduled_date, notes,
                 created_by, created_by_name, created_by_group)
             VALUES (:n, :c, 'DRAFT', :t, :ic, :sd, :nt, :u, :un, :ug)",
            [
                ':n'=>$no, ':c'=>$currency, ':t'=>$total, ':ic'=>count($eligible),
                ':sd'=>$scheduledDate ?: null, ':nt'=>trim($notes) ?: null,
                ':u'=>$userId, ':un'=>$userName, ':ug'=>$userGroup,
            ]
        );
        $sid = (int)$ins['last_id'];
        if ($sid <= 0) return ['ok' => false, 'msg' => 'Schedule create failed.'];

        foreach ($eligible as $r) {
            $db->insertUpdate(
                "INSERT IGNORE INTO payment_schedule_items (schedule_id, lpo_id, amount, method, account_id, added_by)
                 VALUES (:s, :l, :a, :m, :ac, :u)",
                [':s'=>$sid, ':l'=>(int)$r['c0d'], ':a'=>(float)$r['amount'],
                 ':m'=>$r['disburse_method'] ?: null, ':ac'=>$r['disburse_account_id'] ?: null, ':u'=>$userId]
            );
        }
        return ['ok' => true, 'id' => $sid, 'schedule_no' => $no, 'added' => count($eligible), 'skipped' => count($lpoIds) - count($eligible)];
    }

    /**
     * Slice 8Z-w · Slice D4 — Create a schedule directly in READY_TO_EXECUTE from a list of PRs
     * already at FM_SCHEDULE stage. Skips approval since each PR walked PC→PM→FM chain individually.
     *
     * Each schedule item stamps BOTH lpo_id AND payment_request_id so recordDisbursement can dispatch
     * to PaymentRequests::acctRecord (which handles journal + LPO auto-close) instead of Lpos::applyPayment.
     */
    public function createFromPrs(array $prIds, string $scheduledDate, string $notes, int $userId, string $userName, string $userGroup): array
    {
        $prIds = array_values(array_unique(array_map('intval', $prIds)));
        if (empty($prIds)) return ['ok' => false, 'msg' => 'No Payment Requests selected.'];

        $db = new DB();
        $inList = implode(',', array_map('intval', $prIds));
        $rows = $db->mySelect(
            "SELECT pr.c0d AS pr_id, pr.pr_no, pr.requested_amount, pr.currency, pr.status, pr.next_check_status,
                    pr.lpo_id, l.lpo_no, l.disburse_account_id
               FROM payment_requests pr
               JOIN lpos l ON l.c0d = pr.lpo_id
              WHERE pr.c0d IN ($inList) AND pr.deleted = 0",
            []
        );
        if (empty($rows)) return ['ok' => false, 'msg' => 'None of the selected PRs found.'];

        // Filter: PR must be at FM_SCHEDULE rung (status PM_REVIEWED, next=FM_SCHEDULE) AND not already on a schedule.
        $onSch = $db->mySelect("SELECT payment_request_id FROM payment_schedule_items WHERE payment_request_id IN ($inList) AND payment_request_id IS NOT NULL", []);
        $blocked = array_column($onSch, 'payment_request_id');
        $eligible = []; $currency = null; $skipReasons = [];
        foreach ($rows as $r) {
            if ((string)$r['next_check_status'] !== 'FM_SCHEDULE') { $skipReasons[] = $r['pr_no'] . ' not at FM_SCHEDULE'; continue; }
            if (in_array((int)$r['pr_id'], array_map('intval', $blocked), true)) { $skipReasons[] = $r['pr_no'] . ' already on a schedule'; continue; }
            if ($currency === null) $currency = $r['currency'];
            elseif ($currency !== $r['currency']) { $skipReasons[] = $r['pr_no'] . ' currency mismatch'; continue; }
            $eligible[] = $r;
        }
        if (empty($eligible)) return ['ok' => false, 'msg' => 'No eligible PRs — ' . implode('; ', $skipReasons)];

        $total = 0.0; foreach ($eligible as $r) $total += (float)$r['requested_amount'];
        $no = $this->nextScheduleNo();

        // Skip approval — PRs each walked their own PC→PM→FM chain.
        $ins = $db->insertUpdate(
            "INSERT INTO payment_schedules
                (schedule_no, currency, status, total_amount, item_count, scheduled_date, notes,
                 created_by, created_by_name, created_by_group)
             VALUES (:n, :c, 'READY_TO_EXECUTE', :t, :ic, :sd, :nt, :u, :un, :ug)",
            [
                ':n'=>$no, ':c'=>$currency, ':t'=>$total, ':ic'=>count($eligible),
                ':sd'=>$scheduledDate ?: null,
                ':nt'=>trim($notes) ?: ('Batched from ' . count($eligible) . ' Payment Request' . (count($eligible) === 1 ? '' : 's')),
                ':u'=>$userId, ':un'=>$userName, ':ug'=>$userGroup,
            ]
        );
        $sid = (int)$ins['last_id'];
        if ($sid <= 0) return ['ok' => false, 'msg' => 'Schedule create failed.'];

        // Attach each PR — schedule item carries both lpo_id (for existing queries) and payment_request_id.
        $prm = null;
        foreach ($eligible as $r) {
            $db->insertUpdate(
                "INSERT INTO payment_schedule_items (schedule_id, lpo_id, payment_request_id, amount, account_id, added_by)
                 VALUES (:s, :l, :pr, :a, :ac, :u)",
                [':s'=>$sid, ':l'=>(int)$r['lpo_id'], ':pr'=>(int)$r['pr_id'],
                 ':a'=>(float)$r['requested_amount'], ':ac'=>$r['disburse_account_id'] ?: null, ':u'=>$userId]
            );
            // Attach PR to schedule + advance to SCHEDULED / next=ACCT_RECORD
            if ($prm === null) {
                if (!class_exists('PaymentRequests')) require_once ROOT . '/app/models/PaymentRequests.php';
                $prm = new PaymentRequests();
            }
            try { $prm->attachToSchedule((int)$r['pr_id'], $sid, $userId, $userName); }
            catch (\Throwable $e) { /* soldier on — surface via skipReasons if needed */ }
        }

        return ['ok' => true, 'id' => $sid, 'schedule_no' => $no, 'added' => count($eligible),
                'skipped' => count($prIds) - count($eligible), 'skip_reasons' => $skipReasons];
    }

    public function addLpos(int $scheduleId, array $lpoIds, int $userId): array
    {
        $s = $this->byId($scheduleId);
        if (!$s)                              return ['ok' => false, 'msg' => 'Schedule not found.'];
        if ($s['status'] !== 'DRAFT')         return ['ok' => false, 'msg' => 'Schedule is ' . $s['status'] . ' — cannot add LPOs.'];
        $lpoIds = array_values(array_unique(array_map('intval', $lpoIds)));
        if (empty($lpoIds)) return ['ok' => false, 'msg' => 'No LPOs selected.'];

        $db = new DB();
        $inList = implode(',', array_map('intval', $lpoIds));
        $rows = $db->mySelect(
            "SELECT c0d, amount, currency, disburse_method, disburse_account_id, cash_out_status
               FROM lpos WHERE c0d IN ($inList) AND deleted = 0",
            []
        );
        $onSch = $db->mySelect(
            "SELECT lpo_id FROM payment_schedule_items WHERE lpo_id IN ($inList)",
            []
        );
        $blocked = array_map('intval', array_column($onSch, 'lpo_id'));
        $added = 0;
        foreach ($rows as $r) {
            if ($r['cash_out_status'] !== 'BOOKED')             continue;
            if ($r['currency'] !== $s['currency'])              continue;
            if (in_array((int)$r['c0d'], $blocked, true))       continue;
            $db->insertUpdate(
                "INSERT IGNORE INTO payment_schedule_items (schedule_id, lpo_id, amount, method, account_id, added_by)
                 VALUES (:s, :l, :a, :m, :ac, :u)",
                [':s'=>$scheduleId, ':l'=>(int)$r['c0d'], ':a'=>(float)$r['amount'],
                 ':m'=>$r['disburse_method'] ?: null, ':ac'=>$r['disburse_account_id'] ?: null, ':u'=>$userId]
            );
            $added++;
        }
        $this->recomputeTotals($scheduleId);
        return ['ok' => true, 'added' => $added, 'skipped' => count($lpoIds) - $added];
    }

    public function removeLpo(int $scheduleId, int $lpoId): array
    {
        $s = $this->byId($scheduleId);
        if (!$s)                              return ['ok' => false, 'msg' => 'Schedule not found.'];
        if ($s['status'] !== 'DRAFT')         return ['ok' => false, 'msg' => 'Schedule is ' . $s['status'] . ' — cannot remove LPOs.'];
        $db = new DB();
        $db->insertUpdate("DELETE FROM payment_schedule_items WHERE schedule_id = :s AND lpo_id = :l", [':s'=>$scheduleId, ':l'=>$lpoId]);
        $this->recomputeTotals($scheduleId);
        return ['ok' => true];
    }

    private function recomputeTotals(int $scheduleId): void
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT COUNT(*) n, COALESCE(SUM(amount),0) t FROM payment_schedule_items WHERE schedule_id = :s",
            [':s' => $scheduleId]
        );
        $db->insertUpdate(
            "UPDATE payment_schedules SET item_count = :n, total_amount = :t, edited_on = NOW() WHERE c0d = :id",
            [':n'=>(int)($r[0]['n'] ?? 0), ':t'=>(float)($r[0]['t'] ?? 0), ':id'=>$scheduleId]
        );
    }

    /**
     * Submit the schedule into the PAYMENT_SCHEDULE_APPROVAL chain.
     * FM-created schedules auto-approve the FM_APPROVE step (creator ≠ approver rule).
     */
    public function submit(int $scheduleId, int $userId, string $userName, string $userGroup): array
    {
        $s = $this->byId($scheduleId);
        if (!$s)                        return ['ok' => false, 'msg' => 'Schedule not found.'];
        if ($s['status'] !== 'DRAFT')   return ['ok' => false, 'msg' => 'Schedule is ' . $s['status'] . ' — already submitted.'];
        if ((int)$s['item_count'] === 0) return ['ok' => false, 'msg' => 'Schedule has no LPOs — add some before submitting.'];

        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $ac = new ApprovalChain();
        $ref = $s['schedule_no'] . ' · ' . $s['currency'] . ' ' . number_format((float)$s['total_amount']) . ' · ' . $s['item_count'] . ' item' . ($s['item_count'] == 1 ? '' : 's');
        $sr = $ac->submit([
            'chain_key'   => 'PAYMENT_SCHEDULE_APPROVAL',
            'entity_type' => 'payment_schedule',
            'entity_id'   => $scheduleId,
            'ref_label'   => $ref,
            'return_url'  => 'PaymentSchedules/view/' . $scheduleId,
            'user_id'     => $userId,
            'user_name'   => $userName,
            'user_group'  => $userGroup,
            'comment'     => 'Payment schedule submitted for FM/Exec approval.',
        ]);
        if (empty($sr['ok'])) return ['ok' => false, 'msg' => 'Submit failed: ' . ($sr['msg'] ?? '')];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE payment_schedules SET status = 'SUBMITTED', approval_id = :aid, edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':aid'=>(int)$sr['approval_id'], ':u'=>$userId, ':id'=>$scheduleId]
        );

        // Creator ≠ approver: FM-created schedules auto-approve the FM step.
        if (strtoupper(trim($userGroup)) === 'FINANCE MANAGER') {
            $auto = $ac->approve([
                'approval_id' => (int)$sr['approval_id'],
                'user_id'     => $userId,
                'user_name'   => $userName,
                'user_group'  => $userGroup,
                'comment'     => 'Self-approved by creator (FM) — jumps to Exec.',
            ]);
            // If auto-approval failed, chain is still valid — FM can approve again manually.
        }

        // Notify Finance Manager group so they see the queue update.
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notifyGroup(
                'Finance Manager', 'SCHEDULE_PENDING_FM',
                'Payment schedule to approve: ' . $s['schedule_no'],
                $s['currency'] . ' ' . number_format((float)$s['total_amount']) . ' · ' . $s['item_count'] . ' items',
                'FinChains/view/' . (int)$sr['approval_id'], 'payment_schedule', $scheduleId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }

        return ['ok' => true, 'approval_id' => (int)$sr['approval_id']];
    }

    /* ── Slice 8Z-m · proposal review (FM step) ───────────────────────── */

    /** Group items by (payee_type, payee_id, method) — the FM proposal-review structure. */
    public function groupedItemsFor(int $scheduleId): array
    {
        $items = $this->itemsFor($scheduleId);
        // Enrich with LPO remaining + age for FM's decision.
        $groups = [];
        foreach ($items as $it) {
            $lpoId = (int)$it['lpo_id'];
            // Payee resolution: STAFF uses requester (default), VENDOR/CUSTOMER — future when supplier LPOs schedule here.
            $key = 'STAFF:' . (int)$it['requested_by'] . ':' . ($it['method'] ?: 'UNSET');
            if (!isset($groups[$key])) {
                $groups[$key] = [
                    'group_key'    => $key,
                    'payee_type'   => 'STAFF',
                    'payee_id'     => (int)$it['requested_by'],
                    'payee_name'   => $it['requested_by_name'],
                    'method'       => $it['method'] ?: 'UNSET',
                    'items'        => [],
                    'total'        => 0.0,
                    'count'        => 0,
                ];
            }
            $groups[$key]['items'][] = $it;
            $groups[$key]['total']  += (float)$it['amount'];
            $groups[$key]['count']++;
        }
        // FIFO-sort each group's items by LPO created_on ASC (oldest first).
        if (!empty($groups)) {
            $db = new DB();
            $ids = array_column($items, 'lpo_id');
            $ages = [];
            if (!empty($ids)) {
                $inList = implode(',', array_map('intval', $ids));
                $r = $db->mySelect("SELECT c0d, created_on, amount, amount_paid FROM lpos WHERE c0d IN ($inList)", []);
                foreach ($r as $rr) $ages[(int)$rr['c0d']] = $rr;
            }
            foreach ($groups as &$g) {
                foreach ($g['items'] as &$it2) {
                    $meta = $ages[(int)$it2['lpo_id']] ?? [];
                    $it2['lpo_created_on'] = $meta['created_on'] ?? null;
                    $it2['lpo_total']      = (float)($meta['amount'] ?? $it2['amount']);
                    $it2['lpo_paid']       = (float)($meta['amount_paid'] ?? 0);
                    $it2['lpo_remaining']  = max(0, (float)$it2['lpo_total'] - (float)$it2['lpo_paid']);
                }
                unset($it2);
                usort($g['items'], function ($a, $b) {
                    return strcmp((string)$a['lpo_created_on'], (string)$b['lpo_created_on']);
                });
            }
            unset($g);
        }
        return array_values($groups);
    }

    /**
     * FM edits a single line's pay amount (partial pay).
     * Guardrail: amount can't exceed the LPO's remaining amount, and must be > 0 (use excludeItem for 0).
     * Audit-logs the reduction via schedule_exclusion_audit action=AMOUNT_REDUCED.
     */
    public function updateItemAmount(int $scheduleId, int $lpoId, float $newAmount, string $comment, int $userId, string $userName): array
    {
        if ($newAmount <= 0) return ['ok' => false, 'msg' => 'Amount must be > 0. Use Exclude to remove.'];
        $db = new DB();
        $it = $db->mySelect(
            "SELECT psi.c0d AS item_id, psi.amount AS current_amount, l.amount AS lpo_total, l.amount_paid
               FROM payment_schedule_items psi JOIN lpos l ON l.c0d = psi.lpo_id
              WHERE psi.schedule_id = :s AND psi.lpo_id = :l",
            [':s' => $scheduleId, ':l' => $lpoId]
        );
        if (empty($it))                              return ['ok' => false, 'msg' => 'Item not on this schedule.'];
        $remaining = (float)$it[0]['lpo_total'] - (float)$it[0]['amount_paid'];
        if ($newAmount > $remaining + 0.005)         return ['ok' => false, 'msg' => 'Amount exceeds LPO remaining (' . number_format($remaining) . ').'];

        $original = (float)$it[0]['current_amount'];
        $db->insertUpdate(
            "UPDATE payment_schedule_items SET amount = :a WHERE c0d = :id",
            [':a' => $newAmount, ':id' => (int)$it[0]['item_id']]
        );
        // Audit if reduced
        if ($newAmount < $original - 0.005) {
            $db->insertUpdate(
                "INSERT INTO schedule_exclusion_audit
                    (schedule_id, lpo_id, action, original_amount, new_amount, comment, performed_by, performed_by_name)
                 VALUES (:s, :l, 'AMOUNT_REDUCED', :o, :n, :c, :u, :un)",
                [':s'=>$scheduleId, ':l'=>$lpoId, ':o'=>$original, ':n'=>$newAmount,
                 ':c'=>trim($comment) ?: 'FM adjusted amount', ':u'=>$userId, ':un'=>$userName]
            );
        }
        $this->recomputeTotals($scheduleId);
        return ['ok' => true];
    }

    /**
     * FM excludes an item from the schedule. Deletes the junction row (LPO returns to BOOKED pool
     * for another schedule). Requires comment for the audit trail.
     */
    public function excludeItem(int $scheduleId, int $lpoId, string $comment, int $userId, string $userName): array
    {
        if (trim($comment) === '') return ['ok' => false, 'msg' => 'Comment required — reason for exclusion (audit trail).'];
        $db = new DB();
        $it = $db->mySelect(
            "SELECT c0d, amount FROM payment_schedule_items WHERE schedule_id = :s AND lpo_id = :l",
            [':s' => $scheduleId, ':l' => $lpoId]
        );
        if (empty($it))                                     return ['ok' => false, 'msg' => 'Item not on this schedule.'];
        $original = (float)$it[0]['amount'];
        $db->insertUpdate(
            "INSERT INTO schedule_exclusion_audit
                (schedule_id, lpo_id, action, original_amount, new_amount, comment, performed_by, performed_by_name)
             VALUES (:s, :l, 'EXCLUDED', :o, 0, :c, :u, :un)",
            [':s'=>$scheduleId, ':l'=>$lpoId, ':o'=>$original, ':c'=>trim($comment), ':u'=>$userId, ':un'=>$userName]
        );
        $db->insertUpdate("DELETE FROM payment_schedule_items WHERE c0d = :id", [':id' => (int)$it[0]['c0d']]);
        $this->recomputeTotals($scheduleId);

        // Best-effort notification to the LPO's requester
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $r = $db->mySelect("SELECT requested_by, lpo_no FROM lpos WHERE c0d = :l", [':l' => $lpoId]);
            $reqId = (int)($r[0]['requested_by'] ?? 0);
            if ($reqId > 0) {
                (new Notifications())->notify(
                    $reqId, 'SCHEDULE_ITEM_EXCLUDED',
                    'LPO excluded from payment schedule: ' . (string)$r[0]['lpo_no'],
                    'FM excluded this LPO from the current batch — it remains BOOKED for the next schedule. Reason: ' . mb_strimwidth(trim($comment), 0, 200, '…'),
                    'Lpos/view/' . $lpoId, 'lpo', $lpoId, $userId, $userName
                );
            }
        } catch (\Throwable $e) { /* best-effort */ }
        return ['ok' => true];
    }

    /**
     * FIFO cap allocator — FM types a group cap; oldest LPOs (in this group) get filled first
     * up to the cap; overflow items are excluded (removed from the schedule). Returns per-item
     * changes for the UI feedback.
     */
    public function applyGroupCap(int $scheduleId, string $payeeType, int $payeeId, string $method, float $cap, string $comment, int $userId, string $userName): array
    {
        if ($cap <= 0) return ['ok' => false, 'msg' => 'Cap must be > 0.'];
        $groups = $this->groupedItemsFor($scheduleId);
        $target = null;
        foreach ($groups as $g) {
            if ($g['payee_type'] === $payeeType && $g['payee_id'] === $payeeId && $g['method'] === $method) { $target = $g; break; }
        }
        if (!$target) return ['ok' => false, 'msg' => 'Group not found on this schedule.'];

        // Items are already FIFO-sorted (oldest first)
        $remaining = $cap;
        $changes = [];
        foreach ($target['items'] as $it) {
            $lpoId = (int)$it['lpo_id'];
            $lpoRem = (float)$it['lpo_remaining'];
            $currentAmt = (float)$it['amount'];
            if ($remaining <= 0) {
                // Exclude entirely
                $r = $this->excludeItem($scheduleId, $lpoId, $comment ?: 'FM group cap — item outside cap window', $userId, $userName);
                if ($r['ok']) $changes[] = ['lpo_id' => $lpoId, 'action' => 'excluded'];
            } else {
                $newAmt = min($currentAmt, $lpoRem, $remaining);
                if (abs($newAmt - $currentAmt) < 0.005) {
                    // no change
                    $remaining -= $newAmt;
                    continue;
                }
                $r = $this->updateItemAmount($scheduleId, $lpoId, $newAmt, $comment ?: 'FM group cap allocation', $userId, $userName);
                if ($r['ok']) $changes[] = ['lpo_id' => $lpoId, 'action' => 'reduced', 'new_amount' => $newAmt];
                $remaining -= $newAmt;
            }
        }
        return ['ok' => true, 'changes' => $changes, 'remaining_cap' => max(0, $remaining)];
    }

    public function setSourceAccount(int $scheduleId, int $accountId): array
    {
        $db = new DB();
        // Currency match check
        $s = $this->byId($scheduleId);
        if (!$s) return ['ok' => false, 'msg' => 'Schedule not found.'];
        $a = $db->mySelect("SELECT currency, is_active FROM company_bank_accounts WHERE c0d = :id", [':id' => $accountId]);
        if (empty($a))                              return ['ok' => false, 'msg' => 'Source account not found.'];
        if ((int)$a[0]['is_active'] !== 1)         return ['ok' => false, 'msg' => 'Source account is disabled.'];
        if ($s['currency'] !== $a[0]['currency']) return ['ok' => false, 'msg' => 'Source currency (' . $a[0]['currency'] . ') must match schedule (' . $s['currency'] . ').'];
        $db->insertUpdate(
            "UPDATE payment_schedules SET source_account_id = :a, edited_on = NOW() WHERE c0d = :id",
            [':a' => $accountId, ':id' => $scheduleId]
        );
        return ['ok' => true];
    }

    /** Slice 8Z-n · FM stamps their read of the source account balance at approval (advisory). */
    public function setBalanceSnapshot(int $scheduleId, ?float $balance): void
    {
        if ($balance === null || $balance < 0) return;
        $db = new DB();
        $db->insertUpdate(
            "UPDATE payment_schedules SET fm_balance_snapshot = :b, fm_balance_snapshot_at = NOW(), edited_on = NOW() WHERE c0d = :id",
            [':b' => $balance, ':id' => $scheduleId]
        );
    }

    /** Slice 8Z-n · proof of payment attachments per schedule (batch scope) or per LPO (LINE scope). */
    public function attachmentsFor(int $scheduleId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT a.*, l.lpo_no
               FROM schedule_disburse_attachments a
          LEFT JOIN lpos l ON l.c0d = a.lpo_id
              WHERE a.schedule_id = :s
              ORDER BY a.scope DESC, a.c0d DESC",
            [':s' => $scheduleId]
        ) ?: [];
    }

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

    /** Stash an uploaded proof file. Returns [rel_path, orig, mime, size] or throws. */
    public function stashProof(array $file, int $scheduleId): array
    {
        $MAX_BYTES = 10 * 1024 * 1024; // 10 MB per file
        $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 > $MAX_BYTES)          throw new RuntimeException('Proof file size invalid (max 10 MB).');
        $mime = @mime_content_type($file['tmp_name']) ?: (string)($file['type'] ?? '');
        $okMimes = ['application/pdf','image/jpeg','image/png','image/webp'];
        if (!in_array($mime, $okMimes, true))          throw new RuntimeException('Unsupported proof file type: ' . $mime . ' (PDF / JPG / PNG only).');

        $orig = (string)($file['name'] ?? 'proof');
        $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/schedules/' . $scheduleId;
        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 proof.');
        return ['attachments/schedules/' . $scheduleId . '/' . $base, $orig, $mime, $size];
    }

    public function addAttachment(int $scheduleId, string $scope, array $file, ?int $lpoId, int $userId, string $userName): int
    {
        if (!in_array($scope, ['BATCH','LINE'], true)) throw new InvalidArgumentException('Invalid scope.');
        [$rel, $orig, $mime, $size] = $this->stashProof($file, $scheduleId);
        $db = new DB();
        $r = $db->insertUpdate(
            "INSERT INTO schedule_disburse_attachments
                (schedule_id, lpo_id, scope, file_path, original_name, mime, size_bytes, uploaded_by, uploaded_by_name)
             VALUES (:s, :l, :sc, :f, :on, :mm, :sz, :u, :un)",
            [':s'=>$scheduleId, ':l'=>$lpoId ?: null, ':sc'=>$scope, ':f'=>$rel,
             ':on'=>$orig, ':mm'=>$mime, ':sz'=>$size, ':u'=>$userId, ':un'=>$userName]
        );
        return (int)($r['last_id'] ?? 0);
    }

    /**
     * Record disbursement — post-Exec-authorised. Applies per-item amounts to LPO.amount_paid,
     * flips LPO to PAID (fully) or leaves at BOOKED (partial · remainder available for another
     * schedule). Marks the schedule PAID.
     *
     * $refs: ['batch' => 'REF-123', 'per_line' => [lpo_id => 'REF-A', ...]]
     */
    public function recordDisbursement(int $scheduleId, array $refs, int $userId, string $userName): array
    {
        $s = $this->byId($scheduleId);
        if (!$s)                             return ['ok' => false, 'msg' => 'Schedule not found.'];
        // Slice 8Z-o · READY_TO_EXECUTE (post FM Prepare) is the normal path; APPROVED is the pinch bypass.
        if (!in_array($s['status'], ['APPROVED','READY_TO_EXECUTE'], true)) {
            return ['ok' => false, 'msg' => 'Schedule must be APPROVED or READY_TO_EXECUTE to record disbursement (current: ' . $s['status'] . ').'];
        }

        // Slice 8Z-w · Slice D4 — Load raw items including payment_request_id so we can dispatch
        // to PaymentRequests::acctRecord for PR-linked items (which posts journal + auto-closes LPO)
        // vs. Lpos::applyPayment for the classic BOOKED-LPO items.
        $db = new DB();
        $rawItems = $db->mySelect(
            "SELECT c0d AS item_id, lpo_id, payment_request_id, amount
               FROM payment_schedule_items WHERE schedule_id = :s ORDER BY c0d",
            [':s' => $scheduleId]
        ) ?: [];
        if (empty($rawItems))                return ['ok' => false, 'msg' => 'No items on this schedule.'];

        if (!class_exists('Lpos'))            require_once ROOT . '/app/models/Lpos.php';
        if (!class_exists('PaymentRequests')) require_once ROOT . '/app/models/PaymentRequests.php';
        $lm  = new Lpos();
        $prm = new PaymentRequests();
        $batchRef = trim((string)($refs['batch'] ?? ''));
        $perLine  = $refs['per_line'] ?? [];
        $paidCount = 0; $prPaidCount = 0;
        foreach ($rawItems as $it) {
            $ref = $batchRef;
            if (!empty($perLine[(int)$it['lpo_id']])) $ref = (string)$perLine[(int)$it['lpo_id']];
            $prId = (int)($it['payment_request_id'] ?? 0);
            if ($prId > 0) {
                // Slice D4 · PR-linked item — acctRecord handles LPO rollup + journal + auto-close
                try { $prm->acctRecord($prId, $ref, null, 'Executed via schedule #' . $scheduleId, $userId, $userName); $prPaidCount++; }
                catch (\Throwable $e) { /* soldier on */ }
            } else {
                $lm->applyPayment((int)$it['lpo_id'], (float)$it['amount'], $ref, $userId, $userName);
            }
            $paidCount++;
        }
        $db->insertUpdate(
            "UPDATE payment_schedules SET status = 'PAID', edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':u' => $userId, ':id' => $scheduleId]
        );
        return ['ok' => true, 'paid_count' => $paidCount, 'pr_paid_count' => $prPaidCount];
    }

    /** Called from FinChainsController::postDecisionHook on terminal APPROVED. */
    public function onApproved(int $scheduleId): void
    {
        $db = new DB();
        $items = $db->mySelect("SELECT lpo_id FROM payment_schedule_items WHERE schedule_id = :s", [':s' => $scheduleId]) ?: [];
        foreach ($items as $it) {
            $db->insertUpdate(
                "UPDATE lpos SET cash_out_status = 'APPROVED', edited_on = NOW() WHERE c0d = :id AND cash_out_status = 'BOOKED'",
                [':id' => (int)$it['lpo_id']]
            );
        }
        $db->insertUpdate(
            "UPDATE payment_schedules SET status = 'APPROVED', edited_on = NOW() WHERE c0d = :id",
            [':id' => $scheduleId]
        );

        // Slice 8Z-o · fan-out: creator + FM group + Accountant group (deduped).
        // FM handles Prepare next (allocates instrument); Accountant executes after.
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $s = $this->byId($scheduleId);
            if (empty($s)) return;
            $subject = 'Payment schedule approved: ' . $s['schedule_no'];
            $body = 'FM + Exec authorised the batch (' . $s['item_count'] . ' LPOs, ' . $s['currency'] . ' ' . number_format((float)$s['total_amount']) . '). FM: allocate instrument + planned date · Accountant: execute + record refs.';
            $url = 'PaymentSchedules/view/' . $scheduleId;
            $noti = new Notifications();
            $creatorId    = (int)($s['created_by'] ?? 0);
            $creatorGroup = strtoupper(trim((string)($s['created_by_group'] ?? '')));

            // Direct notify creator (unless they'll get it via the group fan-out below)
            if ($creatorId > 0 && !in_array($creatorGroup, ['FINANCE MANAGER','ACCOUNTANT'], true)) {
                $noti->notify($creatorId, 'SCHEDULE_APPROVED', $subject, $body, $url, 'payment_schedule', $scheduleId, 0, 'System');
            }
            // Group fan-out — both FM (to prepare) and Accountant (to execute)
            $noti->notifyGroup('Finance Manager', 'SCHEDULE_APPROVED', $subject, $body, $url, 'payment_schedule', $scheduleId, 0, 'System');
            $noti->notifyGroup('Accountant',      'SCHEDULE_APPROVED', $subject, $body, $url, 'payment_schedule', $scheduleId, 0, 'System');
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /**
     * Slice 8Z-o · FM Prepare — allocates the payment instrument + planned date, flips schedule
     * to READY_TO_EXECUTE, nudges Accountant group to execute.
     * Slice 8Z-p · optional attachment (cheque scan, signed letter PDF, screenshot) stashed with
     * scope=PREP so Accountant sees the same evidence before executing.
     */
    public function preparePayment(int $scheduleId, array $d, int $userId, string $userName, ?array $file = null): array
    {
        $s = $this->byId($scheduleId);
        if (!$s)                                                                   return ['ok' => false, 'msg' => 'Schedule not found.'];
        if (!in_array($s['status'], ['APPROVED','READY_TO_EXECUTE'], true))       return ['ok' => false, 'msg' => 'Schedule must be APPROVED to Prepare (current: ' . $s['status'] . ').'];

        $inst = strtoupper((string)($d['disburse_instrument'] ?? ''));
        if (!in_array($inst, ['CHEQUE','BANK_BATCH','MOBILE_BATCH','BANK_LETTER','CASH_WITHDRAWAL','MIXED'], true)) {
            return ['ok' => false, 'msg' => 'Invalid instrument type.'];
        }
        $ref     = trim((string)($d['disburse_instrument_ref'] ?? ''));
        $planned = trim((string)($d['disburse_planned_date'] ?? ''));
        $notes   = trim((string)($d['disburse_prep_notes'] ?? ''));
        if ($ref === '') return ['ok' => false, 'msg' => 'Instrument reference required (cheque #, batch ID, letter number, etc.).'];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE payment_schedules SET
                status = 'READY_TO_EXECUTE',
                disburse_instrument      = :inst,
                disburse_instrument_ref  = :ref,
                disburse_planned_date    = :pd,
                disburse_prep_notes      = :n,
                disburse_prepared_by     = :u,
                disburse_prepared_by_name= :un,
                disburse_prepared_at     = NOW(),
                edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':inst'=>$inst, ':ref'=>$ref, ':pd'=>$planned ?: null, ':n'=>$notes ?: null,
             ':u'=>$userId, ':un'=>$userName, ':id'=>$scheduleId]
        );

        // Slice 8Z-p · optional prep attachment (cheque scan, signed letter, screenshot).
        $attached = false;
        if ($file && !empty($file['tmp_name']) && is_uploaded_file($file['tmp_name'])) {
            try {
                $this->addAttachment($scheduleId, 'PREP', $file, null, $userId, $userName);
                $attached = true;
            } catch (\Throwable $e) { /* best-effort — don't fail the whole Prepare on a bad upload */ }
        }

        // Nudge Accountant group — the batch is ready for execution.
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $labels = [
                'CHEQUE'=>'Cheque', 'BANK_BATCH'=>'Bank batch', 'MOBILE_BATCH'=>'Mobile batch',
                'BANK_LETTER'=>'Bank letter', 'CASH_WITHDRAWAL'=>'Cash withdrawal', 'MIXED'=>'Mixed',
            ];
            $instLabel = $labels[$inst] ?? $inst;
            (new Notifications())->notifyGroup(
                'Accountant', 'SCHEDULE_READY_TO_EXECUTE',
                'Schedule prepared · execute + record refs: ' . $s['schedule_no'],
                $instLabel . ' · ref ' . $ref . ($planned ? ' · planned ' . $planned : '') . ($attached ? ' · attachment uploaded' : ''),
                'PaymentSchedules/view/' . $scheduleId, 'payment_schedule', $scheduleId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }

        return ['ok' => true, 'attached' => $attached];
    }

    /** Called from FinChainsController::postDecisionHook on terminal DECLINED. */
    public function onDeclined(int $scheduleId): void
    {
        $db = new DB();
        // Free the LPOs — they stay BOOKED (never left), just detach from this schedule.
        $db->insertUpdate("DELETE FROM payment_schedule_items WHERE schedule_id = :s", [':s' => $scheduleId]);
        $db->insertUpdate(
            "UPDATE payment_schedules SET status = 'DECLINED', item_count = 0, total_amount = 0, edited_on = NOW() WHERE c0d = :id",
            [':id' => $scheduleId]
        );
    }
}
