import { sql } from 'drizzle-orm';
import type { Database } from '@rareindex/database';
import { userBadges } from '@rareindex/database';
import { logger } from '@rareindex/shared';

const log = logger.child({ job: 'account.badges' });

/** Recompute data-driven badges for every active member (no fake badges: each has stored evidence). */
export async function runBadges(db: Database): Promise<number> {
  const rows = (await db.execute(sql`
    with items as (
      select c.user_id, ci.id, a.category_slug, ci.certification_number, ci.acquired_at, ci.purchase_price_usd
      from collection_items ci join collections c on c.id = ci.collection_id join assets a on a.id = ci.asset_id
    ), watch as (
      select w.user_id, count(*)::int as n from watchlist_items wi join watchlists w on w.id = wi.watchlist_id group by w.user_id
    )
    select u.id, u.created_at, u.mfa_enabled,
           (select count(*)::int from items i where i.user_id = u.id) as item_count,
           (select count(distinct category_slug)::int from items i where i.user_id = u.id) as categories,
           (select count(*)::int from items i where i.user_id = u.id and i.certification_number is not null and i.certification_number <> '') as certified,
           (select count(*)::int from items i where i.user_id = u.id and (i.acquired_at is null or i.purchase_price_usd is null)) as undocumented,
           (select count(*)::int from collections c where c.user_id = u.id and c.is_public) as public_collections,
           coalesce((select n from watch where watch.user_id = u.id), 0) as watched
    from users u where u.deleted_at is null
  `)) as unknown as Array<{ id: string; created_at: Date; mfa_enabled: boolean; item_count: number; categories: number; certified: number; undocumented: number; public_collections: number; watched: number }>;
  let awarded = 0;
  for (const r of rows) {
    const badges: Array<[string, Record<string, unknown>]> = [];
    if (new Date(r.created_at) < new Date('2027-09-07')) badges.push(['early_member', { joined: new Date(r.created_at).toISOString().slice(0, 10) }]);
    if (r.categories >= 10) badges.push(['ten_categories', { categories: r.categories }]);
    if (r.item_count >= 100) badges.push(['hundred_items', { items: r.item_count }]);
    if (r.certified >= 10) badges.push(['graded_collector', { certified: r.certified }]);
    if (r.item_count >= 5 && r.undocumented === 0) badges.push(['documented', { items: r.item_count }]);
    if (r.public_collections >= 1) badges.push(['public_profile', { publicCollections: r.public_collections }]);
    if (r.watched >= 25) badges.push(['watcher', { watched: r.watched }]);
    if (r.mfa_enabled) badges.push(['two_factor', {}]);
    await db.delete(userBadges).where(sql`${userBadges.userId} = ${r.id}`);
    if (badges.length) {
      await db.insert(userBadges).values(badges.map(([badge, evidence]) => ({ userId: r.id, badge, evidence })));
      awarded += badges.length;
    }
  }
  log.info({ users: rows.length, awarded }, 'badges recomputed');
  return awarded;
}