from __future__ import annotations

import uuid
from datetime import UTC, datetime

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

from memrelay.database import Base


class UTCDateTime(TypeDecorator[datetime]):
    """读取时补回 UTC tzinfo 的 DateTime。

    SQLite 存储 datetime 时丢弃时区信息，读回的是 naive UTC；naive 值经
    isoformat() 序列化后不带偏移，Web 前端 new Date() 与 MCP 客户端会把它
    误当本地时间（显示偏差 8 小时）。统一在类型层补回 UTC，序列化自动携带
    +00:00，也消除 aware/naive 混合比较的隐患。
    """

    impl = DateTime(timezone=True)
    cache_ok = True

    def process_result_value(self, value: datetime | None, dialect: object) -> datetime | None:
        if value is not None and value.tzinfo is None:
            return value.replace(tzinfo=UTC)
        return value


def utcnow() -> datetime:
    return datetime.now(UTC)


def uuid_str() -> str:
    return str(uuid.uuid4())


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)
    updated_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow, onupdate=utcnow)


class Administrator(TimestampMixin, Base):
    __tablename__ = "administrators"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(100), unique=True)
    password_hash: Mapped[str] = mapped_column(Text)


class WebSession(Base):
    __tablename__ = "web_sessions"

    id: Mapped[str] = mapped_column(String(96), primary_key=True)
    administrator_id: Mapped[int] = mapped_column(
        ForeignKey("administrators.id", ondelete="CASCADE"), index=True
    )
    usage_profile_id: Mapped[str | None] = mapped_column(
        ForeignKey("usage_profiles.id", ondelete="SET NULL"), index=True
    )
    csrf_digest: Mapped[str] = mapped_column(String(64))
    expires_at: Mapped[datetime] = mapped_column(UTCDateTime(), index=True)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)
    last_seen_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)


class McpToken(TimestampMixin, Base):
    __tablename__ = "mcp_tokens"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    name: Mapped[str] = mapped_column(String(100))
    token_digest: Mapped[str] = mapped_column(String(64), unique=True, index=True)
    encrypted_token: Mapped[str] = mapped_column(Text)
    access_mode: Mapped[str] = mapped_column(String(16), default="read_write")
    all_profiles: Mapped[bool] = mapped_column(Boolean, default=False)
    revoked: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    last_used_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    usage_profile_id: Mapped[str | None] = mapped_column(
        ForeignKey("usage_profiles.id", ondelete="SET NULL"), index=True
    )


class Project(TimestampMixin, Base):
    __tablename__ = "projects"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    name: Mapped[str] = mapped_column(String(200))
    slug: Mapped[str] = mapped_column(String(200), unique=True, index=True)
    git_remote: Mapped[str | None] = mapped_column(Text, unique=True)
    git_remote_normalized: Mapped[str | None] = mapped_column(Text, unique=True)
    workspace_type: Mapped[str] = mapped_column(String(32), default="general", index=True)
    custom_curation_template: Mapped[str | None] = mapped_column(Text)
    curation_enabled: Mapped[bool] = mapped_column(Boolean, default=True, index=True)
    source_strategy: Mapped[str] = mapped_column(String(20), default="auto")
    source_branch: Mapped[str | None] = mapped_column(String(200))
    source_credential_item_id: Mapped[str | None] = mapped_column(String(100))
    source_last_commit: Mapped[str | None] = mapped_column(String(64))
    source_last_branch: Mapped[str | None] = mapped_column(String(200))
    source_last_fetched_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    source_last_error: Mapped[str | None] = mapped_column(Text)
    source_cache_used: Mapped[bool] = mapped_column(Boolean, default=False)
    last_agent_sync_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    last_agent_branch: Mapped[str | None] = mapped_column(String(200))
    last_agent_commit: Mapped[str | None] = mapped_column(String(64))
    last_agent_dirty: Mapped[bool | None] = mapped_column(Boolean)
    archived: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    aliases: Mapped[list[ProjectAlias]] = relationship(
        back_populates="project", cascade="all, delete-orphan"
    )


