`src/server/listing-lifecycle.dbtest.ts`

166 lines
/**
 * TK-10155 withdrawal-snapshot DB self-test — end-to-end against the real schema.
 *   run: `tsx src/server/listing-lifecycle.dbtest.ts`  (or `npm run test:lifecycle:db`)
 *
 * Follows the house self-test pattern (provenance_selftest.ts): no test framework, exits
 * non-zero on failure, uses a clearly-SYNTHETIC source ('selftest-tk10155') so it can never
 * collide with, or leave residue in, real ingested data — it purges everything it created.
 *
 * Proves the HEADLINE promise on the real tables: when a listing transitions to WITHDRAWN,
 * recordLifecycle preserves a FULL listing_snapshot (the entire listing row + joined
 * broker/firm/region names) so the property data survives after the listing vanishes — AND
 * that the preserved snapshot can reconstruct the listing after the row itself is deleted.
 */
import { pool, query } from '../../db/pool.ts';
import {
  recordLifecycle,
  ensureBaselineSnapshot,
  type LifecycleListing,
  type LifecycleCtx,
} from './listing-lifecycle.ts';

const SOURCE = 'selftest-tk10155';
const SID = 'SNAP-1';
const DAY = 86_400_000;

let failed = 0;
function check(ok: boolean, label: string, detail?: unknown) {
  if (ok) { console.log(`  ok   ${label}`); return; }
  failed++;
  console.error(`  FAIL ${label}${detail !== undefined ? `\n       ${JSON.stringify(detail)}` : ''}`);
}

async function purge() {
  await query(`DELETE FROM listing_snapshot        WHERE source=$1`, [SOURCE]);
  await query(`DELETE FROM listing_lifecycle_event WHERE source=$1`, [SOURCE]);
  await query(`DELETE FROM listing                 WHERE source=$1`, [SOURCE]);
}

