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

MERGE INTO {user_delete_request_table_name} AS target
USING 
(
    SELECT 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:deleted_at as deleted_at,
        $1:persist_user_id as persist_user_id,
        $1:user_id as user_id,
        $1:clerk_id as clerk_id,
        $1:username as username,
        $1:discord_uid as discord_uid,
        $1:is_deleted as is_deleted,
        $1:is_operated_by_admin as is_operated_by_admin,
        date(to_timestamp($1: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.created_at = source.created_at,
        target.updated_at = source.updated_at,
        target.deleted_at = source.deleted_at,
        target.persist_user_id = source.persist_user_id,
        target.user_id = source.user_id,
        target.clerk_id = source.clerk_id,
        target.username = source.username,
        target.discord_uid = source.discord_uid,
        target.is_deleted = source.is_deleted,
        target.is_operated_by_admin = source.is_operated_by_admin,
        target.p_date = source.p_date
WHEN NOT MATCHED THEN
    INSERT (id, created_at, updated_at, deleted_at, persist_user_id, user_id, clerk_id, username, discord_uid, is_deleted, is_operated_by_admin, p_date)
    VALUES (source.id, source.created_at, source.updated_at, source.deleted_at, source.persist_user_id, source.user_id, source.clerk_id, source.username, source.discord_uid, source.is_deleted, source.is_operated_by_admin, source.p_date);

