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.LabelValueDtoReport;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport2;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

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

   public List<LabelValueDtoReport2> getrecoveryexpectation(int idcampana,String contentin) {
		
		List<LabelValueDtoReport2> listarecoveryexpectation=new ArrayList();
		
		//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;
		//List<BcpstrategiesDto> listado=new ArrayList();
		try {
			
			String SentenciaBase = " WITH rangos_rto AS ( "
					+ "    SELECT * FROM (VALUES "
					+ "        ('<60 minutos', 0, 59), "
					+ "        ('1 Hrs', 60, 119), "
					+ "        ('2 Hrs', 120, 239), "
					+ "        ('4 Hrs', 240, 479), "
					+ "        ('8 Hrs', 480, 719), "
					+ "        ('12 Hrs', 720, 1439), "
					+ "        ('1 Dia', 1440, 2879), "
					+ "        ('2 Dias', 2880, 4319), "
					+ "        ('3 Dias', 4320, 5759), "
					+ "        ('4 Dias', 5760, 7199), "
					+ "        ('5 Dias', 7200, 8639), "
					+ "        ('6 Dias', 8640, 10079), "
					+ "        ('7 dias', 10080, 10080),"
					+ "        ('+7 dias', 10081, NULL)  "
					+ "    ) AS t(rango, min_rto, max_rto) "
					+ " ), "
					+ " datos_filtrados AS ( "
					+ "    SELECT ei.rtoqty "
					+ "    FROM main.evalprocesses ep "
					+ "    JOIN main.campaignunits cu ON ep.campaignunitid = cu.id AND cu.campaingid = "  + idcampana
					+ "    JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid "
					+ "    WHERE ep.impactst = 1 "
					+ " ), "
					+ " conteo_por_rango AS ( "
					+ "    SELECT  "
					+ "        r.rango AS rto_window, "
					+ "        r.min_rto, "
					+ "        CAST(COALESCE(COUNT(d.rtoqty), 0) AS INTEGER) AS total_registros "
					+ "    FROM rangos_rto r "
					+ "    LEFT JOIN datos_filtrados d ON d.rtoqty >= r.min_rto AND (r.max_rto IS NULL OR d.rtoqty <= r.max_rto) "
					+ "    GROUP BY r.rango, r.min_rto "
					+ " ) "
					+ " SELECT "
					+ "    rto_window, "
					+ "    total_registros, "
					+ "    cast(SUM(total_registros) OVER (ORDER BY min_rto) as integer) AS total_registros_acumulado "
					+ " FROM "
					+ "    conteo_por_rango "
					+ " ORDER BY "
					+ "    min_rto";
			
			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();
			
			
			//BcpstrategiesDto bcpstrategiesDto= new BcpstrategiesDto();
			LabelValueDtoReport2 detalleLabelValueRecoveryexpectation1 = new LabelValueDtoReport2();
			//QtyDto qtyDto= new QtyDto();
			for (Object[] reg : listacompleta) {
				//detalleLabelValue = new LabelValueDtoReport2();
			
				detalleLabelValueRecoveryexpectation1 = new LabelValueDtoReport2();
				
				
				detalleLabelValueRecoveryexpectation1.setLabel((String) reg[0]);
				BigDecimal bigDecimalDesdeInt = new BigDecimal((int) reg[2]);
				detalleLabelValueRecoveryexpectation1.setValue(bigDecimalDesdeInt);
				//bigDecimalDesdeInt = null;
				
				
				listarecoveryexpectation.add(detalleLabelValueRecoveryexpectation1);
				
			}
			
			return listarecoveryexpectation;
			//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 listarecoveryexpectation;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		 }
		
     }

}