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

CREATE TEMP TABLE w_dbsetup AS
    SELECT 
    	CASE WHEN t_classes_sections.oid = 0 THEN t_classes_sections.oid ELSE 1 END AS oid,
    	t_listes_tables.code as table_code, 
    	t_classes.code AS classe_code,
    	t_classes.sequence as classe_sequence,
    	t_classes_sections.code,
    	t_classes_sections.texte,
        t_classes_sections.condition,
        t_classes_sections.exclude_code,
        t_classes_sections.include_code,
    	0::bigint AS classe_id,
    	0::bigint AS table_id
    FROM compta.t_classes_sections 
    JOIN compta.t_classes ON t_classes_sections.classe_id = t_classes.oid  
    JOIN compta.t_listes_tables ON table_id = t_listes_tables.oid 
    WHERE t_classes.code <> '' AND 
    	(t_classes.is_cti = '1'  OR t_classes.code LIKE 'CTI_%')  AND 
    	1=1
    ORDER BY 2,3,5 LIMIT 0
;

-- 2/3 : Peuplement de la table temporaire
INSERT INTO w_dbsetup(
    oid,
    table_code,
    classe_code,
    classe_sequence,
    code,
    texte,
    condition,
    exclude_code,
    include_code,
    classe_id,
    table_id
)
VALUES
(1, 'CPT', 'CTI_CONTRIBUTION_BLOC_OPERATOIRE', 42, 'S1', 'Consommable', '', '', '', 0, 0),
(1, 'CPT', 'CTI_CONTRIBUTION_BLOC_OPERATOIRE', 42, 'S2', 'Structure', '', '', '', 0, 0),
(1, 'CPT', 'CTI_CONTRIBUTION_BLOC_OPERATOIRE', 42, 'S3', 'Energie', '', '', '', 0, 0)
;

-- 3/3 : Màj de la table iCTI
UPDATE w_dbsetup
SET table_id = t_listes_tables.oid
FROM compta.t_listes_tables
WHERE table_code = t_listes_tables.code
;

UPDATE w_dbsetup
SET classe_id = t_classes.oid
FROM compta.t_classes
WHERE w_dbsetup.classe_code = t_classes.code AND
	w_dbsetup.table_id = t_classes.table_id
;

DELETE FROM compta.t_classes_sections
USING
(
SELECT t_classes_sections.classe_id, t_classes_sections.oid
FROM compta.t_classes_sections
LEFT JOIN w_dbsetup ON
	w_dbsetup.classe_id = t_classes_sections.classe_id AND
	w_dbsetup.code = t_classes_sections.code
WHERE w_dbsetup.oid IS NULL AND
	t_classes_sections.classe_id IN (SELECT classe_id FROM w_dbsetup) AND
	t_classes_sections.oid NOT IN (SELECT section_id FROM compta.t_classes_sections_elements)
) subview
WHERE subview.classe_id = t_classes_sections.classe_id AND
	subview.oid = t_classes_sections.oid
;

UPDATE compta.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
WHERE w_dbsetup.classe_id = t_classes_sections.classe_id AND
	w_dbsetup.code = t_classes_sections.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 compta.t_classes_sections(code, texte, classe_id, condition, exclude_code, include_code)
SELECT 
	w_dbsetup.code, 
	w_dbsetup.texte, 
	w_dbsetup.classe_id,
	w_dbsetup.condition,
	w_dbsetup.exclude_code,
	w_dbsetup.include_code
FROM w_dbsetup 
WHERE w_dbsetup.classe_id > 0 AND
	(w_dbsetup.code||'@'||w_dbsetup.classe_id::text)
	NOT IN (SELECT t_classes_sections.code||'@'||t_classes_sections.classe_id FROM compta.t_classes_sections)
;