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.*;
import com.ruoyi.system.entity.vo.DragTableGroupVo;
import com.ruoyi.system.entity.vo.DragTableVo;
import com.ruoyi.system.mapper.*;
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.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;

    @Resource
    private DragTableStatisticMapper dragTableStatisticMapper;

    @Resource
    private DragTableStyleMapper dragTableStyleMapper;

    @Resource
    private DragTableBtnMapper dragTableBtnMapper;

    @Resource
    private DragTableBtnRelevanceMapper dragTableBtnRelevanceMapper;

    @Resource
    private DragTableBtnRelevanceServiceImpl dragTableBtnRelevanceService;

    /**
     * 查询动态表格
     *
     * @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());
        //dragTable.setCreateById(SecurityUtils.getUserId());
        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 tableKey) {
        //DragTable dragTable = dragTableMapper.selectDragTableBySqlKey(sqlKey);
        //根据tableKey查询动态表格结构数据
        DragTable dragTable = dragTableMapper.selectDragTableByTableKey(tableKey);
        //得到模板数据
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("template", dragTable);
        //resultMap.put("where",dragTableConditionMapper.selectDragTableConditionByTid(dragTable.gettId()));
        //根据表格id查询表格的条件
        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));
        resultMap.put("querySql", tableSqlMapper.selectTableSqlByTSqlKey(dragTable.getSqlKey()));
        // 表格样式信息
        DragTableStyle dragTableStyle = new DragTableStyle();
        dragTableStyle.setTableKey(dragTable.getTableKey());
        List<DragTableStyle> dragTableStyleList = dragTableStyleMapper.selectDragTableStyleList(dragTableStyle);
        resultMap.put("style",dragTableStyleList);
        CommonEntity commonEntity = new CommonEntity();
        // 动态表格按钮信息
        //根据tableKey获取根节点信息
        List<String> btnKeys = dragTableBtnRelevanceMapper.selectBtnKeyByTableKey(dragTable.getTableKey());
        //获取根节点数据
        if(btnKeys.size() > 0){
            List<DragTableBtn> rootNodes = dragTableBtnMapper.selectDragTableBtnListByBtnKey(btnKeys);
            //查询子节点
            List<Long> ids = rootNodes.stream().map(m -> m.getId()).collect(Collectors.toList());
            if (ids.isEmpty()){
                resultMap.put("button",rootNodes);
            }else {
                List<DragTableBtn> childNodes = dragTableBtnMapper.selectChildNodeById(ids);
                List<DragTableBtn> btnList = rootNodes.stream().peek(
                        root -> root.setChildren(getChildrenList(root, childNodes))
                ).collect(Collectors.toList());
                resultMap.put("button",btnList);
            }
        }
        commonEntity.setResultMap(resultMap);
        return commonEntity;
    }

    private List<DragTableBtn> getChildrenList(DragTableBtn root, List<DragTableBtn> childNodes) {
        List<DragTableBtn> list = childNodes.stream().filter(dragTableBtn ->
                //筛选出下一节点元素
                Objects.equals(dragTableBtn.getBtnParentId(), root.getId())).map(dragTableBtn -> {
            //递归set子节点
            dragTableBtn.setChildren(this.getChildrenList(dragTableBtn, childNodes));
            return dragTableBtn;
        }).collect(Collectors.toList());
        return list;
    }

    // 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 = "";
        //add drag_table
        DragTable dragTable = new DragTable();
        BeanUtils.copyProperties(dragTableVo, dragTable);
        dragTable.setDtColumnName(JSON.toJSONString(dragTableVo.getDtColumnName()));
        dragTable.setPrimaryKey(dragTableVo.getPrimaryKey());
        dragTable.setMenuId(dragTableVo.getMenuId());
        dragTable.setDtType("0");//表格类型
        //add table_sql
        TableSql tableSql = new TableSql();
        tableSql.setTableSql(dragTableVo.getTableSql());
        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));
            });
            dragTableConditionList.forEach(e->{
                e.setCreateById(SecurityUtils.getUserId());
            });
            dragTableConditionMapper.insertDragTableConditionByList(dragTableConditionList);
        }
    }

    //表格组添加表格的逻辑
    @Transactional
    @Override
    public void addDragTable(DragTableGroupVo dragTableGroupVo) {
        for (DragTableVo dragTableVo : dragTableGroupVo.getDragTables()) {
            String SQL="";
            //add drag_table
            DragTable dragTable = new DragTable();
            BeanUtils.copyProperties(dragTableVo, dragTable);
            dragTable.setDtColumnName(JSON.toJSONString(dragTableVo.getDtColumnName()));
            dragTable.setPrimaryKey(dragTableVo.getPrimaryKey());
            dragTable.setMenuId(dragTableVo.getMenuId());
            dragTable.setDtName(dragTableVo.getDtTableName());//表格名称
            dragTable.setDtType("1");
            //add table_sql
            TableSql tableSql = new TableSql();
            tableSql.setTableSql(dragTableVo.getTableSql());
            dragTableMapper.insertDragTable(dragTable);
            //拼接sql查询条件
            switch (SecurityUtils.getDatabaseType().toUpperCase()) {
                case "MYSQL":
                    for (int i = 0; dragTableVo.getSearchFieldList().size() > i; i++) {
                            SQL += " AND "+dragTableVo.getSearchFieldList().get(i)+"="+dragTableVo.getConditionDefaultValueMap().get(dragTableVo.getSearchFieldList().get(i));
                    }
                    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'";
            tableSql.setTableCondition(where + SQL);
            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()!=null&&dragTableVo.getSearchFieldList().size() > 0) {
                    List<DragTableCondition> dragTableConditionList = new ArrayList<>();
                    dragTableVo.getSearchFieldList().forEach(item -> {
                        dragTableConditionList.add(new DragTableCondition(dragTable.gettId(), item,dragTableVo.getConditionDefaultValueMap().get(item).toString()));
                    });
                    dragTableConditionMapper.insertDragTableConditionByList(dragTableConditionList);
            }
            //给表格增加按钮
            if(dragTableVo.getDragTableBtnRelevanceList()!=null&&dragTableVo.getDragTableBtnRelevanceList().size()>0){
                dragTableBtnRelevanceService.batchInsertDragTableBtnRelevance(dragTableVo.getDragTableBtnRelevanceList());
            }
        }
    }

    @Override
    public DragTableVo selectDragTableVoByTId(Long tId) {
        DragTableVo vo = dragTableMapper.selectDragTableVoByTId(tId);
        vo.setSearchFieldList(dragTableConditionMapper.selectDragTableSearchField(tId));
        //查询动态表格数据统计
        DragTableStatistic dragTableStatistic = new DragTableStatistic();
        dragTableStatistic.setTableKey(vo.getTableKey());
        vo.setDragTableStatisticList(dragTableStatisticMapper.selectDragTableStatisticList(dragTableStatistic));
        //查询动态表格样式
        DragTableStyle dragTableStyle = new DragTableStyle();
        dragTableStyle.setTableKey(vo.getTableKey());
        vo.setDragTableStyleList(dragTableStyleMapper.selectDragTableStyleList(dragTableStyle));
        //查询动态表格和按钮关联表信息
        DragTableBtnRelevance dragTableBtnRelevance = new DragTableBtnRelevance();
        dragTableBtnRelevance.setTableKey(vo.getTableKey());
        vo.setDragTableBtnRelevanceList(dragTableBtnRelevanceMapper.selectDragTableBtnRelevanceList(dragTableBtnRelevance));
        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(dragTable.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);
    }

    @Override
    public DragTable selectSqlKeyByTableKey(String tableKey) {
        return dragTableMapper.selectDragTableByTableKey(tableKey);
    }
}
