package com.stdproject.service.impl;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.stdproject.common.BusinessException;
import com.stdproject.entity.CoreDatasource;
import com.stdproject.mapper.CoreDatasourceMapper;
import com.stdproject.service.IDynamicDataService;
import io.gisbi.extensions.datasource.dto.DatasourceRequest;
import io.gisbi.extensions.datasource.dto.DatasourceSchemaDTO;
import io.gisbi.extensions.datasource.dto.TableField;
import io.gisbi.extensions.datasource.factory.ProviderFactory;
import io.gisbi.extensions.datasource.provider.Provider;
import io.gisbi.utils.BeanUtils;
import io.gisbi.utils.IDUtils;
import io.gisbi.utils.JsonUtil;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@Service
public class DynamicDataServiceImpl implements IDynamicDataService {
    @Resource
    private CoreDatasourceMapper coreDatasourceMapper;
    @Override
    public boolean addTableData(Long datasourceId, String tableData) throws Exception {
        // 根据数据源 id 查询数据源信息，调用通用数据源执行器
        CoreDatasource coreDatasource = coreDatasourceMapper.selectById(datasourceId);
        if (coreDatasource == null) {
            BusinessException.throwException("数据源不存在");
        }
        // 解析 tableData JSON 字符串  "{ \"tableName\": \"user\", \"data\": [ { \"fieldName\": \"id\", \"fieldType\": \"varchar\", \"IsPrimaryKey\": true, \"fieldValue\": \"0001\" }, { \"fieldName\": \"name\", \"fieldType\": \"varchar\", \"fieldValue\": \"张三\" } ] }";
        Map<String, Object> dataMap = JsonUtil.parseObject(tableData, Map.class);
        String tableName = (String) dataMap.get("tableName");
        List<Map<String, Object>> fieldList = (List<Map<String, Object>>) dataMap.get("data");

        if (fieldList == null || fieldList.isEmpty()) {
            BusinessException.throwException("没有可插入的数据字段");
        }

        // 构建插入语句
        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();

        for (int i = 0; i < fieldList.size(); i++) {
            Map<String, Object> field = fieldList.get(i);
            String fieldName = (String) field.get("fieldName");
            Object fieldValue = field.get("fieldValue");
            boolean isPrimaryKey = field.get("IsPrimaryKey") != null && (boolean) field.get("IsPrimaryKey");
            if (isPrimaryKey) {
                if (fieldValue == null) {fieldValue= IDUtils.snowID();}
            }
            if (i > 0) {
                columns.append(", ");
                values.append(", ");
            }
            columns.append(fieldName);
            if (fieldValue instanceof String) {
                values.append("'").append(fieldValue).append("'");
            } else {
                values.append(fieldValue);
            }
        }

        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns.toString(), values.toString());

        // 调用执行器，向数据表中插入 tableData 数据
        DatasourceSchemaDTO datasourceSchemaDTO = new DatasourceSchemaDTO();
        BeanUtils.copyBean(datasourceSchemaDTO, coreDatasource);

        Provider provider = ProviderFactory.getProvider(coreDatasource.getType());
        DatasourceRequest datasourceRequest = new DatasourceRequest();
        datasourceRequest.setQuery(sql);
        datasourceRequest.setDsList(Map.of(datasourceSchemaDTO.getId(), datasourceSchemaDTO));

        log.debug("执行插入数据的SQL: {}", sql);

