<?php

declare(strict_types=1);

namespace App\Models;

class Sale extends BaseModel
{
    protected string $table = 'ventas';

    private function buildSaleFilters(array $filters): array
    {
        $where = ['v.empresa_id = :empresa_id'];
        $params = [];

        $desde = trim((string) ($filters['fecha_desde'] ?? ''));
        $hasta = trim((string) ($filters['fecha_hasta'] ?? ''));

        if ($desde !== '' && strtotime($desde)) {
            $where[] = 'DATE(v.fecha) >= :fecha_desde';
            $params[':fecha_desde'] = $desde;
        }

        if ($hasta !== '' && strtotime($hasta)) {
            $where[] = 'DATE(v.fecha) <= :fecha_hasta';
            $params[':fecha_hasta'] = $hasta;
        }

        return ['where' => implode(' AND ', $where), 'params' => $params];
    }

    public function countWithFilters(int $companyId, array $filters = []): int
    {
        ['where' => $where, 'params' => $params] = $this->buildSaleFilters($filters);
        $params[':empresa_id'] = $companyId;

        $stmt = $this->db()->prepare("SELECT COUNT(DISTINCT v.id) FROM ventas v WHERE {$where}");
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

    public function listWithRelations(int $companyId, array $filters = [], int $page = 1, int $perPage = 20): array
    {
        ['where' => $where, 'params' => $idParams] = $this->buildSaleFilters($filters);
        $idParams[':empresa_id'] = $companyId;
        $offset = max(0, ($page - 1) * $perPage);

        // Paso 1: obtener sólo los IDs paginados (sin joins extra que multipliquen filas)
        $idSql = "SELECT v.id FROM ventas v
                  WHERE {$where}
                  ORDER BY v.id DESC
                  LIMIT {$perPage} OFFSET {$offset}";

        $idStmt = $this->db()->prepare($idSql);
        $idStmt->execute($idParams);
        $ids = array_column($idStmt->fetchAll(\PDO::FETCH_ASSOC), 'id');

        if ($ids === []) {
            return [];
        }

        // Paso 2: traer los datos completos sólo para esos IDs
        $idPlaceholders = [];
        $sqlParams = [
            ':empresa_id' => $companyId,
        ];
        foreach (array_values($ids) as $index => $id) {
            $placeholder = ':id_' . $index;
            $idPlaceholders[] = $placeholder;
            $sqlParams[$placeholder] = (int) $id;
        }
        $placeholders = implode(',', $idPlaceholders);

        $sql = "SELECT v.id, v.fecha, v.total, v.estado, c.nombre AS cliente, u.nombre AS cajero,
                       s.nombre AS sucursal,
                       f.numero AS documento_numero,
                       (
                           SELECT GROUP_CONCAT(DISTINCT LOWER(vp3.metodo_pago) ORDER BY vp3.metodo_pago SEPARATOR ', ')
                           FROM venta_pagos vp3
                           WHERE vp3.venta_id = v.id
                             AND vp3.empresa_id = v.empresa_id
                       ) AS metodos_pago,
                       COALESCE(SUM(vp.monto), 0) AS pagado,
                       CASE
                           WHEN v.estado = 'anulada' THEN 0
                           ELSE (v.total - COALESCE(SUM(vp.monto), 0))
                       END AS saldo,
                       CASE
                           WHEN v.estado = 'anulada' THEN 'anulada'
                           WHEN COALESCE(SUM(vp.monto), 0) <= 0 THEN 'pendiente'
                           WHEN COALESCE(SUM(vp.monto), 0) < v.total THEN 'parcial'
                           ELSE 'pagada'
                       END AS estado_pago
                FROM ventas v
                LEFT JOIN clientes c ON c.id = v.cliente_id
                LEFT JOIN usuarios u ON u.id = v.usuario_id
                LEFT JOIN sucursales s ON s.id = v.sucursal_id
                LEFT JOIN facturas f ON f.venta_id = v.id
                                    AND f.empresa_id = v.empresa_id
                                    AND f.tipo_documento = 'factura'
                LEFT JOIN venta_pagos vp ON vp.venta_id = v.id
                                        AND vp.empresa_id = v.empresa_id
                WHERE v.id IN ({$placeholders})
                  AND v.empresa_id = :empresa_id
                GROUP BY v.id, v.fecha, v.total, v.estado, c.nombre, u.nombre, s.nombre, f.numero
                ORDER BY v.id DESC";

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

    public function findWithRelations(int $companyId, int $saleId): ?array
    {
        $stmt = $this->db()->prepare(
            'SELECT
                v.*,
                c.nombre AS cliente,
                c.documento AS cliente_documento,
                c.email AS cliente_email,
                c.telefono AS cliente_telefono,
                u.nombre AS cajero,
                s.nombre AS sucursal,
                f.numero AS documento_numero,
                (
                    SELECT GROUP_CONCAT(DISTINCT LOWER(vp3.metodo_pago) ORDER BY vp3.metodo_pago SEPARATOR ", ")
                    FROM venta_pagos vp3
                    WHERE vp3.venta_id = v.id
                      AND vp3.empresa_id = v.empresa_id
                ) AS metodos_pago,
                COALESCE(SUM(vp.monto), 0) AS pagado,
                CASE
                    WHEN v.estado = "anulada" THEN 0
                    ELSE (v.total - COALESCE(SUM(vp.monto), 0))
                END AS saldo,
                CASE
                    WHEN v.estado = "anulada" THEN "anulada"
                    WHEN COALESCE(SUM(vp.monto), 0) <= 0 THEN "pendiente"
                    WHEN COALESCE(SUM(vp.monto), 0) < v.total THEN "parcial"
                    ELSE "pagada"
                END AS estado_pago
             FROM ventas v
             LEFT JOIN clientes c ON c.id = v.cliente_id
             LEFT JOIN usuarios u ON u.id = v.usuario_id
             LEFT JOIN sucursales s ON s.id = v.sucursal_id
             LEFT JOIN facturas f ON f.venta_id = v.id AND f.empresa_id = v.empresa_id AND f.tipo_documento = "factura"
             LEFT JOIN venta_pagos vp ON vp.venta_id = v.id AND vp.empresa_id = v.empresa_id
             WHERE v.empresa_id = :empresa_id
               AND v.id = :venta_id
             GROUP BY
                v.id, v.empresa_id, v.sucursal_id, v.usuario_id, v.cliente_id, v.apertura_caja_id,
                v.subtotal, v.descuento, v.impuestos, v.total, v.estado, v.fecha, v.created_at, v.updated_at,
                c.nombre, c.documento, c.email, c.telefono, u.nombre, s.nombre, f.numero
             LIMIT 1'
        );
        $stmt->execute([
            ':empresa_id' => $companyId,
            ':venta_id' => $saleId,
        ]);

        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function annul(int $saleId, int $companyId): void
    {
        $stmt = $this->db()->prepare('UPDATE ventas SET estado = "anulada" WHERE id = :id AND empresa_id = :empresa_id');
        $stmt->execute([
            ':id' => $saleId,
            ':empresa_id' => $companyId,
        ]);

        $invoiceStmt = $this->db()->prepare(
            'UPDATE facturas
             SET estado = "anulada",
                 updated_at = NOW()
             WHERE venta_id = :venta_id
               AND empresa_id = :empresa_id'
        );
        $invoiceStmt->execute([
            ':venta_id' => $saleId,
            ':empresa_id' => $companyId,
        ]);
    }
}
