<?php
/**
 * WorksIpcs — Interim Payment Certificates for the customer side of a Works project.
 *
 * An IPC declares "as of period X, cumulative work on BOQ line Y = Q units", less
 * previously-certified quantities on prior APPROVED IPCs. The delta (this_period_qty)
 * × contract_rate gives this_period_amount. Summed across lines → certified_gross.
 * Less customer-side retention (works_boqs.retention_pct) and any manual advance
 * recovery gives net_amount. VAT is then applied on top for the invoice_total.
 *
 * On approve(), a sales invoice is raised via Sales::draft() for net_amount at
 * source_module='works' — the invoice enters the standard AR pipeline (customer
 * receipts, aging, journal posting) unchanged.
 *
 * Guards:
 *   - Draft: no prior DRAFT/SUBMITTED IPC allowed on the same project (finish or
 *     cancel it first). Prevents concurrent editing races.
 *   - setLineQty: cumulative_qty must not exceed contract_qty (over-certify block).
 *   - approve: only APPROVED IPCs generate the sales invoice; idempotent per IPC.
 */
class WorksIpcs extends Model
{
    /* ── Header CRUD ─────────────────────────────────────────────────── */

    /** Draft a new IPC. Auto-populates lines from the project's BOQ items. */
    public function draft(int $projectId, array $d, int $userId, string $userName): array
    {
        $db = new DB();
        $boq = $db->mySelect("SELECT c0d, retention_pct FROM works_boqs WHERE project_id = :p AND deleted = 0 LIMIT 1", [':p' => $projectId]);
        if (empty($boq)) return ['ok' => false, 'msg' => 'Project has no BOQ — cannot draft IPC.', 'id' => 0];
        $boqId    = (int)$boq[0]['c0d'];
        $retPct   = (float)$boq[0]['retention_pct'];

        // Guard: no in-flight IPC on this project
        $blocking = $db->mySelect(
            "SELECT c0d, ipc_no, status FROM works_ipcs
              WHERE project_id = :p AND status IN ('DRAFT','SUBMITTED') AND deleted = 0",
            [':p' => $projectId]
        );
        if (!empty($blocking)) return ['ok' => false, 'msg' => 'IPC ' . $blocking[0]['ipc_no'] . ' is still ' . $blocking[0]['status'] . ' — finish or cancel it first.', 'id' => 0];

        // Next sequence + number
        $seq = (int)($db->mySelect(
            "SELECT COALESCE(MAX(ipc_seq),0)+1 n FROM works_ipcs WHERE project_id = :p AND deleted = 0",
            [':p' => $projectId]
        )[0]['n'] ?? 1);
        $ipcNo = 'IPC-' . date('Y') . '-P' . str_pad((string)$projectId, 4, '0', STR_PAD_LEFT) . '-' . str_pad((string)$seq, 2, '0', STR_PAD_LEFT);

        $fb = $db->insertUpdate(
            "INSERT INTO works_ipcs
                (project_id, boq_id, ipc_no, ipc_seq, ipc_date, period_from, period_to,
                 status, retention_pct, vat_pct, notes,
                 created_by, created_by_name, created_on)
             VALUES (:p, :b, :no, :seq, :dt, :pf, :pt,
                     'DRAFT', :rp, :vp, :nt, :u, :un, NOW())",
            [
                ':p' => $projectId, ':b' => $boqId, ':no' => $ipcNo, ':seq' => $seq,
                ':dt' => (string)($d['ipc_date']    ?? date('Y-m-d')),
                ':pf' => (string)($d['period_from'] ?? date('Y-m-01')),
                ':pt' => (string)($d['period_to']   ?? date('Y-m-d')),
                ':rp' => $retPct,
                ':vp' => (float)($d['vat_pct'] ?? 18.00),
                ':nt' => (string)($d['notes'] ?? ''),
                ':u'  => $userId, ':un' => $userName,
            ]
        );
        $ipcId = (int)($fb['last_id'] ?? 0);
        if ($ipcId <= 0) return ['ok' => false, 'msg' => 'Save failed on header.', 'id' => 0];

        $this->seedLines($ipcId);
        return ['ok' => true, 'msg' => 'IPC ' . $ipcNo . ' drafted.', 'id' => $ipcId, 'ipc_no' => $ipcNo];
    }

