xinmi/migrations/versioned/000208_agent_fusions.up.sql

-- Preserve the native field once saved, including an explicitly empty selection.
DO $agent_fusions$
DECLARE mapping RECORD; fusion_ids JSONB; native_ids JSONB; expected_count INTEGER;
BEGIN
  IF to_regclass('yawan_legacy_asset_mappings') IS NOT NULL THEN
    FOR mapping IN
      SELECT m.*, a.config::jsonb AS current_config
      FROM yawan_legacy_asset_mappings m
      JOIN custom_agents a ON a.id=m.target_id AND a.tenant_id=m.target_tenant_id
      WHERE m.source_system='yawan-workbench' AND m.source_organization_id=1
        AND m.source_kind='agent' AND a.deleted_at IS NULL
        AND NOT (a.config::jsonb ? 'fusion_agent_ids')
    LOOP
      fusion_ids := COALESCE(mapping.source_snapshot->'fusion_agent_ids', '[]'::jsonb);
      IF jsonb_typeof(fusion_ids) <> 'array' OR jsonb_array_length(fusion_ids)>8 THEN
        RAISE EXCEPTION 'Legacy Agent fusion IDs require review';
      END IF;
      expected_count := jsonb_array_length(fusion_ids);
      IF EXISTS (SELECT 1 FROM jsonb_array_elements(fusion_ids) value
                 WHERE jsonb_typeof(value)<>'number' OR (value #>> '{}') !~ '^[1-9][0-9]*$'
                   OR (value #>> '{}')::bigint=mapping.source_id)
         OR (SELECT COUNT(DISTINCT value) FROM jsonb_array_elements(fusion_ids) value) <> expected_count
         OR (expected_count>0 AND mapping.source_snapshot->>'role' IS DISTINCT FROM 'production') THEN
        RAISE EXCEPTION 'Legacy Agent fusion IDs are invalid';
      END IF;
      SELECT COALESCE(jsonb_agg(to_jsonb(target.target_id) ORDER BY ids.ordinality), '[]'::jsonb)
      INTO native_ids
      FROM jsonb_array_elements_text(fusion_ids) WITH ORDINALITY ids(source_id, ordinality)
      JOIN yawan_legacy_asset_mappings target
        ON target.source_system=mapping.source_system AND target.source_organization_id=mapping.source_organization_id
          AND target.source_kind='agent' AND target.source_id=ids.source_id::bigint
          AND target.target_tenant_id=mapping.target_tenant_id
          AND target.source_snapshot->>'role'='production'
      JOIN custom_agents a ON a.id=target.target_id AND a.tenant_id=target.target_tenant_id;
      IF jsonb_array_length(native_ids) <> expected_count THEN
        RAISE EXCEPTION 'Legacy Agent fusion target mapping missing';
      END IF;
      -- A later native duty edit must not acquire an incompatible binding.
      IF expected_count=0 OR mapping.current_config->>'role'='production' THEN
        UPDATE custom_agents SET config = config::jsonb || jsonb_build_object('fusion_agent_ids',native_ids)
        WHERE id=mapping.target_id AND tenant_id=mapping.target_tenant_id
          AND NOT (config::jsonb ? 'fusion_agent_ids');
      END IF;
    END LOOP;
  END IF;
END $agent_fusions$;