const mysql = require('mysql2/promise');

// Load environment variables
require('dotenv').config();

async function testConnection() {
  const config = {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
  };

  console.log('Testing database connection with config:');
  console.log(`Host: ${config.host}`);
  console.log(`Port: ${config.port}`);
  console.log(`Database: ${config.database}`);
  console.log(`User: ${config.user}`);
  console.log('Password: **** (hidden for security)');

  // Test 1: Basic connection
  console.log('\n--- Test 1: Basic Connection ---');
  try {
    const connection = await mysql.createConnection(config);
    console.log('✅ Successfully connected to the database!');
    
    // Test a simple query
    const [rows] = await connection.execute('SELECT VERSION() as version');
    console.log('✅ MySQL version:', rows[0].version);
    
    await connection.end();
  } catch (error) {
    console.error('❌ Basic connection failed:', error.message);
    if (error.code) {
      console.error('Error code:', error.code);
    }
    
    // Test 2: Try with SSL disabled
    console.log('\n--- Test 2: Connection with SSL disabled ---');
    try {
      const configNoSSL = {
        ...config,
        ssl: { rejectUnauthorized: false }
      };
      
      const connection = await mysql.createConnection(configNoSSL);
      console.log('✅ Successfully connected with SSL disabled!');
      
      const [rows] = await connection.execute('SELECT VERSION() as version');
      console.log('✅ MySQL version:', rows[0].version);
      
      await connection.end();
    } catch (sslError) {
      console.error('❌ Connection with SSL disabled also failed:', sslError.message);
      if (sslError.code) {
        console.error('Error code:', sslError.code);
      }
    }
  }
  
  // Test 3: Try to connect without specifying database
  console.log('\n--- Test 3: Connection without specifying database ---');
  try {
    const configNoDB = {
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    };
    
    const connection = await mysql.createConnection(configNoDB);
    console.log('✅ Successfully connected without specifying database!');
    
    // List all databases
    const [databases] = await connection.execute('SHOW DATABASES');
    console.log('✅ Available databases:');
    databases.forEach(db => {
      if (db.Database === config.database) {
        console.log(`  → ${db.Database} (TARGET DATABASE)`);
      } else {
        console.log(`  • ${db.Database}`);
      }
    });
    
    await connection.end();
  } catch (error) {
    console.error('❌ Connection without database failed:', error.message);
  }
}

testConnection();