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 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_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,
        taxonomy_grouping,
        cbsa,
        state
    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_id  -- General provider type mapping
    FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    CROSS JOIN UNNEST(ARRAY[
        'Short Term Acute Care Hospital',
        'Critical Access Hospital', 
        'Childrens Hospital',
        'Rehabilitation 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_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 }}
        )
    
    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_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 hcpcs
            FROM {{ cld_params.Tables.CLINICAL_LAB_REFERENCE_PRICING.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_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
    
    -- 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,
        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.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)
            )
),
df AS (
    -- Use UNION ALL with two parts for computational efficiency:

    -- First part: provider_type only (non-ASC)
    SELECT 
        from_big_endian_64(xxhash64(CAST(
            payer_id || 
            CAST(network_id AS VARCHAR) ||
            pr.provider_id ||
            cpf.bill_type ||
            cpf.billing_code ||
            cpf.billing_code_type ||
            COALESCE(CAST(cpf.facility AS VARCHAR), '')
            AS varbinary
        ))) AS roid,
        payer_id,
        network_id,
        pr.provider_id,
        pr.provider_type,
        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 IS NULL)
    
    UNION ALL

    -- Second part: provider_type and provider_id (ASC)
    SELECT
        from_big_endian_64(xxhash64(CAST(
            payer_id || 
            CAST(network_id AS VARCHAR) ||
            pr.provider_id ||
            cpf.bill_type ||
            cpf.billing_code ||
            cpf.billing_code_type ||
            COALESCE(CAST(cpf.facility AS VARCHAR), '')
            AS varbinary
        ))) AS roid,
        payer_id,
        network_id,
        pr.provider_id,
        pr.provider_type,
        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
{% endif %}