'use strict';

/**
 * db/security-posture.js — PostgreSQL persistence for Security Posture scan results.
 *
 * Stores scan results in compliance_reports (report_type='security_posture').
 * 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 = $1 AND user_id = $2`,
    [connId, 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 = `Security Posture — ${result.connection_name || 'Connection ' + connectionId}`;
  const { rows } = 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 ($1, $2, 'security_posture', $3, NOW(), NOW(), $4, $5, $6)
     RETURNING id`,
    [
      userId,
      companyDomain || '',
      title,
      userEmail || String(userId),
      JSON.stringify(result),
      JSON.stringify({ connection_id: connectionId, score: result.score }),
    ]
  );
  return rows[0].id;
}

/**
 * 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 = 'security_posture'
       AND user_id = $1
       AND (row_counts->>'connection_id')::int = $2
     ORDER BY date_from DESC
     LIMIT $3`,
    [userId, connectionId, 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(
      `INSERT INTO finding_history
         (connection_id, check_id, finding_key, title, metric_line, remediation, severity, first_seen_at, last_seen_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
       ON CONFLICT (connection_id, finding_key)
       DO UPDATE SET
         last_seen_at = NOW(),
         metric_line  = EXCLUDED.metric_line,
         title        = EXCLUDED.title,
         remediation  = EXCLUDED.remediation,
         severity     = EXCLUDED.severity,
         resolved_at  = NULL`,
      [connectionId, f.check_id || f.key, f.key, f.title, f.metric_line || null, f.remediation || null, 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) {
    // all resolved
    await pool.query(
      `UPDATE finding_history
       SET resolved_at = NOW()
       WHERE connection_id = $1
         AND check_id LIKE 'sp_%'
         AND resolved_at IS NULL`,
      [connectionId]
    );
    return;
  }
  await pool.query(
    `UPDATE finding_history
     SET resolved_at = NOW()
     WHERE connection_id = $1
       AND check_id LIKE 'sp_%'
       AND resolved_at IS NULL
       AND finding_key <> ALL ($2::text[])`,
    [connectionId, activeKeys]
  );
}

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