/**
 * Analytics Engine read helpers — api-usage-quota-ui (wave-12).
 *
 * All queries go through the Cloudflare Analytics Engine SQL HTTP API
 * (https://api.cloudflare.com/client/v4/accounts/{account_id}/analytics_engine/sql).
 * AE writes are eventually consistent (~1–2 min lag); quota enforcement is
 * best-effort near the boundary (spec-acknowledged soft quota).
 *
 * AE schema (per writeApiUsage):
 *   index1  = tenantId
 *   blob1   = keyId
 *   blob2   = endpoint (route pattern)
 *   blob3   = method
 *   double1 = 1 (request count)
 */
import type { ApiUsageEnv } from './env'
import type { ApiKeyUsageDailyPoint, ApiKeyUsageEndpointPoint } from '@zync/types'
import { currentUtcMonthBounds } from './time'

const AE_UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i

/** AE SQL has no parameter binding — reject non-UUID values before interpolation (S10-i2-009). */
function sanitizeAeId(id: string, label: string): string {
  if (!AE_UUID_RE.test(id)) {
    throw new Error(`Invalid ${label} for Analytics Engine query`)
  }
  return id
}

const AE_SQL_URL = (accountId: string) =>
  `https://api.cloudflare.com/client/v4/accounts/${accountId}/analytics_engine/sql`

async function runAeQuery<T>(
  env: ApiUsageEnv,
  sql: string,
): Promise<T[]> {
  const url = AE_SQL_URL(env.CF_ACCOUNT_ID)
  const resp = await fetch(url, {
    method: 'POST',
    headers: {
      Authorization: `Bearer ${env.CF_ANALYTICS_READ_TOKEN}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ query: sql }),
  })

  if (!resp.ok) {
    const body = await resp.text().catch(() => '')
    throw new Error(`AE SQL query failed ${resp.status}: ${body}`)
  }

  const json = (await resp.json()) as { data: T[] }
  return json.data ?? []
}

/**
 * Total requests for a key in a UTC calendar month.
 * Used for quota enforcement in the public-API auth middleware.
 */
export async function getMonthlyUsage(
  env: ApiUsageEnv,
  tenantId: string,
  keyId: string,
  month?: string,
): Promise<number> {
  const safeTenantId = sanitizeAeId(tenantId, 'tenantId')
  const safeKeyId = sanitizeAeId(keyId, 'keyId')
  const { start, end } = currentUtcMonthBounds(month)
  const sql = `
    SELECT SUM(_sample_interval * double1) AS total
    FROM api_usage
    WHERE index1 = '${safeTenantId}'
      AND blob1  = '${safeKeyId}'
      AND timestamp >= toDateTime('${start}')
      AND timestamp <  toDateTime('${end}')
  `
  const rows = await runAeQuery<{ total: number }>(env, sql)
  return rows[0]?.total ?? 0
}

/**
 * Monthly summary: { used, daily[30], topEndpoints[5] }.
 * Used by GET /api/api-keys/:id/usage.
 */
export async function getKeyUsageSummary(
  env: ApiUsageEnv,
  tenantId: string,
  keyId: string,
  month?: string,
): Promise<number> {
  return getMonthlyUsage(env, tenantId, keyId, month)
}

/**
 * Daily request counts for the last 30 calendar days (UTC), zero-filled.
 */
export async function getDailyUsage(
  env: ApiUsageEnv,
  tenantId: string,
  keyId: string,
  month?: string,
): Promise<ApiKeyUsageDailyPoint[]> {
  const safeTenantId = sanitizeAeId(tenantId, 'tenantId')
  const safeKeyId = sanitizeAeId(keyId, 'keyId')
  const { start, end } = currentUtcMonthBounds(month)

  const sql = `
    SELECT
      toDate(timestamp) AS date,
      SUM(_sample_interval * double1) AS count
    FROM api_usage
    WHERE index1 = '${safeTenantId}'
      AND blob1  = '${safeKeyId}'
      AND timestamp >= toDateTime('${start}')
      AND timestamp <  toDateTime('${end}')
    GROUP BY date
    ORDER BY date ASC
  `

  const rows = await runAeQuery<{ date: string; count: number }>(env, sql)

  // Build a zero-filled map for every day in the month range
  const byDate = new Map<string, number>()
  for (const row of rows) {
    byDate.set(row.date, row.count)
  }

  // Generate every date in the month
  const result: ApiKeyUsageDailyPoint[] = []
  const startDate = new Date(start)
  const endDate = new Date(end)
  const cursor = new Date(startDate)
  while (cursor < endDate) {
    const iso = cursor.toISOString().slice(0, 10)
    result.push({ date: iso, count: byDate.get(iso) ?? 0 })
    cursor.setUTCDate(cursor.getUTCDate() + 1)
  }

  return result
}

/**
 * Top 5 endpoints by request count for the month.
 */
export async function getTopEndpoints(
  env: ApiUsageEnv,
  tenantId: string,
  keyId: string,
  month?: string,
): Promise<ApiKeyUsageEndpointPoint[]> {
  const safeTenantId = sanitizeAeId(tenantId, 'tenantId')
  const safeKeyId = sanitizeAeId(keyId, 'keyId')
  const { start, end } = currentUtcMonthBounds(month)

  const sql = `
    SELECT
      blob2 AS path,
      blob3 AS method,
      SUM(_sample_interval * double1) AS count
    FROM api_usage
    WHERE index1 = '${safeTenantId}'
      AND blob1  = '${safeKeyId}'
      AND timestamp >= toDateTime('${start}')
      AND timestamp <  toDateTime('${end}')
    GROUP BY path, method
    ORDER BY count DESC
    LIMIT 5
  `

  const rows = await runAeQuery<{ path: string; method: string; count: number }>(env, sql)
  return rows
}