-- Filtration table
create table x_sandbox_syntetika.ext_63984482_az_03_filtr as (
    select distinct polozka_vykon.pseudonym_nzis
    from x_nrhzs_data.v_polozka_00_zdravotni_vykon polozka_vykon
    where
        polozka_vykon.kod_polozky in (
            '06141','06142','18511','18513', '18515', '18521', '18522', '18523', '18529', '18530', '18550'
        )
        and polozka_vykon.den >= date '2020-01-01'
);
 
-- Farmakoterapie per patient per month
create table x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie_odb as (
    select
        hvlp.pseudonym_nzis,
        to_char(hvlp.den, 'YYYYMM') as rok_mesic,
        listagg(distinct hvlp.odbornost using parameters separator=',') as farmakoterapie_odbornost
    from x_nrhzs_data.v_polozka_01_hvlp hvlp
    where hvlp.pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_03_filtr)
        and (
            hvlp.cis_atc_kod like 'C02%'
            or hvlp.cis_atc_kod like 'C03%'
            or hvlp.cis_atc_kod like 'C07%'
            or hvlp.cis_atc_kod like 'C08%'
            or hvlp.cis_atc_kod like 'C09%'
            or hvlp.cis_atc_kod like 'C10%'
            or hvlp.cis_atc_kod like 'A02%'
            or hvlp.cis_atc_kod like 'A10%'
            or hvlp.cis_atc_kod like 'A11%'
            or hvlp.cis_atc_kod like 'A12%'
            or hvlp.cis_atc_kod like 'H05%'
            or hvlp.cis_atc_kod like 'V03%'
            or hvlp.cis_atc_kod like 'V06%'
        )
        and hvlp.den >= date '2020-01-01'
    group by hvlp.pseudonym_nzis, to_char(hvlp.den, 'YYYYMM')
);

create table x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie_atc as (
    select
        hvlp.pseudonym_nzis,
        to_char(hvlp.den, 'YYYYMM') as rok_mesic,
        listagg(distinct hvlp.cis_atc_kod using parameters separator=',') as farmakoterapie
    from x_nrhzs_data.v_polozka_01_hvlp hvlp
    where hvlp.pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_03_filtr)
        and (
            hvlp.cis_atc_kod like 'C02%'
            or hvlp.cis_atc_kod like 'C03%'
            or hvlp.cis_atc_kod like 'C07%'
            or hvlp.cis_atc_kod like 'C08%'
            or hvlp.cis_atc_kod like 'C09%'
            or hvlp.cis_atc_kod like 'C10%'
            or hvlp.cis_atc_kod like 'A02%'
            or hvlp.cis_atc_kod like 'A10%'
            or hvlp.cis_atc_kod like 'A11%'
            or hvlp.cis_atc_kod like 'A12%'
            or hvlp.cis_atc_kod like 'H05%'
            or hvlp.cis_atc_kod like 'V03%'
            or hvlp.cis_atc_kod like 'V06%'
        )
        and hvlp.den >= date '2020-01-01'
    group by hvlp.pseudonym_nzis, to_char(hvlp.den, 'YYYYMM')
);

create table x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie as (
    select
        far.pseudonym_nzis,
        far.rok_mesic,
        far.farmakoterapie,
        odb.farmakoterapie_odbornost
    from x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie_atc far
    left join x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie_odb odb
        on far.pseudonym_nzis = odb.pseudonym_nzis and far.rok_mesic = odb.rok_mesic
);
 
-- Vykony per patient per month
create table x_sandbox_syntetika.ext_63984482_az_03_vykony as (
    select
        polozka_vykon.pseudonym_nzis,
        to_char(polozka_vykon.den, 'YYYYMM') as rok_mesic,
        listagg(distinct polozka_vykon.kod_polozky using parameters separator=',') as vykony
    from x_nrhzs_data.v_polozka_00_zdravotni_vykon polozka_vykon
    where polozka_vykon.pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_03_filtr)
        and polozka_vykon.kod_polozky in (
            '06141', '06142', '18511', '18513', '18515', '18521', '18522', '18523', '18529', '18530', '18550',
            '09123', '81115', '81327', '81329', '81499', '81511', '81513', '81675', '81369', '81365', '81125',
            '01021', '01022', '11021', '13021', '14021', '17021', '18021', '76021', '76022', '76023', '11022',
            '11023', '13022', '13023', '14022', '14023', '17022', '17023', '18022', '18023', '01023',
            '51320', '76499', '51902', '76661', '76662', '76663', '76664', '76665', '76666'
        )
        and polozka_vykon.den >= date '2020-01-01'
    group by polozka_vykon.pseudonym_nzis, to_char(polozka_vykon.den, 'YYYYMM')
);
 
-- Main aggregation per patient per month with umrti join
select
    dense_rank() over (order by res.pseudonym_nzis) as pacient_id,
    res.rok_mesic,
    max(res.vykony) as vykony,
    max(res.farmakoterapie) as farmakoterapie,
    max(res.farmakoterapie_odbornost) as farmakoterapie_odbornost,
    to_char(umrti.datumumrti, 'YYYYMM') AS rokmesicumrti,
    umrti.prvotnipricinasmrtimkn10,
    case when to_char(umrti.datumumrti, 'YYYYMM') is not null then true else false end as mrtev
from (
    select vyk.pseudonym_nzis, vyk.rok_mesic, vyk.vykony, null::varchar as farmakoterapie, null::varchar as farmakoterapie_odbornost
    from x_sandbox_syntetika.ext_63984482_az_03_vykony vyk
    where vyk.pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_03_filtr)
    union
    select far.pseudonym_nzis, far.rok_mesic, null::varchar, far.farmakoterapie, far.farmakoterapie_odbornost
    from x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie far
    where far.pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_03_filtr)
) res
left join x_nrhzs_data.v_umrti umrti
    on res.pseudonym_nzis = umrti.pseudonym_nzis
group by
    res.pseudonym_nzis,
    res.rok_mesic,
    to_char(umrti.datumumrti, 'YYYYMM'),
    umrti.prvotnipricinasmrtimkn10
;

DROP TABLE IF EXISTS x_sandbox_syntetika.ext_63984482_az_03_filtr;
DROP TABLE IF EXISTS x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie_odb;
DROP TABLE IF EXISTS x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie_atc;
DROP TABLE IF EXISTS x_sandbox_syntetika.ext_63984482_az_03_farmakoterapie;
DROP TABLE IF EXISTS x_sandbox_syntetika.ext_63984482_az_03_vykony;
