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,
    parent_id uuid,
    menu_order integer NOT NULL DEFAULT 0,
    template_key text,
    excerpt text NOT NULL DEFAULT '',
    featured_media jsonb,
    comment_status text NOT NULL DEFAULT 'open',
    ping_status text NOT NULL DEFAULT 'open',
    sticky boolean NOT NULL DEFAULT false,
    format text,
    deleted_at timestamptz(3),
    last_edited_by text NOT NULL,
    type_definition_revision integer NOT NULL DEFAULT 1,
    status_definition_revision integer NOT NULL DEFAULT 1,
    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
}