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 { WebDavAccount } from '../../viewmodel/WebDavAccount';
import { CommonConstants } from '../constants/CommonConstants';
import Logger from './Logger';
import RdbUtils from './RdbUtils';
import { AudioQuality, Utility } from './Utility';
import { WebDavUrlUtil } from './WebDavUrlUtil';

/**
 * 数据库字段常量接口定义
 */
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;
  IS_CUSTOM_COVER: 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;
  WEBDAV_ACCOUNT_ID: string;
  REMOTE_REL_PATH: 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',
  IS_CUSTOM_COVER: 'isCustomCover',
  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',
  WEBDAV_ACCOUNT_ID: 'webdav_account_id',
  REMOTE_REL_PATH: 'remote_rel_path'
};

/**
 * 统一的 filePath 规范化方法：
 * - WebDAV 的 HTTP/HTTPS 地址转为相对路径，便于与数据库匹配
 * - 其他路径保持不变
 */
function normalizeFilePath(filePath: string): string {
  if (!filePath) {
    return filePath;
  }
  if (WebDavUrlUtil.isHttpUrl(filePath)) {
    const relative = WebDavUrlUtil.toStoragePath(filePath);
    return relative || filePath;
  }
  return filePath;
}

/**
 * 分页查询接口 - 支持条件查询、排序、分页
 */
export interface PageQueryOptions {
  parentPath?: string;        // 文件夹路径(首页模式)
  searchKeyword?: string;      // 搜索关键词
  sortType?: number;           // 排序类型
  pageIndex: number;           // 页码(从0开始)
  pageSize: number;            // 每页数量
  type?: number;               // 媒体类型过滤
  isShowFileName?: boolean;    // 是否显示文件名（影响排序字段选择）
}

export interface PageQueryResult {
  items: VideoItem[];
  totalCount: number;
  hasMore: boolean;
  countMap?: Map<string, number>;
}

