/**
 * AI Infrastructure Layer query helpers — system-ai.
 * Includes getUsageCounter and getExtraSpendThisMonth for accounting.ts.
 * All AI database operations; consumed by packages/ai and API routes.
 * IMPORTANT: Only this module may import raw Drizzle AI tables (no-raw-drizzle-from-routes).
 */
import { and, eq, gte, isNull, lte, sql, desc, asc, sum, count } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import {
  aiGlobalConfig,
  aiModelPricing,
  aiTierQuotas,
  aiTenantSettings,
  aiUsageLog,
  aiCreditPurchases,
} from '../schema/ai'
import { usageCounters } from '../schema/usage'
import { auditLog } from './_audit-forward'
import type {
  AIModelPricingRow,
  AITierQuotaRow,
  NewAIUsageLog,
  NewAICreditPurchase,
  NewAIModelPricing,
  NewAITierQuota,
} from '../schema/ai'

// Sentinel UUID for system-level (cross-tenant) audit rows
const SYSTEM_TENANT_ID = '00000000-0000-0000-0000-000000000000'

// ── Model types ───────────────────────────────────────────────────────────────

export interface ModelConfig {
  provider: 'anthropic' | 'openai' | 'google'
  model: string
  label: string
}

export interface GlobalConfigResult {
  mainModel: ModelConfig
  backupModels: ModelConfig[]
  useCasePrompts: Record<string, string>
}

export interface AITenantSettings {
  tenantId: string
  personalityPrompt: string | null
  useCaseOverrides: Record<string, string>
  extraUsageEnabled: boolean
  extraSpendLimitUsd: string | null
  autoReloadEnabled: boolean
  autoReloadAmountUsd: string | null
  updatedAt: Date
}

export type AIUsageLogInsert = Omit<NewAIUsageLog, 'id' | 'createdAt'>

export interface UsageLogFilter {
  tenantId?: string
  useCase?: string
  modelId?: string
  from?: Date
  to?: Date
}

export interface PageParams {
  page?: number
  pageSize?: number
}

// ── Global config ─────────────────────────────────────────────────────────────

/**
 * Retrieve the single ai_global_config row, resolving model IDs into ModelConfig objects.
 */
export async function getGlobalConfig(db: Db): Promise<GlobalConfigResult> {
  const [row] = await db
    .select()
    .from(aiGlobalConfig)
    .limit(1)

  if (!row) {
    throw new Error('ai_global_config not seeded — run seed first')
  }

  // Resolve main model
  const [mainPricing] = await db
    .select()
    .from(aiModelPricing)
    .where(eq(aiModelPricing.modelId, row.mainModelId))
    .limit(1)

  if (!mainPricing) {
    throw new Error(`Main model '${row.mainModelId}' not found in ai_model_pricing`)
  }

  const mainModel: ModelConfig = {
    provider: mainPricing.provider as 'anthropic' | 'openai' | 'google',
    model: mainPricing.modelId,
    label: mainPricing.label,
  }

  // Resolve backup models
  const backupModelIds = (row.backupModelIds as string[]) ?? []
  const backupModels: ModelConfig[] = []

  for (const modelId of backupModelIds) {
    const [pricing] = await db
      .select()
      .from(aiModelPricing)
      .where(eq(aiModelPricing.modelId, modelId))
      .limit(1)
    if (pricing) {
      backupModels.push({
        provider: pricing.provider as 'anthropic' | 'openai' | 'google',
        model: pricing.modelId,
        label: pricing.label,
      })
    }
  }

  return {
    mainModel,
    backupModels,
    useCasePrompts: (row.useCasePrompts as Record<string, string>) ?? {},
  }
}

/**
 * Update the global AI config singleton.
 */
export async function updateGlobalConfig(
  db: Db,
  patch: {
    mainModelId: string
    backupModelIds: string[]
    useCasePrompts: Record<string, string>
    updatedBy: string
  },
): Promise<void> {
  await db
    .update(aiGlobalConfig)
    .set({
      mainModelId: patch.mainModelId,
      backupModelIds: patch.backupModelIds,
      useCasePrompts: patch.useCasePrompts,
      updatedBy: patch.updatedBy,
      updatedAt: new Date(),
    })
}

// ── Model pricing ─────────────────────────────────────────────────────────────

