"""The discussion tables: decision cards, threads, the comments on every subject, the feature
ranking, the notification inbox, the per-account read state, asks and acknowledgements, 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))
    # The needs-follow-up mark (T13): a curator's one-line note, who and when; the three are
    # NULL together when there is none or it was cleared.
    followup_note: Mapped[str | None] = mapped_column(String(400))
    followup_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    followup_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # The workflow (`.logs/planning/discussion-workflow-2026-09-14.md`): a resolution's outcome
    # (`done` or `later`, NULL while open), the comment posted as its closing word (a plain
    # integer: a foreign key here would make the two tables reference each other in a cycle),
    # and archive, a curator's put-away that hides the thread unless asked for.
    outcome: Mapped[str | None] = mapped_column(String(8))
    closing_comment_id: Mapped[int | None] = mapped_column(Integer)
    archived_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    archived_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))

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


class ThreadAsk(Base):
    """The one hand-off: on a thread, for one person, a line saying what (confirm, respond,
    build, decide); done by that person or a curator, the word, if any, posted as a comment.
    Several may sit on one thread. Rings the person as a `turn` (needs you)."""

    __tablename__ = "thread_asks"
    __table_args__ = (Index("ix_thread_asks_thread_id", "thread_id"), Index("ix_thread_asks_for_id", "for_id"))

    id: Mapped[int] = mapped_column(primary_key=True)
    thread_id: Mapped[int] = mapped_column(ForeignKey("threads.id"), nullable=False)
    for_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), nullable=False)
    by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    note: Mapped[str] = mapped_column(String(400), nullable=False)
    # `later`, `resolve` or `reply`: the person asked gets a button that is the action, and the
    # ask completes when they do it; NULL is a request in words, done by hand.
    kind: Mapped[str | None] = mapped_column(String(16))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    done_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    done_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))


class CommentFlag(Base):
    """A personal flag on a comment (rian's card design): one row per person and comment, seen
    only by the person who set it; the panel's Flagged view lists the threads that hold one."""

    __tablename__ = "comment_flags"
    __table_args__ = (
        UniqueConstraint("comment_id", "account_id", name="uq_comment_flag"),
        Index("ix_comment_flags_account_id", "account_id"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    comment_id: Mapped[int] = mapped_column(ForeignKey("discussion_comments.id"), nullable=False)
    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class CommentAck(Base):
    """"Got it" on a comment: seen and agreed, one row per person and comment, no words. It
    also completes the person's open asks on that thread."""

    __tablename__ = "comment_acks"
    __table_args__ = (
        UniqueConstraint("comment_id", "account_id", name="uq_comment_ack"),
        Index("ix_comment_acks_comment_id", "comment_id"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    comment_id: Mapped[int] = mapped_column(ForeignKey("discussion_comments.id"), nullable=False)
    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class ThreadRead(Base):
    """When an account last opened a thread (T12): one row per (thread, account), `read_at`
    moved on every open. A comment newer than it, by someone else, is unread for that person.
    A surrogate id beside the unique pair, so the staging refresh puts the row back by natural
    key like every other client-written table."""

    __tablename__ = "thread_reads"
    __table_args__ = (
        UniqueConstraint("thread_id", "account_id", name="uq_thread_read"),
        Index("ix_thread_reads_account_id", "account_id"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    thread_id: Mapped[int] = mapped_column(ForeignKey("threads.id"), nullable=False)
    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), nullable=False)
    read_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


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))
    # A re-filed comment (T18) keeps who moved it and the thread it came from; the words, the
    # author and the times never change.
    moved_from_thread_id: Mapped[int | None] = mapped_column(ForeignKey("threads.id"))
    moved_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    moved_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", foreign_keys=[thread_id])


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
    )