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

MERGE INTO {profile_follow_table_name} AS target
USING 
(
    SELECT 
        $1:id as id,
        $1:followed_profile_id as followed_profile_id,
        $1:following_profile_id as following_profile_id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        date(to_timestamp(created_at)) as p_date
    FROM {stage_path} (file_format => 'BASIC_PARQUET_LOADER', pattern => ('.*.parquet'))
) AS source
ON (target.id = source.id AND target.p_date = source.p_date)
WHEN MATCHED THEN
    UPDATE SET 
        target.followed_profile_id = source.followed_profile_id,
        target.following_profile_id = source.following_profile_id,
        target.created_at = source.created_at,
        target.updated_at = source.updated_at,
        target.p_date = source.p_date
WHEN NOT MATCHED THEN
    INSERT (id, followed_profile_id, following_profile_id, created_at, updated_at, p_date)
    VALUES (source.id, source.followed_profile_id, source.following_profile_id, source.created_at, source.updated_at, source.p_date);

