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

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


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

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

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

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

      // 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 {
      // 逆向遍历方案（规避鸿蒙API特性）
      while (resultSet.goToNextRow())  {  // 自动边界检查
        const item = this.buildVideoItem(resultSet);
        // let id = resultSet.getString(resultSet.getColumnIndex('id'));
        // let name = resultSet.getString(resultSet.getColumnIndex('name'));
        // let filePath  = resultSet.getString(resultSet.getColumnIndex('filePath'));
        // let type  = resultSet.getDouble(resultSet.getColumnIndex('mtype'));
        // let videoSize  = resultSet.getDouble(resultSet.getColumnIndex('videoSize'));
        // let cTime = resultSet.getString(resultSet.getColumnIndex('cTime'));
        // let size = resultSet.getString(resultSet.getColumnIndex('size'));
        // let parentPath = resultSet.getString(resultSet.getColumnIndex('parentPath'));
        //
        // let pixelMapToString = resultSet.getString(resultSet.getColumnIndex('pixelMapToString'));
        // let artist  = resultSet.getString(resultSet.getColumnIndex('artist'));
        // let album  = resultSet.getString(resultSet.getColumnIndex('album'));
        // let fileName  = resultSet.getString(resultSet.getColumnIndex('fileName'));
        // let item = new VideoItem(name, id, filePath, type, videoSize, cTime,
        //   undefined,size,pixelMapToString,artist,album,fileName);
        items.push(item);
      }
      // 释放数据集的内存
      resultSet.close();
    } catch (err) {
      Logger.error(` onecold testtag parseResultSetToVideoItems: ${err.code} - ${err.message}`);
    }


    return items;
  }

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

    let item =  new VideoItem(
      safeGet('name'),
      safeGet('id'),
      safeGet('filePath'),
      rs.getDouble(rs.getColumnIndex('mtype'))  || 0,
      rs.getDouble(rs.getColumnIndex('videoSize'))  || 0,safeGet('cTime'),undefined,safeGet('size'),
      safeGet('pixelMapPath'),safeGet('artist'),
      safeGet('album'),safeGet('fileName')
    );
    item.isFav = rs.getDouble(rs.getColumnIndex('isFav'));

    item.duration = safeGet('duration');
    item.mimeType = safeGet('mimeType');
    item.trackCount =  safeGet('trackCount');
    item.sampleRate = safeGet('sampleRate');
    item.lastPlayedStr =safeGet('lastPlayedStr');
    // item.playCount = rs.getDouble(rs.getColumnIndex('playCount'));
    item.lyricContent =  safeGet('lyricContent')

    return item
  }



}

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

  return obj;
}