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.ProductServiceException;
import com.prime.sony.ecommerce.helper.ProductBatch;
import com.prime.sony.ecommerce.model.JobInformation;
import com.prime.sony.ecommerce.model.Product;
import com.prime.sony.ecommerce.service.ProductService;
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 ProductServiceImpl implements ProductService {

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

	@Autowired
	private Connection connection;

	@Autowired
	private BulkConnection bulkConnection;

	@Autowired
	private ProductBatch productBatch;

	@Autowired
	private JobInformation jobInformation;

	@Autowired
	private SFConnections sFConnections;

	@Override
	public Map<String, String> saveProductData() throws ProductServiceException {
		log.info("==>ProductServiceImpl.saveProductData()");

		List<Product> productList = null;
		Map<String, String> resultMap = null;
		Date jobStartDate = new Date();

		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			productList = new ArrayList<Product>();
			Statement statement = connection.createStatement();

			String currentDate = jobInformation.getSchedulerExecutionTime();

			String query = "SELECT ep.PRODUCT_PRIMARY_KEY,ep.STORE_ID,ep.SNOWFLAKE_SCHEMA,ep.PARENT_PRODUCT_ID,ep.PARENT_PRODUCT_SKU,"
					+ "ep.PRODUCT_ID,ep.PRODUCT_VARIANT_ID,ep.PRODUCT_SKU,ep.PRODUCT_URL,ep.PRODUCT_IMAGE_URL,ep.PRODUCT_NAME,ep.PRODUCT_DESCRIPTION,ep.TYPE,"
					+ "ep.PRICE,ep.SALE_PRICE,ep.SALE_PRICE_DATES_FROM,ep.SALE_PRICE_DATES_TO,ep.CATEGORY,ep.CATEGORY_LEVEL_1,ep.CATEGORY_LEVEL_2,ep.CATEGORY_LEVEL_3,ep.CATEGORY_LEVEL_4,"
					+ "ep.TAG,ep.PRODUCT_PUBLISHED,ep.ARTIST,ep.PRODUCT_GENDER,ep.PRODUCT_BRAND,ep.PRODUCT_DATE_CREATED,ep.PRODUCT_DATE_MODIFIED,ep.CONSOLIDATED_DATE_CREATED,"
					+ "ep.CONSOLIDATED_DATE_MODIFIED FROM ECOMMERCE_PRODUCTS ep	 "
					+ "INNER JOIN ECOMMERCE_STORES es ON es.STORE_ID = ep.STORE_ID "
					+ "where es.ACTIVE = 'TRUE' AND (ep.CONSOLIDATED_DATE_CREATED  >= DATEADD("
					+ jobInformation.getScheduledforEvery() + ", -" + schedulerTime + ", '" + currentDate
					+ "') OR ep.CONSOLIDATED_DATE_MODIFIED >= DATEADD(" + jobInformation.getScheduledforEvery() + ", -"
					+ schedulerTime + ", '" + currentDate + "'))";
			log.info("==>ProductServiceImpl.saveProductData() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				Product product = new Product();
				product.setProductPrimaryKey(resultSet.getLong(1));
				product.setStoreId(resultSet.getString(2));
				product.setSnowflake_schema(resultSet.getString(3));
				product.setParent_product_id(resultSet.getString(4));
				product.setParent_product_sku(resultSet.getString(5));
				product.setProductId(resultSet.getLong(6));
				product.setProduct_variant_id(resultSet.getLong(7));
				product.setProductSku(resultSet.getString(8));
				product.setProductUrl(resultSet.getString(9));
				product.setProductImageURL(resultSet.getString(10));

				product.setProductName(resultSet.getString(11));
				product.setProductDescription(resultSet.getString(12));
				product.setType(resultSet.getString(13));
				product.setPrice(resultSet.getFloat(14));
				product.setSalePrice(resultSet.getFloat(15));
				product.setSale_price_dates_from(resultSet.getDate(16));
				product.setSale_price_dates_to(resultSet.getDate(17));
				product.setCategory(resultSet.getString(18));
				product.setCategory_level1(resultSet.getString(19));

				product.setCategory_level2(resultSet.getString(20));
				product.setCategory_level3(resultSet.getString(21));
				product.setCategory_level4(resultSet.getString(22));
				product.setTag(resultSet.getString(23));
				product.setProduct_published(resultSet.getDate(24));
				product.setArtist(resultSet.getString(25));
				product.setProduct_gender(resultSet.getString(26));

				product.setProduct_brand(resultSet.getString(27));

				product.setProduct_created_date(resultSet.getDate(28));
				product.setProduct_modified_date(resultSet.getDate(29));
				product.setConsolidated_date_created(resultSet.getDate(30));
				product.setConsolidated_date_modified(resultSet.getDate(31));

				productList.add(product);
			}
		} catch (SQLException e) {
			log.error("==> Exception at ProductServiceImpl.saveProductData()", e);
			throw new ProductServiceException(e.getMessage(), e.getCause(), null);
		}

		if (productList.size() > 0) {

			JobInfo jobInfo;
			try {
				jobInfo = SFBulkUtils.createJob("Ecommerce_Product__c", bulkConnection);
				List<BatchInfo> productBatchList = productBatch.createBatche(bulkConnection, jobInfo, productList);

				resultMap = productBatch.bulkImport(bulkConnection, jobInfo, productBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", productList.size() + "");
			} catch (Exception e) {

				if (e.getMessage().contains("Invalid session id")) {
					try {
						this.bulkConnection = sFConnections.createBulkConnection();
						jobInfo = SFBulkUtils.createJob("Ecommerce_Product__c", bulkConnection);
						List<BatchInfo> productBatchList = productBatch.createBatche(bulkConnection, jobInfo,
								productList);
						try {
							resultMap = productBatch.bulkImport(bulkConnection, jobInfo, productBatchList,
									jobStartDate);
							resultMap.put("SnowflakeRecordscount", productList.size() + "");
						} catch (ConnectionException ce) {
							log.error("==>Exception at ProductServiceImpl.saveProductData()", ce);
							throw new ProductServiceException(ce.getMessage(), ce.getCause(), null);
						}
					} catch (Exception ie) {
						log.error("==>Exception at ProductServiceImpl.saveProductData()", ie);
						throw new ProductServiceException(ie.getMessage(), ie.getCause(), null);
					}

					return resultMap;
				}

				log.error("==>Exception at ProductServiceImpl.saveProductData()", e);
				throw new ProductServiceException(e.getMessage(), e.getCause(), null);

			}
		} else {
			resultMap = new HashMap<String, String>();
			resultMap.put("NODATA", "true");
		}

		log.info("<==ProductServiceImpl.saveProductData()");
		return resultMap;
	}

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

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

	}

	@Override
	public Map<String, String> loadProductData(String storeId, String fromDate, String toDate)
			throws ProductServiceException {
		log.info("==>ProductServiceImpl.loadProductData()");

		List<Product> productList = null;
		Map<String, String> resultMap = new HashMap<String, String>();
		Date jobStartDate = new Date();

		try {
			productList = new ArrayList<Product>();
			Statement statement = connection.createStatement();

			StringBuilder queryBuilder = new StringBuilder();
			queryBuilder
					.append("SELECT PRODUCT_PRIMARY_KEY,STORE_ID,SNOWFLAKE_SCHEMA,PARENT_PRODUCT_ID,PARENT_PRODUCT_SKU,"
							+ "PRODUCT_ID,PRODUCT_VARIANT_ID,PRODUCT_SKU,PRODUCT_URL,PRODUCT_IMAGE_URL,PRODUCT_NAME,PRODUCT_DESCRIPTION,TYPE,"
							+ "PRICE,SALE_PRICE,SALE_PRICE_DATES_FROM,SALE_PRICE_DATES_TO,CATEGORY,CATEGORY_LEVEL_1,CATEGORY_LEVEL_2,CATEGORY_LEVEL_3,CATEGORY_LEVEL_4,"
							+ "TAG,PRODUCT_PUBLISHED,ARTIST,PRODUCT_GENDER,PRODUCT_BRAND,PRODUCT_DATE_CREATED,PRODUCT_DATE_MODIFIED,CONSOLIDATED_DATE_CREATED,"
							+ "CONSOLIDATED_DATE_MODIFIED FROM ECOMMERCE_PRODUCTS where ");
			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()) {
				Product product = new Product();
				product.setProductPrimaryKey(resultSet.getLong(1));
				product.setStoreId(resultSet.getString(2));
				product.setSnowflake_schema(resultSet.getString(3));
				product.setParent_product_id(resultSet.getString(4));
				product.setParent_product_sku(resultSet.getString(5));
				product.setProductId(resultSet.getLong(6));
				product.setProduct_variant_id(resultSet.getLong(7));
				product.setProductSku(resultSet.getString(8));
				product.setProductUrl(resultSet.getString(9));
				product.setProductImageURL(resultSet.getString(10));

				product.setProductName(resultSet.getString(11));
				product.setProductDescription(resultSet.getString(12));
				product.setType(resultSet.getString(13));
				product.setPrice(resultSet.getFloat(14));
				product.setSalePrice(resultSet.getFloat(15));
				product.setSale_price_dates_from(resultSet.getDate(16));
				product.setSale_price_dates_to(resultSet.getDate(17));
				product.setCategory(resultSet.getString(18));
				product.setCategory_level1(resultSet.getString(19));

				product.setCategory_level2(resultSet.getString(20));
				product.setCategory_level3(resultSet.getString(21));
				product.setCategory_level4(resultSet.getString(22));
				product.setTag(resultSet.getString(23));
				product.setProduct_published(resultSet.getDate(24));
				product.setArtist(resultSet.getString(25));
				product.setProduct_gender(resultSet.getString(26));

				product.setProduct_brand(resultSet.getString(27));

				product.setProduct_created_date(resultSet.getDate(28));
				product.setProduct_modified_date(resultSet.getDate(29));
				product.setConsolidated_date_created(resultSet.getDate(30));
				product.setConsolidated_date_modified(resultSet.getDate(31));

				productList.add(product);
			}
		} catch (SQLException e) {
			log.error("==> Exception at ProductServiceImpl.loadProductData()", e);
			throw new ProductServiceException(e.getMessage(), e.getCause(), null);
		}

		if (productList.size() > 0) {

			JobInfo jobInfo;
			try {
				jobInfo = SFBulkUtils.createJob("Ecommerce_Product__c", bulkConnection);
				List<BatchInfo> storesBatchList = productBatch.createBatche(bulkConnection, jobInfo, productList);

				resultMap = productBatch.bulkImport(bulkConnection, jobInfo, storesBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", productList.size() + "");
			} catch (Exception e) {

				if (e.getMessage().contains("InvalidSessionId : Invalid session id")) {
					try {
						this.bulkConnection = new BulkConnection(bulkConnection.getConfig());
						jobInfo = SFBulkUtils.createJob("Ecommerce_Product__c", bulkConnection);
						List<BatchInfo> storesBatchList = productBatch.createBatche(bulkConnection, jobInfo,
								productList);

						resultMap = productBatch.bulkImport(bulkConnection, jobInfo, storesBatchList, jobStartDate);
						resultMap.put("SnowflakeRecordscount", productList.size() + "");
					} catch (Exception e1) {
						log.error("==>Exception at ProductServiceImpl.loadProductData() in catch of InvalidSessionId ",
								e1);
					}
				} else {
					log.error("==>Exception at ProductServiceImpl.loadProductData()", e);
					throw new ProductServiceException(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("<==ProductServiceImpl.loadProductData()");
		return resultMap;
	}

}