"""Unified aion.db ORM (subset used by chat history + v1 API)."""

from __future__ import annotations

from datetime import datetime
from typing import Optional

from sqlalchemy import (
    Boolean,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    LargeBinary,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

from .encrypted_types import EncryptedOAuthConfigJSON


class Base(DeclarativeBase):
    pass


class Tenant(Base):
    __tablename__ = "tenants"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    metadata_json: Mapped[str] = mapped_column("metadata", Text, default="{}")


class User(Base):
    __tablename__ = "users"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), nullable=False
    )
    identifier: Mapped[str] = mapped_column(String(256), nullable=False)
    display_name: Mapped[Optional[str]] = mapped_column(String(256))
    email: Mapped[Optional[str]] = mapped_column(String(256))
    password_hash: Mapped[Optional[str]] = mapped_column(String(256))
    metadata_json: Mapped[str] = mapped_column("metadata", Text, default="{}")
    # JSON list di ruoli (es. ["admin"]). Default vuoto. Verificare con
    # ``user_password.has_role(u, "admin")`` invece di parsing inline.
    roles_json: Mapped[str] = mapped_column(
        "roles", Text, default="[]", server_default="[]", nullable=False
    )
    # Stile Grafana: True dopo creazione admin di default; il client mostra
    # un banner non-bloccante per cambiare la password al primo accesso.
    must_change_password: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="0", nullable=False
    )
    # SSO migration: esonero dal conteggio utenti in attesa (§1.2).
    sso_migration_exempt: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="0", nullable=False
    )
    # Password temporanea generata dall'admin per la migrazione SSO (§1.2).
    # Valorizzata solo quando password_hash contiene una temp password.
    temp_password_expires_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    last_active_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    # 2FA TOTP (migration v4w5x6y024). Secret cifrato AES-GCM (credential_store).
    # ``totp_enabled_at`` NULL = enrollment non confermato (secret pending).
    totp_secret_encrypted: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    totp_enabled_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    totp_last_used_step: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    totp_failed_count: Mapped[int] = mapped_column(
        Integer, default=0, server_default="0", nullable=False
    )
    totp_locked_until: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    # API key personali (migration y7z8a9b027): l'admin abilita l'utente e
    # fissa quante chiavi attive può avere e la loro durata massima.
    can_create_api_keys: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="0", nullable=False
    )
    max_api_keys: Mapped[int] = mapped_column(
        Integer, default=5, server_default="5", nullable=False
    )
    api_key_max_ttl_days: Mapped[int] = mapped_column(
        Integer, default=90, server_default="90", nullable=False
    )
    # Service accounts (migration g5b6c7d035): ``"human"`` for people,
    # ``"service"`` for non-human accounts managed by admins that can only
    # authenticate with API keys (no password, SSO, 2FA or channels). Extra
    # service settings (description, ltm_enabled, created_by) live in
    # ``metadata["service"]``; see ``src/data/service_accounts.py``.
    account_type: Mapped[str] = mapped_column(
        String(16), default="human", server_default="human", nullable=False, index=True
    )
    # Reversible deactivation: while set, every API key of the account is
    # rejected (``api_key_owner_disabled``) but the keys are kept, so
    # re-enabling the account restores the integration without rotating keys.
    disabled_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )

    __table_args__ = (
        UniqueConstraint("tenant_id", "identifier", name="uq_users_tenant_identifier"),
    )


class ApiKey(Base):
    __tablename__ = "api_keys"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), nullable=False
    )
    # Proprietario: la chiave agisce come questo utente. NULL solo sulle righe
    # legacy (pre y7z8a9b027), sempre rifiutate in autenticazione.
    user_id: Mapped[Optional[str]] = mapped_column(
        String(64),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=True,
        index=True,
    )
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    # Solo per la visualizzazione (``aion_<prefix>_…``); il lookup usa ``hash``.
    prefix: Mapped[str] = mapped_column(String(16), nullable=False, index=True)
    # SHA-256 hex della chiave completa (vedi ``src/api/auth/api_key.py``).
    hash: Mapped[str] = mapped_column(String(256), nullable=False)
    scopes_json: Mapped[str] = mapped_column("scopes", Text, default="[]")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    expires_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_used_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    revoked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    __table_args__ = (Index("ux_api_keys_hash", "hash", unique=True),)


