/**
 * db/fndload.js — FNDLOAD migration audit log queries.
 *
 * Owns: fndload_history table — append-only audit of all FNDLOAD operations.
 * Does NOT own: Oracle proxy execution, AI diff calls, connection lookups.
 */

'use strict';

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

/**
 * Append a new FNDLOAD history row.
 * @param {object} row
 * @returns {Promise<object>} inserted row with id
 */
async function logFndloadAction(row) {
  const {
    user_id, user_email, action,
    source_conn_id, source_conn_name,
    target_conn_id, target_conn_name,
    object_type, lct_file, object_names,
    diff_summary, upload_result, pre_state_ldt,
    success, error_message,
  } = row;

  const ins = await pool.query(
    `INSERT INTO fndload_history
       (user_id, user_email, action,
        source_conn_id, source_conn_name,
        target_conn_id, target_conn_name,
        object_type, lct_file, object_names,
        diff_summary, upload_result, pre_state_ldt,
        success, error_message)
     VALUES (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15)
     RETURNING id INTO :out_id`,
    {
      p1: user_id || null, p2: user_email, p3: action,
      p4: source_conn_id || null, p5: source_conn_name || null,
      p6: target_conn_id || null, p7: target_conn_name || null,
      p8: object_type, p9: lct_file, p10: JSON.stringify(object_names || []),
      p11: pool.clob(diff_summary || null),
      p12: upload_result ? JSON.stringify(upload_result) : null,
      p13: pre_state_ldt || null,
      p14: pool.bool(success !== undefined ? success : null),
      p15: error_message || null,
      out_id: pool.outNumber(),
    },
  );
  const { rows } = await pool.query(
    `SELECT id, executed_at FROM fndload_history WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return rows[0];
}

/**
 * Fetch recent history for a user, optionally filtered by connection.
 * @param {number} userId
 * @param {object} opts  { limit, source_conn_id, target_conn_id }
 */
async function getHistory(userId, opts = {}) {
  const { limit = 50, source_conn_id, target_conn_id } = opts;
  const binds = { p_user: userId, p_limit: limit };
  let where = 'WHERE user_id = :p_user';
  if (source_conn_id) { binds.p_src = source_conn_id; where += ' AND source_conn_id = :p_src'; }
  if (target_conn_id) { binds.p_tgt = target_conn_id; where += ' AND target_conn_id = :p_tgt'; }

  const { rows } = await pool.query(
    `SELECT id, action, object_type, lct_file, object_names,
            source_conn_name, target_conn_name,
            diff_summary, upload_result, success, error_message, executed_at
     FROM fndload_history
     ${where}
     ORDER BY executed_at DESC
     FETCH FIRST :p_limit ROWS ONLY`,
    binds,
  );
  return rows;
}

module.exports = { logFndloadAction, getHistory };
