BEGIN;

DROP TABLE IF EXISTS db_cleaner_version;
CREATE TABLE IF NOT EXISTS db_cleaner_version (
	version NUMERIC UNIQUE NOT NULL
);

DROP TRIGGER IF EXISTS db_cleaner_version_no_delete ON db_cleaner_version;
CREATE OR REPLACE FUNCTION db_cleaner_version_no_delete ()
RETURNS trigger
LANGUAGE plpgsql AS $f$
BEGIN
	RAISE EXCEPTION 'You may not delete the DB version!';
END; $f$;

CREATE TRIGGER db_cleaner_version_no_delete
BEFORE DELETE ON db_cleaner_version
FOR EACH ROW EXECUTE PROCEDURE db_cleaner_version_no_delete();

INSERT INTO db_cleaner_version(version) VALUES (1);

DROP TABLE IF EXISTS history_prune;
CREATE TABLE IF NOT EXISTS history_prune (
	object_id TEXT UNIQUE NOT NULL,
	prune_time BIGINT,
	prune_cnt BIGINT,
	commit_cnt BIGINT
);

DROP TABLE IF EXISTS node_delete;
CREATE TABLE IF NOT EXISTS node_delete (
	object_id TEXT UNIQUE NOT NULL,
	deleted boolean,
	ofile boolean,
	time BIGINT
);

CREATE OR REPLACE FUNCTION afterCleanerNodeDeleted()
RETURNS "trigger" AS $$
	BEGIN
		DELETE FROM history_prune WHERE object_id=OLD.object_id;
		DELETE FROM node_delete WHERE object_id=OLD.object_id;
		RETURN NULL;
	END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION afterCleanerHistoryDeleted()
RETURNS "trigger" AS $$
	BEGIN
		DELETE FROM history_prune WHERE object_id=OLD.object_id;
		RETURN NULL;
	END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS template_cleaner_delete_trigger ON template;
CREATE TRIGGER "template_cleaner_delete_trigger" AFTER DELETE
   ON template
   FOR EACH ROW
   EXECUTE PROCEDURE afterCleanerHistoryDeleted();

DROP TRIGGER IF EXISTS node_delete_delete_trigger ON node;
CREATE TRIGGER "node_cleaner_delete_trigger" AFTER DELETE
   ON node
   FOR EACH ROW
   EXECUTE PROCEDURE afterCleanerNodeDeleted();

COMMIT;