TTMusic/entry/src/main/ets/common/util/RdbUtils.ets

import relationalStore from '@ohos.data.relationalStore';
import { LogUtil, StrUtil } from '@pura/harmony-utils';
import Logger from './Logger';
import NetAxiosUtil from './NetAxiosUtil';

//

interface GeneratedObjectLiteralInterface_1 {
  tableName: string;
  sqlCreate: string;
  columns: string[];
}

export default class RdbUtils {
  private rdbStore: relationalStore.RdbStore | null = null;
  private tableName: string;
  private sqlCreateTable: string;
  private columns: Array<string>;
  static readonly RDB_TAG: string = 'heanup onecold RdbUtils';

  /**
   * Rdb数据库配置。
   * name:数据库名称
   * securityLevel：数据库安全级别
   */
  static readonly STORE_CONFIG: relationalStore.StoreConfig = {
    name: 'mediaDB.db',
    securityLevel: relationalStore.SecurityLevel.S1

  };

  /**
   * 表配置信息
   * tableName：表名称
   * sqlCreate：创建表的语句
   * columns：表的列项目
   */
  static readonly MEDIA_TABLE: GeneratedObjectLiteralInterface_1 = {
    tableName: 'mediaTable',
    sqlCreate: ' CREATE TABLE IF NOT EXISTS mediaTable (\n' +
      '        id TEXT NOT NULL,\n' +
      '        name TEXT NOT NULL,\n' +
      '        filePath TEXT NOT NULL UNIQUE,\n' +
      '        mtype INTEGER NOT NULL,\n' +
      '        videoSize INTEGER,\n' +
      '        cTime TEXT NOT NULL,\n' +
      '        size TEXT,\n' +
      '        artist TEXT,\n' +
      '        album TEXT,\n' +
      '        fileName TEXT ,\n' +
      '        parentPath TEXT,\n' +
      '        isFav INTEGER,\n' +
      '        pixelMapPath TEXT,\n' +
      '        duration TEXT,\n' +
      '        sampleRate TEXT,\n' +
      '        playCount INTEGER DEFAULT 0,\n' +
      '        lastPlayedStr TEXT,\n' +
      '        trackCount TEXT,\n' +
      '        lyricContent TEXT,\n' +
      '        md5Str TEXT,\n' +
      '        extra_json TEXT,\n' +
      '        pyStr TEXT,\n' +

      '        bit_rate TEXT,\n' +
      '        probe_score INTEGER DEFAULT 0,\n' +
      '        year TEXT,\n' +
      '        nb_streams INTEGER DEFAULT 0,\n' +
      '        nb_programs INTEGER DEFAULT 0,\n' +
      '        genre TEXT,\n' +
      '        track TEXT,\n' +

      '        bits_per_raw_sample TEXT,\n' +
      '        channels TEXT,\n' +
      '        channel_layout TEXT,\n' +
      '        start_time TEXT,\n' +

      '        ALBUMARTIST TEXT,\n' +
      '        COMPOSER TEXT,\n' +
      '        LYRICIST TEXT,\n' +
      '        COMMENT TEXT,\n' +
      '        disc TEXT,\n' +
      '        webdav_account_id TEXT,\n' +
      '        remote_rel_path TEXT,\n' +

      '        mimeType TEXT' +
      ')',
    columns: ['id', 'name', 'filePath','mtype', 'videoSize', 'cTime','size', 'artist', 'album',
      'fileName','parentPath','isFav','pixelMapPath',
      'duration',   'sampleRate','playCount',  'lastPlayedStr', 'trackCount',
      'lyricContent','md5Str','extra_json','pyStr','bit_rate','probe_score','year','nb_streams','nb_programs',
      'genre','track',  'bits_per_raw_sample','channels',  'channel_layout','start_time',
      'ALBUMARTIST',  'COMPOSER','LYRICIST','COMMENT','COMMENT','disc','webdav_account_id',
      'remote_rel_path',
      'mimeType']
  };

  /**
   * 歌单表配置
   */
  static readonly PLAYLIST_TABLE: GeneratedObjectLiteralInterface_1 = {
    tableName: 'playlistTable',
    sqlCreate: 'CREATE TABLE IF NOT EXISTS playlistTable (\n' +
      'id TEXT PRIMARY KEY NOT NULL,\n' +
      'name TEXT NOT NULL,\n' +
      'coverPath TEXT,\n' +
      'description TEXT,\n' +
      'createTime TEXT NOT NULL,\n' +
      'updateTime TEXT NOT NULL,\n' +
      'songCount INTEGER DEFAULT 0,\n' +
      'sortOrder INTEGER DEFAULT 0\n' +
      ')',
    columns: ['id', 'name', 'coverPath', 'description', 'createTime', 'updateTime', 'songCount', 'sortOrder']
  };

