package com.dacrt.SBIABackend.service;

import com.dacrt.SBIABackend.dto.MatrixCellDto;
import com.dacrt.SBIABackend.dto.MatrixRowDto;
import com.dacrt.SBIABackend.dto.ScenaryContentBlockDto;
import com.dacrt.SBIABackend.dto.ScenaryValueItemDto;
import com.dacrt.SBIABackend.service.ScenaryRiskService;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

@Service
public class ScenaryRiskServiceImpl implements ScenaryRiskService {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public ScenaryContentBlockDto obtenerNivelesRiesgo(String nombreEscenario, Timestamp fechaFrom, Timestamp fechaTo, Integer idEscenario) {
    	
    	
    	
    	String sqlNiveles = " WITH max_escala AS (SELECT 5 AS max_prob),  "
                + " evaluaciones_tipificadas AS (  "
                + "  SELECT re.id,   "
                + "    	  CASE   "
                + "    	    WHEN re.remedyprob <> 0 AND re.remedyimpact <> 0 THEN   "
                + "    	      CAST(ROUND(CAST(((CAST(ROUND(CAST(re.remedyimpact AS numeric)) AS integer) - 1) * 5 + re.remedyprob) AS numeric)) AS text)   "
                + "    	    ELSE   "
                + "    	      CAST(ROUND(CAST(((CAST(ROUND(CAST(re.impact AS numeric)) AS integer) - 1) * 5 + re.probability) AS numeric)) AS text)   "
                + "    	  END AS pos_ordinal   "
                + "    	FROM main.riskevaluations re   "
                + "    	WHERE CAST(re.initialdate AS DATE) >= CAST(:fechaFrom AS DATE)   "
                + "    	AND CAST(re.initialdate AS DATE) <= CAST(:fechaTo AS DATE)  "
                + "    	AND re.scenery = :idEscenario  "  
                + " ),  "
                + " severidades AS (  "
                + "  SELECT json_extract_path_text(elem, 'value') AS val, json_extract_path_text(elem, 'dsc') AS etiqueta  "
                + "  FROM main.params, LATERAL json_array_elements(CAST(value AS json)) AS elem WHERE paramname = 'RISK_SEVERITYLEVEL'  "
                + " ),  "
                + " colores AS (  "
                + "  SELECT json_extract_path_text(elem, 'value') AS val, '#' || json_extract_path_text(elem, 'dsc') AS color_hex  "
                + "  FROM main.params, LATERAL json_array_elements(CAST(value AS json)) AS elem WHERE paramname = 'RISK_COLORSEVERITY'  "
                + " )  "
                + " SELECT s.etiqueta, COUNT(et.id) AS cant, c.color_hex  "
                + " FROM evaluaciones_tipificadas et  "
                + " JOIN severidades s ON et.pos_ordinal = s.val  "
                + " JOIN colores c ON et.pos_ordinal = c.val  "
                + " GROUP BY s.etiqueta, c.color_hex ORDER BY cant DESC ";
       /* String sqlNiveles = 
            "WITH max_escala AS (SELECT 5 AS max_prob), " +
            "evaluaciones_tipificadas AS ( " +
            "  SELECT re.id, " +
            "    CASE " +
            "      WHEN re.remedyprob <> 0 AND re.remedyimpact <> 0 THEN " +
            "        CAST(ROUND((re.remedyimpact - 1) * m.max_prob + re.remedyprob) AS text) " +
           // + "        ((ROUND(re.impact)::integer - 1) * m.max_prob + re.probability)::integer::text AS pos_ordinal   "
            
            "      ELSE " +
            "        CAST(ROUND((re.impact - 1) * m.max_prob + re.probability) AS text) " +
            "    END AS pos_ordinal " +
            "  FROM main.riskevaluations re CROSS JOIN max_escala m " +
            "  WHERE re.initialdate >= :fechaFrom AND re.initialdate <= :fechaTo AND re.scenery = :idEscenario " +
            "), " +
            "severidades AS ( " +
            "  SELECT json_extract_path_text(elem, 'value') AS val, json_extract_path_text(elem, 'dsc') AS etiqueta " +
            "  FROM main.params, LATERAL json_array_elements(CAST(value AS json)) AS elem WHERE paramname = 'RISK_SEVERITYLEVEL' " +
            "), " +
            "colores AS ( " +
            "  SELECT json_extract_path_text(elem, 'value') AS val, '#' || json_extract_path_text(elem, 'dsc') AS color_hex " +
            "  FROM main.params, LATERAL json_array_elements(CAST(value AS json)) AS elem WHERE paramname = 'RISK_COLORSEVERITY' " +
            ") " +
            "SELECT s.etiqueta, COUNT(et.id) AS cant, c.color_hex " +
            "FROM evaluaciones_tipificadas et " +
            "JOIN severidades s ON et.pos_ordinal = s.val " +
            "JOIN colores c ON et.pos_ordinal = c.val " +
            "GROUP BY s.etiqueta, c.color_hex ORDER BY cant DESC";*/

