"""ORM model for custom skill ownership + publish state.

Note: skill content lives on disk under ``skills/custom/{name}/``; this table
only stores the metadata (owner, published flag, timestamps). The skill
``name`` doubles as the primary key because the filesystem path is already
keyed by name, and we require global name uniqueness across all owners.
"""

from __future__ import annotations

from datetime import UTC, datetime

from sqlalchemy import Boolean, DateTime, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from deerflow.persistence.base import Base
from deerflow.persistence.types import BeijingDateTime


class SkillRow(Base):
    __tablename__ = "skills"

    # Global unique name — must match the on-disk folder name under skills/custom/.
    # Hyphen-case or snake_case allowed; see deerflow.skills.validation.
    name: Mapped[str] = mapped_column(String(64), primary_key=True)
    owner_user_id: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)
    published: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    # Human-authored long-form detail/introduction, editable by the skill owner
    # or an admin. Distinct from the skill ``description`` parsed from SKILL.md;
    # kept in the DB so admins can annotate built-in skills without rewriting
    # the shared on-disk SKILL.md file.
    detail: Mapped[str | None] = mapped_column(Text, nullable=True, default=None)
    # User-configured or LLM-generated Chinese display name. Kept in the DB (not
    # the on-disk SKILL.md) so it can be set for built-in skills too, and so the
    # shared source file is never rewritten.
    name_zh: Mapped[str | None] = mapped_column(String(128), nullable=True, default=None)
    # 常驻(免压缩):开启技能压缩后,标记此项的技能仍以全文形式注入系统提示词,而非
    # 降级为索引 + search_skills 检索。存 DB(而非 extensions_config.json),内置技能
    # 也能标记,且避免在某些部署里回写源码目录下的配置文件导致报错。
    always_on: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="0", index=True)
    # Publish square (发布广场) this skill is filed under once published. Pure
    # organizational category, no access control. Empty string = unassigned →
    # the listing layer resolves it to the default square. One square per skill.
    square_id: Mapped[str] = mapped_column(String(64), nullable=False, default="", server_default="", index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=lambda: datetime.now(UTC))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC))


class SkillFavoriteRow(Base):
    """A user's "我的技能" membership for a skill they don't own.

    Mirrors :class:`deerflow.persistence.agents.model.AgentFavoriteRow`: lets a
    skill land in a user's "我的" list without the user owning it. Skills are
    keyed by ``name`` (the on-disk folder / DB primary key), so this row
    references ``skill_name`` rather than an opaque id. One row per
    (user, skill); the unique constraint makes the grant idempotent.
    """

    __tablename__ = "skill_favorites"
    __table_args__ = (UniqueConstraint("user_id", "skill_name", name="uq_skill_favorites_user_skill"),)

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    user_id: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    skill_name: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    # ``"user"`` = the user added it themselves (reserved for a future favorite
    # button); ``"position"`` = auto-granted by the user's 岗位 (position) via
    # the subscription sync engine. Sync only ever adds/removes ``"position"``
    # rows, so a user's own picks are never clobbered.
    origin: Mapped[str] = mapped_column(String(16), nullable=False, default="user", server_default="user")
    # The position that granted this favorite (only set when ``origin='position'``).
    position_id: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)
    created_at: Mapped[datetime] = mapped_column(BeijingDateTime(), nullable=False, default=lambda: datetime.now(UTC))


class SkillDisplayAdapterRow(Base):
    __tablename__ = "skill_display_adapters"

    skill_name: Mapped[str] = mapped_column(String(64), primary_key=True)
    # Store JSON as text for maximum MySQL compatibility. Some deployments still
    # run MySQL variants where native JSON columns or unquoted names such as
    # ``display`` are not accepted consistently.
    display_config: Mapped[str | None] = mapped_column(Text, nullable=True, default=None)
    sample_payload: Mapped[str | None] = mapped_column(Text, nullable=True, default=None)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=lambda: datetime.now(UTC))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC))