INSERT INTO ledger_entries (id, delta, reason, ref, idempotency_key, created_at)
SELECT credit_ledger.id, credit_ledger.amount_agorot, credit_ledger.entry_type::text,
  jsonb_strip_nulls(jsonb_build_object('sourceType', credit_ledger.source_type, 'sourceId', credit_ledger.source_id, 'userId', credit_ledger.user_id::text, 'memo', credit_ledger.memo, 'referralId', credit_ledger.referral_id::text)),
  concat_ws(':', credit_ledger.entry_type::text, credit_ledger.source_type, credit_ledger.source_id), credit_ledger.created_at
FROM affiliate_payouts INNER JOIN credit_ledger ON credit_ledger.id = affiliate_payouts.ledger_entry_id
WHERE affiliate_payouts.ledger_entry_id IS NOT NULL ON CONFLICT (id) DO NOTHING;
--> statement-breakpoint
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM affiliate_payouts LEFT JOIN ledger_entries ON ledger_entries.id = affiliate_payouts.ledger_entry_id WHERE affiliate_payouts.ledger_entry_id IS NOT NULL AND ledger_entries.id IS NULL) THEN
    RAISE EXCEPTION 'affiliate payout ledger backfill incomplete';
  END IF;
END $$;
--> statement-breakpoint
ALTER TABLE affiliate_payouts DROP CONSTRAINT IF EXISTS affiliate_payouts_ledger_entry_id_fkey;
--> statement-breakpoint
ALTER TABLE affiliate_payouts ADD CONSTRAINT affiliate_payouts_ledger_entry_id_fkey FOREIGN KEY (ledger_entry_id) REFERENCES ledger_entries(id) NOT VALID;
--> statement-breakpoint
ALTER TABLE affiliate_payouts VALIDATE CONSTRAINT affiliate_payouts_ledger_entry_id_fkey;
