wulf-pulse/lib/services/veeam-rpo-service.ts

/**
 * Veeam RPO Service
 * Outcome-based backup alerting: one deduped Autotask ticket per job
 * that has missed its RPO, auto-resolved when backup succeeds.
 */

import postgresClient from './postgres-client';
import { AutotaskClient } from './autotask-client';
import {
  RmmDeviceInfo,
  resolveRmmDevicesForJobs,
  isDeviceOfflineSuppressed,
  hoursOffline,
} from './rmm-device-resolver';

const AT_QUEUE_ID      = 29832283; // Operations Triage
const AT_ISSUE_TYPE    = 38;       // Backups
const AT_SUB_ISSUE     = 637;      // Backup: Veeam Agent for Microsoft Windows
const AT_PRIORITY_MED  = 3;        // Medium
const AT_PRIORITY_HIGH = 2;        // High
const AT_PRIORITY_CRIT = 1;        // Critical
const AT_STATUS_NEW    = 1;
const AT_STATUS_DONE   = 5;

// Shadow mode: log what the service *would* do without touching Autotask.
// Default true — set VEEAM_RPO_SHADOW_MODE=false in env to go live.
const SHADOW_MODE = process.env.VEEAM_RPO_SHADOW_MODE !== 'false';

export interface RpoCheckResult {
  checked: number;
  newTickets: number;
  escalated: number;
  resolved: number;
  skipped: number;
  offlineSuppressed: number;
  errors: string[];
  runAt: Date;
}

export interface RpoJobSummary {
  job_instance_uid: string;
  job_name: string;
  org_name: string;
  status: string;
  schedule_type: string;
  last_end_time: string | null;
  hours_since_backup: number | null;
  rpo_hours: number;
  is_breached: boolean;
  is_offline_suppressed: boolean;
  failure_category: string | null;
  failure_message: string | null;
  rmm_hostname: string | null;
  rmm_site_name: string | null;
  rmm_last_seen: string | null;
  open_ticket: {
    at_ticket_id: number;
    at_ticket_number: string;
    priority_level: string;
    hours_overdue: number;
    opened_at: string;
  } | null;
}

function getRpoThresholds(scheduleType: string): { grace: number; high: number; critical: number } {
  // grace = hours after next_run before we alert (buffer for slow jobs)
  // high/critical = hours after next_run for escalation
  const s = (scheduleType ?? '').toLowerCase();
  if (s.includes('weekly')) {
    return { grace: 4, high: 48, critical: 7 * 24 };
  }
  if (s.includes('continuous') || s.includes('real')) {
    return { grace: 1, high: 4, critical: 12 };
  }
  // Daily (default) — alert 4h after missed window, escalate at 48h / 7 days
  return { grace: 4, high: 48, critical: 7 * 24 };
}

function categorizeFailure(failureMessage: string | null): string {
  if (!failureMessage) return 'No recent successful backup';
  const msg = failureMessage.toLowerCase();
  if (msg.includes('license') && (msg.includes('expired') || msg.includes('grace period'))) {
    return 'License Expired — renew via VSPC';
  }
  if (msg.includes('vcg01') || msg.includes('cloud gateway') || msg.includes('cloud connect')) {
    return 'Cloud Gateway Unreachable — check vcg01.wulfconsulting.com';
  }
  if (msg.includes('repository') && (msg.includes('inaccessible') || msg.includes('not accessible'))) {
    return 'Backup Repository Inaccessible';
  }
  if (msg.includes('maintenance')) {
    return 'Service Provider Maintenance';
  }
  if (msg.includes('ssl') || msg.includes('resolve host') || msg.includes('connection')) {
    return 'Network/Connectivity Error';
  }
  if (msg.includes('timeout')) {
    return 'Backup Job Timeout';
  }
  return failureMessage.trim().substring(0, 200);
}

function buildTicketTitle(jobName: string, orgName: string, hoursOverdue: number): string {
  const h = Math.round(hoursOverdue);
  const display = h >= 48 ? `${Math.round(h / 24)}d` : `${h}h`;
  return `[Veeam RPO] ${jobName} @ ${orgName} — ${display} since last backup`;
}

