from sqlalchemy import text

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


async def get_product_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
                    ra.artist_name as name,
                    il.language_code,
                    ralm.artist_name as localized_name
                FROM release_artist_localized_metadata ralm
                INNER JOIN release_artist ra ON ra.release_artist_id = ralm.release_artist_id
                INNER JOIN itunes_languages il ON il.language_id = ralm.language_id
                WHERE ra.release_id = :product_id;
                """
            ),
            {
                "product_id": product_id,
            },
        )

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