package com.ruoyi.system.service.impl;

import java.sql.SQLSyntaxErrorException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import com.alibaba.fastjson2.JSON;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.uuid.IdUtils;
import com.ruoyi.system.entity.CommonEntity;
import com.ruoyi.system.entity.DragTable;
import com.ruoyi.system.entity.DragTableCondition;
import com.ruoyi.system.entity.TableSql;
import com.ruoyi.system.entity.vo.DragTableVo;
import com.ruoyi.system.mapper.CommonMapper;
import com.ruoyi.system.mapper.DragTableConditionMapper;
import com.ruoyi.system.mapper.TableSqlMapper;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.system.mapper.DragTableMapper;
import com.ruoyi.system.service.IDragTableService;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import javax.annotation.Resource;

/**
 * 动态表格Service业务层处理
 *
 * @author ruoyi
 * @date 2023-07-31
 */
@Service
public class DragTableServiceImpl implements IDragTableService {
    @Autowired
    private DragTableMapper dragTableMapper;

    @Resource
    private DragTableConditionMapper dragTableConditionMapper;

    @Resource
    private TableSqlMapper tableSqlMapper;

    @Resource
    private CommonMapper commonMapper;

    @Resource
    private TransactionTemplate transactionTemplate;

    /**
     * 查询动态表格
     *
     * @param tId 动态表格主键
     * @return 动态表格
     */
    @Override
    public DragTable selectDragTableByTId(Long tId) {
        return dragTableMapper.selectDragTableByTId(tId);
    }

    /**
     * 查询动态表格列表
     *
     * @param dragTable 动态表格
     * @return 动态表格
     */
    @Override
    public List<DragTable> selectDragTableList(DragTable dragTable) {
        return dragTableMapper.selectDragTableList(dragTable);
    }

    /**
     * 新增动态表格
     *
     * @param dragTable 动态表格
     * @return 结果
     */
    @Override
    public int insertDragTable(DragTable dragTable) {
        dragTable.setCreateTime(DateUtils.getNowDate());
        return dragTableMapper.insertDragTable(dragTable);
    }

//    /**
//     * 修改动态表格
//     *
//     * @param dragTable 动态表格
//     * @return 结果
//     */
//    @Override
//    public int updateDragTable(DragTable dragTable) {
//        dragTable.setUpdateTime(DateUtils.getNowDate());
//        return dragTableMapper.updateDragTable(dragTable);
//    }

    /**
     * 批量删除动态表格
     *
     * @param tIds 需要删除的动态表格主键
     * @return 结果
     */
    @Override
    public int deleteDragTableByTIds(List<Long> tIds) {
        return dragTableMapper.deleteDragTableByTIds(tIds);
    }

    /**
     * 删除动态表格信息
     *
     * @param tId 动态表格主键
     * @return 结果
     */
    @Override
    public int deleteDragTableByTId(Long tId) {
        return dragTableMapper.deleteDragTableByTId(tId);
    }

    @Override
    public CommonEntity dragTableInfo(String sqlKey) {
        DragTable dragTable = dragTableMapper.selectDragTableBySqlKey(sqlKey);
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("template", dragTable);
//        resultMap.put("where",dragTableConditionMapper.selectDragTableConditionByTid(dragTable.gettId()));
        List<DragTableCondition> dragTableConditions = dragTableConditionMapper.selectDragTableConditionByTid(dragTable.gettId());
        dragTableConditions.forEach(d -> {
            // 0 查询字典 1 查询表数据
            if (d.getConditionDatasource() != null && d.getConditionDatasource().equals("0")) {
                d.setDropDownList(commonMapper.selectDropDownListByDict(d.getConditionTableName()));
            } else if (d.getConditionDatasource() != null && d.getConditionDatasource().equals("1")) {
                d.setDropDownList(commonMapper.selectDropDownList(d.getConditionTableName(), d.getConditionTableFieldKey(), d.getConditionTableFieldName(), new HashMap<>()));
            }
        });
        resultMap.put("where", dragTableConditions);
        resultMap.put("querySql", tableSqlMapper.selectTableSqlByTSqlKey(sqlKey));
        CommonEntity commonEntity = new CommonEntity();
        commonEntity.setResultMap(resultMap);
        return commonEntity;
    }

