import { and, eq, inArray, lt, sql } from 'drizzle-orm';
import type { Database } from '@rareindex/database';
import { users, sessions, trustedDevices, authCodes, rateLimits, collections, collectionItems, watchlists, watchlistItems, alerts, alertEvents, notifications, savedSearches, priceTargets, recoveryCodes, loginEvents, apiKeys, userBadges, uploads, collectionSnapshots } from '@rareindex/database';
import { logger } from '@rareindex/shared';

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

/** Purge accounts past their grace period, expire codes/sessions, trim rate-limit rows. */
export async function runMaintenance(db: Database, now = new Date()): Promise<{ purged: number }> {
  const due = await db.select({ id: users.id }).from(users).where(and(sql`${users.deletedAt} is not null`, lt(users.purgeAfter, now)));
  for (const u of due) {
    const cols = await db.select({ id: collections.id }).from(collections).where(eq(collections.userId, u.id));
    const colIds = cols.map((c) => c.id);
    if (colIds.length) {
      await db.delete(collectionItems).where(inArray(collectionItems.collectionId, colIds));
      await db.delete(collectionSnapshots).where(inArray(collectionSnapshots.collectionId, colIds));
      await db.delete(collections).where(inArray(collections.id, colIds));
    }
    const wls = await db.select({ id: watchlists.id }).from(watchlists).where(eq(watchlists.userId, u.id));
    if (wls.length) {
      await db.delete(watchlistItems).where(inArray(watchlistItems.watchlistId, wls.map((w) => w.id)));
      await db.delete(watchlists).where(eq(watchlists.userId, u.id));
    }
    for (const t of [alertEvents, alerts, notifications, savedSearches, priceTargets, recoveryCodes, trustedDevices, sessions, loginEvents, apiKeys, userBadges, uploads]) {
      await db.delete(t).where(eq((t as unknown as { userId: typeof users.id }).userId, u.id));
    }
    await db.delete(authCodes).where(eq(authCodes.userId, u.id));
    await db.delete(users).where(eq(users.id, u.id));
    log.info({ userId: u.id }, 'account purged after grace period');
  }
  await db.delete(authCodes).where(lt(authCodes.expiresAt, new Date(now.getTime() - 7 * 86_400_000)));
  await db.delete(sessions).where(lt(sessions.expiresAt, new Date(now.getTime() - 30 * 86_400_000)));
  await db.delete(rateLimits).where(lt(rateLimits.resetAt, new Date(now.getTime() - 86_400_000)));
  return { purged: due.length };
}