<?php
/**
 * WorksRetention — subcontractor retention pool tied to Works LPO payments.
 *
 * When we pay a supplier LPO on a Works project whose BOQ specifies retention_pct > 0,
 * a `works_retention_holdings` row records how much of that payment is "held back"
 * (a liability we still owe the vendor, releasable at DLP expiry).
 *
 * V1 scope — the holding row is a tracking artefact:
 *   - The `payments` row keeps its gross amount (matches the bank ledger).
 *   - The holding records that a slice of that payment is contractually owed back
 *     as retention. Pool aggregates surface this on the project report.
 *   - Release flips status HELD → RELEASED. Issuing a fresh payment for the released
 *     amount is a separate manual step (out of scope for this slice).
 *
 * Idempotency: `works_retention_holdings.payment_id` is UNIQUE, so recordHolding()
 * for the same payment is a no-op after the first call.
 */
class WorksRetention extends Model
{
    /**
     * Read the contract retention rate + DLP for a project's BOQ. Returns:
     *   ['pct' => float, 'dlp_days' => int]  or  null if no BOQ / not works project.
     */
    public function termsFor(int $projectId): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT retention_pct, dlp_days FROM works_boqs WHERE project_id = :p AND deleted = 0 LIMIT 1",
            [':p' => $projectId]
        );
        if (empty($r)) return null;
        return [
            'pct'      => (float)$r[0]['retention_pct'],
            'dlp_days' => (int)$r[0]['dlp_days'],
        ];
    }

    /**
     * Called from PaymentVouchers::recordPayment right after allocation is inserted.
     * Only writes a holding row when:
     *   - the payment's LPO is stamped source_cr_id > 0 (i.e., Works CR-sourced)
     *   - the project's BOQ has retention_pct > 0
     * Idempotent per payment (unique key).
     */
    public function recordHolding(int $paymentId): array
    {
        if ($paymentId <= 0) return ['ok' => false, 'msg' => 'payment_id required', 'id' => 0];
        $db = new DB();

        // Duplicate guard (idempotent)
        $ex = $db->mySelect("SELECT c0d FROM works_retention_holdings WHERE payment_id = :p", [':p' => $paymentId]);
        if (!empty($ex)) return ['ok' => true, 'msg' => 'already held', 'id' => (int)$ex[0]['c0d']];

        // Resolve payment → LPO → project + CR
        $rows = $db->mySelect(
            "SELECT p.c0d payment_id, p.amount gross_amount, p.payment_date,
                    l.c0d lpo_id, l.project_id, l.source_cr_id
               FROM payments p
               JOIN payment_allocations a ON a.payment_id = p.c0d
               JOIN lpos l ON l.c0d = a.lpo_id AND l.deleted = 0
              WHERE p.c0d = :pid AND p.deleted = 0
              LIMIT 1",
            [':pid' => $paymentId]
        );
        if (empty($rows)) return ['ok' => false, 'msg' => 'payment/LPO not found', 'id' => 0];
        $row = $rows[0];

        if ((int)$row['source_cr_id'] === 0) {
            return ['ok' => true, 'msg' => 'not a Works CR-sourced LPO — skipped', 'id' => 0];
        }
        $terms = $this->termsFor((int)$row['project_id']);
        if (!$terms || $terms['pct'] <= 0) {
            return ['ok' => true, 'msg' => 'project has no retention_pct set — skipped', 'id' => 0];
        }

        $gross    = (float)$row['gross_amount'];
        $ret      = round($gross * $terms['pct'] / 100.0, 2);
        $heldOn   = (string)$row['payment_date'];
        $dlpEnd   = date('Y-m-d', strtotime($heldOn . ' +' . $terms['dlp_days'] . ' days'));

        $fb = $db->insertUpdate(
            "INSERT INTO works_retention_holdings
                (project_id, payment_id, lpo_id, cr_id,
                 gross_amount, retention_pct, retention_amount,
                 held_on, dlp_end_on, status, created_on)
             VALUES (:pj, :pay, :lpo, :cr, :g, :pct, :ra, :hd, :dl, 'HELD', NOW())",
            [
                ':pj'  => (int)$row['project_id'],
                ':pay' => $paymentId,
                ':lpo' => (int)$row['lpo_id'],
                ':cr'  => (int)$row['source_cr_id'],
                ':g'   => $gross,
                ':pct' => $terms['pct'],
                ':ra'  => $ret,
                ':hd'  => $heldOn,
                ':dl'  => $dlpEnd,
            ]
        );
        return ['ok' => true, 'msg' => 'held', 'id' => (int)($fb['last_id'] ?? 0), 'retention_amount' => $ret];
    }

    /** Aggregate view: {total_held, total_released, count_held, count_released, past_dlp_held}. */
    public function poolFor(int $projectId): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT status, COUNT(*) n, COALESCE(SUM(retention_amount),0) s
               FROM works_retention_holdings WHERE project_id = :p GROUP BY status",
            [':p' => $projectId]
        ) ?: [];
        $out = ['total_held' => 0.0, 'total_released' => 0.0, 'count_held' => 0, 'count_released' => 0];
        foreach ($rows as $r) {
            if ($r['status'] === 'HELD')     { $out['total_held']     = (float)$r['s']; $out['count_held']     = (int)$r['n']; }
            if ($r['status'] === 'RELEASED') { $out['total_released'] = (float)$r['s']; $out['count_released'] = (int)$r['n']; }
        }
        // How much of the pool is past DLP (releasable)
        $past = $db->mySelect(
            "SELECT COALESCE(SUM(retention_amount),0) s
               FROM works_retention_holdings
              WHERE project_id = :p AND status = 'HELD' AND dlp_end_on <= CURDATE()",
            [':p' => $projectId]
        );
        $out['past_dlp_held'] = (float)($past[0]['s'] ?? 0);
        return $out;
    }

    /** Every holding for a project, ordered by held_on. */
    public function holdingsFor(int $projectId): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT h.*, l.lpo_no, l.payee_name, p.payment_no
               FROM works_retention_holdings h
               JOIN lpos     l ON l.c0d = h.lpo_id
               JOIN payments p ON p.c0d = h.payment_id
              WHERE h.project_id = :pj
              ORDER BY h.held_on DESC, h.c0d DESC",
            [':pj' => $projectId]
        ) ?: [];
        $today = date('Y-m-d');
        foreach ($rows as &$r) {
            if ($r['status'] === 'RELEASED') { $r['dlp_status'] = 'released'; }
            elseif ($r['dlp_end_on'] <= $today) { $r['dlp_status'] = 'past_dlp'; }
            else { $r['dlp_status'] = 'in_dlp'; }
            $r['days_to_dlp'] = (strtotime($r['dlp_end_on']) - strtotime($today)) / 86400;
        }
        return $rows;
    }

    /**
     * Release a holding — status HELD → RELEASED, stamps who + when.
     * Guard: only past-DLP holdings can be released (contract has to run its course).
     * FM/Admin can force-release via forceUnlock=true (site walk-through signed off early).
     */
    public function release(int $holdingId, int $uid, string $uname, string $notes = '', bool $forceUnlock = false): array
    {
        if ($holdingId <= 0) return ['ok' => false, 'msg' => 'holding_id required'];
        $db = new DB();
        $rs = $db->mySelect("SELECT * FROM works_retention_holdings WHERE c0d = :id", [':id' => $holdingId]);
        if (empty($rs))                            return ['ok' => false, 'msg' => 'holding not found'];
        $h = $rs[0];
        if ($h['status'] === 'RELEASED')           return ['ok' => false, 'msg' => 'already released'];
        if (!$forceUnlock && $h['dlp_end_on'] > date('Y-m-d')) {
            return ['ok' => false, 'msg' => 'DLP has not expired (ends ' . $h['dlp_end_on'] . ') — pass forceUnlock=true to release early'];
        }
        $db->insertUpdate(
            "UPDATE works_retention_holdings
                SET status = 'RELEASED',
                    released_by = :u, released_by_name = :un,
                    released_on = NOW(), released_notes = :n
              WHERE c0d = :id AND status = 'HELD'",
            [':u' => $uid, ':un' => $uname, ':n' => $notes, ':id' => $holdingId]
        );
        return ['ok' => true, 'msg' => 'released'];
    }

    /** Convenience: set retention_pct + dlp_days on a project's BOQ. Called by controller + admin scripts. */
    public function setTerms(int $projectId, float $pct, int $dlpDays): array
    {
        if ($pct < 0 || $pct > 100)  return ['ok' => false, 'msg' => 'retention_pct must be 0–100'];
        if ($dlpDays < 0)             return ['ok' => false, 'msg' => 'dlp_days must be >= 0'];
        $db = new DB();
        $db->insertUpdate(
            "UPDATE works_boqs SET retention_pct = :p, dlp_days = :d WHERE project_id = :pj AND deleted = 0",
            [':p' => $pct, ':d' => $dlpDays, ':pj' => $projectId]
        );
        return ['ok' => true, 'msg' => 'terms updated'];
    }
}
