import { desc } from 'drizzle-orm'
import { boolean, index, jsonb, pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'
import type { CleanSubmission, StoredSubmission } from './types.js'

export const formSubmissions = pgTable(
  'form_submissions',
  {
    id: uuid('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
    formId: text('form_id').notNull(),
    data: jsonb('data').$type<CleanSubmission>().notNull(),
    meta: jsonb('meta').$type<NonNullable<StoredSubmission['meta']>>(),
    spam: boolean('spam').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
  },
  (t) => [index('form_submissions_form_created_idx').on(t.formId, desc(t.createdAt))],
)

export const formsSchema = { formSubmissions }
export type FormsSchema = typeof formsSchema

/**
 * Pure DDL helper — returns CREATE TABLE + index SQL the host applies once (per-statement-split
 * on neon-http). Column names + types match the drizzle table above exactly.
 */
export function formsTableSql(table = 'form_submissions'): string {
  return `
CREATE TABLE IF NOT EXISTS ${table} (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  form_id text NOT NULL,
  data jsonb NOT NULL,
  meta jsonb,
  spam boolean NOT NULL DEFAULT false,
  created_at timestamptz(3) NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS form_submissions_form_created_idx ON ${table} (form_id, created_at DESC);
`.trim()
}