"""The discussion tables: decision cards, threads, the comments on every subject, the feature
ranking, the notification inbox, and the two small tables mail needs.

Sources of truth: this module, `alembic/versions/c7d8e9f0a1b2_*.py` (migration #6),
`app/services/discussion.py`, `app/routers/discussion.py`, `docs/CLIENT-SURFACES.md`,
`docs/ACCOUNTS.md`. Design: `.logs/planning/accounts-2026-09.md` §8, on the server's
Interaction Standard. Moved out of `catalog.py` on 2026-09-11 so the merge lane owns that
file and this lane owns these; `catalog.py` re-exports the three original names.

A thread is one conversation attached to a SUBJECT, `(subject_type, subject_id)`, unique: a
decision card, a feature-board card, a structure section, a quote line, a to-do, a running-list
item, or the discuss page itself. A comment belongs to a thread; until the follow-up migration
makes `thread_id` NOT NULL it also carries the legacy key it was written under (`item_id` or
`feature_key`, exactly one), so the pre-thread reads keep working for one release. Comments
are never hard-deleted (`deleted_at`); an edit stamps `edited_at`. The notification row is the
standard's event shape stored locally, with the who-columns as account ids (usernames are
joined at read time) and an absolute `url`, so the inbox can move to a shared service without
a migration.
"""

from datetime import datetime

from sqlalchemy import Boolean, DateTime, ForeignKey, Index, Integer, String, UniqueConstraint, func
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.models.base import Base, TimestampMixin


class DiscussionItem(Base, TimestampMixin):
    """A decision card raised with the client. Deliberately terse: a headline and a line of
    context, so the list stays scannable rather than becoming a document. Its conversation is
    the thread `decision:<id>`."""

    __tablename__ = "discussion_items"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120), nullable=False)
    note: Mapped[str | None] = mapped_column(String(400))
    reality: Mapped[str | None] = mapped_column(String(500))
    recommendation: Mapped[str | None] = mapped_column(String(400))
    theme: Mapped[str | None] = mapped_column(String(40))
    needs_decision: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    resolved: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=100, nullable=False)

    comments: Mapped[list["DiscussionComment"]] = relationship(
        back_populates="item", order_by="DiscussionComment.created_at"
    )


class Thread(Base):
    """One conversation per subject. `resolved` propagates to the notifications the thread
    raised (they leave the unread count); reopening un-flags them. `label` is what the page
    called the subject when the first comment was posted (a breadcrumb in the app's own nouns,
    the raw record), used for the bell's "on ..." line."""

    __tablename__ = "threads"
    __table_args__ = (UniqueConstraint("subject_type", "subject_id", name="uq_thread_subject"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    subject_type: Mapped[str] = mapped_column(String(24), nullable=False)
    subject_id: Mapped[str] = mapped_column(String(80), nullable=False)
    label: Mapped[str | None] = mapped_column(String(160))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    resolved: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false", nullable=False)
    resolved_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    comments: Mapped[list["DiscussionComment"]] = relationship(
        back_populates="thread", order_by="DiscussionComment.created_at"
    )


class DiscussionComment(Base, TimestampMixin):
    """A comment in a thread. `author_id` is the account it was written from (the effective one
    under View As) and `author` its display name as a snapshot, the raw record; NULL for a
    name typed before the app's own login that no backfill mapped. `item_id` / `feature_key`
    are the legacy key (exactly one, by check constraint) kept for one release."""

    __tablename__ = "discussion_comments"

    id: Mapped[int] = mapped_column(primary_key=True)
    thread_id: Mapped[int | None] = mapped_column(ForeignKey("threads.id"), index=True)
    item_id: Mapped[int | None] = mapped_column(
        ForeignKey("discussion_items.id"), nullable=True, index=True
    )
    feature_key: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
    author: Mapped[str] = mapped_column(String(80), nullable=False)
    author_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"), index=True)
    body: Mapped[str] = mapped_column(String(2000), nullable=False)
    edited_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # Soft delete only: reads omit the row, the row stays (never hard-delete a conversation).
    deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    item: Mapped[DiscussionItem | None] = relationship(back_populates="comments")
    thread: Mapped[Thread | None] = relationship(back_populates="comments")


class FeaturePriority(Base, TimestampMixin):
    """The client's demo-scope ranking: one row per feature, latest choice wins.

    Deliberately one row per feature (upsert), not a vote log: the page shows "where does this
    feature currently stand", and the client can change his mind freely.
    """

    __tablename__ = "feature_priorities"

    id: Mapped[int] = mapped_column(primary_key=True)
    feature_key: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    priority: Mapped[str] = mapped_column(String(16), nullable=False)  # must | nice | later
    author: Mapped[str] = mapped_column(String(80), nullable=False)
    author_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))


class Notification(Base):
    """The Interaction Standard's event, stored: "you personally need to know this". One row
    per recipient; `dedupe_key` makes emission idempotent; `read` is the person's, `resolved`
    follows the thread; `delivered_at` is stamped by the mail digest (T6), NULL until then."""

    __tablename__ = "notifications"
    __table_args__ = (Index("ix_notifications_inbox", "recipient_id", "read"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    recipient_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), nullable=False)
    actor_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    app: Mapped[str] = mapped_column(String(40), nullable=False, default="dfp", server_default="dfp")
    kind: Mapped[str] = mapped_column(String(16), nullable=False)  # mention | reply | turn | status | resolved | decision
    category: Mapped[str] = mapped_column(String(80), nullable=False, default="", server_default="")
    context_label: Mapped[str] = mapped_column(String(220), nullable=False, default="", server_default="")
    body: Mapped[str] = mapped_column(String(200), nullable=False, default="", server_default="")
    url: Mapped[str] = mapped_column(String(400), nullable=False)
    source_type: Mapped[str] = mapped_column(String(24), nullable=False, default="comment", server_default="comment")
    source_id: Mapped[str] = mapped_column(String(64), nullable=False, default="", server_default="")
    project_id: Mapped[str | None] = mapped_column(String(80))
    dedupe_key: Mapped[str] = mapped_column(String(180), nullable=False, unique=True)
    read: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")
    resolved: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")
    delivered_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class EmailSend(Base):
    """One row per message sent, keyed by the cap bucket it counts against (`auth:<account
    id>`, `invite:host`, `notify:<account id>`), so the mail caps are a count over a window."""

    __tablename__ = "email_sends"

    id: Mapped[int] = mapped_column(primary_key=True)
    bucket: Mapped[str] = mapped_column(String(120), nullable=False, index=True)
    sent_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class AccountPreference(Base):
    """Per-person settings the inbox reads: whether notification mail goes out at all."""

    __tablename__ = "account_preferences"

    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), primary_key=True)
    mail_notifications: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, server_default="true")
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
    )