import { sql } from "drizzle-orm";
import {
  index,
  integer,
  real,
  sqliteTable,
  text,
  uniqueIndex,
} from "drizzle-orm/sqlite-core";

export const conversations = sqliteTable(
  "conversations",
  {
    id: text("id").primaryKey(),
    title: text("title"),
    createdAt: integer("created_at").notNull(),
    updatedAt: integer("updated_at").notNull(),
    // Nullable (migration 350): NULL = unknown total; readers coalesce to 0.
    totalInputTokens: integer("total_input_tokens").default(0),
    totalOutputTokens: integer("total_output_tokens").notNull().default(0),
    totalEstimatedCost: real("total_estimated_cost").notNull().default(0),
    contextSummary: text("context_summary"),
    contextCompactedMessageCount: integer("context_compacted_message_count")
      .notNull()
      .default(0),
    contextCompactedAt: integer("context_compacted_at"),
    historyStrippedAt: integer("history_stripped_at"),
    slackContextCompactionWatermarkTs: text(
      "slack_context_compaction_watermark_ts",
    ),
    slackContextCompactionWatermarkAt: integer(
      "slack_context_compaction_watermark_at",
    ),
    conversationType: text("conversation_type").notNull().default("standard"),
    source: text("source").notNull().default("user"),
    originChannel: text("origin_channel"),
    originInterface: text("origin_interface"),
    forkParentConversationId: text("fork_parent_conversation_id"),
    forkParentMessageId: text("fork_parent_message_id"),
    /**
     * How this conversation's fork was materialized: `reference` means the
     * rows at-or-before `forkParentMessageId` live on the parent and are read
     * through it, `cloning` (and NULL, the value every pre-existing fork
     * carries) means they were physically copied onto this row. Read through
     * `isReferentialFork` in `conversation-lineage.ts`, never directly: the
     * NULL-is-cloning default is what keeps existing forks from being read
     * twice, once from their copies and once through their parent.
     */
    forkStrategy: text("fork_strategy"),
    /**
     * Id of the conversation that spawned this one (subagent spawns stamp
     * their parent's conversation id). Distinct from
     * `forkParentConversationId`, which records message-history inheritance;
     * this column records spawn attribution for telemetry. NULL for
     * conversations not spawned by another conversation.
     */
    parentConversationId: text("parent_conversation_id"),
    /**
     * Role the subagent that owns this conversation was spawned with, from
     * `SUBAGENT_ROLE_REGISTRY` (`researcher`, `builder`, `advisor`); a spawn
     * that named no role records the default, `builder`. NULL for every
     * conversation that is not a subagent.
     *
     * Denormalized here rather than read from the `subagents` table at
     * telemetry-flush time on purpose: `subagents` rows are deleted on
     * dispose (TTL sweep ~30 minutes after the run goes terminal), while
     * usage telemetry flushes on a watermark that can trail arbitrarily far
     * behind after an ingest outage. Stamping the conversation row, the same
     * rationale that put `parent_conversation_id` here, makes the
     * attribution durable for the life of the usage row.
     */
    subagentRole: text("subagent_role"),
    /**
     * How the subagent that owns this conversation was spawned: context
     * inheritance and lifecycle, orthogonal to {@link subagentRole}. One of
     * the modes described on `SubagentSpawnMode` in `subagent/types.ts`. NULL
     * for every conversation that is not a subagent.
     */
    subagentSpawnMode: text("subagent_spawn_mode"),
    isAutoTitle: integer("is_auto_title").notNull().default(1),
    scheduleJobId: text("schedule_job_id"),
    lastMessageAt: integer("last_message_at"),
    archivedAt: integer("archived_at"),
    /**
     * Epoch-ms timestamp set when a background/scheduled conversation is
     * explicitly promoted into the sidebar's Recents grouping. NULL (the
     * default) means not surfaced. Set/cleared only via the surface API —
     * never automatically.
     */
    surfacedAt: integer("surfaced_at"),
    inferenceProfile: text("inference_profile"),
    // JSON-encoded string[] of plugin ids scoping this chat; null = default (all globally-enabled).
    enabledPlugins: text("enabled_plugins"),
    inferenceProfileSessionId: text("inference_profile_session_id"),
    inferenceProfileExpiresAt: integer("inference_profile_expires_at"),
    lastNotifiedInferenceProfile: text("last_notified_inference_profile"),
    /**
     * Epoch-ms timestamp set when the agent loop starts a turn for this
     * conversation, cleared (NULL) when the turn ends. NULL means not
     * processing. This is the cross-process source of truth for processing
     * state — the in-memory `Conversation._processing` flag is the hot-path
     * read for resident conversations, but CLI-side and other out-of-process
     * callers read this column directly.
     */
    processingStartedAt: integer("processing_started_at"),
    /**
     * Count of consecutive startup auto-resume attempts for this
     * conversation's interrupted turn. Incremented by the startup reconciler
     * when it wakes a conversation whose `processing_started_at` survived the
     * previous process; reset to 0 whenever a turn ends cleanly. Caps
     * resume-loops for turns that repeatedly take the process down.
     */
    processingResumeAttempts: integer("processing_resume_attempts")
      .notNull()
      .default(0),
    /**
     * Highest stream `seq` whose content is durably persisted to this
     * conversation's message rows. Seeded with the global high-water seq when
     * the row is inserted and advanced on each persistence flush
     * (`recordConversationPersistedSeq`). Returned by `/messages` as the
     * snapshot↔stream alignment baseline so a client applies only stream
     * events with a higher `seq`. NULL means the conversation was created
     * before any stream activity (global seq 0) or predates this column — the
     * client cold-starts in that case.
     */
    seq: integer("seq"),
  },
  (table) => [
    index("idx_conversations_updated_at").on(table.updatedAt),
    index("idx_conversations_last_message_at").on(table.lastMessageAt),
    index("idx_conversations_conversation_type").on(table.conversationType),
    index("idx_conversations_archived_at").on(table.archivedAt),
    index("idx_conversations_surfaced_at").on(table.surfacedAt),
    index("idx_conversations_fork_parent_conversation_id").on(
      table.forkParentConversationId,
    ),
    index("idx_conversations_parent_conversation_id").on(
      table.parentConversationId,
    ),
  ],
);

