import { and, eq, sql } from 'drizzle-orm';
import type { Database } from '@rareindex/database';
import { savedSearches, notifications } from '@rareindex/database';
import { logger, newId } from '@rareindex/shared';

const log = logger.child({ job: 'account.saved-searches' });

/**
 * Re-run saved searches that have notifications enabled and report growth in matches.
 * Uses a conservative server-side approximation (title trigram + category param) so it works
 * independently of the web search module; counts are informative, not authoritative.
 */
export async function runSavedSearches(db: Database, now = new Date()): Promise<number> {
  const rows = await db.select().from(savedSearches).where(eq(savedSearches.notify, true));
  let notified = 0;
  for (const s of rows) {
    const params = s.params as Record<string, string>;
    const q = (params.q ?? '').trim();
    const category = params.category ?? params.cat ?? null;
    if (!q && !category) continue;
    const res = (await db.execute(sql`
      select count(*)::int as n from assets a
      where (${q ? sql`(a.title ilike ${'%' + q + '%'} or a.search @@ plainto_tsquery('simple', ${q}))` : sql`true`})
        and (${category ? sql`(a.category_slug = ${category} or a.family_slug = ${category})` : sql`true`})
    `)) as unknown as Array<{ n: number }>;
    const n = res[0]?.n ?? 0;
    const prev = s.lastCount;
    await db.update(savedSearches).set({ lastRunAt: now, lastCount: n }).where(eq(savedSearches.id, s.id));
    if (prev !== null && n > prev) {
      await db.insert(notifications).values({ id: newId('event'), userId: s.userId, kind: 'system', title: `${n - prev} new match${n - prev === 1 ? '' : 'es'} for “${s.name}”`, body: `${n} assets now match your saved search.`, href: s.url, payload: { savedSearchId: s.id, previous: prev, current: n } });
      notified++;
    }
  }
  log.info({ searches: rows.length, notified }, 'saved searches re-run');
  return notified;
}

export async function markSavedSearchRun(db: Database, id: string, count: number): Promise<void> {
  await db.update(savedSearches).set({ lastRunAt: new Date(), lastCount: count }).where(and(eq(savedSearches.id, id)));
}