import { pgTable, uuid, text, timestamp, bigint, jsonb, index, uniqueIndex, customType, boolean, check, integer, pgEnum, primaryKey, type AnyPgColumn } from "drizzle-orm/pg-core";

// pgvector vector type — maps to PostgreSQL vector(n) column
const vector = customType<{
  data: number[];
  config: { dimensions: number };
  configRequired: true;
}>({
  dataType(config) {
    return `vector(${config.dimensions})`;
  },
  toDriver(value: number[]) {
    return JSON.stringify(value);
  },
  fromDriver(value: unknown) {
    if (typeof value === "string") return JSON.parse(value) as number[];
    return value as number[];
  },
});

// PostgreSQL tsvector type — used for documents.search_vector full-text search
const tsvector = customType<{ data: string }>({
  dataType() {
    return "tsvector";
  },
});

import { relations, sql } from "drizzle-orm";

// ============================================
// Enums
// ============================================
export const documentVisibilityEnum = pgEnum("document_visibility", ["private", "shared", "public"]);
export const shareRoleEnum = pgEnum("share_role", ["viewer", "commenter", "editor"]);
export const shareAccessModeEnum = pgEnum("share_access_mode", ["public", "restricted"]);
export const shareGrantStatusEnum = pgEnum("share_grant_status", ["pending", "accepted", "revoked"]);
export const embeddingStatusEnum = pgEnum("embedding_status", [
  "pending",
  "processing",
  "ready",
  "failed",
  "stale",
]);
export const pipelineStageEnum = pgEnum("pipeline_stage", [
  "prepare",
  "embed",
  "graph",
  "summarize",
  "finalize",
]);
export const pipelineStatusEnum = pgEnum("pipeline_status", [
  "pending",
  "processing",
  "ready",
  "retrying",
  "failed",
  "ready_with_warnings",
  "skipped",
  "cancelled",
]);
export const lifecycleOperationKindEnum = pgEnum("lifecycle_operation_kind", [
  "export",
  "purge",
]);
export const lifecycleOperationStatusEnum = pgEnum("lifecycle_operation_status", [
  "pending",
  "running",
  "retryable",
  "completed",
  "rejected",
]);

