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 RiskReportServiceImpl implements RiskReportService {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	    @Override
	    public List<RiskMatrixReportDto> getRiskMatrixReport(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)::integer - 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.status = 1   "
	    			+ "      AND re.probability <> 0  "
	    			+ "      AND re.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 ";
	       /* 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.status = 1 " +
	                 "  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<>();
	        }
	    }
	    
	    
    
}