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.OrderServiceException;
import com.prime.sony.ecommerce.helper.OrdersBatch;
import com.prime.sony.ecommerce.model.JobInformation;
import com.prime.sony.ecommerce.model.Orders;
import com.prime.sony.ecommerce.service.OrderService;
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 OrderServiceImpl implements OrderService {

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

	@Autowired
	private Connection connection;

	@Autowired
	private BulkConnection bulkConnection;

	@Autowired
	private OrdersBatch ordersBatch;

	@Autowired
	private JobInformation jobInformation;
	
	@Autowired
	private SFConnections sFConnections;

	@Override
	public Map<String, String> saveOrderData() throws OrderServiceException {
		log.info("==>OrderServiceImpl.saveOrderData()");

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

			String currentDate = jobInformation.getSchedulerExecutionTime();
			String query = ("select eo.order_primary_key,eo.store_id,eo.snowflake_schema,eo.product_id,"
					+ "eo.product_variant_id,eo.product_sku,eo.store_customer_id,eo.order_id,eo.order_total,eo.order_subtotal,eo.currency,eo.coupon_code,"
					+ "eo.coupon_start_date,eo.coupon_expiry_date,eo.coupon_description,eo.refund_total,eo.refund_date,eo.order_status,eo.order_date,"
					+ "eo.total_quantity,eo.total_order_items,eo.order_items_id,eo.order_items_quantity,eo.order_items_total,"
					+ "eo.order_items_subtotal,eo.order_items_refund_subtotal,eo.order_items_refund_quantity,eo.order_date_created,"
					+ "eo.order_date_modified,eo.consolidated_date_created,eo.consolidated_date_modified,eo.ALT_ORDER_ID,CUSTOMER_ID,eo.EMAIL,eo.MARKETING_CONSENT,eo.DISCOUNT_CODE from ECOMMERCE_ORDERS eo "
					+ "inner join ecommerce_stores es on es.store_id = eo.store_id "
					+ "where es.active = 'TRUE' and eo.DELETE_REQUESTED != 'TRUE' and (eo.CONSOLIDATED_DATE_CREATED  >= DATEADD("
					+ jobInformation.getScheduledforEvery() + ", -" + schedulerTime + ", '" + currentDate
					+ "') OR eo.CONSOLIDATED_DATE_MODIFIED >= DATEADD(" + jobInformation.getScheduledforEvery() + ", -"
					+ schedulerTime + ", '" + currentDate + "'))");
			log.info("==>OrderServiceImpl.saveOrderData() query:"+query);
			ResultSet resultSet = statement.executeQuery(query);
					
			while (resultSet.next()) {
				Orders order = new Orders();
				order.setOrder_primary_key(resultSet.getLong(1));
				order.setStore_id(resultSet.getString(2));
				order.setSnowflake_schema(resultSet.getString(3));
				order.setProduct_id(resultSet.getLong(4));
				order.setProduct_variant_id(resultSet.getLong(5));
				order.setProduct_sku(resultSet.getString(6));
				order.setStore_customer_id(resultSet.getLong(7));
				order.setOrder_id(resultSet.getString(8));
				order.setOrder_total(resultSet.getFloat(9));
				order.setOrder_subtotal(resultSet.getFloat(10));
				order.setCurrency(resultSet.getString(11));
				order.setCoupon_code(resultSet.getString(12));
				order.setCoupon_start_date(resultSet.getDate(13));
				order.setCoupon_expiry_date(resultSet.getDate(14));
				order.setCoupon_description(resultSet.getString(15));
				order.setRefund_total(resultSet.getFloat(16));
				order.setRefund_date(resultSet.getDate(17));
				order.setOrder_status(resultSet.getString(18));
				order.setOrder_date(resultSet.getDate(19));
				order.setTotal_quantity(resultSet.getLong(20));
				order.setTotal_order_items(resultSet.getLong(21));
				order.setOrder_items_id(resultSet.getString(22));
				order.setOrder_items_quantity(resultSet.getLong(23));
				order.setOrder_items_total(resultSet.getFloat(24));
				order.setOrder_items_subtotal(resultSet.getFloat(25));
				order.setOrder_items_refund_subtotal(resultSet.getFloat(26));
				order.setOrder_items_refund_quantity(resultSet.getFloat(27));
				order.setOrder_date_created(resultSet.getDate(28));
				order.setOrder_date_modified(resultSet.getDate(29));
				order.setConsolidated_date_created(resultSet.getDate(30));
				order.setConsolidated_date_modified(resultSet.getDate(31));
				order.setAltOrderId(resultSet.getString(32));
				order.setCustomerId(resultSet.getString(33));
				order.setEmail(resultSet.getString(34));
				order.setMarketing_consent(resultSet.getBoolean(35));
				order.setDiscount_Code__c(resultSet.getString(36));
				
				ordersList.add(order);
			}
		} catch (SQLException e) {
			log.error("==> Exception at OrderServiceImpl.saveOrderData()", e);
			throw new OrderServiceException(e.getMessage(), e.getCause(), null);
		}

		if (ordersList.size() > 0) {

			JobInfo jobInfo;
			try {
				jobInfo = SFBulkUtils.createJob("EcommerceOrder__c", bulkConnection);
				List<BatchInfo> orderBatchList = ordersBatch.createBatche(bulkConnection, jobInfo, ordersList);
				System.out.println("Before  === " + jobInfo.getState());
				Thread.sleep(50000);
				resultMap = ordersBatch.bulkImport(bulkConnection, jobInfo, orderBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", ordersList.size()+"");
				System.out.println("After === " + jobInfo.getState());
			} catch (Exception e) {
				if (e.getMessage().contains("Invalid session id")) {			        
			        try {
			        	this.bulkConnection = sFConnections.createBulkConnection();
						jobInfo = SFBulkUtils.createJob("EcommerceOrder__c", bulkConnection);
						List<BatchInfo> orderBatchList = ordersBatch.createBatche(bulkConnection, jobInfo, ordersList);
						try {
							resultMap = ordersBatch.bulkImport(bulkConnection, jobInfo, orderBatchList, jobStartDate);
							resultMap.put("SnowflakeRecordscount", ordersList.size()+"");
						} catch (ConnectionException ce) {
							log.error("==>Exception at OrderServiceImpl.saveOrderData()", ce);
							throw new OrderServiceException(ce.getMessage(), ce.getCause(), null);
						}
					} catch (Exception ie) {
						log.error("==>Exception at OrderServiceImpl.saveOrderData()", ie);
						throw new OrderServiceException(ie.getMessage(), ie.getCause(), null);
					}

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

		log.info("<==OrderServiceImpl.saveOrderData()");
		return resultMap;
	}

	@Override
	public void saveJobMonitorInfo(JobExecution jobExecution) throws OrderServiceException {
		log.info("==>OrderServiceImpl.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 OrderServiceImpl.saveJobMonitorInfo()", e);
			throw new OrderServiceException(e.getMessage(), e.getCause(), null);
		}

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

	}

	@Override
	public Map<String, String> loadOrderData(String storeId, String fromDate, String toDate)
			throws OrderServiceException {
		log.info("==>OrderServiceImpl.loadOrderData()");

		List<Orders> ordersList = null;
		Map<String, String> resultMap = new HashMap<String,String>();
		Date jobStartDate = new Date();
		//int schedulerTime = 0;
		try {
			ordersList = new ArrayList<Orders>();
			Statement statement = connection.createStatement();

			StringBuilder queryBuilder = new StringBuilder();
			queryBuilder.append("select order_primary_key,store_id,snowflake_schema,product_id,"
					+ "product_variant_id,product_sku,store_customer_id,order_id,order_total,order_subtotal,currency,coupon_code,"
					+ "coupon_start_date,coupon_expiry_date,coupon_description,refund_total,refund_date,order_status,order_date,"
					+ "total_quantity,total_order_items,order_items_id,order_items_quantity,order_items_total,"
					+ "order_items_subtotal,order_items_refund_subtotal,order_items_refund_quantity,order_date_created,"
					+ "order_date_modified,consolidated_date_created,consolidated_date_modified,ALT_ORDER_ID,CUSTOMER_ID,EMAIL,MARKETING_CONSENT,DISCOUNT_CODE from ECOMMERCE_ORDERS where DELETE_REQUESTED != 'TRUE' and ");
			if(null != storeId && storeId != "" && !storeId.equalsIgnoreCase("null")) {
				queryBuilder.append("store_id = '"+storeId+"' and ");
			}
			queryBuilder.append("(EO.CONSOLIDATED_DATE_CREATED  >= '"+fromDate+"' AND EO.CONSOLIDATED_DATE_CREATED <= '"+toDate+"' OR EO.CONSOLIDATED_DATE_MODIFIED >= '"+fromDate+"' and EO.CONSOLIDATED_DATE_MODIFIED <= '"+toDate+"');" );
			
			ResultSet resultSet = statement.executeQuery(queryBuilder.toString());
			while (resultSet.next()) {
				Orders order = new Orders();
				order.setOrder_primary_key(resultSet.getLong(1));
				order.setStore_id(resultSet.getString(2));
				order.setSnowflake_schema(resultSet.getString(3));
				order.setProduct_id(resultSet.getLong(4));
				order.setProduct_variant_id(resultSet.getLong(5));
				order.setProduct_sku(resultSet.getString(6));
				order.setStore_customer_id(resultSet.getLong(7));
				order.setOrder_id(resultSet.getString(8));
				order.setOrder_total(resultSet.getFloat(9));
				order.setOrder_subtotal(resultSet.getFloat(10));
				order.setCurrency(resultSet.getString(11));
				order.setCoupon_code(resultSet.getString(12));
				order.setCoupon_start_date(resultSet.getDate(13));
				order.setCoupon_expiry_date(resultSet.getDate(14));
				order.setCoupon_description(resultSet.getString(15));
				order.setRefund_total(resultSet.getFloat(16));
				order.setRefund_date(resultSet.getDate(17));
				order.setOrder_status(resultSet.getString(18));
				order.setOrder_date(resultSet.getDate(19));
				order.setTotal_quantity(resultSet.getLong(20));
				order.setTotal_order_items(resultSet.getLong(21));
				order.setOrder_items_id(resultSet.getString(22));
				order.setOrder_items_quantity(resultSet.getLong(23));
				order.setOrder_items_total(resultSet.getFloat(24));
				order.setOrder_items_subtotal(resultSet.getFloat(25));
				order.setOrder_items_refund_subtotal(resultSet.getFloat(26));
				order.setOrder_items_refund_quantity(resultSet.getFloat(27));
				order.setOrder_date_created(resultSet.getDate(28));
				order.setOrder_date_modified(resultSet.getDate(29));
				order.setConsolidated_date_created(resultSet.getDate(30));
				order.setConsolidated_date_modified(resultSet.getDate(31));
				order.setAltOrderId(resultSet.getString(32));
				order.setCustomerId(resultSet.getString(33));
				order.setEmail(resultSet.getString(34));
				order.setMarketing_consent(resultSet.getBoolean(34));
				order.setDiscount_Code__c(resultSet.getString(35));
				
				ordersList.add(order);
			}
		} catch (SQLException e) {
			log.error("==> Exception at OrderServiceImpl.loadOrderData()", e);
			throw new OrderServiceException(e.getMessage(), e.getCause(), null);
		}

		if (ordersList.size() > 0) {

			JobInfo jobInfo;
			try {
				jobInfo = SFBulkUtils.createJob("EcommerceOrder__c", bulkConnection);
				List<BatchInfo> customerBatchList = ordersBatch.createBatche(bulkConnection, jobInfo, ordersList);

				resultMap = ordersBatch.bulkImport(bulkConnection, jobInfo, customerBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", ordersList.size()+"");
			} catch (Exception e) {
				
				if(e.getMessage().contains("InvalidSessionId : Invalid session id")) {
					try {
						this.bulkConnection = new BulkConnection(bulkConnection.getConfig());
						jobInfo = SFBulkUtils.createJob("EcommerceOrder__c", bulkConnection);
						List<BatchInfo> customerBatchList = ordersBatch.createBatche(bulkConnection, jobInfo, ordersList);

						resultMap = ordersBatch.bulkImport(bulkConnection, jobInfo, customerBatchList, jobStartDate);
						resultMap.put("SnowflakeRecordscount", ordersList.size()+"");
					} catch (Exception e1) {
						log.error("==>Exception at OrderServiceImpl.loadOrderData() in catch of InvalidSessionId ", e1);
					}
				}else {
					log.error("==>Exception at OrderServiceImpl.loadOrderData()", e);
					throw new OrderServiceException(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("<==OrderServiceImpl.loadOrderData()");
		return resultMap;
	}

}