import { PGlite } from '@electric-sql/pglite'
import { sql } from 'drizzle-orm'
import { drizzle } from 'drizzle-orm/pglite'
import type { TransactionalDatabase } from '@platform-modules/db'
import { withTransactionIdentity } from '@platform-modules/db'
import type { DocumentSpec, InvoiceDocumentResult } from './index.js'

export async function createTestDb(): Promise<TransactionalDatabase> {
  const client = new PGlite()
  const db = withTransactionIdentity(drizzle(client)) as unknown as TransactionalDatabase

  await client.exec(`
    CREATE TABLE invoice_document (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      idempotency_key text NOT NULL UNIQUE,
      document_id text NOT NULL,
      document_number text NOT NULL,
      document_url text NOT NULL,
      claim_token text NULL,
      provider_called_at timestamptz NULL,
      created_at timestamptz NOT NULL DEFAULT NOW()
    );
  `)

  return db
}

export function createDocumentSpec(idempotencyKey: string = crypto.randomUUID()): DocumentSpec {
  return {
    customer: {
      name: 'Jane Buyer',
      email: 'jane@example.test',
      taxId: 'TAX-123',
    },
    lineItems: [
      {
        description: 'Plugin backend core invoice',
        quantity: 2,
        unitAmountMinor: 2_500n,
        taxTreatment: 'exclusive',
        vatRateBasisPoints: 1800,
      },
    ],
    currency: 'USD',
    idempotencyKey,
    docType: 'invoice',
  }
}

export function createInvoiceResult(idempotencyKey: string): InvoiceDocumentResult {
  return {
    documentId: `doc-${idempotencyKey}`,
    documentNumber: `INV-${idempotencyKey}`,
    documentUrl: `https://example.test/docs/${encodeURIComponent(idempotencyKey)}`,
  }
}

export async function countInvoiceDocuments(db: TransactionalDatabase): Promise<number> {
  const result = await db.execute(sql`SELECT COUNT(*)::int AS count FROM invoice_document`)
  return firstCount(result)
}

function firstCount(result: unknown): number {
  const rows = (Array.isArray(result) ? result : (result as { rows?: Array<{ count: number }> }).rows) ?? []
  return Number(rows[0]?.count ?? 0)
}