package com.prime.sony.ecommerce.serviceImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.batch.core.JobExecution;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.prime.sony.ecommerce.exceptions.CustomerServiceException;
import com.prime.sony.ecommerce.helper.CustomerBatch;
import com.prime.sony.ecommerce.model.Customer;
import com.prime.sony.ecommerce.model.JobInformation;
import com.prime.sony.ecommerce.service.CustomerService;
import com.prime.sony.ecommerce.util.SFBulkUtils;
import com.prime.sony.ecommerce.util.SFConnections;
import com.sforce.async.BatchInfo;
import com.sforce.async.BulkConnection;
import com.sforce.async.JobInfo;
import com.sforce.ws.ConnectionException;

@Service
public class CustomerServiceImpl implements CustomerService {

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

	@Autowired
	private Connection connection;

	@Autowired
	private BulkConnection bulkConnection;

	@Autowired
	private CustomerBatch customerBatch;

	@Autowired
	private JobInformation jobInformation;
	
	@Autowired
	private SFConnections sFConnections;
	
	@Override
	public Map<String, String> saveCustomerData() throws CustomerServiceException {
		log.info("==>CustomerServiceImpl.saveCustomerData()");

		List<Customer> customerList = null;
		Map<String, String> resultMap = null;
		Date jobStartDate = new Date();
		
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			customerList = new ArrayList<Customer>();
			Statement statement = connection.createStatement();

			String currentDate = jobInformation.getSchedulerExecutionTime();
			String query = "select ec.customer_primary_key, ec.store_customer_id, ec.customer_id, ec.order_id, ec.abandoned_checkout_id, ec.store_id, "
				+ "ec.snowflake_schema, ec.user_role, ec.registered, ec.email, ec.first_name, ec.last_name, ec.address_1, ec.address_2, ec.phone, ec.city, ec.state_province, ec.country, ec.postal_code, "
				+ "ec.date_of_birth, ec.marketing_consent, ec.profiling_consent, ec.total_orders, ec.total_completed_orders, ec.customer_total_spent, ec.customer_total_spent_all_stores, "
				+ "ec.date_registered, ec.customer_date_created, ec.customer_date_modified, ec.consolidated_date_created, ec.consolidated_date_modified,ec.MARKETING_CONSENT_UPDATED_DATE from ECOMMERCE_CUSTOMERS ec "
				+ "inner join ECOMMERCE_STORES es on es.store_id = ec.store_id "
				+ "where es.active = 'TRUE' AND ec.DELETE_REQUESTED != 'TRUE' and (ec.CONSOLIDATED_DATE_CREATED  >= DATEADD("
				+ jobInformation.getScheduledforEvery() + ", -" + schedulerTime + ", '" + currentDate
				+ "') OR ec.CONSOLIDATED_DATE_MODIFIED >= DATEADD(" + jobInformation.getScheduledforEvery() + ", -"
				+ schedulerTime + ", '" + currentDate + "'))";
			log.info("==>CustomerServiceImpl.saveCustomerData() query:"+query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				Customer customer = new Customer();
				customer.setCustomer_primary_key(resultSet.getLong(1));
				customer.setStore_customer_id(resultSet.getString(2));
				customer.setCustomer_id(resultSet.getLong(3));
				customer.setOrder_id(resultSet.getLong(4));
				customer.setAbandoned_checkout_id(resultSet.getLong(5));
				customer.setStore_id(resultSet.getString(6));
				customer.setSnowflake_schema(resultSet.getString(7));
				customer.setUser_role(resultSet.getString(8));
				customer.setRegistered(resultSet.getBoolean(9));
				customer.setEmail(resultSet.getString(10));
				customer.setFirst_name(resultSet.getString(11));
				customer.setLast_name(resultSet.getString(12));
				customer.setAddress_1(resultSet.getString(13));
				customer.setAddress_2(resultSet.getString(14));
				customer.setPhone(resultSet.getString(15));
				customer.setCity(resultSet.getString(16));
				customer.setState_province(resultSet.getString(17));
				customer.setCountry(resultSet.getString(18));
				customer.setPostal_code(resultSet.getString(19));
				customer.setDate_of_birth(resultSet.getString(20));
				customer.setMarketing_consent(resultSet.getBoolean(21));
				customer.setProfiling_consent(resultSet.getBoolean(22));
				customer.setTotal_orders(resultSet.getLong(23));
				customer.setTotal_completed_orders(resultSet.getLong(24));
				customer.setCustomer_total_spent(resultSet.getFloat(25));
				customer.setCustomer_total_spent_all_stores(resultSet.getFloat(26));
				customer.setDate_registered(resultSet.getDate(27));
				customer.setCustomer_date_created(resultSet.getDate(28));
				customer.setCustomer_date_modified(resultSet.getDate(29));
				customer.setConsolidated_date_created(resultSet.getDate(30));
				customer.setConsolidated_date_modified(resultSet.getDate(31));
				customer.setMarketing_consent_updated_date(resultSet.getDate(32));

				customerList.add(customer);
			}
		} catch (SQLException e) {
			log.error("==> Exception at CustomerServiceImpl.saveCustomerData()", e);
			throw new CustomerServiceException(e.getMessage(), e.getCause(), null);
		}
		