  /**
   * 歌单歌曲关联表配置
   */
  static readonly PLAYLIST_SONG_TABLE: GeneratedObjectLiteralInterface_1 = {
    tableName: 'playlistSongTable',
    sqlCreate: 'CREATE TABLE IF NOT EXISTS playlistSongTable (\n' +
      'id TEXT PRIMARY KEY NOT NULL,\n' +
      'playlistId TEXT NOT NULL,\n' +
      'songFilePath TEXT NOT NULL,\n' +
      'addTime TEXT NOT NULL,\n' +
      'sortOrder INTEGER DEFAULT 0,\n' +
      'UNIQUE(playlistId, songFilePath)\n' +
      ')',
    columns: ['id', 'playlistId', 'songFilePath', 'addTime', 'sortOrder']
  };

  constructor(tableName: string, sqlCreateTable: string, columns: Array<string>) {
    this.tableName = tableName;
    this.sqlCreateTable = sqlCreateTable;
    this.columns = columns;


  }

  getRdbStore(context:Context,callback: Function = () => {
  }) {
    if (!callback || typeof callback === 'undefined' || callback === undefined) {
      Logger.info(RdbUtils.RDB_TAG, 'getRdbStore() has no callback!');
      return;
    }
    if (this.rdbStore !== null) {
      Logger.info(RdbUtils.RDB_TAG, 'The rdbStore exists.');
      callback();
      return
    }

    relationalStore.getRdbStore(context, RdbUtils.STORE_CONFIG, (err, rdb) => {
      if (err) {
        Logger.error(RdbUtils.RDB_TAG, `gerRdbStore() failed, err: ${err}`);
        return;
      }
      this.rdbStore = rdb;

      // 先创建表（如果不存在）
      this.rdbStore.executeSql(this.sqlCreateTable);

      // 检查数据库版本并更新列
      try {
        // 检查表结构
        this.checkAndUpdateTableColumns();
        // Logger.info(RdbUtils.RDB_TAG, `数据库表结构检查完成`);
      } catch (e) {
        Logger.error(RdbUtils.RDB_TAG, `检查表结构时出错: ${e.message}`);
      }

      callback();
    });
  }

