"""Product Manager Mapping Product model."""

import contextlib
from typing import Any

from sqlalchemy import Column, ForeignKey, Integer, select
from sqlalchemy.orm import Mapped, Session, mapped_column

from video.connectors import mysql


class ProductManagerMappingProduct(mysql.ArModel):
    """Product Manager Mapping Product model."""

    __tablename__ = "product_manager_mapping_product"

    product_manager_mapping_product_id = Column(
        Integer,
        name="product_manager_mapping_product_id",
        nullable=False,
        primary_key=True,
        autoincrement=True,
    )
    release_id = Column(
        Integer,
        ForeignKey("product_video.release_id"),
        nullable=False,
    )
    product_manager_id: Mapped[int] = mapped_column(
        Integer, name="product_manager_id", nullable=False
    )

    def to_dict(self) -> dict[str, Any]:
        """Get a dict representation."""
        return {
            "product_manager_mapping_product_id": self.product_manager_mapping_product_id,
            "release_id": self.release_id,
            "product_manager_id": self.product_manager_id,
        }


def _get(release_id: int, session: Session) -> ProductManagerMappingProduct | None:
    return session.execute(
        select(ProductManagerMappingProduct).where(
            ProductManagerMappingProduct.release_id == release_id
        )
    ).scalar_one_or_none()


def get_product_manager(release_id: int) -> dict[str, Any]:
    """Get product_manager by release_id.

    Returns:
        dict: product manager data. Returns empty dict if not found.
    """
    with mysql.ar_db_session() as session:
        product_manager = _get(release_id, session)
        if not product_manager:
            return {}
        return product_manager.to_dict()


def upsert(
    release_id: int,
    product_manager_id: int,
    session: Session | None = None,
) -> None:
    """Create or update."""
    ctx = mysql.ar_db_session() if session is None else contextlib.nullcontext(session)
    with ctx as s:
        product_manager = _get(release_id, s)
        if product_manager:
            product_manager.product_manager_id = product_manager_id
        else:
            product_manager = ProductManagerMappingProduct(
                release_id=release_id, product_manager_id=product_manager_id
            )
        s.add(product_manager)
        s.flush()