from sqlalchemy import text

from delivery_metadata.api.app import app
from delivery_metadata.models.schemas import ParticipantFullnameLocalization


async def get_track_artist_localized_metadata(
    product_id: int,
) -> frozenset[ParticipantFullnameLocalization]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT
                    ta.name,
                    il.language_code,
                    talm.artist_name as localized_name
                FROM track_artist_localized_metadata talm
                INNER JOIN track_artist ta ON ta.id = talm.track_artist_id
                INNER JOIN track t ON t.id = ta.track_id
                INNER JOIN itunes_languages il ON il.language_id = talm.language_id
                WHERE t.release_id = :product_id;
                """
            ),
            {
                "product_id": product_id,
            },
        )

        return frozenset(
            ParticipantFullnameLocalization(
                fullname=track_artist_localization_result.name,
                language_code=track_artist_localization_result.language_code,
                localized_fullname=track_artist_localization_result.localized_name,
            )
            for track_artist_localization_result in result.mappings().all()
        )