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 categories RENAME TO _categories_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 categories (
	category_id TEXT PRIMARY KEY NOT NULL,
	label TEXT NOT NULL,
	category_type INTEGER NOT NULL default 0
);

INSERT INTO categories (category_id, label)
  SELECT category_id, label
  FROM _categories_old;

CREATE TABLE feed_mapping (
	feed_id TEXT NOT NULL REFERENCES feeds(feed_id),
	category_id TEXT NOT NULL REFERENCES categories(category_id),
	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 category_mapping (
	parent_id TEXT NOT NULL,
	category_id TEXT NOT NULL REFERENCES categories(category_id),
    sort_index INTEGER,
	PRIMARY KEY (parent_id, category_id)
);

INSERT INTO category_mapping (parent_id, category_id)
  SELECT "NewsFlash.Toplevel", category_id FROM _categories_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 _categories_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;