WITH latest_vendor_sync AS (
    -- Get the latest successful sync for each user / vendor combo
    SELECT 
        user_id,
        vendor,
        is_bot,
        status_code,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, vendor 
            ORDER BY created_at DESC
        ) as rn
    FROM SUNO_PROD.PROD.BOT_VENDOR_SYNC
    WHERE status_code IN (200, 201)
)
SELECT
    a.user_uid,
    a.user_id,
    a.meets_bot_definition as is_bot,
    a.clerk_id,
FROM SUNO_PROD.PROD.BOT_HOURLY a
LEFT JOIN latest_vendor_sync b
    ON a.user_id::varchar = b.user_id::varchar
    AND b.vendor = %(vendor)s
    AND b.rn = 1
WHERE 
    -- get bots that haven't been synced yet
    (
        a.meets_bot_definition
        AND (
            b.user_id IS NULL
            OR b.status_code IS NULL
            OR b.status_code NOT IN (200, 201)
            OR NOT b.is_bot
        )
    ) OR
    -- and bots that were reclassified as users
    (
        COALESCE(b.is_bot, FALSE) != COALESCE(a.meets_bot_definition, FALSE)
    )
LIMIT %(limit)s