    /**
     * Populate the IPC's lines from BOQ items, with previous_qty pre-computed from
     * prior APPROVED IPCs. Idempotent (unique on ipc_id + boq_item_id).
     */
    public function seedLines(int $ipcId): int
    {
        $ipc = $this->findById($ipcId);
        if (!$ipc) throw new RuntimeException('IPC not found.');
        if ($ipc['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT IPCs can be re-seeded.');
        $projectId = (int)$ipc['project_id'];
        $boqId     = (int)$ipc['boq_id'];

        $db = new DB();
        $items = $db->mySelect(
            "SELECT c0d, item_no, section, description, unit, contract_qty, contract_rate, contract_amount
               FROM works_boq_items WHERE boq_id = :b AND deleted = 0 ORDER BY origin, c0d",
            [':b' => $boqId]
        ) ?: [];
        $n = 0;
        foreach ($items as $it) {
            $prev = $this->cumulativeForBoq($projectId, (int)$it['c0d'], $ipcId);
            // Upsert: skip if line already exists (idempotent seed)
            $ex = $db->mySelect("SELECT c0d FROM works_ipc_lines WHERE ipc_id = :i AND boq_item_id = :b AND deleted = 0", [':i' => $ipcId, ':b' => (int)$it['c0d']]);
            if (!empty($ex)) continue;
            $db->insertUpdate(
                "INSERT INTO works_ipc_lines
                    (ipc_id, boq_item_id, item_no, section, description, unit,
                     contract_qty, contract_rate, contract_amount, previous_qty, cumulative_qty, cumulative_amount, created_on)
                 VALUES (:i, :b, :no, :sec, :d, :u,
                         :cq, :cr, :ca, :pq, :pq, :pa, NOW())",
                [
                    ':i'  => $ipcId, ':b' => (int)$it['c0d'],
                    ':no' => (string)$it['item_no'],
                    ':sec'=> (string)$it['section'],
                    ':d'  => (string)$it['description'],
                    ':u'  => (string)$it['unit'],
                    ':cq' => (float)$it['contract_qty'],
                    ':cr' => (float)$it['contract_rate'],
                    ':ca' => (float)$it['contract_amount'],
                    ':pq' => (float)$prev['qty'],
                    ':pa' => (float)$prev['amount'],
                ]
            );
            $n++;
        }
        return $n;
    }

    /**
     * Cumulative qty + amount for a BOQ item across all APPROVED IPCs
     * (optionally excluding one — used at seed time to compute "previous").
     */
    public function cumulativeForBoq(int $projectId, int $boqItemId, int $excludeIpcId = 0): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT COALESCE(SUM(l.this_period_qty),0)    q,
                    COALESCE(SUM(l.this_period_amount),0) a
               FROM works_ipc_lines l
               JOIN works_ipcs i ON i.c0d = l.ipc_id AND i.deleted = 0
              WHERE l.boq_item_id = :b AND l.deleted = 0
                AND i.project_id = :p
                AND i.status = 'APPROVED'
                AND i.c0d <> :ex",
            [':b' => $boqItemId, ':p' => $projectId, ':ex' => $excludeIpcId]
        );
        return ['qty' => (float)($r[0]['q'] ?? 0), 'amount' => (float)($r[0]['a'] ?? 0)];
    }

    /* ── Line editing (only DRAFT) ───────────────────────────────────── */

    public function setLineQty(int $ipcId, int $lineId, float $thisQty): array
    {
        if ($thisQty < 0) return ['ok' => false, 'msg' => 'this_period_qty must be >= 0'];
        $db = new DB();
        $ipc = $this->findById($ipcId);
        if (!$ipc)                              return ['ok' => false, 'msg' => 'IPC not found.'];
        if ($ipc['status'] !== 'DRAFT')          return ['ok' => false, 'msg' => 'Only DRAFT IPCs are editable (current: ' . $ipc['status'] . ').'];

        $rs = $db->mySelect("SELECT * FROM works_ipc_lines WHERE c0d = :id AND ipc_id = :i AND deleted = 0", [':id' => $lineId, ':i' => $ipcId]);
        if (empty($rs))                         return ['ok' => false, 'msg' => 'IPC line not found.'];
        $line = $rs[0];

        $newCumulative = (float)$line['previous_qty'] + $thisQty;
        if ($newCumulative > (float)$line['contract_qty'] + 0.0001) {
            return ['ok' => false, 'msg' => 'Over-certify: cumulative (' . rtrim(rtrim((string)$newCumulative, '0'), '.') . ') would exceed contract_qty (' . rtrim(rtrim((string)$line['contract_qty'], '0'), '.') . ').'];
        }

        $thisAmt   = round($thisQty * (float)$line['contract_rate'], 2);
        $cumAmt    = round(((float)$line['previous_qty'] + $thisQty) * (float)$line['contract_rate'], 2);

        $db->insertUpdate(
            "UPDATE works_ipc_lines
                SET this_period_qty = :tq, this_period_amount = :ta,
                    cumulative_qty  = :cq, cumulative_amount  = :ca
              WHERE c0d = :id",
            [':tq' => $thisQty, ':ta' => $thisAmt, ':cq' => $newCumulative, ':ca' => $cumAmt, ':id' => $lineId]
        );
        $this->recomputeTotals($ipcId);
        return ['ok' => true, 'msg' => 'saved'];
    }

    /** Set advance_recovery on the IPC header (manual adjustment). */
    public function setAdvanceRecovery(int $ipcId, float $amount): array
    {
        if ($amount < 0) return ['ok' => false, 'msg' => 'advance_recovery must be >= 0'];
        $db = new DB();
        $ipc = $this->findById($ipcId);
        if (!$ipc)                              return ['ok' => false, 'msg' => 'IPC not found.'];
        if ($ipc['status'] !== 'DRAFT')          return ['ok' => false, 'msg' => 'Only DRAFT IPCs are editable.'];
        $db->insertUpdate("UPDATE works_ipcs SET advance_recovery = :a WHERE c0d = :id", [':a' => $amount, ':id' => $ipcId]);
        $this->recomputeTotals($ipcId);
        return ['ok' => true, 'msg' => 'saved'];
    }

    public function recomputeTotals(int $ipcId): void
    {
        $db = new DB();
        $sum = $db->mySelect(
            "SELECT COALESCE(SUM(this_period_amount),0) s FROM works_ipc_lines WHERE ipc_id = :i AND deleted = 0",
            [':i' => $ipcId]
        );
        $gross = (float)($sum[0]['s'] ?? 0);
        $ipc = $this->findById($ipcId);
        $retPct  = (float)$ipc['retention_pct'];
        $adv     = (float)$ipc['advance_recovery'];
        $vatPct  = (float)$ipc['vat_pct'];
        $retAmt  = round($gross * $retPct / 100, 2);
        $net     = round(max(0, $gross - $retAmt - $adv), 2);
        $vatAmt  = round($net * $vatPct / 100, 2);
        $invTot  = round($net + $vatAmt, 2);

        $db->insertUpdate(
            "UPDATE works_ipcs
                SET certified_gross = :g, retention_amount = :ra,
                    net_amount = :na, vat_amount = :va, invoice_total = :it,
                    edited_on = NOW()
              WHERE c0d = :id",
            [':g' => $gross, ':ra' => $retAmt, ':na' => $net, ':va' => $vatAmt, ':it' => $invTot, ':id' => $ipcId]
        );
    }

    /* ── Read helpers ────────────────────────────────────────────────── */

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

    public function linesFor(int $ipcId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM works_ipc_lines WHERE ipc_id = :i AND deleted = 0 ORDER BY c0d",
            [':i' => $ipcId]
        ) ?: [];
    }

    public function listForProject(int $projectId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, ipc_no, ipc_seq, ipc_date, period_from, period_to, status,
                    certified_gross, retention_amount, advance_recovery, net_amount, invoice_total, sale_id
               FROM works_ipcs WHERE project_id = :p AND deleted = 0
               ORDER BY ipc_seq DESC",
            [':p' => $projectId]
        ) ?: [];
    }

    /* ── Approve → raise sales invoice ───────────────────────────────── */

    /**
     * Flip DRAFT → APPROVED and generate a sales invoice for net_amount via Sales::draft.
     * Skips chain for v1; the sales invoice will hit its own GOODS_CUSTOMER_INVOICE chain
     * downstream. Idempotent: if sale_id > 0 already, returns the existing sale.
     */
    public function approve(int $ipcId, int $userId, string $userName, string $userGroup): array
    {
        $canRole = in_array($userGroup, ['Projects Coordinator','Project Manager','Administrator'], true);
        if (!$canRole)                          return ['ok' => false, 'msg' => 'Only PC / PM / Admin can approve an IPC.'];
        $ipc = $this->findById($ipcId);
        if (!$ipc)                              return ['ok' => false, 'msg' => 'IPC not found.'];
        // Idempotency guard first — a prior successful approve stamped sale_id.
        if ((int)$ipc['sale_id'] > 0) {
            return ['ok' => true, 'msg' => 'Already approved · sale #' . $ipc['sale_id'], 'sale_id' => (int)$ipc['sale_id']];
        }
        if (!in_array($ipc['status'], ['DRAFT','SUBMITTED'], true)) {
            return ['ok' => false, 'msg' => 'IPC is ' . $ipc['status'] . ' — cannot approve.'];
        }
        if ((float)$ipc['certified_gross'] <= 0) {
            return ['ok' => false, 'msg' => 'No lines have this_period_qty > 0 — nothing to certify.'];
        }

        $db = new DB();
        $prj = $db->mySelect(
            "SELECT project_no, project_customer, project_customer_id, currency
               FROM c_projects WHERE c0d = :p AND deleted = 0",
            [':p' => (int)$ipc['project_id']]
        );
        if (empty($prj)) return ['ok' => false, 'msg' => 'Project not found.'];
        $custId = (int)$prj[0]['project_customer_id'];
        if ($custId <= 0) return ['ok' => false, 'msg' => 'Project has no customer id — cannot draft sales invoice.'];

        // Build a single sales line describing the IPC. The IPC document itself holds detail.
        $lineDesc = sprintf(
            '%s · certified %s less retention %s%% (%s) less advance recovery %s',
            $ipc['ipc_no'],
            number_format((float)$ipc['certified_gross']),
            number_format((float)$ipc['retention_pct'], 2),
            number_format((float)$ipc['retention_amount']),
            number_format((float)$ipc['advance_recovery'])
        );

        if (!class_exists('Sales')) require_once ROOT . '/app/models/Sales.php';
        $sm = new Sales();
        $sales = $sm->draft([
            'project_id'    => (int)$ipc['project_id'],
            'customer_id'   => $custId,
            'source_module' => 'works',
            'vat_rate'      => (float)$ipc['vat_pct'],
            'narrative'     => 'Interim Payment Certificate ' . $ipc['ipc_no'] . ' (Works project ' . ($prj[0]['project_no'] ?? '') . ')',
            'items'         => [
                ['description' => $lineDesc, 'qty' => 1, 'unit' => 'LS', 'unit_price' => (float)$ipc['net_amount']],
            ],
        ], $userId, $userName);
        if (!$sales['ok'] || (int)$sales['id'] <= 0) {
            return ['ok' => false, 'msg' => 'Sales draft failed: ' . ($sales['msg'] ?? '?')];
        }

        $db->insertUpdate(
            "UPDATE works_ipcs
                SET status = 'APPROVED', sale_id = :s,
                    approved_by = :u, approved_by_name = :un, approved_on = NOW(), edited_on = NOW()
              WHERE c0d = :id",
            [':s' => (int)$sales['id'], ':u' => $userId, ':un' => $userName, ':id' => $ipcId]
        );

        // Slice 8f-IssueOnApprove — auto-submit the freshly-drafted sale into the
        // GOODS_CUSTOMER_INVOICE chain so the receipt gate opens without a manual step.
        // Best-effort: any failure here (chain missing, dup approval, …) leaves the sale
        // in DRAFT and the caller can still walk the chain manually.
        $saleId    = (int)$sales['id'];
        $chainMsg  = '';
        $chainApId = 0;
        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $ac = new ApprovalChain();
        $chainRes = $ac->submit([
            'chain_key'   => 'GOODS_CUSTOMER_INVOICE',
            'entity_type' => 'sales',
            'entity_id'   => $saleId,
            'ref_label'   => 'IPC ' . $ipc['ipc_no'] . ' · sale #' . $saleId . ' · net ' . number_format((float)$ipc['net_amount']),
            'return_url'  => 'Sales/view/' . $saleId,
            'user_id'     => $userId,
            'user_name'   => $userName,
            'user_group'  => $userGroup,
            'comment'     => 'Auto-submitted on IPC approve.',
        ]);
        if (!empty($chainRes['ok'])) {
            $chainApId = (int)($chainRes['approval_id'] ?? 0);
            $sm->markSubmitted($saleId, $chainApId);
            $chainMsg = ' · chain #' . $chainApId . ' submitted';
        } else {
            $chainMsg = ' · sale left DRAFT (chain submit skipped: ' . ($chainRes['msg'] ?? '?') . ')';
        }

        return [
            'ok'          => true,
            'msg'         => 'IPC approved · invoice ' . $saleId . ' drafted' . $chainMsg,
            'sale_id'     => $saleId,
            'approval_id' => $chainApId,
        ];
    }

    /* ── AR-side linkage: receipts against the IPC's sales invoice ────── */

    /**
     * Every receipt allocated to the sale that this IPC generated on approve.
     * Returns [] for DRAFT IPCs (no sale_id yet).
     */
    public function receiptsFor(int $ipcId): array
    {
        $ipc = $this->findById($ipcId);
        if (!$ipc || (int)$ipc['sale_id'] === 0) return [];
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, receipt_no, receipt_type, amount, currency, receipt_date,
                    payment_method, bank_reference
               FROM receipts
              WHERE sale_id = :s AND deleted = 0
              ORDER BY receipt_date DESC, c0d DESC",
            [':s' => (int)$ipc['sale_id']]
        ) ?: [];
    }

    /**
     * AR position for one IPC:
     *   invoiced   = IPC.invoice_total (net + VAT — what customer will pay)
     *   received   = sum of receipts.amount for the IPC's sale
     *   outstanding = invoiced − received (floor at 0)
     *   retained   = IPC.retention_amount (still held by customer, separate liability)
     */
    public function arSummaryFor(int $ipcId): array
    {
        $ipc = $this->findById($ipcId);
        if (!$ipc) return ['invoiced' => 0.0, 'received' => 0.0, 'outstanding' => 0.0, 'retained' => 0.0];
        $invoiced = (float)$ipc['invoice_total'];
        $received = 0.0;
        if ((int)$ipc['sale_id'] > 0) {
            $db = new DB();
            $r = $db->mySelect(
                "SELECT COALESCE(SUM(amount),0) s FROM receipts WHERE sale_id = :s AND deleted = 0",
                [':s' => (int)$ipc['sale_id']]
            );
            $received = (float)($r[0]['s'] ?? 0);
        }
        return [
            'invoiced'    => $invoiced,
            'received'    => $received,
            'outstanding' => max(0, $invoiced - $received),
            'retained'    => (float)$ipc['retention_amount'],
            'sale_id'     => (int)$ipc['sale_id'],
        ];
    }

    /** Project-level receipts against all APPROVED IPCs on the project. */
    public function totalReceivedFor(int $projectId): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT COALESCE(SUM(rc.amount),0) s, COUNT(*) n
               FROM receipts rc
               JOIN works_ipcs i ON i.sale_id = rc.sale_id
              WHERE i.project_id = :p
                AND i.status = 'APPROVED'
                AND i.deleted = 0
                AND rc.deleted = 0",
            [':p' => $projectId]
        );
        return [
            'received' => (float)($r[0]['s'] ?? 0),
            'count'    => (int)  ($r[0]['n'] ?? 0),
        ];
    }

    /* ── Project rollup helper (used by WorksProjectReport later) ───── */

    /** Sum of net_amount across APPROVED IPCs on a project. */
    public function totalCertifiedFor(int $projectId): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT COALESCE(SUM(certified_gross),0)  gross,
                    COALESCE(SUM(retention_amount),0) held,
                    COALESCE(SUM(net_amount),0)       net,
                    COALESCE(SUM(invoice_total),0)    invoiced,
                    COUNT(*)                          n
               FROM works_ipcs WHERE project_id = :p AND status = 'APPROVED' AND deleted = 0",
            [':p' => $projectId]
        );
        $t = $r[0] ?? [];
        return [
            'gross'    => (float)($t['gross']    ?? 0),
            'held'     => (float)($t['held']     ?? 0),   // customer holds back from us
            'net'      => (float)($t['net']      ?? 0),
            'invoiced' => (float)($t['invoiced'] ?? 0),
            'count'    => (int)  ($t['n']        ?? 0),
        ];
    }
}
