import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client';
import { insertFraudEvent } from '@/server/db/queries/fraud-events.js';

export interface GraphScanResult {
  ringsFound: number;
  eventsInserted: number;
}

type CycleRow = {
  start_user: string;
  cycle_users: string[];
  cycle_referral_ids: string[];
};

type Ring = {
  canonicalUserId: string;
  referralIds: string[];
  cycleUsers: string[];
};

function dedupeRings(rows: CycleRow[]): Ring[] {
  const seenRings = new Set<string>();
  const rings: Ring[] = [];

  for (const row of rows) {
    const uniqueUsers = [...new Set(row.cycle_users)].sort();
    const ringKey = uniqueUsers.join(',');
    if (seenRings.has(ringKey)) continue;
    seenRings.add(ringKey);

    rings.push({
      canonicalUserId: uniqueUsers[0]!,
      referralIds: [...new Set(row.cycle_referral_ids)].sort(),
      cycleUsers: uniqueUsers,
    });
  }

  return rings;
}

/**
 * Scan the referral graph for referral rings (cycles) up to depth 5.
 *
 * Walks referrer → referee edges; a cycle (A → B → … → A) is a strong signal of
 * a self-referral ring. Flagged referrals are quarantined (withdrawable sweep
 * excludes them) and a fraud_events row is inserted for the canonical user
 * (lowest UUID in the ring, deduplicated within a 24-hour window).
 *
 * Designed to run once daily via the affiliate-tick cron at 03:00 UTC.
 */
export async function runReferralGraphScan(db: DrizzleClient): Promise<GraphScanResult> {
  const cyclesResult = (await db.execute(sql`
    WITH RECURSIVE walk(
      start_user,
      cur_referrer,
      depth,
      path_users,
      path_referral_ids
    ) AS (
      SELECT
        r.referrer_user_id,
        r.referee_user_id,
        1,
        ARRAY[r.referrer_user_id, r.referee_user_id]::uuid[],
        ARRAY[r.id]::uuid[]
      FROM referrals r
      WHERE r.referrer_user_id <> r.referee_user_id

      UNION ALL

      SELECT
        w.start_user,
        r.referee_user_id,
        w.depth + 1,
        w.path_users || r.referee_user_id,
        w.path_referral_ids || r.id
      FROM walk w
      JOIN referrals r ON r.referrer_user_id = w.cur_referrer
      WHERE w.depth < 5
        AND r.referee_user_id <> w.start_user
        AND NOT (r.referee_user_id = ANY(w.path_users))
    ),
    cycles AS (
      SELECT
        w.start_user,
        w.path_users || w.start_user AS cycle_users,
        w.path_referral_ids || r.id AS cycle_referral_ids
      FROM walk w
      JOIN referrals r ON r.referrer_user_id = w.cur_referrer
      WHERE r.referee_user_id = w.start_user
        AND w.depth >= 1
    )
    SELECT
      start_user,
      cycle_users,
      cycle_referral_ids
    FROM cycles
  `)) as { rows: CycleRow[] };

  const rings = dedupeRings(cyclesResult.rows as CycleRow[]);

  const referralIds = new Set<string>();
  for (const ring of rings) {
    for (const id of ring.referralIds) referralIds.add(id);
  }

  // One UPDATE per referral — avoids ANY(array) binding issues across drivers.
  for (const referralId of referralIds) {
    await db.execute(sql`
      UPDATE referrals
      SET quarantined_at = NOW()
      WHERE id = ${referralId}
        AND quarantined_at IS NULL
    `);
  }

  let eventsInserted = 0;
  for (const ring of rings) {
    const existing = (await db.execute(sql`
      SELECT 1 FROM fraud_events
      WHERE user_id = ${ring.canonicalUserId} AND adapter = 'referral-graph'
        AND ts >= NOW() - INTERVAL '24 hours' LIMIT 1
    `)) as { rows: unknown[] };
    if ((existing.rows as unknown[]).length > 0) continue;

    const insertedId = await insertFraudEvent(db, {
      userId: ring.canonicalUserId,
      referralId: ring.referralIds[0],
      decisionPoint: 'EARN',
      signal: {
        adapter: 'referral-graph',
        action: 'flag',
        codes: ['REFERRAL_CYCLE_DETECTED'],
        detail: { users: ring.cycleUsers, referralIds: ring.referralIds },
      },
    });
    if (insertedId) eventsInserted++;
  }

  return { ringsFound: rings.length, eventsInserted };
}