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

CREATE TEMP TABLE w_dbsetup AS
    SELECT 
    	t_classes_sections.oid,
    	t_classes_sections.code,
    	t_classes_sections.texte,
    	t_classes_sections.condition,
    	t_classes_sections.include_code,
    	t_classes_sections.exclude_code,
    	CASE WHEN NOT (t_classes.sequence != 30 AND t_listes_tables.code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')) THEN t_listes_tables.code ELSE 'TYA' END as table_code,
    	t_classes.sequence as classe_sequence				
    FROM 
    	eco.t_classes_sections, 
    	eco.t_classes, 
    	eco.t_listes_tables 
    WHERE 1=1
    	AND t_classes_sections.classe_id = t_classes.oid 
    	AND table_id = t_listes_tables.oid
    	AND (t_classes.is_cti = '1'  OR (t_classes.code ilike 'CTI_%' AND t_classes.code NOT IN ('CTI_CPTLETBUD','CTI_CPTVOTE','CTI_CPTRCV','CTI_UFCR','CTI_UFUM','CTI_UFSERS','CTI_UFSITE')))
    ORDER BY 
    	t_listes_tables.code, 
    	t_classes.sequence, 
    	t_classes_sections.code LIMIT 0
;

-- 2/3 : Peuplement de la table temporaire
INSERT INTO w_dbsetup(
    oid,
    code,
    texte,
    condition,
    include_code,
    exclude_code,
    table_code,
    classe_sequence
)
VALUES
(1, 'ATE', 'Atelier', '', '0', '0', 'TYA', 30),
(1, 'CUI', 'Cuisine', '', '0', '0', 'TYA', 30),
(1, 'DM', 'Dispositifs Médicaux', '', '0', '0', 'TYA', 30),
(1, 'DMI', 'Dispositifs Médicaux Implantables', '', '0', '0', 'TYA', 30),
(1, 'ECO', 'Economat', '', '0', '0', 'TYA', 30),
(1, 'LIN', 'Lingerie', '', '0', '0', 'TYA', 30),
(1, 'MED', 'Médicaments', '', '0', '0', 'TYA', 30),
(1, 'PAR', 'Parapharmacie', '', '0', '0', 'TYA', 30),
(1, '00', 'Livraison', '', '', '', 'TYM', 1),
(1, '01', 'Consommations', '', '', '', 'TYM', 1),
(1, '02', 'Pertes', '', '', '', 'TYM', 1),
(1, '99', 'Autres', '', '', '', 'TYM', 1),
(1, '11', 'Régularisation livraison', '', '', '', 'TYM', 1),
(1, '12', 'Retour', '', '', '', 'TYM', 1),
(1, '13', 'Avoir', '', '', '', 'TYM', 1),
(1, '30', 'Régularisation inventaire', '', '', '', 'TYM', 1)
;

-- 3/3 : Màj de la table iCTI
-- Sections à supprimer -> Sections CTI non-livrées
DROP TABLE IF EXISTS w_del_sections
;
CREATE TEMP TABLE w_del_sections as (
	SELECT oid FROM eco.t_classes_sections 
	WHERE classe_id IN (
		SELECT
			t_classes.oid
		FROM eco.t_classes
		LEFT JOIN w_dbsetup ON w_dbsetup.code = t_classes_sections.code
		WHERE 1=1
			AND is_cti = '1'
			AND w_dbsetup.code IS NULL
	)
);

DELETE FROM eco.t_classes_sections_elements 
WHERE section_id IN (SELECT oid FROM w_del_sections);


DELETE FROM eco.t_classes_sections 
WHERE oid IN (SELECT oid FROM w_del_sections);

UPDATE eco.t_classes_sections SET
	texte = w_dbsetup.texte,
	condition = w_dbsetup.condition,
	exclude_code = w_dbsetup.exclude_code,
	include_code = w_dbsetup.include_code
FROM w_dbsetup, eco.t_classes, eco.t_listes_tables
WHERE t_classes.oid = t_classes_sections.classe_id
AND t_listes_tables.oid = t_classes.table_id
AND w_dbsetup.code||'@'||w_dbsetup.classe_sequence||'@'||w_dbsetup.table_code = t_classes_sections.code||'@'||t_classes.sequence||'@'||t_listes_tables.code
AND (
	t_classes_sections.texte IS DISTINCT FROM w_dbsetup.texte
	OR t_classes_sections.condition IS DISTINCT FROM w_dbsetup.condition
	OR t_classes_sections.exclude_code IS DISTINCT FROM w_dbsetup.exclude_code
	OR t_classes_sections.include_code IS DISTINCT FROM w_dbsetup.include_code
);

INSERT INTO eco.t_classes_sections(code, texte, classe_id, include_code, exclude_code)
    			SELECT 
    				w_dbsetup.code, 
    				w_dbsetup.texte, 
    				t_classes.oid,
		w_dbsetup.include_code,
		w_dbsetup.exclude_code
    			FROM 
    				w_dbsetup 
    				JOIN eco.t_listes_tables ON (w_dbsetup.table_code = t_listes_tables.code 
		-- ou s'il s'agit de la classe des cles
			OR (classe_sequence = 30 AND t_listes_tables.code = (SELECT t_listes_tables.code FROM eco.t_listes_tables JOIN eco.t_classes ON t_classes.code = 'CTI_CLE_REP' AND table_id = t_listes_tables.oid)))
		JOIN eco.t_classes ON 1=1
			AND t_classes.table_id = t_listes_tables.oid
			AND t_classes.sequence = w_dbsetup.classe_sequence
    			WHERE w_dbsetup.code||'@'||w_dbsetup.classe_sequence||'@'|| CASE WHEN w_dbsetup.classe_sequence != 30 THEN w_dbsetup.table_code ELSE (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION') END
		NOT IN (
		SELECT t_classes_sections.code||'@'||t_classes.sequence||'@'||CASE WHEN w_dbsetup.classe_sequence != 30 THEN t_listes_tables.code ELSE (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION') END
		FROM eco.t_classes_sections
			JOIN eco.t_classes ON t_classes.oid = t_classes_sections.classe_id
			JOIN eco.t_listes_tables ON t_listes_tables.oid = t_classes.table_id)
	;