function buildTicketDescription(
  jobName: string,
  orgName: string,
  scheduleType: string,
  lastEndTime: string | null,
  hoursOverdue: number,
  failureCategory: string,
  failureMessage: string | null,
  restorePoints: number | null,
): string {
  const lastBackup = lastEndTime
    ? new Date(lastEndTime).toLocaleString('en-US', { timeZone: 'America/New_York' }) + ' ET'
    : 'Never';
  const lines = [
    `Job: ${jobName}`,
    `Organization: ${orgName}`,
    `Schedule: ${scheduleType ?? 'Unknown'}`,
    `Last Successful Backup: ${lastBackup}`,
    `Hours Since Backup: ${Math.round(hoursOverdue)}h`,
    `Restore Points Available: ${restorePoints ?? 'Unknown'}`,
    ``,
    `Failure Reason: ${failureCategory}`,
  ];
  if (failureMessage && failureCategory !== failureMessage.trim().substring(0, 200)) {
    lines.push(``, `Raw Error: ${failureMessage.trim().substring(0, 500)}`);
  }
  lines.push(``, `Generated by Pulse RPO Monitor — ${new Date().toISOString()}`);
  return lines.join('\n');
}

function getAutotaskClient(): AutotaskClient {
  return new AutotaskClient({
    apiUrl: process.env.AUTOTASK_API_URL || '',
    username: process.env.AUTOTASK_USERNAME || '',
    password: process.env.AUTOTASK_SECRET || '',
    apiIntegrationCode: process.env.AUTOTASK_API_INTEGRATION_CODE || '',
  });
}

export class VeeamRpoService {
  async runCheck(): Promise<RpoCheckResult> {
    const result: RpoCheckResult = {
      checked: 0,
      newTickets: 0,
      escalated: 0,
      resolved: 0,
      skipped: 0,
      offlineSuppressed: 0,
      errors: [],
      runAt: new Date(),
    };

    const client = SHADOW_MODE ? null : getAutotaskClient();

    // Fetch all enabled workstation jobs with org info
    const jobsRes = await postgresClient.query(`
      SELECT
        j.instance_uid,
        j.name as job_name,
        j.status,
        j.schedule_type,
        j.last_end_time,
        j.next_run,
        j.restore_points,
        j.failure_message,
        j.is_enabled,
        j.operation_mode,
        o.name as org_name,
        EXTRACT(EPOCH FROM (NOW() - j.last_end_time)) / 3600.0 as hours_since_backup
      FROM veeam_backup_agent_jobs j
      JOIN veeam_organizations o ON o.instance_uid = j.organization_uid
      WHERE j.operation_mode = 'Workstation'
        AND j.is_enabled = true
      ORDER BY hours_since_backup DESC NULLS LAST
    `);

    const jobs = jobsRes.rows;
    result.checked = jobs.length;

    // Fetch all currently open tracking tickets (shadow or live table)
    const trackingTable = SHADOW_MODE ? 'veeam_rpo_shadow_tickets' : 'veeam_rpo_tickets';
    const openTicketsRes = await postgresClient.query(
      `SELECT * FROM ${trackingTable} WHERE resolved_at IS NULL`
    );
    const openByJobUid: Record<string, any> = {};
    for (const row of openTicketsRes.rows) {
      openByJobUid[row.job_instance_uid] = row;
    }

    // Bulk-resolve RMM devices for all jobs in a single query
    const rmmMap = await resolveRmmDevicesForJobs(jobs.map((j: any) => j.instance_uid));

    for (const job of jobs) {
      try {
        const rmmDevice = rmmMap.get(job.instance_uid) ?? null;
        await this.processJob(job, openByJobUid, rmmDevice, client as AutotaskClient, result);
      } catch (err: any) {
        result.errors.push(`${job.job_name}: ${err.message}`);
      }
    }

    // Update last_checked_at for all open tracking tickets
    await postgresClient.query(
      `UPDATE ${trackingTable} SET last_checked_at = NOW() WHERE resolved_at IS NULL`
    );

    return result;
  }

