from sqlalchemy import text

from delivery_metadata.api.app import app
from delivery_metadata.models import Model


class ArtRelationsTrackLocalizedMetadata(Model):
    track_id: int
    language_code: str
    localized_track_name: str | None
    localized_track_version: str | None


async def get_track_localized_metadata(
    product_id: int,
) -> list[ArtRelationsTrackLocalizedMetadata]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT tl.track_id, i.language_code, tl.track_name, tl.version
                FROM track_localized_metadata tl
                INNER JOIN track t ON t.id = tl.track_id
                INNER JOIN itunes_languages i ON i.language_id = tl.language_id
                WHERE t.release_id = :product_id;
                """
            ),
            {
                "product_id": product_id,
            },
        )

        track_localized_metadata = [
            ArtRelationsTrackLocalizedMetadata(
                track_id=track_localized_result.track_id,
                language_code=track_localized_result.language_code,
                localized_track_name=track_localized_result.track_name,
                localized_track_version=track_localized_result.version,
            )
            for track_localized_result in result.mappings().all()
        ]

        return track_localized_metadata