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

CREATE TABLE channels(
	id STRING PRIMARY KEY,
	name STRING);
CREATE TABLE playlists(
	id STRING PRIMARY KEY,
	name STRING,
	author STRING);
CREATE TABLE 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 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 playlist_videos(
	video_id STRING,
	playlist_id STRING,
	PRIMARY KEY (video_id, playlist_id));
CREATE TABLE crawler(
	channel_id STRING PRIMARY KEY, -- TODO: rename so it makes sense for playlists
	crawled_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE websub(
	channel_id STRING PRIMARY KEY,
	subscribed_until DATETIME DEFAULT CURRENT_TIMESTAMP,
	pending BOOLEAN DEFAULT 0);
CREATE TABLE flags(
	user STRING,
	video_id STRING,
	display TEXT CHECK(display IN (NULL, 'pinned', 'hidden')),
	PRIMARY KEY (user, video_id));
CREATE TABLE cipher(
	rowid INTEGER PRIMARY KEY CHECK (rowid = 0), -- limit to 1 row
	url TEXT,
	sts INTEGER,
	algorithm TEXT);

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

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

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

CREATE TABLE users(
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	password TEXT NOT NULL,
	token TEXT NOT NULL); -- TODO: move into user_tokens table
-- CREATE TABLE user_tokens( -- stores retractable url tokens for feeds.
-- 	user_id INTEGER NOT NULL,
-- 	token TEXT NOT NULL);
