package com.dacrt.SBIABackend.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

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.Content3UnitResumeBCPDto;
import com.dacrt.SBIABackend.dto.Section1UnitResumenBCPDto;
import com.dacrt.SBIABackend.dto.Section3UnitResumenBCPDto;
import com.dacrt.SBIABackend.dto.StrategyUnitResumeBCPDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

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

	@PersistenceContext
	private EntityManager entityManager;

	public Section3UnitResumenBCPDto getSection3Unitresumebcp(Integer unitid) {

		Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		Section3UnitResumenBCPDto section3 = new Section3UnitResumenBCPDto();
		Content3UnitResumeBCPDto content = new Content3UnitResumeBCPDto();
		List<Content3UnitResumeBCPDto> contenList = new ArrayList();
		String title="Procesos RTO asociados a las estategias definidas";
		try {
			
		/*	String SentenciaBase = " SELECT "
					+ "    ( "
					+ "        SELECT "
					+ "            t_min.rtoqty / CAST(elemento ->> 'filter' AS INTEGER) 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 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, "
					+ "    t_min.name AS proceso "
					+ "FROM "
					+ "    ( "
					+ "    SELECT "
					+ "        t1.rtoqty, "
					+ "        t1.evalprocrtoperiodpar, "
					+ "            p.name, "
					+ "            ROW_NUMBER() OVER (PARTITION BY p.name ORDER BY t1.rtoqty ASC) AS rto_rank "
					+ "    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.rto_rank = 1  order by rtoqty asc ";*/
			String SentenciaBase = " SELECT "
					+ "    ( "
					+ "        SELECT "
					+ "            CASE     "
					+ "                WHEN elemento ->> 'dsc' = 'Horas' "
					+ "                    THEN (t_min.rtoqty / CAST(elemento ->> 'filter' AS INTEGER)) * 60 "
					+ "                WHEN elemento ->> 'dsc' = 'Días' "
					+ "                    THEN (t_min.rtoqty / CAST(elemento ->> 'filter' AS INTEGER)) * 1440 "
					+ "                ELSE (t_min.rtoqty / CAST(elemento ->> 'filter' AS INTEGER)) "
					+ "            END AS rto_en_minutos "
					+ "        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 rtoqty_minutos, "
					+ "     "
					+ "    ( "
					+ "        SELECT "
					+ "            CASE "
					+ "                WHEN elemento ->> 'dsc' IN ('Horas', 'Días')  "
					+ "                    THEN 'Minutos' "
					+ "                ELSE elemento ->> 'dsc'  "
					+ "            END AS descr_ajustada "
					+ "        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, "
					+ "     "
					+ "    t_min.name AS proceso "
					+ "FROM "
					+ "    ( "
					+ "        SELECT "
					+ "            t1.rtoqty, "
					+ "            t1.evalprocrtoperiodpar, "
					+ "            p.name, "
					+ "            ROW_NUMBER() OVER (PARTITION BY p.name ORDER BY t1.rtoqty ASC) AS rto_rank "
					+ "        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.rto_rank = 1 "
					+ "ORDER BY "
					+ "    rtoqty_minutos ASC ";
			

			query = entityManager.createNativeQuery(SentenciaBase);
			query.setParameter("unitid", unitid);
			List<Object[]> listacompleta = query.getResultList();
			int cuantosregistros1 = listacompleta.size();
			System.out.print("cantidad de procesos");
			System.out.print(cuantosregistros1);
			String nameDetalle = "";
			
			if (cuantosregistros1>0) {
				   for (Object[] fila : listacompleta) { 
					   content.setProcess((String) fila[2]);
					   content.setRto(fila[0].toString()+" "+fila[1].toString());
					   contenList.add(content);
					   content = new Content3UnitResumeBCPDto();
				   }
			}
			
			section3.setTitle(title);
			section3.setContent(contenList);
			
			return section3;

		} catch (NoResultException e)  {
			content=new Content3UnitResumeBCPDto();
			contenList.add(content);
			
			section3.setTitle(title);
			section3.setContent(contenList);
			
			return section3;
			
		} catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return section3;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}

	}
}