"""ProductAdditionalUpc model + dual-write mirror helpers.

A product's UPC for an additional (non-stereo) recording edition, discriminated
by a ``type`` enum (``atmos`` today; ``sony360`` / ``5.1`` later) -- the
replacement for the one-per-product ``release_spatial`` table.

During the migration ows-product-digital dual-writes: every ``release_spatial``
write is mirrored here so this table is ready for the later reader cutover.
While ``release_spatial`` stays authoritative the new table simply tracks it --
created/restored on a release_spatial create, soft-deleted when the
release_spatial row is deleted -- so a reader can tell whether a product's atmos
UPC is active from ``deleted_at`` alone. The dual-write only ever writes
``atmos`` (it mirrors ``release_spatial``, the atmos UPC); other formats are
written directly by the type-aware endpoints once this table is authoritative.
"""

from enum import StrEnum

from sqlalchemy import BigInteger
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import Enum
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import UniqueConstraint

from oto import response

from product_digital.connectors import mysql


class ProductAdditionalUpcType(StrEnum):
    """Format of an additional (non-stereo) recording edition.

    Add a member to extend the set; also append the value to the DB enum
    (``ALTER TABLE ... MODIFY type ENUM(...)`` is ALGORITHM=INPLACE when
    appending). StrEnum members compare equal to their string value, so they
    interoperate with the raw ``'atmos'`` token used across the system.
    """

    ATMOS = 'atmos'


class ProductAdditionalUpc(mysql.BaseModel):
    """A product's UPC for one additional (non-stereo) recording, keyed by type."""

    __tablename__ = 'product_additional_upc'
    __table_args__ = (
        UniqueConstraint('upc', name='uq_product_additional_upc_upc'),
        UniqueConstraint(
            'product_id', 'type', name='uq_product_additional_upc_product_type'),
    )

    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)

    # Bare Integer, no model-level FK/relationship (matches release_spatial); the
    # FK + ON DELETE CASCADE to releases(release_id) lives in the deployed DDL
    # (theorchard/database: CDAM-3932-CREATE-additional-isrc-upc-tables.sql).
    product_id = Column(Integer, nullable=False)

    # values_callable stores the enum *values* ('atmos'), not the member names
    # ('ATMOS'), so the column matches the deployed ENUM('atmos') DDL.
    type = Column(
        Enum(
            ProductAdditionalUpcType,
            values_callable=lambda enum_cls: [m.value for m in enum_cls]),
        nullable=False)

    upc = Column(BigInteger, nullable=False)

    created_at = Column(DateTime, default=func.now())

    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())

    deleted_at = Column(DateTime, nullable=True)


def mirror_create_or_restore(product_id, upc, session):
    """Mirror a release_spatial create onto product_additional_upc.

    Ensures the product's (product, atmos) row exists, is active
    (``deleted_at IS NULL``), and carries the given UPC -- restoring and updating
    a soft-deleted row from a prior delete+re-add rather than inserting a second
    one (``UNIQUE(product_id, type)`` allows only one). release_spatial has no
    soft-delete state of its own, so a create always means active. Operates on
    the caller's session and does NOT commit, so the dual-write is one atomic
    transaction with the release_spatial write.
    """
    record = session.query(ProductAdditionalUpc).filter(
        ProductAdditionalUpc.product_id == product_id,
        ProductAdditionalUpc.type == ProductAdditionalUpcType.ATMOS,
    ).one_or_none()

    if record:
        record.upc = upc
        record.deleted_at = None
        return

    session.add(ProductAdditionalUpc(
        product_id=product_id,
        type=ProductAdditionalUpcType.ATMOS,
        upc=upc))


def mirror_soft_delete(product_id, session):
    """Mirror a release_spatial delete by soft-deleting the active atmos row.

    No-op when the product has no active atmos row. Operates on the caller's
    session and does NOT commit.
    """
    record = session.query(ProductAdditionalUpc).filter(
        ProductAdditionalUpc.product_id == product_id,
        ProductAdditionalUpc.type == ProductAdditionalUpcType.ATMOS,
        ProductAdditionalUpc.deleted_at.is_(None),
    ).one_or_none()

    if record:
        record.deleted_at = func.now()


@mysql.wrap_db_errors
@mysql.db_session_wrap
def get_atmos_upc(product_id, session):
    """Return a Response whose message is the product's atmos UPC, or None.

    Lets create_release_spatial reuse a product's additional UPC rather than
    provisioning (and reserving) a fresh one when the product already has one.
    Finds the row regardless of deleted_at (the UPC is permanent and reused on a
    re-add), and returns a Response (not a raw value) so a DB error surfaces as a
    fatal response rather than being mistaken for "no existing UPC" and
    triggering provisioning.
    """
    record = session.query(ProductAdditionalUpc).filter(
        ProductAdditionalUpc.product_id == product_id,
        ProductAdditionalUpc.type == ProductAdditionalUpcType.ATMOS,
    ).one_or_none()

    return response.Response(message=record.upc if record else None)