/**
 * W2 — EXPAND-state additive backfill: donor inline columns → module companion tables.
 * Reads retained donor columns via raw SQL; writes module tables via drizzle pgTable objects.
 */
import { sql } from 'drizzle-orm';
import type { TransactionalDatabase, Transaction } from '@platform-modules/db';
import { ledgerEntries } from '@platform-modules/ledger';
import { walletVesting, ledgerEntryVesting } from '@platform-modules/ledger/vesting';
import { affiliateEntriesTable } from '@platform-modules/affiliate/schema';
import type * as hostSchema from '@/server/db/schema';
import { buildIdempotencyKey } from './host-retained';
import { asReferralId, asUserId, toModuleRef } from '@/server/platform-seams/ids.js';

export class BackfillRequiresTransactionalDatabaseError extends Error {
  readonly code = 'BACKFILL_REQUIRES_TRANSACTIONAL_DATABASE' as const;
  readonly _backfillError = true as const;

  constructor(message = 'backfill requires a TransactionalDatabase') {
    super(message);
    this.name = 'BackfillRequiresTransactionalDatabaseError';
  }
}

export class BackfillUnresolvableOwnerError extends Error {
  readonly code = 'BACKFILL_UNRESOLVABLE_OWNER' as const;
  readonly _backfillError = true as const;

  constructor(message: string) {
    super(message);
    this.name = 'BackfillUnresolvableOwnerError';
  }
}

export function isBackfillUnresolvableOwnerError(e: unknown): e is BackfillUnresolvableOwnerError {
  return (
    typeof e === 'object' &&
    e !== null &&
    '_backfillError' in e &&
    (e as BackfillUnresolvableOwnerError)._backfillError === true &&
    (e as { code?: unknown }).code === 'BACKFILL_UNRESOLVABLE_OWNER'
  );
}

/** Coerce donor number / bigint / string amounts to bigint for module writes. */
export function normMinor(v: bigint | number | string | null | undefined): bigint {
  if (v === null || v === undefined) return 0n;
  if (typeof v === 'bigint') return v;
  return BigInt(v);
}

function nonNegativeMinor(v: bigint | number | string | null | undefined): bigint {
  const minor = normMinor(v);
  return minor < 0n ? 0n : minor;
}

const AFFILIATE_FIVE = [
  'affiliate_commission',
  'referral_reward',
  'refund_clawback',
  'adjustment',
  'redemption',
] as const;

const EARN_TYPES = ['affiliate_commission', 'referral_reward'] as const;

type DonorWalletRow = {
  user_id: string;
  pending_agorot: number | string | bigint;
  matured_agorot: number | string | bigint;
  withdrawable_agorot: number | string | bigint;
  lifetime_earned_agorot: number | string | bigint;
};

type DonorCoreRow = {
  id: string;
  amount_agorot: number | string | bigint;
  entry_type: (typeof AFFILIATE_FIVE)[number];
  source_type: string;
  source_id: string;
  user_id: string;
  referral_id: string | null;
  memo: string | null;
  created_at: string | Date;
};

type DonorEarnRow = {
  id: string;
  mature_at: string | Date;
  swept_at: string | Date | null;
  withdrawable_at: string | Date | null;
};

type DonorAffiliateRow = {
  id: string;
  user_id: string | null;
  entry_type: (typeof AFFILIATE_FIVE)[number];
  source_type: string;
  source_id: string;
  referral_id: string | null;
  memo: string | null;
  resolved_pct: number | null;
  created_at: string | Date;
  swept_at: string | Date | null;
};

type HostTransaction = Transaction<typeof hostSchema>;

function resolveOwnerId(userId: string | null | undefined, entryId: string): string {
  if (userId === null || userId === undefined || userId === '') {
    throw new BackfillUnresolvableOwnerError(`Cannot resolve owner for ledger entry ${entryId}`);
  }
  return String(userId);
}

function toDate(v: string | Date | null | undefined): Date | null {
  if (v === null || v === undefined) return null;
  return v instanceof Date ? v : new Date(v);
}

function assertTransactionalDatabase(
  db: TransactionalDatabase<typeof hostSchema>,
): asserts db is TransactionalDatabase<typeof hostSchema> {
  if (typeof (db as { transaction?: unknown }).transaction !== 'function') {
    throw new BackfillRequiresTransactionalDatabaseError();
  }
}

/**
 * Backfill wallet_vesting, ledger_entries (core), ledger_entry_vesting (earn-only), and
 * affiliate_entries from EXPAND-state donor tables. Does NOT write wallet_balances.
 * Self-opens one atomic tx.
 */