class Conversation(Base):
    __tablename__ = "conversations"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), nullable=False, default="default"
    )
    user_id: Mapped[str] = mapped_column(String(256), nullable=False)
    profile_slug: Mapped[str] = mapped_column(String(256), nullable=False)
    title: Mapped[Optional[str]] = mapped_column(String(512))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    archived_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    message_count: Mapped[int] = mapped_column(Integer, default=0)
    project_id: Mapped[Optional[int]] = mapped_column(
        Integer, ForeignKey("projects.id", ondelete="SET NULL"), nullable=True
    )
    metadata_json: Mapped[str] = mapped_column("metadata", Text, default="{}")
    tags_json: Mapped[str] = mapped_column("tags", Text, default="[]")
    # Side thread: conversazione figlia aperta da un passaggio di un messaggio
    # assistant della conversazione madre (un solo livello di annidamento).
    parent_conversation_id: Mapped[Optional[str]] = mapped_column(
        String(64), nullable=True, index=True
    )
    parent_message_id: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    anchor_json: Mapped[str] = mapped_column(Text, default="{}")

    # Conversation branching / message forking
    root_conversation_id: Mapped[Optional[str]] = mapped_column(
        String(64), nullable=True, index=True
    )
    forked_from_id: Mapped[Optional[str]] = mapped_column(
        String(64), nullable=True, index=True
    )
    forked_at_message_id: Mapped[Optional[str]] = mapped_column(
        String(64), nullable=True, index=True
    )
    fork_seq: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)

    messages: Mapped[list["Message"]] = relationship(
        back_populates="conversation", cascade="all, delete-orphan"
    )


class Message(Base):
    __tablename__ = "messages"
    fts_rowid: Mapped[int] = mapped_column(
        Integer, primary_key=True, autoincrement=True
    )
    id: Mapped[str] = mapped_column(String(64), nullable=False, unique=True, index=True)
    conversation_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("conversations.id", ondelete="CASCADE"), nullable=False
    )
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    seq: Mapped[int] = mapped_column(Integer, nullable=False)
    role: Mapped[str] = mapped_column(String(32), nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    reasoning: Mapped[Optional[str]] = mapped_column(Text)
    timeline_json: Mapped[Optional[str]] = mapped_column(Text)
    metadata_json: Mapped[Optional[str]] = mapped_column(Text)
    tool_name: Mapped[Optional[str]] = mapped_column(String(256))
    tool_call_id: Mapped[Optional[str]] = mapped_column(String(128))
    tokens_in: Mapped[Optional[int]] = mapped_column(Integer)
    tokens_out: Mapped[Optional[int]] = mapped_column(Integer)
    finish_reason: Mapped[Optional[str]] = mapped_column(String(64))
    promoted_to_ltm: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    profile_name: Mapped[str] = mapped_column(String(256), default="default")
    trace_id: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    forked_from_message_id: Mapped[Optional[str]] = mapped_column(
        String(64), nullable=True, index=True
    )
    rating: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    feedback_comment: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    archived_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    archived_reason: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)

    conversation: Mapped["Conversation"] = relationship(back_populates="messages")

    __table_args__ = (
        UniqueConstraint("conversation_id", "seq", name="uq_msg_conv_seq"),
    )


class Attachment(Base):
    __tablename__ = "attachments"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    conversation_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("conversations.id", ondelete="CASCADE"), nullable=False
    )
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    message_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("messages.id", ondelete="SET NULL")
    )
    storage_key: Mapped[str] = mapped_column(String(1024), nullable=False)
    original_name: Mapped[str] = mapped_column(String(512), nullable=False)
    mime: Mapped[str] = mapped_column(String(256), nullable=False)
    size_bytes: Mapped[int] = mapped_column(Integer, nullable=False)
    kind: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class Step(Base):
    __tablename__ = "steps"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    conversation_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("conversations.id", ondelete="CASCADE"), nullable=False
    )
    parent_id: Mapped[Optional[str]] = mapped_column(String(64))
    message_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("messages.id", ondelete="SET NULL")
    )
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    type: Mapped[str] = mapped_column(String(64), nullable=False)
    input: Mapped[Optional[str]] = mapped_column(Text)
    output: Mapped[Optional[str]] = mapped_column(Text)
    is_error: Mapped[int] = mapped_column(Integer, default=0)
    streaming: Mapped[int] = mapped_column(Integer, default=0)
    wait_for_answer: Mapped[Optional[int]] = mapped_column(Integer)
    start: Mapped[Optional[str]] = mapped_column(Text)
    end: Mapped[Optional[str]] = mapped_column(Text)
    indent: Mapped[Optional[int]] = mapped_column(Integer)
    metadata_json: Mapped[Optional[str]] = mapped_column("metadata", Text)
    tags_json: Mapped[Optional[str]] = mapped_column("tags", Text)
    trace_id: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class SessionChart(Base):
    """Chart rendered in a chat turn (emitted by the ``charts`` MCP tools).

    Redis keeps charts for the live turn (fast, TTL-bound); at the end of the SSE
    turn they are copied here so chat-ui can re-render them after a reload or a
    chat switch, inline at the point of the assistant timeline where the tool ran.

    ``id`` is the chart id referenced by the ``{"kind": "chart"}`` timeline
    segment. Rows disappear with the conversation (FK cascade on hard purge,
    explicit delete on soft delete / turn delete, see ``src/charts_store.py``).
    """

    __tablename__ = "session_charts"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    conversation_id: Mapped[str] = mapped_column(
        String(64),
        ForeignKey("conversations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    # Assistant message the chart belongs to. No FK on purpose: charts are
    # persisted at turn end and must not fail if the message row is written
    # later (or collapsed); turn deletion removes them explicitly.
    message_id: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    # Id of the tool call that produced the chart (links to ``steps.id``).
    tool_call_id: Mapped[Optional[str]] = mapped_column(String(128))
    # Normalized chart payload (see ``src/chart_payload.py``) as JSON text.
    payload_json: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class Feedback(Base):
    __tablename__ = "feedbacks"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    message_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("messages.id"), nullable=True
    )
    step_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("steps.id", ondelete="CASCADE"), nullable=True
    )
    conversation_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("conversations.id", ondelete="CASCADE"), nullable=False
    )
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    value: Mapped[int] = mapped_column(Integer, nullable=False)
    comment: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class AuditLog(Base):
    __tablename__ = "audit_log"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[str] = mapped_column(String(64), nullable=False)
    actor: Mapped[str] = mapped_column(String(256), nullable=False)
    operation: Mapped[str] = mapped_column(String(128), nullable=False)
    resource_type: Mapped[Optional[str]] = mapped_column(String(128))
    resource_id: Mapped[Optional[str]] = mapped_column(String(256))
    payload: Mapped[Optional[str]] = mapped_column(Text)
    trace_id: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class ApprovalRule(Base):
    __tablename__ = "approval_rules"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[str] = mapped_column(String(64), nullable=False)
    user_id: Mapped[Optional[str]] = mapped_column(String(256))
    tool_name: Mapped[str] = mapped_column(String(256), nullable=False)
    input_pattern: Mapped[str] = mapped_column(Text, nullable=False)
    decision: Mapped[str] = mapped_column(String(16), nullable=False)
    rationale: Mapped[Optional[str]] = mapped_column(Text)
    source: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    uses: Mapped[int] = mapped_column(Integer, default=0)
    last_used_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))


