from os import environ

from dotenv import load_dotenv

from src.connector import execute

with open("sql/load_from_stage.sql", "r") as f:
    LOAD_FROM_STAGE_SQL = f.read()

with open("sql/append_results.sql", "r") as f:
    APPEND_RESULTS_SQL = f.read()

with open("sql/add_model_column.sql", "r") as f:
    ADD_MODEL_COLUMN_SQL = f.read()

with open("sql/create_table.sql", "r") as f:
    CREATE_TABLE_SQL = f.read()

# list of columns that are newly present in the hive result set. Make sure to
# compare the list of headers in the results to the table structure in sql/create_table.sql
# Add new columns in sql/create_table.sql, then add the column names here, and they will be
# automatically added to the existing table in Snowflake and populated with data from the new results.
COLUMNS_TO_ADD = []

if __name__ == "__main__":
    load_dotenv()
    RUN_ID = environ.get("RUN_ID") or None
    if not RUN_ID:
        raise RuntimeError("RUN_ID environment variable is not set.")
    TABLE_NAME = environ.get("TABLE_NAME") or None
    if not TABLE_NAME:
        raise RuntimeError("TABLE_NAME environment variable is not set.")
    if TABLE_NAME.upper().split(".")[-1] == "HIVE_MODEL_RESPONSE_TRACKING":
        raise RuntimeError(
            "TABLE_NAME must be a per-run staging table, not HIVE_MODEL_RESPONSE_TRACKING itself"
            " — the append step would insert the table into itself and duplicate every row."
        )

    # check connection
    result = execute("SELECT 1")

    # create table to load new results into
    execute(CREATE_TABLE_SQL.format(table_name=TABLE_NAME))

    # load new results from stage into the new table
    execute(LOAD_FROM_STAGE_SQL.format(run_id=RUN_ID, table_name=TABLE_NAME))

    # add any new columns to the existing table
    for column_name in COLUMNS_TO_ADD:
        execute(ADD_MODEL_COLUMN_SQL.format(column_name=column_name))

    # append new results to the existing reporting table
    execute(APPEND_RESULTS_SQL.format(table_name=TABLE_NAME))