"""ORM models for the product knowledge base.

Tables mirror the obsidian-wiki vault into the database so the product can
list, filter, search and audit notes quickly without re-parsing Markdown. The
vault Markdown remains the source-of-truth for ``content_md`` / frontmatter /
graph; these rows are an index + audit layer.

The knowledge base is **global / shared** — there is no ``user_id`` isolation.
Writes record ``created_by`` / ``updated_by`` for auditing only.
"""

from __future__ import annotations

from datetime import UTC, datetime

from sqlalchemy import Float, ForeignKey, Index, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from deerflow.persistence.base import Base
from deerflow.persistence.types import BeijingDateTime, PortableJSON, PortableLongText

# MySQL InnoDB with the legacy 767-byte index-prefix limit (5.6, or 5.7 with
# COMPACT row format) allows at most 191 utf8mb4 chars per index key. Columns
# longer than that use an explicit prefix index (``mysql_length`` — ignored by
# SQLite/Postgres) instead of ``index=True``, otherwise ``create_all`` fails
# with error 1071 ("Specified key was too long").


class KnowledgeNoteRow(Base):
    """Knowledge note main table (mirrors ``knowledge_notes``)."""

    __tablename__ = "knowledge_notes"
    __table_args__ = (
        Index("ix_knowledge_notes_vault_path", "vault_path", mysql_length=191),
        Index("ix_knowledge_notes_source_id", "source_id", mysql_length=191),
        Index("ix_knowledge_notes_folder", "folder", mysql_length=191),
    )

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    title: Mapped[str] = mapped_column(String(512))
    summary: Mapped[str | None] = mapped_column(Text)
    content_md: Mapped[str] = mapped_column(PortableLongText())
    vault_path: Mapped[str | None] = mapped_column(String(1024))
    # User-assigned directory (slash-joined path, e.g. "投研/行业"). Independent
    # of the wiki-capture ``category`` and of ``vault_path``; drives the product
    # directory tree. NULL = unfiled (shown under a fallback group).
    folder: Mapped[str | None] = mapped_column(String(512))
    source_type: Mapped[str] = mapped_column(String(64), index=True)
    source_id: Mapped[str | None] = mapped_column(String(256))
    status: Mapped[str] = mapped_column(String(32), default="approved", index=True)
    confidence: Mapped[float] = mapped_column(Float, default=0.0)
    created_by: Mapped[str | None] = mapped_column(String(128), index=True)
    updated_by: Mapped[str | None] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC), index=True)
    updated_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC), index=True)


class KnowledgeSourceRow(Base):
    """Knowledge source table (mirrors ``knowledge_sources``)."""

    __tablename__ = "knowledge_sources"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    note_id: Mapped[str] = mapped_column(String(64), ForeignKey("knowledge_notes.id", ondelete="CASCADE"), index=True)
    source_type: Mapped[str] = mapped_column(String(64))
    thread_id: Mapped[str | None] = mapped_column(String(128), index=True)
    message_id: Mapped[str | None] = mapped_column(String(128))
    tool_name: Mapped[str | None] = mapped_column(String(128))
    title: Mapped[str | None] = mapped_column(String(512))
    url: Mapped[str | None] = mapped_column(Text)
    snippet: Mapped[str | None] = mapped_column(Text)
    raw_json: Mapped[str | None] = mapped_column(PortableLongText())
    created_by: Mapped[str | None] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC))


class KnowledgeTagRow(Base):
    """Tag table (mirrors ``knowledge_tags``)."""

    __tablename__ = "knowledge_tags"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(128), unique=True, index=True)
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC))


class KnowledgeNoteTagRow(Base):
    """Note ↔ tag join table (mirrors ``knowledge_note_tags``)."""

    __tablename__ = "knowledge_note_tags"
    __table_args__ = (UniqueConstraint("note_id", "tag_id", name="uq_knowledge_note_tags"),)

    note_id: Mapped[str] = mapped_column(String(64), ForeignKey("knowledge_notes.id", ondelete="CASCADE"), primary_key=True)
    tag_id: Mapped[str] = mapped_column(String(64), ForeignKey("knowledge_tags.id", ondelete="CASCADE"), primary_key=True)


class KnowledgeEmbeddingRow(Base):
    """Per-chunk embedding index (phase 2, mirrors ``knowledge_embeddings``).

    The vector is stored as a JSON float array; first-release similarity is
    computed in Python (no external vector DB). ``vector_id`` is reserved for a
    future external store mapping.
    """

    __tablename__ = "knowledge_embeddings"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    note_id: Mapped[str] = mapped_column(String(64), ForeignKey("knowledge_notes.id", ondelete="CASCADE"), index=True)
    chunk_index: Mapped[int] = mapped_column(Integer, default=0)
    content: Mapped[str] = mapped_column(Text)
    vector: Mapped[list] = mapped_column(PortableJSON(), default=list)
    vector_id: Mapped[str | None] = mapped_column(String(256))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC))


