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 RiskReportRemedyServiceImpl implements RiskReportRemedyService {


    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<RiskMatrixReportDto> getRiskMatrixRemedy(RiskReportRequestDto request) {
        
        // Query SQL Nativo tal cual lo solicitaste (se cambian las fechas estáticas por parámetros :startDate y :endDate)
        String sql = " WITH max_escala AS ( "
        		+ "    SELECT 5 AS max_prob "
        		+ " ), "
        		+ " evaluaciones_calculadas AS ( "
        		+ "    SELECT  "
        		+ "        re.id, "
        		+ "        CASE  "
        		+ "            WHEN re.remedyprob > 0 AND re.remedyimpact > 0 THEN "
        		+ "                ROUND((re.remedyimpact - 1) * m.max_prob + re.remedyprob)::integer::text "
        		+ "            ELSE "
        		+ "                ROUND((re.impact - 1) * m.max_prob + re.probability)::integer::text "
        		+ "        END AS pos_ordinal "
        		+ "    FROM main.riskevaluations re "
        		+ "    CROSS JOIN max_escala m "
        		+ " 	WHERE re.remedyprob > 0 AND re.remedyimpact > 0 AND re.initialdate::DATE >= :startDate::DATE "
        		+ "  AND re.initialdate::DATE <= :endDate::DATE "
        		+ "  AND re.status = 0  "
        		+ "  AND probability <> 0 and impact<> 0 "  
        		+ "  ), "
        		+ "  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  "
        		+ "    s.etiqueta AS label, "
        		+ "    COUNT(ec.id) AS value, "
        		+ "    c.color_hex AS color "
        		+ " FROM evaluaciones_calculadas ec "
        		+ " JOIN severidades s ON ec.pos_ordinal = s.val "
        		+ " JOIN colores c ON ec.pos_ordinal = c.val "
        		+ " GROUP BY s.etiqueta, c.color_hex "
        		+ " ORDER BY value DESC ";

        // Mapeamos los valores del DTO del Body a los parámetros del Query
        MapSqlParameterSource params = new MapSqlParameterSource();
        // Nota: Asegúrate de adaptar el formato o tipo de dato si en tu base de datos initialdate es de tipo DATE, TIMESTAMP o VARCHAR
        params.addValue("startDate", request.getInitialDate());
        params.addValue("endDate", request.getEndDate());

        try {
            // Ejecutamos el query y usamos un RowMapper Lambda para poblar la lista de DTOs directamente
            return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) -> {
                RiskMatrixReportDto dto = new RiskMatrixReportDto();
                dto.setLabel(rs.getString("label"));
               // dto.setValue(rs.getLong("value"));
                dto.setValue(rs.getInt("value"));
                dto.setColor(rs.getString("color"));
                return dto;
            });
        } catch (Exception e) {
            e.printStackTrace();
            // Retornamos una lista vacía en caso de error para evitar romper el flujo con un null
            return new ArrayList<>();
        }
    }
    
    
}