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.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.OrderServiceException;
import com.prime.sony.ecommerce.model.ExceptionMessage;
import com.prime.sony.ecommerce.model.JobInformation;
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; 

    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 OrderServiceException {
		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", snowflakeC.getSchema()); // 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 OrderServiceException(e.getMessage(), e.getCause(), null);
		}

	}

	@Bean
	public JobInformation getJobInfo() throws OrderServiceException {
		log.info("==> JDBCConnection.getJobInfo()");
		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 JOB_INFORMATION JI " + 
					"INNER JOIN SCHEDULER_TIME JW ON JI.SCHEDULE_TIME_ID=JW.ID " + 
					"where JOB_ID=4");
			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.getJobInfo()");
			return jobInfo;
		} catch (SQLException e) {
			log.error("==> Exception at JDBCConnection.getJobInfo()", e);
			throw new OrderServiceException(e.getMessage(), e.getCause(), null);
		}

	}

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

	}
	
	
	
	@Bean
	public ExceptionMessage creatExceptionBean() throws OrderServiceException {
		log.info("==> JDBCConnection.creatExceptionBean()");
		ExceptionMessage message = null;
		try {
			message = new ExceptionMessage();
			log.info("==> JDBCConnection.creatExceptionBean()");
			return message;			
		} catch (Exception e) {
			log.error("==> Exception at JDBCConnection.creatExceptionBean()", e);
			throw new OrderServiceException(e.getMessage(), e.getCause(), null);
		}
		
	}

}