/// SQL schema for the reading_progress table
class ReadingProgressTable {
  ReadingProgressTable._();

  static const String tableName = 'reading_progress';

  /// SQL statement to create the reading_progress table
  static const String createTable = '''
    CREATE TABLE $tableName (
      id TEXT PRIMARY KEY,
      publication_id TEXT NOT NULL,
      server_id TEXT NOT NULL,
      current_page INTEGER NOT NULL DEFAULT 0,
      total_pages INTEGER NOT NULL,
      epub_location TEXT,
      last_read_at INTEGER NOT NULL,
      publication_cache_id TEXT,
      series_feed_url TEXT,
      sync_hash TEXT,
      FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
      FOREIGN KEY (publication_cache_id) REFERENCES publications(id) ON DELETE SET NULL,
      UNIQUE(publication_id, server_id)
    )
  ''';

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

  /// Create index on publication_id for faster lookups
  static const String createPublicationIndex = '''
    CREATE INDEX idx_reading_progress_publication_id
    ON $tableName(publication_id)
  ''';

  /// Create index on server_id for faster lookups
  static const String createServerIndex = '''
    CREATE INDEX idx_reading_progress_server_id
    ON $tableName(server_id)
  ''';

  /// Create index on publication_cache_id for faster lookups
  static const String createCacheIdIndex = '''
    CREATE INDEX idx_reading_progress_cache_id
    ON $tableName(publication_cache_id)
  ''';
}