"""SQLAlchemy model for the contributor_roles table."""

from typing import Any

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

from contributor.connectors.mysql import BaseModel


class ContributorRole(BaseModel):
    """Contributor role model."""

    __tablename__ = "contributor_roles"

    id: Mapped[int] = mapped_column(
        INTEGER(unsigned=True),
        primary_key=True,
        autoincrement=True,
    )
    uuid: Mapped[str] = mapped_column(String(36), nullable=False, unique=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False, unique=True)
    ddex_name: Mapped[str | None] = mapped_column(String(100), nullable=True)
    apple_name: Mapped[str | None] = mapped_column(String(100), nullable=True)
    contributor_role_category_id: Mapped[int] = mapped_column(
        INTEGER(unsigned=True),
        nullable=False,
    )

    def to_dict(self) -> dict[str, Any]:
        return {
            "id": self.id,
            "uuid": self.uuid,
            "name": self.name,
            "ddex_name": self.ddex_name,
            "apple_name": self.apple_name,
            "contributor_role_category_id": self.contributor_role_category_id,
        }


@tracer.wrap()
def get_roles(session: Session) -> list[dict[str, Any]]:
    """Get all contributor roles."""
    results = session.query(ContributorRole).all()
    return [result.to_dict() for result in results]


def _to_dict_with_category(
    role: ContributorRole,
    category: ContributorRoleCategory,
) -> dict[str, Any]:
    return {
        "uuid": role.uuid,
        "name": role.name,
        "ddex_name": role.ddex_name,
        "category": {
            "uuid": category.uuid,
            "name": category.name,
        },
    }


@tracer.wrap()
def get_role_by_uuid(
    session: Session,
    uuid: str,
) -> dict[str, Any] | None:
    stmt = (
        select(ContributorRole, ContributorRoleCategory)
        .join(
            ContributorRoleCategory,
            ContributorRole.contributor_role_category_id == ContributorRoleCategory.id,
        )
        .where(ContributorRole.uuid == uuid)
    )
    record = session.execute(stmt).first()
    if not record:
        return None
    role, category = record
    return _to_dict_with_category(role, category)


@tracer.wrap()
def get_roles_by_category_uuid(
    session: Session,
    uuid: str,
) -> list[dict[str, Any]]:
    stmt = (
        select(ContributorRole, ContributorRoleCategory)
        .join(
            ContributorRoleCategory,
            ContributorRole.contributor_role_category_id == ContributorRoleCategory.id,
        )
        .where(ContributorRoleCategory.uuid == uuid)
    )
    records = session.execute(stmt).all()
    return [_to_dict_with_category(role, category) for role, category in records]


@tracer.wrap()
def get_roles_by_category_uuids(
    session: Session,
    uuids: list[str],
) -> list[dict[str, Any]]:
    if not uuids:
        return []

    stmt = (
        select(ContributorRole, ContributorRoleCategory)
        .join(
            ContributorRoleCategory,
            ContributorRole.contributor_role_category_id == ContributorRoleCategory.id,
        )
        .where(ContributorRoleCategory.uuid.in_(uuids))
    )
    records = session.execute(stmt).all()

    grouped_roles: dict[str, list[dict[str, Any]]] = {uuid: [] for uuid in uuids}
    for role, category in records:
        grouped_roles.setdefault(category.uuid, []).append(
            _to_dict_with_category(role, category)
        )

    return [
        {
            "category_uuid": category_uuid,
            "roles": grouped_roles[category_uuid],
        }
        for category_uuid in uuids
    ]


class ContributorRoleCategory(BaseModel):
    """Contributor role category model."""

    __tablename__ = "contributor_role_categories"

    id: Mapped[int] = mapped_column(
        INTEGER(unsigned=True),
        primary_key=True,
        autoincrement=True,
    )
    uuid: Mapped[str] = mapped_column(String(36), nullable=False, unique=True)
    name: Mapped[str] = mapped_column(String(50), nullable=False, unique=True)

    def to_dict(self) -> dict[str, Any]:
        return {"id": self.id, "uuid": self.uuid, "name": self.name}


@tracer.wrap()
def get_role_categories(session: Session) -> list[dict[str, Any]]:
    """Get all contributor role categories."""
    results = session.query(ContributorRoleCategory).all()
    return [result.to_dict() for result in results]