#!/usr/bin/env bun
/**
 * Load-test the /updates manifest query hypothesis in a local Postgres.
 *
 * Compares production-shaped SQL paths:
 *   OLD: channel lookup LEFT JOIN manifest + json_agg + GROUP BY
 *        (paid on every check when the app has manifests, including up-to-date)
 *   NEW: light channel lookup, then indexed
 *        SELECT … FROM manifest WHERE app_version_id = $1
 *        (only on the new-version path; overlaps gates in the app code)
 *
 * Usage:
 *   bun scripts/bench_updates_manifest_path.ts
 *   bun scripts/bench_updates_manifest_path.ts --files 5000 --concurrency 50 --requests 1000
 *   MANIFEST_BENCH_DATABASE_URL=postgres://… bun scripts/bench_updates_manifest_path.ts
 *
 * Default URL points at the local docker bench container on :55432 / db manifest_bench.
 * Intentionally ignores DATABASE_URL so a destructive DROP SCHEMA cannot hit the app DB.
 *
 * Exit code 1 if the new-version path is slower than OLD beyond noise (see PASS_GATE).
 */

import { mkdirSync, writeFileSync } from 'node:fs'
import { resolve } from 'node:path'
import { performance } from 'node:perf_hooks'
import { Client, Pool } from 'pg'

interface LatencyStats {
  count: number
  minMs: number
  p50Ms: number
  p95Ms: number
  p99Ms: number
  maxMs: number
  meanMs: number
  qps: number
  rowBytesApprox: number
}

interface ScenarioResult {
  name: string
  path: 'old_json_agg' | 'new_deferred'
  scenario: 'up_to_date' | 'new_version'
  files: number
  concurrency: number
  stats: LatencyStats
  explain?: string
}

interface BenchReport {
  generatedAt: string
  gitHead: string
  databaseUrlHost: string
  files: number
  concurrency: number
  requests: number
  warmup: number
  results: ScenarioResult[]
  gates: {
    newVersionP95RegressionPct: number
    newVersionP95DeltaMs: number
    upToDateP95ImprovementPct: number
    passed: boolean
    reasons: string[]
  }
}

const ROOT = resolve(import.meta.dirname, '..')
const DEFAULT_URL = 'postgres://postgres:postgres@127.0.0.1:55432/manifest_bench'

function argValue(flag: string, fallback: string): string {
  const idx = process.argv.indexOf(flag)
  if (idx >= 0 && process.argv[idx + 1])
    return process.argv[idx + 1]!
  return fallback
}

function numArg(flag: string, fallback: string, opts?: { min?: number }): number {
  const raw = argValue(flag, fallback)
  const n = Number(raw)
  const min = opts?.min ?? 0
  if (!Number.isFinite(n) || !Number.isInteger(n) || n < min)
    throw new Error(`Invalid value for ${flag}: ${raw} (need integer >= ${min})`)
  return n
}

function percentile(sorted: number[], p: number): number {
  if (sorted.length === 0)
    return 0
  const idx = Math.min(sorted.length - 1, Math.max(0, Math.ceil((p / 100) * sorted.length) - 1))
  return sorted[idx]!
}

function statsOf(samplesMs: number[], wallSec: number, rowBytesApprox: number): LatencyStats {
  const sorted = [...samplesMs].sort((a, b) => a - b)
  const sum = samplesMs.reduce((a, b) => a + b, 0)
  return {
    count: samplesMs.length,
    minMs: sorted[0] ?? 0,
    p50Ms: percentile(sorted, 50),
    p95Ms: percentile(sorted, 95),
    p99Ms: percentile(sorted, 99),
    maxMs: sorted[sorted.length - 1] ?? 0,
    meanMs: samplesMs.length ? sum / samplesMs.length : 0,
    qps: wallSec > 0 ? samplesMs.length / wallSec : 0,
    rowBytesApprox,
  }
}

