/**
 * Cron: Daily notification stats — runs at 00:00 UTC (midnight bundle).
 *
 * Aggregates live_notifications rows since the last successful run by event and
 * sends a metric.daily_summary notification to every admin user.
 *
 * Uses insertNotification + insertOutboxRow directly because this
 * handler runs via CronEnv (no full MultidealEnv); OUTBOX_QUEUE is
 * optional — if absent, the row is written but delivery is deferred to
 * the outbox backstop cron.
 */

import { createDbService } from '@/server/services/db.js';
import { sql, and, gt, lt, eq } from 'drizzle-orm';
import { withSentry } from '@/server/observability/with-sentry';
import { liveNotifications, users } from '../db/schema.js';
import { insertNotification } from '../db/queries/live-notifications.js';
import { insertOutboxRow } from '../db/queries/outbox.js';
import { advanceCursor, getCursor } from '../db/queries/cron-cursors.js';
import type { CronEnv } from './deal-expiry.js';

const JOB_KEY = 'daily-notif-stats';

export interface DailyNotifStatsEnv extends CronEnv {
  /** Optional: if present, outbox delivery is enqueued immediately. */
  OUTBOX_QUEUE?: Queue<{ outboxId: string }>;
}

export const runDailyNotifStats = withSentry(
  async function runDailyNotifStats(env: DailyNotifStatsEnv): Promise<void> {
    const db = env.db ?? createDbService({ DATABASE_URL: env.DATABASE_URL });

    const now = new Date();
    const fallback = new Date(
      Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate() - 1),
    );
    const since = await getCursor(db, JOB_KEY, fallback);

    // Aggregate notifications created since the last successful run (catch-up safe).
    const rows = await db
      .select({
        notif_event: liveNotifications.event,
        count: sql<number>`cast(count(*) as int)`,
      })
      .from(liveNotifications)
      .where(and(gt(liveNotifications.createdAt, since), lt(liveNotifications.createdAt, now)))
      .groupBy(liveNotifications.event);

    const totalCount = rows.reduce((acc, r) => acc + r.count, 0);
    const byEvent: Record<string, number> = {};
    for (const r of rows) byEvent[r.notif_event] = r.count;

    if (totalCount > 0) {
      // Fan out to all admins (cap at 500 — guards against unbounded SELECT on CF Free tier)
      const admins = await db
        .select({ id: users.id })
        .from(users)
        .where(eq(users.isAdmin, true))
        .limit(500);

      if (admins.length > 0) {
        await Promise.allSettled(
          admins.map(async (admin) => {
            const row = await insertNotification(db, {
              userId: admin.id,
              event: 'metric.daily_summary',
              tier: 'marketing',
              titleHe: `סיכום יומי — ${totalCount} התראות`,
              titleEn: `Daily summary — ${totalCount} notifications`,
              bodyHe: null,
              bodyEn: null,
              link: null,
              payload: {
                since: since.toISOString(),
                through: now.toISOString(),
                total: totalCount,
                byEvent,
              },
              expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000),
            });

            const outboxRow = await insertOutboxRow(db, {
              aggregateType: 'notification',
              aggregateId: admin.id,
              eventType: 'notification.deliver',
              payload: { notificationId: row.id, userId: admin.id },
            });

            if (env.OUTBOX_QUEUE) {
              await env.OUTBOX_QUEUE.send({ outboxId: outboxRow.id });
            }
          }),
        );
      }
    }

    await advanceCursor(db, JOB_KEY, now);
  },
  { name: 'cron.daily-notif-stats', kind: 'cron' },
);