/**
 * Withdrawal requests 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 WithdrawalRow = {
  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;
};

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): WithdrawalRow {
  return {
    id: String(r.id),
    ownerId: String(r.owner_id),
    amountTnd: num(r.amount_tnd),
    amountLyd: num(r.amount_lyd),
    exchangeRateRate: num(r.exchange_rate_rate),
    method: String(r.method),
    status: String(r.status),
    note: r.note == null ? null : String(r.note),
    rejectionReason: r.rejection_reason == null ? null : String(r.rejection_reason),
    reviewedBy: r.reviewed_by == null ? null : String(r.reviewed_by),
    reviewedAt: r.reviewed_at ? new Date(r.reviewed_at) : null,
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

export function withdrawalToApi(w: WithdrawalRow) {
  return {
    _id: w.id,
    id: w.id,
    ownerId: w.ownerId,
    amountTnd: w.amountTnd,
    amountLyd: w.amountLyd,
    exchangeRateRate: w.exchangeRateRate,
    method: w.method,
    status: w.status,
    note: w.note ?? undefined,
    rejectionReason: w.rejectionReason ?? undefined,
    reviewedBy: w.reviewedBy ?? undefined,
    reviewedAt: w.reviewedAt ?? undefined,
    createdAt: w.createdAt,
    updatedAt: w.updatedAt,
  };
}

export async function countWithdrawalsMysql(opts?: {
  status?: string;
  ownerId?: string;
}): Promise<number> {
  await ensure();
  const where: string[] = [];
  const params: unknown[] = [];
  if (opts?.status) {
    where.push("status = ?");
    params.push(opts.status);
  }
  if (opts?.ownerId) {
    where.push("owner_id = ?");
    params.push(opts.ownerId);
  }
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT COUNT(*) AS n FROM withdrawal_requests
     ${where.length ? `WHERE ${where.join(" AND ")}` : ""}`,
    params,
  );
  return num(rows[0]?.n);
}

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

export async function listWithdrawalsMysql(opts?: {
  status?: string;
  ownerId?: string;
  statuses?: string[];
  take?: number;
  skip?: number;
}): Promise<WithdrawalRow[]> {
  await ensure();
  const where: string[] = [];
  const params: unknown[] = [];
  if (opts?.status) {
    where.push("status = ?");
    params.push(opts.status);
  }
  if (opts?.statuses?.length) {
    where.push(`status IN (${opts.statuses.map(() => "?").join(",")})`);
    params.push(...opts.statuses);
  }
  if (opts?.ownerId) {
    where.push("owner_id = ?");
    params.push(opts.ownerId);
  }
  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 withdrawal_requests
     ${where.length ? `WHERE ${where.join(" AND ")}` : ""}
     ORDER BY created_at DESC
     LIMIT ? OFFSET ?`,
    params,
  );
  return rows.map(map);
}

export async function sumWithdrawalsMysql(opts?: {
  statuses?: string[];
}): Promise<{ amountLyd: number; amountTnd: number; count: number }> {
  await ensure();
  const where: string[] = [];
  const params: unknown[] = [];
  if (opts?.statuses?.length) {
    where.push(`status IN (${opts.statuses.map(() => "?").join(",")})`);
    params.push(...opts.statuses);
  }
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT COUNT(*) AS count,
            COALESCE(SUM(amount_lyd), 0) AS amountLyd,
            COALESCE(SUM(amount_tnd), 0) AS amountTnd
     FROM withdrawal_requests
     ${where.length ? `WHERE ${where.join(" AND ")}` : ""}`,
    params,
  );
  return {
    count: num(rows[0]?.count),
    amountLyd: num(rows[0]?.amountLyd),
    amountTnd: num(rows[0]?.amountTnd),
  };
}