import mongoose, { type ClientSession } from "mongoose";
import { Booking, LedgerEntry, Refund, WithdrawalRequest } from "@/db/models";
import {
  isBookingsMysql,
  isLedgerMysql,
  isMysqlActive,
  isRefundsMysql,
  isWithdrawalsMysql,
} from "@/db/activeDatabase";
import type { RefundReasonCode, RefundType } from "@/db/types";
import { AppError } from "@/lib/errors";
import { toNumber } from "@/lib/serialize";
import { getCommerceSettings } from "@/services/commerce";
import { creditWallet } from "@/services/wallet";
import { withFinancialDualWrite, isFinancialDualWriteEnabled } from "@/db/dualWriteFinancial";
import {
  deleteLedgerEntryMysql,
  deleteRefundMysql,
  insertLedgerEntryMysql,
  insertRefundMysql,
  upsertBookingMysql,
} from "@/db/mysql/financialWrites";

function roundMoney(value: number) {
  return Math.round(value * 100) / 100;
}

function bookingRate(booking: { exchangeRateRate?: number }) {
  const rate = toNumber(booking.exchangeRateRate);
  return rate > 0 ? rate : 1;
}

function lydToTnd(amountLyd: number, rate: number) {
  return roundMoney(amountLyd / rate);
}

function tndToLyd(amountTnd: number, rate: number) {
  return roundMoney(amountTnd * rate);
}

export async function syncBookingToMysql(booking: {
  _id: unknown;
  propertyId: string;
  customerId: string;
  ownerId: string;
  checkIn: Date;
  checkOut: Date;
  guests: number;
  nights: number;
  status: string;
  exchangeFromCurrency?: string;
  exchangeToCurrency?: string;
  exchangeRateRate: number;
  exchangeRateLockedAt: Date;
  exchangeRateExpiresAt: Date;
  subtotalTnd: number;
  cleaningFeeTnd: number;
  platformFeeTnd: number;
  taxesTnd: number;
  discountTnd?: number;
  totalTnd: number;
  totalLyd: number;
  ownerPayoutTnd: number;
  couponCode?: string | null;
  walletPaidLyd?: number;
  pointsEarned?: number;
  pointsRedeemed?: number;
  pointsDiscountTnd?: number;
  refundLyd?: number;
  refundPercent?: number;
  cancelledBy?: string | null;
  cancelledAt?: Date | null;
  deletedAt?: Date | null;
  payment?: Record<string, unknown> | null;
  invoice?: Record<string, unknown> | null;
  createdAt?: Date;
  updatedAt?: Date;
}) {
  await upsertBookingMysql({
    id: String(booking._id),
    propertyId: String(booking.propertyId),
    customerId: String(booking.customerId),
    ownerId: String(booking.ownerId),
    checkIn: new Date(booking.checkIn),
    checkOut: new Date(booking.checkOut),
    guests: toNumber(booking.guests),
    nights: toNumber(booking.nights),
    status: String(booking.status),
    exchangeFromCurrency: booking.exchangeFromCurrency,
    exchangeToCurrency: booking.exchangeToCurrency,
    exchangeRateRate: toNumber(booking.exchangeRateRate),
    exchangeRateLockedAt: new Date(booking.exchangeRateLockedAt),
    exchangeRateExpiresAt: new Date(booking.exchangeRateExpiresAt),
    subtotalTnd: toNumber(booking.subtotalTnd),
    cleaningFeeTnd: toNumber(booking.cleaningFeeTnd),
    platformFeeTnd: toNumber(booking.platformFeeTnd),
    taxesTnd: toNumber(booking.taxesTnd),
    discountTnd: toNumber(booking.discountTnd),
    totalTnd: toNumber(booking.totalTnd),
    totalLyd: toNumber(booking.totalLyd),
    ownerPayoutTnd: toNumber(booking.ownerPayoutTnd),
    couponCode: booking.couponCode,
    walletPaidLyd: toNumber(booking.walletPaidLyd),
    pointsEarned: toNumber(booking.pointsEarned),
    pointsRedeemed: toNumber(booking.pointsRedeemed),
    pointsDiscountTnd: toNumber(booking.pointsDiscountTnd),
    refundLyd: toNumber(booking.refundLyd),
    refundPercent: toNumber(booking.refundPercent),
    cancelledBy: booking.cancelledBy,
    cancelledAt: booking.cancelledAt ? new Date(booking.cancelledAt) : null,
    deletedAt: booking.deletedAt ? new Date(booking.deletedAt) : null,
    payment: (booking.payment as any) || null,
    invoice: (booking.invoice as any) || null,
    createdAt: booking.createdAt,
    updatedAt: booking.updatedAt,
  });
}

