/**
 * db/alert-policies.js — alert_policies and alert_events query functions.
 *
 * Owns: CRUD for alert_policies, alert_events reads/writes, escalation state.
 * Does NOT own: channel notification logic (services/alert-notifier.js),
 *               policy evaluation (services/alert-policy-evaluator.js),
 *               or default policy seeding (called from routes on first login).
 */

'use strict';

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

// ── alert_policies ─────────────────────────────────────────────────────────────

/**
 * List all policies for a user (with optional connection filter).
 */
async function listPolicies(userId, { connectionId } = {}) {
  let q = `
    SELECT ap.*,
           oc.name AS connection_name
    FROM alert_policies ap
    LEFT JOIN oracle_connections oc ON oc.id = ap.connection_id
    WHERE ap.user_id = :p1
  `;
  const binds = { p1: userId };
  if (connectionId !== undefined) {
    binds.p2 = connectionId;
    q += ` AND (ap.connection_id = :p2 OR ap.connection_id IS NULL)`;
  }
  q += ' ORDER BY ap.is_default ASC, ap.created_at ASC';
  const { rows } = await pool.query(q, binds);
  return rows;
}

/**
 * Get a single policy by id (must belong to userId).
 */
async function getPolicy(id, userId) {
  const { rows } = await pool.query(
    `SELECT ap.*, oc.name AS connection_name
     FROM alert_policies ap
     LEFT JOIN oracle_connections oc ON oc.id = ap.connection_id
     WHERE ap.id = :p1 AND ap.user_id = :p2`,
    { p1: id, p2: userId }
  );
  return rows[0] || null;
}

/**
 * Get active policies for a connection — used by evaluator after each health check.
 * Returns policies scoped to this connection + any "all connections" policies.
 */
async function getActivePoliciesForConnection(userId, connectionId) {
  const { rows } = await pool.query(
    `SELECT * FROM alert_policies
     WHERE user_id = :p1
       AND is_active = TRUE
       AND (connection_id = :p2 OR connection_id IS NULL)
     ORDER BY is_default ASC, created_at ASC`,
    { p1: userId, p2: connectionId }
  );
  return rows;
}

/**
 * Create a new policy. Returns the created row.
 */
