import { sql } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import { contentRevisionsMigrationSql } from './revisions.js'
import { contentSchema, type ContentSchema } from './schema.js'
import { contentTaxonomyMigrationSql } from './taxonomy.js'

const CREATE_ENTRIES = sql`
  CREATE TABLE content_entries (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    slug text NOT NULL,
    type text NOT NULL,
    title text NOT NULL,
    body text NOT NULL DEFAULT '',
    status text NOT NULL DEFAULT 'draft',
    visibility text NOT NULL DEFAULT 'public',
    published_at timestamptz(3),
    author text NOT NULL,
    created_at timestamptz(3) NOT NULL DEFAULT NOW(),
    updated_at timestamptz(3) NOT NULL DEFAULT NOW()
  )
`

const CREATE_INDEX = sql`
  CREATE UNIQUE INDEX content_entries_type_slug_uq ON content_entries (type, slug)
`

export async function setupTaxonomyDb(handle: unknown): Promise<Querier<ContentSchema>> {
  const d = handle as Querier<ContentSchema>
  await d.execute(CREATE_ENTRIES)
  await d.execute(CREATE_INDEX)
  for (const s of contentRevisionsMigrationSql()
    .split(';')
    .map((x) => x.trim())
    .filter(Boolean)) {
    await d.execute(sql.raw(s))
  }
  for (const s of contentTaxonomyMigrationSql()
    .split(';')
    .map((x) => x.trim())
    .filter(Boolean)) {
    await d.execute(sql.raw(s))
  }
  return d
}