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

merge into {clip_extra_table_name} as target
using 
(
    select 
        $1:clip_id as clip_id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:download_audio_count as download_audio_count,
        $1:download_video_count as download_video_count,
        $1:download_audio_wav_count as download_audio_wav_count,
        $1:share_count as share_count,
        $1:is_public_approved as is_public_approved,
        $1:inferred_language as inferred_language,
        $1:lyrics_copyright_metadata as lyrics_copyright_metadata,
        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.created_at = source.created_at,
        target.updated_at = source.updated_at,
        target.download_audio_count = source.download_audio_count,
        target.download_video_count = source.download_video_count,
        target.download_audio_wav_count = source.download_audio_wav_count,
        target.share_count = source.share_count,
        target.is_public_approved = source.is_public_approved,
        target.inferred_language = source.inferred_language,
        target.lyrics_copyright_metadata = source.lyrics_copyright_metadata,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (clip_id, created_at, updated_at, download_audio_count, download_video_count, download_audio_wav_count, share_count, is_public_approved, inferred_language, lyrics_copyright_metadata, p_date, p_hour)
    values (source.clip_id, source.created_at, source.updated_at, source.download_audio_count, source.download_video_count, source.download_audio_wav_count, source.share_count, source.is_public_approved, source.inferred_language, source.lyrics_copyright_metadata, source.p_date, source.p_hour);