// ============================================
// users — managed by Better Auth
// ============================================
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  name: text("name"),
  emailVerified: boolean("email_verified").default(false),
  image: text("image"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// ============================================
// sessions — managed by Better Auth
// ============================================
export const sessions = pgTable("sessions", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  token: text("token").notNull().unique(),
  expiresAt: timestamp("expires_at").notNull(),
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  revokedAt: timestamp("revoked_at"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => [
  index("sessions_user_id_idx").on(table.userId),
  index("sessions_revoked_at_idx")
    .on(table.revokedAt)
    .where(sql`${table.revokedAt} IS NOT NULL`),
]);

// ============================================
// accounts — managed by Better Auth
// ============================================
export const accounts = pgTable("accounts", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  accountId: text("account_id").notNull(),
  issuer: text("issuer").notNull(),
  providerId: text("provider_id").notNull(),
  accessToken: text("access_token"),
  refreshToken: text("refresh_token"),
  accessTokenExpiresAt: timestamp("access_token_expires_at"),
  refreshTokenExpiresAt: timestamp("refresh_token_expires_at"),
  scope: text("scope"),
  password: text("password"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => [
  index("accounts_user_id_idx").on(table.userId),
  uniqueIndex("accounts_issuer_account_idx").on(table.issuer, table.accountId),
  uniqueIndex("accounts_provider_account_idx").on(table.providerId, table.accountId),
]);

// ============================================
// verifications — managed by Better Auth
// ============================================
export const verifications = pgTable("verifications", {
  id: uuid("id").primaryKey().defaultRandom(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => [
  index("verifications_identifier_idx").on(table.identifier),
]);

// ============================================
// folders — hierarchical folder structure
// ============================================
export const folders = pgTable(
  "folders",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    ownerId: uuid("owner_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    parentId: uuid("parent_id").references((): AnyPgColumn => folders.id, {
      onDelete: "set null",
    }),
    categoryId: uuid("category_id").references(() => categories.id, {
      onDelete: "set null",
    }),
    name: text("name").notNull(),
    order: integer("order").notNull().default(0),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => [
    index("folders_owner_id_idx").on(table.ownerId),
    index("folders_parent_id_idx").on(table.parentId),
    index("folders_category_id_idx").on(table.categoryId),
  ]
);

// Self-referencing for parent folder
export const folderRelations = relations(folders, ({ one, many }) => ({
  owner: one(users, { fields: [folders.ownerId], references: [users.id] }),
  parent: one(folders, {
    fields: [folders.parentId],
    references: [folders.id],
    relationName: "folderParent",
  }),
  category: one(categories, {
    fields: [folders.categoryId],
    references: [categories.id],
  }),
  children: many(folders, { relationName: "folderParent" }),
  documents: many(documents),
}));

// ============================================
// documents — core content
// ============================================
export const documents = pgTable(
  "documents",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    ownerId: uuid("owner_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    folderId: uuid("folder_id").references(() => folders.id, {
      onDelete: "set null",
    }),
    categoryId: uuid("category_id").references(() => categories.id, {
      onDelete: "set null",
    }),
    title: text("title").notNull().default("Untitled"),
    content: text("content").default(""),
    contentJson: jsonb("content_json"),
    metadata: jsonb("metadata"),
    visibility: documentVisibilityEnum("visibility").notNull().default("private"),
    contentHash: text("content_hash"),  // SHA-256 of title+content for smart re-embed
    // Smart-reembed fields (added by migration 0009_lying_hardball.sql).
    // These four columns are required by `backend/src/lib/reembed.ts` and
    // `backend/src/lib/reembed-cron.ts`; the TypeScript schema must stay in
    // sync with the migration or Drizzle queries fail at compile time.
    lastSignificantHash: text("last_significant_hash"),
    lastSignificantUpdateAt: timestamp("last_significant_update_at"),
    pendingMinorChanges: boolean("pending_minor_changes")
      .default(false)
      .notNull(),
    metadataChangedAt: timestamp("metadata_changed_at"),
    searchVector: tsvector("search_vector").generatedAlwaysAs(
      sql`to_tsvector('english', left(COALESCE(title, '') || ' ' || regexp_replace(COALESCE(content, ''), 'data:[^[:space:])>]+', ' ', 'g'), 200000))`
    ),
    searchVectorSimple: tsvector("search_vector_simple").generatedAlwaysAs(
      sql`to_tsvector('simple', left(COALESCE(title, '') || ' ' || regexp_replace(COALESCE(content, ''), 'data:[^[:space:])>]+', ' ', 'g'), 200000))`
    ),
    embeddingStatus: embeddingStatusEnum("embedding_status").notNull().default("pending"),
    activeEmbeddingGeneration: uuid("active_embedding_generation"),
    pendingEmbeddingGeneration: uuid("pending_embedding_generation"),
    embeddingProfile: text("embedding_profile"),
    embeddingContextHash: text("embedding_context_hash"),
    embeddingErrorCode: text("embedding_error_code"),
    embeddingUpdatedAt: timestamp("embedding_updated_at"),
    /** Soft-delete marker. Trashed documents remain tenant-scoped until purge. */
    deletedAt: timestamp("deleted_at"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => [
    index("documents_owner_id_idx").on(table.ownerId),
    index("documents_folder_id_idx").on(table.folderId),
    index("documents_category_id_idx").on(table.categoryId),
    index("documents_created_at_idx").on(table.createdAt),
    index("idx_documents_search_vector").using("gin", table.searchVector),
    index("idx_documents_search_vector_simple").using("gin", table.searchVectorSimple),
    index("documents_embedding_status_idx").on(table.embeddingStatus),
    index("documents_workspace_deleted_at_idx").on(table.workspaceId, table.deletedAt),
    index("idx_documents_title_trgm").using(
      "gin",
      sql`${table.title} gin_trgm_ops`
    ),
  ]
);

export const documentRelations = relations(documents, ({ one, many }) => ({
  owner: one(users, { fields: [documents.ownerId], references: [users.id] }),
  folder: one(folders, {
    fields: [documents.folderId],
    references: [folders.id],
  }),
  category: one(categories, {
    fields: [documents.categoryId],
    references: [categories.id],
  }),
  tags: many(documentTags),
  attachments: many(attachments),
  versions: many(versions),
}));

// ============================================
// document_pipeline_runs — durable multi-stage pipeline state
// ============================================
export const documentPipelineRuns = pgTable(
  "document_pipeline_runs",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    ownerId: uuid("owner_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    generationId: uuid("generation_id").notNull(),
    revision: text("revision").notNull(),
    source: text("source").notNull(),
    embeddingContextHash: text("embedding_context_hash"),
    refreshMode: text("refresh_mode").notNull().default("full"),
    status: pipelineStatusEnum("status").notNull().default("pending"),
    prepareStatus: pipelineStatusEnum("prepare_status").notNull().default("pending"),
    embedStatus: pipelineStatusEnum("embed_status").notNull().default("pending"),
    graphStatus: pipelineStatusEnum("graph_status").notNull().default("pending"),
    summarizeStatus: pipelineStatusEnum("summarize_status").notNull().default("pending"),
    finalizeStatus: pipelineStatusEnum("finalize_status").notNull().default("pending"),
    totalBatches: integer("total_batches").notNull().default(0),
    completedBatches: integer("completed_batches").notNull().default(0),
    failedBatches: integer("failed_batches").notNull().default(0),
    errorCode: text("error_code"),
    graphErrorCode: text("graph_error_code"),
    summarizeErrorCode: text("summarize_error_code"),
    attempts: integer("attempts").notNull().default(0),
    requestedAt: timestamp("requested_at").defaultNow().notNull(),
    startedAt: timestamp("started_at"),
    completedAt: timestamp("completed_at"),
    heartbeatAt: timestamp("heartbeat_at"),
    availableAt: timestamp("available_at"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("document_pipeline_runs_document_generation_idx").on(
      table.documentId,
      table.generationId,
    ),
    index("document_pipeline_runs_owner_status_updated_idx").on(
      table.ownerId,
      table.status,
      table.updatedAt,
    ),
    check(
      "document_pipeline_runs_refresh_mode_check",
      sql`${table.refreshMode} in ('incremental', 'full')`,
    ),
  ],
);

// ============================================
// document_pipeline_batches — idempotent embedding work units
// ============================================
export const documentPipelineBatches = pgTable(
  "document_pipeline_batches",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    workspaceId: text("workspace_id"),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    generationId: uuid("generation_id").notNull(),
    batchIndex: integer("batch_index").notNull(),
    stage: pipelineStageEnum("stage").notNull().default("embed"),
    chunkStart: integer("chunk_start").notNull(),
    chunkEnd: integer("chunk_end").notNull(),
    status: pipelineStatusEnum("status").notNull().default("pending"),
    attempts: integer("attempts").notNull().default(0),
    embeddingProfile: text("embedding_profile"),
    errorCode: text("error_code"),
    availableAt: timestamp("available_at"),
    startedAt: timestamp("started_at"),
    completedAt: timestamp("completed_at"),
    heartbeatAt: timestamp("heartbeat_at"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("document_pipeline_batches_generation_index_idx").on(
      table.generationId,
      table.batchIndex,
    ),
    index("document_pipeline_batches_stage_status_available_idx").on(
      table.stage,
      table.status,
      table.availableAt,
    ),
    index("document_pipeline_batches_document_id_idx").on(table.documentId),
  ],
);

// ============================================
// metadata_reembed_outbox -- exact metadata-impact snapshots
// ============================================
export const metadataReembedOutbox = pgTable(
  "metadata_reembed_outbox",
  {
    // The outbox row id is also the deterministic pipeline generation id.
    id: uuid("id").primaryKey(),
    operationId: uuid("operation_id").notNull(),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    ownerId: uuid("owner_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    revision: text("revision").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("metadata_reembed_outbox_operation_document_idx").on(
      table.operationId,
      table.documentId,
    ),
    index("metadata_reembed_outbox_operation_id_idx").on(
      table.operationId,
      table.id,
    ),
    index("metadata_reembed_outbox_created_id_idx").on(
      table.createdAt,
      table.id,
    ),
  ],
);

// ============================================
// document_knowledge_summaries — generation-scoped retrieval metadata
// ============================================
export const documentKnowledgeSummaries = pgTable(
	"document_knowledge_summaries",
	{
		id: uuid().primaryKey().defaultRandom(),
		document_id: uuid()
			.notNull()
			.references(() => documents.id, { onDelete: "cascade" }),
		owner_id: uuid()
			.notNull()
			.references(() => users.id, { onDelete: "cascade" }),
		workspace_id: text(),
		generation_id: uuid().notNull(),
		revision: text().notNull(),
		language: text().notNull(),
		description: text().notNull(),
		keywords: text().array().notNull().default(sql`ARRAY[]::text[]`),
		created_at: timestamp().defaultNow().notNull(),
		updated_at: timestamp().defaultNow().notNull(),
	},
	(table) => [
		uniqueIndex("document_knowledge_summaries_document_generation_idx").on(
			table.document_id,
			table.generation_id,
		),
		index("document_knowledge_summaries_owner_document_idx").on(
			table.owner_id,
			table.document_id,
		),
	],
);

// ============================================
// tags — document tags
// ============================================
export const tags = pgTable(
  "tags",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    ownerId: uuid("owner_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    name: text("name").notNull(),
    color: text("color"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("tags_owner_id_idx").on(table.ownerId),
    uniqueIndex("tags_owner_name_idx").on(table.ownerId, table.name),
  ]
);

export const tagRelations = relations(tags, ({ many }) => ({
  documents: many(documentTags),
}));

// ============================================
// categories — document/folder classification
// ============================================
export const categories = pgTable(
  "categories",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    ownerId: uuid("owner_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    name: text("name").notNull(),
    order: integer("order").notNull().default(0),
    apiMode: text("api_mode").notNull().default("unavailable"),
    apiPermissionRead: boolean("api_permission_read").notNull().default(false),
    apiPermissionEdit: boolean("api_permission_edit").notNull().default(false),
    apiPermissionWrite: boolean("api_permission_write").notNull().default(false),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => [
    index("categories_owner_id_idx").on(table.ownerId),
    index("categories_api_mode_idx").on(table.apiMode),
  ]
);

export const categoryRelations = relations(categories, ({ one, many }) => ({
  owner: one(users, { fields: [categories.ownerId], references: [users.id] }),
  folders: many(folders),
  documents: many(documents),
}));

// ============================================
// document_tags — many-to-many
// ============================================
export const documentTags = pgTable(
  "document_tags",
  {
    workspaceId: text("workspace_id"),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    tagId: uuid("tag_id")
      .notNull()
      .references(() => tags.id, { onDelete: "cascade" }),
  },
  (table) => [
    uniqueIndex("document_tags_unique_idx").on(table.documentId, table.tagId),
  ]
);

export const documentTagRelations = relations(documentTags, ({ one }) => ({
  document: one(documents, {
    fields: [documentTags.documentId],
    references: [documents.id],
  }),
  tag: one(tags, { fields: [documentTags.tagId], references: [tags.id] }),
}));

// ============================================
// share_links — sharing tokens
// ============================================
export const shareLinks = pgTable(
  "share_links",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    documentId: uuid("document_id").references(() => documents.id, {
      onDelete: "cascade",
    }),
    folderId: uuid("folder_id").references(() => folders.id, {
      onDelete: "cascade",
    }),
    categoryId: uuid("category_id").references(() => categories.id, {
      onDelete: "cascade",
    }),
    token: text("token").notNull().unique(),
    passwordHash: text("password_hash"),
    role: shareRoleEnum("role").notNull().default("viewer"),
    accessMode: shareAccessModeEnum("access_mode").notNull().default("public"),
    allowPasswordFallback: boolean("allow_password_fallback").notNull().default(false),
    policyVersion: integer("policy_version").notNull().default(1),
    expiresAt: timestamp("expires_at"),
    createdBy: uuid("created_by")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("share_links_token_idx").on(table.token),
    index("share_links_document_id_idx").on(table.documentId),
    index("share_links_folder_id_idx").on(table.folderId),
    index("share_links_category_id_idx").on(table.categoryId),
    check(
      "share_links_exactly_one_target_check",
      sql`num_nonnulls(${table.documentId}, ${table.folderId}, ${table.categoryId}) = 1`,
    ),
  ]
);

export const shareLinkRelations = relations(shareLinks, ({ one, many }) => ({
  document: one(documents, {
    fields: [shareLinks.documentId],
    references: [documents.id],
  }),
  folder: one(folders, {
    fields: [shareLinks.folderId],
    references: [folders.id],
  }),
  category: one(categories, {
    fields: [shareLinks.categoryId],
    references: [categories.id],
  }),
  creator: one(users, {
    fields: [shareLinks.createdBy],
    references: [users.id],
  }),
  guestAccess: many(guestAccess),
}));

// ============================================
// guest_access — guest email grants
// ============================================
export const guestAccess = pgTable(
  "guest_access",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    shareLinkId: uuid("share_link_id")
      .notNull()
      .references(() => shareLinks.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    guestEmail: text("guest_email").notNull(),
    role: shareRoleEnum("role").notNull().default("viewer"),
    status: shareGrantStatusEnum("status").notNull().default("pending"),
    grantedAt: timestamp("granted_at").defaultNow().notNull(),
    acceptedAt: timestamp("accepted_at"),
    revokedAt: timestamp("revoked_at"),
  },
  (table) => [
    index("guest_access_share_link_idx").on(table.shareLinkId),
    uniqueIndex("guest_access_share_link_email_unique_idx").on(table.shareLinkId, table.guestEmail),
  ]
);

export const guestAccessRelations = relations(guestAccess, ({ one }) => ({
  shareLink: one(shareLinks, {
    fields: [guestAccess.shareLinkId],
    references: [shareLinks.id],
  }),
}));

// ============================================
// attachments — file uploads (SeaweedFS)
// ============================================
export const attachments = pgTable(
  "attachments",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    uploadedBy: uuid("uploaded_by")
      .notNull()
      .references(() => users.id, { onDelete: "restrict" }),
    filename: text("filename").notNull(),
    mimeType: text("mime_type").notNull(),
    size: bigint("size", { mode: "number" }).notNull(),
    storageKey: text("storage_key").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("attachments_document_id_idx").on(table.documentId),
    index("attachments_uploaded_by_idx").on(table.uploadedBy),
    uniqueIndex("attachments_storage_key_unique").on(table.storageKey),
  ]
);

// A direct upload URL can outlive the request that created it. Keeping the
// exact actor/key/expiry admission durable lets confirmation authenticate the
// key after session/document disappearance and lets account purge wait through
// the unrevocable URL lifetime before deleting the exact object.
export const pendingAttachmentUploads = pgTable(
  "pending_attachment_uploads",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "restrict" }),
    actorUserId: uuid("actor_user_id")
      .notNull()
      .references(() => users.id, { onDelete: "restrict" }),
    workspaceId: text("workspace_id"),
    storageKey: text("storage_key").notNull(),
    tokenHash: text("token_hash").notNull(),
    filename: text("filename").notNull(),
    mimeType: text("mime_type").notNull(),
    declaredSize: bigint("declared_size", { mode: "number" }).notNull(),
    quotaReservationId: text("quota_reservation_id"),
    quotaOperationKey: text("quota_operation_key").notNull(),
    quotaState: text("quota_state").notNull(),
    actualSize: bigint("actual_size", { mode: "number" }),
    urlIssuedAt: timestamp("url_issued_at"),
    confirmingAt: timestamp("confirming_at"),
    leaseOwner: text("lease_owner"),
    leaseExpiresAt: timestamp("lease_expires_at"),
    attemptCount: integer("attempt_count").default(0).notNull(),
    lastError: text("last_error"),
    expiresAt: timestamp("expires_at").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("pending_attachment_uploads_storage_key_unique").on(table.storageKey),
    uniqueIndex("pending_attachment_uploads_token_hash_unique").on(table.tokenHash),
    index("pending_attachment_uploads_actor_expiry_idx").on(
      table.actorUserId,
      table.expiresAt,
    ),
    index("pending_attachment_uploads_document_idx").on(table.documentId),
    index("pending_attachment_uploads_cleanup_lease_idx").on(
      table.expiresAt,
      table.leaseExpiresAt,
      table.id,
    ),
    check(
      "pending_attachment_uploads_quota_state_check",
      sql`${table.quotaState} IN ('not_required', 'reserve_pending', 'reserved', 'finalize_pending', 'committed')`,
    ),
  ],
);

// DB-authoritative intents for object removal and quota release. Source rows
// are deleted in the same transaction that inserts an intent; S3 and quota
// effects happen only after commit and remain recoverable across crashes.
export const attachmentStorageCleanupOutbox = pgTable(
  "attachment_storage_cleanup_outbox",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    sourceKind: text("source_kind").notNull(),
    sourceId: uuid("source_id").notNull(),
    storageKey: text("storage_key").notNull(),
    documentId: uuid("document_id").notNull(),
    actorUserId: uuid("actor_user_id").notNull(),
    ownerUserId: uuid("owner_user_id").notNull(),
    requestedByUserId: uuid("requested_by_user_id").notNull(),
    workspaceId: text("workspace_id"),
    size: bigint("size", { mode: "number" }).notNull(),
    quotaOperationKey: text("quota_operation_key").notNull(),
    quotaReleaseKind: text("quota_release_kind").default("none").notNull(),
    quotaReservationId: text("quota_reservation_id"),
    notBefore: timestamp("not_before").defaultNow().notNull(),
    retainUntil: timestamp("retain_until"),
    objectDeletedAt: timestamp("object_deleted_at"),
    leaseOwner: text("lease_owner"),
    leaseExpiresAt: timestamp("lease_expires_at"),
    attemptCount: integer("attempt_count").default(0).notNull(),
    lastError: text("last_error"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("attachment_storage_cleanup_outbox_source_unique").on(
      table.sourceKind,
      table.sourceId,
    ),
    uniqueIndex("attachment_storage_cleanup_outbox_storage_key_unique").on(
      table.storageKey,
    ),
    index("attachment_storage_cleanup_outbox_ready_idx").on(
      table.notBefore,
      table.leaseExpiresAt,
      table.createdAt,
      table.id,
    ),
    index("attachment_storage_cleanup_outbox_actor_idx").on(
      table.actorUserId,
      table.id,
    ),
    index("attachment_storage_cleanup_outbox_owner_idx").on(
      table.ownerUserId,
      table.id,
    ),
    index("attachment_storage_cleanup_outbox_requester_idx").on(
      table.requestedByUserId,
      table.id,
    ),
    check(
      "attachment_storage_cleanup_outbox_source_kind_check",
      sql`${table.sourceKind} IN ('attachment', 'pending_upload', 'uncommitted_upload')`,
    ),
    check(
      "attachment_storage_cleanup_outbox_quota_kind_check",
      sql`${table.quotaReleaseKind} IN ('none', 'reserve_pending', 'reservation', 'finalize_pending', 'committed')`,
    ),
    check("attachment_storage_cleanup_outbox_size_check", sql`${table.size} >= 0`),
    check(
      "attachment_storage_cleanup_outbox_reservation_check",
      sql`${table.quotaReleaseKind} NOT IN ('reservation', 'finalize_pending') OR ${table.quotaReservationId} IS NOT NULL`,
    ),
  ],
);

// Internal transaction-bound proof used only by the narrow signed-admission
// abandonment SECURITY DEFINER function. The runtime role has no table grants.
export const pendingAttachmentCleanupAuthorizations = pgTable(
  "pending_attachment_cleanup_authorizations",
  {
    backendPid: integer("backend_pid").notNull(),
    transactionId: bigint("transaction_id", { mode: "bigint" }).notNull(),
    admissionId: uuid("admission_id").notNull(),
    documentId: uuid("document_id").notNull(),
    actorUserId: uuid("actor_user_id").notNull(),
    ownerUserId: uuid("owner_user_id").notNull(),
    workspaceId: text("workspace_id"),
    storageKey: text("storage_key").notNull(),
    tokenHash: text("token_hash").notNull(),
  },
  (table) => [
    primaryKey({
      columns: [table.backendPid, table.transactionId, table.admissionId],
    }),
  ],
);

export const attachmentRelations = relations(attachments, ({ one }) => ({
  document: one(documents, {
    fields: [attachments.documentId],
    references: [documents.id],
  }),
}));

// ============================================
// versions — document version history
// ============================================
export const versions = pgTable(
  "versions",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    content: text("content").notNull(),
    contentJson: jsonb("content_json"),
    createdBy: uuid("created_by")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    label: text("label"),
    description: text("description"),
    isSnapshot: boolean("is_snapshot").default(false),
    restoredFrom: uuid("restored_from"),
  },
  (table) => [
    index("versions_document_id_idx").on(table.documentId),
    index("versions_created_at_idx").on(table.createdAt),
    index("versions_is_snapshot_idx").on(table.isSnapshot),
  ]
);

// ============================================
// document_embeddings — multi-chunk pgvector storage
// ============================================
export const documentEmbeddings = pgTable(
  "document_embeddings",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    chunkIndex: bigint("chunk_index", { mode: "number" }).notNull(),
    chunkText: text("chunk_text").notNull(),
    chunkHash: text("chunk_hash"),
    charStart: integer("char_start").notNull().default(0),
    charEnd: integer("char_end").notNull().default(0),
    embedding: vector("embedding", { dimensions: 1024 }),
    // Identifier of the embedding model that produced the vector above.
    // Empty string ("") means "unknown / legacy row" (pre-v1 rows that
    // existed before this column was introduced). The targeted reindex
    // endpoint at POST /api/admin/reindex/model filters on this column
    // to refresh only docs whose stored model does not match the
    // currently-configured EMBEDDING_MODEL.
    embeddingModel: text("embedding_model").notNull().default(""),
    generationId: uuid("generation_id").notNull(),
    embeddingDimensions: integer("embedding_dimensions").notNull().default(1024),
    embeddingProfile: text("embedding_profile").notNull().default("legacy"),
    isValid: boolean("is_valid").notNull().default(false),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("document_embeddings_doc_id_idx").on(table.documentId),
    uniqueIndex("document_embeddings_doc_chunk_idx").on(
      table.documentId,
      table.generationId,
      table.chunkIndex,
    ),
    index("document_embeddings_generation_valid_idx").on(
      table.documentId,
      table.generationId,
      table.isValid,
    ),
    // Backs POST /api/admin/reindex/model which selects docs whose stored
    // embedding model differs from the currently-configured EMBEDDING_MODEL.
    index("idx_document_embeddings_embedding_model").on(table.embeddingModel),
    index("idx_document_embeddings_hnsw").using(
      "hnsw",
      sql`${table.embedding} vector_cosine_ops`
    ),
    // StreamingDiskANN index for >100k row corpora. Co-exists with HNSW;
    // the planner picks the right one based on table size and memory.
    // Requires the pgvectorscale extension (enabled in postgres/init.sql).
    index("idx_document_embeddings_diskann").using(
      "diskann",
      sql`${table.embedding} vector_cosine_ops`
    ),
  ]
);

