import { and, eq, lt, sql } from 'drizzle-orm';
import { connectors as connectorsTable, listingEvents, listings } from '@rareindex/database';
import { logger, newId } from '@rareindex/shared';
import { db } from './lib/db.ts';

/**
 * Listings not seen for 3× the connector refresh interval (min 3 days) are marked `removed`
 * (§171 change detection). They are kept for history; a later sighting re-opens them (relisted).
 */
export async function expireListings(): Promise<number> {
  const cons = await db().select({ id: connectorsTable.id, refresh: connectorsTable.refreshFrequencyMinutes }).from(connectorsTable);
  let n = 0;
  for (const c of cons) {
    const ttlMs = Math.max(3 * 86_400_000, c.refresh * 60_000 * 3);
    const cutoff = new Date(Date.now() - ttlMs);
    const stale = await db().select({ id: listings.id, price: listings.price, currency: listings.currency }).from(listings).where(and(eq(listings.connectorId, c.id), eq(listings.availability, 'available'), lt(listings.lastSeenAt, cutoff))).limit(5000);
    if (!stale.length) continue;
    await db().update(listings).set({ availability: 'removed', updatedAt: new Date() }).where(sql`${listings.id} in ${stale.map((s) => s.id)}`);
    await db().insert(listingEvents).values(stale.map((s) => ({ id: newId('event'), listingId: s.id, eventType: 'removed', oldPrice: s.price, currency: s.currency, occurredAt: new Date() })));
    n += stale.length;
  }
  if (n) logger.info({ expired: n }, 'listings expired');
  return n;
}