'use strict';

//
// AutoPilot auto-remediation engine. Owns BOTH trigger paths:
//   Path A — service heartbeat (5-min cron in server.js): live-poll EBS
//            infrastructure status ops that never appear in check_results,
//            track consecutive downs, restart when a threshold is crossed.
//   Path B — HC-triggered (services/schedule-runner.js): CM/OPP checks that DO
//            appear in check_results fire remediation off occurrence_count.
//
// Agent contract (verified against oracle-proxy.py /api/ebs-ctrl + the
// routes/ebs-ops.js middleware-run consumer):
//   sendToAgent(connId, { method, path, body }, timeoutMs) → { statusCode, body }
//   body = { success, ok, stdout, stderr, exit_code, duration_ms }
//     success — the handler ran (false/HTTP≠200 = proxy/handler error, NOT a
//               service-down signal; we never restart on it)
//     ok      — exit_code === 0
//     exit_code — the shell exit code (0 = service up)
//

const pool    = require('../db/index');
const channel = require('./agent-channel');
const ebsJobsDb = require('../db/ebs-jobs');
const { decrypt } = require('../crypto-utils');

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

// ── Status op → start op mapping (Path A — heartbeat services) ──────────────
// Only safe individual-service restarts. NEVER: adcmctl_stop/start (CM full),
// wls_admin (LONG_OP bg-job model), apps_stop_all, DB shutdown, ADOP ops.
const HEARTBEAT_SERVICES = [
  { statusOp: 'adapcctl_status',     startOp: 'adapcctl_start',     label: 'Restart Apache/OHS',    tier: 'apps' },
  { statusOp: 'adopmnctl_status',    startOp: 'adopmnctl_start',    label: 'Restart OPMN',          tier: 'apps' },
  { statusOp: 'adalnctl_status',     startOp: 'adalnctl_start',     label: 'Restart Apps Listener', tier: 'apps' },
  { statusOp: 'adnodemgrctl_status', startOp: 'adnodemgrctl_start', label: 'Restart Node Manager',  tier: 'apps' },
  { statusOp: 'db_listener_status',  startOp: 'db_listener_start',  label: 'Restart DB Listener',   tier: 'db' },
  // DB instance startup. The instance's up/down comes from an AGENT-SIDE connection probe
  // (agentInstanceProbe → POST /api/ping → oracle_listener_up: the agent, co-located on the
  // DB host, opens a real connection and runs SELECT 1 FROM DUAL locally — DUAL is
  // unreadable unless the instance is OPEN, so success = instance genuinely up). This
  // deliberately does NOT read the instance off `lsnrctl status` (db_listener_status): the
  // listener can accept connections while its *status command* is broken, which
  // false-Downed conn 193. It also replaces the old two-probe design (listener poll + a
  // cloud-side SELECT-1 gate that a firewall between TuneVault and the DB false-negatived).
  // There is now ONE agent-side probe, no sync. startOp is db_startup_open (plain STARTUP —
  // a no-op/ORA-01081 on a running instance), never STARTUP FORCE. dbOnly (server_type='db').
  { agentInstanceProbe: true,        startOp: 'db_startup_open',    label: 'Start DB Instance', dbOnly: true },
  // WLS managed servers (OACore/Forms/OAFM). No single-op restart: their real server
  // names vary per environment and are DISCOVERED at runtime from the
  // managed_servers_status response (never hardcoded), then each DOWN server is
  // restarted individually via managed_server_start(server_name). groupKey is what the
  // user opts into (stored in remediate_services); `match` selects this group's servers
  // from the discovered set; startOp is the shared restart op.
  { requiresDiscovery: true, groupKey: 'oacore', match: 'oacore', statusOp: 'managed_servers_status', startOp: 'managed_server_start', label: 'Restart OACore managed server' },
  { requiresDiscovery: true, groupKey: 'forms',  match: 'forms',  statusOp: 'managed_servers_status', startOp: 'managed_server_start', label: 'Restart Forms managed server'  },
  { requiresDiscovery: true, groupKey: 'oafm',   match: 'oafm',   statusOp: 'managed_servers_status', startOp: 'managed_server_start', label: 'Restart OAFM managed server'   },
  // WLS Admin Server — LONG_OP (background job model). Poll is slow (75s). Restart is
  // NOT a simple op: fire wls_admin_start_bg (returns {started,pid}), then poll
  // apps_job_status until done/failed. isLongOp routes it through fireWlsAdminRestart.
  { statusOp: 'wls_admin_status', startOp: 'wls_admin_start', label: 'WLS Admin Server', scope: 'ebs_only', isLongOp: true, tier: 'apps' },
];

