ALTER PROCEDURE [dbo].[WZ_ServerGetPrices]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 0 AS ResultCode

    -- GAMESERVER prices
    SELECT
        0           AS 'Base_US',       -- 8500 base price per server per month in US region. 0 if region is disabled
        80000   AS 'Base_EU',       -- 8500 base price per server per month in europe region. 0 if region is disabled
        0           AS 'Base_RU',       -- 8500 base price per server per month in russian region. 0 if region is disabled
        90000   AS 'Base_SA',       -- South america. Should be 3x price of US\EU server due to high cost of renting server equipment in Brazil
        -- percents of base price per each option
        0       AS 'PVE',
        0       AS 'PVP',
        800     AS 'Slot1',         -- 10 plr
        1200        AS 'Slot2',         -- 20 plr
        -10     AS 'Slot3',         -- 30 plr
        -10     AS 'Slot4',         -- 40 plr
        -10     AS 'Slot5',         -- 50 plr
        0       AS 'Passworded',
        0       AS 'MonthX2',       --
        -10     AS 'MonthX3',       --
        -15     AS 'MonthX6',
        -70     AS 'WeekX1', -- for renting one week only
        0       AS 'OptNameplates',
        0       AS 'OptCrosshair',
        0       AS 'OptTracers'

    -- STRONGHOLD prices
    SELECT
        0       AS 'Base_US',       -- base price per server per month in US region. 0 if region is disabled
        0       AS 'Base_EU',       -- base price per server per month in europe region. 0 if region is disabled
        0       AS 'Base_RU',       -- base price per server per month in russian region. 0 if region is disabled
        0       AS 'Base_SA',
        -- percents of base price per each option
        0       AS 'PVE',
        0       AS 'PVP',
        0       AS 'Slot1',         -- 10 plr
        50      AS 'Slot2',         -- 20 plr
        100     AS 'Slot3',         -- 30 plr
        150     AS 'Slot4',         -- 40 plr
        200     AS 'Slot5',         -- 50 plr
        0       AS 'Passworded',
        0       AS 'MonthX2',       --
        0       AS 'MonthX3',       --
        0       AS 'MonthX6',
        -40     AS 'WeekX1', -- for renting one week only
        0       AS 'OptNameplates',
        0       AS 'OptCrosshair',
        0       AS 'OptTracers'

    -- games CAPACITY report
    DECLARE @Games_US INT = 0
    DECLARE @Games_EU INT = 0
    DECLARE @Games_RU INT = 0
    DECLARE @Games_SA INT = 0
    SELECT @Games_US = COUNT(*) FROM ServersList WHERE ServerRegion=1  AND WorkHours<RentHours AND ServerMap<>3
    SELECT @Games_EU = COUNT(*) FROM ServersList WHERE ServerRegion=10 AND WorkHours<RentHours AND ServerMap<>3
    SELECT @Games_RU = COUNT(*) FROM ServersList WHERE ServerRegion=20 AND WorkHours<RentHours AND ServerMap<>3
    SELECT @Games_SA = COUNT(*) FROM ServersList WHERE ServerRegion=30 AND WorkHours<RentHours AND ServerMap<>3

    SELECT
        -- modify numbers here. 1st is global server capacity. 2nd is our HOSTED server number
        -- mul by 2 as with hibernate we can support more servers now
        (650*4) - 0 AS 'Capacity_US', @Games_US AS 'Games_US',
        (50*1) - 0 AS 'Capacity_EU', @Games_EU AS 'Games_EU',
        (50*4)      AS 'Capacity_RU', @Games_RU AS 'Games_RU',
        (100*3) - 0  AS 'Capacity_SA', @Games_SA AS 'Games_SA'

    -- strongholds CAPACITY report
    SELECT @Games_US = COUNT(*) FROM ServersList WHERE ServerRegion=1  AND WorkHours<RentHours AND ServerMap=3
    SELECT @Games_EU = COUNT(*) FROM ServersList WHERE ServerRegion=10 AND WorkHours<RentHours AND ServerMap=3
    SELECT @Games_RU = COUNT(*) FROM ServersList WHERE ServerRegion=20 AND WorkHours<RentHours AND ServerMap=3
    SELECT @Games_SA = COUNT(*) FROM ServersList WHERE ServerRegion=30 AND WorkHours<RentHours AND ServerMap=3

    SELECT
        -- modify numbers here. 1st is global server capacity. 2nd is our HOSTED server number
        -- mul by 4 as with hibernate we can support more servers now
        (650*4) - 0 AS 'Capacity_US', @Games_US AS 'Games_US',
        (50*1) - 0 AS 'Capacity_EU', @Games_EU AS 'Games_EU',
        (50*4)      AS 'Capacity_RU', @Games_RU AS 'Games_RU',
        (100*3) - 0  AS 'Capacity_SA', @Games_SA AS 'Games_SA'

END