export async function listModelPricing(
  db: Db,
  opts: { activeOnly?: boolean } = {},
): Promise<AIModelPricingRow[]> {
  const conditions = opts.activeOnly ? [eq(aiModelPricing.active, true)] : []
  return db
    .select()
    .from(aiModelPricing)
    .where(conditions.length > 0 ? and(...(conditions as [ReturnType<typeof eq>])) : undefined)
    .orderBy(asc(aiModelPricing.provider), asc(aiModelPricing.label))
}

export async function getModelPricing(
  db: Db,
  modelId: string,
): Promise<{
  inputCostPer1m: number
  outputCostPer1m: number
  provider: string
  isVisionCapable: boolean
} | null> {
  const [row] = await db
    .select()
    .from(aiModelPricing)
    .where(eq(aiModelPricing.modelId, modelId))
    .limit(1)
  if (!row) return null
  return {
    inputCostPer1m: parseFloat(row.inputCostPer1m),
    outputCostPer1m: parseFloat(row.outputCostPer1m),
    provider: row.provider,
    isVisionCapable: row.isVisionCapable,
  }
}

export async function addModel(db: Db, row: NewAIModelPricing): Promise<void> {
  await db.insert(aiModelPricing).values(row)
}

export async function updateModelPricing(
  db: Db,
  modelId: string,
  patch: Partial<Omit<NewAIModelPricing, 'modelId' | 'createdAt'>>,
): Promise<void> {
  await db
    .update(aiModelPricing)
    .set({ ...patch, updatedAt: new Date() })
    .where(eq(aiModelPricing.modelId, modelId))
}

export async function setModelActive(db: Db, modelId: string, active: boolean): Promise<void> {
  await db
    .update(aiModelPricing)
    .set({ active, updatedAt: new Date() })
    .where(eq(aiModelPricing.modelId, modelId))
}

// ── Tier quotas ───────────────────────────────────────────────────────────────

/**
 * Get current tier quota rows (effective_to IS NULL = current).
 */
export async function getTierQuotas(db: Db): Promise<AITierQuotaRow[]> {
  return db
    .select()
    .from(aiTierQuotas)
    .where(isNull(aiTierQuotas.effectiveTo))
    .orderBy(asc(aiTierQuotas.tier))
}

/**
 * Upsert a tier quota: close prior current row (set effective_to = today),
 * insert new row with effective_from = today, effective_to = NULL.
 */
export async function upsertTierQuota(
  db: Db,
  tier: string,
  patch: {
    modelId: string
    monthlyTokens: number
    extraAllowed: boolean
    extraMaxUsd?: string | null
  },
): Promise<void> {
  const today = new Date().toISOString().slice(0, 10)

  await db.transaction(async (tx) => {
    // Close prior current row
    await tx
      .update(aiTierQuotas)
      .set({ effectiveTo: today })
      .where(and(eq(aiTierQuotas.tier, tier), isNull(aiTierQuotas.effectiveTo)))

    // Insert new current row
    const newRow: NewAITierQuota = {
      tier,
      modelId: patch.modelId,
      monthlyTokens: patch.monthlyTokens,
      extraAllowed: patch.extraAllowed,
      extraMaxUsd: patch.extraMaxUsd ?? null,
      effectiveFrom: today,
      effectiveTo: null,
    }
    const [inserted] = await tx.insert(aiTierQuotas).values(newRow).returning()

    await tx.insert(auditLog).values({
      tenantId: SYSTEM_TENANT_ID,
      actorId: null,
      actorType: 'system',
      entityType: 'ai_tier_quota',
      entityId: `${inserted!.tier}:${inserted!.effectiveFrom}`,
      action: 'ai_tier_quota.upserted',
    })
  })
}

// ── Tenant settings ───────────────────────────────────────────────────────────

const DEFAULT_TENANT_SETTINGS: Omit<AITenantSettings, 'tenantId'> = {
  personalityPrompt: null,
  useCaseOverrides: {},
  extraUsageEnabled: false,
  extraSpendLimitUsd: null,
  autoReloadEnabled: false,
  autoReloadAmountUsd: null,
  updatedAt: new Date(0),
}

