<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class EstadisticasController extends Controller
{
    public function index(Request $request)
    {
        $regional           = $request->input('regional');
        $departamento       = $request->input('departamento');
        $razon_social       = $request->input('razon_social');
        $sede               = $request->input('sede');
        $tipo_red           = $request->input('tipo_red');
        $trimestre_evaluado = $request->input('trimestre_evaluado');
        $anio_vigencia      = $request->input('anio_vigencia');

        // 1) Base limpia con filtros (¡no le metas select/groupBy a esta!)
        $base = DB::table('analytics.v_capita_resumen');

        if ($regional)           $base->where('regional', $regional);
        if ($departamento)       $base->where('departamento', $departamento);
        if ($razon_social)       $base->where('razon_social', $razon_social);
        if ($sede)               $base->where('sede', $sede);
        if ($tipo_red)           $base->where('tipo_red', $tipo_red);
        if ($trimestre_evaluado) $base->where('trimestre_evaluado', $trimestre_evaluado);
        if ($anio_vigencia)      $base->where('anio_vigencia', $anio_vigencia);

        // 2) Opciones de filtros -> de la base, con DISTINCT
        $optsRegional     = (clone $base)->select('regional')->distinct()->orderBy('regional')->pluck('regional');
        $optsDepto        = (clone $base)->select('departamento')->distinct()->orderBy('departamento')->pluck('departamento');
        $optsRazon        = (clone $base)->select('razon_social')->distinct()->orderBy('razon_social')->pluck('razon_social');
        $optsSede         = (clone $base)->select('sede')->distinct()->orderBy('sede')->pluck('sede');
        $optsTipoRed      = (clone $base)->select('tipo_red')->distinct()->orderBy('tipo_red')->pluck('tipo_red');
        $optsTrimestre    = (clone $base)->select('trimestre_evaluado')->distinct()->orderBy('trimestre_evaluado')->pluck('trimestre_evaluado');
        $optsAnioVigencia = (clone $base)->select('anio_vigencia')->distinct()->orderBy('anio_vigencia')->pluck('anio_vigencia');

        $filterMatrix = DB::table('analytics.v_capita_resumen')
            ->select(
                'regional',
                'departamento',
                'razon_social',
                'sede',
                'tipo_red',
                'trimestre_evaluado',
                'anio_vigencia'
            )
            ->distinct()
            ->get();

        // 3) Helper SQL para normalizar a 0-100:
        //    Si en el grupo el MAX <= 1.01 => multiplicamos por 100; si no, lo dejamos igual
        $NORM = fn($col) => DB::raw("
            CASE WHEN MAX(ISNULL($col,0)) <= 1.01
                 THEN AVG(CAST(ISNULL($col,0) AS FLOAT)) * 100
                 ELSE AVG(CAST(ISNULL($col,0) AS FLOAT))
            END as val
        ");

        // 4) Resumen porcentual por trimestre
         $trimestres = (clone $base)
            ->select(
                DB::raw(
                    "CASE WHEN MAX(ISNULL(ejec_i_trim_2024,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))\n".
                    "END as ejecucion_i"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(ejec_ii_trim_2024,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))\n".
                    "END as ejecucion_ii"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(ejec_iii_trim_2024,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))\n".
                    "END as ejecucion_iii"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(ejec_iv_trim_2024,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))\n".
                    "END as ejecucion_iv"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(ejec_global,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))\n".
                    "END as ejecucion_global"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(res_salud_i_ii,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))\n".
                    "END as salud_i_ii"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(res_salud_iii,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))\n".
                    "END as salud_iii"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(res_salud_iv,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))\n".
                    "END as salud_iv"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(res_salud_global,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))\n".
                    "END as salud_global"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(generalidades_i_ii,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(generalidades_i_ii,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(generalidades_i_ii,0) AS FLOAT))\n".
                    "END as generalidades_i_ii"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(generalidades_iii,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(generalidades_iii,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(generalidades_iii,0) AS FLOAT))\n".
                    "END as generalidades_iii"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(generalidades_iv,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(generalidades_iv,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(generalidades_iv,0) AS FLOAT))\n".
                    "END as generalidades_iv"
                ),
                DB::raw(
                    "CASE WHEN MAX(ISNULL(generalidades_globales,0)) <= 1.01\n".
                    "     THEN AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))*100\n".
                    "     ELSE AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))\n".
                    "END as generalidades_global"
                )
            )
            ->first();

        // 5) Agregaciones por agrupación (usamos clones independientes SIEMPRE)
        $chartRegionalMulti = (clone $base)
            ->select(
                'regional as etiqueta',
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(resultado_global_cualitativo_num,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))
                        END, 1) as resultado
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))
                        END, 1) as ejecucion
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))
                        END, 1) as salud
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(generalidades_globales,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))
                        END, 1) as generalidades
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_i_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_i
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_ii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iv_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iv
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_i_ii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))
                        END, 1) as salud_i_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))
                        END, 1) as salud_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iv,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))
                        END, 1) as salud_iv
                ")
            )
            ->groupBy('regional')
            ->orderBy('regional')
            ->get();

        $chartDeptoMulti = (clone $base)
            ->select(
                'departamento as etiqueta',
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(resultado_global_cualitativo_num,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))
                        END, 1) as resultado
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))
                        END, 1) as ejecucion
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))
                        END, 1) as salud
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(generalidades_globales,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))
                        END, 1) as generalidades
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_i_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_i
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_ii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iv_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iv
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_i_ii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))
                        END, 1) as salud_i_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))
                        END, 1) as salud_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iv,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))
                        END, 1) as salud_iv
                ")
            )
            ->groupBy('departamento')
            ->orderBy('departamento')
            ->get();

        $chartRazonMulti = (clone $base)
            ->select(
                'razon_social as etiqueta',
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(resultado_global_cualitativo_num,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))
                        END, 1) as resultado
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))
                        END, 1) as ejecucion
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))
                        END, 1) as salud
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(generalidades_globales,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))
                        END, 1) as generalidades
                "),
                 DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_i_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_i
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_ii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iv_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iv
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_i_ii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))
                        END, 1) as salud_i_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))
                        END, 1) as salud_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iv,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))
                        END, 1) as salud_iv
                ")
                
            )
            ->groupBy('razon_social')
            ->orderBy('razon_social')
            ->get();

        $chartSedeMulti = (clone $base)
            ->select(
                'sede as etiqueta',
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(resultado_global_cualitativo_num,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(resultado_global_cualitativo_num,0) AS FLOAT))
                        END, 1) as resultado
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_global,0) AS FLOAT))
                        END, 1) as ejecucion
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_global,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_global,0) AS FLOAT))
                        END, 1) as salud
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(generalidades_globales,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(generalidades_globales,0) AS FLOAT))
                        END, 1) as generalidades
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_i_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_i_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_i
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_ii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_ii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iii_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iii_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(ejec_iv_trim_2024,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(ejec_iv_trim_2024,0) AS FLOAT))
                        END, 1) as ejecucion_iv
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_i_ii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_i_ii,0) AS FLOAT))
                        END, 1) as salud_i_ii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iii,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iii,0) AS FLOAT))
                        END, 1) as salud_iii
                "),
                DB::raw("
                  ROUND(CASE WHEN MAX(ISNULL(res_salud_iv,0)) <= 1.01
                             THEN AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))*100
                             ELSE AVG(CAST(ISNULL(res_salud_iv,0) AS FLOAT))
                        END, 1) as salud_iv
                ")
            )
            ->groupBy('sede')
            ->orderBy('sede')
            ->get();

        $roundPercent = function($v){
            if($v === null) return null;
            $floor = floor($v);
            return $floor + (($v - $floor) > 0.6 ? 1 : 0);
        };

        $applyRound = function($collection) use ($roundPercent){
            return collect($collection)->map(function($row) use ($roundPercent){
                foreach ($row as $k => $v) {
                    if($k !== 'etiqueta') {
                        $row->$k = $roundPercent($v);
                    }
                }
                return $row;
            });
        };

        if($trimestres){
            foreach ($trimestres as $k => $v) {
                $trimestres->$k = $roundPercent($v);
            }
        }

        $chartRegionalMulti = $applyRound($chartRegionalMulti);
        $chartDeptoMulti    = $applyRound($chartDeptoMulti);
        $chartRazonMulti    = $applyRound($chartRazonMulti);
        $chartSedeMulti     = $applyRound($chartSedeMulti);

        // 5) Ajustes: suma monetaria por trimestre y total
        // Nota: algunas vistas no exponen columnas de ajuste; consultamos directamente la tabla analytics.capita_estadisticas
        $cap = DB::table('analytics.capita_estadisticas');
        if ($departamento)  $cap->where('departamento', $departamento);
        if ($razon_social)  $cap->where('razon_social', $razon_social);
        if ($sede)          $cap->where('sede', $sede);
        if ($tipo_red)      $cap->where('tipo_red', $tipo_red);
        if ($trimestre_evaluado) $cap->where('trimestre_evaluado', $trimestre_evaluado);
        if ($anio_vigencia) $cap->where('anio_vigencia', $anio_vigencia);

        $ajustes = $cap->select(
                DB::raw('SUM(CAST(ISNULL(ajuste_i_trim_2024,0) AS FLOAT))   as ajuste_i'),
                DB::raw('SUM(CAST(ISNULL(ajuste_ii_trim_2024,0) AS FLOAT))  as ajuste_ii'),
                DB::raw('SUM(CAST(ISNULL(ajuste_iii_trim_2024,0) AS FLOAT)) as ajuste_iii'),
                DB::raw('SUM(CAST(ISNULL(ajuste_iv_trim_2024,0) AS FLOAT))  as ajuste_iv')
            )->first();
        if ($ajustes) {
            $ajustes->ajuste_total = ($ajustes->ajuste_i ?? 0) + ($ajustes->ajuste_ii ?? 0) + ($ajustes->ajuste_iii ?? 0) + ($ajustes->ajuste_iv ?? 0);
        }

        return view('estadisticas.index', compact(
            'chartRegionalMulti',
            'chartDeptoMulti',
            'chartRazonMulti',
            'chartSedeMulti',
            'optsRegional',
            'optsDepto',
            'optsRazon',
            'optsSede',
            'optsTipoRed',
            'optsTrimestre',
            'optsAnioVigencia',
            'trimestres',
            'filterMatrix',
            'ajustes'
        ));
    }
}
