/**
 * Workspace tables added by the 2026-09 "AI operating system" upgrade:
 * projects (workspaces), project files (reusable context library), prompt library with variables,
 * user model labels, custom OpenAI-compatible endpoints and Arena votes/criteria.
 *
 * Kept in a separate file so parallel feature work never edits `schema.ts` concurrently.
 * `drizzle.config.ts` globs `schema*.ts`; `src/db/index.ts` re-exports everything.
 */
import { pgTable, text, timestamp, boolean, integer, jsonb, index, uniqueIndex, primaryKey } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { users } from "./schema";

const ts = (name: string) => timestamp(name, { withTimezone: true, mode: "date" });
const now = () => sql`now()`;

// ---------------------------------------------------------------------------
// Projects / workspaces
// ---------------------------------------------------------------------------
export const projects = pgTable(
  "projects",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    description: text("description"),
    /** Emoji or lucide icon name. */
    icon: text("icon"),
    color: text("color"),
    /** Default system instructions prepended to every chat in the project. */
    instructions: text("instructions"),
    /** Preferred model keys (first = default for new chats). */
    preferredModelKeys: jsonb("preferred_model_keys").$type<string[]>().notNull().default([]),
    /** Default generation settings for new chats. */
    defaultSettings: jsonb("default_settings").$type<Record<string, unknown>>().notNull().default({}),
    /** Free-form markdown notes. */
    notes: text("notes"),
    archived: boolean("archived").notNull().default(false),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("projects_user_idx").on(t.userId)],
);

/** Reusable files saved to a project (or to the user's global library when projectId is null). */
export const projectFiles = pgTable(
  "project_files",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    projectId: text("project_id").references(() => projects.id, { onDelete: "cascade" }),
    kind: text("kind").notNull(), // image | pdf | text | code | csv | json | document
    name: text("name").notNull(),
    mimeType: text("mime_type").notNull(),
    sizeBytes: integer("size_bytes").notNull(),
    /** Base64 payload (same storage strategy as message_attachments). */
    dataBase64: text("data_base64").notNull(),
    width: integer("width"),
    height: integer("height"),
    /** Heuristic token estimate computed at upload. */
    estimatedTokens: integer("estimated_tokens"),
    /** Optional user description used when injecting into prompts. */
    description: text("description"),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [index("project_files_user_idx").on(t.userId), index("project_files_project_idx").on(t.projectId)],
);

// ---------------------------------------------------------------------------
// Prompt library (templates with {{variables}})
// ---------------------------------------------------------------------------
export const prompts = pgTable(
  "prompts",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    projectId: text("project_id").references(() => projects.id, { onDelete: "set null" }),
    /** system | user | template | structured */
    kind: text("kind").notNull().default("user"),
    name: text("name").notNull(),
    description: text("description"),
    /** Body with `{{variable}}` placeholders. */
    content: text("content").notNull(),
    /** Declared variables (parsed from content, editable defaults). */
    variables: jsonb("variables").$type<{ name: string; label?: string; default?: string; required?: boolean; options?: string[] }[]>().notNull().default([]),
    /** For `structured`: the JSON schema to request. */
    schema: jsonb("schema").$type<Record<string, unknown> | null>(),
    folder: text("folder"),
    tags: jsonb("tags").$type<string[]>().notNull().default([]),
    favorite: boolean("favorite").notNull().default(false),
    defaultModelKey: text("default_model_key"),
    uses: integer("uses").notNull().default(0),
    lastUsedAt: ts("last_used_at"),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("prompts_user_idx").on(t.userId), index("prompts_user_folder_idx").on(t.userId, t.folder)],
);

// ---------------------------------------------------------------------------
// Model intelligence — user labels & parameter presets scoped to capability sets
// ---------------------------------------------------------------------------
export const userModelLabels = pgTable(
  "user_model_labels",
  {
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    modelKey: text("model_key").notNull(),
    label: text("label").notNull(),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [primaryKey({ columns: [t.userId, t.modelKey] })],
);

// ---------------------------------------------------------------------------
// Custom OpenAI-compatible endpoints (Ollama, LM Studio, vLLM, llama.cpp, MLX…)
// ---------------------------------------------------------------------------
export const customEndpoints = pgTable(
  "custom_endpoints",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    /** Base URL up to and including `/v1` (e.g. http://localhost:11434/v1). */
    baseUrl: text("base_url").notNull(),
    /** Optional API key, AES-256-GCM envelope like provider_connections.encrypted_key. */
    encryptedKey: text("encrypted_key"),
    keyHint: text("key_hint"),
    /** Extra headers (values encrypted as a single JSON envelope). */
    encryptedHeaders: text("encrypted_headers"),
    /** Relative path used for discovery, default `/models`. Empty = manual models only. */
    modelsPath: text("models_path").notNull().default("/models"),
    /** Manually declared models when discovery is unavailable. */
    manualModels: jsonb("manual_models").$type<{ id: string; displayName?: string; contextTokens?: number; vision?: boolean; tools?: boolean; reasoning?: boolean }[]>().notNull().default([]),
    status: text("status").notNull().default("unverified"), // unverified | valid | invalid | error
    lastValidatedAt: ts("last_validated_at"),
    lastValidationError: text("last_validation_error"),
    lastLatencyMs: integer("last_latency_ms"),
    modelsAvailable: integer("models_available"),
    /** Snapshot of the last successful discovery (`GET {baseUrl}{modelsPath}`), so /api/models never calls the endpoint. */
    discoveredModels: jsonb("discovered_models").$type<{ id: string; ownedBy?: string; created?: number }[]>().notNull().default([]),
    discoveredAt: ts("discovered_at"),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("custom_endpoints_user_idx").on(t.userId)],
);

// ---------------------------------------------------------------------------
// Arena — votes per criterion (ratings on arena_responses stay for compatibility)
// ---------------------------------------------------------------------------
export const arenaVotes = pgTable(
  "arena_votes",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    sessionId: text("session_id").notNull(),
    responseId: text("response_id").notNull(),
    modelKey: text("model_key").notNull(),
    /** best | accurate | writing | coding | value | fastest | custom:<slug> */
    criterion: text("criterion").notNull(),
    /** Task category the prompt was classified into (coding | research | writing | reasoning | general). */
    category: text("category"),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [uniqueIndex("arena_votes_session_criterion_uq").on(t.sessionId, t.criterion), index("arena_votes_user_model_idx").on(t.userId, t.modelKey)],
);

// ---------------------------------------------------------------------------
// Public share links for Arena sessions
// ---------------------------------------------------------------------------
export const sharedArenaSessions = pgTable(
  "shared_arena_sessions",
  {
    id: text("id").primaryKey(),
    sessionId: text("session_id").notNull(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    snapshot: jsonb("snapshot").$type<Record<string, unknown>>().notNull(),
    isPublic: boolean("is_public").notNull().default(true),
    viewCount: integer("view_count").notNull().default(0),
    createdAt: ts("created_at").notNull().default(now()),
    revokedAt: ts("revoked_at"),
  },
  (t) => [index("shared_arena_session_idx").on(t.sessionId)],
);

export type Project = typeof projects.$inferSelect;
export type ProjectFile = typeof projectFiles.$inferSelect;
export type Prompt = typeof prompts.$inferSelect;
export type CustomEndpoint = typeof customEndpoints.$inferSelect;
export type ArenaVote = typeof arenaVotes.$inferSelect;