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

merge into {video_sprite_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:s3_id as s3_id,
        $1:thumbnail_width as thumbnail_width,
        $1:thumbnail_height as thumbnail_height,
        $1:image_width as image_width,
        $1:image_height as image_height,
        $1:interval as interval,
        $1:total_frames as total_frames,
        $1:video_upload_id as video_upload_id,
        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.id = source.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.s3_id = source.s3_id,
        target.thumbnail_width = source.thumbnail_width,
        target.thumbnail_height = source.thumbnail_height,
        target.image_width = source.image_width,
        target.image_height = source.image_height,
        target.interval = source.interval,
        target.total_frames = source.total_frames,
        target.video_upload_id = source.video_upload_id,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, created_at, updated_at, s3_id, thumbnail_width, thumbnail_height, image_width, image_height, interval, total_frames, video_upload_id, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.s3_id, source.thumbnail_width, source.thumbnail_height, source.image_width, source.image_height, source.interval, source.total_frames, source.video_upload_id, source.p_date, source.p_hour);


