import 'package:sqflite/sqflite.dart';
import 'package:worldhopper/database/database.dart';
import 'package:worldhopper/database/tables/servers_table.dart';
import 'package:worldhopper/models/opds_server.dart';

/// Repository for managing OPDS server data
class ServerRepository {
  final DatabaseHelper _dbHelper;

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

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

  /// Get all servers ordered by name
  Future<List<OPDSServer>> getAllServers() async {
    final db = await _db;
    final List<Map<String, dynamic>> maps = await db.query(
      ServersTable.tableName,
      orderBy: 'name ASC',
    );

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

  /// Get a server by ID
  Future<OPDSServer?> getServer(String id) async {
    final db = await _db;
    final List<Map<String, dynamic>> maps = await db.query(
      ServersTable.tableName,
      where: 'id = ?',
      whereArgs: [id],
    );

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

  /// Add a new server
  Future<void> addServer(OPDSServer server) async {
    final db = await _db;
    await db.insert(
      ServersTable.tableName,
      server.toDatabase(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// Update an existing server
  Future<void> updateServer(OPDSServer server) async {
    final db = await _db;
    await db.update(
      ServersTable.tableName,
      server.toDatabase(),
      where: 'id = ?',
      whereArgs: [server.id],
    );
  }

  /// Delete a server by ID
  Future<void> deleteServer(String id) async {
    final db = await _db;
    await db.delete(
      ServersTable.tableName,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// Update the last synced timestamp for a server
  Future<void> updateLastSynced(String id, DateTime timestamp) async {
    final db = await _db;
    await db.update(
      ServersTable.tableName,
      {'last_synced_at': timestamp.millisecondsSinceEpoch},
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// Check if a server with the given URL already exists
  Future<bool> serverExists(String url) async {
    final db = await _db;
    final List<Map<String, dynamic>> maps = await db.query(
      ServersTable.tableName,
      where: 'url = ?',
      whereArgs: [url],
    );

    return maps.isNotEmpty;
  }

  /// Get server count
  Future<int> getServerCount() async {
    final db = await _db;
    final result = await db.rawQuery(
      'SELECT COUNT(*) as count FROM ${ServersTable.tableName}',
    );
    return Sqflite.firstIntValue(result) ?? 0;
  }
}