#File system and I/O operations
import os
from io import BytesIO
# Data manipulation 
import pandas as pd
import numpy as np
from IPython.display import Image

# Time functions
import math
import time
from datetime import datetime, timedelta
from collections import Counter
import re


# Data Gathering
import requests
import zipfile

#Encoding and Serialization
import base64
import json

#Debugging
import http
http.client.HTTPConnection.debuglevel = 0

#Visualization Libraries
import plotly.graph_objects as go
import plotly.express as px
import plotly.figure_factory as ff

#len(california_hospital_pricing_data)

# Counting the number of NaNs in each column
california_hospital_pricing_data.isna().sum()[california_hospital_pricing_data.isna().sum() > 0]

# Dropping columns where the missing entries are about 80%` of the length of the dataframe
california_hospital_pricing_data = california_hospital_pricing_data.dropna(axis=1, thresh=int(0.2 * len(california_hospital_pricing_data)))

# convert NaN to Null
california_hospital_pricing_data = california_hospital_pricing_data.replace({np.nan: None})

def make_columns_unique(columns, suffix="_ALT"):
    counts = Counter()
    new_columns = []

    for col in columns:
        if counts[col] == 0:
            # First occurrence, keep as is
            new_columns.append(col)
        else:
            # Duplicate found, add suffix
            new_col = f"{col}{suffix}" if counts[col] == 1 else f"{col}{suffix}{counts[col]}"
            new_columns.append(new_col)
        counts[col] += 1

    return new_columns

# Looking for column namws having a # and newlines
def clean_column(col_name):
    # Remove leading/trailing spaces and newlines
    col = col_name.strip()
    # Replace # with _ first
    col = col.replace('#', '_')
    # Replace any other non-alphanumeric characters (except underscore) with _
    col = re.sub(r'\W+', '_', col)
    # Collapse multiple underscores
    col = re.sub(r'_+', '_', col)
    col = col.strip('_')
    return col.upper()

# Applying to all column headers
california_hospital_pricing_data.columns = [
    clean_column(col) for col in california_hospital_pricing_data.columns
]

california_hospital_pricing_data.columns = make_columns_unique(california_hospital_pricing_data.columns)

# Convert start/end date columns
for col in ['BEG_DATE', 'END_DATE']:
    # Convert to datetime, coerce errors to NaT
    california_hospital_pricing_data[col] = pd.to_datetime(
        california_hospital_pricing_data[col], errors='coerce'
    )

# Replace NaT with None so GridDB sees it as null
california_hospital_pricing_data[col] = california_hospital_pricing_data[col].where(
    california_hospital_pricing_data[col].notna(), None
)

"""
important_columns = [
'FAC_NO','FAC_NAME','COUNTY','HSA','HFPA','TYPE_CNTRL','TYPE_CARE','TYPE_HOSP','TEACH_RURL',
'BEG_DATE','END_DATE','DAY_PER','BED_LIC','BED_AVL','BED_STF','BED_ACUTE','BED_PSYCH','BED_REHAB','BED_LTC',
'DAY_TOT','DIS_TOT','DAY_ACUTE','DIS_ACUTE','VIS_TOT','VIS_ER','SURG_IP','SURG_OP','C_SECTIONS',
'OCC_LIC','OCC_AVL','ALOS_ALL','ALOS_EXLTC',
'GR_PT_REV','NET_PT_REV','TOT_OP_EXP','NET_FRM_OP','NET_INCOME','NONOP_REV','NONOP_EXP',
'TOT_ASST','CUR_ASST','CUR_LIAB','NET_LTDEBT','EQUITY',
'HOSP_FTE','NURS_FTE','PROD_HRS','PAID_HRS','MED_STAFF','STDNT_FTE'
]
"""

#california_hospital_pricing_subset = california_hospital_pricing_data[important_columns]

username = "XXX" # Provide your GridDB credentials here
password =   "XXX"
credentials = f"{username}:{password}"
encoded_credentials = base64.b64encode(credentials.encode()).decode()
#print(f"Encoded credentials: Basic {encoded_credentials}")

#Construct an object to hold the request headers (ensure that you replace the XXX placeholder with the correct value that matches the credentials for your GridDB instance)
#header_obj = {"Authorization":"Basic {EncodedAuthorizationToken}","Content-Type":"application/json; charset=UTF-8","User-Agent":"PostmanRuntime/7.29.0"}

