import { supabaseAdmin } from './db.js'
import { unscopedClickhouse } from './clickhouse.js'
import { detectAnomalies, ANOMALY_DEFAULTS, type AnomalyBucket } from './anomaly.js'
import { deliverToChannel, type AlertNotification, type NotificationChannelRow } from './notifiers.js'

/**
 * Daily snapshot job. Runs anomaly detection for every active organization
 * and persists any flagged buckets into `anomaly_events` so the dashboard
 * can show "anomalies on Tuesday at lunchtime" patterns over time.
 *
 * Idempotent per (org, day, provider, model, kind) via the table's UNIQUE
 * constraint — re-runs on the same day update the row instead of duplicating.
 *
 * Skip orgs with no traffic to keep the job fast at scale.
 */

export interface SnapshotResult {
  orgId: string
  detected: number
  errors: string[]
}

const CHUNK_SIZE = 10

export async function snapshotAnomaliesForAllOrgs(
  now: Date = new Date(),
): Promise<SnapshotResult[]> {
  const today = now.toISOString().slice(0, 10) // YYYY-MM-DD

  // Pick orgs with at least one request in the past 24h — anomaly detection
  // needs traffic anyway, no point invoking it for inactive orgs.
  // ClickHouse can DISTINCT cheaply, so we let the database deduplicate
  // instead of pulling rows back to JS like the old Supabase pattern.
  const sinceTs = new Date(now.getTime() - 86_400_000)
    .toISOString()
    .replace('T', ' ')
    .replace('Z', '')
  let uniqueOrgIds: string[]
  try {
    const result = await unscopedClickhouse().query({
      query:
        'SELECT DISTINCT organization_id ' +
        'FROM requests ' +
        'WHERE created_at >= parseDateTime64BestEffort({since:String})',
      query_params: { since: sinceTs },
      format: 'JSONEachRow',
    })
    const rows = (await result.json()) as Array<{ organization_id: string }>
    uniqueOrgIds = rows.map((r) => r.organization_id)
  } catch (err) {
    throw new Error(
      `Failed to fetch active orgs: ${err instanceof Error ? err.message : String(err)}`,
    )
  }

  const results: SnapshotResult[] = []

  // Process in parallel chunks to avoid opening 1 DB connection per org while
  // still keeping throughput high. CHUNK_SIZE = 10 is conservative enough to
  // avoid Supabase connection pool exhaustion.
  for (let i = 0; i < uniqueOrgIds.length; i += CHUNK_SIZE) {
    const chunk = uniqueOrgIds.slice(i, i + CHUNK_SIZE)
    const chunkResults = await Promise.all(
      chunk.map(async (orgId) => {
        const result: SnapshotResult = { orgId, detected: 0, errors: [] }
        try {
          const anomalies = await detectAnomalies(orgId, {
            observationHours: ANOMALY_DEFAULTS.OBSERVATION_HOURS,
            referenceHours: ANOMALY_DEFAULTS.REFERENCE_HOURS,
            sigmaThreshold: ANOMALY_DEFAULTS.SIGMA_THRESHOLD,
          })
          if (anomalies.length === 0) return result

          await persistSnapshot(orgId, today, anomalies)
          result.detected = anomalies.length

          // Send notifications for high-severity (≥5σ) anomalies via configured channels.
          const highSeverity = anomalies.filter((a) => a.deviations >= ANOMALY_DEFAULTS.HIGH_SEVERITY_SIGMA)
          if (highSeverity.length > 0) {
            await notifyHighSeverityAnomalies(orgId, highSeverity).catch((err) => {
              result.errors.push(`notify: ${err instanceof Error ? err.message : 'unknown'}`)
            })
          }
        } catch (err) {
          result.errors.push(err instanceof Error ? err.message : 'unknown')
        }
        return result
      }),
    )
    results.push(...chunkResults)
  }

  return results
}

async function persistSnapshot(
  orgId: string,
  detectedOn: string,
  anomalies: AnomalyBucket[],
): Promise<void> {
  const rows = anomalies.map((a) => ({
    organization_id: orgId,
    detected_on: detectedOn,
    provider: a.provider,
    model: a.model,
    kind: a.kind,
    current_value: a.currentValue,
    baseline_mean: a.baselineMean,
    baseline_stddev: a.baselineStdDev,
    deviations: a.deviations,
    sample_count: a.sampleCount,
    reference_count: a.referenceCount,
    confidence: a.confidence,
  }))

  // Upsert on the unique (org, day, provider, model, kind) tuple — re-runs
  // overwrite the same row rather than blocking on a unique violation.
  const { error } = await supabaseAdmin
    .from('anomaly_events')
    .upsert(rows, { onConflict: 'organization_id,detected_on,provider,model,kind' })

  if (error) {
    throw new Error(`persistSnapshot failed: ${error.message}`)
  }
}

