xinmi/migrations/versioned/000205_agent_duties.up.sql

-- Add the source duties only when the native field has never been saved.
DO $agent_duties$
BEGIN
  IF to_regclass('yawan_legacy_asset_mappings') IS NOT NULL THEN
    UPDATE custom_agents a
    SET config = (a.config::jsonb ||
      CASE WHEN NOT (a.config::jsonb ? 'role')
        THEN jsonb_build_object('role', m.source_snapshot->>'role') ELSE '{}'::jsonb END ||
      CASE WHEN NOT (a.config::jsonb ? 'content_tags')
        THEN jsonb_build_object('content_tags', m.source_snapshot->'content_tags') ELSE '{}'::jsonb END)
    FROM yawan_legacy_asset_mappings m
    WHERE m.source_system = 'yawan-workbench' AND m.source_organization_id = 1
      AND m.source_kind = 'agent' AND a.id = m.target_id AND a.tenant_id = m.target_tenant_id
      AND a.deleted_at IS NULL
      AND (NOT (a.config::jsonb ? 'role') OR NOT (a.config::jsonb ? 'content_tags'))
      AND m.source_snapshot->>'role' IN ('', 'general', 'coordinator', 'production', 'qc', 'service', 'training_map')
      AND jsonb_typeof(m.source_snapshot->'content_tags') = 'array'
      AND NOT EXISTS (SELECT 1 FROM jsonb_array_elements(m.source_snapshot->'content_tags') tag
        WHERE jsonb_typeof(tag) <> 'string' OR length(tag #>> '{}') > 64 OR btrim(tag #>> '{}') = '')
      AND jsonb_array_length(m.source_snapshot->'content_tags') <= 32;
  END IF;
END $agent_duties$;