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

CREATE TEMP TABLE w_dbsetup AS
SELECT
    code, texte, base_request, clear_request, post_class
FROM pmsi.t_etude
ORDER BY 1
LIMIT 0
;

-- 2/3 : Peuplement de la table temporaire
INSERT INTO w_dbsetup(
    code,
    texte,
    base_request,
    clear_request,
    post_class
)
VALUES
    ('CTI_CANCERO_2022', 'Seuils cancérologie 2022', 'WITH chir_digestive_by_year AS (
	SELECT
	EXTRACT(YEAR FROM date_sortie) as year,
	count(*) as total_digestive
	FROM pmsi.p_rss_cancero
	JOIN pmsi.p_rss ON p_rss.oid = p_rss_cancero.rss_id
	WHERE cancero_code = ''CHIR2'' AND cdc = ''2022''
	GROUP BY 1
),
chir_thoracique_by_year AS (
	SELECT
	EXTRACT(YEAR FROM date_sortie) as year,
	count(*) as total_thoracique
	FROM pmsi.p_rss_cancero
	JOIN pmsi.p_rss ON p_rss.oid = p_rss_cancero.rss_id
	WHERE cancero_code = ''CHIR4'' AND cdc = ''2022''
	GROUP BY 1
),
rss_actes AS (
	SELECT
		oid as rss_id,
		array_agg(acte_id) as actes
	FROM pmsi.p_rss
	LEFT JOIN pmsi.p_rss_actes ON p_rss_actes.rss_id = p_rss.oid
	GROUP BY 1
),
rss_diags AS (
	SELECT
		oid as rss_id,
		array_agg(diagnostic_id) as diags
	FROM pmsi.p_rss
	LEFT JOIN pmsi.p_rss_diagnostics ON p_rss_diagnostics.rss_id = p_rss.oid
	GROUP BY 1
)
INSERT INTO pmsi.p_rss_cancero
  	(
  		cdc,
  		cancero_id,
  		cancero_code,
  		cancero_texte,
  		cancero_texte_court,
  		activite_cancero_id,
  		activite_cancero_code,
  		activite_cancero_texte,
  		rss_id
  	)
SELECT
		''2022'',
        v_categorie_cancero.oid AS cancero_id,
        v_categorie_cancero.code AS cancero_code,
        v_categorie_cancero.texte AS cancero_texte,
        v_categorie_cancero.texte_court AS cancero_texte_court,
        v_categorie_cancero.activite_id AS activite_cancero_id,
        v_categorie_cancero.activite_code AS activite_cancero_code,
        v_categorie_cancero.activite_texte AS activite_cancero_texte,
        p_rss.oid AS rss_id
FROM
        pmsi.p_rss
		JOIN rss_actes ON rss_actes.rss_id = p_rss.oid
		JOIN rss_diags ON rss_diags.rss_id = p_rss.oid
        JOIN pmsi.v_categorie_cancero ON 1 = 1
		LEFT JOIN chir_digestive_by_year ON chir_digestive_by_year.year = EXTRACT(YEAR FROM p_rss.date_sortie)
		LEFT JOIN chir_thoracique_by_year ON chir_digestive_by_year.year = EXTRACT(YEAR FROM p_rss.date_sortie)
{{ WHERE }}
GROUP BY 1,2,3,4,5,6,7,8,9', 'DELETE FROM pmsi.p_rss_cancero WHERE cdc = ''2022''', ''),
    ('CTI_CARDIO_INTERVENTIONNELLE_2022', 'Cardiologie Interventionnelle 2022', 'INSERT INTO pmsi.p_rss_cardio(
finess,
rss_id,
acte_id,
date_acte,
nombre,
liste_id,
cardio_modalite_id,
cardio_mention_id ,
cardio_famille_acte_id,
cdc
)
SELECT finess,
rss_id,
acte_id,
date_acte,
nombre,
t_listes.oid AS liste_id,
cardio_modalite_id,
cardio_mention_id ,
t_etude_cardio_familles_actes.oid AS cardio_famille_acte_id,
''2022'' AS cdc
FROM pmsi.p_rss_actes
       JOIN  pmsi.t_listes_contenu on t_listes_contenu.to_id = p_rss_actes.acte_id
       JOIN  pmsi.t_listes ON t_listes.oid = t_listes_contenu.liste_id
       JOIN  pmsi.t_etude_cardio_familles_actes ON t_listes.oid = t_etude_cardio_familles_actes.liste_id
  AND activite_ccam <> 4 -- 4 = Actes d''anesthésie
WHERE est_seuil IS TRUE AND date_acte >= ''20220101''', 'DELETE FROM pmsi.p_rss_cardio WHERE cdc = ''2022''', 'EtudeCardioPost')
;

-- 3/3 : Màj de la table iCTI
TRUNCATE pmsi.t_etude;

INSERT INTO pmsi.t_etude(code, texte, base_request, clear_request, post_class)
SELECT
    w_dbsetup.code,
    w_dbsetup.texte,
    w_dbsetup.base_request,
    w_dbsetup.clear_request,
    w_dbsetup.post_class
FROM w_dbsetup
;