"""SQLAlchemy model for the contributors table."""

from datetime import datetime
from typing import Any

from pydantic import UUID4
from sqlalchemy import DateTime, String, select
from sqlalchemy.dialects.mysql import INTEGER
from sqlalchemy.orm import Mapped, Session, mapped_column

from contributor.connectors.mysql import BaseModel, db_session_wrap


class Contributor(BaseModel):
    __tablename__ = "contributors"

    id: Mapped[int] = mapped_column(
        INTEGER(unsigned=True),
        primary_key=True,
        autoincrement=True,
    )
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    vendor_id: Mapped[int] = mapped_column(INTEGER(unsigned=True), nullable=False)
    subaccount_id: Mapped[int] = mapped_column(
        INTEGER(unsigned=True), nullable=False, default=0
    )
    spotify_id: Mapped[str | None] = mapped_column(String(50), nullable=True)
    apple_music_id: Mapped[str | None] = mapped_column(String(50), nullable=True)
    spotify_artist_key: Mapped[str | None] = mapped_column(String(29), nullable=True)
    neo4j_participant_uuid: Mapped[str | None] = mapped_column(
        String(36), nullable=True
    )
    global_participant_uuid: Mapped[str | None] = mapped_column(
        String(36), nullable=True
    )
    artist_info_id: Mapped[int | None] = mapped_column(
        INTEGER(unsigned=True), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)

    def to_dict(self) -> dict[str, Any]:
        """Convert the Contributors instance to a dictionary."""
        return {
            "uuid": self.neo4j_participant_uuid,
            "id": self.id,
            "name": self.name,
            "label": {
                "vendor_id": self.vendor_id,
                "subaccount_id": self.subaccount_id,
            },
            "spotify_id": self.spotify_id,
            "apple_music_id": self.apple_music_id,
            "spotify_artist_key": self.spotify_artist_key,
            "neo4j_participant_uuid": self.neo4j_participant_uuid,
            "global_participant": (
                {"id": self.global_participant_uuid}
                if self.global_participant_uuid
                else None
            ),
            "artist_info_ids": [self.artist_info_id],
            "created_at": self.created_at,
            "updated_at": self.updated_at,
        }


@db_session_wrap
def get(session: Session, *, contributor_id: int) -> dict[str, Any] | None:
    """Get a contributor by ID."""
    result = session.execute(
        select(Contributor).where(Contributor.id == contributor_id)
    ).scalar_one_or_none()
    return result.to_dict() if result else None


@db_session_wrap
def get_by_uuids(
    session: Session, *, contributor_uuids: list[UUID4]
) -> list[dict[str, Any]]:
    """Get contributors by their Neo4j participant UUIDs."""
    results = (
        session.execute(
            select(Contributor).where(
                Contributor.neo4j_participant_uuid.in_(
                    [str(u) for u in contributor_uuids]
                )
            )
        )
        .scalars()
        .all()
    )
    return [result.to_dict() for result in results]