package com.dacrt.SBIABackend.service;

import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import com.dacrt.SBIABackend.dto.RiskMatrixReportDto;
import com.dacrt.SBIABackend.dto.RiskReportRequestDto;

@Service
public class RiskReportExpoServiceImpl implements RiskReportExpoService {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<RiskMatrixReportDto> getRiskMatrixExpo(RiskReportRequestDto request) {
        
        // El String sql se queda exactamente igual a como me lo pasaste (ya está perfecto)
        String sql = " WITH max_escala AS (  "
                + "    SELECT 5 AS max_prob  "
                + " ),  "
                + " evaluaciones_validadas AS (  "
                + "    SELECT   "
                + "        re.id,  "
                + "        COALESCE(re.scenerydsc, 'Escenario ' || re.scenery) AS nombre_escenario,  "
                + "        CASE   "
                + "            WHEN re.remedyprob > 0 AND re.remedyimpact > 0 THEN re.remedyprob  "
                + "            ELSE re.probability  "
                + "        END AS prob_final,  "
                + "        CASE   "
                + "            WHEN re.remedyprob > 0 AND re.remedyimpact > 0 THEN re.remedyimpact  "
                + "            ELSE re.impact  "
                + "        END AS imp_final  "
                + "    FROM main.riskevaluations re  "
                + " 	WHERE re.initialdate::DATE >= :startDate::DATE "
                + "  AND re.initialdate::DATE <= :endDate::DATE AND probability <> 0 and impact<> 0 "
                + " ),  "
                + " promedios_por_escenario AS (  "
                + "    SELECT   "
                + "        ev.nombre_escenario,   "
                + "        COUNT(*) AS total_evaluaciones_escenario,  "
                + "        ROUND(SUM(ev.prob_final) / COUNT(*)) AS prob_promedio,  "
                + "        ROUND(SUM(ev.imp_final) / COUNT(*)) AS imp_promedio  "
                + "    FROM evaluaciones_validadas ev  "
                + "    GROUP BY ev.nombre_escenario  "
                + " ),  "
                + " posicion_escenarios AS (  "
                + "    SELECT   "
                + "        pe.nombre_escenario,  "
                + "        pe.total_evaluaciones_escenario,  "
                + "        ((pe.imp_promedio - 1) * m.max_prob + pe.prob_promedio)::integer::text AS pos_ordinal  "
                + "    FROM promedios_por_escenario pe  "
                + "    CROSS JOIN max_escala m  "
                + "  ),  "
                + " severidades AS (  "
                + "    SELECT   "
                + "        (elem->>'value') AS val,  "
                + "        (elem->>'dsc') AS etiqueta  "
                + "    FROM main.params,  "
                + "    LATERAL json_array_elements(value::json) AS elem  "
                + "    WHERE paramname = 'RISK_SEVERITYLEVEL'  "
                + " ),  "
                + " colores AS (  "
                + "    SELECT   "
                + "        (elem->>'value') AS val,  "
                + "        '#' || (elem->>'dsc') AS color_hex  "
                + "    FROM main.params,  "
                + "    LATERAL json_array_elements(value::json) AS elem  "
                + "    WHERE paramname = 'RISK_COLORSEVERITY'  "
                + " )  "
                + " SELECT   "
                + "    pe.nombre_escenario AS label,  "
                + "    ROUND(  "
                + "        (pe.total_evaluaciones_escenario::numeric / SUM(pe.total_evaluaciones_escenario) OVER()) * 100  "
                + "    )::integer AS value,  "
                + "    c.color_hex AS color,  "
                + "    s.etiqueta AS severity_label   " // <-- Tu query ya saca esto dinámicamente
                + " FROM posicion_escenarios pe  "
                + " JOIN severidades s ON pe.pos_ordinal = s.val  "
                + " JOIN colores c ON pe.pos_ordinal = c.val  "
                + " ORDER BY pe.nombre_escenario ASC ";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("startDate", request.getInitialDate());
        params.addValue("endDate", request.getEndDate());

