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.StoreServiceException;
import com.prime.sony.ecommerce.helper.StoreBatch;
import com.prime.sony.ecommerce.model.JobInformation;
import com.prime.sony.ecommerce.model.Store;
import com.prime.sony.ecommerce.service.StoreService;
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 StoreServiceImpl implements StoreService {

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

	@Autowired
	private Connection connection;

	@Autowired
	private BulkConnection bulkConnection;

	@Autowired
	private StoreBatch storeBatch;

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

	@Override
	public Map<String, String> saveStoreData() throws StoreServiceException {
		log.info("==>StoreServiceImpl.saveStoreData()");
		
		List<Store> storeList = null;
		Map<String, String> resultMap = null;
		Date jobStartDate = new Date();
		//int schedulerTime = 0;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			storeList = new ArrayList<Store>();
			Statement statement = connection.createStatement();
//			ResultSet result = statement.executeQuery("SELECT * FROM SCHEDULER_TIME WHERE JOB_INFORMATION_ID = 1");
//			while (result.next()) {
//				schedulerTime = result.getInt(3);
//			}
			String currentDate = jobInformation.getSchedulerExecutionTime();
			ResultSet resultSet = statement.executeQuery("SELECT STORE_ID,SNOWFLAKE_SCHEMA,PLATFORM,STORE_NAME,"
					+ "TERRITORY,TERRITORY_ID,LABEL,LABEL_ID,ARTIST,ARTIST_ID,TLA,STORE_URL_PREFIX,DEDICATED_ARTIST_STORE,"
					+ "CONSOLIDATED_DATE_CREATED,CONSOLIDATED_DATE_MODIFIED,MAILING_LIST_ID,FORM_ID, ACTIVE FROM ECOMMERCE_STORES "
					+ "WHERE ACTIVE = TRUE AND (CONSOLIDATED_DATE_CREATED  >= DATEADD(" + jobInformation.getScheduledforEvery() + ", -"
					+ schedulerTime + ", '" + currentDate + "') OR CONSOLIDATED_DATE_MODIFIED >= DATEADD("
					+ jobInformation.getScheduledforEvery() + ", -" + schedulerTime + ", '" + currentDate + "'))");

			while (resultSet.next()) {
				Store store = new Store();
				store.setStoreId(resultSet.getString(1));
				store.setSnowflake_schema(resultSet.getString(2));
				store.setPlatform(resultSet.getString(3));
				store.setStoreName(resultSet.getString(4));
				store.setTerritory(resultSet.getString(5));
				store.setTerritoryId(resultSet.getString(6));
				store.setLabel(resultSet.getString(7));
				store.setLabelId(resultSet.getString(8));
				store.setArtist(resultSet.getString(9));
				store.setArtistId(resultSet.getString(10));
				store.setTla(resultSet.getString(11));
				store.setStoreUrlPrefix(resultSet.getString(12));
				store.setDedicatedArtistStore(resultSet.getBoolean(13));
				store.setConsolidated_date_created(resultSet.getDate(14));
				store.setConsolidated_date_modified(resultSet.getDate(15));
				store.setMailingList_Id(resultSet.getString(16));
				store.setFormId(resultSet.getString(17));
				store.setActive(resultSet.getBoolean(18));
				storeList.add(store);
			}
		} catch (SQLException e) {
			log.error("==> Exception at StoreServiceImpl.saveStoreData()",e);
			throw new StoreServiceException(e.getMessage(), e.getCause(), null);
		}

		if (storeList.size() > 0) {

			JobInfo jobInfo;
			try {
				jobInfo = SFBulkUtils.createJob("Ecommerce_Store__c", bulkConnection);
				List<BatchInfo> storesBatchList = storeBatch.createBatche(bulkConnection, jobInfo, storeList);

				resultMap = storeBatch.bulkImport(bulkConnection, jobInfo, storesBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", storeList.size()+"");
			} catch (Exception e) {
				if (e.getMessage().contains("Invalid session id")) {			        
			        try {
			        	this.bulkConnection = sFConnections.createBulkConnection();
						jobInfo = SFBulkUtils.createJob("Ecommerce_Store__c", bulkConnection);
						List<BatchInfo> storesBatchList = storeBatch.createBatche(bulkConnection, jobInfo, storeList);
						try {
							resultMap = storeBatch.bulkImport(bulkConnection, jobInfo, storesBatchList, jobStartDate);
							resultMap.put("SnowflakeRecordscount", storeList.size()+"");
						} catch (ConnectionException ce) {
							log.error("==>Exception at StoreServiceImpl.saveStoreData()", ce);
							throw new StoreServiceException(ce.getMessage(), ce.getCause(), null);
						}
					} catch (Exception ie) {
						log.error("==>Exception at StoreServiceImpl.saveStoreData()", ie);
						throw new StoreServiceException(ie.getMessage(), ie.getCause(), null);
					}

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


		log.info("<==StoreServiceImpl.saveStoreData()");
		return resultMap;
	}

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

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

	}

	@Override
	public Map<String, String> loadStoreData(String fromDate, String toDate) throws StoreServiceException {
log.info("==>StoreServiceImpl.loadStoreData()");
		
		List<Store> storeList = null;
		Map<String, String> resultMap = new HashMap<String,String>();
		Date jobStartDate = new Date();
		//int schedulerTime = 0;
		try {
			storeList = new ArrayList<Store>();
			Statement statement = connection.createStatement();
			
			StringBuilder queryBuilder = new StringBuilder("SELECT STORE_ID,SNOWFLAKE_SCHEMA,PLATFORM,STORE_NAME,"
					+ "TERRITORY,TERRITORY_ID,LABEL,LABEL_ID,ARTIST,ARTIST_ID,TLA,STORE_URL_PREFIX,DEDICATED_ARTIST_STORE,"
					+ "CONSOLIDATED_DATE_CREATED,CONSOLIDATED_DATE_MODIFIED,MAILING_LIST_ID,FORM_ID FROM ECOMMERCE_STORES  ");
			queryBuilder.append(" where (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()) {
				Store store = new Store();
				store.setStoreId(resultSet.getString(1));
				store.setSnowflake_schema(resultSet.getString(2));
				store.setPlatform(resultSet.getString(3));
				store.setStoreName(resultSet.getString(4));
				store.setTerritory(resultSet.getString(5));
				store.setTerritoryId(resultSet.getString(6));
				store.setLabel(resultSet.getString(7));
				store.setLabelId(resultSet.getString(8));
				store.setArtist(resultSet.getString(9));
				store.setArtistId(resultSet.getString(10));
				store.setTla(resultSet.getString(11));
				store.setStoreUrlPrefix(resultSet.getString(12));
				store.setDedicatedArtistStore(resultSet.getBoolean(13));
				store.setConsolidated_date_created(resultSet.getDate(14));
				store.setConsolidated_date_modified(resultSet.getDate(15));
				store.setMailingList_Id(resultSet.getString(16));
				store.setFormId(resultSet.getString(17));
				storeList.add(store);
			}
		} catch (SQLException e) {
			log.error("==> Exception at StoreServiceImpl.loadStoreData()",e);
			throw new StoreServiceException(e.getMessage(), e.getCause(), null);
		}

		if (storeList.size() > 0) {

			JobInfo jobInfo;
			try {
				jobInfo = SFBulkUtils.createJob("Ecommerce_Store__c", bulkConnection);
				List<BatchInfo> storesBatchList = storeBatch.createBatche(bulkConnection, jobInfo, storeList);

				resultMap = storeBatch.bulkImport(bulkConnection, jobInfo, storesBatchList, jobStartDate);
				resultMap.put("SnowflakeRecordscount", storeList.size()+"");
			} catch (Exception e) {
				//log.error("==>Exception at StoreServiceImpl.saveStoreData()",e);
				//throw new StoreServiceException(e.getMessage(), e.getCause(), null);
				
				if(e.getMessage().contains("InvalidSessionId : Invalid session id")) {
					try {
						this.bulkConnection = new BulkConnection(bulkConnection.getConfig());
						jobInfo = SFBulkUtils.createJob("Ecommerce_Store__c", bulkConnection);
						List<BatchInfo> storesBatchList = storeBatch.createBatche(bulkConnection, jobInfo, storeList);

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

}