import uuid

from sqlalchemy import Boolean, ForeignKey, String
from sqlalchemy.dialects.postgresql import JSONB, UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, TenantScopedMixin, TimestampMixin, UUIDPKMixin


class Master(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "masters"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    field_schema: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    # Names one of this Master's own field_schema entries (must be type
    # "reference") as the row-level "owning scope" field — see
    # app/crud/scope.py. Null (the default) means unrestricted, unchanged
    # from every Master's behavior before this feature existed.
    scope_field: Mapped[str | None] = mapped_column(String(200))


class MasterRecord(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "master_records"

    # Part of the key because the table is partitioned by it, so an update or
    # delete of one record goes straight to its partition (db/schema.sql).
    master_id: Mapped[uuid.UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("masters.id", ondelete="CASCADE"), primary_key=True
    )
    field_values: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    updated_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    # Soft-delete flag — see db/schema.sql's master_records.is_deleted comment.
    is_deleted: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")


class UserScopeAssignment(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    """Grants `user_id` visibility into records of some *scoped* Master whose
    `scope_field` references `master_id`, restricted to the one record
    `record_id` (e.g. one specific Office). A user with zero rows here for a
    given scope-defining Master is unrestricted for anything scoped by it —
    see app/crud/scope.py."""

    __tablename__ = "user_scope_assignments"

    user_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    master_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("masters.id", ondelete="CASCADE"), nullable=False)
    record_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)