/**
 * Refunds MySQL adapter — READ ONLY.
 * Writes stay Mongo-primary + financial dual-write until write cutover.
 */
import type { RowDataPacket } from "mysql2/promise";
import { connectMysql, mysqlPool, sqlQuery } from "./pool";

export type RefundRow = {
  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;
};

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 map(r: RowDataPacket): RefundRow {
  return {
    id: String(r.id),
    bookingId: String(r.booking_id),
    customerId: String(r.customer_id),
    ownerId: String(r.owner_id),
    type: String(r.type),
    amountLyd: num(r.amount_lyd),
    amountTnd: num(r.amount_tnd),
    reasonCode: String(r.reason_code),
    reasonNote: r.reason_note == null ? null : String(r.reason_note),
    platformFeeClawbackTnd: num(r.platform_fee_clawback_tnd),
    ownerPayoutClawbackTnd: num(r.owner_payout_clawback_tnd),
    createdBy: r.created_by == null ? null : String(r.created_by),
    walletCreditedLyd: num(r.wallet_credited_lyd),
    source: String(r.source),
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

export function refundToApi(r: RefundRow) {
  return {
    _id: r.id,
    id: r.id,
    bookingId: r.bookingId,
    customerId: r.customerId,
    ownerId: r.ownerId,
    type: r.type,
    amountLyd: r.amountLyd,
    amountTnd: r.amountTnd,
    reasonCode: r.reasonCode,
    reasonNote: r.reasonNote ?? undefined,
    platformFeeClawbackTnd: r.platformFeeClawbackTnd,
    ownerPayoutClawbackTnd: r.ownerPayoutClawbackTnd,
    createdBy: r.createdBy ?? undefined,
    walletCreditedLyd: r.walletCreditedLyd,
    source: r.source,
    createdAt: r.createdAt,
    updatedAt: r.updatedAt,
  };
}

export async function countRefundsMysql(): Promise<number> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT COUNT(*) AS n FROM refunds`,
  );
  return num(rows[0]?.n);
}

export async function findRefundByIdMysql(id: string): Promise<RefundRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM refunds WHERE id = ? LIMIT 1`,
    [id],
  );
  return rows[0] ? map(rows[0]) : null;
}

export async function listRefundsMysql(opts?: {
  bookingId?: string;
  take?: number;
  skip?: number;
}): Promise<RefundRow[]> {
  await ensure();
  const where: string[] = [];
  const params: unknown[] = [];
  if (opts?.bookingId) {
    where.push("booking_id = ?");
    params.push(opts.bookingId);
  }
  const take = Math.min(Math.max(opts?.take ?? 200, 1), 5000);
  const skip = Math.max(opts?.skip ?? 0, 0);
  params.push(take, skip);
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM refunds
     ${where.length ? `WHERE ${where.join(" AND ")}` : ""}
     ORDER BY created_at DESC
     LIMIT ? OFFSET ?`,
    params,
  );
  return rows.map(map);
}

export async function sumRefundsMysql(): Promise<{
  amountLyd: number;
  amountTnd: number;
  platformFeeClawbackTnd: number;
  count: number;
}> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT COUNT(*) AS count,
            COALESCE(SUM(amount_lyd), 0) AS amountLyd,
            COALESCE(SUM(amount_tnd), 0) AS amountTnd,
            COALESCE(SUM(platform_fee_clawback_tnd), 0) AS platformFeeClawbackTnd
     FROM refunds`,
  );
  return {
    count: num(rows[0]?.count),
    amountLyd: num(rows[0]?.amountLyd),
    amountTnd: num(rows[0]?.amountTnd),
    platformFeeClawbackTnd: num(rows[0]?.platformFeeClawbackTnd),
  };
}