BEGIN;

DROP VIEW IF EXISTS UDC_TEMPLATE_PRIVILEGED_COUNT;
DROP TABLE IF EXISTS db_template_version;
CREATE TABLE IF NOT EXISTS db_template_version (
	version NUMERIC UNIQUE NOT NULL
);

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

CREATE TRIGGER db_template_version_no_delete
BEFORE DELETE ON db_template_version
FOR EACH ROW EXECUTE PROCEDURE db_template_version_no_delete();

INSERT INTO db_template_version(version) VALUES (3);

DROP INDEX IF EXISTS idx_template_owner, idx_template_system, idx_template_ctime, idx_template_mtime, idx_template_ntype, idx_template_sort_order;
DROP TABLE IF EXISTS template;
CREATE TABLE IF NOT EXISTS template (
	object_id TEXT UNIQUE NOT NULL,
	commit_msg JSON,
	title TEXT NOT NULL DEFAULT '',
	brief TEXT,
	acl JSON,
	owner BIGINT NOT NULL,
	system BOOLEAN NOT NULL DEFAULT 'f',
	creator BIGINT,
	version TEXT NOT NULL,
	publish_version TEXT NOT NULL DEFAULT '',
	ctime BIGINT,
	mtime BIGINT,
	thumb JSON,
	link_id TEXT NOT NULL,
	ntype BIGINT NOT NULL,
	sort_order BIGINT DEFAULT 0,
	extra_info JSON
);
CREATE INDEX idx_template_owner ON template(owner);
CREATE INDEX idx_template_system ON template(system);
CREATE INDEX idx_template_ctime ON template(ctime);
CREATE INDEX idx_template_mtime ON template(mtime);
CREATE INDEX idx_template_ntype ON template(ntype);
CREATE INDEX idx_template_sort_order ON template(sort_order);

DROP INDEX IF EXISTS idx_template_link_object_id;
DROP TABLE IF EXISTS template_link;
CREATE TABLE IF NOT EXISTS template_link (
	id TEXT UNIQUE NOT NULL,
	object_id TEXT NOT NULL,
	category TEXT NOT NULL,
	ntype BIGINT NOT NULL,
	CONSTRAINT template_link_mapper_ukey UNIQUE (id, object_id)
);
CREATE INDEX idx_template_link_object_id ON template_link(object_id);

DROP INDEX IF EXISTS idx_template_perm_user_object_id;
DROP TABLE IF EXISTS template_perm_user;
CREATE TABLE IF NOT EXISTS template_perm_user (
	object_id TEXT NOT NULL,
	uid BIGINT NOT NULL,
	perm INTEGER NOT NULL,
	inherit BOOLEAN NOT NULL,
	CONSTRAINT template_perm_user_mapper_ukey UNIQUE (uid, object_id)
);
CREATE INDEX idx_template_perm_user_object_id ON template_perm_user(object_id);

DROP INDEX IF EXISTS idx_template_perm_group_object_id;
DROP TABLE IF EXISTS template_perm_group;
CREATE TABLE IF NOT EXISTS template_perm_group (
	object_id TEXT NOT NULL,
	gid BIGINT NOT NULL,
	perm INTEGER NOT NULL,
	inherit BOOLEAN NOT NULL,
	CONSTRAINT template_perm_group_mapper_ukey UNIQUE (gid, object_id)
);
CREATE INDEX idx_template_perm_group_object_id ON template_perm_group(object_id);

DROP INDEX IF EXISTS idx_template_perm_app_object_id;
DROP TABLE IF EXISTS template_perm_app;
CREATE TABLE IF NOT EXISTS template_perm_app (
	object_id TEXT UNIQUE NOT NULL,
	perm INTEGER NOT NULL,
	inherit BOOLEAN NOT NULL
);
CREATE INDEX idx_template_perm_app_object_id ON template_perm_app(object_id);

DROP INDEX IF EXISTS idx_template_recent_atime, idx_template_recent_requester;
DROP TABLE IF EXISTS template_recent;
CREATE TABLE IF NOT EXISTS template_recent (
	object_id TEXT NOT NULL,
	requester BIGINT NOT NULL,
	atime BIGINT,
	CONSTRAINT template_recent_mapper_ukey UNIQUE (object_id, requester)
);
CREATE INDEX idx_template_recent_atime ON template_recent(atime);
CREATE INDEX idx_template_recent_requester ON template_recent(requester);

COMMIT;