/**
 * Ledger entries MySQL adapter — READ ONLY.
 *
 * Duplicate BOOKING_PAYMENT pair resolved: A (...df805d) VOID, B (...cb531) POSTED canonical.
 * See backend/logs/ledger-duplicate-gate.md — do not auto-reopen without human decision.
 */
import type { RowDataPacket } from "mysql2/promise";
import { connectMysql, mysqlPool, sqlQuery } from "./pool";

export type LedgerEntryRow = {
  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;
  bookingPaymentKey: string | null;
  withdrawalKey: 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): LedgerEntryRow {
  return {
    id: String(r.id),
    type: String(r.type),
    direction: String(r.direction),
    bookingId: r.booking_id == null ? null : String(r.booking_id),
    refundId: r.refund_id == null ? null : String(r.refund_id),
    withdrawalId: r.withdrawal_id == null ? null : String(r.withdrawal_id),
    amountLyd: num(r.amount_lyd),
    amountTnd: num(r.amount_tnd),
    partyUserId: r.party_user_id == null ? null : String(r.party_user_id),
    partyRole: String(r.party_role),
    status: String(r.status),
    meta: parseJson(r.meta),
    bookingPaymentKey:
      r.booking_payment_key == null ? null : String(r.booking_payment_key),
    withdrawalKey: r.withdrawal_key == null ? null : String(r.withdrawal_key),
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

export function ledgerEntryToApi(e: LedgerEntryRow) {
  return {
    _id: e.id,
    id: e.id,
    type: e.type,
    direction: e.direction,
    bookingId: e.bookingId ?? undefined,
    refundId: e.refundId ?? undefined,
    withdrawalId: e.withdrawalId ?? undefined,
    amountLyd: e.amountLyd,
    amountTnd: e.amountTnd,
    partyUserId: e.partyUserId ?? undefined,
    partyRole: e.partyRole,
    status: e.status,
    meta: e.meta ?? undefined,
    createdAt: e.createdAt,
    updatedAt: e.updatedAt,
  };
}

export async function countLedgerEntriesMysql(opts?: {
  includeVoid?: boolean;
}): Promise<number> {
  await ensure();
  const where = opts?.includeVoid ? "" : "WHERE status = 'POSTED'";
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT COUNT(*) AS n FROM ledger_entries ${where}`,
  );
  return num(rows[0]?.n);
}

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

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

export async function sumLedgerByTypeMysql(): Promise<
  Array<{ type: string; count: number; amountLyd: number; amountTnd: number }>
> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT type,
            COUNT(*) AS count,
            COALESCE(SUM(amount_lyd), 0) AS amountLyd,
            COALESCE(SUM(amount_tnd), 0) AS amountTnd
     FROM ledger_entries
     WHERE status = 'POSTED'
     GROUP BY type
     ORDER BY type`,
  );
  return rows.map((r) => ({
    type: String(r.type),
    count: num(r.count),
    amountLyd: num(r.amountLyd),
    amountTnd: num(r.amountTnd),
  }));
}

/** Documented duplicate pair — read helper for recon only. */
export async function listDuplicateBookingPaymentsMysql(
  bookingId: string,
): Promise<LedgerEntryRow[]> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM ledger_entries
     WHERE booking_id = ? AND type = 'BOOKING_PAYMENT' AND status = 'POSTED'
     ORDER BY created_at ASC`,
    [bookingId],
  );
  return rows.map(map);
}