/**
 * SQLite服务模块
 * 提供数据库初始化、表结构管理、JPA风格的增删改查方法和事务管理
 */
import log from '../../dxmodules/dxLogger.js'
import sqlite from '../../dxmodules/dxSqlite.js'

//-------------------------variable-------------------------
const sqliteService = {}
//-------------------------public-------------------------

/**
 * 初始化数据库
 * @param {string} path - 数据库文件路径
 * @throws {Error} 如果路径为空则抛出错误
 */
sqliteService.init = function (path) {
    if (!path) {
        throw new Error("path should not be null or empty")
    }
    // 创建数据库
    sqlite.init(path)
    // 创建表
    createTables()
}

/**
 * 数据库表结构定义
 */
let entities = {
    d1_pass_record: {
        id: "VARCHAR(128) PRIMARY KEY",
        keyId: "VARCHAR(128)",
        permissionId: "VARCHAR(128)",
        permissionId2: "VARCHAR(128)",
        userId: "VARCHAR(128)",
        userId2: "VARCHAR(128)",
        type: "VARCHAR(128)",
        code: "VARCHAR(128)",
        door: "VARCHAR(128)",
        time: "INTEGER",
        result: "INTEGER",
        extra: "TEXT",
        extra2: "TEXT",
        message: "TEXT",
    },
    d1_permission: {
        permissionId: "VARCHAR(128) PRIMARY KEY",
        userId: "VARCHAR(128)",
        door: "VARCHAR(128)",
        extra: "TEXT",
        timeType: "INTEGER",
        beginTime: "INTEGER",
        endTime: "INTEGER",
        repeatBeginTime: "INTEGER",
        repeatEndTime: "INTEGER",
        period: "TEXT",
    },
    d1_security: {
        securityId: "VARCHAR(128) PRIMARY KEY",
        type: "VARCHAR(128)",
        key: "VARCHAR(128)",
        value: "TEXT",
        startTime: "INTEGER",
        endTime: "INTEGER",
    },
    d1_voucher: {
        keyId: "VARCHAR(128) PRIMARY KEY",
        type: "VARCHAR(128)",
        code: "TEXT",
        userId: "VARCHAR(128)",
        extra: "TEXT",
    },
    d1_person: {
        userId: "VARCHAR(128) PRIMARY KEY",
        name: "VARCHAR(128)",
        extra: "TEXT",
    },
    d1_emergency_password: {
        id: "VARCHAR(128) PRIMARY KEY",
        password: "VARCHAR(128)",
        description: "VARCHAR(256)",
        createTime: "INTEGER",
        updateTime: "INTEGER",
        status: "INTEGER"
    }
}

/**
 * SQL类型转JS类型
 * @param {string} sqlType - SQL数据类型
 * @returns {string} JavaScript数据类型
 */
let sqlType2jsType = (sqlType) => {
    if (sqlType.indexOf("INTEGER") > -1) {
        return 'number'
    } else {
        return 'string'
    }
}

/**
 * 创建表结构
 * @throws {Error} 如果表创建失败则抛出错误
 */
function createTables() {
    for (const tableName in entities) {
        const table = entities[tableName];
        let sql = `CREATE TABLE IF NOT EXISTS ${tableName} (`
        for (const column in table) {
            const type = table[column];
            sql += ` ${column} ${type},`
        }
        sql = sql.slice(0, -1);
        sql += ")"
        let ret = sqlite.exec(sql)
        if (ret != 0) {
            throw new Error(`table ${tableName} create exception: ${ret}`)
        }
    }
}

/**
 * 创建JPA风格的代理处理器
 */
let handler = {
    get: function (target, prop, receiver) {
        return (...args) => {
            return createJPA(prop, target.tableName, ...args)
        }
    }
}

// 为每个表创建JPA风格的增删改查方法
sqliteService.d1_pass_record = new Proxy({ tableName: "d1_pass_record" }, handler);