export async function getTenantSettings(db: Db, tenantId: string): Promise<AITenantSettings> {
  const [row] = await db
    .select()
    .from(aiTenantSettings)
    .where(eq(aiTenantSettings.tenantId, tenantId))
    .limit(1)

  if (!row) {
    return { ...DEFAULT_TENANT_SETTINGS, tenantId }
  }

  return {
    tenantId: row.tenantId,
    personalityPrompt: row.personalityPrompt,
    useCaseOverrides: (row.useCaseOverrides as Record<string, string>) ?? {},
    extraUsageEnabled: row.extraUsageEnabled,
    extraSpendLimitUsd: row.extraSpendLimitUsd,
    autoReloadEnabled: row.autoReloadEnabled,
    autoReloadAmountUsd: row.autoReloadAmountUsd,
    updatedAt: row.updatedAt,
  }
}

export async function upsertTenantSettings(
  db: Db,
  tenantId: string,
  patch: Partial<Omit<AITenantSettings, 'tenantId' | 'updatedAt'>>,
): Promise<void> {
  const now = new Date()
  await db
    .insert(aiTenantSettings)
    .values({
      tenantId,
      personalityPrompt: patch.personalityPrompt ?? null,
      useCaseOverrides: patch.useCaseOverrides ?? {},
      extraUsageEnabled: patch.extraUsageEnabled ?? false,
      extraSpendLimitUsd: patch.extraSpendLimitUsd ?? null,
      autoReloadEnabled: patch.autoReloadEnabled ?? false,
      autoReloadAmountUsd: patch.autoReloadAmountUsd ?? null,
      updatedAt: now,
    })
    .onConflictDoUpdate({
      target: [aiTenantSettings.tenantId],
      set: {
        ...('personalityPrompt' in patch ? { personalityPrompt: patch.personalityPrompt } : {}),
        ...('useCaseOverrides' in patch ? { useCaseOverrides: patch.useCaseOverrides } : {}),
        ...('extraUsageEnabled' in patch ? { extraUsageEnabled: patch.extraUsageEnabled } : {}),
        ...('extraSpendLimitUsd' in patch ? { extraSpendLimitUsd: patch.extraSpendLimitUsd } : {}),
        ...('autoReloadEnabled' in patch ? { autoReloadEnabled: patch.autoReloadEnabled } : {}),
        ...('autoReloadAmountUsd' in patch ? { autoReloadAmountUsd: patch.autoReloadAmountUsd } : {}),
        updatedAt: now,
      },
    })
}

// ── Usage log ─────────────────────────────────────────────────────────────────

export async function insertUsageLog(db: Db | DbTx, row: AIUsageLogInsert): Promise<void> {
  await db.insert(aiUsageLog).values({
    ...row,
    id: undefined, // let DB generate
  })
}

export async function listUsageLog(
  db: Db,
  filter: UsageLogFilter,
  page: PageParams = {},
): Promise<{ rows: typeof aiUsageLog.$inferSelect[]; total: number }> {
  const pageSize = page.pageSize ?? 50
  const offset = ((page.page ?? 1) - 1) * pageSize

  const conditions = []
  if (filter.tenantId) conditions.push(eq(aiUsageLog.tenantId, filter.tenantId))
  if (filter.useCase) conditions.push(eq(aiUsageLog.useCase, filter.useCase))
  if (filter.modelId) conditions.push(eq(aiUsageLog.modelId, filter.modelId))
  if (filter.from) conditions.push(gte(aiUsageLog.createdAt, filter.from))
  if (filter.to) conditions.push(lte(aiUsageLog.createdAt, filter.to))

  const where = conditions.length > 0 ? and(...(conditions as [ReturnType<typeof eq>])) : undefined

  const [rows, totalResult] = await Promise.all([
    db
      .select()
      .from(aiUsageLog)
      .where(where)
      .orderBy(desc(aiUsageLog.createdAt))
      .limit(pageSize)
      .offset(offset),
    db
      .select({ total: count() })
      .from(aiUsageLog)
      .where(where),
  ])

  return { rows, total: totalResult[0]?.total ?? 0 }
}

