"""Snowflake DIM_RELEASE_HISTORY update logic for update-dim-tables."""

import snowflake.connector
from snowflake.connector import DictCursor

from src import config
from src.snowflake_connection import execute, get_snowflake_connection


def update_dim_release_history(
    release_ids: list[int],
    destination_vendor_id: int,
    destination_subaccount_id: int | None,
    revenue_cutoff_date: str,
) -> None:
    schema = config.SNOWFLAKE_SCHEMA
    conn = get_snowflake_connection()
    try:
        statement_period_id = _get_statement_period_id(conn, schema, revenue_cutoff_date)
        _close_open_rows(conn, schema, release_ids, revenue_cutoff_date, statement_period_id)
        _insert_new_rows(
            conn,
            schema,
            release_ids,
            destination_vendor_id,
            destination_subaccount_id,
            revenue_cutoff_date,
            statement_period_id,
        )
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def _get_statement_period_id(
    conn: snowflake.connector.SnowflakeConnection,
    schema: str,
    revenue_cutoff_date: str,
) -> int:
    sql = f"""
        SELECT sp.STATEMENT_PERIOD_ID
        FROM ORCHARD_APP_REPORTING_V2.{schema}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD sp
        WHERE sp.STATEMENT_YEAR = YEAR(%s::DATE)
        AND sp.STATEMENT_MONTH = MONTH(%s::DATE)
    """
    cursor: DictCursor = execute(conn, sql, (revenue_cutoff_date, revenue_cutoff_date))
    row = cursor.fetchone()
    if row is None:
        raise RuntimeError(f"No statement period found for revenue_cutoff_date {revenue_cutoff_date!r}")
    return int(row["STATEMENT_PERIOD_ID"])


def _close_open_rows(
    conn: snowflake.connector.SnowflakeConnection,
    schema: str,
    release_ids: list[int],
    revenue_cutoff_date: str,
    statement_period_id: int,
) -> None:
    placeholders = ", ".join(["%s"] * len(release_ids))
    sql = f"""
        UPDATE FACTS.{schema}.DIM_RELEASE_HISTORY
        SET END_DATE_INCLUSIVE = %s::DATE, END_STATEMENT_PERIOD_ID = %s
        WHERE PRODUCT_ID IN ({placeholders})
        AND END_DATE_INCLUSIVE IS NULL
    """
    execute(conn, sql, (revenue_cutoff_date, statement_period_id, *release_ids))


def _insert_new_rows(
    conn: snowflake.connector.SnowflakeConnection,
    schema: str,
    release_ids: list[int],
    destination_vendor_id: int,
    destination_subaccount_id: int | None,
    revenue_cutoff_date: str,
    statement_period_id: int,
) -> None:
    sql = f"""
        INSERT INTO FACTS.{schema}.DIM_RELEASE_HISTORY (
            RELEASEID, PRODUCT_ID, LABELID, SUBACCOUNTID,
            START_DATE_INCLUSIVE, END_DATE_INCLUSIVE,
            START_STATEMENT_PERIOD_ID, END_STATEMENT_PERIOD_ID
        )
        SELECT dr.RELEASEID, %s, %s, %s,
               DATEADD(DAY, 1, %s::DATE), NULL, %s, NULL
        FROM FACTS.{schema}.DIM_RELEASE dr
        WHERE dr.PRODUCT_ID = %s
    """
    for release_id in release_ids:
        execute(
            conn,
            sql,
            (
                release_id,
                destination_vendor_id,
                destination_subaccount_id,
                revenue_cutoff_date,
                statement_period_id + 1,
                release_id,
            ),
        )