class CachedQuery(Base):
    __tablename__ = "cached_queries"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_request: Mapped[str] = mapped_column(Text, nullable=False)
    promql_query: Mapped[str] = mapped_column(Text, nullable=False)
    is_verified: Mapped[int] = mapped_column(Integer, default=0)
    success_count: Mapped[int] = mapped_column(Integer, default=0)
    namespace: Mapped[str] = mapped_column(String(128), default="default")
    metadata_json: Mapped[Optional[str]] = mapped_column("metadata", Text)
    embedding: Mapped[Optional[bytes]] = mapped_column(LargeBinary)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "user_request", "namespace", name="uq_cached_query_request_ns"
        ),
    )


class TenantQueryMemorySettings(Base):
    __tablename__ = "tenant_query_memory_settings"

    tenant_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    sql_default_scope: Mapped[str] = mapped_column(
        String(16), default="per_user", server_default="per_user"
    )
    sql_auto_learn: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="1"
    )
    sql_search_before_run: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="1"
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class Project(Base):
    __tablename__ = "projects"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    slug: Mapped[str] = mapped_column(String(128), nullable=False)
    display_name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text)
    datasource_key: Mapped[Optional[str]] = mapped_column(
        String(128), nullable=True, default="default"
    )
    profile_slug: Mapped[Optional[str]] = mapped_column(String(256))
    scope_mode: Mapped[str] = mapped_column(
        String(16), default="inherit", server_default="inherit"
    )
    created_by: Mapped[Optional[str]] = mapped_column(String(256))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    __table_args__ = (
        UniqueConstraint("tenant_id", "slug", name="uq_projects_tenant_slug"),
    )

    members: Mapped[list["SqlQueryProjectMember"]] = relationship(
        "SqlQueryProjectMember",
        back_populates="project",
        cascade="all, delete-orphan",
    )


class SqlQueryProjectMember(Base):
    __tablename__ = "sql_query_project_members"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("projects.id", ondelete="CASCADE"), nullable=False
    )
    user_identifier: Mapped[str] = mapped_column(String(256), nullable=False)
    role: Mapped[str] = mapped_column(
        String(16), nullable=False, default="member", server_default="member"
    )
    invited_by: Mapped[Optional[str]] = mapped_column(String(256))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    project: Mapped["Project"] = relationship("Project", back_populates="members")

    __table_args__ = (
        UniqueConstraint(
            "project_id", "user_identifier", name="uq_sql_query_project_member"
        ),
    )


class CachedSqlQuery(Base):
    __tablename__ = "cached_sql_queries"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("projects.id", ondelete="CASCADE"), nullable=False
    )
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    user_id: Mapped[Optional[str]] = mapped_column(String(256))
    user_scope_key: Mapped[str] = mapped_column(String(256), nullable=False)
    user_request: Mapped[str] = mapped_column(Text, nullable=False)
    sql_text: Mapped[str] = mapped_column(Text, nullable=False)
    sql_fingerprint: Mapped[str] = mapped_column(String(64), nullable=False)
    embedding_request: Mapped[Optional[bytes]] = mapped_column(LargeBinary)
    embedding_sql: Mapped[Optional[bytes]] = mapped_column(LargeBinary)
    tables_used_json: Mapped[Optional[str]] = mapped_column(Text)
    metadata_json: Mapped[Optional[str]] = mapped_column("metadata", Text)
    is_verified: Mapped[int] = mapped_column(Integer, default=0)
    success_count: Mapped[int] = mapped_column(Integer, default=0)
    failure_count: Mapped[int] = mapped_column(Integer, default=0)
    last_used_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "project_id",
            "user_scope_key",
            "sql_fingerprint",
            name="uq_cached_sql_project_scope_fp",
        ),
    )


