const mysql = require('mysql2/promise');
const sqlite = require('./sqlite');
require('dotenv').config();

// Check if database bypass is enabled
const bypassDb = process.env.BYPASS_DB_FOR_TEST !== 'false';

if (bypassDb) {
  console.log('⚠️  WARNING: BYPASS_DB_FOR_TEST is enabled. Database connections are disabled.');
}

// Database configuration - removed invalid options
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'project_management',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

console.log('🔧 Database configuration:', {
  host: dbConfig.host,
  port: dbConfig.port,
  user: dbConfig.user,
  database: dbConfig.database
});

// Create MySQL connection pool
let pool = null;
let useSqlite = false; // Now default to MySQL

// Initialize MySQL connection pool
if (!bypassDb) {
  console.log('🔄 Initializing MySQL database...');
  try {
    pool = mysql.createPool(dbConfig);
    console.log('✅ MySQL connection pool created');
  } catch (error) {
    console.error('❌ Failed to create MySQL connection pool:', error.message);
  }
}

// Test database connection
const testConnection = async () => {
  if (bypassDb) {
    console.log('⚠️  Database connection test skipped (BYPASS_DB_FOR_TEST enabled)');
    return false;
  }
  
  try {
    // Test MySQL connection
    const connection = await pool.getConnection();
    console.log('✅ MySQL database connected successfully');
    connection.release();
    return true;
  } catch (error) {
    console.error('❌ MySQL database connection failed:', error.message);
    return false;
  }
};

// Execute query with error handling
const executeQuery = async (sql, params = []) => {
  if (bypassDb) {
    console.log('⚠️  Query execution skipped (BYPASS_DB_FOR_TEST enabled)');
    return { bypass: true };
  }
  
  try {
    // Use MySQL
    console.log(`🔍 Executing query: ${sql} with params:`, params);
    const [rows] = await pool.execute(sql, params);
    console.log(`✅ Query returned ${Array.isArray(rows) ? rows.length : 'N/A'} rows`);
    return rows;
  } catch (error) {
    console.error('❌ MySQL query execution failed:', error.message);
    console.error('📝 SQL:', sql);
    console.error('📝 Params:', params);
    throw error;
  }
};

// Get a single connection for transactions
const getConnection = async () => {
  if (bypassDb) {
    throw new Error('Database bypass mode enabled');
  }
  
  try {
    const connection = await pool.getConnection();
    return connection;
  } catch (error) {
    console.error('❌ Failed to get MySQL connection:', error.message);
    throw error;
  }
};

module.exports = {
  pool,
  testConnection,
  executeQuery,
  getConnection,
  bypassDb
};