/** Run work in a Mongo transaction when supported; otherwise sequential fallback. */
export async function withDbTransaction<T>(fn: (session: ClientSession | null) => Promise<T>): Promise<T> {
  // MySQL-primary mode has no mongoose connection — never startSession (10s buffer hang).
  if (isMysqlActive()) {
    return fn(null);
  }

  let session: ClientSession | null = null;
  try {
    session = await mongoose.startSession();
    let result!: T;
    await session.withTransaction(async () => {
      result = await fn(session);
    });
    return result;
  } catch (err) {
    const msg = err instanceof Error ? err.message : String(err);
    const unsupported = /transaction|replica set|not supported|IllegalOperation|TxnNumber/i.test(msg);
    if (unsupported) {
      console.warn("[db] transactions unsupported — falling back to sequential writes:", msg);
      return fn(null);
    }
    throw err;
  } finally {
    if (session) {
      try {
        session.endSession();
      } catch {
        /* ignore */
      }
    }
  }
}

export async function postBookingPaymentLedger(booking: {
  _id: string;
  customerId: string;
  totalLyd: number;
  totalTnd: number;
  platformFeeTnd: number;
  ownerPayoutTnd: number;
  exchangeRateRate?: number;
}) {
  const bookingId = String(booking._id);
  const { isLedgerMysql } = await import("@/db/activeDatabase");
  const meta = {
    platformFeeTnd: toNumber(booking.platformFeeTnd),
    ownerPayoutTnd: toNumber(booking.ownerPayoutTnd),
    platformFeeLyd: tndToLyd(toNumber(booking.platformFeeTnd), bookingRate(booking)),
  };
  const amountLyd = roundMoney(toNumber(booking.totalLyd));
  const amountTnd = roundMoney(toNumber(booking.totalTnd));

  if (isLedgerMysql() && !isFinancialDualWriteEnabled()) {
    const { listLedgerEntriesMysql } = await import("@/db/mysql/ledger");
    const { createId } = await import("@/db/ids");
    const existingRows = await listLedgerEntriesMysql({
      bookingId,
      type: "BOOKING_PAYMENT",
      status: "POSTED",
      take: 1,
    });
    if (existingRows[0]) return { _id: existingRows[0].id, ...existingRows[0] } as any;
    const id = createId();
    const now = new Date();
    await insertLedgerEntryMysql({
      id,
      type: "BOOKING_PAYMENT",
      direction: "IN",
      bookingId,
      amountLyd,
      amountTnd,
      partyUserId: booking.customerId,
      partyRole: "CUSTOMER",
      status: "POSTED",
      meta,
      createdAt: now,
      updatedAt: now,
    });
    return { _id: id, id, type: "BOOKING_PAYMENT", bookingId, amountLyd, amountTnd, status: "POSTED", meta };
  }

  const existing = await LedgerEntry.findOne({
    bookingId,
    type: "BOOKING_PAYMENT",
    status: { $ne: "VOID" },
  }).lean();
  if (existing) return existing;

  try {
    return await withFinancialDualWrite({
      site: "ledger.booking_payment",
      mongoWrite: async () => {
        return await LedgerEntry.create({
          type: "BOOKING_PAYMENT",
          direction: "IN",
          bookingId,
          amountLyd,
          amountTnd,
          partyUserId: booking.customerId,
          partyRole: "CUSTOMER",
          status: "POSTED",
          meta,
        });
      },
      mysqlWrite: async (doc) => {
        await insertLedgerEntryMysql({
          id: String(doc._id),
          type: "BOOKING_PAYMENT",
          direction: "IN",
          bookingId,
          amountLyd,
          amountTnd,
          partyUserId: booking.customerId,
          partyRole: "CUSTOMER",
          status: "POSTED",
          meta,
          createdAt: (doc as any).createdAt,
          updatedAt: (doc as any).updatedAt,
        });
      },
      mongoCompensate: async (doc) => {
        await LedgerEntry.deleteOne({ _id: doc._id });
      },
    });
  } catch (err: any) {
    if (err?.code === 11000) {
      return LedgerEntry.findOne({
        bookingId,
        type: "BOOKING_PAYMENT",
        status: { $ne: "VOID" },
      }).lean();
    }
    throw err;
  }
}

