DELIMITER ;;

CREATE OR REPLACE FUNCTION set_names_test(
  p_delivery_address TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'
) RETURNS TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'
  DETERMINISTIC
  SQL SECURITY INVOKER
  COMMENT ''
BEGIN
  DECLARE p_address, p_dest_name_full, p_zip, p_description, p_delivery_type, p_speditor_name, p_office_name, p_barsy_name_address, p_full_string TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
  
  SET p_full_string = CONCAT(
                        CONCAT(p_dest_name_full, IF(p_dest_name_full <> '', ', ', '')),
                        CONCAT(p_address,        IF(p_address <> '', ', ', '')),
                        CONCAT(p_zip,            IF(p_zip <> '', ', ', '')),
                        p_description
  );

  IF p_delivery_type = 'office' THEN
      SET p_full_string = CONCAT(
              CONCAT(p_speditor_name,  IF(p_speditor_name <> '', ', ', '')),
              CONCAT(p_dest_name_full, IF(p_dest_name_full <> '', ', ', '')),
              p_office_name
          );
  ELSEIF p_delivery_type = 'barsy' THEN
      SET p_full_string = p_barsy_name_address;
  END IF;

  SET p_full_string = TRIM(',' FROM TRIM(p_full_string));

  IF p_full_string = 'null' THEN
      SET p_full_string = '';
  END IF;

  RETURN p_full_string;
END;;
DELIMITER ;
