schema.sql

CREATE TABLE IF NOT EXISTS animes (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  first_image TEXT,
  second_image TEXT,
  total_episodes INTEGER,
  season_number INTEGER,
  slug TEXT UNIQUE NOT NULL,
  is_new INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS anime_categories (
  anime_id TEXT NOT NULL,
  category_id INTEGER NOT NULL,
  PRIMARY KEY (anime_id, category_id),
  FOREIGN KEY (anime_id) REFERENCES animes(id),
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE IF NOT EXISTS episodes (
  id TEXT PRIMARY KEY,
  anime_id TEXT NOT NULL,
  episode_number INTEGER NOT NULL,
  backblaze_link TEXT,
  watch_link_1 TEXT,
  watch_link_2 TEXT,
  watch_link_3 TEXT,
  type TEXT,
  FOREIGN KEY (anime_id) REFERENCES animes(id)
);

CREATE TABLE IF NOT EXISTS other_names (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  anime_id TEXT NOT NULL,
  name TEXT NOT NULL,
  FOREIGN KEY (anime_id) REFERENCES animes(id)
);

CREATE TABLE IF NOT EXISTS related_animes (
  anime_id TEXT NOT NULL,
  related_id TEXT NOT NULL,
  PRIMARY KEY (anime_id, related_id),
  FOREIGN KEY (anime_id) REFERENCES animes(id),
  FOREIGN KEY (related_id) REFERENCES animes(id)
);

CREATE INDEX IF NOT EXISTS idx_animes_slug ON animes(slug);
CREATE INDEX IF NOT EXISTS idx_episodes_anime_id ON episodes(anime_id);