/**
 * Report-only: optional booking_id NULL rows + Mongo bookingId provenance.
 */
import fs from "fs";
import path from "path";
import mysql from "mysql2/promise";
import mongoose from "mongoose";

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;
  }
}

const OPTIONAL_BOOKING_FK_TABLES: Array<{
  table: string;
  mongoCollection: string;
  mongoField: string;
}> = [
  { table: "ledger_entries", mongoCollection: "ledgerentries", mongoField: "bookingId" },
  { table: "wallet_txns", mongoCollection: "wallettxns", mongoField: "bookingId" },
  { table: "loyalty_txns", mongoCollection: "loyaltytxns", mongoField: "bookingId" },
];

type Class =
  | "natural_missing_in_mongo"
  | "bug_undefined_string"
  | "bug_null_string"
  | "orphan_missing_booking_doc"
  | "mysql_only_no_mongo"
  | "unknown";

async function main() {
  loadEnv();
  const dropTest = process.argv.includes("--drop-test");

  const conn = await mysql.createConnection({
    host: process.env.MYSQL_HOST,
    port: Number(process.env.MYSQL_PORT),
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
  });

  await mongoose.connect(process.env.MONGODB_URI!);
  const db = mongoose.connection.db!;

  const report: {
    database: string | undefined;
    generatedAt: string;
    note: string;
    tables: unknown[];
    ledger_entries_test?: unknown;
  } = {
    database: process.env.MYSQL_DATABASE,
    generatedAt: new Date().toISOString(),
    note: "Only tables with nullable booking_id FK. reviews/refunds/coupon_redemptions require booking_id NOT NULL.",
    tables: [],
  };

  for (const t of OPTIONAL_BOOKING_FK_TABLES) {
    const [colInfo] = await conn.query(
      `SELECT IS_NULLABLE FROM information_schema.COLUMNS
       WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = 'booking_id'`,
      [t.table],
    );
    if ((colInfo as unknown[]).length === 0) {
      report.tables.push({ table: t.table, skipped: "no booking_id column" });
      continue;
    }

    const [typeCol] = await conn.query(
      `SELECT 1 AS ok FROM information_schema.COLUMNS
       WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = 'type' LIMIT 1`,
      [t.table],
    );
    const hasType = (typeCol as unknown[]).length > 0;
    const select = hasType
      ? `SELECT id, booking_id, type, created_at FROM \`${t.table}\` WHERE booking_id IS NULL ORDER BY created_at, id`
      : `SELECT id, booking_id, created_at FROM \`${t.table}\` WHERE booking_id IS NULL ORDER BY created_at, id`;

    const [nullRows] = await conn.query(select);
    const rows = nullRows as Array<Record<string, unknown>>;

    const detailed: Array<{
      id: string;
      mysql_booking_id: unknown;
      type: unknown;
      created_at: unknown;
      mongo_bookingId: unknown;
      classification: Class;
    }> = [];
    for (const r of rows) {
      const id = String(r.id);
      const mongo = await db.collection(t.mongoCollection).findOne({ _id: id as never });
      let classification: Class = "unknown";
      let mongoBookingId: unknown = undefined;
      let mongoHasOwnProperty = false;

      if (!mongo) {
        classification = "mysql_only_no_mongo";
      } else {
        const doc = mongo as Record<string, unknown>;
        mongoHasOwnProperty = Object.prototype.hasOwnProperty.call(doc, t.mongoField);
        mongoBookingId = doc[t.mongoField];

        if (!mongoHasOwnProperty || mongoBookingId === undefined || mongoBookingId === null) {
          classification = "natural_missing_in_mongo";
        } else if (String(mongoBookingId) === "undefined") {
          classification = "bug_undefined_string";
        } else if (String(mongoBookingId) === "null") {
          classification = "bug_null_string";
        } else {
          const bookingExists = await db
            .collection("bookings")
            .findOne({ _id: String(mongoBookingId) as never });
          classification = bookingExists ? "unknown" : "orphan_missing_booking_doc";
        }
      }

      detailed.push({
        id,
        mysql_booking_id: r.booking_id,
        type: hasType ? (r.type ?? null) : null,
        created_at: r.created_at ?? null,
        mongo_bookingId: !mongo
          ? "(no mongo doc)"
          : !mongoHasOwnProperty
            ? "(field absent)"
            : mongoBookingId === undefined
              ? "(js undefined)"
              : mongoBookingId === null
                ? null
                : mongoBookingId,
        classification,
      });
    }

    const byClass: Record<string, number> = {};
    for (const d of detailed) {
      byClass[d.classification] = (byClass[d.classification] || 0) + 1;
    }

    report.tables.push({
      table: t.table,
      null_booking_id_count: detailed.length,
      by_classification: byClass,
      rows: detailed,
    });
  }

  const [testTables] = await conn.query(`SHOW TABLES LIKE 'ledger_entries_test'`);
  const testExists = (testTables as unknown[]).length > 0;
  let testRowCount = 0;
  if (testExists) {
    const [c] = await conn.query(`SELECT COUNT(*) AS c FROM ledger_entries_test`);
    testRowCount = Number((c as Array<{ c: number }>)[0].c);
  }

  report.ledger_entries_test = {
    exists: testExists,
    rowCount: testRowCount,
    code_references: [
      "backend/scripts/debug-ledger-fk2.ts (creator only)",
      "backend/logs/mysql-full-migration-report.md (mention only)",
    ],
    used_by_app: false,
  };

  if (dropTest && testExists) {
    await conn.query(`DROP TABLE IF EXISTS ledger_entries_test`);
    report.ledger_entries_test = {
      ...(report.ledger_entries_test as object),
      dropped: true,
    };
    console.log("DROPPED ledger_entries_test");
  }

  const out = path.join(process.cwd(), "logs", "null-booking-id-report.json");
  fs.mkdirSync(path.dirname(out), { recursive: true });
  fs.writeFileSync(out, JSON.stringify(report, null, 2), "utf8");
  console.log(JSON.stringify(report, null, 2));
  console.log(`Wrote ${out}`);

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

main().catch(async (e) => {
  console.error(e instanceof Error ? e.message : e);
  try {
    await mongoose.disconnect();
  } catch {
    /* ignore */
  }
  process.exit(1);
});