Rate Object Space

---

This version's scope is defined at: "What's in the Data?"

SQL

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} 
-- Subversion: {{ sub_version }}


CREATE OR REPLACE TABLE {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH 

-- payers
ref_payers AS (
    SELECT 
        distinct payer_id, network_id, network_class, network_name, network_state
    FROM {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
    {% if sub_version == 'test' or '_test' in schema_name %}
        WHERE payer_id IN ('643', '76', '628', '42', '7')
    {% endif %}
),

-- providers
ref_providers AS (
    SELECT 
        provider_id,
        provider_type,
        provider_subtype,
        taxonomy_grouping,
        cbsa,
        state,
        national_payer_coverage
    FROM {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }}
    {% if sub_version == 'test' or '_test' in schema_name %}
    WHERE provider_id IN (
        SELECT provider_id FROM tq_intermediate.cld_utils.test_data_sample_ros_providers
    )
    {% endif %}
),

-- geo (state-cbsa)
state_cbsa AS (
    SELECT 
        s.state_abbreviation, 
        c.cbsa_id
    FROM {{ cld_params.Tables.SPINES_GEO_RELATIONSHIPS.value }} r
    JOIN {{ cld_params.Tables.SPINES_GEO_STATE.value }} s
        ON r.source_id = s.state_id
    JOIN {{ cld_params.Tables.SPINES_GEO_CBSA.value }} c
        ON r.dest_id = c.cbsa_id
    WHERE 
        r.dest_type = 'cbsa'
        AND r.source_type = 'state'
),

-- Provider type mappings for all providers
code_plausibility_flattened AS (
    -- Hospital codes (Inpatient/Outpatient) - multiple provider types per code
    SELECT DISTINCT
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        t.provider_type,
        NULL as provider_subtype,
        NULL as provider_id  -- General provider type mapping
    FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    CROSS JOIN UNNEST(ARRAY[
        'Hospital'
    ]) AS t(provider_type)
    WHERE cs.bill_type IN ('Inpatient', 'Outpatient')
    
    UNION ALL
    
    -- Physician Group codes
    SELECT DISTINCT
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        'Physician Group' as provider_type,
        NULL as provider_subtype,
        NULL as provider_id  -- General provider type mapping
    FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    WHERE cs.bill_type = 'Professional' 
        AND 
        (
            cs.billing_code IN (
                SELECT procedure_code
                FROM {{ cld_params.Tables.CLD_PHYSICIAN_GROUP_CODESET.value }}
            )
            OR
            cs.billing_code IN (
                SELECT billing_code
                FROM {{ cld_params.Tables.LAB_HOSPITAL_AND_PGS_CODESET.value }}
            )
        )
    
    UNION ALL
    
    -- Laboratory codes
    SELECT DISTINCT
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        'Laboratory' as provider_type,
        NULL as provider_subtype,
        NULL as provider_id  -- General provider type mapping
    FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    WHERE cs.bill_type = 'Professional'
        AND cs.facility = False
        AND 
        (
            cs.billing_code IN (
                SELECT hcpcs
                FROM {{ cld_params.Tables.CLINICAL_LAB_REFERENCE_PRICING.value }}
            )
            OR
            cs.billing_code IN (
                SELECT billing_code
                FROM {{ cld_params.Tables.LAB_HOSPITAL_AND_PGS_CODESET.value }}
            )
        )
    
    UNION ALL
    
    -- Imaging Center codes (from SSP packages)
    SELECT DISTINCT
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        'Imaging Center' as provider_type,
        NULL as provider_subtype,
        NULL as provider_id  -- General provider type mapping
    FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    WHERE cs.bill_type = 'Professional'
        AND cs.billing_code IN (
            SELECT line_code
            FROM {{ cld_params.Tables.SSP_SUB_PACKAGES.value }} c
            LEFT JOIN {{ cld_params.Tables.SSP_PACKAGE_CONTENTS.value }} s
                ON s.sub_package_id = c.sub_package_id
            WHERE ssp_id LIKE 'RA%'
                AND fee_type IN ('Professional Fee', 'Facility Fee', 'Optional Fee')
                AND item_type IN ('package_line')
        )
    
    UNION ALL
    
    -- INFUSION CENTER mappings
    SELECT DISTINCT
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        NULL as provider_type,
        'Infusion Center' as provider_subtype,
        NULL as provider_id
    FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    WHERE cs.bill_type = 'Professional'
        AND cs.is_drug_code = True
        AND cs.facility = False

    UNION ALL
    
    -- ASC mappings from plausibility table (includes specific provider_ids)
    SELECT DISTINCT
        asc.bill_type,
        asc.billing_code_type,
        asc.billing_code,
        asc.facility,
        'ASC' as provider_type,
        NULL as provider_subtype,
        asc.provider_id
    FROM {{ schema_name }}.tmp_ref_asc_plausibility_{{ sub_version }} asc
    WHERE asc.is_plausible = True

),

payer_plan_providers AS (
    -- get all permutations of payer-network, provider_id, billing_code
    SELECT 
        p.payer_id,
        p.network_id,
        pr.provider_id,
        pr.provider_type,
        pr.provider_subtype,
        pr.taxonomy_grouping
    FROM ref_payers p
    INNER JOIN ref_providers pr
        ON
            -- NATIONAL payers
            CONTAINS(p.network_state, 'NATIONAL')
            -- Provider is in a state the payer covers
            OR CONTAINS(p.network_state, pr.state)
            -- OR the provider is in a CBSA mapped to a state the payer covers
            OR pr.cbsa IN (
                SELECT cbsa_id
                FROM state_cbsa sc
                WHERE CONTAINS(p.network_state, sc.state_abbreviation)
            )
            OR pr.national_payer_coverage = True
    WHERE
        p.network_class != 'Exchange'
        OR
        -- NYC Essential Plans get all Provider Types
        p.network_name = 'Essential Plan'
        -- Exchange Networks get only Hospitals
        OR
        (
            p.network_class = 'Exchange'
            AND 
            pr.provider_type = ('Hospital')
        )
),
df AS (
    -- Use UNION ALL with two parts for computational efficiency:

    -- First part: provider_type only (non-ASC)
    SELECT 
        to_hex(substr(
            sha256(CAST(json_format(CAST(ARRAY[
                CAST(pr.provider_type AS VARCHAR),
                CAST(payer_id AS VARCHAR),
                CAST(network_id AS VARCHAR),
                CAST(pr.provider_id AS VARCHAR),
                CAST(cpf.bill_type AS VARCHAR),
                CAST(cpf.billing_code AS VARCHAR),
                CAST(cpf.billing_code_type AS VARCHAR),
                COALESCE(CAST(cpf.facility AS VARCHAR), '')
            ] AS JSON)) AS VARBINARY)),
            1, 12
        )) AS roid,
        payer_id,
        network_id,
        pr.provider_id,
        pr.provider_type,
        pr.provider_subtype,
        taxonomy_grouping,
        cpf.bill_type,
        cpf.billing_code,
        cpf.billing_code_type,
        cpf.facility,
        True as is_plausible
    FROM payer_plan_providers pr
    INNER JOIN code_plausibility_flattened cpf
        ON (
            (cpf.provider_type = pr.provider_type AND COALESCE(pr.provider_subtype, '') != 'Infusion Center')
            OR (cpf.provider_subtype = pr.provider_subtype AND pr.provider_subtype = 'Infusion Center')
        )
        AND cpf.provider_id IS NULL
    
    UNION ALL

    -- Second part: provider_type and provider_id (ASC)
    SELECT
        to_hex(substr(
            sha256(CAST(json_format(CAST(ARRAY[
                CAST(pr.provider_type AS VARCHAR),
                CAST(payer_id AS VARCHAR),
                CAST(network_id AS VARCHAR),
                CAST(pr.provider_id AS VARCHAR),
                CAST(cpf.bill_type AS VARCHAR),
                CAST(cpf.billing_code AS VARCHAR),
                CAST(cpf.billing_code_type AS VARCHAR),
                COALESCE(CAST(cpf.facility AS VARCHAR), '')
            ] AS JSON)) AS VARBINARY)),
            1, 12
        )) AS roid,
        payer_id,
        network_id,
        pr.provider_id,
        pr.provider_type,
        pr.provider_subtype,
        taxonomy_grouping,
        cpf.bill_type,
        cpf.billing_code,
        cpf.billing_code_type,
        cpf.facility,
        True as is_plausible
    FROM payer_plan_providers pr
    INNER JOIN code_plausibility_flattened cpf
        ON (cpf.provider_type = pr.provider_type AND cpf.provider_id = pr.provider_id)
)
{% if sub_version == 'test' or '_test' in schema_name %}
SELECT
    *
FROM df
WHERE roid IN (
    SELECT roid
    FROM tq_intermediate.cld_utils.test_data_sample_ros
)
{% else %}
SELECT
    *
FROM df
WHERE
    -- CAR-T PLAUSIBILITY
    -- Keep all rows except MS-DRG 018 for providers not in CAR-T centers
    (billing_code != '018' OR billing_code_type != 'MS-DRG')
    OR
    -- Allow MS-DRG 018 only for CAR-T centers
    (billing_code = '018' AND billing_code_type = 'MS-DRG' AND provider_id IN (
        SELECT provider_id FROM {{ cld_params.Tables.CAR_T_PLAUSIBILITY.value }}
    ))
{% endif %}