class ProjectAlias(TimestampMixin, Base):
    __tablename__ = "project_aliases"
    __table_args__ = (UniqueConstraint("kind", "value", name="uq_project_alias_kind_value"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    project_id: Mapped[str] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )
    kind: Mapped[str] = mapped_column(String(32))
    value: Mapped[str] = mapped_column(Text)
    project: Mapped[Project] = relationship(back_populates="aliases")


class MemoryReference(TimestampMixin, Base):
    __tablename__ = "memory_references"
    __table_args__ = (Index("ix_memory_scope_project_type", "scope", "project_id", "memory_type"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL"), index=True
    )
    usage_profile_id: Mapped[str | None] = mapped_column(
        ForeignKey("usage_profiles.id", ondelete="SET NULL"), index=True
    )
    scope: Mapped[str] = mapped_column(String(16), index=True)
    memory_type: Mapped[str] = mapped_column(String(32), index=True)
    path: Mapped[str] = mapped_column(Text, unique=True)
    title: Mapped[str] = mapped_column(String(300))
    revision: Mapped[int] = mapped_column(Integer, default=1)
    status: Mapped[str] = mapped_column(String(32), default="active", index=True)
    tags_json: Mapped[str] = mapped_column(Text, default="[]")
    curation_status: Mapped[str] = mapped_column(String(32), default="pending", index=True)
    # Why covered_reason: distinguishes "cited by a curated document" from "reviewed by the
    # model with nothing to extract" so the UI can explain why a memory left the pending list.
    covered_reason: Mapped[str | None] = mapped_column(String(16))
    curated_revision: Mapped[int | None] = mapped_column(Integer)
    curated_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    covered_by_json: Mapped[str] = mapped_column(Text, default="[]")
    superseded_by_json: Mapped[str] = mapped_column(Text, default="[]")
    latest_curation_job_id: Mapped[str | None] = mapped_column(String(36))
    # Reviewed marks are durable job outcomes, not derived state: a model may declare a source
    # redundant / no_action without citing it in any document, so no document metadata can carry
    # the mark. Rebuild treats a matching reviewed_revision as covered with reason "reviewed".
    reviewed_revision: Mapped[int | None] = mapped_column(Integer)
    reviewed_job_id: Mapped[str | None] = mapped_column(String(36))


class IdempotencyRecord(Base):
    __tablename__ = "idempotency_records"
    __table_args__ = (UniqueConstraint("operation", "request_id", name="uq_idempotency_request"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    operation: Mapped[str] = mapped_column(String(100))
    request_id: Mapped[str] = mapped_column(String(100))
    response_json: Mapped[str] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)


class VaultConnection(TimestampMixin, Base):
    __tablename__ = "vault_connections"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, default=1)
    encrypted_server_url: Mapped[str] = mapped_column(Text)
    encrypted_account: Mapped[str] = mapped_column(Text)
    encrypted_password: Mapped[str] = mapped_column(Text)
    login_method: Mapped[str] = mapped_column(String(16), default="password")
    encrypted_client_secret: Mapped[str | None] = mapped_column(Text)
    enabled: Mapped[bool] = mapped_column(Boolean, default=True)
    status: Mapped[str] = mapped_column(String(32), default="pending")
    last_sync_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    last_error: Mapped[str | None] = mapped_column(Text)


class CredentialMapping(TimestampMixin, Base):
    __tablename__ = "credential_mappings"
    __table_args__ = (UniqueConstraint("lookup_key", name="uq_credential_mapping_lookup"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    lookup_key: Mapped[str] = mapped_column(String(300), index=True)
    vault_item_id: Mapped[str] = mapped_column(String(100))


class FileRecord(TimestampMixin, Base):
    __tablename__ = "file_records"
    __table_args__ = (Index("ix_file_project_status", "project_id", "status"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL"), index=True
    )
    storage_path: Mapped[str] = mapped_column(Text, unique=True)
    name: Mapped[str] = mapped_column(String(300), index=True)
    is_directory: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    description: Mapped[str | None] = mapped_column(Text)
    version: Mapped[str | None] = mapped_column(String(100), index=True)
    purpose: Mapped[str | None] = mapped_column(String(200), index=True)
    tags_json: Mapped[str] = mapped_column(Text, default="[]")
    mime_type: Mapped[str] = mapped_column(String(200), default="application/octet-stream")
    size: Mapped[int] = mapped_column(Integer)
    checksum_sha256: Mapped[str] = mapped_column(String(64), index=True)
    status: Mapped[str] = mapped_column(String(32), default="available", index=True)
    modified_at: Mapped[datetime] = mapped_column(UTCDateTime())


class UploadTask(TimestampMixin, Base):
    __tablename__ = "upload_tasks"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    target_path: Mapped[str] = mapped_column(Text)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL"), index=True
    )
    usage_profile_id: Mapped[str | None] = mapped_column(
        ForeignKey("usage_profiles.id", ondelete="SET NULL"), index=True
    )
    origin: Mapped[str] = mapped_column(String(20), default="user")
    expected_size: Mapped[int] = mapped_column(Integer)
    expected_sha256: Mapped[str | None] = mapped_column(String(64))
    description: Mapped[str | None] = mapped_column(Text)
    version: Mapped[str | None] = mapped_column(String(100))
    purpose: Mapped[str | None] = mapped_column(String(200))
    tags_json: Mapped[str] = mapped_column(Text, default="[]")
    overwrite_allowed: Mapped[bool] = mapped_column(Boolean, default=False)
    token_digest: Mapped[str] = mapped_column(String(64), unique=True, index=True)
    expires_at: Mapped[datetime] = mapped_column(UTCDateTime(), index=True)
    status: Mapped[str] = mapped_column(String(32), default="pending", index=True)
    error_message: Mapped[str | None] = mapped_column(Text)
    file_id: Mapped[str | None] = mapped_column(ForeignKey("file_records.id", ondelete="SET NULL"))


class SystemSetting(TimestampMixin, Base):
    __tablename__ = "system_settings"

    key: Mapped[str] = mapped_column(String(100), primary_key=True)
    value_json: Mapped[str] = mapped_column(Text)


class UsageProfile(TimestampMixin, Base):
    __tablename__ = "usage_profiles"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    name: Mapped[str] = mapped_column(String(100), unique=True)
    description: Mapped[str | None] = mapped_column(Text)
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, index=True)
    is_shared: Mapped[bool] = mapped_column(Boolean, default=False)


class CurationSettings(TimestampMixin, Base):
    __tablename__ = "curation_settings"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, default=1)
    enabled: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    timezone: Mapped[str] = mapped_column(String(100), default="Asia/Shanghai")
    output_language: Mapped[str] = mapped_column(String(16), default="auto")
    context_input_tokens: Mapped[int] = mapped_column(Integer, default=32000)
    context_output_tokens: Mapped[int] = mapped_column(Integer, default=32000)
    task_max_calls: Mapped[int] = mapped_column(Integer, default=20)
    task_max_tokens: Mapped[int] = mapped_column(Integer, default=200000)
    batch_chars: Mapped[int] = mapped_column(Integer, default=80000)
    max_merge_levels: Mapped[int] = mapped_column(Integer, default=4)
    max_concurrency: Mapped[int] = mapped_column(Integer, default=1)
    source_freshness_hours: Mapped[int] = mapped_column(Integer, default=24)
    retry_initial_seconds: Mapped[int] = mapped_column(Integer, default=300)
    retry_max_seconds: Mapped[int] = mapped_column(Integer, default=3600)
    max_source_files: Mapped[int] = mapped_column(Integer, default=1000)
    max_source_chars: Mapped[int] = mapped_column(Integer, default=20_000_000)
    text_file_max_bytes: Mapped[int] = mapped_column(Integer, default=2 * 1024 * 1024)
    rich_file_max_bytes: Mapped[int] = mapped_column(Integer, default=50 * 1024 * 1024)


class CurationPolicyOverride(TimestampMixin, Base):
    __tablename__ = "curation_policy_overrides"

    key: Mapped[str] = mapped_column(String(120), primary_key=True)
    scope: Mapped[str] = mapped_column(String(20), index=True)
    workspace_type: Mapped[str | None] = mapped_column(String(32), index=True)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )
    values_json: Mapped[str] = mapped_column(Text, default="{}")


class CurationModelConfig(TimestampMixin, Base):
    __tablename__ = "curation_model_configs"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    revision: Mapped[int] = mapped_column(Integer, unique=True, index=True)
    name: Mapped[str] = mapped_column(String(100))
    base_url: Mapped[str] = mapped_column(Text)
    encrypted_token: Mapped[str | None] = mapped_column(Text)
    text_model: Mapped[str] = mapped_column(String(300))
    vision_model: Mapped[str | None] = mapped_column(String(300))
    protocol: Mapped[str] = mapped_column(String(32), default="auto")
    effective_protocol: Mapped[str | None] = mapped_column(String(32))
    stream: Mapped[bool] = mapped_column(Boolean, default=False)
    timeout_seconds: Mapped[int] = mapped_column(Integer, default=120)
    probe_interval_seconds: Mapped[int] = mapped_column(Integer, default=300)
    management_url: Mapped[str | None] = mapped_column(Text)
    capability_json: Mapped[str] = mapped_column(Text, default="{}")
    # Ordered fallback candidates for the single connection. Each item is
    # {"model": str, "task_classes": [...], "enabled": bool}; empty list means the
    # legacy single text_model chain. Part of the config revision snapshot.
    candidates_json: Mapped[str] = mapped_column(Text, default="[]")
    candidate_cooldown_seconds: Mapped[int] = mapped_column(Integer, default=600)
    active: Mapped[bool] = mapped_column(Boolean, default=True, index=True)


class CurationModelCandidateState(Base):
    """Runtime health per candidate model.

    Kept outside CurationModelConfig on purpose: health is runtime state and must not be
    frozen into config revision snapshots or reset by a config save.
    """

    __tablename__ = "curation_model_candidate_states"

    model_name: Mapped[str] = mapped_column(String(300), primary_key=True)
    cooling_until: Mapped[datetime | None] = mapped_column(UTCDateTime(), index=True)
    last_error_code: Mapped[str | None] = mapped_column(String(100))
    last_error_message: Mapped[str | None] = mapped_column(Text)
    last_success_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    last_failure_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    updated_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow, onupdate=utcnow)


