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

dotenv.config();

// Create the connection pool. The pool-specific settings are the defaults
const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER,
    database: process.env.DB_NAME,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT || 3306,
    waitForConnections: true,
    connectionLimit: 10,
    maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
    idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0,
});

// Test connection without crashing entire app on require
pool.getConnection()
    .then(connection => {
        console.log('✅ MySQL Database connected successfully during startup check.');
        connection.release();
    })
    .catch(err => {
        console.error('⚠️ MySQL Database connection FAILED during startup check.');
        console.error('   Reason:', err.message);
        console.error('   Check your .env file credentials.');
        // Do NOT process.exit(1) here, let the server start so static files work and API returns 500s instead of crashing
    });

console.log('Setup MySQL connection pool');

module.exports = {
    query: async (text, params, options = { silent: false }) => {
        // Log query for debugging (optional)
        // console.log('Executing query:', text, params);

        try {
            const [rows, fields] = await pool.query(text, params);
            // Normalizing result to match pg library structure partially
            // pg returns { rows: [], rowCount: 0 }
            // mysql2 execute returns [rows, fields]

            // If it's an INSERT/UPDATE/DELETE result (not an array of rows)
            if (rows && !Array.isArray(rows)) {
                return {
                    rows: [], // No rows returned for these operations in MySQL
                    rowCount: rows.affectedRows,
                    insertId: rows.insertId, // Custom field for insert ID
                    ...rows
                };
            }

            return {
                rows: rows,
                rowCount: rows.length
            };
        } catch (err) {
            if (!options.silent) {
                console.error('MySQL Query Error:', err.message, '\nQuery:', text, '\nParams:', params);
            }
            throw err;
        }
    },
    pool // Export pool if needed
};
