package com.ruoyi.web.report.service.impl;

import com.alibaba.fastjson2.JSON;
import com.ruoyi.web.report.domain.ErpReportDefinition;
import com.ruoyi.web.report.domain.bo.ErpReportQueryBo;
import com.ruoyi.web.report.domain.meta.ReportFieldMeta;
import com.ruoyi.web.report.domain.meta.ReportJoinMeta;
import com.ruoyi.web.report.domain.vo.ErpReportQueryVo;
import com.ruoyi.web.report.mapper.ErpReportDefinitionMapper;
import com.ruoyi.web.report.service.IErpReportRuntimeService;
import com.ruoyi.web.report.service.support.ErpReportRuntimeGuard;
import com.ruoyi.web.report.service.support.ReportSqlSecurityValidator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.StringJoiner;

/**
 * 报表运行态服务实现。
 *
 * <p>本实现已接入真实查询能力：
 * 1) CUSTOM_SQL 模式：执行审核通过的自定义 SQL；
 * 2) LOW_CODE 模式：根据字段/关联元数据动态拼装 SQL；
 * 3) 统一支持分页、总数统计、运行时过滤与排序。</p>
 */
@Service
public class ErpReportRuntimeServiceImpl implements IErpReportRuntimeService {
    /** 允许的聚合函数。 */
    private static final Set<String> SAFE_AGG_FN = new HashSet<>(Arrays.asList("SUM", "AVG", "MAX", "MIN", "COUNT"));
    /** 允许的关联类型。 */
    private static final Set<String> SAFE_JOIN_TYPE = new HashSet<>(Arrays.asList("INNER", "LEFT", "RIGHT"));
    /** 允许的排序方向。 */
    private static final Set<String> SAFE_SORT_DIRECTION = new HashSet<>(Arrays.asList("ASC", "DESC"));

    @Autowired
    private ErpReportDefinitionMapper erpReportDefinitionMapper;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    private ErpReportRuntimeGuard runtimeGuard;

    @Autowired
    private ReportSqlSecurityValidator sqlSecurityValidator;

    @Override
    public ErpReportQueryVo preview(Long reportId, ErpReportQueryBo queryBo) {
        return runQuery(reportId, queryBo);
    }

    @Override
    public ErpReportQueryVo query(Long reportId, ErpReportQueryBo queryBo) {
        return runQuery(reportId, queryBo);
    }

    /**
     * 统一执行入口：
     * - 参数规范化
     * - 报表定义查询
     * - 根据查询模式分发执行
     */
    private ErpReportQueryVo runQuery(Long reportId, ErpReportQueryBo queryBo) {
        queryBo = runtimeGuard.normalize(queryBo);
        ErpReportDefinition definition = erpReportDefinitionMapper.selectErpReportDefinitionById(reportId);
        if (definition == null) {
            throw new IllegalArgumentException("报表不存在或已删除，reportId=" + reportId);
        }
        // 离线模式优先读取报表专属聚合表。
        if ("OFFLINE".equals(safeUpper(definition.getAggregateMode()))) {
            return executeOfflineSnapshotQuery(reportId, queryBo, definition);
        }
        if ("CUSTOM_SQL".equals(safeUpper(definition.getQueryMode()))) {
            return executeCustomSql(reportId, queryBo, definition);
        }
        return executeLowCodeSql(reportId, queryBo, definition);
    }

    /**
     * 执行离线聚合表查询（每个报表一张聚合表）。
     */
    private ErpReportQueryVo executeOfflineSnapshotQuery(Long reportId, ErpReportQueryBo queryBo, ErpReportDefinition definition) {
        String tableName = aggregateTableName(reportId);
        if (!aggregateTableExists(tableName)) {
            // 聚合表不存在时返回空结果，避免接口报错阻塞页面。
            return buildResult(reportId, definition, Collections.<Map<String, Object>>emptyList(), 0L, "select * from " + tableName);
        }
        int limit = queryBo.getPageSize();
        int offset = (queryBo.getPageNum() - 1) * queryBo.getPageSize();
        String dataSql = "select stat_bucket, dimension_json, metric_json from " + tableName +
                " order by stat_bucket desc limit :limit offset :offset";
        String countSql = "select count(1) from " + tableName;
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("limit", limit);
        params.addValue("offset", offset);

        List<Map<String, Object>> rawRows = namedParameterJdbcTemplate.queryForList(dataSql, params);
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        for (Map<String, Object> raw : rawRows) {
            Map<String, Object> row = new HashMap<String, Object>();
            row.put("stat_bucket", raw.get("stat_bucket"));
            row.putAll(parseJsonToMap(raw.get("dimension_json")));
            row.putAll(parseJsonToMap(raw.get("metric_json")));
            rows.add(row);
        }
        Long total = namedParameterJdbcTemplate.queryForObject(countSql, params, Long.class);
        return buildResult(reportId, definition, rows, total, dataSql);
    }

