/**
 * db/sql-audit-log.js — SQL audit log persistence.
 *
 * Owns: sql_audit_log table reads and writes.
 * Does NOT own: SQL whitelisting logic (routes/sql-execute.js),
 *               Oracle execution (oracle-proxy calls in routes/sql-execute.js).
 *
 * All rows are append-only. No updates or deletes.
 */

'use strict';

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

/**
 * Log a SQL execution attempt.
 * Called for every request to POST /api/connections/:id/execute-sql,
 * whether allowed or blocked, succeeded or failed.
 *
 * @param {object} entry
 * @param {number}  entry.user_id
 * @param {string}  entry.user_email
 * @param {number}  entry.connection_id
 * @param {string}  [entry.connection_name]
 * @param {string}  entry.sql_text
 * @param {boolean} entry.allowed
 * @param {string}  [entry.block_reason]     — set when allowed=false
 * @param {boolean} [entry.success]           — set when allowed=true
 * @param {number}  [entry.row_count]         — set on successful SELECT
 * @param {string}  [entry.error_message]     — set on Oracle error
 * @param {number}  [entry.duration_ms]       — execution wall-clock ms
 * @returns {Promise<object>} inserted row
 */
async function logSqlExecution(entry) {
  const {
    user_id,
    user_email,
    connection_id,
    connection_name = null,
    sql_text,
    allowed,
    block_reason = null,
    success = null,
    row_count = null,
    error_message = null,
    duration_ms = null,
  } = entry;

  const ins = await pool.query(
    `INSERT INTO sql_audit_log
       (user_id, user_email, connection_id, connection_name, sql_text,
        allowed, block_reason, success, row_count, error_message, duration_ms, executed_at)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, CURRENT_TIMESTAMP)
     RETURNING id INTO :out_id`,
    { p1: user_id, p2: user_email, p3: connection_id, p4: connection_name, p5: pool.clob(sql_text),
      p6: pool.bool(allowed), p7: block_reason, p8: pool.bool(success), p9: row_count,
      p10: error_message, p11: duration_ms, out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(`SELECT * FROM sql_audit_log WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

/**
 * Retrieve recent SQL audit log entries for admin review.
 * Returns the most recent rows first, capped at 500.
 *
 * @param {object} [filters]
 * @param {number} [filters.user_id]
 * @param {number} [filters.connection_id]
 * @param {boolean} [filters.blocked_only]
 * @param {number} [filters.limit]
 * @returns {Promise<object[]>}
 */
async function getAuditLog({ user_id, connection_id, blocked_only, limit = 100 } = {}) {
  const conditions = [];
  const binds = {};

  if (user_id) {
    binds.p_user = user_id;
    conditions.push('user_id = :p_user');
  }
  if (connection_id) {
    binds.p_conn = connection_id;
    conditions.push('connection_id = :p_conn');
  }
  if (blocked_only) {
    conditions.push('allowed = FALSE');
  }

  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
  const safeLimit = Math.min(Math.max(1, parseInt(limit, 10) || 100), 500);
  binds.p_limit = safeLimit;

  const { rows } = await pool.query(
    `SELECT * FROM sql_audit_log
     ${where}
     ORDER BY executed_at DESC
     FETCH FIRST :p_limit ROWS ONLY`,
    binds
  );
  return rows;
}

module.exports = { logSqlExecution, getAuditLog };
