const { Pool } = require('pg');

// Optimized connection pool configuration for high throughput
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'stockprovider',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'postgres',
  // Connection pool optimization
  max: 20, // Maximum number of clients in the pool per instance
  min: 5, // Minimum number of clients to keep in pool
  idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000, // Return error after 2 seconds if no connection available
  // Performance tuning
  statement_timeout: 10000, // 10 second query timeout
  query_timeout: 10000,
  // Keep connections alive
  keepAlive: true,
  keepAliveInitialDelayMillis: 10000,
});

// Pool error handling
pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
});

// Pool monitoring - only log in development
if (process.env.NODE_ENV !== 'production') {
  pool.on('connect', () => {
    console.log('New database connection established');
  });

  pool.on('remove', () => {
    console.log('Database connection removed from pool');
  });
}

// Initialize database schema
async function initializeDatabase() {
  const client = await pool.connect();
  try {
    // Use advisory lock to prevent concurrent schema creation
    await client.query('SELECT pg_advisory_lock(123456789)');

    // Create users table
    await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // Create stock_prices table
    await client.query(`
      CREATE TABLE IF NOT EXISTS stock_prices (
        id SERIAL PRIMARY KEY,
        symbol VARCHAR(10) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        change_percent DECIMAL(5, 2),
        last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(symbol)
      )
    `);

    // Create index on symbol for faster lookups
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_stock_symbol ON stock_prices(symbol)
    `);

    // Create refresh_tokens table
    await client.query(`
      CREATE TABLE IF NOT EXISTS refresh_tokens (
        id SERIAL PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        token VARCHAR(500) NOT NULL UNIQUE,
        expires_at TIMESTAMP NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        revoked BOOLEAN DEFAULT FALSE
      )
    `);

    // Create index on token for faster lookups
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_refresh_token ON refresh_tokens(token)
    `);

    // Create index on user_id for faster lookups
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_refresh_token_user ON refresh_tokens(user_id)
    `);

    console.log('Database schema initialized successfully');

    // Seed default test user
    await seedDefaultUser(client);
  } catch (error) {
    // Ignore errors if tables already exist (race condition)
    if (error.code === '42P07' || error.code === '23505') {
      console.log('Database schema already initialized');
      // Try to seed user even if schema exists
      try {
        await seedDefaultUser(client);
      } catch (e) {
        // User might already exist, ignore
      }
    } else {
      console.error('Error initializing database:', error.message);
    }
  } finally {
    // Release advisory lock
    try {
      await client.query('SELECT pg_advisory_unlock(123456789)');
    } catch (e) {
      // Ignore unlock errors
    }
    client.release();
  }
}

// Seed default test user
async function seedDefaultUser(client) {
  const bcrypt = require('bcryptjs');

  try {
    // Check if testuser already exists
    const existingUser = await client.query(
      'SELECT id FROM users WHERE username = $1',
      ['testuser'],
    );

    if (existingUser.rows.length === 0) {
      // Hash password: testpass123
      const hashedPassword = await bcrypt.hash('testpass123', 10);

      // Insert test user
      await client.query(
        'INSERT INTO users (username, password) VALUES ($1, $2)',
        ['testuser', hashedPassword],
      );

      console.log('Default test user created: testuser / testpass123');
    } else {
      console.log('Test user already exists');
    }
  } catch (error) {
    // Ignore if user already exists
    if (error.code !== '23505') {
      console.error('Error seeding test user:', error.message);
    }
  }
}

module.exports = {
  pool,
  initializeDatabase,
};

// Made with Bob
