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;

CREATE TABLE articles (
	article_id TEXT PRIMARY KEY NOT NULL,
	title TEXT,
	author TEXT,
	feed_id TEXT NOT NULL,
	url TEXT,
	timestamp DATETIME NOT NULL,
    synced DATETIME 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 PRIMARY KEY NOT NULL REFERENCES articles(article_id),
	url TEXT NOT NULL,
	mime_type TEXT,
    title Text
);

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

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

INSERT INTO enclosures (article_id, url, mime_type, title)
  SELECT article_id, url, mime_type, title
  FROM _enclosures_old;

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

INSERT INTO thumbnails (article_id, timestamp, format, etag, source_url, data, width, height)
  SELECT article_id, timestamp, format, etag, source_url, data, width, height
  FROM _thumbnails_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;