package com.xiaopeng.order.utils;

import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * 分表查询辅助工具类
 * 提供分表查询的SQL构建和批量查询支持
 *
 * @author auto-generated
 * @date 2026-03-05
 */
@Slf4j
public class ShardingQueryHelper {

    /**
     * 构建单个分表的查询SQL
     *
     * @param baseTableName 基础表名
     * @param shardingValue 分片键值
     * @param shardingCount 分片数量
     * @param whereClause   WHERE子句（不包含WHERE关键字）
     * @return 完整的查询SQL
     */
    public static String buildSingleTableQuery(String baseTableName, Long shardingValue,
                                               int shardingCount, String whereClause) {
        String tableName = ShardingTableUtil.getShardingTableName(baseTableName, shardingValue, shardingCount);
        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        sql.append(tableName);

        if (whereClause != null && !whereClause.trim().isEmpty()) {
            sql.append(" WHERE ").append(whereClause);
        }

        String query = sql.toString();
        log.debug("Generated single table query: {}", query);
        return query;
    }

    /**
     * 构建全表扫描的UNION查询SQL（适用于需要查询所有分表的场景）
     *
     * @param baseTableName 基础表名
     * @param shardingCount 分片数量
     * @param whereClause   WHERE子句（不包含WHERE关键字）
     * @param orderBy       排序字句（不包含ORDER BY关键字）
     * @param limit         限制返回数量，null表示不限制
     * @return UNION查询SQL
     */
    public static String buildUnionAllQuery(String baseTableName, int shardingCount,
                                           String whereClause, String orderBy, Integer limit) {
        StringBuilder sql = new StringBuilder();

        for (int i = 0; i < shardingCount; i++) {
            if (i > 0) {
                sql.append(" UNION ALL ");
            }

            sql.append("SELECT * FROM ").append(baseTableName).append("_").append(i);

            if (whereClause != null && !whereClause.trim().isEmpty()) {
                sql.append(" WHERE ").append(whereClause);
            }
        }

        // 外层排序和限制
        if (orderBy != null && !orderBy.trim().isEmpty()) {
            sql.insert(0, "SELECT * FROM (");
            sql.append(") AS union_result ORDER BY ").append(orderBy);
        }

        if (limit != null && limit > 0) {
            sql.append(" LIMIT ").append(limit);
        }

        String query = sql.toString();
        log.debug("Generated union all query for {} tables: {}", shardingCount, query);
        return query;
    }

    /**
     * 获取需要查询的分表索引列表（基于分片键值范围）
     *
     * @param startValue    起始分片键值
     * @param endValue      结束分片键值
     * @param shardingCount 分片数量
     * @return 需要查询的表索引列表
     */
    public static List<Integer> getShardingIndexesInRange(Long startValue, Long endValue, int shardingCount) {
        List<Integer> indexes = new ArrayList<>();

        if (startValue == null || endValue == null || startValue > endValue) {
            log.warn("Invalid range: start={}, end={}, returning all indexes", startValue, endValue);
            for (int i = 0; i < shardingCount; i++) {
                indexes.add(i);
            }
            return indexes;
        }

        // 计算涉及的分表索引
        for (long value = startValue; value <= endValue; value++) {
            int index = (int) (value % shardingCount);
            if (!indexes.contains(index)) {
                indexes.add(index);
            }
        }

        log.debug("Range query from {} to {} covers indexes: {}", startValue, endValue, indexes);
        return indexes;
    }

    /**
     * 构建基于索引列表的UNION查询
     *
     * @param baseTableName    基础表名
     * @param tableIndexes     要查询的表索引列表
     * @param whereClause      WHERE子句
     * @param orderBy          排序字句
     * @param limit            限制数量
     * @return UNION查询SQL
     */
    public static String buildUnionQueryByIndexes(String baseTableName, List<Integer> tableIndexes,
                                                  String whereClause, String orderBy, Integer limit) {
        if (tableIndexes == null || tableIndexes.isEmpty()) {
            log.warn("No table indexes provided, returning empty query");
            return "";
        }

        StringBuilder sql = new StringBuilder();

        for (int i = 0; i < tableIndexes.size(); i++) {
            if (i > 0) {
                sql.append(" UNION ALL ");
            }

            int tableIndex = tableIndexes.get(i);
            sql.append("SELECT * FROM ").append(baseTableName).append("_").append(tableIndex);

            if (whereClause != null && !whereClause.trim().isEmpty()) {
                sql.append(" WHERE ").append(whereClause);
            }
        }

        // 外层排序和限制
        if (orderBy != null && !orderBy.trim().isEmpty()) {
            sql.insert(0, "SELECT * FROM (");
            sql.append(") AS union_result ORDER BY ").append(orderBy);
        }

        if (limit != null && limit > 0) {
            sql.append(" LIMIT ").append(limit);
        }

        String query = sql.toString();
        log.debug("Generated union query for indexes {}: {}", tableIndexes, query);
        return query;
    }