  private async processJob(
    job: any,
    openByJobUid: Record<string, any>,
    rmmDevice: RmmDeviceInfo | null,
    client: AutotaskClient,
    result: RpoCheckResult,
  ): Promise<void> {
    const thresholds = getRpoThresholds(job.schedule_type);
    const openTicket = openByJobUid[job.instance_uid] ?? null;

    // Skip jobs that are currently running — they haven't failed yet
    if (job.status === 'Running') {
      result.skipped++;
      return;
    }

    const hoursAgo: number | null = job.hours_since_backup !== null ? parseFloat(job.hours_since_backup) : null;
    const intervalHours = (job.schedule_type ?? '').toLowerCase().includes('weekly') ? 168
      : (job.schedule_type ?? '').toLowerCase().includes('continuous') ? 1
      : 24;

    // Suppress new tickets and escalations for Desktop/Laptop devices that have been
    // offline longer than one backup interval — the machine was offline before the backup
    // was due, so a missed backup is expected. Existing open tickets are left untouched,
    // but we backfill rmm_hostname so the comparison page can match them correctly.
    if (rmmDevice && isDeviceOfflineSuppressed(rmmDevice, intervalHours)) {
      const openTicket = openByJobUid[job.instance_uid] ?? null;
      if (openTicket && !openTicket.rmm_hostname) {
        const trackingTable = SHADOW_MODE ? 'veeam_rpo_shadow_tickets' : 'veeam_rpo_tickets';
        await postgresClient.query(
          `UPDATE ${trackingTable} SET rmm_hostname = $1, updated_at = NOW() WHERE job_instance_uid = $2`,
          [rmmDevice.hostname, job.instance_uid]
        );
      }
      await this.logOfflineSuppression(job, rmmDevice, intervalHours);
      result.offlineSuppressed++;
      return;
    }

    // Breach rules:
    // - Failed/Warning: always breached — a failed backup is a failed backup regardless of recency
    // - None (never run): always breached
    // - Success: only breach if last success is older than (interval + grace) — daily = 28h
    //   Use 3x for the >30d cap logic only; the alert threshold is interval+grace
    const rpoWindowHours = intervalHours + thresholds.grace;
    const isBreached = job.status === 'Failed' || job.status === 'Warning'
      || hoursAgo === null
      || hoursAgo > rpoWindowHours;

    if (!isBreached) {
      if (openTicket) {
        if (SHADOW_MODE) {
          await this.shadowResolve(openTicket, job, rmmDevice, result);
        } else {
          await this.resolveTicket(openTicket, client!);
          result.resolved++;
        }
      } else {
        result.skipped++;
      }
      return;
    }

    // hoursOverdue = hours past the RPO window
    const hoursOverdue = hoursAgo !== null ? hoursAgo - rpoWindowHours : thresholds.grace;
    const failureCategory = categorizeFailure(job.failure_message);
    const targetPriority = hoursOverdue >= thresholds.critical ? 'critical'
      : hoursOverdue >= thresholds.high ? 'high'
      : 'medium';

    // Don't create new tickets for jobs broken longer than 30 days on first encounter.
    // These are likely abandoned machines — show as breached in UI but don't flood AT.
    const MAX_NEW_TICKET_AGE_HOURS = 720; // 30 days
    const tooOldForNewTicket = !openTicket && (hoursAgo ?? 0) > MAX_NEW_TICKET_AGE_HOURS;

    if (!openTicket) {
      if (tooOldForNewTicket) {
        if (SHADOW_MODE) await this.writeShadowLog(job, rmmDevice, 'would_skip_too_old', null, null, null);
        result.skipped++;
        return;
      }
      if (SHADOW_MODE) {
        await this.shadowCreate(job, rmmDevice, hoursOverdue, failureCategory, targetPriority, result);
      } else {
        await this.createTicket(job, hoursOverdue, failureCategory, targetPriority, client!, result);
      }
    } else {
      if (openTicket.priority_level !== targetPriority && this.isPriorityHigher(targetPriority, openTicket.priority_level)) {
        if (SHADOW_MODE) {
          await this.shadowEscalate(openTicket, job, rmmDevice, hoursOverdue, failureCategory, targetPriority, result);
        } else {
          await this.escalateTicket(openTicket, job, hoursOverdue, failureCategory, targetPriority, client!, result);
        }
      } else {
        result.skipped++;
      }
    }
  }

  private isPriorityHigher(a: string, b: string): boolean {
    const rank: Record<string, number> = { medium: 1, high: 2, critical: 3 };
    return (rank[a] ?? 0) > (rank[b] ?? 0);
  }

  // ─── Shadow mode methods ───────────────────────────────────────────────────

  private async writeShadowLog(
    job: any,
    rmmDevice: RmmDeviceInfo | null,
    action: string,
    priorityLevel: string | null,
    hoursOverdue: number | null,
    failureCategory: string | null,
  ): Promise<void> {
    await postgresClient.query(`
      INSERT INTO veeam_rpo_shadow_log
        (job_instance_uid, job_name, org_name, rmm_hostname, rmm_site_name,
         action, priority_level, hours_overdue, failure_category, checked_at)
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,NOW())
    `, [
      job.instance_uid, job.job_name, job.org_name,
      rmmDevice?.hostname ?? null, rmmDevice?.site_name ?? null,
      action, priorityLevel,
      hoursOverdue !== null ? Math.round(hoursOverdue) : null,
      failureCategory,
    ]);
  }

