"""Dual-voice notes domain.

CARRIED from v1 (near-verbatim port of the pragma snapshot at
``data/v1-snapshot/v1_schema_pragma.txt``):

- ``field_notes`` — v1's unified capture/comment table (raw captures with
  status='new' + finalized comments with body/kind/comment_date). Carried as
  the immutable "capture inbox" source; gains ``garden_id`` (deviation D1)
  because it is an entity table.
- ``field_note_targets`` — child link table; no ``garden_id`` (scope comes
  from the parent note).

NEW v2 tables (approved plan): the dual-voice notes layer.

- ``notes`` — assistant- or user-authored markdown notes distilled from
  captures/conversations.
- ``note_targets`` — entity links with resolver confidence + provenance.
- ``note_media`` — one row per attached photo/audio file (unlike v1's
  pipe-delimited ``field_notes.photo_paths``, which stays as-is per D3).
- ``note_revisions`` — append-only body history for edited notes.

Conventions per ``base.py``: typed SQLAlchemy 2.0, no ``relationship()``,
no PG native enums (TEXT + named CheckConstraint), v1 TEXT timestamps become
TIMESTAMPTZ with nullability preserved, v1 0/1 flags become Boolean.
"""
from __future__ import annotations

from datetime import date, datetime

import sqlalchemy as sa
from sqlalchemy import (
    BigInteger,
    Boolean,
    CheckConstraint,
    Date,
    DateTime,
    Double,
    ForeignKey,
    Index,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, created_at_column, garden_fk, pk_column


class FieldNote(Base):
    """CARRIED v1 ``field_notes`` — unified raw captures + finalized comments.

    Near-verbatim port. v1 nullability preserved column-for-column; TEXT
    timestamps → TIMESTAMPTZ; ``comment_date`` TEXT → DATE; 0/1 → Boolean.
    ``photo_paths`` stays pipe-delimited Text (deviation D3).
    """

    __tablename__ = "field_notes"

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()  # D1: entity table gains garden scope
    created_at: Mapped[datetime] = created_at_column()
    captured_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    audio_path: Mapped[str | None] = mapped_column(Text, nullable=True)
    # D3: pipe-delimited list of photo paths, carried as-is from v1.
    photo_paths: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    text: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    transcript: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    # v1 vocabulary: 'new' | 'processed' (enforced in app code, no CHECK in v1).
    status: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("'new'")
    )
    processed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    processing_notes: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    body: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    kind: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    comment_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    # V2-ONLY (allowlisted in scripts/canonical.py): SPA route the note was
    # captured from, e.g. "/garden/areas/12". Powers kind='feedback' app
    # feedback ("this layout needs…") — the page IS the context there.
    page_context: Mapped[str | None] = mapped_column(
        Text, nullable=True, server_default=sa.text("''")
    )
    # Self-FK, ON DELETE SET NULL per v1 pragma.
    parent_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("field_notes.id", ondelete="SET NULL"),
        nullable=True,
    )
    # v1: INTEGER 0/1, nullable with default 0 — nullability preserved.
    is_archived: Mapped[bool | None] = mapped_column(
        Boolean, nullable=True, server_default=sa.text("false")
    )

    __table_args__ = (
        # v1: idx_field_notes_archived
        Index(None, "is_archived"),
        # v1: idx_field_notes_comment_date
        Index(None, "comment_date", "created_at"),
        # v1: idx_field_notes_status
        Index(None, "status", "created_at"),
    )


