import { Sequelize } from 'sequelize';
import dotenv from 'dotenv';

dotenv.config();

const dbName = process.env.MYSQL_DATABASE || 'handyman';

// First connect without database to create it if needed
const tempSequelize = new Sequelize('', process.env.MYSQL_USER || 'root', process.env.MYSQL_PASSWORD || '', {
  host: process.env.MYSQL_HOST || 'localhost',
  port: process.env.MYSQL_PORT || 3306,
  dialect: 'mysql',
  logging: false
});

export const sequelize = new Sequelize(
  dbName,
  process.env.MYSQL_USER || 'root',
  process.env.MYSQL_PASSWORD || '',
  {
    host: process.env.MYSQL_HOST || 'localhost',
    port: process.env.MYSQL_PORT || 3306,
    dialect: 'mysql',
    logging: process.env.NODE_ENV === 'development' ? console.log : false,
    pool: { max: 10, min: 0, acquire: 30000, idle: 10000 },
    define: {
      timestamps: true,
      underscored: true,
      createdAt: 'created_at',
      updatedAt: 'updated_at'
    }
  }
);

export async function connectDB() {
  try {
    // Create database if not exists
    await tempSequelize.query(`CREATE DATABASE IF NOT EXISTS \`${dbName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`);
    await tempSequelize.close();
    
    // Now connect to the database
    await sequelize.authenticate();
    console.log('MySQL connected successfully');
    
    if (process.env.NODE_ENV !== 'production') {
      await sequelize.sync();
      console.log('Database synchronized');
    }
  } catch (error) {
    console.error('MySQL connection error:', error);
    process.exit(1);
  }
}

export default sequelize;