		if (customerList.size() > 0) {

			JobInfo jobInfo;
			try {				
				System.out.println("Session iD ===="+bulkConnection.SESSION_ID);
				jobInfo = SFBulkUtils.createJob("Ecommerce_Customer__c", bulkConnection);
				List<BatchInfo> customerBatchList = customerBatch.createBatche(bulkConnection, jobInfo, customerList);
				resultMap = customerBatch.bulkImport(bulkConnection, jobInfo, customerBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", customerList.size()+"");
			}catch (Exception e) {
				if (e.getMessage().contains("Invalid session id")) {			        
			        try {
			        	this.bulkConnection = sFConnections.createBulkConnection();
						jobInfo = SFBulkUtils.createJob("Ecommerce_Customer__c", bulkConnection);
						List<BatchInfo> customerBatchList = customerBatch.createBatche(bulkConnection, jobInfo, customerList);
						try {
							resultMap = customerBatch.bulkImport(bulkConnection, jobInfo, customerBatchList, jobStartDate);
							resultMap.put("SnowflakeRecordscount", customerList.size()+"");
						} catch (ConnectionException ce) {
							log.error("==>Exception at CustomerServiceImpl.saveCustomerData()", ce);
							throw new CustomerServiceException(ce.getMessage(), ce.getCause(), null);
						}
					} catch (Exception ie) {
						log.error("==>Exception at CustomerServiceImpl.saveCustomerData()", ie);
						throw new CustomerServiceException(ie.getMessage(), ie.getCause(), null);
					}

					return resultMap;
			    }
				log.error("==>Exception at CustomerServiceImpl.saveCustomerData()", e);
				throw new CustomerServiceException(e.getMessage(), e.getCause(), null);
			}
		}else {
			resultMap=new HashMap<String, String>();
			resultMap.put("NODATA", "true");
			
		}

		log.info("<==CustomerServiceImpl.saveCustomerData()");
				
		return resultMap;
	}
	
	@Override
	public void saveJobMonitorInfo(JobExecution jobExecution) throws CustomerServiceException {
		log.info("==>CustomerServiceImpl.saveJobMonitorInfo()");
		try {
			PreparedStatement pstmt = connection.prepareStatement(
					"INSERT INTO JOB_MONITORING(JOB_ID,JOB_START_TIME,JOB_END_TIME,STATUS,REASON,JOB_INFO_ID) VALUES(?,?,?,?,?,?)");
			pstmt.setLong(1, jobExecution.getJobId());
			pstmt.setTimestamp(2, new Timestamp(jobExecution.getStartTime().getTime()));
			pstmt.setTimestamp(3, new Timestamp((jobExecution.getEndTime().getTime())));
			pstmt.setString(4, jobExecution.getStatus().toString());
			pstmt.setString(5, jobExecution.getExitStatus().getExitDescription());
			pstmt.setLong(6, jobInformation.getJob_id());

			pstmt.executeUpdate();
		} catch (Exception e) {
			log.error("==>Exception at CustomerServiceImpl.saveJobMonitorInfo()", e);
			throw new CustomerServiceException(e.getMessage(), e.getCause(), null);
		}

		log.info("<==CustomerServiceImpl.saveJobMonitorInfo()");

	}

