import { Op, Sequelize, Model, WhereOptions, Order, IncludeOptions } from "sequelize";
import { ERRORENUM } from "../config/errorEnum";
import { mysqlModelMap } from "../model/sqlModelBind";
import { BizError } from "../util/bizError";

// 定义类型接口
interface QueryParam {
    [key: string]: any;
    "%orderDesc%"?: string;
    "%orderAsc%"?: string;
    "%limit%"?: number;
    "%group%"?: string;
    "%literal%"?: string;
    "%or%"?: Array<QueryParam>;
}

interface AnalysisResult {
    where: WhereOptions;
    attributes?: string[];
    order?: Order;
    limit?: number;
    group?: string;
}

interface IncludeConfig {
    [tableName: string]: {
        where?: QueryParam;
        column?: string[];
    };
}

/**
 * where条件查询参数解析
 * 支持的操作符:
 * %or%：或者条件 [{"列名": 条件}, {"列名": 条件}]
 * %like%：模糊查询 {列名: {"%like%": "值"}}
 * %gt%：大于 {列名: {"%gt%": 值}}
 * %gte%：大于等于 {列名: {"%gte%": 值}}
 * %lt%：小于 {列名: {"%lt%": 值}}
 * %lte%：小于等于 {列名: {"%lte%": 值}}
 * %between%：查询范围内数据 {列名: {"%between%": ["开始", "结束"]}}
 * %notBetween%：查询不在范围内数据 {列名: {"%notBetween%": ["开始", "结束"]}}
 * %in%：IN查询 {列名: {"%in%": [值1, 值2]}}
 * %notIn%：NOT IN查询 {列名: {"%notIn%": [值1, 值2]}}
 * %ne%：不等于 {列名: {"%ne%": 值}}
 * %regexp%：正则表达式 {列名: {"%regexp%": "模式"}}
 * %orderDesc%: 降序排序 {"%orderDesc%": "列名"}
 * %orderAsc%: 升序排序 {"%orderAsc%": "列名"}
 * %limit%: 限制数量 {"%limit%": 数量}
 * %group%: 分组 {"%group%": "列名"}
 * %literal%: 原生SQL {"%literal%": "SQL语句"}
 */
function analysisParamToWhere(param: QueryParam, column?: string[]): AnalysisResult {
    const where: any = {};
    const order: Order = [];
    let group: string | undefined;
    let limit: number | undefined;
    for (const key in param) {
        const value = param[key];
        
        // 处理 %or% 条件
        if (key === "%or%") {
        if (Array.isArray(value) && value.length > 0) {
            const orConditions = value.map((condition: QueryParam) => {
            // 递归处理 or 中的每个条件
            const result = analysisParamToWhere(condition);
            return result.where;
            }).filter(cond => cond && Object.keys(cond).length > 0);
            
            if (orConditions.length > 0) {
            where[Op.or] = orConditions;
            }
        }
        continue;
        }
        
        // 处理普通条件和操作符
        if (typeof value === "object" && value !== null && !Array.isArray(value)) {
        // 处理操作符对象
        const operatorObj: any = {};
        let hasOperator = false;
        
        for (const opKey in value) {
            const opValue = value[opKey];
            
            switch (opKey) {
            case "%like%":
                operatorObj[Op.like] = `%${opValue}%`;
                hasOperator = true;
                break;
            case "%gt%":
                operatorObj[Op.gt] = opValue;
                hasOperator = true;
                break;
            case "%gte%":
                operatorObj[Op.gte] = opValue;
                hasOperator = true;
                break;
            case "%lt%":
                operatorObj[Op.lt] = opValue;
                hasOperator = true;
                break;
            case "%lte%":
                operatorObj[Op.lte] = opValue;
                hasOperator = true;
                break;
            case "%between%":
                operatorObj[Op.between] = opValue;
                hasOperator = true;
                break;
            case "%notBetween%":
                operatorObj[Op.notBetween] = opValue;
                hasOperator = true;
                break;
            case "%in%":
                operatorObj[Op.in] = opValue;
                hasOperator = true;
                break;
            case "%notIn%":
                operatorObj[Op.notIn] = opValue;
                hasOperator = true;
                break;
            case "%ne%":
                operatorObj[Op.ne] = opValue;
                hasOperator = true;
                break;
            case "%regexp%":
                operatorObj[Op.regexp] = opValue;
                hasOperator = true;
                break;
            }
        }
        
        if (hasOperator) {
            where[key] = operatorObj;
        } else {
            // 普通对象，直接赋值
            where[key] = value;
        }
        } else {
        // 处理特殊键和普通值
        switch (key) {
            case "%orderDesc%":
            order.push([Sequelize.col(value), "DESC"]);
            break;
            case "%orderAsc%":
            order.push([Sequelize.col(value), "ASC"]);
            break;
            case "%limit%":
            limit = Number(value);
            break;
            case "%group%":
            group = value;
            break;
            case "%literal%":
            where[Op.and] = Sequelize.literal(value);
            break;
            default:
            where[key] = value;
        }
        }
    }
    const result: AnalysisResult = { where };
    if (column && column.length) result.attributes = column;
    if (order.length) result.order = order;
    if (limit) result.limit = limit;
    if (group) result.group = group;
    return result;
}


