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.List;

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.exceptions.RowStatusServiceException;
import com.prime.sony.ecommerce.model.ReconcilationJobInformation;
import com.prime.sony.ecommerce.model.RowCountStatus;
import com.prime.sony.ecommerce.model.TaskStatus;
import com.prime.sony.ecommerce.service.RecordStatusService;
import com.prime.sony.ecommerce.util.MailService;

@Service
public class RecordStatusServiceImpl implements RecordStatusService {

	private static final Logger log = LoggerFactory.getLogger(RecordStatusServiceImpl.class);

	@Autowired
	private Connection connection;

	@Autowired
	private MailService mailService;
	
	@Autowired
	private ReconcilationJobInformation jobInformation;
		
	@Value("${schema.italy}")
	private String schema_woocommerce_italy;
	
	
	@Value("${snowflake.schema}")
	private String schema;
	
	@Override
	public void getCustomerRowcountStatus() throws RowStatusServiceException{
		log.info("==>RecordStatusServiceImpl.getCustomerRowcountStatus()");
		List<RowCountStatus> rowStatusList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			rowStatusList = new ArrayList<RowCountStatus>();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select STORE_ID,SHOPIFY_SCHEMA,DIFFERENCE,COUNT_SOURCE_FINAL,COUNT_CONSOLIDATED_FINAL,PROCESS_DATE  "
					+ "from "+schema+".ROW_COUNT_RECON_CUSTOMERS "
					+ "where  PROCESS_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()) order by STORE_ID");
			RowCountStatus rowCountStatus=null;
			while (resultSet.next()) {
				rowCountStatus = new RowCountStatus();
				int diff=resultSet.getInt(3);
				if(diff !=0) {					
					rowCountStatus.setStoreID(resultSet.getString(1));
					rowCountStatus.setSchemaName(resultSet.getString(2));
					rowCountStatus.setDifference(resultSet.getInt(3));	
					rowCountStatus.setSourcecFinalCount(resultSet.getInt(4));
					rowCountStatus.setConsolidationFinalCount(resultSet.getInt(5));
					rowCountStatus.setProcessDate(resultSet.getDate(6));
					rowCountStatus.setErrorMessage("STORE_ID:"+resultSet.getString(1)+"  SHOPIFY_SCHEMA:"+resultSet.getString(2)+"  "
							 +"  DIFFERENCE:"+resultSet.getInt(3));
					
					rowStatusList.add(rowCountStatus);
				}
				
			}
			
			
			if(rowStatusList.size()>0) {
				mailService.mailsendingForRowCountStatus("CUSTOMER",rowStatusList);
			}
			
		} catch (Exception e) {
			throw new RowStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		
		log.info("<==RecordStatusServiceImpl.getCustomerRowcountStatus()");
		//return pipeStatusList;
	}
	
	
	
	@Override
	public void getOrderRowcountStatus() throws RowStatusServiceException{
		log.info("==>RecordStatusServiceImpl.getOrderRowcountStatus()");
		List<RowCountStatus> rowStatusList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			rowStatusList = new ArrayList<RowCountStatus>();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select STORE_ID,SHOPIFY_SCHEMA,DIFFERENCE,COUNT_SOURCE_FINAL,COUNT_CONSOLIDATED_FINAL,PROCESS_DATE  "
					+ "from "+schema+".ROW_COUNT_RECON_ORDERS "
					+ "where  PROCESS_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()) order by STORE_ID");
			RowCountStatus rowCountStatus=null;
			while (resultSet.next()) {
				rowCountStatus = new RowCountStatus();
				int diff=resultSet.getInt(3);
				if(diff !=0) {					
					rowCountStatus.setStoreID(resultSet.getString(1));
					rowCountStatus.setSchemaName(resultSet.getString(2));
					rowCountStatus.setDifference(resultSet.getInt(3));
					rowCountStatus.setSourcecFinalCount(resultSet.getInt(4));
					rowCountStatus.setConsolidationFinalCount(resultSet.getInt(5));
					rowCountStatus.setProcessDate(resultSet.getDate(6));
					rowCountStatus.setErrorMessage("STORE_ID:"+resultSet.getString(1)+"  SHOPIFY_SCHEMA:"+resultSet.getString(2)+"  "
							 +"  DIFFERENCE:"+resultSet.getInt(3));
					
					rowStatusList.add(rowCountStatus);
				}
				
			}
			
			
			if(rowStatusList.size()>0) {
				mailService.mailsendingForRowCountStatus("ORDER",rowStatusList);
			}
			
		} catch (Exception e) {
			throw new RowStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		
		log.info("<==RecordStatusServiceImpl.getOrderRowcountStatus()");
		//return pipeStatusList;
	}
	
	
	
	@Override
	public void getProductRowcountStatus() throws RowStatusServiceException{
		log.info("==>RecordStatusServiceImpl.getProductRowcountStatus()");
		List<RowCountStatus> rowStatusList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			rowStatusList = new ArrayList<RowCountStatus>();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select STORE_ID,SHOPIFY_SCHEMA,DIFFERENCE,COUNT_SOURCE_FINAL,COUNT_CONSOLIDATED_FINAL,PROCESS_DATE  "
					+ "from "+schema+".ROW_COUNT_RECON_PRODUCTS "
			+ "where  PROCESS_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()) order by STORE_ID");
			RowCountStatus rowCountStatus=null;
			while (resultSet.next()) {
				rowCountStatus = new RowCountStatus();
				int diff=resultSet.getInt(3);
				if(diff !=0) {					
					rowCountStatus.setStoreID(resultSet.getString(1));
					rowCountStatus.setSchemaName(resultSet.getString(2));
					rowCountStatus.setDifference(resultSet.getInt(3));
					rowCountStatus.setSourcecFinalCount(resultSet.getInt(4));
					rowCountStatus.setConsolidationFinalCount(resultSet.getInt(5));
					rowCountStatus.setProcessDate(resultSet.getDate(6));
					rowCountStatus.setErrorMessage("STORE_ID:"+resultSet.getString(1)+"  SHOPIFY_SCHEMA:"+resultSet.getString(2)+"  "
							 +"  DIFFERENCE:"+resultSet.getInt(3));
					
					rowStatusList.add(rowCountStatus);
				}
				
			}
			
			
			if(rowStatusList.size()>0) {
				mailService.mailsendingForRowCountStatus("PRODUCT",rowStatusList);
			}
			
		} catch (Exception e) {
			throw new RowStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		
		log.info("<==RecordStatusServiceImpl.getProductRowcountStatus()");
		//return pipeStatusList;
	}
	
	@Override
	public List<TaskStatus> loadReconcilationProcess() throws RowStatusServiceException {
		
		log.info("==>RecordStatusServiceImpl.loadReconcilationProcess()");
	
		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_RECONCILIATION'))";
			log.info("==>RecordStatusServiceImpl.loadReconcilationProcess() 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,"RECONCILATION");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("RECONCILATION");
				}
		
		} 
		}catch (Exception e) {
			log.error("RecordStatusServiceImpl.loadReconcilationProcess()", e);
			throw new RowStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==RecordStatusServiceImpl.loadReconcilationProcess()");
		return taskStatusList;
	}

	
	

}