/**
 * Analytics Engine funnel reader — admin-reports-analytics.
 *
 * Reads funnel event counts from the Cloudflare Analytics Engine SQL HTTP API.
 * The AE binding is write-only (CF Workers limitation); reads go via REST.
 *
 * On fetch failure or missing token, throws FunnelUnavailableError so the
 * caller can return 503 without crashing other analytics tabs.
 */
import { dateRangeSchema, type DateRange, type FunnelReport, type FunnelStep } from './types'

export interface FunnelEnv {
  CF_ACCOUNT_ID: string
  CF_ANALYTICS_READ_TOKEN: string
  CF_ANALYTICS_DATASET?: string   // default 'zync_events'
}

export class FunnelUnavailableError extends Error {
  constructor(message: string, public readonly cause?: unknown) {
    super(message)
    this.name = 'FunnelUnavailableError'
  }
}

/** Ordered funnel event names — defines step sequence. */
const FUNNEL_EVENTS = [
  'catalog_view',
  'lead_captured',
  'proposal_accepted',
  'invoice_paid',
] as const

interface AeRow {
  event: string
  count: number
}

interface AeResponse {
  data?: AeRow[]
  rows?: AeRow[]
  meta?: unknown
  error?: { code: number; message: string }
}

/**
 * Fetch funnel step counts from CF Analytics Engine SQL API.
 *
 * range.from / range.to are validated against YYYY-MM-DD before interpolation.
 */
export async function getFunnelReport(
  env: FunnelEnv,
  range: DateRange,
): Promise<FunnelReport> {
  if (!env.CF_ANALYTICS_READ_TOKEN) {
    throw new FunnelUnavailableError('CF_ANALYTICS_READ_TOKEN is not configured')
  }
  if (!env.CF_ACCOUNT_ID) {
    throw new FunnelUnavailableError('CF_ACCOUNT_ID is not configured')
  }

  // Re-validate date strings to ensure no SQL injection into the AE query
  const parsed = dateRangeSchema.safeParse(range)
  if (!parsed.success) {
    throw new FunnelUnavailableError('Invalid date range for funnel query')
  }
  const { from, to } = parsed.data

  const dataset = env.CF_ANALYTICS_DATASET ?? 'zync_events'
  const events = FUNNEL_EVENTS.map((e) => `'${e}'`).join(',')

  const sql = [
    `SELECT blob1 AS event, count() AS count`,
    `FROM ${dataset}`,
    `WHERE timestamp >= toDateTime('${from} 00:00:00')`,
    `  AND timestamp < toDateTime('${to} 00:00:00') + INTERVAL '1' DAY`,
    `  AND blob1 IN (${events})`,
    `GROUP BY blob1`,
  ].join(' ')

  const url = `https://api.cloudflare.com/client/v4/accounts/${env.CF_ACCOUNT_ID}/analytics_engine/sql`

  let res: Response
  try {
    res = await fetch(url, {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${env.CF_ANALYTICS_READ_TOKEN}`,
        'Content-Type': 'text/plain',
      },
      body: sql,
    })
  } catch (err) {
    throw new FunnelUnavailableError('CF Analytics Engine request failed', err)
  }

  if (!res.ok) {
    let errMsg = `AE HTTP ${res.status}`
    try {
      const body = (await res.json()) as AeResponse
      if (body?.error?.message) errMsg = body.error.message
    } catch {
      // ignore parse error
    }
    throw new FunnelUnavailableError(errMsg)
  }

  let body: AeResponse
  try {
    body = (await res.json()) as AeResponse
  } catch (err) {
    throw new FunnelUnavailableError('Failed to parse AE response', err)
  }

  const rawRows: AeRow[] = body.data ?? body.rows ?? []
  const countByEvent: Record<string, number> = {}
  for (const row of rawRows) {
    countByEvent[row.event] = Number(row.count)
  }

  // Build ordered steps, filling missing events with 0, computing pctOfPrev
  const steps: FunnelStep[] = []
  let prevCount: number | null = null

  for (const event of FUNNEL_EVENTS) {
    const count = countByEvent[event] ?? 0
    const pctOfPrev = prevCount === null
      ? 100
      : prevCount > 0
        ? Math.round((count / prevCount) * 1000) / 10
        : 0
    steps.push({ event, count, pctOfPrev })
    prevCount = count
  }

  return { steps }
}