import { PGlite } from '@electric-sql/pglite'
import { eq } from 'drizzle-orm'
import { drizzle } from 'drizzle-orm/pglite'
import type { TransactionalDatabase } from '@platform-modules/db'
import { withTransactionIdentity } from '@platform-modules/db'
import { auditLog, auditSchema } from './schema.js'
import type { AuditEvent } from './types.js'

export type TestSchema = typeof auditSchema

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

  await client.exec(`
    CREATE TABLE audit_log (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      actor_id text,
      actor_type text,
      actor_label text,
      action text NOT NULL,
      entity_type text NOT NULL,
      entity_id text NOT NULL,
      metadata jsonb,
      ip text,
      tenant_id text,
      created_at timestamptz(3) NOT NULL DEFAULT NOW()
    );
    CREATE INDEX audit_log_tenant_created_idx ON audit_log (tenant_id, created_at DESC);
    CREATE INDEX audit_log_tenant_entity_idx ON audit_log (tenant_id, entity_type, entity_id);
    CREATE INDEX audit_log_tenant_actor_idx ON audit_log (tenant_id, actor_id);
  `)

  return db
}

export async function seedAudit(
  db: TransactionalDatabase<TestSchema>,
  event: AuditEvent & { createdAt?: Date },
): Promise<string> {
  const [row] = await db
    .insert(auditLog)
    .values({
      actorId: event.actorId ?? null,
      actorType: event.actorType ?? null,
      actorLabel: event.actorLabel ?? null,
      action: event.action,
      entityType: event.entityType,
      entityId: event.entityId,
      metadata: event.metadata ?? null,
      ip: event.ip ?? null,
      tenantId: event.tenantId ?? null,
      ...(event.createdAt ? { createdAt: event.createdAt } : {}),
    })
    .returning({ id: auditLog.id })

  return row!.id
}

export async function countAudit(db: TransactionalDatabase<TestSchema>): Promise<number> {
  const rows = await db.select({ id: auditLog.id }).from(auditLog)
  return rows.length
}

export async function getAuditById(
  db: TransactionalDatabase<TestSchema>,
  id: string,
): Promise<typeof auditLog.$inferSelect | undefined> {
  const [row] = await db.select().from(auditLog).where(eq(auditLog.id, id)).limit(1)
  return row
}