import fs from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { getDb } from "./db.js";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

export const initDb = async () => {
  const db = getDb();
  
  try {
    // Check if tables already exist by querying the 'users' table
    await db.query("SELECT 1 FROM users LIMIT 1");
    console.log("Database already initialized. Skipping auto-install.");
  } catch (error) {
    // If error means 'users' table doesn't exist (ER_NO_SUCH_TABLE)
    if (error.code === 'ER_NO_SUCH_TABLE' || error.errno === 1146) {
      console.log("Database tables missing. Starting auto-installation...");
      
      try {
        // Path to SQL files (outside backend/src/config)
        const schemaPath = path.resolve(__dirname, "../../../database/schema.sql");
        const seedPath = path.resolve(__dirname, "../../../database/seed.sql");
        
        const schemaSql = await fs.readFile(schemaPath, "utf8");
        const seedSql = await fs.readFile(seedPath, "utf8");
        
        // Execute Schema (split by semicolon if needed, but mysql2 pool can handle single large strings if configured)
        // For safety with large dumps, we split by ';'
        const queries = schemaSql.split(";").filter(q => q.trim().length > 0);
        for (const query of queries) {
          await db.query(query);
        }
        
        // Execute Seed Data
        const seedQueries = seedSql.split(";").filter(q => q.trim().length > 0);
        for (const query of seedQueries) {
          await db.query(query);
        }
        
        console.log("Auto-installation completed successfully! Tables and seed data created.");
      } catch (initError) {
        console.error("FATAL ERROR during auto-installation:", initError.message);
      }
    } else {
      console.error("Database connection error or other issue:", error.message);
    }
  }
};