class FieldNoteTarget(Base):
    """CARRIED v1 ``field_note_targets`` — entity links for field notes.

    Child table: no ``garden_id`` (scoped through the parent field note).
    """

    __tablename__ = "field_note_targets"

    id: Mapped[int] = pk_column()
    # v1: idx_field_note_targets_fnid (via index=True); ON DELETE CASCADE per pragma.
    field_note_id: Mapped[int] = mapped_column(
        BigInteger,
        ForeignKey("field_notes.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    target_type: Mapped[str] = mapped_column(Text, nullable=False)
    target_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    # v1: INTEGER 0/1, nullable with default 0 — nullability preserved.
    is_primary: Mapped[bool | None] = mapped_column(
        Boolean, nullable=True, server_default=sa.text("false")
    )

    __table_args__ = (
        # v1: idx_field_note_targets_unique
        UniqueConstraint("field_note_id", "target_type", "target_id"),
        # v1: idx_field_note_targets_target
        Index(None, "target_type", "target_id"),
    )


class Note(Base):
    """NEW — dual-voice note: assistant-distilled or user-authored markdown."""

    __tablename__ = "notes"

    id: Mapped[int] = pk_column()
    garden_id: Mapped[int] = garden_fk()
    assistant_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("assistants.id"), nullable=True
    )
    # Provenance: the raw capture and/or conversation this note was distilled from.
    source_capture_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("field_notes.id"), nullable=True
    )
    source_conversation_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("chat_sessions.id"), nullable=True
    )
    # Vocabulary: 'assistant' | 'user' (CHECK below).
    author: Mapped[str] = mapped_column(Text, nullable=False)
    body_md: Mapped[str] = mapped_column(Text, nullable=False)
    # Vocabulary: 'assistant' | 'user_edited' | 'user_raw' (CHECK below).
    body_source: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("'assistant'")
    )
    # True once the user has edited the body — the assistant must not rewrite it.
    voice_locked: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default=sa.text("false")
    )
    kind: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("''")
    )
    note_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    is_pinned: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default=sa.text("false")
    )
    created_at: Mapped[datetime] = created_at_column()
    updated_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    is_archived: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default=sa.text("false")
    )

    __table_args__ = (
        CheckConstraint("author IN ('assistant', 'user')", name="author"),
        CheckConstraint(
            "body_source IN ('assistant', 'user_edited', 'user_raw')",
            name="body_source",
        ),
        # Explicit name: the convention-derived name (ix_notes_garden_id)
        # would collide with garden_fk()'s single-column index.
        Index("ix_notes_garden_id_note_date", "garden_id", "note_date"),
    )


class NoteTarget(Base):
    """NEW — entity link for a note, with resolver confidence + provenance."""

    __tablename__ = "note_targets"

    id: Mapped[int] = pk_column()
    note_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("notes.id", ondelete="CASCADE"), nullable=False
    )
    target_type: Mapped[str] = mapped_column(Text, nullable=False)
    target_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    is_primary: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default=sa.text("false")
    )
    # Resolver's match confidence (0..1); NULL for user-made links.
    confidence: Mapped[float | None] = mapped_column(Double, nullable=True)
    # Vocabulary: 'resolver' | 'user' | 'confirmed' (CHECK below).
    link_source: Mapped[str] = mapped_column(
        Text, nullable=False, server_default=sa.text("'resolver'")
    )

    __table_args__ = (
        CheckConstraint(
            "link_source IN ('resolver', 'user', 'confirmed')",
            name="link_source",
        ),
        UniqueConstraint("note_id", "target_type", "target_id"),
        Index(None, "target_type", "target_id"),
    )


class NoteMedia(Base):
    """NEW — one row per media file attached to a note (vs v1's pipe-delimited
    ``field_notes.photo_paths``, which is carried unchanged per D3)."""

    __tablename__ = "note_media"

    id: Mapped[int] = pk_column()
    note_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("notes.id", ondelete="CASCADE"), nullable=False
    )
    # Vocabulary: 'photo' | 'audio' (CHECK below).
    kind: Mapped[str] = mapped_column(Text, nullable=False)
    path: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = created_at_column()

    __table_args__ = (
        CheckConstraint("kind IN ('photo', 'audio')", name="kind"),
    )


class NoteRevision(Base):
    """NEW — append-only body history: prior body snapshot per edit."""

    __tablename__ = "note_revisions"

    id: Mapped[int] = pk_column()
    note_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("notes.id", ondelete="CASCADE"), nullable=False
    )
    body: Mapped[str] = mapped_column(Text, nullable=False)
    body_source: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = created_at_column()