package com.dacrt.SBIABackend.controler;

import java.math.BigDecimal;

import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Optional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;

import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.dacrt.SBIABackend.dto.ApplicationDto;
import com.dacrt.SBIABackend.dto.ApplicationDto2;
import com.dacrt.SBIABackend.dto.ApplicationResponseDto2;
import com.dacrt.SBIABackend.dto.AppstrategyHeaderDto;
import com.dacrt.SBIABackend.dto.AppstrategyResponseDto;
import com.dacrt.SBIABackend.dto.CampaignFormat2Dto;
import com.dacrt.SBIABackend.dto.ContentDto;
import com.dacrt.SBIABackend.dto.ContentExpectativaRtoDto;
import com.dacrt.SBIABackend.dto.DrptesttypesDto;
import com.dacrt.SBIABackend.dto.ExpectativasHeaderRtoDto;
import com.dacrt.SBIABackend.dto.ExpectativasRtoResponseDto;
import com.dacrt.SBIABackend.dto.FacilityDto2;
import com.dacrt.SBIABackend.dto.LabelExpectativaDto;
import com.dacrt.SBIABackend.dto.RecordCatalogoResponseDto;
import com.dacrt.SBIABackend.dto.SectionDto;
import com.dacrt.SBIABackend.dto.StatusDto;
import com.dacrt.SBIABackend.dto.TestTypeResquestDto;
import com.dacrt.SBIABackend.dto.UnitsExpectativasDto;
import com.dacrt.SBIABackend.dto.ValueDto;
import com.dacrt.SBIABackend.dto.responseDto.ApplicationResponseDto;
import com.dacrt.SBIABackend.repository.ApplicationsRepository;
import com.dacrt.SBIABackend.repository.FacilitiesRepository;
import com.dacrt.SBIABackend.repository.SuppliersRepository;
import com.dacrt.SBIABackend.security.dto.AuditRequestDto;
import com.dacrt.SBIABackend.security.dto.PrivilegesAllDto;
import com.dacrt.SBIABackend.security.dto.RespuestaDto;
import com.dacrt.SBIABackend.security.dto.RespuestaMsgDto;
import com.dacrt.SBIABackend.security.entity.Roles;
import com.dacrt.SBIABackend.security.entity.Users;
import com.dacrt.SBIABackend.security.repository.AuditRepository;
import com.dacrt.SBIABackend.security.repository.ParamsRepository;
import com.dacrt.SBIABackend.security.repository.RolesPrivilegesRepository;
import com.dacrt.SBIABackend.security.repository.UsersRepository;
import com.dacrt.SBIABackend.security.service.MenuService;
import com.dacrt.SBIABackend.security.service.ParamsService;
import com.dacrt.SBIABackend.security.service.SecurityService;
import com.dacrt.SBIABackend.security.service.UsersService;

@RestController
//@RequestMapping("/units")   
@CrossOrigin(origins = "*")
public class ExpectativaCumplimientoController {
	@Autowired
	private ParamsRepository paramsRepository;

	@Autowired
	private UsersRepository usersRepository;

	@Autowired
	private AuditRepository auditRepository;

	@Autowired
	private RolesPrivilegesRepository rolesPrivilegesRepository;

	@Autowired
	UsersService usersService;

	@Autowired
	ParamsService paramsService;

	@Autowired
	SecurityService securityService;

	@Autowired
	MenuService menuService;

	@PersistenceContext
	private EntityManager entityManager;

	@Autowired
	SuppliersRepository suppliersRepository;
	
	@Autowired
	ApplicationsRepository applicationsRepository;
	
	@Autowired
	FacilitiesRepository facilitiesRepository;
	
