package com.dacrt.SBIABackend.service;

import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;

import com.dacrt.SBIABackend.dto.BcpstrategiesDto;
import com.dacrt.SBIABackend.dto.DrpcoverageDto;
import com.dacrt.SBIABackend.dto.IddscDto;
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;

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;


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

   public List<LabelValueDtoReport2> getDrpcoverage(String fecha) {
		
		List<LabelValueDtoReport2> lista=new ArrayList();
		
		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));
		
		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 ParamAndSinestatus AS ( "
						+ "    SELECT elem->>'value' AS valor, elem->>'dsc' AS dsc "
						+ "    FROM "
						+ "        (SELECT value FROM main.params WHERE paramname='COVERAGE_STATE') AS ParamValue, "
						+ "        jsonb_array_elements(CAST(ParamValue.value AS jsonb)) AS elem "
						+ "    UNION ALL "
						+ "    SELECT '0' AS valor, 'Sin estatus' AS dsc "
						+ " ), "
						+ " FilteredCoverageHistory AS ( "
						+ "    SELECT "
						+ "        coverageappid, "
						+ "        CAST(coveragestatepar AS TEXT) AS coveragestatepar, "
						+ "        date, "
						+ "        status, "
						+ "        ROW_NUMBER() OVER(PARTITION BY coverageappid ORDER BY date DESC, id DESC) AS rn "
						+ "    FROM "
						+ "        main.coverageappshistory "
						+ " ), "
						+ " AggregatedCounts AS ( "
						+ "    SELECT "
						+ "        COALESCE(H_Filtered.coveragestatepar, '0') AS effective_coveragestatepar, "
						+ "        COUNT(*) AS cnt "
						+ "    FROM "
						+ "        main.strategiesdet S "
						+ "    INNER JOIN "
						+ "        main.applications A ON S.applicationid = A.id "
						+ "    INNER JOIN   "
						+ "        main.strategies E ON S.strategyid = E.id "
						+ "    LEFT JOIN "
						+ "        main.coverageapps CA ON S.id = CA.strategiesdetid "
						+ "    LEFT JOIN "
						//+ "        FilteredCoverageHistory AS H_Filtered ON CA.id = H_Filtered.coverageappid AND H_Filtered.rn = 1 AND H_Filtered.date <= CURRENT_DATE AND H_Filtered.status=1 "
						+ "        FilteredCoverageHistory AS H_Filtered ON CA.id = H_Filtered.coverageappid AND H_Filtered.rn = 1 AND H_Filtered.date <=  CAST('"+ ano +"/"+ mes + "/" + dia + "'" +" AS DATE) AND H_Filtered.status=1 " 
						+ "    WHERE "
						+ "        A.status = 1 "
						+ "        AND S.status = 1 "
						+ "        AND E.strategytypepar = 2 "
						+ "    GROUP BY "
						+ "        COALESCE(H_Filtered.coveragestatepar, '0') "
						+ " ) "
						+ " SELECT "
						+ "    p_final.dsc, "
						+ "    CAST(COALESCE(ac.cnt, 0) AS NUMERIC) AS cantidad, "
						+ "    COALESCE( "
						+ "        ROUND( "
						+ "            (CAST(COALESCE(ac.cnt, 0) AS NUMERIC) / "
						+ "            NULLIF(SUM(CAST(COALESCE(ac.cnt, 0) AS NUMERIC)) OVER(), 0) * 100), "
						+ "        2), 0) AS porcentaje "
						+ " FROM "
						+ "    ParamAndSinestatus p_final "
						+ " LEFT JOIN "
						+ "    AggregatedCounts ac ON p_final.valor = ac.effective_coveragestatepar "
						+ " ORDER BY "
						+ "    CASE p_final.valor "
						+ "        WHEN '1' THEN 1 "
						+ "        WHEN '2' THEN 2 "
						+ "        WHEN '3' THEN 3 "
						+ "        WHEN '4' THEN 4 "
						+ "        WHEN '0' THEN 5 "
						+ "        ELSE 99 "
						+ "    END ";
			
			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();
			
			
			//BcpstrategiesDto bcpstrategiesDto= new BcpstrategiesDto();
			LabelValueDtoReport2 detalleLabelValue = new LabelValueDtoReport2();
			QtyDto qtyDto= new QtyDto();
			for (Object[] reg : listacompleta) {
				detalleLabelValue = new LabelValueDtoReport2();
				detalleLabelValue.setLabel((String) reg[0]);
				
				detalleLabelValue.setValue((BigDecimal) reg[2]);
				lista.add(detalleLabelValue);
				
			
				
			}
			
			return lista;
		
		} catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return lista;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		 }
		
     }

		
}