package com.prime.sony.ecommerce.config;

import java.security.KeyFactory;
import java.security.PrivateKey;
import java.security.spec.PKCS8EncodedKeySpec;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Base64;
import java.util.Date;
import java.util.Properties;

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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope;
import org.springframework.web.context.WebApplicationContext;

import com.prime.sony.ecommerce.exceptions.AutodataCheckServiceException;
import com.prime.sony.ecommerce.exceptions.DeleteRequestServiceException;
import com.prime.sony.ecommerce.exceptions.RowStatusServiceException;
import com.prime.sony.ecommerce.exceptions.TaskStatusServiceException;
import com.prime.sony.ecommerce.model.AutoChecksJobInformation;
import com.prime.sony.ecommerce.model.DeleteRequestJobInformation;
import com.prime.sony.ecommerce.model.JobInformation;
import com.prime.sony.ecommerce.model.PipeJobInformation;
import com.prime.sony.ecommerce.model.ReconcilationJobInformation;
import com.prime.sony.ecommerce.model.SnowflakeC;

//@EnableWebMvc
@Configuration
public class JDBCConnection {

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

	@Autowired
    private SnowflakeC snowflakeC;
	
	@Value("${schema.italy}")
	private String schema_woocommerce_italy;
	
	@Value("${snowflake.schema}")
	private String schema;


    public static PrivateKey getPrivateKey(String base64PrivateKey) throws Exception {
        byte[] keyBytes = Base64.getDecoder().decode(base64PrivateKey);
        PKCS8EncodedKeySpec spec = new PKCS8EncodedKeySpec(keyBytes);
        KeyFactory keyFactory = KeyFactory.getInstance("RSA");
        return keyFactory.generatePrivate(spec);
    }

