package com.dacrt.SBIABackend.service;

import java.math.BigDecimal;
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.ImpactChannelresumeDto;
import com.dacrt.SBIABackend.dto.ImpactContentReportDto;
import com.dacrt.SBIABackend.dto.ImpactReportSection3Dto;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport2;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

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

   public ImpactReportSection3Dto getImpactResumeChannels(int idcampana,String contentin) {
		
		List<ImpactChannelresumeDto> listaImpactChannelresumeDto=new ArrayList();
		
		ImpactReportSection3Dto detalleImpactReportSection3Dto = new ImpactReportSection3Dto();
		ImpactContentReportDto detalleImpactContentReportDto = new ImpactContentReportDto();
		ImpactChannelresumeDto detalleImpactChannelresumeDto = new ImpactChannelresumeDto();
		ImpactChannelresumeDto detalleImpactChannelresumeDto2 = new ImpactChannelresumeDto();
		//String fecha2=fecha;
	
		LabelValueDtoReport detalleLabelValue2 = new LabelValueDtoReport();
		
		detalleLabelValue2 = new LabelValueDtoReport();
		DrpcoverageDto drpcoverageDto= new DrpcoverageDto();
		
		Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		
		try {				
			
			String SentenciaBase = " WITH ResultadoInicial AS (  "
					+ "	  SELECT  c.id as campid, ch.id as chid, ch.name as chname,pr.descr as tipo,pr.valor as valor,  "
					+ "	   cast(ch.capacity as integer) as rtonegocio,MIN(ei.rtoqty) as rtopr ,  "
					+ "	    cast(COUNT(pc.id) as integer) AS total_procesos_por_canal "
					+ "	     FROM    "
					+ "	    main.evalprocesses ep  "
					+ "	    INNER JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid  "
					+ "	    INNER JOIN main.unitprocesses up ON ep.unitprocessid = up.id  "
					+ "	    INNER JOIN main.processes p ON  up.processid =  p.id  "
					+ "	    INNER JOIN main.processchannels pc ON  p.id =  pc.processid  "
					+ "	    INNER JOIN main.channels ch ON pc.channelid = ch.id  "
					+ "	    INNER JOIN main.campaignunits cu ON ep.campaignunitid = cu.id  "
					+ "	    INNER JOIN main.campaigns c ON cu.campaingid = cu. campaingid "
					+ "	    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 = 'CHANNEL_TYPE'     ) pr ON ch.channeltypepar = CAST(pr.valor AS INTEGER)   "
					+ "	    WHERE ep.impactst = 1 AND ch.status = 1 and c.id = " + idcampana + " and cu.campaingid = " + idcampana + "  and ch.id>0    "
					+ "	    GROUP BY c.id, ch.id, ch.name, pr.descr,pr.valor   "
					+ "	   order by ch.capacity asc  "
					+ "	    ),  "
					+ "	    ResultadoConRanking AS (  "
					+ "	     SELECT  campid,  chid,  chname,  tipo,  rtonegocio,  rtopr,  valor,  total_procesos_por_canal  "
					+ "	     FROM  ResultadoInicial  "
					+ "	    	 )  "
					+ "	    SELECT chid,chname,campid,total_procesos_por_canal,rtopr,tipo  "
					+ "	    FROM ResultadoConRanking  "
					+ "      WHERE  rtopr <=1440        "
					+ "	     ORDER BY   "
					+ "	    rtopr ASC, chname ASC ";
			
			query = entityManager.createNativeQuery(SentenciaBase);
			long cuantosregistro = (long) query.getResultList().size();
			List<Object[]> listacompleta = query.getResultList();
			
			for (Object[] fila : listacompleta) {	
				detalleImpactChannelresumeDto = new ImpactChannelresumeDto();
		       detalleImpactChannelresumeDto.setChannel((String) fila[1]);
		       detalleImpactChannelresumeDto.setProcesses((Integer) fila[3]);
		       Integer RtoInteger = (Integer) fila[4];
		       String RtoString = RtoInteger.toString() + " min";
		       
		       detalleImpactChannelresumeDto.setRto(RtoString);
		       detalleImpactChannelresumeDto.setType((String) fila[5]);
		
		       listaImpactChannelresumeDto.add(detalleImpactChannelresumeDto);
		
		
		       detalleImpactContentReportDto.setChannelresume(listaImpactChannelresumeDto);
			}
			
			if (cuantosregistro>0) {
				detalleImpactReportSection3Dto.setContent(detalleImpactContentReportDto);
				detalleImpactReportSection3Dto.setTitle("Resumen de Canales antes de 24 horas");
				
			} else {
				detalleImpactContentReportDto.setChannelresume(listaImpactChannelresumeDto);
				detalleImpactReportSection3Dto.setContent(detalleImpactContentReportDto);
				detalleImpactReportSection3Dto.setTitle("Resumen de Canales antes de 24 horas");
			}
			//detalleImpactContentReportDto.setChannelresume(listaImpactChannelresumeDto);	
		
		
		
			return detalleImpactReportSection3Dto;
			//List<LabelValueDtoReport2> listarecoveryprocesses=new ArrayList();
			///List<LabelValueDtoReport2> recoveryexpectation=new ArrayList();
		
		} catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return detalleImpactReportSection3Dto;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		 }
		
     }

}