"""Notifications (M5) — the bell. Design copied wholesale from the review
app's migrations 0014-0017 (review-patterns-ref §2.6, review-learnings §4):
three kinds (mention | thread_reply | action_to), a denormalized category +
context_label so a notification reads standalone without joining back to its
ball, a 160-char stripped body preview, a deep link, and a `resolved` flag
that mirrors the SOURCE ball's resolved state rather than the read flag.

Only the MECHANISM moved (DB security-definer triggers -> services layer,
plan §6.4); the shape here is the same table the review app proved out,
minus the Supabase-specific `recipient_email`/`actor_email` mirror columns
(this app has one `players` table to join, no `auth.users` split).

`kind` is deliberately plain text, not a DB CHECK/enum — ref §1.6 notes this
is the one vocab the review app never constrained at the data layer either
("established only by convention in application code"); reproduced as-is.

`ball_id` is the one deliberate addition beyond a literal port: the review
app only had `source_table`/`source_id` (comment vs note/issue) and derived
"which ball" by parsing the link or joining through comments for resolved-
propagation. Denormalizing the ball straight onto the row (indexed) turns
that propagation into one UPDATE (services.notifications.propagate_resolved)
instead of a join or a link-parse.
"""
import datetime

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

from .base import Base, created_at, uuid_pk


class Notification(Base):
    __tablename__ = "notifications"
    __table_args__ = (
        # The bell's one query: "my newest, unread-first-ish" — recipient +
        # read scope the WHERE, created_at DESC serves the ORDER BY without
        # a sort step. Spec'd shape: Index(recipient_id, read, created_at desc).
        Index(
            "ix_notifications_recipient_read_created",
            "recipient_id",
            "read",
            text("created_at DESC"),
        ),
        # propagate_resolved's one UPDATE (WHERE ball_id = :ball_id) — this
        # is the column that makes that a single indexed statement.
        Index("ix_notifications_ball_id", "ball_id"),
    )

    id: Mapped[str] = uuid_pk()
    recipient_id: Mapped[str] = mapped_column(
        UUID(as_uuid=True), ForeignKey("players.id", ondelete="CASCADE"), nullable=False
    )
    actor_id: Mapped[str | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("players.id", ondelete="SET NULL")
    )
    # mention | thread_reply | action_to (ref §1.6: convention-only, see docstring)
    kind: Mapped[str] = mapped_column(String, nullable=False)
    # The ball's category AT INSERT (denormalized snapshot; ball.category may
    # change later without rewriting notification history).
    category: Mapped[str | None] = mapped_column(String)
    # The ball's TITLE at insert (denormalized) — review-learnings §4's
    # single biggest clarity win ("...replied in a thread **on Faculty**").
    context_label: Mapped[str | None] = mapped_column(String)
    # 160-char preview: markdown image tags stripped, whitespace collapsed
    # (services.notifications._preview ports the exact regex idiom).
    body: Mapped[str] = mapped_column(Text, nullable=False, server_default=text("''"))
    # Deep link: /?focus=ball:<ball_id>[&comment=<comment_id>]. The frontend
    # appends its own `_t=` cache-buster on click — never stored here (ref
    # §3.6: storing a static link with no buster is what made the re-click
    # bug possible in the first place).
    link: Mapped[str] = mapped_column(String, nullable=False)
    read: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("false"))
    # Mirrors the SOURCE ball's resolved state (services.notifications
    # .propagate_resolved) — a notification's lifecycle tracks the work
    # item, not whether it's been clicked (review-learnings §4).
    resolved: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=text("false"))
    # 'comment' | 'ball' — which table source_id points at. Deliberately NOT
    # a ForeignKey, same call as Comment.parent_id (models/conversation.py):
    # the pointed-at table depends on source_type.
    source_type: Mapped[str] = mapped_column(String, nullable=False)
    source_id: Mapped[str] = mapped_column(UUID(as_uuid=True), nullable=False)
    ball_id: Mapped[str] = mapped_column(
        UUID(as_uuid=True), ForeignKey("balls.id", ondelete="CASCADE"), nullable=False
    )
    created_at: Mapped[datetime.datetime] = created_at()