"""Model for Orchard Datasource.

Fetches data from Orchard's art_relations database.
"""

from typing import Any

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

QUERY_PARAM_CHAR = "%s"

QUERY_ISRC = """SELECT t.id, t.isrc, r.display_upc
    FROM track t INNER JOIN releases r ON r.release_id=t.release_id
    WHERE r.release_id=%s"""

QUERY_RELEASE = """SELECT r.release_id, r.display_upc, rg.grid, rg.product_no,
    r.release_name, '', '', '', ra.artist_name, rg.gras_status, df.context_type
    FROM releases r
    INNER JOIN distribution_format df
    ON df.distribution_format_id=r.distribution_format_id
    LEFT JOIN release_artist ra ON ra.upc=r.upc AND ra.role='performer'
    LEFT JOIN release_grid rg ON rg.release_id=r.release_id
    WHERE r.release_id=%s"""

QUERY_TRACK = """SELECT t.id, r.display_upc, t.isrc, t.track_name, t.version,
    t.p_line, t.track_type, (t.length_minute * 60) + t.length_seconds, ta.name,
    ta.type, mg.grid, mg.product_no FROM track t
    INNER JOIN releases r ON r.release_id=t.release_id
    LEFT JOIN track_artist ta ON ta.track_id=t.id AND ta.type='performer'
    LEFT JOIN (SELECT * FROM master_grid WHERE isrc=%s ORDER BY
    create_time DESC LIMIT 1) mg ON mg.isrc=t.isrc WHERE t.id=%s
    GROUP BY ta.name, ta.type"""

QUERY_RELEASE_SAVE = """INSERT IGNORE INTO release_grid (release_id, grid,
    product_no) VALUES (%s, %s, %s)"""

QUERY_TRACK_SAVE = """INSERT IGNORE INTO master_grid (id, isrc, grid,
    product_no) VALUES (%s, %s, %s, %s)"""


QUERY_UPC = """SELECT release_id, display_upc FROM releases
    WHERE release_id=%s"""


def lookup_release_identifiers(release_id: int) -> ReleaseIdMeta:
    """Retrieve identifier info for a release id.

    Args:
        release_id (int): unique id of release.

    Returns:
        ReleaseIdMeta: item containing identifiers for release.
    """
    result = mysql.execute_query(QUERY_UPC, (release_id,))
    return metadata.ReleaseIdMeta(result[0][0], result[0][1])


def lookup_track_list(id_meta: ReleaseIdMeta) -> list[TrackIdMeta]:
    """Retrieve a list of ISRCs associated with provided UPC.

    Args:
        id_meta (ReleaseIdMeta): identifier info for lookup.

    Returns:
        list[TrackIdMeta]: list of ISRCs.
    """
    result = mysql.execute_query(QUERY_ISRC, (id_meta.release_id,))
    track_result = []
    if result:
        for row in result:
            if row[1]:
                track_result.append(metadata.TrackIdMeta(row[0], row[1], row[2]))
    return track_result


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

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

    Returns:
        Response: release metadata.
    """
    result = mysql.execute_query(QUERY_RELEASE, (id_meta.release_id,))
    if result:
        release_result = result[0]
        return ReleaseMeta(
            release_result[0],
            release_result[1],
            release_result[2],
            release_result[3],
            release_result[4],
            release_result[5],
            release_result[6],
            release_result[7],
            release_result[8],
            release_result[9],
            release_result[10],
        )
    else:
        raise ReleaseNotFound(f"Release {id_meta.release_id} not found")


def lookup_track(id_meta: TrackIdMeta) -> TrackMeta:
    """Retrieve the track metadata associated with provided UPC and ISRC.

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

    Returns:
        TrackMeta: track metadata.
    """
    result = mysql.execute_query(QUERY_TRACK, (id_meta.isrc, id_meta.track_id))
    if result:
        track_result = result[0]
        track_artists = set()
        for temp_result in result:
            track_artists.add(metadata.ArtistMeta(temp_result[8], temp_result[9]))

        return TrackMeta(
            track_result[0],
            track_result[1],
            track_result[2],
            track_result[3],
            track_result[4],
            track_result[5],
            "audio" if track_result[6] == "music" else track_result[6],
            track_result[7],
            track_artists,
            {metadata.GridMeta(track_result[10], track_result[11])},
        )
    else:
        raise TrackNotFound(f"Track {id_meta.isrc} not found")


def save_release(id_meta: ReleaseIdMeta, grid_meta: GridMeta) -> dict[str, Any]:
    """Save GRid and product number of release with provided UPC.

    Args:
        id_meta (ReleaseIdMeta): identifier info of release
        grid_meta: GRid info to save for release.

    Returns:
        dict: message containing save data upon successful save.
    """
    mysql.execute_query(
        QUERY_RELEASE_SAVE, (id_meta.release_id, grid_meta.grid, grid_meta.product_no)
    )

    return {
        "id": id_meta.release_id,
        "grid": grid_meta.grid,
        "product_number": grid_meta.product_no,
    }


def save_track(id_meta: TrackIdMeta, grid_meta: GridMeta) -> dict[str, Any]:
    """Save GRid & product number of track with provided track_id and ISRC.

    Args:
        id_meta (TrackIdMeta): identifier info of track.
        grid_meta (GridMeta): GRid info to save for track.

    Returns:
        dict: message containing save data upon successful save.
    """
    mysql.execute_query(
        QUERY_TRACK_SAVE,
        (id_meta.track_id, id_meta.isrc, grid_meta.grid, grid_meta.product_no),
    )

    return {
        "id": id_meta.track_id,
        "isrc": id_meta.isrc,
        "grid": grid_meta.grid,
        "product_number": grid_meta.product_no,
    }