import { executeRows, firstExecuteRow } from '../execute-rows.js';
import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import type { FraudEventsQuery } from '@/server/schemas/fraud.js';

export type FraudEventRow = {
  id: string;
  referral_id: string | null;
  user_id: string;
  decision_point: string;
  adapter: string;
  action: string;
  codes: string[] | null;
  detail: unknown;
  ts: Date | string;
  resolved_at: Date | string | null;
  resolved_by: string | null;
  amount_agorot: number;
  affiliate_name: string | null;
  enrollment_status: string | null;
  severity_order: number;
};

/**
 * Queue: fraud_events rows with joins for affiliate name, enrollment status,
 * and ₪ at stake. Sorted block→hold→flag, oldest-first within tier.
 * Scope: SIGNUP/EARN/WITHDRAW only (CLICK never lands in fraud_events).
 */
export async function listFraudEvents(
  db: DrizzleClient,
  q: FraudEventsQuery,
): Promise<FraudEventRow[]> {
  const statusFilter = q.status === 'unresolved' ? sql`fe.resolved_at IS NULL` : sql`TRUE`;

  const dpFilter = q.decision_point
    ? sql`fe.decision_point = ${q.decision_point}`
    : sql`fe.decision_point IN ('signup','earn','withdraw')`;

  const actionFilter = q.action ? sql`fe.action = ${q.action}` : sql`TRUE`;
  const adapterFilter = q.adapter ? sql`fe.adapter = ${q.adapter}` : sql`TRUE`;
  const fromFilter = q.from ? sql`fe.ts >= ${q.from}` : sql`TRUE`;
  const toFilter = q.to ? sql`fe.ts <= ${q.to}` : sql`TRUE`;

  const result = await db.execute<FraudEventRow>(sql`
    SELECT
      fe.id,
      fe.referral_id,
      fe.user_id,
      fe.decision_point,
      fe.adapter,
      fe.action,
      fe.codes,
      fe.detail,
      fe.ts,
      fe.resolved_at,
      fe.resolved_by,
      -- ₪ at stake: quarantined module ledger OR held payout
      COALESCE(
        (SELECT SUM(le.delta)
         FROM affiliate_entries aent
         INNER JOIN ledger_entries le ON le.id = aent.entry_id
         INNER JOIN ledger_entry_vesting lev ON lev.entry_id = aent.entry_id
         JOIN referrals r ON r.id = aent.referral_id
         WHERE aent.owner_id = fe.user_id::text
           AND r.quarantined_at IS NOT NULL
           AND lev.withdrawable_at IS NULL),
        (SELECT SUM(ap.amount_agorot)
         FROM affiliate_payouts ap
         WHERE ap.user_id = fe.user_id
           AND ap.status = 'requested'),
        0
      )::int AS amount_agorot,
      -- affiliate info
      u.display_name AS affiliate_name,
      ae.status AS enrollment_status,
      -- sort key: block=0, hold=1, flag=2
      CASE fe.action WHEN 'block' THEN 0 WHEN 'hold' THEN 1 ELSE 2 END AS severity_order
    FROM fraud_events fe
    LEFT JOIN affiliate_enrollments ae ON ae.user_id = fe.user_id
    LEFT JOIN users u ON u.id = fe.user_id
    WHERE ${statusFilter}
      AND ${dpFilter}
      AND ${actionFilter}
      AND ${adapterFilter}
      AND ${fromFilter}
      AND ${toFilter}
    ORDER BY severity_order ASC, fe.ts ASC
    LIMIT ${q.limit} OFFSET ${q.offset}
  `);
  return executeRows(result);
}

/** Status strip counts. */
export async function getFraudStats(db: DrizzleClient) {
  const result = await db.execute(sql`
    SELECT
      (SELECT COUNT(*)::int FROM fraud_events WHERE resolved_at IS NULL) AS items_need_review,
      COALESCE(
        (SELECT SUM(le.delta)::int
         FROM affiliate_entries ae
         INNER JOIN ledger_entries le ON le.id = ae.entry_id
         INNER JOIN ledger_entry_vesting lev ON lev.entry_id = ae.entry_id
         JOIN referrals r ON r.id = ae.referral_id
         WHERE r.quarantined_at IS NOT NULL AND lev.withdrawable_at IS NULL),
        0
      ) AS quarantined_agorot,
      (SELECT COUNT(*)::int
       FROM affiliate_payouts
       WHERE status = 'requested') AS held_payouts_count,
      -- false-positive rate: released ÷ resolved (last 30d)
      (
        SELECT CASE
          WHEN COUNT(*) FILTER (WHERE resolved_at IS NOT NULL) = 0 THEN NULL
          ELSE ROUND(
            100.0 * COUNT(*) FILTER (
              WHERE EXISTS (
                SELECT 1 FROM affiliate_admin_actions aaa
                WHERE aaa.action = 'release_fraud_hold'
                  AND (aaa.payload::jsonb)->>'fraud_event_id' = fe.id::text
              )
            ) / COUNT(*) FILTER (WHERE resolved_at IS NOT NULL),
            1
          )
        END
        FROM fraud_events fe
        WHERE fe.ts >= NOW() - INTERVAL '30 days'
      ) AS false_positive_rate_pct
  `);
  const rows = executeRows(result);
  return rows?.[0] ?? null;
}

