package com.product.datasource.dao.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.util.ArrayUtil;
import com.product.common.enums.IEnum;
import com.product.core.config.Global;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.spring.context.SpringMVCContextHolder;
import com.product.datasource.config.DataBaseType;
import com.product.datasource.config.ErrorCode;
import com.product.datasource.connection.ConnectionManager;
import com.product.datasource.dao.Dao;
import com.product.datasource.entity.BatchResultEntity;
import com.product.datasource.entity.DataBaseEntity;
import com.product.datasource.entity.UpdateFilterEntity;
import com.product.datasource.utils.BatchUtil;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author cheng
 * @Date 2022/7/6 16:43
 * @Desc MySql 数据操作实现类
 */
public class MysqlDaoImpl implements Dao {

    private DataBaseEntity dataBaseEntity;

    private Connection connection;

    private boolean outLog;

    public MysqlDaoImpl(DataBaseEntity dataBaseEntity) {
        if (!DataBaseType.MYSQL.equals(dataBaseEntity.getDataBaseType())) {
            throw new BaseException(ErrorCode.INIT_DAO_IMPL_TYPE_ERROR);
        }
        this.dataBaseEntity = dataBaseEntity;
        this.outLog = "dev".equals(Global.getSystemConfig("spring.profiles.active", "prod"));
    }

    private void info(String message) {
        if (this.outLog) {
            SpringMVCContextHolder.getSystemLogger().info(message);
        }
    }


    /**
     * 获取数据库类型
     *
     * @return
     * @throws BaseException
     */
    @Override
    public DataBaseType getDataBaseType() throws com.product.core.exception.BaseException {
        return DataBaseType.MYSQL;
    }

    public Connection getConnection() throws com.product.core.exception.BaseException {
        try {
            if (this.connection == null || this.connection.isClosed()) {
                //初始化连接
                this.connection = ConnectionManager.getConnection(dataBaseEntity);
            }
            return this.connection;
        } catch (Exception e) {
            e.printStackTrace();
            throw new BaseException(ErrorCode.GET_CONNECTION_FAIL, e);
        }
    }

    /**
     * 新增单条记录
     *
     * @param fse
     * @throws OracleDaoImpl.BaseException
     */
    @Override
    public void add(FieldSetEntity fse) throws com.product.core.exception.BaseException {
        StringBuilder insertSql = BatchUtil.getInsertSql(fse.getMeta());
        try (PreparedStatement pst = getConnection().prepareStatement(insertSql.toString())) {
            Object[] fields = fse.getFields();
            for (int i = 0; i < fields.length; i++) {
                pst.setObject(i + 1, fse.getObject((String) fields[i]));
            }
            pst.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new BaseException(ErrorCode.ADD_RECORD_FAIL, e);
        }
    }

    /**
     * 查询列表数据
     *
     * @param sql
     * @return
     */
    @Override
    public DataTableEntity getList(String sql) throws com.product.core.exception.BaseException {
        return this.getList(sql, new Object[]{});
    }

