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.HashSet;
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.AutodataCheckServiceException;
import com.prime.sony.ecommerce.model.AutoChecksJobInformation;
import com.prime.sony.ecommerce.model.AutoDataChecks;
import com.prime.sony.ecommerce.model.TaskStatus;
import com.prime.sony.ecommerce.service.AutoDataCheckService;
import com.prime.sony.ecommerce.util.MailService;

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

	@Autowired
	private Connection connection;
	
	@Autowired
	private MailService mailService;
	
	@Autowired
	private AutoChecksJobInformation jobInformation;
	
	@Value("${snowflake.schema}")
	private String schema;
	

	

	@Override
	public List<AutoDataChecks> loadProductsForAutoDataChecks() throws AutodataCheckServiceException {
		log.info("==>AutoDataCheckServiceImpl.loadProductsForAutoDataChecks()");
		List<AutoDataChecks> productList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			productList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();

			
			
			String query = "select ATEO.SNOWFLAKE_SCHEMA,ATEO.PRODUCT_ID, ATEO.ERROR_DATE,ATEOM.ITEMS,es.PLATFORM from "
					+ schema + "." + "DATA_TESTING_ECOMMERCE_PRODUCTS as ATEO INNER JOIN (select PRODUCT_ID,SNOWFLAKE_SCHEMA,count(PRODUCT_ID) AS ITEMS,MAX(ERROR_DATE) as maxdate from  "
					+ schema + "." + "DATA_TESTING_ECOMMERCE_PRODUCTS group by PRODUCT_ID,SNOWFLAKE_SCHEMA) AS ATEOM on "
					+"ATEOM.maxdate=ATEO.ERROR_DATE  and ATEOM.PRODUCT_ID = ATEO.PRODUCT_ID "
					+  " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  es ON es.SNOWFLAKE_SCHEMA = ATEO.SNOWFLAKE_SCHEMA " 
				    + "where ATEO.STATUS != 'TRUE' and ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>AutoDataCheckServiceImpl.loadProductsForAutoDataChecks() query:" + query);
			ResultSet resultSetProducts = statement.executeQuery(query);

			while (resultSetProducts.next()) {
				AutoDataChecks product = new AutoDataChecks();
				product.setSnowflakeSchema(resultSetProducts.getString(1));
				product.setItemId(resultSetProducts.getString(2));
				product.setErrorDate(resultSetProducts.getTimestamp(3).toLocalDateTime());
				product.setCount(Integer.valueOf(resultSetProducts.getString(4)));
				product.setPlatform(resultSetProducts.getString(5));
				product.setTableName("DATA_TESTING_ECOMMERCE_PRODUCTS");

				productList.add(product);

			}
		
			ArrayList<AutoDataChecks> productLists = removeDuplicatesInObjects(productList);
			 

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

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

			while (resultSetStores.next()) {

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

			}
			
			
			if (productLists.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<AutoDataChecks> finallist = new ArrayList<>();
					for (AutoDataChecks product : productLists) {
						if (product.getPlatform().equals(store)) {
							finallist.add(product);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForAutoDataChecks("PRODUCT", finallist);
					}
					finallist = null;
				}
			}
			 
			

		} catch (Exception e) {
			log.error("AutoDataCheckServiceImpl.loadProductsForAutoDataChecks()", e);
			throw new AutodataCheckServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==AutoDataCheckServiceImpl.loadProductsForAutoDataChecks()");
		return productList;
	}
	
	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;
	}
	
	public static ArrayList<AutoDataChecks> removeDuplicatesInObjects(List<AutoDataChecks> list) {

		Set<AutoDataChecks> set = new HashSet<>();
		  
		  // Add the elements to set 
		  set.addAll(list);
		  
		  // Clear the list 
		  list.clear();
		  
		  // add the elements of set // with no duplicates to the list
		  list.addAll(set);

		// return the list
		return (ArrayList<AutoDataChecks>) list;
	}

	@Override
	public List<AutoDataChecks> loadOrdersForAutoDataChecks() throws AutodataCheckServiceException {
		
		log.info("==>AutoDataCheckServiceImpl.loadOrdersForAutoDataChecks()");
		List<AutoDataChecks> orderList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			orderList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();

			
			
			String query = "select ATEO.SNOWFLAKE_SCHEMA,ATEO.ALT_ORDER_ID, ATEO.ERROR_DATE,ATEOM.ITEMS,es.PLATFORM from "
					+ schema + "." + "DATA_TESTING_ECOMMERCE_ORDERS as ATEO INNER JOIN (select ALT_ORDER_ID,SNOWFLAKE_SCHEMA,count(ALT_ORDER_ID) AS ITEMS,MAX(ERROR_DATE) as maxdate from  "
					+ schema + "." + "DATA_TESTING_ECOMMERCE_ORDERS group by ALT_ORDER_ID,SNOWFLAKE_SCHEMA) AS ATEOM on "
					+"ATEOM.maxdate=ATEO.ERROR_DATE  and ATEOM.ALT_ORDER_ID = ATEO.ALT_ORDER_ID "
					+  " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  es ON es.SNOWFLAKE_SCHEMA = ATEO.SNOWFLAKE_SCHEMA " 
				    + "where ATEO.STATUS != 'TRUE' and ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>AutoDataCheckServiceImpl.loadOrdersForAutoDataChecks() query:" + query);
			ResultSet resultSetOrders = statement.executeQuery(query);

			while (resultSetOrders.next()) {
				AutoDataChecks order = new AutoDataChecks();
				order.setSnowflakeSchema(resultSetOrders.getString(1));
				order.setItemId(resultSetOrders.getString(2));
				order.setErrorDate(resultSetOrders.getTimestamp(3).toLocalDateTime());
				order.setCount(Integer.valueOf(resultSetOrders.getString(4)));
				order.setPlatform(resultSetOrders.getString(5));
				order.setTableName("DATA_TESTING_ECOMMERCE_ORDERS");

				orderList.add(order);

			}
			
			ArrayList<AutoDataChecks> orderLists = removeDuplicatesInObjects(orderList);
			 

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

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

			while (resultSetStores.next()) {

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

			}
			
			
			if (orderLists.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<AutoDataChecks> finallist = new ArrayList<>();
					for (AutoDataChecks order : orderLists) {
						if (order.getPlatform().equals(store)) {
							finallist.add(order);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForAutoDataChecks("ORDER", finallist);
					}
					finallist = null;
				}
			}
			 
			

		} catch (Exception e) {
			log.error("AutoDataCheckServiceImpl.loadOrdersForAutoDataChecks()", e);
			throw new AutodataCheckServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==AutoDataCheckServiceImpl.loadOrdersForAutoDataChecks()");
		return orderList;
	}

	@Override
	public List<AutoDataChecks> loadCustomersForAutoDataChecks() throws AutodataCheckServiceException {
		log.info("==>AutoDataCheckServiceImpl.loadCustomersForAutoDataChecks()");
		List<AutoDataChecks> customerList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			customerList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();

			
			
			String query = "select ATEO.SNOWFLAKE_SCHEMA,ATEO.STORE_CUSTOMER_ID, ATEO.ERROR_DATE,ATEOM.ITEMS,es.PLATFORM from "
					+ schema + "." + "DATA_TESTING_ECOMMERCE_CUSTOMERS as ATEO INNER JOIN (select STORE_CUSTOMER_ID,SNOWFLAKE_SCHEMA,count(STORE_CUSTOMER_ID) AS ITEMS,MAX(ERROR_DATE) as maxdate from  "
					+ schema + "." + "DATA_TESTING_ECOMMERCE_CUSTOMERS group by STORE_CUSTOMER_ID,SNOWFLAKE_SCHEMA) AS ATEOM on "
					+"ATEOM.maxdate=ATEO.ERROR_DATE  and ATEOM.STORE_CUSTOMER_ID = ATEO.STORE_CUSTOMER_ID "
					+  " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  es ON es.SNOWFLAKE_SCHEMA = ATEO.SNOWFLAKE_SCHEMA " 
				    + "where ATEO.STATUS != 'TRUE' and ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>AutoDataCheckServiceImpl.loadCustomersForAutoDataChecks() query:" + query);
			ResultSet resultSetCustomers = statement.executeQuery(query);

			while (resultSetCustomers.next()) {
				AutoDataChecks customer = new AutoDataChecks();
				customer.setSnowflakeSchema(resultSetCustomers.getString(1));
				customer.setItemId(resultSetCustomers.getString(2));
				customer.setErrorDate(resultSetCustomers.getTimestamp(3).toLocalDateTime());
				customer.setCount(Integer.valueOf(resultSetCustomers.getString(4)));
				customer.setPlatform(resultSetCustomers.getString(5));
				customer.setTableName("DATA_TESTING_ECOMMERCE_CUSTOMERS");

				customerList.add(customer);

			}
			
			ArrayList<AutoDataChecks> customersLists = removeDuplicatesInObjects(customerList);
			 

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

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

			while (resultSetStores.next()) {

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

			}
			
			
			if (customersLists.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<AutoDataChecks> finallist = new ArrayList<>();
					for (AutoDataChecks customer : customersLists) {
						if (customer.getPlatform().equals(store)) {
							finallist.add(customer);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForAutoDataChecks("CUSTOMER", finallist);
					}
					finallist = null;
				}
			}
			 
			

		} catch (Exception e) {
			log.error("AutoDataCheckServiceImpl.loadCustomersForAutoDataChecks()", e);
			throw new AutodataCheckServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==AutoDataCheckServiceImpl.loadCustomersForAutoDataChecks()");
		return customerList;
	}
	
	@Override
	public List<TaskStatus> loadGlobalDataProcess() throws AutodataCheckServiceException {
		
		log.info("==>AutoDataCheckServiceImpl.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=>'SHOPIFY_GLOBAL_DATA_TEST'))";
			log.info("==>AutoDataCheckServiceImpl.loadGlobalDataProcess() 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,"GLOBALDATA");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("GLOBALDATA");
				}
		
		} 
		}catch (Exception e) {
			log.error("AutoDataCheckServiceImpl.loadGlobalDataProcess()", e);
			throw new AutodataCheckServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==AutoDataCheckServiceImpl.loadGlobalDataProcess()");
		return taskStatusList;
	}

}