/**
 * Affiliate auto-suspend pass — M.11 rule.
 *
 * Affiliates with >10 SEARCH_ENGINE_BRAND_BID flags in the last 7 days are
 * automatically suspended with reason='auto.brand_keyword_velocity'.
 * Called every 6 hours via tick endpoint (HOUR % 6 === 0 && MINUTE === 0).
 *
 * The pass:
 *   1. Queries affiliate_admin_actions for SEARCH_ENGINE_BRAND_BID auto_flag events
 *      in the last 7 days, grouped by enrollment.
 *   2. Any enrollment with >10 such flags is suspended via UPDATE.
 *   3. An audit record is written to affiliate_admin_actions for each suspension.
 */

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

export type AutoSuspendResult = { suspended: number; checkedAt: Date };

/**
 * Run the auto-suspend pass.
 *
 * @param db - Drizzle client.
 * @returns Number of enrollments suspended in this pass.
 */
export async function runAutoSuspend(db: DrizzleClient): Promise<AutoSuspendResult> {
  const now = new Date();
  const sevenDaysAgo = new Date(now.getTime() - 7 * 86400_000);

  // Find active enrollments with >10 SEARCH_ENGINE_BRAND_BID flags in last 7 days.
  const overThresholdRows = (await db.execute(sql`
    SELECT ae.user_id, ae.id AS enrollment_id, COUNT(aaa.id) AS flag_count
    FROM affiliate_enrollments ae
    JOIN affiliate_admin_actions aaa
      ON aaa.target_user_id = ae.user_id
      AND aaa.action = 'auto_flag'
      AND aaa.payload->>'code' = 'SEARCH_ENGINE_BRAND_BID'
      AND aaa.ts >= ${sevenDaysAgo.toISOString()}
    WHERE ae.status = 'active'
    GROUP BY ae.user_id, ae.id
    HAVING COUNT(aaa.id) > 10
  `)) as {
    rows: Array<{ user_id: string; enrollment_id: string; flag_count: string | number }>;
  };

  const toSuspend = overThresholdRows.rows as Array<{
    user_id: string;
    enrollment_id: string;
    flag_count: string | number;
  }>;

  if (toSuspend.length === 0) return { suspended: 0, checkedAt: now };

  // Suspend all over-threshold enrollments — one UPDATE per enrollment to avoid
  // ANY(array) binding incompatibilities across drivers (PGLite, Neon serverless).
  for (const row of toSuspend) {
    await db.execute(sql`
      UPDATE affiliate_enrollments
      SET status = 'suspended',
          suspended_at = ${now.toISOString()},
          suspended_reason = 'auto.brand_keyword_velocity'
      WHERE id = ${row.enrollment_id}
        AND status = 'active'
    `);
  }

  // Audit trail — one record per suspended enrollment.
  for (const row of toSuspend) {
    await db.execute(sql`
      INSERT INTO affiliate_admin_actions (
        admin_user_id,
        target_user_id,
        action,
        payload,
        reason
      ) VALUES (
        NULL,
        ${row.user_id},
        'suspend',
        ${JSON.stringify({
          automated: true,
          rule: 'auto.brand_keyword_velocity',
          flag_count: Number(row.flag_count),
          window_days: 7,
          threshold: 10,
        })},
        'auto.brand_keyword_velocity'
      )
    `);
  }

  return { suspended: toSuspend.length, checkedAt: now };
}