<?php
/**
 * SupplierBills — the AP mirror of CustomerInvoices.
 *
 * Life cycle:
 *   draft   → operator entered the bill but no ledger impact
 *   posted  → JE created (Dr Expense + Input VAT / Cr Trade Payables)
 *   paid    → total_amount ≤ amount_paid; set by SupplierPayments::allocate
 *   void    → reversed (either as draft or reversal JE if it was posted)
 *
 * Ledger shape:
 *   Dr Expense (client-picked)     subtotal
 *   Dr 3008 VAT Recoverable        vat_amount   (only if > 0)
 *   Cr 4009 Trade Payables         total_amount
 */
class SupplierBills extends Model
{
    private const AP_ACCOUNT       = '4009';   // Trade Payables (credit-natural)
    private const VAT_INPUT_ACC    = '3008';   // VAT Recoverable
    private const DEFAULT_EXPENSE  = '7014';   // Misc Expenses fallback

    private function s($v): string { return trim((string)$v); }
    private function m($v): float  { return round((float)preg_replace('/[^\d\.\-]/', '', (string)$v), 2); }

    /* ═══════════════ reads ═══════════════ */

    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['sb.deleted = 0', 'sb.tenant_id = :tenant'];
        $params = [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1];

        if (!empty($filters['q'])) {
            $where[] = '(sb.bill_no LIKE :q OR sb.supplier_ref LIKE :q OR v.vendor_name LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))     { $where[] = 'sb.status    = :st'; $params[':st'] = $this->s($filters['status']); }
        if (!empty($filters['vendor_id']))  { $where[] = 'sb.vendor_id = :vid'; $params[':vid'] = (int)$filters['vendor_id']; }
        if (!empty($filters['from_date']))  { $where[] = 'sb.bill_date >= :fd'; $params[':fd'] = $filters['from_date']; }
        if (!empty($filters['to_date']))    { $where[] = 'sb.bill_date <= :td'; $params[':td'] = $filters['to_date']; }

        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM supplier_bills sb LEFT JOIN vendors v ON v.c0d = sb.vendor_id WHERE $whereSql", $params);
        $total = (int)($countRs[0]['n'] ?? 0);

        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT sb.c0d, sb.bill_no, sb.supplier_ref, sb.bill_date, sb.due_date,
                    sb.subtotal, sb.vat_amount, sb.total_amount, sb.amount_paid,
                    sb.currency, sb.status, sb.vendor_id, v.vendor_name,
                    (sb.total_amount - sb.amount_paid) AS balance_due,
                    DATEDIFF(CURDATE(), sb.due_date) AS days_overdue
               FROM supplier_bills sb
          LEFT JOIN vendors v ON v.c0d = sb.vendor_id
              WHERE $whereSql
           ORDER BY sb.bill_date DESC, sb.c0d DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function statusCounts(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT status, COUNT(*) AS n, COALESCE(SUM(total_amount - amount_paid), 0) AS due
               FROM supplier_bills
              WHERE deleted = 0 AND tenant_id = :t
           GROUP BY status",
            [':t' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1]
        ) ?: [];
        $out = ['draft' => 0, 'posted' => 0, 'paid' => 0, 'void' => 0, 'total_due' => 0.0];
        foreach ($rs as $r) {
            $out[$r['status']] = (int)$r['n'];
            if ($r['status'] === 'posted') $out['total_due'] += (float)$r['due'];
        }
        return $out;
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT sb.*, v.vendor_name, v.vendor_tin, v.vendor_vrn,
                    v.vendor_phone_1, v.vendor_email, v.vendor_address,
                    v.vendor_city, v.vendor_po_box,
                    coa.account_code AS expense_code, coa.account_name AS expense_name
               FROM supplier_bills sb
          LEFT JOIN vendors v          ON v.c0d = sb.vendor_id
          LEFT JOIN chart_of_accounts coa ON coa.c0d = sb.expense_account_id
              WHERE sb.c0d = :id AND sb.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function paymentsFor(int $billId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT sp.c0d, sp.payment_no, sp.payment_date, sp.amount, sp.payment_method,
                    sp.reference, sp.status, spa.amount_applied
               FROM supplier_payment_allocations spa
               JOIN supplier_payments sp ON sp.c0d = spa.payment_id
              WHERE spa.bill_id = :bid AND sp.deleted = 0
           ORDER BY sp.payment_date DESC",
            [':bid' => $billId]
        ) ?: [];
    }

    /* ═══════════════ create + post ═══════════════ */

