"""Model for Delphi snowflake Datasource.

Fetches data from Delphi's delphi_exploration snowflake database.
"""

from typing import Any

from gridgen.connectors import snowflake_connector
from gridgen.exceptions import ReleaseNotFound, TrackNotFound
from gridgen.models.metadata import (
    GridMeta,
    ReleaseIdMeta,
    ReleaseMeta,
    TrackIdMeta,
    TrackMeta,
)

QUERY_PRODUCT_GRID = """SELECT
    p.barcode,
    p.grid_no,
    p.prod_no,
    p.is_complete
    FROM V_GRAS_PRODUCT AS p
    WHERE p.barcode=%s"""

QUERY_TRACK_GRID = """SELECT
    t.isrc,
    t.track_name,
    t.p_line,
    t.play_time,
    p.grid_no,
    p.prod_no
    FROM V_GRAS_TRACK AS t
    INNER JOIN V_GRAS_PRODUCT_TRACK AS pt
        ON t.TRACK_NO = pt.TRACK_NO AND t.TRACK_EXT = pt.TRACK_EXT
    INNER JOIN V_GRAS_PRODUCT AS p
        ON pt.PROD_NO = p.PROD_NO
    WHERE t.isrc = %s
        AND t.mod_flag <> 'D'
        AND pt.mod_flag <> 'D'
        AND p.mod_flag <> 'D'
        AND t.track_media_key <> 2
    ORDER BY p.grid_no"""


def lookup_product_grid(upc: str) -> tuple[Any, ...]:
    """Retrieve product grid info for a display UPC.

    Args:
        upc (str): display_upc of release.

    Returns:
        dict: result containing product grid info for release.
    """
    result = snowflake_connector.execute_query(QUERY_PRODUCT_GRID, (upc,))
    if result:
        return result[0]  # type:ignore[no-any-return]
    else:
        raise ReleaseNotFound(f"GRAS product {upc} not found")


def lookup_track_grid(isrc: str) -> tuple[Any, ...]:
    """Retrieve track grid info for a ISRC.

    Args:
        isrc (str): Track's ISRC.

    Returns:
        dict: result containing track grid info for ISRC.
    """
    result = snowflake_connector.execute_query(QUERY_TRACK_GRID, (isrc,))
    if result:
        return result[0]  # type:ignore[no-any-return]
    else:
        raise TrackNotFound(f"GRAS track {isrc} not found")


def lookup_release(id_meta: ReleaseIdMeta) -> ReleaseMeta:
    """Retrieve the grid data of a release associated with provided UPC.

    Args:
        id_meta (ReleaseIdMeta): identifier info of release.

    Returns:
        ReleaseMeta: release object from gras.
    """
    release_obj = lookup_product_grid(id_meta.upc)

    if release_obj[3] == "Y":
        gras_status = "complete"
    else:
        gras_status = "incomplete"

    # Return release metadata
    return ReleaseMeta(
        None,
        release_obj[0],
        release_obj[1],
        release_obj[2],
        None,
        None,
        None,
        None,
        None,
        gras_status,
        None,
    )


def lookup_track(id_meta: TrackIdMeta) -> TrackMeta | None:
    """Retrieve the grid data of a track associated with provided ISRC.

    Args:
        id_meta (TrackIdMeta): identifier info of the track

    Returns:
         TrackMeta: track object from Gras
    """
    track_obj = lookup_track_grid(id_meta.isrc)

    # Return track metadata
    return TrackMeta(
        None,
        None,
        track_obj[0],
        track_obj[1],
        "",
        track_obj[2],
        "",
        track_obj[3],
        set(),
        {GridMeta(track_obj[4], track_obj[5])} if track_obj[4] else set(),
    )