package com.dacrt.SBIABackend.service;

import java.math.BigInteger;
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.Appresume2Dto;
import com.dacrt.SBIABackend.dto.AppresumeDto;
import com.dacrt.SBIABackend.dto.Channelresume2Dto;
import com.dacrt.SBIABackend.dto.ChannelresumeDto;
import com.dacrt.SBIABackend.dto.ContinuitySection2Dto;
import com.dacrt.SBIABackend.dto.ContinuitySection3Dto;
import com.dacrt.SBIABackend.dto.RecuperationgapsDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class ContinuitySection3Service {
	Logger logger = LoggerFactory.getLogger(ContinuitySection3Service.class);

	@PersistenceContext
	private EntityManager entityManager;

	public ContinuitySection3Dto getResumeApps(String fecha) {
			
				
		        AppresumeDto detalleAppresumeDto = new AppresumeDto();
		        AppresumeDto detalleAppresumeDto2 = new AppresumeDto();
		        Appresume2Dto detalleAppList = new Appresume2Dto();
				
				ContinuitySection3Dto detalleContinuitySection3Dto = new ContinuitySection3Dto();
				
				detalleAppresumeDto = new AppresumeDto();
				detalleAppList= new Appresume2Dto();
				detalleAppresumeDto2 = new AppresumeDto();
				
				detalleContinuitySection3Dto = new  ContinuitySection3Dto();
				
				//detalleLabelValue = new LabelValueDtoReport();
				List<AppresumeDto> listadoApps=new ArrayList();
				List<AppresumeDto> listadoApps2=new ArrayList();
				
				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));
				
				RespuestaDto respuesta = new RespuestaDto("", false);
				HttpStatus estatus = HttpStatus.FORBIDDEN;
				List<AppresumeDto> listado=new ArrayList();
				try {
					
					Query query;
					String SentenciaBase = "WITH CampanasConRanking AS ( "
							+ "    SELECT "
							+ "        id, "
							+ "        ROW_NUMBER() OVER (ORDER BY dateto DESC) AS ranking_campana "
							+ "    FROM main.campaigns "
							+ "    WHERE DATE_TRUNC('day', dateto) <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz ) "
							+ "	ORDER BY ranking_campana DESC "
							+ "), CantidadProcesosByEvaluacion AS (   "
							+ "    SELECT   "
							+ "        a.applicationid,   "
							+ "        COUNT(a.evalprocessid) AS procesos   "
							+ "    FROM main.evalprocapps a   "
							+ "    INNER JOIN main.evalprocesses eval   "
							+ "        ON eval.id = a.evalprocessid AND eval.appst = 1  "
							+ "    INNER JOIN main.campaignunits cu ON eval.campaignunitid = cu.id "
							+ "    INNER JOIN main.campaigns c ON cu.campaingid = c.id "
							+ "	INNER JOIN main.evalprocimpacts t1 ON eval.id = t1.evalprocessid "
							+ "    INNER JOIN CampanasConRanking cr ON c.id = cr.id "
							+ "    WHERE eval.impactst = 1 AND t1.rtoqty <= 1440  "
							+ "    GROUP BY a.applicationid "
							+ "           "
							+ "),   "
							+ " canales_por_aplicacion AS (   "
							+ "    SELECT a.id,   "
							+ "        COALESCE(array_length(string_to_array(a.channelids, ','), 1), 0) AS canales "
							+ "    FROM   "
							+ "        main.evalprocapps ab   "
							+ "    INNER JOIN main.evalprocesses eval   "
							+ "        ON eval.id = ab.evalprocessid AND eval.appst = 1 AND eval.impactst = 1 "
							+ "		INNER JOIN main.campaignunits cu ON eval.campaignunitid = cu.id "
							+ "    INNER JOIN main.campaigns c ON cu.campaingid = c.id "
							+ "	INNER JOIN main.evalprocimpacts t1 ON eval.id = t1.evalprocessid "
							+ "    INNER JOIN CampanasConRanking cr ON c.id = cr.id "
							+ "    INNER JOIN main.applications a ON a.id = ab.applicationid and a.status=1 "
							+ "	AND t1.rtoqty <= 1440  "
							+ "    GROUP BY a.id  "
							+ "),   "
							+ " RTOsPotencialesPorAplicacion AS (   "
							+ "   SELECT "
							+ "        epa.applicationid, "
							+ "        MIN(ei.rtoqty) AS rtoqty, "
							+ "		cr.ranking_campana "
							+ "    FROM main.evalprocapps epa "
							+ "    JOIN main.evalprocesses ep ON epa.evalprocessid = ep.id "
							+ "    INNER JOIN main.campaignunits cu ON ep.campaignunitid = cu.id "
							+ "    INNER JOIN main.campaigns c ON cu.campaingid = c.id "
							+ "    JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid "
							+ "    INNER JOIN CampanasConRanking cr ON c.id = cr.id "
							+ "    WHERE ep.appst = 1  AND ep.impactst = 1 AND ei.rtoqty <= 1440  "
							+ "    GROUP BY epa.applicationid, cr.ranking_campana "
							+ "),   "
							+ "RTOsMasProximosFinal AS (   "
							+ "    SELECT   "
							+ "        ranked_rto.applicationid,   "
							+ "        ranked_rto.rtoqty AS rtopr  "
							+ "    FROM (   "
							+ "        SELECT   "
							+ "            A.applicationid,   "
							+ "            A.rtoqty,   "
							+ "            ROW_NUMBER() OVER (   "
							+ "                PARTITION BY A.applicationid "
							+ "                ORDER BY A.ranking_campana ASC,    "
							+ "                    a.rtoqty ASC "
							+ "					 "
							+ "            ) AS rn   "
							+ "        FROM RTOsPotencialesPorAplicacion  A  "
							+ "    ) AS ranked_rto   "
							+ "    WHERE rn = 1   "
							+ ")   "
							+ "SELECT   "
							+ "    distinct cpa.id,   "
							+ "    a.name,   "
							+ "    cpa.canales,   "
							+ "    cpea.procesos,   "
							+ "    pr.descr AS tipo,   "
							+ "    a.recuperation,   "
							+ "    RMPF.rtopr "
							+ "FROM main.applications a   "
							+ "INNER JOIN canales_por_aplicacion cpa ON cpa.id = a.id and a.status=1 "
							+ "INNER JOIN CantidadProcesosByEvaluacion cpea ON cpea.applicationid = a.id   "
							+ "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 = 'APPS_TYPE'   "
							+ ") pr ON a.apptypepar = pr.valor   "
							+ "INNER JOIN RTOsMasProximosFinal RMPF ON RMPF.applicationid = a.id   "
							+ "ORDER BY RMPF.rtopr";
			
					
					System.out.println(SentenciaBase);
					query = entityManager.createNativeQuery(SentenciaBase);
					List<Object[]> listacompleta = query.getResultList();
					
				
					for (Object[] reg : listacompleta) {
						detalleAppresumeDto.setApplication((String) reg[1]);
						detalleAppresumeDto.setChannels((int)reg[2]);
						detalleAppresumeDto.setProcesses(BigInteger.valueOf(((Number) reg[3]).longValue()));
						detalleAppresumeDto.setRealrto(BigInteger.valueOf(((Number) reg[5]).longValue()) +" min ");
						detalleAppresumeDto.setRto(BigInteger.valueOf(((Number) reg[6]).longValue())+" min ");
						detalleAppresumeDto.setType((String) reg[4]);
						
						listadoApps.add(detalleAppresumeDto);
						detalleAppresumeDto = new AppresumeDto();
					}
					
					detalleAppList.setAppresume(listadoApps);
					
					detalleContinuitySection3Dto.setTitle("Resumen de Aplicaciones hasta 24 Horas");
					detalleContinuitySection3Dto.setContent(detalleAppList);
					return detalleContinuitySection3Dto;
					
				}catch (Exception e) {
					// Manejo de excepciones
					logger.error("Error al obtener la Section 3: {}", e.getMessage(), e);
					
					return detalleContinuitySection3Dto;
				} finally {
					if (entityManager != null && entityManager.isOpen()) {
						entityManager.close();
					}
				}

		 	}

}