BEGIN

WITH order_riwayat AS (
    SELECT
        Mgm_RiwayatMcu_RiwayatID,
        Mgm_RiwayatMgm_HeaderID,
        Mgm_HeaderIsNormal
    FROM
        one_etl.mgm_riwayat
    JOIN one_etl.mgm_header
    ON Mgm_RiwayatMgm_HeaderID = Mgm_HeaderID
    WHERE
        Mgm_HeaderMgm_McuID = PID
        AND Mgm_RiwayatMcu_RiwayatGroupID = 6
),
order_location AS (
    SELECT DISTINCT
        COALESCE(NULLIF(Mgm_HeaderM_PatientLocation, ''), '-') AS location,
        Mgm_HeaderID
    FROM
        one_etl.mgm_header
    WHERE
        Mgm_HeaderMgm_McuID = PID
),
total_peserta AS (
    SELECT 
        COALESCE(NULLIF(Mgm_HeaderM_PatientLocation, ''), '-') AS location,
        COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta
    FROM 
        one_etl.mgm_header
    WHERE 
        Mgm_HeaderMgm_McuID = PID
        AND Mgm_HeaderIsActive = 'Y'
    GROUP BY 
        COALESCE(NULLIF(Mgm_HeaderM_PatientLocation, ''), '-')
),
Counts AS (
    SELECT
        ol.location AS site_perusahaan,
        -- Hitung ketika Mgm_HeaderIsNormal = 'Y'
        COUNT(CASE WHEN orw.Mgm_RiwayatMcu_RiwayatID = 149 AND (orw.Mgm_HeaderIsNormal = 'Y' OR orw.Mgm_HeaderIsNormal = 'N') THEN 1 END) AS Tidak_Merokok_Y,
        COUNT(CASE WHEN orw.Mgm_RiwayatMcu_RiwayatID IN (150, 151, 152, 153) AND (orw.Mgm_HeaderIsNormal = 'Y' OR orw.Mgm_HeaderIsNormal = 'N') THEN 1 END) AS Merokok_Y,
        COUNT(CASE WHEN orw.Mgm_RiwayatMcu_RiwayatID = 141 AND (orw.Mgm_HeaderIsNormal = 'Y' OR orw.Mgm_HeaderIsNormal = 'N') THEN 1 END) AS Tidak_Minum_Alkohol_Y,
        COUNT(CASE WHEN orw.Mgm_RiwayatMcu_RiwayatID IN (142, 143, 144) AND (orw.Mgm_HeaderIsNormal = 'Y' OR orw.Mgm_HeaderIsNormal = 'N') THEN 1 END) AS Minum_Alkohol_Y,
        COUNT(CASE WHEN orw.Mgm_RiwayatMcu_RiwayatID = 145 AND (orw.Mgm_HeaderIsNormal = 'Y' OR orw.Mgm_HeaderIsNormal = 'N') THEN 1 END) AS Tidak_Rutin_Olahraga_Y,
        COUNT(CASE WHEN orw.Mgm_RiwayatMcu_RiwayatID IN (146, 147, 148) AND (orw.Mgm_HeaderIsNormal = 'Y' OR orw.Mgm_HeaderIsNormal = 'N') THEN 1 END) AS Olahraga_Y,
        -- Hitung Total pasien
        tp.total_peserta AS total_peserta
    FROM
        order_location ol
    LEFT JOIN order_riwayat orw ON ol.Mgm_HeaderID = orw.Mgm_RiwayatMgm_HeaderID
    LEFT JOIN total_peserta tp ON ol.location = tp.location
    GROUP BY
        ol.location, tp.total_peserta
)
SELECT
    site_perusahaan,
    Tidak_Merokok_Y,
    Merokok_Y,
    (total_peserta - (Tidak_Merokok_Y+Merokok_Y)) AS Merokok_NA,
    Tidak_Minum_Alkohol_Y,
    Minum_Alkohol_Y,
     (total_peserta - (Tidak_Minum_Alkohol_Y+Minum_Alkohol_Y)) AS Alkohol_NA,
    Tidak_Rutin_Olahraga_Y,
    Olahraga_Y,
   (total_peserta - (Tidak_Rutin_Olahraga_Y+Olahraga_Y)) AS  Olahraga_NA,
    total_peserta AS Peserta
FROM
    Counts
ORDER BY
    site_perusahaan;

END