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

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

	@PersistenceContext
	private EntityManager entityManager;

	public Section2UnitResumenBCPDto getSection2Unitresumebcp(Integer unitid) {

		Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		Section2UnitResumenBCPDto section2 = new Section2UnitResumenBCPDto();
		Content2UnitResumeBCPDto content = new Content2UnitResumeBCPDto();
		StrategyUnitResumeBCPDto strategy = new StrategyUnitResumeBCPDto();
		List<Content2UnitResumeBCPDto> contentList = new ArrayList();
		List<String> detail = new ArrayList();
		String title="Escenarios Tipo Considerados y Estrategias";
		try {
			
		/*	String SentenciaBase = "SELECT pr.descr AS scenary, "
					+ "                    s.name AS name, "
					+ "                    STRING_AGG(distinct sd.name, ' ; ') AS detail  "
					+ "             FROM main.strategiesdet sd    "
					+ "             JOIN main.strategies s ON sd.strategyid = s.id "
					+ "			    JOIN main.strategiesdetprocesses sdet ON sd.id = sdet.strategyid  "
					+ "				JOIN main.unitprocesses up ON up.id=sdet.unitprocessid and up.deleted is null "
					+ "             INNER JOIN  (SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor  "
					+ "			                 FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento  "
					+ "			                 WHERE p.paramname = 'STRATEGY_DEPENDENCY'  "
					+ "			                 ) pr ON s.strategydependencypar = CAST(pr.valor AS integer)"
					+ "             WHERE up.unitid = :unitid "
					+ "             GROUP BY s.strategydependencypar, s.name,pr.descr "
					+ "             ORDER BY s.strategydependencypar ASC, s.name ASC;";*/

			String SentenciaBase = "select pr.descr AS scenary,   "
					+ "					                     s.name AS name,   "
					+ "					                     STRING_AGG(distinct ed.name, ' ; ') AS detail   FROM main.strategies s      "
					+ "					              LEFT JOIN  main.strategiesdet ed ON ed.strategyid = s.id and ed.status =1   "
					+ "                           LEFT JOIN  main.strategiesdetprocesses sdet ON ed.id = sdet.strategyid  "
					+ " inner JOIN  main.strategiesdetunits suni ON ed.id = suni.strategydetid and suni.unitid =  :unitid  "
					+ "INNER JOIN  (SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor    "
					+ "					 			                 FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento    "
					+ "					 			                 WHERE p.paramname = 'STRATEGY_DEPENDENCY'    "					
					+ "					 			                 ) pr ON s.strategydependencypar = CAST(pr.valor AS integer) "
					+ " WHERE s.status = 1   "
					+ "   "
					+ " GROUP BY s.strategydependencypar, s.name,pr.descr   "
					+ "					              ORDER BY s.strategydependencypar ASC, s.name ASC ";
			query = entityManager.createNativeQuery(SentenciaBase);
			query.setParameter("unitid", unitid);
			List<Object[]> listacompleta = query.getResultList();
			int cuantosregistros1 = listacompleta.size();
			String nameDetalle = "";
			
			if (cuantosregistros1>0) {
				   for (Object[] fila : listacompleta) { 
					   strategy.setScenary((String)fila[0]);
					   strategy.setName((String)fila[1]);
					   nameDetalle = (String) fila[2];
					   if (nameDetalle != null && !nameDetalle.isEmpty() ) {
						   String[] NameDetalles = nameDetalle.split(";");
						   for (String detalle : NameDetalles) {
							   String name = detalle;
							   detail.add(name);
							   name = "";
						   }
					   }else
						   detail = new ArrayList();
					   
					   strategy.setDetail(detail);
					   content.setStrategy(strategy);
					   contentList.add(content);
					   strategy = new StrategyUnitResumeBCPDto();
					   content = new Content2UnitResumeBCPDto();
					   detail = new ArrayList();
				   }
			}
			
			section2.setTitle(title);
			section2.setContent(contentList);
			
			return section2;

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

	}

	
}