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

import com.bsmlab.dfx.agent.config.AgentConfigDto;
import com.bsmlab.dfx.agent.config.AgentConfigReader;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RequiredArgsConstructor
@Service
@Slf4j
public class SqlExecuteService {
    private final AgentConfigReader agentConfigReader;
    private final DynamicRoutingDataSource dynamicRoutingDataSource;
    private final DynamicDataSourceService dynamicDataSourceService;

    public List<Map<String, Object>> select(String dataSourceId, String sqlId, Map<String, Object> parameter) {
        dynamicRoutingDataSource.setDataSource(dataSourceId);
        try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
            return sqlSession.selectList(sqlId, parameter);
        }
        finally {
            dynamicRoutingDataSource.clearDataSource();
        }
    }

    public Map<String, Object> insert(String dataSourceId, String sqlId, Map<String, Object> parameter) {
        dynamicRoutingDataSource.setDataSource(dataSourceId);
        try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
            sqlSession.insert(sqlId, parameter);
            return parameter;
        }
        finally {
            dynamicRoutingDataSource.clearDataSource();
        }
    }

    public List<Map<String, Object>> insert(String dataSourceId, String sqlId, List<Map<String, Object>> parameterList) {
        List<Map<String, Object>> resultParameterList = new ArrayList<>();
        dynamicRoutingDataSource.setDataSource(dataSourceId);
        List<AgentConfigDto.DataSourceConfig> dataSourceConfigList = agentConfigReader.getAgentConfigDto().getDataSourceConfig();
        AgentConfigDto.DataSourceConfig dataSourceConfig = null;
        for(AgentConfigDto.DataSourceConfig config : dataSourceConfigList) {
            if(dataSourceId.equals(config.getDataSourceId())) {
                dataSourceConfig = config;
            }
        }
        Map<String, Object> parameter = null;
        int maximumRowForTransaction = dataSourceConfig == null ? 1000 : dataSourceConfig.getMaximumRowForTransaction();
        Connection connection = null;
        if(parameterList.size() > maximumRowForTransaction) {
            try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
                try {
                    connection = sqlSession.getConnection();
                    for (int i = 0; i < parameterList.size(); i++) {
                        parameter = parameterList.get(i);
                        sqlSession.insert(sqlId, parameter);
                        resultParameterList.add(parameter);

                        if((i + 1) % maximumRowForTransaction == 0) { // maximumRowForTransaction 마다 커밋
                            sqlSession.flushStatements();
                            sqlSession.commit();
                        }
                    }
                    sqlSession.flushStatements(); // 남은 부분 커밋
                    sqlSession.commit();
                } catch (RuntimeException e) {
                    log.error("dataSourceId: {}, sqlId: {}, parameters: {}", dataSourceId, sqlId, parameter, e);
                    try {
                        sqlSession.rollback();
                    } catch (RuntimeException ex) {
                        log.warn("Rollback failed", ex);
                    }
                    if (connection != null) {
                        try {
                            connection.close();
                        } catch (SQLException ex) {
                            log.warn("Failed to close connection after error", ex);
                        }
                    }
                    throw e;
                } finally {
                    dynamicRoutingDataSource.clearDataSource();
                }
            }
        }
        else {
            try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
                try {
                    connection = sqlSession.getConnection();
                    for(int i = 0; i < parameterList.size(); i++) {
                        parameter = parameterList.get(i);
                        sqlSession.insert(sqlId, parameter);
                        resultParameterList.add(parameter);
                    }
                    sqlSession.flushStatements();
                    sqlSession.commit();
                    return resultParameterList;
                }
                catch(RuntimeException e) {
                    log.error("dataSourceId: {}, sqlId: {}, parameters: {}", dataSourceId, sqlId, parameter, e);
                    try {
                        sqlSession.rollback();
                    }
                    catch (RuntimeException ex) {
                        log.warn("Rollback failed", ex);
                    }
                    if(connection != null) {
                        try {
                            connection.close();
                        }
                        catch (SQLException ex) {
                            log.warn("Failed to close connection after error", ex);
                        }
                    }
                    throw e;
                }
                finally {
                    dynamicRoutingDataSource.clearDataSource();
                }
            }
        }
        return resultParameterList;
    }

    public int update(String dataSourceId, String sqlId, Map<String, Object> parameter) {
        dynamicRoutingDataSource.setDataSource(dataSourceId);
        try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
            return sqlSession.update(sqlId, parameter);
        }
        finally {
            dynamicRoutingDataSource.clearDataSource();
        }
    }

    public List<Map<String, Object>> update(String dataSourceId, String sqlId, List<Map<String, Object>> parameterList) {
        int result = 0;
        List<Map<String, Object>> resultParameterList = new ArrayList<>();
        dynamicRoutingDataSource.setDataSource(dataSourceId);
        List<AgentConfigDto.DataSourceConfig> dataSourceConfigList = agentConfigReader.getAgentConfigDto().getDataSourceConfig();
        AgentConfigDto.DataSourceConfig dataSourceConfig = null;
        for(AgentConfigDto.DataSourceConfig config : dataSourceConfigList) {
            if(dataSourceId.equals(config.getDataSourceId())) {
                dataSourceConfig = config;
            }
        }
        Map<String, Object> parameter = null;
        int maximumRowForTransaction = dataSourceConfig == null ? 1000 : dataSourceConfig.getMaximumRowForTransaction();
        Connection connection = null;
        if(parameterList.size() > maximumRowForTransaction) {
            try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
                try {
                    connection = sqlSession.getConnection();
                    for (int i = 0; i < parameterList.size(); i++) {
                        parameter = parameterList.get(i);
                        result += sqlSession.update(sqlId, parameter);
                        resultParameterList.add(parameter);

                        if((i + 1) % maximumRowForTransaction == 0) { // maximumRowForTransaction 마다 커밋
                            sqlSession.flushStatements();
                            sqlSession.commit();
                        }
                    }
                    sqlSession.flushStatements(); // 남은 부분 커밋
                    sqlSession.commit();
                } catch (RuntimeException e) {
                    log.error("dataSourceId: {}, sqlId: {}, parameters: {}", dataSourceId, sqlId, parameter, e);
                    try {
                        sqlSession.rollback();
                    } catch (RuntimeException ex) {
                        log.warn("Rollback failed", ex);
                    }
                    if (connection != null) {
                        try {
                            connection.close();
                        } catch (SQLException ex) {
                            log.warn("Failed to close connection after error", ex);
                        }
                    }
                    throw e;
                } finally {
                    dynamicRoutingDataSource.clearDataSource();
                }
            }
        }
        else {
            try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
                try {
                    connection = sqlSession.getConnection();
                    for(int i = 0; i < parameterList.size(); i++) {
                        parameter = parameterList.get(i);
                        result += sqlSession.update(sqlId, parameter);
                        resultParameterList.add(parameter);
                    }
                    sqlSession.flushStatements();
                    sqlSession.commit();
                }
                catch(RuntimeException e) {
                    log.error("dataSourceId: {}, sqlId: {}, parameters: {}", dataSourceId, sqlId, parameter, e);
                    try {
                        sqlSession.rollback();
                    }
                    catch (RuntimeException ex) {
                        log.warn("Rollback failed", ex);
                    }
                    if(connection != null) {
                        try {
                            connection.close();
                        }
                        catch (SQLException ex) {
                            log.warn("Failed to close connection after error", ex);
                        }
                    }
                    throw e;
                }
                finally {
                    dynamicRoutingDataSource.clearDataSource();
                }
            }
        }
        log.info("dataSourceId: {}, sqlId: {}, update count: {}", dataSourceId, sqlId, result);
        return resultParameterList;
    }

    public int delete(String dataSourceId, String sqlId, Map<String, Object> parameter) {
        dynamicRoutingDataSource.setDataSource(dataSourceId);
        try(SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false)) {
            return sqlSession.delete(sqlId, parameter);
        }
        finally {
            dynamicRoutingDataSource.clearDataSource();
        }
    }

    public void execute(String dataSourceId, String sqlId, Map<String, Object> parameter) {
        dynamicRoutingDataSource.setDataSource(dataSourceId);
        try {
            SqlSession sqlSession = dynamicDataSourceService.getSqlSession(dataSourceId, false);
            sqlSession.selectOne(sqlId, parameter);
        }
        finally {
            dynamicRoutingDataSource.clearDataSource();
        }
    }
}