  private async shadowCreate(
    job: any,
    rmmDevice: RmmDeviceInfo | null,
    hoursOverdue: number,
    failureCategory: string,
    priorityLevel: string,
    result: RpoCheckResult,
  ): Promise<void> {
    await postgresClient.query(`
      INSERT INTO veeam_rpo_shadow_tickets
        (job_instance_uid, job_name, org_name, rmm_hostname, priority_level,
         hours_overdue, failure_category, opened_at, last_checked_at)
      VALUES ($1,$2,$3,$4,$5,$6,$7,NOW(),NOW())
      ON CONFLICT (job_instance_uid) DO UPDATE SET
        priority_level   = EXCLUDED.priority_level,
        hours_overdue    = EXCLUDED.hours_overdue,
        failure_category = EXCLUDED.failure_category,
        rmm_hostname     = COALESCE(EXCLUDED.rmm_hostname, veeam_rpo_shadow_tickets.rmm_hostname),
        resolved_at      = NULL,
        opened_at        = NOW(),
        last_checked_at  = NOW(),
        updated_at       = NOW()
    `, [
      job.instance_uid, job.job_name, job.org_name,
      rmmDevice?.hostname ?? null, priorityLevel,
      Math.round(hoursOverdue), failureCategory,
    ]);
    await this.writeShadowLog(job, rmmDevice, 'would_create', priorityLevel, hoursOverdue, failureCategory);
    result.newTickets++;
    console.log(`[RPO-SHADOW] Would create ticket: ${job.job_name} @ ${job.org_name} (${Math.round(hoursOverdue)}h overdue, ${priorityLevel})`);
  }

  private async shadowEscalate(
    openTicket: any,
    job: any,
    rmmDevice: RmmDeviceInfo | null,
    hoursOverdue: number,
    failureCategory: string,
    targetPriority: string,
    result: RpoCheckResult,
  ): Promise<void> {
    await postgresClient.query(`
      UPDATE veeam_rpo_shadow_tickets SET
        priority_level   = $1,
        hours_overdue    = $2,
        failure_category = $3,
        last_checked_at  = NOW(),
        updated_at       = NOW()
      WHERE job_instance_uid = $4
    `, [targetPriority, Math.round(hoursOverdue), failureCategory, job.instance_uid]);
    await this.writeShadowLog(job, rmmDevice, 'would_escalate', targetPriority, hoursOverdue, failureCategory);
    result.escalated++;
    console.log(`[RPO-SHADOW] Would escalate to ${targetPriority}: ${job.job_name} (${Math.round(hoursOverdue)}h overdue)`);
  }

  private async shadowResolve(
    openTicket: any,
    job: any,
    rmmDevice: RmmDeviceInfo | null,
    result: RpoCheckResult,
  ): Promise<void> {
    await postgresClient.query(`
      UPDATE veeam_rpo_shadow_tickets SET
        resolved_at     = NOW(),
        last_checked_at = NOW(),
        updated_at      = NOW()
      WHERE job_instance_uid = $1
    `, [openTicket.job_instance_uid]);
    await this.writeShadowLog(job, rmmDevice, 'would_resolve', null, null, null);
    result.resolved++;
    console.log(`[RPO-SHADOW] Would resolve: ${job.job_name} — backup succeeded`);
  }

  // ──────────────────────────────────────────────────────────────────────────

  private async logOfflineSuppression(
    job: any,
    rmmDevice: RmmDeviceInfo,
    intervalHours: number,
  ): Promise<void> {
    const hrs = hoursOffline(rmmDevice) ?? 0;
    await postgresClient.query(`
      INSERT INTO veeam_rpo_offline_log
        (job_instance_uid, job_name, org_name, rmm_hostname, rmm_site_name,
         device_type_category, rmm_last_seen, hours_offline, backup_interval_hours, checked_at)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())
    `, [
      job.instance_uid,
      job.job_name,
      job.org_name,
      rmmDevice.hostname,
      rmmDevice.site_name,
      rmmDevice.device_type_category,
      rmmDevice.last_seen,
      Math.round(hrs * 100) / 100,
      intervalHours,
    ]);
    console.log(`[RPO] Suppressed (offline ${Math.round(hrs)}h): ${job.job_name} @ ${rmmDevice.hostname}`);
  }

