<?php

declare(strict_types=1);

namespace ApiModel\Service;

use PDO;

final class ReporteCanjeService
{
    public function __construct(private readonly PDO $pdo) {}

    /** @return array<string, mixed> */
    public function obtenerResumen(): array
    {
        // 1. KPIs of certificates
        $stKpi = $this->pdo->query(
            'SELECT 
                COALESCE(SUM(CASE WHEN estado <> \'ANULADO\' THEN monto_inicial ELSE 0 END), 0) as total_emitido,
                COALESCE(SUM(CASE WHEN estado = \'AGOTADO\' THEN monto_inicial ELSE 0 END), 0) as total_canjeado,
                COALESCE(SUM(CASE WHEN estado = \'ACTIVO\' THEN monto_actual ELSE 0 END), 0) as total_pendiente,
                COUNT(*) as total_certificados,
                SUM(CASE WHEN estado = \'ACTIVO\' THEN 1 ELSE 0 END) as activos,
                SUM(CASE WHEN estado = \'AGOTADO\' THEN 1 ELSE 0 END) as agotados,
                SUM(CASE WHEN estado = \'ANULADO\' THEN 1 ELSE 0 END) as anulados
             FROM public.certificados'
        );
        $kpi = $stKpi->fetch();

        // 2. Fetch total payments made
        $stPagos = $this->pdo->query('SELECT COALESCE(SUM(monto), 0) FROM public.pagos_ferreterias');
        $totalPagado = (float)$stPagos->fetchColumn();

        // 3. Hardware store redemptions and payments data
        $stChart = $this->pdo->query(
            'SELECT 
                f.id as ferreteria_id,
                f.nombre as ferreteria_nombre,
                COALESCE((SELECT SUM(c.monto) FROM public.canjes c WHERE c.ferreteria_id = f.id), 0) as total_canjeado,
                COALESCE((SELECT COUNT(c.id) FROM public.canjes c WHERE c.ferreteria_id = f.id), 0) as cantidad_canjes,
                COALESCE((SELECT SUM(p.monto) FROM public.pagos_ferreterias p WHERE p.ferreteria_id = f.id), 0) as total_pagado
             FROM public.ferreterias f
             ORDER BY total_canjeado DESC'
        );
        $chartData = $stChart->fetchAll();

        $totalCanjeado = (float)($kpi['total_canjeado'] ?? 0.0);

        return [
            'kpis' => [
                'total_emitido' => (float)($kpi['total_emitido'] ?? 0.0),
                'total_canjeado' => $totalCanjeado,
                'total_pendiente' => (float)($kpi['total_pendiente'] ?? 0.0),
                'total_pagado' => $totalPagado,
                'total_deuda_pendiente' => $totalCanjeado - $totalPagado,
                'total_certificados' => (int)($kpi['total_certificados'] ?? 0),
                'activos' => (int)($kpi['activos'] ?? 0),
                'agotados' => (int)($kpi['agotados'] ?? 0),
                'anulados' => (int)($kpi['anulados'] ?? 0),
            ],
            'ferreterias_chart' => array_map(function(array $row) {
                $canjeado = (float)$row['total_canjeado'];
                $pagado = (float)$row['total_pagado'];
                return [
                    'id' => $row['ferreteria_id'],
                    'nombre' => $row['ferreteria_nombre'],
                    'total_canjeado' => $canjeado,
                    'cantidad_canjes' => (int)$row['cantidad_canjes'],
                    'total_pagado' => $pagado,
                    'deuda_pendiente' => $canjeado - $pagado,
                ];
            }, $chartData)
        ];
    }
}
