import { isEmptyObject } from "../../../is"
import { IKvFilter, IKvFindOptions, IKvUpdateOp } from "./type"

// --- Special Type Handling ---
const KV_TYPE = "__kv_type__"
const TYPE_DATE = "Date"
const TYPE_BIGINT = "BigInt"
const TYPE_BUFFER = "ArrayBuffer"

function replacer(this: any, key: string, value: any) {
    if (typeof value === "bigint") {
        return { [KV_TYPE]: TYPE_BIGINT, value: value.toString() }
    }

    const rawValue = this[key]
    if (rawValue instanceof Date) {
        return { [KV_TYPE]: TYPE_DATE, value: rawValue.toISOString() }
    }
    if (rawValue instanceof ArrayBuffer) {
        return { [KV_TYPE]: TYPE_BUFFER, value: Buffer.from(rawValue).toString("base64") }
    }
    return value
}

function reviver(key: string, value: any) {
    if (value && typeof value === "object" && value[KV_TYPE]) {
        const type = value[KV_TYPE]
        const val = value.value
        if (type === TYPE_DATE) return new Date(val)
        if (type === TYPE_BIGINT) return BigInt(val)
        if (type === TYPE_BUFFER) {
            const buf = Buffer.from(val, "base64")
            return buf.buffer.slice(buf.byteOffset, buf.byteOffset + buf.byteLength)
        }
    }
    return value
}

function isSpecialQueryValue(val: any): boolean {
    if (val instanceof Date) return true
    if (typeof val === "bigint") return true
    if (val instanceof ArrayBuffer) return true
    return false
}
// -----------------------------

export function outputDoc(data: any) {
    if (data === undefined) return undefined
    return JSON.parse(data, reviver)
}

export function inputDoc(data: any) {
    return JSON.stringify(data, replacer)
}

/**
 * 将 filter 转换为 SQL 语句
 * 会处理 filter 输入值的安全问题，并且根据 findOptions 的配置生成 SQL 语句
 * 为了方便复用，limit 和 offset  作为单独的 limitOffset 返回
 */
export function filterToSql(tableName: string, filter: IKvFilter, findOptions?: IKvFindOptions) {
    let conditions: string[] = []
    let parameters: any[] = []
    let limitOffset: number[] = []

    for (const field in filter) {
        const condition = filter[field]
        if (
            condition === undefined ||
            condition === null ||
            typeof condition === "string" ||
            typeof condition === "number" ||
            typeof condition === "boolean" ||
            condition instanceof Date ||
            typeof condition === "bigint" ||
            condition instanceof ArrayBuffer
        ) {
            const value = formatFilterValue(condition)
            let jsonPath = `$.${field}`
            if (isSpecialQueryValue(condition)) {
                jsonPath += `.value`
            }
            const jsonField = `json_extract(data, '${jsonPath}')`
            conditions.push(`${jsonField} IS ?`)
            parameters.push(value)
        } else if (typeof condition == "object") {
            for (const op in condition) {
                const rawOpValue = (condition as any)[op]
                const value = formatFilterValue(rawOpValue)

                let jsonPath = `$.${field}`
                if (["$in", "$nin"].includes(op)) {
                    if (Array.isArray(rawOpValue) && rawOpValue.length > 0 && isSpecialQueryValue(rawOpValue[0])) {
                        jsonPath += `.value`
                    }
                } else {
                    if (isSpecialQueryValue(rawOpValue)) {
                        jsonPath += `.value`
                    }
                }
                const jsonField = `json_extract(data, '${jsonPath}')`

                switch (op) {
                    case "$eq":
                        conditions.push(`${jsonField} IS ?`)
                        parameters.push(value)
                        break
                    case "$ne":
                        conditions.push(`${jsonField} IS NOT ?`)
                        parameters.push(value)
                        break
                    case "$gt":
                        conditions.push(`${jsonField} > ?`)
                        parameters.push(value)
                        break
                    case "$gte":
                        conditions.push(`${jsonField} >= ?`)
                        parameters.push(value)
                        break
                    case "$lt":
                        conditions.push(`${jsonField} < ?`)
                        parameters.push(value)
                        break
                    case "$lte":
                        conditions.push(`${jsonField} <= ?`)
                        parameters.push(value)
                        break
                    case "$in":
                        conditions.push(`${jsonField} IN (${value.map(() => "?").join(", ")})`)
                        parameters.push(...value)
                        break
                    case "$nin":
                        conditions.push(`${jsonField} NOT IN (${value.map(() => "?").join(", ")})`)
                        parameters.push(...value)
                        break
                    case "$like":
                        conditions.push(`${jsonField} LIKE ?`)
                        parameters.push(value)
                        break
                    case "$regex":
                        conditions.push(`${jsonField} REGEXP ?`)
                        parameters.push(value)
                        break
                    case "$exists":
                        const jsonTypeField = `json_type(data, '${jsonPath}')`
                        conditions.push(value ? `${jsonTypeField} IS NOT NULL` : `${jsonTypeField} IS NULL`)
                        break
                    default:
                        throw new Error(`Unsupported operator: ${op}`)
                }
            }
        } else {
            throw new Error(`Unsupported condition: ${field}.${condition}`)
        }
    }

    let sql = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : ""

    if (findOptions?.projection && findOptions.projection.length > 0) {
        sql = `SELECT ${findOptions.projection.join(", ")} FROM ${tableName} ${sql}`
    } else {
        sql = `SELECT * FROM ${tableName} ${sql}`
    }

    if (findOptions?.sort && findOptions.sort.length > 0) {
        sql += ` ORDER BY ${findOptions.sort
            .map((field) => {
                const jsonField = `json_extract(data, '$.${field.replace(/^-/, "")}')`
                return field.startsWith("-") ? `${jsonField} DESC` : jsonField
            })
            .join(", ")}`
    }

    if (findOptions?.limit) {
        sql += ` LIMIT ?`
        limitOffset.push(findOptions.limit!)
    }
    if (findOptions?.limit && findOptions.offset !== undefined) {
        sql += ` OFFSET ?`
        limitOffset.push(findOptions.offset)
    }

    return { sql, parameters, limitOffset }
}

