"""
SQLAlchemy declarative base and shared model mixins.

All ORM models in this project inherit from one or more of these.
This module is the single source of truth for:
  - MetaData naming convention (deterministic constraint names for Alembic)
  - Primary key strategy (UUID v4, server-generated by PostgreSQL)
  - Timestamp columns (created_at / updated_at — TIMESTAMPTZ, server-side)
  - Soft delete pattern (deleted_at — TIMESTAMPTZ, nullable)
  - Branch-scoping (lab_id FK — present on all branch-scoped entities)

Design decisions documented here so they don't need repeating in every model:

1. UUID PK via gen_random_uuid():
   PostgreSQL 16 provides gen_random_uuid() without pgcrypto.
   We use server_default so the DB generates the ID — not Python.
   This means the PK is available after flush, not before insert.
   asyncpg handles UUID ↔ Python uuid.UUID coercion correctly when
   UUID(as_uuid=True) is set on the column.

2. Timestamps — server_default + onupdate:
   created_at: set once by the DB on INSERT (server_default=func.now()).
   updated_at: set on INSERT (server_default) and on every ORM UPDATE
               (onupdate=func.now() — SQLAlchemy fires this on session flush).
   IMPORTANT: onupdate only fires when the ORM session handles the update.
   Raw SQL UPDATE statements that bypass the ORM will NOT touch updated_at.
   This is intentional — we do not use raw SQL in this codebase.

3. Soft delete:
   Not all entities use soft delete. Per-entity decision is documented in
   each model file. SoftDeleteMixin is opt-in, not applied globally.
   Hard-delete entities: permission, role_permission, test_parameter_range,
   test_package_item, order_test, order_package, commission_entry, notification.
   Rationale: these are either immutable audit records or replaceable config rows.

4. Naming convention on MetaData:
   Every index, unique constraint, check constraint, FK, and PK gets a
   deterministic name. Without this, Alembic autogenerate produces unnamed
   constraints which cannot be reliably diff'd or dropped in later migrations.
"""

import uuid
from datetime import datetime
from typing import Any

from sqlalchemy import DateTime, ForeignKey, MetaData, func, text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import DeclarativeBase, Mapped, declared_attr, mapped_column

# ── Naming Convention ──────────────────────────────────────────────────────────
# Applied to MetaData so every constraint has a predictable, diffable name.
# Alembic uses these to track and rename constraints across migrations.
NAMING_CONVENTION: dict[str, str] = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}


class Base(DeclarativeBase):
    """
    Shared declarative base for all ORM models.

    Every model in this codebase inherits from this class.
    MetaData naming convention is applied here so all models
    automatically produce deterministically-named constraints.
    """

    metadata = MetaData(naming_convention=NAMING_CONVENTION)

    # Allow subclasses to define __tablename__ without type complaints
    type_annotation_map: dict[Any, Any] = {}


# ── Mixins ─────────────────────────────────────────────────────────────────────


class UUIDPrimaryKeyMixin:
    """
    Adds a UUID primary key column named `id`.

    The UUID is generated server-side by PostgreSQL's gen_random_uuid().
    This avoids the need for the pgcrypto extension (available in PG 16+
    without any extension install).

    The column is typed as uuid.UUID in Python via asyncpg's UUID adapter.
    """

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=text("gen_random_uuid()"),
        comment="Primary key — UUID v4 generated by PostgreSQL",
    )


class TimestampMixin:
    """
    Adds created_at and updated_at TIMESTAMPTZ columns to any model.

    - created_at: set once on INSERT by the database (server_default).
    - updated_at: set on INSERT and updated on every ORM-level UPDATE.
                  Uses SQLAlchemy's onupdate hook (not a DB trigger).
                  Will NOT update if the row is modified via raw SQL.
    """

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        nullable=False,
        comment="Record creation timestamp (UTC)",
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
        comment="Record last-update timestamp (UTC) — ORM-managed",
    )


class SoftDeleteMixin:
    """
    Adds a deleted_at TIMESTAMPTZ column for soft-delete support.

    A NULL value means the record is active.
    A non-NULL value means the record has been soft-deleted (and when).

    Usage:
        # Soft delete a record
        record.deleted_at = datetime.now(UTC)

        # Query only active records
        stmt = select(Model).where(Model.deleted_at.is_(None))

    NOTE: Soft-delete filtering is NOT automatic. Every query that should
    exclude deleted records must explicitly add .where(Model.deleted_at.is_(None)).
    Use a repository-level base method to enforce this consistently.

    Entities using soft delete (see individual model files for rationale):
        lab, user, patient, test, test_category, test_package,
        appointment_slot, appointment, visit_order, sample, report,
        bill, discount_rule, payment, referral.
    """

    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
        default=None,
        comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.",
    )

    @property
    def is_deleted(self) -> bool:
        """Returns True if this record has been soft-deleted."""
        return self.deleted_at is not None


class LabScopedMixin:
    """
    Adds a lab_id foreign key for branch/unit scoping.

    Every entity that belongs to a specific lab branch includes this mixin.
    The FK references the `lab` table and is non-nullable and indexed.

    Entities that are NOT branch-scoped (global across the org):
        permission, role_permission
    All other entities are branch-scoped via this mixin.

    The FK uses ondelete="RESTRICT" to prevent accidental lab deletion
    when branch data still exists.
    """

    @declared_attr
    @classmethod
    def lab_id(cls) -> Mapped[uuid.UUID]:
        return mapped_column(
            UUID(as_uuid=True),
            ForeignKey("lab.id", ondelete="RESTRICT"),
            nullable=False,
            index=True,
            comment="Branch/unit this record belongs to (FK → lab.id)",
        )