'use strict';

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

const pool = mysql.createPool({
  host:            process.env.DB_HOST     || 'localhost',
  port:     parseInt(process.env.DB_PORT   || '3306'),
  database:        process.env.DB_NAME     || 'chainpay_store',
  user:            process.env.DB_USER     || 'chainpay',
  password:        process.env.DB_PASS     || '',
  waitForConnections: true,
  connectionLimit: parseInt(process.env.DB_POOL_MAX || '20'),
  queueLimit:      0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
  timezone:        'Z',
  charset:         'utf8mb4',
});

// Test connection on startup
async function testConnection() {
  try {
    const conn = await pool.getConnection();
    await conn.ping();
    conn.release();
    console.log('[DB] Connected to MySQL successfully');
  } catch (err) {
    console.error('[DB] Connection failed:', err.message);
    process.exit(1);
  }
}

// Helper: run a query with automatic connection management
async function query(sql, params = []) {
  const [rows] = await pool.execute(sql, params);
  return rows;
}

// Helper: get a single row
async function queryOne(sql, params = []) {
  const rows = await query(sql, params);
  return rows[0] || null;
}

// Helper: run inside a transaction
async function transaction(fn) {
  const conn = await pool.getConnection();
  await conn.beginTransaction();
  try {
    const result = await fn(conn);
    await conn.commit();
    conn.release();
    return result;
  } catch (err) {
    await conn.rollback();
    conn.release();
    throw err;
  }
}

module.exports = { pool, query, queryOne, transaction, testConnection };
