package com.dacrt.SBIABackend.service;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
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.Content1UnitResumeBCPDto;
import com.dacrt.SBIABackend.dto.Content2UnitResumeBCPDto;
import com.dacrt.SBIABackend.dto.LabelValueDtoReport2;
import com.dacrt.SBIABackend.dto.Section1UnitResumenBCPDto;
import com.dacrt.SBIABackend.dto.StrategyUnitResumeBCPDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class Section1unitresumebcpService {
	Logger logger = LoggerFactory.getLogger(Section1unitresumebcpService.class);

	@PersistenceContext
	private EntityManager entityManager;

	public Section1UnitResumenBCPDto getSection1Unitresumebcp(Integer unitid) {

		Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		Section1UnitResumenBCPDto section1 = new Section1UnitResumenBCPDto();
		Content1UnitResumeBCPDto content = new Content1UnitResumeBCPDto();
		String title="Información General para la Unidad";
		try {
			
			String SentenciaBase = "SELECT"
					+ "    p.cantidadpro,        "
					+ "    r.cantidadre,        "
					+ "    s.cantidadsu,         "
					+ "    po.cantidadpo,        "
					+ "    res.cantidadres,      "
					+ "    est.cantidadest,      "
					+ "	   COALESCE(rto_min.rtoqty / CAST(rto_min.filtro AS INTEGER), 0) AS rtoqty,"
					+ "    COALESCE(rto_min.periodpar, '') AS periodpar "
					+ "FROM"
					+ "    ("
					+ "        SELECT COUNT(DISTINCT p.name) AS cantidadpro"
					+ "        FROM main.evalprocesses ev"
					+ "        INNER JOIN main.unitprocesses up ON ev.unitprocessid = up.id AND up.deleted is null AND up.unitid = "+unitid
					+ "        INNER JOIN main.processes p ON up.processid = p.id AND p.status = 1"
					+ "        WHERE ev.impactst = 1"
					+ "    ) AS p,"
					+ "    ("
					+ "        SELECT COUNT(DISTINCT ei.name) AS cantidadre"
					+ "        FROM main.evalprocesses ev"
					+ "        INNER JOIN main.unitprocesses up ON ev.unitprocessid = up.id AND up.deleted is null AND up.unitid = "+unitid
					+ "        INNER JOIN main.processes p ON up.processid = p.id AND p.status = 1"
					+ "        INNER JOIN main.evalprocrecords ei ON ei.evalprocessid = ev.id"
					+ "        WHERE ev.recordst = 1 AND ev.impactst = 1"
					+ "    ) AS r,"
					+ "    ("
					+ "        SELECT COUNT(DISTINCT s.name) AS cantidadsu"
					+ "        FROM main.evalprocesses ev"
					+ "        INNER JOIN main.unitprocesses up ON ev.unitprocessid = up.id AND up.deleted is null AND up.unitid = "+unitid
					+ "        INNER JOIN main.processes p ON up.processid = p.id AND p.status = 1"
					+ "        INNER JOIN main.evalprocsupliers ei ON ei.evalprocessid = ev.id"
					+ "        INNER JOIN main.suppliers s ON ei.supplierid = s.id AND s.status = 1"
					+ "        WHERE ev.suplierst = 1 AND ev.impactst = 1"
					+ "    ) AS s,"
					+ "    ("
					+ "        SELECT COUNT(DISTINCT p.name) AS cantidadpo"
					+ "        FROM main.campaignunits c"
					+ "        INNER JOIN main.evalunits eu ON eu.campaingunitid = c.id AND c.unitid = "+unitid
					+ "        INNER JOIN main.evalunitpos eup ON eup.evalunitid = eu.id"
					+ "        INNER JOIN main.positions p ON p.id = eup.positionid AND p.status = 1"
					+ "        	INNER JOIN main.positionunits pu ON pu.positionid = p.id AND pu.unitid = "+unitid
					+ "        WHERE eu.positionst = 1"
					+ "    ) AS po,"
					+ "    ("
					+ "        SELECT COUNT(DISTINCT r.name) AS cantidadres"
					+ "        FROM main.campaignunits c"
					+ "        INNER JOIN main.evalunits eu ON eu.campaingunitid = c.id AND c.unitid = "+unitid
					+ "        INNER JOIN main.evalunitres eur ON eur.evalunitid = eu.id"
					+ "        INNER JOIN main.resources r ON r.id = eur.resourceid AND r.status = 1"
					+ "        WHERE eu.resourcest = 1"
					+ "    ) AS res,"
					+ "    ("
					+ "        SELECT COUNT(stu.*) AS cantidadest"
					+ "        FROM main.strategiesdetunits stu"
					+ "        INNER JOIN main.strategiesdet ed ON ed.id = stu.strategydetid AND ed.status = 1"
					+ "        WHERE stu.unitid = "+unitid
					+ "    ) AS est "
					+ "LEFT JOIN"
					+ "    ("
					+ "        SELECT"
					+ "            t_min.rtoqty,"
					+ "            ("
					+ "                SELECT elemento ->> 'dsc' AS descr"
					+ "                FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento"
					+ "                WHERE p.paramname = 'EVALPROC_PERIOD' AND t_min.evalprocrtoperiodpar = elemento ->> 'value'"
					+ "            ) AS periodpar,"
					+ "            ("
					+ "                SELECT elemento ->> 'filter' AS filtro"
					+ "                FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento"
					+ "                WHERE p.paramname = 'EVALPROC_PERIOD' AND t_min.evalprocrtoperiodpar = elemento ->> 'value'"
					+ "            ) AS filtro"
					+ "        FROM"
					+ "            ("
					+ "                SELECT"
					+ "                    t1.rtoqty,"
					+ "                    t1.evalprocrtoperiodpar,"
					+ "                    ROW_NUMBER() OVER (ORDER BY t1.rtoqty ASC) AS ranking_minimo"
					+ "                FROM"
					+ "                    main.evalprocesses eval"
					+ "                INNER JOIN"
					+ "                    main.campaignunits cu ON eval.campaignunitid = cu.id AND cu.unitid = "+unitid
					+ "                INNER JOIN"
					+ "                    main.campaigns c ON cu.campaingid = c.id"
					+ "                INNER JOIN"
					+ "                    main.evalprocimpacts t1 ON eval.id = t1.evalprocessid"
					+ "                INNER JOIN"
					+ "                    main.unitprocesses up ON up.id = eval.unitprocessid AND up.deleted is null"
					+ "                INNER JOIN"
					+ "                    main.processes p ON p.id = up.processid AND p.status = 1"
					+ "                INNER JOIN"
					+ "                    ("
					+ "                        SELECT"
					+ "                            id,"
					+ "                            ROW_NUMBER() OVER (ORDER BY dateto DESC) AS ranking_campana"
					+ "                        FROM"
					+ "                            main.campaigns"
					+ "                        WHERE"
					+ "                            DATE_TRUNC('day', dateto) <= CURRENT_DATE"
					+ "                    ) cr ON c.id = cr.id"
					+ "                WHERE"
					+ "                    eval.impactst = 1"
					+ "            ) AS t_min"
					+ "        WHERE"
					+ "            t_min.ranking_minimo = 1"
					+ "    ) AS rto_min ON TRUE ";
			query = entityManager.createNativeQuery(SentenciaBase);
			
			Object[] fila = (Object[]) query.getSingleResult();
			
			if (fila!=null) {
			content.setPersons((BigInteger) fila[3]);
			content.setProcesses((BigInteger) fila[0]);
			content.setRecords((BigInteger) fila[1]);
			content.setResources((BigInteger) fila[4]);
			content.setStrategies((BigInteger) fila[5]);
			content.setSuppliers((BigInteger) fila[2]);
			content.setRto(fila[6].toString() + " "+fila[7].toString());
			
			section1.setTitle(title);
			section1.setContent(content);
			}
			
			return section1;

		} catch (NoResultException e)  {
			// Manejo de excepciones
			content.setPersons(BigInteger.valueOf(0L));
			content.setProcesses(BigInteger.valueOf(0L));
			content.setRecords(BigInteger.valueOf(0L));
			content.setResources(BigInteger.valueOf(0L));
			content.setStrategies(BigInteger.valueOf(0L));
			content.setSuppliers(BigInteger.valueOf(0L));
			content.setRto("");
			
			section1.setTitle(title);
			section1.setContent(content);	
			return section1;
			
		} catch (Exception e) {
			respuesta = new RespuestaDto("Error interno del servidor "+ e.getMessage(), false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return section1;
			
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}

	}

}