worldhopper/lib/repositories/publication_repository.dart

import 'package:sqflite/sqflite.dart';
import 'package:worldhopper/database/database.dart';
import 'package:worldhopper/database/tables/publications_table.dart';
import 'package:worldhopper/models/publication.dart';

/// Repository for publication cache operations
class PublicationRepository {
  final DatabaseHelper _dbHelper;

  PublicationRepository(this._dbHelper);

  /// Get publication by server and entry ID
  Future<Publication?> getPublication(String serverId, String entryId) async {
    final db = await _dbHelper.database;
    final results = await db.query(
      PublicationsTable.tableName,
      where: 'server_id = ? AND entry_id = ?',
      whereArgs: [serverId, entryId],
    );

    if (results.isEmpty) return null;
    return Publication.fromDatabase(results.first);
  }

  /// Get publication by cache ID
  Future<Publication?> getPublicationById(String id) async {
    final db = await _dbHelper.database;
    final results = await db.query(
      PublicationsTable.tableName,
      where: 'id = ?',
      whereArgs: [id],
    );

    if (results.isEmpty) return null;
    return Publication.fromDatabase(results.first);
  }

  /// Save or update publication
  Future<void> savePublication(Publication publication) async {
    final db = await _dbHelper.database;
    await db.insert(
      PublicationsTable.tableName,
      publication.toDatabase(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// Update last accessed timestamp
  Future<void> updateLastAccessed(String id) async {
    final db = await _dbHelper.database;
    await db.update(
      PublicationsTable.tableName,
      {'last_accessed_at': DateTime.now().millisecondsSinceEpoch},
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// Get publications for a server
  Future<List<Publication>> getPublicationsByServer(String serverId) async {
    final db = await _dbHelper.database;
    final results = await db.query(
      PublicationsTable.tableName,
      where: 'server_id = ?',
      whereArgs: [serverId],
      orderBy: 'last_accessed_at DESC',
    );

    return results.map((row) => Publication.fromDatabase(row)).toList();
  }

  /// Get stale publications (not accessed recently and old)
  Future<List<Publication>> getStalePublications({
    int notAccessedDays = 60,
    int olderThanDays = 30,
  }) async {
    final db = await _dbHelper.database;
    final notAccessedDate = DateTime.now()
        .subtract(Duration(days: notAccessedDays))
        .millisecondsSinceEpoch;
    final olderThanDate = DateTime.now()
        .subtract(Duration(days: olderThanDays))
        .millisecondsSinceEpoch;

    final results = await db.query(
      PublicationsTable.tableName,
      where: 'last_accessed_at < ? AND last_cached_at < ?',
      whereArgs: [notAccessedDate, olderThanDate],
    );

    return results.map((row) => Publication.fromDatabase(row)).toList();
  }

  /// Delete publication
  Future<void> deletePublication(String id) async {
    final db = await _dbHelper.database;
    await db.delete(
      PublicationsTable.tableName,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// Delete publications for a server
  Future<void> deletePublicationsByServer(String serverId) async {
    final db = await _dbHelper.database;
    await db.delete(
      PublicationsTable.tableName,
      where: 'server_id = ?',
      whereArgs: [serverId],
    );
  }
}