/**
 * MySQL writers for financial dual-write (Mongo primary → MySQL mirror).
 * Used only via dualWriteFinancial strict helpers — not as route primary yet.
 */
import type { PoolConnection, ResultSetHeader } from "mysql2/promise";
import { connectMysql, mysqlPool, withMysqlTxn } from "./pool";

async function ensure() {
  try {
    mysqlPool();
  } catch {
    await connectMysql();
  }
}

function json(v: unknown) {
  if (v == null) return null;
  return JSON.stringify(v);
}

/** Test hook: set DUAL_WRITE_FINANCIAL_FAIL=1 to force MySQL failure (rollback tests). */
function maybeInjectFail(site: string) {
  if ((process.env.DUAL_WRITE_FINANCIAL_FAIL || "").trim() === "1") {
    throw new Error(`injected dual-write financial fail at ${site}`);
  }
}

export async function upsertWalletMysql(input: {
  id: string;
  userId: string;
  balanceLyd: number;
  createdAt?: Date;
  updatedAt?: Date;
}) {
  await ensure();
  maybeInjectFail("wallet.upsert");
  const now = new Date();
  await withMysqlTxn(async (conn) => {
    await conn.execute(
      `INSERT INTO wallets (id, user_id, balance_lyd, created_at, updated_at)
       VALUES (?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         balance_lyd = VALUES(balance_lyd),
         updated_at = VALUES(updated_at)`,
      [
        input.id,
        input.userId,
        input.balanceLyd,
        input.createdAt || now,
        input.updatedAt || now,
      ],
    );
  });
}

export async function insertWalletTxnMysql(input: {
  id: string;
  walletId: string;
  userId: string;
  type: string;
  amountLyd: number;
  balanceAfter: number;
  bookingId?: string | null;
  topUpId?: string | null;
  note?: string | null;
  meta?: Record<string, unknown> | null;
  createdAt?: Date;
}) {
  await ensure();
  maybeInjectFail("wallet_txn.insert");
  await withMysqlTxn(async (conn) => {
    await conn.execute(
      `INSERT INTO wallet_txns (
         id, wallet_id, user_id, type, amount_lyd, balance_after,
         booking_id, top_up_id, note, meta, created_at
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         amount_lyd = VALUES(amount_lyd),
         balance_after = VALUES(balance_after)`,
      [
        input.id,
        input.walletId,
        input.userId,
        input.type,
        input.amountLyd,
        input.balanceAfter,
        input.bookingId ?? null,
        input.topUpId ?? null,
        input.note ?? null,
        json(input.meta) ?? "null",
        input.createdAt || new Date(),
      ],
    );
  });
}

export async function upsertWalletAndTxnMysql(opts: {
  wallet: {
    id: string;
    userId: string;
    balanceLyd: number;
    createdAt?: Date;
    updatedAt?: Date;
  };
  txn: {
    id: string;
    walletId: string;
    userId: string;
    type: string;
    amountLyd: number;
    balanceAfter: number;
    bookingId?: string | null;
    topUpId?: string | null;
    note?: string | null;
    meta?: Record<string, unknown> | null;
    createdAt?: Date;
  };
}) {
  await ensure();
  maybeInjectFail("wallet+txn");
  await withMysqlTxn(async (conn) => {
    const now = new Date();
    await conn.execute(
      `INSERT INTO wallets (id, user_id, balance_lyd, created_at, updated_at)
       VALUES (?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         balance_lyd = VALUES(balance_lyd),
         updated_at = VALUES(updated_at)`,
      [
        opts.wallet.id,
        opts.wallet.userId,
        opts.wallet.balanceLyd,
        opts.wallet.createdAt || now,
        opts.wallet.updatedAt || now,
      ],
    );
    await conn.execute(
      `INSERT INTO wallet_txns (
         id, wallet_id, user_id, type, amount_lyd, balance_after,
         booking_id, top_up_id, note, meta, created_at
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         amount_lyd = VALUES(amount_lyd),
         balance_after = VALUES(balance_after)`,
      [
        opts.txn.id,
        opts.txn.walletId,
        opts.txn.userId,
        opts.txn.type,
        opts.txn.amountLyd,
        opts.txn.balanceAfter,
        opts.txn.bookingId ?? null,
        opts.txn.topUpId ?? null,
        opts.txn.note ?? null,
        json(opts.txn.meta) ?? "null",
        opts.txn.createdAt || now,
      ],
    );
  });
}

