/**
 * db/schedules.js — connection_schedules and finding_history query functions.
 *
 * Owns: connection_schedules CRUD, finding_history delta reads/writes.
 * Does NOT own: schedule execution logic, email sending (see services/alert-mailer.js),
 *               or health check results (see check_results table queries in server.js).
 */

'use strict';

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

// ── connection_schedules ──────────────────────────────────────────────────────

/**
 * Get schedule for a connection (returns null if not yet configured).
 */
async function getSchedule(connectionId) {
  const { rows } = await pool.query(
    `SELECT * FROM connection_schedules WHERE connection_id = :p1`,
    { p1: connectionId }
  );
  return rows[0] || null;
}

/**
 * Upsert schedule config for a connection.
 * Returns the saved schedule row.
 */
async function upsertSchedule({ connectionId, userId, cadenceMinutes, enabled, alertEmail, severityThreshold, autoCreateTickets = true }) {
  const nextRunAt = enabled
    ? new Date(Date.now() + cadenceMinutes * 60 * 1000)
    : null;

  await pool.query(
    `MERGE INTO connection_schedules t
     USING (SELECT :p1 AS connection_id, :p2 AS user_id, :p3 AS cadence_minutes,
                   :p4 AS enabled, :p5 AS next_run_at, :p6 AS alert_email,
                   :p7 AS severity_threshold, :p8 AS auto_create_tickets FROM DUAL) s
        ON (t.connection_id = s.connection_id)
     WHEN MATCHED THEN UPDATE SET
       cadence_minutes     = s.cadence_minutes,
       enabled             = s.enabled,
       next_run_at         = CASE WHEN s.enabled = TRUE THEN s.next_run_at ELSE NULL END,
       alert_email         = s.alert_email,
       severity_threshold  = s.severity_threshold,
       auto_create_tickets = s.auto_create_tickets,
       updated_at          = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (connection_id, user_id, cadence_minutes, enabled, next_run_at, alert_email, severity_threshold, auto_create_tickets, updated_at)
       VALUES (s.connection_id, s.user_id, s.cadence_minutes, s.enabled, s.next_run_at, s.alert_email, s.severity_threshold, s.auto_create_tickets, CURRENT_TIMESTAMP)`,
    { p1: connectionId, p2: userId, p3: cadenceMinutes, p4: pool.bool(enabled),
      p5: pool.tstz(nextRunAt), p6: alertEmail, p7: severityThreshold, p8: pool.bool(autoCreateTickets) }
  );
  const { rows } = await pool.query(`SELECT * FROM connection_schedules WHERE connection_id = :p1`, { p1: connectionId });
  return rows[0];
}

/**
 * Find all schedule rows that are due (enabled, not snoozed, next_run_at <= now).
 */
async function getDueSchedules() {
  const { rows } = await pool.query(
    `SELECT cs.*, oc.name AS connection_name, oc.host, oc.port, oc.service_name,
            oc.username, oc.encrypted_password, oc.connection_type, oc.proxy_url,
            oc.proxy_api_key_enc, oc.user_id, oc.server_type,
            oc.apps_pwd_enc, oc.weblogic_pwd_enc
     FROM connection_schedules cs
     JOIN oracle_connections oc ON oc.id = cs.connection_id
     WHERE cs.enabled = TRUE
       AND cs.next_run_at <= CURRENT_TIMESTAMP
       AND (cs.snoozed_until IS NULL OR cs.snoozed_until < CURRENT_TIMESTAMP)`
  );
  console.log('[scheduler/getDueSchedules] found:', rows.length, 'due schedules');
  return rows;
}

/**
 * Advance next_run_at after a successful tick.
 * Sets last_run_at = NOW() and next_run_at = NOW() + cadence.
 */
async function advanceSchedule(scheduleId, cadenceMinutes) {
  await pool.query(
    `UPDATE connection_schedules
     SET last_run_at = CURRENT_TIMESTAMP,
         next_run_at = CURRENT_TIMESTAMP + NUMTODSINTERVAL(:p2, 'MINUTE'),
         updated_at  = CURRENT_TIMESTAMP
     WHERE id = :p1`,
    { p1: scheduleId, p2: cadenceMinutes }
  );
}

/**
 * Record that an alert email was sent.
 */
async function recordAlertSent(scheduleId, subject) {
  await pool.query(
    `UPDATE connection_schedules
     SET last_alert_sent_at = CURRENT_TIMESTAMP,
         last_alert_subject  = :p2,
         updated_at          = CURRENT_TIMESTAMP
     WHERE id = :p1`,
    { p1: scheduleId, p2: subject }
  );
}

/**
 * Snooze a schedule for N hours (sets enabled=false + snoozed_until).
 * Used by the "snooze" link in alert emails.
 */
async function snoozeSchedule(scheduleId, hours = 24) {
  await pool.query(
    `UPDATE connection_schedules
     SET snoozed_until = CURRENT_TIMESTAMP + NUMTODSINTERVAL(:p2, 'HOUR'),
         updated_at    = CURRENT_TIMESTAMP
     WHERE id = :p1`,
    { p1: scheduleId, p2: hours }
  );
}

/**
 * Snooze by signed token (token is base64url of scheduleId).
 * Returns { ok: boolean, scheduleId }.
 */
async function snoozeByToken(token, hours = 24) {
  try {
    const id = parseInt(Buffer.from(token, 'base64url').toString(), 10);
    if (!id || isNaN(id)) return { ok: false };
    await snoozeSchedule(id, hours);
    return { ok: true, scheduleId: id };
  } catch {
    return { ok: false };
  }
}

