v2.2.0

# %%
from IPython import get_ipython
from jinja2 import Template

if get_ipython() is not None:
    get_ipython().run_line_magic("load_ext", "autoreload")
    get_ipython().run_line_magic("autoreload", "2")
from dotenv import load_dotenv
import pandas as pd
from tqds.sql import db, sql

import pandas as pd
import numpy as np

import seaborn as sns
import matplotlib.pyplot as plt
sns.set_theme(style='whitegrid', rc={'figure.figsize': (14, 12)})

pd.options.display.max_columns = None
pd.options.display.max_rows = 100
pd.options.display.max_colwidth = None
pd.set_option('display.float_format', lambda x: '%.3f' % x)
trino_conn = db.TrinoClient().conn
redshift_conn = db.RedshiftClient().conn
query = sql.Query()

# %% [markdown]
"""
Table of Contents:

- [CLD-v2.2.0] blue payer indicator 
- [CLD-v2.2.0] always enforce 10x upper bound for IP accuracy scores (even hospital-reported GC transformations) 
- [CLD-v2.2.0] spines physician groups integration 
- [CLD-v2.2.0] lab code fixes 
- [CLD-v2.2.0] update abridged schema 
- [CLD-v2.2.0] Add Raw and Transformations Logic for New Dosage Fields 
- [CLD-v2.2.0] tighten bounds validation drug scores

Misc:
- [CLD-v2.2.0] payer chunk optimizations 
- [CLD-v2.2.0] roid conflict fix (bigint -> varchar + 256-bit hex) 
- [CLD-v2.2.0] combined main traceability efficiency improvements 
- [CLD-v2.2.0] lookback run filters 
- [CLD-v2.2.0] lab code indicator; fix payer rates pull for labs 
"""

# %% [markdown]
"""
- [CLD-v2.2.0] blue payer indicator 
"""

# %%
blues = pd.read_sql(f"""
SELECT DISTINCT payer_id, payer_name, blue_payer
FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
WHERE canonical_rate IS NOT NULL
LIMIT 100
""", con=trino_conn)

# %% [markdown]
"""
- [CLD-v2.2.0] always enforce 10x upper bound for IP accuracy scores (even hospital-reported GC transformations) 
"""


# %%
pd.read_sql(f"""
SELECT 
    canonical_rate_score,
    min(canonical_rate_percent_of_cbsa_avg_medicare),
    max(canonical_rate_percent_of_cbsa_avg_medicare),
    count(*)
FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
WHERE bill_type = 'Inpatient'
AND canonical_rate_score > 1
GROUP BY 1
""", con=trino_conn)


# %% [markdown]
"""
- [CLD-v2.2.0] lab code fixes 
"""

# %%
pd.read_sql(f"""
SELECT provider_type, count(distinct billing_code) as n_codes
FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
WHERE billing_code in (
    SELECT billing_code 
    FROM tq_dev.internal_dev_sjones.lab_template_codes_09_30_2026
)
AND canonical_rate_score > 1
GROUP BY 1
""", con=trino_conn)


# %% [markdown]
"""
- [CLD-v2.2.0] update abridged schema [reverted]

CHECK THAT REVERSION IS CORRECT
"""

# %%
new = pd.read_sql(f"""
DESCRIBE tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
""", con=trino_conn)
old = pd.read_sql(f"""
DESCRIBE tq_dev.internal_dev_csong_cld_v2_1_4.prod_combined_abridged
""", con=trino_conn)

# %%
new.loc[new['Column'].isin(old['Column'])]

# %%
old.loc[~old['Column'].isin(new['Column'])]

# %% [markdown]
"""
- [CLD-v2.2.0] Add Raw and Transformations Logic for New Dosage Fields 
"""

# %%
# Reviewed in scripts/market_solutions/life_sciences


# %% [markdown]
"""
- [CLD-v2.2.0] tighten bounds validation drug scores
"""
# %%
pd.read_sql(f"""
select 
    roid,
    payer_id,
    payer_network_name, 
    canonical_rate, 
    canonical_rate_source, 
    canonical_rate_type, 
    canonical_contract_methodology, 
    canonical_rate_score, 
    best_payer_rate, 
    best_payer_rate_score, 
    best_hospital_rate, 
    best_hospital_rate_score
from tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
where billing_code = 'J9271'
and provider_name = 'Stanford Hospital - 300 Pasteur Dr'
and payer_name = 'Anthem'
""", con=trino_conn)