"""The domain schema — plan §7, verbatim in table form.

Conventions (they encode decisions, not taste):
  * `item_events` is APPEND-ONLY; `items.status` / `items.current_step_id` are
    caches rebuildable by replaying events against the pinned spec.
  * Published workflow specs are immutable: guarded in code (services) and by a
    Postgres trigger (migration 0001); new version, never UPDATE.
  * Statuses are plain strings validated in code, not DB enums — portable
    across Postgres (prod) and SQLite (tests), and adding a value is a code
    change, not a migration.
  * `punchlists.id` is TEXT and equals the auth kit's instance id; the two rows
    are created together in one service path (services/punchlists.py). No FK
    into the kit's tables — same rebuild-insurance rule as caddie.
"""

import uuid
from datetime import datetime, timezone

from sqlalchemy import (
    JSON, Boolean, CheckConstraint, DateTime, ForeignKey, Integer, String,
    Text, UniqueConstraint,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


def utcnow() -> datetime:
    return datetime.now(timezone.utc)


def new_id() -> str:
    return uuid.uuid4().hex


class Base(DeclarativeBase):
    pass


class WorkflowTemplate(Base):
    __tablename__ = "workflow_templates"
    __table_args__ = (UniqueConstraint("key", "version", name="uq_workflow_key_version"),)

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    key: Mapped[str] = mapped_column(String(80), index=True)
    version: Mapped[int] = mapped_column(Integer)
    title: Mapped[str] = mapped_column(String(200))
    spec: Mapped[dict] = mapped_column(JSON)
    status: Mapped[str] = mapped_column(String(16), default="draft")  # draft|published|retired
    created_by: Mapped[str] = mapped_column(String(120))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class TemplateSet(Base):
    __tablename__ = "template_sets"
    __table_args__ = (UniqueConstraint("key", "version", name="uq_set_key_version"),)

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    key: Mapped[str] = mapped_column(String(80), index=True)
    version: Mapped[int] = mapped_column(Integer)
    title: Mapped[str] = mapped_column(String(200))
    variables: Mapped[dict] = mapped_column(JSON, default=dict)
    items: Mapped[list] = mapped_column(JSON, default=list)  # [{template_key, pin_version|null}]
    status: Mapped[str] = mapped_column(String(16), default="draft")
    created_by: Mapped[str] = mapped_column(String(120))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class Punchlist(Base):
    """The product row for a kit instance (id == kit instance id, no FK)."""
    __tablename__ = "punchlists"

    id: Mapped[str] = mapped_column(String(80), primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    client_label: Mapped[str] = mapped_column(String(200), default="")
    client_party_id: Mapped[str | None] = mapped_column(String(36))  # "with" party uuid; hint-keyed, never a name
    external_ref: Mapped[str | None] = mapped_column(String(80), index=True)  # caddie assignment_id
    state: Mapped[str] = mapped_column(String(16), default="active")  # active|archived
    created_by: Mapped[str] = mapped_column(String(120))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class SetRun(Base):
    __tablename__ = "set_runs"

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    punchlist_id: Mapped[str] = mapped_column(ForeignKey("punchlists.id", ondelete="CASCADE"))
    set_template_id: Mapped[str] = mapped_column(ForeignKey("template_sets.id"))
    variables: Mapped[dict] = mapped_column(JSON, default=dict)
    created_by: Mapped[str] = mapped_column(String(120))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class Item(Base):
    """One item on a punchlist, running a pinned workflow.

    Exactly one of template_id / spec_inline is set (CHECK below): library items
    pin an immutable published template; manager one-offs carry their own spec.
    """
    __tablename__ = "items"
    __table_args__ = (
        CheckConstraint(
            "(template_id IS NOT NULL AND spec_inline IS NULL) OR "
            "(template_id IS NULL AND spec_inline IS NOT NULL)",
            name="ck_item_spec_source",
        ),
    )

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    punchlist_id: Mapped[str] = mapped_column(
        ForeignKey("punchlists.id", ondelete="CASCADE"), index=True)
    position: Mapped[int] = mapped_column(Integer, default=0)
    template_id: Mapped[str | None] = mapped_column(ForeignKey("workflow_templates.id"))
    spec_inline: Mapped[dict | None] = mapped_column(JSON(none_as_null=True))
    set_run_id: Mapped[str | None] = mapped_column(ForeignKey("set_runs.id"))
    section: Mapped[str | None] = mapped_column(String(80))   # display grouping (from the set, or manual)
    title: Mapped[str] = mapped_column(String(200))
    variables: Mapped[dict] = mapped_column(JSON, default=dict)
    current_step_id: Mapped[str | None] = mapped_column(String(80))  # NULL + done == "$done"
    status: Mapped[str] = mapped_column(String(24), default="waiting_on_client", index=True)
    # waiting_on_client | waiting_on_team | needs_attention | done  (derived cache)
    flag: Mapped[str | None] = mapped_column(String(24))  # later|trouble|other while open
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=utcnow, onupdate=utcnow)


class ItemEvent(Base):
    """APPEND-ONLY. Every state change on an item, replayable against the spec."""
    __tablename__ = "item_events"
    __table_args__ = (UniqueConstraint("item_id", "seq", name="uq_item_event_seq"),)

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    item_id: Mapped[str] = mapped_column(
        ForeignKey("items.id", ondelete="CASCADE"), index=True)
    seq: Mapped[int] = mapped_column(Integer)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    actor_username: Mapped[str] = mapped_column(String(120))
    actor_kind: Mapped[str] = mapped_column(String(12))  # client|team|system
    action_key: Mapped[str] = mapped_column(String(60))
    step_id: Mapped[str] = mapped_column(String(80))
    from_step: Mapped[str] = mapped_column(String(80))
    to_step: Mapped[str | None] = mapped_column(String(80))
    target: Mapped[str | None] = mapped_column(String(200))  # per-target entry acted on
    field_values: Mapped[dict | None] = mapped_column(JSON(none_as_null=True))
    message: Mapped[str | None] = mapped_column(Text)  # flag/Other/reply text (M1 embryo)


class Thread(Base):
    """One conversation per subject (an item, today). The Interaction Standard's
    embryo, born here for extraction into the shared kit later: comments live
    with the thing they're about; notifications aggregate elsewhere."""
    __tablename__ = "threads"
    __table_args__ = (UniqueConstraint("subject_type", "subject_id", name="uq_thread_subject"),)

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    punchlist_id: Mapped[str] = mapped_column(String(80), index=True)
    subject_type: Mapped[str] = mapped_column(String(24))   # "item"
    subject_id: Mapped[str] = mapped_column(String(32))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class Comment(Base):
    __tablename__ = "comments"

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    thread_id: Mapped[str] = mapped_column(ForeignKey("threads.id", ondelete="CASCADE"), index=True)
    author: Mapped[str] = mapped_column(String(120))
    author_kind: Mapped[str] = mapped_column(String(12))    # client|team|system
    body: Mapped[str] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class Notification(Base):
    """One row per person per event (04 §5 local-only mode): deep link to the
    exact place, a human context label, dedupe-keyed so emission is idempotent."""
    __tablename__ = "notifications"
    __table_args__ = (UniqueConstraint("dedupe_key", name="uq_notification_dedupe"),)

    id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)
    recipient: Mapped[str] = mapped_column(String(120), index=True)
    actor: Mapped[str] = mapped_column(String(120))
    kind: Mapped[str] = mapped_column(String(16))   # mention|reply|turn|status
    context_label: Mapped[str] = mapped_column(String(220))
    body: Mapped[str] = mapped_column(String(200), default="")
    path: Mapped[str] = mapped_column(String(300))  # app path to open
    punchlist_id: Mapped[str] = mapped_column(String(80))
    item_id: Mapped[str | None] = mapped_column(String(32))
    dedupe_key: Mapped[str] = mapped_column(String(160))
    read: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)


class PunchlistSeen(Base):
    """Drives the confirmed pop-out: items done since your last visit animate."""
    __tablename__ = "punchlist_seen"

    punchlist_id: Mapped[str] = mapped_column(String(80), primary_key=True)
    username: Mapped[str] = mapped_column(String(120), primary_key=True)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)