-- Function to merge anonymous user data into authenticated user account
-- Called when an anonymous user logs in via SMS, transferring their conversations & messages
CREATE OR REPLACE FUNCTION public.merge_anonymous_user_data(
  anon_user_id UUID,
  target_user_id UUID
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  conv_count INTEGER := 0;
  msg_count INTEGER := 0;
  ctx_count INTEGER := 0;
BEGIN
  -- Safety: caller must be the target user
  IF auth.uid() IS NULL OR auth.uid() <> target_user_id THEN
    RAISE EXCEPTION 'Unauthorized: caller must be target user';
  END IF;

  IF anon_user_id = target_user_id THEN
    RETURN jsonb_build_object('success', true, 'note', 'same user, nothing to merge');
  END IF;

  -- Move conversations
  UPDATE public.conversations
  SET user_id = target_user_id, updated_at = now()
  WHERE user_id = anon_user_id;
  GET DIAGNOSTICS conv_count = ROW_COUNT;

  -- Move messages
  UPDATE public.messages
  SET user_id = target_user_id
  WHERE user_id = anon_user_id;
  GET DIAGNOSTICS msg_count = ROW_COUNT;

  -- Move agent context (if any)
  UPDATE public.agent_context
  SET user_id = target_user_id, updated_at = now()
  WHERE user_id = anon_user_id;
  GET DIAGNOSTICS ctx_count = ROW_COUNT;

  RETURN jsonb_build_object(
    'success', true,
    'conversations_moved', conv_count,
    'messages_moved', msg_count,
    'contexts_moved', ctx_count
  );
END;
$$;

GRANT EXECUTE ON FUNCTION public.merge_anonymous_user_data(UUID, UUID) TO authenticated;