-- --------------- --
-- YOUTUBE RELATED --
-- --------------- --

CREATE TABLE IF NOT EXISTS channels(
	id STRING PRIMARY KEY,
	name STRING);
CREATE TABLE IF NOT EXISTS playlists(
	id STRING PRIMARY KEY,
	name STRING,
	author STRING);
CREATE TABLE IF NOT EXISTS subscriptions(
	channel_id STRING, -- TODO: rename so it makes sense for playlists
	user STRING,
	type TEXT DEFAULT 'channel' NOT NULL CHECK(type IN ('channel', 'playlist')),
	PRIMARY KEY (channel_id, user));
CREATE TABLE IF NOT EXISTS videos(
	id STRING PRIMARY KEY,
	channel_id STRING,
	title STRING,
	length INTEGER,
	livestream BOOLEAN DEFAULT 0,
	published DATETIME,
	crawled DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS playlist_videos(
	video_id STRING,
	playlist_id STRING,
	PRIMARY KEY (video_id, playlist_id));
CREATE TABLE IF NOT EXISTS crawler(
	channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
	crawled_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS websub(
	channel_id STRING PRIMARY KEY,
	subscribed_until DATETIME DEFAULT CURRENT_TIMESTAMP,
	pending BOOLEAN DEFAULT 0);
CREATE TABLE IF NOT EXISTS flags(
	user STRING,
	video_id STRING,
	display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')),
	PRIMARY KEY (user, video_id));

-- -------------- --
-- REDDIT RELATED --
-- -------------- --

CREATE TABLE IF NOT EXISTS subreddits(
	subreddit STRING COLLATE NOCASE, -- e.g. videos (not /r/...)
	user STRING,
	PRIMARY KEY (subreddit, user));

-- ------------ --
-- ANTI CAPTCHA --
-- ------------ --

CREATE TABLE IF NOT EXISTS captcha_requests(
	nonce STRING,
	url STRING,
	action STRING,
	timestamp DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS captcha_cookies(
	name STRING,
	value STRING);

-- ------ --
-- SHARED --
-- ------ --

CREATE TABLE IF NOT EXISTS users(
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL UNIQUE,
	password TEXT NOT NULL,
	is_admin BOOLEAN DEFAULT 0,
	token TEXT NOT NULL); -- TODO: deprecated; use users.id instead
CREATE TABLE IF NOT EXISTS user_tokens( -- stores revocable url tokens for feeds.
	user_id INTEGER PRIMARY KEY NOT NULL,
	token TEXT NOT NULL);
