package com.ruoyi.system.service.impl;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.system.entity.CommonEntity;
import com.ruoyi.system.entity.TableSql;
import com.ruoyi.system.mapper.CommonMapper;
import com.ruoyi.system.mapper.TableSqlMapper;
import com.ruoyi.system.service.ICommonService;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.util.*;

import static com.ruoyi.common.utils.StringUtils.*;


@Service
public class CommonServiceImpl implements ICommonService {

    @Resource
    private CommonMapper commonMapper;

    @Resource
    private TableSqlMapper tableSqlMapper;

    @Override
    public List<CommonEntity> selectList(CommonEntity commonEntity) {
        String tableName = (String) commonEntity.getBasicMap().get("tableName");
        Map<String, Object> conditions = JSONObject.parseObject(JSON.toJSONString(commonEntity.getQueryMap()));
        return commonMapper.selectList(tableName, conditions);
    }

    @Override
    public int batchInsert(CommonEntity commonEntity) {
        String tableName = (String) commonEntity.getBasicMap().get("tableName");
        List<Map<String, String>> list = commonEntity.getAddListMap();
        list.forEach(item -> {  // 批量导入数据给导入数据增加默认值
            item.put("create_time", DateUtils.getTime());
            item.put("create_by", SecurityUtils.getUsername());
            item.put("del_flag", "0");
        });
        List<Map<String, Object>> mapList = new ArrayList<>();
        for (Object obj : list) {
            Map<String, Object> map1 = (Map<String, Object>) obj;
            mapList.add(map1);
        }
        Set<String> fieldNames = new LinkedHashSet<>();
        mapList.stream().forEach(map1 -> {
            map1.forEach((key, value) -> {
                fieldNames.add(toUnderScoreCase(key));
            });
        });
//        fieldNames.add("create_by");
//        fieldNames.add("create_time");
//        Map<String, Object> objectMap = new HashMap<>();
//        objectMap.put("create_by", SecurityUtils.getUsername());
//        objectMap.put("create_time", DateUtils.getTime());
//        mapList.add(objectMap);
        return commonMapper.batchInsert(fieldNames, tableName, mapList);

//        String tableName = (String) commonEntity.getBasicMap().get("tableName");
//        List<Map<String, String>> list = commonEntity.getAddListMap();
//        List<Map<String, Object>> mapList = new ArrayList<>();
//        for (Object obj : list) {
//            Map<String, Object> map1 = (Map<String, Object>) obj;
//            map1.keySet().forEach(item -> {
//                mapList.add((Map<String, Object>) JSON.parse(toUnderScoreCase(item) + map1.get(item)));
//            });
//        }
//        Set<String> fieldNames = new LinkedHashSet<>();
//        mapList.stream().forEach(map1 -> {
//            map1.forEach((key, value) -> {
//                fieldNames.add(key);
//            });
//        });
//        return commonMapper.batchInsert(fieldNames, tableName, mapList);
    }

    @Override
    public int edit(CommonEntity commonEntity) {
        String tableName = (String) commonEntity.getBasicMap().get("tableName");
        Map<String, Object> conditions = JSONObject.parseObject(JSON.toJSONString(commonEntity.getConditionMap()));
        Map<String, Object> fields = JSONObject.parseObject(JSON.toJSONString(commonEntity.getCommMap()));
        Map<String, Object> endfields = new HashMap<>();
        fields.keySet().forEach(item -> {
            endfields.put(toUnderScoreCase(item), fields.get(item));
        });
        endfields.put("update_by", SecurityUtils.getUsername());
        endfields.put("update_time", DateUtils.getTime());
        return commonMapper.edit(endfields, tableName, conditions);
    }

    @Override
    public int batchDelete(CommonEntity commonEntity) {
        String tableName = (String) commonEntity.getBasicMap().get("tableName");
        Map<String, Object> conditions = JSONObject.parseObject(JSON.toJSONString(commonEntity.getConditionMap()));
        return commonMapper.batchDelete(tableName, conditions);
    }