  private async createTicket(
    job: any,
    hoursOverdue: number,
    failureCategory: string,
    priorityLevel: string,
    client: AutotaskClient,
    result: RpoCheckResult,
  ): Promise<void> {
    const atPriority = priorityLevel === 'critical' ? AT_PRIORITY_CRIT
      : priorityLevel === 'high' ? AT_PRIORITY_HIGH
      : AT_PRIORITY_MED;

    const title = buildTicketTitle(job.job_name, job.org_name, hoursOverdue);
    const description = buildTicketDescription(
      job.job_name, job.org_name, job.schedule_type,
      job.last_end_time, hoursOverdue, failureCategory,
      job.failure_message, job.restore_points,
    );

    // Look up Autotask company ID from org name mapping
    const companyRes = await postgresClient.query(`
      SELECT company_id AS id FROM veeam_organizations
      WHERE name = $1
      LIMIT 1
    `, [job.org_name]);

    const companyId = companyRes.rows[0]?.id ?? null;

    const ticketPayload: Record<string, any> = {
      title,
      description,
      status: AT_STATUS_NEW,
      queueID: AT_QUEUE_ID,
      issueType: AT_ISSUE_TYPE,
      priority: atPriority,
    };
    if (companyId) ticketPayload.companyID = companyId;

    const ticket = await client.createTicket(ticketPayload);

    await postgresClient.query(`
      INSERT INTO veeam_rpo_tickets
        (job_instance_uid, job_name, org_name, at_ticket_id, at_ticket_number,
         priority_level, hours_overdue, failure_category, opened_at, last_checked_at)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW(), NOW())
      ON CONFLICT (job_instance_uid) DO UPDATE SET
        at_ticket_id     = EXCLUDED.at_ticket_id,
        at_ticket_number = EXCLUDED.at_ticket_number,
        priority_level   = EXCLUDED.priority_level,
        hours_overdue    = EXCLUDED.hours_overdue,
        failure_category = EXCLUDED.failure_category,
        resolved_at      = NULL,
        opened_at        = NOW(),
        last_checked_at  = NOW(),
        updated_at       = NOW()
    `, [
      job.instance_uid,
      job.job_name,
      job.org_name,
      ticket.id,
      (ticket as any).ticketNumber ?? null,
      priorityLevel,
      Math.round(hoursOverdue),
      failureCategory,
    ]);

    result.newTickets++;
    console.log(`[RPO] Created ticket ${(ticket as any).ticketNumber} for ${job.job_name} (${Math.round(hoursOverdue)}h overdue)`);
  }

  private async escalateTicket(
    openTicket: any,
    job: any,
    hoursOverdue: number,
    failureCategory: string,
    targetPriority: string,
    client: AutotaskClient,
    result: RpoCheckResult,
  ): Promise<void> {
    const atPriority = targetPriority === 'critical' ? AT_PRIORITY_CRIT
      : targetPriority === 'high' ? AT_PRIORITY_HIGH
      : AT_PRIORITY_MED;

    const note = `RPO Escalation: ${Math.round(hoursOverdue)}h since last successful backup (escalated to ${targetPriority}).\nFailure reason: ${failureCategory}`;

    await client.updateTicket(openTicket.at_ticket_id, { priority: atPriority });

    // Add a note to the ticket
    try {
      await (client as any).createEntity('TicketNotes', {
        ticketID: openTicket.at_ticket_id,
        title: `RPO Escalation — ${targetPriority.toUpperCase()}`,
        description: note,
        noteType: 1,
        publish: 1,
      });
    } catch {
      // Note creation failure is non-fatal
    }

    await postgresClient.query(`
      UPDATE veeam_rpo_tickets SET
        priority_level   = $1,
        hours_overdue    = $2,
        failure_category = $3,
        last_checked_at  = NOW(),
        updated_at       = NOW()
      WHERE id = $4
    `, [targetPriority, Math.round(hoursOverdue), failureCategory, openTicket.id]);

    result.escalated++;
    console.log(`[RPO] Escalated ticket ${openTicket.at_ticket_number} to ${targetPriority} (${Math.round(hoursOverdue)}h overdue)`);
  }

  private async resolveTicket(openTicket: any, client: AutotaskClient): Promise<void> {
    await client.updateTicket(openTicket.at_ticket_id, { status: AT_STATUS_DONE });

    await postgresClient.query(`
      UPDATE veeam_rpo_tickets SET
        resolved_at     = NOW(),
        last_checked_at = NOW(),
        updated_at      = NOW()
      WHERE id = $1
    `, [openTicket.id]);

    console.log(`[RPO] Resolved ticket ${openTicket.at_ticket_number} — backup succeeded`);
  }

