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';
import 'package:worldhopper/database/tables/series_cover_page_table.dart';
import 'package:worldhopper/database/tables/series_two_page_mode_table.dart';
import 'package:worldhopper/database/tables/downloaded_series_table.dart';
import 'package:worldhopper/database/tables/downloaded_chapters_table.dart';

/// 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);
  },
  7: (db) async {
    await db.execute(SeriesToPageModeTable.createTable);
    await db.execute(SeriesCoverPageTable.createTable);
  },
  9: (db) async {
    await db.execute('ALTER TABLE servers ADD COLUMN software_type TEXT');
  },
  10: (db) async {
    await db.execute('ALTER TABLE servers ADD COLUMN software_username TEXT');
    await db.execute('ALTER TABLE servers ADD COLUMN software_password TEXT');
  },
  11: (db) async {
    await db.execute(DownloadedSeriesTable.createTable);
    await db.execute(DownloadedSeriesTable.createServerIndex);
    await db.execute(DownloadedSeriesTable.createStatusIndex);
    await db.execute(DownloadedChaptersTable.createTable);
    await db.execute(DownloadedChaptersTable.createSeriesIndex);
    await db.execute(DownloadedChaptersTable.createServerIndex);
    await db.execute(DownloadedChaptersTable.createStatusIndex);
  },
  12: (db) async {
    // Check if column already exists (migration 11 creates the table with this column)
    final columns = await db.rawQuery('PRAGMA table_info(downloaded_chapters)');
    final hasErrorMessage =
        columns.any((col) => col['name'] == 'error_message');
    if (!hasErrorMessage) {
      await db.execute(
          'ALTER TABLE downloaded_chapters ADD COLUMN error_message TEXT');
    }
  },
  13: (db) async {
    // Rename opds_id → entry_id in publications table.
    // Using table-recreate approach for compatibility with SQLite < 3.25.0.
    // Wrapped in a transaction to ensure atomicity — if any step fails,
    // the entire migration rolls back and the original table is preserved.
    await db.transaction((txn) async {
      await txn.execute('''
        CREATE TABLE publications_new (
          id TEXT PRIMARY KEY,
          server_id TEXT NOT NULL,
          entry_id TEXT NOT NULL,
          title TEXT NOT NULL,
          authors TEXT,
          summary TEXT,
          content TEXT,
          cover_path TEXT,
          thumbnail_path TEXT,
          cover_url TEXT,
          thumbnail_url TEXT,
          series TEXT,
          series_position REAL,
          publisher TEXT,
          language TEXT,
          isbn TEXT,
          categories TEXT,
          links TEXT,
          stream_link TEXT,
          published INTEGER,
          updated INTEGER,
          first_cached_at INTEGER NOT NULL,
          last_cached_at INTEGER NOT NULL,
          last_accessed_at INTEGER NOT NULL,
          FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
          UNIQUE(server_id, entry_id)
        )
      ''');
      await txn.execute('''
        INSERT INTO publications_new
        SELECT id, server_id, opds_id, title, authors, summary, content,
               cover_path, thumbnail_path, cover_url, thumbnail_url,
               series, series_position, publisher, language, isbn,
               categories, links, stream_link, published, updated,
               first_cached_at, last_cached_at, last_accessed_at
        FROM publications
      ''');
      await txn.execute('DROP TABLE publications');
      await txn.execute('ALTER TABLE publications_new RENAME TO publications');
      // Recreate indexes
      await txn.execute(PublicationsTable.createServerIndex);
      await txn.execute(PublicationsTable.createServerEntryIndex);
      await txn.execute(PublicationsTable.createAccessedIndex);
    });
  },
  14: (db) async {
    // Unify credentials: migrate software_username/software_password into
    // username/password where the user had software creds but no OPDS creds,
    // then drop the software credential columns.
    await db.transaction((txn) async {
      // Preserve software credentials for servers that only had those
      await txn.execute('''
        UPDATE servers
        SET username = software_username, password = software_password
        WHERE software_username IS NOT NULL
          AND (username IS NULL OR username = '')
      ''');

      // Recreate table without software credential columns
      await txn.execute('''
        CREATE TABLE servers_new (
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL,
          url TEXT NOT NULL,
          username TEXT,
          password TEXT,
          created_at INTEGER NOT NULL,
          last_synced_at INTEGER,
          software_type TEXT
        )
      ''');
      await txn.execute('''
        INSERT INTO servers_new
        SELECT id, name, url, username, password, created_at,
               last_synced_at, software_type
        FROM servers
      ''');
      await txn.execute('DROP TABLE servers');
      await txn.execute('ALTER TABLE servers_new RENAME TO servers');
    });
  },
};

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

    // Create series_two_page_mode table
    await db.execute(SeriesToPageModeTable.createTable);

    // Create series_cover_page table
    await db.execute(SeriesCoverPageTable.createTable);

    // Create downloaded_series table
    await db.execute(DownloadedSeriesTable.createTable);
    await db.execute(DownloadedSeriesTable.createServerIndex);
    await db.execute(DownloadedSeriesTable.createStatusIndex);

    // Create downloaded_chapters table
    await db.execute(DownloadedChaptersTable.createTable);
    await db.execute(DownloadedChaptersTable.createSeriesIndex);
    await db.execute(DownloadedChaptersTable.createServerIndex);
    await db.execute(DownloadedChaptersTable.createStatusIndex);
  }

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