    @Override
    public List<CommonEntity> queryTableList(CommonEntity commonEntity, TableSql tableSql) {
        // 根据sqlkey查询得到当前表单对应的sql
        Map<String, Object> conditions = JSONObject.parseObject(JSON.toJSONString(commonEntity.getQueryMap()));
        // 得到查询条件的值
        String queryCriteriaValue =
                conditions.containsKey("queryCriteriaValue") == true
                        ? conditions.get("queryCriteriaValue").toString() : "";
        // 得到需要执行的sql条件语句
        String endSQL = tableSql.getTableCondition().replace("#{val}", queryCriteriaValue);
        return commonMapper.queryTableList(tableSql.getTableSql() + " where " + endSQL);
        /**
         * 多个文本框查询拼接查询 废弃保留代码
         // sql当中拼接好条件格式 后台使用AND分割 拼接条件
         //  and 。。。。 and 。。。。 and。。。。
         StringBuilder endSQL = new StringBuilder();
         endSQL.append("" + tableSql.getTableSql() + " where 1=1 ");
         String[] whereTerm = tableSql.getTableCondition().split("AND");
         List<String> test = Arrays.asList(whereTerm);
         // 拼接条件
         if (conditions != null) conditions.forEach((key, val) -> {
         String sqlWhere = "";
         try {
         // 把前端回传的查询参数值名称驼峰转下划线命名，进行筛选是否存在匹配项
         sqlWhere = test.stream().filter(item -> item.contains(toUnderScoreCase(key))).findAny().get();
         } catch (NoSuchElementException e) {
         return;
         }
         if (sqlWhere.indexOf("#{timeframe}") != -1 && !val.toString().isEmpty()) {  // 时间范围查询拼接
         endSQL.append(" AND " + sqlWhere.replace("#{timeframe}", "'" + val.toString().split(",")[0] + "'" + " and " + "'" + val.toString().split(",")[1] + "'"));
         } else if (sqlWhere.indexOf("#{val}") != -1 && !val.toString().isEmpty()) { // 寻常条件拼接
         endSQL.append(" AND " + sqlWhere.replace("#{val}", val.toString()));
         }
         });
         return commonMapper.queryTableList(endSQL.toString());
         */
    }

    @Override
    public void export(HttpServletResponse response, CommonEntity commonEntity) throws Exception {
        String sqlKey = commonEntity.getQueryMap().get("sqlkey").toString();
//        String tableName = commonEntity.getQueryMap().get("tableName").toString();
        //查询标题信息
        TableSql tableSql = tableSqlMapper.selectTableSqlByTSqlKey(sqlKey);

        //查询列表信息
        List<Map<String, Object>> mapList = null;
        //根据条件获取列表信息
        if (commonEntity.getExeclMap() != null && !commonEntity.getExeclMap().equals("")) {
            ObjectMapper mapper = new ObjectMapper();
            try {
                mapList = mapper.readValue(commonEntity.getExeclMap(), new TypeReference<List<Map<String, Object>>>() {
                });
            } catch (JsonProcessingException e) {
                throw new RuntimeException(e);
            }
//            mapList = commonEntity.getExeclMap();
        } else {
            List<Map<String, Object>> maps = new ArrayList<>();
            List<CommonEntity> commonEntities = queryTableList(commonEntity, tableSql);
            commonEntities.stream().forEach(c -> maps.add(c.getResultMap()));
            mapList = maps;
        }

        //获取列表标题和字段
        Map<String, String> map = (Map) JSONObject.parseObject(tableSql.getTableExportField());
        // 创建Excel文档
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Sheet1");
        //标题
        List<String> titleList = new ArrayList<>();
        //字段
        List<String> fieldList = new ArrayList<>();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            fieldList.add(entry.getKey().replace("@", "_"));
            titleList.add(entry.getValue() == null ? "" : entry.getValue());
        }
        // 创建表头
        XSSFRow header = sheet.createRow(0);
        for (int q = 0; q < titleList.size(); q++) {
            header.createCell(q).setCellValue(titleList.get(q));
        }
        int rowIndex = 1;
        //获取列表信息
        for (int w = 0; w < mapList.size(); w++) {
            XSSFRow row = sheet.createRow(rowIndex++);
            for (int e = 0; e < fieldList.size(); e++) {
                Object val = mapList.get(w).get(toCamelCase(fieldList.get(e)));
                row.createCell(e).setCellValue((val == null ? "" : val).toString());
            }
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=" + DateUtils.dateTimeNow() + ".xlsx");
        // 将Excel文档写入响应流中
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    @Override
    public void exportTemplate(HttpServletResponse response, String tableName, String sqlKey) throws Exception {
        //查询标题信息
        TableSql tableSql = tableSqlMapper.selectTableSqlByTSqlKey(sqlKey);
        //获取列表标题和字段
        Map<String, String> map = (Map) JSONObject.parseObject(tableSql.getTableExportField());
        // 创建Excel文档
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Sheet1");
        List<String> titleList = new ArrayList<>();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            if (!entry.getKey().contains("@")) {
                titleList.add(entry.getValue() == null ? "" : entry.getValue());
            }
        }
        // 创建表头
        XSSFRow header = sheet.createRow(0);
        for (int q = 0; q < titleList.size(); q++) {
            header.createCell(q).setCellValue(titleList.get(q));
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=" + DateUtils.dateTimeNow() + ".xlsx");
        // 将Excel文档写入响应流中
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    @Override
    public List<CommonEntity> dragTablePreview(CommonEntity commonEntity) {
        String sql = commonEntity.getBasicMap().get("sql").toString();
        return commonMapper.queryTableList(sql);
    }

    public static String extractSubstring(String input, String identifier) {
        int startIndex = input.indexOf(identifier);
        if (startIndex == -1) {
            return null; // 如果找不到指定的标识符，则返回空
        }

        int endIndex = input.indexOf(" ", startIndex);
        if (endIndex == -1) {
            endIndex = input.length(); // 如果找不到空格，则截取到字符串末尾
        }
        String sqlColumn = input.substring(startIndex + identifier.length() + 1, endIndex);
        return convertToCamelCase(sqlColumn);

    }
}
