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.dto.LabelValueStringDto;
import com.dacrt.SBIABackend.dto.ResumenBiaContentSection1;
import com.dacrt.SBIABackend.dto.ResumenBiaContentSection3;
import com.dacrt.SBIABackend.dto.ResumenBiaSection1;
import com.dacrt.SBIABackend.dto.ResumenBiaSection3;
import com.dacrt.SBIABackend.dto.ResumenBiaTimeTableDto;
import com.dacrt.SBIABackend.dto.StatusDto2;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class BiaresumeproSection3Service {
	Logger logger = LoggerFactory.getLogger(BiaresumeproSection3Service.class);
	@PersistenceContext
	private EntityManager entityManager;
	
	
	 public ResumenBiaSection3 getResumeBiaPeriodos(int idcampana,int idunidad,int idprocesos) {
			ResumenBiaContentSection3 detalleResumenBiaContentSection3 = new ResumenBiaContentSection3();
			LabelValueStringDto detalleLabelValueStringDto = new LabelValueStringDto();
			LabelValueStringDto detalleLabelValueStringDto2 = new LabelValueStringDto();
			ResumenBiaSection3 detalleResumenBiaSection3 = new ResumenBiaSection3();
			List<LabelValueStringDto> listadoResumenBiaTimeTableDto=new ArrayList();
			RespuestaDto respuesta = new RespuestaDto("", false);
				HttpStatus estatus = HttpStatus.FORBIDDEN;	   	
		 try {		
		 String SentenciaBase = " SELECT STRING_AGG(meses.mes, '-') AS meses_con_valor_3,count(1) as cuantos  "
		 		+ "FROM main.evalprocperiods eperi "
		 		+ "    INNER JOIN main.evalprocesses ep ON eperi.evalprocessid = ep.id "
		 		+ "    INNER JOIN main.unitprocesses up ON ep.unitprocessid = up.id "
		 		+ "	INNER JOIN main.campaignunits cu on ep.campaignunitid = cu.id  "
		 		+ "    INNER JOIN LATERAL ( "
		 		+ "         SELECT 'Ene' AS mes WHERE eperi.m1evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Feb' AS mes WHERE eperi.m2evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Mar' AS mes WHERE eperi.m3evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Abr' AS mes WHERE eperi.m4evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'May' AS mes WHERE eperi.m5evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Jun' AS mes WHERE eperi.m6evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Jul' AS mes WHERE eperi.m7evalprocscalepar = '3' "
		 		+ "    	UNION ALL SELECT 'Ago' AS mes WHERE eperi.m8evalprocscalepar = '3'      "
		 		+ "    	UNION ALL SELECT 'Sep' AS mes WHERE eperi.m9evalprocscalepar = '3' "
		 		+ "    	UNION ALL SELECT 'Oct' AS mes WHERE eperi.m10evalprocscalepar = '3' "
		 		+ "     	UNION ALL SELECT 'Nov' AS mes WHERE eperi.m11evalprocscalepar = '3' "
		 		+ "    	UNION ALL SELECT 'Dic' AS mes WHERE eperi.m12evalprocscalepar = '3' "
		 		+ "    ) AS meses ON TRUE "
		 		+ " WHERE "
				+ "    up.unitid =  " + idunidad 
				+ " AND up.processid =  " + idprocesos 
				+ " AND cu.campaingid = " + idcampana + " and ep.impactst = 1 and ep.periodst=1 ";
		 
		 String SentenciaBase2 = " SELECT STRING_AGG(dias.dia, '-') AS dias_con_valor_3,count(1) as cuantos  "
		 		+ "FROM main.evalprocperiods eperi "
		 		+ "    INNER JOIN main.evalprocesses ep ON eperi.evalprocessid = ep.id "
		 		+ "    INNER JOIN main.unitprocesses up ON ep.unitprocessid = up.id "
		 		+ "	INNER JOIN main.campaignunits cu on ep.campaignunitid = cu.id  "
		 		+ "    INNER JOIN LATERAL ( "
		 		+ "        SELECT 'Lun' AS dia WHERE eperi.d1evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Mar' AS dia WHERE eperi.d2evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Mié' AS dia WHERE eperi.d3evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Jue' AS dia WHERE eperi.d4evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Vie' AS dia WHERE eperi.d5evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Sáb' AS dia WHERE eperi.d6evalprocscalepar = '3' "
		 		+ "        UNION ALL SELECT 'Dom' AS dia WHERE eperi.d7evalprocscalepar = '3' "
		 		+ "    ) AS dias ON TRUE "
		 		+ " WHERE "
				+ "    up.unitid =  " + idunidad 
				+ " AND up.processid =  " + idprocesos 
				+ " AND cu.campaingid = " + idcampana + " and ep.impactst = 1 and ep.periodst=1 ";
				
		
			
		 Query query;
		 query = entityManager.createNativeQuery(SentenciaBase);
		 List<Object[]> listacompleta = query.getResultList();
		 int cuantosregistros1 = listacompleta.size();
		 Query query2;
		 query2 = entityManager.createNativeQuery(SentenciaBase2);
		 List<Object[]> listacompleta2 = query2.getResultList();
		 int cuantosregistros2 = listacompleta2.size();  
		 int notraemes = 0;
		 int notraedia = 0 ;
		
			   for (Object[] fila : listacompleta) {
				   if (fila[0] == null || ((String) fila[0]).isEmpty()) {
					   detalleResumenBiaContentSection3.setPeriods(listadoResumenBiaTimeTableDto);
					   
					   
				   } else {
					   detalleLabelValueStringDto.setLabel("Meses");
					      detalleLabelValueStringDto.setValue((String) fila[0]);
					       listadoResumenBiaTimeTableDto.add(detalleLabelValueStringDto);
					       notraemes = 1;
					   
				   }
				     
				   
			   }
               for (Object[] fila2 : listacompleta2) {
            	   if (fila2[0] == null || ((String) fila2[0]).isEmpty()) {
            		   detalleResumenBiaContentSection3.setPeriods(listadoResumenBiaTimeTableDto);
            	   } else 
            	   {
            		   detalleLabelValueStringDto2.setLabel("Dias");
                	   detalleLabelValueStringDto2.setValue((String) fila2[0]);
                		listadoResumenBiaTimeTableDto.add(detalleLabelValueStringDto2);
                		notraedia = 1;
            	   }
            	   
            	  
			   }

   			if (notraedia == 0 && notraemes == 0) {
   				detalleResumenBiaContentSection3.setPeriods(new ArrayList<>());
   			} else {
   				detalleResumenBiaContentSection3.setPeriods(listadoResumenBiaTimeTableDto);
   			}
   		
			

               
		
			
			
			detalleResumenBiaSection3.setTitle("Períodos sensibles para el proceso");
			detalleResumenBiaSection3.setContent(detalleResumenBiaContentSection3);
			
			//RespuestaDto respuesta = new RespuestaDto("", false);
		//	HttpStatus estatus = HttpStatus.FORBIDDEN;	   											
						
				return detalleResumenBiaSection3;	    	 
	    }catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return detalleResumenBiaSection3;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}			 	    					
					
	 	}


}