/**
 * 获取表模型
 */
function getTableModel(tableName: string): typeof Model {
    let tableModel = mysqlModelMap[tableName];
    if (!tableModel) {
        throw new BizError(ERRORENUM.不存在表, `表 ${tableName} 不存在`);
    }
    return tableModel;
}


/**
 * 查询单个数据
 */
export async function selectOneDataByParam(tableName: string, param: QueryParam, column?: string[]) {
    let tableModel = mysqlModelMap[tableName];
    const selectParam = analysisParamToWhere(param, column);
    const data = await tableModel.findOne(selectParam);
    return { data: data || {} };
}


/**
 * 查询多个数据
 */
export async function selectDataListByParam(tableName: string, param: QueryParam, column?: string[]) {
    let tableModel = mysqlModelMap[tableName];
    const selectParam = analysisParamToWhere(param, column);
    const data = await tableModel.findAll(selectParam);
    return { data };
}


/**
 * 分页查询
 */
export async function selectDataListToPageByParam(tableName: string, param: QueryParam, column: string[], pageNumber: number, pageSize: number ) {
    let tableModel = mysqlModelMap[tableName];
    const selectParam: any = analysisParamToWhere(param, column);
    selectParam.limit = pageSize || 10;
    selectParam.offset = (pageNumber - 1) * pageSize;
    const data = await tableModel.findAll(selectParam);
    return { data };
}


/**
 * 查询数据总数
 */
export async function selectDataCountByParam(tableName: string, param: QueryParam) {
let tableModel = mysqlModelMap[tableName];
const selectParam: any = analysisParamToWhere(param, []);
const count = await tableModel.count(selectParam);
return { data: count };
}


/**
 * 关联查询
 */
export async function associationSelect(tableName: string, param: any) {
    let model = mysqlModelMap[tableName];
    if (!model) throw new BizError(ERRORENUM.不存在表);
    const data = await model.aggragateData(param);
    return { data };
}


/**
 * 构建关联查询的include配置
 */
function buildIncludeOptions(includeConf: IncludeConfig): IncludeOptions[] {
    return Object.entries(includeConf).map(([tableName, config]) => {
        const tableModel = mysqlModelMap[tableName];
        if (!tableModel) {
        throw new BizError(ERRORENUM.不存在表, `尝试进行多表联查，但是不存在 ${tableName}`);
        }
        const includeOptions: IncludeOptions = {
        model: tableModel,
        ...analysisParamToWhere(config.where || {}, config.column)
        };
        return includeOptions;
    });
}


/**
 * 多表联查 - 列表
 */
export async function selectDataToTableAssociation(tableName: string, includeConf: IncludeConfig, param: QueryParam, column?: string[]) {
    let tableModel = mysqlModelMap[tableName];
    const include = buildIncludeOptions(includeConf);
    const selectParam: any = analysisParamToWhere(param, column);
    selectParam.include = include;
    const data = await tableModel.findAll(selectParam);
    return { data };
}


/**
 * 多表联查 - 分页
 */
export async function selectDataToTableAssociationToPage(tableName:string, includeConf:IncludeConfig, param:QueryParam, column:string[], pageNumber:number, pageSize:number) {
    let tableModel = mysqlModelMap[tableName];
    const include = buildIncludeOptions(includeConf);
    const selectParam: any = analysisParamToWhere(param, column);
    selectParam.include = include;
    selectParam.limit = pageSize || 10;
    selectParam.offset = (pageNumber - 1) * pageSize;
    const data = await tableModel.findAll(selectParam);
    return { data };
}


/**
 * 多表联查 - 单个
 */