class KnowledgeNoteVersionRow(Base):
    """Immutable snapshot of a note before each update (phase 3 versioning)."""

    __tablename__ = "knowledge_note_versions"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    note_id: Mapped[str] = mapped_column(String(64), ForeignKey("knowledge_notes.id", ondelete="CASCADE"), index=True)
    title: Mapped[str] = mapped_column(String(512))
    summary: Mapped[str | None] = mapped_column(Text)
    content_md: Mapped[str] = mapped_column(PortableLongText())
    status: Mapped[str] = mapped_column(String(32))
    tags_json: Mapped[list] = mapped_column(PortableJSON(), default=list)
    edited_by: Mapped[str | None] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC), index=True)


class KnowledgeEntityRow(Base):
    """Knowledge entity (phase 4, mirrors ``knowledge_entities``)."""

    __tablename__ = "knowledge_entities"
    __table_args__ = (
        Index("ix_knowledge_entities_name", "name", mysql_length=191),
        # Prefix-unique: (120 + 64) * 4 = 736 bytes <= 767. Entity names are
        # realistically far shorter than 120 chars, so the prefix is lossless.
        Index("uq_knowledge_entities_name_type", "name", "entity_type", unique=True, mysql_length={"name": 120}),
    )

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(256))
    entity_type: Mapped[str | None] = mapped_column(String(64))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC))


class KnowledgeGraphBlacklistRow(Base):
    """Graph blacklist: node labels (entity names / note titles) hidden from the graph.

    Matching is case-insensitive on the trimmed label. Global/shared like the
    rest of the knowledge base; ``created_by`` is audit-only.
    """

    __tablename__ = "knowledge_graph_blacklist"
    __table_args__ = (Index("ix_knowledge_graph_blacklist_label", "label", unique=True, mysql_length=191),)

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    label: Mapped[str] = mapped_column(String(512))
    created_by: Mapped[str | None] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC))


class KnowledgeRelationRow(Base):
    """Relation between notes/entities (phase 4, mirrors ``knowledge_relations``)."""

    __tablename__ = "knowledge_relations"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    from_note_id: Mapped[str | None] = mapped_column(String(64), index=True)
    to_note_id: Mapped[str | None] = mapped_column(String(64), index=True)
    from_entity_id: Mapped[str | None] = mapped_column(String(64), index=True)
    to_entity_id: Mapped[str | None] = mapped_column(String(64), index=True)
    relation_type: Mapped[str] = mapped_column(String(128))
    weight: Mapped[float] = mapped_column(Float, default=1.0)
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC))


class KnowledgeFolderRow(Base):
    """User-managed knowledge directory (mirrors ``knowledge_folders``).

    A folder is identified by its full slash-joined ``path`` (e.g. ``投研/行业``);
    nesting is derived by splitting on ``/`` on the client. Folders exist
    independently of notes so an operator can create an empty directory and file
    notes into it later (``knowledge_notes.folder`` stores the chosen path).
    Global/shared like the rest of the KB; ``created_by`` is audit-only.
    """

    __tablename__ = "knowledge_folders"
    __table_args__ = (Index("uq_knowledge_folders_path", "path", unique=True, mysql_length=191),)

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    path: Mapped[str] = mapped_column(String(512))
    name: Mapped[str] = mapped_column(String(256))
    sort_order: Mapped[int] = mapped_column(Integer, default=0)
    created_by: Mapped[str | None] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC))


class KnowledgeExtractTemplateRow(Base):
    """Editable knowledge-extraction prompt template (mirrors ``knowledge_extract_templates``).

    Lets an operator open up, edit and add multiple distillation prompts instead
    of the hard-coded constants. ``scope`` is ``thread`` / ``document`` / ``both``;
    at capture time the service resolves the chosen template (or the default for
    that scope) and feeds its ``system_prompt`` to the extractor. Global/shared.
    """

    __tablename__ = "knowledge_extract_templates"
    __table_args__ = (Index("uq_knowledge_extract_templates_name", "name", unique=True, mysql_length=191),)

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(256))
    description: Mapped[str | None] = mapped_column(Text)
    scope: Mapped[str] = mapped_column(String(16), default="both", index=True)
    system_prompt: Mapped[str] = mapped_column(PortableLongText())
    is_default: Mapped[bool] = mapped_column(default=False, index=True)
    enabled: Mapped[bool] = mapped_column(default=True)
    created_by: Mapped[str | None] = mapped_column(String(128))
    updated_by: Mapped[str | None] = mapped_column(String(128))
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC), index=True)
    updated_at: Mapped[datetime] = mapped_column(BeijingDateTime(), default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC))