BEGIN;

CREATE TABLE IF NOT EXISTS db_version (
	version NUMERIC NOT NULL
);

CREATE UNIQUE INDEX db_version_one_row ON db_version((version IS NOT NULL));

CREATE FUNCTION db_version_no_delete ()
RETURNS trigger
LANGUAGE plpgsql AS $f$
BEGIN
	RAISE EXCEPTION 'You may not delete the DB version!';
END; $f$;

CREATE TRIGGER db_version_no_delete
BEFORE DELETE ON db_version
FOR EACH ROW EXECUTE PROCEDURE db_version_no_delete();

INSERT INTO db_version (version) VALUES (11);

CREATE TABLE IF NOT EXISTS node (
	object_id TEXT UNIQUE NOT NULL,
	category TEXT DEFAULT 'node' NOT NULL,
	commit_msg JSON,
	brief TEXT,
	version TEXT NOT NULL,
	ctime BIGINT,
	mtime BIGINT,
	atime BIGINT,
	encrypt BOOLEAN DEFAULT FALSE,
	thumb TEXT,
	attachment JSON,
	link_id TEXT NOT NULL,
	ntype BIGINT NOT NULL,
	extra_info JSON
);

CREATE INDEX idx_node_ctime ON node(ctime);
CREATE INDEX idx_node_mtime ON node(mtime);
CREATE INDEX idx_node_atime ON node(atime);
CREATE INDEX idx_node_ntype ON node(ntype);

CREATE TABLE IF NOT EXISTS link (
	id TEXT UNIQUE NOT NULL,
	object_id TEXT NOT NULL,
	category TEXT NOT NULL,
	ntype BIGINT NOT NULL,
	CONSTRAINT link_mapper_ukey UNIQUE (id, object_id)
);

CREATE INDEX idx_link_object_id ON link(object_id);

CREATE TABLE IF NOT EXISTS notification (
	id  BIGSERIAL PRIMARY KEY,
	object_id TEXT NOT NULL,
	sender BIGINT NOT NULL,
	receiver BIGINT NOT NULL,
	time INTEGER NOT NULL DEFAULT 0,
	CONSTRAINT notification_mapper_ukey UNIQUE (object_id, sender, receiver)
);

CREATE OR REPLACE FUNCTION upsert_notification (_object_id TEXT, _sender BIGINT, _receiver BIGINT, _time BIGINT)
RETURNS INTEGER AS $$
DECLARE
	result INTEGER;
BEGIN
	BEGIN
		INSERT INTO notification (object_id, sender, receiver, time) VALUES (_object_id, _sender, _receiver, _time) RETURNING id INTO result;
		RETURN result;
	EXCEPTION WHEN unique_violation THEN
		UPDATE notification SET time=_time WHERE id IN (SELECT id FROM notification WHERE object_id=_object_id AND sender=_sender AND receiver=_receiver) AND ABS(_time - (SELECT COALESCE((SELECT time FROM notification WHERE object_id=_object_id AND sender=_sender AND receiver=_receiver), 0))) > 3600 RETURNING id INTO result;
		IF NOT FOUND THEN
			RETURN 0;
		END IF;
		RETURN result;
	END;
END;
$$ LANGUAGE plpgsql;

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

CREATE TRIGGER "onDelete" AFTER DELETE
   ON node
   FOR EACH ROW
   EXECUTE PROCEDURE afterNodeDeleted();

CREATE TABLE IF NOT EXISTS mru_fc (
	id BIGSERIAL PRIMARY KEY,
	owner BIGINT NOT NULL,
	fc TEXT NOT NULL,
	order_sn INTEGER NOT NULL DEFAULT 0,
	CONSTRAINT mru_fc_mapper_ukey UNIQUE (owner, fc)
);
CREATE INDEX idx_mru_fc_owner ON mru_fc(owner);
CREATE INDEX idx_mru_fc_fc ON mru_fc(fc);
CREATE INDEX idx_mru_fc_order_sn ON mru_fc(order_sn);
CREATE INDEX idx_mru_fc_owner_fc ON mru_fc(owner, fc);

CREATE OR REPLACE FUNCTION upsert_mru_fc (_owner BIGINT, _fc TEXT)
RETURNS INTEGER AS $$
DECLARE
	first_try BOOLEAN := TRUE;
	result INTEGER;
	max_order INTEGER;
BEGIN
	LOOP
		SELECT COALESCE(MAX(order_sn), 0) + 1 INTO max_order FROM mru_fc WHERE owner = _owner;
		UPDATE mru_fc SET order_sn = max_order WHERE owner = _owner AND fc = _fc RETURNING id INTO result;
		IF found THEN
			RETURN result;
		END IF;
		BEGIN
			INSERT INTO mru_fc(owner, fc, order_sn) VALUES(_owner, _fc, max_order) RETURNING id INTO result;
			RETURN result;
		EXCEPTION WHEN unique_violation THEN
			IF (first_try = TRUE) THEN
				first_try := FALSE;
			ELSE
				RETURN 0;
			END IF;
		END;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

COMMIT;