export async function mutateWalletAndInsertTxnMysql(opts: {
  wallet: { id: string; userId: string; createdAt?: Date };
  deltaLyd: number;
  txn: {
    id: string;
    type: string;
    bookingId?: string | null;
    topUpId?: string | null;
    note?: string | null;
    meta?: Record<string, unknown> | null;
    createdAt?: Date;
  };
}) {
  await ensure();
  maybeInjectFail("wallet+txn.atomic");
  return withMysqlTxn(async (conn) => {
    const now = opts.txn.createdAt || new Date();
    await conn.execute(
      `INSERT INTO wallets (id, user_id, balance_lyd, created_at, updated_at)
       VALUES (?,?,0,?,?) ON DUPLICATE KEY UPDATE user_id = VALUES(user_id)`,
      [opts.wallet.id, opts.wallet.userId, opts.wallet.createdAt || now, now],
    );
    const [rows] = await conn.query<any[]>(
      `SELECT id, balance_lyd FROM wallets WHERE user_id = ? FOR UPDATE`,
      [opts.wallet.userId],
    );
    const walletId = String(rows[0].id);
    const previousBalance = Number(rows[0].balance_lyd || 0);
    const balanceLyd = Math.round((previousBalance + opts.deltaLyd) * 100) / 100;
    if (balanceLyd < -1e-9) throw new Error("INSUFFICIENT_WALLET_BALANCE");
    await conn.execute(
      `UPDATE wallets SET balance_lyd = ?, updated_at = ? WHERE id = ?`,
      [balanceLyd, now, walletId],
    );
    await conn.execute(
      `INSERT INTO wallet_txns (
         id, wallet_id, user_id, type, amount_lyd, balance_after,
         booking_id, top_up_id, note, meta, created_at
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?)`,
      [
        opts.txn.id, walletId, opts.wallet.userId, opts.txn.type, opts.deltaLyd, balanceLyd,
        opts.txn.bookingId ?? null, opts.txn.topUpId ?? null, opts.txn.note ?? null,
        json(opts.txn.meta) ?? "null", now,
      ],
    );
    return { walletId, balanceLyd };
  });
}

export async function deleteWalletTxnMysql(id: string) {
  await ensure();
  await withMysqlTxn(async (conn) => {
    await conn.execute(`DELETE FROM wallet_txns WHERE id = ?`, [id]);
  });
}

export async function insertLedgerEntryMysql(input: {
  id: string;
  type: string;
  direction: string;
  bookingId?: string | null;
  refundId?: string | null;
  withdrawalId?: string | null;
  amountLyd: number;
  amountTnd: number;
  partyUserId?: string | null;
  partyRole: string;
  status?: string;
  meta?: Record<string, unknown> | null;
  createdAt?: Date;
  updatedAt?: Date;
}) {
  await ensure();
  maybeInjectFail("ledger.insert");
  const bookingPaymentKey =
    input.type === "BOOKING_PAYMENT" && input.bookingId && input.status !== "VOID"
      ? input.bookingId
      : null;
  const withdrawalKey =
    input.type === "WITHDRAWAL" && input.withdrawalId ? input.withdrawalId : null;
  const now = new Date();
  await withMysqlTxn(async (conn) => {
    await conn.execute(
      `INSERT INTO ledger_entries (
         id, type, direction, booking_id, refund_id, withdrawal_id,
         amount_lyd, amount_tnd, party_user_id, party_role, status, meta,
         created_at, updated_at, booking_payment_key, withdrawal_key
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         status = VALUES(status),
         amount_lyd = VALUES(amount_lyd),
         amount_tnd = VALUES(amount_tnd),
         meta = VALUES(meta),
         updated_at = VALUES(updated_at)`,
      [
        input.id,
        input.type,
        input.direction,
        input.bookingId ?? null,
        input.refundId ?? null,
        input.withdrawalId ?? null,
        input.amountLyd,
        input.amountTnd,
        input.partyUserId ?? null,
        input.partyRole,
        input.status || "POSTED",
        json(input.meta) ?? "null",
        input.createdAt || now,
        input.updatedAt || now,
        bookingPaymentKey,
        withdrawalKey,
      ],
    );
  });
}

export async function deleteLedgerEntryMysql(id: string) {
  await ensure();
  await withMysqlTxn(async (conn) => {
    await conn.execute(`DELETE FROM ledger_entries WHERE id = ?`, [id]);
  });
}