// Which server_types a heartbeat service is polled for. dbOnly = db only; tier
// 'db' = db/both; tier 'apps' = apps/both. Prevents polling app-tier ops on a
// db node (and vice-versa), so service_heartbeat_state only holds relevant rows.
function serviceAppliesTo(svc, serverType) {
  if (svc.dbOnly)          return serverType === 'db';
  if (svc.tier === 'db')   return serverType === 'db'   || serverType === 'both';
  if (svc.tier === 'apps') return serverType === 'apps' || serverType === 'both';
  return true; // untagged → poll everywhere (defensive; shouldn't happen)
}

// ── HC check_id → start op mapping (Path B — check_results) ─────────────────
const HC_REMEDIATION_MAP = {
  'EBS_CM01_INTERNAL_MANAGER': { startOp: 'adcmctl_start', label: 'Restart Concurrent Manager', scope: 'ebs_only' },
  'EBS_CM03_OPP_STATUS':       { startOp: 'adcmctl_start', label: 'Restart OPP via adcmctl',    scope: 'ebs_only' },
};

// The opt-in keys the engine can act on: simple services use their startOp; managed-
// server groups use their groupKey (oacore/forms/oafm) — the shared managed_server_start
// op alone can't distinguish the group. Plus adcmctl_start (Path B). remediate_services
// is validated against this at save time so no non-actionable key can ever be stored.
const RESTARTABLE_OPS = new Set([
  ...HEARTBEAT_SERVICES.map(s => (s.requiresDiscovery ? s.groupKey : s.startOp)),
  ...Object.values(HC_REMEDIATION_MAP).map(m => m.startOp),
]);

// Per-start-op agent timeouts (mirror routes/ebs-ops.js _ctrlTimeoutMap).
const START_TIMEOUTS = {
  adapcctl_start: 65000, adopmnctl_start: 65000, adalnctl_start: 35000,
  adnodemgrctl_start: 65000, db_listener_start: 60000,
  adcmctl_start: 130000, managed_server_start: 130000, db_startup_open: 120000,
  wls_admin_start: 130000, // used only if fired directly; the LONG_OP path uses fireWlsAdminRestart
};
const STATUS_TIMEOUT_MS = 25000;
const WLS_STATUS_TIMEOUT_MS = 75000; // wls_admin_status is a slow check
const MANAGED_STATUS_TIMEOUT_MS = 130000; // managed_servers_status loops admanagedsrvctl per server

