/**
 * Admin server query: LLM job list.
 *
 * Supports pagination and optional filtering by status and jobType.
 * Used by the /admin/system/llm-jobs page.
 */

import { desc, eq, and, type SQL } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { llmJobs } from '@/server/db/schema.js';

// ─── Types ────────────────────────────────────────────────────────────────────

export interface LlmJobRow {
  id: string;
  jobType: string;
  targetType: string;
  status: string;
  modelName: string | null;
  totalTokens: number | null;
  /** Stored as numeric string in DB; use parseFloat before display. */
  costUsd: string | null;
  createdAt: string;
  // diagnostics (added 2026-06-10)
  retryCount: number;
  lastError: string | null;
  startedAt: string | null;
  completedAt: string | null;
}

export interface LlmJobDetail extends LlmJobRow {
  targetId: string;
  inputPayload: unknown;
  outputPayload: unknown;
  promptSnapshot: string | null;
  decision: string | null;
  flagReason: string | null;
  promptTokens: number | null;
  completionTokens: number | null;
}

export interface ListLlmJobsInput {
  limit: number;
  offset: number;
  status?: string;
  jobType?: string;
}

export interface ListLlmJobsResult {
  data: LlmJobRow[];
  total: number;
}

// ─── Query ────────────────────────────────────────────────────────────────────

/**
 * List LLM jobs with optional status/jobType filters and pagination.
 *
 * Returns most-recently-created jobs first.
 */
export async function listLlmJobs(
  db: DrizzleClient,
  input: ListLlmJobsInput,
): Promise<ListLlmJobsResult> {
  const { limit, offset, status, jobType } = input;

  const conditions: SQL[] = [];

  if (status) {
    conditions.push(eq(llmJobs.status, status as typeof llmJobs.status._.data));
  }
  if (jobType) {
    conditions.push(eq(llmJobs.jobType, jobType as typeof llmJobs.jobType._.data));
  }

  const where = conditions.length > 0 ? and(...conditions) : undefined;

  const [rows, countRows] = await Promise.all([
    db
      .select({
        id: llmJobs.id,
        jobType: llmJobs.jobType,
        targetType: llmJobs.targetType,
        status: llmJobs.status,
        modelName: llmJobs.modelName,
        totalTokens: llmJobs.totalTokens,
        costUsd: llmJobs.costUsd,
        createdAt: llmJobs.createdAt,
        retryCount: llmJobs.retryCount,
        lastError: llmJobs.lastError,
        startedAt: llmJobs.startedAt,
        completedAt: llmJobs.completedAt,
      })
      .from(llmJobs)
      .where(where)
      .orderBy(desc(llmJobs.createdAt))
      .limit(limit)
      .offset(offset),

    db.select({ id: llmJobs.id }).from(llmJobs).where(where),
  ]);

  return {
    data: rows.map((r) => ({
      id: r.id,
      jobType: r.jobType,
      targetType: r.targetType,
      status: r.status,
      modelName: r.modelName,
      totalTokens: r.totalTokens,
      costUsd: r.costUsd,
      createdAt: r.createdAt.toISOString(),
      retryCount: r.retryCount,
      lastError: r.lastError,
      startedAt: r.startedAt ? r.startedAt.toISOString() : null,
      completedAt: r.completedAt ? r.completedAt.toISOString() : null,
    })),
    total: countRows.length,
  };
}

/**
 * Fetch a single LLM job with full diagnostic payloads.
 * Returns null when no row matches.
 */
export async function getLlmJob(db: DrizzleClient, id: string): Promise<LlmJobDetail | null> {
  const [r] = await db
    .select({
      id: llmJobs.id,
      jobType: llmJobs.jobType,
      targetType: llmJobs.targetType,
      status: llmJobs.status,
      modelName: llmJobs.modelName,
      totalTokens: llmJobs.totalTokens,
      costUsd: llmJobs.costUsd,
      createdAt: llmJobs.createdAt,
      retryCount: llmJobs.retryCount,
      lastError: llmJobs.lastError,
      startedAt: llmJobs.startedAt,
      completedAt: llmJobs.completedAt,
      targetId: llmJobs.targetId,
      inputPayload: llmJobs.inputPayload,
      outputPayload: llmJobs.outputPayload,
      promptSnapshot: llmJobs.promptSnapshot,
      decision: llmJobs.decision,
      flagReason: llmJobs.flagReason,
      promptTokens: llmJobs.promptTokens,
      completionTokens: llmJobs.completionTokens,
    })
    .from(llmJobs)
    .where(eq(llmJobs.id, id))
    .limit(1);
  if (!r) return null;
  return {
    id: r.id,
    jobType: r.jobType,
    targetType: r.targetType,
    status: r.status,
    modelName: r.modelName,
    totalTokens: r.totalTokens,
    costUsd: r.costUsd,
    createdAt: r.createdAt.toISOString(),
    retryCount: r.retryCount,
    lastError: r.lastError,
    startedAt: r.startedAt ? r.startedAt.toISOString() : null,
    completedAt: r.completedAt ? r.completedAt.toISOString() : null,
    targetId: r.targetId,
    inputPayload: r.inputPayload,
    outputPayload: r.outputPayload,
    promptSnapshot: r.promptSnapshot,
    decision: r.decision,
    flagReason: r.flagReason,
    promptTokens: r.promptTokens,
    completionTokens: r.completionTokens,
  };
}