package com.dacrt.SBIABackend.service;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;

import com.dacrt.SBIABackend.dto.GlobalSectionDto;
import com.dacrt.SBIABackend.dto.RowsDto;
import com.dacrt.SBIABackend.dto.TableDto;
import com.dacrt.SBIABackend.dto.TableRaizDTO;
import com.dacrt.SBIABackend.dto.ValueDto2;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;
@Service
public class CriticidadEscenariosService {
Logger logger = LoggerFactory.getLogger(CriticidadEscenariosService.class);
	
	@PersistenceContext
	private EntityManager entityManager;
	
	public TableRaizDTO getCriticidadEscenarios(LocalDate from,LocalDate to, Integer TipoEscenario) throws ParseException {
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		long cuantosregistro;
		TableRaizDTO detalle = new TableRaizDTO();
		List<ValueDto2> headerList = new ArrayList();
		List<RowsDto> rowsList = new ArrayList();
		ValueDto2 value= new ValueDto2();
		RowsDto rows = new RowsDto();
		TableDto tableDto = new TableDto();
		List<ValueDto2> cells= new ArrayList();
		String escenario="";
		LocalDateTime inicioDia = from.atStartOfDay(); // 2026-06-01 00:00:00
		LocalDateTime finDia = to.atTime(LocalTime.MAX);
	try {
		String SentenciaBase = " WITH config AS ( "
				+ "	SELECT CAST(jsonb_array_length(CAST(value AS jsonb)) AS INTEGER) AS total_scale "
				+ "    FROM main.params         "
				+ "    WHERE paramname = 'RISK_SCALEPROB' "
				+ "), "
				+ "evaluaciones_calculadas AS ( "
				+ "    SELECT  "
				+ "        E.relateditemdsc, "
				+ "        E.scenerydsc,    "
				+ "	    E.impact, "
				+ "	    E.probability, "
				+ "	    CASE  "
				+ "            WHEN COALESCE(E.remedyimpact, 0) != 0 AND COALESCE(E.remedyprob, 0) != 0 THEN "
				+ "                ABS(((ROUND(E.remedyimpact, 0) - 1) * c.total_scale) + ROUND(E.remedyprob, 0)) "
				+ "       "
				+ "            WHEN COALESCE(E.impact, 0) != 0 AND COALESCE(E.probability, 0) != 0 THEN "
				+ "                ABS(((ROUND(E.impact, 0) - 1) * c.total_scale) + ROUND(E.probability, 0)) "
				+ "        END AS riesgo_id    "
				+ " FROM   main.riskevaluations E   "
				+ "    CROSS JOIN config c "
				+ "    WHERE E.impact IS NOT NULL AND E.impact != 0  "
				+ "      AND E.probability IS NOT NULL AND E.probability != 0 "
				+ "      AND E.scenery = ?3   "
				+ " AND E.initialdate BETWEEN CAST(?1 AS timestamptz) AND CAST(?2 AS timestamptz)   "
				+ "), "
				+ "severidades AS ( "
				+ "    SELECT  "
				+ "        CAST(elemento ->> 'value' AS INTEGER) AS valor, "
				+ "        elemento ->> 'dsc' AS descr "
				+ "    FROM main.params p,  "
				+ "    LATERAL jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento        "
				+ "  WHERE p.paramname = 'RISK_SEVERITYLEVEL'     "
				+ "), "
				+ "orden_niveles AS ( "
				+ "    SELECT  "
				+ "        CAST(nivel ->> 'value' AS INTEGER) AS nivel_numerico, "
				+ "        nivel ->> 'dsc' AS nivel_descripcion "
				+ "    FROM main.params p, "
				+ "    LATERAL jsonb_array_elements(CAST(p.value AS jsonb)) AS nivel "
				+ "    WHERE p.paramname = 'RISK_LEVELSORDER' "
				+ ") "
				+ "SELECT  "
				+ "    ec.relateditemdsc AS escenario,    "
				+ "    sev.descr AS riesgo,    "
				+ "    sev.valor AS val,   "
				+ "    ec.scenerydsc    "
				+ "FROM evaluaciones_calculadas ec    "
				+ "LEFT JOIN severidades sev ON ec.riesgo_id = sev.valor   "
				+ "LEFT JOIN orden_niveles o ON sev.descr = o.nivel_descripcion "
				+ "WHERE (ec.impact != 0 AND ec.impact IS NOT NULL)    "
				+ "  AND (ec.probability != 0 AND ec.probability IS NOT NULL)   "
				+ "GROUP BY ec.relateditemdsc, ec.scenerydsc, sev.descr, sev.valor, o.nivel_numerico    "
				+ "ORDER BY o.nivel_numerico DESC, ec.relateditemdsc ASC";

		    
		    Query query = entityManager.createNativeQuery(SentenciaBase);
		    query.setParameter(1, inicioDia);
		    query.setParameter(2, finDia);
		    query.setParameter(3, TipoEscenario);
            
		    
		    List<Object[]> listacompleta = query.getResultList();           
			int contador=1;
			
            for (Object[] fila : listacompleta) {
            	if (contador==1) {
            		value.setId(1);
        		    value.setValue((String)fila[3]);
        		    headerList.add(value);
        		    
        		    value= new ValueDto2();
        		    value.setId(2);
        		    value.setValue("Riesgo");
        		    headerList.add(value);
            	}
            	
            	value= new ValueDto2();
            	
            	rows.setId(contador);
            	value.setId(1);
            	value.setValue((String)fila[0]);
            	cells.add(value);
            	
            	value= new ValueDto2();
            	value.setId(2);
            	value.setValue((String)fila[1]);
            	cells.add(value);
            	
            	value= new ValueDto2();
            	
            	rows.setCols(cells);
            	rowsList.add(rows);
            	rows = new RowsDto();
            	contador++;
            	cells= new ArrayList();
            	rows = new RowsDto();
            }
            

            detalle.setHeader(headerList);
            detalle.setRows(rowsList);
			
		    return  detalle;	    	 
	    }catch (Exception e) {
			respuesta = new RespuestaDto("Error interno del servidor: "+e.getMessage(), false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return detalle;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}					
					
	 	}
}