	@Override
	public Map<String, String> loadCustomerData(String storeId,String fromDate, String toDate) throws CustomerServiceException {
		log.info("==>CustomerServiceImpl.loadCustomerData()");

		List<Customer> customerList = null;
		Map<String, String> resultMap = new HashMap<String,String>();
		Date jobStartDate = new Date();
		
		try {
			customerList = new ArrayList<Customer>();
			Statement statement = connection.createStatement();

			StringBuilder queryBuilder = new StringBuilder();
			queryBuilder.append("select customer_primary_key, store_customer_id, customer_id, order_id, abandoned_checkout_id, store_id, "
					+ "snowflake_schema, user_role, registered, email, first_name, last_name, address_1, address_2, phone, city, state_province, country, postal_code, "
					+ "date_of_birth, marketing_consent, profiling_consent, total_orders, total_completed_orders, customer_total_spent, customer_total_spent_all_stores, "
					+ "date_registered, customer_date_created, customer_date_modified, consolidated_date_created, consolidated_date_modified,MARKETING_CONSENT_UPDATED_DATE from ECOMMERCE_CUSTOMERS where DELETE_REQUESTED != 'TRUE' and ");
			if(null != storeId && storeId != "" && !storeId.equalsIgnoreCase("null")) {
				queryBuilder.append("store_id = '"+storeId+"' and ");
			}
				
			queryBuilder.append("(CONSOLIDATED_DATE_CREATED  >= '"+fromDate+"' AND CONSOLIDATED_DATE_CREATED <= '"+toDate+"' OR CONSOLIDATED_DATE_MODIFIED >= '"+fromDate+"' and CONSOLIDATED_DATE_MODIFIED <= '"+toDate+"');" );

			ResultSet resultSet = statement.executeQuery(queryBuilder.toString());
			while (resultSet.next()) {
				Customer customer = new Customer();
				customer.setCustomer_primary_key(resultSet.getLong(1));
				customer.setStore_customer_id(resultSet.getString(2));
				customer.setCustomer_id(resultSet.getLong(3));
				customer.setOrder_id(resultSet.getLong(4));
				customer.setAbandoned_checkout_id(resultSet.getLong(5));
				customer.setStore_id(resultSet.getString(6));
				customer.setSnowflake_schema(resultSet.getString(7));
				customer.setUser_role(resultSet.getString(8));
				customer.setRegistered(resultSet.getBoolean(9));
				customer.setEmail(resultSet.getString(10));
				customer.setFirst_name(resultSet.getString(11));
				customer.setLast_name(resultSet.getString(12));
				customer.setAddress_1(resultSet.getString(13));
				customer.setAddress_2(resultSet.getString(14));
				customer.setPhone(resultSet.getString(15));
				customer.setCity(resultSet.getString(16));
				customer.setState_province(resultSet.getString(17));
				customer.setCountry(resultSet.getString(18));
				customer.setPostal_code(resultSet.getString(19));
				customer.setDate_of_birth(resultSet.getString(20));
				customer.setMarketing_consent(resultSet.getBoolean(21));
				customer.setProfiling_consent(resultSet.getBoolean(22));
				customer.setTotal_orders(resultSet.getLong(23));
				customer.setTotal_completed_orders(resultSet.getLong(24));
				customer.setCustomer_total_spent(resultSet.getFloat(25));
				customer.setCustomer_total_spent_all_stores(resultSet.getFloat(26));
				customer.setDate_registered(resultSet.getDate(27));
				customer.setCustomer_date_created(resultSet.getDate(28));
				customer.setCustomer_date_modified(resultSet.getDate(29));
				customer.setConsolidated_date_created(resultSet.getDate(30));
				customer.setConsolidated_date_modified(resultSet.getDate(31));
				customer.setMarketing_consent_updated_date(resultSet.getDate(32));

				customerList.add(customer);
			}
		} catch (SQLException e) {
			log.error("==> Exception at CustomerServiceImpl.loadCustomerData()", e);
			throw new CustomerServiceException(e.getMessage(), e.getCause(), null);
		}
		
		if (customerList.size() > 0) {

			JobInfo jobInfo;
			try {				
				System.out.println("Session iD ===="+bulkConnection.SESSION_ID);
				jobInfo = SFBulkUtils.createJob("Ecommerce_Customer__c", bulkConnection);
				List<BatchInfo> customerBatchList = customerBatch.createBatche(bulkConnection, jobInfo, customerList);
				resultMap = customerBatch.bulkImport(bulkConnection, jobInfo, customerBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", customerList.size()+"");
			}catch (Exception e) {
				if (e.getMessage().contains("Invalid session id")) {			        
			        try {
			        	this.bulkConnection = sFConnections.createBulkConnection();
						jobInfo = SFBulkUtils.createJob("Ecommerce_Customer__c", bulkConnection);
						List<BatchInfo> customerBatchList = customerBatch.createBatche(bulkConnection, jobInfo, customerList);
						try {
							resultMap = customerBatch.bulkImport(bulkConnection, jobInfo, customerBatchList, jobStartDate);
							resultMap.put("SnowflakeRecordscount", customerList.size()+"");
						} catch (ConnectionException ce) {
							log.error("==>Exception at CustomerServiceImpl.loadCustomerData()", ce);
							throw new CustomerServiceException(ce.getMessage(), ce.getCause(), null);
						}
					} catch (Exception ie) {
						log.error("==>Exception at CustomerServiceImpl.loadCustomerData()", ie);
						throw new CustomerServiceException(ie.getMessage(), ie.getCause(), null);
					}

					return resultMap;
			    }
				log.error("==>Exception at CustomerServiceImpl.loadCustomerData()", e);
				throw new CustomerServiceException(e.getMessage(), e.getCause(), null);
			}
		}else {
			resultMap.put("message", "based on given criteria no records are matching.");
			resultMap=new HashMap<String, String>();
			resultMap.put("NODATA", "true");
		}

		log.info("<==CustomerServiceImpl.loadCustomerData()");
		return resultMap;
	}
	
}