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 NoArtistForRelease


async def get_release_artists(release_id: 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 ra.release_id, ra.artist_name
                    FROM release_artist ra
                    WHERE ra.release_id = :release_id
                        AND ra.role = 'feature_to_primary'
                        AND ra.artist_name != ''
                    GROUP BY 1, 2
                )
                SELECT ra.artist_name as fullname, LOWER(ra.role) AS artist_role,
                    CASE WHEN f2p.artist_name IS NOT NULL THEN 1
                    ELSE 0
                    END AS is_feature_to_primary, ra.release_artist_id
                FROM release_artist ra
                LEFT JOIN feature_to_primary f2p ON f2p.release_id = ra.release_id
                    AND ra.artist_name = f2p.artist_name
                    AND ra.role = 'featuring'
                WHERE ra.release_id = :release_id
                    AND ra.role IN :participant_roles
                    AND ra.artist_name != ''
                ORDER BY CASE WHEN ra.role = 'performer' THEN 0
                    WHEN ra.role = 'featuring' THEN 1
                    WHEN ra.role = 'remixer' THEN 2
                    WHEN ra.role = 'producer' THEN 3
                    ELSE 4 END, ra.release_artist_id
                """
            ).bindparams(bindparam("participant_roles", expanding=True)),
            {
                "release_id": release_id,
                "participant_roles": [role.value for role in ParticipantRole],
            },
        )
    release_artists = [
        ArtRelationsArtist(
            fullname=release_result.fullname,
            role=ParticipantRole(release_result.artist_role),
            is_feature_to_primary=release_result.is_feature_to_primary,
        )
        for release_result in result.mappings().all()
    ]
    if not release_artists:
        raise NoArtistForRelease(release_id)
    return release_artists