/**
 * Bookings MySQL adapter.
 * Reads are used when BOOKINGS_DATABASE=mysql.
 * Expire/clear helpers keep availability consistent during dual-write.
 * Primary creates/updates still go through Mongo services until write cutover.
 */
import type { RowDataPacket } from "mysql2/promise";
import { connectMysql, mysqlPool, sqlExecute, sqlQuery } from "./pool";

export type BookingRow = {
  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;
  invoiceNumber: string | null;
  paymentStatus: string | null;
  createdAt: Date;
  updatedAt: Date;
};

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

function num(v: unknown, fallback = 0) {
  if (v == null) return fallback;
  const n = typeof v === "number" ? v : Number(v);
  return Number.isFinite(n) ? n : fallback;
}

function parseJson(v: unknown): Record<string, unknown> | null {
  if (v == null) return null;
  if (typeof v === "object" && !Buffer.isBuffer(v)) return v as Record<string, unknown>;
  if (typeof v === "string") {
    try {
      const parsed = JSON.parse(v);
      return parsed && typeof parsed === "object" ? (parsed as Record<string, unknown>) : null;
    } catch {
      return null;
    }
  }
  return null;
}

function map(r: RowDataPacket): BookingRow {
  return {
    id: String(r.id),
    propertyId: String(r.property_id),
    customerId: String(r.customer_id),
    ownerId: String(r.owner_id),
    checkIn: new Date(r.check_in),
    checkOut: new Date(r.check_out),
    guests: num(r.guests, 1),
    nights: num(r.nights, 1),
    status: String(r.status),
    exchangeFromCurrency: String(r.exchange_from_currency || "TND"),
    exchangeToCurrency: String(r.exchange_to_currency || "LYD"),
    exchangeRateRate: num(r.exchange_rate_rate),
    exchangeRateLockedAt: new Date(r.exchange_rate_locked_at),
    exchangeRateExpiresAt: new Date(r.exchange_rate_expires_at),
    subtotalTnd: num(r.subtotal_tnd),
    cleaningFeeTnd: num(r.cleaning_fee_tnd),
    platformFeeTnd: num(r.platform_fee_tnd),
    taxesTnd: num(r.taxes_tnd),
    discountTnd: num(r.discount_tnd),
    totalTnd: num(r.total_tnd),
    totalLyd: num(r.total_lyd),
    ownerPayoutTnd: num(r.owner_payout_tnd),
    couponCode: r.coupon_code == null ? null : String(r.coupon_code),
    walletPaidLyd: num(r.wallet_paid_lyd),
    pointsEarned: num(r.points_earned),
    pointsRedeemed: num(r.points_redeemed),
    pointsDiscountTnd: num(r.points_discount_tnd),
    refundLyd: num(r.refund_lyd),
    refundPercent: num(r.refund_percent),
    cancelledBy: r.cancelled_by == null ? null : String(r.cancelled_by),
    cancelledAt: r.cancelled_at ? new Date(r.cancelled_at) : null,
    deletedAt: r.deleted_at ? new Date(r.deleted_at) : null,
    payment: parseJson(r.payment),
    invoice: parseJson(r.invoice),
    invoiceNumber: r.invoice_number == null ? null : String(r.invoice_number),
    paymentStatus: r.payment_status == null ? null : String(r.payment_status),
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

/** Shape close to Mongo lean + serializeBooking inputs. */
export function bookingToApi(b: BookingRow) {
  return {
    _id: b.id,
    id: b.id,
    propertyId: b.propertyId,
    customerId: b.customerId,
    ownerId: b.ownerId,
    checkIn: b.checkIn,
    checkOut: b.checkOut,
    guests: b.guests,
    nights: b.nights,
    status: b.status,
    exchangeFromCurrency: b.exchangeFromCurrency,
    exchangeToCurrency: b.exchangeToCurrency,
    exchangeRateRate: b.exchangeRateRate,
    exchangeRateLockedAt: b.exchangeRateLockedAt,
    exchangeRateExpiresAt: b.exchangeRateExpiresAt,
    subtotalTnd: b.subtotalTnd,
    cleaningFeeTnd: b.cleaningFeeTnd,
    platformFeeTnd: b.platformFeeTnd,
    taxesTnd: b.taxesTnd,
    discountTnd: b.discountTnd,
    totalTnd: b.totalTnd,
    totalLyd: b.totalLyd,
    ownerPayoutTnd: b.ownerPayoutTnd,
    couponCode: b.couponCode ?? undefined,
    walletPaidLyd: b.walletPaidLyd,
    pointsEarned: b.pointsEarned,
    pointsRedeemed: b.pointsRedeemed,
    pointsDiscountTnd: b.pointsDiscountTnd,
    refundLyd: b.refundLyd,
    refundPercent: b.refundPercent,
    cancelledBy: b.cancelledBy ?? undefined,
    cancelledAt: b.cancelledAt ?? undefined,
    deletedAt: b.deletedAt ?? undefined,
    payment: b.payment ?? undefined,
    invoice: b.invoice ?? undefined,
    createdAt: b.createdAt,
    updatedAt: b.updatedAt,
  };
}

export async function countBookingsMysql(opts?: {
  includeDeleted?: boolean;
}): Promise<number> {
  await ensure();
  const where = opts?.includeDeleted ? "" : "WHERE deleted_at IS NULL";
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT COUNT(*) AS n FROM bookings ${where}`,
  );
  return num(rows[0]?.n);
}

export async function findBookingByIdMysql(
  id: string,
  opts?: { includeDeleted?: boolean },
): Promise<BookingRow | null> {
  await ensure();
  const deletedClause = opts?.includeDeleted ? "" : "AND deleted_at IS NULL";
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM bookings WHERE id = ? ${deletedClause} LIMIT 1`,
    [id],
  );
  return rows[0] ? map(rows[0]) : null;
}

export async function findBookingsByIdsMysql(
  ids: string[],
  opts?: { includeDeleted?: boolean },
): Promise<BookingRow[]> {
  await ensure();
  if (!ids.length) return [];
  const deletedClause = opts?.includeDeleted ? "" : "AND deleted_at IS NULL";
  const placeholders = ids.map(() => "?").join(",");
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM bookings WHERE id IN (${placeholders}) ${deletedClause}`,
    ids,
  );
  return rows.map(map);
}

export async function listBookingsMysql(opts: {
  customerId?: string;
  ownerId?: string;
  propertyId?: string;
  status?: string | string[];
  paymentStatus?: string | string[];
  hasPayment?: boolean;
  take?: number;
  skip?: number;
  includeDeleted?: boolean;
}): Promise<BookingRow[]> {
  await ensure();
  const where: string[] = [];
  const params: unknown[] = [];
  if (!opts.includeDeleted) where.push("deleted_at IS NULL");
  if (opts.customerId) {
    where.push("customer_id = ?");
    params.push(opts.customerId);
  }
  if (opts.ownerId) {
    where.push("owner_id = ?");
    params.push(opts.ownerId);
  }
  if (opts.propertyId) {
    where.push("property_id = ?");
    params.push(opts.propertyId);
  }
  if (opts.status) {
    const statuses = Array.isArray(opts.status) ? opts.status : [opts.status];
    if (statuses.length === 1) {
      where.push("status = ?");
      params.push(statuses[0]);
    } else if (statuses.length > 1) {
      where.push(`status IN (${statuses.map(() => "?").join(",")})`);
      params.push(...statuses);
    }
  }
  if (opts.paymentStatus) {
    const statuses = Array.isArray(opts.paymentStatus) ? opts.paymentStatus : [opts.paymentStatus];
    if (statuses.length === 1) {
      where.push("payment_status = ?");
      params.push(statuses[0]);
    } else if (statuses.length > 1) {
      where.push(`payment_status IN (${statuses.map(() => "?").join(",")})`);
      params.push(...statuses);
    }
  }
  if (opts.hasPayment) {
    where.push("payment IS NOT NULL");
  }
  const take = Math.min(Math.max(opts.take ?? 50, 1), 500);
  const skip = Math.max(opts.skip ?? 0, 0);
  params.push(take, skip);
  const sql = `SELECT * FROM bookings
    ${where.length ? `WHERE ${where.join(" AND ")}` : ""}
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?`;
  const rows = await sqlQuery<RowDataPacket[]>(sql, params);
  return rows.map(map);
}

/** Drop the customer's own abandoned/active quotes for this property so they can re-book. */
export async function clearCustomerPendingQuotesMysql(
  customerId: string,
  propertyId: string,
) {
  await ensure();
  await sqlExecute(
    `UPDATE bookings SET status = 'EXPIRED', updated_at = ?
     WHERE customer_id = ? AND property_id = ? AND status = 'PENDING_PAYMENT' AND deleted_at IS NULL`,
    [new Date(), customerId, propertyId],
  );
}

export async function expireStaleQuotesMysql(propertyId?: string) {
  await ensure();
  if (propertyId) {
    await sqlExecute(
      `UPDATE bookings SET status = 'EXPIRED', updated_at = ?
       WHERE status = 'PENDING_PAYMENT' AND deleted_at IS NULL
         AND exchange_rate_expires_at < ? AND property_id = ?`,
      [new Date(), new Date(), propertyId],
    );
  } else {
    await sqlExecute(
      `UPDATE bookings SET status = 'EXPIRED', updated_at = ?
       WHERE status = 'PENDING_PAYMENT' AND deleted_at IS NULL
         AND exchange_rate_expires_at < ?`,
      [new Date(), new Date()],
    );
  }
}

export async function findBlockingBookingMysql(opts: {
  propertyId: string;
  checkIn: Date;
  checkOut: Date;
  excludeCustomerId?: string;
}): Promise<BookingRow | null> {
  await ensure();
  await expireStaleQuotesMysql(opts.propertyId);
  const params: unknown[] = [
    opts.propertyId,
    opts.checkOut,
    opts.checkIn,
    new Date(),
  ];
  let exclude = "";
  if (opts.excludeCustomerId) {
    exclude = "AND customer_id <> ?";
    params.push(opts.excludeCustomerId);
  }
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM bookings
     WHERE deleted_at IS NULL
       AND property_id = ?
       AND check_in < ?
       AND check_out > ?
       AND (
         status IN ('WAITING_OWNER','CONFIRMED')
         OR (status = 'PENDING_PAYMENT' AND exchange_rate_expires_at >= ?)
       )
       ${exclude}
     LIMIT 1`,
    params,
  );
  return rows[0] ? map(rows[0]) : null;
}

