-- Upsert audio upload data from RDS to Snowflake
-- This query inserts new records and updates existing ones based on ID and partition

merge into {audio_upload_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:updated_at as updated_at,
        $1:audio_s3_id as audio_s3_id,
        $1:error_type as error_type,
        $1:upload_type as upload_type,
        $1:created_at as created_at,
        $1:clip_id as clip_id,
        $1:user_id as user_id,
        $1:duration as duration,
        $1:has_vocal as has_vocal,
        $1:image_s3_id as image_s3_id,
        $1:requested_clip_id as requested_clip_id,
        $1:title as title,
        date(to_timestamp(created_at)) as p_date,
        hour(to_timestamp(created_at)) as p_hour,
        $1:inferred_lyrics as inferred_lyrics
    from {stage_path} (file_format => 'BASIC_PARQUET_LOADER', pattern => ('.*.parquet'))
) as source
on (target.id = source.id and target.p_date = source.p_date and target.p_hour = source.p_hour)
when matched then
    update set 
        target.updated_at = source.updated_at,
        target.audio_s3_id = source.audio_s3_id,
        target.error_type = source.error_type,
        target.upload_type = source.upload_type,
        target.created_at = source.created_at,
        target.clip_id = source.clip_id,
        target.user_id = source.user_id,
        target.duration = source.duration,
        target.has_vocal = source.has_vocal,
        target.image_s3_id = source.image_s3_id,
        target.requested_clip_id = source.requested_clip_id,
        target.title = source.title,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour,
        target.inferred_lyrics = source.inferred_lyrics
when not matched then
    insert (id, updated_at, audio_s3_id, error_type, upload_type, created_at, clip_id, user_id, duration, has_vocal, image_s3_id, requested_clip_id, title, p_date, p_hour, inferred_lyrics)
    values (source.id, source.updated_at, source.audio_s3_id, source.error_type, source.upload_type, source.created_at, source.clip_id, source.user_id, source.duration, source.has_vocal, source.image_s3_id, source.requested_clip_id, source.title, source.p_date, source.p_hour, source.inferred_lyrics);

