from sqlalchemy import text

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


class ArtRelationsTrackPublisher(Model):
    fullname: str


async def get_track_publishers(upc: int) -> dict[int, list[ArtRelationsTrackPublisher]]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT DISTINCT
                    tp.publisher_name as fullname,
                    tp.unique_track_id as track_id
                FROM track_publisher tp
                INNER JOIN track t ON t.id = tp.unique_track_id
                WHERE t.upc = :upc
                ORDER BY tp.publisher_name
                """
            ),
            {
                "upc": upc,
            },
        )

    track_publishers: dict[int, list[ArtRelationsTrackPublisher]] = {}

    for track_publisher in result.mappings().all():
        track_publishers.setdefault(track_publisher.track_id, []).append(
            ArtRelationsTrackPublisher(
                fullname=track_publisher.fullname,
            )
        )

    return track_publishers