SixFlagsSuperCalendar/backend/tests/wait-aggregation.test.ts

/**
 * Aggregation query tests.
 *
 * Spins up an in-memory better-sqlite3 instance with the production schema,
 * seeds known samples, and verifies the daily aggregation produces the right
 * avg / max / uptime / sample_count. Locks the SQL semantics so a refactor
 * can't silently change the meaning of "uptime" or how closed samples are
 * filtered.
 *
 * Run with: npm --prefix backend test
 */

import { test } from "node:test";
import assert from "node:assert/strict";
import Database from "better-sqlite3";

const SCHEMA = `
  CREATE TABLE ride_wait_samples (
    park_id           TEXT    NOT NULL,
    qt_ride_id        INTEGER NOT NULL,
    recorded_at       TEXT    NOT NULL,
    local_date        TEXT    NOT NULL,
    local_time        TEXT    NOT NULL,
    is_open           INTEGER NOT NULL,
    wait_minutes      INTEGER,
    fast_lane_minutes INTEGER,
    PRIMARY KEY (park_id, qt_ride_id, recorded_at)
  );
`;

const AGGREGATE_QUERY = `
  SELECT local_date,
         AVG(CASE WHEN is_open = 1 THEN wait_minutes      END) AS avg_wait,
         MAX(CASE WHEN is_open = 1 THEN wait_minutes      END) AS max_wait,
         AVG(CASE WHEN is_open = 1 THEN fast_lane_minutes END) AS avg_fl,
         MAX(CASE WHEN is_open = 1 THEN fast_lane_minutes END) AS max_fl,
         CAST(SUM(is_open) AS REAL) / COUNT(*)                 AS uptime_pct,
         COUNT(*)                                              AS sample_count
  FROM ride_wait_samples
  WHERE park_id = ? AND qt_ride_id = ? AND local_date >= ?
  GROUP BY local_date
  ORDER BY local_date
`;

interface Sample {
  parkId: string;
  qtRideId: number;
  recordedAt: string;
  localDate: string;
  localTime: string;
  isOpen: boolean;
  waitMinutes: number | null;
  fastLaneMinutes: number | null;
}

