WITH duplicate_cleanup AS (
    -- Delete duplicates and return the valid rows we want to process
    DELETE FROM label_maker_annotations
    WHERE id IN (
        SELECT id
        FROM (
            SELECT 
                id,
                ROW_NUMBER() OVER (
                    PARTITION BY user_id, task_id 
                    ORDER BY created_at
                ) as row_num
            FROM label_maker_annotations
            WHERE credits_processed = 0
        ) dups
        WHERE row_num > 1
    )
    RETURNING id
),
matched_tasks AS (
    SELECT 
        a.email, 
        d.extra_credits_balance, 
        l.user_id, 
        l.id, 
        l.credits_earned, 
        l.credits_processed
    FROM label_maker_annotations l
    JOIN auth_user a ON l.user_id = a.id
    JOIN bots_discordinfo d ON a.id = d.user_id
    LEFT JOIN moderation_rules m ON l.user_id::varchar = m.entity_id
        AND m.entity_type = 'user'
        AND m.capability = 'generate'
    WHERE TRUE
        AND COALESCE(m.status, 'granted') NOT IN ('revoked', 'degraded')
        AND l.credits_earned > 0
        AND l.credits_processed = 0
        AND NOT l.skipped
    LIMIT 10000
    FOR UPDATE OF l SKIP LOCKED
),
update_discord AS (
    UPDATE bots_discordinfo 
    SET 
        extra_credits_balance = bots_discordinfo.extra_credits_balance + (
            SELECT SUM(credits_earned) 
            FROM matched_tasks 
            WHERE matched_tasks.user_id = bots_discordinfo.user_id
        )
    FROM matched_tasks
    WHERE bots_discordinfo.user_id = matched_tasks.user_id
    RETURNING 
        bots_discordinfo.user_id,
        bots_discordinfo.extra_credits_balance as new_balance
)
UPDATE label_maker_annotations
SET credits_processed = credits_earned
WHERE id IN (SELECT id FROM matched_tasks)
RETURNING 
    (SELECT email FROM matched_tasks WHERE matched_tasks.id = label_maker_annotations.id) as email,
    user_id,
    id as annotation_id,
    credits_earned as credits_issued,
    credits_processed,
    (SELECT new_balance FROM update_discord WHERE update_discord.user_id = label_maker_annotations.user_id) as current_balance;