async function main() {
  await purge(); // clean slate in case a prior run aborted before its own purge

  const now = new Date();
  const address = '742 Evergreen Terrace, Springfield';
  const price = 875000;

  // 1. insert a synthetic listing, freshly seen.
  const ins = await query<{ id: number }>(
    `INSERT INTO listing (source, source_id, address, price, beds, baths, sqft, status, first_seen, last_seen)
     VALUES ($1,$2,$3,$4,4,3,2600,NULL,$5,$5) RETURNING id`,
    [SOURCE, SID, address, price, new Date(now.getTime() - 120 * DAY).toISOString()],
  );
  const id = ins.rows[0].id;
  check(Number.isInteger(id) && id > 0, 'synthetic listing inserted', { id });

  const listing: LifecycleListing = {
    id, source: SOURCE, source_id: SID, address, price,
    status: null, first_seen: new Date(now.getTime() - 120 * DAY), last_seen: new Date(now),
  };
  // full rescan happened now → gone-from-feed aging is eligible.
  const ctxSeen: LifecycleCtx = { sourceMaxLastSeen: new Date(now), lastFullRescanAt: new Date(now), now };

  // 2. first record. first_seen is 120d ago so it classifies 'active' (not 'new'), and 'active'
  //    is NOT a snapshot state — so no snapshot yet. The withdrawal snapshot (step 3) is the one
  //    the durability promise hinges on.
  const r1 = await recordLifecycle(query, listing, ctxSeen);
  check(r1.state === 'active' && r1.eventInserted && !r1.snapshotInserted,
    "first-see (old first_seen) → 'active' event, no snapshot (not a snapshot state)", r1);

  // 3. now the listing has vanished from the feed for > WITHDRAWN_DAYS. Its last_seen is old,
  //    but the source's reference (max last_seen) has moved forward (later listings were seen).
  const goneListing: LifecycleListing = {
    ...listing,
    last_seen: new Date(now.getTime() - 45 * DAY), // 45d behind the source reference
  };
  const ctxGone: LifecycleCtx = {
    sourceMaxLastSeen: new Date(now),      // source was refreshed at "now"
    lastFullRescanAt: new Date(now),       // via a full rescan
    now,
  };
  const r2 = await recordLifecycle(query, goneListing, ctxGone);
  check(
    r2.state === 'withdrawn' && r2.prev_state === 'active' && r2.snapshotInserted,
    'gone-from-feed (active → withdrawn) + a preserved snapshot',
    r2,
  );

  // 4. assert the WITHDRAWAL snapshot carries the full property info + joined names.
  const snapRes = await query<{ captured_reason: string; snapshot: any }>(
    `SELECT captured_reason, snapshot FROM listing_snapshot
      WHERE source=$1 AND source_id=$2 AND captured_reason='withdrawn'
      ORDER BY captured_at DESC LIMIT 1`,
    [SOURCE, SID],
  );
  const snap = snapRes.rows[0]?.snapshot;
  check(!!snap, 'withdrawal snapshot row exists');
  if (snap) {
    check(Number(snap.price) === price, 'snapshot preserved price', snap.price);
    check(snap.address === address, 'snapshot preserved address', snap.address);
    check(snap.beds != null && snap.sqft != null, 'snapshot preserved beds/sqft', { beds: snap.beds, sqft: snap.sqft });
    check('broker_name' in snap && 'firm_name' in snap && 'region_name' in snap,
      'snapshot carries joined broker/firm/region name fields (nullable ok)');
  }

  // 5. reconstruction test: DELETE the listing row (it "vanished"), the snapshot must still
  //    fully describe it — the whole point of preservation.
  await query(`DELETE FROM listing WHERE id=$1`, [id]);
  const afterDel = await query<{ snapshot: any }>(
    `SELECT snapshot FROM listing_snapshot WHERE source=$1 AND source_id=$2 AND captured_reason='withdrawn' LIMIT 1`,
    [SOURCE, SID],
  );
  const preserved = afterDel.rows[0]?.snapshot;
  check(
    !!preserved && preserved.address === address && Number(preserved.price) === price,
    'after listing row DELETED, snapshot still fully reconstructs the listing',
    preserved && { address: preserved.address, price: preserved.price },
  );

  // 6. Cody FIX-FIRST gate: the 'active'-with-no-snapshot gap. A listing whose first-see snapshot
  //    hook failed and that derives straight to 'active' (not a snapshot state) has NO snapshot —
  //    ensureBaselineSnapshot (called by the sweep) must rescue it, and be idempotent.
  const SID2 = 'SNAP-2';
  await query(`DELETE FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
  await query(`DELETE FROM listing_lifecycle_event WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
  await query(`DELETE FROM listing WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
  const ins2 = await query<{ id: number }>(
    `INSERT INTO listing (source, source_id, address, price, first_seen, last_seen)
     VALUES ($1,$2,'9 Nowhere Rd, Ogdenville',321000,$3,$3) RETURNING id`,
    [SOURCE, SID2, new Date(now.getTime() - 300 * DAY).toISOString()],
  );
  const id2 = ins2.rows[0].id;
  const active: LifecycleListing = {
    id: id2, source: SOURCE, source_id: SID2, address: '9 Nowhere Rd, Ogdenville', price: 321000,
    status: null, first_seen: new Date(now.getTime() - 300 * DAY), last_seen: new Date(now),
  };
  // record only an 'active' event (simulating first-see snapshot hook having failed → no snapshot).
  // classifyState → 'active' (first_seen 300d ago, currently seen); 'active' is NOT a snapshot state,
  // so recordLifecycle writes the event but NO snapshot — reproducing the exact gap Cody flagged.
  const ra = await recordLifecycle(query, active, ctxSeen);
  check(ra.state === 'active' && ra.eventInserted && !ra.snapshotInserted,
    "recordLifecycle on an 'active' listing writes event, NO snapshot (reproduces the gap)", ra);
  const snapCountBefore = await query<{ n: string }>(
    `SELECT count(*)::text n FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
  check(Number(snapCountBefore.rows[0].n) === 0, "active-derived listing starts with NO snapshot (the gap)");
  const rescued = await ensureBaselineSnapshot(query, active);
  check(rescued === true, 'ensureBaselineSnapshot rescues the snapshot-less listing');
  const snapCountAfter = await query<{ n: string }>(
    `SELECT count(*)::text n FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
  check(Number(snapCountAfter.rows[0].n) === 1, 'exactly one baseline snapshot now exists');
  const again = await ensureBaselineSnapshot(query, active);
  check(again === false, 'ensureBaselineSnapshot is idempotent (no-op when a snapshot exists)');
  await query(`DELETE FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
  await query(`DELETE FROM listing_lifecycle_event WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
  await query(`DELETE FROM listing WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);

  await purge();
  console.log(`\n${failed === 0 ? 'PASS — withdrawal snapshot survives listing deletion + no-snapshot gap closed' : `FAIL — ${failed} assertion(s) failed`}\n`);
  await pool.end();
  process.exit(failed === 0 ? 0 : 1);
}

main().catch(async (e) => {
  console.error(e);
  try { await purge(); await pool.end(); } catch {}
  process.exit(1);
});