"""Util functions and helpers related to connecting to Snowflake."""

from os import path
from pathlib import Path

from snowflake import connector

from .. import config

_connection = None


def _get_cursor():
    """Get a connection to Snowflake.

    Returns:
        snowflake.connector.connection: Snowflake connection object
    """
    global _connection
    if _connection is None:
        _connection = connector.connect(**config.SNOWFLAKE_DB_CONFIG)
    return _connection.cursor()


def close_connection():
    """Close the Snowflake connection."""
    global _connection
    if _connection is not None:
        _connection.close()
        _connection = None


def create_splits_table():
    _get_cursor().execute(
        # Order of columns must match RDS splits query
        """
            CREATE TEMPORARY TABLE {temp_split} (
                splitid INTEGER,
                splittypeid INTEGER,
                tuid VARCHAR(255),
                splitrate FLOAT,
                collaboratorid INTEGER,
                ratetype VARCHAR(5) DEFAULT 'NET'
            )
        """.format(**config.SNOWFLAKE_OBJECTS),
    )


def create_collaborators_table():
    _get_cursor().execute(
        # Order of columns must match RDS collaborators query
        """
            CREATE TEMPORARY TABLE {temp_collaborator} (
                id INTEGER,
                performance_rights BOOLEAN
            )
        """.format(**config.SNOWFLAKE_OBJECTS),
    )


def upload_file_to_table_stage(file_path, table_name):
    _get_cursor().execute(f"PUT file://{file_path} @%{table_name}")


def copy_into_table_from_stage(table_name):
    _get_cursor().execute(f"COPY INTO {table_name} FROM @%{table_name}")


def get_track_tuids_by_subaccount(subaccount_ids: list) -> dict:
    if not subaccount_ids:
        return {}
    cursor = _get_cursor()
    subaccount_placeholders = ", ".join(["%s"] * len(subaccount_ids))
    query = (
        "SELECT dr.subaccountid, dt.track_unique_id"
        " FROM {dim_release} dr"
        " JOIN {dim_track} dt ON dr.releaseid = dt.upc"
        " WHERE dt.track_unique_id != 0 AND dt.track_unique_id IS NOT NULL"
        " AND dr.subaccountid IN ({subaccount_placeholders})"
    ).format(**config.SNOWFLAKE_OBJECTS, subaccount_placeholders=subaccount_placeholders)
    cursor.execute(query, subaccount_ids)
    result: dict[str, list] = {}
    for subaccount_id, tuid in cursor.fetchall():
        result.setdefault(str(subaccount_id), []).append(tuid)
    return result


def trigger_reports_query_async(
    report_run_uuid,
    period_ids,
):
    query_file_path = path.join(path.dirname(__file__), "queries", "report.sql")
    query = Path(query_file_path).read_text()

    cursor = _get_cursor()
    cursor.execute_async(
        query.format(
            report_run_uuid=report_run_uuid,
            **config.SNOWFLAKE_OBJECTS,
        ),
        {
            "period_ids": period_ids,
            "exclude_transaction_types": config.TRANSACTION_TYPES_PERFORMANCE_RIGHTS,
        },
    )
    return cursor.sfqid