package com.dacrt.SBIABackend.service;

import java.math.BigInteger;
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.BcpstrategiesDto;
import com.dacrt.SBIABackend.dto.DrpstrategiesDto;
import com.dacrt.SBIABackend.dto.QtyDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class BcpstrategiesService {
	Logger logger = LoggerFactory.getLogger(BcpstrategiesService.class);
	
	@PersistenceContext
	private EntityManager entityManager;
	
	public List<BcpstrategiesDto> getBcpstrategies(String fecha) {
		Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		List<BcpstrategiesDto> listado=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));
		try {
			
			String SentenciaBase = " SELECT  'Con BCP' as bcp, round(CAST(SUM(CASE WHEN (strategytypepar = '1') THEN 1 ELSE 0 END) * 100 AS NUMERIC) / COUNT(*), 2) pctconbcp, "
					+ "                     'Sin BCP' as sinbcp, round(CAST(SUM(CASE WHEN (strategytypepar <> '1') THEN 1 ELSE 0 END) * 100 AS NUMERIC) / COUNT(*), 2)  pctsinbcp "
					+ "            FROM "
					+ "           ( "
					+ "           SELECT        P.name, MAX(COALESCE(E.strategytypepar,'0')) strategytypepar  "
					+ "            FROM            main.processes P "
					+ "            INNER JOIN   main.unitprocesses u ON u.processid=P.id "
					+ "            LEFT JOIN   main.strategiesdetprocesses SP   "
					+ "            ON          u.id=SP.unitprocessid       "
					+ "            LEFT JOIN   main.strategiesdet D   "
					+ "                        ON  SP.strategyid=D.id   "
					+ "            LEFT JOIN    main.strategies E   "
					+ "                        ON  D.strategyid=E.id    "
					+ "            WHERE       CAST(COALESCE(D.modifiedat, CAST('"+ ano +"/"+ mes + "/" + dia + "'" +" AS DATE)) AS DATE) <= CAST('"+ ano +"/"+ mes + "/" + dia + "'" +" AS DATE) "
					+ "              AND       P.status <> 0 "
					+ "              AND       COALESCE(D.status, 1) <> 0 "
					+ "         GROUP BY       P.name "
					+ "         ) A ";
			
			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();
			
			
			BcpstrategiesDto bcpstrategiesDto= new BcpstrategiesDto();
			QtyDto qtyDto= new QtyDto();
			for (Object[] reg : listacompleta) {
				bcpstrategiesDto.setLabel((String) reg[0]);
				bcpstrategiesDto.setValue((Number) reg[1]);
				listado.add(bcpstrategiesDto);
				bcpstrategiesDto= new BcpstrategiesDto();
				
				bcpstrategiesDto.setLabel((String) reg[2]);
				bcpstrategiesDto.setValue((Number) reg[3]);
				listado.add(bcpstrategiesDto);
				bcpstrategiesDto= new BcpstrategiesDto();
				
			}
			
			return listado;
		
		}catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return listado;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}
		
 	}
}