header_obj = {
    "Authorization": f"Basic {encoded_credentials}",  # Add encoded credentials here
    "Content-Type": "application/json", 
    "charset": "UTF-8",
    "User-Agent":"PostmanRuntime/7.29.0"
}

#Construct the base URL based on your GridDB cluster you'd like to connect to (ensure that you replace the placeholders in the URL below with the correct values that correspond to your GridDB instance)
base_url = 'https://[host]:[port]/griddb/v2/[clustername]/dbs/[database_name]/'

INT32_MIN = -2147483648
INT32_MAX = 2147483647

def map_dtype_to_griddb(series):
    """
    Maps Pandas series to GridDB data types.
    Detects INTEGER overflow and upgrades to LONG.
    """

    if pd.api.types.is_integer_dtype(series):

        # Check integer overflow
        if series.dropna().between(INT32_MIN, INT32_MAX).all():
            return "INTEGER"
        else:
            return "LONG"

    elif pd.api.types.is_float_dtype(series):
        return "DOUBLE"

    elif pd.api.types.is_bool_dtype(series):
        return "BOOL"

    elif pd.api.types.is_datetime64_any_dtype(series):
        return "TIMESTAMP"

    else:
        return "STRING"

def generate_griddb_data_obj(df, container_name="MyContainer", container_type="COLLECTION", rowkey=False):
    columns = []
    for col in df.columns:
        griddb_type = map_dtype_to_griddb(df[col])
        columns.append({"name": col, "type": griddb_type})
    
    data_obj = {
        "container_name": container_name,
        "container_type": container_type,
        "rowkey": rowkey,
        "columns": columns
    }
    
    return data_obj

#Set up the GridDB WebAPI URL
url = base_url + 'containers'

#Invoke the GridDB WebAPI with the headers and the request body
x = requests.post(url, json = data_obj, headers = header_obj)
#x.text

#Setup the URL to be used to invoke the GridDB WebAPI to register rows in the container created previously
url = base_url + 'containers/hospital_pricing_data/rows'
print(url)

batch_size = 5000 
total_rows = len(california_hospital_pricing_data)
total_batches = math.ceil(total_rows / batch_size)

print(f"Starting ingestion of {total_rows:,} rows in {total_batches} batches of {batch_size} each...")
start_time = time.time()

for i in range(total_batches):
    start_idx = i * batch_size
    end_idx = min((i + 1) * batch_size, total_rows)
    batch_df = california_hospital_pricing_data.iloc[start_idx:end_idx]
    batch_json = batch_df.to_json(orient='values')
    #print(batch_json)
    #break
    try:
        print(url)
        response = requests.put(url, headers=header_obj, data=batch_json)
        elapsed = time.time() - start_time
        #print(batch_json)
        if response.status_code in [200, 201]:
            print(f" Batch {i+1}/{total_batches} | Rows {start_idx}-{end_idx-1} | "
                  f"Time elapsed: {elapsed:.1f}s")
        else:
            print(f" Batch {i+1}/{total_batches} FAILED "
                  f"(HTTP {response.status_code}) - {response.text[:150]}")

    except Exception as e:
        print(f" Batch {i+1}/{total_batches} ERROR: {e}")
print(f"\n Ingestion completed in {time.time() - start_time:.1f} seconds.")

sql_query1 = """
                SELECT DISTINCT EXTRACT(YEAR, TIMESTAMP(beg_date)) AS Cal_Year,
                (
                    SELECT PERCENTILE_CONT(0.9) 
                    WITHIN GROUP (ORDER BY NET_PT_REV)
                    FROM hospital_pricing_data
                    WHERE EXTRACT(YEAR,TIMESTAMP(beg_date)) = EXTRACT(YEAR, TIMESTAMP(hosp_data.beg_date))
                ) Percentile_Value
                FROM 
                hospital_pricing_data hosp_data;
                """

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query1
    }
])

# Disable HTTP debugging
http.client.HTTPConnection.debuglevel = 0

#Invoke the GridDB WebAPI
data_req1 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req1.json()
top_10_perc_hosp_rev = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"]])