        // 执行插入操作
        int result= provider.executeUpdate(datasourceRequest);
        if (result==1) {
            return true;
            // process result set
        } else {
            return false;
        }
    }
    @Override
    public Map<String, Object> getTableDataByPk(Long datasourceId, String condtion) throws Exception {
        // 获取数据源信息
        CoreDatasource coreDatasource = coreDatasourceMapper.selectById(datasourceId);
        if (coreDatasource == null) {
            BusinessException.throwException("数据源不存在");
        }
        // 解析 JSON 数据
        //condtion={ \"tableName\": \"user\", key:[{ \"fieldName\": \"id\",\"fieldValue\": \"0001\"]
        Map<String, Object> dataMap = JsonUtil.parseObject(condtion, Map.class);
        String tableName = (String) dataMap.get("tableName");
        // 参数校验
        if (StringUtils.isBlank(tableName)) {
            BusinessException.throwException("表名不能为空");
        }
        List<Map<String, Object>> keyFields = (List<Map<String, Object>>) dataMap.get("key");
        if (CollectionUtils.isEmpty(keyFields)) {
            BusinessException.throwException("主键字段或值不能为空");
        }

        String whereClause = buildWhereCondition(keyFields);
        String sql = String.format("SELECT * FROM %s WHERE %s", tableName, whereClause);
        // 执行查询操作
        DatasourceSchemaDTO datasourceSchemaDTO = new DatasourceSchemaDTO();
        BeanUtils.copyBean(datasourceSchemaDTO, coreDatasource);

        Provider provider = ProviderFactory.getProvider(coreDatasource.getType());
        DatasourceRequest datasourceRequest = new DatasourceRequest();
        datasourceRequest.setQuery(sql);
        datasourceRequest.setDsList(Map.of(datasourceSchemaDTO.getId(), datasourceSchemaDTO));

        log.debug("执行查询数据的SQL: {}", sql);

        // 获取查询结果
        Map<String, Object> data = provider.fetchResultField(datasourceRequest);

        // 处理查询结果
        List<String[]> dataList = (List<String[]>) data.get("data");
        List<TableField> fields = (List<TableField>) data.get("fields");

        if (CollectionUtils.isEmpty(dataList) || dataList.size() == 0) {
            return null;
        }

        // 将结果转换为 Map 格式
        String[] row = dataList.get(0);
        Map<String, Object> resultMap = new LinkedHashMap<>();
        for (int i = 0; i < fields.size(); i++) {
            TableField field = fields.get(i);
            String fieldName = field.getOriginName();
            resultMap.put(fieldName, row[i]);
        }
        return resultMap;
    }
    /**
     * 构建 WHERE 条件字符串，用于多字段主键查询/更新/删除操作
     *
     * @param keyFields 主键字段列表，格式如：{ "fieldName": "id", "fieldValue": "0001" }
     * @return SQL WHERE 子句字符串
     */
    private String buildWhereCondition(List<Map<String, Object>> keyFields) {
        StringBuilder whereClause = new StringBuilder();

        for (int i = 0; i < keyFields.size(); i++) {
            Map<String, Object> keyField = keyFields.get(i);
            String fieldName = (String) keyField.get("fieldName");
            Object fieldValue = keyField.get("fieldValue");

            if (i > 0) {
                whereClause.append(" AND ");
            }

            if (fieldValue == null) {
                whereClause.append(fieldName).append(" IS NULL");
            } else if (fieldValue instanceof String) {
                whereClause.append(String.format("%s = '%s'", fieldName, fieldValue));
            } else {
                whereClause.append(String.format("%s = %s", fieldName, fieldValue));
            }
        }

        return whereClause.toString();
    }
    @Override
    public boolean updateTableData(Long datasourceId, String tableData) throws Exception {
        // 获取数据源信息
        CoreDatasource coreDatasource = coreDatasourceMapper.selectById(datasourceId);
        if (coreDatasource == null) {
            BusinessException.throwException("数据源不存在");
        }
        //String tableDataJson = "{ \"tableName\": \"user\", key:[{ \"fieldName\": \"id\",\"fieldValue\": \"0001\"], \"data\": [ { \"fieldName\": \"name\", \"fieldType\": \"varchar\", \"fieldValue\": \"李四\" } ] }";
        // 解析 JSON 数据
        Map<String, Object> dataMap = JsonUtil.parseObject(tableData, Map.class);
        String tableName = (String) dataMap.get("tableName");
        if (StringUtils.isBlank(tableName)) {
            BusinessException.throwException("表名不能为空");
        }
        List<Map<String, Object>> keyFields = (List<Map<String, Object>>) dataMap.get("key");
        if (CollectionUtils.isEmpty(keyFields)) {
            BusinessException.throwException("主键字段或值不能为空");
        }
        List<Map<String, Object>> fieldList = (List<Map<String, Object>>) dataMap.get("data");
        if (fieldList == null || fieldList.isEmpty()) {
            BusinessException.throwException("没有可更新的数据字段");
        }
        // 构建 UPDATE 语句
        StringBuilder setClause = new StringBuilder();
        for (int i = 0; i < fieldList.size(); i++) {
            Map<String, Object> field = fieldList.get(i);
            String fieldName = (String) field.get("fieldName");
            Object fieldValue = field.get("fieldValue");

            if (i > 0) {
                setClause.append(", ");
            }
            if (fieldValue instanceof String) {
                setClause.append(String.format("%s = '%s'", fieldName, fieldValue));
            } else {
                setClause.append(String.format("%s = %s", fieldName, fieldValue));
            }
        }

        String whereClause = buildWhereCondition(keyFields);
        String sql = String.format("UPDATE %s SET %s WHERE %s", tableName, setClause, whereClause);
        // 调用执行器执行 SQL
        DatasourceSchemaDTO datasourceSchemaDTO = new DatasourceSchemaDTO();
        BeanUtils.copyBean(datasourceSchemaDTO, coreDatasource);

        Provider provider = ProviderFactory.getProvider(coreDatasource.getType());
        DatasourceRequest datasourceRequest = new DatasourceRequest();
        datasourceRequest.setQuery(sql);
        datasourceRequest.setDsList(Map.of(datasourceSchemaDTO.getId(), datasourceSchemaDTO));

        log.debug("执行更新数据的SQL: {}", sql);

        // 执行更新操作
        int result= provider.executeUpdate(datasourceRequest);
        if (result==1) {
            return true;
            // process result set
        } else {
            return false;
        }
    }
    @Override
    public boolean deleteTableData(Long datasourceId, String condtion) throws Exception {
        // 获取数据源信息
        CoreDatasource coreDatasource = coreDatasourceMapper.selectById(datasourceId);
        if (coreDatasource == null) {
            BusinessException.throwException("数据源不存在");        }

        // 解析 JSON 数据
        //String tableDataJson = "{ \"tableName\": \"user\",  key:[{ \"fieldName\": \"id\",\"fieldValue\": \"0001\"] }";
        Map<String, Object> dataMap = JsonUtil.parseObject(condtion, Map.class);
        String tableName = (String) dataMap.get("tableName");
        if (StringUtils.isBlank(tableName)) {
            BusinessException.throwException("表名不能为空");
        }
        List<Map<String, Object>> keyFields = (List<Map<String, Object>>) dataMap.get("key");
        if (CollectionUtils.isEmpty(keyFields)) {
            BusinessException.throwException("主键字段或值不能为空");
        }
        String whereClause = buildWhereCondition(keyFields);
        String sql = String.format("DELETE FROM %s WHERE %s", tableName, whereClause);
        // 调用执行器执行 SQL
        DatasourceSchemaDTO datasourceSchemaDTO = new DatasourceSchemaDTO();
        BeanUtils.copyBean(datasourceSchemaDTO, coreDatasource);

        Provider provider = ProviderFactory.getProvider(coreDatasource.getType());
        DatasourceRequest datasourceRequest = new DatasourceRequest();
        datasourceRequest.setQuery(sql);
        datasourceRequest.setDsList(Map.of(datasourceSchemaDTO.getId(), datasourceSchemaDTO));

        log.debug("执行删除数据的SQL: {}", sql);

        // 执行删除操作
        int result= provider.executeUpdate(datasourceRequest);
        if (result==1) {
            return true;
            // process result set
        } else {
            return false;
        }
    }
    @Override
    public Page<Map<String, Object>> queryTableDataPaged(Long datasourceId, String condition) throws Exception {
        // 获取数据源信息
        CoreDatasource coreDatasource = coreDatasourceMapper.selectById(datasourceId);
        if (coreDatasource == null) {
            BusinessException.throwException("数据源不存在");
        }

        // 解析 condition JSON 数据
        Map<String, Object> dataMap = JsonUtil.parseObject(condition, Map.class);
        String tableName = (String) dataMap.get("tableName");
        List<Map<String, Object>> conditionList = (List<Map<String, Object>>) dataMap.get("conditions");

        Integer pageNum = (Integer) dataMap.getOrDefault("pageNum", 1);
        Integer pageSize = (Integer) dataMap.getOrDefault("pageSize", 10);

        if (StringUtils.isBlank(tableName)) {
            BusinessException.throwException("表名不能为空");
        }

        // 构建 WHERE 条件子句
        StringBuilder whereClause = new StringBuilder();
        if (conditionList != null && !conditionList.isEmpty()) {
            whereClause.append(" WHERE ");
            for (int i = 0; i < conditionList.size(); i++) {
                Map<String, Object> cond = conditionList.get(i);
                String field = (String) cond.get("field");
                String operator = ((String) cond.get("operator")).toLowerCase();
                Object value = cond.get("value");

                if (i > 0) {
                    whereClause.append(" AND ");
                }

                switch (operator) {
                    case "like":
                        whereClause.append(String.format("%s LIKE '%%%s%%'", field, value));
                        break;
                    case "=":
                        appendValue(whereClause, field, value, "=");
                        break;
                    case "<":
                        appendValue(whereClause, field, value, "<");
                        break;
                    case ">":
                        appendValue(whereClause, field, value, ">");
                        break;
                    case "<=":
                        appendValue(whereClause, field, value, "<=");
                        break;
                    case ">=":
                        appendValue(whereClause, field, value, ">=");
                        break;
                    case "!=":
                    case "<>":
                        appendValue(whereClause, field, value, "<>");
                        break;
                    case "in":
                        if (!(value instanceof List<?>)) {
                            BusinessException.throwException("IN 操作符要求值为列表类型");
                        }
                        List<?> values = (List<?>) value;
                        String inValues = values.stream()
                                .map(v -> v instanceof String ? "'" + v + "'" : v.toString())
                                .collect(Collectors.joining(", "));
                        whereClause.append(String.format("%s IN (%s)", field, inValues));
                        break;
                    default:
                        BusinessException.throwException("不支持的操作符: " + operator);
                }
            }
        }

        // 构建基础 SQL
        String baseSql = String.format("SELECT * FROM %s%s", tableName, whereClause);
        String dbType = coreDatasource.getType().toLowerCase();
        String pagedSql = buildPagedSQL(baseSql, dbType, pageNum, pageSize);
        String countSql = String.format("SELECT COUNT(*) FROM %s%s", tableName, whereClause);

        DatasourceSchemaDTO schemaDTO = new DatasourceSchemaDTO();
        BeanUtils.copyBean(schemaDTO, coreDatasource);
        Provider provider = ProviderFactory.getProvider(coreDatasource.getType());
        DatasourceRequest request = new DatasourceRequest();
        request.setDsList(Map.of(schemaDTO.getId(), schemaDTO));

        // 查询分页数据
        request.setQuery(pagedSql);
        Map<String, Object> result = provider.fetchResultField(request);
        List<String[]> dataList = (List<String[]>) result.get("data");
        List<TableField> fields = (List<TableField>) result.get("fields");

        // 查询总记录数
        request.setQuery(countSql);
        Map<String, Object> countResult = provider.fetchResultField(request);
        long total = Long.parseLong(((List<String[]>) countResult.get("data")).get(0)[0]);

        // 将数据封装为 Map 形式
        List<Map<String, Object>> records = new ArrayList<>();
        if (!CollectionUtils.isEmpty(dataList)) {
            for (String[] row : dataList) {
                Map<String, Object> rowMap = new LinkedHashMap<>();
                for (int i = 0; i < fields.size() && i < row.length; i++) {
                    String fieldName = fields.get(i).getOriginName();
                    String fieldValue = row[i];
                    rowMap.put(fieldName, fieldValue);
                }
                records.add(rowMap);
            }
        }

        // 返回分页结果
        Page<Map<String, Object>> page = new Page<>();
        page.setCurrent(pageNum);
        page.setSize(pageSize);
        page.setTotal(total);
        page.setRecords(records);

        return page;
    }


    private void appendValue(StringBuilder sb, String field, Object value, String op) {
        if (value instanceof String) {
            sb.append(String.format("%s %s '%s'", field, op, value));
        } else {
            sb.append(String.format("%s %s %s", field, op, value));
        }
    }

    private String buildPagedSQL(String baseSql, String dbType, int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;

        switch (dbType) {
            case "mysql":
            case "mariadb":
                return String.format("%s LIMIT %d OFFSET %d", baseSql, pageSize, offset);
            case "postgresql":
                return String.format("%s LIMIT %d OFFSET %d", baseSql, pageSize, offset);
            case "oracle":
                int start = offset + 1;
                int end = offset + pageSize;
                return String.format("SELECT * FROM (SELECT ROWNUM rn, t.* FROM (%s) t WHERE ROWNUM <= %d) WHERE rn >= %d", baseSql, end, start);
            case "sqlserver":
                return String.format("%s OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", baseSql, offset, pageSize);
            default:
                // 默认使用 MySQL 方式
                return String.format("%s LIMIT %d OFFSET %d", baseSql, pageSize, offset);
        }
    }
}
