package com.dacrt.SBIABackend.service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Comparator; // Necesario si no garantizas el orden en la DB
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.LabelItemDtoReport;
import com.dacrt.SBIABackend.dto.LabelItemDtoReport2;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport2;
// import com.dacrt.SBIABackend.dto.QtyDto; // No usado en esta lógica, puedes quitarlo si no lo necesitas
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class AppteststatusService {

	Logger logger = LoggerFactory.getLogger(AppteststatusService.class);

	@PersistenceContext
	private EntityManager entityManager;

	public List<LabelItemDtoReport2> getAppeststatus(String fecha) {
		Query query;
		// RespuestaDto respuesta = new RespuestaDto("", false); // No se usa si el método devuelve List<LabelItemDtoReport>
		// HttpStatus estatus = HttpStatus.FORBIDDEN; // No se usa si el método devuelve List<LabelItemDtoReport>
		List<LabelItemDtoReport2> finalReportList = new ArrayList<>(); // Lista que contendrá el resultado final
		
		String fecha2=fecha;
		int ano=0,mes=0,dia=0;
		
		ano=Integer.parseInt(fecha2.substring(0, 4));
		mes=Integer.parseInt(fecha2.substring(4, 6));
		dia=Integer.parseInt(fecha2.substring(6, 8));

		try {
		
			
			/*String SentenciaBase = " select EPR.TipoPrueba,EPR.ResultadoPrueba, "
					+ "	CAST(sum(EPR.TotalRegistros) AS INTEGER )   "
					+ "	from ( SELECT "
					+ "    TodaCombinacion.TipoPrueba, "
					+ "    TodaCombinacion.ResultadoPrueba, "
					+ "	 CASE "
					+ "            WHEN (FiltroDataAgregada.TotalRegistros >= 1) THEN 1 "
					+ "            ELSE 0 "
					+ "        END AS TotalRegistros "
					+ "FROM "
					+ "    ( "
					+ "        SELECT "
					+ "            TTP.elem->>'dsc' AS TipoPrueba, "
					+ "            TRP.elem->>'dsc' AS ResultadoPrueba "
					+ "        FROM "
					+ "            ( "
					+ "                SELECT value FROM main.params WHERE paramname='APPS_TESTTYPE' "
					+ "            ) AS P1, "
					+ "            jsonb_array_elements(CAST(P1.value AS jsonb)) AS TTP(elem) "
					+ "        CROSS JOIN "
					+ "            ( "
					+ "                SELECT value FROM main.params WHERE paramname='APPS_TESTRESULT' "
					+ "            ) AS P2, "
					+ "            jsonb_array_elements(CAST(P2.value AS jsonb)) AS TRP(elem) "
					+ "    ) AS TodaCombinacion "
					+ "LEFT JOIN "
					+ "    ( "
					+ "         "
					+ "        SELECT "
					+ "            (SELECT elem->>'dsc' FROM jsonb_array_elements(CAST(P_inner.value AS jsonb)) AS elem WHERE elem->>'value' = CAST(a.drptesttypepar AS TEXT)) AS TipoPrueba, "
					+ "            (SELECT elem->>'dsc' FROM jsonb_array_elements(CAST(P2_inner.value AS jsonb)) AS elem WHERE elem->>'value' = CAST(a.drptestresultpar AS TEXT)) AS ResultadoPrueba, "
					+ "            CAST(COUNT(a.id) as integer) AS TotalRegistros "
					+ "	      "
					+ "        FROM main.applications a "
					+ "        JOIN main.evalprocapps EPA ON a.id = EPA.applicationid "
					+ "        JOIN main.evalprocesses EP ON EPA.evalprocessid = EP.id "
					+ "        JOIN main.campaignunits CU ON EP.campaignunitid = CU.id "
					+ "        JOIN main.evalprocimpacts EI ON EP.id = EI.evalprocessid, "
					+ "            (SELECT value FROM main.params WHERE paramname='APPS_TESTTYPE') AS P_inner, "
					+ "            (SELECT value FROM main.params WHERE paramname='APPS_TESTRESULT') AS P2_inner "
					+ "        WHERE "
					+ "            a.drptestdate <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD') "
					+ "            AND EP.createdat <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD') "
					+ "            AND EI.rtoqty <= 1440 "
					+ "            AND EI.rtoqty <> 0  "
					+ "            AND EI.rtoqty = ( "
					+ "                SELECT MIN(EI2.rtoqty) FROM main.evalprocimpacts EI2 "
					+ "                JOIN main.evalprocesses EP2 ON EI2.evalprocessid = EP2.id "
					+ "                JOIN main.campaignunits CU2 ON EP2.campaignunitid = CU2.id "
					+ "                JOIN main.evalprocapps EPA2 ON EP2.id = EPA2.evalprocessid "
					+ "                WHERE EPA2.applicationid = a.id "
					+ "                AND CU2.campaingid = ( "
					+ "                    SELECT CU_closest.campaingid "
					+ "                    FROM main.campaignunits CU_closest "
					+ "                    JOIN main.evalprocesses EP_closest ON CU_closest.id = EP_closest.campaignunitid "
					+ "                    JOIN main.evalprocapps EPA_closest ON EP_closest.id = EPA_closest.evalprocessid "
					+ "                    JOIN main.campaigns C_closest ON CU_closest.campaingid = C_closest.id "
					+ "                    WHERE EPA_closest.applicationid = a.id "
					+ "                     "
					+ "	                ORDER BY ABS(EXTRACT(DAY FROM (C_closest.dateto - TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD'))))  "
					+ "                    LIMIT 1 "
					+ "                ) "
					+ "            ) "
					+ "        GROUP BY TipoPrueba, ResultadoPrueba,EI.rtoqty ,a.name "
					+ "    ) AS FiltroDataAgregada ON TodaCombinacion.TipoPrueba = FiltroDataAgregada.TipoPrueba AND TodaCombinacion.ResultadoPrueba = FiltroDataAgregada.ResultadoPrueba "
					+ " ORDER BY "
					+ "    TodaCombinacion.TipoPrueba, "
					+ "    TodaCombinacion.ResultadoPrueba) as EPR "
					+ " group by TipoPrueba,ResultadoPrueba ";*/
			   //CAST(EP.createdat AS date) <= '2025-07-24'::date "
			String SentenciaBase = " WITH ParamMaps AS ( "
					+ "    SELECT elem->>'value' AS value, elem->>'dsc' AS descr, paramname "
					+ "    FROM main.params, jsonb_array_elements(CAST(value AS jsonb)) AS elem "
					+ "    WHERE paramname IN ('APPS_TESTTYPE', 'APPS_TESTRESULT') "
					+ "), "
					+ "FilteredApplications AS ( "
					+ "    SELECT "
					+ "        a.id AS idApplication, "
					+ "        CAST(a.drptesttypepar AS TEXT) AS drptesttypepar_val, "
					+ "        CAST(a.drptestresultpar AS TEXT) AS drptestresultpar_val, "
					+ "        a.drptestedpar "
					+ "    FROM main.applications a "
					+ "    JOIN main.evalprocapps EPA ON a.id = EPA.applicationid "
					+ "    JOIN main.evalprocesses EP ON EP.id = EPA.evalprocessid "
					+ "    JOIN main.campaignunits CU ON EP.campaignunitid = CU.id "
					+ "    JOIN main.evalprocimpacts EI ON EP.id = EI.evalprocessid "
					+ "    JOIN main.campaigns C ON CU.campaingid = C.id "
					+ "    WHERE EP.appst =1 AND "
					+ "        CAST(EP.createdat AS date) <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD') "
					+ "        AND EI.rtoqty <= 1440 "
					+ "  AND (CAST(a.drptestdate as date)  <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD') OR a.drptestdate IS NULL)"
					+ "        AND C.Dateto = ( "
					+ "            SELECT "
					+ "                MAX(C2.Dateto) "
					+ "            FROM main.campaigns C2 "
					+ "            JOIN main.campaignunits CU2 ON C2.id = CU2.campaingid "
					+ "            JOIN main.evalprocesses EP2 ON CU2.id = EP2.campaignunitid "
					+ "            JOIN main.evalprocapps EPA2 ON EP2.id = EPA2.evalprocessid "
					+ "            WHERE EPA2.applicationid = a.id "
					+ "              AND CAST(C2.datefrom AS date) <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD') "
					+ "        ) "
					+ "), "
					+ "ActualApplicationCounts AS ( "
					+ "    SELECT "
					+ "        CASE "
					+ "            WHEN fa.drptestedpar = 1 THEN TTP.descr "
					+ "            WHEN fa.drptestedpar = 2 THEN 'Sin Tipo de Prueba' "
					+ "            ELSE 'Desconocido' "
					+ "        END AS TipoPrueba, "
					+ "        CASE "
					+ "            WHEN fa.drptestedpar = 1 AND fa.drptestresultpar_val IS NOT NULL THEN TRP.descr "
					+ "            WHEN fa.drptestedpar = 1 AND fa.drptestresultpar_val IS NULL THEN 'Sin Resultado' "
					+ "            WHEN fa.drptestedpar = 2 AND fa.drptestresultpar_val IS NOT NULL THEN TRP.descr "
					+ "            WHEN fa.drptestedpar = 2 AND fa.drptestresultpar_val IS NULL THEN 'Sin Resultado' "
					+ "            ELSE 'Desconocido' "
					+ "        END AS ResultadoPrueba, "
					+ "        COUNT(DISTINCT fa.idApplication) AS TotalRegistros "
					+ "    FROM FilteredApplications fa "
					+ "    LEFT JOIN ParamMaps TTP ON TTP.paramname = 'APPS_TESTTYPE' AND TTP.value = fa.drptesttypepar_val "
					+ "    LEFT JOIN ParamMaps TRP ON TRP.paramname = 'APPS_TESTRESULT' AND TRP.value = fa.drptestresultpar_val "
					+ "    GROUP BY "
					+ "        CASE WHEN fa.drptestedpar = 1 THEN TTP.descr WHEN fa.drptestedpar = 2 THEN 'Sin Tipo de Prueba' ELSE 'Desconocido' END, "
					+ "        CASE WHEN fa.drptestedpar = 1 AND fa.drptestresultpar_val IS NOT NULL THEN TRP.descr WHEN fa.drptestedpar = 1 AND fa.drptestresultpar_val IS NULL THEN 'Sin Resultado' WHEN fa.drptestedpar = 2 AND fa.drptestresultpar_val IS NOT NULL THEN TRP.descr WHEN fa.drptestedpar = 2 AND fa.drptestresultpar_val IS NULL THEN 'Sin Resultado' ELSE 'Desconocido' END "
					+ "), "
					+ "AllDesiredCombinations AS ( "
					+ "    SELECT "
					+ "        P1.descr AS TipoPrueba, "
					+ "        P2.descr AS ResultadoPrueba "
					+ "    FROM ParamMaps P1 "
					+ "    CROSS JOIN ParamMaps P2 "
					+ "    WHERE P1.paramname = 'APPS_TESTTYPE' AND P2.paramname = 'APPS_TESTRESULT' "
					+ " "
					+ "    UNION ALL "
					+ " "
					+ "    SELECT "
					+ "        P1.descr AS TipoPrueba, "
					+ "        'Sin Resultado' AS ResultadoPrueba "
					+ "    FROM ParamMaps P1 "
					+ "    WHERE P1.paramname = 'APPS_TESTTYPE' AND P1.descr IN ('Unitaria', 'General', 'Full Day') "
					+ " "
					+ "    UNION ALL "
					+ " "
					+ "    SELECT "
					+ "        'Sin Tipo de Prueba' AS TipoPrueba, "
					+ "        P2.descr AS ResultadoPrueba "
					+ "    FROM ParamMaps P2 "
					+ "    WHERE P2.paramname = 'APPS_TESTRESULT' "
					+ " "
					+ "    UNION ALL "
					+ " "
					+ "    SELECT 'Sin Tipo de Prueba' AS TipoPrueba, 'Sin Resultado' AS ResultadoPrueba "
					+ "), "
					+ "FinalReportData AS ( "
					+ "    SELECT "
					+ "        ADC.TipoPrueba, "
					+ "        ADC.ResultadoPrueba, "
					+ "        COALESCE(AAC.TotalRegistros, 0) AS TotalRegistros "
					+ "    FROM AllDesiredCombinations ADC "
					+ "    LEFT JOIN ActualApplicationCounts AAC "
					+ "        ON ADC.TipoPrueba = AAC.TipoPrueba AND ADC.ResultadoPrueba = AAC.ResultadoPrueba "
					+ ") "
					+ "SELECT "
					+ "    TipoPrueba, "
					+ "    ResultadoPrueba, "
					+ "    TotalRegistros, "
					+ " COALESCE( "
					+ "        ROUND( "
					+ "            (CAST(TotalRegistros AS NUMERIC) / NULLIF(SUM(TotalRegistros) OVER (), 0)) * 100, "
					+ "            2 "
					+ "        ), "
					+ "        0.00  "
					+ "    ) AS Porcentaje "
					+ " FROM FinalReportData "
					+ "ORDER BY "
					+ "    CASE "
					+ "        WHEN TipoPrueba = 'Unitaria' THEN 1 "
					+ "        WHEN TipoPrueba = 'General' THEN 2 "
					+ "        WHEN TipoPrueba = 'Full Day' THEN 3 "
					+ "        WHEN TipoPrueba = 'Sin Tipo de Prueba' THEN 4 "
					+ "        ELSE 99 "
					+ "    END, "
					+ "    CASE "
					+ "        WHEN ResultadoPrueba = 'Exitoso' THEN 1 "
					+ "        WHEN ResultadoPrueba = 'Parcial' THEN 2 "
					+ "        WHEN ResultadoPrueba = 'Fallido' THEN 3 "
					+ "        WHEN ResultadoPrueba = 'Sin Resultado' THEN 4 "
					+ "        ELSE 99 "
					+ "    END ";

			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();

			// Lógica para procesar y agrupar los datos
			LabelItemDtoReport2 currentGroup = null; // Para mantener el objeto principal actual
			String lastTipoPruebaProcessed = null; // Para comparar con el tipo de prueba de la iteración anterior

			for (Object[] reg : listacompleta) {
				String tipoPruebaActual = (String) reg[0];      // El "label" del objeto principal (e.g., "Unitaria")
				String resultadoPrueba = (String) reg[1]; // El "label" del item interno (e.g., "Tipo de Prueba")
				BigDecimal totalRegistros = (BigDecimal) reg[3]; // El "value" del item interno (e.g., 47)
                                                             // Usamos Integer para manejar nulls si COUNT retorna 0

				// Si el tipo de prueba actual es diferente al del grupo que estamos construyendo,
				// o si es el primer elemento de la lista (currentGroup es null)
				if (currentGroup == null || !tipoPruebaActual.equals(lastTipoPruebaProcessed)) {
					// Guardamos el grupo anterior (si existe) y creamos uno nuevo
					currentGroup = new LabelItemDtoReport2();
					currentGroup.setLabel(tipoPruebaActual);
					finalReportList.add(currentGroup); // Añadimos el nuevo grupo a la lista final
				}

				// Creamos el objeto de detalle (label y value)
				LabelValueDtoReport2 detailItem = new LabelValueDtoReport2();
				detailItem.setLabel(resultadoPrueba);
				//detailItem.setValue(totalRegistros != null ? totalRegistros : 0); // Manejo de posible null para evitar NPE
				detailItem.setValue(totalRegistros != null ? totalRegistros : BigDecimal.ZERO);


				// Añadimos el detalle al grupo actual
				// Asegúrate de que LabelItemDtoReport tenga un método addItem() o que items esté inicializado
				if (currentGroup.getItems() == null) {
					currentGroup.setItems(new ArrayList<>());
				}
				currentGroup.getItems().add(detailItem);

				// Actualizamos el tipo de prueba procesado para la siguiente iteración
				lastTipoPruebaProcessed = tipoPruebaActual;
			}

			return finalReportList; // Retornamos la lista final de DTOs

		} catch (Exception e) {
			logger.error("Error al obtener la cobertura BCP: {}", e.getMessage(), e);
			// Si ocurre un error, retornar una lista vacía o lanzar una excepción personalizada
			return new ArrayList<>();
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}
	}
}