    /**
     * Persist a bill in draft state. Ledger is untouched — the accountant
     * reviews the draft and posts explicitly via post().
     */
    public function createDraft(array $d, int $uid): array
    {
        $vendorId = (int)($d['vendor_id'] ?? 0);
        if ($vendorId <= 0) return ['ok' => false, 'msg' => 'Pick a supplier.', 'id' => 0];

        $subtotal = $this->m($d['subtotal'] ?? 0);
        $vat      = $this->m($d['vat_amount'] ?? 0);
        $total    = round($subtotal + $vat, 2);
        if ($total <= 0) return ['ok' => false, 'msg' => 'Total must be positive.', 'id' => 0];

        $billDate = $this->s($d['bill_date'] ?? '') ?: date('Y-m-d');
        $terms    = $this->s($d['payment_terms'] ?? 'net_30');
        $dueDate  = $this->s($d['due_date'] ?? '') ?: $this->deriveDueDate($billDate, $terms);

        $db = new DB();
        $ins = $db->insertUpdate(
            "INSERT INTO supplier_bills
                (tenant_id, bill_no, supplier_ref, vendor_id, bill_date, due_date,
                 payment_terms, currency, subtotal, vat_amount, total_amount,
                 expense_account_id, status, notes, created_by, created_on)
             VALUES
                (:t, :bn, :sr, :vid, :bd, :dd,
                 :terms, :cur, :sub, :vat, :tot,
                 :exp, 'draft', :notes, :uid, NOW())",
            [
                ':t'     => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':bn'    => $this->reserveDraftNo(),
                ':sr'    => $this->s($d['supplier_ref'] ?? ''),
                ':vid'   => $vendorId,
                ':bd'    => $billDate,
                ':dd'    => $dueDate,
                ':terms' => $terms,
                ':cur'   => 'TZS',
                ':sub'   => $subtotal,
                ':vat'   => $vat,
                ':tot'   => $total,
                ':exp'   => (int)($d['expense_account_id'] ?? 0) ?: null,
                ':notes' => $this->s($d['notes'] ?? ''),
                ':uid'   => $uid,
            ]
        );
        $id = (int)$ins['last_id'];
        return $id > 0
            ? ['ok' => true, 'msg' => 'Draft bill created.', 'id' => $id]
            : ['ok' => false, 'msg' => 'Could not persist draft.', 'id' => 0];
    }

