-- 1/3 : Création de la table temporaire
DROP TABLE IF EXISTS w_dbsetup
;

CREATE TEMP TABLE w_dbsetup AS
    SELECT
    	t_classes_sections.code AS section_code, 
    	t_ages.code as to_code, 
    	t_listes_tables.code as table_code, 
    	t_classes.sequence as classe_sequence 
    FROM 
    	rh.t_classes_sections_elements, 
    	rh.t_classes_sections, 
    	rh.t_classes, 
    	rh.t_listes_tables, 
    	rh.t_ages
    WHERE 
    	t_classes_sections_elements.section_id = t_classes_sections.oid
    	AND t_classes_sections.classe_id = t_classes.oid 
    	AND t_classes.is_cti = 1
    	AND table_id = t_listes_tables.oid
    	AND t_listes_tables.code = 'AGE'
    	AND t_classes_sections_elements.to_id = t_ages.oid
    ORDER BY 
    	t_classes.sequence, 
    	t_classes_sections.code,
    	t_ages.code
     LIMIT 0
;

-- 2/3 : Peuplement de la table temporaire
INSERT INTO w_dbsetup(
    section_code,
    to_code,
    table_code,
    classe_sequence
)
VALUES
('S1', '001', 'AGE', 9),
('S1', '002', 'AGE', 9),
('S1', '003', 'AGE', 9),
('S1', '004', 'AGE', 9),
('S1', '005', 'AGE', 9),
('S1', '006', 'AGE', 9),
('S1', '007', 'AGE', 9),
('S1', '008', 'AGE', 9),
('S1', '009', 'AGE', 9),
('S1', '010', 'AGE', 9),
('S1', '011', 'AGE', 9),
('S1', '012', 'AGE', 9),
('S1', '013', 'AGE', 9),
('S1', '014', 'AGE', 9),
('S1', '015', 'AGE', 9),
('S1', '016', 'AGE', 9),
('S1', '017', 'AGE', 9),
('S1', '018', 'AGE', 9),
('S1', '019', 'AGE', 9),
('S1', '020', 'AGE', 9),
('S1', '021', 'AGE', 9),
('S1', '022', 'AGE', 9),
('S1', '023', 'AGE', 9),
('S1', '024', 'AGE', 9),
('S2', '025', 'AGE', 9),
('S2', '026', 'AGE', 9),
('S2', '027', 'AGE', 9),
('S2', '028', 'AGE', 9),
('S2', '029', 'AGE', 9),
('S2', '030', 'AGE', 9),
('S2', '031', 'AGE', 9),
('S2', '032', 'AGE', 9),
('S2', '033', 'AGE', 9),
('S2', '034', 'AGE', 9),
('S2', '035', 'AGE', 9),
('S3', '036', 'AGE', 9),
('S3', '037', 'AGE', 9),
('S3', '038', 'AGE', 9),
('S3', '039', 'AGE', 9),
('S3', '040', 'AGE', 9),
('S3', '041', 'AGE', 9),
('S3', '042', 'AGE', 9),
('S3', '043', 'AGE', 9),
('S3', '044', 'AGE', 9),
('S3', '045', 'AGE', 9),
('S4', '046', 'AGE', 9),
('S4', '047', 'AGE', 9),
('S4', '048', 'AGE', 9),
('S4', '049', 'AGE', 9),
('S4', '050', 'AGE', 9),
('S4', '051', 'AGE', 9),
('S4', '052', 'AGE', 9),
('S4', '053', 'AGE', 9),
('S4', '054', 'AGE', 9),
('S4', '055', 'AGE', 9),
('S5', '056', 'AGE', 9),
('S5', '057', 'AGE', 9),
('S5', '058', 'AGE', 9),
('S5', '059', 'AGE', 9),
('S5', '060', 'AGE', 9),
('S5', '061', 'AGE', 9),
('S5', '062', 'AGE', 9),
('S5', '063', 'AGE', 9),
('S5', '064', 'AGE', 9),
('S5', '065', 'AGE', 9),
('S6', '066', 'AGE', 9),
('S6', '067', 'AGE', 9),
('S6', '068', 'AGE', 9),
('S6', '069', 'AGE', 9),
('S6', '070', 'AGE', 9),
('S6', '071', 'AGE', 9),
('S6', '072', 'AGE', 9),
('S6', '073', 'AGE', 9),
('S6', '074', 'AGE', 9),
('S6', '075', 'AGE', 9),
('S6', '076', 'AGE', 9),
('S6', '077', 'AGE', 9),
('S6', '078', 'AGE', 9),
('S6', '079', 'AGE', 9),
('S6', '080', 'AGE', 9),
('S6', '081', 'AGE', 9),
('S6', '082', 'AGE', 9),
('S6', '083', 'AGE', 9),
('S6', '084', 'AGE', 9),
('S6', '085', 'AGE', 9),
('S6', '086', 'AGE', 9),
('S6', '087', 'AGE', 9),
('S6', '088', 'AGE', 9),
('S6', '089', 'AGE', 9),
('S6', '090', 'AGE', 9),
('S6', '091', 'AGE', 9),
('S6', '092', 'AGE', 9),
('S6', '093', 'AGE', 9),
('S6', '094', 'AGE', 9),
('S6', '095', 'AGE', 9),
('S6', '096', 'AGE', 9),
('S6', '097', 'AGE', 9),
('S6', '098', 'AGE', 9),
('S6', '099', 'AGE', 9),
('S6', '100', 'AGE', 9),
('S6', '101', 'AGE', 9),
('S6', '102', 'AGE', 9),
('S6', '103', 'AGE', 9),
('S6', '104', 'AGE', 9),
('S6', '105', 'AGE', 9),
('S6', '106', 'AGE', 9),
('S6', '107', 'AGE', 9),
('S6', '108', 'AGE', 9),
('S6', '109', 'AGE', 9),
('S6', '110', 'AGE', 9)
;

-- 3/3 : Màj de la table iCTI
INSERT INTO rh.t_classes_sections_elements(section_id, to_id)     
	SELECT
		t_classes_sections.oid,
		t_ages.oid 
	FROM w_dbsetup
		JOIN rh.t_listes_tables ON t_listes_tables.code = w_dbsetup.table_code
		JOIN rh.t_classes ON 1=1
			AND t_classes.table_id = t_listes_tables.oid
			AND t_classes.sequence = w_dbsetup.classe_sequence
		JOIN rh.t_classes_sections ON 1=1
			AND t_classes_sections.classe_id = t_classes.oid
			AND t_classes_sections.code = w_dbsetup.section_code
		JOIN rh.t_ages ON t_ages.code = w_dbsetup.to_code
	WHERE t_classes_sections.oid::text || '-' || t_ages.oid::text NOT IN 
			(
			SELECT section_id::text || '-' || to_id::text 
			FROM rh.t_classes_sections_elements
			)
;