export async function upsertBookingMysql(doc: {
  id: string;
  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 ensure();
  maybeInjectFail("booking.upsert");
  const payment = doc.payment || null;
  const invoice = doc.invoice || null;
  const invoiceNumber =
    invoice && typeof invoice.invoiceNumber === "string"
      ? invoice.invoiceNumber
      : null;
  const paymentStatus =
    payment && typeof payment.status === "string" ? payment.status : null;
  const now = new Date();
  await withMysqlTxn(async (conn) => {
    await conn.execute(
      `INSERT INTO bookings (
         id, property_id, customer_id, owner_id, check_in, check_out, guests, nights, status,
         exchange_from_currency, exchange_to_currency, exchange_rate_rate,
         exchange_rate_locked_at, exchange_rate_expires_at,
         subtotal_tnd, cleaning_fee_tnd, platform_fee_tnd, taxes_tnd, discount_tnd,
         total_tnd, total_lyd, owner_payout_tnd, coupon_code, wallet_paid_lyd,
         points_earned, points_redeemed, points_discount_tnd, refund_lyd, refund_percent,
         cancelled_by, cancelled_at, deleted_at, payment, invoice, invoice_number, payment_status,
         created_at, updated_at
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         status = VALUES(status),
         exchange_rate_rate = VALUES(exchange_rate_rate),
         exchange_rate_locked_at = VALUES(exchange_rate_locked_at),
         exchange_rate_expires_at = VALUES(exchange_rate_expires_at),
         subtotal_tnd = VALUES(subtotal_tnd),
         cleaning_fee_tnd = VALUES(cleaning_fee_tnd),
         platform_fee_tnd = VALUES(platform_fee_tnd),
         taxes_tnd = VALUES(taxes_tnd),
         discount_tnd = VALUES(discount_tnd),
         total_tnd = VALUES(total_tnd),
         total_lyd = VALUES(total_lyd),
         owner_payout_tnd = VALUES(owner_payout_tnd),
         coupon_code = VALUES(coupon_code),
         wallet_paid_lyd = VALUES(wallet_paid_lyd),
         points_earned = VALUES(points_earned),
         points_redeemed = VALUES(points_redeemed),
         points_discount_tnd = VALUES(points_discount_tnd),
         refund_lyd = VALUES(refund_lyd),
         refund_percent = VALUES(refund_percent),
         cancelled_by = VALUES(cancelled_by),
         cancelled_at = VALUES(cancelled_at),
         deleted_at = VALUES(deleted_at),
         payment = VALUES(payment),
         invoice = VALUES(invoice),
         invoice_number = VALUES(invoice_number),
         payment_status = VALUES(payment_status),
         updated_at = VALUES(updated_at)`,
      [
        doc.id,
        doc.propertyId,
        doc.customerId,
        doc.ownerId,
        doc.checkIn,
        doc.checkOut,
        doc.guests,
        doc.nights,
        doc.status,
        doc.exchangeFromCurrency || "TND",
        doc.exchangeToCurrency || "LYD",
        doc.exchangeRateRate,
        doc.exchangeRateLockedAt,
        doc.exchangeRateExpiresAt,
        doc.subtotalTnd,
        doc.cleaningFeeTnd,
        doc.platformFeeTnd,
        doc.taxesTnd,
        doc.discountTnd ?? 0,
        doc.totalTnd,
        doc.totalLyd,
        doc.ownerPayoutTnd,
        doc.couponCode ?? null,
        doc.walletPaidLyd ?? 0,
        doc.pointsEarned ?? 0,
        doc.pointsRedeemed ?? 0,
        doc.pointsDiscountTnd ?? 0,
        doc.refundLyd ?? 0,
        doc.refundPercent ?? 0,
        doc.cancelledBy ?? null,
        doc.cancelledAt ?? null,
        doc.deletedAt ?? null,
        json(payment) ?? "null",
        json(invoice) ?? "null",
        invoiceNumber,
        paymentStatus,
        doc.createdAt || now,
        doc.updatedAt || now,
      ],
    );
  });
}

export async function insertRefundMysql(input: {
  id: string;
  bookingId: string;
  customerId: string;
  ownerId: string;
  type: string;
  amountLyd: number;
  amountTnd: number;
  reasonCode: string;
  reasonNote?: string | null;
  platformFeeClawbackTnd?: number;
  ownerPayoutClawbackTnd?: number;
  createdBy?: string | null;
  walletCreditedLyd?: number;
  source?: string;
  createdAt?: Date;
  updatedAt?: Date;
}) {
  await ensure();
  maybeInjectFail("refund.insert");
  const now = new Date();
  await withMysqlTxn(async (conn) => {
    await conn.execute(
      `INSERT INTO refunds (
         id, booking_id, customer_id, owner_id, type, amount_lyd, amount_tnd,
         reason_code, reason_note, platform_fee_clawback_tnd, owner_payout_clawback_tnd,
         created_by, wallet_credited_lyd, source, created_at, updated_at
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         amount_lyd = VALUES(amount_lyd),
         wallet_credited_lyd = VALUES(wallet_credited_lyd),
         updated_at = VALUES(updated_at)`,
      [
        input.id,
        input.bookingId,
        input.customerId,
        input.ownerId,
        input.type,
        input.amountLyd,
        input.amountTnd,
        input.reasonCode,
        input.reasonNote ?? null,
        input.platformFeeClawbackTnd ?? 0,
        input.ownerPayoutClawbackTnd ?? 0,
        input.createdBy ?? null,
        input.walletCreditedLyd ?? 0,
        input.source || "ADMIN",
        input.createdAt || now,
        input.updatedAt || now,
      ],
    );
  });
}

export async function deleteRefundMysql(id: string) {
  await ensure();
  await withMysqlTxn(async (conn) => {
    await conn.execute(`DELETE FROM refunds WHERE id = ?`, [id]);
  });
}

export async function upsertWithdrawalMysql(input: {
  id: string;
  ownerId: string;
  amountTnd: number;
  amountLyd: number;
  exchangeRateRate?: number;
  method: string;
  status: string;
  note?: string | null;
  rejectionReason?: string | null;
  reviewedBy?: string | null;
  reviewedAt?: Date | null;
  createdAt?: Date;
  updatedAt?: Date;
}) {
  await ensure();
  maybeInjectFail("withdrawal.upsert");
  const now = new Date();
  await withMysqlTxn(async (conn: PoolConnection) => {
    await conn.execute(
      `INSERT INTO withdrawal_requests (
         id, owner_id, amount_tnd, amount_lyd, exchange_rate_rate, method, status,
         note, rejection_reason, reviewed_by, reviewed_at, created_at, updated_at
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         status = VALUES(status),
         rejection_reason = VALUES(rejection_reason),
         reviewed_by = VALUES(reviewed_by),
         reviewed_at = VALUES(reviewed_at),
         updated_at = VALUES(updated_at)`,
      [
        input.id,
        input.ownerId,
        input.amountTnd,
        input.amountLyd,
        input.exchangeRateRate ?? 0,
        input.method,
        input.status,
        input.note ?? null,
        input.rejectionReason ?? null,
        input.reviewedBy ?? null,
        input.reviewedAt ?? null,
        input.createdAt || now,
        input.updatedAt || now,
      ],
    );
  });
}

export async function deleteWithdrawalMysql(id: string) {
  await ensure();
  await withMysqlTxn(async (conn) => {
    await conn.execute(`DELETE FROM withdrawal_requests WHERE id = ?`, [id]);
  });
}

export async function upsertWalletTopUpMysql(input: {
  id: string;
  userId: string;
  amountLyd: number;
  bankName: string;
  reference: string;
  status: string;
  reviewedBy?: string | null;
  reviewedAt?: Date | null;
  reviewNote?: string | null;
  createdAt?: Date;
  updatedAt?: Date;
}) {
  await ensure();
  maybeInjectFail("wallet_topup.upsert");
  const now = new Date();
  await withMysqlTxn(async (conn) => {
    await conn.execute(
      `INSERT INTO wallet_topups (
         id, user_id, amount_lyd, bank_name, reference, status,
         reviewed_by, reviewed_at, review_note, created_at, updated_at
       ) VALUES (?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         status = VALUES(status),
         reviewed_by = VALUES(reviewed_by),
         reviewed_at = VALUES(reviewed_at),
         review_note = VALUES(review_note),
         updated_at = VALUES(updated_at)`,
      [
        input.id,
        input.userId,
        input.amountLyd,
        input.bankName,
        input.reference,
        input.status,
        input.reviewedBy ?? null,
        input.reviewedAt ?? null,
        input.reviewNote ?? null,
        input.createdAt || now,
        input.updatedAt || now,
      ],
    );
  });
}

/** no-op type import keep */
export type _FinancialWriteResult = ResultSetHeader;