/**
 * Knowledge Base module schema — three tables for internal wiki and client vaults.
 * Postgres / Neon via Hyperdrive.
 *
 * - kb_spaces:      container for articles (internal or vault, locked to a customer)
 * - kb_articles:    rich-text articles (Tiptap v2 JSONB), hierarchical via parent_id
 * - kb_attachments: R2-backed file attachments per article (signed URL access only)
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  jsonb,
  timestamp,
  integer,
  numeric,
  index,
  uniqueIndex,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { customers } from './customers'

// ── kb_spaces ─────────────────────────────────────────────────────────────────

export const kbSpaces = pgTable(
  'kb_spaces',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    slug: text('slug').notNull(),
    type: text('type').notNull().default('internal'),
    customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'cascade' }),
    icon: text('icon'),
    isPublic: boolean('is_public').notNull().default(false),
    description: text('description'),
    // settings-kb: display ordering for the spaces management UI
    position: integer('position').notNull().default(0),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('idx_kb_spaces_tenant').on(t.tenantId),
    tenantCustomerIdx: index('idx_kb_spaces_customer').on(t.tenantId, t.customerId),
    tenantSlugUniq: uniqueIndex('kb_spaces_tenant_slug_uniq').on(t.tenantId, t.slug),
    typeCheck: check('kb_spaces_type_check', sql`${t.type} IN ('internal', 'vault')`),
  }),
)

export type KbSpaceRow = typeof kbSpaces.$inferSelect
export type NewKbSpace = typeof kbSpaces.$inferInsert

// ── kb_articles ───────────────────────────────────────────────────────────────

export const kbArticles = pgTable(
  'kb_articles',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    spaceId: uuid('space_id')
      .notNull()
      .references(() => kbSpaces.id, { onDelete: 'cascade' }),
    // Self-referential FK — parent_id references kb_articles(id)
    // Drizzle does not support forward-declaring self refs in pgTable callbacks;
    // the FK is expressed here but the actual DB constraint is in raw_ddl since
    // Drizzle's references() for self-refs requires a lambda that may not resolve
    // at schema compile time across modules. We use the raw SQL below and Drizzle
    // column definition without .references() to avoid circular init.
    parentId: uuid('parent_id'),
    title: text('title').notNull(),
    slug: text('slug').notNull(),
    content: jsonb('content').notNull().$type<Record<string, unknown>>(),
    status: text('status').notNull().default('DRAFT'),
    position: numeric('position').notNull(),
    viewCount: integer('view_count').notNull().default(0),
    publishedAt: timestamp('published_at', { withTimezone: true }),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),
    updatedBy: uuid('updated_by').references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    // search-completeness: plain text extracted from JSONB content for FTS
    contentText: text('content_text'),
    // kb-article-editor: SEO metadata
    metaTitle: text('meta_title'),
    metaDescription: text('meta_description'),
    // kb-article-editor: soft delete
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
    // kb-article-editor: generated search text (STORED; read-only — never written by app)
    searchText: text('search_text').generatedAlwaysAs(
      sql`title || ' ' || COALESCE(meta_title, '') || ' ' || COALESCE(meta_description, '')`,
    ),
  },
  (t) => ({
    tenantSpaceIdx: index('idx_kb_articles_space').on(t.tenantId, t.spaceId),
    treeIdx: index('idx_kb_articles_tree').on(t.spaceId, t.parentId, t.position),
    spaceSlugUniq: uniqueIndex('kb_articles_space_slug_uniq').on(t.spaceId, t.slug),
    statusCheck: check('kb_articles_status_check', sql`${t.status} IN ('DRAFT', 'PENDING_REVIEW', 'PUBLISHED')`),
    tenantStatusIdx: index('idx_kb_articles_tenant_status').on(t.tenantId, t.status),
  }),
)

export type KbArticleRow = typeof kbArticles.$inferSelect
export type NewKbArticle = typeof kbArticles.$inferInsert

// ── kb_attachments ────────────────────────────────────────────────────────────

export const kbAttachments = pgTable(
  'kb_attachments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    articleId: uuid('article_id')
      .notNull()
      .references(() => kbArticles.id, { onDelete: 'cascade' }),
    filename: text('filename').notNull(),
    r2Key: text('r2_key').notNull(),
    fileType: text('file_type').notNull(),
    fileSizeBytes: integer('file_size_bytes').notNull(),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    articleIdx: index('idx_kb_attachments_article').on(t.tenantId, t.articleId),
  }),
)

export type KbAttachmentRow = typeof kbAttachments.$inferSelect
export type NewKbAttachment = typeof kbAttachments.$inferInsert

// ── kb_article_versions ───────────────────────────────────────────────────────

/**
 * Append-only version snapshots for KB articles — kb-versioning spec.
 *
 * Every explicit save ([Save & version]), publish, and restore writes an
 * immutable JSONB snapshot of the article's title + Tiptap content.
 * version_number is assigned server-side as MAX(version_number)+1 per article
 * inside the same transaction as the insert; the UNIQUE constraint is the
 * concurrency guard.
 */
export const kbArticleVersions = pgTable(
  'kb_article_versions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    articleId: uuid('article_id')
      .notNull()
      .references(() => kbArticles.id, { onDelete: 'cascade' }),
    // bare UUID — no FK — matches the upstream kb_articles.tenant_id pattern
    tenantId: uuid('tenant_id').notNull(),
    versionNumber: integer('version_number').notNull(),
    title: text('title').notNull(),
    content: jsonb('content').notNull().$type<Record<string, unknown>>(),
    label: text('label'),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    articleVersionIdx: index('idx_kb_article_versions_article').on(
      t.articleId,
      t.versionNumber,
    ),
    articleVersionUniq: uniqueIndex('kb_article_versions_article_version_uniq').on(
      t.articleId,
      t.versionNumber,
    ),
  }),
)

export type KbArticleVersionRow = typeof kbArticleVersions.$inferSelect
export type NewKbArticleVersion = typeof kbArticleVersions.$inferInsert

// ── settings-kb: article status constants ────────────────────────────────────

/** Allowed values for kb_articles.status (no DB CHECK enforced — app-layer only). */
export const KB_ARTICLE_STATUSES = ['DRAFT', 'PENDING_REVIEW', 'PUBLISHED'] as const
export type KbArticleStatus = (typeof KB_ARTICLE_STATUSES)[number]

/** Projection over the KB columns of the shared tenant_settings row. */
export interface TenantKbSettings {
  tenant_id: string
  kb_require_review: boolean
  kb_versioning_enabled: boolean
  kb_default_space_id: string | null
}