<?php
class PaymentVouchers extends Model
{
    public function nextPvNo(): string
    {
        if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
        return (new DocNumberSettings())->allocate('PV');
    }

    public function nextPaymentNo(): string
    {
        if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
        return (new DocNumberSettings())->allocate('PAYMENT');
    }

    /** Create a DRAFT PV against an approved LPO. */
    public function prepFromLpo(int $lpo_id, int $uid, string $uname, string $notes = ''): array
    {
        $db = new DB();
        $lrs = $db->mySelect(
            "SELECT c0d, lpo_no, payee_type, payee_id, payee_name, amount, currency, cash_out_status
               FROM lpos WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $lpo_id]
        );
        $l = $lrs[0] ?? null;
        if (!$l) return ['ok' => false, 'msg' => 'LPO not found.', 'id' => 0];
        if (!in_array($l['cash_out_status'], ['APPROVED','ISSUED'], true)) {
            return ['ok' => false, 'msg' => 'LPO is not APPROVED — cannot prep PV.', 'id' => 0];
        }
        // Prevent duplicate active PV per LPO
        $ex = $db->mySelect(
            "SELECT c0d FROM payment_vouchers WHERE lpo_id = :id AND deleted = 0 AND status IN ('DRAFT','SCHEDULED') LIMIT 1",
            [':id' => $lpo_id]
        );
        if (!empty($ex)) return ['ok' => false, 'msg' => 'A DRAFT / SCHEDULED PV already exists for this LPO.', 'id' => (int)$ex[0]['c0d']];

        $pv  = $this->nextPvNo();
        $r   = $db->insertUpdate(
            "INSERT INTO payment_vouchers
                (pv_no, lpo_id, payee_type, payee_id, payee_name, amount, currency,
                 status, prepared_by, prepared_by_name, prepared_on, notes, created_by, edited_by)
             VALUES
                (:pv, :lid, :pty, :pid, :pnm, :amt, :cur,
                 'DRAFT', :uid, :unm, NOW(), :n, :uid, :uid)",
            [
                ':pv'  => $pv,
                ':lid' => $lpo_id,
                ':pty' => (string)$l['payee_type'],
                ':pid' => (int)$l['payee_id'],
                ':pnm' => (string)$l['payee_name'],
                ':amt' => (float)$l['amount'],
                ':cur' => (string)$l['currency'],
                ':uid' => $uid,
                ':unm' => $uname,
                ':n'   => $notes,
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'PV drafted (' . $pv . ').' : 'Save failed.', 'id' => (int)$r['last_id'], 'pv_no' => $pv];
    }