class LtmNote(Base):
    __tablename__ = "ltm_notes"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    scope_type: Mapped[str] = mapped_column(String(16), nullable=False)
    scope_key: Mapped[str] = mapped_column(String(256), nullable=False)
    seq: Mapped[int] = mapped_column(Integer, nullable=False)
    content: Mapped[str] = mapped_column(String(500), nullable=False)
    category: Mapped[str] = mapped_column(String(32), nullable=False, default="fact")
    importance: Mapped[int] = mapped_column(Integer, nullable=False, default=3)
    status: Mapped[str] = mapped_column(String(16), nullable=False, default="active")
    superseded_by: Mapped[Optional[int]] = mapped_column(
        Integer, ForeignKey("ltm_notes.id", ondelete="SET NULL"), nullable=True
    )
    source_session_id: Mapped[Optional[str]] = mapped_column(String(64))
    source_message_id: Mapped[Optional[str]] = mapped_column(String(64))
    embedding: Mapped[Optional[bytes]] = mapped_column(LargeBinary, nullable=True)
    confidence: Mapped[float] = mapped_column(Float, nullable=False, default=1.0)
    confidence_source: Mapped[Optional[str]] = mapped_column(String(24))
    valid_from: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    valid_to: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_recalled_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True)
    )
    recall_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "tenant_id",
            "scope_type",
            "scope_key",
            "seq",
            name="uq_ltm_notes_scope_seq",
        ),
        Index(
            "ix_ltm_notes_scope_status",
            "tenant_id",
            "scope_type",
            "scope_key",
            "status",
        ),
    )


class LtmDigest(Base):
    __tablename__ = "ltm_digests"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    scope_type: Mapped[str] = mapped_column(String(16), nullable=False)
    scope_key: Mapped[str] = mapped_column(String(256), nullable=False)
    level: Mapped[int] = mapped_column(Integer, nullable=False)
    range_start_seq: Mapped[int] = mapped_column(Integer, nullable=False)
    range_end_seq: Mapped[int] = mapped_column(Integer, nullable=False)
    summary_text: Mapped[Optional[str]] = mapped_column(String(500))
    ready: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "tenant_id",
            "scope_type",
            "scope_key",
            "range_start_seq",
            "range_end_seq",
            name="uq_ltm_digests_scope_range",
        ),
    )


# Backward-compatible alias for SQL QueryMemory imports
SqlQueryProject = Project


class TrustedPath(Base):
    __tablename__ = "trusted_paths"
    path: Mapped[str] = mapped_column(String(1024), primary_key=True)
    added_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class EvalRun(Base):
    __tablename__ = "eval_runs"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    dataset_name: Mapped[str] = mapped_column(String(256), nullable=False)
    profile_name: Mapped[str] = mapped_column(String(256), nullable=False)
    benchmark_id: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    status: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
    config_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    metrics_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    log_path: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    pid: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    started_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    finished_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    overall_score: Mapped[Optional[float]] = mapped_column(
        Integer
    )  # scaled to float logic
    metadata_json: Mapped[str] = mapped_column("metadata", Text, default="{}")


