package com.dacrt.SBIABackend.service;

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

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

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

import com.dacrt.SBIABackend.dto.ImpactAppsresumeDto;
import com.dacrt.SBIABackend.dto.ImpactContentAppReportDto;
import com.dacrt.SBIABackend.dto.ImpactContentScopeDto;
import com.dacrt.SBIABackend.dto.ImpactReportScoperesumeDto;
import com.dacrt.SBIABackend.dto.ImpactReportSection1Dto;
import com.dacrt.SBIABackend.dto.ImpactReportSection4Dto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
@Transactional
public class ImpactReportSection1Service {

	//ImpactReportSection1Dto
Logger logger = LoggerFactory.getLogger(ImpactReportSection1Service.class);
	
	@PersistenceContext
	private EntityManager entityManager;

   public ImpactReportSection1Dto getImpactResumeCampaigns(int idcampana,String contentin) {
		
		List<ImpactReportScoperesumeDto> listaImpactReportScoperesumeDto=new ArrayList();
		
		ImpactReportSection1Dto detalleImpactReportSection1Dto = new ImpactReportSection1Dto();
		ImpactContentScopeDto detalleImpactContentScopeDto = new ImpactContentScopeDto();
		ImpactReportScoperesumeDto detalleImpactReportScoperesumeDto = new ImpactReportScoperesumeDto();
		ImpactReportScoperesumeDto detalleImpactReportScoperesumeDto2 = new ImpactReportScoperesumeDto();
		ImpactReportScoperesumeDto detalleImpactReportScoperesumeDto3 = new ImpactReportScoperesumeDto();
		ImpactReportScoperesumeDto detalleImpactReportScoperesumeDto4 = new ImpactReportScoperesumeDto();
		ImpactReportScoperesumeDto detalleImpactReportScoperesumeDto5 = new ImpactReportScoperesumeDto();
		//String fecha2=fecha;
	
		
		
		Query query;
		
		/*String SentenciaBase =" SELECT 'Unidades con evaluaciones' AS name, count(distinct up.unitid) AS qty "
				+ "FROM main.campaigns c "
				+ "INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana "
				+ "INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id and e.impactst=1 "
				+ "INNER JOIN main.evalprocimpacts ev ON ev.evalprocessid=e.id "
				+ "INNER JOIN main.unitprocesses up ON up.id = unitprocessid "
				+ "UNION  "
				+ "SELECT 'Cantidad de procesos evaluados' AS name, count(distinct up.processid) AS qty "
				+ "FROM main.campaigns c "
				+ "INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana "
				+ "INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id and e.impactst=1 "
				+ "INNER JOIN main.evalprocimpacts ev ON ev.evalprocessid=e.id "
				+ "INNER JOIN main.unitprocesses up ON up.id = unitprocessid "
				+ "UNION "
				+ "SELECT 'Cantidad de aplicaciones calificadas' AS name, count(distinct a.id) AS qty "
				+ "FROM main.campaigns c "
				+ "INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana "
				+ "INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id  "
				+ "INNER JOIN main.evalprocapps eva ON eva.evalprocessid=e.id AND e.appst=1 "
				+ "INNER JOIN main.applications a ON a.id=eva.applicationid "
				+ "UNION "
				+ "SELECT 'Cantidad de registros vitales identificados' AS name, count(distinct e.id) AS qty "
				+ "FROM main.campaigns c "
				+ "INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana "
				+ "INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id and reportst=1 "
				+ "INNER JOIN main.evalprocrecords er ON er.evalprocessid=e.id "
				//+ "INNER JOIN main.regreports r ON r.id=er.regreportid "
				+ "UNION "
				+ "SELECT 'Cantidad de proveedores calificados' AS name, count(distinct s.id) AS qty "
				+ "FROM main.campaigns c "
				+ "INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana "
				+ "INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id and suplierst=1 "
				+ "INNER JOIN main.evalprocsupliers es ON es.evalprocessid=e.id "
				+ "INNER JOIN main.suppliers s ON s.id=es.supplierid "
				+ "order by name";*/
		String SentenciaBase ="SELECT 'Unidades con evaluaciones' AS name, count(distinct up.unitid) AS qty  "
				+ "				 FROM main.campaigns c  "
				+ "				 INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana"
				+ "				 INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id and e.impactst=1  "
				+ "				 INNER JOIN main.evalprocimpacts ev ON ev.evalprocessid=e.id  "
				+ "				 INNER JOIN main.unitprocesses up ON up.id = unitprocessid  "
				+ "				 UNION   "
				+ "				 SELECT 'Cantidad de procesos evaluados' AS name, count(distinct up.processid) AS qty  "
				+ "				 FROM main.campaigns c  "
				+ "				 INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana  "
				+ "				 INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id and e.impactst=1  "
				+ "				 INNER JOIN main.evalprocimpacts ev ON ev.evalprocessid=e.id  "
				+ "				 INNER JOIN main.unitprocesses up ON up.id = unitprocessid  "
				+ "				 UNION  "
				+ "				 SELECT 'Cantidad de aplicaciones calificadas' AS name, count(distinct a.id) AS qty  "
				+ "				 FROM main.campaigns c  "
				+ "				 INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana  "
				+ "				 INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id   "
				+ "				 INNER JOIN main.evalprocapps eva ON eva.evalprocessid=e.id AND e.impactst=1 AND e.appst=1  "
				+ "				 INNER JOIN main.applications a ON a.id=eva.applicationid  "
				+ "				 UNION  "
				+ "				 SELECT 'Cantidad de registros vitales identificados' AS name, count(er.id) AS qty  "
				+ "				 FROM main.campaigns c  "
				+ "				 INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana "
				+ "				 INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id AND e.impactst=1 AND e.recordst=1  "
				+ "				 INNER JOIN main.evalprocrecords er ON er.evalprocessid=e.id  "
				+ "				 UNION  "
				+ "				 SELECT 'Cantidad de proveedores calificados' AS name, count(distinct s.id) AS qty  "
				+ "				 FROM main.campaigns c  "
				+ "				 INNER JOIN main.campaignunits cu ON c.id=cu.campaingid AND c.id=:idcampana  "
				+ "				 INNER JOIN main.evalprocesses e ON e.campaignunitid=cu.id and e.impactst=1 AND suplierst=1  "
				+ "				 INNER JOIN main.evalprocsupliers es ON es.evalprocessid=e.id  "
				+ "				 INNER JOIN main.suppliers s ON s.id=es.supplierid  "
				+ "				 order by name";
		
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		long cuantosregistro;
		
		try {						
				
			query = entityManager.createNativeQuery(SentenciaBase);
			query.setParameter("idcampana", idcampana);
			cuantosregistro = (long) query.getResultList().size();
			List<Object[]> listacompleta = query.getResultList();
			
			for (Object[] fila : listacompleta) {
				detalleImpactReportScoperesumeDto.setName((String) fila[0]);
				detalleImpactReportScoperesumeDto.setQty((BigInteger) fila[1]==null?BigInteger.ZERO:(BigInteger) fila[1]);
				listaImpactReportScoperesumeDto.add(detalleImpactReportScoperesumeDto);
				detalleImpactReportScoperesumeDto = new ImpactReportScoperesumeDto();
			}
			
			detalleImpactContentScopeDto.setScoperesume(listaImpactReportScoperesumeDto);
			detalleImpactReportSection1Dto.setContent(detalleImpactContentScopeDto);
			detalleImpactReportSection1Dto.setTitle("Campaña uno del año 2025");
		
		
			return detalleImpactReportSection1Dto;
		
		} catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor", false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return detalleImpactReportSection1Dto;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		 }
		
     }

}