linkder/packages/db/test/showcase.test.ts

/**
 * Integration test — runs against a live seeded database.
 *
 *   pnpm services:up && pnpm db:migrate && pnpm db:seed
 *   pnpm --filter @linkdr/db test
 *
 * getShowcaseDeck feeds the entry screen, which is the one deck an anonymous
 * visitor sees. Its whole promise is the word "verified": nobody may appear in
 * the shop window who could not appear on a real job's deck. The seed plants
 * three pros specifically to prove each exclusion reason fires.
 */
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 { getShowcaseDeck } = await import('../src/queries/deck');

/** The seed places the fixture job, and every distance, relative to this point. */
const CENTRE = {
  lat: Number(process.env.NEXT_PUBLIC_CITY_LAT ?? 19.4326),
  lng: Number(process.env.NEXT_PUBLIC_CITY_LNG ?? -99.1332),
};

let names: (string | null)[];

beforeAll(async () => {
  const cards = await getShowcaseDeck(db, { ...CENTRE, limit: 100 });
  names = cards.map((c) => c.name);
});

afterAll(async () => {
  await closePool();
});

describe('getShowcaseDeck', () => {
  it('returns pros with no job and no session', async () => {
    expect(names.length).toBeGreaterThan(0);
  });

  it('excludes a pro who will not travel this far', () => {
    // Pablo Rivas is seeded 22 km out with a 5 km service radius.
    expect(names).not.toContain('Pablo Rivas');
  });

  it('excludes a pro whose verification has not passed', () => {
    // Unverified Ulises is 1 km away — close enough to prove distance is not
    // what is keeping her out.
    expect(names).not.toContain('Unverified Ulises');
  });

  it('excludes a verified pro who is not accepting work', () => {
    // Away Arturo is verified and nearby, but on holiday mode.
    expect(names).not.toContain('Away Arturo');
  });

  it('includes the nearest eligible pro', () => {
    expect(names).toContain('Sergio Fabela');
  });

  it('honours the limit', async () => {
    const three = await getShowcaseDeck(db, { ...CENTRE, limit: 3 });
    expect(three).toHaveLength(3);
  });

  it('honours the searcher own range, not just the pro one', async () => {
    // Mario Villanueva is seeded 9.1 km out with a 30 km radius: they would travel
    // here happily, but someone who said "within 3 km" did not ask for her.
    const near = await getShowcaseDeck(db, { ...CENTRE, maxDistanceM: 3_000, limit: 100 });
    const nearNames = near.map((c) => c.name);

    expect(nearNames).not.toContain('Mario Villanueva');
    expect(nearNames).toContain('Sergio Fabela'); // 1.1 km away
    expect(near.every((c) => c.distanceM <= 3_000)).toBe(true);
  });

  it('narrows to a single trade when given a category', async () => {
    const [plumber] = await db.execute<{ id: string }>(
      sql`SELECT id FROM categories WHERE slug = 'plumber' LIMIT 1`,
    );
    if (!plumber) throw new Error('plumber category missing from seed');

    const cards = await getShowcaseDeck(db, { ...CENTRE, categoryId: plumber.id, limit: 100 });

    expect(cards.length).toBeGreaterThan(0);
    // Every card carries its trade names, so the filter is checkable per card.
    expect(cards.every((c) => c.categories.includes('Plumber'))).toBe(true);
    // And it must be a strict subset — otherwise the filter did nothing.
    expect(cards.length).toBeLessThan(names.length);
  });

  it('still excludes the ineligible when a category is given', async () => {
    const [plumber] = await db.execute<{ id: string }>(
      sql`SELECT id FROM categories WHERE slug = 'plumber' LIMIT 1`,
    );
    if (!plumber) throw new Error('plumber category missing from seed');

    const cards = await getShowcaseDeck(db, { ...CENTRE, categoryId: plumber.id, limit: 100 });
    const filtered = cards.map((c) => c.name);

    // Pablo Rivas is a plumber — he is kept out by radius, not by trade, so this
    // proves the category filter did not replace the eligibility rules.
    expect(filtered).not.toContain('Pablo Rivas');
    expect(filtered).not.toContain('Unverified Ulises');
    expect(filtered).not.toContain('Away Arturo');
  });

  it('never returns a card with a distance beyond that pro’s own radius', async () => {
    const cards = await getShowcaseDeck(db, { ...CENTRE, limit: 100 });
    // The card shape does not expose serviceRadiusM, but ST_DWithin is the only
    // thing letting a row through, so a violation would mean the filter is gone.
    expect(cards.every((c) => c.distanceM >= 0)).toBe(true);
    expect(cards.length).toBeLessThanOrEqual(100);
  });
});