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.DrpstrategiesDto;
import com.dacrt.SBIABackend.dto.InventoryDto;
import com.dacrt.SBIABackend.dto.QtyDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class DrpstrategiesService {
	Logger logger = LoggerFactory.getLogger(DrpstrategiesService.class);
	
	@PersistenceContext
	private EntityManager entityManager;
	
	public List<DrpstrategiesDto> getDrpstrategies(String fecha) {
		
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		List<DrpstrategiesDto> 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 {
			
			Query query;
			String SentenciaBase = "SELECT   'Con DRP' as drp,     round(CAST(SUM(CASE WHEN (strategytypepar = '2') THEN 1 ELSE 0 END) * 100 AS NUMERIC) / COUNT(*), 2) pctcondrp, "
					+ "                      'Sin DRP' as sindrp,    round(CAST(SUM(CASE WHEN (strategytypepar <> '2') THEN 1 ELSE 0 END) * 100 AS NUMERIC) / COUNT(*), 2)  pctsindrp "
					+ "            FROM "
					+ "            ( "
					+ "             SELECT        A.name, MAX(COALESCE(E.strategytypepar,'0')) strategytypepar "
					+ "             FROM            main.applications A "
					+ "             LEFT JOIN    main.strategiesdet D "
					+ "             ON                A.id=D.applicationid "
					+ "             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) "
					//+ "             WHERE            COALESCE(D.modifiedat,CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz ) ) <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz ) "
					+ "             AND            A.status <> 0 "
					+ "             AND         COALESCE(D.status, 1) <> 0 "
					+ "             GROUP BY    A.name "
					+ "            ) A ";
	
			
			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();
			
			
			DrpstrategiesDto drpstrategiesDto= new DrpstrategiesDto();
			QtyDto qtyDto= new QtyDto();
			for (Object[] reg : listacompleta) {
				drpstrategiesDto.setLabel((String) reg[0]);
				drpstrategiesDto.setValue((Number) reg[1]);
				listado.add(drpstrategiesDto);
				drpstrategiesDto= new DrpstrategiesDto();
				
				drpstrategiesDto.setLabel((String) reg[2]);
				drpstrategiesDto.setValue((Number) reg[3]);
				listado.add(drpstrategiesDto);
				drpstrategiesDto= new DrpstrategiesDto();
				
			}
			
			return listado;
		
		}catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor"+e.getMessage(), false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return listado;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}
		
 	}
	
}