/**
 * 将 updateOp 转换为 SQL 语句
 */
export function updateOpToSql(tableName: string, filter: IKvFilter | string, updateOp: IKvUpdateOp) {
    let json_set_args: string[] = []
    let json_remove_args: string[] = []

    let parameters: any[] = []

    if (updateOp.$set) {
        for (const [field, value] of Object.entries(updateOp.$set)) {
            if (value === undefined) continue
            json_set_args.push(`'$.${field}', json( ? )`)
            parameters.push(formatUpdateValue(value))
        }
    }

    if (updateOp.$unset) {
        for (const field of Object.keys(updateOp.$unset)) {
            json_remove_args.push(`'$.${field}'`)
        }
    }

    if (updateOp.$inc) {
        for (const [field, value] of Object.entries(updateOp.$inc)) {
            if (value === undefined) continue
            json_set_args.push(`'$.${field}', json( json_extract(data, '$.${field}') + ? )`)
            parameters.push(formatUpdateValue(value))
        }
    }

    if (updateOp.$mul) {
        for (const [field, value] of Object.entries(updateOp.$mul)) {
            if (value === undefined) continue
            json_set_args.push(`'$.${field}', json( json_extract(data, '$.${field}') * ? )`)
            parameters.push(formatUpdateValue(value))
        }
    }

    if (updateOp.$max) {
        for (const [field, value] of Object.entries(updateOp.$max)) {
            if (value === undefined) continue
            json_set_args.push(`'$.${field}', json( MAX(json_extract(data, '$.${field}'), ? ) )`)
            parameters.push(formatUpdateValue(value))
        }
    }

    if (updateOp.$min) {
        for (const [field, value] of Object.entries(updateOp.$min)) {
            if (value === undefined) continue
            json_set_args.push(`'$.${field}', json( MIN(json_extract(data, '$.${field}'), ? ) )`)
            parameters.push(formatUpdateValue(value))
        }
    }

    let sql = `UPDATE ${tableName} SET `
    if (json_set_args.length > 0 && json_remove_args.length > 0) {
        sql += `data = json_set( json_remove(data, ${json_remove_args.join(", ")}), ${json_set_args.join(", ")})`
    } else if (json_set_args.length > 0) {
        sql += `data = json_set( data, ${json_set_args.join(", ")})`
    } else if (json_remove_args.length > 0) {
        sql += `data = json_remove(data, ${json_remove_args.join(", ")})`
    } else {
        throw new Error("Invalid update")
    }

    if (typeof filter === "string") {
        sql += ` WHERE key = ?`
        parameters.push(filter)
    } else if (typeof filter === "object") {
        if (isEmptyObject(filter)) {
        } else {
            let filterSql = filterToSql(tableName, filter)
            sql += " WHERE" + filterSql.sql.split("WHERE")[1]
            parameters = parameters.concat(filterSql.parameters)
        }
    }

    // console.log(">>> sql", sql)
    // console.log(">>> parameters", parameters)
    return { sql, parameters }
}

export function formatFilterValue<T = any>(value: any): T | string | number | null {
    if (value === undefined) return null
    if (value === null) return null
    if (typeof value === "number" && isNaN(value)) return null
    if (value instanceof Date) return value.toISOString()
    if (typeof value === "bigint") return value.toString()
    if (value instanceof ArrayBuffer) return Buffer.from(value).toString("base64")
    if (value === false) return 0
    if (value === true) return 1
    // 递归处理数组中的每个元素（用于 $in/$nin 操作符）
    if (Array.isArray(value)) return value.map((v) => formatFilterValue(v)) as T
    return value
}

export function formatUpdateValue<T = any>(value: any): string {
    if (value === undefined) value = null
    return JSON.stringify(value, replacer)
}