export async function backfill(db: TransactionalDatabase<typeof hostSchema>): Promise<void> {
  assertTransactionalDatabase(db);
  await db.transaction(async (tx) => {
    await backfillInTx(tx);
  });
}

async function backfillInTx(tx: HostTransaction): Promise<void> {
  const walletRows = (await tx.execute(sql`
    SELECT
      user_id,
      pending_agorot,
      matured_agorot,
      withdrawable_agorot,
      lifetime_earned_agorot
    FROM wallet_balances
  `)) as { rows: unknown[] };

  for (const row of walletRows.rows as DonorWalletRow[]) {
    const legacyMaturedMinor = normMinor(row.matured_agorot);
    await tx
      .insert(walletVesting)
      .values({
        ownerId: toModuleRef(asUserId(String(row.user_id))),
        pendingMinor: nonNegativeMinor(row.pending_agorot),
        maturedMinor: nonNegativeMinor(legacyMaturedMinor),
        carriedDebtMinor: legacyMaturedMinor < 0n ? -legacyMaturedMinor : 0n,
        withdrawableMinor: nonNegativeMinor(row.withdrawable_agorot),
        lifetimeEarnedMinor: nonNegativeMinor(row.lifetime_earned_agorot),
      })
      .onConflictDoNothing({ target: walletVesting.ownerId });
  }

  const coreRows = (await tx.execute(sql`
    SELECT
      id,
      amount_agorot,
      entry_type,
      source_type,
      source_id,
      user_id,
      referral_id,
      memo,
      created_at
    FROM credit_ledger
    WHERE entry_type IN (${sql.join(
      AFFILIATE_FIVE.map((t) => sql`${t}`),
      sql`, `,
    )})
  `)) as { rows: unknown[] };

  for (const row of coreRows.rows as DonorCoreRow[]) {
    await tx
      .insert(ledgerEntries)
      .values({
        id: row.id,
        delta: normMinor(row.amount_agorot),
        reason: row.entry_type,
        ref: {
          sourceType: row.source_type,
          sourceId: row.source_id,
          userId: toModuleRef(asUserId(row.user_id)),
          ...(row.memo ? { memo: row.memo } : {}),
          ...(row.referral_id ? { referralId: toModuleRef(asReferralId(row.referral_id)) } : {}),
        },
        idempotencyKey: buildIdempotencyKey(row.entry_type, row.source_type, row.source_id),
        createdAt: toDate(row.created_at)!,
      })
      .onConflictDoNothing({ target: ledgerEntries.id });
  }

  const earnRows = (await tx.execute(sql`
    SELECT id, mature_at, swept_at, withdrawable_at
    FROM credit_ledger
    WHERE mature_at IS NOT NULL
      AND entry_type IN (${sql.join(
        EARN_TYPES.map((t) => sql`${t}`),
        sql`, `,
      )})
  `)) as { rows: unknown[] };

  for (const row of earnRows.rows as DonorEarnRow[]) {
    await tx
      .insert(ledgerEntryVesting)
      .values({
        entryId: row.id,
        matureAt: toDate(row.mature_at),
        sweptAt: toDate(row.swept_at),
        withdrawableAt: toDate(row.withdrawable_at),
      })
      .onConflictDoNothing({ target: ledgerEntryVesting.entryId });
  }

  const affiliateRows = (await tx.execute(sql`
    SELECT
      id,
      user_id,
      entry_type,
      source_type,
      source_id,
      referral_id,
      memo,
      resolved_pct,
      created_at,
      swept_at
    FROM credit_ledger
    WHERE entry_type IN (${sql.join(
      AFFILIATE_FIVE.map((t) => sql`${t}`),
      sql`, `,
    )})
  `)) as { rows: unknown[] };

  for (const row of affiliateRows.rows as DonorAffiliateRow[]) {
    const ownerId = toModuleRef(asUserId(resolveOwnerId(row.user_id, row.id)));
    const consumedAt = row.entry_type === 'refund_clawback' ? toDate(row.swept_at) : null;

    await tx
      .insert(affiliateEntriesTable)
      .values({
        entryId: row.id,
        ownerId,
        entryType: row.entry_type,
        sourceType: row.source_type,
        sourceId: row.source_id,
        referralId: row.referral_id === null ? null : toModuleRef(asReferralId(row.referral_id)),
        resolvedPct: row.resolved_pct,
        memo: row.memo,
        consumedAt,
      })
      .onConflictDoNothing({ target: affiliateEntriesTable.entryId });
  }
}