  /**
   * 检查并更新表列结构
   * 使用表信息查询和ALTER TABLE添加缺失的列
   */
  private checkAndUpdateTableColumns() {
    if (!this.rdbStore) {
      Logger.error(RdbUtils.RDB_TAG, `数据库连接未初始化`);
      return;
    }

    try {
      // 查询表信息获取现有列
      const tableInfoQuery = `PRAGMA table_info(${this.tableName})`;
      // 使用Promise模式代替回调
      this.rdbStore.executeSql(tableInfoQuery)
        .then(() => {
          // 定义应该存在的列及其类型
          const requiredColumns: Record<string, string> = {
            'duration': 'TEXT',
            'sampleRate': 'TEXT',
            'playCount': 'INTEGER DEFAULT 0',
            'lastPlayedStr': 'TEXT',
            'trackCount': 'TEXT',
            'lyricContent': 'TEXT',
            'md5Str': 'TEXT',
            'extra_json': 'TEXT',
            'mimeType': 'TEXT',
            'pyStr': 'TEXT',

            'bit_rate': 'TEXT',
            'probe_score': 'INTEGER DEFAULT 0',
            'year': 'TEXT',
            'nb_streams': 'INTEGER DEFAULT 0',
            'nb_programs': 'INTEGER DEFAULT 0',
            'genre': 'TEXT',
            'track': 'TEXT',

            'bits_per_raw_sample': 'TEXT',
            'channels': 'TEXT',
            'channel_layout': 'TEXT',
            'start_time': 'TEXT',

            'ALBUMARTIST': 'TEXT',
            'COMPOSER': 'TEXT',
            'LYRICIST': 'TEXT',
            'COMMENT': 'TEXT',
            'disc': 'TEXT',
            'webdav_account_id': 'TEXT',
            'remote_rel_path': 'TEXT',
          };

          // 逐个添加列，不依赖于检查结果
          if (this.rdbStore) {
            // 遍历映射
            const columnEntries = Object.keys(requiredColumns);
            for (let i = 0; i < columnEntries.length; i++) {
              const column = columnEntries[i];
              const type = requiredColumns[column];

              const alterSql = `ALTER TABLE ${this.tableName} ADD COLUMN ${column} ${type}`;
              try {
                // 对每个列使用Promise模式
                this.rdbStore.executeSql(alterSql)
                  .then(() => {
                    Logger.info(RdbUtils.RDB_TAG, `成功添加列: ${column}`);
                  })
                  .catch((alterErr: Error) => {
                    // 列可能已经存在，这是预期的错误
                    // Logger.info(RdbUtils.RDB_TAG, `列 ${column} 可能已存在: ${alterErr.message}`);
                  });
              } catch (e) {
                Logger.error(RdbUtils.RDB_TAG, `添加列 ${column} 出错: ${e.message}`);
              }
            }

            // 移除废弃的 pixelMapToString 列
            const dropPixelMapSql = `ALTER TABLE ${this.tableName} DROP COLUMN pixelMapToString`;
            try {
              this.rdbStore.executeSql(dropPixelMapSql)
                .then(() => {
                  Logger.info(RdbUtils.RDB_TAG, '已删除废弃列 pixelMapToString');
                })
                .catch(() => {
                  // 列不存在或数据库版本不支持 DROP COLUMN，忽略
                });
            } catch (dropErr) {
              Logger.warn(RdbUtils.RDB_TAG, `移除 pixelMapToString 列失败: ${(dropErr as Error).message}`);
            }

            // 设置数据库版本
            if (this.rdbStore) {
              this.rdbStore.version = 1;
              // Logger.info(RdbUtils.RDB_TAG, `数据库升级完成，版本设置为 1`);
            }
          }
        })
        .catch((err: Error) => {
          Logger.error(RdbUtils.RDB_TAG, `获取表信息失败: ${err.message}`);
        });
    } catch (e) {
      Logger.error(RdbUtils.RDB_TAG, `检查表结构时出错: ${e.message}`);
    }
  }

  //检查是否存在相同id的记录，存在则不插入，进一步判断pixelMapPath字段，如果旧值为空而新值不为空，则更新该字段。
  async insertData(data: relationalStore.ValuesBucket, callback: Function = () => {},cover_api?:string) {
    if (!callback || typeof callback !== 'function') {
      Logger.info(RdbUtils.RDB_TAG, 'insertData() has no valid callback!');
      return;
    }


    const valueBucket: relationalStore.ValuesBucket = data;
    const id = valueBucket.id;
    let titleNameV = valueBucket.name;
    let artistV = valueBucket.artist ;
    let pixelMapPathV = valueBucket.pixelMapPath

    if (this.rdbStore) {
      // Create a predicate to check if the record exists
      const predicates = new relationalStore.RdbPredicates(this.tableName);
      predicates.equalTo('id', id);

      // Query the database
      this.rdbStore.query(predicates, async (err, resultSet) => {
        if (err) {
          Logger.error(RdbUtils.RDB_TAG, `Query failed, err: ${err}`);
          callback(false);
          return;
        }

        if (resultSet.rowCount > 0) {
          Logger.info(RdbUtils.RDB_TAG, `Record with id ${id} already exists. Skipping insert.`);
          //检查是否存在相同id的记录，存在则不插入，进一步判断pixelMapPath字段，如果旧值为空而新值不为空，则更新该字段。
          //20250924不判断pixelMapPath字段了，如果存在不插入，则返回
          if (resultSet.goToFirstRow()) {
            callback(resultSet);
            resultSet.close();
            return;
            // const existingPixelMapPath = resultSet.getString(resultSet.getColumnIndex('pixelMapPath'));
            // const name = resultSet.getString(resultSet.getColumnIndex('name'));
            // const artist = resultSet.getString(resultSet.getColumnIndex('artist'));
            // if ((!existingPixelMapPath || existingPixelMapPath.trim() === '')&& cover_api!==undefined) {
            //   let newPixelMapPath =  await NetAxiosUtil.getLyricCover(name,artist,cover_api)
            //   console.info('onecold getLyricCover success. name= '+name);
            //   console.info('onecold getLyricCover success. newPixelMapPath= '+newPixelMapPath);
            //   if(StrUtil.isNotEmpty(newPixelMapPath)){
            //     // Update pixelMapPath
            //     const updateValues: relationalStore.ValuesBucket = { pixelMapPath: newPixelMapPath };
            //     this.updateData(predicates, updateValues, (updateSuccess: boolean) => {
            //       if (updateSuccess) {
            //         Logger.info(RdbUtils.RDB_TAG, `onecold Updated pixelMapPath for id ${id}.`);
            //       } else {
            //         Logger.error(RdbUtils.RDB_TAG, `onecold Failed to update pixelMapPath for id ${id}.`);
            //       }
            //       callback(updateSuccess);
            //     });
            //   }
            //
            //   resultSet.close();
            //   return;
            // }
          }

          Logger.info(RdbUtils.RDB_TAG, `No update needed for id ${id}.`);

          callback(false);
          return;
        }
        if (!this.rdbStore) {
          Logger.error(RdbUtils.RDB_TAG, 'RdbStore is not initialized.');
          callback(false);
          return;
        }

        //不存在相同记录 ，判断要不要去请求getLyricCover
        if(pixelMapPathV==undefined||pixelMapPathV.toString()==='' ){
          valueBucket.pixelMapPath = await NetAxiosUtil.getLyricCover(titleNameV+'',artistV+'',cover_api)

        }
        console.info('onecold getLyricCover pixelMapPathV1= '+pixelMapPathV);
        // Proceed with insert if record does not exist
        this.rdbStore.insert(this.tableName, valueBucket, (err, ret) => {
          if (err) {
            Logger.error(RdbUtils.RDB_TAG, `insertData() failed, err: ${err}`);
            callback(false);
            return;
          }
          Logger.info(RdbUtils.RDB_TAG, `insertData() finished: ${ret}`);
          callback(ret);
        });
      });
    }else{
      Logger.error(RdbUtils.RDB_TAG, 'RdbStore2 is not initialized.');
    }
  }