class EvalResult(Base):
    __tablename__ = "eval_results"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    run_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("eval_runs.id", ondelete="CASCADE"), nullable=False
    )
    case_id: Mapped[str] = mapped_column(String(256), nullable=False)
    input_text: Mapped[str] = mapped_column(Text, nullable=False)
    expected_output: Mapped[Optional[str]] = mapped_column(Text)
    actual_output: Mapped[Optional[str]] = mapped_column(Text)
    score: Mapped[float] = mapped_column(Integer, nullable=False)
    reasoning: Mapped[Optional[str]] = mapped_column(Text)
    latency_sec: Mapped[Optional[float]] = mapped_column(Integer)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class ExecutionPlanRecord(Base):
    """Piano orchestrazione (draft / approvato / rifiutato)."""

    __tablename__ = "execution_plans"
    plan_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    session_id: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    user_id: Mapped[str] = mapped_column(String(256), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    draft_json: Mapped[str] = mapped_column(Text, nullable=False)
    draft_markdown: Mapped[Optional[str]] = mapped_column(Text)
    approved_json: Mapped[Optional[str]] = mapped_column(Text)
    approved_markdown: Mapped[Optional[str]] = mapped_column(Text)
    annotations_json: Mapped[Optional[str]] = mapped_column("annotations", Text)
    todos_json: Mapped[Optional[str]] = mapped_column("todos", Text)
    revision: Mapped[int] = mapped_column(Integer, default=1)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    approved_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    audit_meta_json: Mapped[Optional[str]] = mapped_column("audit_meta", Text)


class OrchestrationAudit(Base):
    """Audit approve/reject / timeout piani."""

    __tablename__ = "orchestration_audit"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    plan_id: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    session_id: Mapped[str] = mapped_column(String(128), nullable=False)
    actor: Mapped[str] = mapped_column(String(256), nullable=False)
    action: Mapped[str] = mapped_column(String(64), nullable=False)
    payload_json: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class SecurityScan(Base):
    """Storico delle scansioni di sicurezza statiche (Antivirus)."""

    __tablename__ = "security_scans"
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    timestamp: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    target_path: Mapped[str] = mapped_column(String(1024), nullable=False)
    is_safe: Mapped[bool] = mapped_column(Boolean, nullable=False)
    results_json: Mapped[str] = mapped_column("results", Text, nullable=False)


class McpServerConfig(Base):
    """Admin: quali server MCP sono esposti agli utenti e schema credenziali."""

    __tablename__ = "mcp_server_configs"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    server_slug: Mapped[str] = mapped_column(String(128), nullable=False, unique=True)
    display_name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text)
    icon_url: Mapped[Optional[str]] = mapped_column(String(512))
    is_enabled_for_users: Mapped[bool] = mapped_column(
        Boolean, default=False, nullable=False
    )
    requires_user_credentials: Mapped[bool] = mapped_column(
        Boolean, default=False, nullable=False
    )
    # none | org_shared | per_user — allineato al catalogo connettori e registry env
    credential_mode: Mapped[str] = mapped_column(
        String(32), default="none", nullable=False
    )
    aion_connector_id: Mapped[Optional[str]] = mapped_column(String(64))
    user_may_disable: Mapped[bool] = mapped_column(
        Boolean, default=True, nullable=False
    )
    credential_schema_json: Mapped[Optional[str]] = mapped_column(
        "credential_schema", Text
    )
    # client_secret cifrato a riposo (AION_CREDENTIAL_ENCRYPTION_KEY)
    oauth_config_json: Mapped[Optional[str]] = mapped_column(
        "oauth_config", EncryptedOAuthConfigJSON
    )
    category: Mapped[Optional[str]] = mapped_column(String(64))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class UserMcpPreference(Base):
    """Preferenza utente: integrazione MCP opzionale attiva/disattiva."""

    __tablename__ = "user_mcp_preferences"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    user_id: Mapped[str] = mapped_column(String(256), nullable=False, index=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    server_slug: Mapped[str] = mapped_column(String(128), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "user_id",
            "tenant_id",
            "server_slug",
            name="uq_user_mcp_preference",
        ),
    )


class ScheduledJob(Base):
    """Cron job per-utente (prompt ricorrente verso l'agente)."""

    __tablename__ = "scheduled_jobs"
    job_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default", index=True
    )
    user_id: Mapped[str] = mapped_column(String(256), nullable=False, index=True)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text)
    cron_expression: Mapped[str] = mapped_column(String(128), nullable=False)
    timezone: Mapped[str] = mapped_column(String(64), nullable=False, default="UTC")
    profile_slug: Mapped[str] = mapped_column(String(256), nullable=False)
    prompt: Mapped[str] = mapped_column(Text, nullable=False)
    session_mode: Mapped[str] = mapped_column(
        String(16), nullable=False, default="fixed"
    )
    session_id: Mapped[Optional[str]] = mapped_column(String(128))
    sql_query_project: Mapped[Optional[str]] = mapped_column(String(128))
    enabled: Mapped[bool] = mapped_column(
        Boolean, default=True, nullable=False, index=True
    )
    agent_mode: Mapped[str] = mapped_column(
        String(32), nullable=False, default="normal"
    )
    metadata_json: Mapped[Optional[str]] = mapped_column("metadata", Text)
    created_by: Mapped[str] = mapped_column(String(32), nullable=False, default="user")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )
    next_run_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), index=True
    )

    runs: Mapped[list["ScheduledJobRun"]] = relationship(
        back_populates="job", cascade="all, delete-orphan"
    )


class ScheduledJobRun(Base):
    """Esecuzione di un scheduled job."""

    __tablename__ = "scheduled_job_runs"
    run_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    job_id: Mapped[str] = mapped_column(
        String(64),
        ForeignKey("scheduled_jobs.job_id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    started_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, index=True
    )
    finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    session_id: Mapped[Optional[str]] = mapped_column(String(128))
    conversation_id: Mapped[Optional[str]] = mapped_column(String(128))
    error_message: Mapped[Optional[str]] = mapped_column(Text)
    assistant_preview: Mapped[Optional[str]] = mapped_column(Text)

    job: Mapped["ScheduledJob"] = relationship(back_populates="runs")


class UserMcpCredential(Base):
    """Credenziali MCP per utente (valore cifrato)."""

    __tablename__ = "user_mcp_credentials"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    user_id: Mapped[str] = mapped_column(String(256), nullable=False, index=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    server_slug: Mapped[str] = mapped_column(String(128), nullable=False)
    credential_key: Mapped[str] = mapped_column(String(128), nullable=False)
    value_encrypted: Mapped[str] = mapped_column(Text, nullable=False)
    display_hint: Mapped[Optional[str]] = mapped_column(String(128))
    expires_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "user_id",
            "tenant_id",
            "server_slug",
            "credential_key",
            name="uq_user_mcp_credential",
        ),
    )


