/**
 * db/ssh-targets.js — Database access for SSH credential vault + audit log.
 *
 * Owns: ssh_targets CRUD (admin-scope and user-scope), ssh_audit writes + reads,
 *       oracle_connections proxy lookup for SSH routing (getConnectionProxyById).
 * Does NOT own: SSH execution, encryption/decryption, auth.
 *
 * Scoping convention:
 *   - Admin functions operate on ALL targets (user_id = NULL or any user_id).
 *   - User functions operate on targets WHERE user_id = $userId only.
 *   - Admin-created targets (user_id = NULL) are never visible to regular users.
 */

'use strict';

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

// ─── ssh_targets ─────────────────────────────────────────────────────────────

/**
 * List all SSH targets (no secrets — encrypted values are NOT returned).
 * @returns {Promise<Array>}
 */
async function listTargets() {
  const { rows } = await pool.query(`
    SELECT id, label, host, port, os_user, auth_method, role,
           connection_id, last_connected_at, created_at, updated_at
    FROM ssh_targets
    ORDER BY label
  `);
  return rows;
}

/**
 * Return a single target by ID, including encrypted credential fields.
 * Only the executor and key-rotation code should call this.
 * @param {number} id
 * @returns {Promise<Object|null>}
 */
async function getTargetById(id) {
  const { rows } = await pool.query(`
    SELECT id, label, host, port, os_user, auth_method, role,
           encrypted_private_key, encrypted_passphrase,
           user_id, connection_id, last_connected_at, created_at, updated_at
    FROM ssh_targets
    WHERE id = :p1
  `, { p1: id });
  return rows[0] || null;
}

/**
 * Insert a new SSH target.
 * @param {Object} p
 * @param {string} p.label
 * @param {string} p.host
 * @param {number} p.port
 * @param {string} p.os_user
 * @param {string} p.auth_method  'key' | 'password'
 * @param {string|null} p.encrypted_private_key  Pre-encrypted by caller
 * @param {string|null} p.encrypted_passphrase   Pre-encrypted by caller
 * @param {string} p.role  'apps_tier' | 'db_tier' | 'utility'
 * @param {number|null} p.connection_id
 * @returns {Promise<Object>}
 */
async function createTarget({ label, host, port, os_user, auth_method, encrypted_private_key, encrypted_passphrase, role, connection_id }) {
  const ins = await pool.query(`
    INSERT INTO ssh_targets
      (label, host, port, os_user, auth_method, encrypted_private_key,
       encrypted_passphrase, role, connection_id)
    VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)
    RETURNING id INTO :out_id
  `, { p1: label, p2: host, p3: port || 22, p4: os_user, p5: auth_method, p6: encrypted_private_key || null,
       p7: encrypted_passphrase || null, p8: role || 'db_tier', p9: connection_id || null, out_id: pool.outNumber() });
  const { rows } = await pool.query(`SELECT id, label, host, port, os_user, auth_method, role,
              connection_id, last_connected_at, created_at, updated_at FROM ssh_targets WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

/**
 * Update metadata fields only (host, label, port, os_user, role, connection_id).
 * Credential updates go through updateTargetCredentials().
 * @param {number} id
 * @param {Object} p
 * @returns {Promise<Object|null>}
 */
async function updateTargetMeta(id, { label, host, port, os_user, role, connection_id }) {
  const upd = await pool.query(`
    UPDATE ssh_targets
    SET label = COALESCE(:p2, label),
        host  = COALESCE(:p3, host),
        port  = COALESCE(:p4, port),
        os_user = COALESCE(:p5, os_user),
        role  = COALESCE(:p6, role),
        connection_id = :p7,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = :p1
  `, { p1: id, p2: label, p3: host, p4: pool.num(port), p5: os_user, p6: role, p7: connection_id || null });
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT id, label, host, port, os_user, auth_method, role,
              connection_id, last_connected_at, created_at, updated_at FROM ssh_targets WHERE id = :p1`, { p1: id });
  return rows[0] || null;
}

/**
 * Update credential fields (call only when re-uploading a key or changing password).
 * @param {number} id
 * @param {Object} p
 * @param {string} p.auth_method
 * @param {string|null} p.encrypted_private_key
 * @param {string|null} p.encrypted_passphrase
 * @returns {Promise<Object|null>}
 */
