/**
 * db/ebs-adop-state.js — CRUD for ebs_adop_state table.
 *
 * Owns: upsert + read of the ADOP patch-cycle state snapshot per connection.
 * Does NOT own: detecting ADOP state from Oracle (lib/ebs/adop-state.js),
 *               serving the API (routes/adop-state.js).
 */

'use strict';

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

/**
 * Upsert the ADOP state for a connection.
 * Called after each health-pack run by the schedule-runner or first-run flow.
 *
 * @param {number}  connectionId
 * @param {object}  state  — AdopState object from lib/ebs/adop-state.js
 */
async function upsertAdopState(connectionId, state) {
  await pool.query(
    `MERGE INTO ebs_adop_state t
     USING (SELECT :p1 AS connection_id FROM DUAL) s
        ON (t.connection_id = s.connection_id)
     WHEN MATCHED THEN UPDATE SET
       t.patching               = :p2,
       t.phase                  = :p3,
       t.session_id             = :p4,
       t.started_at             = :p5,
       t.services_in_patch_mode = :p6,
       t.source                 = :p7,
       t.checked_at             = :p8
     WHEN NOT MATCHED THEN INSERT
       (connection_id, patching, phase, session_id, started_at,
        services_in_patch_mode, source, checked_at)
       VALUES (s.connection_id, :p2, :p3, :p4, :p5, :p6, :p7, :p8)`,
    {
      p1: connectionId,
      p2: pool.bool(state.patching),
      p3: state.phase   || null,
      p4: state.session_id !== null && state.session_id !== undefined
        ? String(state.session_id) : null,
      p5: pool.tstz(state.started_at || null),
      p6: JSON.stringify(state.services_in_patch_mode || []),
      p7: state.source  || 'vactive_services_only',
      p8: pool.tstz(state.checked_at || new Date()),
    }
  );
}

/**
 * Get the latest ADOP state for a single connection.
 *
 * @param {number} connectionId
 * @returns {object|null}  row or null if not yet detected
 */
async function getAdopState(connectionId) {
  const { rows } = await pool.query(
    `SELECT connection_id, patching, phase, session_id, started_at,
            services_in_patch_mode, source, checked_at
     FROM   ebs_adop_state
     WHERE  connection_id = :p1`,
    { p1: connectionId }
  );
  return rows[0] || null;
}

/**
 * Get ADOP state for all connections that have patching=TRUE.
 * Used by fleet overview to surface a fleet-wide banner.
 *
 * @param {number[]} connectionIds  — filter to just these connections
 * @returns {object[]}
 */
async function getAdopStateForConnections(connectionIds) {
  if (!connectionIds || connectionIds.length === 0) return [];
  // = ANY(array) -> a generated IN list; Oracle cannot bind an array.
  const binds = {};
  const inList = connectionIds.map((id, i) => { binds[`p${i}`] = id; return `:p${i}`; }).join(', ');
  const { rows } = await pool.query(
    `SELECT connection_id, patching, phase, session_id, started_at,
            services_in_patch_mode, source, checked_at
     FROM   ebs_adop_state
     WHERE  connection_id IN (${inList})`,
    binds
  );
  return rows;
}

/**
 * Return just the patching flag + phase for a connection — lightweight poll.
 * Used by the banner JS poll endpoint.
 *
 * @param {number} connectionId
 * @returns {{ patching: boolean, phase: string|null, checked_at: Date }|null}
 */
async function getAdopPatchingFlag(connectionId) {
  const { rows } = await pool.query(
    `SELECT patching, phase, session_id, started_at, checked_at
     FROM   ebs_adop_state
     WHERE  connection_id = :p1`,
    { p1: connectionId }
  );
  if (!rows[0]) return null;
  return {
    patching:    rows[0].patching,
    phase:       rows[0].phase,
    session_id:  rows[0].session_id,
    started_at:  rows[0].started_at,
    checked_at:  rows[0].checked_at,
  };
}

module.exports = {
  upsertAdopState,
  getAdopState,
  getAdopStateForConnections,
  getAdopPatchingFlag,
};
