-- ----------------------------------------------------
-- O. Creation de la table madeof
-- ----------------------------------------------------
DROP TABLE IF EXISTS madeof;
CREATE TABLE madeof(
parent integer NOT NULL,
child integer,
qte integer
);

INSERT INTO madeof (parent, child, qte) VALUES (1, 2, 1);
INSERT INTO madeof (parent, child, qte) VALUES (1, 3, 2);
INSERT INTO madeof (parent, child, qte) VALUES (2, 5, 1);
INSERT INTO madeof (parent, child, qte) VALUES (2, 6, 2);
INSERT INTO madeof (parent, child, qte) VALUES (3, 2, 10);
INSERT INTO madeof (parent, child, qte) VALUES (3, 5, 3);
INSERT INTO madeof (parent, child, qte) VALUES (4, 2, 1);
INSERT INTO madeof (parent, child, qte) VALUES (4, 7, 5);
INSERT INTO madeof (parent, child, qte) VALUES (5, 6, 4);
INSERT INTO madeof (parent, child, qte) VALUES (7, 8, 1);
INSERT INTO madeof (parent, child, qte) VALUES (8, 9, 1);
INSERT INTO madeof (parent, child, qte) VALUES (8, 10, 2);

-- ----------------------------------------------------
-- A. Calcul de la fermeture transitive
-- ----------------------------------------------------
DROP TABLE IF EXISTS closure;
CREATE TABLE closure(
parent integer NOT NULL,
descendant integer
);

INSERT INTO closure (parent, descendant)
select parent, child from madeof;

-- Instruction suivante à répéter (profondeur max des arbres -1) fois soit 4
INSERT INTO closure (parent, descendant)
((select c.parent, m.child from closure c, madeof m where c.descendant = m.parent) except (select * from closure));
-- Ou encore (pour le select)
-- (select distinct c.parent, m.child from closure c, madeof m where c.descendant = m.parent and not exists (select * from closure c1 where c1.parent = c.parent and c1.descendant = m.child));

-- OU
-- SGBD PL/SQL
-- INSERT INTO closure(parent,descendant) 
-- WITH RECURSIVE closure(parent,descendant) AS (
-- 	(select parent,child from madeof) 
-- 		UNION ALL 
-- 	((select c.parent, m.child from closure c, madeof m where c.descendant = m.parent))
-- )
-- select distinct * from closure;

-- OU ENCORE
-- INSERT INTO closure(parent,descendant) 
-- WITH RECURSIVE closure(parent,descendant) AS (
-- 	(select parent,child from madeof) 
-- 		UNION
-- 	((select c.parent, m.child from closure c, madeof m where c.descendant = m.parent))
-- )
-- select * from closure;
-- Utiliser UNION plutôt que UNION ALL impose à la requete recursive 
-- de ne s'executer que sur les lignes de l'iteration precedente.

-- ----------------------------------------------------
-- B. Verification de la présence d'un cycle
-- ----------------------------------------------------
select c1.parent, c1.descendant from closure c1 where exists in (
	select c2.parent, c2.descendant from closure c2 where c1.parent = c2.descendant and c1.descendant = c2.parent);

-- ----------------------------------------------------
-- C. Nomenclature
-- ----------------------------------------------------
DROP TABLE IF EXISTS niveaux;
CREATE TABLE niveaux(
parent integer NOT NULL,
descendant integer,
qte integer,
niveau integer
);

INSERT INTO niveaux(parent, descendant, qte, niveau)
select *,1 from madeof;
-- instruction suivante à répéter (profondeur max des arbres -1) fois soit 4
INSERT INTO niveaux(parent, descendant, qte, niveau)
select n.parent, m.child, m.qte*n.qte as qte, n.niveau+1 as niveau from niveaux n, madeof m
where n.descendant = m.parent and n.niveau =
	(select max(niveau) from niveaux);

-- OU
-- SGBD PL/SQL
-- INSERT INTO niveaux(parent, descendant, qte, niveau)
-- WITH RECURSIVE niveaux(parent, descendant, qte, niveau) AS (
-- 	(select *,1 as niveau from madeof)
-- 		UNION
-- 	(select n.parent, m.child, m.qte*n.qte as qte, n.niveau+1 as niveau
-- 		from niveaux n, madeof m
-- 		where n.descendant = m.parent)
-- )
-- select * from niveaux;

DROP TABLE IF EXISTS nomenclature;
CREATE TABLE nomenclature(
parent integer NOT NULL,
descendant integer,
niveau integer,
qte integer
);

INSERT INTO nomenclature(parent, descendant, niveau, qte)
select parent,descendant,niveau,sum(qte) from niveaux group by parent,descendant,niveau;