const { Pool } = require('pg');
require('dotenv').config();

// Database connection configuration for VPS PostgreSQL
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'indo_advisory',
  user: process.env.DB_USER || 'indo_user',
  password: process.env.DB_PASSWORD,
  ssl: process.env.DB_SSL === 'true' ? {
    rejectUnauthorized: false // For self-signed certificates on VPS
  } : false,
  max: 20, // Maximum number of connections in the pool
  idleTimeoutMillis: 30000, // Close idle connections after 30 seconds
  connectionTimeoutMillis: 2000, // Return error if connection takes longer than 2 seconds
};

// Create connection pool for enterprise-grade performance
const pool = new Pool(dbConfig);

// Handle connection events
pool.on('connect', () => {
  console.log('✅ Connected to PostgreSQL database');
});

pool.on('error', (err) => {
  console.error('❌ PostgreSQL connection error:', err);
  process.exit(-1);
});

// Database utility functions
const db = {
  // Execute query with parameterized inputs (prevents SQL injection)
  query: async (text, params) => {
    const start = Date.now();
    try {
      const res = await pool.query(text, params);
      const duration = Date.now() - start;
      console.log('Executed query:', { text, duration, rows: res.rowCount });
      return res;
    } catch (err) {
      console.error('Database query error:', err);
      throw err;
    }
  },

  // Get a client from the pool for transactions
  getClient: async () => {
    return await pool.connect();
  },

  // Execute transaction
  transaction: async (callback) => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      const result = await callback(client);
      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  },

  // Close all connections (for graceful shutdown)
  close: async () => {
    await pool.end();
    console.log('📦 Database connections closed');
  }
};

// Test database connection on startup
const testConnection = async () => {
  try {
    const client = await pool.connect();
    console.log('🔗 Database connection test successful');
    client.release();
  } catch (err) {
    console.error('❌ Database connection test failed:', err.message);
    
    // Provide helpful error messages for VPS setup
    if (err.code === 'ECONNREFUSED') {
      console.error('💡 Make sure PostgreSQL is running on your VPS');
      console.error('💡 Check if the database server is accepting connections');
    } else if (err.code === 'ENOTFOUND') {
      console.error('💡 Check your database host configuration');
    } else if (err.message.includes('authentication')) {
      console.error('💡 Check your database username and password');
    } else if (err.message.includes('database') && err.message.includes('does not exist')) {
      console.error('💡 Create the database first or check DB_NAME in .env');
    }
    
    throw err;
  }
};

module.exports = {
  db,
  pool,
  testConnection
};