	@PostMapping("/dashboard/rtocompliancespec")
	public ResponseEntity<?> expectativas(HttpServletRequest request, 
			@RequestBody UnitsExpectativasDto tiposfiltros)
			throws ParseException {
		RespuestaDto respuesta = new RespuestaDto("", false);
		HttpStatus estatus = HttpStatus.FORBIDDEN;
		
		PrivilegesAllDto detallePrivilege;
		Long cuantosregistro = (long) 0;

		List<PrivilegesAllDto> listasPrivelege = new ArrayList<>();

		String sessionid = request.getHeader("Authorization");
		Date fecha = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dataFormattata = formatter.format(fecha);
		Date fechaDate = formatter.parse(dataFormattata);
		AuditRequestDto auditDto = new AuditRequestDto();

		int idrol;
		int unitsIn = 0;
		int iduser;
		String fechaComoCadena;
		
		Date fecha2 = new Date();

		if (sessionid == null) {
			String var = "";
			boolean bloked = false;
			RespuestaDto respuestaDto = new RespuestaDto(var, bloked);
			respuestaDto.setBlocked(bloked);
			respuestaDto.setMsg("Sesión expirada o inválida");
			// Error 400
			return new ResponseEntity(respuestaDto, HttpStatus.BAD_REQUEST);
		} else {

			sessionid = sessionid.substring(7);
			Optional<Users> encontreSessionUsuario = usersRepository.getBySessionid(sessionid);
			iduser = encontreSessionUsuario.get().getId();
			// String usuarioIN = encontreSessionUsuario.get().getUsr();
			// int position = usuarioIN.indexOf('(');

			if (encontreSessionUsuario.isPresent()) {

				Date FechaReg = encontreSessionUsuario.get().getValidthru();

				// fechaComoCadena = securityService.consultarSessionActiva(FechaReg,fecha2);
				fechaComoCadena = securityService.consultarSessionActiva(FechaReg, fecha2,
						encontreSessionUsuario.get().getId());

				if (fechaComoCadena == "") {

					String var = "";
					boolean bloked = false;
					RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var);
					// respuestaDto.setBlocked(bloked);
					respuestaDto.setMsg("Sesión expirada o inválida");
					return new ResponseEntity(respuestaDto, HttpStatus.UNAUTHORIZED);

				}
				
				Roles roles = encontreSessionUsuario.get().getRolid();
				idrol = roles.getId();
				int rolisvalid = auditRepository.getCantbyRolAndPrivi(idrol, 700);
				if (rolisvalid == 0) {
					String var = "";
					boolean bloked = false;
					RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var);
					// respuestaDto.setBlocked(bloked);
					respuestaDto.setMsg("No tiene los Privilegios");
					return new ResponseEntity(respuestaDto, HttpStatus.FORBIDDEN);

				}
				

				unitsIn = tiposfiltros.getUnits();

			} else {
				String var = "";
				boolean bloked = false;
				RespuestaDto respuestaDto = new RespuestaDto(var, bloked);
				respuestaDto.setBlocked(bloked);
				respuestaDto.setMsg("Sesión expirada o inválida");
				// Error 400
				return new ResponseEntity(respuestaDto, HttpStatus.UNAUTHORIZED);
			}
		}

		try {

			String SentenciaBaseIn1 = "";
			String SentenciaBaseIn2 = "";
			Query query;
			Query query2;
			String InUnidades =   " INNER JOIN main.unitprocesses up ON EP.unitprocessid = up.id "
								+ " INNER JOIN main.processes p ON up.processid = p.id  ";
			String filtroUnit = " INNER JOIN main.userunits uu ON up.unitid = uu.unitid AND uu.userid = " + iduser;		   
							
			if (unitsIn == 0) {
				InUnidades=InUnidades;
			} else
				InUnidades =InUnidades +filtroUnit;
			
			/*SentenciaBaseIn1 = "SELECT "
					+ "  'No Cubierto' AS nocubierto, "
					+ "  COALESCE(SUM(CASE WHEN AP.registered > I.min_rtoqty THEN 1 ELSE 0 END), 0) AS cnt, "
					+ "  'Cubierto' AS cubierto, "
					+ "  COALESCE(SUM(CASE WHEN AP.registered <= I.min_rtoqty THEN 1 ELSE 0 END), 0) AS cnt1 "
					+ "FROM ( "
					+ "  SELECT "
					+ "    C.campaingid, "
					+ "    PA.applicationid, "
					+ "    MIN(I.rtoqty) AS min_rtoqty "
					+ "  FROM "
					+ "    main.evalprocimpacts I "
					+ "  INNER JOIN "
					+ "    main.evalprocesses EP ON I.evalprocessid = EP.id "
					+ "  INNER JOIN "
					+ "    main.evalprocapps PA ON EP.id = PA.evalprocessid "
					+ InUnidades
					+ "  INNER JOIN "
					+ "    ( "
					+ "      SELECT "
					+ "        id, "
					+ "        campaignunitid, "
					+ "        campaingid, "
					+ "        applicationid, "
					+ "        ROW_NUMBER() OVER ( "
					+ "          PARTITION BY applicationid "
					+ "          ORDER BY "
					+ "            ABS(EXTRACT(EPOCH FROM latest_createdat - CURRENT_DATE)) 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 <= CURRENT_DATE "
					+ "      ) AS ProcessDetailsForRanking "
					+ "    ) AS C ON EP.id = C.id "
					+ "  WHERE "
					+ "    C.rn = 1 AND EP.appst = 1 "
					+ "  GROUP BY "
					+ "    C.campaingid, PA.applicationid "
					+ ") AS I "
					+ "INNER JOIN "
					+ "  main.applications AP ON I.applicationid = AP.id";*/				
			
			SentenciaBaseIn1 = "SELECT   "
					+ "		'No Cubierto' AS nocubierto,   "
					+ "		COALESCE(SUM(CASE WHEN AP.registered > I.min_rtoqty OR AP.drptestedpar=2 THEN 1 ELSE 0 END), 0) AS cnt,   "
					+ "		'Cubierto' AS cubierto,   "
					+ "		COALESCE(SUM(CASE WHEN AP.registered <= I.min_rtoqty AND AP.drptestedpar=1 THEN 1 ELSE 0 END), 0) AS cnt1    "
					+ "FROM (   "
					+ "		SELECT   "
					+ "			PA.applicationid,   "
					+ "			MIN(I.rtoqty) AS min_rtoqty   "
					+ "		FROM   "
					+ "			main.evalprocimpacts I   "
					+ "		INNER JOIN   "
					+ "			main.evalprocesses EP ON I.evalprocessid = EP.id   "
					+ "		INNER JOIN   "
					+ "			main.evalprocapps PA ON EP.id = PA.evalprocessid   "
					+ "		INNER JOIN   "
					+ "			main.applications a ON a.id = PA.applicationid "
					+ "		INNER JOIN  "
					+ "			main.campaignunits cu ON EP.campaignunitid = cu.id "
					+ "		INNER JOIN  "
					+ "			main.unitprocesses up ON ep.unitprocessid = up.id "
					+ "        INNER JOIN  "
					+ "			main.processes p ON up.processid = p.id "
					+ "		INNER JOIN   "
					+ "			(   "
					+ "				SELECT   "
					+ "					  ids,   "
					+ "					  applicationid,   "
					+ "					  ROW_NUMBER() OVER (   "
					+ "					   PARTITION BY applicationid   "
					+ "					   ORDER BY   "
					+ "					   ABS(EXTRACT(EPOCH FROM latest_createdat - CURRENT_DATE)) ASC,   "
					+ "					   rtoqty_for_ranking ASC,   "
					+ "					   latest_createdat DESC,   "
					+ "					   ids DESC "
					+ "					   ) AS rn   "
					+ "				FROM (   "
					+ "					   SELECT   "
					+ "					      EP.id AS ids,   "
					+ "					      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   "
					+ "						  INNER JOIN  "
					+ "							   main.unitprocesses up ON ep.unitprocessid = up.id "
					+ "		                  INNER JOIN  "
					+ "							   main.processes p ON up.processid = p.id "
					+ "					       WHERE   "
					+ "					           EP.createdat <= CURRENT_DATE AND  "
					+ "							   EP.appst = 1  AND EP.impactst = 1  "
					+ "						   GROUP BY  ids,EP.campaignunitid, CU.campaingid, PA.applicationid "
					+ "					       ) AS ProcessDetailsForRanking   "
					+ "					     ) AS C ON EP.id = C.ids   "
					+ "					   WHERE   "
					+ "					     C.rn = 1 AND ep.appst = 1  AND ep.impactst = 1 "
					+ "					   GROUP BY PA.applicationid   "
					+ "					 ) AS I   "
					+ "					 INNER JOIN   "
					+ "  main.applications AP ON I.applicationid = AP.id AND AP.status=1 ";				
			
			InUnidades = " INNER JOIN main.unitprocesses up ON EP.unitprocessid = up.id   "
			           + " INNER JOIN main.processes p ON up.processid = p.id  ";
			filtroUnit = " INNER JOIN main.userunits uu ON up.unitid = uu.unitid AND uu.userid = " + iduser;		   
							
			if (unitsIn == 0) {
				InUnidades=InUnidades;
			} else
				InUnidades =InUnidades +filtroUnit;
			
			SentenciaBaseIn2 = "        SELECT   "
					+ "  'No Cubierto' AS nocubierto, "
					+ "					   COALESCE(SUM(CASE WHEN main_query.capacity > main_query.min_rtoqty OR main_query.capacity =0 THEN 1 ELSE 0 END), 0) AS cnt,   "
					+ "  'Cubierto' AS cubierto, "
					+ "					   COALESCE(SUM(CASE WHEN main_query.capacity <= main_query.min_rtoqty AND  main_query.capacity <> 0 THEN 1 ELSE 0 END), 0) AS cnt1   "
					+ "					 FROM (   "
					+ "  SELECT "
					+ "    C.campaingid, "
					+ "    C.campaignunitid, "
					+ "    C.channelid, "
					+ "    AP.name AS canal, "
					+ "    AP.capacity, "
					+ "    MIN(I.rtoqty) AS min_rtoqty "
					+ "  FROM "
					+ "    main.evalprocimpacts I "
					+ "  INNER JOIN "
					+ "    main.evalprocesses EP ON I.evalprocessid = EP.id "
					+ "  INNER JOIN "
					+ "    main.campaignunits CU ON EP.campaignunitid = CU.id "
					+ InUnidades
					+ "  INNER JOIN "
					+ "    main.processchannels pc ON pc.processid = up.processid "
					+ "  INNER JOIN "
					+ "    ( "
					+ "      SELECT "
					+ "        id, "
					+ "        campaignunitid, "
					+ "        campaingid, "
					+ "        channelid, "
					+ "        ROW_NUMBER() OVER ( "
					+ "          PARTITION BY channelid "
					+ "          ORDER BY "
					+ "            ABS(EXTRACT(EPOCH FROM latest_createdat - CURRENT_DATE)) 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 "
					+ InUnidades
					+ "        INNER JOIN "
					+ "          main.processchannels pc ON pc.processid = up.processid "
					+ "        WHERE "
					+ "					           EP.createdat <= CURRENT_DATE 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, "
					+ "    C.channelid, "
					+ "    AP.name, "
					+ "    AP.capacity "
					+ "					 ) AS main_query";
						
			

			String QueryTotalIn1 = "";
			String QueryTotalIn2 = "";
			
			
			AppstrategyResponseDto appstrategyResponseDto= new AppstrategyResponseDto();
			ExpectativasHeaderRtoDto expectativasHeaderRtoDto = new ExpectativasHeaderRtoDto("Expectativas de Cumplimiento de RTO");
			ExpectativasRtoResponseDto expectativasRtoResponseDto = new ExpectativasRtoResponseDto();
			ContentExpectativaRtoDto contentExpectativaRtoDto1 = new ContentExpectativaRtoDto();
			ContentExpectativaRtoDto contentExpectativaRtoDto2 = new ContentExpectativaRtoDto();
			List<SectionDto> sections1 = new ArrayList();
			List<SectionDto> sections2 = new ArrayList();
			SectionDto sectionDto1 = new SectionDto();
			SectionDto sectionDto2 = new SectionDto();
			SectionDto sectionDto3 = new SectionDto();
			SectionDto sectionDto4 = new SectionDto();
			List<ContentExpectativaRtoDto> content = new ArrayList();
			
			
			query = entityManager.createNativeQuery(SentenciaBaseIn1);
			Object[] filaAplicaciones = (Object[]) query.getSingleResult();
			expectativasRtoResponseDto.setHeader(expectativasHeaderRtoDto);
			contentExpectativaRtoDto1.setName("Aplicaciones");
			sectionDto1.setLabel(filaAplicaciones[2].toString());
			sectionDto1.setValue((BigInteger)filaAplicaciones[3]);
			
			sections1.add(sectionDto1);
			contentExpectativaRtoDto1.setSections(sections1);
			
			sectionDto2.setLabel(filaAplicaciones[0].toString());
			sectionDto2.setValue((BigInteger)filaAplicaciones[1]);
			
			sections1.add(sectionDto2);
			contentExpectativaRtoDto1.setSections(sections1);
			
			content.add(contentExpectativaRtoDto1);
			
			query2 = entityManager.createNativeQuery(SentenciaBaseIn2);
			Object[] filaCanales = (Object[]) query2.getSingleResult();
			contentExpectativaRtoDto2.setName("Canales");
			
			sectionDto3.setLabel(filaCanales[2].toString());
			sectionDto3.setValue((BigInteger)filaCanales[3]);
			
			sections2.add(sectionDto3);
			contentExpectativaRtoDto2.setSections(sections2);
			
			sectionDto4.setLabel(filaCanales[0].toString());
			sectionDto4.setValue((BigInteger)filaCanales[1]);
			
			sections2.add(sectionDto4);
			contentExpectativaRtoDto2.setSections(sections2);
			content.add(contentExpectativaRtoDto2);
			expectativasRtoResponseDto.setHeader(expectativasHeaderRtoDto);
			expectativasRtoResponseDto.setContent(content);
			
			return ResponseEntity.ok(expectativasRtoResponseDto);



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

		return new ResponseEntity(respuesta, estatus);

	}
}