function setup(samples: Sample[]) {
  const db = new Database(":memory:");
  db.exec(SCHEMA);
  const stmt = db.prepare(
    `INSERT INTO ride_wait_samples
       (park_id, qt_ride_id, recorded_at, local_date, local_time, is_open, wait_minutes, fast_lane_minutes)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
  );
  for (const s of samples) {
    stmt.run(
      s.parkId,
      s.qtRideId,
      s.recordedAt,
      s.localDate,
      s.localTime,
      s.isOpen ? 1 : 0,
      s.waitMinutes,
      s.fastLaneMinutes,
    );
  }
  return db;
}

interface AggregateRow {
  local_date: string;
  avg_wait: number | null;
  max_wait: number | null;
  avg_fl: number | null;
  max_fl: number | null;
  uptime_pct: number;
  sample_count: number;
}

test("avg and max are computed only over open samples", () => {
  const db = setup([
    s("p", 1, "2026-05-29", "10:00", true,  10, null),
    s("p", 1, "2026-05-29", "10:05", true,  20, null),
    s("p", 1, "2026-05-29", "10:10", true,  30, null),
    s("p", 1, "2026-05-29", "10:15", false, null, null),
    s("p", 1, "2026-05-29", "10:20", true,  40, null),
  ]);
  const rows = db.prepare(AGGREGATE_QUERY).all("p", 1, "2026-05-29") as AggregateRow[];
  assert.equal(rows.length, 1);
  assert.equal(rows[0].max_wait, 40);
  assert.equal(rows[0].avg_wait, (10 + 20 + 30 + 40) / 4);
  assert.equal(rows[0].sample_count, 5);
});

test("uptime_pct is open_samples / total_samples", () => {
  const db = setup([
    s("p", 1, "2026-05-29", "10:00", true,  10, null),
    s("p", 1, "2026-05-29", "10:05", true,  20, null),
    s("p", 1, "2026-05-29", "10:10", false, null, null),
    s("p", 1, "2026-05-29", "10:15", false, null, null),
  ]);
  const rows = db.prepare(AGGREGATE_QUERY).all("p", 1, "2026-05-29") as AggregateRow[];
  assert.equal(rows[0].uptime_pct, 0.5);
});

test("an all-closed day reports uptime 0 and null waits", () => {
  const db = setup([
    s("p", 1, "2026-05-29", "10:00", false, null, null),
    s("p", 1, "2026-05-29", "10:05", false, null, null),
  ]);
  const rows = db.prepare(AGGREGATE_QUERY).all("p", 1, "2026-05-29") as AggregateRow[];
  assert.equal(rows.length, 1);
  assert.equal(rows[0].uptime_pct, 0);
  assert.equal(rows[0].avg_wait, null);
  assert.equal(rows[0].max_wait, null);
});

test("multiple days are returned separately, ordered by local_date", () => {
  const db = setup([
    s("p", 1, "2026-05-29", "10:00", true, 10, null),
    s("p", 1, "2026-05-28", "10:00", true, 50, null),
    s("p", 1, "2026-05-30", "10:00", true, 30, null),
  ]);
  const rows = db.prepare(AGGREGATE_QUERY).all("p", 1, "2026-05-28") as AggregateRow[];
  assert.equal(rows.length, 3);
  assert.deepEqual(rows.map((r) => r.local_date), ["2026-05-28", "2026-05-29", "2026-05-30"]);
  assert.deepEqual(rows.map((r) => r.max_wait), [50, 10, 30]);
});

test("local_date filter excludes earlier days", () => {
  const db = setup([
    s("p", 1, "2026-05-20", "10:00", true, 99, null),  // before window
    s("p", 1, "2026-05-29", "10:00", true, 10, null),
  ]);
  const rows = db.prepare(AGGREGATE_QUERY).all("p", 1, "2026-05-29") as AggregateRow[];
  assert.equal(rows.length, 1);
  assert.equal(rows[0].local_date, "2026-05-29");
});

test("fast lane stats roll up independently of regular wait stats", () => {
  const db = setup([
    s("p", 1, "2026-05-29", "10:00", true, 30, 5),
    s("p", 1, "2026-05-29", "10:05", true, 40, 10),
    s("p", 1, "2026-05-29", "10:10", true, 50, null), // open but no FL data
  ]);
  const rows = db.prepare(AGGREGATE_QUERY).all("p", 1, "2026-05-29") as AggregateRow[];
  assert.equal(rows[0].max_fl, 10);
  assert.equal(rows[0].avg_fl, 7.5); // averaged over the two non-null FL samples
  assert.equal(rows[0].max_wait, 50);
});

test("parks and rides are isolated", () => {
  const db = setup([
    s("p1", 1, "2026-05-29", "10:00", true, 10, null),
    s("p1", 2, "2026-05-29", "10:00", true, 99, null),
    s("p2", 1, "2026-05-29", "10:00", true, 50, null),
  ]);
  const r = db.prepare(AGGREGATE_QUERY).all("p1", 1, "2026-05-29") as AggregateRow[];
  assert.equal(r[0].max_wait, 10);
  assert.equal(r[0].sample_count, 1);
});

// ── Helper ───────────────────────────────────────────────────────────────────

function s(
  parkId: string,
  qtRideId: number,
  localDate: string,
  localTime: string,
  isOpen: boolean,
  waitMinutes: number | null,
  fastLaneMinutes: number | null,
): Sample {
  return {
    parkId,
    qtRideId,
    recordedAt: `${localDate}T${localTime}:00Z`,
    localDate,
    localTime,
    isOpen,
    waitMinutes,
    fastLaneMinutes,
  };
}