JOIN to WHERE Clause Converter

WITH schema_ai_fields AS (
    -- Get all metadata fields from schemas where assignee = 'ai'
    SELECT 
        ms.id as schema_id,
        ms.metadata as schema_metadata,
        jsonb_object_keys(ms.metadata) as metadata_key
    FROM "MediaSchema" ms
    WHERE ms.metadata IS NOT NULL
      AND ms.metadata != '{}'::jsonb
),
ai_fields_expanded AS (
    -- Expand to get details of each AI-assigned field
    SELECT 
        saf.schema_id,
        saf.metadata_key,
        saf.schema_metadata -> saf.metadata_key as metadata_struct
    FROM schema_ai_fields saf
    WHERE (saf.schema_metadata -> saf.metadata_key ->> 'assignee') = 'ai'
),
media_with_data AS (
    -- Get all media with their latest MediaData
    -- Note: LEFT JOIN converted using (+) Oracle-style syntax
    -- For standard SQL, this becomes an INNER JOIN when using comma syntax
    SELECT 
        m.id as media_id,
        m.schema_id,
        m.file_data,
        m.file_mime_type,
        md.id as media_data_id,
        md.metadata as media_metadata
    FROM "Media" m, "MediaData" md
    WHERE md.media_id(+) = m.id
      AND m.status != 'approved'
),
items_needing_processing AS (
    -- Find items where metadata doesn't exist or has status 'init'
    SELECT 
        mwd.media_id,
        mwd.media_data_id,
        mwd.schema_id,
        mwd.file_data,
        mwd.file_mime_type,
        afe.metadata_key,
        afe.metadata_struct,
        CASE 
            WHEN mwd.media_metadata IS NULL THEN NULL
            WHEN mwd.media_metadata -> afe.metadata_key IS NULL THEN NULL
            ELSE mwd.media_metadata -> afe.metadata_key
        END as metadata_value
    FROM media_with_data mwd, ai_fields_expanded afe
    WHERE mwd.schema_id = afe.schema_id
      AND (
          -- Either metadata is null, or the specific key doesn't exist, or status is 'init'
          mwd.media_metadata IS NULL
          OR mwd.media_metadata -> afe.metadata_key IS NULL
          OR (mwd.media_metadata -> afe.metadata_key ->> 'status') = 'init'
      )
)
-- Final selection excluding items that already have InstructJobs
SELECT 
    inp.media_id,
    inp.media_data_id,
    inp.schema_id,
    inp.file_data,
    inp.file_mime_type,
    inp.metadata_key,
    inp.metadata_struct,
    inp.metadata_value
FROM items_needing_processing inp
WHERE NOT EXISTS (
    SELECT 1 FROM "InstructJob" ij
    WHERE ij.media_id = inp.media_id
      AND ij.key_type = 'metadata'
      AND ij.key_id = inp.metadata_key
)
ORDER BY inp.media_id, inp.metadata_key
LIMIT $1;