export const documentEmbeddingRelations = relations(documentEmbeddings, ({ one }) => ({
  document: one(documents, {
    fields: [documentEmbeddings.documentId],
    references: [documents.id],
  }),
}));

// ============================================
// api_keys — user API keys
// ============================================
export const apiKeys = pgTable(
  "api_keys",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    ownerId: uuid("owner_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    workspaceId: text("workspace_id"),
    name: text("name").notNull(),
    keyHash: text("key_hash").notNull().unique(),
    prefix: text("prefix").notNull(),
    encryptedKey: text("encrypted_key"),
    scopes: jsonb("scopes").notNull().default("[]"),
    lastUsedAt: timestamp("last_used_at"),
    expiresAt: timestamp("expires_at"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("idx_api_keys_owner").on(table.ownerId),
    index("idx_api_keys_prefix").on(table.prefix),
  ]
);

export const apiKeyRelations = relations(apiKeys, ({ one }) => ({
  owner: one(users, { fields: [apiKeys.ownerId], references: [users.id] }),
}));

// ============================================
// audit_log — append-only audit trail
// ============================================
export const auditLog = pgTable(
  "audit_log",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    actorId: uuid("actor_id").notNull(),
    workspaceId: text("workspace_id"),
    action: text("action").notNull(),
    resourceType: text("resource_type").notNull(),
    resourceId: uuid("resource_id"),
    details: jsonb("details").notNull().default("{}"),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("idx_audit_log_actor").on(table.actorId),
    index("idx_audit_log_resource").on(table.resourceType, table.resourceId),
    index("idx_audit_log_created").on(table.createdAt),
  ]
);