export async function postWithdrawalLedger(withdrawal: {
  _id: string;
  ownerId: string;
  amountLyd: number;
  amountTnd: number;
}) {
  const withdrawalId = String(withdrawal._id);
  const amountLyd = roundMoney(toNumber(withdrawal.amountLyd));
  const amountTnd = roundMoney(toNumber(withdrawal.amountTnd));

  if (isLedgerMysql()) {
    const { sqlQuery } = await import("@/db/mysql/pool");
    const existing = await sqlQuery<import("mysql2/promise").RowDataPacket[]>(
      `SELECT id FROM ledger_entries WHERE withdrawal_id = ? AND type = 'WITHDRAWAL' LIMIT 1`,
      [withdrawalId],
    );
    if (existing[0]) {
      return { _id: String(existing[0].id), id: String(existing[0].id) };
    }
    const { createId } = await import("@/db/ids");
    const id = createId();
    const now = new Date();
    await insertLedgerEntryMysql({
      id,
      type: "WITHDRAWAL",
      direction: "OUT",
      withdrawalId,
      amountLyd,
      amountTnd,
      partyUserId: withdrawal.ownerId,
      partyRole: "OWNER",
      status: "POSTED",
      createdAt: now,
      updatedAt: now,
    });
    return { _id: id, id };
  }

  const existing = await LedgerEntry.findOne({ withdrawalId, type: "WITHDRAWAL" }).lean();
  if (existing) return existing;

  try {
    return await withFinancialDualWrite({
      site: "ledger.withdrawal",
      mongoWrite: async () => {
        return await LedgerEntry.create({
          type: "WITHDRAWAL",
          direction: "OUT",
          withdrawalId,
          amountLyd,
          amountTnd,
          partyUserId: withdrawal.ownerId,
          partyRole: "OWNER",
          status: "POSTED",
        });
      },
      mysqlWrite: async (doc) => {
        await insertLedgerEntryMysql({
          id: String(doc._id),
          type: "WITHDRAWAL",
          direction: "OUT",
          withdrawalId,
          amountLyd,
          amountTnd,
          partyUserId: withdrawal.ownerId,
          partyRole: "OWNER",
          status: "POSTED",
          createdAt: (doc as any).createdAt,
          updatedAt: (doc as any).updatedAt,
        });
      },
      mongoCompensate: async (doc) => {
        await LedgerEntry.deleteOne({ _id: doc._id });
      },
    });
  } catch (err: any) {
    if (err?.code === 11000) {
      return LedgerEntry.findOne({ withdrawalId, type: "WITHDRAWAL" }).lean();
    }
    throw err;
  }
}

type RecordRefundParams = {
  booking: InstanceType<typeof Booking>;
  type: RefundType;
  amountLyd: number;
  reasonCode: RefundReasonCode;
  reasonNote?: string;
  createdBy?: string;
  source: "ADMIN" | "CANCEL" | "OWNER_REJECT";
  session?: ClientSession | null;
};

