/// SQL schema for the downloaded_chapters table
class DownloadedChaptersTable {
  DownloadedChaptersTable._();

  static const String tableName = 'downloaded_chapters';

  /// SQL statement to create the downloaded_chapters table
  static const String createTable = '''
    CREATE TABLE $tableName (
      id TEXT PRIMARY KEY,
      series_id TEXT NOT NULL,
      server_id TEXT NOT NULL,
      entry_id TEXT NOT NULL,
      title TEXT NOT NULL,
      content_type TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending',
      file_path TEXT,
      total_pages INTEGER NOT NULL DEFAULT 0,
      downloaded_pages INTEGER NOT NULL DEFAULT 0,
      entry_json TEXT NOT NULL,
      cover_url TEXT,
      cover_path TEXT,
      sort_order INTEGER NOT NULL DEFAULT 0,
      error_message TEXT,
      downloaded_at INTEGER,
      updated_at INTEGER NOT NULL,
      FOREIGN KEY (series_id) REFERENCES downloaded_series(id) ON DELETE CASCADE,
      FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
      UNIQUE(series_id, entry_id)
    )
  ''';

  /// SQL statement to drop the downloaded_chapters table
  static const String dropTable = 'DROP TABLE IF EXISTS $tableName';

  /// Create index on series_id for faster lookups
  static const String createSeriesIndex = '''
    CREATE INDEX idx_downloaded_chapters_series_id
    ON $tableName(series_id)
  ''';

  /// Create index on server_id for cascade cleanup
  static const String createServerIndex = '''
    CREATE INDEX idx_downloaded_chapters_server_id
    ON $tableName(server_id)
  ''';

  /// Create index on status for filtering
  static const String createStatusIndex = '''
    CREATE INDEX idx_downloaded_chapters_status
    ON $tableName(status)
  ''';
}