    // SQL 条件的开始
    public static final String SQL_START = "CONCAT(";

    // SQL 超级查询常量
    public static final String SQL_MIDDLE = "IFNULL( #{VAL}, '' )";

    // sqlserver || DM 数据类型的超级查询常量
    public static final String SQL_DM_SERVER_MIDDLE = "COALESCE( #{VAL}, '' )";

    // SQL 条件的结束
    public static final String SQL_END = "LIKE '%#{val}%'";

    @Transactional
    @Override
    public void addDragTable(DragTableVo dragTableVo) {
        String SQL = "";
        //生成dtNickname表格别名
        String dtNickname = IdUtils.fastSimpleUUID();
        //生成sqlKey唯一标识
//        String sqlKey = IdUtils.fastSimpleUUID();
        //add drag_table
        DragTable dragTable = new DragTable();
        BeanUtils.copyProperties(dragTableVo, dragTable);
        //dragTable.setDtTableName(dtNickname);
        dragTable.setDtColumnName(JSON.toJSONString(dragTableVo.getDtColumnName()));
        dragTable.setPrimaryKey(dragTableVo.getPrimaryKey());
        dragTable.setMenuId(dragTableVo.getMenuId());
        //add table_sql
        TableSql tableSql = new TableSql();
        tableSql.setTableSql(dragTableVo.getTableSql());
        tableSql.setCreateTime(DateUtils.getNowDate());
        dragTableMapper.insertDragTable(dragTable);
        // 拼接sql查询条件
        switch (SecurityUtils.getDatabaseType().toUpperCase()) {
            case "MYSQL":
                SQL += SQL_START;
                for (int i = 0; dragTableVo.getSearchFieldList().size() > i; i++) {
                    SQL += SQL_MIDDLE.replace("#{VAL}", dragTableVo.getSearchFieldList().get(i));
                    SQL += dragTableVo.getSearchFieldList().size() - 1 == i ? ")" : ",\n";
                }
                break;
            case "DM":
            case "SQLSERVER":
                SQL += SQL_START;
                for (int i = 0; dragTableVo.getSearchFieldList().size() > i; i++) {
                    SQL += SQL_DM_SERVER_MIDDLE.replace("#{VAL}", dragTableVo.getSearchFieldList().get(i));
                    SQL += dragTableVo.getSearchFieldList().size() - 1 == i ? ",'')" : ",\n";
                }
                break;
            case "ORACLE":
//                SQL_START = "";
                for (int i = 0; dragTableVo.getSearchFieldList().size() > i; i++) {
                    SQL += dragTableVo.getSearchFieldList().get(i);
                    SQL += (dragTableVo.getSearchFieldList().size() - 1 == i ? " " : "||");
                }
                break;
        }
        String where = dragTableVo.getDtTableName() + ".del_flag = '0' AND ";
        tableSql.setTableCondition(where + SQL + SQL_END);
        tableSql.setTableAlias(dragTableVo.getDtTableName());
        tableSql.setTableExportField(JSON.toJSONString(dragTableVo.getTableExportField()));
        tableSql.setSqlKey(dragTableVo.getSqlKey());
        tableSql.setOrderByColumn(dragTableVo.getOrderByColumn());
        tableSql.setSortOrder(dragTableVo.getSortOrder());
        tableSqlMapper.insertTableSql(tableSql);
        //add drag_table_condition
        if (dragTableVo.getSearchFieldList().size() > 0) {
            List<DragTableCondition> dragTableConditionList = new ArrayList<>();
            dragTableVo.getSearchFieldList().forEach(item -> {
                dragTableConditionList.add(new DragTableCondition(dragTable.gettId(), item));
            });
            dragTableConditionMapper.insertDragTableConditionByList(dragTableConditionList);
        }
    }

