from sqlalchemy import text
from sqlalchemy.exc import NoResultFound

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


class ArtRelationsProductVideo(Model):
    channel_selection: str | None
    description: str | None
    type_of_video: str
    keywords: str | None
    associated_track_isrc: str | None
    contributors: str | None
    primary_artist_name: str


async def get_product_video(upc: int) -> ArtRelationsProductVideo:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT
                    pv.channel_selection,
                    pv.description,
                    pv.type_of_video,
                    pv.keywords,
                    pv.contributors,
                    ai.name AS primary_artist_name,
                    t.isrc AS associated_track_isrc
                FROM product_video pv
                INNER JOIN artist_info ai ON pv.primary_artist_id = ai.artist_id
                LEFT JOIN track t ON t.id = pv.associated_track_id
                WHERE pv.upc = :upc
                """
            ),
            {
                "upc": upc,
            },
        )

    try:
        product_result = result.mappings().one()

        return ArtRelationsProductVideo(
            channel_selection=product_result.channel_selection,
            description=product_result.description,
            type_of_video=product_result.type_of_video,
            keywords=product_result.keywords,
            associated_track_isrc=product_result.associated_track_isrc,
            contributors=product_result.contributors,
            primary_artist_name=product_result.primary_artist_name,
        )
    except NoResultFound as exc:
        raise VideoProductNotFound(f"No video product found for UPC {upc}") from exc