CREATE TABLE public."type" (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	"schema" jsonb DEFAULT '{}'::jsonb NOT NULL,
	updated timestamptz DEFAULT now() NOT NULL,
	CONSTRAINT type_pk PRIMARY KEY (id)
);

CREATE TABLE public.node (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	type_id int4 NOT NULL,
	updated timestamptz DEFAULT now() NOT NULL,
	"data" jsonb DEFAULT '{}'::jsonb NOT NULL,
	CONSTRAINT node_pk PRIMARY KEY (id),
	CONSTRAINT node_type_fk FOREIGN KEY (type_id) REFERENCES public."type"(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

CREATE TABLE public."connection" (
	parent int4 NOT NULL,
	child int4 NOT NULL,
	created timestamptz DEFAULT now() NOT NULL,
	CONSTRAINT connection_from_fk FOREIGN KEY (parent) REFERENCES public.node(id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT connection_to_fk FOREIGN KEY (child) REFERENCES public.node(id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO public.type(id, name, schema) VALUES(0, 'root_node', '{"$id": "https://data.ahall.se/schema/root_node.json", "type": "object", "title": "Root node", "$schema": "https://json-schema.org/draft/2020-12/schema", "required": [], "properties": {}, "description": "Graph start node"}');
INSERT INTO public.node(id, name, type_id) VALUES(0, 'root_node', 0);