DROP TRIGGER IF EXISTS on_delete_article_trigger;
DROP TRIGGER IF EXISTS on_delete_feed_trigger;
DROP TRIGGER IF EXISTS on_delete_tag_trigger;

ALTER TABLE articles RENAME TO _articles_old;
ALTER TABLE enclosures RENAME TO _enclosures_old;
ALTER TABLE taggings RENAME TO _taggings_old;
ALTER TABLE thumbnails RENAME TO _thumbnails_old;
ALTER TABLE fav_icons RENAME TO _fav_icons_old;

CREATE TABLE articles (
	article_id TEXT PRIMARY KEY NOT NULL,
	title TEXT,
	author TEXT,
	feed_id TEXT NOT NULL,
	url TEXT,
	date TEXT NOT NULL,
    synced TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL,
	html TEXT,
	summary TEXT,
	direction INTEGER,
	unread INTEGER NOT NULL,
	marked INTEGER NOT NULL,
    scraped_content TEXT default null,
    plain_text TEXT default null,
    thumbnail_url TEXT default null
);

CREATE TABLE enclosures (
    article_id TEXT NOT NULL REFERENCES articles(article_id),
    url TEXT NOT NULL,
    mime_type TEXT default null,
    title TEXT default null,
    summary TEXT default null,
    thumbnail_url TEXT default null,
    filesize INTEGER default null,
    width INTEGER default null,
    height INTEGER default null,
    duration INTEGER default null,
    framerate DOUBLE default null,
    position DOUBLE default null,
    alternative TEXT default null,
    is_default BOOLEAN NOT NULL default 0,
    PRIMARY KEY (article_id, url)
);

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 thumbnails (
	article_id TEXT NOT NULL REFERENCES articles(article_id),
    date TEXT NOT NULL,
	format TEXT,
	etag TEXT,
	source_url TEXT,
	data BLOB,
	width INTEGER,
	height INTEGER,
    PRIMARY KEY (article_id)
);

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

INSERT INTO articles (article_id, title, author, feed_id, url, date, synced, html, summary, direction, unread, marked, scraped_content, plain_text, thumbnail_url)
  SELECT article_id, title, author, feed_id, url, date, synced, html, summary, direction, unread, marked, scraped_content, plain_text, thumbnail_url
  FROM _articles_old;

INSERT INTO enclosures (article_id, url, mime_type, title, summary, thumbnail_url, filesize, width, height, duration, framerate, position, alternative, is_default)
  SELECT article_id, url, mime_type, title, summary, thumbnail_url, filesize, width, height, duration, framerate, position, alternative, is_default
  FROM _enclosures_old;

INSERT INTO taggings (article_id, tag_id)
  SELECT article_id, tag_id
  FROM _taggings_old;

INSERT INTO thumbnails (article_id, date, format, etag, source_url, data, width, height)
  SELECT article_id, date, format, etag, source_url, data, width, height
  FROM _thumbnails_old;

INSERT INTO fav_icons (feed_id, date, format, etag, source_url, data)
  SELECT feed_id, date, format, etag, source_url, data FROM _fav_icons_old;

DROP TABLE _fav_icons_old;
DROP TABLE _enclosures_old;
DROP TABLE _taggings_old;
DROP TABLE _thumbnails_old;
DROP TABLE _articles_old;

CREATE TRIGGER on_delete_feed_trigger
	BEFORE 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 AND articles.marked=1;
		DELETE FROM fav_icons WHERE fav_icons.feed_id=OLD.feed_id;
	END;

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

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

DROP TABLE fts_table;

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

INSERT INTO fts_table(fts_table) VALUES('rebuild');
