/**
 * Read-only dump of duplicate BOOKING_PAYMENT pair + related booking/payment/logs.
 * No writes.
 */
import fs from "fs";
import path from "path";
import mongoose from "mongoose";
import mysql from "mysql2/promise";

const BOOKING_ID = "cmrxh9kaje08c3e4d050caf7d245e";
const LEDGER_A = "cmrxh9kd4f7821148453e76df805d";
const LEDGER_B = "cmrxh9ko07d94b53da748e78cb531";

function loadEnv() {
  const t = fs.readFileSync(path.join(process.cwd(), ".env"), "utf8");
  for (const line of t.split(/\r?\n/)) {
    const s = line.trim();
    if (!s || s.startsWith("#")) continue;
    const eq = s.indexOf("=");
    if (eq <= 0) continue;
    const k = s.slice(0, eq).trim();
    let v = s.slice(eq + 1).trim();
    if (
      (v.startsWith('"') && v.endsWith('"')) ||
      (v.startsWith("'") && v.endsWith("'"))
    )
      v = v.slice(1, -1);
    process.env[k] = v;
  }
}

function diffKeys(a: any, b: any, prefix = ""): Array<{ path: string; a: unknown; b: unknown }> {
  const out: Array<{ path: string; a: unknown; b: unknown }> = [];
  const keys = new Set([...Object.keys(a || {}), ...Object.keys(b || {})]);
  for (const k of keys) {
    const pa = `${prefix}${k}`;
    const va = a?.[k];
    const vb = b?.[k];
    const bothObj =
      va &&
      vb &&
      typeof va === "object" &&
      typeof vb === "object" &&
      !Array.isArray(va) &&
      !Array.isArray(vb) &&
      !(va instanceof Date) &&
      !(vb instanceof Date);
    if (bothObj) {
      out.push(...diffKeys(va, vb, `${pa}.`));
    } else {
      const sa = JSON.stringify(va);
      const sb = JSON.stringify(vb);
      if (sa !== sb) out.push({ path: pa, a: va, b: vb });
    }
  }
  return out;
}