/**
 * Creates Refund + Ledger REFUND, updates booking payment/refund fields,
 * optionally cancels booking on FULL, credits wallet. Does not rewrite original pricing fields.
 */
export async function recordRefund(params: RecordRefundParams) {
  const booking = params.booking;
  const paymentStatus = booking.payment?.status;
  if (paymentStatus !== "PAID" && paymentStatus !== "PARTIALLY_REFUNDED") {
    throw new AppError(400, "Booking is not eligible for refund");
  }

  const totalLyd = roundMoney(toNumber(booking.totalLyd));
  const alreadyRefunded = roundMoney(toNumber(booking.refundLyd));
  const remainingLyd = roundMoney(Math.max(0, totalLyd - alreadyRefunded));
  if (remainingLyd <= 0) {
    throw new AppError(400, "Booking already fully refunded");
  }

  let refundLyd = roundMoney(params.amountLyd);
  if (params.type === "FULL") {
    refundLyd = remainingLyd;
  }
  if (!(refundLyd > 0)) throw new AppError(400, "Invalid refund amount");
  if (refundLyd > remainingLyd + 0.001) {
    throw new AppError(400, "Refund exceeds remaining paid amount");
  }

  const rate = bookingRate(booking);
  const refundTnd = lydToTnd(refundLyd, rate);
  const share = totalLyd > 0 ? refundLyd / totalLyd : 0;
  const platformFeeClawbackTnd = roundMoney(toNumber(booking.platformFeeTnd) * share);
  const ownerPayoutClawbackTnd = roundMoney(toNumber(booking.ownerPayoutTnd) * share);

  const ledgerMeta = {
    platformFeeClawbackTnd,
    ownerPayoutClawbackTnd,
    reasonCode: params.reasonCode,
    source: params.source,
  };

  const refundDoc = params.session
    ? (
        await Refund.create(
          [
            {
              bookingId: String(booking._id),
              customerId: booking.customerId,
              ownerId: booking.ownerId,
              type: params.type,
              amountLyd: refundLyd,
              amountTnd: refundTnd,
              reasonCode: params.reasonCode,
              reasonNote: params.reasonNote || undefined,
              platformFeeClawbackTnd,
              ownerPayoutClawbackTnd,
              createdBy: params.createdBy,
              walletCreditedLyd: 0,
              source: params.source,
            },
          ],
          { session: params.session },
        )
      )[0]
    : await Refund.create({
        bookingId: String(booking._id),
        customerId: booking.customerId,
        ownerId: booking.ownerId,
        type: params.type,
        amountLyd: refundLyd,
        amountTnd: refundTnd,
        reasonCode: params.reasonCode,
        reasonNote: params.reasonNote || undefined,
        platformFeeClawbackTnd,
        ownerPayoutClawbackTnd,
        createdBy: params.createdBy,
        walletCreditedLyd: 0,
        source: params.source,
      });

  const ledgerDocs = params.session
    ? await LedgerEntry.create(
        [
          {
            type: "REFUND",
            direction: "OUT",
            bookingId: String(booking._id),
            refundId: String(refundDoc._id),
            amountLyd: refundLyd,
            amountTnd: refundTnd,
            partyUserId: booking.customerId,
            partyRole: "CUSTOMER",
            status: "POSTED",
            meta: ledgerMeta,
          },
        ],
        { session: params.session },
      )
    : [
        await LedgerEntry.create({
          type: "REFUND",
          direction: "OUT",
          bookingId: String(booking._id),
          refundId: String(refundDoc._id),
          amountLyd: refundLyd,
          amountTnd: refundTnd,
          partyUserId: booking.customerId,
          partyRole: "CUSTOMER",
          status: "POSTED",
          meta: ledgerMeta,
        }),
      ];
  const ledgerDoc = ledgerDocs[0];

  const prevBookingSnapshot = {
    refundLyd: alreadyRefunded,
    refundPercent: toNumber(booking.refundPercent),
    status: booking.status,
    paymentStatus: booking.payment?.status,
    paymentMetadata: booking.payment?.metadata
      ? { ...(booking.payment.metadata as object) }
      : undefined,
    cancelledBy: (booking as any).cancelledBy,
    cancelledAt: (booking as any).cancelledAt,
  };

  const newRefundTotal = roundMoney(alreadyRefunded + refundLyd);
  booking.refundLyd = newRefundTotal;
  booking.refundPercent = totalLyd > 0 ? roundMoney((newRefundTotal / totalLyd) * 100) : 0;

  if (booking.payment) {
    booking.payment.status =
      newRefundTotal >= totalLyd - 0.001 ? "REFUNDED" : "PARTIALLY_REFUNDED";
    booking.payment.updatedAt = new Date();
    booking.payment.metadata = {
      ...(booking.payment.metadata || {}),
      refundLyd: newRefundTotal,
      refundPercent: booking.refundPercent,
      lastRefundId: String(refundDoc._id),
      lastRefundReason: params.reasonCode,
    };
  }

  if (params.source === "ADMIN" && (params.type === "FULL" || newRefundTotal >= totalLyd - 0.001)) {
    booking.status = "CANCELLED";
    if (!(booking as { cancelledBy?: string }).cancelledBy) {
      (booking as { cancelledBy?: string; cancelledAt?: Date }).cancelledBy = "ADMIN";
      (booking as { cancelledAt?: Date }).cancelledAt = new Date();
    }
  }

  if (params.session) {
    await booking.save({ session: params.session });
  } else {
    await booking.save();
  }

  // Mirror refund + ledger + booking to MySQL (strict when dual-write on; skip mid-session).
  if (!params.session) {
    try {
      const { isFinancialDualWriteEnabled } = await import("@/db/dualWriteFinancial");
      if (isFinancialDualWriteEnabled()) {
        await insertRefundMysql({
          id: String(refundDoc._id),
          bookingId: String(booking._id),
          customerId: booking.customerId,
          ownerId: booking.ownerId,
          type: params.type,
          amountLyd: refundLyd,
          amountTnd: refundTnd,
          reasonCode: params.reasonCode,
          reasonNote: params.reasonNote,
          platformFeeClawbackTnd,
          ownerPayoutClawbackTnd,
          createdBy: params.createdBy,
          walletCreditedLyd: 0,
          source: params.source,
          createdAt: (refundDoc as any).createdAt,
          updatedAt: (refundDoc as any).updatedAt,
        });
        await insertLedgerEntryMysql({
          id: String(ledgerDoc._id),
          type: "REFUND",
          direction: "OUT",
          bookingId: String(booking._id),
          refundId: String(refundDoc._id),
          amountLyd: refundLyd,
          amountTnd: refundTnd,
          partyUserId: booking.customerId,
          partyRole: "CUSTOMER",
          status: "POSTED",
          meta: ledgerMeta,
          createdAt: (ledgerDoc as any).createdAt,
          updatedAt: (ledgerDoc as any).updatedAt,
        });
        await syncBookingToMysql(booking as any);
      }
    } catch (e) {
      console.error("[dual-write-financial] FAIL refund.bundle:", e);
      await LedgerEntry.deleteOne({ _id: ledgerDoc._id });
      await Refund.deleteOne({ _id: refundDoc._id });
      booking.refundLyd = prevBookingSnapshot.refundLyd;
      booking.refundPercent = prevBookingSnapshot.refundPercent;
      booking.status = prevBookingSnapshot.status as any;
      if (booking.payment) {
        booking.payment.status = prevBookingSnapshot.paymentStatus as any;
        booking.payment.metadata = prevBookingSnapshot.paymentMetadata as any;
      }
      (booking as any).cancelledBy = prevBookingSnapshot.cancelledBy;
      (booking as any).cancelledAt = prevBookingSnapshot.cancelledAt;
      await booking.save();
      try {
        await deleteLedgerEntryMysql(String(ledgerDoc._id));
      } catch {
        /* ignore */
      }
      try {
        await deleteRefundMysql(String(refundDoc._id));
      } catch {
        /* ignore */
      }
      throw e;
    }
  }

  // Credit wallet after durable refund records (wallet ops are not session-scoped).
  const settings = await getCommerceSettings();
  let walletCreditedLyd = 0;
  if (settings.walletEnabled && refundLyd > 0) {
    const { txn } = await creditWallet({
      userId: booking.customerId,
      amountLyd: refundLyd,
      type: "REFUND",
      bookingId: String(booking._id),
      note: `Refund ${params.type} (${params.reasonCode})`,
      meta: { source: params.source, reasonCode: params.reasonCode, refundId: String(refundDoc._id) },
    });
    walletCreditedLyd = Math.abs(toNumber(txn.amountLyd));
    refundDoc.walletCreditedLyd = walletCreditedLyd;
    if (params.session) {
      await refundDoc.save({ session: params.session });
    } else {
      await refundDoc.save();
      try {
        const { isFinancialDualWriteEnabled } = await import("@/db/dualWriteFinancial");
        if (isFinancialDualWriteEnabled()) {
          await insertRefundMysql({
            id: String(refundDoc._id),
            bookingId: String(booking._id),
            customerId: booking.customerId,
            ownerId: booking.ownerId,
            type: params.type,
            amountLyd: refundLyd,
            amountTnd: refundTnd,
            reasonCode: params.reasonCode,
            reasonNote: params.reasonNote,
            platformFeeClawbackTnd,
            ownerPayoutClawbackTnd,
            createdBy: params.createdBy,
            walletCreditedLyd,
            source: params.source,
            createdAt: (refundDoc as any).createdAt,
            updatedAt: new Date(),
          });
        }
      } catch (e) {
        console.error("[dual-write-financial] FAIL refund.walletCredited sync:", e);
      }
    }
  }

  return {
    refund: refundDoc,
    refundLyd,
    refundTnd,
    refundPercent: booking.refundPercent,
    bookingStatus: booking.status,
    paymentStatus: booking.payment?.status,
  };
}