export async function selectOneDataToTableAssociation(tableName:string,includeConf:IncludeConfig,param:QueryParam,column?:string[]) {
    let tableModel = mysqlModelMap[tableName];
    const include = buildIncludeOptions(includeConf);
    const selectParam: any = analysisParamToWhere(param, column);
    selectParam.include = include;
    const data = await tableModel.findOne(selectParam);
    return { data: data || {} };
}


// ==================== 新增的通用方法 ====================
/**
 * 查询并计数（返回数据和总数）
 */
export async function selectDataWithCount( tableName: string, param: QueryParam, column?: string[] ) {
    let tableModel = mysqlModelMap[tableName];
    const selectParam = analysisParamToWhere(param, column);
    const result = await tableModel.findAndCountAll(selectParam);
    return { data: result.rows, count: result.count };
}


/**
 * 分页查询（增强版，返回数据、总数和分页信息）
 */
export async function selectPaginatedData( tableName: string, param: QueryParam, column: string[], pageNumber: number, pageSize: number ) {
    let tableModel = mysqlModelMap[tableName];
    const selectParam: any = analysisParamToWhere(param, column);
    selectParam.limit = pageSize;
    selectParam.offset = (pageNumber - 1) * pageSize;
    const result = await tableModel.findAndCountAll(selectParam);
    
    return {
        data: result.rows,
        pagination: {
        page: pageNumber,
        pageSize,
        total: result.count,
        totalPages: Math.ceil(result.count / pageSize)
        }
    };
}


/**
 * 自定义排序查询
 * @param orders 排序数组，格式: [['column1', 'DESC'], ['column2', 'ASC']]
 */
export async function selectDataWithCustomOrder( tableName: string, param: QueryParam, column: string[], orders: [string, 'ASC' | 'DESC'][] ) {
    let tableModel = mysqlModelMap[tableName];
    const selectParam: any = analysisParamToWhere(param, column);
    
    // 添加自定义排序
    selectParam.order = orders.map(([column, direction]) => 
        [Sequelize.col(column), direction]
    );
    const data = await tableModel.findAll(selectParam);
    return { data };
}


/**
 * 查询最大值
 */
export async function selectMaxValue( tableName: string, column: string, whereParam?: QueryParam ) {
    let tableModel = mysqlModelMap[tableName];
    const where = whereParam ? analysisParamToWhere(whereParam, []).where : {};
    
    const result = await tableModel.max(column, { where });
    return { max: result };
}


/**
 * 查询最小值
 */
export async function selectMinValue( tableName: string, column: string, whereParam?: QueryParam ) {
    let tableModel = mysqlModelMap[tableName];
    const where = whereParam ? analysisParamToWhere(whereParam, []).where : {};
    
    const result = await tableModel.min(column, { where });
    return { min: result };
}


/**
 * 查询平均值
 */
export async function selectAvgValue( tableName: string, column: string, whereParam?: QueryParam ) {
    let tableModel = mysqlModelMap[tableName];
    const where = whereParam ? analysisParamToWhere(whereParam, []).where : {};
    
    const result = await tableModel.average(column, { where });
    return { average: result };
}


/**
 * 查询求和
 */
export async function selectSumValue( tableName: string, column: string, whereParam?: QueryParam ) {
    let tableModel = mysqlModelMap[tableName];
    const where = whereParam ? analysisParamToWhere(whereParam, []).where : {};
    
    const result = await tableModel.sum(column, { where });
    return { sum: result };
}


/**
 * 分页查询（支持自定义排序）
 * @param tableName 表名
 * @param param 查询参数
 * @param column 查询列
 * @param pageNumber 页码
 * @param pageSize 每页大小
 * @param orders 排序数组，格式: [['column1', 'DESC'], ['column2', 'ASC']]
 */
export async function selectPaginatedDataWithOrder( tableName:string, param:QueryParam, column:string[], pageNumber:number, pageSize:number, orders:[string, 'ASC' | 'DESC'][] = []) {
    let tableModel = mysqlModelMap[tableName];
    const selectParam: any = analysisParamToWhere(param, column);
    selectParam.limit = pageSize;
    selectParam.offset = (pageNumber - 1) * pageSize;
    // 添加自定义排序
    if (orders && orders.length > 0) {
        selectParam.order = orders.map(([column, direction]) => 
            [Sequelize.col(column), direction]
        );
    }
    const result = await tableModel.findAndCountAll(selectParam);

    return {
        data: result.rows,
        pagination: {
            page: pageNumber,
            pageSize,
            total: result.count,
            totalPages: Math.ceil(result.count / pageSize)
        }
    };
}