// 添加根据时间范围删除记录的方法
sqliteService.d1_pass_record.deleteByTimeRange = function (startTime, endTime) {
    try {
        // 构建查询条件
        let conditions = {};
        if (startTime) {
            conditions.time = {
                $gte: startTime
            };
        }
        if (endTime) {
            if (!conditions.time) {
                conditions.time = {};
            }
            conditions.time.$lte = endTime;
        }
        
        // 使用JPA风格的delete方法
        let ret = sqliteService.d1_pass_record.delete(conditions);
        log.info('[sqliteService] deleteByTimeRange返回值: ' + ret);
        return ret;
    } catch (error) {
        log.error('deleteByTimeRange error:', error);
        return -1;
    }
};
sqliteService.d1_permission = new Proxy({ tableName: "d1_permission" }, handler);
sqliteService.d1_security = new Proxy({ tableName: "d1_security" }, handler);
sqliteService.d1_voucher = new Proxy({ tableName: "d1_voucher" }, handler);
sqliteService.d1_person = new Proxy({ tableName: "d1_person" }, handler);
sqliteService.d1_emergency_password = new Proxy({ tableName: "d1_emergency_password" }, handler);

/**
 * 开始事务
 * 事务不提交数据库重启后，数据会还原，所以transaction后一定要commit
 * 如果在一个事务尚未提交或回滚的情况下执行另一个 BEGIN TRANSACTION，SQLite 会自动将新的事务嵌套在之前的事务内部，而不是覆盖之前的事务。
 */
sqliteService.transaction = function () {
    sqlite.exec("BEGIN TRANSACTION;")
}

/**
 * 回滚事务
 */
sqliteService.rollback = function () {
    sqlite.exec("ROLLBACK;")
}

/**
 * 提交事务
 * 提交后无法回滚，数据无法还原
 */
sqliteService.commit = function () {
    sqlite.exec("COMMIT;")
}

/**
 * 自动创建jpa常用增删改查sql方法，
 * 支持的规则：findByAAndBAndC,findAll,findAllOrderByADescBAsc,deleteByAAndBAndC,deleteAll,deleteInBatch,deleteByIdInBatch,updateAByBAndCAndD,save,saveAll,count,countBy
 * 条件分页查询，eg：findByAAndBAndC(x,x,x,{ page: 0, size: 200, 其他条件, id:"123456" })
 * 批量删除，eg：deleteInBatch([{ a: 1, b: 2, c: "3" }, { a: 2 }])
 * 条件删除，eg：deleteAll({ a: 1, b: 2, c: "3" })
 * 单条件批量删除，eg：deleteByIdInBatch([1,2,3,4,5,6])
 * 更多示例可参考下面测试方法
 * @param {string} methodName 方法名
 * @param {string} tableName 表名
 * @param  {...any} nums 方法参数
 * @returns {any} sqlite执行结果
 */
