CREATE TABLE feeds (
	feed_id TEXT PRIMARY KEY NOT NULL,
	label VARCHAR NOT NULL,
	website TEXT,
	feed_url TEXT,
	icon_url TEXT,
	sort_index INTEGER
);

CREATE TABLE categories (
	category_id TEXT PRIMARY KEY NOT NULL,
	label TEXT NOT NULL,
	parent_id TEXT NOT NULL,
	sort_index INTEGER
);

CREATE TABLE tags (
	tag_id TEXT PRIMARY KEY NOT NULL,
	label TEXT NOT NULL,
	color TEXT,
	sort_index INTEGER
);

CREATE TABLE articles (
	article_id TEXT PRIMARY KEY NOT NULL,
	title TEXT,
	author TEXT,
	feed_id TEXT NOT NULL REFERENCES feeds(feed_id),
	url TEXT,
	timestamp DATETIME NOT NULL,
	html TEXT,
	summary TEXT,
	direction INTEGER,
	unread INTEGER NOT NULL,
	marked INTEGER NOT NULL
);

CREATE TABLE enclosures (
	article_id TEXT PRIMARY KEY NOT NULL REFERENCES articles(article_id),
	url TEXT NOT NULL,
	enclosure_type INTEGER NOT NULL
);

CREATE TABLE taggings (
	article_id TEXT NOT NULL REFERENCES articles(article_id),
	tag_id TEXT NOT NULL REFERENCES tags(tag_id),
	PRIMARY KEY (article_id, tag_id)
);

CREATE TABLE feed_mapping (
	feed_id TEXT NOT NULL REFERENCES feeds(feed_id),
	category_id TEXT NOT NULL REFERENCES categories(category_id),
	PRIMARY KEY (feed_id, category_id)
);

CREATE TABLE fav_icons (
	feed_id TEXT NOT NULL REFERENCES feeds(feed_id),
	timestamp DATETIME NOT NULL,
	format TEXT,
	etag TEXT,
	source_url TEXT,
	data BLOB,
	PRIMARY KEY (feed_id)
);

CREATE VIRTUAL TABLE IF NOT EXISTS fts_table USING fts4 (content='articles', article_id, summary, title, author);

CREATE INDEX article_unread_feeds ON articles (feed_id, unread);
CREATE INDEX article_marked_feeds ON articles (feed_id, marked);

CREATE TRIGGER on_delete_feed_trigger
	AFTER DELETE ON feeds
	BEGIN
		DELETE FROM feed_mapping WHERE feed_mapping.feed_id=OLD.feed_id;
		DELETE FROM articles WHERE articles.feed_id=OLD.feed_id;
		DELETE FROM fav_icons WHERE fav_icons.feed_id=OLD.feed_id;
	END;

CREATE TRIGGER on_delete_category_trigger
	AFTER DELETE ON categories
	BEGIN
		DELETE FROM feed_mapping WHERE feed_mapping.category_id=OLD.category_id;
	END;

CREATE TRIGGER on_delete_article_trigger
	AFTER DELETE ON articles
	BEGIN
		DELETE FROM taggings WHERE taggings.article_id=OLD.article_id;
		DELETE FROM enclosures WHERE enclosures.article_id=OLD.article_id;
	END;

CREATE TRIGGER on_delete_tag_trigger
	AFTER DELETE ON tags
	BEGIN
		DELETE FROM taggings WHERE taggings.tag_id=OLD.tag_id;
	END;