package com.dacrt.SBIABackend.service;

import java.math.BigInteger;
import java.util.ArrayList;
import javax.persistence.Query;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;

import com.dacrt.SBIABackend.dto.InventoryDto;
import com.dacrt.SBIABackend.dto.QtyDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class InventoryService {
	Logger logger = LoggerFactory.getLogger(InventoryService.class);
	
	@PersistenceContext
	private EntityManager entityManager;
	
	public List<InventoryDto> getInventory(String fecha) {
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		List<InventoryDto> 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     'Aplicaciones' dsc,  "
					+ "                        SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                        SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                        COUNT(*) total "
					+ "            FROM        main.applications A "
					+ "            WHERE       A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            UNION "
					+ "            SELECT     'Unidades' dsc,  "
					+ "                       SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                       SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                       COUNT(*) total "
					+ "            FROM       main.units  A "
					+ "            WHERE      A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            UNION "
					+ "            SELECT     'Procesos/Servicios' dsc,  "
					+ "                       SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                       SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                       COUNT(*) total "
					+ "            FROM        main.processes A "
					+ "            WHERE      A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            UNION "
					+ "            SELECT     'Canales' dsc,  "
					+ "                       SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                       SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                       COUNT(*) total "
					+ "            FROM        main.channels A "
					+ "            WHERE      A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            UNION "
					+ "            SELECT     'Cargos' dsc,  "
					+ "                       SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                       SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                       COUNT(*) total "
					+ "            FROM        main.positions A "
					+ "            WHERE      A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            UNION "
					+ "            SELECT     'Entidades Externas' dsc,  "
					+ "                       SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                       SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                       COUNT(*) total "
					+ "            FROM        main.externalreqs A "
					//+ "            FROM        main.requesters A "
					+ "            WHERE      A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            UNION "
					+ "            SELECT     'Tipos de Recursos' dsc,  "
					+ "                       SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                       SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                       COUNT(*) total "
					+ "            FROM        main.resources A "
					+ "            WHERE      A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            UNION "
					+ "            SELECT     'Instalaciones' dsc,  "
					+ "                       SUM(CASE WHEN (A.status <> 0) THEN 1 ELSE 0 END) activos, "
					+ "                       SUM(CASE WHEN (A.status = 0) THEN 1 ELSE 0 END) inactivos, "
					+ "                       COUNT(*) total "
					+ "            FROM        main.facilities A " 
					+ "            WHERE      A.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "
					+ "            ORDER BY dsc ";
	
			
			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();
			
			
			InventoryDto inventoryDto= new InventoryDto();
			QtyDto qtyDto= new QtyDto();
			for (Object[] reg : listacompleta) {
				qtyDto.setActive((BigInteger) reg[1]==null?BigInteger.ZERO:(BigInteger) reg[1]); 
				qtyDto.setInactive((BigInteger) reg[2]==null?BigInteger.ZERO:(BigInteger) reg[2]);
				qtyDto.setTotal((BigInteger) reg[3]==null?BigInteger.ZERO:(BigInteger) reg[3]);
				inventoryDto.setName((String) reg[0]);
				inventoryDto.setQty(qtyDto);
				listado.add(inventoryDto);
				inventoryDto= new InventoryDto();
				qtyDto= new QtyDto();
				
			}
			
			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();
			}
		}
		
 	}
}