import { executeRows } from '../execute-rows.js';
import { eq, sql } from 'drizzle-orm';
import { QUEUE_DEFAULT_PROMPTS } from '@/server/ai/queue-defaults.js';
import type { DrizzleClient } from '@/server/db/client.js';
import { llmQueues } from '@/server/db/schema.js';
import type { LlmQueue, LlmQueueId, LlmQueueChainEntry } from '@/server/db/schema.js';

export type { LlmQueue, LlmQueueId, LlmQueueChainEntry };

export interface EnrichedChainEntry {
  llmProviderId: string;
  providerName: string;
  providerType: string;
  model: string;
}

export interface EnrichedLlmQueue {
  id: string;
  name: string;
  chain: EnrichedChainEntry[];
  prompt: string;
  updatedAt: Date;
}

export async function getLlmQueue(db: DrizzleClient, id: LlmQueueId): Promise<LlmQueue | null> {
  const rows = await db.select().from(llmQueues).where(eq(llmQueues.id, id));
  return rows[0] ?? null;
}

export async function setLlmQueue(
  db: DrizzleClient,
  id: LlmQueueId,
  chain: LlmQueueChainEntry[],
  prompt: string,
  updatedBy: string,
): Promise<void> {
  await db
    .update(llmQueues)
    .set({
      chain: chain as unknown as (typeof llmQueues.$inferInsert)['chain'],
      prompt,
      updatedAt: new Date(),
      updatedBy,
    })
    .where(eq(llmQueues.id, id));
}

export async function getAllLlmQueuesEnriched(db: DrizzleClient): Promise<EnrichedLlmQueue[]> {
  const result = await db.execute<{
    id: string;
    name: string;
    chain_enriched: string;
    prompt: string;
    updated_at: Date;
  }>(sql`
    SELECT
      q.id,
      q.name,
      q.prompt,
      q.updated_at,
      COALESCE(
        jsonb_agg(
          jsonb_build_object(
            'llmProviderId', entry->>'llmProviderId',
            'providerName',  COALESCE(p.name, 'Unknown'),
            'providerType',  COALESCE(p.type::text, 'unknown'),
            'model',         entry->>'model'
          )
          ORDER BY ordinality
        ) FILTER (WHERE entry IS NOT NULL AND jsonb_array_length(q.chain) > 0),
        '[]'::jsonb
      ) AS chain_enriched
    FROM llm_queues q
    LEFT JOIN LATERAL jsonb_array_elements(q.chain) WITH ORDINALITY AS arr(entry, ordinality)
      ON jsonb_array_length(q.chain) > 0
    LEFT JOIN llm_providers p ON p.id::text = entry->>'llmProviderId'
    GROUP BY q.id, q.name, q.prompt, q.updated_at
    ORDER BY q.id
  `);

  return executeRows<{
    id: string;
    name: string;
    chain_enriched: string;
    prompt: string;
    updated_at: Date;
  }>(result).map((row) => ({
    id: row.id,
    name: row.name,
    prompt: row.prompt || QUEUE_DEFAULT_PROMPTS[row.id as keyof typeof QUEUE_DEFAULT_PROMPTS] || '',
    updatedAt: row.updated_at,
    chain: (typeof row.chain_enriched === 'string'
      ? JSON.parse(row.chain_enriched)
      : row.chain_enriched) as EnrichedChainEntry[],
  }));
}