import { Sequelize } from "sequelize";
import dotenv from "dotenv";
import fs from "fs";
import path from "path";
import { fileURLToPath, pathToFileURL } from "url";
import configFile from "../config/config.cjs";

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

dotenv.config();

const env = process.env.NODE_ENV || "development";
const config = configFile[env];

// ✅ Add logging config per environment
if (env === "production") {
  config.logging = false; // disable all SQL logs in production
} else {
  // optional: keep default logging in dev
  config.logging = false;
}

const db = {};

let sequelize;

const dbName = process.env.DB_NAME || config.database;
const dbUser = process.env.DB_USER || process.env.DB_USERNAME || config.username;
const dbPass = process.env.DB_PASSWORD || config.password;

const sequelizeOptions = {
  ...config,
  pool: {
    max: 20,          // Max number of connections in pool (Default is 5)
    min: 0,           // Min number of connections in pool
    acquire: 30000,   // Max time (ms) to wait for a connection
    idle: 10000       // Max time (ms) a connection can be idle
  },
  retry: {
    max: 3 // Retry 3 times on transient connection errors
  }
};

if (config.use_env_variable && process.env[config.use_env_variable]) {
  sequelize = new Sequelize(process.env[config.use_env_variable], sequelizeOptions);
} else {
  sequelize = new Sequelize(
    dbName,
    dbUser,
    dbPass,
    sequelizeOptions
  );
}

// Dynamically read all model files and register them automatically
const files = fs
  .readdirSync(__dirname)
  .filter(
    (file) =>
      file.indexOf(".") !== 0 &&
      file !== basename &&
      file.slice(-3) === ".js"
  );

for (const file of files) {
  // Convert path → file:// URL for ESM dynamic import
  const modelUrl = pathToFileURL(path.join(__dirname, file)).href;
  const modelModule = await import(modelUrl);
  const model = modelModule.default(sequelize, Sequelize.DataTypes);
  db[model.name] = model;
}

// Call associate if defined (we added this to each model file)
Object.keys(db).forEach((modelName) => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

export default db;
