Gacha game centered around vore.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
vore-gacha/migrations/committed/000017-pull-command-gets-ti...

132 lines
5.9 KiB

--! Previous: sha1:c36f619890ff43d233e58abb4c5b9d5a4e556ca3
--! Hash: sha1:3a9ed9f26777390a1dccadc516bade27ffddf684
--! Message: pull command gets tiers
ALTER TABLE UnitTier
ADD COLUMN IF NOT EXISTS pullWeightLower FLOAT NOT NULL DEFAULT 0,
ADD COLUMN IF NOT EXISTS pullWeightUpper FLOAT NOT NULL DEFAULT 0;
--- Updates the UnitTier pull weight bounds to span from 0.0 to 1.0 and match the weights given.
CREATE OR REPLACE PROCEDURE UnitTier_UpdatePullWeightBounds() AS
$$
WITH weights AS (
SELECT UnitTier.id AS id,
(sum(UnitTier.pullWeight) OVER (ORDER BY UnitTier.sortOrder) - UnitTier.pullWeight)::FLOAT /
(sum(UnitTier.pullWeight) OVER ())::FLOAT AS newPullWeightLower,
(sum(pullWeight) OVER (ORDER BY sortOrder))::FLOAT /
(sum(pullWeight) OVER ())::FLOAT AS newPullWeightUpper
FROM UnitTier
)
UPDATE UnitTier
SET pullWeightLower = weights.newPullWeightLower,
pullWeightUpper = weights.newPullWeightUpper
FROM weights
WHERE weights.id = UnitTier.id;
$$ LANGUAGE 'sql';
CALL UnitTier_UpdatePullWeightBounds();
DROP TRIGGER IF EXISTS UnitTier_UpdatePullWeightBounds_Trigger ON UnitTier;
CREATE OR REPLACE FUNCTION UnitTier_UpdatePullWeightBounds_TriggerFunc() RETURNS TRIGGER AS
$$
BEGIN
CALL UnitTier_UpdatePullWeightBounds();
END;
$$
LANGUAGE 'plpgsql';
--- Automatically updates the pull weight bounds whenever the table changes.
CREATE TRIGGER UnitTier_UpdatePullWeightBounds_Trigger
AFTER INSERT OR UPDATE OF pullWeight OR DELETE
ON UnitTier
FOR EACH STATEMENT
EXECUTE PROCEDURE UnitTier_UpdatePullWeightBounds_TriggerFunc();
DROP FUNCTION IF EXISTS Command_Pull(
IN requestedChannel DiscordChannel.discordId%TYPE,
IN requestedGuild DiscordChannel.guildId%TYPE,
IN forId DiscordUser.discordId%TYPE,
IN newUsername DiscordUser.username%TYPE,
IN newDiscriminator DiscordUser.discriminator%TYPE,
IN count INT
);
--- Runs the full /pull command.
--- Error codes:
--- VGBCG: Bad channel (game). This is not a valid channel to send game commands in.
--- VGBGG: Bad guild (game). This is not a valid guild to send game commands in.
--- VGNYJ: Not yet joined. The Discord user using has not joined the game yet.
--- VGNEC: Not enough currency.
CREATE OR REPLACE FUNCTION Command_Pull(
IN requestedChannel DiscordChannel.discordId%TYPE,
IN requestedGuild DiscordChannel.guildId%TYPE,
IN forId DiscordUser.discordId%TYPE,
IN newUsername DiscordUser.username%TYPE,
IN newDiscriminator DiscordUser.discriminator%TYPE,
IN count INT
)
RETURNS TABLE
(
summonedUnitInstanceId SummonedUnit.instanceId%TYPE,
summonedUnitId Unit.id%TYPE,
summonedUnitName Unit.name%TYPE,
summonedUnitSubtitle Unit.subtitle%TYPE,
summonedUnitTierId UnitTier.id%TYPE,
summonedUnitTierName UnitTier.name%TYPE,
summonCost Player.currency%TYPE,
resultingCurrency Player.currency%TYPE,
firstTimePull BOOLEAN,
wasAlreadySummoned BOOLEAN
)
STRICT
VOLATILE
ROWS 10
AS
$$
DECLARE
costPerSummon CONSTANT INT := 10;
playerId Player.id%TYPE;
cost Player.currency%TYPE;
oldCurrency Player.currency%TYPE;
playerLastDaily Player.lastDaily%TYPE;
BEGIN
CALL CheckGameCommandIn(requestedChannel, requestedGuild);
SELECT InvokingPlayer.id, InvokingPlayer.currency, InvokingPlayer.lastDaily
INTO playerId, oldCurrency, playerLastDaily
FROM GetInvokingPlayer(forId, newUsername, newDiscriminator) AS InvokingPlayer;
IF playerId IS NULL THEN
RAISE EXCEPTION USING
ERRCODE = 'VGNYJ',
MESSAGE = 'Not yet joined',
DETAIL = 'You haven''t joined the game yet, and can''t use this command until you do.',
HINT = 'Use the /join command to join the game!';
END IF;
cost = costPerSummon * count;
UPDATE Player SET currency = currency - cost WHERE id = playerId AND currency >= cost;
IF NOT FOUND THEN
RAISE EXCEPTION USING
ERRCODE = 'VGNEC',
MESSAGE = 'Not enough currency',
DETAIL = format('Pulling %s heroines would cost %s currency, but you only have %s currency.',
count, cost, oldCurrency),
HINT = CASE playerLastDaily IS NULL OR playerLastDaily < NOW() - '1 day'::interval
WHEN TRUE THEN 'Try using the /daily command to get some more currency for today!'
ELSE format('Wait %s and you can use the /daily command to get some more currency!',
(playerLastDaily + '1 day'::interval) - NOW())
END;
END IF;
RETURN QUERY
WITH rng AS (SELECT summonId, random() AS tierRandom FROM generate_series(1, count) AS series(summonId))
SELECT NULL::INTEGER AS summonedUnitInstanceId,
NULL::INTEGER AS summonedUnitId,
NULL::VARCHAR AS summonedUnitName,
NULL::VARCHAR AS summonedUnitSubtitle,
t.id AS summonedUnitTierId,
t.name AS summonedUnitTierName,
costPerSummon AS summonCost,
oldCurrency - (costPerSummon * r.summonId) AS resultingCurrency,
NULL::BOOLEAN AS firstTimePull,
NULL::BOOLEAN AS wasAlreadySummoned
FROM rng AS r
INNER JOIN UnitTier AS t ON (t.pullWeightLower <= r.tierRandom AND t.pullWeightUpper > r.tierRandom);
END;
$$ LANGUAGE 'plpgsql';