CREATE TABLE x_sandbox_syntetika.pacient_lecba_icp_brand AS
SELECT
    p.pseudonym_nzis,
    p.icp,
    p.icz,
    p.cis_nazev AS brand,
    p.cis_atc_kod_posledni AS atc,
    p.den AS datum_vydani,
    DATE_TRUNC('quarter', p.den) AS ctvrleti,
    TO_CHAR(p.den, 'YYYY') || '-Q' || EXTRACT(QUARTER FROM p.den) AS rok_kvartal,
    EXTRACT(YEAR FROM p.den) AS rok
FROM x_nrhzs_data.v_polozka_01_hvlp p
WHERE p.den >= DATE '2018-01-01';

WITH 
-- Kvartály v datech a maximální datum v každém kvartálu
kvartaly_v_datech AS (
    SELECT 
        TO_CHAR(datum_vydani, 'YYYY') || '-Q' || EXTRACT(QUARTER FROM datum_vydani) AS rok_kvartal,
        MAX(datum_vydani) AS max_datum_v_kvartalu
    FROM x_sandbox_syntetika.pacient_lecba_icp_brand
    GROUP BY 1
),

-- Poslední úplný kvartál (končící na poslední den čtvrtletí)
posledni_uplny_kvartal AS (
    SELECT 
        rok_kvartal AS rk,
        max_datum_v_kvartalu AS konec_dat
    FROM kvartaly_v_datech
    WHERE max_datum_v_kvartalu::DATE = 
        ADD_MONTHS(DATE_TRUNC('quarter', max_datum_v_kvartalu::DATE), 3) - INTERVAL '1 day'
    ORDER BY konec_dat DESC
    LIMIT 1
),

-- Rok a číslo kvartálu pro přehlednost
rok_kvartalu AS (
    SELECT 
        rk, 
        konec_dat,
        CAST(SPLIT_PART(rk, '-Q', 1) AS INT) AS rok,
        CAST(SPLIT_PART(rk, '-Q', 2) AS INT) AS kvartal
    FROM posledni_uplny_kvartal
),

-- Označení prvních výdejů v rámci ICZ a ICZ + brand
data_s_oznacenim AS (
    SELECT *,
        MIN(datum_vydani) OVER (PARTITION BY pseudonym_nzis, icz) AS prvni_den_v_icz,
        MIN(datum_vydani) OVER (PARTITION BY pseudonym_nzis, icz, brand) AS prvni_den_v_icz_brand
    FROM x_sandbox_syntetika.pacient_lecba_icp_brand
),

-- Aktivní pacienti s daným ATC za posledních 12 měsíců
aktivni_poslednich_12m AS (
    SELECT DISTINCT p.pseudonym_nzis, p.icz, p.brand, p.icp
    FROM data_s_oznacenim p
    JOIN rok_kvartalu r 
      ON p.datum_vydani BETWEEN r.konec_dat - INTERVAL '12 months' AND r.konec_dat
    WHERE p.atc LIKE 'V01%'
),

-- Pacienti s prvním výdejem brandu v ICZ právě ve sledovaném kvartále
prvni_v_icz_brand_v_poslednim_kvartale AS (
    SELECT DISTINCT p.pseudonym_nzis, p.icz, p.brand
    FROM data_s_oznacenim p
    JOIN rok_kvartalu r 
      ON TO_CHAR(p.prvni_den_v_icz_brand, 'YYYY') || '-Q' || EXTRACT(QUARTER FROM p.prvni_den_v_icz_brand) = r.rk
    WHERE p.atc LIKE 'V01%'
),

-- Noví pacienti pro danou ICZ (bez ohledu na ICP či brand)
posledni_kvartal_bez_brandu_clean AS (
    SELECT 
        p.icz,
        COUNT(DISTINCT p.pseudonym_nzis) AS pocet
    FROM data_s_oznacenim p
    JOIN rok_kvartalu r 
      ON TO_CHAR(p.prvni_den_v_icz, 'YYYY') || '-Q' || EXTRACT(QUARTER FROM p.prvni_den_v_icz) = r.rk
    WHERE p.atc LIKE 'V01%'
    GROUP BY p.icz
)

-- Finální výstup: 
SELECT
    r.rok,
    r.kvartal,
    a.icz,
    a.icp,
    a.brand,
    MIN(COALESCE(b.pocet, 0)) AS posledni_kvartal_pocet_bez_brandu,
	COUNT(DISTINCT CASE WHEN pib.pseudonym_nzis IS NOT NULL THEN a.pseudonym_nzis END) AS posledni_kvartal_pocet_s_brandem,
    COUNT(DISTINCT CASE WHEN EXTRACT(YEAR FROM p.prvni_den_v_icz_brand) = r.rok THEN a.pseudonym_nzis END) AS prvnilecba_letos,
    COUNT(DISTINCT CASE WHEN EXTRACT(YEAR FROM p.prvni_den_v_icz_brand) = r.rok - 1 THEN a.pseudonym_nzis END) AS prvnilecba_minus1rok,
    COUNT(DISTINCT CASE WHEN EXTRACT(YEAR FROM p.prvni_den_v_icz_brand) = r.rok - 2 THEN a.pseudonym_nzis END) AS prvnilecba_minus2roky,
    COUNT(DISTINCT CASE WHEN EXTRACT(YEAR FROM p.prvni_den_v_icz_brand) = r.rok - 3 THEN a.pseudonym_nzis END) AS prvnilecba_minus3roky,
    COUNT(DISTINCT CASE WHEN EXTRACT(YEAR FROM p.prvni_den_v_icz_brand) <= r.rok - 4 THEN a.pseudonym_nzis END) AS prvnilecba_minus4rokyavice
FROM aktivni_poslednich_12m a
LEFT JOIN data_s_oznacenim p 
    ON a.pseudonym_nzis = p.pseudonym_nzis 
    AND a.icz = p.icz 
    AND a.brand = p.brand
CROSS JOIN rok_kvartalu r
LEFT JOIN prvni_v_icz_brand_v_poslednim_kvartale pib 
    ON a.pseudonym_nzis = pib.pseudonym_nzis 
    AND a.icz = pib.icz 
    AND a.brand = pib.brand
LEFT JOIN posledni_kvartal_bez_brandu_clean b 
    ON a.icz = b.icz
GROUP BY 
    r.rok, r.kvartal, a.icz, a.icp, a.brand ORDER BY a.icz;

DROP TABLE x_sandbox_syntetika.pacient_lecba_icp_brand;
