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

CREATE TEMP TABLE w_dbsetup AS
    SELECT 
    	severite_id as keepoid,
    	severite_code,
    	severite_texte
    FROM activite.t_severites_cti
    ORDER BY severite_code LIMIT 0
;

-- 2/3 : Peuplement de la table temporaire
INSERT INTO w_dbsetup(
    keepoid,
    severite_code,
    severite_texte
)
VALUES
('0', '**', 'Non renseignee'),
('1', '1', 'Sévérité 1'),
('8', '8', 'Non PMSI'),
('2', 'ABCDE', 'Sévérité ABCDE'),
('3', 'J', 'Sévérité J'),
('4', 'S', 'séances'),
('5', 'T', 'Sévérité T'),
('6', 'Z', 'Sévérité Z'),
('7', 's', 'DS > 2 nuits')
;

-- 3/3 : Màj de la table iCTI
UPDATE activite.t_severites_cti SET 
	severite_code = w_dbsetup.severite_code,
	severite_texte = w_dbsetup.severite_code
FROM w_dbsetup
WHERE w_dbsetup.keepoid = t_severites_cti.severite_id AND w_dbsetup.keepoid = 0;

UPDATE activite.t_severites_cti SET
	severite_texte = w_dbsetup.severite_texte
FROM w_dbsetup
WHERE 1=1
	AND activite.t_severites_cti.severite_code = w_dbsetup.severite_code
	AND activite.t_severites_cti.severite_texte IS DISTINCT FROM w_dbsetup.severite_texte
;

INSERT INTO activite.t_severites_cti(severite_id, severite_code, severite_texte)
	SELECT keepoid, severite_code, severite_texte
	FROM w_dbsetup 
	WHERE 1=1
		AND keepoid NOT IN (SELECT severite_id FROM activite.t_severites_cti)
;