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

CREATE TEMP TABLE w_dbsetup AS
    SELECT 
    	CASE WHEN t_rapports.oid = 0 THEN t_rapports.oid ELSE 1 END AS oid,
    	t_rapports.code, 
    	t_rapports.texte, 
    	t_rapports.is_cti, 
    	t_rapports.is_essentiel, 
    	t_rapports.essentiel_date_level, 
    	t_rapports.colonnes_essentiel, 
    	t_rapports.entete, 
    	t_rapports.entete_2, 
    	CASE WHEN t_rapports.group_id = 0 THEN t_rapports.group_id ELSE 1 END AS group_id,
    	t_rapports.is_hide, 
    	array_to_string(
    		ARRAY[
    		COALESCE(t_indicateur_condition_1.external_code::text,''),
    		COALESCE(t_indicateur_condition_2.external_code::text,''),
    		COALESCE(t_indicateur_condition_3.external_code::text,''),
    		COALESCE(t_indicateur_condition_4.external_code::text,'')
    		]
    		,'||') AS rapport_indicateur_condition_code, 
    	t_rapports.label, 
    	array_to_string(serie_entete,'||') AS serie_entete, 
    	t_rapports.colonnes_essentiel_style
    FROM activite.t_rapports 
    LEFT JOIN activite.t_indicateur_condition t_indicateur_condition_1 ON t_rapports.rapport_indicateur_condition_id[1] = t_indicateur_condition_1.oid
    LEFT JOIN activite.t_indicateur_condition t_indicateur_condition_2 ON t_rapports.rapport_indicateur_condition_id[2] = t_indicateur_condition_2.oid
    LEFT JOIN activite.t_indicateur_condition t_indicateur_condition_3 ON t_rapports.rapport_indicateur_condition_id[3] = t_indicateur_condition_3.oid
    LEFT JOIN activite.t_indicateur_condition t_indicateur_condition_4 ON t_rapports.rapport_indicateur_condition_id[4] = t_indicateur_condition_4.oid
    WHERE (t_rapports.is_cti = '1' OR t_rapports.code LIKE 'CTI_%') AND
        t_rapports.code <> 'CTI_PORTFO'
    ORDER BY t_rapports.code
     LIMIT 0
;

-- 2/3 : Peuplement de la table temporaire
INSERT INTO w_dbsetup(
    oid,
    code,
    texte,
    is_cti,
    is_essentiel,
    essentiel_date_level,
    colonnes_essentiel,
    entete,
    entete_2,
    group_id,
    is_hide,
    rapport_indicateur_condition_code,
    label,
    serie_entete,
    colonnes_essentiel_style
)
VALUES
(1, 'ACTI_ESS_1', 'CTI Essentiels Occupation (ne plus utiliser)', '1', '1', 'M', '', '', '', 1, '1', '||||||', '', '||', ''),
(1, 'ACTI_ESS_2', 'CTI Essentiels Activité DAF (ne plus utiliser)', '1', '1', 'M', '', '', '', 1, '1', '||||||', '', '||', ''),
(1, 'ACTI_ESS_3', 'CTI Essentiels Activité DIM (ne plus utiliser)', '1', '1', 'M', '', '', '', 1, '1', '||||||', '', '||', ''),
(1, 'ACTI_ESS_4', 'CTI Essentiels Activité Direction (ne plus utiliser)', '1', '1', 'M', '', '', '', 1, '1', '||||||', '', '||', ''),
(1, 'ACTI_ESS_5', 'CTI Essentiels Occupation SSR (ne plus utiliser)', '1', '1', 'M', '', '', '', 1, '1', '||||||', '', '||', ''),
(1, 'CTI_ESS_001', 'Occupation', '1', '1', 'M', '', '', '', 1, '0', '||||||', '', '||||||||||||||||', ''),
(1, 'CTI_ESS_002', 'Activité Direction (Date activité)', '1', '1', 'M', '', '', '', 1, '0', '||||||', '', '||', ''),
(1, 'CTI_ESS_003', 'Activité Direction (Date sortie)', '1', '1', 'M', '', '', '', 1, '0', '||||||', '', '||', ''),
(1, 'CTI_ESS_004', 'Activité Comptable (Date ventes)', '1', '1', 'M', '', '', '', 1, '0', '||||||', '', '||', ''),
(1, 'CTI_ESS_006', 'Séjours', '1', '1', 'M', 'ENTETE_ETENDUE|VAP100|DAM100|', '', '', 1, '0', '||||||', '', '||||||||||||||||', ''),
(1, 'CTI_EX1', 'CTI Exemple 1. Activité', '1', '0', 'M', '', '', '', 0, '0', '||||||', '', '||', ''),
(1, 'CTI_EX2', 'CTI Exemple 2. Occupation', '1', '0', 'M', '', '', '', 0, '0', '||||||', '', '||', ''),
(1, 'CTI_EX3', 'CTI Exemple 3. Activité', '1', '0', 'M', '', '', '', 0, '0', '||||||', '', '||', ''),
(1, 'CTI_EX4', 'CTI Exemple 4. Activité', '1', '0', 'M', '', '', '', 0, '0', '||||||', '', '||', ''),
(1, 'CTI_PF_AGG', 'CTI Portfolio', '1', '1', 'M', '', '', '', 1, '1', '||||||', '', '||', '')
;

