package com.dacrt.SBIABackend.service;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
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.http.HttpStatus;
import org.springframework.stereotype.Service;

import com.dacrt.SBIABackend.dto.DrpstrategiesDto;
import com.dacrt.SBIABackend.dto.QtyDto;
import com.dacrt.SBIABackend.dto.RecuperationgapsDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;

@Service
public class RecuperationgapsService {
	Logger logger = LoggerFactory.getLogger(RecuperationgapsService.class);
	
	@PersistenceContext
	private EntityManager entityManager;
	
	public List<RecuperationgapsDto> getRecuperationgaps(String fecha) {
		
		String fecha2=fecha;
		int ano=0,mes=0,dia=0;
		
		ano=Integer.parseInt(fecha2.substring(0, 4));
		mes=Integer.parseInt(fecha2.substring(4, 6));
		dia=Integer.parseInt(fecha2.substring(6, 8));
		
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		List<RecuperationgapsDto> listado=new ArrayList();
		try {
			
			Query query;
			String SentenciaBase = "SELECT 'Cantidad de instalaciones con Atestamiento Vencido',count(f.id) "
					+ "             FROM main.facilities f "
					+ "             WHERE f.expiration <= TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD')   ";
	
			
			query = entityManager.createNativeQuery(SentenciaBase);
			List<Object[]> listacompleta = query.getResultList();
			
			
			RecuperationgapsDto recuperationgapsDto= new RecuperationgapsDto();
			for (Object[] reg : listacompleta) {
				recuperationgapsDto.setName((String) reg[0]);
				recuperationgapsDto.setQty(BigInteger.valueOf(((Number) reg[1]).longValue()));
				listado.add(recuperationgapsDto);
			}
			
			Query query2;
			
			SentenciaBase="              SELECT   "
					+ "					     'Cantidad de aplicaciones fuera de RTO esperado' AS nombre,   "
					+ "					     COALESCE(SUM(X.sumar), 0) AS cnt   "
					+ "					 FROM (   "
					+ "					     SELECT   "
					+ "					         C.campaingid,   "
					+ "					         AP.name AS aplicacion,   "
					+ "					         AP.registered,   "
					+ "					         MIN(I.rtoqty) AS rtoqty,    "
					+ "					         (CASE WHEN (AP.registered > MIN(I.rtoqty) OR AP.drptestedpar=2 ) THEN 1 ELSE 0 END) AS sumar   "
					+ "					     FROM   "
					+ "					         main.evalprocimpacts I   "
					+ "					     INNER JOIN   "
					+ "					         main.evalprocesses EP ON I.evalprocessid = EP.id AND EP.appst = 1   "
					+ "					     INNER JOIN   "
					+ "					         main.evalprocapps PA ON EP.id = PA.evalprocessid  "
					+ "					     INNER JOIN   "
					+ "					         (   "
					+ "					             SELECT   "
					+ "					                 id,    "
					+ "					                 campaignunitid,   "
					+ "					                 campaingid,    "
					+ "					                 applicationid,   "
					+ "					                 latest_createdat,    "
					+ "					                 rtoqty_for_ranking,    "
					+ "					                 ABS(EXTRACT(EPOCH FROM latest_createdat  - CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )   )) AS time_difference_seconds,   "
					+ "					                 ROW_NUMBER() OVER (   "
					+ "					                     PARTITION BY applicationid   "
					+ "					                     ORDER BY   "
					+ "					                         ABS(EXTRACT(EPOCH FROM latest_createdat  - CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )    )) ASC,    "
					+ "					                         rtoqty_for_ranking ASC,    "
					+ "					                         latest_createdat DESC,   "
					+ "					                         id DESC    "
					+ "					                 ) AS rn   "
					+ "					             FROM   "
					+ "					                 (   "
					+ "					                     SELECT   "
					+ "					                         EP.id AS id,    "
					+ "					                         EP.campaignunitid,   "
					+ "					                         CU.campaingid,    "
					+ "					                         PA.applicationid,   "
					+ "					                         EP.createdat AS latest_createdat,    "
					+ "					                         (SELECT MIN(sub_i.rtoqty) FROM main.evalprocimpacts sub_i WHERE sub_i.evalprocessid = EP.id) AS rtoqty_for_ranking   "
					+ "					                     FROM   "
					+ "					                         main.evalprocapps PA   "
					+ "					                     INNER JOIN   "
					+ "					                         main.evalprocesses EP ON PA.evalprocessid = EP.id   "
					+ "					                     INNER JOIN   "
					+ "					                         main.campaignunits CU ON EP.campaignunitid = CU.id    "
					+ "					                     WHERE   "
					+ "					                         EP.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )    "
					+ "											 AND EP.appst = 1  AND EP.impactst = 1  "
					+ "					                 ) AS ProcessDetailsForRanking   "
					+ "					         ) AS C   "
					+ "					         ON EP.id = C.id    "
					+ "					         AND EP.campaignunitid = C.campaignunitid    "
					+ "					         AND PA.applicationid = C.applicationid    "
					+ "					     INNER JOIN   "
					+ "					         main.applications AP ON C.applicationid = AP.id   "
					+ "					     WHERE   "
					+ "					         C.rn = 1    "
					+ "					     GROUP BY  "
					+ "					         C.campaingid, C.campaignunitid, AP.name, AP.registered,AP.drptestedpar     "
					+ "					 ) AS X";
			
			/*SentenciaBase="SELECT "
					+ "    'Cantidad de aplicaciones fuera de RTO esperado' AS nombre, "
					+ "    COALESCE(SUM(X.sumar), 0) AS cnt "
					+ "FROM ( "
					+ "    SELECT "
					+ "        C.campaingid, "
					+ "        AP.name AS aplicacion, "
					+ "        AP.registered, "
					+ "        MIN(I.rtoqty) AS rtoqty,  "
					+ "        (CASE WHEN (AP.registered > MIN(I.rtoqty)) THEN 1 ELSE 0 END) AS sumar "
					+ "    FROM "
					+ "        main.evalprocimpacts I "
					+ "    INNER JOIN "
					+ "        main.evalprocesses EP ON I.evalprocessid = EP.id AND EP.appst = 1 "
					+ "    INNER JOIN "
					+ "        main.evalprocapps PA ON EP.id = PA.evalprocessid"
					+ "    INNER JOIN "
					+ "        ( "
					+ "            SELECT "
					+ "                id,  "
					+ "                campaignunitid, "
					+ "                campaingid,  "
					+ "                applicationid, "
					+ "                latest_createdat,  "
					+ "                rtoqty_for_ranking,  "
					+ "                ABS(EXTRACT(EPOCH FROM latest_createdat - CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz ) "+")) AS time_difference_seconds, "
					+ "                ROW_NUMBER() OVER ( "
					+ "                    PARTITION BY applicationid "
					+ "                    ORDER BY "
					+ "                        ABS(EXTRACT(EPOCH FROM latest_createdat - CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  "+")) ASC,  "
					+ "                        rtoqty_for_ranking ASC,  "
					+ "                        latest_createdat DESC, "
					+ "                        id DESC  "
					+ "                ) AS rn "
					+ "            FROM "
					+ "                ( "
					+ "                    SELECT "
					+ "                        EP.id AS id,  "
					+ "                        EP.campaignunitid, "
					+ "                        CU.campaingid,  "
					+ "                        PA.applicationid, "
					+ "                        EP.createdat AS latest_createdat,  "
					+ "                        (SELECT MIN(sub_i.rtoqty) FROM main.evalprocimpacts sub_i WHERE sub_i.evalprocessid = EP.id) AS rtoqty_for_ranking "
					+ "                    FROM "
					+ "                        main.evalprocapps PA "
					+ "                    INNER JOIN "
					+ "                        main.evalprocesses EP ON PA.evalprocessid = EP.id "
					+ "                    INNER JOIN "
					+ "                        main.campaignunits CU ON EP.campaignunitid = CU.id  "
					+ "                    WHERE "
					+ "                        EP.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz)  "
					+ "                ) AS ProcessDetailsForRanking "
					+ "        ) AS C "
					+ "        ON EP.id = C.id  "
					+ "        AND EP.campaignunitid = C.campaignunitid  "
					+ "        AND PA.applicationid = C.applicationid  "
					+ "    INNER JOIN "
					+ "        main.applications AP ON C.applicationid = AP.id "
					+ "    WHERE "
					+ "        C.rn = 1  "
					+ "    GROUP BY\r\n"
					+ "        C.campaingid, C.campaignunitid, AP.name, AP.registered "
					+ "    HAVING "
					+ "        AP.registered > MIN(I.rtoqty)  "
					+ ") AS X";*/
			
			/*SentenciaBase = "SELECT 'Cantidad de aplicaciones fuera de RTO esperado' AS nombre,SUM(X.sumar) cnt "
					+ "      FROM ( SELECT AP.id, AP.name, AP.registered, MIN(I.rtoqty) rtoqty,  "
					+ "             (CASE WHEN (AP.recuperation>MIN(I.rtoqty)) THEN 1 ELSE 0 END) sumar "
					+ "             FROM   main.evalprocimpacts I  "
					+ "	            INNER JOIN  (SELECT MAX(id) id, unitprocessid  "
					+ "	                         FROM main.evalprocesses  "
					+ "					         WHERE COALESCE(modifiedat, createdat)<=TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD')   "
					+ "					         GROUP BY unitprocessid) EP "
					+ "       ON     I.evalprocessid=EP.id "
					+ "       INNER JOIN  main.evalprocapps PA "
					+ "       ON     I.evalprocessid=PA.evalprocessid "
					+ "       INNER JOIN  main.applications AP "
					+ "       ON     PA.applicationid=AP.id "
					+ "       GROUP BY    AP.id, AP.name, AP.registered "
					+ "     ) X";*/
	
			System.out.println(SentenciaBase);
			query2 = entityManager.createNativeQuery(SentenciaBase);
			listacompleta = query2.getResultList();
			
			
			recuperationgapsDto= new RecuperationgapsDto();
			for (Object[] reg : listacompleta) {
				recuperationgapsDto.setName((String) reg[0]);
				recuperationgapsDto.setQty(BigInteger.valueOf(((Number) reg[1]).longValue()));
				listado.add(recuperationgapsDto);
			}
			
			Query query3;
			
			SentenciaBase ="                SELECT   "
					+ "    'Cantidad de canales fuera de RTO esperado' AS nombre, "
					+ "    COALESCE(SUM(X.sumar), 0) AS cnt "
					+ "	   FROM ( "
					+ "       SELECT "
					+ "          C.campaingid, "
					+ "          AP.name AS canal, "
					+ "          AP.capacity, "
					+ "          MIN(I.rtoqty) AS rtoqty, "
					+ "					           (CASE WHEN (AP.capacity > MIN(I.rtoqty) OR AP.capacity =0 ) THEN 1 ELSE 0 END) AS sumar   "
					+ "       FROM "
					+ "          main.evalprocimpacts I "
					+ "       INNER JOIN "
					+ "          main.evalprocesses EP ON I.evalprocessid = EP.id  "
					+ "       INNER JOIN main.campaignunits CU ON EP.campaignunitid = CU.id "
					+ "       INNER JOIN main.unitprocesses up ON up.id=EP.unitprocessid "
					+ "       INNER JOIN main.processchannels pc ON pc.processid=up.processid "
					+ "    INNER JOIN "
					+ "        ( "
					+ "            SELECT "
					+ "                id,  "
					+ "                campaignunitid, "
					+ "                campaingid,  "
					+ "                channelid, "
					+ "                latest_createdat,  "
					+ "                rtoqty_for_ranking,  "
					+ "					                 ABS(EXTRACT(EPOCH FROM latest_createdat - CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz ) )) AS time_difference_seconds,   "
					+ "                ROW_NUMBER() OVER ( "
					+ "                    PARTITION BY channelid "
					+ "                    ORDER BY "
					+ "					                         ABS(EXTRACT(EPOCH FROM latest_createdat - CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz )  )) ASC,   "
					+ "                        rtoqty_for_ranking ASC,  "
					+ "                        latest_createdat DESC,  "
					+ "                        id DESC  "
					+ "                ) AS rn "
					+ "            FROM "
					+ "                ( "
					+ "                    SELECT EP.id AS id, "
					+ "                        EP.campaignunitid, "
					+ "                        CU.campaingid,  "
					+ "                        pc.channelid, "
					+ "                        EP.createdat AS latest_createdat,  "
					+ "                        COALESCE((SELECT MIN(sub_i.rtoqty) FROM main.evalprocimpacts sub_i WHERE sub_i.evalprocessid = EP.id),0) AS rtoqty_for_ranking "
					+ "					FROM "
					+ "                        main.evalprocesses EP  "
					+ "                    INNER JOIN "
					+ "                        main.campaignunits CU ON EP.campaignunitid = CU.id  "
					+ "					INNER JOIN "
					+ "                    	main.unitprocesses up ON up.id=EP.unitprocessid "
					+ "					INNER JOIN "
					+ "						main.processchannels pc ON pc.processid=up.processid "
					+ "                    WHERE "
					+ "					                         EP.createdat <= CAST(TO_DATE('"+ ano +"/"+ mes + "/" + dia +"','YYYY/MM/DD') AS timestamptz ) AND EP.impactst = 1  "
					+ "                 ) AS ProcessDetailsForRanking "
					+ "		   WHERE ProcessDetailsForRanking.rtoqty_for_ranking<>0 "
					+ "        ) AS C "
					+ "        ON EP.id = C.id   "
					+ "        AND EP.campaignunitid = C.campaignunitid  "
					+ "        AND pc.channelid = C.channelid  "
					+ "    INNER JOIN "
					+ "					         main.channels AP ON C.channelid = AP.id AND AP.status=1 "
					+ "    WHERE "
					+ "        C.rn = 1 "
					+ "    GROUP BY "
					+ "        C.campaingid, C.campaignunitid, AP.name, AP.capacity "
					+ "					 		) X";
			
			System.out.println(SentenciaBase);
			query3 = entityManager.createNativeQuery(SentenciaBase);
			listacompleta = query3.getResultList();
			
			
			recuperationgapsDto= new RecuperationgapsDto();
			for (Object[] reg : listacompleta) {
				recuperationgapsDto.setName((String) reg[0]);
				recuperationgapsDto.setQty(BigInteger.valueOf(((Number) reg[1]).longValue()));
				listado.add(recuperationgapsDto);
			}
			
			return listado;
		
		}catch (Exception e) {
			// Manejo de excepciones
			respuesta = new RespuestaDto("Error interno del servidor"+e.getMessage(), false);
			estatus = HttpStatus.INTERNAL_SERVER_ERROR;
			return listado;
		} finally {
			if (entityManager != null && entityManager.isOpen()) {
				entityManager.close();
			}
		}
		
 	}
}