class CurationDependencyState(TimestampMixin, Base):
    __tablename__ = "curation_dependency_state"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, default=1)
    status: Mapped[str] = mapped_column(String(32), default="unconfigured", index=True)
    capability_json: Mapped[str] = mapped_column(Text, default="{}")
    last_check_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    last_success_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    last_failure_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    http_status: Mapped[int | None] = mapped_column(Integer)
    error_code: Mapped[str | None] = mapped_column(String(100))
    error_message: Mapped[str | None] = mapped_column(Text)
    next_probe_at: Mapped[datetime | None] = mapped_column(UTCDateTime(), index=True)


class CurationSchedule(TimestampMixin, Base):
    __tablename__ = "curation_schedules"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    name: Mapped[str] = mapped_column(String(200))
    trigger_type: Mapped[str] = mapped_column(String(40), index=True)
    scope: Mapped[str] = mapped_column(String(16), default="project")
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, index=True)
    inheritance: Mapped[str] = mapped_column(String(20), default="instance")
    timezone: Mapped[str | None] = mapped_column(String(100))
    recurrence_json: Mapped[str] = mapped_column(Text, default="{}")
    missed_policy: Mapped[str] = mapped_column(String(20), default="run_once")
    last_scheduled_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    last_triggered_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    next_run_at: Mapped[datetime | None] = mapped_column(UTCDateTime(), index=True)
    status: Mapped[str] = mapped_column(String(32), default="active")
    last_error: Mapped[str | None] = mapped_column(Text)


