CREATE TABLE x_sandbox_syntetika.ext_63984482_az_05_filtr_hvlp AS
SELECT DISTINCT
    pseudonym_nzis
FROM x_nrhzs_data.v_polozka_01_hvlp
WHERE diagnoza LIKE 'C50%';

CREATE TABLE x_sandbox_syntetika.ext_63984482_az_05_hvlp AS
SELECT
    pseudonym_nzis,
    TO_CHAR(den, 'YYYYMM') AS rok_mesic,
    cis_atc_kod AS pripravek_vykon,
    mnozstvi AS mnozstvi_pocet,
    CASE
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 0 AND 5 THEN '0-5 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 6 AND 10 THEN '6-10 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 11 AND 15 THEN '11-15 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 16 AND 20 THEN '16-20 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 21 AND 25 THEN '21-25 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 26 AND 30 THEN '26-30 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 31 AND 35 THEN '31-35 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 36 AND 40 THEN '36-40 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 41 AND 45 THEN '41-45 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 46 AND 50 THEN '46-50 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 51 AND 55 THEN '51-55 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 56 AND 60 THEN '56-60 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 61 AND 65 THEN '61-65 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 66 AND 70 THEN '66-70 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 71 AND 75 THEN '71-75 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 76 AND 80 THEN '76-80 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 81 AND 85 THEN '81-85 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 86 AND 90 THEN '86-90 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 91 AND 95 THEN '91-95 let'
        ELSE 'Starsi 95 let'
    END AS vekovy_interval,
    icz
FROM x_nrhzs_data.v_polozka_01_hvlp
WHERE pseudonym_nzis IN (
    SELECT pseudonym_nzis
    FROM x_sandbox_syntetika.ext_63984482_az_05_filtr
)
AND cis_atc_kod IN (
    -- CDK4/6i
    'L01EF01', 'L01EF02', 'L01EF03',
    -- HT
    'L02BA01', 'L02BG06', 'L02BG04', 'L02BG03', 'L02BA03', 'L02BA04',
    -- CHT
    'L01DB01', 'L01AA01', 'L01CD01', 'L01CD02', 'L01CD03', 'L01CD04', 'L01XA01', 'L01XA02',
    'L01DB03', 'L04AX03', 'L01BC02', 'L01BC05', 'L01BC06', 'L01CA04', 'L01XX41',
    -- HER2 terapie
    'L01FD01', 'L01FD02', 'L01FD03', 'L01FD04', 'L01FY01', 'L01EH01', 'L01EH02', 'L01EH03',
    -- IO (imunoterapie)
    'L01FF02', 'L01FF05',
    -- LHRH analoga (L02AE)
    'L02AE01', 'L02AE02', 'L02AE03', 'L02AE04', 'L02AE05', 'L02AE51',
    -- Další:
    'L01FG01', 'L01EG02', 'L01EM03', 'L01XK01', 'L01XK04', 'L01FX17',
    -- Ostatní:
    'L03AA02', 'L03AA13', 'M05BX04', 'M05BA01', 'M05BA02', 'M05BA03', 'M05BA04', 'M05BA05',
    'M05BA06', 'M05BA07', 'M05BA08'
);

CREATE TABLE x_sandbox_syntetika.ext_63984482_az_05_filtr_vykon AS
SELECT DISTINCT
    pseudonym_nzis
FROM x_nrhzs_data.v_polozka_00_zdravotni_vykon
WHERE diagnoza LIKE 'C50%';

CREATE TABLE x_sandbox_syntetika.ext_63984482_az_05_filtr AS
SELECT pseudonym_nzis
FROM x_sandbox_syntetika.ext_63984482_az_05_filtr_hvlp
UNION
SELECT pseudonym_nzis
FROM x_sandbox_syntetika.ext_63984482_az_05_filtr_vykon;

CREATE TABLE x_sandbox_syntetika.ext_63984482_az_05_vykon AS
SELECT
    pseudonym_nzis,
    TO_CHAR(den, 'YYYYMM') AS rok_mesic,
    kod_polozky AS pripravek_vykon,
    mnozstvi AS mnozstvi_pocet,
    CASE
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 0 AND 5 THEN '0-5 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 6 AND 10 THEN '6-10 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 11 AND 15 THEN '11-15 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 16 AND 20 THEN '16-20 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 21 AND 25 THEN '21-25 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 26 AND 30 THEN '26-30 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 31 AND 35 THEN '31-35 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 36 AND 40 THEN '36-40 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 41 AND 45 THEN '41-45 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 46 AND 50 THEN '46-50 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 51 AND 55 THEN '51-55 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 56 AND 60 THEN '56-60 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 61 AND 65 THEN '61-65 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 66 AND 70 THEN '66-70 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 71 AND 75 THEN '71-75 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 76 AND 80 THEN '76-80 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 81 AND 85 THEN '81-85 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 86 AND 90 THEN '86-90 let'
        WHEN EXTRACT(YEAR FROM den) - rok_narozeni BETWEEN 91 AND 95 THEN '91-95 let'
        ELSE 'Starsi 95 let'
    END AS vekovy_interval,
    icz
