import { PGlite } from '@electric-sql/pglite'
import { drizzle } from 'drizzle-orm/pglite'
import type { TransactionalDatabase } from '@platform-modules/db'
import { helpdeskSchema, type HelpdeskSchema } from './schema.js'

export async function createTestDb(): Promise<TransactionalDatabase<HelpdeskSchema>> {
  const client = new PGlite()
  const db = drizzle(client, { schema: helpdeskSchema }) as unknown as TransactionalDatabase<HelpdeskSchema>

  await client.exec(`
    CREATE TABLE support_messages (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      parent_type text NOT NULL,
      parent_id text NOT NULL,
      author_type text NOT NULL,
      body text NOT NULL,
      visibility text,
      created_at timestamptz NOT NULL DEFAULT NOW()
    );

    CREATE TABLE support_state_transitions (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      parent_type text NOT NULL,
      parent_id text NOT NULL,
      from_status text NOT NULL,
      to_status text NOT NULL,
      actor_id text NOT NULL,
      reason text,
      at timestamptz NOT NULL DEFAULT NOW()
    );

    CREATE TABLE support_attachments (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      parent_type text NOT NULL,
      parent_id text NOT NULL,
      object_key text NOT NULL,
      file_name text,
      mime_type text,
      created_at timestamptz NOT NULL DEFAULT NOW()
    );

    CREATE TABLE sla_policies (
      scope_key text PRIMARY KEY,
      target text NOT NULL,
      due_minutes integer NOT NULL
    );
  `)

  return db
}