<?php
class Sales extends Model
{
    /** Internal handle used while an invoice is still DRAFT (before sale_no is allocated). */
    public function nextInvoiceRef(): string
    {
        $db   = new DB();
        $year = (int) date('Y');
        $rs   = $db->mySelect(
            "SELECT invoice_ref FROM sales WHERE invoice_ref LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'INV-DRAFT-' . $year . '-%']
        );
        $seq = (!empty($rs) && preg_match('/-(\d+)$/', $rs[0]['invoice_ref'], $m)) ? (int)$m[1] + 1 : 1;
        return sprintf('INV-DRAFT-%d-%04d', $year, $seq);
    }

    /** Formal sale_no — allocated ONLY on Accountant Ack (chain terminal). Slice 8i central. */
    public function nextSaleNo(): string
    {
        if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
        return (new DocNumberSettings())->allocate('SALE');
    }

    /**
     * Create a DRAFT invoice against a project + customer with N line items.
     * $d = [
     *   'project_id' => int, 'customer_id' => int,
     *   'invoice_date' => 'YYYY-MM-DD', 'due_date' => 'YYYY-MM-DD',
     *   'currency' => 'TZS', 'vat_rate' => 18,
     *   'narrative' => '...',
     *   'items' => [ ['description'=>'...', 'qty'=>1, 'unit'=>'ea', 'unit_price'=>...] , ... ]
     * ]
     */
    public function draft(array $d, int $uid, string $uname): array
    {
        $db     = new DB();
        $items  = $d['items'] ?? [];
        if (empty($items)) return ['ok' => false, 'msg' => 'Add at least one line item.', 'id' => 0];

        $pid = (int)($d['project_id'] ?? 0);
        $cid = (int)($d['customer_id'] ?? 0);
        if ($pid <= 0 || $cid <= 0) return ['ok' => false, 'msg' => 'Project and customer are required.', 'id' => 0];

        // Ops gate — the project must be onboarded or migrated ------------
        $prj = $db->mySelect(
            "SELECT project_no, crm_status, migrated_flag, currency, project_customer, project_type
               FROM c_projects WHERE c0d = :id AND deleted = 0",
            [':id' => $pid]
        );
        if (empty($prj)) return ['ok' => false, 'msg' => 'Project not found.', 'id' => 0];
        $p = $prj[0];
        if ($p['crm_status'] !== 'APPROVED' && (int)$p['migrated_flag'] !== 1) {
            return ['ok' => false, 'msg' => 'Project has not passed CRM onboarding — cannot draft invoice.', 'id' => 0];
        }

        // Customer -------------------------------------------------------
        $cust = $db->mySelect("SELECT fname, lname FROM customers WHERE c0d = :id AND deleted = 0", [':id' => $cid]);
        $custNm = !empty($cust) ? trim(($cust[0]['fname'] ?? '') . ' ' . ($cust[0]['lname'] ?? '')) : ($p['project_customer'] ?? '');

        // Totals ---------------------------------------------------------
        $vatRate  = (float)($d['vat_rate'] ?? 18);
        $subtotal = 0.0;
        $cleanItems = [];
        foreach ($items as $it) {
            $qty  = (float)($it['qty']        ?? 0);
            $upr  = (float)($it['unit_price'] ?? 0);
            $desc = trim((string)($it['description'] ?? ''));
            if ($qty <= 0 || $upr < 0 || $desc === '') continue; // silently skip malformed
            $lt = round($qty * $upr, 2);
            $subtotal += $lt;
            $cleanItems[] = ['description' => $desc, 'qty' => $qty, 'unit' => (string)($it['unit'] ?? ''), 'unit_price' => $upr, 'line_total' => $lt];
        }
        if (empty($cleanItems)) return ['ok' => false, 'msg' => 'No valid line items.', 'id' => 0];

        $vatAmt = round($subtotal * $vatRate / 100, 2);
        $total  = round($subtotal + $vatAmt, 2);

        $ref = $this->nextInvoiceRef();
        $srcModule = (string)($d['source_module'] ?? ($p['project_type'] ?: 'goods'));
        if (!in_array($srcModule, ['goods','services','works'], true)) $srcModule = 'goods';

        $r = $db->insertUpdate(
            "INSERT INTO sales
                (invoice_ref, project_id, client_po_id, customer_id, customer_name, source_module,
                 invoice_date, due_date, currency, subtotal, vat_rate, vat_amount, total,
                 invoice_workflow_status, narrative, drafted_by, drafted_by_name, drafted_on,
                 created_by, edited_by)
             VALUES
                (:ir, :pid, :cpo, :cid, :cnm, :src,
                 :id, :dd, :cur, :sub, :vr, :vat, :tot,
                 'DRAFT', :n, :uid, :unm, NOW(),
                 :uid, :uid)",
            [
                ':ir'  => $ref,
                ':pid' => $pid,
                ':cpo' => (int)($d['client_po_id'] ?? 0),
                ':cid' => $cid,
                ':cnm' => $custNm,
                ':src' => $srcModule,
                ':id'  => (string)($d['invoice_date'] ?? date('Y-m-d')),
                ':dd'  => !empty($d['due_date']) ? $d['due_date'] : null,
                ':cur' => (string)($d['currency'] ?? ($p['currency'] ?: 'TZS')),
                ':sub' => $subtotal,
                ':vr'  => $vatRate,
                ':vat' => $vatAmt,
                ':tot' => $total,
                ':n'   => (string)($d['narrative'] ?? ''),
                ':uid' => $uid,
                ':unm' => $uname,
            ]
        );
        $sid = (int)$r['last_id'];
        if ($sid <= 0) return ['ok' => false, 'msg' => 'Save failed.', 'id' => 0];

        $n = 0;
        foreach ($cleanItems as $it) {
            $n++;
            $db->insertUpdate(
                "INSERT INTO sales_items (sale_id, item_no, description, qty, unit, unit_price, line_total)
                 VALUES (:sid, :n, :d, :q, :u, :up, :lt)",
                [
                    ':sid' => $sid, ':n' => $n,
                    ':d' => $it['description'], ':q' => $it['qty'], ':u' => $it['unit'],
                    ':up' => $it['unit_price'], ':lt' => $it['line_total'],
                ]
            );
        }
        return ['ok' => true, 'msg' => 'Invoice drafted (' . $ref . ').', 'id' => $sid, 'invoice_ref' => $ref];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT s.*, p.project_no, p.project_name, c.fname, c.lname, c.email, c.customer_tin
               FROM sales s
          LEFT JOIN c_projects p ON p.c0d = s.project_id
          LEFT JOIN customers  c ON c.c0d = s.customer_id
              WHERE s.c0d = :id AND s.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function itemsFor(int $sale_id): array
    {
        $db = new DB();
        return $db->mySelect("SELECT * FROM sales_items WHERE sale_id = :id ORDER BY item_no ASC", [':id' => $sale_id]) ?: [];
    }

    public function list(string $status = '', int $projectId = 0): array
    {
        $db     = new DB();
        $where  = "WHERE s.deleted = 0";
        $params = [];
        if ($status !== '')  { $where .= " AND s.invoice_workflow_status = :st"; $params[':st'] = $status; }
        if ($projectId > 0)  { $where .= " AND s.project_id = :p";               $params[':p']  = $projectId; }
        return $db->mySelect(
            "SELECT s.c0d, s.sale_no, s.invoice_ref, s.invoice_date, s.customer_name, s.currency,
                    s.subtotal, s.vat_amount, s.total, s.invoice_workflow_status, s.source_module,
                    s.approval_id, s.journal_entry_id, s.drafted_by_name,
                    p.project_no, p.project_name
               FROM sales s
          LEFT JOIN c_projects p ON p.c0d = s.project_id
               $where
              ORDER BY s.c0d DESC",
            $params
        ) ?: [];
    }

    public function markSubmitted(int $sale_id, int $approval_id): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE sales SET approval_id = :aid, invoice_workflow_status = 'SUBMITTED', edited_on = NOW() WHERE c0d = :id",
            [':aid' => $approval_id, ':id' => $sale_id]
        );
    }

    /** Called on Accountant Ack terminal: allocate formal sale_no + flip status. */
    public function allocateAndAck(int $sale_id, int $uid, string $uname, int $je_id): array
    {
        $db  = new DB();
        $sno = $this->nextSaleNo();
        $r = $db->insertUpdate(
            "UPDATE sales
                SET sale_no = :sn,
                    invoice_workflow_status = 'ISSUED',
                    journal_entry_id = :je,
                    acknowledged_by = :uid,
                    acknowledged_by_name = :unm,
                    acknowledged_on = NOW(),
                    edited_by = :uid
              WHERE c0d = :id AND (sale_no IS NULL OR sale_no = '')",
            [':sn' => $sno, ':je' => $je_id, ':uid' => $uid, ':unm' => $uname, ':id' => $sale_id]
        );
        // Slice 8Z-x · TZ enrichment: snapshot TIN/VRN/address + set due_date + compute outstanding
        $sale = $this->byId($sale_id);
        if (!empty($sale)) {
            $cust = $db->mySelect("SELECT customer_tin, customer_vrn, physical_address FROM customers WHERE c0d = :id", [':id' => (int)$sale['customer_id']]);
            $tin  = trim((string)($cust[0]['customer_tin'] ?? ''));
            $vrn  = trim((string)($cust[0]['customer_vrn'] ?? ''));
            $addr = trim((string)($cust[0]['physical_address'] ?? ''));
            $terms = (int)($sale['payment_terms_days'] ?: 30);
            $dueDate = date('Y-m-d', strtotime('+' . $terms . ' days'));
            $db->insertUpdate(
                "UPDATE sales SET
                    customer_tin_snapshot = COALESCE(customer_tin_snapshot, :tin),
                    customer_vrn_snapshot = COALESCE(customer_vrn_snapshot, :vrn),
                    customer_address_snapshot = COALESCE(customer_address_snapshot, :addr),
                    payment_terms_days = COALESCE(payment_terms_days, :d),
                    due_date = COALESCE(due_date, :due),
                    issued_at = COALESCE(issued_at, NOW()),
                    issued_by = COALESCE(issued_by, :uid),
                    issued_by_name = COALESCE(issued_by_name, :unm)
                  WHERE c0d = :id",
                [':tin'=>$tin ?: null, ':vrn'=>$vrn ?: null, ':addr'=>$addr ?: null,
                 ':d'=>$terms, ':due'=>$dueDate, ':uid'=>$uid, ':unm'=>$uname, ':id'=>$sale_id]
            );
            $this->recomputeOutstanding($sale_id);
        }
        return ['ok' => $r['success'] > 0, 'sale_no' => $sno];
    }

    /**
     * Slice 8Z-x · Recompute outstanding_amount + days_overdue on a sale.
     * outstanding = total - amount_paid - wht_settled_amount
     * Flips workflow status intelligently:
     *   outstanding = 0 & on-time → PAID
     *   outstanding = 0 & late    → PAID_LATE
     *   outstanding > 0 & past due → OVERDUE
     *   outstanding > 0 & any receipts → PART_PAID
     */
    public function recomputeOutstanding(int $sale_id): void
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT total, amount_paid, wht_settled_amount, due_date, invoice_workflow_status
               FROM sales WHERE c0d = :id AND deleted = 0",
            [':id' => $sale_id]
        );
        if (empty($rs)) return;
        $row = $rs[0];
        $total     = (float)$row['total'];
        $paid      = (float)$row['amount_paid'];
        $wht       = (float)$row['wht_settled_amount'];
        $outstanding = round(max(0.0, $total - $paid - $wht), 2);
        $today     = strtotime(date('Y-m-d'));
        $due       = !empty($row['due_date']) && $row['due_date'] !== '0000-00-00' ? strtotime($row['due_date']) : 0;
        $daysOver  = ($due > 0) ? (int)floor(($today - $due) / 86400) : 0;

        $newStatus = $row['invoice_workflow_status'];
        if ($outstanding < 0.005) {
            $newStatus = ($daysOver > 0) ? 'PAID_LATE' : 'PAID';
        } elseif ($paid + $wht > 0.005) {
            $newStatus = ($daysOver > 0) ? 'OVERDUE' : 'PART_PAID';
        } elseif ($daysOver > 0 && in_array($newStatus, ['ISSUED','PART_PAID'], true)) {
            $newStatus = 'OVERDUE';
        }
        $db->insertUpdate(
            "UPDATE sales SET outstanding_amount = :o, days_overdue = :d, invoice_workflow_status = :st, edited_on = NOW()
              WHERE c0d = :id",
            [':o'=>$outstanding, ':d'=>$daysOver, ':st'=>$newStatus, ':id'=>$sale_id]
        );
    }

    /**
     * Slice 8Z-x · Apply a customer receipt allocation to this sale.
     * Increments amount_paid + wht_settled_amount, refreshes status.
     */
    public function applyReceipt(int $sale_id, float $amount, float $wht, int $receiptId, int $uid, string $uname, string $notes = ''): void
    {
        if ($amount <= 0 && $wht <= 0) return;
        $db = new DB();
        $db->insertUpdate(
            "INSERT INTO invoice_receipt_allocations (sale_id, receipt_id, allocated_amount, wht_amount_of_allocation, allocated_by, allocated_by_name, notes)
             VALUES (:s, :r, :a, :w, :u, :un, :nt)",
            [':s'=>$sale_id, ':r'=>$receiptId, ':a'=>$amount, ':w'=>$wht,
             ':u'=>$uid, ':un'=>$uname, ':nt'=>trim($notes) ?: null]
        );
        $db->insertUpdate(
            "UPDATE sales SET amount_paid = amount_paid + :a, wht_settled_amount = wht_settled_amount + :w, edited_on = NOW()
              WHERE c0d = :id",
            [':a'=>$amount, ':w'=>$wht, ':id'=>$sale_id]
        );
        $this->recomputeOutstanding($sale_id);
    }

    /**
     * Slice 8Z-x · PC gate: is there a valid PoD (signed DN) that authorises invoice drafting?
     * Returns [ok, dn_id, dn_no, reason].
     */
    public function podGate(int $client_po_id): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT c0d, dn_no, delivered_to, delivered_to_signature, condition_ok, status
               FROM goods_customer_deliveries
              WHERE client_po_id = :p AND deleted = 0
              ORDER BY c0d DESC LIMIT 1",
            [':p' => $client_po_id]
        );
        if (empty($r))                                   return ['ok'=>false,'reason'=>'No delivery note yet — record a DN with client signature first.'];
        if (empty($r[0]['delivered_to_signature']))      return ['ok'=>false,'reason'=>'DN ' . $r[0]['dn_no'] . ' has no client signature yet.'];
        if ((int)$r[0]['condition_ok'] !== 1)            return ['ok'=>false,'reason'=>'DN ' . $r[0]['dn_no'] . ' has condition_ok=0 — resolve dispute first.'];
        return ['ok'=>true, 'dn_id'=>(int)$r[0]['c0d'], 'dn_no'=>$r[0]['dn_no']];
    }

    /** Slice 8Z-x · Aged debtors buckets for FM/Exec dashboard. */
    public function agedBuckets(): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                SUM(CASE WHEN days_overdue <= 0 THEN outstanding_amount ELSE 0 END) AS current_bucket,
                SUM(CASE WHEN days_overdue BETWEEN 1  AND 30 THEN outstanding_amount ELSE 0 END) AS b_1_30,
                SUM(CASE WHEN days_overdue BETWEEN 31 AND 60 THEN outstanding_amount ELSE 0 END) AS b_31_60,
                SUM(CASE WHEN days_overdue BETWEEN 61 AND 90 THEN outstanding_amount ELSE 0 END) AS b_61_90,
                SUM(CASE WHEN days_overdue > 90 THEN outstanding_amount ELSE 0 END) AS b_over_90,
                SUM(outstanding_amount) AS total_outstanding,
                COUNT(*) AS invoice_count
              FROM sales
             WHERE deleted = 0 AND invoice_workflow_status IN ('ISSUED','PART_PAID','OVERDUE') AND outstanding_amount > 0",
            []
        );
        return $r[0] ?? ['current_bucket'=>0,'b_1_30'=>0,'b_31_60'=>0,'b_61_90'=>0,'b_over_90'=>0,'total_outstanding'=>0,'invoice_count'=>0];
    }

    /** Slice 8Z-x · Overdue invoice list for FM/Exec. */
    public function overdueList(int $minDays = 1): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT s.c0d, s.sale_no, s.invoice_date, s.due_date, s.days_overdue, s.customer_name,
                    s.currency, s.total, s.amount_paid, s.wht_settled_amount, s.outstanding_amount,
                    s.first_reminder_sent_at, s.last_reminder_sent_at, s.reminder_count,
                    p.project_no
               FROM sales s
          LEFT JOIN c_projects p ON p.c0d = s.project_id
              WHERE s.deleted = 0
                AND s.invoice_workflow_status IN ('ISSUED','PART_PAID','OVERDUE')
                AND s.outstanding_amount > 0
                AND s.days_overdue >= :d
              ORDER BY s.days_overdue DESC, s.outstanding_amount DESC",
            [':d' => $minDays]
        ) ?: [];
    }
}
