wulf-pulse/migrations/080_device_xref_company_id.sql

-- Backfill device_external_ids.company_id for sources that have a known
-- per-tool → Autotask-company mapping table. Populating company_id unlocks
-- the reconciler's hostname-in-company strategy for s1 + veeam + datto_rmm
-- rows that had no serial.
--
-- Mappings used:
--   datto_rmm → datto_rmm_devices.site_uid → rmm_site_mappings.company_id
--   s1        → s1_agents.site_id          → s1_company_mappings.company_id
--   itglue    → itg_configurations.organization_id → itg_organizations.psa_id (cast)
--                where psa_integration = 'autotask'
--   veeam     → no clean mapping today; left null.
--
-- Also adds device_link_review to capture reconciler conflicts (multiple
-- candidate CIs) for admin resolution.

-- ---------------------------------------------------------------------------
-- 1. company_id backfill — datto_rmm
-- ---------------------------------------------------------------------------

UPDATE device_external_ids dx
   SET company_id = m.company_id
  FROM datto_rmm_devices d
  JOIN rmm_site_mappings m ON m.rmm_site_uid = d.site_uid
 WHERE dx.source = 'datto_rmm'
   AND dx.source_id = d.uid
   AND dx.company_id IS NULL;

-- ---------------------------------------------------------------------------
-- 2. company_id backfill — s1
-- ---------------------------------------------------------------------------

UPDATE device_external_ids dx
   SET company_id = m.company_id
  FROM s1_agents sa
  JOIN s1_company_mappings m ON m.s1_site_id = sa.site_id
 WHERE dx.source = 's1'
   AND dx.source_id = sa.id::text
   AND dx.company_id IS NULL;

-- ---------------------------------------------------------------------------
-- 3. company_id backfill — itglue
-- ---------------------------------------------------------------------------
-- itg_organizations.psa_id is unpopulated in this environment. Fall back to
-- fuzzy name match against companies — the same approach asset-matcher.ts
-- and itglue-redact.ts use elsewhere in the codebase. Imperfect but consistent.
UPDATE device_external_ids dx
   SET company_id = c.id
  FROM itg_configurations igc
  JOIN itg_organizations ig ON ig.id = igc.organization_id
  JOIN companies c ON LOWER(c.company_name) = LOWER(ig.name)
 WHERE dx.source = 'itglue'
   AND dx.source_id = igc.id::text
   AND dx.company_id IS NULL;

-- ---------------------------------------------------------------------------
-- 4. device_link_review — conflicts queue for admin resolution
-- ---------------------------------------------------------------------------
-- One row per (xref-row, batch) when the reconciler finds 2+ CI candidates.
-- Resolving = the admin picks one CI; the reconciler's match logic doesn't
-- get to decide on its own.

CREATE TABLE IF NOT EXISTS device_link_review (
  id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  device_external_id       BIGINT NOT NULL REFERENCES device_external_ids(id) ON DELETE CASCADE,
  candidate_ci_ids         BIGINT[] NOT NULL,
  match_confidences        TEXT[]   NOT NULL,
  detected_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  resolved_at              TIMESTAMPTZ,
  resolved_by_user_id      TEXT NULL REFERENCES "user"(id) ON DELETE SET NULL,
  resolved_to_ci_id        BIGINT NULL REFERENCES configuration_items(id) ON DELETE SET NULL,
  resolution_note          TEXT
);

CREATE INDEX IF NOT EXISTS ix_device_link_review_unresolved
  ON device_link_review(detected_at DESC) WHERE resolved_at IS NULL;
CREATE INDEX IF NOT EXISTS ix_device_link_review_xref
  ON device_link_review(device_external_id);

-- Don't keep stale rows: when the underlying xref row gets linked some other
-- way (admin manually fixes the source-of-truth), a unique index would block
-- new reviews. Instead we use a partial index for "open reviews per xref".
CREATE UNIQUE INDEX IF NOT EXISTS uq_device_link_review_open_per_xref
  ON device_link_review(device_external_id) WHERE resolved_at IS NULL;

COMMENT ON TABLE device_link_review IS
  'Reconciler conflicts queue: one row per unlinked device_external_ids row that matches 2+ configuration_items. Admin picks the right CI; reconciler does not auto-merge.';