"""
Snowflake queries for the audit app not related to the Audit Looks.
"""

from collections import deque
from datetime import date

from ..config import SNOWFLAKE_CREDENTIALS
from ..connectors.snowflake_db import Client as SnowflakeClient
from ..typings import Database, LabelID, LabelName, Schema
from ..utils.sql import placeholders


def sf_client_pool():
    """Create a Snowflake client pool, for reusing Snowflake clients with
    a same database and schema."""

    client_pool: dict[tuple[Database, Schema], SnowflakeClient] = {}

    def _(db: Database, schema: Schema) -> SnowflakeClient:
        """Create or get a Snowflake client from the pool.

        Args:
            db (str): Snowflake database.
            schema (str): Snowflake schema.

        Returns:
            Snowflake: Snowflake client.
        """
        client_key = (db, schema)
        if client_key not in client_pool:
            client_pool[client_key] = SnowflakeClient(
                **SNOWFLAKE_CREDENTIALS,
                database=db,
                schema=schema,
            )
        return client_pool[client_key]

    return _


get_client = sf_client_pool()


def sf_facts():
    """Create a Snowflake client for the FACTS database."""
    return get_client("FACTS", "PROD")


async def get_label_name(label_id: LabelID) -> LabelName | None:
    """Get label name for a label ID.

    Args:
        label_id (LabelID): Label ID.

    Returns:
        The label name or None if label ID is not found.
    """
    sf_facts_client = sf_facts()
    query = "SELECT LABELNAME FROM DIM_LABEL WHERE LABELID = %s"
    result = await sf_facts_client.afetch_one(query, (label_id,))
    return result[0]


async def get_top_label_artists_by_revenue(
    label_id: LabelID,
    stores: list[int] | None = None,
    start_date: date | None = None,
    end_date: date | None = None,
    limit: int | None = 10,
) -> list[dict]:
    """Get top artists by revenue for a label ID.

    Args:
        label_id: Label ID.
        stores: List of store IDs to filter by. Defaults to None.
            E.g. [453,] for YouTube.
        start_date: Start date to filter by. Defaults to None.
        end_date: End date to filter by. Defaults to None.
        limit: Limit of artists to return. Defaults to 10. Max 500.

    Returns:
        list[dict]: List of top artists by revenue.
    """
    stores = set(stores or [])
    limit = min(limit or 1, 500)

    and_date = ""  # Default to no date filter.

    if start_date:
        start_date = start_date.isoformat()
        and_date = "AND DATE_FROM_PARTS(ACTIVITYYEAR, ACTIVITYMONTH, 1) >= %s"
    if end_date:
        end_date = end_date.isoformat()
        and_date = "AND DATE_FROM_PARTS(ACTIVITYYEAR, ACTIVITYMONTH, 1) <= %s"
    if start_date and end_date:
        and_date = (
            "AND DATE_FROM_PARTS(ACTIVITYYEAR, ACTIVITYMONTH, 1) BETWEEN %s AND %s"
        )
    dates = [dt for dt in [start_date, end_date] if dt]

    and_store_id = "AND STOREID IN ({})".format(placeholders(stores)) if stores else ""

    query = f"""
        SELECT
            S.ARTISTID AS id,
            A.ARTISTNAME AS name,
            ROUND(SUM(S.FX_ACTUAL_NET), 2) AS net_revenue
        FROM
            FACTS.PROD.FACT_SALES S
        LEFT JOIN
            FACTS.PROD.DIM_ARTIST A USING(ARTISTID)
        WHERE
            LABELID = %s
            {and_store_id}
            {and_date}
        GROUP BY
            ARTISTID, ARTISTNAME
        ORDER BY
            net_revenue DESC
        LIMIT %s
    """
    params = (label_id, *stores, *dates, limit)

    # Memory efficient transformation of the result set
    # to a list of dicts.
    sf_facts_client = sf_facts()
    results = deque(await sf_facts_client.afetch_all(query, params))
    rows = []
    while results:
        row = results.popleft()
        rows.append(
            {
                "id": row[0],
                "name": row[1],
                "net_revenue": row[2],
            }
        )
    return rows