'use strict';

/**
 * db/resilience.js — PostgreSQL persistence for Resilience scan results.
 *
 * Stores scan results in compliance_reports (report_type='resilience').
 * Writing findings to finding_history enables delta tracking across scans.
 */

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

/**
 * Load oracle_connections row, ownership-checked.
 * Returns null if not found or not owned by userId.
 */
async function getConnectionForScan(connId, userId) {
  const { rows } = await pool.query(
    `SELECT id, host, port, service_name, username, encrypted_password,
            connection_type, server_type, ebs_instance_name, name
     FROM oracle_connections
     WHERE id = :p1 AND user_id = :p2`,
    { p1: connId, p2: userId }
  );
  return rows[0] || null;
}

/**
 * Save a completed scan to compliance_reports.
 * @param {number} userId
 * @param {string} companyDomain
 * @param {string} userEmail
 * @param {number} connectionId
 * @param {object} result
 * Returns the new row's id.
 */
async function saveScan(userId, companyDomain, userEmail, connectionId, result) {
  const title = `Resilience — ${result.connection_name || 'Connection ' + connectionId}`;
  const ins = await pool.query(
    `INSERT INTO compliance_reports
       (user_id, company_domain, report_type, title, date_from, date_to, generated_by, generated_data, row_counts)
     VALUES (:p1, :p2, 'resilience', :p3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, :p4, :p5, :p6)
     RETURNING id INTO :out_id`,
    {
      p1: userId,
      p2: companyDomain || '',
      p3: title,
      p4: userEmail || String(userId),
      p5: pool.clob(JSON.stringify(result)),
      p6: JSON.stringify({ connection_id: connectionId, score: result.score }),
      out_id: pool.outNumber(),
    }
  );
  return ins.outBinds.out_id[0];
}

/**
 * Return the N most recent scans for a connection from compliance_reports.
 */
async function getHistory(connectionId, userId, limit = 10) {
  const { rows } = await pool.query(
    `SELECT id, title, generated_data, row_counts, generated_at AS created_at
     FROM compliance_reports
     WHERE report_type = 'resilience'
       AND user_id = :p1
       AND JSON_VALUE(row_counts, '$.connection_id' RETURNING NUMBER) = :p2
     ORDER BY date_from DESC
     FETCH FIRST :p3 ROWS ONLY`,
    { p1: userId, p2: connectionId, p3: limit }
  );
  return rows;
}

/**
 * Upsert per-finding delta rows into finding_history.
 * Called after each scan to power "first seen / last seen" tracking.
 *
 * @param {number} connectionId
 * @param {Array<{key, title, severity, metric_line, remediation}>} findings
 */
async function upsertFindings(connectionId, findings) {
  if (!findings.length) return;

  for (const f of findings) {
    await pool.query(
      `MERGE INTO finding_history t
       USING (SELECT :p1 AS connection_id, :p3 AS finding_key FROM DUAL) s
          ON (t.connection_id = s.connection_id AND t.finding_key = s.finding_key)
       WHEN MATCHED THEN UPDATE SET
         last_seen_at = CURRENT_TIMESTAMP,
         metric_line  = :p5,
         title        = :p4,
         remediation  = :p6,
         severity     = :p7,
         resolved_at  = NULL
       WHEN NOT MATCHED THEN
         INSERT (connection_id, check_id, finding_key, title, metric_line, remediation, severity, first_seen_at, last_seen_at)
         VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)`,
      { p1: connectionId, p2: f.check_id || f.key, p3: f.key, p4: f.title,
        p5: f.metric_line || null, p6: f.remediation || null, p7: f.severity || 'warning' }
    );
  }
}

/**
 * Mark findings that no longer appear in the current scan as resolved.
 *
 * @param {number} connectionId
 * @param {string[]} activeKeys — finding_key values from the current scan
 */
async function resolveStaleFindings(connectionId, activeKeys) {
  if (!activeKeys.length) {
    await pool.query(
      `UPDATE finding_history
       SET resolved_at = CURRENT_TIMESTAMP
       WHERE connection_id = :p1
         AND check_id LIKE 'res_%'
         AND resolved_at IS NULL`,
      { p1: connectionId }
    );
    return;
  }
  const kb = {}; const kn = activeKeys.map((v, i) => { kb['k' + i] = v; return ':k' + i; });
  await pool.query(
    `UPDATE finding_history
     SET resolved_at = CURRENT_TIMESTAMP
     WHERE connection_id = :p1
       AND check_id LIKE 'res_%'
       AND resolved_at IS NULL
       AND finding_key NOT IN (${kn.join(', ')})`,
    { p1: connectionId, ...kb }
  );
}

module.exports = { getConnectionForScan, saveScan, getHistory, upsertFindings, resolveStaleFindings };
