/**
 * db/sql-console-history.js — SQL Console query history persistence.
 *
 * Owns: sql_console_history table reads and writes.
 * Does NOT own: SQL execution logic (routes/console.js),
 *               Oracle connections (oracle-client.js).
 *
 * All rows are append-only. Purge handled by housekeeping cron.
 */

'use strict';

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

/**
 * Log a SQL console execution.
 * Called after every execute/explain attempt from the SQL Console.
 *
 * @param {object} entry
 * @returns {Promise<object>} inserted row
 */
async function insertHistory(entry) {
  const {
    connection_id,
    user_id,
    sql_text,
    elapsed_ms = null,
    rows_returned = null,
    error_message = null,
    success = true,
    source_ip = null,
    user_agent = null,
    session_id = null,
  } = entry;

  const ins = await pool.query(
    `INSERT INTO sql_console_history
       (connection_id, user_id, sql_text, elapsed_ms, rows_returned,
        error_message, success, source_ip, user_agent, session_id, executed_at)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, CURRENT_TIMESTAMP)
     RETURNING id INTO :out_id`,
    { p1: connection_id, p2: user_id, p3: pool.clob(sql_text), p4: elapsed_ms, p5: rows_returned,
      p6: error_message, p7: pool.bool(success), p8: source_ip, p9: user_agent, p10: session_id,
      out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(`SELECT * FROM sql_console_history WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

/**
 * Get recent query history for a user+connection pair.
 *
 * @param {object} opts
 * @param {number} opts.user_id
 * @param {number} opts.connection_id
 * @param {number} [opts.limit=50]
 * @returns {Promise<object[]>}
 */
async function getHistory({ user_id, connection_id, limit = 50 }) {
  const safeLimit = Math.min(Math.max(1, parseInt(limit, 10) || 50), 200);
  const { rows } = await pool.query(
    `SELECT id, sql_text, elapsed_ms, rows_returned, success, error_message, executed_at
     FROM sql_console_history
     WHERE user_id = :p1 AND connection_id = :p2
     ORDER BY executed_at DESC
     FETCH FIRST :p3 ROWS ONLY`,
    { p1: user_id, p2: connection_id, p3: safeLimit }
  );
  return rows;
}

/**
 * Get full audit history for admin review. Supports date range filtering.
 *
 * @param {object} opts
 * @param {number} opts.connection_id
 * @param {number} [opts.user_id]
 * @param {string} [opts.from_date] - ISO date string
 * @param {string} [opts.to_date]   - ISO date string
 * @param {number} [opts.limit=500]
 * @returns {Promise<object[]>}
 */
async function getAuditHistory({ connection_id, user_id, from_date, to_date, limit = 500 }) {
  const conditions = ['connection_id = :p_conn'];
  const binds = { p_conn: connection_id };

  if (user_id) {
    binds.p_user = user_id;
    conditions.push('user_id = :p_user');
  }
  if (from_date) {
    binds.p_from = pool.tstz(from_date);
    conditions.push('executed_at >= :p_from');
  }
  if (to_date) {
    binds.p_to = pool.tstz(to_date);
    conditions.push('executed_at <= :p_to');
  }

  const safeLimit = Math.min(Math.max(1, parseInt(limit, 10) || 500), 2000);
  binds.p_limit = safeLimit;

  const { rows } = await pool.query(
    `SELECT h.id, h.user_id, u.email AS user_email, h.sql_text,
            h.elapsed_ms, h.rows_returned, h.success, h.error_message,
            h.source_ip, h.executed_at
     FROM sql_console_history h
     LEFT JOIN users u ON u.id = h.user_id
     WHERE ${conditions.join(' AND ')}
     ORDER BY h.executed_at DESC
     FETCH FIRST :p_limit ROWS ONLY`,
    binds
  );
  return rows;
}

/**
 * Purge entries older than a given number of days.
 * Called by housekeeping cron.
 *
 * @param {number} [retentionDays=90]
 * @returns {Promise<number>} rows deleted
 */
async function purgeOldHistory(retentionDays = 90) {
  const { rowCount } = await pool.query(
    `DELETE FROM sql_console_history
     WHERE executed_at < CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p1, 'DAY')`,
    { p1: retentionDays }
  );
  return rowCount;
}

module.exports = { insertHistory, getHistory, getAuditHistory, purgeOldHistory };
