PRAGMA foreign_keys=ON;
CREATE TABLE metadata(key TEXT PRIMARY KEY,value_json TEXT NOT NULL);
CREATE TABLE titles(
 id INTEGER PRIMARY KEY,type TEXT NOT NULL,title TEXT,description TEXT,
 year INTEGER,imdb REAL,rating REAL,duration TEXT,classification TEXT,
 image TEXT,cover TEXT,downloadas TEXT,playas TEXT,comments_enabled INTEGER,
 raw_json TEXT NOT NULL);
CREATE INDEX titles_type ON titles(type);
CREATE INDEX titles_title ON titles(title COLLATE NOCASE);
CREATE INDEX titles_year ON titles(year);
CREATE TABLE genres(id INTEGER PRIMARY KEY,title TEXT);
CREATE TABLE countries(id INTEGER PRIMARY KEY,title TEXT,image TEXT);
CREATE TABLE title_genres(title_id INTEGER REFERENCES titles(id),genre_id INTEGER REFERENCES genres(id),PRIMARY KEY(title_id,genre_id));
CREATE TABLE title_countries(title_id INTEGER REFERENCES titles(id),country_id INTEGER REFERENCES countries(id),PRIMARY KEY(title_id,country_id));
CREATE TABLE movie_sources(
 title_id INTEGER NOT NULL REFERENCES titles(id),source_index INTEGER NOT NULL,
 source_id INTEGER,quality TEXT,media_type TEXT,url TEXT,raw_json TEXT NOT NULL,
 PRIMARY KEY(title_id,source_index));
CREATE INDEX movie_sources_title ON movie_sources(title_id);
CREATE TABLE series_fetch_status(
 series_id INTEGER PRIMARY KEY REFERENCES titles(id),status TEXT NOT NULL,
 season_count INTEGER,source_url TEXT NOT NULL);
CREATE TABLE seasons(
 series_id INTEGER NOT NULL REFERENCES titles(id),season_index INTEGER NOT NULL,
 id INTEGER,title TEXT,raw_json TEXT NOT NULL,PRIMARY KEY(series_id,season_index));
CREATE TABLE episodes(
 series_id INTEGER NOT NULL,season_index INTEGER NOT NULL,episode_index INTEGER NOT NULL,
 id INTEGER,title TEXT,description TEXT,duration TEXT,downloadas TEXT,playas TEXT,raw_json TEXT NOT NULL,
 PRIMARY KEY(series_id,season_index,episode_index),
 FOREIGN KEY(series_id,season_index) REFERENCES seasons(series_id,season_index));
CREATE TABLE episode_sources(
 series_id INTEGER NOT NULL,season_index INTEGER NOT NULL,episode_index INTEGER NOT NULL,source_index INTEGER NOT NULL,
 source_id INTEGER,quality TEXT,media_type TEXT,url TEXT,raw_json TEXT NOT NULL,
 PRIMARY KEY(series_id,season_index,episode_index,source_index),
 FOREIGN KEY(series_id,season_index,episode_index) REFERENCES episodes(series_id,season_index,episode_index));
CREATE INDEX episode_sources_series ON episode_sources(series_id);