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 javax.transaction.Transactional;

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

import com.dacrt.SBIABackend.dto.DrpcoverageDto;
import com.dacrt.SBIABackend.dto.ImpactAppsresumeDto;
import com.dacrt.SBIABackend.dto.ImpactChannelresumeDto;
import com.dacrt.SBIABackend.dto.ImpactContentAppReportDto;
import com.dacrt.SBIABackend.dto.ImpactContentReportDto;
import com.dacrt.SBIABackend.dto.ImpactReportScoperesumeDto;
import com.dacrt.SBIABackend.dto.ImpactReportSection3Dto;
import com.dacrt.SBIABackend.dto.ImpactReportSection4Dto;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
@Transactional
public class ImpactReportSection4AppsService {
	
Logger logger = LoggerFactory.getLogger(ImpactReportSection4AppsService.class);
	
	@PersistenceContext
	private EntityManager entityManager;

   public ImpactReportSection4Dto getImpactResumeChannels(int idcampana,String contentin) {
		
		List<ImpactAppsresumeDto> listaImpactAppsresumeDto=new ArrayList();
		
		ImpactReportSection4Dto detalleImpactReportSection4Dto = new ImpactReportSection4Dto();
		ImpactContentAppReportDto detalleImpactContentAppReportDto = new ImpactContentAppReportDto();
		ImpactAppsresumeDto detalleImpactAppsresumeDto = new ImpactAppsresumeDto();
		ImpactAppsresumeDto detalleImpactAppsresumeDto2 = new ImpactAppsresumeDto();
		
		Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		
		try {						
			
			String SentenciaBase = "WITH CantidadProcesosByEvaluacion AS (     "
					+ "	SELECT a.applicationid,     "
					+ "		   COUNT(a.evalprocessid) AS procesos     "
					+ "		   FROM main.evalprocapps a     "
					+ "		   INNER JOIN main.evalprocesses eval     "
					+ "		   ON eval.id = a.evalprocessid     "
					+ "		   INNER JOIN main.campaignunits cu ON eval.campaignunitid = cu.id   "
					+ "		   INNER JOIN main.campaigns c ON cu.campaingid = c.id AND c.id=:idcampana "
					+ "		   INNER JOIN main.evalprocimpacts t1 ON eval.id = t1.evalprocessid   "
					+ "		   WHERE eval.impactst = 1 AND eval.appst = 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 AND c.id=:idcampana  "
					+ "		INNER JOIN main.evalprocimpacts t1 ON eval.id = t1.evalprocessid    "
					+ "		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,   "
					+ "				a.name, "
					+ "				MIN(ei.rtoqty) AS rtoqty "
					+ "		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  AND c.id=:idcampana "
					+ "		JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid    "
					+ "		INNER JOIN main.applications a ON a.id=epa.applicationid "
					+ "		WHERE ep.appst = 1  AND ep.impactst = 1 AND ei.rtoqty <= 1440    "
					+ "		GROUP BY epa.applicationid,a.name "
					+ "	)  "
					+ "	SELECT  distinct cpa.id,     "
					+ "		    a.name,     "
					+ "			cpa.canales,     "
					+ "			cpea.procesos,     "
					+ "			pr.descr AS tipo,     "
					+ "			a.recuperation,     "
					+ "			RMPF.rtoqty   "
					+ "	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 RTOsPotencialesPorAplicacion RMPF ON RMPF.applicationid = a.id     "
					+ "	ORDER BY RMPF.rtoqty";
	
			
			System.out.println(SentenciaBase);
			query = entityManager.createNativeQuery(SentenciaBase);
			query.setParameter("idcampana", idcampana);
			List<Object[]> listacompleta = query.getResultList();
			
			for (Object[] fila : listacompleta) {
				detalleImpactAppsresumeDto.setApplication((String) fila[1]);
				detalleImpactAppsresumeDto.setProcesses((BigInteger) fila[3]==null?BigInteger.ZERO:(BigInteger) fila[3]);
				detalleImpactAppsresumeDto.setRto(BigInteger.valueOf(((Number) fila[6]).longValue()) + " min");
				detalleImpactAppsresumeDto.setType((String) fila[4]);
				detalleImpactAppsresumeDto.setChannels((Integer) fila[2]);

				listaImpactAppsresumeDto.add(detalleImpactAppsresumeDto);
				detalleImpactAppsresumeDto = new ImpactAppsresumeDto();
			}
		
			detalleImpactContentAppReportDto.setAppsresume(listaImpactAppsresumeDto);
			detalleImpactReportSection4Dto.setContent(detalleImpactContentAppReportDto);
			detalleImpactReportSection4Dto.setTitle("Resumen de Aplicaciones antes de 24 horas");
		
		
			return detalleImpactReportSection4Dto;
		
		} catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return detalleImpactReportSection4Dto;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		 }
		
     }

}