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

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

from contributor.connectors.mysql import BaseModel
from contributor.queries.mysql.project import Project
from contributor.queries.mysql.release import Release


class ReleaseArtist(BaseModel):
    __tablename__ = "release_artist"

    release_artist_id: Mapped[int] = mapped_column(
        INTEGER(unsigned=True),
        primary_key=True,
        autoincrement=True,
    )
    release_id: Mapped[int] = mapped_column(INTEGER(unsigned=True), nullable=False)
    artist_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    artist_info_id: Mapped[int | None] = mapped_column(
        INTEGER(unsigned=True), nullable=True
    )


def rename_release_artists(
    *,
    session: Session,
    current_artist_name: str,
    vendor_id: int,
    new_artist_name: str,
) -> None:
    """
    Bulk update release artists with a given name + vendor_id to a new name.
    """
    session.execute(
        update(ReleaseArtist)
        .where(
            ReleaseArtist.release_id.in_(
                select(Release.release_id)
                .join(Project, Project.project_id == Release.project_id)
                .where(Project.vendor_id == vendor_id)
                .scalar_subquery()
            )
        )
        .where(ReleaseArtist.artist_name == current_artist_name)
        .values(artist_name=new_artist_name)
    )


def reassign_artist_info(
    *,
    session: Session,
    artist_name: str,
    artist_info_id: int,
    duplicate_artist_info_id: int,
) -> None:
    """
    Reassign release artists from duplicate_artist_info_id to artist_info_id,
    updating the artist name at the same time.
    """
    session.execute(
        update(ReleaseArtist)
        .where(ReleaseArtist.artist_info_id == duplicate_artist_info_id)
        .values(artist_info_id=artist_info_id, artist_name=artist_name)
    )