DROP TRIGGER IF EXISTS on_delete_feed_trigger;
DROP TRIGGER IF EXISTS on_delete_category_trigger;

ALTER TABLE feeds RENAME TO _feeds_old;
ALTER TABLE feed_mapping RENAME TO _feed_mapping_old;
ALTER TABLE fav_icons RENAME TO _fav_icons_old;

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

INSERT INTO feeds (feed_id, label, website, feed_url, icon_url)
  SELECT feed_id, label, website, feed_url, icon_url
  FROM _feeds_old;

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

INSERT INTO feed_mapping (feed_id, category_id, sort_index)
  SELECT feed_id, category_id, NULL FROM _feed_mapping_old;

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)
);

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

DROP TABLE _fav_icons_old;
DROP TABLE _feed_mapping_old;
DROP TABLE _feeds_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_category_trigger
	BEFORE DELETE ON categories
	BEGIN
		DELETE FROM feed_mapping WHERE feed_mapping.category_id=OLD.category_id;
		DELETE FROM category_mapping WHERE category_mapping.category_id=OLD.category_id;
	END;