  async getStatus(): Promise<{ shadowMode: boolean; summary: Record<string, number>; jobs: RpoJobSummary[] }> {
    const jobsRes = await postgresClient.query(`
      SELECT
        j.instance_uid,
        j.name as job_name,
        j.status,
        j.schedule_type,
        j.last_end_time,
        j.next_run,
        j.restore_points,
        j.failure_message,
        o.name as org_name,
        EXTRACT(EPOCH FROM (NOW() - j.last_end_time)) / 3600.0 as hours_since_backup,
        EXTRACT(EPOCH FROM (j.next_run - NOW())) / 3600.0 as hours_until_next_run,
        rt.at_ticket_id,
        rt.at_ticket_number,
        rt.priority_level,
        rt.hours_overdue,
        rt.opened_at
      FROM veeam_backup_agent_jobs j
      JOIN veeam_organizations o ON o.instance_uid = j.organization_uid
      LEFT JOIN veeam_rpo_tickets rt
        ON rt.job_instance_uid = j.instance_uid AND rt.resolved_at IS NULL
      WHERE j.operation_mode = 'Workstation'
        AND j.is_enabled = true
      ORDER BY hours_since_backup DESC NULLS LAST
    `);

    const rows = jobsRes.rows;
    const rmmMap = await resolveRmmDevicesForJobs(rows.map((r: any) => r.instance_uid));

    const jobs: RpoJobSummary[] = rows.map((row: any) => {
      const thresholds = getRpoThresholds(row.schedule_type);
      const hoursAgo: number | null = row.hours_since_backup !== null ? parseFloat(row.hours_since_backup) : null;
      const intervalHours = (row.schedule_type ?? '').toLowerCase().includes('weekly') ? 168
        : (row.schedule_type ?? '').toLowerCase().includes('continuous') ? 1
        : 24;
      const rpoWindowHours = intervalHours + thresholds.grace;
      const isBreached = row.status !== 'Running'
        && (row.status === 'Failed' || row.status === 'Warning'
          || hoursAgo === null
          || hoursAgo > rpoWindowHours);

      const rmmDevice = rmmMap.get(row.instance_uid) ?? null;
      const isOfflineSuppressed = isBreached
        && rmmDevice !== null
        && isDeviceOfflineSuppressed(rmmDevice, intervalHours);

      return {
        job_instance_uid:     row.instance_uid,
        job_name:             row.job_name,
        org_name:             row.org_name,
        status:               row.status,
        schedule_type:        row.schedule_type,
        last_end_time:        row.last_end_time,
        hours_since_backup:   hoursAgo !== null ? Math.round(hoursAgo * 10) / 10 : null,
        rpo_hours:            thresholds.grace,
        is_breached:          isBreached,
        is_offline_suppressed: isOfflineSuppressed,
        failure_category:     row.failure_message ? categorizeFailure(row.failure_message) : null,
        failure_message:      row.failure_message,
        rmm_hostname:         rmmDevice?.hostname ?? null,
        rmm_site_name:        rmmDevice?.site_name ?? null,
        rmm_last_seen:        rmmDevice?.last_seen?.toISOString() ?? null,
        open_ticket: row.at_ticket_id ? {
          at_ticket_id:     row.at_ticket_id,
          at_ticket_number: row.at_ticket_number,
          priority_level:   row.priority_level,
          hours_overdue:    row.hours_overdue,
          opened_at:        row.opened_at,
        } : null,
      };
    });

    const breached           = jobs.filter(j => j.is_breached).length;
    const offlineSuppressed  = jobs.filter(j => j.is_offline_suppressed).length;
    const withTicket         = jobs.filter(j => j.open_ticket !== null).length;
    const healthy            = jobs.filter(j => !j.is_breached).length;
    const critical           = jobs.filter(j => j.open_ticket?.priority_level === 'critical').length;
    const high               = jobs.filter(j => j.open_ticket?.priority_level === 'high').length;

    return {
      shadowMode: SHADOW_MODE,
      summary: {
        total: jobs.length,
        healthy,
        breached,
        offlineSuppressed,
        withOpenTicket: withTicket,
        critical,
        high,
      },
      jobs,
    };
  }
}

let _instance: VeeamRpoService | null = null;
export function getVeeamRpoService(): VeeamRpoService {
  if (!_instance) _instance = new VeeamRpoService();
  return _instance;
}