require("dotenv/config");
const mysql = require("mysql2/promise");
const fs = require("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");

    let createdCount = 0;
    let existingCount = 0;
    let errorCount = 0;

    for (const statement of statements) {
      if (statement.trim()) {
        try {
          await conn.execute(statement);
          const tableName = statement.match(/CREATE TABLE `(\w+)`/)?.[1];
          console.log(`✅ Created table: ${tableName}`);
          createdCount++;
        } catch (err) {
          if (err.code === "ER_TABLE_EXISTS_ERROR") {
            const tableName = statement.match(/CREATE TABLE `(\w+)`/)?.[1];
            console.log(`⚠️  Table already exists: ${tableName}`);
            existingCount++;
          } else {
            const tableName = statement.match(/CREATE TABLE `(\w+)`/)?.[1];
            console.error(`❌ Error creating ${tableName}: ${err.message}`);
            errorCount++;
          }
        }
      }
    }

    // Verify all tables
    const [tables] = await conn.execute("SHOW TABLES");
    console.log("\n📊 SUMMARY:");
    console.log(`   Tables created: ${createdCount}`);
    console.log(`   Tables already existed: ${existingCount}`);
    console.log(`   Errors: ${errorCount}`);
    console.log(`   Total tables in database: ${tables.length}`);
    
    if (tables.length >= 20) {
      console.log("\n✅ All 20 tables are now available in the database!");
    } else {
      console.log(`\n⚠️  Expected 20 tables, but found ${tables.length}. Some tables may be missing.`);
    }

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