package com.dacrt.SBIABackend.service;
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;

import java.util.ArrayList;
import java.util.List;

@Service
public class RiskReportServiceEvaluadoImpl implements RiskReportServiceEvaluado {
	

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<RiskMatrixReportDto> getRiskMatrixEvaluado(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, " +
                "        ROUND((re.impact - 1) * m.max_prob + re.probability)::integer::text AS pos_ordinal " +
                "    FROM main.riskevaluations re " +
                "    CROSS JOIN max_escala m " +
                "    WHERE re.initialdate::DATE >= :startDate::DATE " +
                
                " AND re.initialdate::DATE <= :endDate::DATE  " +
                //"      AND re.initialdate <= :endDate::timestamp " +
                
                "      AND re.status = 0 " +
                " AND (re.remedyprob = 0 OR re.remedyprob IS NULL)  " +
                " AND (re.remedyimpact = 0 OR re.remedyimpact IS NULL) " +
                " 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)::int 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<>();
        }
    }
    
    
    
}