<?php
/**
 * WorksCustomerRetention — customer-side retention pool (mirror of WorksRetention).
 *
 * Held pool = SUM(works_ipcs.retention_amount) across APPROVED IPCs on a project — i.e.,
 * what the customer withheld from what we invoiced. Released = SUM of manually-recorded
 * `works_customer_retention_releases` events (typically 50% at practical completion, 50%
 * at DLP end). In-pool = held − released.
 *
 * DLP end is estimated as (last APPROVED IPC date) + BOQ.dlp_days. Real projects will
 * anchor DLP off practical-completion certificates; that's a future refinement.
 *
 * V1 scope: passive tracking. Recording a release is a book-keeping event only — it does
 * NOT create a bank receipt (that comes via the standard AR receipts pipeline). Match
 * `matched_receipt_id` if you want to tie the release event to the actual bank credit.
 */
class WorksCustomerRetention extends Model
{
    /** Contract retention terms (mirrors WorksRetention::termsFor). */
    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'],
        ];
    }

    /** Aggregate: {total_held, total_released, in_pool, count_ipcs, count_releases, dlp_end_estimate, days_to_dlp}. */
    public function poolFor(int $projectId): array
    {
        $db = new DB();
        $held = $db->mySelect(
            "SELECT COALESCE(SUM(retention_amount),0) s, COUNT(*) n, MAX(ipc_date) last_dt
               FROM works_ipcs
              WHERE project_id = :p AND status = 'APPROVED' AND deleted = 0",
            [':p' => $projectId]
        );
        $rel = $db->mySelect(
            "SELECT COALESCE(SUM(amount),0) s, COUNT(*) n
               FROM works_customer_retention_releases
              WHERE project_id = :p AND deleted = 0",
            [':p' => $projectId]
        );
        $totalHeld  = (float)($held[0]['s'] ?? 0);
        $totalRel   = (float)($rel[0]['s'] ?? 0);

        $terms = $this->termsFor($projectId) ?: ['pct' => 0.0, 'dlp_days' => 365];
        $dlpEnd = null; $daysToDlp = null;
        if (!empty($held[0]['last_dt'])) {
            $dlpEnd    = date('Y-m-d', strtotime((string)$held[0]['last_dt'] . ' +' . (int)$terms['dlp_days'] . ' days'));
            $daysToDlp = (int)round((strtotime($dlpEnd) - strtotime(date('Y-m-d'))) / 86400);
        }

        return [
            'total_held'       => $totalHeld,
            'total_released'   => $totalRel,
            'in_pool'          => round($totalHeld - $totalRel, 2),
            'count_ipcs'       => (int)($held[0]['n'] ?? 0),
            'count_releases'   => (int)($rel[0]['n']  ?? 0),
            'last_ipc_date'    => $held[0]['last_dt'] ?? null,
            'dlp_end_estimate' => $dlpEnd,
            'days_to_dlp'      => $daysToDlp,
            'contract_pct'     => (float)$terms['pct'],
            'contract_dlp_days'=> (int)$terms['dlp_days'],
        ];
    }

    /** IPCs with their retention_amount, ordered by seq. Useful for the breakdown table. */
    public function holdingsBreakdown(int $projectId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, ipc_no, ipc_seq, ipc_date, certified_gross, retention_pct,
                    retention_amount, net_amount, invoice_total, sale_id
               FROM works_ipcs
              WHERE project_id = :p AND status = 'APPROVED' AND deleted = 0
              ORDER BY ipc_seq DESC",
            [':p' => $projectId]
        ) ?: [];
    }

    public function releasesFor(int $projectId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, released_on, amount, matched_receipt_id, notes,
                    released_by, released_by_name, created_on
               FROM works_customer_retention_releases
              WHERE project_id = :p AND deleted = 0
              ORDER BY released_on DESC, c0d DESC",
            [':p' => $projectId]
        ) ?: [];
    }

    /**
     * Record a release event. Guards:
     *   - amount > 0
     *   - amount ≤ in_pool (can't release more than held − already-released)
     */
    public function recordRelease(int $projectId, float $amount, string $releasedOn, string $notes, int $matchedReceiptId, int $uid, string $uname): array
    {
        if ($projectId <= 0) return ['ok' => false, 'msg' => 'project_id required.'];
        if ($amount <= 0)    return ['ok' => false, 'msg' => 'amount must be > 0.'];
        if ($releasedOn === '') $releasedOn = date('Y-m-d');

        $pool = $this->poolFor($projectId);
        if ($amount > (float)$pool['in_pool'] + 0.01) {
            return ['ok' => false, 'msg' => 'Amount exceeds in-pool balance ' . number_format((float)$pool['in_pool']) . '.'];
        }

        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO works_customer_retention_releases
                (project_id, released_on, amount, matched_receipt_id, notes,
                 released_by, released_by_name, created_on)
             VALUES (:p, :d, :a, :r, :n, :u, :un, NOW())",
            [
                ':p' => $projectId,
                ':d' => $releasedOn,
                ':a' => $amount,
                ':r' => $matchedReceiptId,
                ':n' => $notes,
                ':u' => $uid, ':un' => $uname,
            ]
        );
        return ['ok' => true, 'msg' => 'Release recorded.', 'id' => (int)($fb['last_id'] ?? 0)];
    }

    /** Soft-delete a release event (in case of mis-entry). Admin/FM only at controller. */
    public function deleteRelease(int $releaseId, int $uid): array
    {
        if ($releaseId <= 0) return ['ok' => false, 'msg' => 'release_id required.'];
        $db = new DB();
        $r = $db->mySelect("SELECT c0d FROM works_customer_retention_releases WHERE c0d = :id AND deleted = 0", [':id' => $releaseId]);
        if (empty($r)) return ['ok' => false, 'msg' => 'Release not found.'];
        $db->insertUpdate(
            "UPDATE works_customer_retention_releases SET deleted = 1 WHERE c0d = :id",
            [':id' => $releaseId]
        );
        return ['ok' => true, 'msg' => 'Release removed.'];
    }
}
