/**
 * Wallets / wallet_txns / wallet_topups MySQL adapter — READ ONLY.
 * No credit/debit/create until an explicit write cutover is approved.
 */
import type { RowDataPacket } from "mysql2/promise";
import { connectMysql, mysqlPool, sqlQuery } from "./pool";

export type WalletRow = {
  id: string;
  userId: string;
  balanceLyd: number;
  createdAt: Date;
  updatedAt: Date;
};

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

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

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 mapWallet(r: RowDataPacket): WalletRow {
  return {
    id: String(r.id),
    userId: String(r.user_id),
    balanceLyd: num(r.balance_lyd),
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

function mapTxn(r: RowDataPacket): WalletTxnRow {
  return {
    id: String(r.id),
    walletId: String(r.wallet_id),
    userId: String(r.user_id),
    type: String(r.type),
    amountLyd: num(r.amount_lyd),
    balanceAfter: num(r.balance_after),
    bookingId: r.booking_id == null ? null : String(r.booking_id),
    topUpId: r.top_up_id == null ? null : String(r.top_up_id),
    note: r.note == null ? null : String(r.note),
    meta: parseJson(r.meta),
    createdAt: new Date(r.created_at),
  };
}

function mapTopUp(r: RowDataPacket): WalletTopUpRow {
  return {
    id: String(r.id),
    userId: String(r.user_id),
    amountLyd: num(r.amount_lyd),
    bankName: String(r.bank_name),
    reference: String(r.reference),
    status: String(r.status),
    reviewedBy: r.reviewed_by == null ? null : String(r.reviewed_by),
    reviewedAt: r.reviewed_at ? new Date(r.reviewed_at) : null,
    reviewNote: r.review_note == null ? null : String(r.review_note),
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

export function walletToApi(w: WalletRow) {
  return {
    _id: w.id,
    id: w.id,
    userId: w.userId,
    balanceLyd: w.balanceLyd,
    createdAt: w.createdAt,
    updatedAt: w.updatedAt,
  };
}

export function walletTxnToApi(t: WalletTxnRow) {
  return {
    _id: t.id,
    id: t.id,
    walletId: t.walletId,
    userId: t.userId,
    type: t.type,
    amountLyd: t.amountLyd,
    balanceAfter: t.balanceAfter,
    bookingId: t.bookingId ?? undefined,
    topUpId: t.topUpId ?? undefined,
    note: t.note ?? undefined,
    meta: t.meta ?? undefined,
    createdAt: t.createdAt,
  };
}

export function walletTopUpToApi(t: WalletTopUpRow) {
  return {
    _id: t.id,
    id: t.id,
    userId: t.userId,
    amountLyd: t.amountLyd,
    bankName: t.bankName,
    reference: t.reference,
    status: t.status,
    reviewedBy: t.reviewedBy ?? undefined,
    reviewedAt: t.reviewedAt ?? undefined,
    reviewNote: t.reviewNote ?? undefined,
    createdAt: t.createdAt,
    updatedAt: t.updatedAt,
  };
}

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

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

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

export async function findWalletByUserIdMysql(userId: string): Promise<WalletRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM wallets WHERE user_id = ? LIMIT 1`,
    [userId],
  );
  return rows[0] ? mapWallet(rows[0]) : null;
}

export async function listWalletTxnsMysql(opts: {
  userId?: string;
  walletId?: string;
  take?: number;
  skip?: number;
}): Promise<WalletTxnRow[]> {
  await ensure();
  const where: string[] = [];
  const params: unknown[] = [];
  if (opts.userId) {
    where.push("user_id = ?");
    params.push(opts.userId);
  }
  if (opts.walletId) {
    where.push("wallet_id = ?");
    params.push(opts.walletId);
  }
  const take = Math.min(Math.max(opts.take ?? 50, 1), 200);
  const skip = Math.max(opts.skip ?? 0, 0);
  params.push(take, skip);
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM wallet_txns
     ${where.length ? `WHERE ${where.join(" AND ")}` : ""}
     ORDER BY created_at DESC
     LIMIT ? OFFSET ?`,
    params,
  );
  return rows.map(mapTxn);
}

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

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

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

export async function listWalletsMysql(opts?: {
  take?: number;
  skip?: number;
}): Promise<WalletRow[]> {
  await ensure();
  const take = Math.min(Math.max(opts?.take ?? 100, 1), 200);
  const skip = Math.max(opts?.skip ?? 0, 0);
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM wallets ORDER BY updated_at DESC LIMIT ? OFFSET ?`,
    [take, skip],
  );
  return rows.map(mapWallet);
}