class CurationChange(Base):
    __tablename__ = "curation_change_log"

    sequence: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    scope: Mapped[str] = mapped_column(String(16), index=True)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL"), index=True
    )
    source_type: Mapped[str] = mapped_column(String(40), index=True)
    source_id: Mapped[str] = mapped_column(String(100), index=True)
    change_type: Mapped[str] = mapped_column(String(20))
    revision: Mapped[str | None] = mapped_column(String(100))
    content_hash: Mapped[str | None] = mapped_column(String(64))
    target_hint: Mapped[str | None] = mapped_column(String(200))
    summary: Mapped[str | None] = mapped_column(Text)
    usage_profile_id: Mapped[str | None] = mapped_column(
        ForeignKey("usage_profiles.id", ondelete="SET NULL"), index=True
    )
    origin: Mapped[str] = mapped_column(String(20), index=True)
    observed_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow, index=True)


class CurationJob(TimestampMixin, Base):
    __tablename__ = "curation_jobs"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    scope: Mapped[str] = mapped_column(String(16), index=True)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL"), index=True
    )
    usage_profile_id: Mapped[str | None] = mapped_column(
        ForeignKey("usage_profiles.id", ondelete="SET NULL"), index=True
    )
    mode: Mapped[str] = mapped_column(String(20), default="incremental")
    trigger: Mapped[str] = mapped_column(String(40), index=True)
    source_strategy: Mapped[str] = mapped_column(String(20), default="auto")
    target_documents_json: Mapped[str] = mapped_column(Text, default="[]")
    coalesce_key: Mapped[str | None] = mapped_column(String(200), index=True)
    slot: Mapped[str | None] = mapped_column(String(240), unique=True)
    status: Mapped[str] = mapped_column(String(32), default="queued", index=True)
    last_success_cursor: Mapped[int] = mapped_column(Integer, default=0)
    latest_observed_cursor: Mapped[int] = mapped_column(Integer, default=0)
    merged_trigger_count: Mapped[int] = mapped_column(Integer, default=1)
    trigger_summary_json: Mapped[str] = mapped_column(Text, default="{}")
    model_config_id: Mapped[str | None] = mapped_column(
        ForeignKey("curation_model_configs.id", ondelete="SET NULL")
    )
    prompt_version: Mapped[str | None] = mapped_column(String(100))
    schema_version: Mapped[str | None] = mapped_column(String(100))
    lease_owner: Mapped[str | None] = mapped_column(String(100), index=True)
    lease_expires_at: Mapped[datetime | None] = mapped_column(UTCDateTime(), index=True)
    retries: Mapped[int] = mapped_column(Integer, default=0)
    next_retry_at: Mapped[datetime | None] = mapped_column(UTCDateTime(), index=True)
    error_code: Mapped[str | None] = mapped_column(String(100))
    error_message: Mapped[str | None] = mapped_column(Text)
    stats_json: Mapped[str] = mapped_column(Text, default="{}")
    started_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    finished_at: Mapped[datetime | None] = mapped_column(UTCDateTime())