/**
 * Get all schedules with connection info — for admin page.
 */
async function getAllSchedulesAdmin() {
  const { rows } = await pool.query(
    `SELECT cs.*, oc.name AS connection_name, oc.host, u.email AS user_email
     FROM connection_schedules cs
     JOIN oracle_connections oc ON oc.id = cs.connection_id
     JOIN users u ON u.id = cs.user_id
     ORDER BY cs.updated_at DESC`
  );
  return rows;
}

// ── finding_history ───────────────────────────────────────────────────────────

/**
 * Get all open (unresolved) findings for a connection.
 * Returns map: finding_key → row
 */
async function getOpenFindings(connectionId) {
  const { rows } = await pool.query(
    `SELECT * FROM finding_history
     WHERE connection_id = :p1 AND resolved_at IS NULL`,
    { p1: connectionId }
  );
  const map = {};
  for (const row of rows) map[row.finding_key] = row;
  return map;
}

/**
 * Upsert a finding. Returns { isNew: bool, isWorsened: bool, row }.
 * - isNew: finding_key not previously seen (or was resolved before).
 * - isWorsened: same key, severity escalated (green/amber → red).
 */
async function upsertFinding({ connectionId, scheduleId, checkId, findingKey, title, metricLine, remediation, severity }) {
  // Try to find existing open finding
  const existing = await pool.query(
    `SELECT * FROM finding_history
     WHERE connection_id = :p1 AND finding_key = :p2 AND resolved_at IS NULL`,
    { p1: connectionId, p2: findingKey }
  );

  const severityRank = { ok: 0, green: 0, info: 1, amber: 2, warning: 2, red: 3, critical: 3 };
  const rankOf = s => severityRank[s?.toLowerCase()] ?? 0;

  if (existing.rows.length === 0) {
    // New finding (or previously resolved, re-opened)
    await pool.query(
      `MERGE INTO finding_history t
       USING (SELECT :p1 AS connection_id, :p4 AS finding_key FROM DUAL) s
          ON (t.connection_id = s.connection_id AND t.finding_key = s.finding_key)
       WHEN MATCHED THEN UPDATE SET
         resolved_at  = NULL,
         severity     = :p8,
         title        = :p5,
         metric_line  = :p6,
         remediation  = :p7,
         last_seen_at = CURRENT_TIMESTAMP,
         occurrence_count = 1
       WHEN NOT MATCHED THEN
         INSERT (connection_id, schedule_id, check_id, finding_key, title, metric_line,
                 remediation, severity, first_seen_at, last_seen_at, occurrence_count)
         VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1)`,
      { p1: connectionId, p2: scheduleId, p3: checkId, p4: findingKey, p5: title,
        p6: metricLine, p7: remediation, p8: severity }
    );
    const { rows } = await pool.query(
      `SELECT * FROM finding_history WHERE connection_id = :p1 AND finding_key = :p2`,
      { p1: connectionId, p2: findingKey }
    );
    return { isNew: true, isWorsened: false, row: rows[0] };
  }

  const prev = existing.rows[0];
  const isWorsened = rankOf(severity) > rankOf(prev.severity);

  await pool.query(
    `UPDATE finding_history
     SET severity     = :p1,
         title        = :p2,
         metric_line  = :p3,
         remediation  = :p4,
         last_seen_at = CURRENT_TIMESTAMP,
         occurrence_count = occurrence_count + 1
     WHERE id = :p5`,
    { p1: severity, p2: title, p3: metricLine, p4: remediation, p5: prev.id }
  );
  const { rows } = await pool.query(`SELECT * FROM finding_history WHERE id = :p1`, { p1: prev.id });
  return { isNew: false, isWorsened, row: rows[0] };
}

/**
 * Mark findings as resolved if their finding_key is NOT in currentKeys.
 * Called at end of each delta run to clean up disappeared findings.
 */
async function resolveStaleFindings(connectionId, currentKeys) {
  if (currentKeys.length === 0) {
    // All findings resolved
    await pool.query(
      `UPDATE finding_history
       SET resolved_at = CURRENT_TIMESTAMP
       WHERE connection_id = :p1 AND resolved_at IS NULL`,
      { p1: connectionId }
    );
    return;
  }
  const kb = { p1: connectionId }; const kn = currentKeys.map((v, i) => { kb['k' + i] = v; return ':k' + i; });
  await pool.query(
    `UPDATE finding_history
     SET resolved_at = CURRENT_TIMESTAMP
     WHERE connection_id = :p1
       AND resolved_at IS NULL
       AND finding_key NOT IN (${kn.join(', ')})`,
    kb
  );
}

/**
 * Check if an alert was already sent for this (connection_id, finding_key, severity) within 6h.
 * Used for idempotency / flap suppression.
 */
async function wasAlertRecentlySent(scheduleId, windowHours = 6) {
  const { rows } = await pool.query(
    `SELECT 1 FROM connection_schedules
     WHERE id = :p1
       AND last_alert_sent_at > CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p2, 'HOUR')`,
    { p1: scheduleId, p2: windowHours }
  );
  return rows.length > 0;
}

module.exports = {
  getSchedule,
  upsertSchedule,
  getDueSchedules,
  advanceSchedule,
  recordAlertSent,
  snoozeSchedule,
  snoozeByToken,
  getAllSchedulesAdmin,
  getOpenFindings,
  upsertFinding,
  resolveStaleFindings,
  wasAlertRecentlySent,
};
