import { bigint, bigserial, index, integer, pgTable, text } from 'drizzle-orm/pg-core'
import type { CommentStatus } from './types.js'

/**
 * Drizzle pgTable for comments — tenantless public schema (spec §4, content parity).
 * seq: bigserial (auto-incrementing tie-break for collation-safe keyset, spec §3).
 * created_at_ms: bigint stored as number (avoids string round-trip from pg drivers, advisor note).
 * depth: stored at insert (spec §3 — immutable; not recomputed on read).
 */
export const comments = pgTable(
  'comments',
  {
    // seq first so bigserial is registered before id — drizzle column order = DDL column order
    seq: bigserial('seq', { mode: 'number' }).notNull(),
    id: text('id').primaryKey(),
    targetType: text('target_type').notNull(),
    targetId: text('target_id').notNull(),
    parentId: text('parent_id'),
    depth: integer('depth').notNull().default(0),
    authorKind: text('author_kind').notNull(),
    authorUserId: text('author_user_id'),
    authorName: text('author_name'),
    authorEmail: text('author_email'),
    authorUrl: text('author_url'),
    body: text('body').notNull().default(''),
    bodyHtml: text('body_html').notNull().default(''),
    status: text('status').$type<CommentStatus>().notNull().default('pending'),
    createdAtMs: bigint('created_at_ms', { mode: 'number' }).notNull(),
    createdAt: text('created_at').notNull(),
    editedAt: text('edited_at'),
  },
  (t) => [
    index('comments_target_keyset_idx').on(t.targetType, t.targetId, t.createdAtMs, t.seq),
    index('comments_parent_id_idx').on(t.parentId),
    index('comments_status_idx').on(t.status),
    index('comments_moderation_idx').on(t.status, t.createdAtMs, t.seq),
  ],
)

export const commentsSchema = { comments }
export type CommentsSchema = typeof commentsSchema

/**
 * Pure DDL helper — returns a CREATE TABLE SQL string the host applies once (spec §4).
 * Column names + types match the drizzle table above exactly (seam-reviewed parity requirement).
 * The host owns migrations; this module never ships a migration file.
 */
export function commentsTableSql(table = 'comments'): string {
  return `
CREATE TABLE IF NOT EXISTS ${table} (
  seq        bigserial NOT NULL,
  id         text PRIMARY KEY,
  target_type text NOT NULL,
  target_id  text NOT NULL,
  parent_id  text NULL REFERENCES ${table}(id),
  depth      integer NOT NULL DEFAULT 0,
  author_kind text NOT NULL,
  author_user_id text NULL,
  author_name text NULL,
  author_email text NULL,
  author_url  text NULL,
  body        text NOT NULL DEFAULT '',
  body_html   text NOT NULL DEFAULT '',
  status      text NOT NULL DEFAULT 'pending',
  created_at_ms bigint NOT NULL,
  created_at  text NOT NULL,
  edited_at   text NULL
);
CREATE INDEX IF NOT EXISTS ${table}_target_keyset_idx ON ${table} (target_type, target_id, created_at_ms, seq);
CREATE INDEX IF NOT EXISTS ${table}_parent_id_idx ON ${table} (parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS ${table}_status_idx ON ${table} (status);
CREATE INDEX IF NOT EXISTS ${table}_moderation_idx ON ${table} (status, created_at_ms, seq);
`.trim()
}