// Durable, server-only lifecycle saga state. This table records only safe
// metadata and counts; raw fence tokens, documents, secrets, and provider
// errors are intentionally excluded from the schema.
export const lifecycleOperations = pgTable(
  "lifecycle_operations",
  {
    id: uuid("id").primaryKey().defaultRandom(),
	actorUserId: uuid("actor_user_id").references(() => users.id, { onDelete: "set null" }),
	actorSubjectHash: text("actor_subject_hash").notNull(),
    idempotencyKey: text("idempotency_key").notNull(),
    operationKind: lifecycleOperationKindEnum("operation_kind").notNull(),
    status: lifecycleOperationStatusEnum("status").notNull().default("pending"),
    leaseOwner: text("lease_owner"),
    leaseExpiresAt: timestamp("lease_expires_at"),
    fenceTokenHash: text("fence_token_hash"),
    completedSteps: jsonb("completed_steps").notNull().default("[]"),
    terminalResult: jsonb("terminal_result"),
    safeErrorCode: text("safe_error_code"),
    attemptCount: integer("attempt_count").notNull().default(0),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
    completedAt: timestamp("completed_at"),
  },
  (table) => [
    uniqueIndex("lifecycle_operations_actor_idempotency_idx").on(table.actorUserId, table.idempotencyKey),
    index("lifecycle_operations_status_lease_idx").on(table.status, table.leaseExpiresAt),
    index("lifecycle_operations_actor_idx").on(table.actorUserId),
	index("lifecycle_operations_retryable_idx").on(table.status).where(sql`${table.status} = 'retryable'`),
	check("lifecycle_operations_actor_subject_hash", sql`${table.actorSubjectHash} ~ '^[a-f0-9]{64}$'`),
  ],
);

// Atomically committed alongside a document and its initial version when an
// authenticated client supplies Idempotency-Key on POST /api/documents.
export const documentCreateOperations = pgTable(
  "document_create_operations",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    workspaceId: text("workspace_id").notNull(),
    actorUserId: uuid("actor_user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    idempotencyKey: text("idempotency_key").notNull(),
    documentId: uuid("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("document_create_operations_workspace_actor_key_idx").on(
      table.workspaceId,
      table.actorUserId,
      table.idempotencyKey,
    ),
    uniqueIndex("document_create_operations_document_idx").on(table.documentId),
  ],
);

export const versionRelations = relations(versions, ({ one }) => ({
  document: one(documents, {
    fields: [versions.documentId],
    references: [documents.id],
  }),
  creator: one(users, {
    fields: [versions.createdBy],
    references: [users.id],
  }),
}));