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

merge into {playlist_clip_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:relative_index as relative_index,
        $1:updated_at as updated_at,
        $1:clip_id as clip_id,
        $1:playlist_id as playlist_id,
        $1:updated_by_id as updated_by_id,
        $1:created_session_id as created_session_id,
        date(to_timestamp(updated_at)) as p_date,
        hour(to_timestamp(updated_at)) as p_hour
    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.relative_index = source.relative_index,
        target.updated_at = source.updated_at,
        target.clip_id = source.clip_id,
        target.playlist_id = source.playlist_id,
        target.updated_by_id = source.updated_by_id,
        target.created_session_id = source.created_session_id,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, relative_index, updated_at, clip_id, playlist_id, updated_by_id, created_session_id, p_date, p_hour)
    values (source.id, source.relative_index, source.updated_at, source.clip_id, source.playlist_id, source.updated_by_id, source.created_session_id, source.p_date, source.p_hour);
