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 Logger from './Logger';
import RdbUtils from './RdbUtils';
import { 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);
  }

  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'));
      obj.md5Str  = resultSet.getString(resultSet.getColumnIndex('md5Str'));
      obj.extra_json  = resultSet.getString(resultSet.getColumnIndex('extra_json'));
      obj.pyStr  = resultSet.getString(resultSet.getColumnIndex('pyStr'));
      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',  ''); // 排除空字符串
      // 使用 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');
    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;
  }
  if(item.md5Str){
    obj.md5Str = item.md5Str;
  }
  if(item.extra_json){
    obj.extra_json = item.extra_json;
  }
  if(item.pyStr){
    obj.pyStr = item.pyStr;
  }

  return obj;
}