export async function tenantUsageBreakdown(
  db: Db,
  tenantId: string,
  range: { from: Date; to: Date },
): Promise<{ useCase: string; calls: number; totalTokens: number; percentOfUsage: number }[]> {
  const rows = await db
    .select({
      useCase: aiUsageLog.useCase,
      calls: count(),
      totalTokens: sum(aiUsageLog.totalTokens).mapWith(Number),
    })
    .from(aiUsageLog)
    .where(
      and(
        eq(aiUsageLog.tenantId, tenantId),
        gte(aiUsageLog.createdAt, range.from),
        lte(aiUsageLog.createdAt, range.to),
        isNull(aiUsageLog.error),
      ),
    )
    .groupBy(aiUsageLog.useCase)

  const totalTokens = rows.reduce((sum, r) => sum + (r.totalTokens ?? 0), 0)
  return rows.map((r) => ({
    useCase: r.useCase,
    calls: r.calls,
    totalTokens: r.totalTokens ?? 0,
    percentOfUsage: totalTokens > 0 ? Math.round(((r.totalTokens ?? 0) / totalTokens) * 100) : 0,
  }))
}

// ── Analytics aggregations ────────────────────────────────────────────────────

export async function tokensByUseCase(
  db: Db,
  period: string,
): Promise<{ useCase: string; totalTokens: number }[]> {
  const startDate = new Date(`${period}-01`)
  const endDate = new Date(startDate)
  endDate.setMonth(endDate.getMonth() + 1)

  return db
    .select({
      useCase: aiUsageLog.useCase,
      totalTokens: sum(aiUsageLog.totalTokens).mapWith(Number),
    })
    .from(aiUsageLog)
    .where(and(gte(aiUsageLog.createdAt, startDate), lte(aiUsageLog.createdAt, endDate)))
    .groupBy(aiUsageLog.useCase)
    .orderBy(desc(sum(aiUsageLog.totalTokens)))
    .then((rows) =>
      rows.map((r) => ({
        useCase: r.useCase,
        totalTokens: r.totalTokens ?? 0,
      })),
    )
}

export async function costByTenant(
  db: Db,
  period: string,
): Promise<{ tenantId: string; totalCostUsd: number }[]> {
  const startDate = new Date(`${period}-01`)
  const endDate = new Date(startDate)
  endDate.setMonth(endDate.getMonth() + 1)

  return db
    .select({
      tenantId: aiUsageLog.tenantId,
      totalCostUsd: sum(aiUsageLog.costUsd).mapWith(Number),
    })
    .from(aiUsageLog)
    .where(and(gte(aiUsageLog.createdAt, startDate), lte(aiUsageLog.createdAt, endDate)))
    .groupBy(aiUsageLog.tenantId)
    .orderBy(desc(sum(aiUsageLog.costUsd)))
    .then((rows) =>
      rows.map((r) => ({
        tenantId: r.tenantId,
        totalCostUsd: r.totalCostUsd ?? 0,
      })),
    )
}

export async function errorRateByModel(
  db: Db,
  period: string,
): Promise<{ modelId: string; totalCalls: number; errorCalls: number; errorRate: number }[]> {
  const startDate = new Date(`${period}-01`)
  const endDate = new Date(startDate)
  endDate.setMonth(endDate.getMonth() + 1)

  const rows = await db
    .select({
      modelId: aiUsageLog.modelId,
      totalCalls: count(),
      errorCalls: sum(sql<number>`CASE WHEN ${aiUsageLog.error} IS NOT NULL THEN 1 ELSE 0 END`).mapWith(Number),
    })
    .from(aiUsageLog)
    .where(and(gte(aiUsageLog.createdAt, startDate), lte(aiUsageLog.createdAt, endDate)))
    .groupBy(aiUsageLog.modelId)

  return rows.map((r) => ({
    modelId: r.modelId,
    totalCalls: r.totalCalls,
    errorCalls: r.errorCalls ?? 0,
    errorRate: r.totalCalls > 0 ? ((r.errorCalls ?? 0) / r.totalCalls) * 100 : 0,
  }))
}

export async function costTrend(
  db: Db,
  months: number = 6,
): Promise<{ period: string; totalCostUsd: number }[]> {
  const result: { period: string; totalCostUsd: number }[] = []
  const now = new Date()

  for (let i = months - 1; i >= 0; i--) {
    const d = new Date(now.getFullYear(), now.getMonth() - i, 1)
    const period = `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}`
    const startDate = new Date(`${period}-01`)
    const endDate = new Date(startDate)
    endDate.setMonth(endDate.getMonth() + 1)

    const [row] = await db
      .select({ totalCostUsd: sum(aiUsageLog.costUsd).mapWith(Number) })
      .from(aiUsageLog)
      .where(and(gte(aiUsageLog.createdAt, startDate), lte(aiUsageLog.createdAt, endDate)))

    result.push({ period, totalCostUsd: row?.totalCostUsd ?? 0 })
  }

  return result
}

