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

import relationalStore from '@ohos.data.relationalStore';
import { LogUtil, StrUtil } from '@pura/harmony-utils';
import { VideoItem } from '../../viewmodel/VideoItem';
import { CommonConstants } from '../constants/CommonConstants';
import Logger from './Logger';
import RdbUtils from './RdbUtils';
import { AudioQuality, Utility } from './Utility';

/**
 * 数据库字段常量接口定义
 */
interface DBColumnsInterface {
  ID: string;
  NAME: string;
  FILE_PATH: string;
  TYPE: string;
  VIDEO_SIZE: string;
  C_TIME: string;
  PARENT_PATH: string;
  IS_FAV: string;
  PIXEL_MAP_PATH: string;
  ARTIST: string;
  ALBUM: string;
  FILE_NAME: string;
  SIZE: string;
  DURATION: string;
  MIME_TYPE: string;
  TRACK_COUNT: string;
  SAMPLE_RATE: string;
  LAST_PLAYED_STR: string;
  PLAY_COUNT: string;
  LYRIC_CONTENT: string;
}

/**
 * 数据库字段常量，避免硬编码
 */
const DB_COLUMNS: DBColumnsInterface = {
  ID: 'id',
  NAME: 'name',
  FILE_PATH: 'filePath',
  TYPE: 'mtype',
  VIDEO_SIZE: 'videoSize',
  C_TIME: 'cTime',
  PARENT_PATH: 'parentPath',
  IS_FAV: 'isFav',
  PIXEL_MAP_PATH: 'pixelMapPath',
  ARTIST: 'artist',
  ALBUM: 'album',
  FILE_NAME: 'fileName',
  SIZE: 'size',
  DURATION: 'duration',
  MIME_TYPE: 'mimeType',
  TRACK_COUNT: 'trackCount',
  SAMPLE_RATE: 'sampleRate',
  LAST_PLAYED_STR: 'lastPlayedStr',
  PLAY_COUNT: 'playCount',
  LYRIC_CONTENT: 'lyricContent'
};

export default class MediaTable {
  private accountTable = new RdbUtils(RdbUtils.MEDIA_TABLE.tableName, RdbUtils.MEDIA_TABLE.sqlCreate,
    RdbUtils.MEDIA_TABLE.columns);

  constructor(context:Context,callback: Function = () => {
  }) {
    this.accountTable.getRdbStore(context,callback);
  }

  getRdbStore(context:Context,callback: Function = () => {
  }) {
    this.accountTable.getRdbStore(context,callback);
  }


  insert(item: VideoItem, callback: Function,cover_api?:string) {
    const valueBucket: relationalStore.ValuesBucket = generateBucket(item);
    this.accountTable.insertData(valueBucket, callback,cover_api);
  }

  deleteData(item: VideoItem, callback: Function) {
    let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('id', item.id);
    this.accountTable.deleteData(predicates, callback);
  }

  deleteDataFilePath(filePath: string, callback: Function) {
    let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('filePath', filePath);
    this.accountTable.deleteData(predicates, callback);
  }

  deleteDataForParentPath(parentPath:string, callback: Function) {
    let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('parentPath', parentPath);
    this.accountTable.deleteData(predicates, callback);
  }

  //更新音乐封面地址
  public updatePixelMapPath(filePath: string, newPixelMapPath: string, callback: Function) {
    // Step 1: 构建查询条件验证文件存在性
    const queryPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    queryPredicates.equalTo('filePath',  filePath);

    // Step 2: 执行存在性验证
    this.accountTable.query(queryPredicates,  (resultSet: relationalStore.ResultSet) => {
      if (resultSet.rowCount  === 0) {
        callback(false, 'Error: Target file not found in database');
        resultSet.close();
        return;
      }
      resultSet.close();

      // Step 3: 构建更新条件与数据
      const updatePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      updatePredicates.equalTo('filePath',  filePath);
      const valueBucket: relationalStore.ValuesBucket = {
        pixelMapPath: newPixelMapPath
      };

      // Step 4: 执行原子化更新操作
      this.accountTable.updateData(updatePredicates,  valueBucket, (success: boolean) => {
        callback(success, success ? null : 'Database update operation failed');
      });
    });
  }