top_10_perc_hosp_rev['Percentile_Value'] = top_10_perc_hosp_rev['Percentile_Value'].apply(lambda x: f"{x/1e6:.1f} M")
top_10_perc_hosp_rev = top_10_perc_hosp_rev.sort_values(by="Percentile_Value", ascending=False)

sql_query2 = """SELECT COUNTY, COUNT(*) AS hospital_count
                FROM hospital_pricing_data
                WHERE EXTRACT(YEAR, TIMESTAMP(beg_date)) = 2024
                GROUP BY COUNTY
                HAVING COUNT(*) >
                (
                    SELECT AVG(hospital_count)
                    FROM (
                        SELECT subset.COUNTY, COUNT(*) AS hospital_count
                        FROM hospital_pricing_data subset
                        WHERE EXTRACT(YEAR, TIMESTAMP(subset.beg_date)) = 2024
                        GROUP BY subset.COUNTY
                    ) t
                )
                ORDER BY hospital_count DESC;
                """

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query2
    }
])

# Disable HTTP debugging
http.client.HTTPConnection.debuglevel = 0

#Invoke the GridDB WebAPI
data_req2 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req2.json()
counties_hosp_more_avg = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"]])

sql_query3 = """SELECT FAC_NAME, EXTRACT(YEAR, TIMESTAMP(beg_date)) As Cal_Year, AVG(SURG_IP + SURG_OP) AS total_surgeries
                FROM hospital_pricing_data
                WHERE (SURG_IP + SURG_OP) >
                (
                SELECT MEDIAN(SURG_IP + SURG_OP) as average_surg
                FROM hospital_pricing_data
                )
                GROUP BY 1,2
                ORDER BY 3 DESC
                LIMIT 10
                """

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query3
    }
])

# Disable HTTP debugging
http.client.HTTPConnection.debuglevel = 0

#Invoke the GridDB WebAPI
data_req3 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req3.json()
high_vol_surgical_centers = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"], myJson[0]["columns"][2]["name"]])
high_vol_surgical_center_sorted = high_vol_surgical_centers.sort_values(by='total_surgeries', ascending=False)

We calculate the revenue per bed by dividing the Gross Patient Revenue by the number of licensed beds.

sql_query4 = """
            SELECT FAC_NAME, EXTRACT(YEAR, TIMESTAMP(beg_date)) As Cal_Year, AVG(GR_PT_REV / BED_LIC) AS revenue_per_bed
            FROM hospital_pricing_data
            WHERE BED_LIC > 0
              AND (GR_PT_REV / BED_LIC) > (
                  SELECT AVG(GR_PT_REV / BED_LIC)
                  FROM hospital_pricing_data
                  WHERE BED_LIC > 0
              )
              GROUP BY 1,2
              ORDER BY 3 DESC
              LIMIT 10;
            """

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query4
    }
])

# Disable HTTP debugging
http.client.HTTPConnection.debuglevel = 0

#Invoke the GridDB WebAPI
data_req4 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req4.json()
hosp_rev_per_bed = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"], myJson[0]["columns"][2]["name"]])
hosp_rev_per_bed_sorted = hosp_rev_per_bed.sort_values(by='revenue_per_bed', ascending=False)
hosp_rev_per_bed_sorted['revenue_per_bed'] = hosp_rev_per_bed_sorted['revenue_per_bed'].apply(lambda x: f"${x:,.0f}")

fig = ff.create_table(hosp_rev_per_bed_sorted)
fig.show()

sql_query5 = """SELECT FAC_NAME, EXTRACT(YEAR, TIMESTAMP(beg_date)) AS Calendar_Year, 
                AVG(ALOS_ALL) AS "Average_Stay_Length"
                FROM hospital_pricing_data
                GROUP BY 1,2
                HAVING AVG(ALOS_ALL) > 0
                ORDER BY 3 DESC
                LIMIT 10"""

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query5
    }
])

# Disable HTTP debugging
http.client.HTTPConnection.debuglevel = 0

#Invoke the GridDB WebAPI
data_req5 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req5.json()
alos_all_df = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"], myJson[0]["columns"][2]["name"]])
alos_all_df = alos_all_df.sort_values(by='Average_Stay_Length', ascending=False)