/**
 * services/db-ops-proxy.js — run a catalog SQL op on a proxy (agent) connection.
 *
 * Owns: the one way a DB Ops SQL op reaches an agent — agent-liveness check,
 *       SQL render, sendToAgent POST /api/run_sql, response normalisation.
 * Does NOT own: the op catalog (services/db-ops-executor.js), direct-TCP
 *               execution (that is runOp/withOracleConnection), HTTP status
 *               codes, or the ADOP gate.
 *
 * Why this exists: this logic was inline in routes/db-ops.js, welded to
 * res.status().json(), so services/validation-suite.js could not reuse it — it
 * called runOp() directly instead, which is direct-TCP only. On a proxy
 * connection that meant dialling the customer's Oracle host through the very
 * firewall the agent exists to avoid. Rather than copy the block (a second
 * implementation of "how a proxy connection reaches the agent", which is the
 * drift pattern that has bitten this codebase repeatedly), the mechanism moved
 * here unchanged and both callers use it.
 *
 * Returns a structured result rather than sending a response, because callers
 * differ: routes/db-ops.js maps `reason` onto its existing HTTP status codes,
 * validation-suite maps it onto pass/fail/skip.
 *
 * @returns {Promise<
 *   { ok: true,  rows: any[], columns: string[], durationMs?: number } |
 *   { ok: false, reason: 'unknown_op'|'not_sql'|'agent_offline'|'agent_check_failed'
 *                       |'render_failed'|'missing_params'|'proxy_error'
 *                       |'proxy_exception', error: string }
 * >}
 *
 * proxy_error vs proxy_exception is a real distinction, not noise: a reachable
 * agent that returned a SQL/driver error means the request worked and the query
 * did not (routes/db-ops.js answers 200 {ok:false}); a throw from sendToAgent
 * means the transport itself broke (that route answers 500). Collapsing them
 * would silently change that route's wire contract.
 */

'use strict';

const executor = require('./db-ops-executor');
const channel  = require('./agent-channel');

const AGENT_LIVENESS_TIMEOUT_MS = 5000;   // matches routes/db-ops.js
const RUN_SQL_TIMEOUT_MS        = 30000;  // matches routes/db-ops.js

async function runSqlViaAgent(connParams, opKey, params = {}, logTag = 'db-ops') {
  const catalog = executor.getOpCatalog();
  const opEntry = catalog.find(o => o.key === opKey);
  if (!opEntry) {
    return { ok: false, reason: 'unknown_op', error: `Unknown op: ${opKey}` };
  }
  if (opEntry.type !== 'sql') {
    return {
      ok: false, reason: 'not_sql',
      error: 'Only SQL operations are supported on proxy connections (SSH ops require a direct connection).',
    };
  }

  // Agent liveness. Promise.race guards against isAgentConnected() itself
  // hanging — an offline agent must fail fast and clearly, never hang a caller.
  let agentOnline = false;
  try {
    agentOnline = await Promise.race([
      channel.isAgentConnected(connParams.id),
      new Promise(resolve => setTimeout(() => resolve(false), AGENT_LIVENESS_TIMEOUT_MS)),
    ]);
  } catch (err) {
    console.error(`[${logTag}/proxy] isAgentConnected error:`, err.message);
    return { ok: false, reason: 'agent_check_failed', error: 'Agent check failed: ' + err.message };
  }
  console.log('[%s/proxy] conn=%s agentOnline=%s', logTag, connParams.id, agentOnline);
  if (!agentOnline) {
    return {
      ok: false, reason: 'agent_offline',
      error: 'Agent is not connected. Wait up to 30 seconds for the agent to check in, then retry.',
    };
  }

  const renderedSql = executor.renderOpSql(opKey, params || {});
  if (!renderedSql) {
    return { ok: false, reason: 'render_failed', error: `Cannot render SQL for op: ${opKey}` };
  }
  if (renderedSql.includes('{{')) {
    return { ok: false, reason: 'missing_params', error: 'Missing required parameters' };
  }

  try {
    const proxyResp = await channel.sendToAgent(connParams.id, {
      method: 'POST',
      path: '/api/run_sql',
      body: {
        sql:          renderedSql,
        service_name: connParams.serviceName || '',
        username:     connParams.username || '',
        password:     connParams.password || '',
        host:         connParams.host || 'localhost',
        port:         connParams.port || 1521,
      },
    }, RUN_SQL_TIMEOUT_MS);

    const body = proxyResp.body || {};
    if (proxyResp.statusCode !== 200 || !body.success) {
      return {
        ok: false, reason: 'proxy_error',
        error: body.error || `Proxy returned HTTP ${proxyResp.statusCode}`,
      };
    }
    return {
      ok: true,
      rows:       body.rows || [],
      columns:    body.columns || [],
      durationMs: body.duration_ms,
    };
  } catch (err) {
    console.error(`[${logTag}/proxy] run_sql error:`, err.message);
    return { ok: false, reason: 'proxy_exception', error: err.message };
  }
}

module.exports = { runSqlViaAgent };