  updateData(item: VideoItem, callback: Function) {
    const valueBucket: relationalStore.ValuesBucket = generateBucket(item);
    let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('id', item.id);
    this.accountTable.updateData(predicates, valueBucket, callback);
  }

  //编辑歌曲的信息更新数据库
  public updateMediaInfo(filePath: string, title: string, artist: string, album: string,
    lyricContent:string,year:string,genre:string,track:string,
    ALBUMARTIST:string,COMPOSER:string,LYRICIST:string,COMMENT:string,disc:string,
    callback: Function) {
    if (!callback || typeof callback !== 'function') {
      Logger.info(RdbUtils.RDB_TAG, 'updateMediaInfo() has no valid callback!');
      return;
    }

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

    // Step 1: Create a predicate to find the record by filePath
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('filePath', filePath);

    // Step 2: Query the database to check if the record exists
    this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
      if (resultSet.rowCount === 0) {
        Logger.info(RdbUtils.RDB_TAG, `No record found with filePath ${filePath}.`);
        callback(false);
        resultSet.close();
        return;
      }

      // Step 3: Prepare the values to update
      const valuesToUpdate: relationalStore.ValuesBucket = {};
      if (title !== '') {
        valuesToUpdate.name = title;
      }
      if (artist !== '') {
        valuesToUpdate.artist = artist;
      }
      if (album !== '') {
        valuesToUpdate.album = album;
      }
      if (lyricContent !== '') {
        valuesToUpdate.lyricContent = lyricContent;
      }
      if (year !== '') {
        valuesToUpdate.year = year;
      }
      if (genre !== '') {
        valuesToUpdate.genre = genre;
      }
      if (track !== '') {
        valuesToUpdate.track = track;
      }

      if (ALBUMARTIST !== '') {
        valuesToUpdate.ALBUMARTIST = ALBUMARTIST;
      }
      if (COMPOSER !== '') {
        valuesToUpdate.COMPOSER = COMPOSER;
      }
      if (LYRICIST !== '') {
        valuesToUpdate.LYRICIST = LYRICIST;
      }
      if (COMMENT !== '') {
        valuesToUpdate.COMMENT = COMMENT;
      }
      if (disc !== '') {
        valuesToUpdate.disc = disc;
      }

      resultSet.close();

      // Step 4: Update the record if there are values to update
      if (Object.keys(valuesToUpdate).length > 0) {
        this.accountTable.updateData(predicates, valuesToUpdate, (success: boolean) => {
          callback(success, success ? null : 'Database update operation failed');
        });
      } else {
        Logger.info(RdbUtils.RDB_TAG, 'No fields to update.');
        callback(false);
      }
    });
  }



  //更新重命名数据操作
  public updateRename(newName: string, oldPath: string, newPath: string, callback: Function) {
    // Step 1: 查询原始记录
    const queryPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    queryPredicates.equalTo('filePath',  oldPath);

    this.accountTable.query(queryPredicates,  (resultSet: relationalStore.ResultSet) => {
      if (resultSet.rowCount  === 0) {
        callback(false, 'Error: File not found');
        return;
      }
      resultSet.goToFirstRow();


      // Step 3: 构建更新数据
      const currentName = resultSet.getString(resultSet.getColumnIndex('name'));
      const currentFileName = resultSet.getString(resultSet.getColumnIndex('fileName'));

      let obj: relationalStore.ValuesBucket = {};
      obj.id = newPath
      obj.filePath = newPath;
      if (currentName === currentFileName) {
        obj.name = newName;
        obj.fileName = newName;
      } else {
        obj.fileName = newName;
      }
      obj.mtype   = resultSet.getDouble(resultSet.getColumnIndex('mtype'));
      obj.videoSize  = resultSet.getDouble(resultSet.getColumnIndex('videoSize'));
      obj.cTime = resultSet.getString(resultSet.getColumnIndex('cTime'));
      obj.parentPath = resultSet.getString(resultSet.getColumnIndex('parentPath'));
      // obj.pixelMapToString = resultSet.getString(resultSet.getColumnIndex('pixelMapToString'));
      obj.artist  = resultSet.getString(resultSet.getColumnIndex('artist'));
      obj.album  = resultSet.getString(resultSet.getColumnIndex('album'));
      obj.isFav  = resultSet.getDouble(resultSet.getColumnIndex('isFav'));
      obj.pixelMapPath = resultSet.getString(resultSet.getColumnIndex('pixelMapPath'));

      obj.duration  = resultSet.getString(resultSet.getColumnIndex('duration'));
      obj.mimeType  = resultSet.getString(resultSet.getColumnIndex('mimeType'));
      obj.trackCount  = resultSet.getString(resultSet.getColumnIndex('trackCount'));
      obj.sampleRate  = resultSet.getString(resultSet.getColumnIndex('sampleRate'));
      obj.lastPlayedStr  = resultSet.getString(resultSet.getColumnIndex('lastPlayedStr'));
      obj.playCount  = resultSet.getDouble(resultSet.getColumnIndex('playCount'));
      obj.lyricContent  = resultSet.getString(resultSet.getColumnIndex('lyricContent'));
      obj.md5Str  = resultSet.getString(resultSet.getColumnIndex('md5Str'));
      obj.extra_json  = resultSet.getString(resultSet.getColumnIndex('extra_json'));
      obj.pyStr  = resultSet.getString(resultSet.getColumnIndex('pyStr'));

      obj.bit_rate  = resultSet.getString(resultSet.getColumnIndex('bit_rate'));
      obj.probe_score  = resultSet.getDouble(resultSet.getColumnIndex('probe_score'));
      obj.year  = resultSet.getString(resultSet.getColumnIndex('year'));
      obj.nb_streams  = resultSet.getDouble(resultSet.getColumnIndex('nb_streams'));
      obj.nb_programs  = resultSet.getDouble(resultSet.getColumnIndex('nb_programs'));
      obj.genre  = resultSet.getString(resultSet.getColumnIndex('genre'));
      obj.track  = resultSet.getString(resultSet.getColumnIndex('track'));

      obj.bits_per_raw_sample  = resultSet.getString(resultSet.getColumnIndex('bits_per_raw_sample'));
      obj.channels  = resultSet.getString(resultSet.getColumnIndex('channels'));
      obj.channel_layout  = resultSet.getString(resultSet.getColumnIndex('channel_layout'));
      obj.start_time  = resultSet.getString(resultSet.getColumnIndex('start_time'));

      obj.ALBUMARTIST  = resultSet.getString(resultSet.getColumnIndex('ALBUMARTIST'));
      obj.COMPOSER  = resultSet.getString(resultSet.getColumnIndex('COMPOSER'));
      obj.LYRICIST  = resultSet.getString(resultSet.getColumnIndex('LYRICIST'));
      obj.COMMENT  = resultSet.getString(resultSet.getColumnIndex('COMMENT'));
      obj.disc  = resultSet.getString(resultSet.getColumnIndex('disc'));

      const valueBucket: relationalStore.ValuesBucket = obj
      // Step 4: 执行更新
      const updatePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      updatePredicates.equalTo('filePath',  oldPath);

      this.accountTable.updateData(updatePredicates,  valueBucket, (success: boolean) => {
        callback(success, success ? null : 'Update failed');
      });
      resultSet.close()
    });
  }

  // 根据isFav查询数据
  public queryByisFav(isFav: number, callback: (result: VideoItem[]) => void) {
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('isFav', isFav);

    this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
      const result = this.parseResultSetToVideoItems(resultSet);
      callback(result);
    });
  }

  // 根据filePath更新isFav的值
  public updateIsFavByFilePath(filePath: string, isFav: number, callback: (success: boolean, error?: string) => void) {
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('filePath', filePath);

    this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
      if (resultSet.rowCount === 0) {
        callback(false, 'Error: File not found');
        resultSet.close();
        return;
      }
      resultSet.close();

      const valueBucket: relationalStore.ValuesBucket = { isFav: isFav };
      this.accountTable.updateData(predicates, valueBucket, (success: boolean) => {
        callback(success, success ? '' : 'Update failed');
      });
    });
  }

  // 查询全部，或者某个id(查询的字段，回调，是否查询全部)
  query(id: number, callback: Function, isAll: boolean = true) {
    let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    if (!isAll) {
      predicates.equalTo('id', id);
    }
    this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
      let count: number = resultSet.rowCount;
      if (count === 0 || typeof count === 'string') {
        console.log(`${RdbUtils.RDB_TAG}` + 'Query no results!');
        callback([]);
      } else {


        const result = this.parseResultSetToVideoItems(resultSet);
        callback(result);
      }
    });
  }


  // 新增方法：根据parentPath查询数据
  public queryByParentPath(path: string, callback: (result: VideoItem[]) => void) {
    try {
      const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      predicates.equalTo('parentPath',  path);
      LogUtil.info('onecold parentPath = '+path)
      // 2. 执行查询并处理结果
      this.accountTable.query(predicates,  (resultSet: relationalStore.ResultSet) => {
        // 3. 复用已有的解析逻辑
        const result = this.parseResultSetToVideoItems(resultSet);
        callback(result);
      });
    }catch (err) {
      Logger.error(` onecold testtag queryByParentPath: ${err.code}  - ${err.message}`);

    }
    // 1. 构建查询条件

  }

  // 查询所有艺术家及其歌曲（返回Map结构：artist -> VideoItem[]）
  public queryArtistsWithSongs(callback: (result: Map<string, VideoItem[]>) => void) {
    // 1. 查询去重的艺术家列表（非空）
    const artistPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    artistPredicates.isNotNull('artist').distinct();
    this.accountTable.query(artistPredicates, (resultSet: relationalStore.ResultSet) => {
      const artists: string[] = this.parseDistinctColumn(resultSet,  'artist');

      // 2. 遍历每个艺术家，查询其歌曲
      const resultMap = new Map<string, VideoItem[]>();
      let processedCount = 0;
      if (artists.length  === 0) {
        callback(resultMap);
        return;
      }

      artists.forEach(artist  => {
        const songPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        songPredicates.equalTo('artist',  artist);
        this.accountTable.query(songPredicates,  (songResultSet: relationalStore.ResultSet) => {
          const songs = this.parseResultSetToVideoItems(songResultSet);
          resultMap.set(artist,  songs);
          processedCount++;

          // 3. 全部查询完成后回调
          if (processedCount === artists.length)  {
            callback(resultMap);
          }
        });
      });
    });
  }




  // 查询所有专辑及其歌曲（返回Map结构：album -> VideoItem[]）
  public queryAlbumsWithSongs(callback: (result: Map<string, VideoItem[]>) => void) {
    // 1. 查询去重的专辑列表（非空）
    const albumPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    albumPredicates.isNotNull('album').distinct();
    this.accountTable.query(albumPredicates, (resultSet: relationalStore.ResultSet) => {
      const albums: string[] = this.parseDistinctColumn(resultSet,  'album');

      // 2. 遍历每个专辑，查询其歌曲
      const resultMap = new Map<string, VideoItem[]>();
      let processedCount = 0;
      if (albums.length  === 0) {
        callback(resultMap);
        return;
      }

      albums.forEach(album  => {
        const songPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        songPredicates.equalTo('album',  album);
        this.accountTable.query(songPredicates,  (songResultSet: relationalStore.ResultSet) => {
          const songs = this.parseResultSetToVideoItems(songResultSet);
          resultMap.set(album,  songs);
          processedCount++;

          // 3. 全部查询完成后回调
          if (processedCount === albums.length)  {
            callback(resultMap);
          }
        });
      });
    });
  }



  // 解析去重列数据（如artist/album）
  private parseDistinctColumn(resultSet: relationalStore.ResultSet, columnName: string): string[] {
    const uniqueValues = new Set<string>();  // 使用Set特性自动去重
    if (resultSet.rowCount  > 0) {
      resultSet.goToFirstRow();
      for (let i = 0; i < resultSet.rowCount;  i++) {
        const value = resultSet.getString(resultSet.getColumnIndex(columnName))?.trim();   // 处理空格
        if (value) {  // 过滤空值
          uniqueValues.add(value);
        }
        if (i < resultSet.rowCount  - 1) {  // 避免最后一行越界
          resultSet.goToNextRow();
        }
      }
    }
    resultSet.close();
    return Array.from(uniqueValues);   // Set转数组
  }

  // 将ResultSet解析为VideoItem数组（复用原有逻辑）
  private parseResultSetToVideoItems(resultSet: relationalStore.ResultSet): VideoItem[] {
    const items: VideoItem[] = [];

    try {
      // 检查结果集是否有效
      if (resultSet && resultSet.rowCount > 0) {
        while (resultSet.goToNextRow()) {
          const item = this.buildVideoItem(resultSet);
          items.push(item);
        }
      }
    } catch (err) {
      Logger.error(`解析结果集出错: ${err.message}`);
    } finally {
      // 确保结果集被关闭
      if (resultSet) {
        resultSet.close();
      }
    }

    return items;
  }

  // 根据filePath更新lastPlayedStr的值同时playCount值加1
  public updateLastPlayedStrByFilePath(filePath: string, lastPlayedStr: string, callback: (success: boolean, error?: string) => void) {
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('filePath',   filePath);

    this.accountTable.query(predicates,   (resultSet: relationalStore.ResultSet) => {
      if (resultSet.rowCount   === 0) {
        callback(false, 'Error: File not found');
        resultSet.close();
        return;
      }

      // 获取当前的 playCount 值
      let currentPlayCount = 0;
      if (resultSet.goToFirstRow())  {
        currentPlayCount = resultSet.getLong(resultSet.getColumnIndex('playCount'));
      }

      resultSet.close();

      // 计算新的 playCount 值
      const newPlayCount = currentPlayCount + 1;

      // 准备要更新的值
      const valueBucket: relationalStore.ValuesBucket = {
        lastPlayedStr: lastPlayedStr,
        playCount: newPlayCount
      };

      // 更新数据
      this.accountTable.updateData(predicates,   valueBucket, (success: boolean) => {
        callback(success, success ? '' : 'Update failed');
      });
    });
  }

  // 根据最近播放时间查询指定数量的记录
  public queryRecentPlayedRecords(count: number, callback: (result: VideoItem[]) => void) {
    try {
      // 1. 构建查询条件：按lastPlayedStr降序排列，限制返回条数，且lastPlayedStr不为空
      const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      // 添加筛选条件，确保lastPlayedStr不为空
      predicates.isNotNull('lastPlayedStr');
      predicates.notEqualTo('lastPlayedStr',  ''); // 排除空字符串
      predicates.notEqualTo('mtype', CommonConstants.TYPE_LOCK);
      // 使用 orderByDesc 方法进行降序排序
      predicates.orderByDesc('lastPlayedStr');
      // 使用 limit 方法限制返回的记录数量
      predicates.limitAs(count);

      // 2. 执行查询并处理结果
      this.accountTable.query(predicates,  (resultSet: relationalStore.ResultSet) => {
        // 3. 复用已有的解析逻辑
        const result = this.parseResultSetToVideoItems(resultSet);
        callback(result);
      });
    } catch (err) {
      Logger.error(`queryRecentPlayedRecords   error: ${err.code}   - ${err.message}`);
      callback([]);
    }
  }

  // 清空播放历史记录
  public clearPlayHistory(callback: (success: boolean, error?: string) => void) {
    // 1. 构建查询条件：筛选lastPlayedStr非空的记录
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.isNotNull('lastPlayedStr');

    // 2. 准备更新数据：将lastPlayedStr设为空字符串
    const valueBucket: relationalStore.ValuesBucket = {
      lastPlayedStr: ''
    };

    // 3. 执行批量更新操作
    this.accountTable.updateData(predicates,   valueBucket, (success: boolean) => {
      // 将 null 替换为 undefined
      callback(success, success ? 'Clear play history success' : 'Clear play history operation failed');
    });
  }

  private buildVideoItem(rs: relationalStore.ResultSet): VideoItem {
    // 添加空值保护
    const safeGet = (col: string) => {
      const index = rs.getColumnIndex(col);
      return index >= 0 ? rs.getString(index) || '' : '';
    };

    const safeGetNumber = (col: string) => {
      const index = rs.getColumnIndex(col);
      return index >= 0 ? rs.getDouble(index) || 0 : 0;
    };

    let item = new VideoItem(
      safeGet(DB_COLUMNS.NAME),
      safeGet(DB_COLUMNS.ID),
      safeGet(DB_COLUMNS.FILE_PATH),
      safeGetNumber(DB_COLUMNS.TYPE),
      safeGetNumber(DB_COLUMNS.VIDEO_SIZE),
      safeGet(DB_COLUMNS.C_TIME),
      undefined,
      safeGet(DB_COLUMNS.SIZE),
      safeGet(DB_COLUMNS.PIXEL_MAP_PATH),
      safeGet(DB_COLUMNS.ARTIST),
      safeGet(DB_COLUMNS.ALBUM),
      safeGet(DB_COLUMNS.FILE_NAME)
    );

    // 设置额外属性，添加安全检查
    item.isFav = safeGetNumber(DB_COLUMNS.IS_FAV);
    item.duration = safeGet(DB_COLUMNS.DURATION);
    item.mimeType = safeGet(DB_COLUMNS.MIME_TYPE);
    item.trackCount = safeGet(DB_COLUMNS.TRACK_COUNT);
    item.sampleRate = safeGet(DB_COLUMNS.SAMPLE_RATE);
    item.lastPlayedStr = safeGet(DB_COLUMNS.LAST_PLAYED_STR);
    item.playCount = safeGetNumber(DB_COLUMNS.PLAY_COUNT);
    item.lyricContent = safeGet(DB_COLUMNS.LYRIC_CONTENT);

    item.md5Str = safeGet('md5Str');
    item.extra_json = safeGet('extra_json');
    item.pyStr = safeGet('pyStr');

    item.bit_rate = safeGet('bit_rate');
    item.probe_score = safeGetNumber('probe_score');
    item.year = safeGet('year');
    item.nb_streams = safeGetNumber('nb_streams');
    item.nb_programs = safeGetNumber('nb_programs');
    item.genre = safeGet('genre');
    item.track = safeGet('track');

    item.bits_per_raw_sample = safeGet('bits_per_raw_sample');
    item.channels = safeGet('channels');
    item.channel_layout = safeGet('channel_layout');
    item.start_time = safeGet('start_time');

    item.ALBUMARTIST = safeGet('ALBUMARTIST');
    item.COMPOSER = safeGet('COMPOSER');
    item.LYRICIST = safeGet('LYRICIST');
    item.COMMENT = safeGet('COMMENT');
    item.disc = safeGet('disc');

    return item;
  }


  /**
   * 查询播放次数大于0的记录，并按播放次数降序排列
   * @param limitCount 限制返回的记录数量，0表示返回全部
   * @param callback 回调函数，返回VideoItem数组
   */
  public queryByPlayCountDesc(limitCount: number = 0, callback: (result: VideoItem[]) => void): void {
    try {
      // 1. 构建查询条件：playCount > 0，按playCount降序排列
      const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      predicates.greaterThan('playCount',  0);  // 播放次数大于0
      predicates.orderByDesc('playCount');      // 按播放次数降序排列

      // 只有当limitCount大于0时才设置限制
      if (limitCount > 0) {
        predicates.limitAs(limitCount);
      }

      // 2. 执行查询
      this.accountTable.query(predicates,  (resultSet: relationalStore.ResultSet) => {
        // 3. 解析结果集
        const result = this.parseResultSetToVideoItems(resultSet);
        callback(result);
      });
    } catch (err) {
      Logger.error(`queryByPlayCountDesc  error: ${err.code}  - ${err.message}`);
      callback([]);  // 发生错误时返回空数组
    }
  }


  public countSongsByQualityOptimized(callback: (result: Record<AudioQuality, number>) => void): void {
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.in('md5Str',  [AudioQuality.LQ, AudioQuality.SQ, AudioQuality.HQ, AudioQuality.HIRES, AudioQuality.HR]);

    this.accountTable.query(predicates,  (resultSet: relationalStore.ResultSet) => {
      const result: Record<AudioQuality, number> = {
        [AudioQuality.LQ]: 0,
        [AudioQuality.SQ]: 0,
        [AudioQuality.HQ]: 0,
        [AudioQuality.HIRES]: 0 , // 只保留HR，HIRES的统计将合并到这里
        [AudioQuality.HR]: 0  // 只保留HR，HIRES的统计将合并到这里
      };

      if (resultSet.rowCount  > 0) {
        resultSet.goToFirstRow();
        do {
          const quality = resultSet.getString(resultSet.getColumnIndex('md5Str'))  as AudioQuality;
          if (quality === AudioQuality.LQ ||
            quality === AudioQuality.SQ ||
            quality === AudioQuality.HQ ||
            quality === AudioQuality.HIRES ||  // HIRES的统计将被合并到HR
            quality === AudioQuality.HR) {

            // 如果质量是HIRES或HR，都统计到HR中
            const targetKey = (quality === AudioQuality.HIRES || quality === AudioQuality.HR)
              ? AudioQuality.HR
              : quality;

            result[targetKey]++;
          }
        } while (resultSet.goToNextRow());
      }

      resultSet.close();
      callback(result);
    });
  }


  /**
   * Get lyric content by file path (Promise version)
   * @param filePath The file path to query
   * @returns Promise that resolves with the lyric content (string) or null if not found
   */
  public getLyricContentByFilePath(filePath: string): Promise<string | null> {
    return new Promise((resolve, reject) => {
      // Create query predicates
      const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      predicates.equalTo(DB_COLUMNS.FILE_PATH,  filePath);

      // Execute the query
      this.accountTable.query(predicates,  (resultSet: relationalStore.ResultSet) => {
        try {
          if (resultSet.rowCount  === 0) {
            Logger.info(RdbUtils.RDB_TAG,  `No record found for filePath: ${filePath}`);
            resolve(null);
            return;
          }

          // Get the first row
          resultSet.goToFirstRow();

          // Get the column index safely
          const columnIndex = resultSet.getColumnIndex(DB_COLUMNS.LYRIC_CONTENT);
          if (columnIndex < 0) {
            Logger.error(RdbUtils.RDB_TAG,  `Column ${DB_COLUMNS.LYRIC_CONTENT} not found`);
            resolve(null);
            return;
          }

          // Get the lyric content
          const lyricContent = resultSet.getString(columnIndex);
          resolve(lyricContent || null);
        } catch (err) {
          Logger.error(RdbUtils.RDB_TAG,  `Error getting lyric content: ${err.message}`);
          reject(err);
        } finally {
          // Ensure the result set is closed
          resultSet.close();
        }
      });
    });
  }

  /**
   * Check if a record exists in the database by filePath
   * @param filePath The file path to check
   * @returns Promise that resolves to true if record exists, false otherwise
   */
  public isRecordExists(filePath: string): Promise<boolean> {
    return new Promise((resolve, reject) => {
      try {
        // Create query predicates
        const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        predicates.equalTo(DB_COLUMNS.FILE_PATH,  filePath);

        // Execute the query
        this.accountTable.query(predicates,  (resultSet: relationalStore.ResultSet) => {
          try {
            // If rowCount > 0, record exists
            resolve(resultSet.rowCount  > 0);
          } catch (err) {
            Logger.error(RdbUtils.RDB_TAG,  `Error checking record existence: ${err.message}`);
            reject(err);
          } finally {
            // Ensure the result set is closed
            resultSet.close();
          }
        });
      } catch (err) {
        Logger.error(RdbUtils.RDB_TAG,  `Error creating query: ${err.message}`);
        reject(err);
      }
    });
  }

  /**
   * Query a VideoItem by file path
   * @param filePath The file path to query
   * @returns Promise that resolves with the VideoItem or null if not found
   */
  public queryVideoByFilePath(filePath: string): Promise<VideoItem | null> {
    return new Promise((resolve, reject) => {
      try {
        // Create query predicates
        const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        predicates.equalTo(DB_COLUMNS.FILE_PATH, filePath);

        // Execute the query
        this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
          try {
            if (resultSet.rowCount === 0) {
              Logger.info(RdbUtils.RDB_TAG, `No record found for filePath: ${filePath}`);
              resolve(null);
              return;
            }

            // Get the first row
            if (resultSet.goToFirstRow()) {
              const videoItem = this.buildVideoItem(resultSet);
              resolve(videoItem);
            } else {
              resolve(null);
            }
          } catch (err) {
            Logger.error(RdbUtils.RDB_TAG, `Error querying video by filePath: ${err.message}`);
            reject(err);
          } finally {
            // Ensure the result set is closed
            resultSet.close();
          }
        });
      } catch (err) {
        Logger.error(RdbUtils.RDB_TAG, `Error creating query: ${err.message}`);
        reject(err);
      }
    });
  }

  /**
   * Query all video items
   * @returns Promise that resolves with an array of VideoItem objects
   */
  public queryAllVideos(): Promise<VideoItem[]> {
    return new Promise((resolve, reject) => {
      try {
        Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 开始查询所有音乐文件');
        
        // Create query predicates for all records
        const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        // Only query music files (type = 0)
        predicates.equalTo(DB_COLUMNS.TYPE, 0);
        // Order by name
        predicates.orderByAsc(DB_COLUMNS.NAME);
        
        Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 创建查询条件完成');

        // Execute the query
        this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
          try {
            Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 查询回调执行');
            
            if (resultSet.rowCount === 0) {
              Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 没有找到音乐文件');
              resolve([]);
              return;
            }

            Logger.info(RdbUtils.RDB_TAG, `queryAllVideos: 找到 ${resultSet.rowCount} 条记录`);
            
            const items: VideoItem[] = [];
            
            // Go to first row
            if (resultSet.goToFirstRow()) {
              do {
                const item = this.buildVideoItem(resultSet);
                items.push(item);
              } while (resultSet.goToNextRow());
            }
            
            Logger.info(RdbUtils.RDB_TAG, `queryAllVideos: 解析完成，返回 ${items.length} 个项目`);
            resolve(items);
          } catch (err) {
            Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 解析结果集出错: ${err.message}`);
            Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 错误堆栈: ${err.stack || '无堆栈信息'}`);
            reject(err);
          }
          // 注意：不在这里关闭 resultSet，因为 RdbUtils.query 会在回调函数执行后关闭它
        });
      } catch (err) {
        Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 创建查询出错: ${err.message}`);
        Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 错误堆栈: ${err.stack || '无堆栈信息'}`);
        reject(err);
      }
    });
  }

}