interface AnomalyEventRow {
  id: string
  detected_on: string
  provider: string
  model: string
  kind: 'latency' | 'cost' | 'error_rate'
  current_value: string | number
  baseline_mean: string | number
  baseline_stddev: string | number
  deviations: string | number
  sample_count: number
  reference_count: number
  /** P3.2: nullable for rows persisted before the column was added. */
  confidence: 'low' | 'medium' | 'high' | null
}

export interface AnomalyHistoryEntry {
  id: string
  detectedOn: string
  provider: string
  model: string
  kind: 'latency' | 'cost' | 'error_rate'
  currentValue: number
  baselineMean: number
  baselineStdDev: number
  deviations: number
  sampleCount: number
  referenceCount: number
  /** Null for pre-P3.2 historical rows; otherwise 'low' | 'medium' | 'high'. */
  confidence: 'low' | 'medium' | 'high' | null
}

async function notifyHighSeverityAnomalies(
  orgId: string,
  anomalies: AnomalyBucket[],
): Promise<void> {
  const [channelsRes, orgRes] = await Promise.all([
    supabaseAdmin
      .from('notification_channels')
      .select('kind, target')
      .eq('organization_id', orgId)
      .eq('is_active', true)
      .returns<NotificationChannelRow[]>(),
    supabaseAdmin
      .from('organizations')
      .select('name')
      .eq('id', orgId)
      .single<{ name: string }>(),
  ])

  const channels = channelsRes.data ?? []
  if (channels.length === 0) return

  const orgName = orgRes.data?.name ?? orgId
  const webUrl = process.env['WEB_URL'] ?? 'https://www.spanlens.io'

  // Fan out all (anomaly × channel) pairs in parallel.
  // Collect !ok results — deliverToChannel never throws, it returns {ok:false,error}.
  const deliveryErrors: string[] = []

  await Promise.all(
    anomalies.flatMap((anomaly) => {
      // 'cost' maps to 'budget' — AlertNotification.alertType uses Alerts enum vocabulary.
      const kindLabel =
        anomaly.kind === 'latency' ? 'latency_p95'
        : anomaly.kind === 'error_rate' ? 'error_rate'
        : 'budget'

      const notification: AlertNotification = {
        alertName: `${anomaly.provider}/${anomaly.model} · ${anomaly.kind} (${anomaly.deviations.toFixed(1)}σ)`,
        alertType: kindLabel as AlertNotification['alertType'],
        threshold: anomaly.baselineMean,
        currentValue: anomaly.currentValue,
        windowMinutes: 60,
        organizationName: orgName,
        dashboardUrl: `${webUrl}/anomalies`,
      }

      return channels.map(async (channel) => {
        const result = await deliverToChannel(channel.kind, channel.target, notification)
        if (!result.ok) {
          const errMsg = `${channel.kind}: ${result.error ?? 'unknown'}`
          deliveryErrors.push(errMsg)
          console.error('[anomaly-notify]', orgId, errMsg)
        }
      })
    }),
  )

  if (deliveryErrors.length > 0) {
    throw new Error(deliveryErrors.join('; '))
  }
}

export async function getAnomalyHistory(
  organizationId: string,
  days = 30,
): Promise<AnomalyHistoryEntry[]> {
  const now = Date.now()
  const since = new Date(now - days * 86_400_000).toISOString().slice(0, 10)
  // Exclude today: today's state is shown in real-time detection, not history.
  const today = new Date(now).toISOString().slice(0, 10)
  const { data, error } = await supabaseAdmin
    .from('anomaly_events')
    .select(
      'id, detected_on, provider, model, kind, current_value, baseline_mean, baseline_stddev, deviations, sample_count, reference_count, confidence',
    )
    .eq('organization_id', organizationId)
    .gte('detected_on', since)
    .lt('detected_on', today)
    .order('detected_on', { ascending: false })
    .limit(5000)
    .returns<AnomalyEventRow[]>()

  if (error) {
    console.error('[getAnomalyHistory] query failed:', error.message)
    return []
  }
  if (!data) return []

  return data.map((r) => ({
    id: r.id,
    detectedOn: r.detected_on,
    provider: r.provider,
    model: r.model,
    kind: r.kind,
    currentValue: Number(r.current_value) || 0,
    baselineMean: Number(r.baseline_mean) || 0,
    baselineStdDev: Number(r.baseline_stddev) || 0,
    deviations: Number(r.deviations) || 0,
    sampleCount: r.sample_count,
    referenceCount: r.reference_count,
    confidence: r.confidence,
  }))
}