export async function getReconciliationSummary() {
  const paidStatuses = ["PAID", "PARTIALLY_REFUNDED", "REFUNDED"];
  let paidBookings: Array<{
    totalLyd: number;
    totalTnd: number;
    platformFeeTnd: number;
    ownerPayoutTnd?: number;
    exchangeRateRate: number;
    status?: string;
  }>;

  if (isBookingsMysql()) {
    const { listBookingsMysql } = await import("@/db/mysql/bookings");
    const rows = await listBookingsMysql({
      paymentStatus: paidStatuses,
      take: 5000,
    });
    paidBookings = rows.map((b) => ({
      totalLyd: b.totalLyd,
      totalTnd: b.totalTnd,
      platformFeeTnd: b.platformFeeTnd,
      ownerPayoutTnd: b.ownerPayoutTnd,
      exchangeRateRate: b.exchangeRateRate,
      status: b.status,
    }));
  } else {
    paidBookings = await Booking.find({
      deletedAt: null,
      "payment.status": { $in: paidStatuses },
    })
      .select("totalLyd totalTnd platformFeeTnd ownerPayoutTnd exchangeRateRate status")
      .lean();
  }

  let revenueLyd = 0;
  let revenueTnd = 0;
  let platformFeeTnd = 0;
  let platformFeeLyd = 0;

  for (const b of paidBookings) {
    revenueLyd += toNumber(b.totalLyd);
    revenueTnd += toNumber(b.totalTnd);
    const fee = toNumber(b.platformFeeTnd);
    platformFeeTnd += fee;
    platformFeeLyd += tndToLyd(fee, bookingRate(b));
  }

  revenueLyd = roundMoney(revenueLyd);
  revenueTnd = roundMoney(revenueTnd);
  platformFeeTnd = roundMoney(platformFeeTnd);
  platformFeeLyd = roundMoney(platformFeeLyd);

  const [refundAgg, approvedWithdrawals, pendingAndApproved] = await Promise.all([
    (async () => {
      if (isRefundsMysql()) {
        const { sumRefundsMysql } = await import("@/db/mysql/refunds");
        const s = await sumRefundsMysql();
        return [
          {
            amountLyd: s.amountLyd,
            amountTnd: s.amountTnd,
            platformFeeClawbackTnd: s.platformFeeClawbackTnd,
          },
        ];
      }
      return Refund.aggregate([
        {
          $group: {
            _id: null,
            amountLyd: { $sum: "$amountLyd" },
            amountTnd: { $sum: "$amountTnd" },
            platformFeeClawbackTnd: { $sum: "$platformFeeClawbackTnd" },
          },
        },
      ]);
    })(),
    (async () => {
      if (isWithdrawalsMysql()) {
        const { listWithdrawalsMysql } = await import("@/db/mysql/withdrawals");
        return listWithdrawalsMysql({ status: "APPROVED", take: 5000 });
      }
      return WithdrawalRequest.find({ status: "APPROVED" })
        .select("amountLyd amountTnd")
        .lean();
    })(),
    (async () => {
      if (isWithdrawalsMysql()) {
        const { listWithdrawalsMysql } = await import("@/db/mysql/withdrawals");
        return listWithdrawalsMysql({
          statuses: ["PENDING", "APPROVED"],
          take: 5000,
        });
      }
      return WithdrawalRequest.find({ status: { $in: ["PENDING", "APPROVED"] } })
        .select("amountTnd amountLyd")
        .lean();
    })(),
  ]);

  const refundsLyd = roundMoney(toNumber(refundAgg[0]?.amountLyd));
  const refundsTnd = roundMoney(toNumber(refundAgg[0]?.amountTnd));
  const clawbackTnd = roundMoney(toNumber(refundAgg[0]?.platformFeeClawbackTnd));

  const approvedWithdrawalsLyd = roundMoney(
    approvedWithdrawals.reduce((s, w) => s + toNumber(w.amountLyd), 0),
  );
  const approvedWithdrawalsTnd = roundMoney(
    approvedWithdrawals.reduce((s, w) => s + toNumber(w.amountTnd), 0),
  );

  // Pending payouts across all owners: COMPLETED payouts minus PENDING+APPROVED withdrawals
  let completed: Array<{ ownerPayoutTnd: number; exchangeRateRate: number }>;
  if (isBookingsMysql()) {
    const { listBookingsMysql } = await import("@/db/mysql/bookings");
    const rows = await listBookingsMysql({ status: "COMPLETED", take: 5000 });
    completed = rows.map((b) => ({
      ownerPayoutTnd: b.ownerPayoutTnd,
      exchangeRateRate: b.exchangeRateRate,
    }));
  } else {
    completed = await Booking.find({
      deletedAt: null,
      status: "COMPLETED",
    })
      .select("ownerPayoutTnd exchangeRateRate")
      .lean();
  }
  const grossOwnerTnd = completed.reduce((s, b) => s + toNumber(b.ownerPayoutTnd), 0);
  const reservedTnd = pendingAndApproved.reduce((s, w) => s + toNumber(w.amountTnd), 0);
  const pendingPayoutsTnd = roundMoney(Math.max(0, grossOwnerTnd - reservedTnd));
  // Approximate LYD using average rate from completed bookings or 1
  const avgRate =
    completed.length > 0
      ? completed.reduce((s, b) => s + bookingRate(b), 0) / completed.length
      : 1;
  const pendingPayoutsLyd = tndToLyd(pendingPayoutsTnd, avgRate);

  const netBalanceLyd = roundMoney(revenueLyd - refundsLyd - approvedWithdrawalsLyd);
  const netBalanceTnd = roundMoney(revenueTnd - refundsTnd - approvedWithdrawalsTnd);
  const platformProfitTnd = roundMoney(platformFeeTnd - clawbackTnd);
  const platformProfitLyd = roundMoney(
    platformFeeLyd - tndToLyd(clawbackTnd, avgRate > 0 ? avgRate : 1),
  );

  return {
    revenueLyd,
    revenueTnd,
    platformFeeLyd,
    platformFeeTnd,
    pendingPayoutsLyd,
    pendingPayoutsTnd,
    refundsLyd,
    refundsTnd,
    approvedWithdrawalsLyd,
    approvedWithdrawalsTnd,
    netBalanceLyd,
    netBalanceTnd,
    platformProfitLyd,
    platformProfitTnd,
  };
}