async function createPolicy({
  userId, name, checkType, connectionId,
  conditions, sustainedMinutes, notificationChannels, escalationChain, isDefault
}) {
  const ins = await pool.query(
    `INSERT INTO alert_policies
       (user_id, name, check_type, connection_id, conditions, sustained_minutes,
        notification_channels, escalation_chain, is_active, is_default, created_at, updated_at)
     VALUES (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,TRUE,:p9,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
     RETURNING id INTO :out_id`,
    {
      p1: userId, p2: name, p3: checkType, p4: connectionId || null,
      // JSON columns — a JS array cannot be bound directly (NJS-044).
      p5: JSON.stringify(conditions || []),
      p6: sustainedMinutes || null,
      p7: JSON.stringify(notificationChannels || []),
      p8: JSON.stringify(escalationChain || []),
      p9: pool.bool(isDefault || false),
      out_id: pool.outNumber(),
    }
  );
  const { rows } = await pool.query(
    `SELECT * FROM alert_policies WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return rows[0];
}

/**
 * Update an existing policy. Partial update — only supplied fields change.
 */
async function updatePolicy(id, userId, updates) {
  const fields = [];
  const binds = {};
  let n = 0;

  const allowed = ['name','check_type','connection_id','conditions','sustained_minutes',
                   'notification_channels','escalation_chain','is_active'];
  const jsonCols = ['conditions','notification_channels','escalation_chain'];

  for (const [key, val] of Object.entries(updates)) {
    if (!allowed.includes(key)) continue;
    const bind = `p${++n}`;
    // is_active is BOOLEAN and can arrive as null/undefined; an untyped null binds
    // as CHAR, which Oracle refuses against a BOOLEAN column (ORA-00932).
    binds[bind] = jsonCols.includes(key) ? JSON.stringify(val)
                : key === 'is_active'    ? pool.bool(val)
                : val;
    fields.push(`${key} = :${bind}`);
  }
  if (fields.length === 0) return null;

  binds.p_id = id;
  binds.p_user = userId;
  const upd = await pool.query(
    `UPDATE alert_policies
     SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP
     WHERE id = :p_id AND user_id = :p_user`,
    binds
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(
    `SELECT * FROM alert_policies WHERE id = :p1`, { p1: id });
  return rows[0] || null;
}

/**
 * Delete a policy (user-scoped). Cascades to alert_events.
 */
async function deletePolicy(id, userId) {
  const { rowCount } = await pool.query(
    'DELETE FROM alert_policies WHERE id = :p1 AND user_id = :p2',
    { p1: id, p2: userId }
  );
  return rowCount > 0;
}

/**
 * Toggle is_active for a policy.
 */
async function togglePolicy(id, userId, isActive) {
  const upd = await pool.query(
    `UPDATE alert_policies
     SET is_active = :p1, updated_at = CURRENT_TIMESTAMP
     WHERE id = :p2 AND user_id = :p3`,
    { p1: pool.bool(isActive), p2: id, p3: userId }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(
    `SELECT * FROM alert_policies WHERE id = :p1`, { p1: id });
  return rows[0] || null;
}

/**
 * Seed default policies for a new user if they have none yet.
 * Idempotent — skips if defaults already exist.
 */
async function seedDefaultPolicies(userId) {
  const { rows: existing } = await pool.query(
    'SELECT id FROM alert_policies WHERE user_id = :p1 AND is_default = TRUE FETCH FIRST 1 ROWS ONLY',
    { p1: userId }
  );
  if (existing.length > 0) return; // already seeded

  const defaults = [
    {
      name: 'Tablespace Warning (>90%)',
      checkType: 'tablespace_usage',
      conditions: [{ metric: 'usage_percent', operator: '>=', value: 90, severity: 'warning' }],
      notificationChannels: [{ type: 'email', config: { emails: [] }, is_active: true }],
      escalationChain: [],
    },
    {
      name: 'Tablespace Critical (>95%)',
      checkType: 'tablespace_usage',
      conditions: [{ metric: 'usage_percent', operator: '>=', value: 95, severity: 'critical' }],
      notificationChannels: [{ type: 'email', config: { emails: [] }, is_active: true }],
      escalationChain: [
        { delay_minutes: 15, notification_channels: [{ type: 'email', config: { emails: [] }, is_active: true }] }
      ],
    },
    {
      name: 'Low Health Score (<50)',
      checkType: 'health_score',
      conditions: [{ metric: 'score', operator: '<', value: 50, severity: 'warning' }],
      notificationChannels: [{ type: 'email', config: { emails: [] }, is_active: true }],
      escalationChain: [],
    },
    {
      name: 'Failed Health Check',
      checkType: 'check_failure',
      conditions: [{ metric: 'status', operator: '==', value: 'failed', severity: 'critical' }],
      notificationChannels: [{ type: 'email', config: { emails: [] }, is_active: true }],
      escalationChain: [],
    },
  ];

  for (const d of defaults) {
    await createPolicy({ userId, ...d, isDefault: true });
  }
}

// ── alert_events ───────────────────────────────────────────────────────────────

/**
 * Get open (non-resolved) events for a policy + connection pair.
 * Used for deduplication: don't re-trigger if already open.
 */
async function getOpenEvent(policyId, connectionId) {
  const { rows } = await pool.query(
    `SELECT * FROM alert_events
     WHERE policy_id = :p1
       AND connection_id = :p2
       AND status IN ('triggered','acknowledged','escalated')
     ORDER BY triggered_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: policyId, p2: connectionId }
  );
  return rows[0] || null;
}

/**
 * Create a new alert event when a policy fires.
 */