async function updateTargetCredentials(id, { auth_method, encrypted_private_key, encrypted_passphrase }) {
  const upd = await pool.query(`
    UPDATE ssh_targets
    SET auth_method = :p2,
        encrypted_private_key = :p3,
        encrypted_passphrase  = :p4,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = :p1
  `, { p1: id, p2: auth_method, p3: encrypted_private_key || null, p4: encrypted_passphrase || null });
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT id, label, host, port, os_user, auth_method, role,
              connection_id, last_connected_at, created_at, updated_at FROM ssh_targets WHERE id = :p1`, { p1: id });
  return rows[0] || null;
}

/**
 * Delete an SSH target (cascades to ssh_audit rows).
 * @param {number} id
 * @returns {Promise<boolean>}
 */
async function deleteTarget(id) {
  const { rowCount } = await pool.query('DELETE FROM ssh_targets WHERE id = :p1', { p1: id });
  return rowCount > 0;
}

/**
 * Stamp last_connected_at to now (called after a successful test-connection).
 * @param {number} id
 * @returns {Promise<void>}
 */
async function markConnected(id) {
  await pool.query(
    'UPDATE ssh_targets SET last_connected_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE id = :p1',
    { p1: id }
  );
}

// ─── User-scoped ssh_targets ──────────────────────────────────────────────────

/**
 * List SSH targets owned by a specific user (no secrets returned).
 * @param {number} userId
 * @returns {Promise<Array>}
 */
async function listTargetsByUser(userId) {
  const { rows } = await pool.query(`
    SELECT id, label, host, port, os_user, auth_method, role,
           connection_id, last_connected_at, created_at, updated_at
    FROM ssh_targets
    WHERE user_id = :p1
    ORDER BY label
  `, { p1: userId });
  return rows;
}

/**
 * Verify that a target belongs to a specific user.
 * Returns the target row (no secrets) or null if not found / not owned.
 * @param {number} id
 * @param {number} userId
 * @returns {Promise<Object|null>}
 */
async function getTargetByIdForUser(id, userId) {
  const { rows } = await pool.query(`
    SELECT id, label, host, port, os_user, auth_method, role,
           connection_id, last_connected_at, created_at, updated_at
    FROM ssh_targets
    WHERE id = :p1 AND user_id = :p2
  `, { p1: id, p2: userId });
  return rows[0] || null;
}

/**
 * Return a single target with encrypted credentials, only if owned by userId.
 * Only the executor should call this after verifying user ownership.
 * @param {number} id
 * @param {number} userId
 * @returns {Promise<Object|null>}
 */
async function getTargetByIdForUserWithCreds(id, userId) {
  const { rows } = await pool.query(`
    SELECT id, label, host, port, os_user, auth_method, role,
           encrypted_private_key, encrypted_passphrase,
           connection_id, last_connected_at, created_at, updated_at
    FROM ssh_targets
    WHERE id = :p1 AND user_id = :p2
  `, { p1: id, p2: userId });
  return rows[0] || null;
}

/**
 * Insert a new SSH target owned by userId.
 * @param {Object} p  Same fields as createTarget() plus user_id
 * @returns {Promise<Object>}
 */
async function createTargetForUser({ user_id, label, host, port, os_user, auth_method, encrypted_private_key, encrypted_passphrase, role, connection_id }) {
  const ins = await pool.query(`
    INSERT INTO ssh_targets
      (user_id, label, host, port, os_user, auth_method, encrypted_private_key,
       encrypted_passphrase, role, connection_id)
    VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10)
    RETURNING id INTO :out_id
  `, { p1: user_id, p2: label, p3: host, p4: port || 22, p5: os_user, p6: auth_method,
       p7: encrypted_private_key || null, p8: encrypted_passphrase || null,
       p9: role || 'db_tier', p10: connection_id || null, out_id: pool.outNumber() });
  const { rows } = await pool.query(`SELECT id, label, host, port, os_user, auth_method, role,
              connection_id, last_connected_at, created_at, updated_at FROM ssh_targets WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

/**
 * Update metadata for a user-owned target.
 * @param {number} id
 * @param {number} userId
 * @param {Object} p
 * @returns {Promise<Object|null>}
 */
async function updateTargetMetaForUser(id, userId, { label, host, port, os_user, role, connection_id }) {
  const upd = await pool.query(`
    UPDATE ssh_targets
    SET label = COALESCE(:p3, label),
        host  = COALESCE(:p4, host),
        port  = COALESCE(:p5, port),
        os_user = COALESCE(:p6, os_user),
        role  = COALESCE(:p7, role),
        connection_id = :p8,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = :p1 AND user_id = :p2
  `, { p1: id, p2: userId, p3: label, p4: host, p5: pool.num(port), p6: os_user, p7: role, p8: connection_id || null });
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT id, label, host, port, os_user, auth_method, role,
              connection_id, last_connected_at, created_at, updated_at FROM ssh_targets WHERE id = :p1 AND user_id = :p2`, { p1: id, p2: userId });
  return rows[0] || null;
}

/**
 * Update credentials for a user-owned target.
 * @param {number} id
 * @param {number} userId
 * @param {Object} p
 * @returns {Promise<Object|null>}
 */
async function updateTargetCredentialsForUser(id, userId, { auth_method, encrypted_private_key, encrypted_passphrase }) {
  const upd = await pool.query(`
    UPDATE ssh_targets
    SET auth_method = :p3,
        encrypted_private_key = :p4,
        encrypted_passphrase  = :p5,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = :p1 AND user_id = :p2
  `, { p1: id, p2: userId, p3: auth_method, p4: encrypted_private_key || null, p5: encrypted_passphrase || null });
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT id, label, host, port, os_user, auth_method, role,
              connection_id, last_connected_at, created_at, updated_at FROM ssh_targets WHERE id = :p1 AND user_id = :p2`, { p1: id, p2: userId });
  return rows[0] || null;
}