/** Posted-only ledger sums (excludes VOID). Used for parity checks during hybrid. */
export async function sumLedgerByTypePosted() {
  const { isLedgerMysql } = await import("@/db/activeDatabase");
  if (isLedgerMysql()) {
    const { sumLedgerByTypeMysql } = await import("@/db/mysql/ledger");
    return sumLedgerByTypeMysql();
  }
  const rows = await LedgerEntry.aggregate([
    { $match: { status: "POSTED" } },
    {
      $group: {
        _id: "$type",
        count: { $sum: 1 },
        amountLyd: { $sum: "$amountLyd" },
        amountTnd: { $sum: "$amountTnd" },
      },
    },
    { $sort: { _id: 1 } },
  ]);
  return rows.map((r) => ({
    type: String(r._id),
    count: Number(r.count),
    amountLyd: roundMoney(toNumber(r.amountLyd)),
    amountTnd: roundMoney(toNumber(r.amountTnd)),
  }));
}

/** Explicit Mongo posted sums (for cross-DB VOID exclusion checks). */
export async function sumLedgerByTypePostedMongo() {
  const rows = await LedgerEntry.aggregate([
    { $match: { status: "POSTED" } },
    {
      $group: {
        _id: "$type",
        count: { $sum: 1 },
        amountLyd: { $sum: "$amountLyd" },
        amountTnd: { $sum: "$amountTnd" },
      },
    },
    { $sort: { _id: 1 } },
  ]);
  return rows.map((r) => ({
    type: String(r._id),
    count: Number(r.count),
    amountLyd: roundMoney(toNumber(r.amountLyd)),
    amountTnd: roundMoney(toNumber(r.amountTnd)),
  }));
}