        Query qNiveles = entityManager.createNativeQuery(sqlNiveles);
        qNiveles.setParameter("fechaFrom", fechaFrom);
        qNiveles.setParameter("fechaTo", fechaTo);
        qNiveles.setParameter("idEscenario", idEscenario);

        @SuppressWarnings("unchecked")
        List<Object[]> resNiveles = qNiveles.getResultList();

        List<ScenaryValueItemDto> valoresNiveles = new ArrayList<>();
        int totalEvaluaciones = 0;

        for (Object[] fila : resNiveles) {
            Integer cant = ((Number) fila[1]).intValue();
            totalEvaluaciones += cant;
            valoresNiveles.add(new ScenaryValueItemDto((String) fila[0], cant, (String) fila[2]));
        }

        ScenaryContentBlockDto bloqueNiveles = new ScenaryContentBlockDto();
        bloqueNiveles.setName("Niveles de Riesgo " + nombreEscenario);
        bloqueNiveles.setTotal(totalEvaluaciones);
        bloqueNiveles.setValues(valoresNiveles);

        return bloqueNiveles;
    }
    
    @Override
    public ScenaryContentBlockDto obtenerEstadoEvaluacion(String nombreEscenario, Timestamp fechaFrom, Timestamp fechaTo, Integer idEscenario) {
        
        // Query Nativo aplicando la regla de negocio jerárquica para las 3 tipificaciones
    	String sqlEstadosReal = "WITH evaluaciones_tipificadas AS (  "
    			+ "  SELECT  "
    			+ "    CASE  "
    			+ "      WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN 'Remediados'  "
    			+ "      WHEN status = 1 THEN 'Identificados'   "
    			+ "      WHEN status = 0 THEN 'Evaluados'      "
    			+ "      ELSE 'Otros'  "
    			+ "    END AS tipo_estado  "
    			+ "  FROM main.riskevaluations  "
    			+ "  WHERE initialdate >= :fechaFrom "
    			+ "    AND initialdate	 <= :fechaTo  "
    			+ "    AND scenery = :idEscenario  "
    			+ "    AND probability <> 0  "
    			+ "    AND impact <> 0  "
    			+ " ),  "
    			+ " status_colores AS ( "
    			+ "  SELECT  "
    			+ "    json_extract_path_text(elem, 'label') AS estado_label,  "
    			+ "    '#' || json_extract_path_text(elem, 'dsc') AS color_hex   "
    			+ "  FROM main.params,  "
    			+ "  LATERAL json_array_elements(CAST(value AS json)) AS elem  "
    			+ "  WHERE paramname = 'RISK_STATUSCOLORS'  "
    			+ " )  "
    			+ " SELECT  "
    			+ "  et.tipo_estado AS tipo_estado,  "
    			+ "  COUNT(*) AS cantidad, "
    			+ "  COALESCE(sc.color_hex, '#FFFFFF') AS color_hex  "
    			+ " FROM evaluaciones_tipificadas et  "
    			+ " LEFT JOIN status_colores sc ON et.tipo_estado = sc.estado_label  "
    			+ " GROUP BY et.tipo_estado, sc.color_hex  "
    			+ " ORDER BY cantidad DESC";
    			
    /*   String sqlEstadosReal = 
            " WITH evaluaciones_tipificadas AS ( " +
            "  SELECT " +
            "    CASE " +
            "      WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN 'Remediados' " +
            "      WHEN status = 1 THEN 'Identificados' " + // "en progreso"
            "      WHEN status = 0 THEN 'Evaluados' " +     // "cerrados"
            "      ELSE 'Otros' " +
            "    END AS tipo_estado " +
            "  FROM main.riskevaluations " +
            "  WHERE initialdate >= :fechaFrom " +
            "    AND initialdate <= :fechaTo " +
            "    AND scenery = :idEscenario AND probability <> 0 and impact<> 0 " +
            ") " +
            "SELECT tipo_estado, COUNT(*) AS cantidad " +
            "FROM evaluaciones_tipificadas " +
            "GROUP BY tipo_estado " +
            "ORDER BY cantidad DESC";*/

        Query qEstados = entityManager.createNativeQuery(sqlEstadosReal);
        qEstados.setParameter("fechaFrom", fechaFrom);
        qEstados.setParameter("fechaTo", fechaTo);
        qEstados.setParameter("idEscenario", idEscenario);

        @SuppressWarnings("unchecked")
        List<Object[]> resEstados = qEstados.getResultList();

        List<ScenaryValueItemDto> listaEstadosValores = new ArrayList<>();
        int totalEstadosBloque = 0;

        // Primer recorrido para calcular el total exacto de este query
        for (Object[] fila : resEstados) {
            totalEstadosBloque += ((Number) fila[1]).intValue();
        }

        // Segundo recorrido para armar los objetos con el formato de etiqueta: "X Tipificación"
        for (Object[] fila : resEstados) {
            String tipoEstado = (String) fila[0];
            Integer cantidad = ((Number) fila[1]).intValue();
            String Color = (String) fila[2];
            // Construye la etiqueta dinámica requerida por tu JSON ("30 Evaluados", "26 Pendiente por Evaluar", etc.)
            String labelDinamico = cantidad + " " + tipoEstado;
            
            listaEstadosValores.add(new ScenaryValueItemDto(labelDinamico, cantidad,Color));
        }

        // Construcción del bloque estructurado para el JSON final
        ScenaryContentBlockDto bloqueEstados = new ScenaryContentBlockDto();
        bloqueEstados.setName("Estado de evaluación de " + nombreEscenario);
        bloqueEstados.setTotal(totalEstadosBloque); // Sumatoria exacta calculada en caliente
        bloqueEstados.setValues(listaEstadosValores);

        return bloqueEstados;
    }
    
    
    @Override
    public ScenaryContentBlockDto obtenerExposicionOrganizacional(String nombreEscenario, Timestamp fechaFrom, Timestamp fechaTo, Integer idEscenario) {
        
    	String sqlMatriz = " WITH  "
    			+ "             probabilidades AS (  "
    			+ "              SELECT  "
    			+ "                CAST(json_extract_path_text(elem, 'value') AS integer) AS p_val,  "
    			+ "                json_extract_path_text(elem, 'dsc') AS p_lbl  "
    			+ "              FROM main.params,  "
    			+ "              LATERAL json_array_elements(CAST(value AS json)) AS elem  "
    			+ "              WHERE paramname = 'RISK_SCALEPROB'  "
    			+ "             ),  "
    			+ "             impactos AS (  "
    			+ "              SELECT  "
    			+ "                CAST(json_extract_path_text(elem, 'value') AS integer) AS i_val,  "
    			+ "                json_extract_path_text(elem, 'dsc') AS i_lbl  "
    			+ "              FROM main.params,  "
    			+ "              LATERAL json_array_elements(CAST(value AS json)) AS elem  "
    			+ "              WHERE paramname = 'RISK_SCALEIMPACT'  "
    			+ "            ),  "
    			+ "            matriz_base AS (  "
    			+ "              SELECT p.p_val, p.p_lbl, i.i_val, i.i_lbl   "
    			+ "              FROM probabilidades p CROSS JOIN impactos i  "
    			+ "            ),  "
    			+ "            evaluaciones AS (  "
    			+ "              SELECT CAST(ROUND(CAST(CASE  WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN remedyprob  "
    			+ "               ELSE probability  END  AS numeric)) AS integer) AS prob,  "
    			+ "              CAST(ROUND(CAST( CASE   WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN remedyimpact  ELSE impact  "
    			+ "                 END AS numeric)) AS integer) AS imp   "
    			+ "  FROM main.riskevaluations  WHERE initialdate >= :fechaFrom AND initialdate <= :fechaTo AND scenery = :idEscenario   "
    			+ "    AND probability <> 0 AND impact <> 0   "
    			+ "             ),  "
    			+ "             colores AS (  "
    			+ "              SELECT  "
    			+ "                json_extract_path_text(elem, 'value') AS val,  "
    			+ "                '#' || json_extract_path_text(elem, 'dsc') AS color_hex   "
    			+ "              FROM main.params,  "
    			+ "              LATERAL json_array_elements(CAST(value AS json)) AS elem  "
    			+ "              WHERE paramname = 'RISK_COLORSEVERITY'  "
    			+ "             )  "
    			+ "             SELECT   "
    			+ "              mb.p_lbl, mb.i_lbl, COUNT(e.prob) AS cantidad, COALESCE(c.color_hex, '#FFFFFF') AS color_hex  "
    			+ "             FROM matriz_base mb  "
    			+ "             LEFT JOIN evaluaciones e ON mb.p_val = e.prob AND mb.i_val = e.imp  "
    			+ "             LEFT JOIN colores c ON CAST((mb.i_val - 1) * 5 + mb.p_val AS text) = c.val  "
    			+ "             GROUP BY mb.p_val, mb.p_lbl, mb.i_val, mb.i_lbl, c.color_hex  "
    			+ "             ORDER BY mb.p_val DESC, mb.i_val ASC ";
       /* String sqlMatriz = 
            "WITH " +
            " probabilidades AS ( " +
            "  SELECT " +
            "    CAST(json_extract_path_text(elem, 'value') AS integer) AS p_val, " +
            "    json_extract_path_text(elem, 'dsc') AS p_lbl " +
            "  FROM main.params, " +
            "  LATERAL json_array_elements(CAST(value AS json)) AS elem " +
            "  WHERE paramname = 'RISK_SCALEPROB' " +
            " ), " +
            " impactos AS ( " +
            "  SELECT " +
            "    CAST(json_extract_path_text(elem, 'value') AS integer) AS i_val, " +
            "    json_extract_path_text(elem, 'dsc') AS i_lbl " +
            "  FROM main.params, " +
            "  LATERAL json_array_elements(CAST(value AS json)) AS elem " +
            "  WHERE paramname = 'RISK_SCALEIMPACT' " +
            "), " +
            "matriz_base AS ( " +
            "  SELECT p.p_val, p.p_lbl, i.i_val, i.i_lbl  " +
            "  FROM probabilidades p CROSS JOIN impactos i " +
            "), " +
            "evaluaciones AS ( " +
            "  SELECT  " +
            "    CASE WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN remedyprob ELSE probability END AS prob, " +
            "    CASE WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN remedyimpact ELSE impact END AS imp " +
            "  FROM main.riskevaluations " +
            "  WHERE initialdate >= :fechaFrom AND initialdate <= :fechaTo AND scenery = :idEscenario " +
            " AND probability <> 0 and impact<> 0  " +
            " ), " +
            " colores AS ( " +
            "  SELECT " +
            "    json_extract_path_text(elem, 'value') AS val, " +
            "    '#' || json_extract_path_text(elem, 'dsc') AS color_hex  " +
            "  FROM main.params, " +
            "  LATERAL json_array_elements(CAST(value AS json)) AS elem " +
            "  WHERE paramname = 'RISK_COLORSEVERITY' " +
            " ) " +
            " SELECT  " +
            "  mb.p_lbl, mb.i_lbl, COUNT(e.prob) AS cantidad, COALESCE(c.color_hex, '#FFFFFF') AS color_hex " +
            " FROM matriz_base mb " +
            " LEFT JOIN evaluaciones e ON mb.p_val = e.prob AND mb.i_val = e.imp " +
            " LEFT JOIN colores c ON CAST((mb.i_val - 1) * 5 + mb.p_val AS text) = c.val " +
            " GROUP BY mb.p_val, mb.p_lbl, mb.i_val, mb.i_lbl, c.color_hex " +
            " ORDER BY mb.p_val DESC, mb.i_val ASC ";*/

        Query qMatriz = entityManager.createNativeQuery(sqlMatriz);
        qMatriz.setParameter("fechaFrom", fechaFrom);
        qMatriz.setParameter("fechaTo", fechaTo);
        qMatriz.setParameter("idEscenario", idEscenario);

        @SuppressWarnings("unchecked")
        List<Object[]> resMatriz = qMatriz.getResultList();

        List<MatrixRowDto> matrizFilas = new ArrayList<>();
        String probActual = "";
        List<MatrixCellDto> celdasActuales = null;

        for (Object[] fila : resMatriz) {
            String probLabel = (String) fila[0];
            String impLabel = (String) fila[1];
            Integer cantidad = ((Number) fila[2]).intValue();
            String colorHex = (String) fila[3];

            if (!probLabel.equals(probActual)) {
                if (!probActual.isEmpty()) {
                    matrizFilas.add(new MatrixRowDto(probActual, celdasActuales));
                }
                probActual = probLabel;
                celdasActuales = new ArrayList<>();
            }
            celdasActuales.add(new MatrixCellDto(impLabel, cantidad, colorHex));
        }
        
        if (!probActual.isEmpty()) {
            matrizFilas.add(new MatrixRowDto(probActual, celdasActuales));
        }

        ScenaryContentBlockDto bloqueMatriz = new ScenaryContentBlockDto();
        bloqueMatriz.setLabel("Exposición organizacional"); 
        bloqueMatriz.setValues(matrizFilas); 

        return bloqueMatriz;
    }
   /* @Override
    public ScenaryContentBlockDto obtenerExposicionOrganizacional(String nombreEscenario, Timestamp fechaFrom, Timestamp fechaTo, Integer idEscenario) {
        
        String sqlMatriz = 
            "WITH " +
         //   "-- 1. Eje Y: Probabilidades con nombres idénticos al formato solicitado " +
            "probabilidades AS ( " +
            "  SELECT 1 AS p_val, 'Remoto' AS p_lbl UNION ALL " +
            "  SELECT 2, 'Poco Probable' UNION ALL " +
            "  SELECT 3, 'Probable' UNION ALL " +
            "  SELECT 4, 'Muy Probable' UNION ALL " +
            "  SELECT 5, 'Casi Cierto' " +
            "), " +
         //   "-- 2. Eje X: Impactos con nombres idénticos al formato solicitado " +
            "impactos AS ( " +
            "  SELECT 1 AS i_val, 'Muy bajo' AS i_lbl UNION ALL " +
            "  SELECT 2, 'Bajo' UNION ALL " +
            "  SELECT 3, 'Moderado' UNION ALL " +
            "  SELECT 4, 'Severo' UNION ALL " +
            "  SELECT 5, 'Crítico' " +
            "), " +
          //  "-- 3. Matriz Matemática Base de 5x5 " +
            "matriz_base AS ( " +
            "  SELECT p.p_val, p.p_lbl, i.i_val, i.i_lbl  " +
            "  FROM probabilidades p CROSS JOIN impactos i " +
            "), " +
           // "-- 4. Evaluaciones aplicando la regla jerárquica de tipificación corporativa " +
            "evaluaciones AS ( " +
            "  SELECT  " +
            "    CASE WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN remedyprob ELSE probability END AS prob, " +
            "    CASE WHEN remedyprob <> 0 AND remedyimpact <> 0 THEN remedyimpact ELSE impact END AS imp " +
            "  FROM main.riskevaluations " +
            "  WHERE initialdate >= :fechaFrom AND initialdate <= :fechaTo AND scenery = :idEscenario " +
            "), " +
          //  "-- 5. Catálogo de colores dinámicos calculados por posición ordinal " +
            "colores AS ( " +
            "  SELECT json_extract_path_text(elem, 'value') AS val, '#' || json_extract_path_text(elem, 'dsc') AS color_hex  " +
            "  FROM main.params, LATERAL json_array_elements(CAST(value AS json)) AS elem WHERE paramname = 'RISK_COLORSEVERITY' " +
            ") " +
            "SELECT  " +
            "  mb.p_lbl, mb.i_lbl, COUNT(e.prob) AS cantidad, COALESCE(c.color_hex, '#FFFFFF') AS color_hex " +
            "FROM matriz_base mb " +
            "LEFT JOIN evaluaciones e ON mb.p_val = e.prob AND mb.i_val = e.imp " +
            "LEFT JOIN colores c ON CAST((mb.i_val - 1) * 5 + mb.p_val AS text) = c.val " +
            "GROUP BY mb.p_val, mb.p_lbl, mb.i_val, mb.i_lbl, c.color_hex " +
            "ORDER BY mb.p_val DESC, mb.i_val ASC;"; // Mantiene Casi Cierto arriba y Remoto abajo

        Query qMatriz = entityManager.createNativeQuery(sqlMatriz);
        qMatriz.setParameter("fechaFrom", fechaFrom);
        qMatriz.setParameter("fechaTo", fechaTo);
        qMatriz.setParameter("idEscenario", idEscenario);

        @SuppressWarnings("unchecked")
        List<Object[]> resMatriz = qMatriz.getResultList();

        List<MatrixRowDto> matrizFilas = new ArrayList<>();
        String probActual = "";
        List<MatrixCellDto> celdasActuales = null;

        for (Object[] fila : resMatriz) {
            String probLabel = (String) fila[0];
            String impLabel = (String) fila[1];
            Integer cantidad = ((Number) fila[2]).intValue();
            String colorHex = (String) fila[3];

            // Control de ruptura por grupo de Probabilidad (Filas del JSON)
            if (!probLabel.equals(probActual)) {
                if (!probActual.isEmpty()) {
                    matrizFilas.add(new MatrixRowDto(probActual, celdasActuales));
                }
                probActual = probLabel;
                celdasActuales = new ArrayList<>();
            }
            // Celda interna con cantidad calculada (o 0 gracias al LEFT JOIN)
            celdasActuales.add(new MatrixCellDto(impLabel, cantidad, colorHex));
        }
        
        // Adjuntar residuo final
        if (!probActual.isEmpty()) {
            matrizFilas.add(new MatrixRowDto(probActual, celdasActuales));
        }

        // Construcción del bloque mapeando la matriz en el atributo polimórfico "values"
        ScenaryContentBlockDto bloqueMatriz = new ScenaryContentBlockDto();
        
        // Tu JSON objetivo para este bloque usa "label": "Exposición organizacional" en vez de "name"
        bloqueMatriz.setLabel("Exposición organizacional"); 
        bloqueMatriz.setValues(matrizFilas); // Injectamos la estructura de listas anidadas aquí

        return bloqueMatriz;
    }*/
}