class LlmProvider(Base):
    """Provider LLM configurabile (multi-tenant, multi-model)."""

    __tablename__ = "llm_providers"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default"
    )
    slug: Mapped[str] = mapped_column(String(128), nullable=False)
    display_name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text)
    icon_url: Mapped[Optional[str]] = mapped_column(String(512))
    provider: Mapped[str] = mapped_column(String(64), nullable=False)
    model_name: Mapped[str] = mapped_column(String(256), nullable=False)
    api_base_url: Mapped[Optional[str]] = mapped_column(String(1024))
    api_key_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    timeout: Mapped[int] = mapped_column(Integer, default=120)
    max_chat_tokens: Mapped[Optional[int]] = mapped_column(Integer)
    thinking_token_budget: Mapped[Optional[int]] = mapped_column(Integer)
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    is_default: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    metadata_json: Mapped[str] = mapped_column("metadata", Text, default="{}")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint("tenant_id", "slug", name="uq_llm_provider_tenant_slug"),
    )


class UserProfileAccess(Base):
    """Mappa l'accesso di ogni utente ai soli profili selezionati."""

    __tablename__ = "user_profile_access"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[str] = mapped_column(
        String(64),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    tenant_id: Mapped[str] = mapped_column(
        String(64), nullable=False, default="default", server_default="default"
    )
    profile_slug: Mapped[str] = mapped_column(String(256), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "user_id",
            "tenant_id",
            "profile_slug",
            name="uq_user_profile_access",
        ),
    )


class SsoProvider(Base):
    """Configurazione provider SSO (Microsoft/Google) per tenant."""

    __tablename__ = "sso_providers"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), nullable=False
    )
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    client_id: Mapped[str] = mapped_column(String(256), nullable=False)
    client_secret_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    directory_tenant_id: Mapped[Optional[str]] = mapped_column(String(64))
    allowed_domains: Mapped[Optional[str]] = mapped_column(Text)
    auto_provision: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="1"
    )
    default_roles: Mapped[str] = mapped_column(
        Text, default='["user"]', server_default='["user"]'
    )
    enabled: Mapped[bool] = mapped_column(Boolean, default=False, server_default="0")
    validated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    validated_by_user_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("users.id")
    )
    client_secret_expires_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True)
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "tenant_id", "provider", name="uq_sso_providers_tenant_provider"
        ),
    )


class UserSsoIdentity(Base):
    """Collegamento tra utente AION e identità esterna SSO."""

    __tablename__ = "user_sso_identities"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(String(64), nullable=False)
    user_id: Mapped[str] = mapped_column(
        String(64),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    subject: Mapped[str] = mapped_column(String(256), nullable=False)
    email: Mapped[Optional[str]] = mapped_column(String(256))
    display_name: Mapped[Optional[str]] = mapped_column(String(256))
    access_token_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    token_expires_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True)
    )
    scope: Mapped[Optional[str]] = mapped_column(Text)
    last_login_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint(
            "tenant_id",
            "provider",
            "subject",
            name="uq_user_sso_identities_provider_subject",
        ),
        # Fix 0.10: impedisce a un utente di avere due identità dello stesso provider.
        UniqueConstraint(
            "tenant_id",
            "user_id",
            "provider",
            name="uq_user_sso_identities_user_provider",
        ),
    )


class SsoAuthState(Base):
    """Stati di autorizzazione OAuth per l'uso durante il login."""

    __tablename__ = "sso_auth_states"

    state_hash: Mapped[str] = mapped_column(String(64), primary_key=True)
    kind: Mapped[str] = mapped_column(String(32), nullable=False)
    purpose: Mapped[str] = mapped_column(String(32), nullable=False)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    nonce: Mapped[Optional[str]] = mapped_column(String(64))
    code_verifier_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    user_id: Mapped[Optional[str]] = mapped_column(String(64))
    return_to: Mapped[Optional[str]] = mapped_column(String(2048))
    payload_json: Mapped[Optional[str]] = mapped_column(Text)
    expires_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )
    consumed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))


