<?php
/**
 * LpoPaymentTerms — Slice 8Z-r · Slice D1
 *
 * Structured payment terms per SUPPLIER LPO. Each row is a milestone (advance, on-delivery,
 * retention, net-days, etc.) with a percent OR fixed amount. Denorm `computed_amount`
 * = percent × lpo.amount OR fixed_amount, refreshed whenever a term changes or the LPO
 * amount changes.
 *
 * Slice D2 will read `claimed_amount` here as PRs get raised — the claimable-now math
 * is (computed − claimed) minus any trigger gating (GRN threshold / net-days / retention).
 *
 * Only editable while LPO is DRAFT / SUBMITTED / APPROVED (before Issue). Locks on ISSUED.
 */
class LpoPaymentTerms extends Model
{
    public const TRIGGERS = [
        'ADVANCE'     => 'Advance (before issue)',
        'ON_ORDER'    => 'On order (at issue)',
        'ON_DELIVERY' => 'On delivery',
        'RETENTION'   => 'Retention',
        'NET_DAYS'    => 'Net days after invoice',
        'MILESTONE'   => 'Milestone (custom)',
    ];

    public function forLpo(int $lpoId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM lpo_payment_terms WHERE lpo_id = :l ORDER BY seq_no, c0d",
            [':l' => $lpoId]
        ) ?: [];
    }

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

    /** Add a term. Guards LPO status < ISSUED. */
    public function add(int $lpoId, array $d, int $userId, string $userName): int
    {
        $this->assertEditable($lpoId);
        $type = $this->normaliseTrigger((string)($d['trigger_type'] ?? 'ON_DELIVERY'));
        $label = trim((string)($d['label'] ?? '')) ?: self::TRIGGERS[$type];
        [$pct, $fixed] = $this->normalisePctOrFixed($d);

        $db = new DB();
        $nextSeq = ((int)($db->mySelect("SELECT COALESCE(MAX(seq_no),0) n FROM lpo_payment_terms WHERE lpo_id=:l", [':l'=>$lpoId])[0]['n'] ?? 0)) + 1;
        $fb = $db->insertUpdate(
            "INSERT INTO lpo_payment_terms
                (lpo_id, seq_no, label, trigger_type, percent, fixed_amount, computed_amount,
                 net_days, retention_days, milestone_condition, grn_percent_gate, notes,
                 created_by, created_by_name)
             VALUES
                (:l, :sq, :lb, :t, :p, :fx, 0,
                 :nd, :rd, :mc, :gp, :nt,
                 :u, :un)",
            [
                ':l'=>$lpoId, ':sq'=>$nextSeq, ':lb'=>$label, ':t'=>$type,
                ':p'=>$pct, ':fx'=>$fixed,
                ':nd'=>$type === 'NET_DAYS'    ? (int)($d['net_days']       ?? 30) : null,
                ':rd'=>$type === 'RETENTION'   ? (int)($d['retention_days'] ?? 90) : null,
                ':mc'=>$type === 'MILESTONE'   ? trim((string)($d['milestone_condition'] ?? '')) : null,
                ':gp'=>$type === 'ON_DELIVERY' ? (float)($d['grn_percent_gate'] ?? 100) : null,
                ':nt'=>trim((string)($d['notes'] ?? '')) ?: null,
                ':u'=>$userId, ':un'=>$userName,
            ]
        );
        $termId = (int)($fb['last_id'] ?? 0);
        $this->recomputeAll($lpoId);
        return $termId;
    }

    public function update(int $termId, array $d): void
    {
        $t = $this->find($termId);
        if (!$t) throw new RuntimeException('Term not found.');
        $this->assertEditable((int)$t['lpo_id']);
        $type = $this->normaliseTrigger((string)($d['trigger_type'] ?? $t['trigger_type']));
        $label = trim((string)($d['label'] ?? $t['label'])) ?: self::TRIGGERS[$type];
        [$pct, $fixed] = $this->normalisePctOrFixed(array_merge((array)$t, $d));
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpo_payment_terms SET
                label = :lb, trigger_type = :t, percent = :p, fixed_amount = :fx,
                net_days = :nd, retention_days = :rd, milestone_condition = :mc,
                grn_percent_gate = :gp, notes = :nt, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':lb'=>$label, ':t'=>$type, ':p'=>$pct, ':fx'=>$fixed,
                ':nd'=>$type === 'NET_DAYS'    ? (int)($d['net_days']       ?? ($t['net_days'] ?: 30))       : null,
                ':rd'=>$type === 'RETENTION'   ? (int)($d['retention_days'] ?? ($t['retention_days'] ?: 90)) : null,
                ':mc'=>$type === 'MILESTONE'   ? trim((string)($d['milestone_condition'] ?? $t['milestone_condition'])) : null,
                ':gp'=>$type === 'ON_DELIVERY' ? (float)($d['grn_percent_gate'] ?? ($t['grn_percent_gate'] ?: 100)) : null,
                ':nt'=>isset($d['notes']) ? (trim((string)$d['notes']) ?: null) : $t['notes'],
                ':id'=>$termId,
            ]
        );
        $this->recomputeAll((int)$t['lpo_id']);
    }

    public function remove(int $termId): void
    {
        $t = $this->find($termId);
        if (!$t) return;
        $this->assertEditable((int)$t['lpo_id']);
        if ((float)$t['claimed_amount'] > 0) {
            throw new RuntimeException('Cannot remove — a Payment Request has already been raised against this term.');
        }
        $db = new DB();
        $db->insertUpdate("DELETE FROM lpo_payment_terms WHERE c0d = :id", [':id' => $termId]);
        $this->recomputeAll((int)$t['lpo_id']);
    }

    /**
     * Recompute every term's `computed_amount` off current LPO amount + refresh
     * `lpos.terms_summary` denorm string.
     */
    public function recomputeAll(int $lpoId): void
    {
        $db = new DB();
        $lpo = $db->mySelect("SELECT amount FROM lpos WHERE c0d = :id", [':id' => $lpoId]);
        if (empty($lpo)) return;
        $amt   = (float)$lpo[0]['amount'];
        $terms = $this->forLpo($lpoId);
        foreach ($terms as $t) {
            $computed = $t['percent'] !== null
                ? round($amt * ((float)$t['percent'] / 100), 2)
                : (float)($t['fixed_amount'] ?? 0);
            $db->insertUpdate(
                "UPDATE lpo_payment_terms SET computed_amount = :c WHERE c0d = :id",
                [':c' => $computed, ':id' => (int)$t['c0d']]
            );
        }
        // Denorm human summary onto lpos row
        $summary = $this->summaryString($lpoId);
        $db->insertUpdate("UPDATE lpos SET terms_summary = :s WHERE c0d = :id", [':s' => $summary ?: null, ':id' => $lpoId]);
    }

    /** Human-readable summary — e.g. "30% Advance · 70% On Delivery · net 30d". */
    public function summaryString(int $lpoId): string
    {
        $terms = $this->forLpo($lpoId);
        if (empty($terms)) return '';
        $parts = [];
        foreach ($terms as $t) {
            $piece = '';
            if ($t['percent'] !== null && (float)$t['percent'] > 0) $piece .= number_format((float)$t['percent'], (fmod((float)$t['percent'],1)==0?0:1)) . '%';
            elseif ((float)$t['fixed_amount'] > 0)                  $piece .= number_format((float)$t['fixed_amount']);
            $piece .= ' ' . $this->humanTrigger((string)$t['trigger_type'], $t);
            $parts[] = trim($piece);
        }
        return implode(' · ', $parts);
    }

    /** Sum of all computed_amount rows on this LPO — used to warn on under/over-coverage. */
    public function totalCovered(int $lpoId): float
    {
        $db = new DB();
        $r  = $db->mySelect("SELECT COALESCE(SUM(computed_amount),0) s FROM lpo_payment_terms WHERE lpo_id = :l", [':l' => $lpoId]);
        return (float)($r[0]['s'] ?? 0);
    }

    /**
     * Slice 8Z-s · Slice D2 — per-term availability calc.
     *
     * Returns each term with `remaining` (computed − claimed), `blocked_reason` (null when claimable
     * now), and `claimable_now`. Gating rules:
     *   ADVANCE  — LPO must be APPROVED or later (advance can be paid before issue)
     *   ON_ORDER — LPO must be ISSUED
     *   ON_DELIVERY — GRN weighted qty% must reach grn_percent_gate (default 100)
     *   RETENTION  — LPO fully received AND retention_days elapsed since last GRN
     *   NET_DAYS   — supplier_invoice_date + net_days <= today (invoice ref required on PR)
     *   MILESTONE  — always claimable manually (Procurement chooses when)
     */
    public function availabilityForLpo(int $lpoId): array
    {
        $db  = new DB();
        $lpo = $db->mySelect(
            "SELECT c0d, amount, cash_out_status, receipt_status, issued_at
               FROM lpos WHERE c0d = :l AND deleted = 0",
            [':l' => $lpoId]
        );
        if (empty($lpo)) return [];
        $lpo = $lpo[0];
        $terms = $this->forLpo($lpoId);
        if (empty($terms)) return [];

        // GRN weighted qty% (item-value-weighted) — captures whether the LPO is at least X% delivered.
        $grnPct = $this->grnWeightedPct($lpoId);
        // Last GRN date (for retention timer).
        $lastGrn = $db->mySelect("SELECT MAX(received_on) g FROM goods_grn WHERE lpo_id = :l AND deleted = 0", [':l' => $lpoId]);
        $lastGrnOn = (string)($lastGrn[0]['g'] ?? '');

        $out = [];
        foreach ($terms as $t) {
            $computed = (float)$t['computed_amount'];
            $claimed  = (float)$t['claimed_amount'];
            $remaining = round($computed - $claimed, 2);
            $blockedReason = null;
            $claimable = 0.0;

            switch ((string)$t['trigger_type']) {
                case 'ADVANCE':
                    if (!in_array($lpo['cash_out_status'], ['APPROVED','ISSUED','PAID','RETIRED','CLOSED'], true)) {
                        $blockedReason = 'LPO not yet approved.';
                    }
                    break;
                case 'ON_ORDER':
                    if ((string)$lpo['cash_out_status'] === 'APPROVED' || empty($lpo['issued_at'])) {
                        $blockedReason = 'LPO not yet issued to supplier.';
                    }
                    break;
                case 'ON_DELIVERY':
                    $gate = (float)($t['grn_percent_gate'] ?? 100);
                    if ($grnPct + 0.01 < $gate) {
                        $blockedReason = 'Awaiting GRN — currently ' . number_format($grnPct, 1) . '% delivered, gate ' . number_format($gate, 0) . '%.';
                    }
                    break;
                case 'RETENTION':
                    if ((string)$lpo['receipt_status'] !== 'FULLY_RECEIVED') {
                        $blockedReason = 'LPO not fully received.';
                    } elseif (!empty($t['retention_days'])) {
                        $days = (int)$t['retention_days'];
                        $lastGrnTs = $lastGrnOn !== '' ? strtotime($lastGrnOn) : 0;
                        $eligibleTs = $lastGrnTs + ($days * 86400);
                        if (time() < $eligibleTs) {
                            $daysLeft = (int)ceil(($eligibleTs - time()) / 86400);
                            $blockedReason = 'Retention hold — ' . $daysLeft . 'd left of ' . $days . 'd.';
                        }
                    }
                    break;
                case 'NET_DAYS':
                    // Availability depends on supplier invoice attached to the PR; treat as manual.
                    // The raise dialog will require invoice date; PR raise validates net_days here.
                    $blockedReason = 'Requires supplier invoice — enter on the PR to unlock net-day check.';
                    break;
                case 'MILESTONE':
                    // Always manually claimable when Procurement confirms the milestone happened.
                    break;
            }

            if ($blockedReason === null && $remaining > 0.005) {
                $claimable = $remaining;
            }

            $out[] = array_merge($t, [
                'remaining'      => $remaining,
                'blocked_reason' => $blockedReason,
                'claimable_now'  => $claimable,
                'grn_percent'    => $grnPct,
                'last_grn_on'    => $lastGrnOn,
            ]);
        }
        return $out;
    }

    /**
     * Item-value-weighted GRN percent for the LPO.
     * (sum(qty_received × unit_price)) / (sum(qty × unit_price)) × 100
     */
    private function grnWeightedPct(int $lpoId): float
    {
        $db = new DB();
        $r  = $db->mySelect(
            "SELECT
                COALESCE(SUM(qty * unit_price),0)          AS ordered_val,
                COALESCE(SUM(qty_received * unit_price),0) AS received_val
              FROM lpo_items WHERE lpo_id = :l",
            [':l' => $lpoId]
        );
        $ord = (float)($r[0]['ordered_val'] ?? 0);
        if ($ord <= 0) return 0.0;
        $rec = (float)($r[0]['received_val'] ?? 0);
        return round(($rec / $ord) * 100, 2);
    }

    /** Increment claimed_amount on a term (called when a PR is raised). */
    public function bumpClaimed(int $termId, float $delta): void
    {
        if ($delta === 0.0) return;
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpo_payment_terms SET claimed_amount = claimed_amount + :d, edited_on = NOW() WHERE c0d = :id",
            [':d' => $delta, ':id' => $termId]
        );
    }

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

    private function assertEditable(int $lpoId): void
    {
        $db = new DB();
        $r  = $db->mySelect("SELECT cash_out_status FROM lpos WHERE c0d = :id", [':id' => $lpoId]);
        if (empty($r)) throw new RuntimeException('LPO not found.');
        $lockedStates = ['ISSUED','PAID','RETIRED','CLOSED','DECLINED'];
        if (in_array((string)$r[0]['cash_out_status'], $lockedStates, true)) {
            throw new RuntimeException('Payment terms are locked once the LPO is ' . $r[0]['cash_out_status'] . '.');
        }
    }

    private function normaliseTrigger(string $t): string
    {
        $t = strtoupper(trim($t));
        return array_key_exists($t, self::TRIGGERS) ? $t : 'ON_DELIVERY';
    }

    private function normalisePctOrFixed(array $d): array
    {
        $pct   = isset($d['percent']) && $d['percent'] !== '' && $d['percent'] !== null ? (float)$d['percent'] : null;
        $fixed = isset($d['fixed_amount']) && $d['fixed_amount'] !== '' && $d['fixed_amount'] !== null ? (float)$d['fixed_amount'] : null;
        if ($pct !== null && $fixed !== null && $fixed > 0 && $pct > 0) {
            // Prefer percent when both supplied.
            $fixed = null;
        }
        if (($pct === null || $pct <= 0) && ($fixed === null || $fixed <= 0)) {
            throw new InvalidArgumentException('Enter either a percent or a fixed amount (> 0).');
        }
        if ($pct !== null && ($pct <= 0 || $pct > 100)) {
            throw new InvalidArgumentException('Percent must be between 0 and 100.');
        }
        return [$pct, $fixed];
    }

    private function humanTrigger(string $type, array $t): string
    {
        switch ($type) {
            case 'ADVANCE':     return 'Advance';
            case 'ON_ORDER':    return 'On order';
            case 'ON_DELIVERY':
                $gp = (float)($t['grn_percent_gate'] ?? 100);
                return 'On delivery' . ($gp > 0 && $gp < 100 ? ' (' . number_format($gp,0) . '%)' : '');
            case 'RETENTION':   return 'Retention' . (!empty($t['retention_days']) ? ' ' . (int)$t['retention_days'] . 'd' : '');
            case 'NET_DAYS':    return 'Net ' . (int)($t['net_days'] ?: 30) . 'd';
            case 'MILESTONE':   return 'Milestone';
            default:            return 'On delivery';
        }
    }
}
