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

// MySQL database connection with auto-reconnect
const db = mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'wavejtpe_masm_app'
});

// Handle connection errors and auto-reconnect
db.on('error', (err) => {
    console.error('Database connection error:', err);
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
        console.log('Attempting to reconnect to database...');
        handleDisconnect();
    } else {
        throw err;
    }
});

// Handle disconnection
db.on('end', () => {
    console.log('Database connection ended. Attempting to reconnect...');
    handleDisconnect();
});

// Reconnection function
function handleDisconnect() {
    db.connect((err) => {
        if (err) {
            console.error('Error reconnecting to database:', err);
            // Try to reconnect after 2 seconds
            setTimeout(handleDisconnect, 2000);
        } else {
            console.log('Successfully reconnected to database as id ' + db.threadId);
        }
    });
}

// Initial connection
db.connect((err) => {
    if (err) {
        console.error('Database connection failed: ' + err.stack);
        return;
    }
    console.log('Connected to database as id ' + db.threadId);
});

module.exports = db;