function generateBucket(item: VideoItem): relationalStore.ValuesBucket {
  let obj: relationalStore.ValuesBucket = {};
  obj.id = item.id
  obj.name = item.name;
  obj.filePath = item.filePath;
  obj.mtype = item.type;
  obj.videoSize = item.videoSize;
  obj.cTime = item.cTime;
  obj.parentPath = item.parentPath;
  obj.isFav = item.isFav;

  // if(item.pixelMapToString){
  //   obj.pixelMapToString = item.pixelMapToString;
  // }
  if(item.artist){
    obj.artist = item.artist;
  }
  if(item.album){
    obj.album = item.album;
  }
  if(item.fileName){
    obj.fileName = item.fileName;
  }
  if(item.size){
    obj.size = item.size;
  }
  if(item.pixelMapPath){
    obj.pixelMapPath = item.pixelMapPath;
  }


  if(item.duration){
    obj.duration = item.duration;
  }
  if(item.mimeType){
    obj.mimeType = item.mimeType;
  }
  if(item.trackCount){
    obj.trackCount = item.trackCount;
  }

  if(item.sampleRate){
    obj.sampleRate = item.sampleRate;
  }

  if(item.lastPlayedStr){
    obj.lastPlayedStr = item.lastPlayedStr;
  }
  if(item.playCount){
    obj.playCount = item.playCount;
  }
  if(item.lyricContent){
    obj.lyricContent = item.lyricContent;
  }
  if(item.md5Str){
    obj.md5Str = item.md5Str;
  }
  if(item.extra_json){
    obj.extra_json = item.extra_json;
  }
  if(item.pyStr){
    obj.pyStr = item.pyStr;
  }


  if(item.bit_rate){
    obj.bit_rate = item.bit_rate;
  }
  if(item.probe_score){
    obj.probe_score = item.probe_score;
  }
  if(item.year){
    obj.year = item.year;
  }
  if(item.nb_streams){
    obj.nb_streams = item.nb_streams;
  }
  if(item.nb_programs){
    obj.nb_programs = item.nb_programs;
  }
  if(item.genre){
    obj.genre = item.genre;
  }
  if(item.track){
    obj.track = item.track;
  }

  if(item.bits_per_raw_sample){
    obj.bits_per_raw_sample = item.bits_per_raw_sample;
  }
  if(item.channels){
    obj.channels = item.channels;
  }
  if(item.channel_layout){
    obj.channel_layout = item.channel_layout;
  }
  if(item.start_time){
    obj.start_time = item.start_time;
  }

  if(item.ALBUMARTIST){
    obj.ALBUMARTIST = item.ALBUMARTIST;
  }
  if(item.COMPOSER){
    obj.COMPOSER = item.COMPOSER;
  }
  if(item.LYRICIST){
    obj.LYRICIST = item.LYRICIST;
  }
  if(item.COMMENT){
    obj.COMMENT = item.COMMENT;
  }
  if(item.disc){
    obj.disc = item.disc;
  }


  return obj;
}