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())