async function main() {
  loadEnv();
  await mongoose.connect(process.env.MONGODB_URI!);
  const db = mongoose.connection.db!;

  const pool = await mysql.createPool({
    host: process.env.MYSQL_HOST || "127.0.0.1",
    port: Number(process.env.MYSQL_PORT || 3306),
    user: process.env.MYSQL_USER || "root",
    password: process.env.MYSQL_PASSWORD ?? "",
    database: process.env.MYSQL_DATABASE || "safar_libya",
  });

  const mongoA = await db.collection("ledgerentries").findOne({ _id: LEDGER_A as any });
  const mongoB = await db.collection("ledgerentries").findOne({ _id: LEDGER_B as any });
  const mongoBooking = await db.collection("bookings").findOne({ _id: BOOKING_ID as any });

  const [mysqlRows] = await pool.query(
    `SELECT * FROM ledger_entries WHERE id IN (?, ?) ORDER BY created_at ASC`,
    [LEDGER_A, LEDGER_B],
  );
  const [mysqlBookingRows] = await pool.query(`SELECT * FROM bookings WHERE id = ?`, [
    BOOKING_ID,
  ]);

  // Related wallet txns for this booking
  const mongoWalletTxns = await db
    .collection("wallettxns")
    .find({ bookingId: BOOKING_ID })
    .toArray();
  const [mysqlWalletTxns] = await pool.query(
    `SELECT * FROM wallet_txns WHERE booking_id = ? ORDER BY created_at ASC`,
    [BOOKING_ID],
  );

  // Refunds for booking
  const mongoRefunds = await db.collection("refunds").find({ bookingId: BOOKING_ID }).toArray();
  const [mysqlRefunds] = await pool.query(
    `SELECT * FROM refunds WHERE booking_id = ? ORDER BY created_at ASC`,
    [BOOKING_ID],
  );

  // All ledger rows for booking
  const mongoAllLedger = await db
    .collection("ledgerentries")
    .find({ bookingId: BOOKING_ID })
    .sort({ createdAt: 1 })
    .toArray();
  const [mysqlAllLedger] = await pool.query(
    `SELECT id, type, direction, amount_lyd, amount_tnd, party_role, booking_payment_key, created_at
     FROM ledger_entries WHERE booking_id = ? ORDER BY created_at ASC`,
    [BOOKING_ID],
  );

  // Activity / audit collections if any
  const collNames = (await db.listCollections().toArray()).map((c) => c.name);
  const activityLike = collNames.filter((n) =>
    /activit|audit|log|event|request/i.test(n),
  );

  const tMin = new Date("2026-07-23T06:00:00.000Z");
  const tMax = new Date("2026-07-23T15:00:00.000Z");

  const activityHits: Record<string, unknown[]> = {};
  for (const name of activityLike) {
    const hits = await db
      .collection(name)
      .find({
        $or: [
          { bookingId: BOOKING_ID },
          { "meta.bookingId": BOOKING_ID },
          { entityId: BOOKING_ID },
          { resourceId: BOOKING_ID },
          { message: { $regex: BOOKING_ID } },
          { _id: { $in: [LEDGER_A, LEDGER_B] as any } },
          {
            entityType: /booking|payment|ledger|wallet/i,
            createdAt: { $gte: tMin, $lte: tMax },
          },
        ],
      })
      .limit(100)
      .toArray();
    if (hits.length) activityHits[name] = hits;
  }

  // Explicit activitylogs for this booking + nearby finance actions
  const activityExact = await db
    .collection("activitylogs")
    .find({
      $or: [
        { entityId: BOOKING_ID },
        { "meta.bookingId": BOOKING_ID },
        {
          createdAt: { $gte: tMin, $lte: tMax },
          action: { $regex: /pay|refund|book|wallet|ledger/i },
        },
      ],
    })
    .sort({ createdAt: 1 })
    .limit(100)
    .toArray();

  const [mysqlActivityExact] = await pool.query(
    `SELECT * FROM activity_logs
     WHERE entity_id = ?
        OR meta LIKE ?
        OR (created_at BETWEEN ? AND ? AND action REGEXP 'pay|refund|book|wallet|ledger')
     ORDER BY created_at ASC
     LIMIT 100`,
    [BOOKING_ID, `%${BOOKING_ID}%`, tMin, tMax],
  );

  // MySQL activity-like tables
  const [mysqlTables] = await pool.query(`SHOW TABLES`);
  const tableNames = (mysqlTables as any[]).map((r) => Object.values(r)[0] as string);
  const mysqlActivityTables = tableNames.filter((n) =>
    /activit|audit|log|event/i.test(n),
  );
  const mysqlActivityHits: Record<string, unknown[]> = {};
  for (const t of mysqlActivityTables) {
    try {
      const [cols] = await pool.query(`SHOW COLUMNS FROM \`${t}\``);
      const colNames = (cols as any[]).map((c) => String(c.Field));
      const searchable = colNames.filter((c) =>
        /booking|entity|resource|message|meta|payload|ref/i.test(c),
      );
      if (!searchable.length) continue;
      const ors = searchable.map((c) => `\`${c}\` LIKE ?`).join(" OR ");
      const [rows] = await pool.query(
        `SELECT * FROM \`${t}\` WHERE ${ors} LIMIT 50`,
        searchable.map(() => `%${BOOKING_ID}%`),
      );
      if ((rows as any[]).length) mysqlActivityHits[t] = rows as any[];
    } catch {
      /* skip */
    }
  }

  // Notifications mentioning booking
  const mongoNotifs = await db
    .collection("notifications")
    .find({
      $or: [
        { link: { $regex: BOOKING_ID } },
        { messageAr: { $regex: BOOKING_ID } },
        { messageEn: { $regex: BOOKING_ID } },
        { "meta.bookingId": BOOKING_ID },
      ],
    })
    .limit(20)
    .toArray();

  const aCreated = mongoA?.createdAt ? new Date(mongoA.createdAt).toISOString() : null;
  const bCreated = mongoB?.createdAt ? new Date(mongoB.createdAt).toISOString() : null;
  const deltaMs =
    aCreated && bCreated
      ? Math.abs(new Date(bCreated).getTime() - new Date(aCreated).getTime())
      : null;

  const report = {
    bookingId: BOOKING_ID,
    ledgerIds: { A: LEDGER_A, B: LEDGER_B },
    timing: {
      mongoA_createdAt: aCreated,
      mongoB_createdAt: bCreated,
      deltaMs,
      deltaSeconds: deltaMs != null ? deltaMs / 1000 : null,
      mongoA_updatedAt: mongoA?.updatedAt
        ? new Date(mongoA.updatedAt).toISOString()
        : null,
      mongoB_updatedAt: mongoB?.updatedAt
        ? new Date(mongoB.updatedAt).toISOString()
        : null,
    },
    mongo: {
      A: mongoA,
      B: mongoB,
      fieldDiffs: diffKeys(mongoA || {}, mongoB || {}),
    },
    mysql: {
      rows: mysqlRows,
    },
    booking: {
      mongo: mongoBooking,
      mysql: (mysqlBookingRows as any[])[0] || null,
    },
    related: {
      mongoWalletTxns,
      mysqlWalletTxns,
      mongoRefunds,
      mysqlRefunds,
      mongoAllLedgerForBooking: mongoAllLedger,
      mysqlAllLedgerForBooking: mysqlAllLedger,
      mongoNotifications: mongoNotifs,
    },
    activityDiscovery: {
      mongoCollectionsMatchingActivityPattern: activityLike,
      mongoActivityHits: activityHits,
      mongoActivityExact: activityExact,
      mysqlTablesMatchingActivityPattern: mysqlActivityTables,
      mysqlActivityHits,
      mysqlActivityExact,
    },
  };

  const outPath = path.join(process.cwd(), "logs", "ledger-duplicate-decision-facts.json");
  fs.writeFileSync(outPath, JSON.stringify(report, null, 2), "utf8");
  console.log(
    JSON.stringify(
      {
        wrote: outPath,
        timing: report.timing,
        fieldDiffPaths: report.mongo.fieldDiffs.map((d) => d.path),
        fieldDiffs: report.mongo.fieldDiffs,
        activityCollections: activityLike,
        activityHitCollections: Object.keys(activityHits),
        activityExactCount: activityExact.length,
        mysqlActivityTables,
        mysqlActivityHitTables: Object.keys(mysqlActivityHits),
        mysqlActivityExactCount: (mysqlActivityExact as any[]).length,
        walletTxnCount: mongoWalletTxns.length,
        walletTxnSummaries: mongoWalletTxns.map((t: any) => ({
          id: String(t._id),
          type: t.type,
          amountLyd: t.amountLyd,
          balanceAfter: t.balanceAfter,
          createdAt: t.createdAt ? new Date(t.createdAt).toISOString() : null,
          note: t.note,
          meta: t.meta,
        })),
        refundCount: mongoRefunds.length,
        ledgerForBookingCount: mongoAllLedger.length,
        ledgerForBooking: mongoAllLedger.map((e: any) => ({
          id: String(e._id),
          type: e.type,
          amountLyd: e.amountLyd,
          amountTnd: e.amountTnd,
          bookingId: e.bookingId,
          createdAt: e.createdAt ? new Date(e.createdAt).toISOString() : null,
        })),
        payment: (mongoBooking as any)?.payment || null,
        bookingStatus: (mongoBooking as any)?.status,
        bookingCreatedAt: (mongoBooking as any)?.createdAt
          ? new Date((mongoBooking as any).createdAt).toISOString()
          : null,
        bookingUpdatedAt: (mongoBooking as any)?.updatedAt
          ? new Date((mongoBooking as any).updatedAt).toISOString()
          : null,
        walletPaidLyd: (mongoBooking as any)?.walletPaidLyd,
      },
      null,
      2,
    ),
  );

  await pool.end();
  await mongoose.disconnect();
}

main().catch((e) => {
  console.error(e instanceof Error ? e.message : e);
  process.exit(1);
});