function createJPA(methodName, tableName, ...nums) {
    let sql
    let isFind = false
    let isCount = false
    let noPageable = false
    let hasOrderBy = false
    if (methodName.startsWith("save")) {
        // 增
        if (methodName.startsWith("saveAll")) {
            // 批量
            nums = nums[0]
            sql = `INSERT INTO ${tableName} VALUES `
            for (let i = 0; i < nums.length; i++) {
                const record = nums[i];
                sql += `(`
                for (const column in entities[tableName]) {
                    const item = record[column];
                    if (sqlType2jsType(entities[tableName][column]) == 'string') {
                        sql += `'${isEmpty(item) ? "" : item}',`
                    } else {
                        sql += `${isEmpty(item) ? 0 : item},`
                    }
                }
                sql = sql.slice(0, -1);
                sql += `)`
                if (i != nums.length - 1) {
                    sql += `, `
                }
            }
        } else {
            // 单条
            let record = nums[0]
            sql = `INSERT INTO ${tableName} VALUES (`
            for (const column in entities[tableName]) {
                const item = record[column];
                if (sqlType2jsType(entities[tableName][column]) == 'string') {
                    sql += `'${isEmpty(item) ? "" : item}',`
                } else {
                    sql += `${isEmpty(item) ? 0 : item},`
                }
            }
            sql = sql.slice(0, -1);
            sql += `)`
        }
        methodName = ""
        noPageable = true
    } else if (methodName.startsWith("delete")) {
        // 删
        if (methodName.startsWith("deleteAll")) {
            // 清空表
            sql = `DELETE FROM ${tableName} `
            methodName = ""
        } else if (methodName.endsWith("InBatch")) {
            if (nums.length != 1) {
                log.error("[JPA]:", "缺少参数")
                return
            }
            sql = `DELETE FROM ${tableName} WHERE `
            if (methodName.indexOf("By") > -1) {
                methodName = methodName.split("By")[1].split("InBatch")[0]
                sql += `${firstLower(methodName)} IN `
                let whereClauses = ""
                for (let i = 0; i < nums[0].length; i++) {
                    const value = nums[0][i];
                    if (typeof value == 'string') {
                        whereClauses += `'${value}'`
                    } else {
                        whereClauses += `${value} `
                    }
                    if (i != nums[0].length - 1) {
                        whereClauses += ","
                    }
                }
                sql += `(${whereClauses})`
            } else {
                for (let i = 0; i < nums[0].length; i++) {
                    let whereClauses = ""
                    const record = nums[0][i];
                    for (const column in record) {
                        const value = record[column];
                        if (typeof value == 'string') {
                            whereClauses += `${column} = '${value}'`
                        } else {
                            whereClauses += `${column} = ${value}`
                        }
                        whereClauses += ` AND `
                    }
                    whereClauses = whereClauses.slice(0, " AND ".length * (-1))
                    sql += `(${whereClauses})`
                    if (i != nums[0].length - 1) {
                        sql += ` OR `
                    }
                }
            }
            methodName = ""
            noPageable = true
        } else {
            sql = `DELETE FROM ${tableName} `
            methodName = methodName.substring("delete".length)
        }
    } else if (methodName.startsWith("update")) {
        // 改
        sql = `UPDATE ${tableName} SET`
        methodName = methodName.substring("update".length)
    } else if (methodName.startsWith("find")) {
        // 查
        isFind = true
        sql = `SELECT * FROM ${tableName} `
        if (methodName.startsWith("findAll")) {
            methodName = methodName.substring("findAll".length)
        } else {
            methodName = methodName.substring("find".length)
        }
        let index = methodName.indexOf("OrderBy")
        if (index > -1) {
            hasOrderBy = methodName.substring(index + "OrderBy".length).match(/\w+?(Desc|Asc)/g)
            methodName = methodName.substring(0, index)
        }
    } else if (methodName.startsWith("count")) {
        // 统计
        isFind = true
        isCount = true
        sql = `SELECT COUNT(*) FROM ${tableName} `
        methodName = methodName.substring("count".length)
    } else {
        log.error("[JPA]:", "不支持的方法")
        return
    }
    // where条件构建
    let index = methodName.indexOf("By")
    let whereClauses = ""
    if (index > -1) {
        let count = 0
        let conditionsPart = methodName.substring(index + 2)
        if (conditionsPart.indexOf("And") > -1) {
            conditionsPart = conditionsPart.split("And")
            if (nums.length < conditionsPart.length) {
                log.error("[JPA]:", "缺少参数")
                return
            }
            for (let i = 0; i < conditionsPart.length; i++) {
                const field = conditionsPart[i];
                if (typeof nums[i] == 'string') {
                    whereClauses += `${firstLower(field)} = '${nums[i]}'`
                } else {
                    whereClauses += `${firstLower(field)} = ${nums[i]}`
                }
                if (i != conditionsPart.length - 1) {
                    whereClauses += ` AND `
                }
                count = i
            }
        } else if (conditionsPart.indexOf("Or") > -1) {
            conditionsPart = conditionsPart.split("Or")
            if (nums.length < conditionsPart.length) {
                log.error("[JPA]:", "缺少参数")
                return
            }
            for (let i = 0; i < conditionsPart.length; i++) {
                const field = conditionsPart[i];
                if (typeof nums[i] == 'string') {
                    whereClauses += `${firstLower(field)} = '${nums[i]}'`
                } else {
                    whereClauses += `${firstLower(field)} = ${nums[i]}`
                }
                if (i != conditionsPart.length - 1) {
                    whereClauses += ` OR `
                }
                count = i
            }
        } else {
            if (nums.length < 1) {
                log.error("[JPA]:", "缺少参数")
                return
            }
            if (typeof nums[0] == 'string') {
                whereClauses = `${firstLower(conditionsPart)} = '${nums[0]}' `
            } else {
                whereClauses = `${firstLower(conditionsPart)} = ${nums[0]} `
            }
        }
        count++
        // update的set项构建
        let setClauses = ""
        let prefix = methodName.substring(0, index);
        if (prefix.length > 0) {
            prefix = prefix.split("And")
            if ((nums.length - count) < prefix.length) {
                log.error("[JPA]:", "缺少参数")
                return
            }
            for (let i = 0; i < prefix.length; i++) {
                const field = prefix[i];
                if (typeof nums[i + count] == 'string') {
                    setClauses += `${firstLower(field)} = '${nums[i + count]}',`
                } else {
                    setClauses += `${firstLower(field)} = ${nums[i + count]},`
                }
            }
            setClauses = setClauses.slice(0, -1)
            sql += ` ${setClauses} `
        }
        sql += `WHERE ${whereClauses} `
    }
    // order排序
    let orderByClauses = ""
    if (hasOrderBy) {
        orderByClauses = "ORDER BY "
        let conditionsPart = hasOrderBy
        for (let i = 0; i < conditionsPart.length; i++) {
            const orderItem = conditionsPart[i];
            let isDesc = orderItem.indexOf("Desc")
            let isAsc = orderItem.indexOf("Asc")
            if (isDesc > -1) {
                orderByClauses += `${firstLower(orderItem.substring(0, isDesc))} DESC,`
            }
            if (isAsc > -1) {
                orderByClauses += `${firstLower(orderItem.substring(0, isAsc))} ASC,`
            }
        }
        orderByClauses = orderByClauses.slice(0, -1)
    }
    // 判断分页条件查询
    let pageable = nums[nums.length - 1]
    if (typeof pageable == 'object' && !noPageable) {
        let clauses = ""
        for (const key in pageable) {
            const condition = pageable[key];
            if (key == "page" || key == "size") {
                continue
            }
            if (typeof condition == 'string') {
                clauses += `${firstLower(key)} = '${condition}'`
            } else {
                clauses += `${firstLower(key)} = ${condition}`
            }
            clauses += ` AND `
        }
        if (clauses.length > 0) {
            clauses = clauses.slice(0, " AND ".length * (-1))
            if (sql.indexOf("WHERE") > -1) {
                sql += `AND ${clauses} `
            } else {
                sql += `WHERE ${clauses} `
            }
        }
        sql += `${orderByClauses} `
        if (isFind && !isCount && !isEmpty(pageable.page) && !isEmpty(pageable.size)) {
            sql += `LIMIT ${pageable.size} OFFSET ${pageable.page * pageable.size} `
        }
    } else {
        sql += `${orderByClauses} `
    }
    sql += `;`;
    // log.info("[JPA]:", sql)
    let ret
    if (isFind) {
        ret = sqlite.select(sql)
        if (isCount) {
            if (ret[0] && ret[0]["COUNT(*)"]) {
                return ret[0]["COUNT(*)"]
            } else {
                return 0
            }
        }
    } else {
        ret = sqlite.exec(sql)
    }
    return ret
}

