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

merge into {invite_history_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:updated_at as updated_at,
        $1:invitee_reward_amount as invitee_reward_amount,
        $1:inviter_reward_amount as inviter_reward_amount,
        $1:invitee_id as invitee_id,
        $1:inviter_id as inviter_id,
        $1:is_active as is_active,
        $1:is_recorded as is_recorded,
        $1:reward_condition_met_at as reward_condition_met_at,
        $1:has_sent_credit_limit_depletion_email as has_sent_credit_limit_depletion_email,
        $1:has_sent_email_to_user_credits as has_sent_email_to_user_credits,
        $1:has_sent_invite_success_email as has_sent_invite_success_email,
        $1:created_at_new as created_at_new,
        $1:created_at as created_at,
        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.updated_at = source.updated_at,
        target.invitee_reward_amount = source.invitee_reward_amount,
        target.inviter_reward_amount = source.inviter_reward_amount,
        target.invitee_id = source.invitee_id,
        target.inviter_id = source.inviter_id,
        target.is_active = source.is_active,
        target.is_recorded = source.is_recorded,
        target.reward_condition_met_at = source.reward_condition_met_at,
        target.has_sent_credit_limit_depletion_email = source.has_sent_credit_limit_depletion_email,
        target.has_sent_email_to_user_credits = source.has_sent_email_to_user_credits,
        target.has_sent_invite_success_email = source.has_sent_invite_success_email,
        target.created_at_new = source.created_at_new,
        target.created_at = source.created_at
when not matched then
    insert (id, updated_at, invitee_reward_amount, inviter_reward_amount, invitee_id, inviter_id, is_active, is_recorded, reward_condition_met_at, has_sent_credit_limit_depletion_email, has_sent_email_to_user_credits, has_sent_invite_success_email, created_at_new, created_at, p_date, p_hour)
    values (source.id, source.updated_at, source.invitee_reward_amount, source.inviter_reward_amount, source.invitee_id, source.inviter_id, source.is_active, source.is_recorded, source.reward_condition_met_at, source.has_sent_credit_limit_depletion_email, source.has_sent_email_to_user_credits, source.has_sent_invite_success_email, source.created_at_new, source.created_at, source.p_date, source.p_hour);

