from sqlalchemy import bindparam, text

from delivery_metadata.api.app import app
from delivery_metadata.clients.art_relations.schemas import ArtRelationsArtist
from delivery_metadata.constants import ParticipantRole
from delivery_metadata.exceptions import NoArtistForTrack


async def get_track_artists(upc: int) -> dict[int, list[ArtRelationsArtist]]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                WITH feature_to_primary AS (
                    SELECT ta.track_id, ta.name
                    FROM track t
                    JOIN track_artist ta
                        ON t.id = ta.track_id
                        AND ta.type = 'feature_to_primary'
                        AND ta.name != ''
                    WHERE t.upc = :upc
                    GROUP BY 1, 2
                )
                SELECT ta.id as artist_id, ta.name AS fullname, LOWER(ta.type) AS artist_role, t.id AS track_id,
                    CASE
                    WHEN f2p.name IS NOT NULL THEN 1
                    ELSE 0
                    END AS is_feature_to_primary
                FROM track t
                LEFT JOIN track_artist ta
                    ON t.id = ta.track_id
                    AND ta.type IN :participant_roles
                    AND ta.name != ''
                LEFT JOIN feature_to_primary f2p
                    ON f2p.track_id = t.id
                    AND f2p.name = ta.name
                    AND ta.type = 'featuring'
                WHERE t.upc = :upc
                ORDER BY CASE WHEN ta.type = 'performer' THEN 0
                    WHEN ta.type = 'featuring' THEN 1
                    WHEN ta.type = 'remixer' THEN 2
                    WHEN ta.type = 'producer' THEN 3
                    ELSE 4 END, ta.id;
                """
            ).bindparams(bindparam("participant_roles", expanding=True)),
            {
                "upc": upc,
                "participant_roles": [
                    "cast",
                    "composer",
                    "conductor",
                    "ensemble",
                    "featuring",
                    "orchestra",
                    "performer",
                    "producer",
                    "publisher",
                    "remixer",
                    "track_writer",
                    "director",
                    "editor",
                    "videographer",
                ],
            },
        )

    track_artists: dict[int, list[ArtRelationsArtist]] = {}

    for track_result in result.mappings().all():
        if not track_result.artist_role:
            raise NoArtistForTrack(track_result.track_id)
        track_artists.setdefault(track_result.track_id, []).append(
            ArtRelationsArtist(
                fullname=track_result.fullname,
                role=ParticipantRole(track_result.artist_role),
                is_feature_to_primary=track_result.is_feature_to_primary,
            )
        )

    return track_artists