/**
 * 判空函数
 * @param {any} value - 要判断的值
 * @returns {boolean} 是否为空
 */
function isEmpty(value) {
    return value === undefined || value === null
}

/**
 * 首字母小写
 * @param {string} str - 字符串
 * @returns {string} 首字母小写的字符串
 */
function firstLower(str) {
    return str.charAt(0).toLowerCase() + str.slice(1);
}

/**
 * JPA测试方法
 * 提供各种JPA方法的使用示例
 */
sqliteService.testJPA = function () {
    // 查询
    // SELECT * FROM d1_pass_record ;
    sqliteService.d1_pass_record.find()
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.find({ a: 1, b: 2 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.find({ a: 1, b: 2, page: 1 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
    sqliteService.d1_pass_record.find({ a: 1, b: 2, page: 1, size: 1 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
    sqliteService.d1_pass_record.findByAAndBAndC(1, 2, 3)
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.findByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
    sqliteService.d1_pass_record.findByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
    // SELECT * FROM d1_pass_record ;
    sqliteService.d1_pass_record.findAll()
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.findAll({ a: 1, b: 2 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.findAll({ a: 1, b: 2, page: 1 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
    sqliteService.d1_pass_record.findAll({ a: 1, b: 2, page: 1, size: 1 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
    sqliteService.d1_pass_record.findAllByAAndBAndC(1, 2, 3)
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.findAllByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
    sqliteService.d1_pass_record.findAllByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
    // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ORDER BY a DESC,b ASC,c ASC LIMIT 1 OFFSET 1 ;
    sqliteService.d1_pass_record.findAllByAAndBAndCOrderByADescBAscCAsc(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
    // 删除
    // DELETE FROM d1_pass_record ;
    sqliteService.d1_pass_record.delete()
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.delete({ a: 1, b: 2 })
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.delete({ a: 1, b: 2, page: 1 })
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.delete({ a: 1, b: 2, page: 1, size: 1 })
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
    sqliteService.d1_pass_record.deleteByAAndBAndC(1, 2, 3)
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.deleteByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.deleteByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
    // DELETE FROM d1_pass_record ;
    sqliteService.d1_pass_record.deleteAll()
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.deleteAll({ a: 1, b: 2 })
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.deleteAll({ a: 1, b: 2, page: 1 })
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.deleteAll({ a: 1, b: 2, page: 1, size: 1 })
    // DELETE FROM d1_pass_record ;
    sqliteService.d1_pass_record.deleteAllByAAndBAndC(1, 2, 3)
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.deleteAllByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
    // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
    sqliteService.d1_pass_record.deleteAllByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
    // DELETE FROM d1_pass_record WHERE (a = 1 AND b = 2) OR (a = 1 AND b = 2 AND page = 1) OR (a = 1 AND b = 2 AND page = 1 AND size = 1);
    sqliteService.d1_pass_record.deleteInBatch([{ a: 1, b: 2 }, { a: 1, b: 2, page: 1 }, { a: 1, b: 2, page: 1, size: 1 }])
    // DELETE FROM d1_pass_record WHERE id IN (1 ,2 ,3 );
    sqliteService.d1_pass_record.deleteByIdInBatch([1, 2, 3])
    // 更新
    // UPDATE d1_pass_record SET a = 4 WHERE b = 1 AND c = 2 AND d = 3 ;
    sqliteService.d1_pass_record.updateAByBAndCAndD(1, 2, 3, 4)
    // UPDATE d1_pass_record SET a = 4,b = 5,c = 6 WHERE d = 1 AND e = 2 AND f = 3 ;
    sqliteService.d1_pass_record.updateAAndBAndCByDAndEAndF(1, 2, 3, 4, 5, 6)
    // 添加
    // INSERT INTO d1_pass_record VALUES (,,,,,,,0,0,,);
    sqliteService.d1_pass_record.save({ a: 1, b: 2 })
    // INSERT INTO d1_pass_record VALUES (,,,,,,,0,0,,), (,,,,,,,0,0,,);
    sqliteService.d1_pass_record.saveAll([{ a: 1, b: 2 }, { a: 1, b: 2 }])
    // 聚合
    // SELECT COUNT(*) FROM d1_pass_record ;
    sqliteService.d1_pass_record.count();
    // SELECT COUNT(*) FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
    sqliteService.d1_pass_record.countByAAndBAndC(1, 2, 3);
}

/**
 * 安全密钥查询
 * @param {string} code - 代码
 * @param {string} type - 类型
 * @param {string} id - 安全ID
 * @param {number} time - 时间
 * @param {string} key - 密钥
 * @param {string} index - 索引
 * @returns {array} 查询结果
 */
sqliteService.securityFindAllByCodeAndTypeAndTimeAndkey = function (code, type, id, time, key, index) {
    var query = `SELECT * FROM d1_security WHERE 1=1`
    if (code) {
        query += ` AND code = '${code}'`
    }
    if (type) {
        query += ` AND type = '${type}'`
    }
    if (id) {
        query += ` AND securityId = '${id}'`
    }
    if (index) {
        query += ` AND door = '${index}'`
    }
    if (key) {
        query += ` AND key = '${key}'`
    }
    if (time) {
        query += ` AND endTime >= '${time}'`
    }
    return sqlite.select(query)
}

export default sqliteService