worldhopper/lib/database/database.dart

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:worldhopper/config/constants.dart';
import 'package:worldhopper/database/tables/servers_table.dart';
import 'package:worldhopper/database/tables/reading_progress_table.dart';
import 'package:worldhopper/database/tables/publications_table.dart';

/// Database helper for SQLite operations
class DatabaseHelper {
  DatabaseHelper._();

  static final DatabaseHelper instance = DatabaseHelper._();
  static Database? _database;

  /// Get the database instance, creating it if necessary
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  /// Initialize the database
  Future<Database> _initDatabase() async {
    final databasePath = await getDatabasesPath();
    final path = join(databasePath, AppConstants.databaseName);

    return await openDatabase(
      path,
      version: AppConstants.databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
      onConfigure: _onConfigure,
    );
  }

  /// Configure database settings
  Future<void> _onConfigure(Database db) async {
    // Enable foreign key constraints
    await db.execute('PRAGMA foreign_keys = ON');
  }

  /// Create database tables
  Future<void> _onCreate(Database db, int version) async {
    // Create servers table
    await db.execute(ServersTable.createTable);

    // Create publications table
    await db.execute(PublicationsTable.createTable);
    await db.execute(PublicationsTable.createServerIndex);
    await db.execute(PublicationsTable.createServerOpdsIndex);
    await db.execute(PublicationsTable.createAccessedIndex);

    // Create reading_progress table
    await db.execute(ReadingProgressTable.createTable);
    await db.execute(ReadingProgressTable.createPublicationIndex);
    await db.execute(ReadingProgressTable.createServerIndex);
    await db.execute(ReadingProgressTable.createCacheIdIndex);
  }

  /// Handle database upgrades
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // No backwards compatibility needed - just recreate the database
    // Drop all existing tables
    await db.execute(ReadingProgressTable.dropTable);
    await db.execute(PublicationsTable.dropTable);
    await db.execute(ServersTable.dropTable);
    
    // Recreate with new schema
    await _onCreate(db, newVersion);
  }

  /// Close the database
  Future<void> close() async {
    final db = await database;
    await db.close();
    _database = null;
  }

  /// Delete the database (for testing purposes)
  Future<void> deleteDatabase() async {
    final databasePath = await getDatabasesPath();
    final path = join(databasePath, AppConstants.databaseName);
    await databaseFactory.deleteDatabase(path);
    _database = null;
  }
}