/**
 * db/ssh-profiles.js — connection_ssh_profiles CRUD.
 * Owns: read/write of SSH connection profiles, key fingerprint extraction.
 * Does NOT own: encryption/decryption (crypto-utils.js), ownership checks (routes layer),
 *               SSH execution (agent-side only), agent-channel dispatch.
 *
 * Key material (ssh_key_encrypted, bastion_key_encrypted) is always encrypted
 * before being handed to this module. This module stores and retrieves the
 * ciphertext only — never the plaintext key.
 */

'use strict';

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

// Columns safe to return to API callers. Key ciphertext is excluded.
const PUBLIC_COLUMNS = `
  id, connection_id, role, ssh_host, ssh_port, ssh_user, auth_method,
  ssh_key_fingerprint,
  bastion_host, bastion_port, bastion_user,
  known_hosts_pin,
  created_at, updated_at, last_tested_at, last_test_status
`;

/**
 * Return all SSH profiles for a connection (safe, no key material).
 */
async function getProfilesForConnection(connectionId) {
  const result = await pool.query(
    `SELECT ${PUBLIC_COLUMNS}
       FROM connection_ssh_profiles
      WHERE connection_id = :p1
      ORDER BY role`,
    { p1: connectionId }
  );
  return result.rows;
}

/**
 * Return a single profile by connection + role (safe, no key material).
 */
async function getProfile(connectionId, role) {
  const result = await pool.query(
    `SELECT ${PUBLIC_COLUMNS}
       FROM connection_ssh_profiles
      WHERE connection_id = :p1 AND role = :p2`,
    { p1: connectionId, p2: role }
  );
  return result.rows[0] || null;
}

/**
 * Return a profile including encrypted key material (for agent dispatch only).
 * Do not expose this to API response bodies.
 */
async function getProfileWithKeys(connectionId, role) {
  const result = await pool.query(
    `SELECT *
       FROM connection_ssh_profiles
      WHERE connection_id = :p1 AND role = :p2`,
    { p1: connectionId, p2: role }
  );
  return result.rows[0] || null;
}

/**
 * Upsert a profile (create if not exists, update if role already exists).
 * Returns the saved profile (no key material).
 *
 * @param {object} params
 *   connection_id, role, ssh_host, ssh_port, ssh_user, auth_method,
 *   ssh_key_encrypted (optional), ssh_key_fingerprint (optional),
 *   bastion_host (optional), bastion_port (optional), bastion_user (optional),
 *   bastion_key_encrypted (optional)
 */
async function upsertProfile(params) {
  const {
    connection_id, role, ssh_host, ssh_port, ssh_user, auth_method,
    ssh_key_encrypted, ssh_key_fingerprint,
    bastion_host, bastion_port, bastion_user, bastion_key_encrypted,
  } = params;

  await pool.query(
    `MERGE INTO connection_ssh_profiles t
     USING (SELECT :p1 AS connection_id, :p2 AS role, :p3 AS ssh_host, :p4 AS ssh_port,
                   :p5 AS ssh_user, :p6 AS auth_method, :p7 AS ssh_key_encrypted,
                   :p8 AS ssh_key_fingerprint, :p9 AS bastion_host, :p10 AS bastion_port,
                   :p11 AS bastion_user, :p12 AS bastion_key_encrypted FROM DUAL) s
        ON (t.connection_id = s.connection_id AND t.role = s.role)
     WHEN MATCHED THEN UPDATE SET
       ssh_host              = s.ssh_host,
       ssh_port              = s.ssh_port,
       ssh_user              = s.ssh_user,
       auth_method           = s.auth_method,
       ssh_key_encrypted     = COALESCE(s.ssh_key_encrypted, t.ssh_key_encrypted),
       ssh_key_fingerprint   = COALESCE(s.ssh_key_fingerprint, t.ssh_key_fingerprint),
       bastion_host          = s.bastion_host,
       bastion_port          = s.bastion_port,
       bastion_user          = s.bastion_user,
       bastion_key_encrypted = COALESCE(s.bastion_key_encrypted, t.bastion_key_encrypted),
       updated_at            = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (connection_id, role, ssh_host, ssh_port, ssh_user, auth_method,
               ssh_key_encrypted, ssh_key_fingerprint,
               bastion_host, bastion_port, bastion_user, bastion_key_encrypted,
               created_at, updated_at)
       VALUES (s.connection_id, s.role, s.ssh_host, s.ssh_port, s.ssh_user, s.auth_method,
               s.ssh_key_encrypted, s.ssh_key_fingerprint,
               s.bastion_host, s.bastion_port, s.bastion_user, s.bastion_key_encrypted,
               CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
    {
      p1: connection_id, p2: role, p3: ssh_host, p4: ssh_port || 22, p5: ssh_user || 'oracle',
      p6: auth_method || 'agent_forward', p7: ssh_key_encrypted || null, p8: ssh_key_fingerprint || null,
      p9: bastion_host || null, p10: bastion_port || 22, p11: bastion_user || null, p12: bastion_key_encrypted || null,
    }
  );
  const result = await pool.query(
    `SELECT ${PUBLIC_COLUMNS} FROM connection_ssh_profiles WHERE connection_id = :p1 AND role = :p2`,
    { p1: connection_id, p2: role }
  );
  return result.rows[0];
}

/**
 * Update last_tested_at and last_test_status after a test attempt.
 * Also stores known_hosts_pin if provided (captured on first successful connect).
 */
async function updateTestResult(connectionId, role, status, knownHostsPin) {
  await pool.query(
    `UPDATE connection_ssh_profiles
        SET last_tested_at  = CURRENT_TIMESTAMP,
            last_test_status = :p3,
            known_hosts_pin  = COALESCE(:p4, known_hosts_pin),
            updated_at       = CURRENT_TIMESTAMP
      WHERE connection_id = :p1 AND role = :p2`,
    { p1: connectionId, p2: role, p3: status, p4: knownHostsPin || null }
  );
}

/**
 * Delete a profile by connection + role.
 * Returns true if a row was deleted.
 */
async function deleteProfile(connectionId, role) {
  const result = await pool.query(
    `DELETE FROM connection_ssh_profiles
      WHERE connection_id = :p1 AND role = :p2`,
    { p1: connectionId, p2: role }
  );
  return result.rowCount > 0;
}

module.exports = {
  getProfilesForConnection,
  getProfile,
  getProfileWithKeys,
  upsertProfile,
  updateTestResult,
  deleteProfile,
};
