linkder/packages/db/test/stats.test.ts

/**
 * Integration test — runs against a live seeded database.
 *
 *   pnpm services:up && pnpm db:migrate && pnpm db:seed
 *
 * `recomputeProStats` is the only thing that ever writes the five denormalised
 * ranking counters, and `score()` reads all five. If it drifts from the rows it
 * claims to summarise, the deck ranks on fiction and nothing fails — which is
 * the situation this function was written to end, so it needs a test that
 * checks the arithmetic rather than that it ran.
 */
import { config } from 'dotenv';
import { sql } from 'drizzle-orm';
import { afterAll, beforeAll, describe, expect, it } from 'vitest';

config({ path: '../../.env' });

const { closePool, db } = await import('../src/client');
const { recomputeProStats } = await import('../src/queries/stats');

const RUN = Math.random().toString(36).slice(2, 8);

interface Counters {
  rating_avg: string | null;
  rating_count: number;
  completed_jobs: number;
  response_rate: string | null;
  avg_response_minutes: number | null;
}

/** This file's own pro and client, so a parallel test file never sees them. */
let pro: string;
let client: string;
let categoryId: string;

async function counters(): Promise<Counters> {
  const [row] = await db.execute<Counters>(sql`
    SELECT rating_avg, rating_count, completed_jobs, response_rate, avg_response_minutes
      FROM pro_profiles WHERE user_id = ${pro}
  `);
  return row!;
}

/**
 * One finished, reviewed job.
 *
 * The whole chain, because that is what the counters read: a review hangs off a
 * booking, which hangs off a quote, a match and a request.
 */
async function completedJob(opts: {
  rating: number;
  published: boolean;
  replyMinutes: number;
}): Promise<void> {
  const [job] = await db.execute<{ id: string }>(sql`
    INSERT INTO jobs (client_id, category_id, title, description, urgency, location, address_text, status)
    VALUES (${client}, ${categoryId}, 'Stats probe', 'Probe job for the stats tests.', 'flexible',
            ST_SetSRID(ST_MakePoint(0.7, 0.7), 4326)::geography, 'Nowhere', 'completed')
    RETURNING id
  `);
  const [request] = await db.execute<{ id: string }>(sql`
    INSERT INTO requests (job_id, pro_id, status, created_at, responded_at, expires_at)
    VALUES (${job!.id}, ${pro}, 'accepted',
            now() - interval '10 days',
            now() - interval '10 days' + ${`${opts.replyMinutes} minutes`}::interval,
            now() - interval '8 days')
    RETURNING id
  `);
  const [match] = await db.execute<{ id: string }>(sql`
    INSERT INTO matches (request_id, job_id, pro_id, client_id)
    VALUES (${request!.id}, ${job!.id}, ${pro}, ${client}) RETURNING id
  `);
  const [quote] = await db.execute<{ id: string }>(sql`
    INSERT INTO quotes (match_id, kind, amount_cents, scope, status, valid_until)
    VALUES (${match!.id}, 'fixed', 10000, 'Probe', 'accepted', now() - interval '7 days')
    RETURNING id
  `);
  const [booking] = await db.execute<{ id: string }>(sql`
    INSERT INTO bookings (match_id, quote_id, scheduled_start, scheduled_end, status)
    VALUES (${match!.id}, ${quote!.id}, now() - interval '6 days',
            now() - interval '6 days' + interval '2 hours', 'completed')
    RETURNING id
  `);
  await db.execute(sql`
    INSERT INTO reviews (booking_id, author_id, subject_id, rating, body, published_at)
    VALUES (${booking!.id}, ${client}, ${pro}, ${opts.rating}, 'Probe review.',
            ${opts.published ? sql`now() - interval '5 days'` : sql`NULL`})
  `);
}

/** A request the pro let run out. Counts against responseRate, nothing else. */
async function ignoredRequest(): Promise<void> {
  const [job] = await db.execute<{ id: string }>(sql`
    INSERT INTO jobs (client_id, category_id, title, description, urgency, location, address_text, status)
    VALUES (${client}, ${categoryId}, 'Stats probe (ignored)', 'Probe job.', 'flexible',
            ST_SetSRID(ST_MakePoint(0.7, 0.7), 4326)::geography, 'Nowhere', 'cancelled')
    RETURNING id
  `);
  await db.execute(sql`
    INSERT INTO requests (job_id, pro_id, status, created_at, expires_at)
    VALUES (${job!.id}, ${pro}, 'expired', now() - interval '10 days', now() - interval '8 days')
  `);
}