    /**
     * 执行自定义 SQL 查询。
     */
    private ErpReportQueryVo executeCustomSql(Long reportId, ErpReportQueryBo queryBo, ErpReportDefinition definition) {
        String customSql = erpReportDefinitionMapper.selectApprovedCustomSqlByReportId(reportId);
        if (customSql == null || customSql.trim().isEmpty()) {
            throw new IllegalArgumentException("自定义SQL模式下未找到审核通过的 SQL。");
        }
        sqlSecurityValidator.validateCustomSelectSql(customSql);

        int limit = queryBo.getPageSize();
        int offset = (queryBo.getPageNum() - 1) * queryBo.getPageSize();
        String pageSql = "select * from (" + customSql + ") t limit :limit offset :offset";
        String countSql = "select count(1) from (" + customSql + ") t";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("limit", limit);
        params.addValue("offset", offset);

        List<Map<String, Object>> rows = namedParameterJdbcTemplate.queryForList(pageSql, params);
        Long total = namedParameterJdbcTemplate.queryForObject(countSql, params, Long.class);
        return buildResult(reportId, definition, rows, total, customSql.trim());
    }

    /**
     * 执行低代码查询。
     */
    private ErpReportQueryVo executeLowCodeSql(Long reportId, ErpReportQueryBo queryBo, ErpReportDefinition definition) {
        List<ReportFieldMeta> fieldMetaList = erpReportDefinitionMapper.selectFieldMetaByReportId(reportId);
        if (fieldMetaList == null || fieldMetaList.isEmpty()) {
            throw new IllegalArgumentException("低代码模式下未配置字段元数据。");
        }
        List<ReportJoinMeta> joinMetaList = erpReportDefinitionMapper.selectJoinMetaByReportId(reportId);

        Map<String, String> fieldExprMap = new HashMap<>();
        List<String> selectItems = new ArrayList<>();
        List<String> groupByItems = new ArrayList<>();
        for (ReportFieldMeta meta : fieldMetaList) {
            if (meta == null || meta.getIsEnabled() == null || meta.getIsEnabled() != 1) {
                continue;
            }
            String fieldKey = meta.getFieldKey();
            sqlSecurityValidator.validateIdentifier(fieldKey);
            String sourceExpr = resolveSourceExpr(meta);
            fieldExprMap.put(fieldKey, sourceExpr);
            String aggFn = safeUpper(meta.getAggregateFn());
            if (!aggFn.isEmpty()) {
                if (!SAFE_AGG_FN.contains(aggFn)) {
                    throw new IllegalArgumentException("不支持的聚合函数: " + aggFn);
                }
                selectItems.add(aggFn + "(" + sourceExpr + ") AS " + fieldKey);
            } else {
                selectItems.add(sourceExpr + " AS " + fieldKey);
                if (Objects.equals(meta.getIsGroupDim(), 1)) {
                    groupByItems.add(sourceExpr);
                }
            }
        }
        if (selectItems.isEmpty()) {
            throw new IllegalArgumentException("低代码模式下无可用查询字段。");
        }

        String fromSql = buildFromAndJoinSql(fieldMetaList, joinMetaList);
        StringBuilder baseSql = new StringBuilder();
        baseSql.append("select ").append(String.join(", ", selectItems)).append(" ")
                .append(fromSql).append(" where 1=1 ");

        MapSqlParameterSource params = new MapSqlParameterSource();
        appendRuntimeFilters(queryBo, fieldExprMap, baseSql, params);
        if (!groupByItems.isEmpty()) {
            baseSql.append(" group by ").append(String.join(", ", groupByItems));
        }
        appendRuntimeSort(queryBo, fieldExprMap, baseSql);

        String dataSql = baseSql + " limit :limit offset :offset";
        params.addValue("limit", queryBo.getPageSize());
        params.addValue("offset", (queryBo.getPageNum() - 1) * queryBo.getPageSize());
        List<Map<String, Object>> rows = namedParameterJdbcTemplate.queryForList(dataSql, params);

        String countSql = "select count(1) from (" + baseSql + ") x";
        Long total = namedParameterJdbcTemplate.queryForObject(countSql, params, Long.class);
        return buildResult(reportId, definition, rows, total, baseSql.toString());
    }