async function setupSchema(client: Client) {
  await client.query(`
    DROP SCHEMA IF EXISTS public CASCADE;
    CREATE SCHEMA public;

    CREATE TABLE app_versions (
      id bigint PRIMARY KEY,
      app_id text NOT NULL,
      name text NOT NULL,
      deleted boolean NOT NULL DEFAULT false,
      r2_path text,
      external_url text,
      checksum text,
      key_id text,
      session_key text,
      manifest_count integer NOT NULL DEFAULT 0
    );

    CREATE TABLE channels (
      id bigint PRIMARY KEY,
      app_id text NOT NULL,
      name text NOT NULL,
      version bigint REFERENCES app_versions(id),
      public boolean NOT NULL DEFAULT true,
      allow_device_self_set boolean NOT NULL DEFAULT false,
      ios boolean NOT NULL DEFAULT true,
      android boolean NOT NULL DEFAULT true,
      electron boolean NOT NULL DEFAULT true
    );

    CREATE TABLE manifest (
      app_version_id bigint NOT NULL REFERENCES app_versions(id) ON DELETE CASCADE,
      file_name text NOT NULL,
      file_hash text NOT NULL,
      s3_path text NOT NULL
    );

    -- Production index used by the deferred path
    CREATE INDEX idx_manifest_app_version_id ON manifest USING btree (app_version_id);
    CREATE INDEX idx_channels_app_name ON channels (app_id, name);
  `)
}

async function seed(client: Client, files: number, noiseVersions = 40) {
  // Live channel points at version 1002. Extra versions fill the table so
  // idx_manifest_app_version_id is selective (prod: millions of rows, many versions).
  const versionRows: string[] = []
  for (let v = 1001; v <= 1001 + noiseVersions; v++) {
    versionRows.push(`(${v}, 'com.bench.manifest', '${(v - 1000)}.0.0', false, NULL, 'checksum-${v}', ${files})`)
  }
  // SQL built from local loop integers/literals only — not user input.
  await client.query(`
    INSERT INTO app_versions (id, app_id, name, deleted, r2_path, checksum, manifest_count)
    VALUES ${versionRows.join(',')}
  `)

  await client.query(`
    INSERT INTO channels (id, app_id, name, version, public, ios, android)
    VALUES (1, 'com.bench.manifest', 'production', 1002, true, true, true)
  `)

  const batchSize = 500
  const versionsToFill = Array.from({ length: noiseVersions + 1 }, (_, i) => 1001 + i)
  for (const versionId of versionsToFill) {
    for (let offset = 0; offset < files; offset += batchSize) {
      const values: unknown[] = []
      const placeholders: string[] = []
      const end = Math.min(files, offset + batchSize)
      let p = 1
      for (let i = offset; i < end; i++) {
        placeholders.push(`($${p++}, $${p++}, $${p++}, $${p++})`)
        values.push(
          versionId,
          `assets/file-${i}.js`,
          `hash-${versionId}-${i}`,
          `apps/com.bench.manifest/${versionId}/file-${i}.js`,
        )
      }
      await client.query(
        `INSERT INTO manifest (app_version_id, file_name, file_hash, s3_path) VALUES ${placeholders.join(',')}`,
        values,
      )
    }
  }

  await client.query('ANALYZE app_versions')
  await client.query('ANALYZE channels')
  await client.query('ANALYZE manifest')
}

/** OLD hot path: channel + version + json_agg(manifest) in one statement */
const OLD_CHANNEL_WITH_MANIFEST = `
SELECT
  c.id AS channel_id,
  c.name AS channel_name,
  v.id AS version_id,
  v.name AS version_name,
  COALESCE(json_agg(
    json_build_object(
      'file_name', m.file_name,
      'file_hash', m.file_hash,
      's3_path', m.s3_path
    )
  ) FILTER (WHERE m.file_name IS NOT NULL), '[]'::json) AS manifest_entries
FROM channels c
LEFT JOIN app_versions v
  ON c.version = v.id
 AND (v.deleted = false OR v.name = 'builtin')
LEFT JOIN manifest m
  ON m.app_version_id = v.id
WHERE c.app_id = $1
  AND c.name = $2
  AND c.android = true
  AND (c.public = true OR c.allow_device_self_set = true)
GROUP BY c.id, v.id
ORDER BY c.name, c.id
LIMIT 1
`

/** NEW light channel lookup (no manifest join) */
const NEW_CHANNEL_LIGHT = `
SELECT
  c.id AS channel_id,
  c.name AS channel_name,
  v.id AS version_id,
  v.name AS version_name
FROM channels c
LEFT JOIN app_versions v
  ON c.version = v.id
 AND (v.deleted = false OR v.name = 'builtin')
WHERE c.app_id = $1
  AND c.name = $2
  AND c.android = true
  AND (c.public = true OR c.allow_device_self_set = true)
ORDER BY c.name, c.id
LIMIT 1
`

/** NEW deferred indexed manifest fetch */
const NEW_MANIFEST_BY_VERSION = `
SELECT file_name, file_hash, s3_path
FROM manifest
WHERE app_version_id = $1
`

