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.ResumenBiaContentSection2;
import com.dacrt.SBIABackend.dto.ResumenBiaSection1;
import com.dacrt.SBIABackend.dto.ResumenBiaSection2;
import com.dacrt.SBIABackend.dto.ResumenBiaTimeTableDto;
import com.dacrt.SBIABackend.dto.StatusDto2;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class BiaresumeproSection2Service {
	Logger logger = LoggerFactory.getLogger(BiaresumeproSection2Service.class);
	@PersistenceContext
	private EntityManager entityManager;
	
	
	 public ResumenBiaSection2 getResumeBiaImpacts(int idcampana,int idunidad,int idprocesos) {
			LabelValueStringDto detalleLabelValueStringDto = new LabelValueStringDto();
			LabelValueStringDto detalleLabelValueStringDto2 = new LabelValueStringDto();
			LabelValueStringDto detalleLabelValueStringDto3 = new LabelValueStringDto();
			LabelValueStringDto detalleLabelValueStringDto4 = new LabelValueStringDto();
			LabelValueStringDto detalleLabelValueStringDto5 = new LabelValueStringDto();
			ResumenBiaSection2 detalleResumenBiaSection2 = new ResumenBiaSection2();
		 RespuestaDto respuesta = new RespuestaDto("", false);
			HttpStatus estatus = HttpStatus.FORBIDDEN;
		 try {	
		 String SentenciaBase = " SELECT "
		 		+ "   CAST(ei.rtolegalpar AS INTEGER),pr.descr as legal,CAST(ei.rtofinantialpar AS INTEGER),pr1.descr as financiero, "
		 		+ "	CAST(ei.rtooperativepar AS INTEGER),pr2.descr as operativo,CAST(ei.rtoreputationalpar AS INTEGER),pr3.descr as reputacion, "
		 		+ "	CAST(ei.rtocustomerpar AS INTEGER),pr4.descr as cliente "
		 		+ "FROM "
		 		+ "    main.evalprocesses ep "
		 		+ "	inner join main.evalprocimpacts ei on ep.id=ei.evalprocessid "
		 		+ "    INNER JOIN main.evalprocperiods eperi 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  "
		 		+ "    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 = 'EVALPROC_SCALEIMPACT') "
		 		+ "	 pr ON ei.rtolegalpar = pr.valor "
		 		+ "	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 = 'EVALPROC_SCALEIMPACT') "
		 		+ "	 pr1 ON ei.rtofinantialpar = pr1.valor "
		 		+ "	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 = 'EVALPROC_SCALEIMPACT') "
		 		+ "	 pr2 ON ei.rtooperativepar = pr2.valor "
		 		+ "	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 = 'EVALPROC_SCALEIMPACT') "
		 		+ "	 pr3 ON ei.rtoreputationalpar = pr3.valor "
		 		+ "	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 = 'EVALPROC_SCALEIMPACT') "
		 		+ "	 pr4 ON ei.rtocustomerpar = pr4.valor "
		 		+ " WHERE "
				+ "    up.unitid =  " + idunidad 
				+ " AND up.processid =  " + idprocesos 
				+ " AND cu.campaingid = " + idcampana + " and ep.impactst = 1";
		 
		 ResumenBiaContentSection2 detalleResumenBiaContentSection2 = new ResumenBiaContentSection2();
		
			List<LabelValueStringDto> listadoLabelValueStringDto=new ArrayList();
			//StatusDto2 detalleRTO = new StatusDto2();
			Query query;
			 query = entityManager.createNativeQuery(SentenciaBase);
			 
			   List<Object[]> listacompleta = query.getResultList();
			   int cuantosregistros1 = listacompleta.size();
			   
			   if (cuantosregistros1>0) {
				   for (Object[] fila : listacompleta) { 
					   detalleLabelValueStringDto = new LabelValueStringDto();
					   detalleLabelValueStringDto2 = new LabelValueStringDto();
					   detalleLabelValueStringDto3 = new LabelValueStringDto();
					   detalleLabelValueStringDto4 = new LabelValueStringDto();
					   detalleLabelValueStringDto5 = new LabelValueStringDto();
					   
					   detalleLabelValueStringDto.setLabel("Exposición Legal");
					   detalleLabelValueStringDto.setValue((String) fila[1]);
					   detalleLabelValueStringDto2.setLabel("Impacto Financiero");
					   detalleLabelValueStringDto2.setValue((String) fila[3]);
					   detalleLabelValueStringDto3.setLabel("Impacto Operativo");
					   detalleLabelValueStringDto3.setValue((String) fila[5]);
					   detalleLabelValueStringDto4.setLabel("Impacto Reputacional");
					   detalleLabelValueStringDto4.setValue((String) fila[7]);
					   detalleLabelValueStringDto5.setLabel("Exposición Cliente");
					   detalleLabelValueStringDto5.setValue((String) fila[9]);
					   
					   listadoLabelValueStringDto.add(detalleLabelValueStringDto);
					   listadoLabelValueStringDto.add(detalleLabelValueStringDto2);
					   listadoLabelValueStringDto.add(detalleLabelValueStringDto3);
					   listadoLabelValueStringDto.add(detalleLabelValueStringDto4);
					   listadoLabelValueStringDto.add(detalleLabelValueStringDto5);
				   }
				   detalleResumenBiaContentSection2.setImpacts(listadoLabelValueStringDto);
				   
			   } else {
				   detalleResumenBiaContentSection2.setImpacts(new ArrayList<>());
				   
			   }
		
			
			
			detalleResumenBiaSection2.setTitle("Impactos Operativos y Financieros");
			detalleResumenBiaSection2.setContent(detalleResumenBiaContentSection2);
			
		
						
			return detalleResumenBiaSection2;
	 }catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return detalleResumenBiaSection2;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}				 	    	 
					
	 	}

}