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';
import 'package:worldhopper/database/tables/series_reading_mode_table.dart';

/// Baseline version: all versions before this use destructive migration.
const int _baselineVersion = 4;

/// Incremental migrations from one version to the next.
/// Key is the target version (e.g. 5 means "migrate from 4 to 5").
final Map<int, Future<void> Function(Database)> _migrations = {
  5: (db) async {
    await db.execute(
        'ALTER TABLE reading_progress ADD COLUMN series_feed_url TEXT');
  },
  6: (db) async {
    await db.execute(SeriesReadingModeTable.createTable);
  },
};

/// 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);

    // Create series_reading_mode table
    await db.execute(SeriesReadingModeTable.createTable);
  }

  /// Handle database upgrades
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // Apply incremental migrations from oldVersion+1 to newVersion
    for (var version = oldVersion + 1; version <= newVersion; version++) {
      final migration = _migrations[version];
      if (migration != null) {
        await migration(db);
      }
    }
  }

  /// 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;
  }
}