DROP TRIGGER IF EXISTS on_delete_category_trigger;

ALTER TABLE category_mapping RENAME TO _category_mapping_old;
ALTER TABLE categories RENAME TO _categories_old;


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

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 categories (category_id, label)
  SELECT category_id, label
  FROM _categories_old;

INSERT INTO category_mapping (parent_id, category_id, sort_index)
  SELECT parent_id, category_id, sort_index
  FROM _category_mapping_old;

DROP TABLE _category_mapping_old;
DROP TABLE _categories_old;

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;