	@Bean
	@Scope(value = WebApplicationContext.SCOPE_APPLICATION)
	public Connection getConnection() throws TaskStatusServiceException {
		log.info("==>JDBCConnection.getConnection()");
		Connection con = null;
		try {
			Class.forName(snowflakeC.getDriverclassname());

			Properties properties = new Properties();
			properties.put("user", snowflakeC.getUsername()); // replace "" with your username
			properties.put("privateKey", getPrivateKey(snowflakeC.getPrivateKey())); // replace "" with your password
			properties.put("account", snowflakeC.getAccount()); // replace "" with your account name
			properties.put("db", snowflakeC.getDb()); // replace "" with target database name
			//properties.put("schema", "WOOCOMMERCE_ITALY"); // replace "" with target schema name
			properties.put("warehouse", snowflakeC.getWarehouse());
			properties.put("role", snowflakeC.getRole());
			properties.put("CLIENT_SESSION_KEEP_ALIVE","true");
			// Avoid Apache Arrow result parsing issues in some JVM/driver combinations.
			properties.put("JDBC_QUERY_RESULT_FORMAT", "JSON");
			String connectStr = snowflakeC.getUrl();
			
			log.info("<==JDBCConnection.getConnection()");
			return DriverManager.getConnection(connectStr, properties);

		} catch (Exception e) {
			log.error("==> Exception at JDBCConnection.getConnection()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}

	@Bean
	public PipeJobInformation getJobInfoForPipe() throws TaskStatusServiceException {
		log.info("==> JDBCConnection.getJobInfoForPipe()");
		String cronExpression = null;
		PipeJobInformation jobInfo = new PipeJobInformation();
		try {
			Connection con = getConnection();
			Statement statement = con.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT JI.JOB_ID,JI.JOB_NAME,JI.CRON_EXPRESSION,JI."
					+ "SCHEDULE_TIME_ID,JW.ID,JW.SCHHEDULED_FOR_EVERY,JI.DATA_INTERVAL_TIME"
					+ " FROM "+schema_woocommerce_italy+"."+"JOB_INFORMATION JI "
					+ "INNER JOIN "+schema_woocommerce_italy+"."+"SCHEDULER_TIME JW ON JI.SCHEDULE_TIME_ID=JW.ID "
					+ "where JOB_ID=1");
			while (resultSet.next()) {
				jobInfo.setJob_id(resultSet.getInt(1));
				jobInfo.setJob_name(resultSet.getString(2));
				jobInfo.setCron_expression(resultSet.getString(3));
				jobInfo.setScheduledforEvery(resultSet.getString(6));
				jobInfo.setDataIntervalTime(resultSet.getInt(7));
			}
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    	String jobStartTime = format.format(new Date().getTime());
	    	jobInfo.setSchedulerExecutionTime(jobStartTime);
	    	
			log.info("<== JDBCConnection.getJobInfoForPipe()");
			return jobInfo;
		} catch (SQLException e) {
			log.error("==> Exception at JDBCConnection.getJobInfoForPipe()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}
	
	@Bean
	public JobInformation getJobInfoForTask() throws TaskStatusServiceException {
		log.info("==> JDBCConnection.getJobInfoForTask()");
		String cronExpression = null;
		JobInformation jobInfo = new JobInformation();
		try {
			Connection con = getConnection();
			Statement statement = con.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT JI.JOB_ID,JI.JOB_NAME,JI.CRON_EXPRESSION,JI."
					+"SCHEDULE_TIME_ID,JW.ID,JW.SCHHEDULED_FOR_EVERY,JI.DATA_INTERVAL_TIME"
					+" FROM "+schema_woocommerce_italy+"."+ "JOB_INFORMATION JI "
					+"INNER JOIN "+schema_woocommerce_italy+"."+ "SCHEDULER_TIME JW ON JI.SCHEDULE_TIME_ID=JW.ID "
					+"where JOB_ID=2");
			while (resultSet.next()) {
				jobInfo.setJob_id(resultSet.getInt(1));
				jobInfo.setJob_name(resultSet.getString(2));
				jobInfo.setCron_expression(resultSet.getString(3));
				jobInfo.setScheduledforEvery(resultSet.getString(6));
				jobInfo.setDataIntervalTime(resultSet.getInt(7));
			}
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    	String jobStartTime = format.format(new Date().getTime());
	    	jobInfo.setSchedulerExecutionTime(jobStartTime);
	    	
			log.info("<== JDBCConnection.getJobInfoForTask()");
			return jobInfo;
		} catch (SQLException e) {
			log.error("==> Exception at JDBCConnection.getJobInfoForTask()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}
	
	@Bean
	public AutoChecksJobInformation getJobInfoForAutoDataChecks() throws TaskStatusServiceException {
		log.info("==> JDBCConnection.getJobInfoForAutoDataChecks()");
		String cronExpression = null;
		AutoChecksJobInformation jobInfo = new AutoChecksJobInformation();
		try {
			Connection con = getConnection();
			Statement statement = con.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT JI.JOB_ID,JI.JOB_NAME,JI.CRON_EXPRESSION,JI."
					+"SCHEDULE_TIME_ID,JW.ID,JW.SCHHEDULED_FOR_EVERY,JI.DATA_INTERVAL_TIME"
					+" FROM "+schema+"."+ "JOB_INFORMATION JI "
					+"INNER JOIN "+schema+"."+ "SCHEDULER_TIME JW ON JI.SCHEDULE_TIME_ID=JW.ID "
					+"where JOB_ID=5");
			while (resultSet.next()) {
				jobInfo.setJob_id(resultSet.getInt(1));
				jobInfo.setJob_name(resultSet.getString(2));
				jobInfo.setCron_expression(resultSet.getString(3));
				jobInfo.setScheduledforEvery(resultSet.getString(6));
				jobInfo.setDataIntervalTime(resultSet.getInt(7));
			}
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    	String jobStartTime = format.format(new Date().getTime());
	    	jobInfo.setSchedulerExecutionTime(jobStartTime);
	    	
			log.info("<== JDBCConnection.getJobInfoForAutoDataChecks()");
			return jobInfo;
		} catch (SQLException e) {
			log.error("==> Exception at JDBCConnection.getJobInfoForAutoDataChecks()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}
	
	@Bean
	public ReconcilationJobInformation getJobInfoForReconcilation() throws TaskStatusServiceException {
		log.info("==> JDBCConnection.getJobInfoForReconcilation()");
		String cronExpression = null;
		ReconcilationJobInformation jobInfo = new ReconcilationJobInformation();
		try {
			Connection con = getConnection();
			Statement statement = con.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT JI.JOB_ID,JI.JOB_NAME,JI.CRON_EXPRESSION,JI."
					+"SCHEDULE_TIME_ID,JW.ID,JW.SCHHEDULED_FOR_EVERY,JI.DATA_INTERVAL_TIME"
					+" FROM "+schema+"."+ "JOB_INFORMATION JI "
					+"INNER JOIN "+schema+"."+ "SCHEDULER_TIME JW ON JI.SCHEDULE_TIME_ID=JW.ID "
					+"where JOB_ID=7");
			while (resultSet.next()) {
				jobInfo.setJob_id(resultSet.getInt(1));
				jobInfo.setJob_name(resultSet.getString(2));
				jobInfo.setCron_expression(resultSet.getString(3));
				jobInfo.setScheduledforEvery(resultSet.getString(6));
				jobInfo.setDataIntervalTime(resultSet.getInt(7));
			}
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    	String jobStartTime = format.format(new Date().getTime());
	    	jobInfo.setSchedulerExecutionTime(jobStartTime);
	    	
			log.info("<== JDBCConnection.getJobInfoForReconcilation()");
			return jobInfo;
		} catch (SQLException e) {
			log.error("==> Exception at JDBCConnection.getJobInfoForReconcilation()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}
	
	@Bean
	public DeleteRequestJobInformation getJobInfoForDeleteReport() throws TaskStatusServiceException {
		log.info("==> JDBCConnection.getJobInfoForDeleteReport()");
		String cronExpression = null;
		DeleteRequestJobInformation jobInfo = new DeleteRequestJobInformation();
		try {
			Connection con = getConnection();
			Statement statement = con.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT JI.JOB_ID,JI.JOB_NAME,JI.CRON_EXPRESSION,JI."
					+"SCHEDULE_TIME_ID,JW.ID,JW.SCHHEDULED_FOR_EVERY,JI.DATA_INTERVAL_TIME"
					+" FROM "+schema+"."+ "JOB_INFORMATION JI "
					+"INNER JOIN "+schema+"."+ "SCHEDULER_TIME JW ON JI.SCHEDULE_TIME_ID=JW.ID "
					+"where JOB_ID=6");
			while (resultSet.next()) {
				jobInfo.setJob_id(resultSet.getInt(1));
				jobInfo.setJob_name(resultSet.getString(2));
				jobInfo.setCron_expression(resultSet.getString(3));
				jobInfo.setScheduledforEvery(resultSet.getString(6));
				jobInfo.setDataIntervalTime(resultSet.getInt(7));
			}
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    	String jobStartTime = format.format(new Date().getTime());
	    	jobInfo.setSchedulerExecutionTime(jobStartTime);
	    	
			log.info("<== JDBCConnection.getJobInfoForDeleteReport()");
			return jobInfo;
		} catch (SQLException e) {
			log.error("==> Exception at JDBCConnection.getJobInfoForDeleteReport()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}

	@Bean
	public String getCronValueForPipe() throws TaskStatusServiceException {
		log.info("==> JDBCConnection.getCronValueForPipe()");
		PipeJobInformation jobInfo = null;
		;
		try {
			jobInfo = getJobInfoForPipe();
			log.info("<== JDBCConnection.getCronValueForPipe()");
			return jobInfo.getCron_expression();
		} catch (Exception e) {
			log.error("==> Exception at JDBCConnection.getCronValueForPipe()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}
	
	@Bean
	public String getCronValue() throws TaskStatusServiceException {
		log.info("==> JDBCConnection.getCronValue()");
		JobInformation jobInfo = null;
		;
		try {
			jobInfo = getJobInfoForTask();
			log.info("<== JDBCConnection.getCronValue()");
			return jobInfo.getCron_expression();
		} catch (Exception e) {
			log.error("==> Exception at JDBCConnection.getCronValue()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}
	
	@Bean
	public String getCronValueForAutoDataChecks() throws AutodataCheckServiceException {
		log.info("==> JDBCConnection.getCronValueForAutoDataChecks()");
		AutoChecksJobInformation jobInfo = null;
		;
		try {
			jobInfo = getJobInfoForAutoDataChecks();
			log.info("<== JDBCConnection.getCronValueForAutoDataChecks()");
			return jobInfo.getCron_expression();
		} catch (Exception e) {
			log.error("==> Exception at JDBCConnection.getCronValueForAutoDataChecks()", e);
			throw new AutodataCheckServiceException(e.getMessage(), e.getCause(), null);
		}

	}
	
	@Bean
	public String getCronValueForReconcilation() throws RowStatusServiceException {
		log.info("==> JDBCConnection.getCronValueForReconcilation()");
		ReconcilationJobInformation jobInfo = null;
		;
		try {
			jobInfo = getJobInfoForReconcilation();
			log.info("<== JDBCConnection.getCronValueForReconcilation()");
			return jobInfo.getCron_expression();
		} catch (Exception e) {
			log.error("==> Exception at JDBCConnection.getCronValueForReconcilation()", e);
			throw new RowStatusServiceException(e.getMessage(), e.getCause(), null);
		}

	}

	@Bean
	public String getCronValueForDeleteReport() throws DeleteRequestServiceException {
		log.info("==> JDBCConnection.getCronValueForDeleteReport()");
		DeleteRequestJobInformation jobInfo = null;
		;
		try {
			jobInfo = getJobInfoForDeleteReport();
			log.info("<== JDBCConnection.getCronValueForDeleteReport()");
			return jobInfo.getCron_expression();
		} catch (Exception e) {
			log.error("==> Exception at JDBCConnection.getCronValueForDeleteReport()", e);
			throw new DeleteRequestServiceException(e.getMessage(), e.getCause(), null);
		}

	}
}