async function runOldPathMeasured(pool: Pool, appId: string, channel: string): Promise<{ ms: number, bytes: number, files: number }> {
  const start = performance.now()
  const res = await pool.query(OLD_CHANNEL_WITH_MANIFEST, [appId, channel])
  const entries = res.rows[0]?.manifest_entries ?? []
  return {
    ms: performance.now() - start,
    bytes: JSON.stringify(entries).length,
    files: Array.isArray(entries) ? entries.length : 0,
  }
}

async function runNewUpToDate(pool: Pool, appId: string, channel: string): Promise<{ ms: number, bytes: number, files: number }> {
  const start = performance.now()
  const res = await pool.query(NEW_CHANNEL_LIGHT, [appId, channel])
  return {
    ms: performance.now() - start,
    bytes: JSON.stringify(res.rows[0] ?? {}).length,
    files: 0,
  }
}

async function runNewVersion(pool: Pool, appId: string, channel: string): Promise<{ ms: number, bytes: number, files: number }> {
  const start = performance.now()
  // App code overlaps these with gates; here we measure the DB critical path as
  // light channel + indexed select (sequential worst case for DB time).
  const channelRes = await pool.query(NEW_CHANNEL_LIGHT, [appId, channel])
  const versionId = channelRes.rows[0]?.version_id
  const manifestRes = await pool.query(NEW_MANIFEST_BY_VERSION, [versionId])
  return {
    ms: performance.now() - start,
    bytes: JSON.stringify(manifestRes.rows).length,
    files: manifestRes.rows.length,
  }
}

async function loadScenario(
  pool: Pool,
  name: string,
  path: ScenarioResult['path'],
  scenario: ScenarioResult['scenario'],
  files: number,
  concurrency: number,
  requests: number,
  warmup: number,
  fn: () => Promise<{ ms: number, bytes: number, files: number }>,
): Promise<ScenarioResult> {
  for (let i = 0; i < warmup; i++)
    await fn()

  const samples: number[] = []
  let bytes = 0
  let fileCount = 0
  let next = 0
  const wallStart = performance.now()

  async function worker() {
    while (true) {
      const i = next++
      if (i >= requests)
        return
      const r = await fn()
      samples.push(r.ms)
      bytes = r.bytes
      fileCount = r.files
    }
  }

  await Promise.all(Array.from({ length: concurrency }, () => worker()))
  const wallSec = (performance.now() - wallStart) / 1000
  const s = statsOf(samples, wallSec, bytes)

  console.log(
    `[${name}] n=${s.count} conc=${concurrency} files=${fileCount}/${files} `
    + `p50=${s.p50Ms.toFixed(2)}ms p95=${s.p95Ms.toFixed(2)}ms p99=${s.p99Ms.toFixed(2)}ms `
    + `max=${s.maxMs.toFixed(2)}ms qps=${s.qps.toFixed(1)} payload~${Math.round(bytes / 1024)}KB`,
  )

  return { name, path, scenario, files, concurrency, stats: s }
}

type BenchExplainSql = typeof OLD_CHANNEL_WITH_MANIFEST | typeof NEW_CHANNEL_LIGHT | typeof NEW_MANIFEST_BY_VERSION

// sql is always one of the module-level constants above — not user input.
async function explain(client: Client, sql: BenchExplainSql, params: unknown[]): Promise<string> {
  const res = await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) ${sql}`, params)
  return res.rows.map((r: { 'QUERY PLAN': string }) => r['QUERY PLAN']).join('\n')
}

function assertSafeBenchDatabaseUrl(databaseUrl: string) {
  let parsed: URL
  try {
    parsed = new URL(databaseUrl)
  }
  catch {
    throw new Error(`Invalid MANIFEST_BENCH_DATABASE_URL: ${databaseUrl}`)
  }
  const dbName = decodeURIComponent(parsed.pathname.replace(/^\/+/, '').split('/')[0] ?? '')
  const host = parsed.hostname
  const port = parsed.port || (parsed.protocol === 'postgres:' || parsed.protocol === 'postgresql:' ? '5432' : '')
  const isLocalBenchHost = (host === '127.0.0.1' || host === 'localhost') && port === '55432'
  if (dbName !== 'manifest_bench' || !isLocalBenchHost) {
    throw new Error(
      `Refusing destructive bench against ${host}:${port}/${dbName}. `
      + `Only postgres://…@127.0.0.1:55432/manifest_bench (or localhost) is allowed. `
      + `Do not pass DATABASE_URL; use MANIFEST_BENCH_DATABASE_URL or --database-url.`,
    )
  }
}

