CREATE OR REPLACE FUNCTION audit_cleanup(deploymentId VARCHAR, days int, dryRun BOOL) RETURNS TABLE(processInstanceId INT8) AS
$$
BEGIN
	CREATE TEMPORARY TABLE logsToBeDeleted ON COMMIT DROP AS 
	WITH RECURSIVE cte_hierarchy (pid, processInstanceId, parentProcessInstanceId, externalid, end_date, status) AS (
	   SELECT pil.processInstanceId, pil.processInstanceId, pil.parentProcessInstanceId, pil.externalid, pil.end_date, pil.status
	   FROM ProcessInstanceLog pil
	   WHERE pil.status IN (2,3) AND pil.externalid = deploymentId AND pil.end_date <= NOW() - (days || ' DAYS')::interval
	   UNION 
	   SELECT cte.pid, pil.processInstanceId, pil.parentProcessInstanceId, pil.externalid, pil.end_date,  pil.status
	   FROM ProcessInstanceLog pil
	   INNER JOIN cte_hierarchy cte ON cte.parentProcessInstanceId = pil.processInstanceId
	)
	SELECT DISTINCT pil.processInstanceId, pil.parentProcessInstanceId, pil.externalId, pil.end_date, pil.status
	FROM cte_hierarchy pil
	WHERE NOT EXISTS (SELECT pid FROM cte_hierarchy cte WHERE pil.processInstanceId = cte.pid AND status IN (0,1,4)) and status IN (2,3);
	CREATE INDEX ON logsToBeDeleted (processinstanceid);
	IF NOT dryRun THEN
		DELETE FROM ProcessInstanceLog pil WHERE EXISTS (SELECT logsToBeDeleted.processInstanceId FROM logsToBeDeleted WHERE logsToBeDeleted.processInstanceId = pil.processInstanceId);
		DELETE FROM NodeInstanceLog nil WHERE EXISTS (SELECT logsToBeDeleted.processInstanceId FROM logsToBeDeleted WHERE logsToBeDeleted.processInstanceId = nil.processInstanceId);
		DELETE FROM VariableInstanceLog vil WHERE EXISTS (SELECT logsToBeDeleted.processInstanceId FROM logsToBeDeleted WHERE logsToBeDeleted.processInstanceId = vil.processInstanceId);
		DELETE FROM TaskEvent te WHERE EXISTS (SELECT logsToBeDeleted.processInstanceId FROM logsToBeDeleted WHERE logsToBeDeleted.processInstanceId = te.processInstanceId);
		DELETE FROM AuditTaskImpl ati WHERE EXISTS (SELECT logsToBeDeleted.processInstanceId FROM logsToBeDeleted WHERE logsToBeDeleted.processInstanceId = ati.processInstanceId);
		DELETE FROM ErrorInfo ei WHERE EXISTS (SELECT del.processInstanceId FROM RequestInfo ri, logsToBeDeleted del WHERE ri.id = ei.request_id AND del.processInstanceId = ri.processInstanceId);
		DELETE FROM RequestInfo ri WHERE EXISTS (SELECT logsToBeDeleted.processInstanceId FROM logsToBeDeleted WHERE logsToBeDeleted.processInstanceId = ri.processInstanceId);
	END IF;
	RETURN QUERY SELECT logsToBeDeleted.processInstanceId FROM logsToBeDeleted;
END;
$$ LANGUAGE plpgsql