import 'package:sqflite/sqflite.dart';
import 'package:worldhopper/database/database.dart';
import 'package:worldhopper/database/tables/reading_progress_table.dart';
import 'package:worldhopper/models/reading_progress.dart';

/// Repository for managing reading progress data
class ReadingProgressRepository {
  final DatabaseHelper _dbHelper;

  ReadingProgressRepository({DatabaseHelper? dbHelper})
      : _dbHelper = dbHelper ?? DatabaseHelper.instance;

  /// Get database instance
  Future<Database> get _db async => await _dbHelper.database;

  /// Get reading progress for a publication
  Future<ReadingProgress?> getProgress(
    String publicationId,
    String serverId,
  ) async {
    final db = await _db;
    final List<Map<String, dynamic>> maps = await db.query(
      ReadingProgressTable.tableName,
      where: 'publication_id = ? AND server_id = ?',
      whereArgs: [publicationId, serverId],
    );

    if (maps.isEmpty) return null;
    return ReadingProgress.fromDatabase(maps.first);
  }

  /// Save or update reading progress
  Future<void> saveProgress(ReadingProgress progress) async {
    final db = await _db;

    // First check if progress exists for this publication/server combo
    final existing =
        await getProgress(progress.publicationId, progress.serverId);

    if (existing != null) {
      // Update existing entry keeping the same ID
      final updated = progress.copyWith(id: existing.id);
      await db.insert(
        ReadingProgressTable.tableName,
        updated.toDatabase(),
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    } else {
      // Insert new entry
      await db.insert(
        ReadingProgressTable.tableName,
        progress.toDatabase(),
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
  }

  /// Update current page for a publication
  Future<void> updateCurrentPage(
    String publicationId,
    String serverId,
    int currentPage,
  ) async {
    final db = await _db;
    await db.update(
      ReadingProgressTable.tableName,
      {
        'current_page': currentPage,
        'last_read_at': DateTime.now().millisecondsSinceEpoch,
      },
      where: 'publication_id = ? AND server_id = ?',
      whereArgs: [publicationId, serverId],
    );
  }

  /// Get all reading progress for a server
  Future<List<ReadingProgress>> getProgressByServer(String serverId) async {
    final db = await _db;
    final List<Map<String, dynamic>> maps = await db.query(
      ReadingProgressTable.tableName,
      where: 'server_id = ?',
      whereArgs: [serverId],
      orderBy: 'last_read_at DESC',
    );

    return List.generate(maps.length, (i) {
      return ReadingProgress.fromDatabase(maps[i]);
    });
  }

  /// Get recently read publications across all servers
  Future<List<ReadingProgress>> getRecentlyRead({int limit = 20}) async {
    final db = await _db;
    final List<Map<String, dynamic>> maps = await db.query(
      ReadingProgressTable.tableName,
      orderBy: 'last_read_at DESC',
      limit: limit,
    );

    return List.generate(maps.length, (i) {
      return ReadingProgress.fromDatabase(maps[i]);
    });
  }

  /// Delete progress for a publication
  Future<void> deleteProgress(String publicationId, String serverId) async {
    final db = await _db;
    await db.delete(
      ReadingProgressTable.tableName,
      where: 'publication_id = ? AND server_id = ?',
      whereArgs: [publicationId, serverId],
    );
  }

  /// Delete all progress for a server
  Future<void> deleteProgressByServer(String serverId) async {
    final db = await _db;
    await db.delete(
      ReadingProgressTable.tableName,
      where: 'server_id = ?',
      whereArgs: [serverId],
    );
  }

  /// Get total count of publications in progress
  Future<int> getInProgressCount() async {
    final db = await _db;
    final result = await db.rawQuery(
      'SELECT COUNT(*) as count FROM ${ReadingProgressTable.tableName} '
      'WHERE current_page > 0 AND current_page < total_pages - 1',
    );
    return Sqflite.firstIntValue(result) ?? 0;
  }

  /// Get total count of completed publications
  Future<int> getCompletedCount() async {
    final db = await _db;
    final result = await db.rawQuery(
      'SELECT COUNT(*) as count FROM ${ReadingProgressTable.tableName} '
      'WHERE current_page >= total_pages - 1',
    );
    return Sqflite.firstIntValue(result) ?? 0;
  }
}