"""Logic for new_music_friday."""
from typing import Literal
from uuid import UUID

from pydantic import BaseModel
from pydantic import Field
from pydantic import TypeAdapter

from charts.connectors import snowflake
from charts.logic import sql_loader
from charts.utils.dict import safe_dict_update
from charts.utils.sql_format import sql_and


class NewMusicFridayDateEntriesFilter(BaseModel):
    isrc: str | None = Field(default=None)
    appears_on_market: str | None = Field(default=None)
    brand_uuid_in: list[UUID] | None = Field(default=None)

    class Config:
        extra = 'forbid'


def new_music_friday_date_entries_filter_to_sql(filter: NewMusicFridayDateEntriesFilter) -> tuple[str, snowflake.SnowflakeParameterType]:
    filter_sqls = []
    filter_params: snowflake.SnowflakeParameterType = {}

    if filter.appears_on_market is not None:
        filter_params['filter_appears_on_market'] = filter.appears_on_market
        filter_sqls.append(
            """
                EXISTS(
                    SELECT 0
                    FROM FACT_CHARTS fc2
                    JOIN DIM_NEW_MUSIC_FRIDAY_CHART dnmfc2
                    ON fc2.chartid = dnmfc2.chartid
                    WHERE
                        dnmfc2.country = :filter_appears_on_market
                    AND fc2.isrc = isrcs.isrc
                    AND fc2.chart_date = :date
                )
            """
        )

    if filter.brand_uuid_in is not None:
        if not filter.brand_uuid_in:
            return 'FALSE', {}
        filter_sqls.append(
            f"""
                EXISTS(
                    SELECT 0
                    FROM dim_track_clean_mv t
                    INNER JOIN dim_release r
                        ON t.upc = r.releaseid
                    INNER JOIN vendor_company_brand_parent_company vcbpc
                        ON t.labelid = vcbpc.vendor_id
                    WHERE
                        t.isrc = isrcs.isrc
                        AND t.offer_type in ('all', 'stream_only', 'album_download_stream', 'track_download_stream')
                        AND r.not_for_distribution IN ('N', 'EditoriallySuspectContent', 'SMEAnalyticsDummy', 'SwitchboardDummy')
                        AND r.product_type = 'digital'
                        AND r.deletions = 'N'
                        AND r.compilation != 'Y'
                        AND vcbpc.company_brand_uuid IN ({', '.join(f"'{uuid}'" for uuid in filter.brand_uuid_in)})
                )
            """
        )

    if filter.isrc is not None:
        filter_params['filter_isrc'] = filter.isrc
        filter_sqls.append('isrcs.isrc = :filter_isrc')

    return sql_and(filter_sqls), filter_params


class NewMusicFridayDateEntriesOrder(BaseModel):
    by: Literal['AVERAGE_POSITION', 'FEATURING', 'TOP_10_RANK', 'POSITION_IN_MARKET']
    dir: Literal['ASC', 'DESC']
    market: str | None = Field(default=None)

    class Config:
        extra = 'forbid'


def new_music_friday_date_entires_order_to_sql(order: NewMusicFridayDateEntriesOrder) -> str:
    match order.by:
        case 'FEATURING':
            return f'feature {order.dir}'
        case 'AVERAGE_POSITION':
            return f'average_position {order.dir}'
        case 'TOP_10_RANK':
            return f'top_10_rank {order.dir}'
        case 'POSITION_IN_MARKET':
            return f"MIN(CASE WHEN fna.country = '{order.market}' THEN position ELSE NULL END) {order.dir} NULLS LAST"


def new_music_friday_date_entires_orders_to_sql(orders: list[NewMusicFridayDateEntriesOrder]) -> str:
    return ', '.join(new_music_friday_date_entires_order_to_sql(order) for order in orders)


newMusicFridayDateEntriesSnowflakeResponseTA = TypeAdapter(list[tuple[int, str, str | None, str | None, str | None, int, float | None, int, str]])


class NewMusicFridayPlacement(BaseModel):
    market: str
    position: int | None
    spotify_id: str


newMusicFridayPlacementListTA = TypeAdapter(list[NewMusicFridayPlacement])


class NewMusicFridayDateEntry(BaseModel):
    isrc: str
    track_name: str | None
    artist_name: str | None
    image_url: str | None
    feature: int
    average_position: float | None
    top_10_rank: int
    placements: list[NewMusicFridayPlacement]


class NewMusicFridayDateEntriesResponse(BaseModel):
    total: int
    entries: list[NewMusicFridayDateEntry]


def new_music_friday_date_entries(
    date: str,
    filter: NewMusicFridayDateEntriesFilter,
    order: list[NewMusicFridayDateEntriesOrder],
    offset: int,
    limit: int,
) -> NewMusicFridayDateEntriesResponse:
    sql = sql_loader.load_query('new_music_friday/by_date/entries')

    filter_sql, filter_params = new_music_friday_date_entries_filter_to_sql(filter)

    sql = sql.replace('{{ filter }}', filter_sql)

    order_sql = new_music_friday_date_entires_orders_to_sql(order)

    sql = sql.replace('{{ order }}', order_sql)

    params: snowflake.SnowflakeParameterType = {
        "date": date,
        "offset": offset,
        "limit": limit,
    }
    safe_dict_update(params, filter_params)
    raw_db_result = snowflake.fetchall_nocache(sql, params)
    parsed_db_result = newMusicFridayDateEntriesSnowflakeResponseTA.validate_python(raw_db_result)

    total = 0
    entries = []
    for row in parsed_db_result:
        total = row[0]
        entries.append(
            NewMusicFridayDateEntry(
                isrc=row[1],
                track_name=row[2],
                artist_name=row[3],
                image_url=row[4],
                feature=row[5],
                average_position=row[6],
                top_10_rank=row[7],
                placements=newMusicFridayPlacementListTA.validate_json(row[8]),
            )
        )

    return NewMusicFridayDateEntriesResponse(
        total=total,
        entries=entries,
    )


newMusicFridayDateMarketsSnowflakeResponseTA = TypeAdapter(list[tuple[str, bool]])


class NewMusicFridayDateMarket(BaseModel):
    market: str
    data_received: bool


class NewMusicFridayDateMarketsResponse(BaseModel):
    markets: list[NewMusicFridayDateMarket]


def new_music_friday_date_markets(date: str) -> NewMusicFridayDateMarketsResponse:
    sql = sql_loader.load_query('new_music_friday/by_date/markets')

    params: snowflake.SnowflakeParameterType = {
        'date': date,
    }

    raw_db_result = snowflake.fetchall_nocache(sql, params)
    parsed_db_result = newMusicFridayDateMarketsSnowflakeResponseTA.validate_python(raw_db_result)

    return NewMusicFridayDateMarketsResponse(
        markets=[
            NewMusicFridayDateMarket(
                market=market,
                data_received=data_received,
            )
            for (market, data_received)
            in parsed_db_result
        ]
    )