/**
 * db/performance.js — PostgreSQL queries for the Performance tab.
 *
 * Owns: sql_fix_cache reads/writes (AI-generated SQL fix recommendations),
 *       oracle_connections lookup for the performance route.
 * Does NOT own: Oracle query execution (routes/performance.js via oracle-client),
 *               auth, Pool construction, other health-check data.
 */

'use strict';

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

/**
 * getCachedFix — look up a cached AI fix by (sql_id, plan_hash_value).
 * Returns the cached row if found and not expired, null otherwise.
 *
 * @param {string} sqlId
 * @param {number|string} planHashValue
 * @returns {Promise<{fix_type, fix_sql, rationale}|null>}
 */
async function getCachedFix(sqlId, planHashValue) {
  const result = await pool.query(
    `SELECT fix_type, fix_sql, rationale
     FROM sql_fix_cache
     WHERE sql_id = :p1
       AND plan_hash_value = :p2
       AND expires_at > CURRENT_TIMESTAMP`,
    { p1: sqlId, p2: String(planHashValue) }
  );
  return result.rows[0] || null;
}

/**
 * upsertFixCache — insert or update a cached AI fix.
 * TTL is 24 hours from the time of insert/update.
 *
 * @param {object} params
 * @param {string}  params.sqlId
 * @param {string}  params.planHashValue
 * @param {string}  params.fixType        'index' | 'hint' | 'rewrite'
 * @param {string}  params.fixSql
 * @param {string}  params.rationale
 * @param {string}  [params.sqlTextPrefix]
 * @returns {Promise<void>}
 */
async function upsertFixCache({ sqlId, planHashValue, fixType, fixSql, rationale, sqlTextPrefix }) {
  await pool.query(
    `MERGE INTO sql_fix_cache t
     USING (SELECT :p1 AS sql_id, :p2 AS plan_hash_value FROM DUAL) s
        ON (t.sql_id = s.sql_id AND t.plan_hash_value = s.plan_hash_value)
     WHEN MATCHED THEN UPDATE SET
       fix_type        = :p3,
       fix_sql         = :p4,
       rationale       = :p5,
       sql_text_prefix = :p6,
       created_at      = CURRENT_TIMESTAMP,
       expires_at      = CURRENT_TIMESTAMP + INTERVAL '24' HOUR
     WHEN NOT MATCHED THEN
       INSERT (sql_id, plan_hash_value, fix_type, fix_sql, rationale, sql_text_prefix, created_at, expires_at)
       VALUES (:p1, :p2, :p3, :p4, :p5, :p6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '24' HOUR)`,
    { p1: sqlId, p2: String(planHashValue), p3: fixType, p4: fixSql, p5: rationale,
      p6: pool.clob(sqlTextPrefix || null) }
  );
}

/**
 * purgeStaleFixes — delete expired cache rows. Called opportunistically on
 * each top-SQL fetch; keeps the table small without a dedicated cron.
 *
 * @returns {Promise<void>}
 */
async function purgeStaleFixes() {
  await pool.query(`DELETE FROM sql_fix_cache WHERE expires_at <= CURRENT_TIMESTAMP`);
}

/**
 * getConnectionForPerf — load an oracle_connection row (ownership-checked).
 * Returns null if not found or not owned by userId.
 *
 * @param {number} connId
 * @param {number} userId
 * @returns {Promise<{id, host, port, service_name, username, encrypted_password, connection_type}|null>}
 */
async function getConnectionForPerf(connId, userId) {
  const result = await pool.query(
    `SELECT id, host, port, service_name, username, encrypted_password, connection_type
     FROM oracle_connections
     WHERE id = :p1 AND user_id = :p2`,
    { p1: connId, p2: userId }
  );
  return result.rows[0] || null;
}

/**
 * getUserForPerf — load a user row by id (used by auth middleware).
 *
 * @param {number} userId
 * @returns {Promise<{id, email}|null>}
 */
async function getUserForPerf(userId) {
  const result = await pool.query('SELECT id, email FROM users WHERE id = :p1', { p1: userId });
  return result.rows[0] || null;
}

module.exports = { getCachedFix, upsertFixCache, purgeStaleFixes, getConnectionForPerf, getUserForPerf };
