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

type NegativeWalletQueryRow = Record<string, unknown> & {
  enrollment_id: string;
  user_id: string;
  matured_minor: bigint | number | string;
  carried_debt_minor: bigint | number | string;
  debt_started_at: Date | string | null;
  uncollectable: boolean;
};

export interface NegativeAffiliateWallet {
  enrollmentId: string;
  userId: string;
  netMaturedAgorot: number;
  debtAgorot: number;
  debtStartedAt: Date | null;
  debtAgeDays: number | null;
  uncollectable: boolean;
}

function toSafeNumber(value: bigint | number | string): number {
  const numberValue = Number(value);
  if (!Number.isSafeInteger(numberValue)) {
    throw new Error('NEGATIVE_WALLET_AMOUNT_OUT_OF_RANGE');
  }
  return numberValue;
}

function toDebtAgeDays(debtStartedAt: Date | string | null, now: Date): number | null {
  if (!debtStartedAt) return null;
  const startedAt = debtStartedAt instanceof Date ? debtStartedAt : new Date(debtStartedAt);
  if (!Number.isFinite(startedAt.getTime())) {
    throw new Error('NEGATIVE_WALLET_INVALID_DEBT_DATE');
  }
  return Math.max(0, Math.floor((now.getTime() - startedAt.getTime()) / 86_400_000));
}

/** Returns every enrolled wallet with carried debt, preserving signed net and debt separately. */
export async function getNegativeAffiliateWallets(
  db: DrizzleClient,
  now = new Date(),
): Promise<NegativeAffiliateWallet[]> {
  const result = (await db.execute(sql`
    WITH matured_events AS (
      SELECT
        ae.owner_id,
        ae.entry_id,
        le.delta,
        CASE
          WHEN ae.entry_type IN ('affiliate_commission', 'referral_reward')
            THEN lev.swept_at
          WHEN ae.entry_type = 'refund_clawback'
            THEN ae.consumed_at
          ELSE le.created_at
        END AS event_at
      FROM affiliate_entries ae
      INNER JOIN ledger_entries le ON le.id = ae.entry_id
      LEFT JOIN ledger_entry_vesting lev ON lev.entry_id = ae.entry_id
      WHERE (
        ae.entry_type IN ('affiliate_commission', 'referral_reward')
        AND lev.swept_at IS NOT NULL
      )
      OR (
        ae.entry_type = 'refund_clawback'
        AND ae.consumed_at IS NOT NULL
      )
      OR ae.entry_type = 'redemption'
      OR ae.entry_type = 'adjustment'
    ),
    running_matured AS (
      SELECT
        owner_id,
        event_at,
        entry_id,
        SUM(delta) OVER (
          PARTITION BY owner_id
          ORDER BY event_at, entry_id
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_minor
      FROM matured_events
      WHERE event_at IS NOT NULL
    ),
    debt_start AS (
      SELECT owner_id, MIN(event_at) FILTER (WHERE running_minor < 0) AS debt_started_at
      FROM running_matured
      GROUP BY owner_id
    ),
    payout_redemptions AS (
      SELECT DISTINCT ae.owner_id
      FROM affiliate_entries ae
      INNER JOIN affiliate_payouts ap ON ap.id::text = ae.source_id
      WHERE ae.entry_type = 'redemption'
        AND ae.source_type = 'affiliate_payout'
        AND ap.status = 'paid'
    )
    SELECT
      ae.id AS enrollment_id,
      ae.user_id,
      w.matured_minor,
      w.carried_debt_minor,
      ds.debt_started_at,
      (pr.owner_id IS NOT NULL) AS uncollectable
    FROM affiliate_enrollments ae
    INNER JOIN wallet_vesting w ON w.owner_id = ae.user_id::text
    LEFT JOIN debt_start ds ON ds.owner_id = w.owner_id
    LEFT JOIN payout_redemptions pr ON pr.owner_id = w.owner_id
    WHERE w.carried_debt_minor > 0
    ORDER BY ds.debt_started_at ASC NULLS LAST, ae.id ASC
  `)) as { rows: NegativeWalletQueryRow[] };

  return result.rows.map((row) => {
    const maturedMinor = BigInt(row.matured_minor);
    const carriedDebtMinor = BigInt(row.carried_debt_minor);
    const netMaturedAgorot = toSafeNumber(maturedMinor - carriedDebtMinor);
    const debtStartedAt = row.debt_started_at
      ? row.debt_started_at instanceof Date
        ? row.debt_started_at
        : new Date(row.debt_started_at)
      : null;

    return {
      enrollmentId: String(row.enrollment_id),
      userId: String(row.user_id),
      netMaturedAgorot,
      debtAgorot: toSafeNumber(carriedDebtMinor),
      debtStartedAt,
      debtAgeDays: toDebtAgeDays(debtStartedAt, now),
      uncollectable: Boolean(row.uncollectable),
    };
  });
}