import { sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'

export type JobQueryDb = Db | DbTx

export type JobIdempotencyKeyRow = {
  key: string
  status: string
  firstSeenAt: Date
  processedAt: Date | null
  expiresAt: Date | null
  lastReleasedAt: Date | null
  releaseCount: number
  payload: unknown
}

export type PlatformJobRow = {
  id: string
  type: string
  payload: unknown
  status: 'pending' | 'processing' | 'completed' | 'failed'
  scheduledFor: Date
  attempts: number
  maxAttempts: number
  lastError: string | null
  createdAt: Date
  processedAt: Date | null
  failedAt: Date | null
}

export async function claimJobIdempotencyKey(
  db: JobQueryDb,
  key: string,
  firstSeenAt: Date,
  payload?: unknown,
): Promise<boolean> {
  const inserted = await db.execute<{ key: string }>(sql`
    INSERT INTO job_idempotency_keys ("key", "status", "first_seen_at", "payload")
    VALUES (${key}, 'processing', ${firstSeenAt}, ${payload ?? null}::jsonb)
    ON CONFLICT ("key") DO NOTHING
    RETURNING "key"
  `)
  return inserted.length > 0
}

export async function markJobIdempotencyKeyProcessed(
  db: JobQueryDb,
  key: string,
  processedAt: Date,
  expiresAt: Date | null,
): Promise<void> {
  await db.execute(sql`
    UPDATE job_idempotency_keys
    SET status = 'processed',
        processed_at = ${processedAt},
        expires_at = ${expiresAt}
    WHERE key = ${key}
  `)
}

export async function releaseJobIdempotencyKey(
  db: JobQueryDb,
  key: string,
): Promise<void> {
  await db.execute(sql`
    DELETE FROM job_idempotency_keys
    WHERE key = ${key}
      AND status = 'processing'
  `)
}

export async function listJobIdempotencyKeys(
  db: JobQueryDb,
  now: Date,
): Promise<JobIdempotencyKeyRow[]> {
  return db.execute<JobIdempotencyKeyRow>(sql`
    SELECT
      key,
      status,
      first_seen_at AS "firstSeenAt",
      processed_at AS "processedAt",
      expires_at AS "expiresAt",
      last_released_at AS "lastReleasedAt",
      release_count AS "releaseCount",
      payload
    FROM job_idempotency_keys
    WHERE expires_at IS NULL OR expires_at > ${now}
    ORDER BY first_seen_at ASC
  `)
}

export async function claimPendingPlatformJobs(
  db: JobQueryDb,
  limit: number,
): Promise<PlatformJobRow[]> {
  return db.execute<PlatformJobRow>(sql`
    UPDATE jobs
    SET status = 'processing'
    WHERE id IN (
      SELECT id
      FROM jobs
      WHERE status = 'pending' AND scheduled_for <= NOW()
      ORDER BY scheduled_for
      FOR UPDATE SKIP LOCKED
      LIMIT ${limit}
    )
    RETURNING
      id,
      type,
      payload,
      status,
      scheduled_for AS "scheduledFor",
      attempts,
      max_attempts AS "maxAttempts",
      last_error AS "lastError",
      created_at AS "createdAt",
      processed_at AS "processedAt",
      failed_at AS "failedAt"
  `)
}

export async function markPlatformJobProcessed(
  db: JobQueryDb,
  jobId: string,
): Promise<void> {
  await db.execute(sql`
    UPDATE jobs
    SET status = 'completed', processed_at = NOW()
    WHERE id = ${jobId}
  `)
}

export async function requeuePlatformJob(
  db: JobQueryDb,
  jobId: string,
  error: string,
): Promise<PlatformJobRow | undefined> {
  const [row] = await db.execute<PlatformJobRow>(sql`
    WITH current AS (
      SELECT *
      FROM jobs
      WHERE id = ${jobId}
      LIMIT 1
    )
    UPDATE jobs
    SET
      status = CASE WHEN (current.attempts + 1) >= current.max_attempts THEN 'failed' ELSE 'pending' END,
      attempts = current.attempts + 1,
      last_error = ${error},
      scheduled_for = CASE
        WHEN (current.attempts + 1) >= current.max_attempts THEN jobs.scheduled_for
        ELSE NOW() + ((POWER(2, current.attempts + 1)) * INTERVAL '1 minute')
      END,
      failed_at = CASE WHEN (current.attempts + 1) >= current.max_attempts THEN NOW() ELSE NULL END
    FROM current
    WHERE jobs.id = current.id
    RETURNING
      jobs.id,
      jobs.type,
      jobs.payload,
      jobs.status,
      jobs.scheduled_for AS "scheduledFor",
      jobs.attempts,
      jobs.max_attempts AS "maxAttempts",
      jobs.last_error AS "lastError",
      jobs.created_at AS "createdAt",
      jobs.processed_at AS "processedAt",
      jobs.failed_at AS "failedAt"
  `)
  return row
}

export async function listPlatformJobs(
  db: JobQueryDb,
  jobIds?: readonly string[],
): Promise<PlatformJobRow[]> {
  if (!jobIds || jobIds.length === 0) {
    return db.execute<PlatformJobRow>(sql`
      SELECT
        id,
        type,
        payload,
        status,
        scheduled_for AS "scheduledFor",
        attempts,
        max_attempts AS "maxAttempts",
        last_error AS "lastError",
        created_at AS "createdAt",
        processed_at AS "processedAt",
        failed_at AS "failedAt"
      FROM jobs
      ORDER BY scheduled_for ASC
    `)
  }

  return db.execute<PlatformJobRow>(sql`
    SELECT
      id,
      type,
      payload,
      status,
      scheduled_for AS "scheduledFor",
      attempts,
      max_attempts AS "maxAttempts",
      last_error AS "lastError",
      created_at AS "createdAt",
      processed_at AS "processedAt",
      failed_at AS "failedAt"
    FROM jobs
    WHERE id = ANY(${jobIds}::uuid[])
    ORDER BY scheduled_for ASC
  `)
}