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

@Service
public class BcpcoverageService {
	Logger logger = LoggerFactory.getLogger(BcpcoverageService.class);
	
	@PersistenceContext
	private EntityManager entityManager;
	
	 public List<LabelValueDtoReport2> getBcpcoverage(String fecha) {
			
			
			
			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));

			
		//	detalleLabelValue.setLabel("En Validación");
	    //	detalleLabelValue.setValue(35);
			
			//detalleLabelValue2.setLabel("Implementada");
			//detalleLabelValue2.setValue(45);
			
			//listado.add(detalleLabelValue);
			///listado.add(detalleLabelValue2);													
	 
	 Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		//List<BcpstrategiesDto> listado=new ArrayList();
		List<LabelValueDtoReport2> listado2=new ArrayList();
		try {
			//+ "        date <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD')  and status=1  "
			String SentenciaBase = " WITH CPH_Filtered_CTE AS ( "
					+ "    SELECT "
					+ "        coverageprocessid, "
					+ "        CAST(coveragestatepar AS TEXT) AS coveragestatepar, "
					+ "        date, "
					+ "        id,  "
					+ "        ROW_NUMBER() OVER(PARTITION BY coverageprocessid ORDER BY date DESC, id DESC) as rn "
					+ "    FROM "
					+ "        main.coverageprocesseshistory "
					+ "    WHERE "
					+ "        date <= TO_DATE('"+ ano +"-"+ mes + "-" + dia +"','YYYY/MM/DD')  and status=1  "
					+ "), "
					+ "RankedCoverageProcesses AS ( "
					+ "    SELECT "
					+ "        CP.strategiesdetprocessid AS sp_id, "
					+ "        CP.id AS coverageprocess_id, "
					+ "        CPH_Filtered.coveragestatepar, "
					+ "        CPH_Filtered.date AS history_date, "
					+ "        ROW_NUMBER() OVER(PARTITION BY CP.strategiesdetprocessid ORDER BY CPH_Filtered.date DESC, CPH_Filtered.id DESC) as rnk "
					+ "    FROM "
					+ "        main.coverageprocesses CP "
					+ "    INNER JOIN  "
					+ "        CPH_Filtered_CTE CPH_Filtered ON CP.id = CPH_Filtered.coverageprocessid AND CPH_Filtered.rn = 1 "
					+ "), "
					+ "RelevantProcessInstances AS ( "
					+ "    SELECT   "
					+ "        P.id AS process_id,   "
					+ "        D.id AS strategiesdet_id,   "
					+ "        SP.id AS strategiesdetprocess_id,   "
					+ "        COALESCE(RCP.coveragestatepar, '0') AS effective_coveragestatepar   "
					+ "    FROM   "
					+ "        main.strategiesdetprocesses SP "
					+ "INNER JOIN   "
					+ "   main.unitprocesses u ON u.id=SP.unitprocessid "
					+ "    INNER JOIN   "
					+ "        main.processes P ON P.id = u.processid  AND u.deleted is null "
					+ "    INNER JOIN   "
					+ "        main.strategiesdet D ON SP.strategyid = D.id AND D.channelid is null  "
					+ "    INNER JOIN   "
					+ "        main.strategies E ON D.strategyid = E.id  "
					+ "    LEFT JOIN   "
					+ "        RankedCoverageProcesses RCP ON SP.id = RCP.sp_id AND RCP.rnk = 1  "
					+ "    WHERE   "
					+ "        P.status <> 0   "
					+ "        AND COALESCE(D.status, 1) <> 0   "
					+ "        AND E.strategytypepar = 1   "
					+ "), "
					+ "FinalCounts AS ( "
					+ "    SELECT "
					+ "        COALESCE(CAST(SUM(CASE WHEN (effective_coveragestatepar = '1') THEN 1 ELSE 0 END) AS NUMERIC), 0) AS qtydisenada, "
					+ "        COALESCE(CAST(SUM(CASE WHEN (effective_coveragestatepar = '2') THEN 1 ELSE 0 END) AS NUMERIC), 0) AS qtyimple, "
					+ "        COALESCE(CAST(SUM(CASE WHEN (effective_coveragestatepar = '3') THEN 1 ELSE 0 END) AS NUMERIC), 0) AS qtyenvalidacion, "
					+ "        COALESCE(CAST(SUM(CASE WHEN (effective_coveragestatepar = '4') THEN 1 ELSE 0 END) AS NUMERIC), 0) AS qtycertificada, "
					+ "        COALESCE(CAST(SUM(CASE WHEN (effective_coveragestatepar NOT IN ('1', '2', '3', '4')) THEN 1 ELSE 0 END) AS NUMERIC), 0) AS qtyotros, "
					+ "        STRING_AGG(CAST(process_id AS TEXT), ', ' ORDER BY process_id ASC, strategiesdet_id ASC) AS proid_all_instances, "
					+ "        STRING_AGG(CAST(strategiesdet_id AS TEXT), ', ' ORDER BY strategiesdet_id ASC, process_id ASC) AS straid_all_instances, "
					+ "        STRING_AGG(CASE WHEN (effective_coveragestatepar = '1') THEN CAST(process_id AS TEXT) ELSE NULL END, ', ' ORDER BY CAST(process_id AS TEXT)) AS ids_disenada, "
					+ "        STRING_AGG(CASE WHEN (effective_coveragestatepar = '2') THEN CAST(process_id AS TEXT) ELSE NULL END, ', ' ORDER BY CAST(process_id AS TEXT)) AS ids_imple, "
					+ "        STRING_AGG(CASE WHEN (effective_coveragestatepar = '3') THEN CAST(process_id AS TEXT) ELSE NULL END, ', ' ORDER BY CAST(process_id AS TEXT)) AS ids_envalidacion, "
					+ "        STRING_AGG(CASE WHEN (effective_coveragestatepar = '4') THEN CAST(process_id AS TEXT) ELSE NULL END, ', ' ORDER BY CAST(process_id AS TEXT)) AS ids_certificada, "
					+ "        STRING_AGG(CASE WHEN (effective_coveragestatepar NOT IN ('1', '2', '3', '4')) THEN CAST(process_id AS TEXT) ELSE NULL END, ', ' ORDER BY CAST(process_id AS TEXT)) AS ids_otros "
					+ "    FROM "
					+ "        RelevantProcessInstances "
					+ "    GROUP BY ()  "
					+ ") "
					+ "SELECT "
					+ "	   ROUND((fc.qtydisenada / NULLIF(fc.qtydisenada + fc.qtyimple + fc.qtyenvalidacion + fc.qtycertificada + fc.qtyotros, 0)) * 100, 2) AS pct_disenada, "
					+ "    ROUND((fc.qtyimple / NULLIF(fc.qtydisenada + fc.qtyimple + fc.qtyenvalidacion + fc.qtycertificada + fc.qtyotros, 0)) * 100, 2) AS pct_imple, "
					+ "    ROUND((fc.qtyenvalidacion / NULLIF(fc.qtydisenada + fc.qtyimple + fc.qtyenvalidacion + fc.qtycertificada + fc.qtyotros, 0)) * 100, 2) AS pct_envalidacion, "
					+ "    ROUND((fc.qtycertificada / NULLIF(fc.qtydisenada + fc.qtyimple + fc.qtyenvalidacion + fc.qtycertificada + fc.qtyotros, 0)) * 100, 2) AS pct_certificada, "
					+ "    ROUND((fc.qtyotros / NULLIF(fc.qtydisenada + fc.qtyimple + fc.qtyenvalidacion + fc.qtycertificada + fc.qtyotros, 0)) * 100, 2) AS pct_otros, "
					+ "	(fc.qtydisenada + fc.qtyimple + fc.qtyenvalidacion + fc.qtycertificada + fc.qtyotros) AS total_estados, "
					+ "    fc.qtydisenada, "
					+ "    fc.qtyimple, "
					+ "    fc.qtyenvalidacion, "
					+ "    fc.qtycertificada, "
					+ "    fc.qtyotros, "
					+ "     "
					+ "     "
					+ "    fc.ids_disenada, "
					+ "    fc.ids_imple, "
					+ "    fc.ids_envalidacion, "
					+ "    fc.ids_certificada, "
					+ "    fc.ids_otros, "
					+ "    fc.proid_all_instances, "
					+ "    fc.straid_all_instances "
					+ "FROM "
					+ "    FinalCounts fc ";
			
			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();
			
			
			//BcpstrategiesDto bcpstrategiesDto= new BcpstrategiesDto();
			LabelValueDtoReport2 detalleLabelValue  = new LabelValueDtoReport2();
			LabelValueDtoReport2 detalleLabelValue2 = new LabelValueDtoReport2();
			LabelValueDtoReport2 detalleLabelValue3 = new LabelValueDtoReport2();
			LabelValueDtoReport2 detalleLabelValue4 = new LabelValueDtoReport2();
			LabelValueDtoReport2 detalleLabelValue5 = new LabelValueDtoReport2();
			//QtyDto qtyDto= new QtyDto();
			
			for (Object[] reg : listacompleta) {
				
				detalleLabelValue = new LabelValueDtoReport2();
				detalleLabelValue2 = new LabelValueDtoReport2();
				detalleLabelValue3 = new LabelValueDtoReport2();
				detalleLabelValue4 = new LabelValueDtoReport2();
				detalleLabelValue5 = new LabelValueDtoReport2();
				detalleLabelValue.setLabel("Diseñada");
				detalleLabelValue.setValue((BigDecimal) reg[0]);
				detalleLabelValue2.setLabel("Implementada");
				detalleLabelValue2.setValue((BigDecimal) reg[1]);
				detalleLabelValue3.setLabel("En Validación");
				detalleLabelValue3.setValue((BigDecimal) reg[2]);
				detalleLabelValue4.setLabel("Certificada");
				detalleLabelValue4.setValue((BigDecimal) reg[3]);
				detalleLabelValue5.setLabel("Sin estatus");
				detalleLabelValue5.setValue((BigDecimal) reg[4]);
				listado2.add(detalleLabelValue);
				listado2.add(detalleLabelValue2);
				listado2.add(detalleLabelValue3);
				listado2.add(detalleLabelValue4);
				listado2.add(detalleLabelValue5);
				
			}
			
			return listado2;
		
		} catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return listado2;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		 }
		
  }

		
}