    /**
     * 构造返回对象。
     */
    private ErpReportQueryVo buildResult(
            Long reportId,
            ErpReportDefinition definition,
            List<Map<String, Object>> rows,
            Long total,
            String generatedSql
    ) {
        ErpReportQueryVo vo = new ErpReportQueryVo();
        vo.setReportId(reportId);
        vo.setDataSourceMode(safeUpper(definition.getAggregateMode()));
        vo.setTotal(total == null ? 0L : total);
        vo.setRows(rows == null ? Collections.emptyList() : rows);
        vo.setGeneratedSql(generatedSql);
        return vo;
    }

    /**
     * 解析字段来源表达式。
     */
    private String resolveSourceExpr(ReportFieldMeta meta) {
        String expr = meta.getExprText();
        if (expr != null && !expr.trim().isEmpty()) {
            sqlSecurityValidator.validateExpression(expr);
            return expr.trim();
        }
        String table = Objects.toString(meta.getSourceTable(), "").trim();
        String column = Objects.toString(meta.getSourceColumn(), "").trim();
        if (table.isEmpty() || column.isEmpty()) {
            throw new IllegalArgumentException("字段缺少来源表或来源字段: " + meta.getFieldKey());
        }
        sqlSecurityValidator.validateIdentifier(table);
        sqlSecurityValidator.validateIdentifier(column);
        return table + "." + column;
    }

    /**
     * 构建 FROM 与 JOIN 子句。
     */
    private String buildFromAndJoinSql(List<ReportFieldMeta> fieldMetaList, List<ReportJoinMeta> joinMetaList) {
        String baseTable = fieldMetaList.stream()
                .map(ReportFieldMeta::getSourceTable)
                .filter(Objects::nonNull)
                .map(String::trim)
                .filter(s -> !s.isEmpty())
                .findFirst()
                .orElseThrow(() -> new IllegalArgumentException("低代码模式缺少基础表配置。"));
        sqlSecurityValidator.validateIdentifier(baseTable);

        StringBuilder sql = new StringBuilder("from ").append(baseTable).append(" ");
        if (joinMetaList == null || joinMetaList.isEmpty()) {
            return sql.toString();
        }
        for (ReportJoinMeta joinMeta : joinMetaList) {
            if (joinMeta == null) {
                continue;
            }
            String joinType = safeUpper(joinMeta.getJoinType());
            if (!SAFE_JOIN_TYPE.contains(joinType)) {
                throw new IllegalArgumentException("不支持的关联类型: " + joinType);
            }
            sqlSecurityValidator.validateIdentifier(joinMeta.getLeftTable());
            sqlSecurityValidator.validateIdentifier(joinMeta.getLeftColumn());
            sqlSecurityValidator.validateIdentifier(joinMeta.getRightTable());
            sqlSecurityValidator.validateIdentifier(joinMeta.getRightColumn());

            sql.append(joinType).append(" JOIN ").append(joinMeta.getRightTable())
                    .append(" ON ")
                    .append(joinMeta.getLeftTable()).append(".").append(joinMeta.getLeftColumn())
                    .append(" = ")
                    .append(joinMeta.getRightTable()).append(".").append(joinMeta.getRightColumn());
            if (joinMeta.getExtraCondition() != null && !joinMeta.getExtraCondition().trim().isEmpty()) {
                sqlSecurityValidator.validateExpression(joinMeta.getExtraCondition());
                sql.append(" AND ").append(joinMeta.getExtraCondition().trim());
            }
            sql.append(" ");
        }
        return sql.toString();
    }

