source: src/config/database.js

const { Client: SSHClient } = require('ssh2');
const { Client: PGClient } = require('pg');
const net = require('net');

let pgClient = null;
let sshClient = null;
let sshServer = null;

async function connect() {
  return new Promise((resolve, reject) => {
    sshClient = new SSHClient();

    sshClient.on('ready', () => {
      console.log('SSH Connected successfuly.');

      sshServer = net.createServer((sock) => {
        sshClient.forwardOut(
          sock.remoteAddress,
          sock.remotePort,
          process.env.DB_HOST,
          parseInt(process.env.DB_PORT),
          (err, stream) => {
            if (err) {
              sock.end();
              console.error('SSH forwarding error:', err);
              return;
            }
            sock.pipe(stream).pipe(sock);
          }
        );
      });

      sshServer.listen(0, '127.0.0.1', async () => {
        const localPort = sshServer.address().port;
        console.log(`SSH tunnel: localhost:${localPort} -> ${process.env.DB_HOST}:${process.env.DB_PORT}`);

        pgClient = new PGClient({
          host: '127.0.0.1',
          port: localPort,
          user: process.env.DB_USER,
          password: process.env.DB_PASSWORD,
          database: process.env.DB_NAME,
        });

        try {
          await pgClient.connect();
          
          await pgClient.query(`SET search_path TO ${process.env.DB_SCHEMA}`);
          
          console.log(`Database connected (schema: ${process.env.DB_SCHEMA})`);
          resolve(pgClient);
        } catch (err) {
          console.error('Database connection error:', err);
          reject(err);
        }
      });

      sshServer.on('error', (err) => {
        console.error('SSH server error:', err);
        reject(err);
      });
    });

    sshClient.on('error', (err) => {
      console.error('SSH Connection error:', err);
      reject(err);
    });

    sshClient.connect({
      host: process.env.SSH_HOST,
      port: parseInt(process.env.SSH_PORT),
      username: process.env.SSH_USER,
      password: process.env.SSH_PASSWORD,
    });
  });
}

function getPool() {
  if (!pgClient) {
    throw new Error('Database not connected. Call connect() first.');
  }
  return {
    query: (...args) => pgClient.query(...args),
  };
}

async function close() {
  if (pgClient) {
    await pgClient.end();
    console.log('Database connection closed');
  }
  if (sshServer) {
    sshServer.close();
    console.log('SSH server closed');
  }
  if (sshClient) {
    sshClient.end();
    console.log('SSH connection closed');
  }
}

module.exports = {
  connect,
  getPool,
  close,
};