export const messages = sqliteTable(
  "messages",
  {
    id: text("id").primaryKey(),
    conversationId: text("conversation_id")
      .notNull()
      .references(() => conversations.id, { onDelete: "cascade" }),
    role: text("role").notNull(),
    /**
     * Union of two JSON shapes: an inline `ContentBlock[]` (or a legacy
     * plain string), or `{ ref: "<workspace-relative path>" }` pointing at
     * a file-backed content payload. Resolve to typed blocks with
     * `resolveMessageContentBlocks` (message-content-file.ts) — never
     * interpret this column's shape by hand.
     */
    content: text("content").notNull(),
    createdAt: integer("created_at").notNull(),
    metadata: text("metadata"),
    clientMessageId: text("client_message_id"),
    /**
     * 1 (default) = `content` is the complete, immutable value (inline or
     * `{ ref }`). 0 = the message is still streaming and `content` is a
     * `{ ref }` to its in-flight delta file. Batch readers (search, memory
     * indexing, fork) must filter `finalized = 1`; only the live turn and
     * crash recovery read unfinalized rows.
     */
    finalized: integer("finalized").notNull().default(1),
  },
  (table) => [
    uniqueIndex("idx_messages_conv_client_msg_id")
      .on(table.conversationId, table.clientMessageId)
      .where(sql`client_message_id IS NOT NULL`),
  ],
);

export const toolInvocations = sqliteTable(
  "tool_invocations",
  {
    id: text("id").primaryKey(),
    conversationId: text("conversation_id")
      .notNull()
      .references(() => conversations.id),
    toolName: text("tool_name").notNull(),
    input: text("input").notNull(),
    result: text("result").notNull(),
    decision: text("decision").notNull(),
    riskLevel: text("risk_level").notNull(),
    matchedTrustRuleId: text("matched_trust_rule_id"),
    durationMs: integer("duration_ms").notNull(),
    createdAt: integer("created_at").notNull(),
    /** Serialized input size in bytes, computed before any redaction. Null pre-migration-278. */
    argBytes: integer("arg_bytes"),
    /** Full serialized result size in bytes, computed before truncation/redaction. Null pre-migration-278 and for denied rows. */
    resultBytes: integer("result_bytes"),
    provider: text("provider"),
    model: text("model"),
    inferenceProfile: text("inference_profile"),
    inferenceProfileSource: text("inference_profile_source"),
  },
  (table) => [
    index("idx_tool_invocations_conversation_id").on(table.conversationId),
  ],
);