class CurationAttempt(Base):
    __tablename__ = "curation_attempts"
    __table_args__ = (UniqueConstraint("job_id", "attempt_number", name="uq_curation_attempt"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    attempt_key: Mapped[str] = mapped_column(String(64), unique=True, index=True)
    job_id: Mapped[str] = mapped_column(
        ForeignKey("curation_jobs.id", ondelete="CASCADE"), index=True
    )
    attempt_number: Mapped[int] = mapped_column(Integer)
    model_config_id: Mapped[str | None] = mapped_column(
        ForeignKey("curation_model_configs.id", ondelete="SET NULL")
    )
    effective_protocol: Mapped[str | None] = mapped_column(String(32))
    model: Mapped[str | None] = mapped_column(String(300))
    prompt_version: Mapped[str] = mapped_column(String(100), default="1")
    schema_version: Mapped[str] = mapped_column(String(100), default="1")
    budget_json: Mapped[str] = mapped_column(Text, default="{}")
    status: Mapped[str] = mapped_column(String(32), default="running")
    error_code: Mapped[str | None] = mapped_column(String(100))
    error_message: Mapped[str | None] = mapped_column(Text)
    started_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)
    finished_at: Mapped[datetime | None] = mapped_column(UTCDateTime())


class CurationExecutionEvent(Base):
    __tablename__ = "curation_execution_events"
    __table_args__ = (Index("ix_curation_execution_events_job_created", "job_id", "created_at"),)

    sequence: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    job_id: Mapped[str] = mapped_column(
        ForeignKey("curation_jobs.id", ondelete="CASCADE"), index=True
    )
    attempt_id: Mapped[str | None] = mapped_column(
        ForeignKey("curation_attempts.id", ondelete="SET NULL"), index=True
    )
    phase: Mapped[str] = mapped_column(String(32), index=True)
    level: Mapped[str] = mapped_column(String(16), default="info")
    message_code: Mapped[str] = mapped_column(String(80))
    details_json: Mapped[str] = mapped_column(Text, default="{}")
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)


