TX/artifacts/api-server/tests/admin-app-opens-pagination.test.mjs

import { test, before, after } from "node:test";
import assert from "node:assert/strict";
import pg from "pg";

const API_BASE = process.env.TEST_API_BASE ?? "http://localhost:8080";
const DATABASE_URL = process.env.DATABASE_URL;
if (!DATABASE_URL) {
  throw new Error("DATABASE_URL must be set to run these tests");
}

const TEST_PASSWORD = "TestPass123!";
const TEST_PASSWORD_HASH =
  "$2b$10$Bs636ukPMyz01nKrsi.5m.JlDXSN22AVCvn8cgPWWDbo5yJRQX2vu";

const pool = new pg.Pool({ connectionString: DATABASE_URL });

let adminUserId;
let adminUsername;
let appId;
let sessionCookie;
const insertedOpenIds = [];

async function loginAndGetCookie(username, password) {
  const res = await fetch(`${API_BASE}/api/auth/login`, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ username, password }),
  });
  assert.equal(res.status, 200, `login expected 200, got ${res.status}`);
  const setCookie = res.headers.get("set-cookie");
  assert.ok(setCookie, "expected Set-Cookie header from login");
  const sid = setCookie
    .split(",")
    .map((c) => c.split(";")[0].trim())
    .find((c) => c.startsWith("connect.sid="));
  assert.ok(sid, "expected connect.sid cookie");
  return sid;
}

before(async () => {
  adminUsername = `admin_paging_${Date.now().toString(36)}_${Math.random()
    .toString(36)
    .slice(2, 6)}`;

  const userRes = await pool.query(
    `INSERT INTO users (username, email, password_hash, display_name_en, preferred_language, is_active)
     VALUES ($1, $2, $3, 'Admin Paging Test', 'en', true) RETURNING id`,
    [adminUsername, `${adminUsername}@example.com`, TEST_PASSWORD_HASH],
  );
  adminUserId = userRes.rows[0].id;

  await pool.query(
    `INSERT INTO user_roles (user_id, role_id)
     SELECT $1, id FROM roles WHERE name = 'admin'`,
    [adminUserId],
  );

  const appRes = await pool.query(
    `SELECT id FROM apps WHERE is_active = true ORDER BY id ASC LIMIT 1`,
  );
  assert.ok(appRes.rows[0], "no active app found to test against");
  appId = appRes.rows[0].id;

  // Walking pagination assumes totalCount is bounded. Other tests can leave
  // app_opens rows behind (or seed/admin runs may have populated some) for
  // the same app within the 7d window. Push any pre-existing rows for this
  // app outside the window so totalCount reflects only the rows we insert.
  await pool.query(
    `UPDATE app_opens SET created_at = NOW() - INTERVAL '30 days'
     WHERE app_id = $1 AND created_at > NOW() - INTERVAL '7 days'`,
    [appId],
  );

  // Insert 3 opens for this admin user/app within the past few minutes,
  // strictly within the 7d range.
  const now = Date.now();
  for (let i = 0; i < 3; i++) {
    const ts = new Date(now - i * 60 * 1000).toISOString();
    const r = await pool.query(
      `INSERT INTO app_opens (user_id, app_id, created_at) VALUES ($1, $2, $3) RETURNING id`,
      [adminUserId, appId, ts],
    );
    insertedOpenIds.push(r.rows[0].id);
  }

  sessionCookie = await loginAndGetCookie(adminUsername, TEST_PASSWORD);
});

after(async () => {
  if (insertedOpenIds.length > 0) {
    await pool.query(`DELETE FROM app_opens WHERE id = ANY($1::int[])`, [insertedOpenIds]);
  }
  if (adminUserId !== undefined) {
    await pool.query(`DELETE FROM app_opens WHERE user_id = $1`, [adminUserId]);
    await pool.query(`DELETE FROM user_roles WHERE user_id = $1`, [adminUserId]);
    await pool.query(`DELETE FROM users WHERE id = $1`, [adminUserId]);
  }
  await pool.end();
});

async function getJson(path) {
  const res = await fetch(`${API_BASE}${path}`, {
    headers: { Cookie: sessionCookie },
  });
  return { status: res.status, body: await res.json() };
}

test("by-app: paginating with limit returns nextOffset until exhausted", async () => {
  const page1 = await getJson(
    `/api/stats/admin/app-opens/by-app/${appId}?range=7d&limit=2`,
  );
  assert.equal(page1.status, 200);
  assert.ok(page1.body.totalCount >= 3, "totalCount should include our inserted opens");
  assert.equal(page1.body.limit, 2);
  assert.equal(page1.body.offset, 0);
  assert.equal(page1.body.opens.length, 2);
  assert.equal(page1.body.nextOffset, 2);

  const page2 = await getJson(
    `/api/stats/admin/app-opens/by-app/${appId}?range=7d&limit=2&offset=2`,
  );
  assert.equal(page2.status, 200);
  assert.equal(page2.body.offset, 2);
  assert.equal(page2.body.opens.length >= 1, true);

  // Walk to the last page; nextOffset eventually becomes null.
  let nextOffset = page2.body.nextOffset;
  let safety = 50;
  while (nextOffset !== null && safety-- > 0) {
    const np = await getJson(
      `/api/stats/admin/app-opens/by-app/${appId}?range=7d&limit=2&offset=${nextOffset}`,
    );
    assert.equal(np.status, 200);
    nextOffset = np.body.nextOffset;
  }
  assert.equal(nextOffset, null, "should reach end of pages");
});

test("by-user: paginating with limit returns nextOffset until exhausted", async () => {
  const page1 = await getJson(
    `/api/stats/admin/app-opens/by-user/${adminUserId}?range=7d&limit=2`,
  );
  assert.equal(page1.status, 200);
  assert.equal(page1.body.totalCount, 3);
  assert.equal(page1.body.limit, 2);
  assert.equal(page1.body.offset, 0);
  assert.equal(page1.body.opens.length, 2);
  assert.equal(page1.body.nextOffset, 2);

  const page2 = await getJson(
    `/api/stats/admin/app-opens/by-user/${adminUserId}?range=7d&limit=2&offset=2`,
  );
  assert.equal(page2.status, 200);
  assert.equal(page2.body.offset, 2);
  assert.equal(page2.body.opens.length, 1);
  assert.equal(page2.body.nextOffset, null);
});

test("by-app: invalid limit returns 400", async () => {
  const r = await getJson(
    `/api/stats/admin/app-opens/by-app/${appId}?range=7d&limit=0`,
  );
  assert.equal(r.status, 400);
});

test("by-app: limit above max returns 400", async () => {
  const r = await getJson(
    `/api/stats/admin/app-opens/by-app/${appId}?range=7d&limit=999`,
  );
  assert.equal(r.status, 400);
});

test("by-user: invalid offset returns 400", async () => {
  const r = await getJson(
    `/api/stats/admin/app-opens/by-user/${adminUserId}?range=7d&offset=-1`,
  );
  assert.equal(r.status, 400);
});

test("by-app: default page size is 100 when no limit specified", async () => {
  const r = await getJson(
    `/api/stats/admin/app-opens/by-app/${appId}?range=7d`,
  );
  assert.equal(r.status, 200);
  assert.equal(r.body.limit, 100);
  assert.equal(r.body.offset, 0);
});