solelog/apps/api/test/integration/report-aggregation.test.ts

import { describe, it, expect, beforeEach } from 'vitest';
import { eq } from 'drizzle-orm';
import { createApp } from '../../src/app';
import { db } from '../../src/db/client';
import { user, workSessions } from '../../src/db/schema';
import { authToken, bearer, seedActivity, resetDb } from '../helpers';

const WIDE = `from=${new Date('2000-01-01').toISOString()}&to=${new Date('2100-01-01').toISOString()}`;

async function userIdByEmail(email: string): Promise<string> {
  const [u] = await db.select({ id: user.id }).from(user).where(eq(user.email, email));
  return u.id;
}

// Insert a completed session with exact, known metrics — no timing, fully deterministic.
async function seedCompleted(opts: {
  userId: string;
  activityId: number;
  insoleType: string;
  worked: number;
  paused: number;
  pairs: number;
}): Promise<void> {
  const start = new Date('2026-03-01T08:00:00.000Z');
  await db.insert(workSessions).values({
    userId: opts.userId,
    activityId: opts.activityId,
    insoleType: opts.insoleType,
    pairCount: opts.pairs,
    startTime: start,
    endTime: new Date(start.getTime() + (opts.worked + opts.paused) * 1000),
    durationSeconds: opts.worked,
    pausedSeconds: opts.paused,
    status: 'completed',
    source: 'manual',
  });
}

describe('report aggregation lifecycle (2 workers × 2 activities → exact totals + breakdowns)', () => {
  beforeEach(resetDb);

  it('reports exact totals, breakdowns sorted by worked desc, and narrows on a worker filter', async () => {
    const app = createApp();
    const adminTok = await authToken(app, 'report-agg-life-admin@example.com', 'admin');
    await authToken(app, 'report-agg-life-w1@example.com');
    await authToken(app, 'report-agg-life-w2@example.com');
    const w1 = await userIdByEmail('report-agg-life-w1@example.com');
    const w2 = await userIdByEmail('report-agg-life-w2@example.com');
    const frezen = await seedActivity('Frezen');
    const lijmen = await seedActivity('Lijmen');

    // W1: Frezen/Kurk 300s, Lijmen/Berk 100s. W2: Frezen/Kurk 200s, Lijmen/Berk 50s.
    await seedCompleted({
      userId: w1,
      activityId: frezen,
      insoleType: 'Kurk',
      worked: 300,
      paused: 30,
      pairs: 2,
    });
    await seedCompleted({
      userId: w1,
      activityId: lijmen,
      insoleType: 'Berk',
      worked: 100,
      paused: 10,
      pairs: 1,
    });
    await seedCompleted({
      userId: w2,
      activityId: frezen,
      insoleType: 'Kurk',
      worked: 200,
      paused: 20,
      pairs: 3,
    });
    await seedCompleted({
      userId: w2,
      activityId: lijmen,
      insoleType: 'Berk',
      worked: 50,
      paused: 5,
      pairs: 1,
    });

    const body = await (
      await app.request(`/api/admin/report?${WIDE}`, { headers: bearer(adminTok) })
    ).json();

    // Totals sum every session.
    expect(body.totals).toEqual({ worked_seconds: 650, paused_seconds: 65, pairs: 7, sessions: 4 });

    // by_worker: W1 (400) before W2 (250) — sorted worked desc.
    expect(body.by_worker.map((w: { user_name: string }) => w.user_name)).toEqual([
      'report-agg-life-w1',
      'report-agg-life-w2',
    ]);
    expect(body.by_worker[0]).toMatchObject({
      user_id: w1,
      worked_seconds: 400,
      paused_seconds: 40,
      pairs: 3,
      sessions: 2,
    });
    expect(body.by_worker[1]).toMatchObject({
      user_id: w2,
      worked_seconds: 250,
      paused_seconds: 25,
      pairs: 4,
      sessions: 2,
    });

    // by_activity: Frezen (500) before Lijmen (150).
    expect(body.by_activity.map((a: { activity_name: string }) => a.activity_name)).toEqual([
      'Frezen',
      'Lijmen',
    ]);
    expect(body.by_activity[0]).toMatchObject({
      activity_id: frezen,
      worked_seconds: 500,
      pairs: 5,
      sessions: 2,
    });
    expect(body.by_activity[1]).toMatchObject({
      activity_id: lijmen,
      worked_seconds: 150,
      pairs: 2,
      sessions: 2,
    });

    // by_type: Kurk (500) before Berk (150).
    expect(body.by_type.map((t: { insole_type: string }) => t.insole_type)).toEqual([
      'Kurk',
      'Berk',
    ]);
    expect(body.by_type[0]).toMatchObject({
      insole_type: 'Kurk',
      worked_seconds: 500,
      pairs: 5,
      sessions: 2,
    });
    expect(body.by_type[1]).toMatchObject({
      insole_type: 'Berk',
      worked_seconds: 150,
      pairs: 2,
      sessions: 2,
    });

    // A worker filter narrows totals and breakdowns to W1 only.
    const narrowed = await (
      await app.request(`/api/admin/report?${WIDE}&user_id=${w1}`, { headers: bearer(adminTok) })
    ).json();
    expect(narrowed.totals).toEqual({
      worked_seconds: 400,
      paused_seconds: 40,
      pairs: 3,
      sessions: 2,
    });
    expect(narrowed.by_worker).toHaveLength(1);
    expect(narrowed.by_worker[0].user_id).toBe(w1);
  });
});