from sqlalchemy import text

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


class ArtRelationsProductLocalizedMetadata(Model):
    language_code: str
    localized_product_name: str
    localized_product_version: str | None


async def get_product_localized_metadata(
    product_id: int,
) -> list[ArtRelationsProductLocalizedMetadata]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT i.language_code, rlm.release_name, rlm.delivered_version
                FROM release_localized_metadata rlm
                INNER JOIN itunes_languages i ON i.language_id = rlm.language_id
                WHERE release_id = :product_id;
                """
            ),
            {
                "product_id": product_id,
            },
        )

        return [
            ArtRelationsProductLocalizedMetadata(
                language_code=product_localized_result.language_code,
                localized_product_name=product_localized_result.release_name,
                localized_product_version=product_localized_result.delivered_version,
            )
            for product_localized_result in result.mappings().all()
        ]