package com.xiaopeng.itinerary.utils;

import lombok.extern.slf4j.Slf4j;

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

/**
 * 分表查询辅助工具类
 * 提供分表查询的SQL构建和批量查询支持
 *
 * @author auto-generated
 */
@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 ItineraryQueryBuilder {

        /**
         * 根据用户ID查询操作日志
         */
        public static String querySysOperLogByUserId(Long userId, String additionalWhere) {
            String tableName = ShardingTableUtil.getSysOperLogTableName(userId);
            StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableName);
            sql.append(" WHERE userId = ").append(userId);

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

            return sql.toString();
        }

        /**
         * 根据用户ID查询协议用户信息
         */
        public static String queryAgreementAppUserByUserId(Long appUserId) {
            String tableName = ShardingTableUtil.getAgreementAppUserTableName(appUserId);
            return "SELECT * FROM " + tableName + " WHERE app_user_id = " + appUserId;
        }

        /**
         * 根据电子围栏区域ID查询点位信息
         */
        public static String queryPointByElectronicAreaId(Long electronicAreaId) {
            String tableName = ShardingTableUtil.getPointTableName(electronicAreaId);
            return "SELECT * FROM " + tableName + " WHERE electronic_area_id = " + electronicAreaId;
        }

        /**
         * 查询所有操作日志（全表扫描）
         */
        public static String queryAllSysOperLogs(String whereClause, String orderBy, Integer limit) {
            return ShardingQueryHelper.buildUnionAllQuery("sys_oper_log", 10, whereClause, orderBy, limit);
        }

        /**
         * 查询所有协议用户（全表扫描）
         */
        public static String queryAllAgreementAppUsers(String whereClause, String orderBy, Integer limit) {
            return ShardingQueryHelper.buildUnionAllQuery("xp_agreement_app_user", 10, whereClause, orderBy, limit);
        }

        /**
         * 查询所有点位（全表扫描）
         */
        public static String queryAllPoints(String whereClause, String orderBy, Integer limit) {
            return ShardingQueryHelper.buildUnionAllQuery("xp_point", 10, 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 countAllSysOperLogs(String whereClause) {
            return buildCountQuery("sys_oper_log", 10, whereClause);
        }

        /**
         * 统计所有协议用户数量
         */
        public static String countAllAgreementAppUsers(String whereClause) {
            return buildCountQuery("xp_agreement_app_user", 10, whereClause);
        }

        /**
         * 统计所有点位数量
         */
        public static String countAllPoints(String whereClause) {
            return buildCountQuery("xp_point", 10, whereClause);
        }
    }

    /**
     * 打印查询示例
     */
    public static void printQueryExamples() {
        log.info("========== Sharding Query Examples ==========");
        log.info("1. Query sys_oper_log by userId 12345:");
        log.info("   {}", ItineraryQueryBuilder.querySysOperLogByUserId(12345L, "status = 1"));
        log.info("");
        log.info("2. Query agreement_app_user by userId 67890:");
        log.info("   {}", ItineraryQueryBuilder.queryAgreementAppUserByUserId(67890L));
        log.info("");
        log.info("3. Query all sys_oper_logs (UNION ALL):");
        log.info("   {}", ItineraryQueryBuilder.queryAllSysOperLogs("status = 1", "create_time DESC", 100));
        log.info("");
        log.info("4. Count all agreement_app_users:");
        log.info("   {}", ShardingCountHelper.countAllAgreementAppUsers(null));
        log.info("=============================================");
    }
}