import { relationalStore } from '@kit.ArkData';
import { Context } from '@kit.AbilityKit';
import Logger from './Logger';
import RdbUtils from './RdbUtils';
import { Playlist, PlaylistSong } from '../../viewmodel/Playlist';

/**
 * 歌单数据库操作类
 */
export default class PlaylistTable {
  private context: Context;
  private rdbStore: relationalStore.RdbStore | null = null;
  private initPromise: Promise<void>;

  constructor(context: Context) {
    this.context = context;
    this.initPromise = this.initRdbStore();
  }

  /**
   * 确保数据库已初始化
   */
  private async ensureInitialized(): Promise<void> {
    await this.initPromise;
  }

  /**
   * 初始化数据库
   */
  private async initRdbStore(): Promise<void> {
    try {
      const config: relationalStore.StoreConfig = {
        name: 'PlaylistStore.db',
        securityLevel: relationalStore.SecurityLevel.S1  // 使用标准安全级别
      };

      this.rdbStore = await relationalStore.getRdbStore(this.context, config);

      // 创建表
      await this.createTables();

      Logger.info('heanup PlaylistTable', '数据库初始化成功');
    } catch (error) {
      Logger.error('heanup PlaylistTable', `初始化数据库失败: ${error.message}`);
    }
  }

  /**
   * 创建表
   */
  private async createTables(): Promise<void> {
    if (!this.rdbStore) {
      return;
    }

    try {
      // 创建歌单表
      await this.rdbStore.executeSql(RdbUtils.PLAYLIST_TABLE.sqlCreate);
      
      // 创建歌单歌曲关联表
      await this.rdbStore.executeSql(RdbUtils.PLAYLIST_SONG_TABLE.sqlCreate);
      
      Logger.info('heanup PlaylistTable', '数据库表创建成功');
    } catch (error) {
      Logger.error('heanup PlaylistTable', `创建表失败: ${error.message}`);
    }
  }

  /**
   * 生成唯一ID
   */
  private generateId(): string {
    return Date.now().toString() + Math.random().toString(36).substr(2, 9);
  }

