-- name: BatchRelation :exec
WITH RECURSIVE closure AS (
 SELECT 
 id,
 path_depth,
 created_at,
 ancester_user_id,
 descendant_user_id
 FROM relation
UNION ALL
 
 SELECT
 hex(randomblob(16)) AS id,
 closure.path_depth + r.path_depth AS path_depth,
 CURRENT_TIMESTAMP AS created_at,
 r.ancester_user_id,
 closure.descendant_user_id
 FROM closure
 JOIN relation r ON closure.ancester_user_id = r.descendant_user_id
 WHERE r.path_depth = 1 
)
INSERT INTO relation_batch (
 id,
 path_depth,
 created_at,
 ancester_user_id,
 descendant_user_id,
 contract_id
)
SELECT
 closure.id,
 closure.path_depth,
 closure.created_at,
 closure.ancester_user_id,
 closure.descendant_user_id,
 (SELECT contract_id FROM relation WHERE ancester_user_id = closure.ancester_user_id AND descendant_user_id = closure.descendant_user_id LIMIT 1) AS contract_id
FROM closure
WHERE NOT EXISTS (
 SELECT 1 FROM relation_batch
 WHERE relation_batch.ancester_user_id = closure.ancester_user_id
 AND relation_batch.descendant_user_id = closure.descendant_user_id
 AND relation_batch.path_depth = closure.path_depth
);