"""Release table."""

import contextlib
from typing import Any

from flask import g
from sqlalchemy import (
    BigInteger,
    Column,
    Date,
    DateTime,
    Enum,
    ForeignKey,
    Integer,
    String,
    delete,
    select,
    text,
)
from sqlalchemy.orm import Mapped, Session, mapped_column

from video.connectors import mysql
from video.constants import release as release_constants
from video.exceptions import ReleaseNotFound
from video.models.ows import product as ows_product

RESERVE_UPC = """
CALL sp_set_upc_to_used
"""

SP_CLAIM_ISRCS_FOR_USE = "CALL `sp_claim_isrcs_for_use`(1);"


class ArtistInfo(mysql.ArModel):
    """Release ArtistInfo."""

    __tablename__ = "artist_info"
    # only need these two columns for joins
    artist_id = Column(Integer, primary_key=True)
    vendor_id = Column(Integer)
    entered_by = Column(Integer)


class Release(mysql.ArModel):
    """Release Model.

    Represents Release metadata
    """

    __tablename__ = "releases"

    release_id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    artist_id = Column(Integer, ForeignKey("artist_info.artist_id"))
    c_line = Column(String)
    description = Column(String)
    display_upc = Column(String)
    distribution_format_id = Column(Integer)
    genre_id = Column(Integer)
    itunes_previewable: Mapped[str | None] = mapped_column(Enum("yes", "no"))
    label = Column(String)
    manufacturer_upc = Column(String)
    meta_language = Column(String)
    language_id = Column(String)
    preorder_date = Column(Date)
    product_code = Column(String)
    product_type_id = Column(Integer)
    project_id = Column(BigInteger)
    release_date = Column(Date)
    original_release_date = Column(Date)
    release_format = Column("format", String)
    release_name = Column(String)
    release_status: Mapped[str | None] = mapped_column(
        Enum(*release_constants.STATUS_TYPES),
        default=release_constants.ORCHARD_PROCESSING,
    )
    sale_start_date = Column(Date)
    special_instructions = Column(String)
    subaccount_id = Column(Integer)
    upc = Column(BigInteger, nullable=False)
    vendor_catalog_number = Column(String)
    vendor_release_identifier = Column(String)
    version = Column(String(255))
    keywords = Column(String)
    new_release: Mapped[str | None] = mapped_column(
        Enum(release_constants.NEW_RELEASE_NEW, release_constants.NEW_RELEASE_CATALOG),
        default=release_constants.NEW_RELEASE_NEW,
    )
    not_for_distribution: Mapped[str | None] = mapped_column(
        Enum(
            "N",
            "AccountingDummy",
            "EditoriallySuspectContent",
            "NotforFurtherDistribution",
            "TVSeasonAccountingDummy",
            "LabelRCRevenueDummy",
            "iTunesRingtone",
            "CatalogDuplicate",
            "YouTubeRemap",
            "PhysicalProduct",
            "IncompleteAssets",
            "SwitchboardDummy",
            "SMEAnalyticsDummy",
            "MissingAssets",
            "AWALNotOurDistribution",
            "ReviewedWontDeliver",
            "BulkIngestInProgress",
        ),
        default="N",
    )
    ingestion_completed = Column(DateTime, nullable=True)

    def to_dict(self) -> dict[str, Any]:
        """Return a dictionary of this release's properties."""
        return {
            "id": self.release_id,
            "language_id": self.language_id,
            "original_release_date": self.original_release_date,
            "artist_id": self.artist_id,
            "c_line": self.c_line,
            "new_release": self.new_release,
            "description": self.description,
            "display_upc": self.display_upc,
            "distribution_format_id": self.distribution_format_id,
            "format": self.release_format,
            "genre_id": self.genre_id,
            "itunes_previewable": self.itunes_previewable,
            "label": self.label,
            "manufacturer_upc": self.manufacturer_upc,
            "meta_language": self.meta_language,
            "preorder_date": self.preorder_date,
            "product_code": self.product_code,
            "product_type_id": self.product_type_id,
            "project_id": self.project_id,
            "release_date": self.release_date,
            "release_id": self.release_id,
            "release_name": self.release_name,
            "release_status": self.release_status,
            "sale_start_date": self.sale_start_date,
            "special_instructions": self.special_instructions,
            "subaccount_id": self.subaccount_id,
            "upc": self.upc,
            "vendor_catalog_number": self.vendor_catalog_number,
            "vendor_release_identifier": self.vendor_release_identifier,
            "keywords": self.keywords,
            "version": self.version,
            "not_for_distribution": self.not_for_distribution,
        }