class CuratedDocument(TimestampMixin, Base):
    __tablename__ = "curated_documents"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    scope: Mapped[str] = mapped_column(String(16), index=True)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )
    usage_profile_id: Mapped[str | None] = mapped_column(
        ForeignKey("usage_profiles.id", ondelete="SET NULL"), index=True
    )
    document_type: Mapped[str] = mapped_column(String(100), index=True)
    title: Mapped[str] = mapped_column(String(300))
    path: Mapped[str] = mapped_column(Text, unique=True)
    revision: Mapped[int] = mapped_column(Integer, default=1)
    latest_job_id: Mapped[str | None] = mapped_column(
        ForeignKey("curation_jobs.id", ondelete="SET NULL")
    )
    source_hash: Mapped[str] = mapped_column(String(64))
    source_cursor: Mapped[int] = mapped_column(Integer, default=0)
    metadata_json: Mapped[str] = mapped_column(Text, default="{}")
    model: Mapped[str | None] = mapped_column(String(300))
    prompt_version: Mapped[str] = mapped_column(String(100), default="1")
    status: Mapped[str] = mapped_column(String(32), default="active", index=True)


Index(
    "uq_active_curated_document",
    CuratedDocument.scope,
    func.coalesce(CuratedDocument.project_id, ""),
    func.coalesce(CuratedDocument.usage_profile_id, ""),
    CuratedDocument.document_type,
    unique=True,
    sqlite_where=text("status = 'active'"),
)