export async function busyPropertyIdsFromBookingsMysql(
  checkIn: Date,
  checkOut: Date,
): Promise<string[]> {
  await ensure();
  await expireStaleQuotesMysql();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT DISTINCT property_id AS propertyId FROM bookings
     WHERE deleted_at IS NULL
       AND check_in < ?
       AND check_out > ?
       AND (
         status IN ('WAITING_OWNER','CONFIRMED')
         OR (status = 'PENDING_PAYMENT' AND exchange_rate_expires_at >= ?)
       )`,
    [checkOut, checkIn, new Date()],
  );
  return rows.map((r) => String(r.propertyId));
}

export async function listBookingDateRangesMysql(
  propertyId: string,
  from: Date,
  to: Date,
): Promise<Array<{ checkIn: Date; checkOut: Date }>> {
  await ensure();
  await expireStaleQuotesMysql(propertyId);
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT check_in, check_out FROM bookings
     WHERE deleted_at IS NULL
       AND property_id = ?
       AND check_in < ?
       AND check_out > ?
       AND (
         status IN ('WAITING_OWNER','CONFIRMED')
         OR (status = 'PENDING_PAYMENT' AND exchange_rate_expires_at >= ?)
       )`,
    [propertyId, to, from, new Date()],
  );
  return rows.map((r) => ({
    checkIn: new Date(r.check_in),
    checkOut: new Date(r.check_out),
  }));
}