/**
 * SQLite数据库操作模块
 * 提供数据库初始化、执行SQL语句、查询数据等功能
 */

import {
	dbConfig
} from './dbconfig.js';
import {
	createTables,
	initData
} from './sql.js';

/**
 * 初始化数据库
 * @returns {Promise} 返回Promise对象
 */
export const initDatabase = () => {
	return new Promise((resolve, reject) => {
		// 创建表
		createAllTables()
			.then(() => {
				// 初始化数据
				return initializeData();
			})
			.then(() => {
				resolve();
			})
			.catch(err => {
				console.error('数据库初始化失败', err);
				reject(err);
			});

	});
};

/**
 * 创建所有表
 * @returns {Promise} 返回Promise对象
 */
const createAllTables = () => {
	return new Promise(async (resolve, reject) => {
		try {
			for (const sql of createTables) {
				await executeSql(sql);
			}
			resolve();
		} catch (error) {
			reject(error);
		}
	});
};

/**
 * 初始化数据
 * @returns {Promise} 返回Promise对象
 */
const initializeData = () => {
	return new Promise(async (resolve, reject) => {
		try {
			for (const sql of initData) {
				await executeSql(sql);
			}
			resolve();
		} catch (error) {
			reject(error);
		}
	});
};

/**
 * 执行SQL语句
 * @param {string} sql SQL语句
 * @param {Array} params 参数数组
 * @returns {Promise} 返回Promise对象
 */
export const executeSql = (sql, params = []) => {
	return new Promise((resolve, reject) => {
		try {
			openDatabase()
			let exeSql = formatSQL(sql, params);
			// console.log(exeSql)
			plus.sqlite.executeSql({
				name: dbConfig.dbName,
				sql: exeSql,
				success: (e) => {
					resolve(e);
				},
				fail: (e) => {
					console.error('SQL执行失败', sql, params, e);
					reject(e);
				}
			});
		} catch (e) {
			console.log('executeSql执行SQL语句失败', e)
		} finally {
			closeDatabase();
		}

	});
};

/**
 * 查询数据
 * @param {string} sql SQL查询语句
 * @param {Array} params 参数数组
 * @returns {Promise<Array>} 返回查询结果数组
 */
export const query = (sql, params = []) => {
	return new Promise((resolve, reject) => {
		let exeSql = formatSQL(sql, params);
		try {
			// console.log(exeSql)
			openDatabase()
			plus.sqlite.selectSql({
				name: dbConfig.dbName,
				sql: exeSql,
				success: (e) => {
					resolve(e);
				},
				fail: (e) => {
					console.error('查询失败', sql, params, e);
					reject(e);
				}
			});
		} catch (e) {
			console.log(`query执行SQL语句失败【${exeSql}】`, e)
		} finally {
			closeDatabase();
		}
	});
};

/**
 * 开始事务
 * @returns {Promise} 返回Promise对象
 */
export const beginTransaction = () => {
	return executeSql('BEGIN TRANSACTION');
};

/**
 * 提交事务
 * @returns {Promise} 返回Promise对象
 */
export const commitTransaction = () => {
	return executeSql('COMMIT');
};

/**
 * 回滚事务
 * @returns {Promise} 返回Promise对象
 */
export const rollbackTransaction = () => {
	return executeSql('ROLLBACK');
};

/**
 * 插入数据并返回插入的ID
 * @param {string} tableName 表名
 * @param {Object} data 要插入的数据对象
 * @returns {Promise<number>} 返回插入的ID
 */
export const insert = async (tableName, data) => {
	const keys = Object.keys(data);
	const values = Object.values(data);
	const placeholders = keys.map(() => '?').join(',');

	// console.log(data)
	const sql = `INSERT INTO ${tableName} (${keys.join(',')}) VALUES (${placeholders})`;

	await executeSql(sql, values);

	// 获取最后插入的ID
	const result = await query("SELECT * from sqlite_sequence where name = '" + tableName + "'");
	return result[0].seq;
};

/**
 * 更新数据
 * @param {string} tableName 表名
 * @param {Object} data 要更新的数据对象
 * @param {string} whereClause WHERE子句
 * @param {Array} whereParams WHERE子句参数
 * @returns {Promise} 返回Promise对象
 */
export const update = (tableName, data, whereClause, whereParams = []) => {
	const keys = Object.keys(data);
	const values = Object.values(data);

	const setClause = keys.map(key => `${key} = ?`).join(',');

	const sql = `UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`;

	return executeSql(sql, [...values, ...whereParams]);
};

/**
 * 删除数据
 * @param {string} tableName 表名
 * @param {string} whereClause WHERE子句
 * @param {Array} whereParams WHERE子句参数
 * @returns {Promise} 返回Promise对象
 */
export const deleteData = (tableName, whereClause, whereParams = []) => {
	const sql = `DELETE FROM ${tableName} WHERE ${whereClause}`;

	return executeSql(sql, whereParams);
};

// 打开/创建数据库
const openDatabase = () => {
	// #ifdef APP-PLUS
	return new Promise((resolve, reject) => {
		plus.sqlite.openDatabase({
			name: dbConfig.dbName,
			path: dbConfig.dbPath,
			version: dbConfig.dbVersion,
			success: (e) => {
				resolve(e)
			},
			fail: (e) => reject(`数据库打开失败: ${JSON.stringify(e)}`)
		});
	});
	// #endif
}

/**
 * 关闭数据库
 * @returns {Promise} 返回Promise对象
 */
const closeDatabase = () => {
	return new Promise((resolve, reject) => {
		plus.sqlite.closeDatabase({
			name: dbConfig.dbName,
			path: dbConfig.dbPath,
			success: (e) => resolve(e),
			fail: (e) => reject(`数据库关闭失败: ${JSON.stringify(e)}`)
		});
	});
}

/**
 * 检查数据库是否已初始化
 * @returns {boolean} 是否已初始化
 */
export const isDatabaseInitialized = () => {
	return db !== null;
};

/**
 * 获取数据库信息
 * @returns {Object} 数据库信息
 */
export const getDatabaseInfo = () => {
	return {
		name: dbConfig.dbName,
		version: dbConfig.dbVersion,
		path: dbConfig.dbPath
	};
};

// SQL格式化工具函数
// SQL格式化工具函数
const formatSQL = (sql, params) => {
	try {
		return sql.replace(/\?/g, (match) => {
			const value = params.shift();
			// 处理不同类型的数据
			if (value === null || value === undefined) return 'NULL';
			if (typeof value === 'string') return `'${value.replace(/'/g, "''")}'`;
			if (value instanceof Date) return `'${value.toISOString()}'`;
			return value;
		});
	} catch (e) {
		console.error('SQL格式化错误', e);
	}

}