-- 3/3 : Màj de la table iCTI
ALTER TABLE w_dbsetup ADD COLUMN to_serie_entete text[];
UPDATE w_dbsetup SET to_serie_entete = string_to_array(serie_entete,'||');


ALTER TABLE w_dbsetup ADD COLUMN to_rapport_indicateur_condition_code text[];
UPDATE w_dbsetup SET to_rapport_indicateur_condition_code = string_to_array(rapport_indicateur_condition_code,'||');

ALTER TABLE w_dbsetup ADD COLUMN to_rapport_indicateur_condition_id bigint[];
UPDATE w_dbsetup SET to_rapport_indicateur_condition_id[1] = t_indicateur_condition.oid FROM activite.t_indicateur_condition WHERE t_indicateur_condition.code = to_rapport_indicateur_condition_code[1] AND to_rapport_indicateur_condition_code[1] <> '';
UPDATE w_dbsetup SET to_rapport_indicateur_condition_id[2] = t_indicateur_condition.oid FROM activite.t_indicateur_condition WHERE t_indicateur_condition.code = to_rapport_indicateur_condition_code[2] AND to_rapport_indicateur_condition_code[2] <> '';
UPDATE w_dbsetup SET to_rapport_indicateur_condition_id[3] = t_indicateur_condition.oid FROM activite.t_indicateur_condition WHERE t_indicateur_condition.code = to_rapport_indicateur_condition_code[3] AND to_rapport_indicateur_condition_code[3] <> '';
UPDATE w_dbsetup SET to_rapport_indicateur_condition_id[4] = t_indicateur_condition.oid FROM activite.t_indicateur_condition WHERE t_indicateur_condition.code = to_rapport_indicateur_condition_code[4] AND to_rapport_indicateur_condition_code[4] <> '';
UPDATE w_dbsetup SET to_rapport_indicateur_condition_id = array_remove(to_rapport_indicateur_condition_id,NULL);

UPDATE activite.t_rapports SET 
	texte = w_dbsetup.texte, 
	is_cti = w_dbsetup.is_cti,
	is_essentiel = w_dbsetup.is_essentiel,
	essentiel_date_level = w_dbsetup.essentiel_date_level,
	colonnes_essentiel = w_dbsetup.colonnes_essentiel,
	entete = w_dbsetup.entete,
	entete_2 = w_dbsetup.entete_2,
	group_id  = w_dbsetup.group_id,
	rapport_indicateur_condition_id = to_rapport_indicateur_condition_id,
	label  = w_dbsetup.label,
	serie_entete  = to_serie_entete,
	colonnes_essentiel_style = w_dbsetup.colonnes_essentiel_style
FROM w_dbsetup
WHERE t_rapports.code = w_dbsetup.code;


INSERT INTO activite.t_rapports(oid, code, texte, is_cti, is_essentiel)
SELECT oid, code, texte, is_cti, is_essentiel 
FROM w_dbsetup 
WHERE 
	oid = 0 AND
	oid NOT IN (SELECT oid FROM activite.t_rapports);


INSERT INTO activite.t_rapports(
	code, 
	texte, 
	is_cti, 
	is_essentiel, 
	essentiel_date_level, 
	colonnes_essentiel, 
	entete, 
	entete_2, 
	group_id, 
	is_hide, 
	rapport_indicateur_condition_id, 
	label, 
	serie_entete, 
	colonnes_essentiel_style
	)
SELECT 
	code, 
	texte, 
	is_cti, 
	is_essentiel, 
	essentiel_date_level, 
	colonnes_essentiel, 
	entete, 
	entete_2, 
	group_id, 
	is_hide, 
	to_rapport_indicateur_condition_id, 
	label, 
	to_serie_entete, 
	colonnes_essentiel_style
FROM w_dbsetup 
WHERE 
	oid <> 0 AND
	code NOT IN (SELECT code FROM activite.t_rapports);