<?php

class Supplier extends Model
{
    protected string $table = 'suppliers';

    public function allWithDues(): array
    {
        return $this->db->query(
            "SELECT s.*,
                    COALESCE((SELECT SUM(po.subtotal) FROM purchase_orders po WHERE po.supplier_id = s.id AND po.status != 'CANCELLED'),0) as total_ordered,
                    COALESCE((SELECT SUM(po.paid_amount) FROM purchase_orders po WHERE po.supplier_id = s.id AND po.status != 'CANCELLED'),0) as total_paid,
                    (SELECT COUNT(*) FROM purchase_orders po WHERE po.supplier_id = s.id) as po_count,
                    (SELECT MAX(po.order_date) FROM purchase_orders po WHERE po.supplier_id = s.id) as last_order_date
             FROM suppliers s
             ORDER BY s.is_active DESC, s.supplier_name ASC"
        )->fetchAll();
    }

    public function summaryStats(): array
    {
        $stmt = $this->db->query(
            "SELECT
                COUNT(*) as total_active,
                (SELECT COUNT(*) FROM (
                    SELECT s.id FROM suppliers s
                    WHERE (SELECT COALESCE(SUM(po.subtotal - po.paid_amount),0) FROM purchase_orders po WHERE po.supplier_id = s.id AND po.status != 'CANCELLED') > 0
                ) t) as with_payable
             FROM suppliers WHERE is_active = 1"
        );
        return $stmt->fetch();
    }

    public function totalDues(): float
    {
        $stmt = $this->db->query(
            "SELECT COALESCE(SUM(subtotal - paid_amount),0) as total FROM purchase_orders WHERE status != 'CANCELLED'"
        );
        return (float) $stmt->fetch()['total'];
    }
}
