import { sql } from 'drizzle-orm'
import { it, expect } from 'vitest'
import { createPgliteClient } from '../../db/src/postgres/pglite.js'
import {
  claim,
  jobsSchema,
  jobsTable,
  markJobCompleted,
  requeueAfterFailure,
  type JobRow,
} from './db-poll.js'

const CREATE_JOBS = sql`
  CREATE TABLE jobs (
    id uuid PRIMARY KEY,
    type text NOT NULL,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    scheduled_for timestamptz NOT NULL DEFAULT NOW(),
    attempts integer NOT NULL DEFAULT 0,
    max_attempts integer NOT NULL DEFAULT 5,
    last_error text,
    created_at timestamptz NOT NULL DEFAULT NOW(),
    processed_at timestamptz,
    failed_at timestamptz
  )
`

function rowStatus(row: JobRow | Record<string, unknown>): string {
  return String((row as JobRow).status ?? (row as Record<string, unknown>).status)
}

function rowAttempts(row: JobRow | Record<string, unknown>): number {
  return Number((row as JobRow).attempts ?? (row as Record<string, unknown>).attempts)
}

function rowLastError(row: JobRow | Record<string, unknown>): string | null {
  const v = (row as JobRow).lastError ?? (row as Record<string, unknown>).last_error
  return v == null ? null : String(v)
}

async function setupDb() {
  const db = createPgliteClient({ schema: jobsSchema })
  await db.execute(CREATE_JOBS)
  return db
}

function jobId(suffix: string): string {
  return `00000000-0000-4000-8000-${suffix.padStart(12, '0')}`
}

it('claim flips pending→processing and returns claimed rows', async () => {
  const db = await setupDb()
  await db.insert(jobsTable).values({
    id: jobId('000000000001'),
    type: 'email',
    payload: { to: 'a@b.c' },
    status: 'pending',
  })

  const claimed = await claim(db, { limit: 5 })
  expect(claimed).toHaveLength(1)
  expect(rowStatus(claimed[0]!)).toBe('processing')
  // Regression guard: returned rows must be the hydrated camelCase JobRow contract
  // (raw db.execute leaked snake_case keys + string timestamps).
  expect(claimed[0]!.status).toBe('processing')
  expect(claimed[0]!.maxAttempts).toBe(5)
  expect(claimed[0]!.scheduledFor).toBeInstanceOf(Date)

  const remaining = await db.select().from(jobsTable)
  expect(rowStatus(remaining[0]!)).toBe('processing')
})

it('markJobCompleted sets status=completed and processedAt', async () => {
  const db = await setupDb()
  const [inserted] = await db
    .insert(jobsTable)
    .values({
      id: jobId('000000000005'),
      type: 'done',
      payload: {},
      status: 'processing',
    })
    .returning()

  await markJobCompleted(db, inserted!.id)

  const [row] = await db.select().from(jobsTable)
  expect(row!.status).toBe('completed')
  expect(row!.processedAt).toBeInstanceOf(Date)
})

it('concurrent claim does not double-claim the same row (SKIP LOCKED)', async () => {
  const db = await setupDb()
  await db.insert(jobsTable).values({
    id: jobId('000000000002'),
    type: 'solo',
    payload: {},
    status: 'pending',
  })

  const [first, second] = await Promise.all([
    db.transaction(async (tx) => {
      const rows = await claim(tx, { limit: 1 })
      await new Promise((r) => setTimeout(r, 30))
      return rows
    }),
    db.transaction(async (tx) => {
      await new Promise((r) => setTimeout(r, 5))
      return claim(tx, { limit: 1 })
    }),
  ])

  const totalClaimed = first.length + second.length
  expect(totalClaimed).toBe(1)
}, 15000)

it('requeueAfterFailure increments attempts and backs off scheduled_for', async () => {
  const db = await setupDb()
  const [inserted] = await db
    .insert(jobsTable)
    .values({
      id: jobId('000000000003'),
      type: 'retry',
      payload: {},
      status: 'processing',
      attempts: 1,
      maxAttempts: 5,
    })
    .returning()

  const requeued = await requeueAfterFailure(db, inserted!.id, 'transient')
  expect(rowStatus(requeued!)).toBe('pending')
  expect(rowAttempts(requeued!)).toBe(2)
  expect(rowLastError(requeued!)).toBe('transient')
})

it('requeueAfterFailure at maxAttempts → failed + last_error', async () => {
  const db = await setupDb()
  const [inserted] = await db
    .insert(jobsTable)
    .values({
      id: jobId('000000000004'),
      type: 'dead',
      payload: {},
      status: 'processing',
      attempts: 4,
      maxAttempts: 5,
    })
    .returning()

  const failed = await requeueAfterFailure(db, inserted!.id, 'gave up')
  expect(rowStatus(failed!)).toBe('failed')
  expect(rowAttempts(failed!)).toBe(5)
  expect(rowLastError(failed!)).toBe('gave up')
  expect(failed!.failedAt).toBeInstanceOf(Date)
})