text: USE [Monopoly]
  GO
  
  /****** Object:  UserDefinedFunction [Game].[svfEvaluateOtherSpace]    Script Date: 2/19/2022 10:28:38 AM ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  CREATE FUNCTION [Game].[svfEvaluateOtherSpace] (
  	@player int,
  	@space tinyint,
  	@roll_total smallint
  )
  RETURNS FLOAT
  AS
  BEGIN
  	DECLARE @cost FLOAT;
  
  	DECLARE @space_name VARCHAR(50) = (SELECT [name] FROM [Game].[Board] WHERE [space] = @space);
  
  	IF @space_name = 'Go' OR @space_name = 'Jail' OR @space_name = 'Free Parking'
  		BEGIN
  			SET @cost = 0;
  		END
  	ELSE IF @space_name = 'Community Chest'
  		BEGIN
  			-- Initialize with sum of "Pay 50" cards
  			SET @cost = 200;
  
  			-- Add fee for number of buildings owned
  			SELECT @cost += 40 * ISNULL(SUM([houses]), 0)
  			FROM [Player].[PropertiesOwned]
  			WHERE [player] = @player;
  			SELECT @cost += 115 * COUNT([property])
  			FROM [Player].[PropertiesOwned]
  			WHERE [player] = @player AND [hotel] = 1;
  
  			-- Divide by number of Community Chest cards
  			SET @cost /= 17.0;
  		END
  	ELSE IF @space_name = 'Income Tax'
  		BEGIN
  			-- TODO: add 10% option
  			SET @cost = 200;
  		END
  	ELSE IF @space_name = 'Chance'
  		BEGIN
  			-- Initialize with simple fees
  			SET @cost = 65;
  
  			SELECT @cost += [Game].[svfEvaluatePropertyCost](@player, 'Illinois Avenue');
  
  			SELECT @cost += [Game].[svfEvaluatePropertyCost](@player, 'St. Charles Place');
  
  			-- Advance to nearest utility
  			DECLARE @nearest_utility VARCHAR(50);
  			SELECT TOP (1) @nearest_utility = [name]
  			FROM [Game].[Board]
  			WHERE [type] = 'Utility'
  			ORDER BY IIF([space] < @space, 40 - @space + [space], [space] - @space) ASC;
  			IF @nearest_utility IN (SELECT [utility] FROM [Player].[UtilitiesOwned])
  				AND @player <> (
  					SELECT [player]
  					FROM [Player].[UtilitiesOwned]
  					WHERE [utility] = @nearest_utility
  				)
  				-- Utility is owned by another player
  				SET @cost += 10 * @roll_total;
  			ELSE
  				-- Utility is available for purchase from the bank
  				SET @cost += 150;
  
  			-- Advance to nearest railroad
  			DECLARE @nearest_railroad VARCHAR(50);
  			SELECT TOP (1) @nearest_railroad = [name]
  			FROM [Game].[Board]
  			WHERE [type] = 'Railroad'
  			ORDER BY IIF([space] < @space, 40 - @space + [space], [space] - @space) ASC;
  			IF @nearest_railroad IN (SELECT [railroad] FROM [Player].[RailroadsOwned])
  				AND @player <> (
  					SELECT [player]
  					FROM [Player].[RailroadsOwned]
  					WHERE [railroad] = @nearest_railroad
  				)
  				-- Railroad is owned by another player
  				SELECT @cost += 2 * [rent] FROM [Game].[vwRailroadRent] WHERE [railroad] = @nearest_railroad;
  			ELSE
  				-- Railroad is available for purchase from the bank
  				SET @cost += 200;
  
  			-- Go three spaces back
  			DECLARE @prior_name VARCHAR(50), @prior_type VARCHAR(50);
  			SELECT @prior_name = [name], @prior_type = [type]
  			FROM [Game].[Board]
  			WHERE [space] = @space - 3;
  			SELECT @cost += CASE
  				WHEN @prior_type = 'Other' THEN [Game].[svfEvaluateOtherSpace](@player, @space - 3, @roll_total)
  				WHEN @prior_type = 'Property' THEN [Game].[svfEvaluatePropertyCost](@player, @prior_name)
  			END;
  
  			-- Add fee for number of buildings owned
  			SELECT @cost += 25 * ISNULL(SUM([houses]), 0)
  			FROM [Player].[PropertiesOwned]
  			WHERE [player] = @player;
  			SELECT @cost += 100 * COUNT([property])
  			FROM [Player].[PropertiesOwned]
  			WHERE [player] = @player AND [hotel] = 1;			
  			
  			SELECT @cost += [Game].[svfEvaluateRailroadCost](@player, 'Reading Railroad');
  			
  			SELECT @cost += [Game].[svfEvaluatePropertyCost](@player, 'Boardwalk');
  
  			-- Pay each player 50
  			SELECT @cost += 50 * COUNT([player])
  			FROM [Player].[vwPlayersNotBankrupt]
  			WHERE [player] <> @player;
  
  			-- Divide by number of Chance cards
  			SET @cost /= 16.0;
  		END
  	ELSE IF @space_name = 'Go To Jail'
  		BEGIN
  			SET @cost = 50;
  		END
  	ELSE IF @space_name = 'Luxury Tax'
  		BEGIN
  			SET @cost = 100;
  		END
  
  	RETURN @cost;
  END
  GO
  
  
  