/**
 * db/proxy-exec-audit.js — Proxy exec audit log + connection lookup helpers.
 *
 * Owns: proxy_exec_audit table reads and writes; oracle_connections
 *       lookups needed by the proxy-exec route (proxy_url, api_key, ownership).
 * Does NOT own: connection CRUD, auth middleware, proxy HTTP transport.
 */

'use strict';

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

/**
 * Append one audit record for a proxy /exec call.
 */
async function logExec({
  connectionId,
  userId,
  commandId,
  args,
  exitCode,
  durationMs,
  stdout,
  stderr,
  error,
}) {
  const succeeded = !error && exitCode === 0;
  await pool.query(
    `INSERT INTO proxy_exec_audit
       (connection_id, user_id, command_id, args, exit_code, duration_ms,
        stdout_bytes, stderr_bytes, succeeded, error_message, executed_at)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, CURRENT_TIMESTAMP)`,
    {
      p1: connectionId,
      p2: userId,
      p3: commandId,
      p4: JSON.stringify(args || {}),
      p5: exitCode ?? -1,
      p6: durationMs ?? 0,
      p7: stdout ? Buffer.byteLength(stdout, 'utf8') : 0,
      p8: stderr ? Buffer.byteLength(stderr, 'utf8') : 0,
      p9: pool.bool(succeeded),
      p10: error || null,
    }
  );
}

/**
 * Return recent audit rows for a connection.
 */
async function getAuditLog({ connectionId, limit = 50, offset = 0 }) {
  const { rows } = await pool.query(
    `SELECT id, connection_id, user_id, command_id, args, exit_code,
            duration_ms, stdout_bytes, stderr_bytes, succeeded,
            error_message, executed_at
       FROM proxy_exec_audit
      WHERE connection_id = :p1
      ORDER BY executed_at DESC
      OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY`,
    { p1: connectionId, p_limit: Math.min(limit, 200), p_offset: offset }
  );
  return rows;
}

/**
 * Fetch proxy connection fields for a given connection id.
 * Returns null if not found.
 */
async function getProxyConnection(connectionId) {
  const { rows } = await pool.query(
    `SELECT id, connection_type, proxy_url, proxy_api_key_enc
       FROM oracle_connections
      WHERE id = :p1`,
    { p1: connectionId }
  );
  return rows[0] || null;
}

/**
 * Check whether a given user owns a connection.
 * Returns the connection row if owned, null otherwise.
 */
async function getOwnedConnection(connectionId, userId) {
  const { rows } = await pool.query(
    `SELECT id FROM oracle_connections
      WHERE id = :p1 AND user_id = :p2`,
    { p1: connectionId, p2: userId }
  );
  return rows[0] || null;
}

module.exports = { logExec, getAuditLog, getProxyConnection, getOwnedConnection };