export interface RandomSongQueryOptions {
  type?: number;
  excludeFilePaths?: string[];
  parentPath?: string;              // 文件夹路径(用于首页模式随机播放)
  playlistSongFilePaths?: string[]; // 歌单歌曲路径列表(用于歌单模式随机播放)
}

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);
  }

  /**
   * 判断指定 filePath 是否已存在
   */
  private async existsByFilePath(filePath: string): Promise<boolean> {
    const normalizedPath = normalizeFilePath(filePath);
    return new Promise<boolean>((resolve, reject) => {
      try {
        const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        predicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath);
        this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
          try {
            resolve(resultSet.rowCount > 0);
          } catch (e) {
            reject(e);
          } finally {
            resultSet.close();
          }
        });
      } catch (err) {
        reject(err);
      }
    });
  }

  /**
   * WebDAV歌曲入库（若不存在则插入）
   */
  public async upsertWebDavVideoItem(item: VideoItem): Promise<boolean> {
    try {
      if (!item || !item.filePath) {
        return false;
      }

      const isPureWebDav = item.type === CommonConstants.TYPE_WEBDAV;

      if (isPureWebDav) {
        // 将完整URL转换为相对路径用于存储
        const relativePath = WebDavUrlUtil.toStoragePath(item.filePath);
        if (relativePath !== item.filePath) {
          // 只有当转换成功时才设置remote_rel_path
          item.remote_rel_path = relativePath;
          // 更新filePath为相对路径
          item.filePath = relativePath;
        Logger.info(RdbUtils.RDB_TAG, `WebDAV URL转换: 原始URL -> 存储路径: ${relativePath}`);
        }
      }

      const exists = await this.existsByFilePath(item.filePath);
      if (exists) {
        return true; // 已存在不再重复插入
      }
      return await this.insertWebDavItem(item);
    } catch (e) {
      Logger.error(RdbUtils.RDB_TAG, 'upsertWebDavVideoItem 失败: ' + (e as Error).message);
      return false;
    }
  }

  /**
   * 插入WebDAV歌曲的最小字段集合
   */
  private async insertWebDavItem(item: VideoItem): Promise<boolean> {
    return new Promise<boolean>((resolve) => {
      try {
        const bucket: relationalStore.ValuesBucket = generateBucket(item);
        if (!bucket.id) { // 使用filePath作为主键/ID以避免为空
          bucket.id = item.filePath;
        }
        if (!bucket.parentPath) {
          // 以路径去掉文件名部分作为 parentPath（已经是相对路径）
          const idx = item.filePath.lastIndexOf('/');
          if (idx > 0) {
            bucket.parentPath = item.filePath.substring(0, idx);
          }
        }
        if (item.remote_rel_path) {
          bucket.remote_rel_path = item.remote_rel_path;
        }
        this.accountTable.insertData(bucket, (success: boolean) => {
          resolve(success);
        });
      } catch (e) {
        Logger.error(RdbUtils.RDB_TAG, 'insertWebDavItem 出错: ' + (e as Error).message);
        resolve(false);
      }
    });
  }

  /**
   * 保存或更新WebDAV歌曲的完整元数据
   */
  public async saveOrUpdateWebDavItem(item: VideoItem): Promise<boolean> {
    try {
      if (!item || !item.filePath) {
        return false;
      }
      item.filePath = normalizeFilePath(item.filePath);
      if (!item.remote_rel_path) {
        item.remote_rel_path = item.filePath;
      }
      if (!item.parentPath) {
        const idx = item.filePath.lastIndexOf('/');
        item.parentPath = idx > 0 ? item.filePath.substring(0, idx) : '';
      }
      const exists = await this.existsByFilePath(item.filePath);
      if (exists) {
        const cached = await this.queryVideoByFilePath(item.filePath);
        if (cached && cached.isCustomCover === 1 && item.isCustomCover !== 1) {
          item.pixelMapPath = cached.pixelMapPath || item.pixelMapPath;
          item.isCustomCover = 1;
        }
        return await new Promise<boolean>((resolve) => {
          const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
          predicates.equalTo(DB_COLUMNS.FILE_PATH, item.filePath);
          const bucket = generateBucket(item);
          this.accountTable.updateData(predicates, bucket, (success: boolean) => resolve(success));
        });
      }
      return await this.insertWebDavItem(item);
    } catch (error) {
      Logger.error(RdbUtils.RDB_TAG, 'saveOrUpdateWebDavItem 失败: ' + (error as Error).message);
      return false;
    }
  }

  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) {
    const normalizedPath = normalizeFilePath(filePath);
    let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('filePath', normalizedPath);
    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) {
    this.updateCoverInfo(filePath, newPixelMapPath, undefined, false, callback);
  }

  public updateCustomCoverPath(filePath: string, newPixelMapPath: string, callback: Function) {
    this.updateCoverInfo(filePath, newPixelMapPath, 1, true, callback);
  }

  private updateCoverInfo(filePath: string, newPixelMapPath: string, isCustomCover: number | undefined,
    allowReplaceCustomCover: boolean, callback: Function) {
    const normalizedPath = normalizeFilePath(filePath);
    const queryPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    queryPredicates.equalTo('filePath',  normalizedPath);

    this.accountTable.query(queryPredicates,  (resultSet: relationalStore.ResultSet) => {
      if (resultSet.rowCount  === 0) {
        callback(false, 'Error: Target file not found in database');
        resultSet.close();
        return;
      }
      let existingPixelMapPath = '';
      let existingIsCustomCover = 0;
      if (resultSet.goToFirstRow()) {
        const pixelMapIndex = resultSet.getColumnIndex(DB_COLUMNS.PIXEL_MAP_PATH);
        if (pixelMapIndex >= 0) {
          existingPixelMapPath = resultSet.getString(pixelMapIndex) || '';
        }
        const customCoverIndex = resultSet.getColumnIndex(DB_COLUMNS.IS_CUSTOM_COVER);
        if (customCoverIndex >= 0) {
          existingIsCustomCover = resultSet.getDouble(customCoverIndex) || 0;
        }
      }
      resultSet.close();

      if (existingIsCustomCover === 1 && !allowReplaceCustomCover &&
        existingPixelMapPath.length > 0 && existingPixelMapPath !== newPixelMapPath) {
        callback(true, 'Skip updating custom cover');
        return;
      }

      const updatePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      updatePredicates.equalTo('filePath',  normalizedPath);
      const valueBucket: relationalStore.ValuesBucket = {
        pixelMapPath: newPixelMapPath
      };
      if (isCustomCover !== undefined) {
        valueBucket.isCustomCover = isCustomCover;
      }

      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): boolean {
    if (!callback || typeof callback !== 'function') {
      Logger.info(RdbUtils.RDB_TAG, 'updateMediaInfo() has no valid callback!');
      return false;
    }

    if (!this.accountTable) {
      Logger.error(RdbUtils.RDB_TAG, 'RdbStore is not initialized.');
      callback(false);
      return false;
    }
    const normalizedPath = normalizeFilePath(filePath);
    console.info('onecold filePath updateMediaInfo= '+normalizedPath)
    // Step 1: Create a predicate to find the record by filePath
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('filePath', normalizedPath);

    // 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 ${normalizedPath}.`);
        console.info('onecold No record found with filePath '+normalizedPath)
        callback(false);
        resultSet.close();
        return;
      }
      console.info('onecold filePath updateMediaInfo1= '+normalizedPath)
      // 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);
      }
    });
    
    return true; // 表示方法已成功启动
  }

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

    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 = normalizedNewPath
      obj.filePath = normalizedNewPath;
      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'));
      const customCoverIndex = resultSet.getColumnIndex(DB_COLUMNS.IS_CUSTOM_COVER);
      if (customCoverIndex >= 0) {
        obj.isCustomCover = resultSet.getDouble(customCoverIndex);
      }

      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',  normalizedOldPath);

      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 normalizedPath = normalizeFilePath(filePath);
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo('filePath', normalizedPath);

    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);
          }
        });
      });
    });
  }

  /**
   * 按艺术家查询歌曲列表（用于艺术家详情页）
   */
  public async querySongsByArtist(artist: string, sortType?: number, isShowFileName?: boolean): Promise<VideoItem[]> {
    try {
      const trimmedArtist = artist?.trim() || '';
      if (!trimmedArtist) {
        return [];
      }

      // 使用SQL的TRIM函数匹配，解决数据库中artist字段可能有前后空格的问题
      const tableName = RdbUtils.MEDIA_TABLE.tableName;
      let orderBy = `${DB_COLUMNS.NAME} COLLATE NOCASE ASC`;
      if (isShowFileName) {
        orderBy = `${DB_COLUMNS.FILE_NAME} COLLATE NOCASE ASC`;
      }

      const sql = `SELECT * FROM ${tableName} WHERE TRIM(${DB_COLUMNS.ARTIST}) = ? AND ${DB_COLUMNS.TYPE} = ? ORDER BY ${orderBy}`;
      const params: (string | number)[] = [trimmedArtist, CommonConstants.TYPE_LOCAL];

      const resultSet = await this.accountTable.querySql(sql, params);
      try {
        const result = this.parseResultSetToVideoItems(resultSet, sortType);
        return result;
      } finally {
        resultSet.close();
      }
    } catch (err) {
      const error = err as Error;
      Logger.error('heanup MediaTable', `querySongsByArtist error: ${error.message}`);
      return [];
    }
  }

  /**
   * 按专辑查询歌曲列表（用于专辑详情页）
   */
  public async querySongsByAlbum(album: string, sortType?: number, isShowFileName?: boolean): Promise<VideoItem[]> {
    try {
      const trimmedAlbum = album?.trim() || '';
      if (!trimmedAlbum) {
        return [];
      }

      // 使用SQL的TRIM函数匹配，解决数据库中album字段可能有前后空格的问题
      const tableName = RdbUtils.MEDIA_TABLE.tableName;
      let orderBy = `${DB_COLUMNS.NAME} COLLATE NOCASE ASC`;
      if (isShowFileName) {
        orderBy = `${DB_COLUMNS.FILE_NAME} COLLATE NOCASE ASC`;
      }

      const sql = `SELECT * FROM ${tableName} WHERE TRIM(${DB_COLUMNS.ALBUM}) = ? AND ${DB_COLUMNS.TYPE} = ? ORDER BY ${orderBy}`;
      const params: (string | number)[] = [trimmedAlbum, CommonConstants.TYPE_LOCAL];

      const resultSet = await this.accountTable.querySql(sql, params);
      try {
        const result = this.parseResultSetToVideoItems(resultSet, sortType);
        return result;
      } finally {
        resultSet.close();
      }
    } catch (err) {
      const error = err as Error;
      Logger.error('heanup MediaTable', `querySongsByAlbum error: ${error.message}`);
      return [];
    }
  }



  // 解析去重列数据（如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数组（复用原有逻辑）
   * 当sortType=10时,会按照disc和track进行二次排序
   */
  private parseResultSetToVideoItems(resultSet: relationalStore.ResultSet, sortType?: number): 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();
      }
    }

    // sortType=10: 按碟片编号(disc)和音轨号(track)升序排序
    if (sortType === 10) {
      items.sort((a, b) => {
        // 解析disc字段,处理可能为空或包含非数字字符的情况
        const discA = this.parseDiscNumber(a.disc);
        const discB = this.parseDiscNumber(b.disc);

        // 先按碟片编号排序
        if (discA !== discB) {
          return discA - discB;
        }

        // 碟片编号相同,按音轨号排序
        const trackA = this.parseTrackNumber(a.track);
        const trackB = this.parseTrackNumber(b.track);
        return trackA - trackB;
      });
      Logger.info(RdbUtils.RDB_TAG, `parseResultSetToVideoItems: 已按碟片-音轨升序排序,歌曲数量=${items.length}`);
    }

    // sortType=11: 按碟片编号(disc)和音轨号(track)降序排序
    if (sortType === 11) {
      items.sort((a, b) => {
        // 解析disc字段,处理可能为空或包含非数字字符的情况
        const discA = this.parseDiscNumber(a.disc);
        const discB = this.parseDiscNumber(b.disc);

        // 先按碟片编号降序排序
        if (discA !== discB) {
          return discB - discA;
        }

        // 碟片编号相同,按音轨号降序排序
        const trackA = this.parseTrackNumber(a.track);
        const trackB = this.parseTrackNumber(b.track);
        return trackB - trackA;
      });
      Logger.info(RdbUtils.RDB_TAG, `parseResultSetToVideoItems: 已按碟片-音轨降序排序,歌曲数量=${items.length}`);
    }

    return items;
  }

  /**
   * 解析碟片编号,返回数字进行比较
   * 支持格式: "1", "1/5", "1.5", "CD1", "Vol1"等
   */
  private parseDiscNumber(disc: string | undefined | null): number {
    if (!disc) return 0;

    // 移除空格并转大写
    const discStr = disc.toString().trim().toUpperCase();

    // 尝试提取数字
    const match = discStr.match(/(\d+)/);
    if (match) {
      return parseInt(match[1], 10);
    }

    return 0;
  }

  /**
   * 解析音轨号,返回数字进行比较
   * 支持格式: "1", "1/10", "1.12", "Track 1"等
   */
  private parseTrackNumber(track: string | undefined | null): number {
    if (!track) return 0;

    // 移除空格并转大写
    const trackStr = track.toString().trim().toUpperCase();

    // 尝试提取数字
    const match = trackStr.match(/(\d+)/);
    if (match) {
      return parseInt(match[1], 10);
    }

    return 0;
  }

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

    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),
      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.isCustomCover = safeGetNumber(DB_COLUMNS.IS_CUSTOM_COVER);
    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');
    item.webdav_account_id = safeGet('webdav_account_id');
    item.remote_rel_path = safeGet('remote_rel_path');

    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> {
    const normalizedPath = normalizeFilePath(filePath);
    return new Promise((resolve, reject) => {
      // Create query predicates
      const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      predicates.equalTo(DB_COLUMNS.FILE_PATH,  normalizedPath);

      // 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: ${normalizedPath}`);
            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> {
    const normalizedPath = normalizeFilePath(filePath);
    return new Promise((resolve, reject) => {
      try {
        // Create query predicates
        const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        predicates.equalTo(DB_COLUMNS.FILE_PATH,  normalizedPath);

        // 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> {
    const normalizedPath = normalizeFilePath(filePath);
    return new Promise((resolve, reject) => {
      try {
        // Create query predicates
        const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        predicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath);

        // 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: ${normalizedPath}`);
              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);
      }
    });
  }

  /**
   * 从数据库随机获取一首歌曲（可选条件：类型、排除路径、文件夹、歌单）
   */
  public async queryRandomSong(options?: RandomSongQueryOptions): Promise<VideoItem | null> {
    try {
      // 优先处理歌单模式
      if (options?.playlistSongFilePaths && options.playlistSongFilePaths.length > 0) {
        Logger.info(RdbUtils.RDB_TAG, `queryRandomSong: 歌单模式,歌曲数量=${options.playlistSongFilePaths.length}`);
        // 过滤掉排除的路径
        let availablePaths = options.playlistSongFilePaths;
        if (options.excludeFilePaths && options.excludeFilePaths.length > 0) {
          const excludeSet = new Set(options.excludeFilePaths);
          availablePaths = availablePaths.filter(path => !excludeSet.has(path));
        }
        if (availablePaths.length === 0) {
          Logger.warn(RdbUtils.RDB_TAG, 'queryRandomSong: 歌单中没有可用歌曲');
          return null;
        }
        // 随机选择一个路径
        const randomPath = availablePaths[Math.floor(Math.random() * availablePaths.length)];
        return await this.queryVideoByFilePath(randomPath);
      }

      // 文件夹模式或全局模式
      const conditions: string[] = [];
      const params: Array<string | number> = [];
      if (options?.type !== undefined) {
        conditions.push(`${DB_COLUMNS.TYPE} = ?`);
        params.push(options.type);
      }
      // 文件夹条件
      if (options?.parentPath) {
        conditions.push(`${DB_COLUMNS.PARENT_PATH} = ?`);
        params.push(options.parentPath);
      }
      // 排除路径条件
      if (options?.excludeFilePaths && options.excludeFilePaths.length > 0) {
        const normalizedPaths = options.excludeFilePaths.map(path => normalizeFilePath(path));
        const placeholders = normalizedPaths.map(() => '?').join(', ');
        conditions.push(`${DB_COLUMNS.FILE_PATH} NOT IN (${placeholders})`);
        params.push(...normalizedPaths);
      }
      let sql = `SELECT * FROM ${RdbUtils.MEDIA_TABLE.tableName}`;
      if (conditions.length > 0) {
        sql += ` WHERE ${conditions.join(' AND ')}`;
      }
      sql += ' ORDER BY RANDOM() LIMIT 1';

      Logger.info(RdbUtils.RDB_TAG, `queryRandomSong: SQL=${sql}, params=${JSON.stringify(params)}`);

      const resultSet = await this.accountTable.querySql(sql, params);
      try {
        if (resultSet.rowCount === 0) {
          return null;
        }
        resultSet.goToFirstRow();
        return this.buildVideoItem(resultSet);
      } finally {
        resultSet.close();
      }
    } catch (error) {
      Logger.error(RdbUtils.RDB_TAG, `queryRandomSong failed: ${(error as Error).message}`);
      return null;
    }
  }

  /**
   * 应用搜索条件到查询谓词
   * 支持对 name、artist、album、fileName 多字段模糊搜索
   */
  private applySearchConditions(predicates: relationalStore.RdbPredicates, searchKeyword: string): void {
    if (searchKeyword && searchKeyword.trim().length > 0) {
      const keyword = `%${searchKeyword.trim()}%`;
      predicates.beginWrap();
      predicates.like(DB_COLUMNS.NAME, keyword);
      predicates.or();
      predicates.like(DB_COLUMNS.ARTIST, keyword);
      predicates.or();
      predicates.like(DB_COLUMNS.ALBUM, keyword);
      predicates.or();
      predicates.like(DB_COLUMNS.FILE_NAME, keyword);
      predicates.endWrap();
    }
  }

  /**
   * 应用排序条件到查询谓词
   * sortType: 0-艺术家升序 1-艺术家降序 2-专辑升序 3-专辑降序 4-名称升序 5-名称降序 6-时间升序 7-时间降序
   * 10-碟片编号升序+音轨号升序(用于专辑和文件夹)
   * @param predicates 查询谓词
   * @param sortType 排序类型
   * @param isShowFileName 是否显示文件名（当sortType为4或5时，此参数决定按name还是fileName排序）
   */
  private applySortConditions(predicates: relationalStore.RdbPredicates, sortType?: number, isShowFileName?: boolean): void {
    const type = sortType !== undefined ? sortType : 4;

    // 当按名称排序时，根据isShowFileName决定使用name还是fileName字段
    let nameColumn = DB_COLUMNS.NAME;
    if ((type === 4 || type === 5) && isShowFileName === true) {
      nameColumn = DB_COLUMNS.FILE_NAME;
    }

    // sortType=10 或 11 需要特殊处理:按碟片编号+音轨号排序
    // 由于RdbPredicates不支持多字段排序,我们需要标记这个谓词需要使用原生SQL
    if (type === 10) {
      Logger.info(RdbUtils.RDB_TAG, 'applySortConditions: 碟片-音轨升序排序,需要使用内存排序');
      predicates.orderByAsc('disc'); // 先按碟片编号排序
      // 音轨号排序需要在查询后进行内存排序
      return;
    }
    if (type === 11) {
      Logger.info(RdbUtils.RDB_TAG, 'applySortConditions: 碟片-音轨降序排序,需要使用内存排序');
      predicates.orderByDesc('disc'); // 先按碟片编号降序排序
      // 音轨号排序需要在查询后进行内存排序
      return;
    }

    switch (type) {
      case 0:
        predicates.orderByAsc(DB_COLUMNS.ARTIST);
        break;
      case 1:
        predicates.orderByDesc(DB_COLUMNS.ARTIST);
        break;
      case 2:
        predicates.orderByAsc(DB_COLUMNS.ALBUM);
        break;
      case 3:
        predicates.orderByDesc(DB_COLUMNS.ALBUM);
        break;
      case 4:
        predicates.orderByAsc(nameColumn);
        break;
      case 5:
        predicates.orderByDesc(nameColumn);
        break;
      case 6:
        predicates.orderByAsc(DB_COLUMNS.C_TIME);
        break;
      case 7:
        predicates.orderByDesc(DB_COLUMNS.C_TIME);
        break;
      default:
        predicates.orderByAsc(nameColumn);
        break;
    }
  }

  /**
   * 获取总记录数（用于计算分页）
   */
  private async getTotalCount(predicates: relationalStore.RdbPredicates): Promise<number> {
    return new Promise((resolve, reject) => {
      this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
        try {
          const count = resultSet.rowCount;
          resolve(count);
        } catch (err) {
          const error = err as Error;
          Logger.error(RdbUtils.RDB_TAG, `getTotalCount error: ${error.message}`);
          reject(err);
        } finally {
          resultSet.close();
        }
      });
    });
  }

  /**
   * 首页文件夹内容分页查询(modeType=0)
   * 注意：此方法只返回数据库中的文件记录，不包含文件夹
   * 文件夹需要通过文件系统API单独获取
   */
  public async queryByParentPathPaged(options: PageQueryOptions): Promise<PageQueryResult> {
    try {
      Logger.info('heanup MediaTable', `queryByParentPathPaged: 开始分页查询 parentPath=${options.parentPath}, page=${options.pageIndex}, size=${options.pageSize}`);
      
      // 1. 构建基础查询条件
      const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      if (options.parentPath) {
        predicates.equalTo(DB_COLUMNS.PARENT_PATH, options.parentPath);
      }
      
      // 2. 应用搜索条件
      if (options.searchKeyword) {
        this.applySearchConditions(predicates, options.searchKeyword);
      }
      
      // 3. 先获取总数
      const totalCount = await this.getTotalCount(predicates);
      
      // 4. 构建分页查询条件
      const pagePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      if (options.parentPath) {
        pagePredicates.equalTo(DB_COLUMNS.PARENT_PATH, options.parentPath);
      }
      if (options.searchKeyword) {
        this.applySearchConditions(pagePredicates, options.searchKeyword);
      }
      
      // 5. 应用排序
      this.applySortConditions(pagePredicates, options.sortType, options.isShowFileName);
      
      // 6. 应用分页
      const offset = options.pageIndex * options.pageSize;
      pagePredicates.limitAs(options.pageSize);
      pagePredicates.offsetAs(offset);
      
      // 7. 执行查询
      return new Promise((resolve, reject) => {
        this.accountTable.query(pagePredicates, (resultSet: relationalStore.ResultSet) => {
          try {
            const items = this.parseResultSetToVideoItems(resultSet, options.sortType);
            const hasMore = (offset + items.length) < totalCount;
            Logger.info('heanup MediaTable', `queryByParentPathPaged: 查询完成 items=${items.length}, total=${totalCount}, hasMore=${hasMore}`);
            resolve({ items, totalCount, hasMore });
          } catch (err) {
            const error = err as Error;
            Logger.error('heanup MediaTable', `queryByParentPathPaged error: ${error.message}`);
            reject(err);
          }
        });
      });
    } catch (err) {
      const error = err as Error;
      Logger.error('heanup MediaTable', `queryByParentPathPaged failed: ${error.message}`);
      throw error;
    }
  }

  /**
   * 媒体库分页查询(modeType=1)
   */
  public async queryMediaLibraryPaged(options: PageQueryOptions): Promise<PageQueryResult> {
    try {
      Logger.info('heanup MediaTable', `queryMediaLibraryPaged: 开始分页查询 page=${options.pageIndex}, size=${options.pageSize}`);
      
      // 1. 构建基础查询条件 - 查询所有type=0的音乐文件
      const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      predicates.equalTo(DB_COLUMNS.TYPE, 0);
      
      // 2. 应用搜索条件
      if (options.searchKeyword) {
        this.applySearchConditions(predicates, options.searchKeyword);
      }
      
      // 3. 先获取总数
      const totalCount = await this.getTotalCount(predicates);
      
      // 4. 构建分页查询条件
      const pagePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
      pagePredicates.equalTo(DB_COLUMNS.TYPE, 0);
      if (options.searchKeyword) {
        this.applySearchConditions(pagePredicates, options.searchKeyword);
      }
      
      // 5. 应用排序
      this.applySortConditions(pagePredicates, options.sortType, options.isShowFileName);
      
      // 6. 应用分页
      const offset = options.pageIndex * options.pageSize;
      pagePredicates.limitAs(options.pageSize);
      pagePredicates.offsetAs(offset);
      
      // 7. 执行查询
      return new Promise((resolve, reject) => {
        this.accountTable.query(pagePredicates, (resultSet: relationalStore.ResultSet) => {
          try {
            const items = this.parseResultSetToVideoItems(resultSet, options.sortType);
            const hasMore = (offset + items.length) < totalCount;
            Logger.info('heanup MediaTable', `queryMediaLibraryPaged: 查询完成 items=${items.length}, total=${totalCount}, hasMore=${hasMore}`);
            resolve({ items, totalCount, hasMore });
          } catch (err) {
            const error = err as Error;
            Logger.error('heanup MediaTable', `queryMediaLibraryPaged error: ${error.message}`);
            reject(err);
          }
        });
      });
    } catch (err) {
      const error = err as Error;
      Logger.error('heanup MediaTable', `queryMediaLibraryPaged failed: ${error.message}`);
      throw error;
    }
  }

  /**
   * 艺术家维度分页查询(modeType=2)
   * 返回艺术家封面列表的代表性歌曲
   */
  public async queryArtistsPaged(options: PageQueryOptions): Promise<PageQueryResult> {
    return await this.queryGroupedPaged(DB_COLUMNS.ARTIST, CommonConstants.TYPE_IS_ARTIST, options);
  }

  /**
   * 专辑维度分页查询(modeType=3)
   * 返回专辑封面列表的代表性歌曲
   */
  public async queryAlbumsPaged(options: PageQueryOptions): Promise<PageQueryResult> {
    return await this.queryGroupedPaged(DB_COLUMNS.ALBUM, CommonConstants.TYPE_IS_ALBUM, options);
  }

  private async queryGroupedPaged(columnName: string, itemType: number, options: PageQueryOptions): Promise<PageQueryResult> {
    try {
      const keyword = options.searchKeyword?.trim();
      const tableName = RdbUtils.MEDIA_TABLE.tableName;
      const whereClauses = [
        `${columnName} IS NOT NULL`,
        `${columnName} != ''`,
        `${DB_COLUMNS.TYPE} = ?`
      ];
      const params: Array<string | number> = [CommonConstants.TYPE_LOCAL];
      if (keyword) {
        whereClauses.push(`${columnName} LIKE ?`);
        params.push(`%${keyword}%`);
      }
      const whereSql = whereClauses.join(' AND ');

      // 使用TRIM函数去除空格，合并相同艺术家/专辑名的数据
      const totalSql = `SELECT COUNT(DISTINCT TRIM(${columnName})) AS totalCount FROM ${tableName} WHERE ${whereSql}`;
      const totalResultSet = await this.accountTable.querySql(totalSql, params);
      let totalCount = 0;
      if (totalResultSet.goToFirstRow()) {
        totalCount = totalResultSet.getLong(totalResultSet.getColumnIndex('totalCount'));
      }
      totalResultSet.close();

      const sortType = options.sortType ?? 9;
      let orderBy = `name ASC`;
      if (sortType === 5) {
        orderBy = `name DESC`;
      } else if (sortType === 8) {
        orderBy = `songCount ASC, name ASC`;
      } else if (sortType === 9) {
        orderBy = `songCount DESC, name ASC`;
      }

      const offset = options.pageIndex * options.pageSize;
      const pageSql =
        `SELECT TRIM(${columnName}) AS name, COUNT(*) AS songCount, ` +
        `MAX(${DB_COLUMNS.PIXEL_MAP_PATH}) AS coverPath ` +
        `FROM ${tableName} WHERE ${whereSql} ` +
        `GROUP BY TRIM(${columnName}) ` +
        `HAVING COUNT(*) > 0 ` +
        `ORDER BY ${orderBy} ` +
        `LIMIT ? OFFSET ?`;
      const pageParams = [...params, options.pageSize, offset];
      const resultSet = await this.accountTable.querySql(pageSql, pageParams);

      const items: VideoItem[] = [];
      const countMap = new Map<string, number>();
      if (resultSet.rowCount > 0) {
        resultSet.goToFirstRow();
        for (let i = 0; i < resultSet.rowCount; i++) {
          const name = resultSet.getString(resultSet.getColumnIndex('name')) || '';
          const songCount = resultSet.getLong(resultSet.getColumnIndex('songCount'));
          const coverPath = resultSet.getString(resultSet.getColumnIndex('coverPath')) || '';
          countMap.set(name, songCount);
          const item = new VideoItem(name, name, name, itemType, 0, '');
          item.pixelMapPath = coverPath;
          if (itemType === CommonConstants.TYPE_IS_ARTIST) {
            item.artist = name;
          } else {
            item.album = name;
          }
          items.push(item);
          if (i < resultSet.rowCount - 1) {
            resultSet.goToNextRow();
          }
        }
      }
      resultSet.close();

      const hasMore = (offset + items.length) < totalCount;
      Logger.info('heanup MediaTable', `queryGroupedPaged: 查询完成 items=${items.length}, total=${totalCount}, hasMore=${hasMore}`);
      return { items, totalCount, hasMore, countMap };
    } catch (err) {
      const error = err as Error;
      Logger.error('heanup MediaTable', `queryGroupedPaged failed: ${error.message}`);
      throw error;
    }
  }

  private async getCountByColumn(columnName: string, value: string): Promise<number> {
    const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
    predicates.equalTo(columnName, value);
    predicates.equalTo(DB_COLUMNS.TYPE, CommonConstants.TYPE_LOCAL);
    return await this.getTotalCount(predicates);
  }

  /**
   * 更新歌曲的webdav_account_id
   * @param filePath 歌曲文件路径
   * @param accountId 新的账号ID
   * @returns Promise<boolean> 更新是否成功
   */
  public async updateWebDavAccountId(filePath: string, accountId: string): Promise<boolean> {
    const normalizedPath = normalizeFilePath(filePath);
    return new Promise<boolean>((resolve, reject) => {
      try {
        // 先查询记录是否存在
        const queryPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
        queryPredicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath);

        this.accountTable.query(queryPredicates, (resultSet: relationalStore.ResultSet) => {
          try {
            if (resultSet.rowCount === 0) {
              Logger.warn(RdbUtils.RDB_TAG, `updateWebDavAccountId: 未找到记录 ${normalizedPath}`);
              resolve(false);
              resultSet.close();
              return;
            }
            resultSet.close();

            // 更新webdav_account_id
            const updatePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName);
            updatePredicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath);
            const valueBucket: relationalStore.ValuesBucket = {
              webdav_account_id: accountId
            };

            this.accountTable.updateData(updatePredicates, valueBucket, (success: boolean) => {
              if (success) {
                Logger.info(RdbUtils.RDB_TAG, `updateWebDavAccountId: 成功更新 ${normalizedPath} -> ${accountId}`);
              } else {
                Logger.error(RdbUtils.RDB_TAG, `updateWebDavAccountId: 更新失败 ${normalizedPath}`);
              }
              resolve(success);
            });
          } catch (err) {
            Logger.error(RdbUtils.RDB_TAG, `updateWebDavAccountId error: ${(err as Error).message}`);
            reject(err);
          }
        });
      } catch (err) {
        Logger.error(RdbUtils.RDB_TAG, `updateWebDavAccountId error: ${(err as Error).message}`);
        reject(err);
      }
    });
  }

}

function generateBucket(item: VideoItem): relationalStore.ValuesBucket {
  let obj: relationalStore.ValuesBucket = {};
  obj.id = item.id
  obj.name = item.name;
  const normalizedPath = normalizeFilePath(item.filePath);
  obj.filePath = normalizedPath;
  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.isCustomCover !== undefined && item.isCustomCover !== null){
    obj.isCustomCover = item.isCustomCover;
  }


  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;
  }
  if(item.webdav_account_id){
    obj.webdav_account_id = item.webdav_account_id;
  }
  if(item.remote_rel_path){
    obj.remote_rel_path = item.remote_rel_path;
  }

  return obj;
}