/**
 * db/patches.js — PostgreSQL queries for the Patch Advisor tab.
 *
 * Owns: oracle_connections lookup for patch advisor (credentials, connection type).
 * Does NOT own: Oracle patch query execution (routes/patches.js via oracle-client),
 *               auth, Pool construction, health check data, or other tabs' queries.
 */

'use strict';

const pool = require('./index');

/**
 * getConnectionForPatches — load a connection row for a given user + connection ID.
 * Returns null when no matching row exists (wrong owner or missing row).
 *
 * @param {number} userId
 * @param {number|string} connectionId
 * @returns {Promise<object|null>}
 */
async function getConnectionForPatches(userId, connectionId) {
  const result = await pool.query(
    `SELECT id, name, host, port, service_name, username,
            encrypted_password, connection_type, proxy_url, proxy_api_key_enc
     FROM oracle_connections
     WHERE id = $1 AND user_id = $2`,
    [connectionId, userId]
  );
  return result.rows[0] || null;
}

/**
 * getUserForPatches — minimal user row needed for auth.
 *
 * @param {number} userId
 * @returns {Promise<{id, email}|null>}
 */
async function getUserForPatches(userId) {
  const result = await pool.query(
    `SELECT id, email FROM users WHERE id = $1`,
    [userId]
  );
  return result.rows[0] || null;
}

/**
 * getAppTierAgentConn — resolve the app-tier proxy connection to run FMW opatch on.
 * If the given connection is itself an apps/both proxy, use it; otherwise find the
 * paired app-tier proxy connection sharing the same ebs_instance_name.
 * Returns { id, name } or null when no app-tier agent connection exists.
 *
 * @param {number} userId
 * @param {number|string} connectionId
 * @returns {Promise<{id:number, name:string}|null>}
 */
async function getAppTierAgentConn(userId, connectionId) {
  const self = await pool.query(
    `SELECT id, name, server_type, ebs_instance_name, connection_type
       FROM oracle_connections
      WHERE id = $1 AND user_id = $2`,
    [connectionId, userId]
  );
  const c = self.rows[0];
  if (!c) return null;
  if (c.connection_type === 'proxy' && (c.server_type === 'apps' || c.server_type === 'both')) {
    return { id: c.id, name: c.name };
  }
  if (!c.ebs_instance_name) return null;
  const paired = await pool.query(
    `SELECT id, name FROM oracle_connections
      WHERE ebs_instance_name = $1
        AND server_type IN ('apps', 'both')
        AND connection_type = 'proxy'
        AND (user_id = $2 OR user_id IS NULL)
      ORDER BY id
      LIMIT 1`,
    [c.ebs_instance_name, userId]
  );
  return paired.rows[0] || null;
}

module.exports = { getConnectionForPatches, getUserForPatches, getAppTierAgentConn };