  deleteData(predicates: relationalStore.RdbPredicates, callback: Function = () => {
  }) {
    if (!callback || typeof callback === 'undefined' || callback === undefined) {
      Logger.info(RdbUtils.RDB_TAG, 'deleteData() has no callback!');
      return;
    }
    let resFlag: boolean = false;
    if (this.rdbStore) {
      this.rdbStore.delete(predicates, (err, ret) => {
        if (err) {
          Logger.info('xiaozheng 校正数据开始 deleteData5 = ' )
          Logger.error(RdbUtils.RDB_TAG, `deleteData() failed, err: ${err}`);
          callback(resFlag);
          return;
        }
        Logger.info('xiaozheng 校正数据开始 deleteData6 = ' )
        Logger.info(RdbUtils.RDB_TAG, `deleteData() finished: ${ret}`);
        callback(!resFlag);
      });
    }else{
      Logger.info(RdbUtils.RDB_TAG, 'deleteData rdbStore() 未初始化!');
    }
  }

  updateData(predicates: relationalStore.RdbPredicates, data: relationalStore.ValuesBucket, callback: Function = () => {
  }) {
    if (!callback || typeof callback === 'undefined' || callback === undefined) {
      Logger.info(RdbUtils.RDB_TAG, 'updateDate() has no callback!');
      return;
    }
    let resFlag: boolean = false;
    const valueBucket: relationalStore.ValuesBucket = data;
    if (this.rdbStore) {
      this.rdbStore.update(valueBucket, predicates, (err, ret) => {
        if (err) {
          Logger.error(RdbUtils.RDB_TAG, `updateData() failed, err: ${err}`);
          callback(resFlag);
          return;
        }
        console.info( `onecold updateData() finished: ${ret}`);
        callback(!resFlag);
      });
    }
  }


  query(predicates: relationalStore.RdbPredicates, callback: Function = () => {
  }) {
    if (!callback || typeof callback === 'undefined' || callback === undefined) {
      Logger.info(RdbUtils.RDB_TAG, 'query() has no callback!');
      return;
    }
    if (this.rdbStore) {
      this.rdbStore.query(predicates, this.columns, (err, resultSet) => {
        if (err) {
          Logger.error(RdbUtils.RDB_TAG, `query() failed, err:  ${err}`);
          return;
        }
        // Logger.info(RdbUtils.RDB_TAG, 'query() finished.');
        callback(resultSet);
        resultSet.close();
      });
    }
  }

}