// ── Credit purchases ──────────────────────────────────────────────────────────

export async function getCreditPurchases(
  db: Db,
  tenantId: string,
): Promise<typeof aiCreditPurchases.$inferSelect[]> {
  return db
    .select()
    .from(aiCreditPurchases)
    .where(eq(aiCreditPurchases.tenantId, tenantId))
    .orderBy(desc(aiCreditPurchases.createdAt))
}

export async function insertCreditPurchase(
  db: Db,
  row: Omit<NewAICreditPurchase, 'id' | 'createdAt'>,
): Promise<typeof aiCreditPurchases.$inferSelect> {
  const [inserted] = await db.insert(aiCreditPurchases).values(row).returning()
  return inserted!
}

/**
 * Deduct purchased credits FIFO from non-expired rows with tokens_remaining > 0.
 * Returns the actual number of tokens deducted (may be less than requested if balance insufficient).
 */
export async function deductPurchasedCredit(
  db: Db | DbTx,
  tenantId: string,
  tokens: number,
): Promise<number> {
  let remaining = tokens
  let totalDeducted = 0

  const purchases = await db
    .select()
    .from(aiCreditPurchases)
    .where(
      and(
        eq(aiCreditPurchases.tenantId, tenantId),
        sql`${aiCreditPurchases.tokensRemaining} > 0`,
        sql`(${aiCreditPurchases.expiresAt} IS NULL OR ${aiCreditPurchases.expiresAt} > NOW())`,
      ),
    )
    .orderBy(asc(aiCreditPurchases.createdAt))

  for (const purchase of purchases) {
    if (remaining <= 0) break
    const available = purchase.tokensRemaining
    const deduct = Math.min(available, remaining)
    await db
      .update(aiCreditPurchases)
      .set({ tokensRemaining: available - deduct })
      .where(eq(aiCreditPurchases.id, purchase.id))
    totalDeducted += deduct
    remaining -= deduct
  }

  return totalDeducted
}

// ── Accounting helpers ────────────────────────────────────────────────────────

/**
 * Atomically increment a usage counter by an arbitrary amount (for token accounting).
 * Uses the same ON CONFLICT upsert pattern as incrementCounter but accepts an `amount`
 * so that each AI call credits its actual token count, not just 1.
 */
export async function incrementCounterBy(
  db: Db | DbTx,
  tenantId: string,
  key: string,
  period: string,
  amount: number,
): Promise<number> {
  if (amount <= 0) return getUsageCounter(db, tenantId, key, period)
  const [row] = await db
    .insert(usageCounters)
    .values({ tenantId, counterKey: key, period, count: amount })
    .onConflictDoUpdate({
      target: [usageCounters.tenantId, usageCounters.counterKey, usageCounters.period],
      set: { count: sql`${usageCounters.count} + ${amount}` },
    })
    .returning({ count: usageCounters.count })
  return row?.count ?? 0
}

/**
 * Read a usage counter value for a tenant/key/period.
 * Returns 0 if no row exists.
 */
export async function getUsageCounter(
  db: Db | DbTx,
  tenantId: string,
  key: string,
  period: string,
): Promise<number> {
  const [row] = await db
    .select({ count: usageCounters.count })
    .from(usageCounters)
    .where(
      and(
        eq(usageCounters.tenantId, tenantId),
        eq(usageCounters.counterKey, key),
        eq(usageCounters.period, period),
      ),
    )
    .limit(1)
  return row?.count ?? 0
}

/**
 * Sum of extra-billed USD spend for a tenant in a given YYYY-MM period.
 */
export async function getExtraSpendThisMonth(
  db: Db,
  tenantId: string,
  period: string,
): Promise<number> {
  const startDate = new Date(`${period}-01`)
  const endDate = new Date(startDate)
  endDate.setMonth(endDate.getMonth() + 1)

  const [row] = await db
    .select({ totalExtraUsd: sum(aiUsageLog.costUsd).mapWith(Number) })
    .from(aiUsageLog)
    .where(
      and(
        eq(aiUsageLog.tenantId, tenantId),
        eq(aiUsageLog.billedFrom, 'extra'),
        gte(aiUsageLog.createdAt, startDate),
        lte(aiUsageLog.createdAt, endDate),
      ),
    )

  return row?.totalExtraUsd ?? 0
}