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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;

import com.dacrt.SBIABackend.dto.Channelresume2Dto;
import com.dacrt.SBIABackend.dto.ChannelresumeDto;
import com.dacrt.SBIABackend.dto.ContinuitySection2Dto;
import com.dacrt.SBIABackend.dto.LabelItemDtoReport;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class ContinuitySection2Service {
	Logger logger = LoggerFactory.getLogger(ContinuitySection2Service.class);
	@PersistenceContext
	private EntityManager entityManager;
	
	 public ContinuitySection2Dto getResumeChannels(String fecha) {
			
		
		   Query query;
			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));

			ChannelresumeDto detalleChannelresumeDto = new ChannelresumeDto();
			ChannelresumeDto detalleChannelresumeDto2 = new ChannelresumeDto();
			Channelresume2Dto detalleChannelList = new Channelresume2Dto();
			
			ContinuitySection2Dto detalleContinuitySection2Dto = new ContinuitySection2Dto();
			
			
			detalleChannelList= new Channelresume2Dto();
			detalleChannelresumeDto2 = new ChannelresumeDto();
			
			detalleContinuitySection2Dto = new  ContinuitySection2Dto();
			
			//detalleLabelValue = new LabelValueDtoReport();
			
			/* 
			detalleChannelresumeDto.setChannel("Banca en Línea");
			detalleChannelresumeDto.setProcesses(16);
			detalleChannelresumeDto.setRealrto("35 min");
			detalleChannelresumeDto.setRto("30 min");
			detalleChannelresumeDto.setType("Transaccional");
			detalleChannelresumeDto.setWeight(35);
			
			detalleChannelresumeDto2.setChannel("ATM");
			detalleChannelresumeDto2.setProcesses(22);
			detalleChannelresumeDto2.setRealrto("45 min");
			detalleChannelresumeDto2.setRto("43 min");
			detalleChannelresumeDto2.setType("Híbrido");
			detalleChannelresumeDto2.setWeight(56);	
				+ "    WHERE ep.impactst = 1 AND ch.status = 1 and ep.modifiedat <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD')  "*/
			RespuestaDto respuesta = new RespuestaDto("", false);
			HttpStatus estatus = HttpStatus.FORBIDDEN;
	     try {
	    	 //TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD') 
	    	 String SentenciaSql = "  WITH CampanasConRanking AS ( "
	    	 		+ "    SELECT "
	    	 		+ "        id, "
	    	 		+ "        dateto, "
	    	 		+ "        ROW_NUMBER() OVER (ORDER BY dateto DESC) AS ranking_campana "
	    	 		+ "    FROM main.campaigns "
	    	 		+ "    WHERE DATE_TRUNC('day', dateto) <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD')  "
	    	 		+ " ), "
	    	 		+ " ResultadoInicial AS ( "
	    	 		+ "    SELECT "
	    	 		+ "        cr.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, "
	    	 		+ "        ROUND(100.0 * ((ch.amountop * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.amountop) FROM main.channels ch1), 0), 1)) + "
	    	 		+ "                     (ch.volumeop * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.volumeop) FROM main.channels ch1), 0), 1)) + "
	    	 		+ "                     (ch.customerqty * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.customerqty) FROM main.channels ch1), 0), 1)) ) / 3, 2) AS peso, "
	    	 		+ "        cr.ranking_campana  "
	    	 		+ "    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 = c.id "
	    	 		+ "    LEFT JOIN ( "
	    	 		+ "        SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor "
	    	 		+ "        FROM main.params pa, jsonb_array_elements(CAST(pa.value AS jsonb)) AS elemento "
	    	 		+ "        WHERE pa.paramname = 'CHANNEL_TYPE' "
	    	 		+ "    ) pr ON ch.channeltypepar = CAST(pr.valor AS INTEGER) "
	    	 		+ "    INNER JOIN CampanasConRanking cr ON c.id = cr.id "
	    	 		+ "    WHERE ep.impactst = 1 AND ch.status = 1 "
	    	 		+ "    GROUP BY "
	    	 		+ "        cr.id, ch.id, ch.name, pr.descr, pr.valor, cr.ranking_campana "
	    	 		+ " ), "
	    	 		+ " ResultadoConRankingFinal AS ( "
	    	 		+ "    SELECT "
	    	 		+ "        *, "
	    	 		+ "        ROW_NUMBER() OVER (PARTITION BY chid ORDER BY ranking_campana ASC, rtopr ASC) AS rn "
	    	 		+ "    FROM ResultadoInicial "
	    	 		+ " ) "
	    	 		+ " SELECT "
	    	 		+ "    chid, "
	    	 		+ "    chname, "
	    	 		+ "    tipo, "
	    	 		+ "    campid, "
	    	 		+ "    rtonegocio, "
	    	 		+ "    total_procesos_por_canal, "
	    	 		+ "    rtopr, "
	    	 		+ "    valor, "
	    	 		+ "    peso "
	    	 		+ " FROM ResultadoConRankingFinal "
	    	 		+ " WHERE rn = 1 AND rtopr <= 1440 "
	    	 		+ " ORDER BY rtopr ASC ";
	    	 
	    	 query = entityManager.createNativeQuery(SentenciaSql);
				List<Object[]> listacompleta = query.getResultList();
				List<ChannelresumeDto> listadoChannels=new ArrayList();
				List<ChannelresumeDto> listadoChannels2=new ArrayList();
				
				int cuantos =listacompleta.size();
				//cuantos=0;
				if (cuantos>0) {
				for (Object[] reg : listacompleta) {
					detalleChannelresumeDto = new ChannelresumeDto();
					
					detalleChannelresumeDto.setChannel((String) reg[1]);
					detalleChannelresumeDto.setProcesses((Integer) reg[5]);
					Integer valorrto = (Integer) reg[6];
					String rtoString = valorrto.toString() + " min";
					Integer valorrtoReal = (Integer) reg[4];
					String rtoRealString = valorrtoReal.toString() + " min";
					
					detalleChannelresumeDto.setRealrto(rtoRealString);
					detalleChannelresumeDto.setRto(rtoString);
					detalleChannelresumeDto.setType((String) reg[2]);
					detalleChannelresumeDto.setWeight((BigDecimal) reg[8]);
					
					listadoChannels.add(detalleChannelresumeDto);
					
				}
                  detalleChannelList.setChannelresume(listadoChannels);
				
				detalleContinuitySection2Dto.setTitle("Resumen de Canales hasta 24 Horas");
				detalleContinuitySection2Dto.setContent(detalleChannelList);
				} else {
					detalleContinuitySection2Dto.setTitle("Resumen de Canales hasta 24 Horas");
					listadoChannels=new ArrayList();
					detalleChannelList = new Channelresume2Dto();
					detalleChannelList.setChannelresume(listadoChannels);
					detalleContinuitySection2Dto.setContent(detalleChannelList);
				}
	    	
				
				
				
						
				return detalleContinuitySection2Dto;
	    	 
	    	 
	     } catch (Exception e) {
	    	 respuesta = new RespuestaDto("Error interno del servidor", false);
				estatus = HttpStatus.INTERNAL_SERVER_ERROR;
				return detalleContinuitySection2Dto;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}
			
			
			/*
*/ 
			
					
	 	}


}