package com.prime.sony.ecommerce.serviceImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.prime.sony.ecommerce.exceptions.DeleteRequestServiceException;
import com.prime.sony.ecommerce.model.DeleteRequest;
import com.prime.sony.ecommerce.model.DeleteRequestJobInformation;
import com.prime.sony.ecommerce.model.DeleteRequestLog;
import com.prime.sony.ecommerce.model.TaskStatus;
import com.prime.sony.ecommerce.service.DeleteRequestService;
import com.prime.sony.ecommerce.util.MailService;

@Service
public class DeleteRequestServiceImpl implements DeleteRequestService {
	
	private static final Logger log = LoggerFactory.getLogger(DeleteRequestServiceImpl.class);

	@Autowired
	private Connection connection;
	
	@Autowired
	private MailService mailService;
	

	@Autowired
	private DeleteRequestJobInformation jobInformation;
	
	@Value("${snowflake.schema}")
	private String schema;
	

	

	@Override
	public List<DeleteRequest> loadDeleteRequestReport() throws DeleteRequestServiceException {
		log.info("==>DeleteRequestServiceImpl.loadDeleteRequestReport()");
		List<DeleteRequest> deleteList = null;
		try {
			Date jobStartDate = new Date();
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			deleteList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();

			
			
			String query = "select ECDR.STORE_ID,ECDR.SNOWFLAKE_SCHEMA,ECDR.CUSTOMER_ROWS_PROCESSED, "
					+ "ECDR.ORDER_ROWS_PROCESSED,ECDR.PROCESS_RUN_DATE,ES.PLATFORM from "
					+ schema + "." + "ECOMMERCE_CUSTOMERS_DELETE_REPORT as ECDR "
					+  " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  ES ON ES.SNOWFLAKE_SCHEMA = ECDR.SNOWFLAKE_SCHEMA " 
				    + "where PROCESS_RUN_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>DeleteRequestServiceImpl.loadDeleteRequestReport() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				DeleteRequest item = new DeleteRequest();
				item.setStoreId(resultSet.getString(1));
				item.setSnowflakeSchema(resultSet.getString(2));
				item.setCustomerRowsProcessed(Integer.valueOf(resultSet.getString(3)));
				//item.setCustomerLKPRowsProcessed(Integer.valueOf(resultSet.getString(4)));
				item.setOrderRowsProcessed(Integer.valueOf(resultSet.getString(4)));
				item.setProcessRunTime(resultSet.getTimestamp(5).toLocalDateTime());
				item.setPlatform(resultSet.getString(6));

				deleteList.add(item);

			}
			 

			String query1 = ("select PLATFORM FROM " + schema + "." + "ECOMMERCE_STORES");

			log.info("==>DeleteRequestServiceImpl.loadDeleteRequestReport() query:" + query1);
			ResultSet resultSetStores = statement.executeQuery(query1);

			while (resultSetStores.next()) {

				storeList.add(resultSetStores.getString(1));

			}
			
			
			if (deleteList.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<DeleteRequest> finallist = new ArrayList<>();
					for (DeleteRequest items : deleteList) {
						if (items.getPlatform().equals(store)) {
							finallist.add(items);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForDeleteRequestReport(finallist,jobStartDate);
					}
					finallist = null;
				}
			}
			 
			

		} catch (Exception e) {
			log.error("DeleteRequestServiceImpl.loadDeleteRequestReport()", e);
			throw new DeleteRequestServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==DeleteRequestServiceImpl.loadDeleteRequestReport()");
		return deleteList;
	}
	
	@Override
	public List<DeleteRequestLog> loadDeleteRequestLog() throws DeleteRequestServiceException {
		log.info("==>DeleteRequestServiceImpl.loadDeleteRequestLog()");
		List<DeleteRequestLog> deleteList = null;
		try {
			Date jobStartDate = new Date();
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			deleteList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();

			String query = "select ERROR_MESSAGE,ERROR_DATE from "
					+ schema + "." + "LOG_CUSTOMER_DELETE "
				    + "where ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>DeleteRequestServiceImpl.loadDeleteRequestLog() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				DeleteRequestLog item = new DeleteRequestLog();
				item.setErrorMsg(resultSet.getString(1));
				item.setErrorDate(resultSet.getTimestamp(2).toLocalDateTime());

				deleteList.add(item);

			}
			 
			if (deleteList.size() > 0) {
					mailService.mailsendingForDeleteRequestFailure(deleteList);
			}
		
		} catch (Exception e) {
			log.error("DeleteRequestServiceImpl.loadDeleteRequestLog()", e);
			throw new DeleteRequestServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==DeleteRequestServiceImpl.loadDeleteRequestLog()");
		return deleteList;
	}
	
	public static ArrayList<String> removeDuplicates(List<String> storeList) {

		// Create a new LinkedHashSet
		Set<String> set = new LinkedHashSet<>();

		// Add the elements to set
		set.addAll(storeList);

		// Clear the list
		storeList.clear();

		// add the elements of set
		// with no duplicates to the list
		storeList.addAll(set);

		// return the list
		return (ArrayList<String>) storeList;
	}

	@Override
	public List<TaskStatus> loadDeleteRequestProcess() throws DeleteRequestServiceException {
		
		log.info("==>DeleteRequestServiceImpl.loadDeleteRequestProcess()");
	
		List<TaskStatus> taskStatusList = new ArrayList<TaskStatus>();
		int schedulerTime = jobInformation.getDataIntervalTime();
		try {
			
			Statement statement = connection.createStatement();

			String query = "select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME  "
					+ " from table(information_schema.task_history( "
				    + " scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()), "
				    + " task_name=>'ECOMMERCE_CUSTOMERS_DATA_DELETION'))";
			log.info("==>DeleteRequestServiceImpl.loadDeleteRequestProcess() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			List<TaskStatus> successTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}else if(taskStatus.getState().equalsIgnoreCase("SUCCEEDED")) {
						successTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForAllProcessFailure(failedTasks,"DELETE");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("DELETE");
				}
		
		} 
		}catch (Exception e) {
			log.error("DeleteRequestServiceImpl.loadDeleteRequestProcess()", e);
			throw new DeleteRequestServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==DeleteRequestServiceImpl.loadDeleteRequestProcess()");
		return taskStatusList;
	}

	

}