// ── Per-op cooldown ──────────────────────────────────────────────────────────
// The window is the connection's configurable remediate_cooldown_minutes
// (connection_schedules — the SINGLE source of truth). Both fire paths (heartbeat
// applyDownSignal + maybeRemediateFromHC) call THIS one function; neither reads the
// value itself, so there is exactly one lookup. NVL(...,30) covers the (shouldn't-
// happen) missing-schedule-row case; the column is NOT NULL DEFAULT 30 otherwise.
async function isCoolingDown(connectionId, opKey) {
  const { rows } = await pool.query(
    `SELECT id FROM auto_remediation_log
     WHERE connection_id = :p1 AND op_key = :p2
       -- 'already_running' (no-op STARTUP, ORA-01081) COUNTS: it consumed a fire attempt,
       -- so it must open the cooldown window or a bad probe re-fires every 5 min.
       AND outcome IN ('success', 'failed', 'already_running')
       AND executed_at > SYSTIMESTAMP - NUMTODSINTERVAL(
             NVL((SELECT remediate_cooldown_minutes FROM connection_schedules
                  WHERE connection_id = :p1), 30), 'MINUTE')
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId, p2: opKey }
  );
  return rows.length > 0;
}

// Whole minutes remaining in the current cooldown window for (connection, op_key),
// for the skipped_cooldown log message. Computed entirely in SQL against SYSTIMESTAMP
// (no Node/DB clock-skew). Uses the same window basis as isCoolingDown: the most recent
// success|failed attempt + the connection's cooldown. 0 when not cooling / no attempts.
async function cooldownMinutesRemaining(connectionId, opKey) {
  const { rows } = await pool.query(
    `SELECT CEIL( EXTRACT(DAY    FROM d) * 1440
                + EXTRACT(HOUR   FROM d) * 60
                + EXTRACT(MINUTE FROM d)
                + EXTRACT(SECOND FROM d) / 60 ) AS mins
     FROM (
       SELECT ( MAX(l.executed_at)
                + NUMTODSINTERVAL(
                    NVL((SELECT remediate_cooldown_minutes FROM connection_schedules
                         WHERE connection_id = :p1), 30), 'MINUTE')
                - SYSTIMESTAMP ) AS d
       FROM auto_remediation_log l
       WHERE l.connection_id = :p1 AND l.op_key = :p2
         AND l.outcome IN ('success', 'failed', 'already_running')
     )`,
    { p1: connectionId, p2: opKey }
  );
  const mins = rows[0] && rows[0].mins;
  return (mins != null && mins > 0) ? mins : 0;
}

// ── ADOP gate ────────────────────────────────────────────────────────────────
// ebs_adop_state only ever has a row for EBS connections; absence → not patching.
async function isAdopActive(connectionId) {
  const { rows } = await pool.query(
    `SELECT patching FROM ebs_adop_state WHERE connection_id = :p1`,
    { p1: connectionId }
  );
  return !!(rows[0] && rows[0].patching);
}

// ── Agent-side DB instance liveness probe ────────────────────────────────────
// Ask the agent (co-located on the DB host) to open a real connection and run
// SELECT 1 FROM DUAL via POST /api/ping → oracle_listener_up. Because DUAL is only
// readable when the instance is OPEN, true = the instance is genuinely up. Two reasons
// this must run ON the agent, not from the cloud:
//   1. A firewall between TuneVault and the customer DB can't false-negative it (the
//      agent connects locally), unlike the old cloud-side SELECT-1 probe.
//   2. It reflects real connectability through the (possibly up) listener — so a
//      listener whose lsnrctl *status command* is broken but which still accepts
//      connections reads correctly as up, which lsnrctl status does not.
// Returns: true  = instance up (explicit oracle_listener_up === true),
//          false = the agent's probe explicitly reported down (oracle_listener_up === false),
//          null  = UNKNOWN — anything else. Every failure/ambiguous path maps to null,
//                  NEVER to "down": timeout, non-200, missing creds, AND a 200 whose body
//                  is malformed or lacks a boolean oracle_listener_up. Only an explicit
//                  boolean from a real /api/ping response is trusted; UNKNOWN leaves
//                  heartbeat state untouched and never contributes to a STARTUP decision.
//                  (Since probeInstanceViaAgent is now the sole gate before STARTUP — the
//                  old design required two independent probes to agree — nothing but a
//                  clear, explicit down reading may push toward firing.)
async function probeInstanceViaAgent(connectionId) {
  let c;
  try {
    const { rows } = await pool.query(
      `SELECT host, port, service_name, username, encrypted_password
       FROM oracle_connections WHERE id = :p1`,
      { p1: connectionId }
    );
    c = rows[0];
  } catch (err) {
    console.warn(`[autopilot/heartbeat] instance-probe cred load error conn ${connectionId}: ${err.message}`);
    return null;
  }
  // Without stored username/password we can't ask the agent to authenticate a probe.
  // Return UNKNOWN (not "down") so a credential-less connection never fires STARTUP —
  // strictly safer than the old cloud probe, which treated a missing password as down.
  if (!c || !c.service_name || !c.username || !c.encrypted_password) return null;

  try {
    const result = await channel.sendToAgent(connectionId, {
      method: 'POST',
      path: '/api/ping',
      body: {
        host: c.host, port: c.port, service_name: c.service_name,
        username: c.username, password: decrypt(c.encrypted_password),
      },
    }, STATUS_TIMEOUT_MS);
    if (!result || result.statusCode !== 200) return null; // transport/handler failure → UNKNOWN
    const body = result.body || {};
    // Trust ONLY an explicit boolean. A 200 with a missing/non-boolean field (malformed
    // response, wrong shape) is UNKNOWN, never "down" — mapping a malformed body to down
    // would be the same false-Down class of bug this whole change fixes.
    if (body.oracle_listener_up === true)  return true;
    if (body.oracle_listener_up === false) return false;
    return null;
  } catch (err) {
    console.warn(`[autopilot/heartbeat] instance-probe error conn ${connectionId}: ${err.message}`);
    return null;
  }
}

// ── Log helper (never throws — remediation must not die on a log-write error) ─
async function logAction(fields) {
  await pool.query(
    `INSERT INTO auto_remediation_log
       (connection_id, trigger_path, triggered_by, consecutive_count,
        op_key, op_label, outcome, exit_code, stdout_tail, tuneops_ticket_id)
     VALUES (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10)`,
    {
      p1: fields.connectionId, p2: fields.triggerPath, p3: fields.triggeredBy,
      p4: fields.consecutiveCount, p5: fields.opKey, p6: fields.opLabel,
      p7: fields.outcome, p8: fields.exitCode == null ? null : fields.exitCode,
      p9: fields.stdoutTail == null ? null : fields.stdoutTail,
      p10: fields.tuneopsTicketId == null ? null : fields.tuneopsTicketId,
    }
  ).catch(err => console.error('[autopilot] log error:', err.message));
}

// ── Fire a start op via agent-channel ────────────────────────────────────────
// ok requires HTTP 200 AND body.success (handler ran) AND body.ok (exit 0) —
// the same gate routes/ebs-ops.js applies before reporting success.
async function fireStartOp(connectionId, startOp, appsPwd, weblogicPwd, serverName) {
  const timeoutMs = START_TIMEOUTS[startOp] || 40000;

  const reqBody = {
    op: startOp,
    apps_pwd: appsPwd || '',
    weblogic_pwd: weblogicPwd || '',
  };
  // managed_server_start targets one WLS managed server by its discovered name.
  if (serverName) reqBody.server_name = serverName;

  const result = await channel.sendToAgent(connectionId, {
    method: 'POST',
    path: '/api/ebs-ctrl',
    body: reqBody,
  }, timeoutMs);

  const body = (result && result.body) || {};
  return {
    exitCode: body.exit_code == null ? null : body.exit_code,
    stdout: body.stdout || '',
    ok: result && result.statusCode === 200 && body.success === true && body.ok === true,
  };
}

// ── Fire a LONG_OP (WLS Admin) via the background-job model ───────────────────
// createJob → wls_admin_start_bg (returns {started,pid}) → poll apps_job_status
// (returns {state,rc,log_tail}) every 15s up to 10× (~150s) → completeJob. We do
// NOT call startJob, so the row stays 'queued' and the separate ebs-jobs poller
// (which only handles status='running') never races this inline poll. Returns the
// same { ok, exitCode, stdout } shape fireStartOp does.
async function fireWlsAdminRestart(connectionId, appsPwd, weblogicPwd) {
  const t0 = Date.now();
  let jobId = null;
  try {
    const created = await ebsJobsDb.createJob(connectionId, 'wls_admin_start', null);
    jobId = created.id;

    const launch = await channel.sendToAgent(connectionId, {
      method: 'POST', path: '/api/ebs-ctrl',
      body: { op: 'wls_admin_start_bg', job_id: String(jobId), weblogic_pwd: weblogicPwd || '', apps_pwd: appsPwd || '' },
    }, 15000);
    const lb = (launch && launch.body) || {};
    if (lb.started !== true) {
      const msg = lb.error || 'Background start not confirmed';
      await ebsJobsDb.completeJob(jobId, { ok: false, stdout: msg, exit_code: 1, duration_ms: Date.now() - t0 }).catch(() => {});
      return { ok: false, exitCode: 1, stdout: msg };
    }

    // Poll apps_job_status (state ∈ done|failed|cancelled|running|unknown).
    let state = 'running', rc = null, logTail = '';
    for (let i = 0; i < 10; i++) {
      await sleep(15000);
      let pr;
      try {
        pr = await channel.sendToAgent(connectionId, {
          method: 'POST', path: '/api/ebs-ctrl',
          body: { op: 'apps_job_status', job_id: String(jobId) },
        }, 20000);
      } catch (e) { continue; } // transient poll error — try again next tick
      const pb = (pr && pr.body) || {};
      if (pb.success !== true) continue;
      if (pb.log_tail) logTail = pb.log_tail;
      if (pb.state) state = pb.state;
      if (pb.rc != null) rc = pb.rc;
      if (state === 'done' || state === 'failed' || state === 'cancelled') break;
    }

    const ok = state === 'done';
    const exitCode = ok ? 0 : (rc != null ? rc : 1);
    await ebsJobsDb.completeJob(jobId, { ok, stdout: logTail, exit_code: exitCode, duration_ms: Date.now() - t0 }).catch(() => {});
    const tail = (state === 'running' || state === 'unknown')
      ? (logTail + '\n[still running after ~150s — may finish on its own]')
      : logTail;
    return { ok, exitCode, stdout: tail };
  } catch (err) {
    if (jobId != null) await ebsJobsDb.completeJob(jobId, { ok: false, stdout: err.message, exit_code: 1, duration_ms: Date.now() - t0 }).catch(() => {});
    return { ok: false, exitCode: 1, stdout: err.message };
  }
}

// Parse a managed_servers_status response into [{ name, state, raw }] by discovering
// server names from the TV_SERVER_NAME / TV_SERVER_EXIT blocks the proxy emits — no
// hardcoded names. Classification mirrors oracle-proxy.py exactly (states RUNNING |
// DOWN | TIMEOUT | NOT_DEPLOYED | UNKNOWN) so we never diverge from what the proxy
// itself considers down.
function parseServerNames(stdout) {
  const out = [];
  let cur = null, lines = [], exit = 0;
  for (const line of String(stdout || '').split('\n')) {
    if (line.indexOf('TV_SERVER_NAME ') === 0) {
      cur = line.slice(15).trim(); lines = []; exit = 0;
    } else if (line.indexOf('TV_SERVER_EXIT ') === 0) {
      exit = parseInt(line.slice(15).trim(), 10) || 0;
      if (cur) {
        const raw = lines.join('\n');
        const escName = cur.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
        let state;
        if (/TV_SERVER_DISABLED/.test(raw)) state = 'DISABLED';
        else if (exit === 124) state = 'TIMEOUT';
        else if (/invalid server name|invalid managed server/i.test(raw)) state = 'NOT_DEPLOYED';
        else if (new RegExp('\\b' + escName + '\\s+is\\s+running', 'i').test(raw)) state = 'RUNNING';
        else if (/is shutdown|is not running|not running/i.test(raw)) state = 'DOWN';
        else state = 'UNKNOWN';
        out.push({ name: cur, state, raw });
        cur = null;
      }
    } else if (cur !== null) {
      lines.push(line);
    }
  }
  return out;
}

// Shared down-signal lifecycle for both simple services and individual managed
// servers: upsert consecutive_down (keyed by o.stateKey), gate on threshold / ADOP /
// cooldown, fire the restart, log, reset. The log + cooldown key is startOp[:serverName]
// so managed servers cool down (30 min) per individual server, not as a group.
async function applyDownSignal(o) {
  const opKey = o.serverName ? (o.startOp + ':' + o.serverName) : o.startOp;

  await pool.query(
    `MERGE INTO service_heartbeat_state t
     USING (SELECT :p1 AS connection_id, :p2 AS op_key FROM dual) s
     ON (t.connection_id = s.connection_id AND t.op_key = s.op_key)
     WHEN MATCHED THEN UPDATE SET
       t.consecutive_down = CASE WHEN :p4 = 'down'
             THEN t.consecutive_down + 1 ELSE 0 END,
       t.last_checked_at = SYSTIMESTAMP,
       t.last_status = :p4
     WHEN NOT MATCHED THEN
       INSERT (connection_id, op_key, consecutive_down, last_checked_at, last_status)
       VALUES (:p1, :p2, :p3, SYSTIMESTAMP, :p4)`,
    { p1: o.connectionId, p2: o.stateKey, p3: o.isDown ? 1 : 0, p4: o.isDown ? 'down' : 'up' }
  );
  // Oracle MERGE has no RETURNING — read consecutive_down back. Path A is a single
  // 5-min cron processing each (connection, op_key) sequentially, so contention on
  // this UNIQUE key is effectively nil.
  const { rows } = await pool.query(
    `SELECT consecutive_down FROM service_heartbeat_state
     WHERE connection_id = :p1 AND op_key = :p2`,
    { p1: o.connectionId, p2: o.stateKey }
  );
  const consecutiveDown = (rows[0] && rows[0].consecutive_down) || 0;
  if (!o.isDown || consecutiveDown < o.threshold) return;

  if (o.adopActive) {
    await logAction({ connectionId: o.connectionId, triggerPath: 'heartbeat', triggeredBy: o.triggeredBy,
      consecutiveCount: consecutiveDown, opKey, opLabel: o.label, outcome: 'skipped_adop' });
    return;
  }
  if (await isCoolingDown(o.connectionId, opKey)) {
    // Suppressed by cooldown — log it (outcome='skipped_cooldown') so the Activity Log
    // distinguishes "held back by cooldown" from "not firing at all". Does NOT extend the
    // window: isCoolingDown gates on success|failed only, so skipped_cooldown never counts.
    const remaining = await cooldownMinutesRemaining(o.connectionId, opKey);
    await logAction({ connectionId: o.connectionId, triggerPath: 'heartbeat', triggeredBy: o.triggeredBy,
      consecutiveCount: consecutiveDown, opKey, opLabel: o.label, outcome: 'skipped_cooldown',
      stdoutTail: `cooldown active — ${remaining} min remaining` });
    return;
  }

  console.log(`[autopilot] Firing ${opKey} on conn ${o.connectionId} — ${o.triggeredBy} down ${consecutiveDown}x`);
  let outcome = 'failed', exitCodeR = null, stdoutTail = null;
  try {
    // o.fireFn (LONG_OP path, e.g. WLS Admin bg job) overrides the simple fireStartOp.
    const r = o.fireFn
      ? await o.fireFn()
      : await fireStartOp(o.connectionId, o.startOp, o.appsPwd, o.weblogicPwd, o.serverName);
    exitCodeR = r.exitCode;
    stdoutTail = (r.stdout || '').slice(-500);
    // A no-op STARTUP against an already-running instance now exits non-zero with
    // ORA-01081 ("cannot start already-running ORACLE") because the proxy sets WHENEVER
    // SQLERROR EXIT SQL.SQLCODE (3.21.22+). That's benign — record it as its own outcome,
    // NOT 'failed'. It STILL counts toward cooldown (see isCoolingDown's IN-list) so a
    // wrong "down" reading can't re-fire STARTUP every 5 min against a live instance.
    if (r.ok) outcome = 'success';
    else if (/ORA-01081/.test(r.stdout || '')) outcome = 'already_running';
    else outcome = 'failed';
  } catch (err) {
    stdoutTail = err.message;
  }
  await logAction({ connectionId: o.connectionId, triggerPath: 'heartbeat', triggeredBy: o.triggeredBy,
    consecutiveCount: consecutiveDown, opKey, opLabel: o.label, outcome, exitCode: exitCodeR, stdoutTail });

  // Reset after any attempt so the next window must re-accumulate.
  await pool.query(
    `UPDATE service_heartbeat_state SET consecutive_down = 0 WHERE connection_id = :p1 AND op_key = :p2`,
    { p1: o.connectionId, p2: o.stateKey }
  ).catch(() => {});
  console.log(`[autopilot] ${opKey} → ${outcome} (exit ${exitCodeR}) conn ${o.connectionId}`);
}

// State-only heartbeat update (last_status/last_checked_at, no consecutive_down
// bump) — for services polled purely for the heartbeat table when NOT opted into
// auto-restart. Never throws.
async function updateHeartbeatStatusOnly(connectionId, opKey, isUp) {
  await pool.query(
    `MERGE INTO service_heartbeat_state t
     USING (SELECT :p1 AS connection_id, :p2 AS op_key FROM dual) s
     ON (t.connection_id = s.connection_id AND t.op_key = s.op_key)
     WHEN MATCHED THEN UPDATE SET t.last_checked_at = SYSTIMESTAMP, t.last_status = :p3
     WHEN NOT MATCHED THEN
       INSERT (connection_id, op_key, consecutive_down, last_checked_at, last_status)
       VALUES (:p1, :p2, 0, SYSTIMESTAMP, :p3)`,
    { p1: connectionId, p2: opKey, p3: isUp ? 'up' : 'down' }
  ).catch(() => {});
}

// ── PATH A: Heartbeat poll for one connection ────────────────────────────────
// Called from the 5-min cron for EVERY proxy connection. Always polls + updates
// service_heartbeat_state for visibility; only opted-in services (remediate_services)
// go through the restart engine.
async function runHeartbeatForConnection(conn) {
  const connectionId = conn.id;
  const threshold    = conn.remediate_threshold || 3;
  const serverType   = conn.server_type || null;
  // Restart only fires when auto_remediate is ON *and* the service is opted in.
  // auto_remediate OFF (or nothing opted in) → we still poll + update state below
  // for the heartbeat table, but restart nothing (fail-closed).
  const autoRemediate = conn.auto_remediate === true;
  const services      = Array.isArray(conn.remediate_services) ? conn.remediate_services : [];
  const appsPwd      = conn.apps_pwd_enc     ? decrypt(conn.apps_pwd_enc)     : '';
  const weblogicPwd  = conn.weblogic_pwd_enc ? decrypt(conn.weblogic_pwd_enc) : '';

  // Agent must be online before we poll.
  const online = await channel.isAgentConnected(connectionId).catch(() => false);
  if (!online) return;

  const adopActive = await isAdopActive(connectionId);

  // ── Simple services (one status op each; exit-code down signal) ────────────
  for (const svc of HEARTBEAT_SERVICES) {
    if (svc.requiresDiscovery) continue;                 // managed servers handled below
    if (!serviceAppliesTo(svc, serverType)) continue;    // scope polling by server_type

    // Poll status. A non-success/timeout/unreachable response is an UNKNOWN, never a
    // "down" signal — treating a probe error as down would trigger spurious restarts.
    // Two poll shapes:
    //   agentInstanceProbe (DB Instance Startup) — agent-side /api/ping connection probe
    //     (probeInstanceViaAgent); ok = the instance answered SELECT 1 FROM DUAL.
    //   everything else — /api/ebs-ctrl status op; ok = body.ok (the proxy's own exit-code
    //     verdict). wls_admin_status is slow, so it gets a longer poll timeout.
    let ok;
    if (svc.agentInstanceProbe) {
      const up = await probeInstanceViaAgent(connectionId);
      if (up === null) continue;   // agent unreachable / probe error / no creds → UNKNOWN
      ok = up;
    } else {
      try {
        const result = await channel.sendToAgent(connectionId, {
          method: 'POST',
          path: '/api/ebs-ctrl',
          body: { op: svc.statusOp, apps_pwd: appsPwd, weblogic_pwd: weblogicPwd },
        }, svc.isLongOp ? WLS_STATUS_TIMEOUT_MS : STATUS_TIMEOUT_MS);
        const body = (result && result.body) || {};
        if (!result || result.statusCode !== 200 || body.success !== true) {
          console.warn(`[autopilot/heartbeat] non-success poll conn ${connectionId} ${svc.statusOp}: http=${result && result.statusCode} success=${body.success}`);
          continue; // leave heartbeat state untouched
        }
        // Trust the proxy's ok. NOTE: for /api/ebs-ctrl DB/shell ops this is a SHELL
        // EXIT-CODE check (_db_ok = _db_exit == 0, oracle-proxy.py ~:6533) — it is NOT the
        // connection-based signal the health check uses; that's exactly why DB Instance
        // Startup above uses the /api/ping probe instead. Never re-implement ok in Node.
        ok = body.ok;
      } catch (pollErr) {
        console.warn(`[autopilot/heartbeat] poll error conn ${connectionId} ${svc.statusOp}: ${pollErr.message}`);
        continue;
      }
    }

    // db_startup_open keys its state by startOp so it never shares the db_listener_status
    // counter with db_listener_start; other services key by statusOp.
    const stateKey     = svc.agentInstanceProbe ? svc.startOp : svc.statusOp;
    const triggeredBy  = svc.agentInstanceProbe ? 'db_instance_probe' : svc.statusOp;

    // Source of truth: ok=false => down. (last_status becomes ok ? 'up' : 'down' via
    // updateHeartbeatStatusOnly(..., !isDown).)
    const isDown = !ok;

    // auto_remediate off OR not opted in → keep heartbeat state current (visibility)
    // but skip the restart engine. Keyed by stateKey (NOT statusOp) to avoid the
    // db_startup_open collision.
    if (!autoRemediate || !services.includes(svc.startOp)) {
      await updateHeartbeatStatusOnly(connectionId, stateKey, !isDown);
      continue;
    }

    // LONG_OP services (WLS Admin) restart via the background-job model, not fireStartOp.
    const fireFn = svc.isLongOp
      ? (async () => fireWlsAdminRestart(connectionId, appsPwd, weblogicPwd))
      : undefined;

    await applyDownSignal({
      connectionId, stateKey, isDown, threshold, adopActive,
      startOp: svc.startOp, label: svc.label, triggeredBy, appsPwd, weblogicPwd, fireFn,
    });
  }

  // ── Managed servers (OACore/Forms/OAFM) — dynamic per-server discovery ─────
  await runManagedServerHeartbeat({ connectionId, services, threshold, adopActive, appsPwd, weblogicPwd, serverType, autoRemediate });
}

// One shared managed_servers_status poll covers every opted-in managed-server group;
// server names are discovered from the response (never hardcoded), each DOWN server
// restarted individually via managed_server_start(server_name).
async function runManagedServerHeartbeat({ connectionId, services, threshold, adopActive, appsPwd, weblogicPwd, serverType, autoRemediate }) {
  // Managed servers are app-tier only. Always poll (for the heartbeat table); the
  // restart is gated per group below.
  if (serverType !== 'apps' && serverType !== 'both') return;
  // managed_servers_status AND managed_server_start both need the WebLogic admin pw.
  if (!weblogicPwd) return;

  let stdout;
  try {
    const result = await channel.sendToAgent(connectionId, {
      method: 'POST',
      path: '/api/ebs-ctrl',
      body: { op: 'managed_servers_status', apps_pwd: appsPwd, weblogic_pwd: weblogicPwd },
    }, MANAGED_STATUS_TIMEOUT_MS);
    const body = (result && result.body) || {};
    if (!result || result.statusCode !== 200 || body.success !== true) {
      console.warn(`[autopilot/heartbeat] managed_servers_status non-success conn ${connectionId}: http=${result && result.statusCode}`);
      return;
    }
    stdout = body.stdout || '';
    // Can't evaluate → skip (never restart on doubt).
    if (/NO_WLS_PWD|NO_SERVERS_FOUND/.test(stdout)) return;
  } catch (pollErr) {
    console.warn(`[autopilot/heartbeat] managed_servers_status error conn ${connectionId}: ${pollErr.message}`);
    return;
  }

  const servers = parseServerNames(stdout);
  const groups = HEARTBEAT_SERVICES.filter(s => s.requiresDiscovery);
  for (const grp of groups) {
    const optedIn = autoRemediate && services.includes(grp.groupKey);
    for (const srv of servers) {
      const lc = srv.name.toLowerCase();
      if (lc.indexOf(grp.match) === -1) continue;           // not this group's server
      if (lc.indexOf('c4ws') !== -1 || lc.indexOf('oaea') !== -1) continue; // optional — never auto-restart
      // Only a clear DOWN/RUNNING is a signal; TIMEOUT / UNKNOWN / NOT_DEPLOYED are
      // ambiguous — leave state untouched, never act on doubt.
      // Only DOWN and RUNNING are actionable. DISABLED (group disabled in the
      // context file), NOT_DEPLOYED, TIMEOUT and UNKNOWN are all deliberately
      // inert: restarting a server the instance does not run is not a repair.
      if (srv.state !== 'DOWN' && srv.state !== 'RUNNING') continue;
      const isDown = srv.state === 'DOWN';
      // Not opted in → update heartbeat state for visibility, but don't restart.
      if (!optedIn) {
        await updateHeartbeatStatusOnly(connectionId, srv.name, !isDown);
        continue;
      }
      await applyDownSignal({
        connectionId, stateKey: srv.name, isDown, threshold, adopActive,
        startOp: grp.startOp, serverName: srv.name,
        label: 'Restart ' + srv.name + ' (' + grp.groupKey + ')',
        triggeredBy: 'managed_servers_status:' + srv.name, appsPwd, weblogicPwd,
      });
    }
  }
}

// ── PATH B: HC finding trigger ───────────────────────────────────────────────
// Called from services/schedule-runner.js after finding_history upsert. Only
// fires for check_ids in HC_REMEDIATION_MAP once occurrence_count ≥ threshold.
async function maybeRemediateFromHC({ connectionId, checkId, consecutiveCount }) {
  try {
    const mapping = HC_REMEDIATION_MAP[checkId];
    if (!mapping) return;

    const { rows: schedRows } = await pool.query(
      `SELECT cs.auto_remediate, cs.remediate_threshold, cs.remediate_services,
              oc.apps_pwd_enc, oc.weblogic_pwd_enc
       FROM connection_schedules cs
       JOIN oracle_connections oc ON oc.id = cs.connection_id
       WHERE cs.connection_id = :p1`,
      { p1: connectionId }
    );
    const sched = schedRows[0];
    if (!sched || !sched.auto_remediate) return;

    const threshold = sched.remediate_threshold || 3;
    if (consecutiveCount < threshold) return;

    // Only fire if the user opted this connection's CM (adcmctl_start) in.
    const services = Array.isArray(sched.remediate_services) ? sched.remediate_services : [];
    if (!services.includes(mapping.startOp)) return;

    if (await isAdopActive(connectionId)) {
      await logAction({ connectionId, triggerPath: 'hc_finding', triggeredBy: checkId,
        consecutiveCount, opKey: mapping.startOp, opLabel: mapping.label, outcome: 'skipped_adop' });
      return;
    }

    if (await isCoolingDown(connectionId, mapping.startOp)) {
      const remaining = await cooldownMinutesRemaining(connectionId, mapping.startOp);
      await logAction({ connectionId, triggerPath: 'hc_finding', triggeredBy: checkId,
        consecutiveCount, opKey: mapping.startOp, opLabel: mapping.label, outcome: 'skipped_cooldown',
        stdoutTail: `cooldown active — ${remaining} min remaining` });
      return;
    }

    const online = await channel.isAgentConnected(connectionId).catch(() => false);
    if (!online) {
      await logAction({ connectionId, triggerPath: 'hc_finding', triggeredBy: checkId,
        consecutiveCount, opKey: mapping.startOp, opLabel: mapping.label, outcome: 'skipped_no_agent' });
      return;
    }

    const appsPwd     = sched.apps_pwd_enc     ? decrypt(sched.apps_pwd_enc)     : '';
    const weblogicPwd = sched.weblogic_pwd_enc ? decrypt(sched.weblogic_pwd_enc) : '';

    console.log(`[autopilot/hc] Firing ${mapping.startOp} on conn ${connectionId} — ${checkId} down ${consecutiveCount}x`);

    let outcome = 'failed', exitCode = null, stdoutTail = null;
    try {
      const r = await fireStartOp(connectionId, mapping.startOp, appsPwd, weblogicPwd);
      exitCode = r.exitCode;
      stdoutTail = (r.stdout || '').slice(-500);
      outcome = r.ok ? 'success' : 'failed';
    } catch (err) {
      stdoutTail = err.message;
    }

    await logAction({ connectionId, triggerPath: 'hc_finding', triggeredBy: checkId,
      consecutiveCount, opKey: mapping.startOp, opLabel: mapping.label,
      outcome, exitCode, stdoutTail });

    console.log(`[autopilot/hc] ${mapping.startOp} → ${outcome} (exit ${exitCode}) conn ${connectionId}`);
  } catch (err) {
    console.error(`[autopilot/hc] error conn ${connectionId}:`, err.message);
  }
}

module.exports = { runHeartbeatForConnection, maybeRemediateFromHC, HEARTBEAT_SERVICES, HC_REMEDIATION_MAP, RESTARTABLE_OPS, parseServerNames };
