-- file_node_links Keeps tracks of the files referenced by each node.
CREATE TABLE public.file_node_links (
	file_uuid uuid NOT NULL,
	node_uuid uuid NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT file_node_pk PRIMARY KEY (file_uuid,node_uuid,user_id),
	CONSTRAINT file_node_user_fk FOREIGN KEY (user_id) REFERENCES public."user"(id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT file_node_node_fk FOREIGN KEY (user_id,node_uuid) REFERENCES public.node(user_id,"uuid") ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT file_node_file_fk FOREIGN KEY (user_id,file_uuid) REFERENCES public.file(user_id,"uuid") ON DELETE CASCADE ON UPDATE CASCADE
);

-- add_nodes needs to call the new manage_node_file_links procedure.
CREATE OR REPLACE PROCEDURE public.add_nodes(IN p_user_id integer, IN p_client_uuid uuid, IN p_nodes jsonb)
 LANGUAGE plpgsql
AS $procedure$

DECLARE
	node_data         jsonb;
	node_updated      timestamptz;
	db_updated        timestamptz;
	db_uuid           uuid;
	db_client         uuid;
	db_history_uuid   uuid;
	node_uuid         uuid;
	node_parent_uuid  uuid;
	node_history_uuid uuid;

BEGIN
	FOR node_data IN SELECT * FROM jsonb_array_elements(p_nodes)
	LOOP
		node_uuid         = (node_data->>'UUID')::uuid;
		node_history_uuid = (node_data->>'HistoryUUID')::uuid;
		node_updated      = (node_data->>'Updated')::timestamptz;



		-- Frontend is using an all-zero UUID to define the root node.
		-- Database is using NULL.
		IF node_data->>'ParentUUID' = '00000000-0000-0000-0000-000000000000' OR node_data->>'ParentUUID' = '' THEN
			node_parent_uuid = NULL;
		ELSE
			node_parent_uuid = (node_data->>'ParentUUID')::uuid;
		END IF;

		-- Safeguard against being your own parent.
		IF node_uuid = node_parent_uuid THEN
			RAISE EXCEPTION 'Node UUID is same as node parent UUID.' USING ERRCODE = 'XPRNT';
		END IF;


		-- Every jode has a new history UUID to keep the history entry uniquely identifiable
		-- across clients. A history entry could potentially be sent again, but should be
		-- safe to ignore as every change to a node should have a new history UUID.
		--
		-- The current node is also stored as history.
		INSERT INTO node_history(
			user_id, "uuid", "history_uuid", parents, created, updated,
			"name", "content", "content_encrypted",
			client
		)
		VALUES(
			p_user_id, -- combined key
			node_uuid, -- combined key
			node_history_uuid, -- combined key
			(jsonb_populate_record(null::json_ancestor_array, node_data))."Ancestors",
			COALESCE((node_data->>'Created')::timestamptz, NOW()),
			COALESCE((node_data->>'Updated')::timestamptz, NOW()),
			(node_data->>'Name')::varchar,
			(node_data->>'Content')::text,
			'', /* content_encrypted */
			p_client_uuid
		)
		ON CONFLICT ("user_id", "uuid", "history_uuid")
		DO NOTHING;



		-- Retrieve the current modified timestamp for this node from the database.
		SELECT
			uuid, updated, client
		INTO
			db_uuid, db_updated, db_client
		FROM public."node"
		WHERE
			user_id = p_user_id AND
			uuid::uuid = node_uuid::uuid;



		-- Is the node not in database? It needs to be created.
		IF db_uuid IS NULL THEN
			RAISE NOTICE '01 New node %', node_uuid;

			INSERT INTO public."node" (
				user_id, "uuid", parent_uuid, created, updated,
				"name", "content", "content_encrypted",
				client
			)
			VALUES(
				p_user_id,
				node_uuid,
				node_parent_uuid,
				COALESCE((node_data->>'Created')::timestamptz, NOW()),
				COALESCE((node_data->>'Updated')::timestamptz, NOW()),
				(node_data->>'Name')::varchar,
				(node_data->>'Content')::text,
				'', /* content_encrypted */
				p_client_uuid
			);

			-- Remove/insert file links for the newest content of this node.
			CALL manage_node_file_links(p_user_id, node_uuid, (node_data->>'Content')::text);

			CONTINUE;
		END IF;



		-- Update the public node as well if it was older than incoming node.
		IF node_updated > db_updated THEN
			UPDATE public."node"
			SET
				updated = (node_data->>'Updated')::timestamptz,
				updated_seq = nextval('node_updates'),
				parent_uuid = node_parent_uuid,
				name = (node_data->>'Name')::varchar,
				content = (node_data->>'Content')::text,
				client = p_client_uuid
			WHERE
				user_id = p_user_id AND
				uuid::uuid = node_uuid::uuid;

			-- Remove/insert file links for the newest content of this node.
			CALL manage_node_file_links(p_user_id, node_uuid, (node_data->>'Content')::text);
		END IF;

	END LOOP;
END
$procedure$
;

CREATE OR REPLACE PROCEDURE public.manage_node_file_links(IN p_user_id integer, IN p_node_uuid uuid, IN p_node_content text)
LANGUAGE plpgsql
AS $fn$
BEGIN

	-- Old file links are purged to get rid of deleted ones.
	-- New links will be added from latest node update.
	DELETE FROM file_node_links
	WHERE
		user_id = p_user_id AND
		node_uuid = p_node_uuid;

	-- db:// URLs are extracted from the content to manage them in a separate table.
	WITH file_uuids AS (
		SELECT regexp_matches(
			p_node_content,
			'db://([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})',
			'g'
		) AS file_uuid
	) 
	INSERT INTO file_node_links(user_id, node_uuid, file_uuid)
	SELECT
		p_user_id,
		p_node_uuid,
		(file_uuid[1])::uuid
	FROM file_uuids;

END
$fn$
;