export const conversationKeys = sqliteTable("conversation_keys", {
  id: text("id").primaryKey(),
  conversationKey: text("conversation_key").notNull(),
  conversationId: text("conversation_id")
    .notNull()
    .references(() => conversations.id, { onDelete: "cascade" }),
  createdAt: integer("created_at").notNull(),
});

export const attachments = sqliteTable("attachments", {
  id: text("id").primaryKey(),
  originalFilename: text("original_filename").notNull(),
  mimeType: text("mime_type").notNull(),
  sizeBytes: integer("size_bytes").notNull(),
  kind: text("kind").notNull(),
  dataBase64: text("data_base64").notNull(),
  contentHash: text("content_hash"),
  thumbnailBase64: text("thumbnail_base64"),
  filePath: text("file_path"),
  createdAt: integer("created_at").notNull(),
});

export const messageAttachments = sqliteTable("message_attachments", {
  id: text("id").primaryKey(),
  messageId: text("message_id")
    .notNull()
    .references(() => messages.id, { onDelete: "cascade" }),
  attachmentId: text("attachment_id")
    .notNull()
    .references(() => attachments.id, { onDelete: "cascade" }),
  position: integer("position").notNull().default(0),
  createdAt: integer("created_at").notNull(),
});

// Per-conversation ConversationGraphMemory + InContextTracker snapshot,
// rehydrated on resume. Lives in the dedicated memory database
// (`assistant-memory.db`), not main — access it via the memory connection
// (`getMemoryDb()` / `getMemorySqlite()`). No FK to conversations.id: SQLite
// foreign keys cannot span database files, so the deleted-conversation cascade
// is replaced by an explicit delete in the memory `conversation-deleted` hook.
export const conversationGraphMemoryState = sqliteTable(
  "conversation_graph_memory_state",
  {
    conversationId: text("conversation_id").primaryKey(),
    stateJson: text("state_json").notNull(),
    createdAt: integer("created_at").notNull(),
    updatedAt: integer("updated_at").notNull(),
  },
);

/**
 * Append-only ledger of every compaction event for a conversation. The
 * `conversations` row keeps only the latest compaction (`context_summary` /
 * `context_compacted_message_count` / `context_compacted_at`) as the hot-path
 * cache the load path reads; this table preserves the full history so a fork
 * can inherit the most recent compaction whose event time (`compacted_at`)
 * is at-or-before the boundary message it forks from.
 */
export const conversationCompactionEvents = sqliteTable(
  "conversation_compaction_events",
  {
    id: text("id").primaryKey(),
    conversationId: text("conversation_id")
      .notNull()
      .references(() => conversations.id, { onDelete: "cascade" }),
    compactedAt: integer("compacted_at").notNull(),
    summary: text("summary").notNull(),
    compactedMessageCount: integer("compacted_message_count").notNull(),
    createdAt: integer("created_at").notNull(),
  },
  (table) => [
    index("idx_compaction_events_conv_at").on(
      table.conversationId,
      table.compactedAt,
    ),
  ],
);

export const channelInboundEvents = sqliteTable("channel_inbound_events", {
  id: text("id").primaryKey(),
  sourceChannel: text("source_channel").notNull(),
  externalChatId: text("external_chat_id").notNull(),
  externalMessageId: text("external_message_id").notNull(),
  sourceMessageId: text("source_message_id"),
  conversationId: text("conversation_id")
    .notNull()
    .references(() => conversations.id, { onDelete: "cascade" }),
  messageId: text("message_id").references(() => messages.id, {
    onDelete: "cascade",
  }),
  deliveryStatus: text("delivery_status").notNull().default("pending"),
  processingStatus: text("processing_status").notNull().default("pending"),
  processingAttempts: integer("processing_attempts").notNull().default(0),
  deliveryAttempts: integer("delivery_attempts").notNull().default(0),
  lastProcessingError: text("last_processing_error"),
  retryAfter: integer("retry_after"),
  rawPayload: text("raw_payload"),
  deliveredSegmentCount: integer("delivered_segment_count")
    .notNull()
    .default(0),
  createdAt: integer("created_at").notNull(),
  updatedAt: integer("updated_at").notNull(),
});