import "dotenv/config";
import mysql from "mysql2/promise";
import fs from "fs";

(async () => {
  try {
    const conn = await mysql.createConnection({
      host: process.env.DB_HOST || "localhost",
      user: process.env.DB_USER || "root",
      password: process.env.DB_PASSWORD || undefined,
      database: process.env.DB_NAME || "tuc",
    });

    const sqlFile = fs.readFileSync("./create-missing-tables.sql", "utf8");
    const statements = sqlFile.split(";").filter((s) => s.trim());

    console.log("\n🔧 Creating missing tables...\n");

    for (const statement of statements) {
      if (statement.trim()) {
        try {
          await conn.execute(statement);
          // Regex to match table name, handling optional backticks and IF NOT EXISTS
          const tableNameMatch = statement.match(
            /CREATE TABLE (?:IF NOT EXISTS )?`?(\w+)`?/
          );
          const tableName = tableNameMatch ? tableNameMatch[1] : "unknown";
          console.log(`✅ Created table: ${tableName}`);
        } catch (err: any) {
          if (err.code === "ER_TABLE_EXISTS_ERROR") {
            const tableNameMatch = statement.match(
              /CREATE TABLE (?:IF NOT EXISTS )?`?(\w+)`?/
            );
            const tableName = tableNameMatch ? tableNameMatch[1] : "unknown";
            console.log(`⚠️  Table already exists: ${tableName}`);
          } else {
            console.error(`❌ Error: ${err.message}`);
          }
        }
      }
    }

    // Verify all tables
    const [tables] = await conn.execute("SHOW TABLES");
    console.log("\n📊 FINAL TABLE COUNT: " + (tables as any[]).length);
    console.log("\n✅ All tables created successfully!");

    await conn.end();
  } catch (error: any) {
    console.error("❌ Connection Error:", error.message);
    process.exit(1);
  }
})();
