/**
 * Resume applying schema.sql: skip CREATE TABLE if table already exists.
 * No DROP/TRUNCATE. Never prints password.
 */
import fs from "fs";
import path from "path";
import mysql from "mysql2/promise";

function loadEnv() {
  const text = fs.readFileSync(path.join(process.cwd(), ".env"), "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);
    }
    process.env[key] = val;
  }
}

function splitSqlStatements(sql: string): string[] {
  const lines = sql.split(/\r?\n/).map((l) => {
    const i = l.indexOf("--");
    return i >= 0 ? l.slice(0, i) : l;
  });
  return lines
    .join("\n")
    .split(";")
    .map((s) => s.trim())
    .filter(
      (s) =>
        s.length > 0 &&
        !/^SET\s+/i.test(s) &&
        !/^USE\s+/i.test(s) &&
        !/^CREATE\s+DATABASE/i.test(s),
    );
}

async function main() {
  loadEnv();
  const schemaPath = path.join(process.cwd(), "schema.sql");
  const sql = fs.readFileSync(schemaPath, "utf8");
  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,
    charset: "utf8mb4",
  });

  const [existing] = await conn.query("SHOW TABLES");
  const have = new Set(
    (existing as Array<Record<string, string>>).map((t) => Object.values(t)[0]),
  );
  console.log(`existing_tables=${have.size}`);

  // Ensure ledger_entries has unique key columns if created without them
  if (have.has("ledger_entries")) {
    const [cols] = await conn.query(`SHOW COLUMNS FROM ledger_entries`);
    const names = new Set((cols as Array<{ Field: string }>).map((c) => c.Field));
    if (!names.has("booking_payment_key")) {
      await conn.query(
        `ALTER TABLE ledger_entries
         ADD COLUMN booking_payment_key VARCHAR(36) NULL,
         ADD COLUMN withdrawal_key VARCHAR(36) NULL,
         ADD UNIQUE KEY uk_ledger_booking_payment (booking_payment_key),
         ADD UNIQUE KEY uk_ledger_withdrawal (withdrawal_key)`,
      );
      console.log("altered ledger_entries: added unique key columns");
    }
  }

  for (const stmt of splitSqlStatements(sql)) {
    const m = stmt.match(/^CREATE TABLE\s+`?(\w+)`?/i);
    if (!m) {
      await conn.query(stmt);
      continue;
    }
    const table = m[1];
    if (have.has(table)) {
      console.log(`skip existing ${table}`);
      continue;
    }
    await conn.query(stmt);
    have.add(table);
    console.log(`created ${table}`);
  }

  const [tables] = await conn.query("SHOW TABLES");
  const names = (tables as Array<Record<string, string>>).map((t) => Object.values(t)[0]);
  console.log(`table_count=${names.length}`);
  console.log(`tables=${names.sort().join(",")}`);
  await conn.end();
  console.log("SCHEMA_RESUME_OK");
}

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