export async function backfillLedgerEntries() {
  // When bookings/ledger are on MySQL (no mongoose connection), skip Mongo backfill.
  if (isBookingsMysql() || isLedgerMysql()) {
    return { bookingPosted: 0, withdrawalPosted: 0 };
  }

  const paidStatuses = ["PAID", "PARTIALLY_REFUNDED", "REFUNDED"];
  const bookings = await Booking.find({
    deletedAt: null,
    "payment.status": { $in: paidStatuses },
  })
    .select("_id customerId totalLyd totalTnd platformFeeTnd ownerPayoutTnd exchangeRateRate")
    .lean();

  let bookingPosted = 0;
  for (const b of bookings) {
    const before = await LedgerEntry.findOne({ bookingId: String(b._id), type: "BOOKING_PAYMENT" }).lean();
    await postBookingPaymentLedger(b as any);
    if (!before) bookingPosted += 1;
  }

  const withdrawals = await WithdrawalRequest.find({ status: "APPROVED" }).lean();
  let withdrawalPosted = 0;
  for (const w of withdrawals) {
    const before = await LedgerEntry.findOne({
      withdrawalId: String(w._id),
      type: "WITHDRAWAL",
    }).lean();
    await postWithdrawalLedger(w as any);
    if (!before) withdrawalPosted += 1;
  }

  return { bookingPosted, withdrawalPosted };
}