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.PipeStatusServiceException;
import com.prime.sony.ecommerce.model.PipeJobInformation;
import com.prime.sony.ecommerce.model.PipeStatus;
import com.prime.sony.ecommerce.service.PipeStatusService;
import com.prime.sony.ecommerce.util.MailService;

@Service
public class PipeStatusServiceImpl implements PipeStatusService {

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

	@Autowired
	private Connection connection;

//	@Autowired
//	private PartnerConnection partnerConnection;
//
//	@Autowired
//	private BulkConnection bulkConnection;

	@Autowired
	private MailService mailService;
	
	@Autowired
	private PipeJobInformation pipeJobInformation;
	
	@Value("${schema.italy}")
	private String schema_woocommerce_italy;
	
	
	@Override
	public List<PipeStatus> loadProductPipeHistory() throws PipeStatusServiceException{
		log.info("==>PipeStatusServiceImpl.loadProductPipeHistory()");
		List<PipeStatus> pipeStatusList = null;
		try {
			pipeStatusList = new ArrayList<PipeStatus>();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select *\r\n" + 
					"from table(information_schema.copy_history(table_name=>'\"WOOCOMMERCE_ITALY\".\"PRODUCTS_STAGE\"', \r\n" + 
					"  start_time=> dateadd("+pipeJobInformation.getScheduledforEvery()+", -"+pipeJobInformation.getDataIntervalTime()+", current_timestamp())));");
			while (resultSet.next()) {
				PipeStatus pipeStatus = new PipeStatus();
				pipeStatus.setFileName(resultSet.getString(1));
				pipeStatus.setStoreLocation(resultSet.getString(2));
				if(null != resultSet.getTimestamp(3)) {
					pipeStatus.setLastLoadTime(resultSet.getTimestamp(3).toLocalDateTime());
				}
				pipeStatus.setRowCount(resultSet.getInt(4));
				pipeStatus.setRowParsed(resultSet.getInt(5));
				pipeStatus.setFileSize(resultSet.getLong(6));
				pipeStatus.setFirst_error_message(resultSet.getString(7));
				pipeStatus.setFirst_error_line_number(resultSet.getInt(8));
				pipeStatus.setFirst_error_character_position(resultSet.getInt(9));
				pipeStatus.setFirst_error_column_name(resultSet.getString(10));
				pipeStatus.setErrorCount(resultSet.getInt(11));
				pipeStatus.setErrorLimit(resultSet.getInt(12));
				pipeStatus.setStatus(resultSet.getString(13));
				pipeStatus.setTable_catalog_name(resultSet.getString(14));
				pipeStatus.setTable_schema_name(resultSet.getString(15));
				pipeStatus.setTable_name(resultSet.getString(16));
				pipeStatus.setPipe_catalog_name(resultSet.getString(17));
				pipeStatus.setPipe_schema_name(resultSet.getString(18));
				pipeStatus.setPipe_name(resultSet.getString(19));
				if(null != resultSet.getTimestamp(20)) {
					pipeStatus.setPipe_received_time(resultSet.getTimestamp(20).toLocalDateTime());
				}
				pipeStatusList.add(pipeStatus);
				
			}
			
			
			List<PipeStatus> failedPipes = new ArrayList<PipeStatus>();
			
			for (PipeStatus pipeStatus : pipeStatusList) {
				if(pipeStatus.getStatus().equalsIgnoreCase("LOAD_FAILED")) {
					failedPipes.add(pipeStatus);
				}
				
			}
			
			if(failedPipes.size()>0) {
				mailService.mailsendingForPipe("PRODUCT",failedPipes);
			}else {
				mailService.mailsendingForPipeSuccess("PRODUCT");
			}
			
		} catch (Exception e) {
			throw new PipeStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		
		log.info("<==PipeStatusServiceImpl.loadProductPipeHistory()");
		return pipeStatusList;
	}
	