        try {
            return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) -> {
                RiskMatrixReportDto dto = new RiskMatrixReportDto();
                dto.setLabel(rs.getString("label"));
                dto.setValue(rs.getInt("value")); 
                dto.setColor(rs.getString("color"));
                
                // =============================================================
                // ¡AQUÍ ESTÁ EL CAMBIO! Mapeamos la etiqueta de la BD al DTO
                // =============================================================
                dto.setLevelLabel(rs.getString("severity_label")); 
                
                return dto;
            });
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }
    /*@Override
    public List<RiskMatrixReportDto> getRiskMatrixExpo(RiskReportRequestDto request) {
        
        // CORRECCIÓN: Query unificado por nombre_escenario y con parámetros :startDate y :endDate
        String sql = " WITH max_escala AS (  "
        		+ "    SELECT 5 AS max_prob  "
        		+ " ),  "
        		+ " evaluaciones_validadas AS (  "
        		+ "    SELECT   "
        		+ "        re.id,  "
        		+ "        COALESCE(re.scenerydsc, 'Escenario ' || re.scenery) AS nombre_escenario,  "
        		+ "        CASE   "
        		+ "            WHEN re.remedyprob > 0 AND re.remedyimpact > 0 THEN re.remedyprob  "
        		+ "            ELSE re.probability  "
        		+ "        END AS prob_final,  "
        		+ "        CASE   "
        		+ "            WHEN re.remedyprob > 0 AND re.remedyimpact > 0 THEN re.remedyimpact  "
        		+ "            ELSE re.impact  "
        		+ "        END AS imp_final  "
        		+ "    FROM main.riskevaluations re  "
        		+ " 	WHERE re.initialdate::DATE >= :startDate::DATE "
        		+ "  AND re.initialdate::DATE <= :endDate::DATE "
        		+ " ),  "
        		+ " promedios_por_escenario AS (  "
        		+ "    SELECT   "
        		+ "        ev.nombre_escenario,   "
        		+ "        COUNT(*) AS total_evaluaciones_escenario,  "
        		+ "        ROUND(SUM(ev.prob_final) / COUNT(*)) AS prob_promedio,  "
        		+ "        ROUND(SUM(ev.imp_final) / COUNT(*)) AS imp_promedio  "
        		+ "    FROM evaluaciones_validadas ev  "
        		+ "    GROUP BY ev.nombre_escenario  "
        		+ " ),  "
        		+ " posicion_escenarios AS (  "
        		+ "    SELECT   "
        		+ "        pe.nombre_escenario,  "
        		+ "        pe.total_evaluaciones_escenario,  "
        		+ "        ((pe.imp_promedio - 1) * m.max_prob + pe.prob_promedio)::integer::text AS pos_ordinal  "
        		+ "    FROM promedios_por_escenario pe  "
        		+ "    CROSS JOIN max_escala m  "
        		+ "  ),  "
        		+ " severidades AS (  "
        		+ "    SELECT   "
        		+ "        (elem->>'value') AS val,  "
        		+ "        (elem->>'dsc') AS etiqueta  "
        		+ "    FROM main.params,  "
        		+ "    LATERAL json_array_elements(value::json) AS elem  "
        		+ "    WHERE paramname = 'RISK_SEVERITYLEVEL'  "
        		+ " ),  "
        		+ " colores AS (  "
        		+ "    SELECT   "
        		+ "        (elem->>'value') AS val,  "
        		+ "        '#' || (elem->>'dsc') AS color_hex  "
        		+ "    FROM main.params,  "
        		+ "    LATERAL json_array_elements(value::json) AS elem  "
        		+ "    WHERE paramname = 'RISK_COLORSEVERITY'  "
        		+ " )  "
        		+ " SELECT   "
        		+ "    pe.nombre_escenario AS label,  "
        		+ "    ROUND(  "
        		+ "        (pe.total_evaluaciones_escenario::numeric / SUM(pe.total_evaluaciones_escenario) OVER()) * 100  "
        		+ "    )::integer AS value,  "
        		+ "    c.color_hex AS color,  "
        		+ "    s.etiqueta AS severity_label   "
        		+ " FROM posicion_escenarios pe  "
        		+ " JOIN severidades s ON pe.pos_ordinal = s.val  "
        		+ " JOIN colores c ON pe.pos_ordinal = c.val  "
        		+ " ORDER BY pe.nombre_escenario ASC ";

        MapSqlParameterSource params = new MapSqlParameterSource();
        // Aseguramos el envío de las fechas del Request
        params.addValue("startDate", request.getInitialDate());
        params.addValue("endDate", request.getEndDate());

        try {
            return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) -> {
                RiskMatrixReportDto dto = new RiskMatrixReportDto();
                dto.setLabel(rs.getString("label"));
                
                // NOTA: Si tu RiskMatrixReportDto maneja Double/BigDecimal para guardar los decimales del %,
                // cambia aquí a rs.getDouble("value"). Si usa int, déjalo con rs.getInt.
                dto.setValue(rs.getInt("value")); 
                dto.setColor(rs.getString("color"));
                return dto;
            });
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }*/
}