<?php

declare(strict_types=1);

namespace App\Models;

class Purchase extends BaseModel
{
    protected string $table = 'compras';

    public function listWithRelations(int $companyId): array
    {
        $sql = 'SELECT c.id, c.fecha, c.subtotal, c.impuestos, c.total, c.estado,
                       c.orden_compra_id, c.recepcion_compra_id,
                       oc.numero AS orden_numero,
                       p.nombre AS proveedor, u.nombre AS usuario,
                       COALESCE(SUM(pc.monto), 0) AS pagado,
                       CASE
                           WHEN c.estado = "anulada" THEN 0
                           ELSE (c.total - COALESCE(SUM(pc.monto), 0))
                       END AS saldo,
                       CASE
                           WHEN c.estado = "anulada" THEN "anulada"
                           WHEN COALESCE(SUM(pc.monto), 0) <= 0 THEN "pendiente"
                           WHEN COALESCE(SUM(pc.monto), 0) < c.total THEN "parcial"
                           ELSE "pagada"
                       END AS estado_pago
                FROM compras c
                INNER JOIN proveedores p ON p.id = c.proveedor_id
                INNER JOIN usuarios u ON u.id = c.usuario_id
                LEFT JOIN ordenes_compra oc ON oc.id = c.orden_compra_id AND oc.empresa_id = c.empresa_id
                LEFT JOIN pagos_compra pc ON pc.compra_id = c.id AND pc.empresa_id = c.empresa_id
                WHERE c.empresa_id = :empresa_id
                GROUP BY c.id, c.fecha, c.subtotal, c.impuestos, c.total, c.estado,
                         c.orden_compra_id, c.recepcion_compra_id, oc.numero, p.nombre, u.nombre
                ORDER BY c.id DESC';

        $stmt = $this->db()->prepare($sql);
        $stmt->execute([':empresa_id' => $companyId]);
        return $stmt->fetchAll() ?: [];
    }

    public function payablesSummary(int $companyId): array
    {
        $sql = 'SELECT
                    COALESCE(SUM(CASE WHEN c.estado <> "anulada" THEN c.total ELSE 0 END), 0) AS total_compras,
                    COALESCE(SUM(CASE WHEN c.estado <> "anulada" THEN COALESCE(pc_total.pagado, 0) ELSE 0 END), 0) AS total_pagado,
                    COALESCE(SUM(CASE WHEN c.estado <> "anulada" THEN c.total - COALESCE(pc_total.pagado, 0) ELSE 0 END), 0) AS total_saldo
                FROM compras c
                LEFT JOIN (
                    SELECT compra_id, empresa_id, SUM(monto) AS pagado
                    FROM pagos_compra
                    WHERE empresa_id = :empresa_id_sub
                    GROUP BY compra_id, empresa_id
                ) pc_total ON pc_total.compra_id = c.id AND pc_total.empresa_id = c.empresa_id
                WHERE c.empresa_id = :empresa_id_main';

        $stmt = $this->db()->prepare($sql);
        $stmt->execute([
            ':empresa_id_sub' => $companyId,
            ':empresa_id_main' => $companyId,
        ]);

        return $stmt->fetch() ?: [
            'total_compras' => 0,
            'total_pagado' => 0,
            'total_saldo' => 0,
        ];
    }
}