async function createEvent({ policyId, connectionId, checkRunId, currentValue, severity }) {
  const ins = await pool.query(
    `INSERT INTO alert_events
       (policy_id, connection_id, check_run_id, current_value, severity,
        status, escalation_step, triggered_at, notifications_sent)
     VALUES (:p1,:p2,:p3,:p4,:p5,'triggered',0,CURRENT_TIMESTAMP,'[]')
     RETURNING id INTO :out_id`,
    { p1: policyId, p2: connectionId, p3: checkRunId || null, p4: currentValue, p5: severity,
      out_id: pool.outNumber() }
  );
  const { rows } = await pool.query(
    `SELECT * FROM alert_events WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return rows[0];
}

/**
 * Resolve an open alert event.
 */
async function resolveEvent(eventId) {
  await pool.query(
    `UPDATE alert_events
     SET status = 'resolved', resolved_at = CURRENT_TIMESTAMP
     WHERE id = :p1 AND status != 'resolved'`,
    { p1: eventId }
  );
}

/**
 * Acknowledge an event (user clicked "acknowledge").
 */
async function acknowledgeEvent(eventId, userId) {
  // Out-of-order placeholders ($2 before $1) — named binds keep the mapping.
  const upd = await pool.query(
    `UPDATE alert_events
     SET status = 'acknowledged', acknowledged_at = CURRENT_TIMESTAMP, acknowledged_by = :p2
     WHERE id = :p1 AND status IN ('triggered','escalated')`,
    { p1: eventId, p2: userId }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(
    `SELECT * FROM alert_events WHERE id = :p1`, { p1: eventId });
  return rows[0] || null;
}

/**
 * Append a notification attempt to the event's notifications_sent log.
 */
async function appendNotification(eventId, entry) {
  // Postgres appended in one statement: notifications_sent || $2::jsonb.
  // Oracle's equivalent, JSON_TRANSFORM(... APPEND '$' = ...), cannot be used:
  // it writes OSON version 2, which node-oracledb 6.10 then refuses to read
  // back (NJS-126). Any later SELECT of this column — including the plain
  // `SELECT *` in createEvent and listEvents — would start failing.
  //
  // So this is a read-modify-write, held under SELECT ... FOR UPDATE in one
  // transaction. The row lock is what stops two concurrent notification
  // attempts from each reading the same array and one overwriting the other's
  // entry; without it this would silently lose delivery records.
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      `SELECT notifications_sent FROM alert_events WHERE id = :p1 FOR UPDATE`,
      { p1: eventId }
    );
    if (!rows.length) { await client.query('ROLLBACK'); return; }
    const current = Array.isArray(rows[0].notifications_sent) ? rows[0].notifications_sent : [];
    await client.query(
      `UPDATE alert_events SET notifications_sent = :p2 WHERE id = :p1`,
      { p1: eventId, p2: JSON.stringify(current.concat([entry])) }
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

/**
 * Advance escalation step and set next_escalation_at.
 */
async function advanceEscalation(eventId, nextStep, nextEscalationAt) {
  await pool.query(
    `UPDATE alert_events
     SET escalation_step = :p1,
         status = 'escalated',
         next_escalation_at = :p2
     WHERE id = :p3`,
    { p1: nextStep, p2: pool.tstz(nextEscalationAt), p3: eventId }
  );
}

/**
 * Get all events due for escalation.
 */
async function getDueEscalations() {
  const { rows } = await pool.query(
    `SELECT ae.*, ap.notification_channels, ap.escalation_chain, ap.name AS policy_name,
            ap.user_id,
            oc.name AS connection_name
     FROM alert_events ae
     JOIN alert_policies ap ON ap.id = ae.policy_id
     JOIN oracle_connections oc ON oc.id = ae.connection_id
     WHERE ae.status IN ('triggered','escalated')
       AND ae.next_escalation_at IS NOT NULL
       AND ae.next_escalation_at <= CURRENT_TIMESTAMP`
  );
  return rows;
}

/**
 * List recent events for a user (for the UI).
 */
async function listEvents(userId, { limit = 50, connectionId, status } = {}) {
  let q = `
    SELECT ae.*, ap.name AS policy_name, ap.check_type,
           oc.name AS connection_name
    FROM alert_events ae
    JOIN alert_policies ap ON ap.id = ae.policy_id
    JOIN oracle_connections oc ON oc.id = ae.connection_id
    WHERE ap.user_id = :p_user
  `;
  const binds = { p_user: userId };
  if (connectionId) { binds.p_conn = connectionId; q += ` AND ae.connection_id = :p_conn`; }
  if (status)       { binds.p_status = status;    q += ` AND ae.status = :p_status`; }
  binds.p_limit = limit;
  q += ` ORDER BY ae.triggered_at DESC FETCH FIRST :p_limit ROWS ONLY`;
  const { rows } = await pool.query(q, binds);
  return rows;
}

// ── Queries used by evaluator ─────────────────────────────────────────────────

/**
 * Get the max tablespace usage percent across all recent ST01 check_results for a connection.
 */
async function getMaxTablespaceUsage(connectionId) {
  const { rows } = await pool.query(
    `SELECT MAX(JSON_VALUE(raw_payload, '$.pct_used' RETURNING NUMBER)) AS max_pct
     FROM check_results
     WHERE connection_id = :p1
       AND check_id = 'ST01_TABLESPACE_USAGE'
       AND JSON_VALUE(raw_payload, '$.pct_used') IS NOT NULL`,
    { p1: connectionId }
  );
  return rows[0]?.max_pct != null ? Number(rows[0].max_pct) : null;
}

/** Get health check score for a run. */
async function getCheckScore(checkRunId) {
  // Column is overall_score, not score (there is no `score` column on
  // health_checks). A bare `score` also parses as the Oracle Text SCORE()
  // builtin, so the typo surfaced as ORA-00938 rather than ORA-00904.
  const { rows } = await pool.query(
    'SELECT overall_score FROM health_checks WHERE id = :p1',
    { p1: checkRunId }
  );
  return rows[0]?.overall_score != null ? Number(rows[0].overall_score) : null;
}

/** Get session count from most recent PF01 check_result. */
async function getSessionCount(connectionId) {
  const { rows } = await pool.query(
    `SELECT JSON_VALUE(raw_payload, '$.session_count' RETURNING NUMBER) AS cnt
     FROM check_results
     WHERE connection_id = :p1 AND check_id = 'PF01_SESSION_COUNT'
     ORDER BY executed_at DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return rows[0]?.cnt != null ? Number(rows[0].cnt) : null;
}

/** Get redo log switch frequency from most recent RA01 check_result. */
async function getRedoLogFrequency(connectionId) {
  const { rows } = await pool.query(
    `SELECT JSON_VALUE(raw_payload, '$.switches_per_hour' RETURNING NUMBER) AS freq
     FROM check_results
     WHERE connection_id = :p1 AND check_id = 'RA01_LOG_SWITCH_FREQ'
     ORDER BY executed_at DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return rows[0]?.freq != null ? Number(rows[0].freq) : null;
}

/** Get listener status from most recent CO02 check_result. */
async function getListenerStatus(connectionId) {
  const { rows } = await pool.query(
    `SELECT status FROM check_results
     WHERE connection_id = :p1 AND check_id = 'CO02_LISTENER_STATUS'
     ORDER BY executed_at DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return rows[0]?.status || null;
}

/** Get health check status (for check_failure check type). */
async function getHealthCheckStatus(checkRunId) {
  const { rows } = await pool.query(
    'SELECT status FROM health_checks WHERE id = :p1',
    { p1: checkRunId }
  );
  return rows[0]?.status || null;
}

/** Get worst check_result severity across all recent results for a connection. */
async function getWorstSeverity(connectionId) {
  const { rows } = await pool.query(
    `SELECT status FROM check_results
     WHERE connection_id = :p1
     ORDER BY executed_at DESC FETCH FIRST 100 ROWS ONLY`,
    { p1: connectionId }
  );
  const sevOrder = ['ok','green','info','amber','warning','red','critical'];
  let worst = 'ok';
  for (const r of rows) {
    if (sevOrder.indexOf(r.status) > sevOrder.indexOf(worst)) worst = r.status;
  }
  return worst;
}

/** Count completed health checks in the sustained window. */
async function getRecentCompletedCheckCount(connectionId, minutesWindow) {
  const { rows } = await pool.query(
    `SELECT COUNT(*) AS cnt FROM health_checks
     WHERE connection_id = :p1
       AND created_at > CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p2, 'MINUTE')
       AND status = 'completed'`,
    { p1: connectionId, p2: minutesWindow }
  );
  return Number(rows[0]?.cnt || 0);
}

/** Get connection name and user_id for a connection. */
async function getConnectionInfo(connectionId) {
  const { rows } = await pool.query(
    'SELECT name, user_id FROM oracle_connections WHERE id = :p1',
    { p1: connectionId }
  );
  return rows[0] || null;
}

/** Get user email by userId. */
async function getUserEmail(userId) {
  const { rows } = await pool.query(
    'SELECT email FROM users WHERE id = :p1',
    { p1: userId }
  );
  return rows[0]?.email || null;
}

/** Get user plan tier for tier-gating (route helper). */
async function getUserPlanTier(userId) {
  const { rows } = await pool.query(
    `SELECT uc.plan_tier, t.plan_tier AS team_tier
     FROM users u
     LEFT JOIN user_credits uc ON uc.user_id = u.id
     LEFT JOIN teams t ON t.id = u.team_id
     WHERE u.id = :p1`,
    { p1: userId }
  );
  const row = rows[0] || {};
  return row.team_tier || row.plan_tier || 'free';
}

/** Clear next_escalation_at on an event. */
async function clearEscalationTimer(eventId) {
  await pool.query(
    'UPDATE alert_events SET next_escalation_at = NULL WHERE id = :p1',
    { p1: eventId }
  );
}

/** Advance escalation step without changing status to 'escalated'. */
async function setEscalationStep(eventId, step) {
  await pool.query(
    'UPDATE alert_events SET escalation_step = :p1, next_escalation_at = NULL WHERE id = :p2',
    { p1: step, p2: eventId }
  );
}

/** Get first connection owned by user (for test notification context). */
async function getFirstUserConnection(userId) {
  const { rows } = await pool.query(
    'SELECT name FROM oracle_connections WHERE user_id = :p1 FETCH FIRST 1 ROWS ONLY',
    { p1: userId }
  );
  return rows[0] || null;
}

module.exports = {
  listPolicies,
  getPolicy,
  getActivePoliciesForConnection,
  createPolicy,
  updatePolicy,
  deletePolicy,
  togglePolicy,
  seedDefaultPolicies,
  getOpenEvent,
  createEvent,
  resolveEvent,
  acknowledgeEvent,
  appendNotification,
  advanceEscalation,
  getDueEscalations,
  listEvents,
  // evaluator helpers
  getMaxTablespaceUsage,
  getCheckScore,
  getSessionCount,
  getRedoLogFrequency,
  getListenerStatus,
  getHealthCheckStatus,
  getWorstSeverity,
  getRecentCompletedCheckCount,
  getConnectionInfo,
  getUserEmail,
  getUserPlanTier,
  clearEscalationTimer,
  setEscalationStep,
  getFirstUserConnection,
};
