"""SQLAlchemy model and lookup for the product_display_artists table."""

from typing import Any

from sqlalchemy import Enum, select
from sqlalchemy.dialects.mysql import INTEGER
from sqlalchemy.orm import Mapped, Session, mapped_column

from contributor.connectors.mysql import BaseModel, db_session_wrap
from contributor.queries.mysql.contributor import Contributor


class ProductDisplayArtist(BaseModel):
    """Product display artist model."""

    __tablename__ = "product_display_artists"

    id: Mapped[int] = mapped_column(
        INTEGER(unsigned=True),
        primary_key=True,
        autoincrement=True,
    )
    product_id: Mapped[int] = mapped_column(INTEGER(unsigned=True), nullable=False)
    contributor_id: Mapped[int] = mapped_column(INTEGER(unsigned=True), nullable=False)
    display_artist_role: Mapped[str] = mapped_column(
        Enum(
            "featuring",
            "primary",
            "feature_to_primary",
            name="display_artist_role",
        ),
        nullable=False,
    )
    sequence_number: Mapped[int] = mapped_column(
        INTEGER(unsigned=True), nullable=False, default=0
    )


@db_session_wrap
def get_by_product_ids(
    session: Session, *, product_ids: list[int]
) -> list[dict[str, Any]]:
    result = session.execute(
        select(
            ProductDisplayArtist.product_id,
            ProductDisplayArtist.display_artist_role,
            ProductDisplayArtist.sequence_number,
            ProductDisplayArtist.id,
            Contributor.neo4j_participant_uuid,
        )
        .join(Contributor, Contributor.id == ProductDisplayArtist.contributor_id)
        .where(ProductDisplayArtist.product_id.in_(product_ids))
        .order_by(
            ProductDisplayArtist.product_id,
            ProductDisplayArtist.sequence_number,
            ProductDisplayArtist.id,
        )
    ).all()

    display_artists_by_product_id: dict[int, list[dict[str, Any]]] = {}
    for product_id, role, _, _, contributor_uuid in result:
        display_artists_by_product_id.setdefault(product_id, []).append(
            {
                "contributor": {"uuid": contributor_uuid},
                "role": role,
            }
        )

    return [
        {
            "product": {"product_id": product_id},
            "display_artists": display_artists_by_product_id.get(product_id, []),
        }
        for product_id in product_ids
        if product_id in display_artists_by_product_id
    ]