WITH latest_date AS (
    SELECT p_date
    FROM SUNO_PROD.PROD.%(table_name)s
    ORDER BY p_date DESC
    LIMIT 1
),
ranked_clips AS (
    SELECT 
        tm.CLIP_ID, 
        tm.SCORE,
        tm.QUALITY_MULTIPLIER,
        tm.SCORE * COALESCE(tm.QUALITY_MULTIPLIER, 1) as WEIGHTED_SCORE,
        tm.INFERRED_LANGUAGE,
        ROW_NUMBER() OVER (PARTITION BY tm.INFERRED_LANGUAGE ORDER BY WEIGHTED_SCORE DESC) as language_rank,
        ROW_NUMBER() OVER (ORDER BY WEIGHTED_SCORE DESC) as global_rank
    FROM 
        SUNO_PROD.PROD.%(table_name)s tm
    JOIN 
        latest_date ld 
        ON tm.P_DATE = ld.p_date
    WHERE
        tm.score > 0
)
SELECT 
    CLIP_ID, 
    SCORE,
    QUALITY_MULTIPLIER,
    WEIGHTED_SCORE,
    '--' as INFERRED_LANGUAGE
FROM 
    ranked_clips
WHERE 
    global_rank <= 50

UNION ALL

SELECT 
    CLIP_ID, 
    SCORE,
    QUALITY_MULTIPLIER,
    WEIGHTED_SCORE,
    INFERRED_LANGUAGE
FROM 
    ranked_clips
WHERE 
    language_rank <= 50
    AND INFERRED_LANGUAGE IN (%(language_codes)s)
ORDER BY 
    INFERRED_LANGUAGE, 
    WEIGHTED_SCORE DESC
