package com.bsmlab.dfx.agent.config.datasource;

import com.bsmlab.dfx.agent.config.AgentConfigDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Component
public class DynamicDataSourceService {
    private Map<String, SqlSessionFactory> sqlSessionFactoryMap = new HashMap<>();
    private Map<String, DataSourceTransactionManager> transactionManagerMap = new HashMap<>();

    public static BasicDataSource createBasicDataSource(AgentConfigDto.DataSourceConfig dataSourceConfig) {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(dataSourceConfig.getDriverClassName());
        dataSource.setUrl(dataSourceConfig.getUrl());
        dataSource.setUsername(dataSourceConfig.getUsername());
        dataSource.setPassword(dataSourceConfig.getPassword());

        if(oracle.jdbc.driver.OracleDriver.class.getCanonicalName().equals(dataSourceConfig.getDriverClassName())) {
            dataSource.setValidationQuery("SELECT 1 FROM DUAL");
        }
        else if(org.postgresql.Driver.class.getCanonicalName().equals(dataSourceConfig.getDriverClassName())) {
            dataSource.setValidationQuery("SELECT 1");
        }
        else if(com.mysql.cj.jdbc.Driver.class.getCanonicalName().equals(dataSourceConfig.getDriverClassName())) {
            dataSource.setValidationQuery("SELECT 1");
        }
        else if(org.mariadb.jdbc.Driver.class.getCanonicalName().equals(dataSourceConfig.getDriverClassName())) {
            dataSource.setValidationQuery("SELECT 1");
        }
        else if(com.ibm.db2.jcc.DB2Driver.class.getCanonicalName().equals(dataSourceConfig.getDriverClassName())) {
            dataSource.setValidationQuery("SELECT 1 FROM SYSIBM.SYSDUMMY1");
        }
        else if(cubrid.jdbc.driver.CUBRIDDriver.class.getCanonicalName().equals(dataSourceConfig.getDriverClassName())) {
            dataSource.setValidationQuery("SELECT 1 FROM DB_ROOT");
            dataSource.setValidationQueryTimeout(Duration.ofMillis(3));
            dataSource.setDurationBetweenEvictionRuns(Duration.ofMillis(60000));
        }
        dataSource.setEvictionPolicyClassName("org.apache.commons.pool2.impl.DefaultEvictionPolicy");
        dataSource.setSoftMinEvictableIdle(Duration.ofSeconds(30));
        dataSource.setMinEvictableIdle(Duration.ofSeconds(60));
        dataSource.setRemoveAbandonedOnBorrow(true);
        dataSource.setRemoveAbandonedOnMaintenance(true);
        dataSource.setTestOnBorrow(true);
        dataSource.setTestOnReturn(true);
        dataSource.setTestOnCreate(true);
        dataSource.setTestWhileIdle(true);
        dataSource.setInitialSize(dataSourceConfig.getInitialSize());
        dataSource.setMinIdle(dataSourceConfig.getMinIdle());
        dataSource.setMaxIdle(dataSourceConfig.getMaxIdle());
        dataSource.setMaxTotal(dataSourceConfig.getMaxTotal());
        return dataSource;
    }

    public SqlSessionFactory getSqlSessionFactory(String dataSourceId) {
        SqlSessionFactory sqlSessionFactory = this.sqlSessionFactoryMap.get(dataSourceId);
        return this.sqlSessionFactoryMap.get(dataSourceId);
    }

    /**
     * @param dataSourceId
     * @param isBatch true인 경우
     * @return
     */
    public SqlSession getSqlSession(String dataSourceId, boolean isBatch) {
        SqlSessionFactory sqlSessionFactory = this.sqlSessionFactoryMap.get(dataSourceId);
        SqlSession sqlSession = isBatch ? sqlSessionFactory.openSession(ExecutorType.BATCH, false) : sqlSessionFactory.openSession();
        Connection connection = sqlSession.getConnection();
        if(!this.isConnected(connection)) {
            sqlSession.close();
            sqlSession = isBatch ? sqlSessionFactory.openSession(ExecutorType.BATCH, false) : sqlSessionFactory.openSession();
            log.info("connection is not valid then create new DB connection");
        }
        return sqlSession;
    }

    public boolean isConnected(Connection connection) {
        boolean connected = false;
        try {
            if(connection.getMetaData().getDriverName().contains("cubrid") || connection.getMetaData().getDriverName().contains("CUBRID")) {
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("SELECT 1 FROM DB_ROOT");
                resultSet.next();
                int result = resultSet.getInt(1);
                if(result == 1) {
                    connected = true;
                }
                else {
                    connected = false;
                }
            }
            else if(connection.getMetaData().getDriverName().toLowerCase().contains("oracle")) {
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("SELECT 1 FROM DUAL");
                resultSet.next();
                int result = resultSet.getInt(1);
                if(result == 1) {
                    connected = true;
                }
                else {
                    connected = false;
                }
            }
            else {
                connected = connection.isValid(1);
            }
        } catch (SQLException e) {
            connected = false;
        }
        return connected;
    }

    public DataSourceTransactionManager getTransactionManager(String dataSourceId) {
        return this.transactionManagerMap.get(dataSourceId);
    }

    public void setSqlSessionFactoryMap(Map<String, SqlSessionFactory> sqlSessionFactoryMap) {
        this.sqlSessionFactoryMap = sqlSessionFactoryMap;
    }

    public void setTransactionManagerMap(Map<String, DataSourceTransactionManager> transactionManagerMap) {
        this.transactionManagerMap = transactionManagerMap;
    }
}
