/**
 * Inspect/create empty MySQL database for migration (no DROP/TRUNCATE).
 * Uses backend/.env; never prints password.
 */
import fs from "fs";
import path from "path";
import mysql from "mysql2/promise";

function loadEnv() {
  const envPath = path.join(process.cwd(), ".env");
  const text = fs.readFileSync(envPath, "utf8");
  for (const line of text.split(/\r?\n/)) {
    const t = line.trim();
    if (!t || t.startsWith("#")) continue;
    const eq = t.indexOf("=");
    if (eq <= 0) continue;
    const key = t.slice(0, eq).trim();
    let val = t.slice(eq + 1).trim();
    if (
      (val.startsWith('"') && val.endsWith('"')) ||
      (val.startsWith("'") && val.endsWith("'"))
    ) {
      val = val.slice(1, -1).replace(/\\"/g, '"').replace(/\\\\/g, "\\");
    }
    // File wins — do not keep empty shell overrides for MYSQL_*
    process.env[key] = val;
  }
}

function setEnvKey(key: string, value: string) {
  const envPath = path.join(process.cwd(), ".env");
  let text = fs.readFileSync(envPath, "utf8");
  const line = `${key}="${value.replace(/\\/g, "\\\\").replace(/"/g, '\\"')}"`;
  const re = new RegExp(`^${key}=.*$`, "m");
  if (re.test(text)) text = text.replace(re, line);
  else text += `\n${line}\n`;
  fs.writeFileSync(envPath, text, "utf8");
}

async function tableCount(conn: mysql.Connection, db: string) {
  const [rows] = await conn.query(
    `SELECT COUNT(*) AS c FROM information_schema.tables WHERE table_schema = ? AND table_type = 'BASE TABLE'`,
    [db],
  );
  return Number((rows as Array<{ c: number }>)[0].c);
}

async function main() {
  loadEnv();
  const host = process.env.MYSQL_HOST || "127.0.0.1";
  const port = Number(process.env.MYSQL_PORT || 3306);
  const user = process.env.MYSQL_USER || "root";
  const password = process.env.MYSQL_PASSWORD ?? "";
  const preferred = process.env.MYSQL_DATABASE || "safar_libya";

  const conn = await mysql.createConnection({
    host,
    port,
    user,
    password,
    multipleStatements: false,
  });

  console.log(`Connected MySQL as ${user}@${host}:${port}`);

  const [dbs] = await conn.query("SHOW DATABASES");
  const names = (dbs as Array<Record<string, string>>).map(
    (r) => r.Database ?? Object.values(r)[0],
  );
  console.log(`databases_visible=${names.length}`);

  let chosen = preferred;
  let reason = "";

  if (!names.includes(preferred)) {
    await conn.query(
      `CREATE DATABASE \`${preferred}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`,
    );
    chosen = preferred;
    reason = "created_new_empty";
    console.log(`Created database ${preferred}`);
  } else {
    const c = await tableCount(conn, preferred);
    if (c === 0) {
      chosen = preferred;
      reason = "existing_empty";
      console.log(`Using existing empty database ${preferred}`);
    } else {
      const stamp = new Date()
        .toISOString()
        .replace(/[-:TZ.]/g, "")
        .slice(0, 14);
      chosen = `safar_libya_test_${stamp}`;
      await conn.query(
        `CREATE DATABASE \`${chosen}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`,
      );
      setEnvKey("MYSQL_DATABASE", chosen);
      reason = `preferred_had_${c}_tables_created_test_db`;
      console.log(
        `Preferred ${preferred} has ${c} tables — created empty ${chosen} and updated .env`,
      );
    }
  }

  const finalCount = await tableCount(conn, chosen);
  if (finalCount !== 0) {
    throw new Error(`Chosen database ${chosen} is not empty (tables=${finalCount})`);
  }

  await conn.end();
  console.log(JSON.stringify({ chosenDatabase: chosen, reason, empty: true }));
}

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