import { sql } from "@/lib/db/client"

/**
 * What polling would have cost, priced against the real table.
 *
 *     bun run tmp/sse/cost.ts
 *
 * Throwaway. It fills the notifications table with three months of traffic for
 * the whole roster, times the query a poll would run, and deletes every row it
 * wrote. The table starts and ends empty.
 */

const PER_PERSON = 50

function line(label: string, value: unknown) {
  console.log(`  ${label}: ${JSON.stringify(value)}`)
}

const before = {
  events: Number((await sql`select count(*)::int n from domain_events`)[0]!.n),
  notifications: Number(
    (await sql`select count(*)::int n from notifications`)[0]!.n,
  ),
}
console.log("BEFORE")
line("domain_events", before.events)
line("notifications", before.notifications)
if (before.events !== 0 || before.notifications !== 0) {
  console.error("Not starting from empty. Stopping rather than guessing.")
  await sql.end()
  process.exit(1)
}

try {
  const roster = await sql<{ id: string }[]>`select id from people`
  console.log(`\nFilling: ${roster.length} people x ${PER_PERSON} rows`)

  // One event per round, fanned out to everybody. The unique index is on
  // (event_id, person_id), so this is the cheapest shape that stays legal.
  const eventIds: string[] = []
  for (let round = 0; round < PER_PERSON; round++) {
    const [row] = await sql<{ id: string }[]>`
      insert into domain_events (type, occurred_at)
      values ('task.reminded', now() - ${`${PER_PERSON - round} days`}::interval)
      returning id`
    eventIds.push(row!.id)
  }

  const values = eventIds.flatMap((eventId, round) =>
    roster.map((person) => ({
      person_id: person.id,
      event_id: eventId,
      message_key: "notify.reminded",
      created_at: sql`now() - ${`${PER_PERSON - round} days`}::interval`,
    })),
  )
  for (let i = 0; i < values.length; i += 2000) {
    await sql`insert into notifications ${sql(values.slice(i, i + 2000))}`
  }

  const total = Number(
    (await sql`select count(*)::int n from notifications`)[0]!.n,
  )
  line("notifications now", total)
  await sql`analyze notifications`

  /* The person the stream is for, and the row it would resume from. */
  const [mid] = await sql<{ id: string; person_id: string }[]>`
    select id, person_id from notifications
    order by created_at desc limit 1 offset 200`
  const personId = mid!.person_id
  const cursor = mid!.id
  line("person", personId)
  line("cursor", cursor)

  const explain = async (label: string, run: () => Promise<unknown[]>) => {
    // Warm, then five, because the first of anything measures the plan cache.
    await run()
    const times: number[] = []
    for (let i = 0; i < 5; i++) {
      const started = performance.now()
      await run()
      times.push(performance.now() - started)
    }
    console.log(`\n${label}`)
    line("round trips (ms)", times.map((t) => Number(t.toFixed(3))))
  }

  const plan = async (label: string, rows: unknown[]) => {
    console.log(`\n${label}`)
    for (const row of rows) console.log("   ", Object.values(row as object)[0])
  }

  const streamQuery = () => sql`
    select id from notifications
    where person_id = ${personId}
      and (created_at, id) > (select created_at, id from notifications where id = ${cursor})
    order by created_at, id
    limit 50`

  const badgeQuery = () => sql`
    select count(*) from notifications
    where person_id = ${personId} and read_at is null`

  await plan(
    "EXPLAIN ANALYZE — what a poll would run",
    await sql`
      explain (analyze, buffers)
      select id from notifications
      where person_id = ${personId}
        and (created_at, id) > (select created_at, id from notifications where id = ${cursor})
      order by created_at, id
      limit 50`,
  )
  await explain("Timed from the application, including the round trip", () =>
    streamQuery(),
  )

  await plan(
    "EXPLAIN ANALYZE — the badge the shell already reads once per request",
    await sql`
      explain (analyze, buffers)
      select count(*) from notifications
      where person_id = ${personId} and read_at is null`,
  )
  await explain("Timed from the application", () => badgeQuery())

  /* The same query 400 times, which is one poll tick for the whole factory. */
  const people400 = (
    await sql<{ person_id: string }[]>`
      select distinct person_id from notifications limit 400`
  ).map((r) => r.person_id)
  const started = performance.now()
  for (const id of people400) {
    await sql`
      select id from notifications
      where person_id = ${id}
        and (created_at, id) > (select created_at, id from notifications where id = ${cursor})
      order by created_at, id
      limit 50`
  }
  const elapsed = performance.now() - started
  console.log("\nOne poll tick for the whole roster, serially on one connection")
  line("people", people400.length)
  line("total ms", Number(elapsed.toFixed(1)))
  line("ms each", Number((elapsed / people400.length).toFixed(3)))
} finally {
  console.log("\nPUTTING EVERY ROW BACK")
  await sql`delete from notifications`
  await sql`delete from domain_events`
  console.log("AFTER")
  line(
    "domain_events",
    Number((await sql`select count(*)::int n from domain_events`)[0]!.n),
  )
  line(
    "notifications",
    Number((await sql`select count(*)::int n from notifications`)[0]!.n),
  )
  await sql.end()
}