"""
Test package models.

Tables in this file:
  test_package      — a named bundle of tests sold at a combined price
  test_package_item — join table: TestPackage ↔ Test (M:N)

Design
──────
A package allows the lab to group related tests and offer them at a
discounted combined price (e.g., "Full Body Checkup" = CBC + LFT + KFT + TFT).

Pricing behaviour mirrors individual tests:
  When a package is added to an order, the current package.price is
  copied to order_package.price_at_order. Future price changes do not
  affect existing orders.

The individual tests inside a package are also independently orderable.
There is no constraint preventing the same test from appearing both
individually and via a package in the same order — the receptionist
is responsible for avoiding duplicates (a UI-level concern, not a
DB-level constraint, to avoid blocking legitimate edge cases).

Soft Delete Decisions
─────────────────────
  TestPackage     → YES: discontinued packages must be retained in order
                         history (orders reference them, bills were raised).
  TestPackageItem → NO:  join table — hard delete when a test is removed
                         from a package (existing orders are unaffected because
                         order_package captures the package snapshot at order time).

Lab Scoping
───────────
  TestPackage     → YES (LabScopedMixin) — pricing is per-branch
  TestPackageItem → NO  — scoped indirectly via package_id → lab_id
"""

import uuid
from decimal import Decimal

from sqlalchemy import Boolean, ForeignKey, Index, Integer, Numeric, String, Text, UniqueConstraint, text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.models.base import (
    Base,
    LabScopedMixin,
    SoftDeleteMixin,
    TimestampMixin,
    UUIDPrimaryKeyMixin,
)


class TestPackage(UUIDPrimaryKeyMixin, TimestampMixin, SoftDeleteMixin, LabScopedMixin, Base):
    """
    A named bundle of tests offered at a combined price.

    category_id is optional — a package can span multiple categories
    (e.g., a "Health Screening" package covering Hematology + Biochemistry).
    When set, it controls where the package appears in catalog listings.

    Soft delete: YES — see module docstring.
    """

    __tablename__ = "test_package"
    __table_args__ = (UniqueConstraint("lab_id", "code", name="uq_test_package_lab_id_code"),)

    category_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("test_category.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
        comment="Optional FK → test_category.id for catalog grouping",
    )
    name: Mapped[str] = mapped_column(
        String(200),
        nullable=False,
        index=True,
        comment="Package display name (e.g., 'Full Body Checkup')",
    )
    code: Mapped[str | None] = mapped_column(
        String(50),
        nullable=True,
        index=True,
        comment="Optional short code for quick lookup (e.g., 'FBC')",
    )
    description: Mapped[str | None] = mapped_column(
        Text,
        nullable=True,
        comment="Package description shown to receptionists in the order UI",
    )
    price: Mapped[Decimal] = mapped_column(
        Numeric(10, 2),
        nullable=False,
        comment="Combined price — copied to order_package.price_at_order at booking",
    )
    is_active: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        default=True,
        server_default="true",
        comment="False = hidden from new order creation",
    )

    # Relationships
    lab: Mapped["Lab"] = relationship(  # type: ignore[name-defined]
        "Lab",
        foreign_keys="[TestPackage.lab_id]",
        lazy="selectin",
    )
    category: Mapped["TestCategory"] = relationship(  # type: ignore[name-defined]
        "TestCategory",
        foreign_keys="[TestPackage.category_id]",
        lazy="selectin",
    )
    items: Mapped[list["TestPackageItem"]] = relationship(
        back_populates="package",
        lazy="noload",
        cascade="all, delete-orphan",
        order_by="TestPackageItem.sort_order",
    )

    def __repr__(self) -> str:
        return f"<TestPackage id={self.id!s:.8} name={self.name!r} price={self.price}>"


class TestPackageItem(UUIDPrimaryKeyMixin, Base):
    """
    Join table: links a TestPackage to the Tests it includes.

    Uses UUID PK (consistent with project convention) + unique constraint
    on (package_id, test_id) to prevent duplicate entries.

    sort_order controls the display sequence of tests within the package
    on reports and the order entry UI.

    No timestamps: this is a configuration record, not a transactional one.
    No soft delete: removing a test from a package = deleting this row.
                    Existing orders that included this package are unaffected.
    """

    __tablename__ = "test_package_item"
    __table_args__ = (
        Index(
            "uq_pkg_item_test_null",
            "package_id",
            "test_id",
            unique=True,
            postgresql_where=text("parameter_id IS NULL"),
        ),
        Index(
            "uq_pkg_item_test_param",
            "package_id",
            "test_id",
            "parameter_id",
            unique=True,
            postgresql_where=text("parameter_id IS NOT NULL"),
        ),
    )
    package_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("test_package.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        comment="FK → test_package.id",
    )
    test_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("test.id", ondelete="RESTRICT"),
        nullable=False,
        index=True,
        comment="FK → test.id — the test included in this package",
    )
    parameter_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("test_parameter.id", ondelete="CASCADE"),
        nullable=True,
        index=True,
        comment="Optional FK → test_parameter.id if bundling individual analyte",
    )
    sort_order: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        default=0,
        server_default="0",
        comment="Display order of this test within the package (ascending)",
    )

    # Relationships
    package: Mapped["TestPackage"] = relationship(
        back_populates="items",
        lazy="selectin",
    )
    test: Mapped["Test"] = relationship(  # type: ignore[name-defined]
        "Test",
        back_populates="package_items",
        lazy="selectin",
    )
    parameter: Mapped["TestParameter | None"] = relationship(  # type: ignore[name-defined]
        "TestParameter",
        lazy="selectin",
    )

    def __repr__(self) -> str:
        return (
            f"<TestPackageItem package_id={self.package_id!s:.8}"
            f" test_id={self.test_id!s:.8} param_id={self.parameter_id!s:.8}>"
        )