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

MERGE INTO {user_config_table_name} AS target
USING 
(
    SELECT 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:shown_creation_tour as shown_creation_tour,
        $1:user_id as user_id,
        $1:trending_song_language as trending_song_language,
        $1:trending_song_rankby as trending_song_rankby,
        $1:has_accepted_timbaland_terms as has_accepted_timbaland_terms,
        $1:perferred_tags as perferred_tags,
        $1:shown_claim_username as shown_claim_username,
        $1:has_set_remix_perm as has_set_remix_perm,
        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.shown_creation_tour = source.shown_creation_tour,
        target.user_id = source.user_id,
        target.trending_song_language = source.trending_song_language,
        target.trending_song_rankby = source.trending_song_rankby,
        target.has_accepted_timbaland_terms = source.has_accepted_timbaland_terms,
        target.perferred_tags = source.perferred_tags,
        target.shown_claim_username = source.shown_claim_username,
        target.has_set_remix_perm = source.has_set_remix_perm,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
WHEN NOT MATCHED THEN
    INSERT (id, created_at, updated_at, shown_creation_tour, user_id, trending_song_language, trending_song_rankby, has_accepted_timbaland_terms, perferred_tags, shown_claim_username, has_set_remix_perm, p_date, p_hour)
    VALUES (source.id, source.created_at, source.updated_at, source.shown_creation_tour, source.user_id, source.trending_song_language, source.trending_song_rankby, source.has_accepted_timbaland_terms, source.perferred_tags, source.shown_claim_username, source.has_set_remix_perm, source.p_date, source.p_hour);