    /**
     * 构建订单相关表的批量查询SQL示例
     */
    public static class OrderQueryBuilder {

        /**
         * 根据用户ID查询订单
         */
        public static String queryOrderByUserId(Long appUserId, String additionalWhere) {
            String tableName = ShardingTableUtil.getOrderTableName(appUserId);
            StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableName);
            sql.append(" WHERE app_user_id = ").append(appUserId);

            if (additionalWhere != null && !additionalWhere.trim().isEmpty()) {
                sql.append(" AND ").append(additionalWhere);
            }

            return sql.toString();
        }

        /**
         * 根据订单ID查询订单详情
         */
        public static String queryOrderDetailByOrderId(Long orderId) {
            String tableName = ShardingTableUtil.getOrderDetailTableName(orderId);
            return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
        }

        /**
         * 根据订单ID查询订单支付信息
         */
        public static String queryOrderPaymentByOrderId(Long orderId) {
            String tableName = ShardingTableUtil.getOrderPaymentTableName(orderId);
            return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
        }

        /**
         * 根据订单ID查询订单评价
         */
        public static String queryOrderEvaluateByOrderId(Long orderId) {
            String tableName = ShardingTableUtil.getOrderEvaluateTableName(orderId);
            return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
        }

        /**
         * 根据订单ID查询订单取消记录
         */
        public static String queryOrderCancelByOrderId(Long orderId) {
            String tableName = ShardingTableUtil.getOrderCancelTableName(orderId);
            return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
        }

        /**
         * 查询所有订单（全表扫描）
         */
        public static String queryAllOrders(String whereClause, String orderBy, Integer limit) {
            return ShardingQueryHelper.buildUnionAllQuery("xp_order", 20, whereClause, orderBy, limit);
        }

        /**
         * 查询所有订单详情（全表扫描）
         */
        public static String queryAllOrderDetails(String whereClause, String orderBy, Integer limit) {
            return ShardingQueryHelper.buildUnionAllQuery("xp_order_detail", 20, whereClause, orderBy, limit);
        }
    }

    /**
     * 分表查询计数工具
     */
    public static class ShardingCountHelper {

        /**
         * 构建分表计数查询（UNION ALL + SUM）
         */
        public static String buildCountQuery(String baseTableName, int shardingCount, String whereClause) {
            StringBuilder sql = new StringBuilder("SELECT SUM(cnt) AS total_count FROM (");

            for (int i = 0; i < shardingCount; i++) {
                if (i > 0) {
                    sql.append(" UNION ALL ");
                }

                sql.append("SELECT COUNT(*) AS cnt FROM ").append(baseTableName).append("_").append(i);

                if (whereClause != null && !whereClause.trim().isEmpty()) {
                    sql.append(" WHERE ").append(whereClause);
                }
            }

            sql.append(") AS count_result");

            String query = sql.toString();
            log.debug("Generated count query: {}", query);
            return query;
        }

        /**
         * 统计所有订单数量
         */
        public static String countAllOrders(String whereClause) {
            return buildCountQuery("xp_order", 20, whereClause);
        }
    }

    /**
     * 打印查询示例
     */
    public static void printQueryExamples() {
        log.info("========== Sharding Query Examples ==========");
        log.info("1. Query order by userId 12345:");
        log.info("   {}", OrderQueryBuilder.queryOrderByUserId(12345L, "status = 1"));
        log.info("");
        log.info("2. Query order detail by orderId 67890:");
        log.info("   {}", OrderQueryBuilder.queryOrderDetailByOrderId(67890L));
        log.info("");
        log.info("3. Query all orders with status=1 (UNION ALL):");
        log.info("   {}", OrderQueryBuilder.queryAllOrders("status = 1", "create_time DESC", 100));
        log.info("");
        log.info("4. Count all orders:");
        log.info("   {}", ShardingCountHelper.countAllOrders("status IN (9, 10)"));
        log.info("=============================================");
    }
}