	@Override
	public List<PipeStatus> loadCustomerPipeHistory() throws PipeStatusServiceException{
		log.info("==>PipeStatusServiceImpl.loadCustomerPipeHistory()");
		List<PipeStatus> pipeStatusList = null;
		try {
			pipeStatusList = new ArrayList<PipeStatus>();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select *\r\n" + 
					"from table(information_schema.copy_history(table_name=>'\"WOOCOMMERCE_ITALY\".\"CUSTOMERS_STAGE\"', \r\n" + 
					"  start_time=> dateadd("+pipeJobInformation.getScheduledforEvery()+", -"+pipeJobInformation.getDataIntervalTime()+", current_timestamp())));");
			while (resultSet.next()) {
				PipeStatus pipeStatus = new PipeStatus();
				pipeStatus.setFileName(resultSet.getString(1));
				pipeStatus.setStoreLocation(resultSet.getString(2));
				if(null != resultSet.getTimestamp(3)) {
					pipeStatus.setLastLoadTime(resultSet.getTimestamp(3).toLocalDateTime());
				}
				
				pipeStatus.setRowCount(resultSet.getInt(4));
				pipeStatus.setRowParsed(resultSet.getInt(5));
				pipeStatus.setFileSize(resultSet.getLong(6));
				pipeStatus.setFirst_error_message(resultSet.getString(7));
				pipeStatus.setFirst_error_line_number(resultSet.getInt(8));
				pipeStatus.setFirst_error_character_position(resultSet.getInt(9));
				pipeStatus.setFirst_error_column_name(resultSet.getString(10));
				pipeStatus.setErrorCount(resultSet.getInt(11));
				pipeStatus.setErrorLimit(resultSet.getInt(12));
				pipeStatus.setStatus(resultSet.getString(13));
				pipeStatus.setTable_catalog_name(resultSet.getString(14));
				pipeStatus.setTable_schema_name(resultSet.getString(15));
				pipeStatus.setTable_name(resultSet.getString(16));
				pipeStatus.setPipe_catalog_name(resultSet.getString(17));
				pipeStatus.setPipe_schema_name(resultSet.getString(18));
				pipeStatus.setPipe_name(resultSet.getString(19));
				if(null != resultSet.getTimestamp(20)) {
					pipeStatus.setPipe_received_time(resultSet.getTimestamp(20).toLocalDateTime());
				}
				pipeStatusList.add(pipeStatus);
				
			}
			
			List<PipeStatus> failedPipes = new ArrayList<PipeStatus>();
			
			for (PipeStatus pipeStatus : pipeStatusList) {
				if(pipeStatus.getStatus().equalsIgnoreCase("LOAD_FAILED")) {
					failedPipes.add(pipeStatus);
				}
				
			}
			
			if(failedPipes.size()>0) {
				mailService.mailsendingForPipe("CUSTOMER",failedPipes);
			}else {
				mailService.mailsendingForPipeSuccess("CUSTOMER");
			}
		} catch (Exception e) {
			throw new PipeStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		
		log.info("<==PipeStatusServiceImpl.loadCustomerPipeHistory()");
		return pipeStatusList;
	}

	@Override
	public List<PipeStatus> loadOrderPipeHistory() throws PipeStatusServiceException{
		log.info("==>PipeStatusServiceImpl.loadOrderPipeHistory()");
		List<PipeStatus> pipeStatusList = null;
		try {
			pipeStatusList = new ArrayList<PipeStatus>();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select *\r\n" + 
					"from table(information_schema.copy_history(table_name=>'\"WOOCOMMERCE_ITALY\".\"ORDERS_STAGE\"', \r\n" + 
					"  start_time=> dateadd("+pipeJobInformation.getScheduledforEvery()+", -"+pipeJobInformation.getDataIntervalTime()+", current_timestamp())));");
			while (resultSet.next()) {
				PipeStatus pipeStatus = new PipeStatus();
				pipeStatus.setFileName(resultSet.getString(1));
				pipeStatus.setStoreLocation(resultSet.getString(2));
				if(null != resultSet.getTimestamp(3)) {
					pipeStatus.setLastLoadTime(resultSet.getTimestamp(3).toLocalDateTime());
				}
				pipeStatus.setRowCount(resultSet.getInt(4));
				pipeStatus.setRowParsed(resultSet.getInt(5));
				pipeStatus.setFileSize(resultSet.getLong(6));
				pipeStatus.setFirst_error_message(resultSet.getString(7));
				pipeStatus.setFirst_error_line_number(resultSet.getInt(8));
				pipeStatus.setFirst_error_character_position(resultSet.getInt(9));
				pipeStatus.setFirst_error_column_name(resultSet.getString(10));
				pipeStatus.setErrorCount(resultSet.getInt(11));
				pipeStatus.setErrorLimit(resultSet.getInt(12));
				pipeStatus.setStatus(resultSet.getString(13));
				pipeStatus.setTable_catalog_name(resultSet.getString(14));
				pipeStatus.setTable_schema_name(resultSet.getString(15));
				pipeStatus.setTable_name(resultSet.getString(16));
				pipeStatus.setPipe_catalog_name(resultSet.getString(17));
				pipeStatus.setPipe_schema_name(resultSet.getString(18));
				pipeStatus.setPipe_name(resultSet.getString(19));
				if(null != resultSet.getTimestamp(20)) {
					pipeStatus.setPipe_received_time(resultSet.getTimestamp(20).toLocalDateTime());
				}
				pipeStatusList.add(pipeStatus);
				
			}
			
			List<PipeStatus> failedPipes = new ArrayList<PipeStatus>();
			
			for (PipeStatus pipeStatus : pipeStatusList) {
				if(pipeStatus.getStatus().equalsIgnoreCase("LOAD_FAILED")) {
					failedPipes.add(pipeStatus);
				}
				
			}
			
			if(failedPipes.size()>0) {
				mailService.mailsendingForPipe("ORDER",failedPipes);
			}else {
				mailService.mailsendingForPipeSuccess("ORDER");
			}
		} catch (Exception e) {
			throw new PipeStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		
		log.info("<==PipeStatusServiceImpl.loadOrderPipeHistory()");
		return pipeStatusList;
	}

}