idr_claim_professional_ss.py

from datetime import date, datetime
from typing import Annotated, override

from pydantic import BeforeValidator

from ..constants import (
    CLAIM_PROFESSIONAL_SS_TABLE,
    DEFAULT_MAX_DATE,
    IDR_CLAIM_DATE_SIGNATURE_TABLE,
    IDR_CLAIM_DOCUMENTATION_TABLE,
    IDR_CLAIM_LOCATION_HISTORY_TABLE,
    IDR_CLAIM_PROFESSIONAL_TABLE,
    IDR_CLAIM_TABLE,
    IDR_PROVIDER_HISTORY_TABLE,
)
from ..load_partition import LoadPartition
from ..model.base_model import (
    ALIAS,
    ALIAS_CLM,
    ALIAS_LCTN_HSTRY,
    ALIAS_OCRNC_SGNTR_DERIVED_DATES,
    ALIAS_PRFNL,
    ALIAS_PRVDR_BLG,
    ALIAS_PRVDR_OTHR,
    ALIAS_PRVDR_RFRG,
    ALIAS_RLT_OCRNC_SGNTR_DERIVED_DATES,
    ALIAS_SGNTR,
    BATCH_ID,
    BATCH_TIMESTAMP,
    COLUMN_MAP,
    EXPR,
    HISTORICAL_BATCH_TIMESTAMP,
    INSERT_EXCLUDE,
    INSERT_FIELD,
    LAST_UPDATED_TIMESTAMP,
    PRIMARY_KEY_ORDER,
    UPDATE_TIMESTAMP,
    IdrBaseModel,
    ModelType,
    Source,
    base_claim_filter,
    claim_occurrence_cte,
    claim_related_occurrences_cte,
    clm_base_query,
    clm_child_query,
    clm_dt_sgntr_query,
    clm_ocrnc_sgntr_query,
    clm_orig_cntl_num_expr,
    clm_query,
    clm_rlt_ocrnc_clause,
    provider_careteam_name_expr,
    provider_last_or_legal_name_expr,
    provider_npi_type_expr,
    transform_default_date_to_null,
    transform_default_string,
    transform_null_date_to_min,
)


