-- Upsert clip embedding data from RDS to Snowflake
-- This query inserts new records and updates existing ones based on CLIP_ID and partition

merge into {clip_embedding_table_name} as target
using 
(
    select 
        $1:clip_id as clip_id,
        $1:vector as vector,
        $1:similar_clip_ids as similar_clip_ids,
        $1:is_processing as is_processing,
        $1:lyric_semantic_embedding as lyric_semantic_embedding,
        $1:ditto_self_sim_embedding as ditto_self_sim_embedding,
        $1:ditto_genre_embedding as ditto_genre_embedding,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        date(to_timestamp(created_at)) as p_date,
        hour(to_timestamp(created_at)) as p_hour
    from {stage_path} (file_format => 'BASIC_PARQUET_LOADER', pattern => ('.*.parquet'))
) as source
on (target.clip_id = source.clip_id and target.p_date = source.p_date and target.p_hour = source.p_hour)
when matched then
    update set 
        target.vector = source.vector,
        target.similar_clip_ids = source.similar_clip_ids,
        target.is_processing = source.is_processing,
        target.lyric_semantic_embedding = source.lyric_semantic_embedding,
        target.ditto_self_sim_embedding = source.ditto_self_sim_embedding,
        target.ditto_genre_embedding = source.ditto_genre_embedding,
        target.created_at = source.created_at,
        target.updated_at = source.updated_at,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (clip_id, vector, similar_clip_ids, is_processing, lyric_semantic_embedding, ditto_self_sim_embedding, ditto_genre_embedding, created_at, updated_at, p_date, p_hour)
    values (source.clip_id, source.vector, source.similar_clip_ids, source.is_processing, source.lyric_semantic_embedding, source.ditto_self_sim_embedding, source.ditto_genre_embedding, source.created_at, source.updated_at, source.p_date, source.p_hour);