async function main() {
  // Never fall back to DATABASE_URL — setupSchema() DROP SCHEMA public CASCADE.
  const databaseUrl = process.env.MANIFEST_BENCH_DATABASE_URL || argValue('--database-url', DEFAULT_URL)
  assertSafeBenchDatabaseUrl(databaseUrl)
  const files = numArg('--files', '5000', { min: 1 })
  const concurrency = numArg('--concurrency', '50', { min: 1 })
  const requests = numArg('--requests', '800', { min: 1 })
  const warmup = numArg('--warmup', '20', { min: 0 })
  const noiseVersions = numArg('--noise-versions', '40', { min: 0 })
  const savePath = argValue('--save', 'scripts/bench/updates_manifest_path_results.json')

  console.log(`DB=${databaseUrl.replace(/:[^:@/]+@/, ':***@')}`)
  console.log(`files=${files} noiseVersions=${noiseVersions} concurrency=${concurrency} requests=${requests} warmup=${warmup}`)

  const admin = new Client({ connectionString: databaseUrl })
  await admin.connect()
  console.log('Setting up schema + seed…')
  await setupSchema(admin)
  await seed(admin, files, noiseVersions)
  const countRes = await admin.query('SELECT count(*)::int AS n FROM manifest')
  console.log(`Seeded manifest rows: ${countRes.rows[0].n} (live version=${files}, table≈${files * (noiseVersions + 1)})`)

  const oldExplain = await explain(admin, OLD_CHANNEL_WITH_MANIFEST, ['com.bench.manifest', 'production'])
  const lightExplain = await explain(admin, NEW_CHANNEL_LIGHT, ['com.bench.manifest', 'production'])
  const manifestExplain = await explain(admin, NEW_MANIFEST_BY_VERSION, [1002])
  console.log('\n--- EXPLAIN OLD channel+json_agg ---\n', oldExplain)
  console.log('\n--- EXPLAIN NEW channel light ---\n', lightExplain)
  console.log('\n--- EXPLAIN NEW manifest by version ---\n', manifestExplain)

  const pool = new Pool({
    connectionString: databaseUrl,
    max: Math.max(concurrency, 10),
    idleTimeoutMillis: 10000,
  })

  const appId = 'com.bench.manifest'
  const channel = 'production'

  // Sanity: both paths return same file count for new_version
  const oldOnce = await runOldPathMeasured(pool, appId, channel)
  const newOnce = await runNewVersion(pool, appId, channel)
  if (oldOnce.files !== files || newOnce.files !== files) {
    throw new Error(`Sanity failed: old files=${oldOnce.files} new files=${newOnce.files} expected=${files}`)
  }
  console.log(`Sanity OK: both paths return ${files} files`)

  const results: ScenarioResult[] = []

  results.push(await loadScenario(
    pool,
    'OLD up_to_date (still pays json_agg)',
    'old_json_agg',
    'up_to_date',
    files,
    concurrency,
    requests,
    warmup,
    () => runOldPathMeasured(pool, appId, channel),
  ))

  results.push(await loadScenario(
    pool,
    'NEW up_to_date (channel only)',
    'new_deferred',
    'up_to_date',
    files,
    concurrency,
    requests,
    warmup,
    () => runNewUpToDate(pool, appId, channel),
  ))

  results.push(await loadScenario(
    pool,
    'OLD new_version (json_agg)',
    'old_json_agg',
    'new_version',
    files,
    concurrency,
    requests,
    warmup,
    () => runOldPathMeasured(pool, appId, channel),
  ))

  results.push(await loadScenario(
    pool,
    'NEW new_version (light + indexed)',
    'new_deferred',
    'new_version',
    files,
    concurrency,
    requests,
    warmup,
    () => runNewVersion(pool, appId, channel),
  ))

  // Attach explains to first matching results
  results[0]!.explain = oldExplain
  results[1]!.explain = lightExplain
  results[3]!.explain = `${lightExplain}\n---\n${manifestExplain}`

  const oldNew = results.find(r => r.path === 'old_json_agg' && r.scenario === 'new_version')!
  const newNew = results.find(r => r.path === 'new_deferred' && r.scenario === 'new_version')!
  const oldUtd = results.find(r => r.path === 'old_json_agg' && r.scenario === 'up_to_date')!
  const newUtd = results.find(r => r.path === 'new_deferred' && r.scenario === 'up_to_date')!

  const newVersionP95RegressionPct = oldNew.stats.p95Ms > 0
    ? ((newNew.stats.p95Ms - oldNew.stats.p95Ms) / oldNew.stats.p95Ms) * 100
    : 0
  const newVersionP95DeltaMs = newNew.stats.p95Ms - oldNew.stats.p95Ms
  const upToDateP95ImprovementPct = oldUtd.stats.p95Ms > 0
    ? ((oldUtd.stats.p95Ms - newUtd.stats.p95Ms) / oldUtd.stats.p95Ms) * 100
    : 0

  // PASS GATE (strict — user closed prior PR over new-version latency risk):
  // NEW new_version must not be slower than OLD beyond measurement noise.
  // Noise = max(10ms, 5% of OLD p95). Also require up-to-date p95 improvement >= 50%.
  const reasons: string[] = []
  const newVersionNoiseMs = Math.max(10, oldNew.stats.p95Ms * 0.05)
  if (newVersionP95DeltaMs > newVersionNoiseMs) {
    reasons.push(
      `NEW new_version p95 slower than OLD by ${newVersionP95DeltaMs.toFixed(2)}ms `
      + `(limit +${newVersionNoiseMs.toFixed(2)}ms / ${newVersionP95RegressionPct.toFixed(1)}%; `
      + `${oldNew.stats.p95Ms.toFixed(2)} → ${newNew.stats.p95Ms.toFixed(2)} ms)`,
    )
  }
  if (upToDateP95ImprovementPct < 50) {
    reasons.push(
      `NEW up_to_date p95 improvement only ${upToDateP95ImprovementPct.toFixed(1)}% `
      + `(expected >= 50%; ${oldUtd.stats.p95Ms.toFixed(2)} → ${newUtd.stats.p95Ms.toFixed(2)} ms)`,
    )
  }
  // Tail: p99 must not regress beyond max(15% , +25ms)
  const p99Delta = newNew.stats.p99Ms - oldNew.stats.p99Ms
  const p99Limit = Math.max(oldNew.stats.p99Ms * 0.15, 25)
  if (p99Delta > p99Limit) {
    reasons.push(
      `NEW new_version p99 slower than OLD by ${p99Delta.toFixed(2)}ms `
      + `(limit +${p99Limit.toFixed(2)}ms; ${oldNew.stats.p99Ms.toFixed(2)} → ${newNew.stats.p99Ms.toFixed(2)} ms)`,
    )
  }

  const passed = reasons.length === 0

  const gitHead = Bun.spawnSync(['git', 'rev-parse', '--short', 'HEAD'], { cwd: ROOT }).stdout.toString().trim()
  const report: BenchReport = {
    generatedAt: new Date().toISOString(),
    gitHead,
    databaseUrlHost: new URL(databaseUrl).host,
    files,
    concurrency,
    requests,
    warmup,
    results,
    gates: {
      newVersionP95RegressionPct,
      newVersionP95DeltaMs,
      upToDateP95ImprovementPct,
      passed,
      reasons,
    },
  }

  const out = resolve(ROOT, savePath)
  mkdirSync(resolve(out, '..'), { recursive: true })
  writeFileSync(out, `${JSON.stringify(report, null, 2)}\n`)

  console.log('\n=== GATE SUMMARY ===')
  console.log(`new_version p95: OLD ${oldNew.stats.p95Ms.toFixed(2)}ms → NEW ${newNew.stats.p95Ms.toFixed(2)}ms `
    + `(${newVersionP95RegressionPct >= 0 ? '+' : ''}${newVersionP95RegressionPct.toFixed(1)}% / ${newVersionP95DeltaMs >= 0 ? '+' : ''}${newVersionP95DeltaMs.toFixed(2)}ms)`)
  console.log(`up_to_date p95:  OLD ${oldUtd.stats.p95Ms.toFixed(2)}ms → NEW ${newUtd.stats.p95Ms.toFixed(2)}ms `
    + `(improvement ${upToDateP95ImprovementPct.toFixed(1)}%)`)
  console.log(passed ? 'PASS: new-version not slower than OLD (within noise); up-to-date improved' : `FAIL:\n- ${reasons.join('\n- ')}`)
  console.log(`Wrote ${out}`)

  await pool.end()
  await admin.end()
  process.exit(passed ? 0 : 1)
}

main().catch((err) => {
  console.error(err)
  process.exit(2)
})