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

/**
 * Veeam Compliance Service
 * Cross-references Autotask config items (with backup UDFs on active contracts)
 * against Veeam protected workloads to identify mismatches.
 */

import postgresClient from './postgres-client';

export interface ComplianceComputeResult {
  totalContracted: number;
  matched: number;
  contractedNotBackedUp: number;
  backedUpNotContracted: number;
  duration: number;
}

export class VeeamComplianceService {

  /**
   * Compute compliance results by cross-referencing Autotask config items
   * with Veeam protected workloads and agent jobs.
   */
  async computeCompliance(): Promise<ComplianceComputeResult> {
    const startTime = Date.now();
    console.log('[VEEAM-COMPLIANCE] Starting compliance computation...');

    // 1. Get all contracted backup devices from Autotask
    //    A device is "contracted for backup" when:
    //    - backup_type_udf IS NOT NULL and NOT empty
    //    - is_active = true
    //    - The company has at least one active contract (status = 1)
    //    Note: configuration_items don't have a direct contract_id column;
    //    we match via company having active contracts instead.
    const contractedDevices = await postgresClient.query(`
      SELECT
        ci.id as configuration_item_id,
        ci.company_id,
        ci.reference_title,
        ci.backup_type_udf,
        ct.contract_name
      FROM configuration_items ci
      JOIN contracts ct ON ct.company_id = ci.company_id AND ct.status = 1
      WHERE ci.backup_type_udf IS NOT NULL
        AND ci.backup_type_udf != ''
        AND ci.is_active = true
        AND ci.is_deleted = false
      GROUP BY ci.id, ci.company_id, ci.reference_title, ci.backup_type_udf, ct.contract_name
    `);

    console.log(`[VEEAM-COMPLIANCE] Found ${contractedDevices.rows.length} contracted backup devices`);

    // 2. Get all Veeam protected workloads with their org's company_id
    const veeamWorkloads = await postgresClient.query(`
      SELECT
        pw.instance_uid,
        pw.name,
        pw.organization_uid,
        vo.company_id
      FROM veeam_protected_workloads pw
      LEFT JOIN veeam_organizations vo ON vo.instance_uid = pw.organization_uid
    `);

    // 3. Get all Veeam agent jobs with their org's company_id
    const veeamAgentJobs = await postgresClient.query(`
      SELECT
        aj.instance_uid,
        aj.name,
        aj.organization_uid,
        vo.company_id
      FROM veeam_backup_agent_jobs aj
      LEFT JOIN veeam_organizations vo ON vo.instance_uid = aj.organization_uid
      WHERE aj.is_enabled = true
    `);

    // Build lookup maps for Veeam data (lowercase name → record)
    const veeamByName = new Map<string, { uid: string; name: string; companyId: number | null }>();
    for (const w of veeamWorkloads.rows) {
      if (w.name) {
        veeamByName.set(w.name.toLowerCase(), {
          uid: w.instance_uid,
          name: w.name,
          companyId: w.company_id,
        });
      }
    }
    for (const j of veeamAgentJobs.rows) {
      if (j.name) {
        // Agent job names often have policy prefix, try to extract hostname
        const name = j.name.toLowerCase();
        if (!veeamByName.has(name)) {
          veeamByName.set(name, {
            uid: j.instance_uid,
            name: j.name,
            companyId: j.company_id,
          });
        }
      }
    }

    // Track which Veeam workloads are matched
    const matchedVeeamUids = new Set<string>();

    // 4. Match contracted devices to Veeam workloads
    const contractedNotBackedUp: Array<{
      companyId: number; configItemId: number; deviceName: string;
      backupTypeUdf: string; contractName: string;
    }> = [];

    for (const device of contractedDevices.rows) {
      const hostname = (device.reference_title || '').toLowerCase().trim();
      if (!hostname) {
        contractedNotBackedUp.push({
          companyId: device.company_id,
          configItemId: device.configuration_item_id,
          deviceName: device.reference_title || 'Unknown',
          backupTypeUdf: device.backup_type_udf,
          contractName: device.contract_name || '',
        });
        continue;
      }

      // Try exact match first
      let match = veeamByName.get(hostname);

      // Try partial match (hostname contained in workload name or vice versa)
      if (!match) {
        for (const [wName, wData] of veeamByName) {
          if (wName.includes(hostname) || hostname.includes(wName)) {
            // Prefer same-company match
            if (wData.companyId === device.company_id) {
              match = wData;
              break;
            }
            if (!match) match = wData;
          }
        }
      }

      if (match) {
        matchedVeeamUids.add(match.uid);
      } else {
        contractedNotBackedUp.push({
          companyId: device.company_id,
          configItemId: device.configuration_item_id,
          deviceName: device.reference_title || 'Unknown',
          backupTypeUdf: device.backup_type_udf,
          contractName: device.contract_name || '',
        });
      }
    }

    // 5. Find Veeam workloads not matched to any contracted device
    const backedUpNotContracted: Array<{
      companyId: number | null; veeamUid: string; workloadName: string;
    }> = [];

    for (const w of veeamWorkloads.rows) {
      if (!matchedVeeamUids.has(w.instance_uid)) {
        backedUpNotContracted.push({
          companyId: w.company_id,
          veeamUid: w.instance_uid,
          workloadName: w.name,
        });
      }
    }

    // 6. Truncate and reinsert compliance results
    await postgresClient.query('DELETE FROM veeam_compliance_results');

    for (const item of contractedNotBackedUp) {
      await postgresClient.query(
        `INSERT INTO veeam_compliance_results (company_id, configuration_item_id, mismatch_type, backup_type_udf, device_name, contract_name, computed_at)
         VALUES ($1, $2, 'contracted_not_backed_up', $3, $4, $5, NOW())`,
        [item.companyId, item.configItemId, item.backupTypeUdf, item.deviceName, item.contractName]
      );
    }

    for (const item of backedUpNotContracted) {
      await postgresClient.query(
        `INSERT INTO veeam_compliance_results (company_id, veeam_workload_uid, mismatch_type, device_name, veeam_workload_name, computed_at)
         VALUES ($1, $2, 'backed_up_not_contracted', $3, $3, NOW())`,
        [item.companyId, item.veeamUid, item.workloadName]
      );
    }

    const duration = Date.now() - startTime;
    const totalContracted = contractedDevices.rows.length;
    const matched = totalContracted - contractedNotBackedUp.length;

    console.log(`[VEEAM-COMPLIANCE] Compliance computed in ${duration}ms:`);
    console.log(`  Contracted: ${totalContracted}, Matched: ${matched}`);
    console.log(`  Missing backup: ${contractedNotBackedUp.length}`);
    console.log(`  No contract: ${backedUpNotContracted.length}`);

    return {
      totalContracted,
      matched,
      contractedNotBackedUp: contractedNotBackedUp.length,
      backedUpNotContracted: backedUpNotContracted.length,
      duration,
    };
  }
}