import { sql, eq } from 'drizzle-orm'
import {
  integer,
  jsonb,
  pgTable,
  text,
  timestamp,
  uuid,
} from 'drizzle-orm/pg-core'
import type { Querier } from '@platform-modules/db'

export const jobsTable = pgTable('jobs', {
  id: uuid('id').primaryKey().defaultRandom(),
  type: text('type').notNull(),
  payload: jsonb('payload').notNull(),
  status: text('status').notNull().default('pending'),
  scheduledFor: timestamp('scheduled_for', { withTimezone: true }).notNull().defaultNow(),
  attempts: integer('attempts').notNull().default(0),
  maxAttempts: integer('max_attempts').notNull().default(5),
  lastError: text('last_error'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  processedAt: timestamp('processed_at', { withTimezone: true }),
  failedAt: timestamp('failed_at', { withTimezone: true }),
})

export type JobRow = typeof jobsTable.$inferSelect
export type NewJobRow = typeof jobsTable.$inferInsert

const schema = { jobs: jobsTable }
type JobsSchema = typeof schema

export async function claim<S extends JobsSchema>(
  db: Querier<S>,
  opts: { limit: number },
): Promise<JobRow[]> {
  const limit = opts.limit
  // Spec SQL preserved verbatim as the WHERE subquery; routed through the Drizzle
  // update builder + `.returning()` so columns hydrate to camelCase JobRow (Date,
  // not raw snake_case strings). Drizzle has no UPDATE…LIMIT → subquery-LIMIT form.
  return db
    .update(jobsTable)
    .set({ status: 'processing' })
    .where(sql`id IN (
      SELECT id FROM jobs
      WHERE status = 'pending' AND scheduled_for <= NOW()
      ORDER BY scheduled_for
      FOR UPDATE SKIP LOCKED
      LIMIT ${limit}
    )`)
    .returning()
}

export async function requeueAfterFailure<S extends JobsSchema>(
  db: Querier<S>,
  jobId: string,
  error: string,
): Promise<JobRow | undefined> {
  const [row] = await db
    .select()
    .from(jobsTable)
    .where(eq(jobsTable.id, jobId))
    .limit(1)
  if (!row) return undefined

  const nextAttempts = row.attempts + 1
  if (nextAttempts >= row.maxAttempts) {
    const [failed] = await db
      .update(jobsTable)
      .set({
        status: 'failed',
        attempts: nextAttempts,
        lastError: error,
        failedAt: new Date(),
      })
      .where(eq(jobsTable.id, jobId))
      .returning()
    return failed
  }

  const delayMinutes = 2 ** nextAttempts
  const [requeued] = await db
    .update(jobsTable)
    .set({
      status: 'pending',
      attempts: nextAttempts,
      lastError: error,
      scheduledFor: sql`NOW() + (${delayMinutes} * INTERVAL '1 minute')`,
    })
    .where(eq(jobsTable.id, jobId))
    .returning()
  return requeued
}

export async function markJobCompleted<S extends JobsSchema>(
  db: Querier<S>,
  jobId: string,
): Promise<void> {
  await db
    .update(jobsTable)
    .set({ status: 'completed', processedAt: new Date() })
    .where(eq(jobsTable.id, jobId))
}

export { schema as jobsSchema }