  /**
   * 创建歌单
   */
  async createPlaylist(name: string, description?: string, coverPath?: string): Promise<boolean> {
    await this.ensureInitialized();

    if (!this.rdbStore) {
      Logger.error('heanup PlaylistTable', '数据库未初始化');
      return false;
    }

    try {
      const id = this.generateId();
      const now = new Date().toISOString();
      const sql = 'INSERT INTO playlistTable (id, name, coverPath, description, createTime, updateTime, songCount, sortOrder) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
      const params = [id, name, coverPath || null, description || null, now, now, 0, 0];

      await this.rdbStore.executeSql(sql, params);
      Logger.info('heanup PlaylistTable', `歌单创建成功: ${name}`);
      return true;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `创建歌单失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 删除歌单及其所有歌曲
   */
  async deletePlaylist(playlistId: string): Promise<boolean> {
    if (!this.rdbStore) {
      return false;
    }

    try {
      // 先删除歌单歌曲关联
      const deleteSongsSql = 'DELETE FROM playlistSongTable WHERE playlistId = ?';
      await this.rdbStore.executeSql(deleteSongsSql, [playlistId]);
      
      // 再删除歌单
      const deletePlaylistSql = 'DELETE FROM playlistTable WHERE id = ?';
      await this.rdbStore.executeSql(deletePlaylistSql, [playlistId]);
      
      Logger.info('heanup PlaylistTable', `歌单删除成功: ${playlistId}`);
      return true;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `删除歌单失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 更新歌单信息
   */
  async updatePlaylist(playlistId: string, name?: string, description?: string, coverPath?: string): Promise<boolean> {
    if (!this.rdbStore) {
      return false;
    }

    try {
      const updateTime = new Date().toISOString();
      const updates: string[] = [];
      const params: (string | number | null)[] = [];

      if (name !== undefined) {
        updates.push('name = ?');
        params.push(name);
      }
      if (description !== undefined) {
        updates.push('description = ?');
        params.push(description);
      }
      if (coverPath !== undefined) {
        updates.push('coverPath = ?');
        params.push(coverPath);
      }
      
      updates.push('updateTime = ?');
      params.push(updateTime);
      params.push(playlistId);

      const sql = `UPDATE playlistTable SET ${updates.join(', ')} WHERE id = ?`;
      await this.rdbStore.executeSql(sql, params);
      
      Logger.info('heanup PlaylistTable', `歌单更新成功: ${playlistId}`);
      return true;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `更新歌单失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 查询所有歌单
   */
  async queryAllPlaylists(): Promise<Playlist[]> {
    await this.ensureInitialized();

    if (!this.rdbStore) {
      Logger.error('heanup PlaylistTable', '数据库未初始化');
      return [];
    }

    try {
      const sql = 'SELECT * FROM playlistTable ORDER BY sortOrder ASC, createTime DESC';
      Logger.info('heanup PlaylistTable', '开始查询所有歌单');
      const resultSet = await this.rdbStore.querySql(sql);
      
      const playlists: Playlist[] = [];
      if (resultSet.goToFirstRow()) {
        do {
          const playlist = new Playlist(
            resultSet.getString(resultSet.getColumnIndex('id')),
            resultSet.getString(resultSet.getColumnIndex('name')),
            resultSet.getString(resultSet.getColumnIndex('createTime')),
            resultSet.getString(resultSet.getColumnIndex('updateTime')),
            resultSet.getLong(resultSet.getColumnIndex('songCount')),
            resultSet.getLong(resultSet.getColumnIndex('sortOrder')),
            resultSet.getString(resultSet.getColumnIndex('coverPath')),
            resultSet.getString(resultSet.getColumnIndex('description'))
          );
          playlists.push(playlist);
        } while (resultSet.goToNextRow());
      }
      
      resultSet.close();
      Logger.info('heanup PlaylistTable', `查询到 ${playlists.length} 个歌单`);
      return playlists;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `查询歌单失败: ${error.message}`);
      return [];
    }
  }

  /**
   * 根据ID查询歌单
   */
  async queryPlaylistById(playlistId: string): Promise<Playlist | null> {
    if (!this.rdbStore) {
      return null;
    }

    try {
      const sql = 'SELECT * FROM playlistTable WHERE id = ?';
      const resultSet = await this.rdbStore.querySql(sql, [playlistId]);
      
      if (resultSet.goToFirstRow()) {
        const playlist = new Playlist(
          resultSet.getString(resultSet.getColumnIndex('id')),
          resultSet.getString(resultSet.getColumnIndex('name')),
          resultSet.getString(resultSet.getColumnIndex('createTime')),
          resultSet.getString(resultSet.getColumnIndex('updateTime')),
          resultSet.getLong(resultSet.getColumnIndex('songCount')),
          resultSet.getLong(resultSet.getColumnIndex('sortOrder')),
          resultSet.getString(resultSet.getColumnIndex('coverPath')),
          resultSet.getString(resultSet.getColumnIndex('description'))
        );
        resultSet.close();
        return playlist;
      }
      
      resultSet.close();
      return null;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `查询歌单失败: ${error.message}`);
      return null;
    }
  }

  /**
   * 添加歌曲到歌单
   */
  async addSongToPlaylist(playlistId: string, songFilePath: string): Promise<boolean> {
    await this.ensureInitialized();

    if (!this.rdbStore) {
      Logger.error('heanup PlaylistTable', '数据库未初始化');
      return false;
    }

    try {
      Logger.info('heanup PlaylistTable', `开始添加歌曲到歌单: playlistId=${playlistId}, songFilePath=${songFilePath}`);

      // 检查歌曲是否已在歌单中
      const isInPlaylist = await this.isSongInPlaylist(playlistId, songFilePath);
      if (isInPlaylist) {
        Logger.info('heanup PlaylistTable', '歌曲已在歌单中');
        return false;
      }

      const id = this.generateId();
      const addTime = new Date().toISOString();
      const sql = 'INSERT INTO playlistSongTable (id, playlistId, songFilePath, addTime, sortOrder) VALUES (?, ?, ?, ?, ?)';
      const params = [id, playlistId, songFilePath, addTime, 0];

      Logger.info('heanup PlaylistTable', `执行SQL插入: ${sql}`);
      await this.rdbStore.executeSql(sql, params);
      Logger.info('heanup PlaylistTable', '歌曲插入成功');

      // 更新歌单歌曲数量
      Logger.info('heanup PlaylistTable', '开始更新歌单歌曲数量');
      await this.updatePlaylistSongCount(playlistId);

      Logger.info('heanup PlaylistTable', `歌曲添加到歌单成功: ${songFilePath}`);
      return true;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `添加歌曲到歌单失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 批量添加歌曲到歌单
   */
  async addSongsToPlaylist(playlistId: string, songFilePaths: string[]): Promise<boolean> {
    await this.ensureInitialized();

    if (!this.rdbStore) {
      Logger.error('heanup PlaylistTable', '数据库未初始化');
      return false;
    }

    if (!songFilePaths || songFilePaths.length === 0) {
      Logger.error('heanup PlaylistTable', '歌曲路径列表为空');
      return false;
    }

    try {
      Logger.info('heanup PlaylistTable', `开始批量添加歌曲到歌单: playlistId=${playlistId}, 歌曲数量=${songFilePaths.length}`);

      let successCount = 0;
      for (const songFilePath of songFilePaths) {
        try {
          // 检查歌曲是否已在歌单中
          const isInPlaylist = await this.isSongInPlaylist(playlistId, songFilePath);
          if (isInPlaylist) {
            Logger.info('heanup PlaylistTable', `歌曲已在歌单中，跳过: ${songFilePath}`);
            continue;
          }

          const id = this.generateId();
          const addTime = new Date().toISOString();
          const sql = 'INSERT INTO playlistSongTable (id, playlistId, songFilePath, addTime, sortOrder) VALUES (?, ?, ?, ?, ?)';
          const params = [id, playlistId, songFilePath, addTime, 0];

          await this.rdbStore.executeSql(sql, params);
          successCount++;
          Logger.info('heanup PlaylistTable', `成功添加歌曲: ${songFilePath}`);
        } catch (error) {
          Logger.error('heanup PlaylistTable', `添加歌曲失败: ${songFilePath}, 错误: ${error.message}`);
        }
      }

      // 更新歌单歌曲数量
      Logger.info('heanup PlaylistTable', '开始更新歌单歌曲数量');
      await this.updatePlaylistSongCount(playlistId);

      Logger.info('heanup PlaylistTable', `批量添加歌曲完成，成功添加 ${successCount} 首，共 ${songFilePaths.length} 首`);
      return successCount > 0;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `批量添加歌曲到歌单失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 从歌单中移除歌曲
   */
  async removeSongFromPlaylist(playlistId: string, songFilePath: string): Promise<boolean> {
    if (!this.rdbStore) {
      return false;
    }

    try {
      const sql = 'DELETE FROM playlistSongTable WHERE playlistId = ? AND songFilePath = ?';
      await this.rdbStore.executeSql(sql, [playlistId, songFilePath]);
      
      // 更新歌单歌曲数量
      await this.updatePlaylistSongCount(playlistId);
      
      Logger.info('heanup PlaylistTable', `歌曲从歌单移除成功: ${songFilePath}`);
      return true;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `从歌单移除歌曲失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 查询歌单中的歌曲
   */
  async queryPlaylistSongs(playlistId: string): Promise<PlaylistSong[]> {
    await this.ensureInitialized();

    if (!this.rdbStore) {
      Logger.error('heanup PlaylistTable', '数据库未初始化');
      return [];
    }

    try {
      const sql = 'SELECT * FROM playlistSongTable WHERE playlistId = ? ORDER BY sortOrder ASC, addTime ASC';
      Logger.info('heanup PlaylistTable', `queryPlaylistSongs: 开始查询歌单歌曲, playlistId=${playlistId}`);
      Logger.info('heanup PlaylistTable', `queryPlaylistSongs: SQL=${sql}`);
      
      const resultSet = await this.rdbStore.querySql(sql, [playlistId]);
      Logger.info('heanup PlaylistTable', `queryPlaylistSongs: 查询执行完成, rowCount=${resultSet.rowCount}`);
      
      const songs: PlaylistSong[] = [];
      if (resultSet.goToFirstRow()) {
        Logger.info('heanup PlaylistTable', 'queryPlaylistSongs: 移动到第一行成功');
        do {
          const song = new PlaylistSong(
            resultSet.getString(resultSet.getColumnIndex('id')),
            resultSet.getString(resultSet.getColumnIndex('playlistId')),
            resultSet.getString(resultSet.getColumnIndex('songFilePath')),
            resultSet.getString(resultSet.getColumnIndex('addTime')),
            resultSet.getLong(resultSet.getColumnIndex('sortOrder'))
          );
          songs.push(song);
          Logger.info('heanup PlaylistTable', `queryPlaylistSongs: 添加歌曲, songFilePath=${song.songFilePath}`);
        } while (resultSet.goToNextRow());
      } else {
        Logger.info('heanup PlaylistTable', 'queryPlaylistSongs: 没有数据，无法移动到第一行');
      }
      
      resultSet.close();
      Logger.info('heanup PlaylistTable', `queryPlaylistSongs: 查询到 ${songs.length} 首歌曲`);
      return songs;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `queryPlaylistSongs: 查询歌单歌曲失败: ${error.message}`);
      Logger.error('heanup PlaylistTable', `queryPlaylistSongs: 错误堆栈: ${error.stack || '无堆栈信息'}`);
      return [];
    }
  }

  /**
   * 检查歌曲是否在歌单中
   */
  async isSongInPlaylist(playlistId: string, songFilePath: string): Promise<boolean> {
    if (!this.rdbStore) {
      return false;
    }

    try {
      const sql = 'SELECT COUNT(*) as count FROM playlistSongTable WHERE playlistId = ? AND songFilePath = ?';
      const resultSet = await this.rdbStore.querySql(sql, [playlistId, songFilePath]);
      
      let isInPlaylist = false;
      if (resultSet.goToFirstRow()) {
        const count = resultSet.getLong(resultSet.getColumnIndex('count'));
        isInPlaylist = count > 0;
      }
      
      resultSet.close();
      return isInPlaylist;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `检查歌曲是否在歌单中失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 更新歌单歌曲数量
   */
  private async updatePlaylistSongCount(playlistId: string): Promise<void> {
    if (!this.rdbStore) {
      return;
    }

    try {
      const countSql = 'SELECT COUNT(*) as count FROM playlistSongTable WHERE playlistId = ?';
      const resultSet = await this.rdbStore.querySql(countSql, [playlistId]);
      
      let count = 0;
      if (resultSet.goToFirstRow()) {
        count = resultSet.getLong(resultSet.getColumnIndex('count'));
      }
      resultSet.close();
      
      const updateSql = 'UPDATE playlistTable SET songCount = ?, updateTime = ? WHERE id = ?';
      const updateTime = new Date().toISOString();
      await this.rdbStore.executeSql(updateSql, [count, updateTime, playlistId]);
    } catch (error) {
      Logger.error('heanup PlaylistTable', `更新歌单歌曲数量失败: ${error.message}`);
    }
  }

  /**
   * 更新歌单排序
   */
  async updatePlaylistSortOrder(playlistId: string, sortOrder: number): Promise<boolean> {
    if (!this.rdbStore) {
      return false;
    }

    try {
      const updateTime = new Date().toISOString();
      const sql = 'UPDATE playlistTable SET sortOrder = ?, updateTime = ? WHERE id = ?';
      await this.rdbStore.executeSql(sql, [sortOrder, updateTime, playlistId]);
      
      Logger.info('heanup PlaylistTable', `歌单排序更新成功: ${playlistId}`);
      return true;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `更新歌单排序失败: ${error.message}`);
      return false;
    }
  }

  /**
   * 更新歌单歌曲排序
   */
  async updatePlaylistSongSortOrder(playlistId: string, songFilePath: string, sortOrder: number): Promise<boolean> {
    if (!this.rdbStore) {
      return false;
    }

    try {
      const sql = 'UPDATE playlistSongTable SET sortOrder = ? WHERE playlistId = ? AND songFilePath = ?';
      await this.rdbStore.executeSql(sql, [sortOrder, playlistId, songFilePath]);
      
      Logger.info('heanup PlaylistTable', `歌单歌曲排序更新成功: ${songFilePath}`);
      return true;
    } catch (error) {
      Logger.error('heanup PlaylistTable', `更新歌单歌曲排序失败: ${error.message}`);
      return false;
    }
  }
}