import mysql from "mysql2/promise";
import dotenv from "dotenv";
import path from "path";
import { fileURLToPath } from "url";

const __dirname = path.dirname(fileURLToPath(import.meta.url));
dotenv.config({ path: path.resolve(__dirname, "../../.env") });

async function testConnection() {
  console.log("Connecting to:", process.env.DB_NAME);
  try {
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST || "127.0.0.1",
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || "root",
      password: process.env.DB_PASSWORD || "",
    });

    const [dbRows] = await connection.execute("SHOW DATABASES LIKE 'e_notary'");
    if (dbRows.length > 0) {
      console.log("SUCCESS: Database 'e_notary' exists.");
      await connection.query("USE e_notary");
      const [tableRows] = await connection.execute("SHOW TABLES");
      console.log("Tables in e_notary:", tableRows.map(r => Object.values(r)[0]));
    } else {
      console.log("FAILURE: Database 'e_notary' NOT found.");
    }
    await connection.end();
  } catch (err) {
    console.error("ERROR connecting to MySQL:", err.message);
  }
}

testConnection();
