import 'package:sqflite/sqflite.dart';
import 'package:wow_english/common/core/user_util.dart';

class VideoProgressCacheEntry {
  const VideoProgressCacheEntry({
    required this.progressSeconds,
    required this.durationSeconds,
  });

  final int progressSeconds;
  final int durationSeconds;
}

/// 使用 SQLite 按用户保存视频播放进度。
class VideoProgressCache {
  static const _databaseName = 'video_progress.db';
  static const _databaseVersion = 1;
  static const _tableName = 'video_progress';
  static const _maxRecordCount = 100;

  static Database? _database;
  static Future<Database>? _openingDatabase;
  static Future<void> _writeQueue = Future<void>.value();

  static Future<VideoProgressCacheEntry?> get(int videoId) async {
    final userId = _currentUserId;
    if (userId == null) return null;
    await _writeQueue;
    final database = await _getDatabase();
    final rows = await database.query(
      _tableName,
      columns: const ['progress_seconds', 'duration_seconds'],
      where: 'user_id = ? AND video_id = ?',
      whereArgs: [userId, videoId],
      limit: 1,
    );
    if (rows.isEmpty) return null;
    return VideoProgressCacheEntry(
      progressSeconds: _toInt(rows.first['progress_seconds']),
      durationSeconds: _toInt(rows.first['duration_seconds']),
    );
  }

  static Future<void> save({
    required int videoId,
    required int progressSeconds,
    required int durationSeconds,
  }) async {
    final userId = _currentUserId;
    if (userId == null) return;
    await _enqueueWrite(() async {
      final database = await _getDatabase();
      await database.transaction((transaction) async {
        await transaction.insert(
          _tableName,
          {
            'user_id': userId,
            'video_id': videoId,
            'progress_seconds': progressSeconds,
            'duration_seconds': durationSeconds,
            'updated_at': DateTime.now().millisecondsSinceEpoch,
          },
          conflictAlgorithm: ConflictAlgorithm.replace,
        );
        await transaction.rawDelete(
          '''
        DELETE FROM $_tableName
        WHERE user_id = ? AND video_id NOT IN (
          SELECT video_id FROM $_tableName
          WHERE user_id = ?
          ORDER BY updated_at DESC
          LIMIT ?
        )
        ''',
          [userId, userId, _maxRecordCount],
        );
      });
    });
  }

  static Future<void> remove(int videoId) async {
    final userId = _currentUserId;
    if (userId == null) return;
    await _enqueueWrite(() async {
      final database = await _getDatabase();
      await database.delete(
        _tableName,
        where: 'user_id = ? AND video_id = ?',
        whereArgs: [userId, videoId],
      );
    });
  }

  static int? get _currentUserId => UserUtil.getUser()?.id;

  static Future<void> _enqueueWrite(Future<void> Function() operation) {
    final future = _writeQueue.then((_) => operation());
    _writeQueue = future.then<void>((_) {}, onError: (_, __) {});
    return future;
  }

  static Future<Database> _getDatabase() async {
    final database = _database;
    if (database != null && database.isOpen) return database;
    final openingDatabase = _openingDatabase;
    if (openingDatabase != null) return openingDatabase;
    final future = _openDatabase();
    _openingDatabase = future;
    try {
      final result = await future;
      _database = result;
      return result;
    } finally {
      _openingDatabase = null;
    }
  }

  static Future<Database> _openDatabase() async {
    final databasesPath = await getDatabasesPath();
    return openDatabase(
      '$databasesPath/$_databaseName',
      version: _databaseVersion,
      onCreate: (database, _) => database.execute('''
        CREATE TABLE $_tableName (
          user_id INTEGER NOT NULL,
          video_id INTEGER NOT NULL,
          progress_seconds INTEGER NOT NULL,
          duration_seconds INTEGER NOT NULL,
          updated_at INTEGER NOT NULL,
          PRIMARY KEY (user_id, video_id)
        )
      '''),
    );
  }

  static int _toInt(Object? value) =>
      value is num ? value.toInt() : int.tryParse('$value') ?? 0;
}