/**
 * db/ebs-credentials.js
 * Owns: ebs_credentials table CRUD + credential_access_log writes.
 * Does NOT own: encryption/decryption (see crypto-utils.js), route auth (see middleware/auth.js).
 *
 * SECURITY CONTRACT:
 *   - upsertCredential() accepts pre-encrypted fields — never stores plaintext.
 *   - getDecryptedValue() is the ONLY function that returns the encrypted blob;
 *     callers must decrypt in-memory, never log the result, never return it over API.
 *   - logAccess() must be called by every caller of getDecryptedValue().
 */

'use strict';

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

// ssh_private_key: stores the SSH private key for the oracle/applmgr OS user
// alongside DB/EBS credentials — same vault, same AES-256-GCM threat model.
const VALID_TYPES = ['apps', 'system', 'sys', 'weblogic_admin', 'sysadmin_user', 'xml_gateway', 'ssh_private_key'];

/**
 * Upsert an encrypted credential row.
 * @param {number} connectionId — oracle_connections.id (INTEGER, not UUID)
 * @param {string} credentialType — one of VALID_TYPES
 * @param {string} username
 * @param {string} encryptedValue — hex ciphertext
 * @param {string} iv — hex
 * @param {string} authTag — hex
 * @returns {Promise<object>} the upserted row (metadata only, no plaintext)
 */
async function upsertCredential(connectionId, credentialType, username, encryptedValue, iv, authTag) {
  if (!VALID_TYPES.includes(credentialType)) {
    throw new Error(`Invalid credential_type: ${credentialType}`);
  }

  await pool.query(
    `MERGE INTO ebs_credentials t
     USING (SELECT :p1 AS connection_id, :p2 AS credential_type FROM DUAL) s
        ON (t.connection_id = s.connection_id AND t.credential_type = s.credential_type)
     WHEN MATCHED THEN UPDATE SET
       t.username        = :p3,
       t.encrypted_value = :p4,
       t.iv              = :p5,
       t.auth_tag        = :p6,
       t.rotated_at      = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN INSERT
       (connection_id, credential_type, username, encrypted_value, iv, auth_tag, rotated_at)
       VALUES (s.connection_id, s.credential_type, :p3, :p4, :p5, :p6, CURRENT_TIMESTAMP)`,
    { p1: connectionId, p2: credentialType, p3: username,
      p4: encryptedValue, p5: iv, p6: authTag }
  );

  // MERGE has no RETURNING; read back on the composite key that was upserted.
  // Deliberately selects metadata only — never encrypted_value/iv/auth_tag.
  const result = await pool.query(
    `SELECT id, connection_id, credential_type, username, rotated_at, created_at
     FROM ebs_credentials
     WHERE connection_id = :p1 AND credential_type = :p2`,
    { p1: connectionId, p2: credentialType }
  );

  return result.rows[0];
}

/**
 * List credential metadata for a connection — never includes plaintext.
 * @param {string} connectionId
 * @returns {Promise<Array>} [{credential_type, username, rotated_at, created_at}]
 */
async function listCredentials(connectionId) {
  const result = await pool.query(
    `SELECT credential_type, username, rotated_at, created_at
     FROM ebs_credentials
     WHERE connection_id = :p1
     ORDER BY credential_type`,
    { p1: connectionId }
  );
  return result.rows;
}

/**
 * Fetch the encrypted blob for a single credential — for in-memory decryption only.
 * Caller MUST call logAccess() immediately after.
 * @param {string} connectionId
 * @param {string} credentialType
 * @returns {Promise<object|null>} {encrypted_value, iv, auth_tag} or null if not set
 */
async function getDecryptedValue(connectionId, credentialType) {
  const result = await pool.query(
    `SELECT encrypted_value, iv, auth_tag
     FROM ebs_credentials
     WHERE connection_id = :p1 AND credential_type = :p2`,
    { p1: connectionId, p2: credentialType }
  );
  return result.rows[0] || null;
}

/**
 * Delete a credential (revoke).
 * @param {string} connectionId
 * @param {string} credentialType
 * @returns {Promise<boolean>} true if a row was deleted
 */
async function deleteCredential(connectionId, credentialType) {
  const result = await pool.query(
    `DELETE FROM ebs_credentials
     WHERE connection_id = :p1 AND credential_type = :p2`,
    { p1: connectionId, p2: credentialType }
  );
  return result.rowCount > 0;
}

/**
 * Append an audit log entry for every decryption event.
 * @param {string} connectionId
 * @param {string} credentialType
 * @param {string} action — e.g. 'adop_run', 'wls_bounce', 'cm_bounce'
 * @param {number|null} userId
 */
async function logAccess(connectionId, credentialType, action, userId) {
  await pool.query(
    `INSERT INTO credential_access_log
       (connection_id, credential_type, action, user_id)
     VALUES (:p1, :p2, :p3, :p4)`,
    { p1: connectionId, p2: credentialType, p3: action, p4: userId || null }
  );
}

/**
 * Get recent access log entries for a connection (admin audit view).
 * @param {string} connectionId
 * @param {number} [limit=50]
 * @returns {Promise<Array>}
 */
async function getAccessLog(connectionId, limit = 50) {
  const result = await pool.query(
    `SELECT credential_type, action, user_id, accessed_at
     FROM credential_access_log
     WHERE connection_id = :p1
     ORDER BY accessed_at DESC
     FETCH FIRST :p2 ROWS ONLY`,
    { p1: connectionId, p2: limit }
  );
  return result.rows;
}

module.exports = {
  upsertCredential,
  listCredentials,
  getDecryptedValue,
  deleteCredential,
  logAccess,
  getAccessLog,
  VALID_TYPES,
};
