wulf-pulse/lib/services/rmm/loglift-matcher.ts

/**
 * Resolve linkages from a LogLift webhook payload to Pulse's identity model.
 *
 *   clientId (Datto site uid)  →  datto_rmm_sites.id, autotask_company_id
 *   computerName (hostname)    →  datto_rmm_devices.uid (the real Datto uid)
 *   hostname + company         →  itg_configurations.id (single match only)
 *
 * Auto-audit only fires when a Configuration matches exactly one row —
 * multi-match is logged but skipped to avoid auditing the wrong asset.
 */

import postgresClient from '@/lib/services/postgres-client';

export interface DattoSiteResolution {
  datto_site_id: number;
  datto_site_uid: string;
  autotask_company_id: string | null;
  autotask_company_name: string | null;
}

export async function resolveDattoSiteByUid(
  siteUid: string
): Promise<DattoSiteResolution | null> {
  // datto_rmm_sites.uid is the Datto-side site uuid; the autotask_company_id
  // FK is sometimes null (only the company NAME is populated). Mirror the
  // Phase 4.2 fallback: match by company name if FK is null.
  const res = await postgresClient.query<{
    id: number;
    uid: string;
    autotask_company_id: string | null;
    autotask_company_name: string | null;
    by_name_company_id: string | null;
  }>(
    `SELECT s.id,
            s.uid,
            s.autotask_company_id::text AS autotask_company_id,
            s.autotask_company_name,
            (SELECT c.id::text FROM companies c
              WHERE LOWER(c.company_name) = LOWER(s.autotask_company_name)
              LIMIT 1) AS by_name_company_id
       FROM datto_rmm_sites s
      WHERE s.uid = $1
      LIMIT 1`,
    [siteUid]
  );
  if (res.rowCount === 0) return null;
  const r = res.rows[0];
  return {
    datto_site_id: r.id,
    datto_site_uid: r.uid,
    autotask_company_id: r.autotask_company_id ?? r.by_name_company_id,
    autotask_company_name: r.autotask_company_name,
  };
}

export async function resolveDattoDeviceByHostname(
  siteId: number,
  hostname: string
): Promise<{ uid: string; hostname: string | null; online: boolean } | null> {
  const res = await postgresClient.query<{
    uid: string;
    hostname: string | null;
    online: boolean;
  }>(
    `SELECT uid, hostname, online
       FROM datto_rmm_devices
      WHERE site_id = $1
        AND LOWER(hostname) = LOWER($2)
      LIMIT 1`,
    [siteId, hostname]
  );
  return res.rowCount === 0 ? null : res.rows[0];
}

export interface ConfigurationMatch {
  id: string;
  hostname: string | null;
  name: string;
  /** True when the company has exactly one Configuration with this hostname. */
  single_match: boolean;
}

export async function resolveConfigurationByHostname(
  autotaskCompanyId: string | number,
  hostname: string
): Promise<ConfigurationMatch | null> {
  // Two-pass: count + fetch. Avoids ambiguous auto-audits when multiple
  // Configurations share a hostname (rare but possible after a sync glitch).
  const countRes = await postgresClient.query<{ n: string }>(
    `SELECT COUNT(*)::text AS n
       FROM itg_configurations c
       JOIN companies comp ON LOWER(comp.company_name) = LOWER(c.organization_name)
      WHERE comp.id = $1
        AND (
          LOWER(c.hostname) = LOWER($2)
          OR LOWER(c.name) = LOWER($2)
        )`,
    [autotaskCompanyId, hostname]
  );
  const n = Number(countRes.rows[0]?.n ?? 0);
  if (n === 0) return null;

  const fetchRes = await postgresClient.query<{
    id: string;
    hostname: string | null;
    name: string;
  }>(
    `SELECT c.id::text AS id, c.hostname, c.name
       FROM itg_configurations c
       JOIN companies comp ON LOWER(comp.company_name) = LOWER(c.organization_name)
      WHERE comp.id = $1
        AND (
          LOWER(c.hostname) = LOWER($2)
          OR LOWER(c.name) = LOWER($2)
        )
      LIMIT 1`,
    [autotaskCompanyId, hostname]
  );
  if (fetchRes.rowCount === 0) return null;
  const r = fetchRes.rows[0];
  return {
    id: r.id,
    hostname: r.hostname,
    name: r.name,
    single_match: n === 1,
  };
}