    /**
     * Post the bill to the ledger:
     *   Dr Expense (subtotal)
     *   Dr 3008 VAT Recoverable (vat_amount, if > 0)
     *   Cr 4009 Trade Payables (total_amount)
     */
    public function post(int $id, int $uid, string $uname): array
    {
        $bill = $this->byId($id);
        if (!$bill) return ['ok' => false, 'msg' => 'Bill not found.'];
        if ($bill['status'] !== 'draft') return ['ok' => false, 'msg' => 'Only draft bills can be posted.'];

        $expenseCode = $bill['expense_code'] ?: self::DEFAULT_EXPENSE;
        $subtotal    = (float)$bill['subtotal'];
        $vat         = (float)$bill['vat_amount'];
        $total       = (float)$bill['total_amount'];

        $lines = [
            ['account_code' => $expenseCode, 'debit' => $subtotal, 'credit' => 0.0,
             'narrative' => 'Bill ' . $bill['bill_no'], 'vendor_id' => (int)$bill['vendor_id']],
        ];
        if ($vat > 0.005) {
            $lines[] = ['account_code' => self::VAT_INPUT_ACC, 'debit' => $vat, 'credit' => 0.0,
                        'narrative' => 'Input VAT ' . $bill['bill_no'], 'vendor_id' => (int)$bill['vendor_id']];
        }
        $lines[] = ['account_code' => self::AP_ACCOUNT, 'debit' => 0.0, 'credit' => $total,
                    'narrative' => 'A/P ' . $bill['bill_no'], 'vendor_id' => (int)$bill['vendor_id']];

        $post = (new JournalPosting())->post([
            'tenant_id'   => (int)$bill['tenant_id'],
            'entry_date'  => $bill['bill_date'],
            'source_type' => 'BILL',
            'source_id'   => $id,
            'source_ref'  => $bill['bill_no'],
            'narrative'   => 'Supplier bill ' . $bill['bill_no'] . ' · ' . ($bill['vendor_name'] ?? ''),
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines'       => $lines,
        ]);
        if (!$post['ok']) return ['ok' => false, 'msg' => 'Journal post failed: ' . $post['msg']];

        // Promote draft → posted and stamp the real bill_no + JE id
        $realNo = $this->nextBillNo();
        $db = new DB();
        $db->insertUpdate(
            "UPDATE supplier_bills SET
                status = 'posted', bill_no = :bn,
                journal_entry_id = :je, issued_by = :uid, issued_at = NOW(),
                edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':bn' => $realNo, ':je' => (int)$post['id'], ':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => "Bill posted ($realNo)."];
    }

    public function voidBill(int $id, string $reason, int $uid, string $uname): array
    {
        $bill = $this->byId($id);
        if (!$bill) return ['ok' => false, 'msg' => 'Bill not found.'];
        if ($bill['status'] === 'void') return ['ok' => false, 'msg' => 'Already void.'];
        if ((float)$bill['amount_paid'] > 0.005) return ['ok' => false, 'msg' => 'Bill has payments — void the payments first.'];

        // If posted, post a reversing JE; if draft, just mark void.
        if ($bill['status'] === 'posted') {
            $expenseCode = $bill['expense_code'] ?: self::DEFAULT_EXPENSE;
            $subtotal    = (float)$bill['subtotal'];
            $vat         = (float)$bill['vat_amount'];
            $total       = (float)$bill['total_amount'];
            $lines = [
                ['account_code' => self::AP_ACCOUNT, 'debit' => $total, 'credit' => 0.0,
                 'narrative' => 'Reversal ' . $bill['bill_no'], 'vendor_id' => (int)$bill['vendor_id']],
                ['account_code' => $expenseCode, 'debit' => 0.0, 'credit' => $subtotal,
                 'narrative' => 'Reversal ' . $bill['bill_no'], 'vendor_id' => (int)$bill['vendor_id']],
            ];
            if ($vat > 0.005) {
                $lines[] = ['account_code' => self::VAT_INPUT_ACC, 'debit' => 0.0, 'credit' => $vat,
                            'narrative' => 'Reversal ' . $bill['bill_no'], 'vendor_id' => (int)$bill['vendor_id']];
            }
            $post = (new JournalPosting())->post([
                'tenant_id'   => (int)$bill['tenant_id'],
                'entry_date'  => date('Y-m-d'),
                'source_type' => 'BILL_VOID',
                'source_id'   => $id,
                'source_ref'  => $bill['bill_no'],
                'narrative'   => 'Void bill ' . $bill['bill_no'] . ' · ' . $reason,
                'posted_by'      => $uid,
                'posted_by_name' => $uname,
                'lines'       => $lines,
            ]);
            if (!$post['ok']) return ['ok' => false, 'msg' => 'Reversal JE failed: ' . $post['msg']];
        }

        $db = new DB();
        $db->insertUpdate(
            "UPDATE supplier_bills SET
                status = 'void', void_by = :uid, void_at = NOW(),
                void_reason = :r, edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':r' => $reason, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Bill voided.'];
    }

    /**
     * Refresh amount_paid + status from allocations. Called by SupplierPayments
     * whenever it allocates / voids.
     */
    public function refreshPaidState(int $id): void
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT COALESCE(SUM(spa.amount_applied), 0) AS paid
               FROM supplier_payment_allocations spa
               JOIN supplier_payments sp ON sp.c0d = spa.payment_id AND sp.status != 'void' AND sp.deleted = 0
              WHERE spa.bill_id = :b",
            [':b' => $id]
        );
        $paid = (float)($rs[0]['paid'] ?? 0);
        $bill = $this->byId($id);
        if (!$bill) return;
        $status = ($paid >= (float)$bill['total_amount'] - 0.005 && $bill['status'] !== 'void')
                    ? 'paid'
                    : ($bill['status'] === 'paid' ? 'posted' : $bill['status']);
        $db->insertUpdate(
            "UPDATE supplier_bills SET amount_paid = :p, status = :s WHERE c0d = :id",
            [':p' => round($paid, 2), ':s' => $status, ':id' => $id]
        );
    }

    /** Open bills a payment can allocate to. */
    public function openBillsForVendor(int $vendorId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, bill_no, supplier_ref, bill_date, due_date, total_amount, amount_paid,
                    (total_amount - amount_paid) AS balance_due
               FROM supplier_bills
              WHERE vendor_id = :v AND deleted = 0 AND status IN ('posted','paid')
                AND (total_amount - amount_paid) > 0.005
           ORDER BY bill_date, c0d",
            [':v' => $vendorId]
        ) ?: [];
    }

    /* ═══════════════ helpers ═══════════════ */

    private function deriveDueDate(string $billDate, string $terms): string
    {
        $days = ['net_7' => 7, 'net_14' => 14, 'net_30' => 30, 'net_45' => 45, 'net_60' => 60, 'net_90' => 90][$terms] ?? 30;
        return date('Y-m-d', strtotime($billDate . " +$days days"));
    }

    private function reserveDraftNo(): string
    {
        // Draft numbers avoid burning a sequential BILL number until the
        // accountant actually posts. Uses timestamp + rand to stay unique.
        return 'DRAFT-' . date('YmdHis') . '-' . mt_rand(1000, 9999);
    }

    private function nextBillNo(): string
    {
        $db = new DB();
        $yr = date('Y');
        $rs = $db->mySelect(
            "SELECT bill_no FROM supplier_bills
              WHERE bill_no LIKE :p ORDER BY bill_no DESC LIMIT 1",
            [':p' => "BILL-$yr-%"]
        );
        $n = 1;
        if (!empty($rs) && preg_match('/BILL-\d{4}-(\d+)/', $rs[0]['bill_no'], $m)) {
            $n = (int)$m[1] + 1;
        }
        return sprintf('BILL-%s-%04d', $yr, $n);
    }
}