/**
 * Delete a user-owned target (cascades to ssh_audit rows).
 * @param {number} id
 * @param {number} userId
 * @returns {Promise<boolean>}
 */
async function deleteTargetForUser(id, userId) {
  const { rowCount } = await pool.query(
    'DELETE FROM ssh_targets WHERE id = :p1 AND user_id = :p2',
    { p1: id, p2: userId }
  );
  return rowCount > 0;
}

/**
 * Return proxy_url + proxy_api_key_enc for an oracle_connection by ID.
 * Used by ssh-executor to route SSH commands through the proxy when connection_id is set.
 * Returns null if the connection doesn't exist or has no proxy configured.
 * @param {number} connectionId
 * @returns {Promise<{proxy_url: string, proxy_api_key_enc: string}|null>}
 */
async function getConnectionProxyById(connectionId) {
  const { rows } = await pool.query(`
    SELECT proxy_url, proxy_api_key_enc
    FROM oracle_connections
    WHERE id = :p1
      AND connection_type = 'proxy'
      AND proxy_url IS NOT NULL
      AND proxy_api_key_enc IS NOT NULL
  `, { p1: connectionId });
  return rows[0] || null;
}

/**
 * Fetch oracle_connections owned by userId for the connection dropdown.
 * Returns id + connection_name + connection_type (safe for display — no credentials).
 * connection_type is included so the UI can show "via proxy" badge on linked targets.
 * @param {number} userId
 * @returns {Promise<Array>}
 */
async function getConnectionsForUser(userId) {
  // oracle_connections' display-name column is `name`; there is no
  // connection_name here (health_checks has one — an easy confusion, and the
  // source of pre-existing bug #9). Aliased AS connection_name so the dropdown
  // UI keeps reading the same key.
  const { rows } = await pool.query(`
    SELECT id, name AS connection_name, connection_type
    FROM oracle_connections
    WHERE user_id = :p1
    ORDER BY name
  `, { p1: userId });
  return rows;
}

// ─── ssh_audit ────────────────────────────────────────────────────────────────

/**
 * Write an SSH audit row.
 * @param {Object} p
 * @param {number}  p.target_id
 * @param {string}  p.command_key
 * @param {string}  p.rendered_command
 * @param {number|null}  p.exit_code
 * @param {number|null}  p.stdout_bytes
 * @param {number|null}  p.stderr_bytes
 * @param {number|null}  p.duration_ms
 * @param {boolean} p.was_rejected
 * @param {string|null}  p.rejection_reason
 * @param {string|null}  p.initiated_by
 * @returns {Promise<void>}
 */
async function writeAudit({ target_id, command_key, rendered_command, exit_code, stdout_bytes, stderr_bytes, duration_ms, was_rejected, rejection_reason, initiated_by }) {
  await pool.query(`
    INSERT INTO ssh_audit
      (target_id, command_key, rendered_command, exit_code, stdout_bytes,
       stderr_bytes, duration_ms, was_rejected, rejection_reason, initiated_by)
    VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10)
  `, { p1: target_id, p2: command_key, p3: rendered_command, p4: exit_code ?? null,
      p5: stdout_bytes ?? null, p6: stderr_bytes ?? null, p7: duration_ms ?? null,
      p8: pool.bool(was_rejected), p9: rejection_reason || null, p10: initiated_by || null });
}

/**
 * Return audit rows for the admin UI.
 * @param {Object} opts
 * @param {number|null} opts.target_id  Filter by target (null = all)
 * @param {number}      opts.limit
 * @param {number}      opts.offset
 * @returns {Promise<{rows: Array, total: number}>}
 */
async function listAudit({ target_id = null, limit = 100, offset = 0 } = {}) {
  const conditions = [];
  const binds = {};
  if (target_id) {
    binds.p_target = target_id;
    conditions.push('a.target_id = :p_target');
  }
  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

  const { rows } = await pool.query(`
    SELECT a.id, a.target_id, t.label AS target_label, t.host,
           a.command_key, a.exit_code, a.stdout_bytes, a.stderr_bytes,
           a.duration_ms, a.was_rejected, a.rejection_reason,
           a.initiated_by, a.ts
    FROM ssh_audit a
    JOIN ssh_targets t ON t.id = a.target_id
    ${where}
    ORDER BY a.ts DESC
    OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY
  `, { ...binds, p_limit: limit, p_offset: offset });

  const { rows: countRows } = await pool.query(`
    SELECT COUNT(*) AS total FROM ssh_audit a ${where}
  `, binds);

  return { rows, total: parseInt(countRows[0].total, 10) };
}

module.exports = {
  // Admin-scope (all users)
  listTargets,
  getTargetById,
  createTarget,
  updateTargetMeta,
  updateTargetCredentials,
  deleteTarget,
  markConnected,
  writeAudit,
  listAudit,
  // User-scope (ownership-enforced)
  listTargetsByUser,
  getTargetByIdForUser,
  getTargetByIdForUserWithCreds,
  createTargetForUser,
  updateTargetMetaForUser,
  updateTargetCredentialsForUser,
  deleteTargetForUser,
  getConnectionsForUser,
  // Cross-entity: oracle_connections proxy lookup
  getConnectionProxyById,
};
