/**
 * Apply schema.sql to MYSQL_DATABASE (empty). No DROP/TRUNCATE.
 * 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, "\\");
    }
    process.env[key] = val;
  }
}

function splitSqlStatements(sql: string): string[] {
  // Strip line comments; keep statements separated by ;
  const lines = sql.split(/\r?\n/).map((l) => {
    const i = l.indexOf("--");
    if (i >= 0) return l.slice(0, i);
    return l;
  });
  const cleaned = lines.join("\n");
  return cleaned
    .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 = fs.existsSync(path.join(process.cwd(), "schema.sql"))
    ? path.join(process.cwd(), "schema.sql")
    : path.join(process.cwd(), "scripts", "schema.sql");

  const sql = fs.readFileSync(schemaPath, "utf8");
  const createCount = (sql.match(/^CREATE TABLE/gim) || []).length;
  console.log(`schema_file=${schemaPath}`);
  console.log(`create_table_count_in_file=${createCount}`);

  const citiesBlock = sql.match(/CREATE TABLE\s+cities\s*\([\s\S]*?\)\s*ENGINE/i)?.[0] || "";
  const hasCityAliases = /CREATE TABLE\s+city_aliases/i.test(sql);
  const citiesHasAliasesCol = /^\s*aliases\s+/im.test(citiesBlock);
  console.log(`has_city_aliases=${hasCityAliases}`);
  console.log(`cities_has_aliases_column=${citiesHasAliasesCol}`);

  if (!hasCityAliases) throw new Error("city_aliases missing from schema");
  if (citiesHasAliasesCol) throw new Error("cities still has aliases column");
  if (createCount !== 30) {
    console.warn(`WARNING: expected 30 CREATE TABLE, found ${createCount}`);
  }

  const conn = await mysql.createConnection({
    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,
    multipleStatements: false,
    charset: "utf8mb4",
  });

  console.log(`Applying to database=${process.env.MYSQL_DATABASE}`);

  const stmts = splitSqlStatements(sql);
  let applied = 0;
  for (const stmt of stmts) {
    try {
      await conn.query(stmt);
      applied++;
      if (/^CREATE TABLE/i.test(stmt)) {
        const m = stmt.match(/CREATE TABLE\s+`?(\w+)`?/i);
        console.log(`created ${m?.[1] || "?"}`);
      }
    } catch (e) {
      console.error(`Failed statement #${applied + 1}: ${e instanceof Error ? e.message : e}`);
      console.error(stmt.slice(0, 200));
      await conn.end();
      process.exit(1);
    }
  }

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

  const [cityCols] = await conn.query(`SHOW COLUMNS FROM cities`);
  const colNames = (cityCols as Array<{ Field: string }>).map((c) => c.Field);
  console.log(`cities_columns=${colNames.join(",")}`);
  console.log(`cities_has_aliases=${colNames.includes("aliases")}`);

  const [aliasesOk] = await conn.query(`SHOW TABLES LIKE 'city_aliases'`);
  console.log(`city_aliases_exists=${(aliasesOk as unknown[]).length > 0}`);

  const [fks] = await conn.query(
    `SELECT COUNT(*) AS c FROM information_schema.table_constraints
     WHERE table_schema = DATABASE() AND constraint_type = 'FOREIGN KEY'`,
  );
  console.log(`foreign_key_count=${(fks as Array<{ c: number }>)[0].c}`);

  const [idxs] = await conn.query(
    `SELECT COUNT(*) AS c FROM information_schema.statistics WHERE table_schema = DATABASE()`,
  );
  console.log(`index_row_count=${(idxs as Array<{ c: number }>)[0].c}`);

  await conn.end();
  console.log("SCHEMA_APPLY_OK");
}

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