import {
  pgTable,
  text,
  timestamp,
  boolean,
  integer,
  doublePrecision,
  jsonb,
  index,
  uniqueIndex,
  primaryKey,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import type {
  ModelCapabilities,
  ModelLimits,
  ModelParameters,
  ModelPricing,
  ModelStatus,
  ProviderId,
} from "@/lib/ai/core/types";

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

// ---------------------------------------------------------------------------
// Auth (Better Auth compatible — plural table names)
// ---------------------------------------------------------------------------
export const users = pgTable(
  "users",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull().default(""),
    email: text("email").notNull(),
    emailVerified: boolean("email_verified").notNull().default(false),
    image: text("image"),
    role: text("role").notNull().default("user"), // user | admin
    onboardingCompletedAt: ts("onboarding_completed_at"),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [uniqueIndex("users_email_uq").on(t.email)],
);

export const sessions = pgTable(
  "sessions",
  {
    id: text("id").primaryKey(),
    expiresAt: ts("expires_at").notNull(),
    token: text("token").notNull(),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
  },
  (t) => [uniqueIndex("sessions_token_uq").on(t.token), index("sessions_user_idx").on(t.userId)],
);

export const accounts = pgTable(
  "accounts",
  {
    id: text("id").primaryKey(),
    accountId: text("account_id").notNull(),
    providerId: text("provider_id").notNull(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    idToken: text("id_token"),
    accessTokenExpiresAt: ts("access_token_expires_at"),
    refreshTokenExpiresAt: ts("refresh_token_expires_at"),
    scope: text("scope"),
    password: text("password"),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("accounts_user_idx").on(t.userId)],
);

export const verifications = pgTable(
  "verifications",
  {
    id: text("id").primaryKey(),
    identifier: text("identifier").notNull(),
    value: text("value").notNull(),
    expiresAt: ts("expires_at").notNull(),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("verifications_identifier_idx").on(t.identifier)],
);

// ---------------------------------------------------------------------------
// User preferences & audit
// ---------------------------------------------------------------------------
export const userPreferences = pgTable("user_preferences", {
  userId: text("user_id")
    .primaryKey()
    .references(() => users.id, { onDelete: "cascade" }),
  theme: text("theme").notNull().default("system"), // system | light | dark
  language: text("language").notNull().default("en"),
  defaultModelKey: text("default_model_key"),
  defaultSystemPrompt: text("default_system_prompt"),
  enterToSend: boolean("enter_to_send").notNull().default(true),
  streaming: boolean("streaming").notNull().default(true),
  codeWrap: boolean("code_wrap").notNull().default(false),
  showReasoning: boolean("show_reasoning").notNull().default(true),
  showCosts: boolean("show_costs").notNull().default(true),
  autoTitle: boolean("auto_title").notNull().default(true),
  extra: jsonb("extra").$type<Record<string, unknown>>().notNull().default({}),
  updatedAt: ts("updated_at").notNull().default(now()),
});

export const auditEvents = pgTable(
  "audit_events",
  {
    id: text("id").primaryKey(),
    userId: text("user_id").references(() => users.id, { onDelete: "set null" }),
    action: text("action").notNull(),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    meta: jsonb("meta").$type<Record<string, unknown>>().notNull().default({}),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [index("audit_user_created_idx").on(t.userId, t.createdAt)],
);

// ---------------------------------------------------------------------------
// Provider connections (encrypted BYOK keys)
// ---------------------------------------------------------------------------
export const providerConnections = pgTable(
  "provider_connections",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    provider: text("provider").$type<ProviderId>().notNull(),
    /** AES-256-GCM envelope: `v1.<iv>.<tag>.<ciphertext>` (base64url). Never leaves the server. */
    encryptedKey: text("encrypted_key").notNull(),
    /** Last 4 characters of the key for display: `sk-••••9A2K`. */
    keyHint: text("key_hint").notNull(),
    /** SHA-256 of the key — lets us detect "same key re-entered" without decrypting. */
    keyFingerprint: text("key_fingerprint").notNull(),
    status: text("status").notNull().default("unverified"), // unverified | valid | invalid | error
    lastValidatedAt: ts("last_validated_at"),
    lastValidationError: text("last_validation_error"),
    lastSuccessAt: ts("last_success_at"),
    lastErrorAt: ts("last_error_at"),
    lastErrorCode: text("last_error_code"),
    modelsAvailable: integer("models_available"),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [uniqueIndex("provider_connections_user_provider_uq").on(t.userId, t.provider)],
);

// ---------------------------------------------------------------------------
// Model registry (normalized, provider-agnostic)
// ---------------------------------------------------------------------------
export const models = pgTable(
  "models",
  {
    /** `provider/modelId`, e.g. `openai/gpt-5.5`. */
    key: text("key").primaryKey(),
    provider: text("provider").$type<ProviderId>().notNull(),
    modelId: text("model_id").notNull(),
    displayName: text("display_name").notNull(),
    family: text("family"),
    capabilities: jsonb("capabilities").$type<ModelCapabilities>().notNull(),
    limits: jsonb("limits").$type<ModelLimits>().notNull().default({}),
    parameters: jsonb("parameters").$type<ModelParameters>().notNull().default({}),
    pricing: jsonb("pricing").$type<ModelPricing | null>(),
    status: text("status").$type<ModelStatus>().notNull().default("unknown"),
    /** discovery = seen on the provider list API; catalog = documented but not listed; merged = both. */
    source: text("source").notNull().default("catalog"),
    hidden: boolean("hidden").notNull().default(false),
    sortWeight: integer("sort_weight").notNull().default(0),
    metadata: jsonb("metadata").$type<Record<string, unknown>>().notNull().default({}),
    firstSeenAt: ts("first_seen_at").notNull().default(now()),
    lastSeenAt: ts("last_seen_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("models_provider_idx").on(t.provider), index("models_status_idx").on(t.status)],
);

export const modelSyncRuns = pgTable(
  "model_sync_runs",
  {
    id: text("id").primaryKey(),
    provider: text("provider").$type<ProviderId>().notNull(),
    triggeredBy: text("triggered_by").notNull().default("schedule"), // startup | schedule | admin | user
    startedAt: ts("started_at").notNull().default(now()),
    finishedAt: ts("finished_at"),
    ok: boolean("ok"),
    modelsFound: integer("models_found"),
    modelsAdded: integer("models_added"),
    modelsRemoved: integer("models_removed"),
    latencyMs: integer("latency_ms"),
    errorCode: text("error_code"),
    errorMessage: text("error_message"),
  },
  (t) => [index("model_sync_runs_provider_started_idx").on(t.provider, t.startedAt)],
);

export const userModelFavorites = pgTable(
  "user_model_favorites",
  {
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    modelKey: text("model_key").notNull(),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [primaryKey({ columns: [t.userId, t.modelKey] })],
);

export const userModelRecents = pgTable(
  "user_model_recents",
  {
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    modelKey: text("model_key").notNull(),
    usedAt: ts("used_at").notNull().default(now()),
    uses: integer("uses").notNull().default(1),
  },
  (t) => [primaryKey({ columns: [t.userId, t.modelKey] })],
);

// ---------------------------------------------------------------------------
// Conversations
// ---------------------------------------------------------------------------
export const folders = pgTable(
  "folders",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    color: text("color"),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [index("folders_user_idx").on(t.userId)],
);

export const tags = pgTable(
  "tags",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    color: text("color"),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [uniqueIndex("tags_user_name_uq").on(t.userId, t.name)],
);

export const conversations = pgTable(
  "conversations",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    title: text("title").notNull().default("New chat"),
    titleSource: text("title_source").notNull().default("auto"), // auto | user
    folderId: text("folder_id").references(() => folders.id, { onDelete: "set null" }),
    /** Workspace the conversation belongs to (see schema-workspace.ts `projects`). No FK: projects may be deleted independently. */
    projectId: text("project_id"),
    pinned: boolean("pinned").notNull().default(false),
    archived: boolean("archived").notNull().default(false),
    /** Last model used (`provider/modelId`). */
    modelKey: text("model_key"),
    provider: text("provider").$type<ProviderId>(),
    systemPrompt: text("system_prompt"),
    /** Unified generation settings (temperature, maxTokens, reasoningEffort, tools…). */
    settings: jsonb("settings").$type<Record<string, unknown>>().notNull().default({}),
    parentConversationId: text("parent_conversation_id"),
    branchedFromMessageId: text("branched_from_message_id"),
    messageCount: integer("message_count").notNull().default(0),
    totalCostUsd: doublePrecision("total_cost_usd").notNull().default(0),
    totalInputTokens: integer("total_input_tokens").notNull().default(0),
    totalOutputTokens: integer("total_output_tokens").notNull().default(0),
    lastMessageAt: ts("last_message_at"),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [
    index("conversations_user_updated_idx").on(t.userId, t.updatedAt),
    index("conversations_user_folder_idx").on(t.userId, t.folderId),
    index("conversations_user_model_idx").on(t.userId, t.modelKey),
    index("conversations_user_project_idx").on(t.userId, t.projectId),
  ],
);

export const conversationTags = pgTable(
  "conversation_tags",
  {
    conversationId: text("conversation_id")
      .notNull()
      .references(() => conversations.id, { onDelete: "cascade" }),
    tagId: text("tag_id")
      .notNull()
      .references(() => tags.id, { onDelete: "cascade" }),
  },
  (t) => [primaryKey({ columns: [t.conversationId, t.tagId] })],
);

export const messages = pgTable(
  "messages",
  {
    id: text("id").primaryKey(),
    conversationId: text("conversation_id")
      .notNull()
      .references(() => conversations.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    role: text("role").notNull(), // user | assistant | system
    /** Plain markdown text (search + export). Derived from `parts`. */
    content: text("content").notNull().default(""),
    /** Ordered content parts: text, reasoning, tool-call, tool-result, attachment, citation, structured. */
    parts: jsonb("parts").$type<unknown[]>().notNull().default([]),
    modelKey: text("model_key"),
    provider: text("provider").$type<ProviderId>(),
    status: text("status").notNull().default("complete"), // complete | streaming | stopped | error
    finishReason: text("finish_reason"),
    error: jsonb("error").$type<{ code: string; message: string } | null>(),
    usage: jsonb("usage").$type<Record<string, number> | null>(),
    settings: jsonb("settings").$type<Record<string, unknown> | null>(),
    latencyMs: integer("latency_ms"),
    ttftMs: integer("ttft_ms"),
    costUsd: doublePrecision("cost_usd"),
    /** Edits/regenerations: siblings share `parentMessageId`; `version` orders them; `active` marks the shown one. */
    parentMessageId: text("parent_message_id"),
    version: integer("version").notNull().default(1),
    active: boolean("active").notNull().default(true),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [
    index("messages_conversation_created_idx").on(t.conversationId, t.createdAt),
    index("messages_user_idx").on(t.userId),
  ],
);

export const messageAttachments = pgTable(
  "message_attachments",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    messageId: text("message_id").references(() => messages.id, { onDelete: "cascade" }),
    conversationId: text("conversation_id").references(() => conversations.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 (max ~10 MB). Kept in Postgres to avoid an object store on the cluster. */
    dataBase64: text("data_base64").notNull(),
    width: integer("width"),
    height: integer("height"),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [index("attachments_message_idx").on(t.messageId), index("attachments_user_idx").on(t.userId)],
);

export const sharedConversations = pgTable(
  "shared_conversations",
  {
    /** Public slug used in `/share/<id>`. */
    id: text("id").primaryKey(),
    conversationId: text("conversation_id")
      .notNull()
      .references(() => conversations.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    /** Frozen copy of the messages at share time (no live updates, no attachments data). */
    snapshot: jsonb("snapshot").$type<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_conversation_idx").on(t.conversationId)],
);

// ---------------------------------------------------------------------------
// Presets
// ---------------------------------------------------------------------------
export const promptPresets = pgTable(
  "prompt_presets",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    description: text("description"),
    icon: text("icon"),
    systemPrompt: text("system_prompt").notNull(),
    defaultModelKey: text("default_model_key"),
    parameters: jsonb("parameters").$type<Record<string, unknown>>().notNull().default({}),
    tools: jsonb("tools").$type<Record<string, unknown>>().notNull().default({}),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("prompt_presets_user_idx").on(t.userId)],
);

export const modelPresets = pgTable(
  "model_presets",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    description: text("description"),
    icon: text("icon"),
    modelKey: text("model_key").notNull(),
    systemPrompt: text("system_prompt"),
    parameters: jsonb("parameters").$type<Record<string, unknown>>().notNull().default({}),
    tools: jsonb("tools").$type<Record<string, unknown>>().notNull().default({}),
    fileSettings: jsonb("file_settings").$type<Record<string, unknown>>().notNull().default({}),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt: ts("created_at").notNull().default(now()),
    updatedAt: ts("updated_at").notNull().default(now()),
  },
  (t) => [index("model_presets_user_idx").on(t.userId)],
);

// ---------------------------------------------------------------------------
// Usage & Arena
// ---------------------------------------------------------------------------
export const usageRecords = pgTable(
  "usage_records",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    conversationId: text("conversation_id"),
    messageId: text("message_id"),
    arenaSessionId: text("arena_session_id"),
    provider: text("provider").$type<ProviderId>().notNull(),
    modelKey: text("model_key").notNull(),
    kind: text("kind").notNull().default("chat"), // chat | arena | validate | title | structured
    status: text("status").notNull().default("ok"), // ok | error | stopped
    errorCode: text("error_code"),
    inputTokens: integer("input_tokens").notNull().default(0),
    outputTokens: integer("output_tokens").notNull().default(0),
    cachedTokens: integer("cached_tokens").notNull().default(0),
    reasoningTokens: integer("reasoning_tokens").notNull().default(0),
    costUsd: doublePrecision("cost_usd"),
    latencyMs: integer("latency_ms"),
    ttftMs: integer("ttft_ms"),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [
    index("usage_user_created_idx").on(t.userId, t.createdAt),
    index("usage_user_provider_idx").on(t.userId, t.provider),
    index("usage_user_model_idx").on(t.userId, t.modelKey),
  ],
);

export const arenaSessions = pgTable(
  "arena_sessions",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    prompt: text("prompt").notNull(),
    systemPrompt: text("system_prompt"),
    modelKeys: jsonb("model_keys").$type<string[]>().notNull(),
    settings: jsonb("settings").$type<Record<string, unknown>>().notNull().default({}),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [index("arena_sessions_user_idx").on(t.userId, t.createdAt)],
);

export const arenaResponses = pgTable(
  "arena_responses",
  {
    id: text("id").primaryKey(),
    sessionId: text("session_id")
      .notNull()
      .references(() => arenaSessions.id, { onDelete: "cascade" }),
    modelKey: text("model_key").notNull(),
    provider: text("provider").$type<ProviderId>().notNull(),
    content: text("content").notNull().default(""),
    reasoning: text("reasoning"),
    status: text("status").notNull().default("complete"),
    error: jsonb("error").$type<{ code: string; message: string } | null>(),
    usage: jsonb("usage").$type<Record<string, number> | null>(),
    latencyMs: integer("latency_ms"),
    ttftMs: integer("ttft_ms"),
    costUsd: doublePrecision("cost_usd"),
    ratings: jsonb("ratings").$type<Record<string, boolean>>().notNull().default({}),
    createdAt: ts("created_at").notNull().default(now()),
  },
  (t) => [index("arena_responses_session_idx").on(t.sessionId)],
);

export type User = typeof users.$inferSelect;
export type ProviderConnection = typeof providerConnections.$inferSelect;
export type ModelRow = typeof models.$inferSelect;
export type Conversation = typeof conversations.$inferSelect;
export type Message = typeof messages.$inferSelect;
export type MessageAttachment = typeof messageAttachments.$inferSelect;
export type UsageRecord = typeof usageRecords.$inferSelect;
export type PromptPreset = typeof promptPresets.$inferSelect;
export type ModelPreset = typeof modelPresets.$inferSelect;
export type Folder = typeof folders.$inferSelect;
export type UserPreferences = typeof userPreferences.$inferSelect;
export type ArenaSession = typeof arenaSessions.$inferSelect;
export type ArenaResponse = typeof arenaResponses.$inferSelect;