from sqlalchemy import text

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


class ArtRelationsTrackWriter(Model):
    fullname: str


async def get_track_writers(
    upc: int,
) -> dict[int, list[ArtRelationsTrackWriter]]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT DISTINCT
                    tw.writer_name AS fullname,
                    t.id AS track_id
                FROM track_writer tw
                INNER JOIN track t ON t.id = tw.unique_track_id
                WHERE t.upc = :upc
                """
            ),
            {
                "upc": upc,
            },
        )

    track_writers: dict[int, list[ArtRelationsTrackWriter]] = {}

    for track_writer in result.mappings().all():
        track_writers.setdefault(track_writer.track_id, []).append(
            ArtRelationsTrackWriter(
                fullname=track_writer.fullname,
            )
        )

    return track_writers