    /** Move DRAFT → SCHEDULED (Accountant or FM). */
    public function schedule(int $pv_id, string $scheduled_on, int $bank_account_id, int $uid, string $uname, string $notes = ''): array
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT c0d, status FROM payment_vouchers WHERE c0d = :id AND deleted = 0", [':id' => $pv_id]);
        $p  = $rs[0] ?? null;
        if (!$p) return ['ok' => false, 'msg' => 'PV not found.'];
        if ($p['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'PV is not DRAFT (status=' . $p['status'] . ').'];
        if (!$scheduled_on) return ['ok' => false, 'msg' => 'A scheduled date is required.'];

        $r = $db->insertUpdate(
            "UPDATE payment_vouchers
                SET status = 'SCHEDULED', scheduled_on = :sd, bank_account_id = :ba,
                    approved_by = :uid, approved_by_name = :unm, approved_on = NOW(),
                    notes = CONCAT_WS('\n', NULLIF(notes,''), :n),
                    edited_by = :uid
              WHERE c0d = :id",
            [':sd' => $scheduled_on, ':ba' => $bank_account_id, ':uid' => $uid, ':unm' => $uname, ':n' => $notes, ':id' => $pv_id]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Scheduled.' : 'Save failed.'];
    }

    /** Record actual payment (SCHEDULED → PAID); creates payments + payment_allocations rows. */
    public function recordPayment(int $pv_id, array $d, int $uid): array
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT * FROM payment_vouchers WHERE c0d = :id AND deleted = 0", [':id' => $pv_id]);
        $p  = $rs[0] ?? null;
        if (!$p) return ['ok' => false, 'msg' => 'PV not found.'];
        if ($p['status'] !== 'SCHEDULED') return ['ok' => false, 'msg' => 'PV is not SCHEDULED (status=' . $p['status'] . ').'];

        $method = (string)($d['payment_method'] ?? 'BANK_TRANSFER');
        $ref    = (string)($d['reference_no'] ?? '');
        $paidOn = !empty($d['payment_date']) ? $d['payment_date'] : date('Y-m-d');
        $bank   = (int)($d['bank_account_id'] ?? $p['bank_account_id']);
        $notes  = (string)($d['notes'] ?? '');

        // Create the payment row
        $payNo = $this->nextPaymentNo();
        $r1 = $db->insertUpdate(
            "INSERT INTO payments
                (payment_no, payment_voucher_id, bank_account_id, amount, currency,
                 payment_method, reference_no, payment_date, received_by, notes, created_by, edited_by)
             VALUES
                (:pn, :pv, :ba, :amt, :cur, :m, :r, :pd, :rb, :n, :uid, :uid)",
            [
                ':pn'  => $payNo,
                ':pv'  => $pv_id,
                ':ba'  => $bank,
                ':amt' => (float)$p['amount'],
                ':cur' => (string)$p['currency'],
                ':m'   => $method,
                ':r'   => $ref,
                ':pd'  => $paidOn,
                ':rb'  => (string)$p['payee_name'],
                ':n'   => $notes,
                ':uid' => $uid,
            ]
        );
        $paymentId = (int)$r1['last_id'];

        // Allocate payment against the LPO
        if ($paymentId > 0) {
            $db->insertUpdate(
                "INSERT INTO payment_allocations (payment_id, lpo_id, amount, allocation_type)
                 VALUES (:pay, :lpo, :amt, 'FULL')",
                [':pay' => $paymentId, ':lpo' => (int)$p['lpo_id'], ':amt' => (float)$p['amount']]
            );
        }

        // Flip PV → PAID
        $db->insertUpdate(
            "UPDATE payment_vouchers
                SET status = 'PAID', paid_on = :pd, reference_no = :r, bank_account_id = :ba, edited_by = :uid
              WHERE c0d = :id",
            [':pd' => $paidOn, ':r' => $ref, ':ba' => $bank, ':uid' => $uid, ':id' => $pv_id]
        );

        // Flip LPO → PAID
        $db->insertUpdate(
            "UPDATE lpos SET cash_out_status = 'PAID', edited_by = :uid WHERE c0d = :id",
            [':uid' => $uid, ':id' => (int)$p['lpo_id']]
        );

        // Auto-post the cash-out journal (idempotent — skips if already posted)
        $jeMsg = '';
        if ($paymentId > 0 && class_exists('JournalPosting')) {
            $jp   = new JournalPosting();
            $jres = $jp->postForPayment($paymentId);
            $jeMsg = $jres['ok'] ? ' · ' . $jres['msg'] : ' · Journal WARNING: ' . ($jres['msg'] ?? 'post failed');
        }

        // Auto-hold retention for Works CR-sourced payments (idempotent per payment)
        if ($paymentId > 0) {
            if (!class_exists('WorksRetention')) require_once ROOT . '/app/models/WorksRetention.php';
            (new WorksRetention())->recordHolding($paymentId);
        }

        return [
            'ok'         => $paymentId > 0,
            'msg'        => $paymentId > 0 ? 'Payment recorded (' . $payNo . '). LPO closed.' . $jeMsg : 'Save failed.',
            'payment_id' => $paymentId,
            'payment_no' => $payNo,
        ];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT pv.*, l.lpo_no, l.lpo_type, l.source_module, l.project_id, l.purpose,
                    p.project_no, p.project_name,
                    b.account_name AS bank_account_name, b.bank_name, b.account_number
               FROM payment_vouchers pv
          LEFT JOIN lpos l           ON l.c0d = pv.lpo_id
          LEFT JOIN c_projects p     ON p.c0d = l.project_id
          LEFT JOIN bank_accounts b  ON b.c0d = pv.bank_account_id
              WHERE pv.c0d = :id AND pv.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function list(string $status = ''): array
    {
        $db     = new DB();
        $params = [];
        $where  = "WHERE pv.deleted = 0";
        if ($status !== '') { $where .= " AND pv.status = :s"; $params[':s'] = $status; }
        return $db->mySelect(
            "SELECT pv.c0d, pv.pv_no, pv.status, pv.amount, pv.currency, pv.scheduled_on, pv.paid_on,
                    pv.payee_name, pv.prepared_by_name, pv.prepared_on, pv.reference_no,
                    l.lpo_no, l.lpo_type, p.project_no
               FROM payment_vouchers pv
          LEFT JOIN lpos l       ON l.c0d = pv.lpo_id
          LEFT JOIN c_projects p ON p.c0d = l.project_id
               $where
              ORDER BY pv.c0d DESC",
            $params
        ) ?: [];
    }

    /** Any PVs (DRAFT/SCHEDULED/PAID) attached to an LPO — for the LPO view. */
    public function forLpo(int $lpo_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, pv_no, status, amount, currency, scheduled_on, paid_on, reference_no,
                    prepared_by_name, approved_by_name
               FROM payment_vouchers WHERE lpo_id = :id AND deleted = 0 ORDER BY c0d DESC",
            [':id' => $lpo_id]
        ) ?: [];
    }
}
