/**
 * db/option-consent.js — per-connection Oracle option licensing consent.
 *
 * Owns: reading + writing connection_option_consent (current state per
 *       connection × option) and appending to option_consent_log (immutable
 *       audit trail of every state change).
 * Does NOT own: the ADDM gate itself (routes/addm.js), detection of what Oracle
 *       reports (that is evidence shown to a human, never authorization).
 *
 * The governing rule (see the 2026-07-19 compliance work): detection informs,
 * consent decides, default is closed. Absence of a row === 'unset' === NOT
 * authorized. control_management_pack_access and DBA_FEATURE_USAGE_STATISTICS
 * are recorded as `detected_evidence` at grant time, never used to authorize.
 */

'use strict';

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

// Extensible now, enforced by the CHECK constraint in ensureColumns(). Only
// diagnostics_pack and tuning_pack are wired to a gate today; the rest are
// watch-items so a future execute path has a place to hang.
const VALID_OPTIONS = new Set([
  'diagnostics_pack', 'tuning_pack', 'advanced_compression',
  'advanced_security', 'partitioning', 'in_memory', 'rat',
]);
const VALID_STATES = new Set(['unset', 'granted', 'denied']);

/**
 * Current consent row for one (connection, option), or null if never set.
 * A null return means 'unset' — the caller must treat it as NOT authorized.
 */
async function getConsent(connectionId, optionKey) {
  const { rows } = await pool.query(
    `SELECT connection_id, option_key, state, acknowledged_by, acknowledged_at,
            source, detected_evidence, updated_at
       FROM connection_option_consent
      WHERE connection_id = :p1 AND option_key = :p2`,
    { p1: connectionId, p2: optionKey }
  );
  return rows[0] || null;
}

/** 'granted' | 'denied' | 'unset' (no row → 'unset'). */
async function getConsentState(connectionId, optionKey) {
  const row = await getConsent(connectionId, optionKey);
  return row ? row.state : 'unset';
}

/**
 * The only authorization question a gate should ask. True ONLY when an explicit
 * 'granted' row exists — every other state (unset, denied, missing) is false.
 */
async function isGranted(connectionId, optionKey) {
  return (await getConsentState(connectionId, optionKey)) === 'granted';
}

/** All consent rows for a connection — for a settings UI / the consent modal. */
async function listForConnection(connectionId) {
  const { rows } = await pool.query(
    `SELECT connection_id, option_key, state, acknowledged_by, acknowledged_at,
            source, detected_evidence, updated_at
       FROM connection_option_consent
      WHERE connection_id = :p1
      ORDER BY option_key`,
    { p1: connectionId }
  );
  return rows;
}

/**
 * Set consent state and append an audit-log row, atomically. Reads the prior
 * state so the log records old → new for every transition (including the very
 * first, where old is 'unset').
 *
 * @param {number} connectionId
 * @param {string} optionKey    must be in VALID_OPTIONS
 * @param {string} newState     'granted' | 'denied' | 'unset'
 * @param {object} opts
 *   opts.actor     user id making the attestation (recorded in both tables)
 *   opts.source    'setup' | 'first_use' | 'settings'
 *   opts.evidence  what detection reported at decision time (JSON, nullable)
 *   opts.ip        request IP for the audit trail (nullable)
 * @returns the persisted connection_option_consent row
 */
async function setConsent(connectionId, optionKey, newState, opts = {}) {
  if (!VALID_OPTIONS.has(optionKey)) {
    throw new Error(`Unknown option_key: ${optionKey}`);
  }
  if (!VALID_STATES.has(newState)) {
    throw new Error(`Invalid consent state: ${newState}`);
  }

  const { actor = null, source = null, evidence = null, ip = null } = opts;
  const evidenceJson = evidence == null ? null : JSON.stringify(evidence);

  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const prior = await client.query(
      `SELECT state FROM connection_option_consent
        WHERE connection_id = :p1 AND option_key = :p2 FOR UPDATE`,
      { p1: connectionId, p2: optionKey }
    );
    const oldState = prior.rows[0] ? prior.rows[0].state : 'unset';

    await client.query(
      `MERGE INTO connection_option_consent t
       USING (SELECT :p1 AS connection_id, :p2 AS option_key FROM DUAL) s
          ON (t.connection_id = s.connection_id AND t.option_key = s.option_key)
       WHEN MATCHED THEN UPDATE SET
         state             = :p3,
         acknowledged_by   = :p4,
         acknowledged_at   = CURRENT_TIMESTAMP,
         source            = :p5,
         detected_evidence = :p6,
         updated_at        = CURRENT_TIMESTAMP
       WHEN NOT MATCHED THEN
         INSERT (connection_id, option_key, state, acknowledged_by, acknowledged_at,
                 source, detected_evidence, updated_at)
         VALUES (:p1, :p2, :p3, :p4, CURRENT_TIMESTAMP, :p5, :p6, CURRENT_TIMESTAMP)`,
      { p1: connectionId, p2: optionKey, p3: newState, p4: actor, p5: source,
        p6: pool.clob(evidenceJson) }
    );
    const upserted = await client.query(
      `SELECT connection_id, option_key, state, acknowledged_by,
              acknowledged_at, source, detected_evidence, updated_at
         FROM connection_option_consent
        WHERE connection_id = :p1 AND option_key = :p2`,
      { p1: connectionId, p2: optionKey }
    );

    // Append-only audit trail — one row per transition, never updated/deleted.
    await client.query(
      `INSERT INTO option_consent_log
         (connection_id, option_key, old_state, new_state, actor, source,
          detected_evidence, ip)
       VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8)`,
      { p1: connectionId, p2: optionKey, p3: oldState, p4: newState, p5: actor,
        p6: source, p7: pool.clob(evidenceJson), p8: ip }
    );

    await client.query('COMMIT');
    return upserted.rows[0];
  } catch (err) {
    try { await client.query('ROLLBACK'); } catch (_) { /* connection already broken */ }
    throw err;
  } finally {
    client.release();
  }
}

/** Read the audit trail for a connection (newest first). */
async function getLog(connectionId, limit = 100) {
  const { rows } = await pool.query(
    `SELECT id, connection_id, option_key, old_state, new_state, actor,
            source, detected_evidence, ip, created_at
       FROM option_consent_log
      WHERE connection_id = :p1
      ORDER BY created_at DESC, id DESC
      FETCH FIRST :p2 ROWS ONLY`,
    { p1: connectionId, p2: limit }
  );
  return rows;
}

module.exports = {
  VALID_OPTIONS,
  VALID_STATES,
  getConsent,
  getConsentState,
  isGranted,
  listForConnection,
  setConsent,
  getLog,
};