def create(
    release_data: dict[str, Any], session: Session | None = None
) -> dict[str, Any]:
    """Create a new release instance.

    Returns:
        dict: data for the release created
    """
    ctx = mysql.ar_db_session() if session is None else contextlib.nullcontext(session)
    with ctx as s:
        release = Release(**release_data)
        s.add(release)
        s.flush()
        return release.to_dict()


def update(
    release_id: int,
    release_data: dict[str, Any],
    session: Session | None = None,
) -> dict[str, Any]:
    """Update a release instance.

    Returns:
        dict: data for the release updated
    """
    ctx = mysql.ar_db_session() if session is None else contextlib.nullcontext(session)
    with ctx as s:
        release: Release | None = s.execute(
            select(Release).where(Release.release_id == release_id)
        ).scalar_one_or_none()

        if release is None:
            raise ReleaseNotFound(f"Product {release_id} not found.")

        release_data = sanitize_input(release_data)
        for field_name, value in release_data.items():
            setattr(release, field_name, value)

        s.add(release)
        s.flush()

        return release.to_dict()


def get_by_id_with_vendor(
    release_id: int,
    *,
    session: Session | None = None,
) -> dict[str, Any]:
    """Get by id and returns vendor_id as well.

    Returns:
        dict: release data including vendor_id. Returns empty dict if not found.
    """
    ctx = mysql.ar_db_session() if session is None else contextlib.nullcontext(session)
    with ctx as s:
        query = s.execute(
            select(Release, ArtistInfo)
            .where(Release.release_id == release_id)
            .join(ArtistInfo)
        ).first()

        if query is None:
            return {}

        result: dict[str, Any] = query[0].to_dict()
        result["vendor_id"] = query[1].vendor_id

        return result


def get_by_upc(upc: int, session: Session | None = None) -> dict[str, Any]:
    """Get by upc.

    Returns:
        dict: release data. Returns empty dict if not found.
    """
    ctx = mysql.ar_db_session() if session is None else contextlib.nullcontext(session)
    with ctx as s:
        result: Release | None = s.execute(
            select(Release).where(Release.upc == upc)
        ).scalar_one_or_none()
        if result:
            return result.to_dict()
        return {}


def get_upc(session: Session | None = None) -> str:
    """Get an orchard UPC."""
    try:
        provisioned_upc = ows_product.get_provisioned_upc(True)
        _log_upc_provisioner_success(provisioned_upc)
        return provisioned_upc
    except Exception:
        _log_upc_provisioner_error()
    if session is None:
        with mysql.ar_db_session() as s:
            return str(s.execute(text(RESERVE_UPC)).scalar())
    return str(session.execute(text(RESERVE_UPC)).scalar())


def get_isrc(session: Session | None = None) -> str:
    """Get an orchard ISRC."""
    if session is None:
        with mysql.ar_db_session() as s:
            return str(s.execute(text(SP_CLAIM_ISRCS_FOR_USE)).scalar())
    return str(session.execute(text(SP_CLAIM_ISRCS_FOR_USE)).scalar())


def delete_release(release_id: int, session: Session) -> None:
    """Delete an entry from the releases table."""
    session.execute(delete(Release).where(Release.release_id == release_id))


def get(release_id: int, session: Session | None = None) -> dict[str, Any]:
    """Get by release_id."""
    ctx = mysql.ar_db_session() if session is None else contextlib.nullcontext(session)
    with ctx as s:
        release: Release | None = s.execute(
            select(Release).where(Release.release_id == release_id)
        ).scalar_one_or_none()
        if not release:
            return {}
        return release.to_dict()


def get_upc_by_product_id(
    release_id: int, session: Session | None = None
) -> dict[str, Any]:
    """Get the UPC for a release by release_id.

    Returns:
        dict: dict with a single "upc" key.

    Raises:
        ReleaseNotFound: if no release with the given release_id exists.
    """
    ctx = mysql.ar_db_session() if session is None else contextlib.nullcontext(session)
    with ctx as s:
        release: Release | None = s.execute(
            select(Release).where(Release.release_id == release_id)
        ).scalar_one_or_none()
        if not release:
            raise ReleaseNotFound()
        return {"upc": release.to_dict()["upc"]}


def sanitize_input(release_data: dict[str, Any]) -> dict[str, Any]:
    """Drop keys that shouldn't be monkeyed with."""
    valid_columns = Release.__table__.columns.keys()
    sanitized_keys = release_data.keys() & valid_columns
    sanitized_dict = {k: release_data[k] for k in sanitized_keys}
    sanitized_dict.pop("release_id", None)
    return sanitized_dict


def _log_upc_provisioner_error() -> None:
    g.log.info("Could not get provisioned UPC, falling back to stored procedure.")


def _log_upc_provisioner_success(upc: str) -> None:
    g.log.info("Assigned UPC {} from upc provisioner".format(upc))