    @Override
    public DragTableVo selectDragTableVoByTId(Long tId) {
        DragTableVo vo = dragTableMapper.selectDragTableVoByTId(tId);
        vo.setSearchFieldList(dragTableConditionMapper.selectDragTableSearchField(tId));
        return vo;
    }

    @Transactional
    @Override
    public void updateDragTable(DragTableVo dragTableVo) {
        String SQL = "";
        //update drag_table
        DragTable dragTable = new DragTable();
        BeanUtils.copyProperties(dragTableVo, dragTable);
        dragTable.setDtColumnName(JSON.toJSONString(dragTableVo.getDtColumnName()));
        dragTableMapper.updateDragTable(dragTable);

        //update table_sql
        TableSql tableSql = new TableSql();
        tableSql.setTableSql(dragTableVo.getTableSql());

        // 拼接sql查询条件
        switch (SecurityUtils.getDatabaseType().toUpperCase()) {
            case "MYSQL":
                SQL += SQL_START;
                for (int i = 0; dragTableVo.getSearchFieldList().size() > i; i++) {
                    SQL += SQL_MIDDLE.replace("#{VAL}", dragTableVo.getSearchFieldList().get(i));
                    SQL += dragTableVo.getSearchFieldList().size() - 1 == i ? ")" : ",\n";
                }
                break;
            case "DM":
            case "SQLSERVER":
                SQL += SQL_START;
                for (int i = 0; dragTableVo.getSearchFieldList().size() > i; i++) {
                    SQL += SQL_DM_SERVER_MIDDLE.replace("#{VAL}", dragTableVo.getSearchFieldList().get(i));
                    SQL += dragTableVo.getSearchFieldList().size() - 1 == i ? ",'')" : ",\n";
                }
                break;
            case "ORACLE":
//                SQL_START = "";
                for (int i = 0; dragTableVo.getSearchFieldList().size() > i; i++) {
                    SQL += dragTableVo.getSearchFieldList().get(i);
                    SQL += (dragTableVo.getSearchFieldList().size() - 1 == i ? " " : "||");
                }
                break;
        }
        String where = dragTableVo.getDtTableName() + ".del_flag = '0' AND ";
        tableSql.setTableCondition(where + SQL + SQL_END);
        tableSql.setTableAlias(dragTableVo.getDtTableName());
        tableSql.setSqlKey(dragTableVo.getSqlKey());
        tableSql.setTableExportField(JSON.toJSONString(dragTableVo.getTableExportField()));
        tableSql.setOrderByColumn(dragTableVo.getOrderByColumn());
        tableSql.setSortOrder(dragTableVo.getSortOrder());
        tableSqlMapper.updateTableSqlBySqlKey(tableSql);
        //update drag_table_condition
        if (dragTableVo.getSearchFieldList().size() > 0) {
            //delete
            dragTableConditionMapper.deleteDragTableConditionBytIds(Collections.singletonList(dragTableVo.gettId()));
            //insert
            List<DragTableCondition> dragTableConditionList = new ArrayList<>();
            dragTableVo.getSearchFieldList().forEach(item -> {
                dragTableConditionList.add(new DragTableCondition(dragTableVo.gettId(), item));
            });
            dragTableConditionMapper.insertDragTableConditionByList(dragTableConditionList);
        }
    }

    @Transactional
    @Override
    public void deleteDragTable(List<Long> tIds, List<String> sqlKeys) {
        // delete drag_table
        dragTableMapper.deleteDragTableByTIds(tIds);
        //delete table_sql
        tableSqlMapper.deleteTableSqlBySqlKeys(sqlKeys);
        //delete drag_table_condition
        dragTableConditionMapper.deleteDragTableConditionBytIds(tIds);
    }
}