class AuthSettings(Base):
    """Impostazioni di autenticazione per tenant (§1.1).

    Una riga per tenant. Fonte di verità per login_mode.
    """

    __tablename__ = "auth_settings"

    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), primary_key=True
    )
    # Valori: 'password', 'microsoft', 'google'
    login_mode: Mapped[str] = mapped_column(
        String(16), default="password", server_default="password", nullable=False
    )
    # Valori: 'first_setup', 'migration', oppure NULL
    sso_origin: Mapped[Optional[str]] = mapped_column(String(16), nullable=True)
    migration_started_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    # Solo informativo; la visibilità è calcolata in tempo reale
    migration_completed_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    # Se True, cancella password_hash al link (solo per non-admin)
    clear_password_on_link: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="1", nullable=False
    )
    # 2FA TOTP obbligatorio per il login con password (migration v4w5x6y024)
    totp_required: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="0", nullable=False
    )
    totp_required_changed_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    # Chi puo' ottenere un nuovo account al primo login SSO (migration z8a9b0c028).
    # Valori: 'open', 'allowlist', 'closed'
    sso_signup_policy: Mapped[str] = mapped_column(
        String(16), default="open", server_default="open", nullable=False
    )
    # JSON list di email complete o domini "@azienda.it" (solo con 'allowlist')
    sso_signup_allowlist: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    updated_by_user_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("users.id"), nullable=True
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class WhatsAppChannel(Base):
    """Configurazione del canale WhatsApp (Cloud API) per tenant.

    Una riga per tenant: il numero aziendale collegato all'app Meta del cliente.
    Token, app secret e verify token sono cifrati con ``credential_store``.
    """

    __tablename__ = "whatsapp_channels"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), nullable=False, unique=True
    )
    enabled: Mapped[bool] = mapped_column(Boolean, default=False, server_default="0")
    waba_id: Mapped[Optional[str]] = mapped_column(String(64))
    phone_number_id: Mapped[Optional[str]] = mapped_column(String(64), index=True)
    display_phone_number: Mapped[Optional[str]] = mapped_column(String(32))
    verified_name: Mapped[Optional[str]] = mapped_column(String(256))
    access_token_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    app_secret_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    verify_token_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    default_profile_slug: Mapped[Optional[str]] = mapped_column(String(128))
    # Thinking level for WhatsApp turns: off | min | medium | max.
    # NULL = system default (AION_DEFAULT_REASONING_EFFORT / AION_THINKING_ENABLED).
    default_thinking_level: Mapped[Optional[str]] = mapped_column(String(8))
    validated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    validated_by_user_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("users.id", ondelete="SET NULL")
    )
    webhook_verified_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True)
    )
    last_webhook_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_error: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class WhatsAppIdentity(Base):
    """Collegamento tra un utente WhatsApp (BSUID) e un utente AION."""

    __tablename__ = "whatsapp_identities"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(String(64), nullable=False)
    # Business-scoped user ID di Meta; il numero puo' mancare (username WhatsApp).
    bsuid: Mapped[str] = mapped_column(String(128), nullable=False)
    phone_e164: Mapped[Optional[str]] = mapped_column(String(32))
    wa_profile_name: Mapped[Optional[str]] = mapped_column(String(256))
    user_id: Mapped[str] = mapped_column(
        String(64),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    active_conversation_id: Mapped[Optional[str]] = mapped_column(String(64))
    linked_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    last_inbound_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    # JSON list of AttachmentRef dicts (+ ``added_at``) for media received
    # without a caption: they are attached to the user's next text message.
    pending_attachments_json: Mapped[Optional[str]] = mapped_column(Text)

    __table_args__ = (
        UniqueConstraint("tenant_id", "bsuid", name="uq_whatsapp_identities_bsuid"),
        UniqueConstraint("tenant_id", "user_id", name="uq_whatsapp_identities_user"),
    )


class WhatsAppLinkCode(Base):
    """Codici monouso ``COLLEGA <codice>`` generati dalla chat-ui."""

    __tablename__ = "whatsapp_link_codes"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(String(64), nullable=False)
    user_id: Mapped[str] = mapped_column(
        String(64),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    # SHA-256 hex del codice normalizzato (maiuscolo, senza spazi).
    code_hash: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    expires_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )
    used_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class WhatsAppMessage(Base):
    """Messaggi WhatsApp in entrata/uscita: dedup dei webhook e stato di consegna."""

    __tablename__ = "whatsapp_messages"

    wamid: Mapped[str] = mapped_column(String(256), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(String(64), nullable=False)
    # 'in' | 'out'
    direction: Mapped[str] = mapped_column(String(8), nullable=False)
    bsuid: Mapped[Optional[str]] = mapped_column(String(128))
    conversation_id: Mapped[Optional[str]] = mapped_column(String(64))
    # in: received/processing/done/failed — out: sent/delivered/read/failed
    status: Mapped[str] = mapped_column(String(16), nullable=False)
    error_code: Mapped[Optional[str]] = mapped_column(String(32))
    # Solo inbound, svuotato a fine elaborazione.
    payload_json: Mapped[Optional[str]] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (Index("ix_whatsapp_messages_status", "direction", "status"),)


class SmtpSettings(Base):
    """Server SMTP per le email di piattaforma (migration a9b0c1d029).

    Una riga per tenant. La password e' cifrata con credential_store.
    """

    __tablename__ = "smtp_settings"

    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), primary_key=True
    )
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, server_default="1")
    host: Mapped[Optional[str]] = mapped_column(String(256))
    port: Mapped[int] = mapped_column(Integer, default=587, server_default="587")
    # Valori: 'starttls', 'ssl', 'none'
    security: Mapped[str] = mapped_column(
        String(16), default="starttls", server_default="starttls", nullable=False
    )
    username: Mapped[Optional[str]] = mapped_column(String(256))
    password_encrypted: Mapped[Optional[str]] = mapped_column(Text)
    from_address: Mapped[Optional[str]] = mapped_column(String(256))
    from_name: Mapped[Optional[str]] = mapped_column(String(256))
    reply_to: Mapped[Optional[str]] = mapped_column(String(256))
    # Valori: 'it', 'en'
    email_language: Mapped[str] = mapped_column(
        String(8), default="it", server_default="it", nullable=False
    )
    last_test_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_test_ok: Mapped[Optional[bool]] = mapped_column(Boolean)
    last_test_error: Mapped[Optional[str]] = mapped_column(String(64))
    updated_by_user_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("users.id"), nullable=True
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class SmbSettings(Base):
    """Global switch and limits of the ``smb_files`` MCP connector (migration e2f3a4b033).

    One row per tenant, written by the admin API (``src/api/admin_smb.py``) and
    read by the MCP server through ``src/tools/smb_files/settings_store.py``.
    The file servers themselves live in :class:`SmbServer` (migration
    f3a4b5c034).  No secret here: each user's Windows credentials live in the
    per-user credential store and reach the MCP process as env at spawn time.
    """

    __tablename__ = "smb_settings"

    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), primary_key=True
    )
    # Disabled by default: the connector stays off until the admin opts in.
    enabled: Mapped[bool] = mapped_column(Boolean, default=False, server_default="0")
    max_read_mb: Mapped[int] = mapped_column(Integer, default=25, server_default="25")
    max_write_mb: Mapped[int] = mapped_column(Integer, default=25, server_default="25")
    # How long a local AD-lockout block lasts (see smb_files/auth_guard.py).
    lockout_minutes: Mapped[int] = mapped_column(
        Integer, default=30, server_default="30"
    )
    updated_by_user_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("users.id"), nullable=True
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class SmbServer(Base):
    """One Windows file server reachable through ``smb_files`` (migration f3a4b5c034).

    The agent addresses it as ``<alias>/<Share>/<path>``.  Servers sharing a
    ``credential_set`` (typically: the same AD domain) share the user's
    credentials; ``DEFAULT`` maps to the ``SMB_USERNAME``/``SMB_PASSWORD``
    keys, any other set to ``SMB_USERNAME__<SET>``/``SMB_PASSWORD__<SET>``.
    Holds no secret.
    """

    __tablename__ = "smb_servers"
    __table_args__ = (
        UniqueConstraint("tenant_id", "alias", name="uq_smb_servers_tenant_alias"),
        # smbclient caches one session per host: two rows on the same host would
        # silently share a session (and credentials).
        UniqueConstraint("tenant_id", "host", name="uq_smb_servers_tenant_host"),
    )

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(
        String(64), ForeignKey("tenants.id"), nullable=False, index=True
    )
    alias: Mapped[str] = mapped_column(String(32), nullable=False)
    label: Mapped[Optional[str]] = mapped_column(String(128))
    # Stored lowercase: host names are case-insensitive.
    host: Mapped[str] = mapped_column(String(256), nullable=False)
    port: Mapped[int] = mapped_column(Integer, default=445, server_default="445")
    # Used for bare usernames ("mario.rossi" -> "DOMAIN\mario.rossi").
    default_domain: Mapped[Optional[str]] = mapped_column(String(128))
    credential_set: Mapped[str] = mapped_column(
        String(24), default="DEFAULT", server_default="DEFAULT", nullable=False
    )
    # JSON list: [{"name": "Documenti", "read_only": false, "label": "..."}]
    shares_json: Mapped[str] = mapped_column(
        Text, default="[]", server_default="[]", nullable=False
    )
    # Values: 'ntlm', 'negotiate' (negotiate prepares the Kerberos roadmap).
    auth_protocol: Mapped[str] = mapped_column(
        String(16), default="ntlm", server_default="ntlm", nullable=False
    )
    require_encryption: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="0"
    )
    # Lets the admin take one server offline without losing its configuration.
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, server_default="1")
    sort_order: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    last_test_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_test_ok: Mapped[Optional[bool]] = mapped_column(Boolean)
    last_test_error: Mapped[Optional[str]] = mapped_column(String(64))
    updated_by_user_id: Mapped[Optional[str]] = mapped_column(
        String(64), ForeignKey("users.id"), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class PasswordSetupToken(Base):
    """Link monouso inviati via email per impostare la password.

    Si salva solo lo SHA-256 del token: il valore in chiaro esiste solo
    nell'email inviata all'utente (migration a9b0c1d029).
    """

    __tablename__ = "password_setup_tokens"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(String(64), nullable=False)
    user_id: Mapped[str] = mapped_column(
        String(64),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    token_hash: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)
    # Valori: 'invite', 'reset', 'migration'
    purpose: Mapped[str] = mapped_column(String(16), nullable=False)
    # NULL = richiesta self-service ("Password dimenticata?")
    created_by_user_id: Mapped[Optional[str]] = mapped_column(String(64))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    expires_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )
    sent_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    # Codice dell'errore SMTP se l'invio e' fallito (es. smtp_auth_failed)
    send_error: Mapped[Optional[str]] = mapped_column(String(64))
    consumed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