/** Evidence drawer: identity signals from referrals join. */
export async function getFraudEventEvidence(db: DrizzleClient, eventId: string) {
  const result = await db.execute(sql`
    SELECT
      fe.id,
      fe.detail,
      fe.codes,
      fe.adapter,
      -- identity signals from referrals
      r.visitor_id_referee,
      r.ip_hash_referee,
      referee.email_canonical_index AS email_canonical_index,
      -- how many other referrals share the same ip_hash_referee
      (SELECT COUNT(*)::int FROM referrals r2
       WHERE r2.ip_hash_referee = r.ip_hash_referee
         AND r2.ip_hash_referee IS NOT NULL) AS ip_seen_count,
      -- linked events (same user or referral, last 30d)
      (SELECT json_agg(json_build_object('id', lfe.id, 'action', lfe.action, 'adapter', lfe.adapter, 'ts', lfe.ts))
       FROM fraud_events lfe
       WHERE lfe.id != fe.id
         AND (lfe.user_id = fe.user_id OR lfe.referral_id = fe.referral_id)
         AND lfe.ts >= NOW() - INTERVAL '30 days'
      ) AS linked_events,
      -- affiliate history (last 3 admin actions)
      (SELECT json_agg(json_build_object('action', aaa.action, 'ts', aaa.ts, 'reason', aaa.reason))
       FROM (SELECT * FROM affiliate_admin_actions aaa2
             WHERE aaa2.target_user_id IN (
               SELECT ae.user_id FROM affiliate_enrollments ae WHERE ae.user_id = fe.user_id
             )
             ORDER BY aaa2.ts DESC LIMIT 3) aaa
      ) AS affiliate_history
    FROM fraud_events fe
    LEFT JOIN referrals r ON r.id = fe.referral_id
    LEFT JOIN users referee ON referee.id = r.referee_user_id
    WHERE fe.id = ${eventId}
  `);
  const rows = executeRows(result);
  return rows?.[0] ?? null;
}

/** Analytics widget: stacked daily bars by action, 30d. */
export async function getFraudEventsOverTime(
  db: DrizzleClient,
): Promise<{ day: string; action: string; count: number }[]> {
  const result = await db.execute(sql`
    SELECT
      DATE_TRUNC('day', ts)::date AS day,
      action,
      COUNT(*)::int AS count
    FROM fraud_events
    WHERE ts >= NOW() - INTERVAL '30 days'
    GROUP BY 1, 2
    ORDER BY 1 ASC, 2 ASC
  `);
  return executeRows<{ day: string; action: string; count: number }>(result);
}

/** Analytics widget: false-positive rate per adapter, 30d. */
export async function getFalsePositiveRate(
  db: DrizzleClient,
): Promise<
  { adapter: string; resolved_count: number; released_count: number; fp_rate_pct: number | null }[]
> {
  const result = await db.execute(sql`
    SELECT
      fe.adapter,
      COUNT(*) FILTER (WHERE fe.resolved_at IS NOT NULL)::int AS resolved_count,
      COUNT(*) FILTER (
        WHERE EXISTS (
          SELECT 1 FROM affiliate_admin_actions aaa
          WHERE aaa.action = 'release_fraud_hold'
            AND (aaa.payload::jsonb)->>'fraud_event_id' = fe.id::text
        )
      )::int AS released_count,
      CASE
        WHEN COUNT(*) FILTER (WHERE fe.resolved_at IS NOT NULL) = 0 THEN NULL
        ELSE ROUND(
          100.0 * COUNT(*) FILTER (
            WHERE EXISTS (
              SELECT 1 FROM affiliate_admin_actions aaa
              WHERE aaa.action = 'release_fraud_hold'
                AND (aaa.payload::jsonb)->>'fraud_event_id' = fe.id::text
            )
          ) / COUNT(*) FILTER (WHERE fe.resolved_at IS NOT NULL),
          1
        )
      END AS fp_rate_pct
    FROM fraud_events fe
    WHERE fe.ts >= NOW() - INTERVAL '30 days'
    GROUP BY fe.adapter
    ORDER BY resolved_count DESC
  `);
  return executeRows<{
    adapter: string;
    resolved_count: number;
    released_count: number;
    fp_rate_pct: number | null;
  }>(result);
}

/** Analytics widget: top-firing adapters by action in the last 30 days. */
export async function getTopFiringAdapters(
  db: DrizzleClient,
): Promise<{ adapter: string; action: string; count: number }[]> {
  const result = await db.execute(sql`
    SELECT adapter, action, COUNT(*)::int AS count
    FROM fraud_events
    WHERE ts >= NOW() - INTERVAL '30 days'
    GROUP BY adapter, action
    ORDER BY count DESC
    LIMIT 20
  `);
  const rows = executeRows<{ adapter: string; action: string; count: number }>(result);
  return rows;
}

/** Analytics widget: quarantined credit + recovered clawback amounts. */
export async function getMoneyProtected(
  db: DrizzleClient,
): Promise<{ quarantined_agorot: number; recovered_90d_agorot: number }> {
  const result = await db.execute(sql`
    SELECT
      COALESCE(
        (SELECT SUM(le.delta)::int
         FROM affiliate_entries ae
         INNER JOIN ledger_entries le ON le.id = ae.entry_id
         INNER JOIN ledger_entry_vesting lev ON lev.entry_id = ae.entry_id
         JOIN referrals r ON r.id = ae.referral_id
         WHERE r.quarantined_at IS NOT NULL AND lev.withdrawable_at IS NULL),
        0
      ) AS quarantined_agorot,
      COALESCE(
        (SELECT SUM(ABS(le.delta))::int
         FROM affiliate_entries ae
         INNER JOIN ledger_entries le ON le.id = ae.entry_id
         WHERE ae.entry_type = 'refund_clawback'
           AND le.created_at >= NOW() - INTERVAL '90 days'),
        0
      ) AS recovered_90d_agorot
  `);
  const row = firstExecuteRow<{
    quarantined_agorot: number;
    recovered_90d_agorot: number;
  }>(result);
  return (
    row ?? {
      quarantined_agorot: 0,
      recovered_90d_agorot: 0,
    }
  );
}