def email_game_invitation(requester_id: int, invited_user_email: str,
game_id: int):
if check_external_game_invite(requester_id, invited_user_email, game_id):
return
email_response = send_invite_email(requester_id, invited_user_email,
"game")
if not email_response:
raise InvalidEmailError
add_row('external_invites',
requester_id=requester_id,
invited_email=invited_user_email,
status="invited",
timestamp=time.time(),
game_id=game_id,
type='game')
# if a user isn't on the platform and doesn't have an invite, do there here as well
platform_id = get_user_ids_from_passed_emails([invited_user_email])
if not check_platform_invite_exists(
requester_id, invited_user_email) and not platform_id:
add_row('external_invites',
requester_id=requester_id,
invited_email=invited_user_email,
status="invited",
timestamp=time.time(),
game_id=None,
type='platform')
def respond_to_friend_invite(requester_username, invited_id, decision):
requester_id = get_user_ids([requester_username])[0]
add_row("friends",
requester_id=requester_id,
invited_id=invited_id,
status=decision,
timestamp=time.time())
def mark_invites_expired(game_id, status_list: List[str], update_time):
"""For a given game ID and list of statuses, this function will convert those invitations to "expired." This
happens when games past their invite window still have pending invitations, or when games pass their invite window
without meeting the minimum user count to kick off
"""
if not status_list:
return
with engine.connect() as conn:
result = conn.execute(
f"""
SELECT gi.user_id
FROM game_invites gi
INNER JOIN
(SELECT game_id, user_id, max(id) as max_id
FROM game_invites
GROUP BY game_id, user_id) grouped_gi
ON
gi.id = grouped_gi.max_id
WHERE
gi.game_id = %s AND
status IN ({','.join(['%s'] * len(status_list))});
""", game_id, *status_list)
ids_to_close = [x[0] for x in result]
for user_id in ids_to_close:
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status="expired",
timestamp=update_time)
def update_index_value(symbol):
value = get_index_value(symbol)
if during_trading_day():
add_row("indexes", symbol=symbol, value=value, timestamp=time.time())
return True
# a bit of logic to get the close of day price
with engine.connect() as conn:
max_time = conn.execute(
"SELECT MAX(timestamp) FROM indexes WHERE symbol = %s;",
symbol).fetchone()[0]
if max_time is None:
max_time = 0
ref_day = time.time()
eod = get_end_of_last_trading_day(ref_day)
while eod > ref_day:
ref_day -= SECONDS_IN_A_DAY
eod = get_end_of_last_trading_day(ref_day)
if max_time < eod <= time.time():
add_row("indexes", symbol=symbol, value=value, timestamp=eod)
return True
return False
def async_cache_price(self, symbol: str, price: float, last_updated: float):
cache_price, cache_time = get_cache_price(symbol)
if cache_price is not None and cache_time == last_updated:
return
if during_trading_day():
add_row("prices", symbol=symbol, price=price, timestamp=last_updated)
set_cache_price(symbol, price, last_updated)
def cancel_order(order_id: int):
order_ticket = query_to_dict("SELECT * FROM orders WHERE id = %s",
order_id)[0]
add_row("order_status",
order_id=order_id,
timestamp=time.time(),
status="cancelled")
removing_pending_order(order_ticket["game_id"], order_ticket["user_id"],
order_id)
def add_game(creator_id: int,
title: str,
game_mode: str,
duration: int,
benchmark: str,
stakes: str = None,
buy_in: float = None,
side_bets_perc=None,
side_bets_period: str = None,
invitees: List[str] = None,
invite_window: int = None,
email_invitees: List[str] = None):
if invitees is None:
invitees = []
if email_invitees is None:
email_invitees = []
opened_at = time.time()
invite_window_posix = None
if invite_window is not None:
invite_window_posix = opened_at + int(invite_window) * SECONDS_IN_A_DAY
game_id = add_row("games",
creator_id=creator_id,
title=title,
game_mode=game_mode,
duration=duration,
benchmark=benchmark,
buy_in=buy_in,
side_bets_perc=side_bets_perc,
side_bets_period=side_bets_period,
invite_window=invite_window_posix,
stakes=stakes)
user_ids = [creator_id]
if invitees:
user_ids += get_user_ids(invitees)
matched_ids = []
if email_invitees:
matched_ids = get_user_ids_from_passed_emails(email_invitees)
user_ids = list(set(user_ids).union(set(matched_ids)))
create_game_invites_entries(game_id, creator_id, user_ids, opened_at)
if game_mode in ["public", "multi_player"]:
add_row("game_status",
game_id=game_id,
status="pending",
timestamp=opened_at,
users=user_ids)
for email in email_invitees:
email_game_invitation(creator_id, email, game_id)
else:
kick_off_game(game_id, user_ids, opened_at)
return game_id
def close_open_game(game_id, update_time, close_status="expired"):
game_status_entry = query_to_dict(
"SELECT * FROM game_status WHERE game_id = %s", game_id)[0]
add_row("game_status",
game_id=game_id,
status=close_status,
users=json.loads(game_status_entry["users"]),
timestamp=update_time)
mark_invites_expired(game_id, ["invited"], update_time)
def invite_friend(requester_id, invited_id):
"""Since the user is sending the request, we'll know their user ID via their web token. We don't post this
information to the frontend for other users, though, so we'll look up their ID based on username
"""
add_row("friends",
requester_id=requester_id,
invited_id=invited_id,
status="invited",
timestamp=time.time())
def process_order(order_id: int):
timestamp = time.time()
if get_order_expiration_status(order_id):
add_row("order_status",
order_id=order_id,
timestamp=timestamp,
status="expired",
clear_price=None)
return
order_ticket = query_to_dict("SELECT * FROM orders WHERE id = %s",
order_id)[0]
symbol = order_ticket["symbol"]
game_id = order_ticket["game_id"]
user_id = order_ticket["user_id"]
buy_or_sell = order_ticket["buy_or_sell"]
quantity = order_ticket["quantity"]
order_type = order_ticket["order_type"]
market_price, _ = fetch_price(symbol)
# Only process active outstanding orders during trading day
cash_balance = get_current_game_cash_balance(user_id, game_id)
current_holding = get_current_stock_holding(user_id, game_id, symbol)
if during_trading_day():
if execute_order(buy_or_sell, order_type, market_price,
order_ticket["price"], cash_balance, current_holding,
quantity):
order_status_id = add_row("order_status",
order_id=order_id,
timestamp=timestamp,
status="fulfilled",
clear_price=market_price)
update_balances(user_id, game_id, order_status_id, timestamp,
buy_or_sell, cash_balance, current_holding,
market_price, quantity, symbol)
serialize_and_pack_pending_orders(
game_id, user_id) # refresh the pending orders table
add_fulfilled_order_entry(
game_id, user_id,
order_id) # add the new fulfilled orders entry to the table
serialize_and_pack_portfolio_details(game_id, user_id)
else:
# if a market order was placed after hours, there may not be enough cash on hand to clear it at the new
# market price. If this happens, cancel the order and recalculate the purchase quantity with the new price
if order_type == "market":
cancel_order(order_id)
updated_quantity = cash_balance // market_price
if updated_quantity <= 0:
return
place_order(user_id, game_id, symbol, buy_or_sell,
cash_balance, current_holding, order_type,
"Shares", market_price, updated_quantity,
order_ticket["time_in_force"])
serialize_and_pack_portfolio_details(game_id, user_id)
def add_to_game_invites_if_registered(game_id, invited_user_email):
# check and see if the externally invited user is registered in the DB. if they are, add them to the internal
# invite table as well
user_id = get_user_ids_from_passed_emails([invited_user_email])
if user_id:
add_row("game_invites",
game_id=game_id,
user_id=user_id[0],
status="invited",
timestamp=time.time())
def place_order(user_id: int,
game_id: int,
symbol: str,
buy_or_sell: str,
cash_balance: float,
current_holding: int,
order_type: str,
quantity_type: str,
market_price: float,
amount: float,
time_in_force: str,
stop_limit_price: float = None):
timestamp = time.time()
order_price = get_order_price(order_type, market_price, stop_limit_price)
order_quantity = get_order_quantity(order_price, amount, quantity_type)
if order_quantity <= 0:
raise NoNegativeOrders
if buy_or_sell == "buy":
qc_buy_order(order_type, quantity_type, order_price, market_price,
amount, cash_balance)
elif buy_or_sell == "sell":
qc_sell_order(order_type, quantity_type, order_price, market_price,
amount, current_holding)
else:
raise Exception(f"Invalid buy or sell option {buy_or_sell}")
# having validated the order, now we'll go ahead and book it
order_id = add_row("orders",
user_id=user_id,
game_id=game_id,
symbol=symbol,
buy_or_sell=buy_or_sell,
quantity=order_quantity,
price=order_price,
order_type=order_type,
time_in_force=time_in_force)
add_row("order_status",
order_id=order_id,
timestamp=timestamp,
status="pending",
clear_price=None)
# If this is a market order and we're inside a trading day we'll execute this order at the current price
if order_type == "market" and during_trading_day():
os_id = add_row("order_status",
order_id=order_id,
timestamp=timestamp,
status="fulfilled",
clear_price=order_price)
update_balances(user_id, game_id, os_id, timestamp, buy_or_sell,
cash_balance, current_holding, order_price,
order_quantity, symbol)
return order_id
def create_game_invites_entries(game_id: int, creator_id: int,
user_ids: List[int], opened_at: float):
for user_id in user_ids:
status = "invited"
if user_id == creator_id:
status = "joined"
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status=status,
timestamp=opened_at)
def close_finished_game_with_context(**context):
game_id = context_parser(context, "game_id")[0]
_, game_end = get_game_start_and_end(game_id)
current_time = time.time()
if current_time >= game_end:
user_ids = get_active_game_user_ids(game_id)
last_status_entry = query_to_dict("""SELECT * FROM game_status
WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1""", game_id)[0]
if last_status_entry["status"] == "active":
# close game and update ratings
add_row("game_status", game_id=game_id, status="finished", users=user_ids, timestamp=current_time)
update_ratings(game_id)
def make_db_mocks():
table_names = MOCK_DATA.keys()
db_metadata = MetaData(bind=engine)
db_metadata.reflect()
for table in table_names:
if MOCK_DATA.get(table) and table != 'users':
populate_table(table)
if table == 'users':
for user in MOCK_DATA.get(table):
add_row("users", name=user["name"], email=user["email"], profile_pic=user["profile_pic"],
username=user["username"], created_at=user["created_at"], provider=user["provider"],
password=None, resource_uuid=user["resource_uuid"])
def add_virtual_cash(game_id: int, user_id: int, dividend_id: int,
amount: float):
current_cash = get_current_game_cash_balance(user_id, game_id) + amount
now = datetime_to_posix(dt.now())
add_row("game_balances",
user_id=user_id,
game_id=game_id,
timestamp=now,
balance_type='virtual_cash',
balance=current_cash,
dividend_id=dividend_id,
transaction_type="stock_dividend")
def respond_to_game_invite(game_id: int, user_id: int, decision: str,
response_time: float):
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status=decision,
timestamp=response_time)
game_info = query_to_dict("SELECT * FROM games WHERE id = %s", game_id)[0]
if game_info["game_mode"] in ["single_player", "multi_player"]:
update_external_invites(game_id, user_id, decision)
update_game_if_all_invites_responded(game_id)
if game_info["game_mode"] == "public" and decision == "joined":
handle_public_game_acceptance(game_id)
def update_pending_game_status_for_new_user(game_id: int, user_id: int):
with engine.connect() as conn:
res = conn.execute(
"SELECT users FROM game_status WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1;",
game_id).fetchone()[0]
user_ids = json.loads(res)
if user_id not in user_ids:
user_ids += [user_id]
add_row("game_status",
game_id=game_id,
status="pending",
users=user_ids,
timestamp=time.time())
def add_user_via_platform(game_id: int, user_id: int):
# no adding users reduntantly
with engine.connect() as conn:
user_ids = json.loads(
conn.execute(
"SELECT users FROM game_status WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1;",
game_id).fetchone()[0])
if user_id in user_ids:
return
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status="invited",
timestamp=time.time())
update_pending_game_status_for_new_user(game_id, user_id)
def add_external_game_invites(email: str, user_id: int):
# is this user already invited to any games?
external_game_invites = get_pending_external_game_invites(email)
current_time = time.time()
for invite_entry in external_game_invites:
game_id = invite_entry["game_id"]
gs_entry = query_to_dict(
"SELECT * FROM game_status WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1",
game_id)[0]
if gs_entry["status"] == "pending":
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status="invited",
timestamp=current_time)
def email_platform_invitation(requester_id: int, invited_user_email: str):
"""Sends an email to your friend to joins stockbets, adds a friend request to the username once the person has
joined
"""
if check_platform_invite_exists(requester_id, invited_user_email):
return
email_response = send_invite_email(requester_id, invited_user_email)
if not email_response:
raise InvalidEmailError
add_row('external_invites',
requester_id=requester_id,
invited_email=invited_user_email,
status="invited",
timestamp=time.time(),
game_id=None,
type='platform')
def apply_stock_splits(start_time: float = None, end_time: float = None):
splits = get_splits(start_time, end_time)
if splits.empty:
return
last_prices = get_most_recent_prices(splits["symbol"].to_list())
for i, row in splits.iterrows():
symbol = row["symbol"]
numerator = row["numerator"]
denominator = row["denominator"]
exec_date = row["exec_date"]
df = get_active_holdings_for_games(symbol)
if df.empty:
continue
# make order status updates here. implement dask or spark here when the job getting to heavy to run locally
# (e.g. see https://docs.dask.org/en/latest/dataframe-api.html#dask.dataframe.read_sql_table)
df["transaction_type"] = "stock_split"
df["order_status_id"] = None
df["timestamp"] = exec_date
df["fractional_balance"] = df["balance"] * numerator / denominator
df["balance"] = df["balance"] * numerator // denominator
df["fractional_balance"] -= df["balance"]
# identify any fractional shares that need to be converted to cash
mask = df["fractional_balance"] > 0
fractional_df = df[mask]
last_price = float(last_prices.loc[last_prices["symbol"] == symbol,
"price"].iloc[0])
for _, fractional_row in fractional_df.iterrows():
game_id = fractional_row["game_id"]
user_id = fractional_row["user_id"]
cash_balance = get_current_game_cash_balance(user_id, game_id)
add_row("game_balances",
user_id=user_id,
game_id=game_id,
timestamp=exec_date,
balance_type="virtual_cash",
balance=cash_balance +
fractional_row["fractional_balance"] * last_price,
transaction_type="stock_split")
# write updated balances
del df["fractional_balance"]
with engine.connect() as conn:
df.to_sql("game_balances", conn, index=False, if_exists="append")
def update_external_invites(game_id: int, user_id: int, decision: str):
if decision == "joined":
decision = "accepted"
# check if the user has an external invite for this game. if they do, mark the external invite as accepted
user_email = get_user_information(user_id)["email"]
external_invite_entries = query_to_dict(
"""
SELECT * FROM external_invites WHERE
game_id = %s AND LOWER(REPLACE(invited_email, '.', '')) = %s;""",
game_id, standardize_email(user_email))
for entry in external_invite_entries:
add_row("external_invites",
requester_id=entry["requester_id"],
invited_email=user_email,
status=decision,
timestamp=time.time(),
game_id=game_id,
type="game")
def setUp(self):
self.game_id = 47
self.start_time = time.time()
self.engine = engine
self.requests_session = requests.Session()
s3_cache.flushall()
rds.flushall()
drop_all_tables()
os.system(f"mysql -h db -uroot main < database/fixtures/canonical_games/game_id_{self.game_id}.sql")
# convert stockbets_rating table ID to autoincrement
with self.engine.connect() as conn:
conn.execute("ALTER TABLE stockbets_rating CHANGE id id INT(11) AUTO_INCREMENT PRIMARY KEY;")
# manually add the indexes that we want to track to the scores table
for index in TRACKED_INDEXES:
add_row("stockbets_rating", index_symbol=index, rating=STARTING_ELO_SCORE, update_type="sign_up",
timestamp=-99, basis=0, n_games=0, total_return=0)
def process_payment():
user_id = decode_token(request)
game_id = request.json.get("game_id")
amount = request.json["amount"]
currency = request.json["currency"]
processor = request.json["processor"]
payment_type = request.json["type"]
payer_email = request.json.get("payer_email")
uuid = request.json.get("uuid")
winner_table_id = request.json.get("winner_table_id")
# get payment profile id
profile_id = check_payment_profile(user_id, processor, uuid, payer_email)
# register payment
add_row("payments", game_id=game_id, user_id=user_id, profile_id=profile_id, winner_table_id=winner_table_id,
type=payment_type, amount=amount, currency=currency, direction='inflow', timestamp=time.time())
return make_response("Payment processed", 200)
def kick_off_game(game_id: int, user_id_list: List[int], update_time):
"""Mark a game as active and seed users' virtual cash balances
"""
add_row("game_status",
game_id=game_id,
status="active",
users=user_id_list,
timestamp=update_time)
for user_id in user_id_list:
add_row("game_balances",
user_id=user_id,
game_id=game_id,
timestamp=update_time,
balance_type="virtual_cash",
balance=STARTING_VIRTUAL_CASH,
transaction_type="kickoff")
# Mark any outstanding invitations as "expired" now that the game is active
mark_invites_expired(game_id, ["invited"], update_time)
init_game_assets(game_id)
def test_db_helpers(self):
dummy_symbol = "ACME"
dummy_name = "ACME CORP"
symbol_id = add_row("symbols", symbol=dummy_symbol, name=dummy_name)
# There's nothing special about primary key #27. If we update the mocks this will need to update, too.
self.assertEqual(symbol_id, 27)
acme_entry = query_to_dict("SELECT * FROM symbols WHERE symbol = %s",
dummy_symbol)[0]
self.assertEqual(acme_entry["symbol"], dummy_symbol)
self.assertEqual(acme_entry["name"], dummy_name)
user_id = add_row("users",
name="diane browne",
email="*****@*****.**",
profile_pic="private",
username="******",
created_at=time.time(),
provider="twitter",
resource_uuid="aaa")
rds.set(f"{PLAYER_RANK_PREFIX}_{user_id}", STARTING_ELO_SCORE)
rds.set(f"{THREE_MONTH_RETURN_PREFIX}_{user_id}", 0)
new_entry = get_user_information(user_id)
self.assertEqual(new_entry["name"], "diane browne")
game_id = add_row("games",
creator_id=1,
title="db test",
game_mode="multi_player",
duration=1_000,
buy_in=0,
benchmark="return_ratio",
side_bets_perc=0,
invite_window=time.time() + 1_000_000_000_000)
add_row("game_status",
game_id=game_id,
status="pending",
users=[1, 1],
timestamp=time.time())
new_entry = get_game_info(game_id)
self.assertEqual(new_entry["title"], "db test")
def setup_new_user(name: str,
email: str,
profile_pic: str,
created_at: float,
provider: str,
resource_uuid: str,
password: str = None) -> int:
user_id = add_row("users",
name=name,
email=email,
username=None,
profile_pic=profile_pic,
created_at=created_at,
provider=provider,
password=password,
resource_uuid=resource_uuid)
requester_friends_ids = get_requester_ids_from_email(email)
for requester_id in requester_friends_ids:
add_row("external_invites",
requester_id=requester_id,
invited_email=email,
status="accepted",
timestamp=time.time(),
type="platform")
invite_friend(requester_id, user_id)
# seed public rank and 3-month return
add_row("stockbets_rating",
user_id=user_id,
index_symbol=None,
game_id=None,
rating=STARTING_ELO_SCORE,
update_type="sign_up",
timestamp=time.time(),
n_games=0,
total_return=0,
basis=0)
rds.set(f"{PLAYER_RANK_PREFIX}_{user_id}", STARTING_ELO_SCORE)
rds.set(f"{THREE_MONTH_RETURN_PREFIX}_{user_id}", 0)
return user_id
def update_balances(user_id, game_id, order_status_id, timestamp, buy_or_sell,
cash_balance, current_holding, order_price, order_quantity,
symbol):
"""This function books an order and updates a user's cash balance at the same time.
"""
sign = 1 if buy_or_sell == "buy" else -1
ttype = "stock_purchase" if buy_or_sell == "buy" else "stock_sale"
add_row("game_balances",
user_id=user_id,
game_id=game_id,
order_status_id=order_status_id,
timestamp=timestamp,
balance_type="virtual_cash",
balance=cash_balance - sign * order_quantity * order_price,
transaction_type=ttype)
add_row("game_balances",
user_id=user_id,
game_id=game_id,
order_status_id=order_status_id,
timestamp=timestamp,
balance_type="virtual_stock",
balance=current_holding + sign * order_quantity,
symbol=symbol,
transaction_type=ttype)
def check_payment_profile(user_id: int, processor: str, uuid: str,
payer_email: str):
"""Check to see if a payment profile entry exists for a user. If not create profile. Return profile_id
"""
profile_entry = query_to_dict(
"""
SELECT * FROM payment_profiles
WHERE user_id = %s AND uuid = %s AND processor = %s AND payer_email = %s
ORDER BY timestamp DESC LIMIT 1;""", user_id, uuid, processor,
payer_email)
if profile_entry:
return profile_entry[0]["id"]
return add_row("payment_profiles",
user_id=user_id,
processor=processor,
uuid=uuid,
payer_email=payer_email,
timestamp=time.time())
