export interface DBSchema {
  dbName: string;
  dbPath: string;
  tableName: string;
  columns: string;     // 建表 SQL
  indexes?: string[];  // 索引
  jsonFields?: string[]; // 需要自动 JSON 序列化的字段名
	primaryKey: string;          // 主键
	immutableFields?: string[];  // 只插入不更新的值
}

// 导入输入下面这个👇
// import { DBManager , DBSchema} from '@/src/service/DBManager/DBManager'

export class DBManager {
  private tableColumns!: string[];
  constructor(private config: DBSchema) {}

 // 外部调用的开启数据库的接口 
  async open(): Promise<void> {
	this.compileColumns();// 建立Scheme规范
    await this.connectDB(); // 先链接
    const sqlList = this.generateInitSqls(); // 后建表
    if (sqlList.length > 0) { await this.executeSql(sqlList);} // 再建索引
  }
  
  /* ================================= 基础 CRUD 封装 ================================= */
  
async Upsert(data: any): Promise<void> {
  const row = this.serialize(data);
  const keys = Object.keys(row);
  if (keys.length === 0) return;
  const values = keys.map(k => this.formatValue(row[k]));
  const insertSql =
    `INSERT INTO ${this.config.tableName} (${keys.join(',')})
     VALUES (${values.join(',')})`;

  const updateCols = keys.filter(
    k =>
      k !== this.config.primaryKey &&
      !this.config.immutableFields?.includes(k)
  );

  if (updateCols.length === 0) {
    // 只有主键或不可变字段，直接插
    return this.executeSql(insertSql);
  }

  const updateSet = updateCols
    .map(k => `${k} = excluded.${k}`)
    .join(',');

  const sql = `
    ${insertSql}
    ON CONFLICT(${this.config.primaryKey})
    DO UPDATE SET ${updateSet}
  `;
  return this.executeSql(sql);
}

async UpsertOnlyChanged(data: any): Promise<void> {
  const row = this.serialize(data);
  const keys = Object.keys(row);
  if (keys.length === 0) return;

  const pk = this.config.primaryKey;
  if (!pk || !keys.includes(pk)) {
    throw new Error('[DB] UpsertOnlyChanged 需要 primaryKey');
  }

  const values = keys.map(k => this.formatValue(row[k]));

  const insertSql =
    `INSERT INTO ${this.config.tableName} (${keys.join(',')})
     VALUES (${values.join(',')})`;

  const updateCols = keys.filter(
    k =>
      k !== pk &&
      !this.config.immutableFields?.includes(k)
  );

  if (updateCols.length === 0) {
    return this.executeSql(insertSql);
  }

  // SET 子句
  const setClause = updateCols
    .map(k => `${k} = excluded.${k}`)
    .join(', ');

  // 变化检测（核心）
  const diffWhere = updateCols
    .map(k => `${k} IS NOT excluded.${k}`)
    .join(' OR ');

  const sql = `
    ${insertSql}
    ON CONFLICT(${pk})
    DO UPDATE SET ${setClause}
    WHERE ${diffWhere}
  `;
  return this.executeSql(sql);
}



  /** 批量保存 */
  async saveBatch(list: any[]): Promise<void> {
    if (!list || list.length === 0) return;
    await this.executeSql("BEGIN TRANSACTION;");
    try {
      for (const item of list) { await this.Upsert(item);}
	  
      await this.executeSql("COMMIT;");
    } catch (e) {
      await this.executeSql("ROLLBACK;");
      throw e;
    }
  }