class IdrClaimProfessionalSs(IdrBaseModel):
    # Columns from v2_mdcr_clm
    clm_uniq_id: Annotated[
        int,
        {PRIMARY_KEY_ORDER: 0, BATCH_ID: True, ALIAS: ALIAS_CLM, LAST_UPDATED_TIMESTAMP: True},
    ]
    clm_type_cd: Annotated[int, {ALIAS: ALIAS_CLM}]
    bene_sk: Annotated[int, ALIAS:ALIAS_CLM]
    clm_cntl_num: Annotated[str, {ALIAS: ALIAS_CLM}]
    clm_orig_cntl_num: Annotated[
        str,
        {
            ALIAS: ALIAS_CLM,
            EXPR: clm_orig_cntl_num_expr(),
        },
        BeforeValidator(transform_default_string),
    ]
    clm_from_dt: Annotated[date, {ALIAS: ALIAS_CLM}]
    clm_thru_dt: Annotated[date, {ALIAS: ALIAS_CLM}]
    clm_efctv_dt: Annotated[date, {ALIAS: ALIAS_CLM}]
    clm_obslt_dt: Annotated[
        date | None, {ALIAS: ALIAS_CLM}, BeforeValidator(transform_default_date_to_null)
    ]
    clm_finl_actn_ind: Annotated[str, {ALIAS: ALIAS_CLM}]
    clm_src_id: Annotated[str, {ALIAS: ALIAS_CLM}]
    meta_src_sk: Annotated[int, {ALIAS: ALIAS_CLM}]
    clm_bene_pmt_amt: Annotated[float | None, {ALIAS: ALIAS_CLM}]
    clm_pd_dt: Annotated[
        date | None, {ALIAS: ALIAS_CLM}, BeforeValidator(transform_default_date_to_null)
    ]
    clm_ltst_clm_ind: Annotated[str, {ALIAS: ALIAS_CLM}]
    clm_prvdr_pmt_amt: float | None
    clm_adjstmt_type_cd: Annotated[
        str, {ALIAS: ALIAS_CLM}, BeforeValidator(transform_default_string)
    ]
    clm_pd_stus_cd: Annotated[str, {ALIAS: ALIAS_CLM}, BeforeValidator(transform_default_string)]
    clm_cntrctr_num: Annotated[str, BeforeValidator(transform_default_string)]
    clm_pmt_amt: Annotated[float | None, {ALIAS: ALIAS_CLM}]
    clm_alowd_chrg_amt: float | None
    clm_sbmt_chrg_amt: Annotated[float | None, {ALIAS: ALIAS_CLM}]
    clm_bene_pd_amt: Annotated[float | None, {ALIAS: ALIAS_CLM}]
    clm_blg_prvdr_tax_num: Annotated[
        str, {ALIAS: ALIAS_CLM}, BeforeValidator(transform_default_string)
    ]
    clm_mdcr_coinsrnc_amt: float | None
    clm_blood_lblty_amt: float | None
    clm_ncvrd_chrg_amt: float | None
    clm_nch_prmry_pyr_cd: Annotated[str, BeforeValidator(transform_default_string)]
    clm_sbmt_frmt_cd: Annotated[str, BeforeValidator(transform_default_string)]
    clm_bene_intrst_pd_amt: float | None
    clm_bene_pmt_coinsrnc_amt: float | None
    clm_cob_ptnt_resp_amt: float | None
    clm_prvdr_otaf_amt: float | None
    clm_othr_tp_pd_amt: float | None
    clm_prvdr_rmng_due_amt: float | None
    clm_blood_ncvrd_chrg_amt: float | None
    clm_prvdr_intrst_pd_amt: float | None
    idr_insrt_ts: Annotated[
        datetime,
        {BATCH_TIMESTAMP: True, INSERT_EXCLUDE: True, ALIAS: ALIAS_CLM, COLUMN_MAP: "idr_insrt_ts"},
        BeforeValidator(transform_null_date_to_min),
    ]
    idr_updt_ts: Annotated[
        datetime,
        {UPDATE_TIMESTAMP: True, INSERT_EXCLUDE: True, ALIAS: ALIAS_CLM, COLUMN_MAP: "idr_updt_ts"},
        BeforeValidator(transform_null_date_to_min),
    ]
    clm_idr_ld_dt: Annotated[date, {HISTORICAL_BATCH_TIMESTAMP: True, ALIAS: ALIAS_CLM}]

    # Columns from v2_mdcr_clm_dt_sngtr
    clm_submsn_dt: Annotated[date | None, BeforeValidator(transform_default_date_to_null)]
    idr_insrt_ts_sgntr: Annotated[
        datetime,
        {
            BATCH_TIMESTAMP: True,
            INSERT_EXCLUDE: True,
            ALIAS: ALIAS_SGNTR,
            COLUMN_MAP: "idr_insrt_ts",
        },
        BeforeValidator(transform_null_date_to_min),
    ]
    idr_updt_ts_sgntr: Annotated[
        datetime,
        {
            UPDATE_TIMESTAMP: True,
            INSERT_EXCLUDE: True,
            ALIAS: ALIAS_SGNTR,
            COLUMN_MAP: "idr_updt_ts",
        },
        BeforeValidator(transform_null_date_to_min),
    ]

    # Columns from v2_mdcr_clm_prfnl
    clm_clncl_tril_num: Annotated[str, BeforeValidator(transform_default_string)]
    clm_mdcr_ddctbl_amt: float | None
    clm_mdcr_prfnl_prvdr_asgnmt_sw: Annotated[str, BeforeValidator(transform_default_string)]
    clm_prvdr_acnt_rcvbl_ofst_amt: float | None
    idr_insrt_ts_prfnl: Annotated[
        datetime,
        {
            BATCH_TIMESTAMP: True,
            INSERT_EXCLUDE: True,
            ALIAS: ALIAS_PRFNL,
            COLUMN_MAP: "idr_insrt_ts",
        },
        BeforeValidator(transform_null_date_to_min),
    ]
    idr_updt_ts_prfnl: Annotated[
        datetime,
        {
            UPDATE_TIMESTAMP: True,
            INSERT_EXCLUDE: True,
            ALIAS: ALIAS_PRFNL,
            COLUMN_MAP: "idr_updt_ts",
        },
        BeforeValidator(transform_null_date_to_min),
    ]
    # columns from v2_mdcr_clm_lctn_hstry
    clm_audt_trl_stus_cd: Annotated[
        str, {ALIAS: ALIAS_LCTN_HSTRY}, BeforeValidator(transform_default_string)
    ]
    clm_audt_trl_lctn_cd: Annotated[
        str, {ALIAS: ALIAS_LCTN_HSTRY}, BeforeValidator(transform_default_string)
    ]
    idr_insrt_ts_lctn_hstry: Annotated[
        datetime,
        {
            BATCH_TIMESTAMP: True,
            ALIAS: ALIAS_LCTN_HSTRY,
            INSERT_EXCLUDE: True,
            COLUMN_MAP: "idr_insrt_ts",
        },
        BeforeValidator(transform_null_date_to_min),
    ]
    idr_updt_ts_lctn_hstry: Annotated[
        datetime,
        {
            UPDATE_TIMESTAMP: True,
            ALIAS: ALIAS_LCTN_HSTRY,
            INSERT_EXCLUDE: True,
            COLUMN_MAP: "idr_updt_ts",
        },
        BeforeValidator(transform_null_date_to_min),
    ]

    # Columns from v2_mdcr_prvdr_hstry
    prvdr_blg_prvdr_npi_num: Annotated[
        str,
        {COLUMN_MAP: "prvdr_npi_num", ALIAS: ALIAS_PRVDR_BLG},
        BeforeValidator(transform_default_string),
    ]
    prvdr_blg_1st_name: Annotated[
        str,
        {COLUMN_MAP: "prvdr_1st_name", ALIAS: ALIAS_PRVDR_BLG},
        BeforeValidator(transform_default_string),
    ]
    bfd_prvdr_blg_last_or_lgl_name: Annotated[
        str,
        {EXPR: provider_last_or_legal_name_expr(ALIAS_PRVDR_BLG)},
        BeforeValidator(transform_default_string),
    ]

    bfd_blg_prvdr_npi_type: Annotated[
        int | None,
        {EXPR: provider_npi_type_expr(ALIAS_PRVDR_BLG)},
    ]

    prvdr_rfrg_prvdr_npi_num: Annotated[
        str,
        {COLUMN_MAP: "prvdr_npi_num", ALIAS: ALIAS_PRVDR_RFRG},
        BeforeValidator(transform_default_string),
    ]
    bfd_prvdr_rfrg_careteam_name: Annotated[
        str,
        {EXPR: provider_careteam_name_expr(ALIAS_PRVDR_RFRG, None)},
        BeforeValidator(transform_default_string),
    ]

    bfd_prvdr_rfrg_npi_type: Annotated[
        int | None,
        {EXPR: provider_npi_type_expr(ALIAS_PRVDR_RFRG)},
    ]

    prvdr_othr_prvdr_npi_num: Annotated[
        str,
        {COLUMN_MAP: "prvdr_npi_num", ALIAS: ALIAS_PRVDR_OTHR},
        BeforeValidator(transform_default_string),
    ]
    bfd_prvdr_othr_careteam_name: Annotated[
        str,
        {EXPR: provider_careteam_name_expr(ALIAS_PRVDR_OTHR, "OTHR")},
        BeforeValidator(transform_default_string),
    ]

    bfd_prvdr_othr_npi_type: Annotated[
        int | None,
        {EXPR: provider_npi_type_expr(ALIAS_PRVDR_OTHR)},
    ]

    # columns derived from v2_mdcr_clm_ocrnc_sgntr_mbr
    bfd_clm_ncvrd_from_dt: Annotated[
        date | None,
        {ALIAS: ALIAS_OCRNC_SGNTR_DERIVED_DATES},
        BeforeValidator(transform_default_date_to_null),
    ]
    bfd_clm_ncvrd_thru_dt: Annotated[
        date | None,
        {ALIAS: ALIAS_OCRNC_SGNTR_DERIVED_DATES},
        BeforeValidator(transform_default_date_to_null),
    ]
    bfd_clm_qlfy_stay_from_dt: Annotated[
        date | None,
        {ALIAS: ALIAS_OCRNC_SGNTR_DERIVED_DATES},
        BeforeValidator(transform_default_date_to_null),
    ]
    bfd_clm_qlfy_stay_thru_dt: Annotated[
        date | None,
        {ALIAS: ALIAS_OCRNC_SGNTR_DERIVED_DATES},
        BeforeValidator(transform_default_date_to_null),
    ]
    idr_insrt_ts_ocrnc_sgntr: Annotated[
        datetime,
        {ALIAS: ALIAS_OCRNC_SGNTR_DERIVED_DATES, **INSERT_FIELD},
        BeforeValidator(transform_null_date_to_min),
    ]

    # columns derived from v2_clm_rlt_ocrnc_sgntr_mbr
    bfd_clm_mdcr_exhstd_dt: Annotated[
        date | None,
        {ALIAS: ALIAS_RLT_OCRNC_SGNTR_DERIVED_DATES},
        BeforeValidator(transform_default_date_to_null),
    ]
    bfd_clm_actv_care_thru_dt: Annotated[
        date | None,
        {ALIAS: ALIAS_RLT_OCRNC_SGNTR_DERIVED_DATES},
        BeforeValidator(transform_default_date_to_null),
    ]
    idr_insrt_ts_rlt_ocrnc_sgntr: Annotated[
        datetime,
        {ALIAS: ALIAS_RLT_OCRNC_SGNTR_DERIVED_DATES, **INSERT_FIELD},
        BeforeValidator(transform_null_date_to_min),
    ]

    @override
    @staticmethod
    def table() -> str:
        return CLAIM_PROFESSIONAL_SS_TABLE

    @override
    @staticmethod
    def last_updated_date_column() -> list[str]:
        return ["bfd_claim_updated_ts"]

    @override
    @staticmethod
    def model_type() -> ModelType:
        return ModelType.CLAIM_PROFESSIONAL_SS

    @override
    @classmethod
    def fetch_query(cls, partition: LoadPartition, start_time: datetime, source: Source) -> str:
        clm = ALIAS_CLM
        sgntr = ALIAS_SGNTR
        prfnl = ALIAS_PRFNL
        lctn_hstry = ALIAS_LCTN_HSTRY
        prvdr_blg = ALIAS_PRVDR_BLG
        prvdr_rfrg = ALIAS_PRVDR_RFRG
        prvdr_othr = ALIAS_PRVDR_OTHR
        ocrnc_sgntr_dd = ALIAS_OCRNC_SGNTR_DERIVED_DATES
        rlt_ocrnc_sgntr_dd = ALIAS_RLT_OCRNC_SGNTR_DERIVED_DATES
        not_materialized = "" if source == Source.SNOWFLAKE else "NOT MATERIALIZED"
        return f"""
            WITH claim_base AS (
                {clm_base_query(start_time, partition, cls.model_type())}
            ),
            claims AS (
                {clm_query()}
                UNION
                {clm_dt_sgntr_query()}
                UNION
                {clm_child_query(IDR_CLAIM_PROFESSIONAL_TABLE)}
                UNION
                {clm_child_query(IDR_CLAIM_DOCUMENTATION_TABLE)}
                UNION
                {clm_child_query(IDR_CLAIM_LOCATION_HISTORY_TABLE)}
                UNION
                {clm_ocrnc_sgntr_query()}
                UNION
                {clm_rlt_ocrnc_clause()}
            ),
            latest_clm_lctn_hstry AS (
                SELECT
                    claims.geo_bene_sk,
                    claims.clm_type_cd,
                    claims.clm_dt_sgntr_sk,
                    claims.clm_num_sk,
                    MAX({lctn_hstry}.clm_lctn_cd_sqnc_num) AS max_clm_lctn_cd_sqnc_num
                FROM {IDR_CLAIM_LOCATION_HISTORY_TABLE} {lctn_hstry}
                JOIN claims ON
                    {lctn_hstry}.geo_bene_sk = claims.geo_bene_sk AND
                    {lctn_hstry}.clm_type_cd = claims.clm_type_cd AND
                    {lctn_hstry}.clm_dt_sgntr_sk = claims.clm_dt_sgntr_sk AND
                    {lctn_hstry}.clm_num_sk = claims.clm_num_sk
                GROUP BY
                    claims.geo_bene_sk,
                    claims.clm_type_cd,
                    claims.clm_dt_sgntr_sk,
                    claims.clm_num_sk
            ),
            claim_occurrence_spans_dates AS {not_materialized}
                ({claim_occurrence_cte()}),
            claim_related_occurrences_dates AS {not_materialized}
                ({claim_related_occurrences_cte()})
            SELECT {{COLUMNS}}
            FROM claims c
            JOIN {IDR_CLAIM_TABLE} {clm} ON
                {clm}.geo_bene_sk = c.geo_bene_sk AND
                {clm}.clm_dt_sgntr_sk = c.clm_dt_sgntr_sk AND
                {clm}.clm_type_cd = c.clm_type_cd AND
                {clm}.clm_num_sk = c.clm_num_sk
            JOIN {IDR_CLAIM_DATE_SIGNATURE_TABLE} {sgntr} ON
                {sgntr}.clm_dt_sgntr_sk = {clm}.clm_dt_sgntr_sk
            LEFT JOIN {IDR_CLAIM_PROFESSIONAL_TABLE} {prfnl} ON
                {clm}.geo_bene_sk = {prfnl}.geo_bene_sk AND
                {clm}.clm_dt_sgntr_sk = {prfnl}.clm_dt_sgntr_sk AND
                {clm}.clm_type_cd = {prfnl}.clm_type_cd AND
                {clm}.clm_num_sk = {prfnl}.clm_num_sk
            LEFT JOIN latest_clm_lctn_hstry latest_lctn ON
                {clm}.geo_bene_sk = latest_lctn.geo_bene_sk AND
                {clm}.clm_type_cd = latest_lctn.clm_type_cd AND
                {clm}.clm_dt_sgntr_sk = latest_lctn.clm_dt_sgntr_sk AND
                {clm}.clm_num_sk = latest_lctn.clm_num_sk
            LEFT JOIN {IDR_CLAIM_LOCATION_HISTORY_TABLE} {lctn_hstry} ON
                {clm}.geo_bene_sk = {lctn_hstry}.geo_bene_sk AND
                {clm}.clm_type_cd = {lctn_hstry}.clm_type_cd AND
                {clm}.clm_dt_sgntr_sk = {lctn_hstry}.clm_dt_sgntr_sk AND
                {clm}.clm_num_sk = {lctn_hstry}.clm_num_sk AND
                {lctn_hstry}.clm_lctn_cd_sqnc_num = latest_lctn.max_clm_lctn_cd_sqnc_num
            LEFT JOIN {IDR_PROVIDER_HISTORY_TABLE} {prvdr_blg}
                ON {prvdr_blg}.prvdr_npi_num = {clm}.prvdr_blg_prvdr_npi_num
                AND {prvdr_blg}.prvdr_hstry_obslt_dt >= '{DEFAULT_MAX_DATE}'
            LEFT JOIN {IDR_PROVIDER_HISTORY_TABLE} {prvdr_rfrg}
                ON {prvdr_rfrg}.prvdr_npi_num = {clm}.prvdr_rfrg_prvdr_npi_num
                AND {prvdr_rfrg}.prvdr_hstry_obslt_dt >= '{DEFAULT_MAX_DATE}'
            LEFT JOIN {IDR_PROVIDER_HISTORY_TABLE} {prvdr_othr}
                ON {prvdr_othr}.prvdr_npi_num = {clm}.prvdr_othr_prvdr_npi_num
                AND {prvdr_othr}.prvdr_hstry_obslt_dt >= '{DEFAULT_MAX_DATE}'
            LEFT JOIN claim_occurrence_spans_dates {ocrnc_sgntr_dd}
                ON {ocrnc_sgntr_dd}.clm_ocrnc_sgntr_sk = {clm}.clm_ocrnc_sgntr_sk
            LEFT JOIN claim_related_occurrences_dates {rlt_ocrnc_sgntr_dd}
                ON {rlt_ocrnc_sgntr_dd}.clm_rlt_ocrnc_sgntr_sk = {clm}.clm_rlt_ocrnc_sgntr_sk
            {{WHERE_CLAUSE}} AND {base_claim_filter(partition)}
            {{ORDER_BY}}
            {{LIMIT}}
        """