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 DetalleEvaluacionEscenarioService {
Logger logger = LoggerFactory.getLogger(DetalleEvaluacionEscenarioService.class);
	
	@PersistenceContext
	private EntityManager entityManager;

	public DetalleEvaluacionEscenarioService() {
		super();
	}

	public DetalleEvaluacionEscenarioService(Logger logger, EntityManager entityManager) {
		super();
		this.logger = logger;
		this.entityManager = entityManager;
	}
	
	public TableRaizDTO getDetalleEvaluacionEscenario(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 = " SELECT ROW_NUMBER() OVER(ORDER BY relateditemdsc, scenery, scenerydsc, pr.descr,modifiedat) as num_fila,"
				+ "              scenery, "
				+ "              scenerydsc, "
				+ "              relateditemdsc as escenario,       "
				+ "   CASE      "
				+ "   WHEN COUNT(CASE WHEN tiene_seguimiento = 1 AND mitigation IS NULL THEN 1 END) = 0  AND COUNT(CASE WHEN tiene_seguimiento = 1 THEN 1 END) > 0 THEN 'Cerrado'      "
				+ "     WHEN MAX(progreso_accion) = 100 AND COUNT(CASE WHEN mitigation IS NULL THEN 1 END) > 0 THEN 'Por Mitigar'      "
				+ "     ELSE 'En Proceso'      "
				+ "   END estado,  "
				+ "   pr.descr as riesgo,"
				+ "   modifiedat  "
				+ "FROM          "
				+ "(          "
				+ " SELECT E.relateditemdsc,"
				+ "        E.scenery, "
				+ "        E.scenerydsc,        "
				+ "        FL.mitigation as mitigation,      "
				+ "        FLA.progress as progreso_accion,      "
				+ "        CASE WHEN FL.id IS NOT NULL THEN 1 ELSE 0 END as tiene_seguimiento,  "
				+ "        CASE   "
				+ "				WHEN COALESCE(E.remedyimpact, 0) != 0 AND COALESCE(E.remedyprob, 0) != 0 THEN  "
				+ "				     ABS(((ROUND(E.remedyimpact, 0) - 1) * t.total) + ROUND(E.remedyprob, 0))  "
				+ "				WHEN COALESCE(E.impact, 0) != 0 AND COALESCE(E.probability, 0) != 0 THEN  "
				+ "        ABS(((ROUND(E.impact, 0) - 1) * t.total) + ROUND(E.probability, 0))     "
				+ "		   END AS riesgo,"
				+ "        E.modifiedat  "
				+ " FROM   main.riskevaluations E   "
				+ " CROSS JOIN (     "
				+ "        SELECT CAST(jsonb_array_length(CAST(value AS jsonb)) AS INTEGER) AS total       "
				+ "        FROM main.params       "
				+ "        WHERE paramname = 'RISK_SCALEPROB'     "
				+ " ) t  "
				+ " INNER JOIN  main.riskevalfactors F          "
				+ "       ON  E.id=F.riskevaluationid AND E.scenery=?3  "
				+ " AND E.initialdate BETWEEN CAST(?1 AS timestamptz) AND CAST(?2 AS timestamptz)   "
				+ " INNER JOIN  main.riskevalfactorelements EL          "
				+ "     ON  F.id=EL.riskevalfactorid        "
				+ "     LEFT JOIN  main.riskfollowups FL          "
				+ "     ON  EL.id=FL.riskevalfactorelementid          "
				+ "     LEFT JOIN   (SELECT riskfollowupid id, MAX(progress) progress FROM main.riskfollowupactions GROUP BY riskfollowupid) FLA          "
				+ "     ON          FL.id=FLA.id          "
				+ ") E  "
				+ "LEFT JOIN     "
				+ " (SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor     "
				+ "  FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento     "
				+ "  WHERE p.paramname = 'RISK_SEVERITYLEVEL'     "
				+ "  ) pr ON riesgo = CAST(pr.valor AS integer)   "
				+ "GROUP BY  relateditemdsc,scenery,scenerydsc,pr.descr,modifiedat ";
		    
		/*String SentenciaBase = "WITH config AS ( "
			    + "    SELECT CAST(jsonb_array_length(CAST(value AS jsonb)) AS INTEGER) AS total "
			    + "    FROM main.params "
			    + "    WHERE paramname = 'RISK_SCALEPROB' "
			    + "), "
			    + "evaluaciones AS ( "
			    + "    SELECT "
			    + "        E.relateditemdsc, "
			    + "        E.scenery, "
			    + "        E.scenerydsc, "
			    + "        E.initialdate, " 
			    + "        E.modifiedat, "
			    + "        CASE "
			    + "            WHEN E.status = 0 AND COALESCE(E.remedyprob, 0) != 0 AND COALESCE(E.remedyimpact, 0) != 0 "
			    + "            THEN ABS(((ROUND(COALESCE(E.remedyimpact, 0), 0) - 1) * t.total) + ROUND(COALESCE(E.remedyprob, 0), 0)) "
			    + "            ELSE ABS(((ROUND(COALESCE(E.impact, 0), 0) - 1) * t.total) + ROUND(COALESCE(E.probability, 0), 0)) "
			    + "        END AS riesgo, "
			    + "        CASE "
			    + "            WHEN E.status = 1 THEN 'Identificados' "
			    + "            WHEN E.status = 0 AND COALESCE(E.remedyprob, 0) != 0 AND COALESCE(E.remedyimpact, 0) != 0 THEN 'Remediados' "
			    + "            ELSE 'Evaluados' " 
			    + "        END as estado "
			    + "    FROM main.riskevaluations E "
			    + "    CROSS JOIN config t "
			    + "    WHERE COALESCE(E.impact, 0) != 0 AND COALESCE(E.probability, 0) != 0 "
			    + ") "
			    + "SELECT "
			    + "    ROW_NUMBER() OVER(ORDER BY ev.relateditemdsc, ev.scenery, ev.scenerydsc, pr.descr, ev.modifiedat, ev.estado) as num_fila, "
			    + "    ev.relateditemdsc, "
			    + "    ev.scenery, "
			    + "    ev.scenerydsc, "
			    + "    pr.descr, "
			    + "    ev.modifiedat, "
			    + "    ev.estado "
			    + "FROM evaluaciones ev "
			    + "LEFT JOIN ( "
			    + "    SELECT elemento ->> 'dsc' AS descr, CAST(elemento ->> 'value' AS INTEGER) AS valor "
			    + "    FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento "
			    + "    WHERE p.paramname = 'RISK_SEVERITYLEVEL' "
			    + ") pr ON ev.riesgo = pr.valor "
			    + "WHERE ev.scenery = ?3 "
			    + "  AND ev.initialdate BETWEEN CAST(?1 AS timestamptz) AND CAST(?2 AS timestamptz)";*/
		   
		    Query query = entityManager.createNativeQuery(SentenciaBase);
		    
		    // 4. Pasamos los parámetros como String. El motor de Postgres se encargará del resto.
		    query.setParameter(1, inicioDia);
		    query.setParameter(2, finDia);
		    query.setParameter(3, TipoEscenario);
            
		    List<Object[]> listacompleta = query.getResultList();
		    java.sql.Timestamp ts;
    	    
    	    java.text.SimpleDateFormat formateador = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
           
			
            for (Object[] fila : listacompleta) {
            	if (((Number) fila[0]).intValue()==1) {
            		escenario=(String)fila[2];
            		value.setId(1);
         		    value.setValue(escenario);
         		    headerList.add(value);
         		    
         		    value= new ValueDto2();
         		    value.setId(2);
         		    value.setValue("Estado");
         		    headerList.add(value);
         		    
         		    value= new ValueDto2();
         		    value.setId(3);
         		    value.setValue("Riesgo");
         		    headerList.add(value);
         		    
         		    value= new ValueDto2();
        		    value.setId(4);
        		    value.setValue("Evaluación");
        		    headerList.add(value);
         		    
         		    value= new ValueDto2();
            	}

            	rows.setId(((Number) fila[0]).intValue());
            	value.setId(1);
            	value.setValue(String.valueOf(fila[3]));
            	cells.add(value);
            	value= new ValueDto2();
            	
            	
            	value.setId(2);
            	value.setValue((String)fila[4]);
            	cells.add(value);
            	value = new ValueDto2();
            	rows.setCols(cells);
            	
            	value.setId(3);
            	value.setValue((String)fila[5]);
            	cells.add(value);
            	value= new ValueDto2();
            	
            	value.setId(4);
            	if (fila[6] != null) {
            		value.setValue(fila[6] instanceof java.sql.Timestamp ? new java.text.SimpleDateFormat("dd/MM/yyyy").format((java.sql.Timestamp) fila[6]) : String.valueOf(fila[6]));
                } else {
                    value.setValue("");
                }
            	/*if (fila[5] != null) {
            	    ts = (java.sql.Timestamp) fila[5];
            	    formateador = new java.text.SimpleDateFormat("dd/MM/yyyy");
            	    value.setValue(formateador.format(ts));
            	} */  
            	cells.add(value);
            	value = new ValueDto2();
            	rows.setCols(cells);
            	
            	rowsList.add(rows);
            	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();
			}
		}					
					
	 	}
}