beforeAll(async () => {
  const [cat] = await db.execute<{ id: string }>(
    sql`SELECT id FROM categories ORDER BY position LIMIT 1`,
  );
  categoryId = cat!.id;

  const [p] = await db.execute<{ id: string }>(sql`
    INSERT INTO users (name, email, role) VALUES ('Stats Probe', ${`stats-pro-${RUN}@example.com`}, 'pro')
    RETURNING id
  `);
  pro = p!.id;

  const [c] = await db.execute<{ id: string }>(sql`
    INSERT INTO users (name, email, role) VALUES ('Stats Client', ${`stats-client-${RUN}@example.com`}, 'client')
    RETURNING id
  `);
  client = c!.id;

  await db.execute(sql`
    INSERT INTO pro_profiles (
      user_id, headline, bio, hourly_rate_cents, base_location, service_radius_m,
      verification_status, verified_at, rating_avg, rating_count, completed_jobs,
      response_rate, avg_response_minutes
    )
    VALUES (
      ${pro}, 'Stats probe', 'Exists only for the stats tests.', 3000,
      ST_SetSRID(ST_MakePoint(0.7, 0.7), 4326)::geography, 15000, 'verified', now(),
      -- Deliberate nonsense, so a test that passes proves the recompute WROTE
      -- rather than that the seeded value happened to be right.
      '1.00', 999, 999, '0.001', 999
    )
  `);
});

afterAll(async () => {
  await db.execute(sql`DELETE FROM users WHERE id IN (${pro}, ${client})`);
  await closePool();
});

describe('recomputeProStats', () => {
  it('zeroes a pro with no history instead of leaving stale numbers', async () => {
    await recomputeProStats(db, pro);
    const c = await counters();

    expect(c.rating_count).toBe(0);
    expect(c.completed_jobs).toBe(0);
    // Not 0.0 — an unrated pro has no rating, and "0.0 ★" reads as a bad score
    // everywhere it is rendered.
    expect(c.rating_avg).toBeNull();
    // Likewise: nobody has asked them anything, so there is no rate to report.
    expect(c.response_rate).toBeNull();
    expect(c.avg_response_minutes).toBeNull();
  });

  it('averages the published reviews and counts them', async () => {
    await completedJob({ rating: 5, published: true, replyMinutes: 10 });
    await completedJob({ rating: 4, published: true, replyMinutes: 30 });
    await recomputeProStats(db, pro);

    const c = await counters();
    expect(c.rating_count).toBe(2);
    expect(Number(c.rating_avg)).toBeCloseTo(4.5, 2);
    expect(c.completed_jobs).toBe(2);
    expect(c.avg_response_minutes).toBe(20);
  });

  it('ignores an embargoed review, so the count matches what pro.reviews lists', async () => {
    // Published_at is the moderation gate. A rating counted in the header but
    // absent from the list underneath is the exact mismatch the seed used to
    // have, and the reason both read the same predicate.
    await completedJob({ rating: 1, published: false, replyMinutes: 10 });
    await recomputeProStats(db, pro);

    const c = await counters();
    expect(c.rating_count).toBe(2);
    expect(Number(c.rating_avg)).toBeCloseTo(4.5, 2);
    // The booking behind it still happened, though — that is not moderated.
    expect(c.completed_jobs).toBe(3);
  });

  it('scores response rate over decided requests, not over sent ones', async () => {
    await ignoredRequest();
    await recomputeProStats(db, pro);

    // Three answered, one expired unanswered.
    expect(Number((await counters()).response_rate)).toBeCloseTo(0.75, 3);
  });

  it('does not count a request that is still inside its window', async () => {
    const [job] = await db.execute<{ id: string }>(sql`
      INSERT INTO jobs (client_id, category_id, title, description, urgency, location, address_text)
      VALUES (${client}, ${categoryId}, 'Stats probe (live)', 'Probe job.', 'flexible',
              ST_SetSRID(ST_MakePoint(0.7, 0.7), 4326)::geography, 'Nowhere')
      RETURNING id
    `);
    await db.execute(sql`
      INSERT INTO requests (job_id, pro_id, status, created_at, expires_at)
      VALUES (${job!.id}, ${pro}, 'pending', now(), now() + interval '12 hours')
    `);
    await recomputeProStats(db, pro);

    // Still 0.75: a request that arrived an hour ago has not been ignored, and
    // counting it as a miss would punish a pro for work that just came in.
    expect(Number((await counters()).response_rate)).toBeCloseTo(0.75, 3);
  });

  it('is idempotent — it derives rather than increments', async () => {
    await recomputeProStats(db, pro);
    const once = await counters();
    await recomputeProStats(db, pro);
    await recomputeProStats(db, pro);
    expect(await counters()).toEqual(once);
  });
});