  /**
   * 批量查询多个 target 的最新 N 条记录
   * @param targetField 目标字段名 (如 'target_guid')
   * @param targetIds 目标ID数组
   * @param limit 每个 target 取多少条
   * @param orderByField 排序字段 (默认按此字段倒序)
   * @returns 所有结果的数组
   */
  async findBatchLatest<T>(
    targetField: string,
    targetIds: string[],
    limit: number = 50,
    orderByField: string = 'timestamp'
  ): Promise<T[]> {
    if (!targetIds || targetIds.length === 0) return [];
    
    // 过滤掉无效的 ID（undefined、null、空字符串）
    const validIds = targetIds.filter(id => id != null && id !== '');
    if (validIds.length === 0) return [];
    
    // 使用子查询 + UNION ALL，每个子查询需要用括号包起来
    const subQueries = validIds.map(id => {
      const safeId = id.replace(/'/g, "''");
      return `SELECT * FROM (
                SELECT * FROM ${this.config.tableName} 
                WHERE ${targetField} = '${safeId}' 
                ORDER BY ${orderByField} DESC 
                LIMIT ${limit}
              )`;
    });
    
    const sql = subQueries.join(' UNION ALL ');
    const res = await this.selectRaw(sql);
    return res.map(row => this.deserialize(row)) as T[];
  }

  /** 通用条件查询 */
  async find<T>(options: { 
    where?: Record<string, any>, 
    columns?: string[], // 支持只查特定列，如 ['key', 'local_path']
    orderBy?: string, 
    limit?: number, 
    offset?: number 
  }): Promise<T[]> {
    const colStr = options.columns && options.columns.length > 0 
      ? options.columns.join(',')  : '*';
    
    let sql = `SELECT ${colStr} FROM ${this.config.tableName}`;
    
    const whereClause = this.buildWhere(options.where);
    if (whereClause) sql += ` WHERE ${whereClause}`;

    if (options.orderBy) sql += ` ORDER BY ${options.orderBy}`;
    if (options.limit) sql += ` LIMIT ${options.limit}`;
    if (options.offset) sql += ` OFFSET ${options.offset}`;

    const res = await this.selectRaw(sql);
    return res.map(row => this.deserialize(row)) as T[];
  }

  /** 通用删除 */
  async delete(whereClause: string): Promise<void> {
    const sql = `DELETE FROM ${this.config.tableName} WHERE ${whereClause}`;
    return this.executeSql(sql);
  }

  /** 执行原生SQL查询 */
  async queryRaw<T>(sql: string): Promise<T[]> {
    const res = await this.selectRaw(sql);
    return res.map(row => this.deserialize(row)) as T[];
  }
  
    /** 统计数量 */
    async count(where?: Record<string, any>): Promise<number> {
      let sql = `SELECT COUNT(*) as total FROM ${this.config.tableName}`;
      const whereClause = this.buildWhere(where);
      if (whereClause) sql += ` WHERE ${whereClause}`;
  
      const res = await this.selectRaw(sql);
      return res.length > 0 ? res[0].total : 0;
    }
	
	  /** 
	   * 替代手动写 "DELETE FROM table WHERE key='...'"
	   */
	  async deleteBy(where: Record<string, any>): Promise<void> {
	    const whereClause = this.buildWhere(where);
	    if (!whereClause) {
	      console.warn('[DB] deleteBy 被调用但没有条件，操作被阻止以防止清空全表');
	      return;
	    }
	    const sql = `DELETE FROM ${this.config.tableName} WHERE ${whereClause}`;
	    return this.executeSql(sql);
	  }

  /* ================================= 内部工具 ================================= */
  
    private buildWhere(where?: Record<string, any>): string {
      if (!where) return '';
      const clauses: string[] = [];
      for (const key in where) {
        const val = where[key];
        if (val === undefined) continue;
        // 处理特殊符号，如 "age >": 18
        if (key.includes(' ') || key.includes('<') || key.includes('>') || key.includes('LIKE')) 
           clauses.push(`${key} ${this.formatValue(val)}`);
         else 
           clauses.push(`${key} = ${this.formatValue(val)}`);
      }
      return clauses.join(' AND ');
    }
  
  // 执行SQL语句
    public executeSql(sql: string | string[]): Promise<void> {
    return new Promise((resolve, reject) => {
      plus.sqlite.executeSql({
        name: this.config.dbName,
        sql: sql,
        success: () => resolve(),
        fail: (e) => {
          console.error(`[DB] SQL Error:`, e);
          reject(e);
        }
      });
    });
  }
  
    private selectRaw(sql: string): Promise<any[]> {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: this.config.dbName,
        sql,
        success: (res) => resolve(res as any[]),
        fail: (e) => {
          console.error("SQL查询失败:", sql, e);
          reject(e);
        }
      });
    });
  }

// 序列化数值
  private formatValue(val: any): string {
    if (typeof val === 'string') return `'${val.replace(/'/g, "''")}'`;
    if (typeof val === 'boolean') return val ? '1' : '0';
    if (val === null || val === undefined) return 'NULL';
    return String(val);
  }

  /** 整体序列化*/
private serialize(data: any): Record<string, any> {
  const row: any = {};
  for (const col of this.tableColumns) {
    let val = data[col];
    if (val === undefined) continue;
    // JSON 字段处理
    if (this.config.jsonFields?.includes(col)) {
      if (val !== null && typeof val === 'object') {  val = JSON.stringify(val);}
    }
    // boolean 统一
    if (typeof val === 'boolean') 
      val = val ? 1 : 0;
    row[col] = val;
  }
  return row;
}


  /** 整体反序列化 */
  private deserialize(row: any) {
    if (!row) return row;
    this.config.jsonFields?.forEach(f => {
      if (row[f] && typeof row[f] === 'string') {
        try {
          // 判断是否像 json 只有 {, [ 开头的才解析
          if (row[f].startsWith('{') || row[f].startsWith('[')) {
             row[f] = JSON.parse(row[f]);
          }
        } catch (e) {
          console.warn(`[DB] JSON 解析错误 ${f}:`, e);
        }
      }
    });
    return row;
  }
  
  // 初始化数据库SQL样式
    private generateInitSqls(): string[] {
      const sqls: string[] = [];
      const { tableName, columns, indexes } = this.config;
      sqls.push(`CREATE TABLE IF NOT EXISTS ${tableName} (${columns})`);
      if (indexes && indexes.length > 0) {
        indexes.forEach((fieldStr) => {
          const safeSuffix = fieldStr.replace(/[, ]+/g, '_');
          const indexName = `idx_${tableName}_${safeSuffix}`;
          sqls.push(`CREATE INDEX IF NOT EXISTS ${indexName} ON ${tableName} (${fieldStr})`);
        });
      }
      return sqls;
    }
	
	  private get realDbPath(): string {
	    const p = this.config.dbPath;
	    // 如果本来就以 .db/.sqlite/.sqlite3 结尾，就不再拼
	    return /(\.db|\.sqlite3?)$/i.test(p) ? p : `${p}.db`;
	  }
    
	  private compileColumns() {
		this.tableColumns = this.config.columns
		  .split(',')
		  .map(c => c.trim().split(/\s+/)[0]);
	  }

    private connectDB(): Promise<void> {
        return new Promise((resolve, reject) => {
        if (plus.sqlite.isOpenDatabase({ name: this.config.dbName, path: this.realDbPath  })) {
          return resolve();
        }
        plus.sqlite.openDatabase({
          name: this.config.dbName,
          path: this.realDbPath,
          success: () => resolve(),
          fail: (e) => reject(e)
        });
      });
    }
	
}