    /**
     * 查询列表数据
     *
     * @param sql    sql
     * @param params 参数
     * @return
     */
    @Override
    public DataTableEntity getList(String sql, Object[] params) throws com.product.core.exception.BaseException {
        try (PreparedStatement pst = getConnection().prepareStatement(sql)) {
            info("执行SQL：\n" + sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    info("param" + (i + 1) + ": " + params[i]);
                    pst.setObject(i + 1, params[i]);
                }
            }
            ResultSet resultSet = pst.executeQuery();
            return loaddingDataList(resultSet);
        } catch (Exception e) {
            SpringMVCContextHolder.getSystemLogger().error("EXECUTE_ERROR_SQL:\n" + sql);
            throw new BaseException(ErrorCode.GET_LIST_FAIL, e);
        }
    }

    /**
     * 查询列表数据
     *
     * @param tableName
     * @param filter
     * @return
     */
    @Override
    public DataTableEntity getList(String tableName, String filter) throws com.product.core.exception.BaseException {
        return getList(tableName, filter, new Object[]{});
    }

    /**
     * 查询列表数据
     *
     * @param tableName 表名
     * @param filter    条件
     * @param params    参数
     * @return
     */
    @Override
    public DataTableEntity getList(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
        return this.getList(tableName, filter, null, params);
    }

    /**
     * 查询列表数据
     *
     * @param tableName 表名
     * @param filter    条件
     * @param fields
     * @param params    参数
     * @return
     */
    @Override
    public DataTableEntity getList(String tableName, String filter, String[] fields, Object[] params) throws com.product.core.exception.BaseException {
        StringBuilder sql = new StringBuilder("SELECT ");
        if (ArrayUtil.isEmpty(fields)) {
            sql.append(" * ");
        } else {
            for (int i = 0; i < fields.length; i++) {
                sql.append(fields[i]);
                if (i + 1 < fields.length) {
                    sql.append(",");
                }
            }
        }
        sql.append(" FROM ");
        sql.append(tableName);
        if (!StringUtils.isEmpty(filter)) {
            sql.append(" WHERE (").append(filter).append(" ) ");
        }
        return this.getList(sql.toString(), params);
    }

    /**
     * 查询列表数据
     *
     * @param tableName 表名
     * @param filter    条件
     * @param params    参数
     * @param pageIndex 页数
     * @param pageSize  条数
     * @return
     */
    @Override
    public DataTableEntity getList(String tableName, String filter, Object[] params, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * FROM ").append(tableName);
        if (!StringUtils.isEmpty(filter)) {
            sql.append(" WHERE (").append(filter).append(" )");
        }
        return getList(sql.toString(), params, pageIndex, pageSize);
    }


    @Override
    public DataTableEntity getList(String tableName, String filter, Object[] params, String orderBy, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * FROM ").append(tableName);
        if (!StringUtils.isEmpty(filter)) {
            sql.append(" WHERE (").append(filter).append(" )");
        }
        if (!StringUtils.isEmpty(orderBy)) {
            sql.append(" ORDER BY ").append(orderBy);
        }
        return getList(sql.toString(), params, pageIndex, pageSize);
    }

    @Override
    public DataTableEntity getList(String tableName, String[] fields, String filter, Object[] params, String orderBy, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        if (ArrayUtil.isEmpty(fields)) {
            sql.append("*");
        } else {
            for (int i = 0; i < fields.length; i++) {
                sql.append(fields[i]);
                if (i + 1 < fields.length) {
                    sql.append(",");
                }
            }
        }
        sql.append(" FROM ").append(tableName);
        if (!StringUtils.isEmpty(filter)) {
            sql.append(" WHERE (").append(filter).append(" )");
        }
        if (!StringUtils.isEmpty(orderBy)) {
            sql.append(" ORDER BY ").append(orderBy);
        }
        return null;
    }

    public DataTableEntity getList(String sql, Object[] params, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
        int number = getPageParam(pageIndex, pageSize);
        StringBuilder sb = new StringBuilder(sql);
        sb.append(" LIMIT ?,? ");
        if (params == null) {
            params = new Object[]{number, pageSize};
        } else {
            params = ArrayUtil.append(params, number, pageSize);
        }
        return this.getList(sb.toString(), params);
    }

    private int getPageParam(int pageIndex, int pageSize) {
        return pageIndex <= 1 ? 0 : (pageIndex - 1) * pageSize;
    }

    /**
     * 查询一条数据
     *
     * @param sql
     * @return
     */
    @Override
    public FieldSetEntity getOne(String sql) throws com.product.core.exception.BaseException {
        return this.getOne(sql, new Object[]{});
    }

    /**
     * 查询一条数据
     *
     * @param sql    sql
     * @param params 参数
     * @return
     */
    @Override
    public FieldSetEntity getOne(String sql, Object[] params) throws com.product.core.exception.BaseException {
        DataTableEntity list = this.getList(sql, params);
        if (DataTableEntity.isEmpty(list)) {
            return null;
        }
        return list.getFieldSetEntity(0);
    }

    /**
     * 查询一条数据
     *
     * @param tableName
     * @param filter
     * @return
     */
    @Override
    public FieldSetEntity getOne(String tableName, String filter) throws com.product.core.exception.BaseException {
        return this.getOne(tableName, filter, null);
    }

    /**
     * 查询一条数据
     *
     * @param tableName 表名
     * @param filter    条件
     * @param params    参数
     * @return
     */
    @Override
    public FieldSetEntity getOne(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
        return this.getOne(tableName, filter, null, params);
    }

    /**
     * 查询一条数据
     *
     * @param tableName 表名
     * @param filter    条件
     * @param fields
     * @param params    参数
     * @return
     */
    @Override
    public FieldSetEntity getOne(String tableName, String filter, String[] fields, Object[] params) throws com.product.core.exception.BaseException {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT ");
        if (ArrayUtil.isEmpty(fields)) {
            sb.append(" * ");
        } else {
            for (int i = 0; i < fields.length; i++) {
                sb.append(fields[i]);
                if (i + 1 < fields.length) {
                    sb.append(",");
                }
            }
        }
        sb.append(" FROM ").append(tableName);
        sb.append(" WHERE ");
        if (!StringUtils.isEmpty(filter)) {
            sb.append("  (").append(filter).append(") ");
        }
        sb.append(" limit 1");
        DataTableEntity list = this.getList(sb.toString(), params);
        return DataTableEntity.isEmpty(list) ? null : list.getFieldSetEntity(0);
    }

    /**
     * 批处理添加数据
     *
     * @param data 数据集
     * @return
     */
    @Override
    public BatchResultEntity addBatch(DataTableEntity data) throws com.product.core.exception.BaseException {
        info("开始批量新增数据");
        if (DataTableEntity.isEmpty(data)) {
            info("批量新增数据条数为0");
            return new BatchResultEntity(0);
        }
        TimeInterval timer = DateUtil.timer();
        info("批量新增数据条数为：" + data.getRows());
        BatchResultEntity batchResultEntity = new BatchResultEntity(data.getRows());
//        Connection connection = ConnectionManager.getConnection(dataBaseEntity);
        try {
            StringBuilder insertSql = BatchUtil.getInsertSql(data.getMeta());
            info("SQL:" + insertSql);
            Connection connection = getConnection();
            connection.setAutoCommit(false);

            try (PreparedStatement pst = connection.prepareStatement(insertSql.toString())) {
                Object[] fields = data.getMeta().getFields();
                for (int i = 0; i < data.getRows(); i++) {
                    FieldSetEntity fse = data.getFieldSetEntity(i);
                    for (int j = 1; j <= fields.length; j++) {
                        if (fse.getObject(fields[j - 1].toString()) instanceof oracle.sql.TIMESTAMP) {
                            pst.setObject(j, fse.getObject(fields[j - 1].toString()).toString());
                        } else {
                            pst.setObject(j, fse.getObject(fields[j - 1].toString()));
                        }
                    }
                    pst.addBatch();
                }
                info("开始执行批量提交数据");
                int[] ints = pst.executeBatch();
                connection.commit();
                info("执行批量提交数据完成，共提交 " + data.getRows() + " 条,耗时：" + timer.intervalSecond() + " 秒 ！");
                pst.clearBatch();
            } catch (Exception e) {
                throw e;
            } finally {
                connection.setAutoCommit(true);
            }
        } catch (Exception e) {
            SpringMVCContextHolder.getSystemLogger().error(e);
            throw new BaseException(ErrorCode.ADD_BATCH_ERROR, e);
        }
        return batchResultEntity;
    }

    /**
     * 批处理添加数据
     *
     * @param data                      数据集
     * @param AutomaticallyPrimaryField
     * @return
     */
    @Override
    @Deprecated
    public BatchResultEntity addBatch(DataTableEntity data, String AutomaticallyPrimaryField) throws com.product.core.exception.BaseException {
        return addBatch(data);
    }

    @Override
    public int update(FieldSetEntity fse, UpdateFilterEntity updateFilter) throws com.product.core.exception.BaseException {
        //todo 待实现
        return -1;
    }

    /**
     * 批处理更新数据
     *
     * @param data
     * @param updateFilter 过滤条件
     * @param isCommit     自动提交
     * @return
     */
    @Override
    public BatchResultEntity updateBatch(DataTableEntity data, UpdateFilterEntity updateFilter, boolean isCommit) throws com.product.core.exception.BaseException {
        info("开始批量更新数据");
        if (DataTableEntity.isEmpty(data)) {
            info("批量更新数据条数为0");
            return new BatchResultEntity(0);
        }
        TimeInterval timer = DateUtil.timer();
        info("批量更新数据条数为：" + data.getRows());
        BatchResultEntity batchResultEntity = new BatchResultEntity(data.getRows());
//        Connection connection = ConnectionManager.getConnection(dataBaseEntity);
        try {
            StringBuilder updateSql = BatchUtil.getUpdateSql(data.getMeta(), updateFilter.getFilter());
            info("SQL: " + updateSql);
            Connection connection = getConnection();
            connection.setAutoCommit(false);
            try (PreparedStatement pst = connection.prepareStatement(updateSql.toString())) {
                Object[] fields = data.getMeta().getFields();
                for (int i = 0; i < data.getRows(); i++) {
                    FieldSetEntity fse = data.getFieldSetEntity(i);
                    int j = 1;
                    for (; j <= fields.length; j++) {
                        Object value = fse.getObject(fields[j - 1].toString());
                        pst.setObject(j,value );
                        info("参数： " + (fields[j - 1]) + " = " + value);
                    }
                    j--;
                    Object[] valueFields = updateFilter.getValueFields();
                    for (int i1 = 1; i1 <= valueFields.length; i1++) {
                        Object value = fse.getObject(valueFields[i1 - 1].toString());
                        pst.setObject(j + i1, value);
                        info("参数： " + (valueFields[i1 - 1]) + " = " + value);
                    }
                    pst.addBatch();
                }
                info("开始执行批量提交数据");
                pst.executeBatch();
                info("执行批量提交数据完成，共提交 " + data.getRows() + " 条,耗时：" + timer.intervalSecond() + " 秒 ！");
                pst.clearBatch();
                if (isCommit) {
                    connection.commit();
                }
                batchResultEntity.setConnection(connection);
                return batchResultEntity;
            } catch (Exception e) {
                throw e;
            } finally {
                if (isCommit) {
                    connection.setAutoCommit(true);
                }
//                this.closeConnection();
            }
        } catch (Exception e) {
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error(e);
            throw new BaseException(ErrorCode.UPDATE_BATCH_ERROR, e);
        }
    }

    /**
     * 删除数据
     *
     * @param sql    sql语句
     * @param params 参数
     * @return
     */
    @Override
    public Boolean delete(String sql, Object[] params) throws com.product.core.exception.BaseException {
        return executeSql(sql, params);
    }

    /**
     * 删除数据
     *
     * @param tableName 表名
     * @param filter    条件
     * @return
     */
    @Override
    public Boolean delete(String tableName, String filter) throws com.product.core.exception.BaseException {
        return delete(tableName, filter, null);
    }

    /**
     * 删除数据
     *
     * @param tableName 表名
     * @param filter    条件
     * @param params    参数
     * @return
     */
    @Override
    public Boolean delete(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(tableName);
        if (!StringUtils.isEmpty(filter)) {
            sql.append(" WHERE (").append(filter).append(" ) ");
        }
        return executeSql(sql.toString(), params);
    }

    public int deleteRInt(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(tableName);
        if (!StringUtils.isEmpty(filter)) {
            sql.append(" WHERE (").append(filter).append(" ) ");
        }
        return executeSqlResult(sql.toString(), params);
    }


    /**
     * 执行sql
     *
     * @param sql
     * @return
     */
    @Override
    public boolean executeSql(String sql) throws com.product.core.exception.BaseException {
        return executeSql(sql, null);
    }


    /**
     * 执行sql
     *
     * @param sql
     * @param params
     * @return
     */
    @Override
    public boolean executeSql(String sql, Object[] params) throws com.product.core.exception.BaseException {
        info("执行SQL：\n" + sql);
        try (PreparedStatement pst = getConnection().prepareStatement(sql)) {
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    info("param" + (i + 1) + ": " + params[i]);
                    pst.setObject(i + 1, params[i].toString());
                }
            }
            return pst.execute();
        } catch (Exception e) {
            e.printStackTrace();
            info("ERROR_SQL：\n" + sql);
            throw new BaseException(ErrorCode.EXECUTE_SQL_FAIL, e);
        }
    }

    /**
     * 执行sql
     *
     * @param sql
     * @param params
     * @return
     */
    public int executeSqlResult(String sql, Object[] params) throws com.product.core.exception.BaseException {
        try (PreparedStatement pst = getConnection().prepareStatement(sql)) {
            info("执行SQL：\n" + sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    info("param" + (i + 1) + ": " + params[i]);
                    pst.setObject(i + 1, params[i].toString());
                }
            }
            return pst.executeUpdate();
        } catch (Exception e) {
            throw new BaseException(ErrorCode.EXECUTE_SQL_FAIL, e);
        }
    }

    @Override
    public void closeConnection() {
        try {
            if (this.connection != null && !this.connection.isClosed()) {
                this.connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.connection = null;
        }
    }

    class BaseException extends com.product.core.exception.BaseException {
        public BaseException(IEnum a) {
            super(a);
        }


        public BaseException(IEnum iEnum, Throwable throwable) {
            super(iEnum.getValue(), iEnum.getText() + (throwable.getMessage() != null ? "," + throwable.getMessage() : ""));
        }
    }

    @Override
    protected void finalize() throws Throwable {
        this.closeConnection();
        super.finalize();
    }
}