import { ISQLiteAPI } from "../sqlite/type"

export type IKvIndex =
    | {
          type?: string
          unique?: boolean
      }
    | boolean

export interface IKvDefineIndexesOptions {
    /** 是否强制重新创建索引，默认 false */
    force?: boolean
    /** 索引所在的列名，默认 'data'
     * KvliteArray 使用 value 列存储元素值
     * KvliteCollection 使用 data 列存储键值
     */
    columnName?: string
}
/**
 * 为一个 SQLite 表定义 JSON 索引
 */
export function defineIndexes(
    sqlite: ISQLiteAPI,
    collectionName: string,
    indexes: { [field: string]: IKvIndex },
    options?: IKvDefineIndexesOptions
) {
    const columnName = options?.columnName || "data"
    for (let [field, index] of Object.entries(indexes)) {
        let indexName = `idx_${collectionName.replace(/\W+/g, "-")}_${field.replace(/\W+/g, "-")}`
        if (index === false) {
            // 删除已有索引
            let sql = `DROP INDEX IF EXISTS ${indexName};`
            sqlite.exec(sql)
            continue
        }

        let type = index === true ? "string" : index.type
        let isUnique = index === true ? false : index.unique

        // 如果指定强制重新创建索引，先删除已有索引
        if (options?.force) {
            let sql = `DROP INDEX IF EXISTS ${indexName};`
            sqlite.exec(sql)
        }

        let uniqueClause = isUnique ? "UNIQUE" : ""

        if (type === "number") {
            let sql = `CREATE  ${uniqueClause} INDEX IF NOT EXISTS ${indexName} ON ${collectionName} (CAST(json_extract(${columnName}, '$.${field}') AS REAL));`
            sqlite.exec(sql)
        } else {
            let sql = `CREATE ${uniqueClause} INDEX IF NOT EXISTS ${indexName} ON ${collectionName} (json_extract(${columnName}, '$.${field}'));`
            sqlite.exec(sql)
        }
    }
}