package com.dacrt.SBIABackend.service;

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.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.dacrt.SBIABackend.dto.Content3UnitResumeBCPDto;
import com.dacrt.SBIABackend.dto.Content4UnitResumeBCPDto;
import com.dacrt.SBIABackend.dto.DetailSection4UnitResumeBCPDto;
import com.dacrt.SBIABackend.dto.Section3UnitResumenBCPDto;
import com.dacrt.SBIABackend.dto.Section4UnitResumenBCPDto;
import com.dacrt.SBIABackend.dto.StrategySection4UnitResumeBCPDto;
import com.dacrt.SBIABackend.dto.UnitsUsersDto2;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

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

	@PersistenceContext
	private EntityManager entityManager;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public Section4UnitResumenBCPDto getSection4Unitresumebcp(Integer unitid) {

		Query query;
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		Section4UnitResumenBCPDto section4 = new Section4UnitResumenBCPDto();
		Content4UnitResumeBCPDto content = new Content4UnitResumeBCPDto();
		StrategySection4UnitResumeBCPDto strategy = new StrategySection4UnitResumeBCPDto();
		List<StrategySection4UnitResumeBCPDto> strategyList = new ArrayList();
		List<Content4UnitResumeBCPDto> contentList = new ArrayList();
		DetailSection4UnitResumeBCPDto detail = new DetailSection4UnitResumeBCPDto();
		List<DetailSection4UnitResumeBCPDto> detailList = new ArrayList();
		String title="Relación de estrategias por Escenario";
		String SentenciaBase="";
		try {
			
			 SentenciaBase = " SELECT e.id AS idestra, STRING_AGG(cast(ed.id as text),'@')  AS idestradetallada,  pr1.descr1, STRING_AGG(ed.name,'@') AS namedeta,  "
			 		+ "	STRING_AGG(TO_CHAR(ed.modifiedat, 'DD/MM/YYYY'), '@') AS fechamodi, "
			 		+ "   STRING_AGG(COALESCE(s.name, a.name, f.name, po.name, r.name, ch.name),'@') AS nameescena, STRING_AGG(ed.dsc,'@') AS descdeta, e.name AS nombre, e.dsc AS descr "
			 		+ "FROM "
			 		+ "    main.strategies e "
			 		+ "LEFT JOIN ( "
			 		+ "    SELECT "
			 		+ "        elemento ->> 'filter' AS filter, "
			 		+ "        elemento ->> 'dsc' AS descr1, "
			 		+ "        elemento ->> 'value' AS valor1 "
			 		+ "    FROM "
			 		+ "        main.params p, "
			 		+ "        jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento "
			 		+ "    WHERE "
			 		+ "        p.paramname = 'STRATEGY_DEPENDENCY' "
			 		+ " ) pr1 ON e.strategydependencypar = CAST(pr1.valor1 AS integer) "
			 		+ " LEFT JOIN main.strategiesdet ed ON e.id = ed.strategyid and ed.status =1 "
			 		+ " INNER JOIN 	main.strategiesdetunits eu ON ed.id = eu.strategydetid and eu.unitid = " + unitid
			 		+ "  LEFT JOIN main.suppliers s ON ed.supplierid = s.id and s.status = 1 "
			 		+ " LEFT JOIN main.applications a ON ed.applicationid = a.id and a.status = 1 "
			 		+ " LEFT JOIN main.facilities f ON ed.facilityid = f.id and f.status = 1 "
			 		+ " LEFT JOIN main.positions po ON ed.positionid = po.id and po.status = 1 "
			 		+ " LEFT JOIN main.resources r ON ed.resourceid = r.id and r.status = 1 "
			 		+ " LEFT JOIN main.channels ch ON ed.channelid = ch.id and ch.status = 1 "
			 		+ " WHERE e.status = 1 AND e.strategytypepar = 1 "
			 		+ " GROUP BY  e.id, pr1.descr1, e.name, e.dsc "
			 		+ " ORDER BY "
			 		+ "    pr1.descr1 ASC ";

			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> resultados = query.getResultList();
		//	int cuantosreg = query.
			Long cuantosregistro = (long) jdbcTemplate.queryForList(SentenciaBase).size();
			
			if (cuantosregistro>0) {
			for (Object[] fila : resultados) {
				 strategy = new StrategySection4UnitResumeBCPDto();
				 detail = new DetailSection4UnitResumeBCPDto();
				 content = new Content4UnitResumeBCPDto();
				 strategy.setScenary((String) fila[2]);
				 strategy.setName((String) fila[7]);
				 strategy.setDsc((String) fila[8]);
				 
				   String IdStraDet = (String) fila[1];
				   String fechaStraDetString = (String) fila[4]; // AQUI VIENEN LAS FECHAS DE MODIFICACION
	    		   String NameStraDetString = (String) fila[3]; // AQUI VIENEN LOS NOMBRES  
	    		   String EscenaStraDetString = (String) fila[5]; // AQUI VIENEN LAS DEPDENDENCIAS
	    		   String DescStraDetString = (String) fila[6];   // AQUI VIENEN LAS DESCRIPCIONES
	    		   
				    if (IdStraDet != null && !IdStraDet.isEmpty()) {
				    	
				        String[] StraIds = IdStraDet.split("@");
				        String[] StraFecha = fechaStraDetString.split("@");
				        String[] StraName = NameStraDetString.split("@"); 
				        String[] StraEscena = EscenaStraDetString.split("@");
				        String[] StraDesc = DescStraDetString.split("@");
				        
				        int i = 0;
				        int j = 1;
				        detailList = new ArrayList<>();
				        for (String straIds : StraIds) {
				        	
				        //	detalleUnits2 = new UnitsUsersDto2();
				        	detail = new DetailSection4UnitResumeBCPDto();
				        	String FechaStra = StraFecha[i];
				        	String NameStra= StraName[i];
				        	String EscenaStra= StraEscena[i];
				        	String DescStra= StraDesc[i];
				        	NameStra = j + ". " + NameStra;
				        	detail.setName(NameStra);
				        	detail.setModifiedat(FechaStra);
				        	detail.setDsc(DescStra);
				        	detail.setDependency(EscenaStra);
				        	
				        
				        	detailList.add(detail);
				        	 j = j+1;
				             i = i+1;
				        }
				    }  else {
				    	System.out.print(IdStraDet);
				    	detail = new DetailSection4UnitResumeBCPDto();
				    	
				    
				    }		
				    
				    strategy.setDetail(detailList);
				    strategyList.add(strategy);
				    content.setStrategy(strategy);
				    contentList.add(content);
			}
			// contentList.add(content);
			} else {
				 content = new Content4UnitResumeBCPDto();
				 contentList = new ArrayList();
			}
			 
			 
			
		/*	strategy.setScenary("Provedores");
			strategy.setName("Lineamientos de estrategia");
			strategy.setDsc("Descripción");
			detail.setDependency("CTEA");
			detail.setDsc("Descripción");
			detail.setName("1. Estrategias Detallada");
			detail.setModifiedat(null);
			detailList.add(detail);
			strategy.setDetail(detailList);
			strategyList.add(strategy);
			content.setStrategy(strategyList);
			contentList.add(content);*/
			section4.setTitle(title);
			section4.setContent(contentList);
		    
			return section4;

		} catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return section4;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}

	}
}