    /**
     * 追加运行时过滤条件。
     */
    private void appendRuntimeFilters(
            ErpReportQueryBo queryBo,
            Map<String, String> fieldExprMap,
            StringBuilder sql,
            MapSqlParameterSource params
    ) {
        if (queryBo.getFilters() == null || queryBo.getFilters().isEmpty()) {
            return;
        }
        int index = 0;
        for (Map<String, Object> filter : queryBo.getFilters()) {
            if (filter == null) {
                continue;
            }
            String fieldKey = Objects.toString(filter.get("fieldKey"), "").trim();
            String op = Objects.toString(filter.get("op"), "").trim().toUpperCase(Locale.ROOT);
            Object value = filter.get("value");
            if (fieldKey.isEmpty() || op.isEmpty()) {
                continue;
            }
            String expr = fieldExprMap.get(fieldKey);
            if (expr == null) {
                continue;
            }
            String p = "p" + index++;
            switch (op) {
                case "=":
                case "!=":
                case ">":
                case ">=":
                case "<":
                case "<=":
                    sql.append(" AND ").append(expr).append(" ").append(op).append(" :").append(p);
                    params.addValue(p, value);
                    break;
                case "LIKE":
                    sql.append(" AND ").append(expr).append(" LIKE :").append(p);
                    params.addValue(p, "%" + Objects.toString(value, "") + "%");
                    break;
                case "IN":
                    if (!(value instanceof List)) {
                        throw new IllegalArgumentException("IN 操作符的 value 必须为数组。");
                    }
                    List<?> inList = (List<?>) value;
                    if (inList.isEmpty()) {
                        sql.append(" AND 1 = 0 ");
                    } else {
                        sql.append(" AND ").append(expr).append(" IN (:").append(p).append(")");
                        params.addValue(p, inList);
                    }
                    break;
                case "BETWEEN":
                    if (!(value instanceof List) || ((List<?>) value).size() != 2) {
                        throw new IllegalArgumentException("BETWEEN 操作符的 value 必须为长度为 2 的数组。");
                    }
                    List<?> range = (List<?>) value;
                    sql.append(" AND ").append(expr).append(" BETWEEN :").append(p).append("_s AND :").append(p).append("_e");
                    params.addValue(p + "_s", range.get(0));
                    params.addValue(p + "_e", range.get(1));
                    break;
                default:
                    throw new IllegalArgumentException("不支持的过滤操作符: " + op);
            }
        }
    }

    /**
     * 追加运行时排序条件。
     */
    private void appendRuntimeSort(ErpReportQueryBo queryBo, Map<String, String> fieldExprMap, StringBuilder sql) {
        if (queryBo.getSorts() == null || queryBo.getSorts().isEmpty()) {
            return;
        }
        StringJoiner joiner = new StringJoiner(", ");
        for (Map<String, String> sort : queryBo.getSorts()) {
            if (sort == null) {
                continue;
            }
            String fieldKey = Objects.toString(sort.get("fieldKey"), "").trim();
            String direction = safeUpper(sort.get("direction"));
            if (fieldKey.isEmpty() || direction.isEmpty()) {
                continue;
            }
            if (!SAFE_SORT_DIRECTION.contains(direction)) {
                throw new IllegalArgumentException("不支持的排序方向: " + direction);
            }
            String expr = fieldExprMap.get(fieldKey);
            if (expr != null) {
                joiner.add(expr + " " + direction);
            }
        }
        if (!joiner.toString().isEmpty()) {
            sql.append(" ORDER BY ").append(joiner);
        }
    }

    /**
     * 安全转大写。
     */
    private String safeUpper(String text) {
        return text == null ? "" : text.trim().toUpperCase(Locale.ROOT);
    }

    /**
     * 构建报表专属聚合表名。
     */
    private String aggregateTableName(Long reportId) {
        return "rpt_agg_report_" + reportId;
    }

    /**
     * 判断报表聚合表是否存在。
     */
    private boolean aggregateTableExists(String tableName) {
        String sql = "select count(1) from information_schema.tables " +
                "where table_schema = (select database()) and table_name = :tableName";
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("tableName", tableName);
        Long count = namedParameterJdbcTemplate.queryForObject(sql, params, Long.class);
        return count != null && count > 0;
    }

    /**
     * 将 JSON 字符串解析为 Map。
     */
    @SuppressWarnings("unchecked")
    private Map<String, Object> parseJsonToMap(Object jsonObj) {
        if (jsonObj == null) {
            return Collections.emptyMap();
        }
        String text = Objects.toString(jsonObj, "").trim();
        if (text.isEmpty()) {
            return Collections.emptyMap();
        }
        Object parsed = JSON.parse(text);
        if (parsed instanceof Map) {
            return (Map<String, Object>) parsed;
        }
        return Collections.emptyMap();
    }
}
