519 lines
14 KiB
PL/PgSQL
519 lines
14 KiB
PL/PgSQL
create or replace function create_user(
|
|
p_name varchar,
|
|
-- p_discord_id numeric(20,0),
|
|
p_wallet varchar(64),
|
|
p_user_id uuid default null
|
|
) returns uuid
|
|
as $$
|
|
declare
|
|
new_uuid uuid;
|
|
res_id integer;
|
|
currency_start_amount float8;
|
|
begin
|
|
if p_user_id is null then
|
|
insert into users(name, wallet) values (p_name, p_wallet)
|
|
returning id into new_uuid;
|
|
else
|
|
new_uuid := p_user_id;
|
|
insert into users(id, name, wallet) values (p_user_id, p_name, p_wallet);
|
|
end if;
|
|
|
|
select value into currency_start_amount
|
|
from game_constants
|
|
where key = 'MoonbucksStartAmount';
|
|
|
|
insert into bank_account(user_id, balance) values (new_uuid, currency_start_amount);
|
|
|
|
insert into resource_account (resource_id, user_id, balance)
|
|
select resource.id, new_uuid, 50
|
|
from resource
|
|
left join lateral (values (id)) as subquery(value) on true;
|
|
|
|
return new_uuid;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_accounts(user_id uuid)
|
|
returns table (
|
|
id uuid,
|
|
"primaryBalance" float8,
|
|
"resourceAccounts" json
|
|
)
|
|
as $$
|
|
begin
|
|
return query
|
|
select
|
|
bank_account.id,
|
|
bank_account.balance,
|
|
json_agg(
|
|
json_build_object(
|
|
'id', resource_account.id,
|
|
'resourceType', resource.name,
|
|
'balance', resource_account.balance)
|
|
order by resource.name)
|
|
from bank_account
|
|
join resource_account on bank_account.user_id = resource_account.user_id
|
|
join resource on resource.id = resource_account.resource_id
|
|
where bank_account.user_id = $1
|
|
group by bank_account.id;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
|
|
create or replace function sell_resources(
|
|
p_user_id uuid,
|
|
p_resources jsonb
|
|
)
|
|
returns table (
|
|
"resourceName" text,
|
|
"resourceAmount" float8,
|
|
"returnAmount" float8,
|
|
"saleResult" text
|
|
)
|
|
as $$
|
|
declare
|
|
resource jsonb;
|
|
resource_name text;
|
|
resource_amount float8;
|
|
return_amount float8;
|
|
resource_sell_factor real;
|
|
sale_result text;
|
|
begin
|
|
for resource in select * from jsonb_array_elements(p_resources)
|
|
loop
|
|
resource_name := resource ->> 'resourceType';
|
|
resource_amount := (resource ->> 'resourceAmount')::float8;
|
|
sale_result := null;
|
|
|
|
begin
|
|
update resource_account
|
|
set balance = balance - resource_amount
|
|
where user_id = p_user_id and resource_id = (
|
|
select id from resource where name = resource_name
|
|
);
|
|
exception
|
|
-- TODO: Test this out to see if it is properly returned
|
|
when others then
|
|
sale_result := 'Error: Insufficient amount';
|
|
end;
|
|
|
|
if sale_result is null then
|
|
select value into resource_sell_factor
|
|
from game_constants
|
|
where key = resource_name || 'ToMoonbucks';
|
|
|
|
update bank_account
|
|
set balance = balance + resource_amount / resource_sell_factor
|
|
where user_id = p_user_id
|
|
returning resource_amount / resource_sell_factor into return_amount;
|
|
|
|
sale_result := 'Success';
|
|
end if;
|
|
return query select resource_name, resource_amount, return_amount, sale_result;
|
|
end loop;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function clear_user_data(p_user_id uuid)
|
|
returns void
|
|
as $$
|
|
begin
|
|
delete from staking_source where user_id = p_user_id;
|
|
delete from inventory_item where user_id = p_user_id;
|
|
update bank_account set balance = 2000 where user_id = p_user_id;
|
|
update resource_account set balance = 0 where user_id = p_user_id;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_game_config()
|
|
returns table (
|
|
resources json,
|
|
"gameConstants" json,
|
|
"stakingSources" json,
|
|
"storeItems" json
|
|
)
|
|
as $$
|
|
begin
|
|
select json_agg(name) into resources from resource;
|
|
select json_object_agg(key, value) into "gameConstants" from game_constants;
|
|
select json_agg(staking_source_item) into "stakingSources" from staking_source_item;
|
|
select json_agg(
|
|
json_build_object(
|
|
'id', store_item.id,
|
|
'name', store_item.name,
|
|
'description', store_item.description,
|
|
'price', store_item.price,
|
|
'image', store_item.image_name,
|
|
'claimAmount', store_item.claim_amount,
|
|
'completionTimeInMins', store_item.completion_time_in_mins,
|
|
'upgrades', upgrades
|
|
) order by store_item.id
|
|
) into "storeItems"
|
|
from store_item
|
|
join (
|
|
select store_item_id, json_agg(
|
|
json_build_object(
|
|
'tier', tier,
|
|
'price', upgrade_item.price,
|
|
'claimBoost', claim_boost
|
|
) order by upgrade_item.tier
|
|
) as upgrades
|
|
from upgrade_item
|
|
group by store_item_id
|
|
) u on u.store_item_id = store_item.id;
|
|
return next;
|
|
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_staking_sources(user_id uuid)
|
|
returns table (
|
|
id uuid,
|
|
name varchar,
|
|
description varchar,
|
|
"imageName" text,
|
|
"resourceWells" json,
|
|
"activeStakes" json
|
|
)
|
|
as $$
|
|
begin
|
|
return query
|
|
select
|
|
staking_source.id as id,
|
|
staking_source.name,
|
|
staking_source.description,
|
|
staking_source.image_name,
|
|
json_agg(
|
|
json_build_object(
|
|
'id', resource_well.id,
|
|
'resourceType', resource.name,
|
|
'supply', resource_well.supply
|
|
)
|
|
) as "resourceWells",
|
|
COALESCE (json_agg(
|
|
json_build_object(
|
|
'id', se.id,
|
|
'resourceType', resource.name,
|
|
'inventoryItemId', se.inventory_item_id,
|
|
'durationInMins', se.duration_in_mins,
|
|
'stakeAmount', se.stake_amount,
|
|
'startTime', se.created_at
|
|
)
|
|
) filter (where se.id is not null), '[]'::json) as "activeStakes"
|
|
from staking_source
|
|
inner join resource_well on resource_well.source_id = staking_source.id
|
|
inner join resource on resource.id = resource_well.resource_id
|
|
left join (
|
|
select staking_event.id, staking_event.well_id, staking_event.created_at,
|
|
duration_in_mins, stake_amount, inventory_item_id
|
|
from staking_event
|
|
left join claim_event on claim_event.staking_event_id = staking_event.id
|
|
where claim_event.staking_event_id is null
|
|
) se on se.well_id = resource_well.id
|
|
where staking_source.user_id = $1
|
|
group by staking_source.id;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function create_staking_source(p_user_id uuid)
|
|
returns uuid
|
|
as $$
|
|
declare
|
|
source_item record;
|
|
new_source_id uuid;
|
|
res_id integer;
|
|
address varchar;
|
|
price float8;
|
|
min_res float8;
|
|
max_res float8;
|
|
begin
|
|
select value into price from game_constants where key = 'price';
|
|
|
|
begin
|
|
update bank_account set balance = balance - price where user_id = p_user_id;
|
|
exception
|
|
when check_violation then
|
|
raise exception 'Insufficient funds';
|
|
end;
|
|
|
|
select * into source_item from staking_source_item order by random() limit 1;
|
|
select substr(md5(random()::text), 1, 16) AS random_string into address;
|
|
select value into min_res from game_constants where key = 'resourceMinStartAmount';
|
|
select value into max_res from game_constants where key = 'resourceMaxStartAmount';
|
|
|
|
insert into staking_source(user_id, name, description, image_name, address)
|
|
values (
|
|
p_user_id,
|
|
source_item.name,
|
|
source_item.description,
|
|
source_item.image_name,
|
|
'0x' || address
|
|
)
|
|
returning id into new_source_id;
|
|
|
|
insert into resource_well (source_id, resource_id, supply)
|
|
select new_source_id, resource.id, random_supply
|
|
from resource,
|
|
(select floor(random() * (max_res - min_res) + min_res) as random_supply)
|
|
subquery
|
|
left join lateral (values(id)) subquery2(value) on true;
|
|
|
|
return new_source_id as result;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_leaderboard(top_n integer)
|
|
returns table (
|
|
id uuid,
|
|
name varchar,
|
|
wallet varchar,
|
|
balance float8
|
|
)
|
|
as $$
|
|
begin
|
|
return query
|
|
select users.id, users.name, users.wallet, bank_account.balance
|
|
from users
|
|
join bank_account on bank_account.user_id = users.id
|
|
order by balance desc
|
|
limit top_n;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function purchase_item(
|
|
p_user_id uuid,
|
|
p_store_item_id integer
|
|
) returns uuid
|
|
as $$
|
|
declare
|
|
new_item_id uuid;
|
|
item_price float8;
|
|
begin
|
|
select price into item_price from store_item where store_item.id = p_store_item_id;
|
|
|
|
begin
|
|
update bank_account set balance = balance - item_price where user_id = p_user_id;
|
|
exception
|
|
when check_violation then
|
|
raise exception 'Insufficient funds';
|
|
end;
|
|
|
|
begin
|
|
insert into inventory_item (user_id, store_item_id) values (p_user_id, p_store_item_id)
|
|
returning id into new_item_id;
|
|
exception
|
|
when unique_violation then
|
|
raise exception 'User already owns this weapons';
|
|
end;
|
|
|
|
return new_item_id;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function upgrade_item(
|
|
p_user_id uuid,
|
|
p_item_id uuid
|
|
) returns uuid
|
|
as $$
|
|
declare
|
|
upgrade_event_id uuid;
|
|
item_insert_id uuid;
|
|
upgrade_price float8;
|
|
begin
|
|
|
|
select upgrade_item.price into upgrade_price
|
|
from inventory_item
|
|
join store_item on inventory_item.store_item_id = store_item.id
|
|
join upgrade_item on store_item.id = upgrade_item.store_item_id
|
|
where inventory_item.id = p_item_id and upgrade_item.tier = inventory_item.tier + 1;
|
|
|
|
if upgrade_price is null then
|
|
raise exception 'Inventory item not found';
|
|
end if;
|
|
|
|
begin
|
|
update bank_account set balance = balance - upgrade_price where user_id = p_user_id;
|
|
exception
|
|
when check_violation then
|
|
raise exception 'Insufficient funds';
|
|
end;
|
|
|
|
update inventory_item set tier = tier + 1
|
|
where inventory_item.id = p_item_id and inventory_item.user_id = p_user_id
|
|
returning id into item_insert_id;
|
|
|
|
if item_insert_id is null then
|
|
raise exception 'Inventory item does not belong to the user';
|
|
end if;
|
|
|
|
insert into upgrade_event(inventory_item_id) values (p_item_id) returning id into upgrade_event_id;
|
|
return upgrade_event_id;
|
|
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function stake(
|
|
p_user_id uuid,
|
|
p_well_id uuid,
|
|
p_inventory_item_id uuid
|
|
) returns uuid
|
|
as $$
|
|
declare
|
|
staking_event_id uuid;
|
|
item_stake_duration integer;
|
|
stake_end_time timestamp;
|
|
inv_id uuid;
|
|
stake_id uuid;
|
|
stake_created_at timestamp;
|
|
total_stake_amount float8;
|
|
found_resource_well_id uuid;
|
|
begin
|
|
|
|
select completion_time_in_mins
|
|
into item_stake_duration
|
|
from inventory_item
|
|
join store_item on inventory_item.store_item_id = store_item.id
|
|
where inventory_item.id = p_inventory_item_id;
|
|
|
|
select
|
|
inventory_item.id,
|
|
staking_event.id,
|
|
staking_event.created_at
|
|
from inventory_item
|
|
left join staking_event
|
|
on staking_event.inventory_item_id = inventory_item.id
|
|
where inventory_item.id = p_inventory_item_id and inventory_item.user_id = p_user_id
|
|
order by stake_created_at desc limit 1
|
|
into inv_id, stake_id, stake_created_at;
|
|
|
|
if inv_id is null then
|
|
raise exception 'Unable to find an inventory item that belongs to the user';
|
|
end if;
|
|
|
|
stake_end_time := stake_created_at + (item_stake_duration * interval '1 minute');
|
|
|
|
if stake_id is not null and now() at time zone 'utc' < stake_end_time then
|
|
raise exception 'Unable to find an inventory item that is owned and not actively staked';
|
|
end if;
|
|
|
|
select claim_amount + coalesce(claim_boost, 0)
|
|
into total_stake_amount
|
|
from inventory_item
|
|
join store_item on inventory_item.store_item_id = store_item.id
|
|
left join upgrade_item on store_item.id = upgrade_item.store_item_id
|
|
and upgrade_item.tier = inventory_item.tier
|
|
where inventory_item.id = p_inventory_item_id;
|
|
|
|
select resource_well.id
|
|
into found_resource_well_id
|
|
from resource_well
|
|
join staking_source on source_id = staking_source.id
|
|
where user_id = p_user_id and resource_well.id = p_well_id;
|
|
|
|
if not found then
|
|
raise exception 'Resource well does not belong to this user';
|
|
end if;
|
|
|
|
insert into staking_event(user_id, well_id, inventory_item_id, duration_in_mins, stake_amount)
|
|
values (p_user_id, p_well_id, p_inventory_item_id, item_stake_duration, total_stake_amount)
|
|
returning id into staking_event_id;
|
|
|
|
return staking_event_id;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_all_stakes(p_user_id uuid)
|
|
returns table (
|
|
id uuid,
|
|
"sourceId" uuid,
|
|
"wellId" uuid,
|
|
"inventoryItemId" uuid,
|
|
"resourceType" varchar,
|
|
"stakeAmount" float8,
|
|
"stakeTime" timestamp with time zone,
|
|
"durationInMins" integer,
|
|
unclaimed bool
|
|
)
|
|
as $$
|
|
begin
|
|
return query
|
|
select
|
|
staking_event.id,
|
|
staking_source.id as sourceId,
|
|
resource_well.id as wellId,
|
|
inventory_item_id as inventoryItemId,
|
|
resource.name as resourceType,
|
|
stake_amount as stakeAmount,
|
|
staking_event.created_at as stakeTime,
|
|
duration_in_mins as durationInMins,
|
|
case when claim_event.staking_event_id is null then true else false end as unclaimed
|
|
from staking_event
|
|
inner join resource_well on well_id = resource_well.id
|
|
inner join resource on resource.id = resource_well.resource_id
|
|
inner join staking_source on source_id = staking_source.id
|
|
left join claim_event on staking_event.id = claim_event.staking_event_id
|
|
where staking_event.user_id = p_user_id;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function claim(p_user_id uuid, p_stake_id uuid)
|
|
returns uuid
|
|
as $$
|
|
declare
|
|
stake record;
|
|
claim_event_id uuid;
|
|
stake_end_time timestamp;
|
|
resource_supply float8;
|
|
final_supply float8;
|
|
begin
|
|
select
|
|
staking_event.id,
|
|
resource.id as res_id,
|
|
staking_event.user_id,
|
|
well_id,
|
|
inventory_item_id,
|
|
duration_in_mins,
|
|
stake_amount,
|
|
created_at
|
|
into stake from staking_event
|
|
join resource_well on resource_well.id = well_id
|
|
join resource on resource.id = resource_well.resource_id
|
|
where staking_event.id = p_stake_id and staking_event.user_id = p_user_id;
|
|
|
|
if stake is null then
|
|
raise exception sqlstate '90001'
|
|
using message = 'Staking event not found';
|
|
end if;
|
|
|
|
if (select id from claim_event where staking_event_id = stake.id) is not null then
|
|
raise exception sqlstate '90001'
|
|
using message = 'Stake already claimed';
|
|
end if;
|
|
|
|
stake_end_time := stake.created_at + (stake.duration_in_mins * interval '1 minute');
|
|
|
|
if now() at time zone 'utc' < stake_end_time then
|
|
raise exception sqlstate '90001'
|
|
using message = 'Stake has not finished, cannot claim';
|
|
end if;
|
|
|
|
select supply from resource_well where id = stake.well_id into resource_supply;
|
|
|
|
final_supply := case when resource_supply > stake.stake_amount
|
|
then stake.stake_amount
|
|
else resource_supply
|
|
end;
|
|
|
|
update resource_account set balance = balance + final_supply
|
|
where user_id = p_user_id and resource_id = stake.res_id;
|
|
|
|
update resource_well set supply = supply - final_supply
|
|
where resource_id = stake.res_id;
|
|
|
|
insert into claim_event(staking_event_id, claim_amount) values (stake.id, final_supply)
|
|
returning id into claim_event_id;
|
|
|
|
return claim_event_id;
|
|
end;
|
|
$$ language plpgsql;
|