class CuratedDocumentRevision(Base):
    __tablename__ = "curated_document_revisions"
    __table_args__ = (
        UniqueConstraint("document_id", "revision", name="uq_curated_document_revision"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    document_id: Mapped[str] = mapped_column(
        ForeignKey("curated_documents.id", ondelete="CASCADE"), index=True
    )
    revision: Mapped[int] = mapped_column(Integer)
    storage_path: Mapped[str] = mapped_column(Text, unique=True)
    job_id: Mapped[str | None] = mapped_column(
        ForeignKey("curation_jobs.id", ondelete="SET NULL"), index=True
    )
    model: Mapped[str | None] = mapped_column(String(300))
    source_hash: Mapped[str] = mapped_column(String(64))
    metadata_json: Mapped[str] = mapped_column(Text, default="{}")
    change_summary: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)


class CurationSource(Base):
    __tablename__ = "curation_sources"
    __table_args__ = (
        UniqueConstraint("job_id", "source_type", "source_id", name="uq_curation_source"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    job_id: Mapped[str] = mapped_column(
        ForeignKey("curation_jobs.id", ondelete="CASCADE"), index=True
    )
    source_type: Mapped[str] = mapped_column(String(40))
    source_id: Mapped[str] = mapped_column(String(100))
    source_revision: Mapped[str | None] = mapped_column(String(100))
    content_hash: Mapped[str | None] = mapped_column(String(64))
    origin: Mapped[str] = mapped_column(String(20))
    sent_to_model: Mapped[bool] = mapped_column(Boolean, default=False)
    disposition: Mapped[str] = mapped_column(String(20), default="processed")
    reason: Mapped[str | None] = mapped_column(Text)


class ModelCall(Base):
    __tablename__ = "curation_model_calls"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    job_id: Mapped[str] = mapped_column(
        ForeignKey("curation_jobs.id", ondelete="CASCADE"), index=True
    )
    attempt_id: Mapped[str | None] = mapped_column(
        ForeignKey("curation_attempts.id", ondelete="SET NULL"), index=True
    )
    purpose: Mapped[str] = mapped_column(String(40))
    protocol: Mapped[str] = mapped_column(String(32))
    stream: Mapped[bool] = mapped_column(Boolean, default=False)
    requested_model: Mapped[str] = mapped_column(String(300))
    response_model: Mapped[str | None] = mapped_column(String(300))
    request_id: Mapped[str | None] = mapped_column(String(300))
    prompt_version: Mapped[str] = mapped_column(String(100))
    input_tokens: Mapped[int | None] = mapped_column(Integer)
    output_tokens: Mapped[int | None] = mapped_column(Integer)
    latency_ms: Mapped[int | None] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(String(32))
    error_code: Mapped[str | None] = mapped_column(String(100))
    started_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)
    finished_at: Mapped[datetime | None] = mapped_column(UTCDateTime())


class GitConnection(TimestampMixin, Base):
    __tablename__ = "git_connections"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    enabled: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    name: Mapped[str] = mapped_column(String(100))
    mode: Mapped[str] = mapped_column(String(20), default="single")
    remote_url: Mapped[str | None] = mapped_column(Text)
    username: Mapped[str | None] = mapped_column(String(500))
    transport: Mapped[str] = mapped_column(String(20), default="https")
    credential_storage: Mapped[str] = mapped_column(String(20), default="local")
    encrypted_credential: Mapped[str | None] = mapped_column(Text)
    vault_item_id: Mapped[str | None] = mapped_column(String(100))
    default_branch: Mapped[str] = mapped_column(String(200), default="main")
    author_name: Mapped[str] = mapped_column(String(200), default="MemRelay")
    author_email: Mapped[str] = mapped_column(String(320), default="memrelay@localhost")
    allow_write_test: Mapped[bool] = mapped_column(Boolean, default=False)
    allow_push_to_create: Mapped[bool] = mapped_column(Boolean, default=True)
    capability_json: Mapped[str] = mapped_column(Text, default="{}")
    last_error: Mapped[str | None] = mapped_column(Text)


class MemoryRepository(TimestampMixin, Base):
    __tablename__ = "memory_repositories"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    connection_id: Mapped[str | None] = mapped_column(
        ForeignKey("git_connections.id", ondelete="SET NULL"), index=True
    )
    mode: Mapped[str] = mapped_column(String(20))
    scope: Mapped[str] = mapped_column(String(16), index=True)
    project_id: Mapped[str | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL"), index=True
    )
    local_path: Mapped[str] = mapped_column(Text, unique=True)
    remote_url: Mapped[str | None] = mapped_column(Text)
    branch: Mapped[str] = mapped_column(String(200), default="main")
    status: Mapped[str] = mapped_column(String(32), default="local", index=True)
    current_commit: Mapped[str | None] = mapped_column(String(64))
    last_pushed_commit: Mapped[str | None] = mapped_column(String(64))
    pending_commits: Mapped[int] = mapped_column(Integer, default=0)
    archived_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    last_error: Mapped[str | None] = mapped_column(Text)


class GitSyncJob(TimestampMixin, Base):
    __tablename__ = "git_sync_jobs"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    operation_id: Mapped[str] = mapped_column(String(100), unique=True, index=True)
    repository_id: Mapped[str] = mapped_column(
        ForeignKey("memory_repositories.id", ondelete="CASCADE"), index=True
    )
    resource_id: Mapped[str | None] = mapped_column(String(100), index=True)
    action: Mapped[str] = mapped_column(String(40))
    summary: Mapped[str] = mapped_column(String(500))
    status: Mapped[str] = mapped_column(String(32), default="pending", index=True)
    target_commit: Mapped[str | None] = mapped_column(String(64))
    attempts: Mapped[int] = mapped_column(Integer, default=0)
    next_retry_at: Mapped[datetime | None] = mapped_column(UTCDateTime(), index=True)
    lease_owner: Mapped[str | None] = mapped_column(String(140), index=True)
    lease_expires_at: Mapped[datetime | None] = mapped_column(UTCDateTime(), index=True)
    error_message: Mapped[str | None] = mapped_column(Text)


class CurationScheduleTrigger(Base):
    __tablename__ = "curation_schedule_triggers"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    schedule_id: Mapped[str] = mapped_column(
        ForeignKey("curation_schedules.id", ondelete="CASCADE"), index=True
    )
    trigger_key: Mapped[str] = mapped_column(String(180), unique=True, index=True)
    scheduled_for: Mapped[datetime] = mapped_column(UTCDateTime(), index=True)
    job_ids_json: Mapped[str] = mapped_column(Text, default="[]")
    status: Mapped[str] = mapped_column(String(32), default="created", index=True)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)


class RuntimeLease(Base):
    __tablename__ = "runtime_leases"

    name: Mapped[str] = mapped_column(String(100), primary_key=True)
    owner: Mapped[str] = mapped_column(String(100), index=True)
    lease_expires_at: Mapped[datetime] = mapped_column(UTCDateTime(), index=True)
    updated_at: Mapped[datetime] = mapped_column(UTCDateTime(), default=utcnow)