-- Merge/upsert DynamoDB item_info data from S3 stage into target table
MERGE INTO {ddb_item_info_table_name} as target
USING
(
    SELECT
        $1:Keys:itemId:S as id,
        $1:Keys:type:S as type,
        $1:NewImage:cover_photo_url:S as cover_photo_url,
        $1:NewImage:instagram_link:S as instagram_link,
        $1:NewImage:location:S as location,
        $1:NewImage:section_order:L as section_order,
        $1:NewImage:soundcloud_link:S as soundcloud_link,
        $1:NewImage:spotify_link:S as spotify_link,
        $1:NewImage:user_inputted_genres:L as user_inputted_genres,
        $1:NewImage:x_link:S as x_link,
        $1:NewImage:youtube_link:S as youtube_link,
        $1:NewImage as json_data,
        '{p_date}'::DATE as p_date,
        {p_hour} as p_hour
    FROM {stage_path} (file_format => 'BASIC_JSON_LOADER', pattern => ('.*'))
) as source
ON (target.id = source.id AND target.type = source.type)
WHEN MATCHED THEN
    UPDATE SET
        target.cover_photo_url = source.cover_photo_url,
        target.instagram_link = source.instagram_link,
        target.location = source.location,
        target.section_order = source.section_order,
        target.soundcloud_link = source.soundcloud_link,
        target.spotify_link = source.spotify_link,
        target.user_inputted_genres = source.user_inputted_genres,
        target.x_link = source.x_link,
        target.youtube_link = source.youtube_link,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour,
        target.json_data = source.json_data
WHEN NOT MATCHED THEN
    INSERT (id, type, cover_photo_url, instagram_link, location, section_order, soundcloud_link, spotify_link, user_inputted_genres, x_link, youtube_link, p_date, p_hour, json_data)
    VALUES (source.id, source.type, source.cover_photo_url, source.instagram_link, source.location, source.section_order, source.soundcloud_link, source.spotify_link, source.user_inputted_genres, source.x_link, source.youtube_link, source.p_date, source.p_hour, source.json_data);
