/**
 * AI chat query helpers — ai-assistant.
 *
 * All helpers are tenant-scoped. Route files and queue consumers must import
 * from this module (via `@zync/db/queries`) — never raw Drizzle tables directly.
 */
import { and, eq, desc, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { aiChatSessions, aiChatMessages } from '../schema'

// ── Public interfaces ─────────────────────────────────────────────────────────

export interface AiChatSession {
  id: string
  tenantId: string
  /** Null for bot-channel (telegram/whatsapp) sessions where no in-app user exists. */
  userId: string | null
  title: string | null
  channel: 'in_app' | 'telegram' | 'whatsapp'
  createdAt: string
  updatedAt: string
}

export interface AiChatMessage {
  id: string
  sessionId: string
  tenantId: string
  role: 'user' | 'assistant' | 'system'
  content: string
  tokensUsed: number | null
  metadata: Record<string, unknown>
  createdAt: string
}

// ── Row mappers ───────────────────────────────────────────────────────────────

function mapSession(row: typeof aiChatSessions.$inferSelect): AiChatSession {
  return {
    id: row.id,
    tenantId: row.tenantId,
    userId: row.userId ?? null,
    title: row.title ?? null,
    channel: row.channel as 'in_app' | 'telegram' | 'whatsapp',
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
  }
}

function mapMessage(row: typeof aiChatMessages.$inferSelect): AiChatMessage {
  return {
    id: row.id,
    sessionId: row.sessionId,
    tenantId: row.tenantId,
    role: row.role as 'user' | 'assistant' | 'system',
    content: row.content,
    tokensUsed: row.tokensUsed ?? null,
    metadata: (row.metadata as Record<string, unknown>) ?? {},
    createdAt: row.createdAt.toISOString(),
  }
}

// ── Session queries ───────────────────────────────────────────────────────────

export async function createAiChatSession(
  db: Db,
  tenantId: string,
  input: {
    userId: string | null
    channel: 'in_app' | 'telegram' | 'whatsapp'
    title?: string
  },
): Promise<{ id: string }> {
  const [row] = await db
    .insert(aiChatSessions)
    .values({
      tenantId,
      userId: input.userId,
      channel: input.channel,
      title: input.title ?? null,
    })
    .returning({ id: aiChatSessions.id })

  if (!row) throw new Error('Failed to create AI chat session')
  return { id: row.id }
}

export async function getAiChatSession(
  db: Db,
  tenantId: string,
  sessionId: string,
): Promise<AiChatSession | null> {
  const [row] = await db
    .select()
    .from(aiChatSessions)
    .where(and(eq(aiChatSessions.tenantId, tenantId), eq(aiChatSessions.id, sessionId)))
    .limit(1)

  return row ? mapSession(row) : null
}

export async function listAiChatSessions(
  db: Db,
  tenantId: string,
  userId: string,
  limit: number,
): Promise<Array<{ id: string; title: string | null; updatedAt: string }>> {
  const rows = await db
    .select({
      id: aiChatSessions.id,
      title: aiChatSessions.title,
      updatedAt: aiChatSessions.updatedAt,
    })
    .from(aiChatSessions)
    .where(and(eq(aiChatSessions.tenantId, tenantId), eq(aiChatSessions.userId, userId)))
    .orderBy(desc(aiChatSessions.updatedAt))
    .limit(limit)

  return rows.map((r) => ({
    id: r.id,
    title: r.title ?? null,
    updatedAt: r.updatedAt.toISOString(),
  }))
}

export async function deleteAiChatSession(
  db: Db,
  tenantId: string,
  sessionId: string,
): Promise<void> {
  await db
    .delete(aiChatSessions)
    .where(and(eq(aiChatSessions.tenantId, tenantId), eq(aiChatSessions.id, sessionId)))
}

export async function touchAiChatSession(
  db: Db,
  tenantId: string,
  sessionId: string,
): Promise<void> {
  await db
    .update(aiChatSessions)
    .set({ updatedAt: new Date() })
    .where(and(eq(aiChatSessions.tenantId, tenantId), eq(aiChatSessions.id, sessionId)))
}

// ── Message queries ───────────────────────────────────────────────────────────

export async function appendAiChatMessage(
  db: Db,
  tenantId: string,
  input: {
    sessionId: string
    role: 'user' | 'assistant' | 'system'
    content: string
    tokensUsed?: number
    metadata?: Record<string, unknown>
  },
): Promise<{ id: string }> {
  const [row] = await db
    .insert(aiChatMessages)
    .values({
      sessionId: input.sessionId,
      tenantId,
      role: input.role,
      content: input.content,
      tokensUsed: input.tokensUsed ?? null,
      metadata: input.metadata ?? {},
    })
    .returning({ id: aiChatMessages.id })

  if (!row) throw new Error('Failed to append AI chat message')
  return { id: row.id }
}

export async function getAiChatMessages(
  db: Db,
  tenantId: string,
  sessionId: string,
): Promise<AiChatMessage[]> {
  const rows = await db
    .select()
    .from(aiChatMessages)
    .where(and(eq(aiChatMessages.tenantId, tenantId), eq(aiChatMessages.sessionId, sessionId)))
    .orderBy(aiChatMessages.createdAt)

  return rows.map(mapMessage)
}

/**
 * Return the last `n` messages for a session, in chronological order.
 * Used to cap context window for the AI assistant.
 */
export async function lastAiChatMessages(
  db: Db,
  tenantId: string,
  sessionId: string,
  n: number,
): Promise<Array<{ role: string; content: string }>> {
  // Fetch n newest DESC, then reverse to get chronological order
  const rows = await db
    .select({ role: aiChatMessages.role, content: aiChatMessages.content, createdAt: aiChatMessages.createdAt })
    .from(aiChatMessages)
    .where(and(eq(aiChatMessages.tenantId, tenantId), eq(aiChatMessages.sessionId, sessionId)))
    .orderBy(desc(aiChatMessages.createdAt))
    .limit(n)

  return rows.reverse().map((r) => ({ role: r.role, content: r.content }))
}

/**
 * Return the last `n` messages scoped to a Telegram chat ID.
 * Used by the Telegram AI flow for per-chat context (no in-app session).
 */
export async function lastTelegramAiMessages(
  db: Db,
  tenantId: string,
  telegramChatId: string,
  n: number,
): Promise<Array<{ role: string; content: string }>> {
  const rows = await db
    .select({ role: aiChatMessages.role, content: aiChatMessages.content, createdAt: aiChatMessages.createdAt })
    .from(aiChatMessages)
    .where(
      and(
        eq(aiChatMessages.tenantId, tenantId),
        sql`${aiChatMessages.metadata} ->> 'telegram_chat_id' = ${telegramChatId}`,
      ),
    )
    .orderBy(desc(aiChatMessages.createdAt))
    .limit(n)

  return rows.reverse().map((r) => ({ role: r.role, content: r.content }))
}

/**
 * Return the last `n` messages scoped to a WhatsApp chat ID.
 * Used by the WhatsApp AI flow for per-chat context parity with Telegram.
 */
export async function lastWhatsAppAiMessages(
  db: Db,
  tenantId: string,
  whatsappChatId: string,
  n: number,
): Promise<Array<{ role: string; content: string }>> {
  const rows = await db
    .select({ role: aiChatMessages.role, content: aiChatMessages.content, createdAt: aiChatMessages.createdAt })
    .from(aiChatMessages)
    .where(
      and(
        eq(aiChatMessages.tenantId, tenantId),
        sql`${aiChatMessages.metadata} ->> 'whatsapp_chat_id' = ${whatsappChatId}`,
      ),
    )
    .orderBy(desc(aiChatMessages.createdAt))
    .limit(n)

  return rows.reverse().map((r) => ({ role: r.role, content: r.content }))
}