FROM x_nrhzs_data.v_polozka_00_zdravotni_vykon
WHERE pseudonym_nzis IN (
    SELECT pseudonym_nzis
    FROM x_sandbox_syntetika.ext_63984482_az_05_filtr
)
AND kod_polozky IN (
    -- Operace
    '51283', '51285', '51287', '51289', '61449', '61467',
    -- Ostatní
    '51631', '61391', '61455', '61441', '61443', '89314', '89510', '89335', '89339', '89341', '89343', '89512',
    -- Přídavek 14.1
    '10177', '10179', '51233', '81737', '10102', '51231', '51233', '89223',
    -- Radioterapie
    '43210', '43213', '43215', '43217', '43219', '43311', '43313', '43315', '43317', '43319', '43434', '43611',
    '43631', '43633', '43635', '43637', '43638', '43641',
    -- Zobrazovací metody
    '43601', '43603', '43605', '47351', '47353', '47355', '47357', '47241', '47245', '47249', '47251',
    '89314', '89711', '89713', '89715', '89719', '89725', '89178', '89180', '89225', '89343', '89510', '63423', '63703',
    -- Ozáření
    '43613', '43617', '43639', '43652', '43653',
    -- Biopsie
   '89339',
   -- NGS
   '87701', '87800','87803','87804','87807','87808','94363','94365','94982','94983','94984','94985',
   -- RTG
   '89113','89115','89117','89119','89121','89131','89135',
   -- Radiochirurgicka lecba
   '91945',
   -- Podávání LP
   '06555','09220','42520'
);

SELECT
    DENSE_RANK() OVER (ORDER BY res.pseudonym_nzis) AS pacient_id,
    res.rok_mesic,
    res.pripravek_vykon,
    SUM(res.mnozstvi_pocet) AS mnozstvi_pocet,
    res.vekovy_interval,
    TO_CHAR(um.DatumUmrti, 'YYYYMM') AS rok_mesic_umrti,
    mkn_cis.mkn_kod3 AS pricina_umrti_mkn3,
    res.icz,
    nor.poradi AS morfologie_poradi,
    nor.morfologie_grade,
    nor.T_vysl,
    nor.N_vysl,
    nor.M_vysl,
    nor.M_OTH, nor.M_SKI, nor.M_LYM, nor.M_ADR, nor.M_BRA, nor.M_PER, nor.M_HEP, nor.M_PLE, nor.M_OSS, nor.M_MAR, nor.M_PUL, nor.M_XXX,
    TO_CHAR(nor.diagnoza_datum, 'YYYYMM') AS diagnoza_datum
FROM (
    SELECT pseudonym_nzis, rok_mesic, pripravek_vykon, mnozstvi_pocet, vekovy_interval, icz
    FROM x_sandbox_syntetika.ext_63984482_az_05_hvlp
    UNION
    SELECT pseudonym_nzis, rok_mesic, pripravek_vykon, mnozstvi_pocet, vekovy_interval, icz
    FROM x_sandbox_syntetika.ext_63984482_az_05_vykon
) res
LEFT JOIN x_nrhzs_data.v_umrti um ON um.pseudonym_nzis = res.pseudonym_nzis
LEFT JOIN x_nrhzs_ciselniky.V_CIS_MKN mkn_cis ON um.PrvotniPricinaSmrtiMKN10 = mkn_cis.mkn_kod5
LEFT JOIN (
    SELECT
        RANK() OVER (PARTITION BY nor.pseudonym_nzis ORDER BY nor.diagnoza_datum) AS poradi,
        nor.pseudonym_nzis,
        nor.morfologie_grade,
        nor.T_vysl,
        nor.N_vysl,
        nor.M_vysl,
        nor.M_OTH, nor.M_SKI, nor.M_LYM, nor.M_ADR, nor.M_BRA, nor.M_PER, nor.M_HEP, nor.M_PLE, nor.M_OSS, nor.M_MAR, nor.M_PUL, nor.M_XXX,
        nor.diagnoza_datum
    FROM x_nzis_data.v_nor nor
    WHERE nor.diagnoza LIKE 'C50%'
) nor ON nor.pseudonym_nzis = res.pseudonym_nzis
WHERE res.pseudonym_nzis IN (
    SELECT pseudonym_nzis
    FROM x_sandbox_syntetika.ext_63984482_az_05_filtr
)
GROUP BY
    res.pseudonym_nzis,
    res.rok_mesic,
    res.pripravek_vykon,
    res.vekovy_interval,
    res.icz,
    TO_CHAR(um.DatumUmrti, 'YYYYMM'),
    mkn_cis.mkn_kod3,
    nor.poradi,
    nor.morfologie_grade,
    nor.T_vysl,
    nor.N_vysl,
    nor.M_vysl,
    nor.M_OTH, nor.M_SKI, nor.M_LYM, nor.M_ADR, nor.M_BRA, nor.M_PER, nor.M_HEP, nor.M_PLE, nor.M_OSS, nor.M_MAR, nor.M_PUL, nor.M_XXX,
    nor.diagnoza_datum
ORDER BY
    DENSE_RANK() OVER (ORDER BY res.pseudonym_nzis), rok_mesic;

