/**
 * Analytics aggregation service — admin-reports-analytics.
 *
 * Two cross-tenant aggregation functions for the /admin/analytics endpoint:
 *   - getModuleAdoptionReport : feature adoption from tenant_audit_log
 *   - getAiUsageReport        : token/cost totals + top consumers from ai_usage_log
 *
 * All queries use the un-scoped Db handle (cross-tenant). Never call tenantQuery here.
 */
import { sql } from '@zync/db'
import type { Db } from '@zync/db/queries'
import {
  MODULE_EVENT_PREFIXES,
  type DateRange,
  type ModuleAdoptionReport,
  type ModuleAdoptionRow,
  type AiUsageReport,
  type AiConsumerRow,
} from './types'

/** Parse a YYYY-MM-DD string to a Date (UTC midnight). */
function parseDate(s: string): Date {
  return new Date(`${s}T00:00:00Z`)
}

// ── getModuleAdoptionReport ───────────────────────────────────────────────────

export async function getModuleAdoptionReport(
  db: Db,
  range: DateRange,
): Promise<ModuleAdoptionReport> {
  const fromDate = parseDate(range.from)
  const toDate = parseDate(range.to)
  const toEnd = new Date(toDate.getTime() + 86400000 - 1)

  // Count active+trialing tenants as the denominator
  const activeTenantRow = await db.execute<{ cnt: string }>(sql`
    SELECT count(DISTINCT tenant_id) AS cnt
    FROM zync_subscriptions
    WHERE status IN ('active', 'trialing')
  `)
  const activeTenantCount = Number(
    (activeTenantRow?.[0] as { cnt?: string } | undefined)?.cnt ?? 0,
  )

  const rows: ModuleAdoptionRow[] = []

  for (const [moduleId, { label, prefixes }] of Object.entries(MODULE_EVENT_PREFIXES)) {
    // Build LIKE conditions for event_type + entity_type prefix matching
    const likeConditions = prefixes
      .map((p) => `event_type LIKE '${p}%' OR entity_type = '${p}'`)
      .join(' OR ')

    const moduleRows = await db.execute<{ active_tenants: string; events: string }>(sql.raw(`
      SELECT
        count(DISTINCT tenant_id) AS active_tenants,
        count(*) AS events
      FROM tenant_audit_log
      WHERE created_at >= '${fromDate.toISOString()}'
        AND created_at <= '${toEnd.toISOString()}'
        AND (${likeConditions})
    `))

    const r = moduleRows?.[0] as
      | { active_tenants?: string; events?: string }
      | undefined
    const activeTenants = Number(r?.active_tenants ?? 0)
    const events = Number(r?.events ?? 0)

    rows.push({
      moduleId,
      moduleLabel: label,
      activeTenants,
      adoptionPct:
        activeTenantCount > 0
          ? Math.round((activeTenants / activeTenantCount) * 1000) / 10
          : 0,
      events,
    })
  }

  rows.sort((a, b) => b.activeTenants - a.activeTenants)

  return { activeTenantCount, rows }
}

// ── getAiUsageReport ──────────────────────────────────────────────────────────

export async function getAiUsageReport(
  db: Db,
  range: DateRange,
  usdIlsRate: number,
): Promise<AiUsageReport> {
  const fromDate = parseDate(range.from)
  const toDate = parseDate(range.to)
  const toEnd = new Date(toDate.getTime() + 86400000 - 1)

  // Platform totals
  const totalsRow = await db.execute<{
    total_input: string | null
    total_output: string | null
    total_cost_usd: string | null
  }>(sql`
    SELECT
      sum(input_tokens)::text AS total_input,
      sum(output_tokens)::text AS total_output,
      sum(cost_usd)::text AS total_cost_usd
    FROM ai_usage_log
    WHERE created_at >= ${fromDate.toISOString()}
      AND created_at <= ${toEnd.toISOString()}
  `)

  const tr = totalsRow?.[0] as
    | { total_input?: string | null; total_output?: string | null; total_cost_usd?: string | null }
    | undefined
  const totalInputTokens = Number(tr?.total_input ?? 0)
  const totalOutputTokens = Number(tr?.total_output ?? 0)
  const totalCostUsd = Number(tr?.total_cost_usd ?? 0)

  // Top 10 consumers by cost
  const consumerRows = await db.execute<{
    tenant_slug: string
    tenant_name: string
    input_tokens: string
    output_tokens: string
    cost_usd: string
  }>(sql`
    SELECT
      ten.slug AS tenant_slug,
      ten.name AS tenant_name,
      sum(al.input_tokens)::text AS input_tokens,
      sum(al.output_tokens)::text AS output_tokens,
      sum(al.cost_usd)::text AS cost_usd
    FROM ai_usage_log al
    INNER JOIN tenants ten ON ten.id = al.tenant_id
    WHERE al.created_at >= ${fromDate.toISOString()}
      AND al.created_at <= ${toEnd.toISOString()}
    GROUP BY ten.id, ten.slug, ten.name
    ORDER BY sum(al.cost_usd) DESC
    LIMIT 10
  `)

  const topConsumers: AiConsumerRow[] = (consumerRows ?? []).map((r) => ({
    tenantSlug: r.tenant_slug,
    tenantName: r.tenant_name,
    inputTokens: Number(r.input_tokens),
    outputTokens: Number(r.output_tokens),
    costIls: Math.round(Number(r.cost_usd) * usdIlsRate),
  }))

  return {
    totalInputTokens,
    totalOutputTokens,
    totalCostIls: Math.round(totalCostUsd * usdIlsRate),
    topConsumers,
  }
}