/// SQL schema for the publications table
class PublicationsTable {
  PublicationsTable._();

  static const String tableName = 'publications';

  /// SQL statement to create the publications table
  static const String createTable = '''
    CREATE TABLE $tableName (
      id TEXT PRIMARY KEY,
      server_id TEXT NOT NULL,
      opds_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, opds_id)
    )
  ''';

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

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

  /// Create composite index on server_id and opds_id for unique lookups
  static const String createServerOpdsIndex = '''
    CREATE INDEX idx_publications_server_opds
    ON $tableName(server_id, opds_id)
  ''';

  /// Create index on last_accessed_at for cache cleanup
  static const String createAccessedIndex = '''
    CREATE INDEX idx_publications_last_accessed
    ON $tableName(last_accessed_at)
  ''';
}