Files
KittyRec2018-Leak/core/database.py
2026-07-24 15:38:44 +00:00

3613 lines
151 KiB
Python

import os
import sqlite3
import threading
from core.password_crypto import encrypt_password, check_password as crypto_check_password, has_password as crypto_has_password, decrypt_password
from werkzeug.security import generate_password_hash
class Database:
def __init__(self, db_path="kittyrec.db"):
self.db_path = db_path
self.local = threading.local()
_root = os.path.dirname(os.path.abspath(db_path if os.path.isabs(db_path) else os.path.join(os.getcwd(), db_path)))
self._dir_profile = os.path.join(_root, "uploads", "images", "profile")
self._dir_hd = os.path.join(_root, "uploads", "images", "hd")
self._dir_lq = os.path.join(_root, "uploads", "images", "lq")
self._dir_history = os.path.join(_root, "uploads", "images", "pfp_history")
for _d in (self._dir_profile, self._dir_hd, self._dir_lq, self._dir_history):
os.makedirs(_d, exist_ok=True)
self._amp_log_path = os.path.join(_root, "logs", "amplitude_events.jsonl")
self._amp_lock = threading.Lock()
self._init_db()
def _get_conn(self):
if not hasattr(self.local, "conn") or self.local.conn is None:
self.local.conn = sqlite3.connect(self.db_path, timeout=30, isolation_level=None)
self.local.conn.row_factory = sqlite3.Row
return self.local.conn
def _init_db(self):
conn = self._get_conn()
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("PRAGMA foreign_keys=ON")
conn.execute("PRAGMA temp_store=MEMORY")
conn.execute("PRAGMA cache_size=-32000")
conn.commit()
conn.executescript("""
CREATE TABLE IF NOT EXISTS profiles (
id INTEGER PRIMARY KEY AUTOINCREMENT,
platform TEXT NOT NULL,
platform_id TEXT NOT NULL,
username TEXT NOT NULL,
display_name TEXT NOT NULL,
password TEXT NOT NULL DEFAULT '',
xp INTEGER DEFAULT 999999,
level INTEGER DEFAULT 30,
reputation INTEGER DEFAULT 0,
verified INTEGER DEFAULT 1,
developer INTEGER DEFAULT 0,
bio TEXT DEFAULT '',
birthday TEXT DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
registration_status INTEGER DEFAULT 2,
tokens INTEGER DEFAULT 0,
UNIQUE(platform, platform_id)
);
CREATE TABLE IF NOT EXISTS avatars (
profile_id TEXT PRIMARY KEY,
outfit_selections TEXT DEFAULT '',
skin_color TEXT DEFAULT '',
hair_color TEXT DEFAULT '',
face_features TEXT DEFAULT ''
);
CREATE TABLE IF NOT EXISTS settings (
profile_id TEXT NOT NULL,
key TEXT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY(profile_id, key)
);
CREATE TABLE IF NOT EXISTS profile_images (
profile_id TEXT PRIMARY KEY,
image_data BLOB
);
CREATE TABLE IF NOT EXISTS cached_logins (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
token TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS relationships (
profile_id INTEGER NOT NULL,
target_id INTEGER NOT NULL,
relationship_type INTEGER NOT NULL DEFAULT 0,
muted INTEGER NOT NULL DEFAULT 0,
ignored INTEGER NOT NULL DEFAULT 0,
favorited INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(profile_id, target_id)
);
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
from_player_id INTEGER NOT NULL,
to_player_id INTEGER NOT NULL,
type INTEGER NOT NULL,
data TEXT NOT NULL DEFAULT '',
room_id INTEGER DEFAULT NULL,
player_event_id INTEGER DEFAULT NULL,
sent_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS chat_threads (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS chat_thread_players (
thread_id INTEGER NOT NULL,
profile_id INTEGER NOT NULL,
last_read_message_id INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(thread_id, profile_id)
);
CREATE TABLE IF NOT EXISTS chat_messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
thread_id INTEGER NOT NULL,
sender_id INTEGER NOT NULL,
contents TEXT NOT NULL,
sent_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS gifts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
avatar_item_desc TEXT DEFAULT '',
consumable_item_desc TEXT DEFAULT '',
equipment_prefab_name TEXT DEFAULT '',
equipment_modification_guid TEXT DEFAULT '',
currency_type INTEGER DEFAULT 0,
currency INTEGER DEFAULT 0,
xp INTEGER DEFAULT 0,
level INTEGER DEFAULT 0,
gift_rarity INTEGER DEFAULT 0,
message TEXT DEFAULT '',
gift_context INTEGER DEFAULT 0,
consumed INTEGER DEFAULT 0,
pushed INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS consumable_inventory (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
consumable_item_desc TEXT NOT NULL,
count INTEGER DEFAULT 1,
is_active INTEGER DEFAULT 0,
unlocked_level INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
UNIQUE(profile_id, consumable_item_desc)
);
CREATE TABLE IF NOT EXISTS rooms (
id INTEGER PRIMARY KEY AUTOINCREMENT,
creator_id INTEGER NOT NULL,
name TEXT DEFAULT '',
description TEXT DEFAULT '',
activity_level_id TEXT DEFAULT '',
accessibility INTEGER DEFAULT 0,
is_sandbox INTEGER DEFAULT 0,
instanced INTEGER DEFAULT 1,
max_players INTEGER DEFAULT 20,
screen_mode_support INTEGER DEFAULT 0,
data_blob_name TEXT DEFAULT '',
state INTEGER DEFAULT 0,
data_blob BLOB DEFAULT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
modified_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS room_permissions (
room_id INTEGER NOT NULL,
player_id INTEGER NOT NULL,
is_host INTEGER DEFAULT 0,
is_owner INTEGER DEFAULT 0,
PRIMARY KEY (room_id, player_id)
);
CREATE TABLE IF NOT EXISTS room_bookmarks (
player_id INTEGER NOT NULL,
room_id INTEGER NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (player_id, room_id)
);
CREATE TABLE IF NOT EXISTS room_cheers (
player_id INTEGER NOT NULL,
room_id INTEGER NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (player_id, room_id)
);
CREATE TABLE IF NOT EXISTS unlocked_items (
profile_id INTEGER NOT NULL,
avatar_item_desc TEXT NOT NULL,
unlocked_at TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(profile_id, avatar_item_desc)
);
CREATE TABLE IF NOT EXISTS player_notes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
note TEXT NOT NULL,
created_by TEXT NOT NULL DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
""")
conn.commit()
for idx_sql in [
"CREATE INDEX IF NOT EXISTS idx_profiles_username ON profiles(username)",
"CREATE INDEX IF NOT EXISTS idx_profiles_platform ON profiles(platform, platform_id)",
"CREATE INDEX IF NOT EXISTS idx_relationships_pid ON relationships(profile_id)",
"CREATE INDEX IF NOT EXISTS idx_messages_to ON messages(to_player_id)",
"CREATE INDEX IF NOT EXISTS idx_gifts_profile ON gifts(profile_id, consumed)",
"CREATE INDEX IF NOT EXISTS idx_platform_accounts ON platform_accounts(platform, platform_id)",
"CREATE INDEX IF NOT EXISTS idx_subscriptions_sub ON subscriptions(subscriber_id)",
"CREATE INDEX IF NOT EXISTS idx_unlocked_items_pid ON unlocked_items(profile_id)",
"CREATE INDEX IF NOT EXISTS idx_profiles_banned ON profiles(banned)",
"CREATE INDEX IF NOT EXISTS idx_ban_log_profile ON ban_log(profile_id)",
"CREATE INDEX IF NOT EXISTS idx_ip_logins_profile ON ip_logins(profile_id)",
"CREATE INDEX IF NOT EXISTS idx_messages_from ON messages(from_player_id)",
"CREATE INDEX IF NOT EXISTS idx_admin_action_log_created ON admin_action_log(created_at)",
]:
try:
conn.execute(idx_sql)
except sqlite3.OperationalError:
pass
conn.commit()
for col, defn in [
("password", "TEXT NOT NULL DEFAULT ''"),
("bio", "TEXT DEFAULT ''"),
("birthday", "TEXT DEFAULT ''"),
("created_at", "TEXT DEFAULT CURRENT_TIMESTAMP"),
("registration_status", "INTEGER DEFAULT 2"),
("profile_image_name", "TEXT DEFAULT ''"),
]:
try:
conn.execute(f"ALTER TABLE profiles ADD COLUMN {col} {defn}")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE avatars ADD COLUMN face_features TEXT DEFAULT ''")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE profiles ADD COLUMN tokens INTEGER DEFAULT 500")
conn.execute("UPDATE profiles SET tokens = 500")
conn.commit()
except sqlite3.OperationalError:
pass
for col, defn in [
("banned", "INTEGER DEFAULT 0"),
("ban_reason", "TEXT DEFAULT ''"),
("banned_until", "TEXT DEFAULT NULL"),
("banned_by", "TEXT DEFAULT ''"),
]:
try:
conn.execute(f"ALTER TABLE profiles ADD COLUMN {col} {defn}")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE profiles ADD COLUMN selected_cheer INTEGER DEFAULT 0")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE profiles ADD COLUMN created_via TEXT NOT NULL DEFAULT 'game'")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE rooms ADD COLUMN data_blob BLOB DEFAULT NULL")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE rooms ADD COLUMN special_gamemode INTEGER DEFAULT 0")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE rooms ADD COLUMN creator_name TEXT DEFAULT ''")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE rooms ADD COLUMN unity_asset_bundle_url TEXT DEFAULT ''")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE rooms ADD COLUMN unity_scene_name TEXT DEFAULT ''")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE rooms ADD COLUMN image_name TEXT DEFAULT ''")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE rooms ADD COLUMN disabled INTEGER DEFAULT 0")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("UPDATE rooms SET image_name = data_blob_name WHERE image_name = '' AND data_blob_name != ''")
conn.commit()
except sqlite3.OperationalError:
pass
try:
conn.execute("ALTER TABLE saved_outfits ADD COLUMN face_features TEXT DEFAULT ''")
conn.commit()
except sqlite3.OperationalError:
pass
conn.execute("""
CREATE TABLE IF NOT EXISTS room_gamemode_defaults (
activity_level_id TEXT PRIMARY KEY,
special_gamemode INTEGER NOT NULL DEFAULT 0
)
""")
conn.commit()
conn.execute("""
CREATE TABLE IF NOT EXISTS disabled_activities (
activity_level_id TEXT PRIMARY KEY
)
""")
conn.commit()
conn.execute("""
CREATE TABLE IF NOT EXISTS platform_accounts (
platform TEXT NOT NULL,
platform_id TEXT NOT NULL,
profile_id INTEGER NOT NULL,
linked_at TEXT DEFAULT CURRENT_TIMESTAMP,
UNIQUE(platform, platform_id, profile_id)
)
""")
conn.commit()
conn.execute("""
CREATE TABLE IF NOT EXISTS room_visits (
profile_id INTEGER NOT NULL,
activity_level_id TEXT NOT NULL,
room_id INTEGER,
visited_at TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(profile_id, activity_level_id)
)
""")
conn.commit()
conn.execute("""
CREATE TABLE IF NOT EXISTS subscriptions (
subscriber_id INTEGER NOT NULL,
target_id INTEGER NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(subscriber_id, target_id)
)
""")
conn.commit()
conn.execute("""
INSERT OR IGNORE INTO platform_accounts (platform, platform_id, profile_id)
SELECT platform, platform_id, id FROM profiles
WHERE platform != '' AND platform_id != '' AND platform_id != '0'
""")
conn.commit()
conn.execute("""
CREATE TABLE IF NOT EXISTS ip_logins (
ip_address TEXT NOT NULL,
profile_id INTEGER NOT NULL,
last_seen TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(ip_address, profile_id)
)
""")
conn.commit()
conn.execute("""
CREATE TABLE IF NOT EXISTS ban_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
action TEXT NOT NULL,
reason TEXT DEFAULT '',
duration_hours INTEGER DEFAULT 0,
banned_until TEXT DEFAULT NULL,
admin_name TEXT DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS player_challenge_progress (
profile_id INTEGER NOT NULL,
challenge_id INTEGER NOT NULL,
current_score INTEGER DEFAULT 0,
completed INTEGER DEFAULT 0,
reward_claimed INTEGER DEFAULT 0,
PRIMARY KEY (profile_id, challenge_id)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS daily_objective_completions (
profile_id INTEGER NOT NULL,
date_str TEXT NOT NULL,
slot_index INTEGER NOT NULL,
PRIMARY KEY (profile_id, date_str, slot_index)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS daily_login_rewards (
profile_id INTEGER NOT NULL,
date_str TEXT NOT NULL,
PRIMARY KEY (profile_id, date_str)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS weekly_schedule (
week_number INTEGER PRIMARY KEY,
challenge_id INTEGER NOT NULL,
custom_title TEXT DEFAULT '',
custom_target INTEGER DEFAULT 0,
custom_reward_xp INTEGER DEFAULT 0,
custom_reward_rarity INTEGER DEFAULT 0
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS store_featured_items (
slot INTEGER PRIMARY KEY,
gift_drop_id INTEGER NOT NULL,
added_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS saved_outfits (
profile_id INTEGER NOT NULL,
slot INTEGER NOT NULL,
preview_image_name TEXT DEFAULT '',
outfit_selections TEXT DEFAULT '',
hair_color TEXT DEFAULT '',
skin_color TEXT DEFAULT '',
bio TEXT DEFAULT '',
PRIMARY KEY (profile_id, slot)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS saved_images (
name TEXT PRIMARY KEY,
profile_id INTEGER NOT NULL,
image_data BLOB NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS profile_image_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
image_name TEXT NOT NULL,
image_data BLOB NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS admin_action_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
admin_name TEXT NOT NULL,
action TEXT NOT NULL,
details TEXT DEFAULT '',
ip TEXT DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS memory_uploads (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
game_session_id TEXT DEFAULT '',
room_name TEXT DEFAULT '',
filename TEXT NOT NULL,
size_bytes INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS daily_gifts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
date_str TEXT NOT NULL,
activity_guid TEXT NOT NULL DEFAULT ''
)
""")
conn.executescript("""
CREATE TABLE IF NOT EXISTS daily_cheers (
from_pid INTEGER NOT NULL,
to_pid INTEGER NOT NULL,
date_str TEXT NOT NULL,
PRIMARY KEY (from_pid, to_pid, date_str)
)
""")
conn.executescript("""
CREATE TABLE IF NOT EXISTS player_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
start_time TEXT NOT NULL,
end_time TEXT NOT NULL,
poster_image TEXT NOT NULL DEFAULT '',
creator_player_id INTEGER NOT NULL DEFAULT 1,
activity_level_id TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL DEFAULT ''
)
""")
conn.execute("""
CREATE INDEX IF NOT EXISTS idx_daily_gifts_pid_date
ON daily_gifts(profile_id, date_str)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS amplitude_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL DEFAULT 0,
event_type TEXT NOT NULL,
event_time INTEGER NOT NULL DEFAULT 0,
event_props TEXT DEFAULT '{}',
user_props TEXT DEFAULT '{}',
app_version TEXT DEFAULT '',
insert_id TEXT DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE INDEX IF NOT EXISTS idx_amplitude_event_type
ON amplitude_events(event_type)
""")
conn.execute("""
CREATE INDEX IF NOT EXISTS idx_amplitude_user_id
ON amplitude_events(user_id)
""")
conn.execute("""
CREATE INDEX IF NOT EXISTS idx_amplitude_created
ON amplitude_events(created_at)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS player_count_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
recorded_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
online INTEGER NOT NULL DEFAULT 0,
sessions INTEGER NOT NULL DEFAULT 0
)
""")
conn.execute("""
CREATE INDEX IF NOT EXISTS idx_pch_recorded
ON player_count_history(recorded_at)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS admin_todos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
description TEXT DEFAULT '',
priority INTEGER DEFAULT 1,
status TEXT DEFAULT 'open',
assigned_to TEXT DEFAULT '',
created_by TEXT DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
completed_at TEXT
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS admin_issues (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
description TEXT DEFAULT '',
category TEXT DEFAULT 'bug',
severity TEXT DEFAULT 'medium',
status TEXT DEFAULT 'open',
reported_by TEXT DEFAULT '',
assigned_to TEXT DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
resolved_at TEXT
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS equipment_favorites (
profile_id INTEGER NOT NULL,
modification_guid TEXT NOT NULL,
PRIMARY KEY (profile_id, modification_guid)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS leaderboard_scores (
profile_id INTEGER NOT NULL,
objective INTEGER NOT NULL,
all_time INTEGER DEFAULT 0,
periodic INTEGER DEFAULT 0,
period_start TEXT DEFAULT '',
PRIMARY KEY (profile_id, objective)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS dll_archive (
id INTEGER PRIMARY KEY AUTOINCREMENT,
filename TEXT NOT NULL UNIQUE,
sha512 TEXT NOT NULL DEFAULT '',
label TEXT NOT NULL DEFAULT '',
file_size INTEGER NOT NULL DEFAULT 0,
uploaded_at TEXT NOT NULL DEFAULT '',
uploaded_by TEXT NOT NULL DEFAULT ''
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS player_playtime (
profile_id INTEGER PRIMARY KEY,
total_seconds INTEGER NOT NULL DEFAULT 0,
session_count INTEGER NOT NULL DEFAULT 0,
last_seen TEXT NOT NULL DEFAULT ''
)
""")
import os as _os, hashlib as _hashlib, datetime as _datetime, json as _json
_dll_dir = _os.path.join(_os.path.dirname(_os.path.dirname(__file__)), "kittyrec_dlls")
_data_dir = _os.path.join(_os.path.dirname(_os.path.dirname(__file__)), "data")
try:
with open(_os.path.join(_data_dir, "KittyRecDlls.json")) as _f:
_dll_json = _json.load(_f)
_known_hashes = {v["file"]: v["hash"] for v in _dll_json.values() if isinstance(v, dict) and "file" in v}
except Exception:
_known_hashes = {}
if _os.path.isdir(_dll_dir):
for _fname in _os.listdir(_dll_dir):
if not _fname.endswith(".dll"):
continue
_fpath = _os.path.join(_dll_dir, _fname)
try:
if conn.execute("SELECT id FROM dll_archive WHERE filename=?", (_fname,)).fetchone():
continue
_fsize = _os.path.getsize(_fpath)
_sha = _known_hashes.get(_fname, "")
if not _sha:
with open(_fpath, "rb") as _fh:
_sha = _hashlib.sha512(_fh.read()).hexdigest()
_dt = _datetime.datetime.utcfromtimestamp(_os.path.getmtime(_fpath)).strftime("%Y-%m-%dT%H:%M:%SZ")
conn.execute(
"INSERT OR IGNORE INTO dll_archive (filename, sha512, label, file_size, uploaded_at, uploaded_by) VALUES (?,?,?,?,?,?)",
(_fname, _sha, "", _fsize, _dt, "system"),
)
except Exception:
pass
conn.commit()
conn.execute(
"UPDATE avatars SET skin_color = '' WHERE skin_color LIKE '%,%'"
)
conn.execute(
"UPDATE avatars SET hair_color = '' WHERE hair_color LIKE '%,%'"
)
conn.commit()
conn.execute("""
DELETE FROM messages
WHERE type = 4
AND EXISTS (
SELECT 1 FROM relationships r
WHERE r.profile_id = messages.to_player_id
AND r.target_id = messages.from_player_id
AND r.relationship_type = 3
)
""")
conn.commit()
_SYS_PID = 23
all_pids = [r["id"] for r in conn.execute("SELECT id FROM profiles WHERE id != ?", (_SYS_PID,)).fetchall()]
for pid in all_pids:
conn.execute(
"""INSERT INTO relationships (profile_id, target_id, relationship_type) VALUES (?, ?, 3)
ON CONFLICT(profile_id, target_id) DO UPDATE SET relationship_type = 3""",
(pid, _SYS_PID))
conn.execute(
"""INSERT INTO relationships (profile_id, target_id, relationship_type) VALUES (?, ?, 3)
ON CONFLICT(profile_id, target_id) DO UPDATE SET relationship_type = 3""",
(_SYS_PID, pid))
conn.commit()
bad_pids = conn.execute(
"SELECT id FROM profiles WHERE platform_id GLOB '*[^0-9]*'"
).fetchall()
for row in bad_pids:
pid = row["id"]
conn.execute(
"DELETE FROM platform_accounts WHERE platform_id GLOB '*[^0-9]*' AND profile_id = ?",
(pid,),
)
conn.execute(
"UPDATE profiles SET platform = '', platform_id = ? WHERE id = ?",
(str(pid), pid),
)
if bad_pids:
conn.commit()
print(f"[DB] Cleared {len(bad_pids)} non-numeric platform_id(s) — will be set on next login")
try:
conn.execute("ALTER TABLE profiles ADD COLUMN must_change_password INTEGER NOT NULL DEFAULT 0")
conn.commit()
except sqlite3.OperationalError:
pass
conn.execute("""
CREATE TABLE IF NOT EXISTS system_reply_queue (
profile_id INTEGER PRIMARY KEY,
handler_type TEXT NOT NULL,
context TEXT DEFAULT '{}',
expires_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS discord_links (
profile_id INTEGER PRIMARY KEY REFERENCES profiles(id),
discord_user_id TEXT NOT NULL UNIQUE,
discord_username TEXT NOT NULL DEFAULT '',
linked_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS discord_link_codes (
code TEXT PRIMARY KEY,
profile_id INTEGER NOT NULL,
discord_user_id TEXT NOT NULL,
discord_username TEXT NOT NULL DEFAULT '',
expires_at TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""")
conn.commit()
try:
cols = {r[1]: r[3] for r in conn.execute("PRAGMA table_info(saved_images)").fetchall()}
if cols.get("image_data") == 1:
conn.execute("ALTER TABLE saved_images RENAME TO _saved_images_old")
conn.execute("""CREATE TABLE saved_images (
name TEXT PRIMARY KEY,
profile_id INTEGER NOT NULL,
image_data BLOB,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
room_name TEXT DEFAULT '',
max_dim INTEGER DEFAULT 0
)""")
conn.execute("INSERT INTO saved_images SELECT name, profile_id, image_data, created_at, COALESCE(room_name,''), COALESCE(max_dim,0) FROM _saved_images_old")
conn.execute("DROP TABLE _saved_images_old")
conn.commit()
print("[DB] Migrated saved_images: image_data now nullable")
except Exception:
pass
try:
cols = {r[1]: r[3] for r in conn.execute("PRAGMA table_info(profile_image_history)").fetchall()}
if cols.get("image_data") == 1:
conn.execute("ALTER TABLE profile_image_history RENAME TO _pih_old")
conn.execute("""CREATE TABLE profile_image_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_id INTEGER NOT NULL,
image_name TEXT NOT NULL,
image_data BLOB,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)""")
conn.execute("INSERT INTO profile_image_history SELECT id, profile_id, image_name, image_data, created_at FROM _pih_old")
conn.execute("DROP TABLE _pih_old")
conn.commit()
print("[DB] Migrated profile_image_history: image_data now nullable")
except Exception:
pass
conn.execute("""
CREATE TABLE IF NOT EXISTS session_gift_counts (
profile_id INTEGER NOT NULL,
activity_guid TEXT NOT NULL,
count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (profile_id, activity_guid)
)
""")
conn.commit()
try:
conn.execute("ALTER TABLE gifts ADD COLUMN pushed INTEGER DEFAULT 0")
conn.commit()
except Exception:
pass
try:
conn.execute("ALTER TABLE profiles ADD COLUMN last_login TEXT DEFAULT NULL")
conn.commit()
except Exception:
pass
def _row_to_profile(self, row):
keys = row.keys()
return {
"Id": row["id"],
"Username": row["username"],
"DisplayName": row["display_name"],
"XP": row["xp"],
"Level": row["level"],
"Reputation": row["reputation"],
"Verified": bool(row["verified"]),
"Developer": bool(row["developer"]),
"Bio": row["bio"] if "bio" in keys else "",
"Birthday": row["birthday"] if "birthday" in keys else "",
"RegistrationStatus": 2,
"ProfileImageName": row["profile_image_name"] if "profile_image_name" in keys else "",
"Tokens": row["tokens"] if "tokens" in keys else 0,
"platform": row["platform"] if "platform" in keys else "0",
"platform_id": row["platform_id"] if "platform_id" in keys else "",
"SelectedCheer": row["selected_cheer"] if "selected_cheer" in keys else 0,
"Banned": bool(row["banned"]) if "banned" in keys else False,
"BanReason": row["ban_reason"] if "ban_reason" in keys else "",
"BannedUntil": row["banned_until"] if "banned_until" in keys else None,
"BannedBy": row["banned_by"] if "banned_by" in keys else "",
"CreatedAt": row["created_at"] if "created_at" in keys else "",
"LastLogin": row["last_login"] if "last_login" in keys else None,
}
def get_or_create_profile(self, platform, platform_id, name, password=""):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM profiles WHERE platform = ? AND platform_id = ?",
(platform, platform_id),
).fetchone()
if row:
return self._row_to_profile(row)
import random as _rnd
candidate = name
for _ in range(10):
existing = conn.execute(
"SELECT id FROM profiles WHERE LOWER(username) = LOWER(?)", (candidate,)
).fetchone()
if not existing:
break
candidate = f"{name}{_rnd.randint(10, 9999)}"
try:
cursor = conn.execute(
"INSERT INTO profiles (platform, platform_id, username, display_name, password, xp, level, registration_status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
(platform, platform_id, candidate, candidate, password, 0, 1, 2),
)
except Exception:
return None
conn.commit()
new_id = cursor.lastrowid
conn.execute(
"INSERT OR IGNORE INTO avatars (profile_id, outfit_selections, skin_color, hair_color, face_features) VALUES (?, ?, ?, ?, ?)",
(str(new_id), self._DEFAULT_OUTFIT, self._DEFAULT_SKIN_COLOR, self._DEFAULT_HAIR_COLOR, self._DEFAULT_FACE_FEATURES),
)
conn.commit()
self.seed_default_settings(new_id)
self.ensure_system_friend(new_id)
return self.get_profile(new_id)
def get_profile(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM profiles WHERE id = ?", (profile_id,)
).fetchone()
if row is None:
return None
return self._row_to_profile(row)
def get_profiles_batch(self, profile_ids):
if not profile_ids:
return {}
conn = self._get_conn()
placeholders = ",".join("?" * len(profile_ids))
rows = conn.execute(
f"SELECT * FROM profiles WHERE id IN ({placeholders})", list(profile_ids)
).fetchall()
return {row["id"]: self._row_to_profile(row) for row in rows}
def get_profile_by_platform(self, platform, platform_id):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM profiles WHERE platform = ? AND platform_id = ?",
(platform, platform_id),
).fetchone()
if row is None:
return None
return self._row_to_profile(row)
def get_profile_by_username(self, username):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM profiles WHERE username = ?", (username,)
).fetchone()
if row is None:
return None
return self._row_to_profile(row)
def search_profiles(self, query, limit=50):
conn = self._get_conn()
if not query or not query.strip():
rows = conn.execute(
"SELECT * FROM profiles ORDER BY id LIMIT ?", (limit,)
).fetchall()
else:
pattern = f"%{query.strip()}%"
rows = conn.execute(
"SELECT * FROM profiles WHERE username LIKE ? OR display_name LIKE ?"
" ORDER BY id LIMIT ?",
(pattern, pattern, limit),
).fetchall()
return [self._row_to_profile(r) for r in rows]
def discord_link_code_age_seconds(self, profile_id: int) -> float | None:
import datetime as _dt
conn = self._get_conn()
row = conn.execute(
"SELECT created_at FROM discord_link_codes WHERE profile_id = ? ORDER BY created_at DESC LIMIT 1",
(profile_id,),
).fetchone()
if row is None:
return None
try:
created = _dt.datetime.strptime(row["created_at"], "%Y-%m-%dT%H:%M:%SZ")
return (_dt.datetime.utcnow() - created).total_seconds()
except Exception:
return None
def create_discord_link_code(self, profile_id: int, discord_user_id: str, discord_username: str) -> str:
import secrets as _secrets
import datetime as _dt
conn = self._get_conn()
conn.execute(
"DELETE FROM discord_link_codes WHERE discord_user_id = ? OR profile_id = ?",
(discord_user_id, profile_id),
)
_CHARS = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789"
code = "".join(_secrets.choice(_CHARS) for _ in range(6))
expires = (_dt.datetime.utcnow() + _dt.timedelta(minutes=10)).strftime("%Y-%m-%dT%H:%M:%SZ")
conn.execute(
"INSERT INTO discord_link_codes (code, profile_id, discord_user_id, discord_username, expires_at) VALUES (?,?,?,?,?)",
(code, profile_id, discord_user_id, discord_username, expires),
)
conn.commit()
return code
def consume_discord_link_code(self, code: str, discord_user_id: str) -> int | None:
import datetime as _dt
conn = self._get_conn()
now = _dt.datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ")
row = conn.execute(
"SELECT profile_id FROM discord_link_codes WHERE code = ? AND discord_user_id = ? AND expires_at > ?",
(code, discord_user_id, now),
).fetchone()
if row is None:
return None
profile_id = row["profile_id"]
conn.execute("DELETE FROM discord_link_codes WHERE code = ?", (code,))
conn.commit()
return profile_id
def create_discord_link(self, profile_id: int, discord_user_id: str, discord_username: str) -> None:
import datetime as _dt
conn = self._get_conn()
now = _dt.datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ")
conn.execute(
"INSERT OR REPLACE INTO discord_links (profile_id, discord_user_id, discord_username, linked_at) VALUES (?,?,?,?)",
(profile_id, discord_user_id, discord_username, now),
)
conn.commit()
def get_discord_link_by_discord_id(self, discord_user_id: str) -> dict | None:
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM discord_links WHERE discord_user_id = ?", (discord_user_id,)
).fetchone()
if row is None:
return None
return dict(row)
def get_discord_link_by_profile_id(self, profile_id: int) -> dict | None:
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM discord_links WHERE profile_id = ?", (profile_id,)
).fetchone()
if row is None:
return None
return dict(row)
def check_password(self, username, password):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM profiles WHERE username = ?",
(username,),
).fetchone()
if row is None:
return None
stored = row["password"]
if not stored:
return None
if not crypto_check_password(stored, password):
return None
return self._row_to_profile(row)
_DEFAULT_SKIN_COLOR = "85343b16-d58a-4091-96d8-083a81fb03ae"
_DEFAULT_HAIR_COLOR = "fT0t-J1A-0i_tdoogFEz-g"
_DEFAULT_OUTFIT = "7b857a8c-92ad-4028-a2c2-b3c20cdab5f2,5c4a2b35-0e1c-44de-8c3a-96d4a6458b1b,9c03f381-7357-4d0f-8cda-8737d4c43d25,,1"
_DEFAULT_FACE_FEATURES = '{"ver":3,"eyeId":"AjGMoJhEcEehacRZjUMuDg","eyePos":{"x":0.0,"y":0.0},"eyeScl":0.0,"mouthId":"FrZBRanXEEK29yKJ4jiMjg","mouthPos":{"x":0.0,"y":0.0},"mouthScl":0.0,"beardColorId":"f9a50d3d-a6e0-42a4-a4e3-42dc89f84a2e"}'
def get_avatar(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM avatars WHERE profile_id = ?", (str(profile_id),)
).fetchone()
if row is None:
return {
"OutfitSelections": "",
"SkinColor": self._DEFAULT_SKIN_COLOR,
"HairColor": self._DEFAULT_HAIR_COLOR,
"FaceFeatures": "",
}
return {
"OutfitSelections": row["outfit_selections"],
"SkinColor": row["skin_color"] or self._DEFAULT_SKIN_COLOR,
"HairColor": row["hair_color"] or self._DEFAULT_HAIR_COLOR,
"FaceFeatures": row["face_features"] if "face_features" in row.keys() else "",
}
def set_avatar(self, profile_id, data):
conn = self._get_conn()
conn.execute(
"""INSERT INTO avatars (profile_id, outfit_selections, skin_color, hair_color, face_features)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(profile_id) DO UPDATE SET
outfit_selections = excluded.outfit_selections,
skin_color = CASE WHEN excluded.skin_color = '' THEN avatars.skin_color ELSE excluded.skin_color END,
hair_color = CASE WHEN excluded.hair_color = '' THEN avatars.hair_color ELSE excluded.hair_color END,
face_features = CASE WHEN excluded.face_features = '' THEN avatars.face_features ELSE excluded.face_features END""",
(
str(profile_id),
data.get("OutfitSelections", ""),
data.get("SkinColor", ""),
data.get("HairColor", ""),
data.get("FaceFeatures", ""),
),
)
conn.commit()
def get_settings(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT key, value FROM settings WHERE profile_id = ?",
(str(profile_id),),
).fetchall()
return [{"Key": r["key"], "Value": r["value"]} for r in rows]
def get_setting(self, profile_id, key):
conn = self._get_conn()
row = conn.execute(
"SELECT value FROM settings WHERE profile_id = ? AND key = ?",
(str(profile_id), key),
).fetchone()
return row["value"] if row else None
def set_setting(self, profile_id, key, value):
conn = self._get_conn()
conn.execute(
"""INSERT INTO settings (profile_id, key, value) VALUES (?, ?, ?)
ON CONFLICT(profile_id, key) DO UPDATE SET value = excluded.value""",
(str(profile_id), key, value),
)
conn.commit()
def remove_setting(self, profile_id, key):
conn = self._get_conn()
conn.execute(
"DELETE FROM settings WHERE profile_id = ? AND key = ?",
(str(profile_id), key),
)
conn.commit()
def get_profile_image(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT p.profile_image_name, pi.image_data FROM profiles p LEFT JOIN profile_images pi ON CAST(pi.profile_id AS INTEGER) = p.id WHERE p.id = ?",
(int(profile_id),),
).fetchone()
if row is None:
return None
img_name = row["profile_image_name"] or ""
if img_name:
path = os.path.join(self._dir_profile, f"{img_name}.jpg")
if os.path.exists(path):
with open(path, "rb") as fh:
return fh.read()
return row["image_data"]
def set_profile_image(self, profile_id, image_data, image_name=None):
if image_data is None:
conn = self._get_conn()
row = conn.execute(
"SELECT profile_image_name FROM profiles WHERE id = ?", (int(profile_id),)
).fetchone()
if row and row["profile_image_name"]:
path = os.path.join(self._dir_profile, f"{row['profile_image_name']}.jpg")
if os.path.exists(path):
try:
os.remove(path)
except Exception:
pass
conn.execute("DELETE FROM profile_images WHERE profile_id = ?", (str(profile_id),))
conn.commit()
elif image_name:
path = os.path.join(self._dir_profile, f"{image_name}.jpg")
try:
with open(path, "wb") as fh:
fh.write(image_data)
except Exception:
pass
def save_outfit(self, profile_id, slot, preview_image_name, outfit_selections, hair_color, skin_color, face_features=""):
conn = self._get_conn()
conn.execute(
"""INSERT INTO saved_outfits
(profile_id, slot, preview_image_name, outfit_selections, hair_color, skin_color, face_features)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(profile_id, slot) DO UPDATE SET
preview_image_name = excluded.preview_image_name,
outfit_selections = excluded.outfit_selections,
hair_color = excluded.hair_color,
skin_color = excluded.skin_color,
face_features = excluded.face_features""",
(profile_id, slot, preview_image_name, outfit_selections, hair_color, skin_color, face_features),
)
conn.commit()
def get_saved_outfits(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM saved_outfits WHERE profile_id = ? ORDER BY slot",
(profile_id,),
).fetchall()
return [
{
"Slot": row["slot"],
"PreviewImageName": row["preview_image_name"],
"OutfitSelections": row["outfit_selections"],
"HairColor": row["hair_color"],
"SkinColor": row["skin_color"],
"FaceFeatures": row["face_features"],
}
for row in rows
]
def save_named_image(self, name, profile_id, image_data, room_name="", max_dim=0):
conn = self._get_conn()
conn.execute(
"""INSERT INTO saved_images (name, profile_id, image_data, room_name, max_dim) VALUES (?, ?, ?, ?, ?)
ON CONFLICT(name) DO UPDATE SET profile_id=excluded.profile_id, image_data=excluded.image_data, room_name=excluded.room_name, max_dim=excluded.max_dim""",
(name, profile_id, image_data, room_name or "", max_dim),
)
conn.commit()
def get_recent_photos(self, limit=24):
conn = self._get_conn()
rows = conn.execute(
"""SELECT s.name, s.profile_id, s.room_name, s.created_at,
p.display_name, p.username
FROM saved_images s
LEFT JOIN profiles p ON p.id = s.profile_id
WHERE s.max_dim > 720 AND s.profile_id > 0
ORDER BY s.created_at DESC LIMIT ?""",
(limit,),
).fetchall()
return [dict(r) for r in rows]
def get_photos_paginated(self, profile_id=None, limit=24, offset=0):
conn = self._get_conn()
if profile_id is not None:
rows = conn.execute(
"""SELECT s.name, s.profile_id, s.room_name, s.created_at,
p.display_name, p.username
FROM saved_images s
LEFT JOIN profiles p ON p.id = s.profile_id
WHERE s.profile_id = ? AND s.max_dim > 720 AND s.profile_id > 0
ORDER BY s.created_at DESC LIMIT ? OFFSET ?""",
(profile_id, limit, offset),
).fetchall()
else:
rows = conn.execute(
"""SELECT s.name, s.profile_id, s.room_name, s.created_at,
p.display_name, p.username
FROM saved_images s
LEFT JOIN profiles p ON p.id = s.profile_id
WHERE s.max_dim > 720 AND s.profile_id > 0
ORDER BY s.created_at DESC LIMIT ? OFFSET ?""",
(limit, offset),
).fetchall()
return [dict(r) for r in rows]
def get_photo_count(self, profile_id=None):
conn = self._get_conn()
if profile_id is not None:
row = conn.execute(
"SELECT COUNT(*) as c FROM saved_images WHERE profile_id = ? AND max_dim > 720 AND profile_id > 0", (profile_id,)
).fetchone()
else:
row = conn.execute("SELECT COUNT(*) as c FROM saved_images WHERE max_dim > 720 AND profile_id > 0").fetchone()
return row["c"] if row else 0
def add_pfp_to_history(self, profile_id: int, image_name: str, image_data: bytes):
path = os.path.join(self._dir_history, f"{image_name}.jpg")
try:
with open(path, "wb") as fh:
fh.write(image_data)
except Exception:
pass
conn = self._get_conn()
conn.execute(
"INSERT INTO profile_image_history (profile_id, image_name, image_data) VALUES (?, ?, ?)",
(profile_id, image_name, None),
)
conn.commit()
def get_pfp_history(self, profile_id: int, limit: int = 12):
conn = self._get_conn()
rows = conn.execute(
"SELECT id, image_name, created_at FROM profile_image_history WHERE profile_id = ? ORDER BY created_at DESC LIMIT ?",
(profile_id, limit),
).fetchall()
return [dict(r) for r in rows]
def get_latest_jtis(self) -> dict:
conn = self._get_conn()
rows = conn.execute(
"""SELECT profile_id, token FROM cached_logins
WHERE rowid IN (SELECT MAX(rowid) FROM cached_logins GROUP BY profile_id)"""
).fetchall()
return {row["profile_id"]: row["token"] for row in rows}
def get_pfp_history_image(self, history_id: int, profile_id: int):
conn = self._get_conn()
row = conn.execute(
"SELECT image_name, image_data FROM profile_image_history WHERE id = ? AND profile_id = ?",
(history_id, profile_id),
).fetchone()
if row is None:
return None
img_name = row["image_name"] or ""
if img_name:
path = os.path.join(self._dir_history, f"{img_name}.jpg")
if os.path.exists(path):
with open(path, "rb") as fh:
return fh.read()
return row["image_data"] if row["image_data"] else None
def get_named_image(self, name):
for d in (self._dir_hd, self._dir_lq):
path = os.path.join(d, name)
if os.path.exists(path):
with open(path, "rb") as fh:
return fh.read()
conn = self._get_conn()
row = conn.execute(
"SELECT image_data FROM saved_images WHERE name = ?", (name,)
).fetchone()
return row["image_data"] if row else None
def get_saved_image_names(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT name FROM saved_images WHERE profile_id = ? ORDER BY created_at DESC",
(profile_id,),
).fetchall()
return [row["name"] for row in rows]
def create_account(self, username, password, display_name=None, platform="Steam", platform_id=None, created_via="game"):
import uuid as _uuid
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM profiles WHERE LOWER(username) = LOWER(?)",
(username,),
).fetchone()
if row is not None:
return None
tmp_pid = platform_id if platform_id else str(_uuid.uuid4())
try:
cursor = conn.execute(
"INSERT INTO profiles (platform, platform_id, username, display_name, password, xp, level, registration_status, created_via) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
(platform if platform_id else "", tmp_pid, username, display_name or username, generate_password_hash(password) if password else "", 0, 1, 2, created_via),
)
conn.commit()
except Exception:
return None
new_id = cursor.lastrowid
if not platform_id:
conn.execute(
"UPDATE profiles SET platform = '', platform_id = ? WHERE id = ?",
(str(new_id), new_id),
)
conn.commit()
conn.execute(
"INSERT OR IGNORE INTO avatars (profile_id, outfit_selections, skin_color, hair_color, face_features) VALUES (?, ?, ?, ?, ?)",
(str(new_id), self._DEFAULT_OUTFIT, self._DEFAULT_SKIN_COLOR, self._DEFAULT_HAIR_COLOR, self._DEFAULT_FACE_FEATURES),
)
conn.commit()
self.seed_default_settings(new_id)
self.ensure_system_friend(new_id)
return self.get_profile(new_id)
def update_profile(self, profile_id, field_dict):
conn = self._get_conn()
allowed = {"display_name", "username", "bio", "birthday", "reputation", "profile_image_name", "selected_cheer"}
updates = {k: v for k, v in field_dict.items() if k in allowed}
if not updates:
return
set_clause = ", ".join(f"{k} = ?" for k in updates)
values = list(updates.values()) + [profile_id]
conn.execute(f"UPDATE profiles SET {set_clause} WHERE id = ?", values)
conn.commit()
def update_xp(self, profile_id, xp, level):
conn = self._get_conn()
conn.execute(
"UPDATE profiles SET xp = ?, level = ? WHERE id = ?",
(xp, level, profile_id),
)
conn.commit()
def award_xp(self, profile_id, delta_xp):
"""Atomically add delta_xp to profile, recalculate level, return (new_xp, new_level).
If a level-up occurs, generates a level-up gift and pushes it via WS."""
from core.constants import xp_to_level
conn = self._get_conn()
old_row = conn.execute("SELECT xp, level FROM profiles WHERE id = ?", (profile_id,)).fetchone()
old_level = old_row["level"] if old_row else 1
conn.execute("UPDATE profiles SET xp = xp + ? WHERE id = ?", (delta_xp, profile_id))
conn.commit()
row = conn.execute("SELECT xp FROM profiles WHERE id = ?", (profile_id,)).fetchone()
new_xp = row["xp"] if row else 0
new_level = xp_to_level(new_xp)
conn.execute("UPDATE profiles SET level = ? WHERE id = ?", (new_level, profile_id))
conn.commit()
if new_level > old_level:
self._grant_level_up_gift(profile_id, new_level)
return new_xp, new_level
def _grant_level_up_gift(self, profile_id, new_level):
from core.ws_helpers import push_gift
gift = self.add_gift(
profile_id,
xp=0,
gift_rarity=[0, 10, 20, 30][min(new_level // 10, 3)],
currency_type=2,
currency=100 + (new_level * 10),
message=f"Level {new_level}!",
gift_context=100,
level=new_level,
)
push_gift(profile_id, gift)
print(f"[LevelUp] Player {profile_id} reached level {new_level}!")
if new_level in {10, 25, 50, 100, 200, 500}:
try:
from core.discord import send_public, avatar_url
p = self.get_profile(profile_id)
uname = p["Username"] if p else str(profile_id)
img = avatar_url((p or {}).get("ProfileImageName", ""))
send_public(
title="Level Milestone!",
color=0xF1C40F,
fields=[{"name": "Player", "value": uname, "inline": True},
{"name": "Level", "value": str(new_level), "inline": True}],
description=f"**{uname}** just reached **Level {new_level}**!",
thumbnail_url=img,
throttle_key=f"level_{profile_id}_{new_level}",
throttle_secs=0,
)
except Exception:
pass
def get_tokens(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT tokens FROM profiles WHERE id = ?", (profile_id,)
).fetchone()
return row["tokens"] if row else 0
def update_tokens(self, profile_id, delta):
conn = self._get_conn()
conn.execute(
"UPDATE profiles SET tokens = MAX(0, tokens + ?) WHERE id = ?",
(delta, profile_id),
)
conn.commit()
row = conn.execute(
"SELECT tokens FROM profiles WHERE id = ?", (profile_id,)
).fetchone()
return row["tokens"] if row else 0
def deduct_tokens_if_enough(self, profile_id, amount):
conn = self._get_conn()
cursor = conn.execute(
"UPDATE profiles SET tokens = tokens - ? WHERE id = ? AND tokens >= ?",
(amount, profile_id, amount),
)
conn.commit()
if cursor.rowcount == 0:
return None
row = conn.execute(
"SELECT tokens FROM profiles WHERE id = ?", (profile_id,)
).fetchone()
return row["tokens"] if row else 0
def claim_daily_login_reward(self, profile_id: int, tokens: int = 500) -> bool:
from datetime import datetime, timezone
date_str = datetime.now(timezone.utc).strftime("%Y-%m-%d")
conn = self._get_conn()
try:
conn.execute(
"INSERT INTO daily_login_rewards (profile_id, date_str) VALUES (?, ?)",
(profile_id, date_str),
)
conn.commit()
except Exception:
return False
self.update_tokens(profile_id, tokens)
return True
def update_profile_platform(self, profile_id, platform, platform_id):
conn = self._get_conn()
existing = conn.execute(
"SELECT id FROM profiles WHERE platform = ? AND platform_id = ? AND id != ?",
(platform, platform_id, profile_id),
).fetchone()
if existing is None:
conn.execute(
"UPDATE profiles SET platform = ?, platform_id = ? WHERE id = ?",
(platform, platform_id, profile_id),
)
conn.commit()
def has_password(self, profile_id):
conn = self._get_conn()
row = conn.execute("SELECT password FROM profiles WHERE id = ?", (profile_id,)).fetchone()
if not row or not row["password"]:
return False
return crypto_has_password(row["password"])
def has_temp_password(self, profile_id):
conn = self._get_conn()
row = conn.execute("SELECT password FROM profiles WHERE id = ?", (profile_id,)).fetchone()
if not row or not row["password"]:
return False
return not row["password"].startswith(("pbkdf2:", "scrypt:", "argon2:"))
def set_temp_password(self, profile_id, plain_text):
if self.has_password(profile_id):
return
conn = self._get_conn()
conn.execute("UPDATE profiles SET password = ? WHERE id = ? AND (password IS NULL OR password = '')", (plain_text, profile_id))
conn.commit()
def set_developer(self, profile_id, is_dev):
conn = self._get_conn()
conn.execute(
"UPDATE profiles SET developer = ? WHERE id = ?",
(1 if is_dev else 0, profile_id),
)
conn.commit()
def add_cached_login(self, profile_id, token):
conn = self._get_conn()
conn.execute("DELETE FROM cached_logins WHERE profile_id = ?", (profile_id,))
conn.execute(
"INSERT INTO cached_logins (profile_id, token) VALUES (?, ?)",
(profile_id, token),
)
conn.commit()
def get_cached_logins(self):
conn = self._get_conn()
rows = conn.execute("""
SELECT cl.profile_id, p.username, p.display_name
FROM cached_logins cl
JOIN profiles p ON p.id = cl.profile_id
""").fetchall()
return [
{
"PlayerId": r["profile_id"],
"Username": r["username"],
"DisplayName": r["display_name"],
}
for r in rows
]
def remove_cached_login(self, profile_id):
conn = self._get_conn()
conn.execute("DELETE FROM cached_logins WHERE profile_id = ?", (profile_id,))
conn.commit()
def link_platform_to_profile(self, platform, platform_id, profile_id):
if not platform or not platform_id or platform_id == "0":
return
conn = self._get_conn()
conn.execute(
"INSERT OR IGNORE INTO platform_accounts (platform, platform_id, profile_id) VALUES (?, ?, ?)",
(str(platform), str(platform_id), int(profile_id)),
)
conn.commit()
def get_linked_profile_ids(self, profile_id):
conn = self._get_conn()
platform_ids = conn.execute(
"SELECT platform, platform_id FROM platform_accounts WHERE profile_id = ?",
(int(profile_id),)
).fetchall()
if not platform_ids:
return []
linked = set()
for row in platform_ids:
rows = conn.execute(
"SELECT profile_id FROM platform_accounts WHERE platform = ? AND platform_id = ? AND profile_id != ?",
(row["platform"], row["platform_id"], int(profile_id))
).fetchall()
for r in rows:
linked.add(r["profile_id"])
also_main = conn.execute(
"""SELECT id FROM profiles WHERE id != ? AND (
SELECT COUNT(*) FROM platform_accounts
WHERE profile_id = profiles.id AND platform_id IN (
SELECT platform_id FROM platform_accounts WHERE profile_id = ?
)
) > 0""",
(int(profile_id), int(profile_id))
).fetchall()
for r in also_main:
linked.add(r["id"])
return list(linked)
def record_ip_login(self, profile_id: int, ip: str) -> None:
if not ip or ip in ("127.0.0.1", "::1"):
return
conn = self._get_conn()
conn.execute(
"INSERT INTO ip_logins (ip_address, profile_id, last_seen) VALUES (?, ?, CURRENT_TIMESTAMP) "
"ON CONFLICT(ip_address, profile_id) DO UPDATE SET last_seen=CURRENT_TIMESTAMP",
(ip, int(profile_id)),
)
conn.commit()
def get_profile_ids_by_ip(self, ip: str) -> list:
conn = self._get_conn()
rows = conn.execute(
"SELECT DISTINCT profile_id FROM ip_logins WHERE ip_address = ?", (ip,)
).fetchall()
return [r["profile_id"] for r in rows]
def get_ips_for_profile(self, profile_id: int) -> list:
conn = self._get_conn()
rows = conn.execute(
"SELECT DISTINCT ip_address FROM ip_logins WHERE profile_id = ?", (int(profile_id),)
).fetchall()
return [r["ip_address"] for r in rows]
def get_all_associated_ids(self, profile_id: int) -> set:
linked = set(self.get_linked_profile_ids(profile_id))
for ip in self.get_ips_for_profile(profile_id):
for pid in self.get_profile_ids_by_ip(ip):
if pid != profile_id:
linked.add(pid)
return linked
def get_associated_accounts_with_evidence(self, profile_id: int) -> dict:
conn = self._get_conn()
result = {}
ips = [r["ip_address"] for r in conn.execute(
"SELECT DISTINCT ip_address FROM ip_logins WHERE profile_id=?", (int(profile_id),)
).fetchall()]
for ip in ips:
rows = conn.execute(
"SELECT DISTINCT profile_id FROM ip_logins WHERE ip_address=? AND profile_id!=?",
(ip, int(profile_id))
).fetchall()
for r in rows:
pid = r["profile_id"]
entry = result.setdefault(pid, {"shared_ips": [], "shared_platform_ids": []})
entry["shared_ips"].append(ip)
platform_ids = conn.execute(
"SELECT platform, platform_id FROM platform_accounts WHERE profile_id=?", (int(profile_id),)
).fetchall()
for row in platform_ids:
matches = conn.execute(
"SELECT DISTINCT profile_id FROM platform_accounts WHERE platform=? AND platform_id=? AND profile_id!=?",
(row["platform"], row["platform_id"], int(profile_id))
).fetchall()
for r in matches:
pid = r["profile_id"]
entry = result.setdefault(pid, {"shared_ips": [], "shared_platform_ids": []})
entry["shared_platform_ids"].append(row["platform_id"])
return result
def is_ip_banned(self, ip: str) -> bool:
if not ip or ip in ("127.0.0.1", "::1"):
return False
conn = self._get_conn()
from datetime import datetime
now = datetime.utcnow().isoformat()
row = conn.execute(
"SELECT 1 FROM profiles p JOIN ip_logins i ON p.id=i.profile_id "
"WHERE i.ip_address=? AND p.banned=1 "
"AND (p.banned_until IS NULL OR p.banned_until > ?) LIMIT 1",
(ip, now),
).fetchone()
return row is not None
def get_all_profiles_for_ips(self, ips: list) -> list:
if not ips:
return []
conn = self._get_conn()
placeholders = ",".join("?" * len(ips))
rows = conn.execute(
f"SELECT DISTINCT profile_id FROM ip_logins WHERE ip_address IN ({placeholders})",
list(ips)
).fetchall()
return [r["profile_id"] for r in rows]
def get_challenge_progress(self, profile_id: int, challenge_id: int) -> dict:
conn = self._get_conn()
row = conn.execute(
"SELECT current_score, completed, reward_claimed FROM player_challenge_progress WHERE profile_id=? AND challenge_id=?",
(profile_id, challenge_id),
).fetchone()
if row:
return {"current_score": row["current_score"], "completed": bool(row["completed"]), "reward_claimed": bool(row["reward_claimed"])}
return {"current_score": 0, "completed": False, "reward_claimed": False}
def increment_challenge_progress(self, profile_id: int, challenge_id: int, delta: int = 1) -> int:
conn = self._get_conn()
conn.execute(
"""INSERT INTO player_challenge_progress (profile_id, challenge_id, current_score)
VALUES (?, ?, ?)
ON CONFLICT(profile_id, challenge_id) DO UPDATE SET current_score = current_score + ?""",
(profile_id, challenge_id, delta, delta),
)
conn.commit()
row = conn.execute(
"SELECT current_score FROM player_challenge_progress WHERE profile_id=? AND challenge_id=?",
(profile_id, challenge_id),
).fetchone()
return row["current_score"] if row else delta
def set_challenge_progress(self, profile_id: int, challenge_id: int, score: int) -> None:
conn = self._get_conn()
conn.execute(
"""INSERT INTO player_challenge_progress (profile_id, challenge_id, current_score)
VALUES (?, ?, ?)
ON CONFLICT(profile_id, challenge_id) DO UPDATE SET current_score=MAX(current_score, ?)""",
(profile_id, challenge_id, score, score),
)
conn.commit()
def mark_challenge_completed(self, profile_id: int, challenge_id: int) -> None:
conn = self._get_conn()
conn.execute(
"""INSERT INTO player_challenge_progress (profile_id, challenge_id, completed, reward_claimed)
VALUES (?, ?, 1, 1)
ON CONFLICT(profile_id, challenge_id) DO UPDATE SET completed=1, reward_claimed=1""",
(profile_id, challenge_id),
)
conn.commit()
def try_mark_challenge_completed(self, profile_id: int, challenge_id: int) -> bool:
conn = self._get_conn()
cursor = conn.execute(
"""UPDATE player_challenge_progress SET completed=1, reward_claimed=1
WHERE profile_id=? AND challenge_id=? AND completed=0""",
(profile_id, challenge_id),
)
conn.commit()
return cursor.rowcount > 0
def increment_leaderboard(self, profile_id: int, objective_type: int) -> None:
conn = self._get_conn()
conn.execute(
"""INSERT INTO leaderboard_scores (profile_id, objective, all_time, periodic)
VALUES (?, ?, 1, 1)
ON CONFLICT(profile_id, objective) DO UPDATE SET
all_time = all_time + 1,
periodic = periodic + 1""",
(profile_id, objective_type),
)
conn.commit()
def get_leaderboard(self, objective_type: int, limit: int,
friend_ids: list | None = None, sort_periodic: bool = False) -> list:
conn = self._get_conn()
col = "periodic" if sort_periodic else "all_time"
if friend_ids is not None:
if not friend_ids:
return []
placeholders = ",".join("?" * len(friend_ids))
rows = conn.execute(
f"""SELECT ls.profile_id, ls.all_time, ls.periodic
FROM leaderboard_scores ls
WHERE ls.objective = ?
AND ls.profile_id IN ({placeholders})
ORDER BY ls.{col} DESC
LIMIT ?""",
(objective_type, *friend_ids, limit),
).fetchall()
else:
rows = conn.execute(
f"""SELECT ls.profile_id, ls.all_time, ls.periodic
FROM leaderboard_scores ls
WHERE ls.objective = ?
ORDER BY ls.{col} DESC
LIMIT ?""",
(objective_type, limit),
).fetchall()
return [
{"PlayerId": row["profile_id"], "Count": row[col], "Order": i + 1}
for i, row in enumerate(rows)
]
def dll_archive_list(self) -> list:
conn = self._get_conn()
rows = conn.execute(
"SELECT id, filename, sha512, label, file_size, uploaded_at, uploaded_by FROM dll_archive ORDER BY uploaded_at DESC"
).fetchall()
return [dict(r) for r in rows]
def dll_archive_add(self, filename: str, sha512: str, label: str, file_size: int, uploaded_by: str) -> int:
from datetime import datetime, timezone
conn = self._get_conn()
uploaded_at = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
cur = conn.execute(
"INSERT OR IGNORE INTO dll_archive (filename, sha512, label, file_size, uploaded_at, uploaded_by) VALUES (?,?,?,?,?,?)",
(filename, sha512, label, file_size, uploaded_at, uploaded_by),
)
conn.commit()
return cur.lastrowid
def dll_archive_update(self, filename: str, label: str) -> None:
conn = self._get_conn()
conn.execute("UPDATE dll_archive SET label=? WHERE filename=?", (label, filename))
conn.commit()
def get_weekly_schedule(self, week_number: int) -> dict | None:
from core.constants import WEEKLY_CHALLENGES
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM weekly_schedule WHERE week_number=?", (week_number,)
).fetchone()
if not row:
return None
base = next((c for c in WEEKLY_CHALLENGES if c["id"] == row["challenge_id"]), None)
if not base:
return None
ch = base.copy()
if row["custom_title"]:
ch["title"] = row["custom_title"]
if row["custom_target"]:
ch["target"] = row["custom_target"]
if row["custom_reward_xp"]:
ch["reward_xp"] = row["custom_reward_xp"]
if row["custom_reward_rarity"]:
ch["reward_rarity"] = row["custom_reward_rarity"]
return ch
def set_weekly_schedule(self, week_number: int, challenge_id: int,
custom_title: str = "", custom_target: int = 0,
custom_reward_xp: int = 0, custom_reward_rarity: int = 0) -> None:
conn = self._get_conn()
conn.execute(
"""INSERT INTO weekly_schedule
(week_number, challenge_id, custom_title, custom_target, custom_reward_xp, custom_reward_rarity)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(week_number) DO UPDATE SET
challenge_id=excluded.challenge_id,
custom_title=excluded.custom_title,
custom_target=excluded.custom_target,
custom_reward_xp=excluded.custom_reward_xp,
custom_reward_rarity=excluded.custom_reward_rarity""",
(week_number, challenge_id, custom_title, custom_target, custom_reward_xp, custom_reward_rarity),
)
conn.commit()
def delete_weekly_schedule(self, week_number: int) -> None:
conn = self._get_conn()
conn.execute("DELETE FROM weekly_schedule WHERE week_number=?", (week_number,))
conn.commit()
def get_all_weekly_schedules(self) -> dict:
conn = self._get_conn()
rows = conn.execute("SELECT * FROM weekly_schedule").fetchall()
return {row["week_number"]: dict(row) for row in rows}
def get_store_featured(self) -> list:
conn = self._get_conn()
rows = conn.execute("SELECT slot, gift_drop_id FROM store_featured_items ORDER BY slot").fetchall()
return [{"slot": r["slot"], "gift_drop_id": r["gift_drop_id"]} for r in rows]
def set_store_featured(self, slot: int, gift_drop_id: int) -> None:
conn = self._get_conn()
conn.execute(
"""INSERT INTO store_featured_items (slot, gift_drop_id)
VALUES (?, ?)
ON CONFLICT(slot) DO UPDATE SET gift_drop_id=excluded.gift_drop_id,
added_at=CURRENT_TIMESTAMP""",
(slot, gift_drop_id),
)
conn.commit()
def clear_store_featured(self, slot: int | None = None) -> None:
conn = self._get_conn()
if slot is None:
conn.execute("DELETE FROM store_featured_items")
else:
conn.execute("DELETE FROM store_featured_items WHERE slot=?", (slot,))
conn.commit()
def get_daily_completions(self, profile_id: int, date_str: str) -> set:
conn = self._get_conn()
rows = conn.execute(
"SELECT slot_index FROM daily_objective_completions WHERE profile_id=? AND date_str=?",
(profile_id, date_str),
).fetchall()
return {row["slot_index"] for row in rows}
def mark_daily_complete(self, profile_id: int, date_str: str, slot_index: int) -> None:
self.try_mark_daily_complete(profile_id, date_str, slot_index)
def try_mark_daily_complete(self, profile_id: int, date_str: str, slot_index: int) -> bool:
conn = self._get_conn()
cur = conn.execute(
"INSERT OR IGNORE INTO daily_objective_completions (profile_id, date_str, slot_index) VALUES (?, ?, ?)",
(profile_id, date_str, slot_index),
)
conn.commit()
return cur.rowcount > 0
def get_session_gift_count(self, profile_id: int, activity_guid: str) -> int:
conn = self._get_conn()
row = conn.execute(
"SELECT count FROM session_gift_counts WHERE profile_id=? AND activity_guid=?",
(profile_id, activity_guid),
).fetchone()
return row["count"] if row else 0
def increment_session_gift_count(self, profile_id: int, activity_guid: str) -> None:
conn = self._get_conn()
conn.execute(
"""INSERT INTO session_gift_counts (profile_id, activity_guid, count) VALUES (?, ?, 1)
ON CONFLICT(profile_id, activity_guid) DO UPDATE SET count = count + 1""",
(profile_id, activity_guid),
)
conn.commit()
def reset_session_gift_counts(self, profile_id: int) -> None:
conn = self._get_conn()
conn.execute("DELETE FROM session_gift_counts WHERE profile_id=?", (profile_id,))
conn.commit()
def get_daily_gift_count(self, profile_id: int, date_str: str) -> int:
conn = self._get_conn()
row = conn.execute(
"SELECT COUNT(*) FROM daily_gifts WHERE profile_id=? AND date_str=?",
(profile_id, date_str),
).fetchone()
return row[0] if row else 0
def get_daily_gift_activity_count(self, profile_id: int, date_str: str, activity_guid: str) -> int:
conn = self._get_conn()
row = conn.execute(
"SELECT COUNT(*) FROM daily_gifts WHERE profile_id=? AND date_str=? AND activity_guid=?",
(profile_id, date_str, activity_guid),
).fetchone()
return row[0] if row else 0
def record_daily_gift(self, profile_id: int, date_str: str, activity_guid: str) -> None:
self.try_record_daily_gift(profile_id, date_str, activity_guid, max_count=20)
def try_record_daily_gift(self, profile_id: int, date_str: str, activity_guid: str, max_count: int = 15) -> bool:
conn = self._get_conn()
conn.execute("BEGIN IMMEDIATE")
try:
row = conn.execute(
"SELECT COUNT(*) FROM daily_gifts WHERE profile_id=? AND date_str=?",
(profile_id, date_str),
).fetchone()
if row and row[0] >= max_count:
conn.execute("ROLLBACK")
return False
conn.execute(
"INSERT INTO daily_gifts (profile_id, date_str, activity_guid) VALUES (?,?,?)",
(profile_id, date_str, activity_guid),
)
conn.execute("COMMIT")
return True
except Exception:
conn.execute("ROLLBACK")
raise
def get_associated_ban(self, profile_id: int) -> dict | None:
for linked_id in self.get_all_associated_ids(profile_id):
ban = self.get_ban_info(linked_id)
if ban:
reason = (ban.get("ban_reason") or "").lower()
if "association" in reason or "ip ban" in reason:
continue
linked_profile = self.get_profile(linked_id)
linked_name = linked_profile.get("Username", str(linked_id)) if linked_profile else str(linked_id)
return {**ban, "_source_name": linked_name, "_source_id": linked_id}
return None
def get_profiles_by_platform(self, platform, platform_id):
conn = self._get_conn()
rows = conn.execute(
"""SELECT p.* FROM profiles p
JOIN platform_accounts pa ON pa.profile_id = p.id
WHERE pa.platform = ? AND pa.platform_id = ?
ORDER BY pa.linked_at DESC""",
(str(platform), str(platform_id)),
).fetchall()
return [self._row_to_profile(r) for r in rows]
def subscribe(self, subscriber_id, target_id):
conn = self._get_conn()
conn.execute(
"INSERT OR IGNORE INTO subscriptions (subscriber_id, target_id) VALUES (?, ?)",
(subscriber_id, target_id),
)
conn.commit()
def unsubscribe(self, subscriber_id, target_id):
conn = self._get_conn()
conn.execute(
"DELETE FROM subscriptions WHERE subscriber_id = ? AND target_id = ?",
(subscriber_id, target_id),
)
conn.commit()
def get_subscriptions(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"""SELECT p.id, p.username, p.display_name FROM profiles p
JOIN subscriptions s ON s.target_id = p.id
WHERE s.subscriber_id = ?""",
(profile_id,),
).fetchall()
return [{"PlayerId": r["id"], "Username": r["username"], "DisplayName": r["display_name"]} for r in rows]
def get_subscriber_count(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT COUNT(*) as cnt FROM subscriptions WHERE target_id = ?",
(profile_id,),
).fetchone()
return row["cnt"] if row else 0
def is_subscribed(self, subscriber_id, target_id):
conn = self._get_conn()
row = conn.execute(
"SELECT 1 FROM subscriptions WHERE subscriber_id = ? AND target_id = ?",
(subscriber_id, target_id),
).fetchone()
return row is not None
_DEFAULT_SETTINGS = [
("QualitySettings", "3"),
("VoiceChat", "1"),
("MicEnabled", "1"),
]
def seed_default_settings(self, profile_id):
conn = self._get_conn()
for key, value in self._DEFAULT_SETTINGS:
conn.execute(
"INSERT OR IGNORE INTO settings (profile_id, key, value) VALUES (?, ?, ?)",
(str(profile_id), key, value),
)
conn.commit()
def get_cached_login_tokens(self):
conn = self._get_conn()
rows = conn.execute("SELECT token, profile_id FROM cached_logins").fetchall()
return [(r["token"], r["profile_id"]) for r in rows]
def seed_account(self, username, password, display_name=None, developer=False):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM profiles WHERE (platform = 'Steam' AND platform_id = ?) OR LOWER(username) = LOWER(?)",
(username, username),
).fetchone()
if row is None:
cursor = conn.execute(
"INSERT OR IGNORE INTO profiles (platform, platform_id, username, display_name, password, developer) VALUES (?, ?, ?, ?, ?, ?)",
("Steam", username, username, display_name or username, generate_password_hash(password) if password else "", 1 if developer else 0),
)
conn.commit()
new_id = cursor.lastrowid
conn.execute(
"INSERT OR IGNORE INTO avatars (profile_id) VALUES (?)",
(str(new_id),),
)
conn.commit()
self.seed_default_settings(new_id)
return new_id
else:
if developer:
conn.execute("UPDATE profiles SET developer = 1 WHERE id = ?", (row["id"],))
conn.commit()
return row["id"]
def seed_preset_rooms(self, activities: dict) -> None:
from datetime import datetime, timezone
conn = self._get_conn()
existing = {r[0] for r in conn.execute("SELECT activity_level_id FROM rooms").fetchall()}
now = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
for guid, (name, _key, is_sandbox) in activities.items():
if guid not in existing:
conn.execute(
"""INSERT INTO rooms
(creator_id, name, description, activity_level_id,
accessibility, is_sandbox, instanced, max_players,
screen_mode_support, created_at, modified_at)
VALUES (23, ?, '', ?, 0, ?, 1, 20, 0, ?, ?)""",
(name, guid, 1 if is_sandbox else 0, now, now),
)
conn.commit()
def _rel_row_to_dict(self, row):
return {
"PlayerID": row["target_id"],
"RelationshipType": row["relationship_type"],
"Muted": row["muted"],
"Ignored": row["ignored"],
"Favorited": row["favorited"],
}
def get_relationships(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM relationships WHERE profile_id = ? AND (relationship_type != 0 OR muted != 0 OR ignored != 0 OR favorited != 0)",
(profile_id,)
).fetchall()
return [self._rel_row_to_dict(r) for r in rows]
def get_relationship(self, profile_id, target_id):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM relationships WHERE profile_id = ? AND target_id = ?",
(profile_id, target_id),
).fetchone()
if row is None:
return {"PlayerID": target_id, "RelationshipType": 0, "Muted": 0, "Ignored": 0, "Favorited": 0}
return self._rel_row_to_dict(row)
_KITTYREC_SYSTEM_PID = 23
def ensure_system_friend(self, profile_id):
if profile_id == self._KITTYREC_SYSTEM_PID:
return
self.set_relationship_type(profile_id, self._KITTYREC_SYSTEM_PID, 3)
self.set_relationship_type(self._KITTYREC_SYSTEM_PID, profile_id, 3)
def set_relationship_type(self, profile_id, target_id, rel_type):
conn = self._get_conn()
conn.execute(
"""INSERT INTO relationships (profile_id, target_id, relationship_type)
VALUES (?, ?, ?)
ON CONFLICT(profile_id, target_id) DO UPDATE SET relationship_type = excluded.relationship_type""",
(profile_id, target_id, rel_type),
)
conn.commit()
_REL_FIELDS = {"muted", "ignored", "favorited"}
def set_rel_field(self, profile_id, target_id, field, value):
if field not in self._REL_FIELDS:
raise ValueError(f"Invalid relationship field: {field!r}")
conn = self._get_conn()
conn.execute(
f"""INSERT INTO relationships (profile_id, target_id, {field})
VALUES (?, ?, ?)
ON CONFLICT(profile_id, target_id) DO UPDATE SET {field} = excluded.{field}""",
(profile_id, target_id, value),
)
conn.commit()
def remove_relationship(self, profile_id, target_id):
conn = self._get_conn()
conn.execute(
"DELETE FROM relationships WHERE profile_id = ? AND target_id = ?",
(profile_id, target_id),
)
conn.commit()
def ban_player(self, profile_id, reason="", duration_hours=0, admin_name=""):
"""Ban a player. duration_hours=0 means permanent."""
conn = self._get_conn()
if duration_hours > 0:
from datetime import datetime, timedelta
until = (datetime.utcnow() + timedelta(hours=duration_hours)).isoformat()
else:
until = None
conn.execute(
"UPDATE profiles SET banned=1, ban_reason=?, banned_until=?, banned_by=? WHERE id=?",
(reason, until, admin_name, profile_id),
)
conn.execute(
"INSERT INTO ban_log (profile_id, action, reason, duration_hours, banned_until, admin_name) VALUES (?,?,?,?,?,?)",
(profile_id, "ban", reason, duration_hours, until, admin_name),
)
conn.commit()
return until
def delete_profile(self, profile_id: int) -> None:
conn = self._get_conn()
for table, col in [
("gifts", "profile_id"),
("ban_log", "profile_id"),
("relationships", "profile_id"),
("relationships", "target_id"),
("messages", "from_player_id"),
("messages", "to_player_id"),
("settings", "profile_id"),
("platform_accounts", "profile_id"),
("ip_logins", "profile_id"),
("cached_logins", "profile_id"),
]:
try:
conn.execute(f"DELETE FROM {table} WHERE {col}=?", (profile_id,))
except Exception:
pass
conn.execute("DELETE FROM profiles WHERE id=?", (profile_id,))
conn.commit()
def unban_player(self, profile_id, admin_name=""):
from core.ban_tracker import get_ban_group, find_group_for_member, clear_ban_group
conn = self._get_conn()
to_unban = {int(profile_id)}
group = get_ban_group(profile_id) or find_group_for_member(profile_id)
if group:
for aid in group.get("associated_ids", []):
to_unban.add(int(aid))
primary_id = int(group.get("primary_id", profile_id))
to_unban.add(primary_id)
clear_ban_group(primary_id)
for linked_id in self.get_all_associated_ids(profile_id):
row = conn.execute(
"SELECT ban_reason FROM profiles WHERE id=? AND banned=1", (linked_id,),
).fetchone()
if row:
reason = (row["ban_reason"] or "").lower()
if "association" in reason or "ip ban" in reason:
to_unban.add(int(linked_id))
profile = self.get_profile(profile_id)
primary_name = profile.get("Username", "") if profile else ""
if primary_name:
for pattern in (f"%association with {primary_name}%", f"%IP ban ({primary_name})%"):
rows = conn.execute(
"SELECT id FROM profiles WHERE banned=1 AND ban_reason LIKE ?", (pattern,),
).fetchall()
for row in rows:
to_unban.add(int(row["id"]))
for pid in to_unban:
conn.execute(
"UPDATE profiles SET banned=0, ban_reason='', banned_until=NULL WHERE id=?", (pid,),
)
conn.execute(
"INSERT INTO ban_log (profile_id, action, reason, admin_name) VALUES (?,?,?,?)",
(pid, "unban", f"cascade unban from player {profile_id}", admin_name),
)
conn.commit()
def get_ban_info(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT banned, ban_reason, banned_until, banned_by FROM profiles WHERE id=?",
(profile_id,)
).fetchone()
if row is None or not row["banned"]:
return None
return dict(row)
@staticmethod
def format_ban_message(ban_info: dict) -> str:
from datetime import datetime
reason = ban_info.get("ban_reason") or "No reason given"
banned_until = ban_info.get("banned_until")
if not banned_until:
return f"Permanently banned: {reason}"
try:
exp = datetime.fromisoformat(banned_until)
delta = exp - datetime.utcnow()
total_secs = max(0, int(delta.total_seconds()))
days = total_secs // 86400
hours = (total_secs % 86400) // 3600
minutes = (total_secs % 3600) // 60
if days > 0:
duration = f"{days}d {hours}h"
elif hours > 0:
duration = f"{hours}h {minutes}m"
else:
duration = f"{minutes}m"
return f"Banned for {duration} for: {reason}"
except Exception:
return f"Banned: {reason}"
def is_banned(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT banned, banned_until FROM profiles WHERE id=?", (profile_id,)
).fetchone()
if row is None or not row["banned"]:
return False
if row["banned_until"]:
from datetime import datetime
try:
if datetime.fromisoformat(row["banned_until"]) < datetime.utcnow():
conn.execute("UPDATE profiles SET banned=0 WHERE id=?", (profile_id,))
conn.commit()
return False
except Exception:
pass
return True
def get_ban_log(self, profile_id=None, limit=100):
conn = self._get_conn()
if profile_id:
rows = conn.execute(
"SELECT bl.*, p.username FROM ban_log bl JOIN profiles p ON p.id=bl.profile_id "
"WHERE bl.profile_id=? ORDER BY bl.id DESC LIMIT ?",
(profile_id, limit),
).fetchall()
else:
rows = conn.execute(
"SELECT bl.*, p.username FROM ban_log bl JOIN profiles p ON p.id=bl.profile_id "
"ORDER BY bl.id DESC LIMIT ?",
(limit,),
).fetchall()
return [dict(r) for r in rows]
def log_admin_action(self, admin_name, action, details="", ip=""):
conn = self._get_conn()
conn.execute(
"INSERT INTO admin_action_log (admin_name, action, details, ip) VALUES (?, ?, ?, ?)",
(admin_name, action, details, ip),
)
conn.commit()
def get_admin_action_log(self, limit=30):
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM admin_action_log ORDER BY id DESC LIMIT ?", (limit,)
).fetchall()
return [dict(r) for r in rows]
def get_player_notes(self, profile_id):
conn = self._get_conn()
return [dict(r) for r in conn.execute(
"SELECT * FROM player_notes WHERE profile_id=? ORDER BY created_at DESC", (profile_id,)
).fetchall()]
def add_player_note(self, profile_id, note, created_by):
conn = self._get_conn()
conn.execute(
"INSERT INTO player_notes (profile_id, note, created_by) VALUES (?, ?, ?)",
(profile_id, note.strip(), created_by)
)
conn.commit()
return conn.execute("SELECT last_insert_rowid() AS id").fetchone()["id"]
def delete_player_note(self, note_id, profile_id):
conn = self._get_conn()
conn.execute("DELETE FROM player_notes WHERE id=? AND profile_id=?", (note_id, profile_id))
conn.commit()
def save_memory_upload(self, profile_id, game_session_id, room_name, filename, size_bytes):
conn = self._get_conn()
conn.execute(
"INSERT INTO memory_uploads (profile_id, game_session_id, room_name, filename, size_bytes) VALUES (?, ?, ?, ?, ?)",
(profile_id, game_session_id or "", room_name or "", filename, size_bytes),
)
conn.commit()
def get_memory_uploads(self, profile_id=None, limit=100):
conn = self._get_conn()
if profile_id:
rows = conn.execute(
"SELECT * FROM memory_uploads WHERE profile_id=? ORDER BY id DESC LIMIT ?",
(profile_id, limit),
).fetchall()
else:
rows = conn.execute(
"SELECT * FROM memory_uploads ORDER BY id DESC LIMIT ?", (limit,)
).fetchall()
return [dict(r) for r in rows]
def get_all_banned(self):
conn = self._get_conn()
rows = conn.execute(
"SELECT id, username, display_name, ban_reason, banned_until, banned_by, created_at "
"FROM profiles WHERE banned=1 ORDER BY id"
).fetchall()
return [dict(r) for r in rows]
def update_last_login(self, profile_id: int) -> None:
conn = self._get_conn()
conn.execute(
"UPDATE profiles SET last_login = CURRENT_TIMESTAMP WHERE id = ?",
(profile_id,),
)
conn.commit()
def get_all_profiles_summary(self):
conn = self._get_conn()
rows = conn.execute(
"SELECT id, username, display_name, level, xp, tokens, developer, created_at, last_login, banned, ban_reason, banned_until FROM profiles ORDER BY id"
).fetchall()
return [dict(r) for r in rows]
def _msg_row_to_dict(self, row):
sent = row["sent_at"]
if "T" not in sent:
sent = sent.replace(" ", "T") + "Z"
return {
"Id": row["id"],
"FromPlayerId": row["from_player_id"],
"SentTime": sent,
"Type": row["type"],
"Data": row["data"],
"RoomId": row["room_id"],
"PlayerEventId": row["player_event_id"],
}
def send_message(self, from_id, to_id, msg_type, data="", room_id=None, event_id=None):
conn = self._get_conn()
cursor = conn.execute(
"INSERT INTO messages (from_player_id, to_player_id, type, data, room_id, player_event_id) VALUES (?, ?, ?, ?, ?, ?)",
(from_id, to_id, msg_type, data, room_id, event_id),
)
conn.commit()
return cursor.lastrowid
def get_messages(self, to_player_id, since_id=0):
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM messages WHERE to_player_id = ? AND id > ? ORDER BY id",
(to_player_id, since_id),
).fetchall()
return [self._msg_row_to_dict(r) for r in rows]
def delete_message(self, message_id, to_player_id):
conn = self._get_conn()
conn.execute(
"DELETE FROM messages WHERE id = ? AND to_player_id = ?",
(message_id, to_player_id),
)
conn.commit()
def delete_messages_of_type(self, to_player_id, from_player_id, msg_type):
conn = self._get_conn()
conn.execute(
"DELETE FROM messages WHERE to_player_id = ? AND from_player_id = ? AND type = ?",
(to_player_id, from_player_id, msg_type),
)
conn.commit()
def _chat_msg_row_to_dict(self, row):
import json as _json
sent = row["sent_at"]
if "T" not in sent:
sent = sent.replace(" ", "T") + "Z"
contents = row["contents"]
if not contents.startswith("{"):
contents = _json.dumps({"Type": 0, "Version": 1, "Data": contents})
return {
"ChatMessageId": row["id"],
"ChatThreadId": row["thread_id"],
"SenderPlayerId": row["sender_id"],
"TimeSent": sent,
"Contents": contents,
}
def find_chat(self, player_ids):
conn = self._get_conn()
sorted_ids = sorted(int(x) for x in player_ids)
n = len(sorted_ids)
if n < 2:
return None
placeholders = ",".join("?" * n)
candidates = conn.execute(
f"""SELECT ctp.thread_id FROM chat_thread_players ctp
WHERE ctp.profile_id IN ({placeholders})
GROUP BY ctp.thread_id HAVING COUNT(*) = ?""",
sorted_ids + [n],
).fetchall()
for row in candidates:
tid = row["thread_id"]
total = conn.execute(
"SELECT COUNT(*) AS c FROM chat_thread_players WHERE thread_id = ?", (tid,)
).fetchone()["c"]
if total == n:
return tid
return None
def get_or_create_chat(self, player_ids):
conn = self._get_conn()
sorted_ids = sorted(int(x) for x in player_ids)
n = len(sorted_ids)
if n >= 2:
placeholders = ",".join("?" * n)
candidates = conn.execute(
f"""SELECT ctp.thread_id FROM chat_thread_players ctp
WHERE ctp.profile_id IN ({placeholders})
GROUP BY ctp.thread_id HAVING COUNT(*) = ?""",
sorted_ids + [n],
).fetchall()
for row in candidates:
tid = row["thread_id"]
total = conn.execute(
"SELECT COUNT(*) AS c FROM chat_thread_players WHERE thread_id = ?", (tid,)
).fetchone()["c"]
if total == n:
return tid
cursor = conn.execute("INSERT INTO chat_threads (created_at) VALUES (CURRENT_TIMESTAMP)")
conn.commit()
thread_id = cursor.lastrowid
for pid in sorted_ids:
conn.execute(
"INSERT INTO chat_thread_players (thread_id, profile_id) VALUES (?, ?)",
(thread_id, pid),
)
conn.commit()
return thread_id
def send_chat_message(self, thread_id, sender_id, text):
contents = text
conn = self._get_conn()
cursor = conn.execute(
"INSERT INTO chat_messages (thread_id, sender_id, contents) VALUES (?, ?, ?)",
(thread_id, sender_id, contents),
)
conn.commit()
return cursor.lastrowid
def get_chat_message(self, msg_id):
conn = self._get_conn()
row = conn.execute("SELECT * FROM chat_messages WHERE id = ?", (msg_id,)).fetchone()
return self._chat_msg_row_to_dict(row) if row else None
def get_chat_messages(self, thread_id, count=50, before_id=None):
conn = self._get_conn()
if before_id:
rows = conn.execute(
"SELECT * FROM chat_messages WHERE thread_id = ? AND id < ? ORDER BY id DESC LIMIT ?",
(thread_id, before_id, count),
).fetchall()
else:
rows = conn.execute(
"SELECT * FROM chat_messages WHERE thread_id = ? ORDER BY id DESC LIMIT ?",
(thread_id, count),
).fetchall()
return [self._chat_msg_row_to_dict(r) for r in rows]
def get_chat_latest_message(self, thread_id):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM chat_messages WHERE thread_id = ? ORDER BY id DESC LIMIT 1",
(thread_id,),
).fetchone()
return self._chat_msg_row_to_dict(row) if row else None
def get_chat_thread_players(self, thread_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT profile_id FROM chat_thread_players WHERE thread_id = ?", (thread_id,)
).fetchall()
return [r["profile_id"] for r in rows]
def get_player_chats(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"""SELECT ctp.thread_id, ctp.last_read_message_id
FROM chat_thread_players ctp
WHERE ctp.profile_id = ?
ORDER BY ctp.thread_id DESC""",
(profile_id,),
).fetchall()
result = []
for row in rows:
tid = row["thread_id"]
latest = self.get_chat_latest_message(tid)
if latest is None:
continue
players = self.get_chat_thread_players(tid)
result.append({
"ChatThreadId": tid,
"LastReadMessageId": row["last_read_message_id"],
"LatestMessage": latest,
"PlayerIds": players,
})
return result
def get_chat_thread_detail(self, thread_id, requesting_pid, count=50, before_id=None):
conn = self._get_conn()
row = conn.execute(
"SELECT last_read_message_id FROM chat_thread_players WHERE thread_id = ? AND profile_id = ?",
(thread_id, requesting_pid),
).fetchone()
if row is None:
return None
result = {
"ChatThreadId": thread_id,
"LastReadMessageId": row["last_read_message_id"],
"LatestMessage": self.get_chat_latest_message(thread_id),
"PlayerIds": self.get_chat_thread_players(thread_id),
}
return result
def mark_chat_read(self, thread_id, profile_id, message_id):
conn = self._get_conn()
conn.execute(
"UPDATE chat_thread_players SET last_read_message_id = ? WHERE thread_id = ? AND profile_id = ?",
(message_id, thread_id, profile_id),
)
conn.commit()
def add_to_chat_thread(self, thread_id, profile_id):
conn = self._get_conn()
conn.execute(
"INSERT OR IGNORE INTO chat_thread_players (thread_id, profile_id) VALUES (?, ?)",
(thread_id, profile_id),
)
conn.commit()
def leave_chat_thread(self, thread_id, profile_id):
conn = self._get_conn()
conn.execute(
"DELETE FROM chat_thread_players WHERE thread_id = ? AND profile_id = ?",
(thread_id, profile_id),
)
conn.commit()
def _gift_row_to_dict(self, row):
return {
"Id": row["id"],
"GiftDropId": row["id"],
"AvatarItemDesc": row["avatar_item_desc"],
"ConsumableItemDesc": row["consumable_item_desc"],
"EquipmentPrefabName": row["equipment_prefab_name"],
"EquipmentModificationGuid": row["equipment_modification_guid"],
"StorefrontType": 0,
"CurrencyType": row["currency_type"],
"Currency": row["currency"],
"Xp": row["xp"],
"Level": row["level"],
"GiftRarity": row["gift_rarity"],
"Message": row["message"],
"GiftContext": row["gift_context"],
"Consumed": bool(row["consumed"]),
"CreatedAt": row["created_at"] or "",
}
def add_gift(self, profile_id, xp=0, gift_rarity=0, avatar_item_desc="",
equipment_prefab_name="", equipment_modification_guid="",
consumable_item_desc="", currency_type=0, currency=0,
message="", gift_context=0, level=0):
conn = self._get_conn()
cursor = conn.execute(
"""INSERT INTO gifts
(profile_id, avatar_item_desc, consumable_item_desc,
equipment_prefab_name, equipment_modification_guid, currency_type,
currency, xp, level, gift_rarity, message, gift_context)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(profile_id, avatar_item_desc, consumable_item_desc, equipment_prefab_name,
equipment_modification_guid, currency_type, currency, xp, level,
gift_rarity, message, gift_context),
)
conn.commit()
row = conn.execute("SELECT * FROM gifts WHERE id = ?", (cursor.lastrowid,)).fetchone()
return self._gift_row_to_dict(row)
def get_pending_gifts(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM gifts WHERE profile_id = ? AND consumed = 0 AND pushed = 0 ORDER BY id",
(profile_id,),
).fetchall()
return [self._gift_row_to_dict(r) for r in rows]
def mark_gift_pushed(self, gift_id: int) -> None:
conn = self._get_conn()
conn.execute("UPDATE gifts SET pushed = 1 WHERE id = ?", (gift_id,))
conn.commit()
def is_guest(self, profile_id: int) -> bool:
conn = self._get_conn()
row = conn.execute(
"SELECT password FROM profiles WHERE id=?", (profile_id,)
).fetchone()
if row is None:
return True
return not row["password"]
def consume_gift(self, gift_id, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM gifts WHERE id = ? AND profile_id = ? AND consumed = 0",
(gift_id, profile_id),
).fetchone()
if row is None:
return None
gift = self._gift_row_to_dict(row)
conn.execute("UPDATE gifts SET consumed = 1 WHERE id = ?", (gift_id,))
if gift.get("AvatarItemDesc"):
self.unlock_avatar_item(profile_id, gift["AvatarItemDesc"])
if gift.get("ConsumableItemDesc"):
desc = gift["ConsumableItemDesc"]
count = 60 if desc.startswith("Consumable_") else 1
self.add_consumable(profile_id, desc, count)
if not self.is_guest(profile_id):
old_level = None
if gift["Xp"] > 0:
from core.constants import xp_to_level
old_row = conn.execute("SELECT level FROM profiles WHERE id = ?", (profile_id,)).fetchone()
old_level = old_row["level"] if old_row else 1
conn.execute("UPDATE profiles SET xp = xp + ? WHERE id = ?", (gift["Xp"], profile_id))
xp_row = conn.execute("SELECT xp FROM profiles WHERE id = ?", (profile_id,)).fetchone()
if xp_row:
new_xp = xp_row["xp"]
new_level = xp_to_level(new_xp)
conn.execute("UPDATE profiles SET level = ? WHERE id = ?", (new_level, profile_id))
if gift["CurrencyType"] == 2 and gift["Currency"] > 0:
conn.execute(
"UPDATE profiles SET tokens = MAX(0, tokens + ?) WHERE id = ?",
(gift["Currency"], profile_id),
)
conn.commit()
if not self.is_guest(profile_id) and gift["Xp"] > 0 and old_level is not None:
from core.constants import xp_to_level
current_row = conn.execute("SELECT xp, level FROM profiles WHERE id = ?", (profile_id,)).fetchone()
if current_row and current_row["level"] > old_level:
self._grant_level_up_gift(profile_id, current_row["level"])
return gift
def add_consumable(self, profile_id, consumable_item_desc, count=1):
if not consumable_item_desc:
return
conn = self._get_conn()
conn.execute(
"INSERT INTO consumable_inventory (profile_id, consumable_item_desc, count) "
"VALUES (?, ?, ?) "
"ON CONFLICT(profile_id, consumable_item_desc) DO UPDATE SET count = count + ?",
(profile_id, consumable_item_desc, count, count),
)
conn.commit()
def get_consumable_inventory(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM consumable_inventory WHERE profile_id = ? AND count > 0",
(profile_id,),
).fetchall()
return [dict(r) for r in rows]
def use_consumable(self, profile_id, consumable_id, delta=1):
conn = self._get_conn()
cursor = conn.execute(
"UPDATE consumable_inventory SET count = count - ? "
"WHERE id = ? AND profile_id = ? AND count >= ?",
(delta, consumable_id, profile_id, delta),
)
if cursor.rowcount > 0:
conn.execute(
"DELETE FROM consumable_inventory WHERE id = ? AND profile_id = ? AND count <= 0",
(consumable_id, profile_id),
)
conn.commit()
return cursor.rowcount > 0
def get_consumable_by_id(self, profile_id, consumable_id):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM consumable_inventory WHERE id = ? AND profile_id = ?",
(consumable_id, profile_id),
).fetchone()
return dict(row) if row else None
def get_consumable_by_desc(self, profile_id, consumable_item_desc):
conn = self._get_conn()
row = conn.execute(
"SELECT * FROM consumable_inventory WHERE profile_id = ? AND consumable_item_desc = ?",
(profile_id, consumable_item_desc),
).fetchone()
return dict(row) if row else None
def set_consumable_active(self, profile_id, consumable_id, is_active):
conn = self._get_conn()
conn.execute(
"UPDATE consumable_inventory SET is_active = ? WHERE id = ? AND profile_id = ?",
(1 if is_active else 0, consumable_id, profile_id),
)
conn.commit()
def update_gift_item(self, gift_id, avatar_item_desc):
conn = self._get_conn()
conn.execute(
"UPDATE gifts SET avatar_item_desc = ? WHERE id = ?",
(avatar_item_desc, gift_id),
)
conn.commit()
def unlock_avatar_item(self, profile_id, avatar_item_desc):
if not avatar_item_desc:
return
conn = self._get_conn()
conn.execute(
"INSERT OR IGNORE INTO unlocked_items (profile_id, avatar_item_desc) VALUES (?, ?)",
(profile_id, avatar_item_desc),
)
conn.commit()
def unlock_avatar_items(self, profile_id, descs):
if not descs:
return
conn = self._get_conn()
for desc in descs:
if desc:
conn.execute(
"INSERT OR IGNORE INTO unlocked_items (profile_id, avatar_item_desc) VALUES (?, ?)",
(profile_id, desc),
)
conn.commit()
def get_unlocked_items(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT avatar_item_desc FROM unlocked_items WHERE profile_id = ?",
(profile_id,),
).fetchall()
result = set()
for r in rows:
desc = r["avatar_item_desc"]
result.add(desc)
result.add(desc.split(",")[0])
return result
def has_unlocked_items(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT 1 FROM unlocked_items WHERE profile_id = ? LIMIT 1",
(profile_id,),
).fetchone()
return row is not None
def get_unlocked_equipment(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT DISTINCT equipment_prefab_name, equipment_modification_guid FROM gifts "
"WHERE profile_id = ? AND consumed = 1 AND equipment_modification_guid != ''",
(profile_id,),
).fetchall()
favorites = {r["modification_guid"] for r in conn.execute(
"SELECT modification_guid FROM equipment_favorites WHERE profile_id = ?",
(profile_id,),
).fetchall()}
return [
{
"PrefabName": r["equipment_prefab_name"],
"ModificationGuid": r["equipment_modification_guid"],
"UnlockedLevel": 1,
"Favorited": r["equipment_modification_guid"] in favorites,
}
for r in rows
]
def set_equipment_favorites(self, profile_id, items):
conn = self._get_conn()
for item in items:
guid = item.get("ModificationGuid", "")
if not guid:
continue
if item.get("Favorited"):
conn.execute(
"INSERT OR IGNORE INTO equipment_favorites (profile_id, modification_guid) VALUES (?, ?)",
(profile_id, guid),
)
else:
conn.execute(
"DELETE FROM equipment_favorites WHERE profile_id = ? AND modification_guid = ?",
(profile_id, guid),
)
conn.commit()
def migrate_unlocks_from_gifts(self, profile_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT avatar_item_desc FROM gifts WHERE profile_id = ? AND consumed = 1 AND avatar_item_desc != ''",
(profile_id,),
).fetchall()
for row in rows:
conn.execute(
"INSERT OR IGNORE INTO unlocked_items (profile_id, avatar_item_desc) VALUES (?, ?)",
(profile_id, row["avatar_item_desc"]),
)
conn.commit()
def _room_row_to_dict(self, row):
import datetime
now = datetime.datetime.now(datetime.timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
room_id = row["id"]
conn = self._get_conn()
perms = conn.execute("SELECT player_id, is_host, is_owner FROM room_permissions WHERE room_id = ?",
(room_id,)).fetchall()
coowners = [p["player_id"] for p in perms if p["is_owner"]]
hosts = [p["player_id"] for p in perms if p["is_host"]]
cheer_row = conn.execute("SELECT COUNT(*) as c FROM room_cheers WHERE room_id = ?", (room_id,)).fetchone()
cheer_count = cheer_row["c"] if cheer_row else 0
keys = row.keys()
special_gm = row["special_gamemode"] if "special_gamemode" in keys else 0
asset_url = row["unity_asset_bundle_url"] if "unity_asset_bundle_url" in keys else ""
scene_name = row["unity_scene_name"] if "unity_scene_name" in keys else ""
disabled = bool(row["disabled"]) if "disabled" in keys else False
return {
"RoomId": room_id,
"Name": row["name"],
"Description": row["description"],
"CreatorPlayerId": row["creator_id"],
"ImageName": row["image_name"] if "image_name" in keys else "",
"ActivityLevelId": row["activity_level_id"],
"State": row["state"],
"Accessibility": row["accessibility"],
"IsSandbox": bool(row["is_sandbox"]),
"Instanced": bool(row["instanced"]),
"ScreenModeSupport": row["screen_mode_support"],
"DataBlobName": (row["data_blob_name"] or "") if row["data_blob"] is not None else "",
"MaxPlayers": row["max_players"],
"SpecialGamemode": special_gm or 0,
"UnityAssetBundleUrl": asset_url or "",
"UnitySceneName": scene_name or "",
"AccessibilityLocked": False,
"CreatedAt": row["created_at"] if row["created_at"] else now,
"ModifiedAt": row["modified_at"] if row["modified_at"] else now,
"StateModifiedAt": row["modified_at"] if row["modified_at"] else now,
"DataModifiedAt": None,
"LastVisitedAt": row["modified_at"] if row["modified_at"] else now,
"VisitorCount": 0,
"VisitCount": 0,
"CheerCount": cheer_count,
"ReportCount": 0,
"CoOwners": coowners,
"Hosts": hosts,
"Disabled": disabled,
}
def create_room(self, creator_id, name, description="", activity_level_id="",
accessibility=0, is_sandbox=False, instanced=True,
max_players=20, screen_mode_support=0):
import datetime
now = datetime.datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ")
conn = self._get_conn()
cur = conn.execute(
"""INSERT INTO rooms
(creator_id, name, description, activity_level_id, accessibility,
is_sandbox, instanced, max_players, screen_mode_support, created_at, modified_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(creator_id, name, description, activity_level_id, accessibility,
1 if is_sandbox else 0, 1 if instanced else 0,
max_players, screen_mode_support, now, now),
)
conn.commit()
return self.get_room(cur.lastrowid)
def get_room(self, room_id):
conn = self._get_conn()
row = conn.execute("SELECT * FROM rooms WHERE id = ?", (room_id,)).fetchone()
return self._room_row_to_dict(row) if row else None
def get_rooms_by_creator(self, creator_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM rooms WHERE creator_id = ? ORDER BY modified_at DESC", (creator_id,)
).fetchall()
return [self._room_row_to_dict(r) for r in rows]
def record_room_visit(self, profile_id, activity_level_id, room_id=None):
conn = self._get_conn()
conn.execute(
"INSERT OR REPLACE INTO room_visits (profile_id, activity_level_id, room_id, visited_at) VALUES (?, ?, ?, CURRENT_TIMESTAMP)",
(profile_id, activity_level_id, room_id),
)
conn.commit()
def get_recent_rooms(self, profile_id, limit=10):
conn = self._get_conn()
rows = conn.execute(
"SELECT rv.activity_level_id, rv.room_id, rv.visited_at FROM room_visits rv "
"WHERE rv.profile_id = ? ORDER BY rv.visited_at DESC LIMIT ?",
(profile_id, limit),
).fetchall()
results = []
for rv in rows:
room_id = rv["room_id"]
if room_id:
room = self.get_room(room_id)
if room:
results.append(room)
continue
rooms = conn.execute(
"SELECT * FROM rooms WHERE activity_level_id = ? LIMIT 1",
(rv["activity_level_id"],),
).fetchone()
if rooms:
results.append(self._room_row_to_dict(rooms))
return results
def get_all_rooms(self):
conn = self._get_conn()
rows = conn.execute("SELECT * FROM rooms ORDER BY modified_at DESC").fetchall()
return [self._room_row_to_dict(r) for r in rows]
def save_room_data(self, room_id, data):
import datetime
now = datetime.datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ")
conn = self._get_conn()
if isinstance(data, str):
data = data.encode("utf-8")
conn.execute(
"UPDATE rooms SET data_blob = ?, modified_at = ? WHERE id = ?",
(data, now, room_id),
)
conn.commit()
def get_room_data(self, room_id):
conn = self._get_conn()
row = conn.execute("SELECT data_blob FROM rooms WHERE id = ?", (room_id,)).fetchone()
return row["data_blob"] if row else None
def update_room_field(self, room_id, field, value):
import datetime
allowed = {"name", "description", "accessibility", "is_sandbox", "instanced",
"max_players", "screen_mode_support", "data_blob_name", "image_name", "state",
"activity_level_id", "special_gamemode", "creator_id",
"unity_asset_bundle_url", "unity_scene_name"}
if field not in allowed:
return self.get_room(room_id)
now = datetime.datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ")
conn = self._get_conn()
conn.execute(
f"UPDATE rooms SET {field} = ?, modified_at = ? WHERE id = ?",
(value, now, room_id),
)
conn.commit()
return self.get_room(room_id)
def get_room_by_name(self, name):
conn = self._get_conn()
row = conn.execute("SELECT * FROM rooms WHERE LOWER(name) = LOWER(?)", (name,)).fetchone()
return self._room_row_to_dict(row) if row else None
def get_room_by_activity(self, activity_level_id):
conn = self._get_conn()
row = conn.execute("SELECT * FROM rooms WHERE activity_level_id = ? LIMIT 1", (activity_level_id,)).fetchone()
return self._room_row_to_dict(row) if row else None
def count_rooms_by_creator(self, creator_id):
conn = self._get_conn()
row = conn.execute("SELECT COUNT(*) as c FROM rooms WHERE creator_id = ?", (creator_id,)).fetchone()
return row["c"] if row else 0
def delete_room(self, room_id):
conn = self._get_conn()
conn.execute("DELETE FROM room_permissions WHERE room_id = ?", (room_id,))
conn.execute("DELETE FROM room_bookmarks WHERE room_id = ?", (room_id,))
conn.execute("DELETE FROM room_cheers WHERE room_id = ?", (room_id,))
conn.execute("DELETE FROM rooms WHERE id = ?", (room_id,))
conn.commit()
def update_room(self, room_id, **fields):
import datetime
allowed = {"name", "description", "accessibility", "is_sandbox", "instanced",
"max_players", "screen_mode_support", "data_blob_name", "image_name", "state",
"activity_level_id", "special_gamemode", "creator_id",
"unity_asset_bundle_url", "unity_scene_name"}
updates = {k: v for k, v in fields.items() if k in allowed}
if not updates:
return self.get_room(room_id)
now = datetime.datetime.now(datetime.timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
updates["modified_at"] = now
set_clause = ", ".join(f"{k} = ?" for k in updates)
conn = self._get_conn()
conn.execute(f"UPDATE rooms SET {set_clause} WHERE id = ?",
list(updates.values()) + [room_id])
conn.commit()
return self.get_room(room_id)
def get_gamemode_default(self, activity_level_id):
conn = self._get_conn()
row = conn.execute("SELECT special_gamemode FROM room_gamemode_defaults WHERE activity_level_id = ?",
(activity_level_id,)).fetchone()
return row["special_gamemode"] if row else 0
def set_gamemode_default(self, activity_level_id, special_gamemode):
conn = self._get_conn()
if special_gamemode == 0:
conn.execute("DELETE FROM room_gamemode_defaults WHERE activity_level_id = ?",
(activity_level_id,))
else:
conn.execute("INSERT OR REPLACE INTO room_gamemode_defaults (activity_level_id, special_gamemode) VALUES (?, ?)",
(activity_level_id, special_gamemode))
conn.commit()
def get_all_gamemode_defaults(self):
conn = self._get_conn()
rows = conn.execute("SELECT activity_level_id, special_gamemode FROM room_gamemode_defaults").fetchall()
return {r["activity_level_id"]: r["special_gamemode"] for r in rows}
def is_activity_disabled(self, activity_level_id):
conn = self._get_conn()
row = conn.execute("SELECT 1 FROM disabled_activities WHERE activity_level_id = ?",
(activity_level_id,)).fetchone()
return row is not None
def set_activity_disabled(self, activity_level_id, disabled):
conn = self._get_conn()
if disabled:
conn.execute("INSERT OR IGNORE INTO disabled_activities (activity_level_id) VALUES (?)",
(activity_level_id,))
else:
conn.execute("DELETE FROM disabled_activities WHERE activity_level_id = ?",
(activity_level_id,))
conn.commit()
def get_all_disabled_activities(self):
conn = self._get_conn()
rows = conn.execute("SELECT activity_level_id FROM disabled_activities").fetchall()
return [r["activity_level_id"] for r in rows]
def is_room_disabled(self, room_id):
conn = self._get_conn()
row = conn.execute("SELECT disabled FROM rooms WHERE id = ?", (room_id,)).fetchone()
return bool(row["disabled"]) if row else False
def set_room_disabled(self, room_id, disabled):
conn = self._get_conn()
conn.execute("UPDATE rooms SET disabled = ? WHERE id = ?", (1 if disabled else 0, room_id))
conn.commit()
def get_special_gamemode_for_room(self, room_id):
conn = self._get_conn()
row = conn.execute("SELECT special_gamemode, activity_level_id FROM rooms WHERE id = ?",
(room_id,)).fetchone()
if not row:
return 0
room_gm = row["special_gamemode"] or 0
if room_gm:
return room_gm
return self.get_gamemode_default(row["activity_level_id"])
def set_room_permission(self, room_id, player_id, is_host, is_owner):
conn = self._get_conn()
if not is_host and not is_owner:
conn.execute("DELETE FROM room_permissions WHERE room_id = ? AND player_id = ?",
(room_id, player_id))
else:
conn.execute(
"INSERT OR REPLACE INTO room_permissions (room_id, player_id, is_host, is_owner) VALUES (?, ?, ?, ?)",
(room_id, player_id, 1 if is_host else 0, 1 if is_owner else 0))
conn.commit()
def get_room_permissions(self, room_id):
conn = self._get_conn()
return conn.execute("SELECT * FROM room_permissions WHERE room_id = ?", (room_id,)).fetchall()
def is_room_owner_or_coowner(self, room_id, player_id):
conn = self._get_conn()
room = conn.execute("SELECT creator_id FROM rooms WHERE id = ?", (room_id,)).fetchone()
if room and room["creator_id"] == player_id:
return True
perm = conn.execute("SELECT is_owner FROM room_permissions WHERE room_id = ? AND player_id = ?",
(room_id, player_id)).fetchone()
return bool(perm and perm["is_owner"])
def toggle_room_bookmark(self, player_id, room_id):
conn = self._get_conn()
existing = conn.execute("SELECT 1 FROM room_bookmarks WHERE player_id = ? AND room_id = ?",
(player_id, room_id)).fetchone()
if existing:
conn.execute("DELETE FROM room_bookmarks WHERE player_id = ? AND room_id = ?",
(player_id, room_id))
conn.commit()
return False
conn.execute("INSERT INTO room_bookmarks (player_id, room_id) VALUES (?, ?)",
(player_id, room_id))
conn.commit()
return True
def is_room_bookmarked(self, player_id, room_id):
conn = self._get_conn()
return bool(conn.execute("SELECT 1 FROM room_bookmarks WHERE player_id = ? AND room_id = ?",
(player_id, room_id)).fetchone())
def toggle_room_cheer(self, player_id, room_id):
conn = self._get_conn()
existing = conn.execute("SELECT 1 FROM room_cheers WHERE player_id = ? AND room_id = ?",
(player_id, room_id)).fetchone()
if existing:
conn.execute("DELETE FROM room_cheers WHERE player_id = ? AND room_id = ?",
(player_id, room_id))
conn.commit()
return False
conn.execute("INSERT INTO room_cheers (player_id, room_id) VALUES (?, ?)",
(player_id, room_id))
conn.commit()
return True
def is_room_cheered(self, player_id, room_id):
conn = self._get_conn()
return bool(conn.execute("SELECT 1 FROM room_cheers WHERE player_id = ? AND room_id = ?",
(player_id, room_id)).fetchone())
def get_room_cheer_count(self, room_id):
conn = self._get_conn()
row = conn.execute("SELECT COUNT(*) as c FROM room_cheers WHERE room_id = ?", (room_id,)).fetchone()
return row["c"] if row else 0
def get_bookmarked_rooms(self, player_id):
conn = self._get_conn()
rows = conn.execute(
"SELECT r.* FROM rooms r JOIN room_bookmarks b ON r.id = b.room_id WHERE b.player_id = ? ORDER BY b.created_at DESC",
(player_id,)).fetchall()
return [self._room_row_to_dict(r) for r in rows]
def has_cheered_today(self, from_pid: int, to_pid: int, date_str: str) -> bool:
conn = self._get_conn()
row = conn.execute(
"SELECT 1 FROM daily_cheers WHERE from_pid=? AND to_pid=? AND date_str=?",
(from_pid, to_pid, date_str),
).fetchone()
return row is not None
def record_cheer(self, from_pid: int, to_pid: int, date_str: str) -> None:
conn = self._get_conn()
conn.execute(
"INSERT OR IGNORE INTO daily_cheers (from_pid, to_pid, date_str) VALUES (?,?,?)",
(from_pid, to_pid, date_str),
)
conn.commit()
def get_all_events(self):
conn = self._get_conn()
rows = conn.execute("SELECT * FROM player_events ORDER BY start_time DESC").fetchall()
return [dict(r) for r in rows]
def get_active_events(self):
from datetime import datetime, timezone
now = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
conn = self._get_conn()
rows = conn.execute(
"SELECT * FROM player_events WHERE end_time > ? ORDER BY start_time ASC",
(now,),
).fetchall()
return [dict(r) for r in rows]
def create_event(self, name, description, start_time, end_time, poster_image="", creator_player_id=1, activity_level_id=""):
from datetime import datetime, timezone
now = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
conn = self._get_conn()
cur = conn.execute(
"INSERT INTO player_events (name, description, start_time, end_time, poster_image, creator_player_id, activity_level_id, created_at) VALUES (?,?,?,?,?,?,?,?)",
(name, description, start_time, end_time, poster_image, creator_player_id, activity_level_id, now),
)
conn.commit()
return cur.lastrowid
def delete_event(self, event_id):
conn = self._get_conn()
conn.execute("DELETE FROM player_events WHERE id=?", (event_id,))
conn.commit()
def get_event(self, event_id):
conn = self._get_conn()
row = conn.execute("SELECT * FROM player_events WHERE id=?", (event_id,)).fetchone()
return dict(row) if row else None
def update_event(self, event_id, **kwargs):
conn = self._get_conn()
allowed = {"name", "description", "start_time", "end_time", "poster_image", "creator_player_id", "activity_level_id"}
updates = {k: v for k, v in kwargs.items() if k in allowed}
if not updates:
return
sets = ", ".join(f"{k}=?" for k in updates)
conn.execute(f"UPDATE player_events SET {sets} WHERE id=?", list(updates.values()) + [event_id])
conn.commit()
def _get_amp_conn(self):
import json as _json
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("""CREATE TABLE amplitude_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL DEFAULT 0,
event_type TEXT NOT NULL DEFAULT '',
event_time INTEGER NOT NULL DEFAULT 0,
event_props TEXT DEFAULT '{}',
user_props TEXT DEFAULT '{}',
app_version TEXT DEFAULT '',
insert_id TEXT DEFAULT '',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)""")
try:
with open(self._amp_log_path, "r") as fh:
for line in fh:
line = line.strip()
if not line:
continue
try:
ev = _json.loads(line)
conn.execute(
"INSERT INTO amplitude_events (user_id, event_type, event_time, event_props, user_props, app_version, insert_id, created_at) VALUES (?,?,?,?,?,?,?,?)",
(ev.get("user_id", 0), ev.get("event_type", ""),
ev.get("event_time", 0), ev.get("event_props", "{}"),
ev.get("user_props", "{}"), ev.get("app_version", ""),
ev.get("insert_id", ""), ev.get("created_at", "")),
)
except Exception:
pass
except FileNotFoundError:
pass
conn.commit()
return conn
def save_amplitude_event(self, user_id, event_type, event_time, event_props, user_props, app_version, insert_id):
import json as _json, datetime as _dt
row = _json.dumps({
"user_id": user_id,
"event_type": event_type,
"event_time": event_time,
"event_props": _json.dumps(event_props) if isinstance(event_props, dict) else str(event_props),
"user_props": _json.dumps(user_props) if isinstance(user_props, dict) else str(user_props),
"app_version": app_version,
"insert_id": insert_id,
"created_at": _dt.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S"),
})
with self._amp_lock:
with open(self._amp_log_path, "a") as fh:
fh.write(row + "\n")
def save_amplitude_events_batch(self, events):
import json as _json, datetime as _dt
now = _dt.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
rows = []
for ev in events:
rows.append(_json.dumps({
"user_id": ev.get("user_id", 0),
"event_type": ev.get("event_type", ""),
"event_time": ev.get("event_time", 0),
"event_props": _json.dumps(ev.get("event_properties", {})),
"user_props": _json.dumps(ev.get("user_properties", {})),
"app_version": ev.get("app_version", ""),
"insert_id": ev.get("insert_id", ""),
"created_at": now,
}))
if rows:
with self._amp_lock:
with open(self._amp_log_path, "a") as fh:
fh.write("\n".join(rows) + "\n")
def get_amplitude_events(self, event_type=None, user_id=None, limit=200):
conn = self._get_amp_conn()
q = "SELECT * FROM amplitude_events WHERE 1=1"
params = []
if event_type:
q += " AND event_type=?"
params.append(event_type)
if user_id:
q += " AND user_id=?"
params.append(user_id)
q += " ORDER BY id DESC LIMIT ?"
params.append(limit)
return [dict(r) for r in conn.execute(q, params).fetchall()]
def get_amplitude_stats(self):
conn = self._get_amp_conn()
main_conn = self._get_conn()
total = conn.execute("SELECT COUNT(*) AS c FROM amplitude_events").fetchone()["c"]
by_type = conn.execute(
"SELECT event_type, COUNT(*) AS c FROM amplitude_events GROUP BY event_type ORDER BY c DESC"
).fetchall()
by_user = conn.execute(
"SELECT user_id, COUNT(*) AS c FROM amplitude_events GROUP BY user_id ORDER BY c DESC LIMIT 30"
).fetchall()
recent_activity = conn.execute(
"SELECT event_type, event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type='activity_stats' ORDER BY id DESC LIMIT 50"
).fetchall()
events_by_day = conn.execute(
"SELECT date(created_at) AS d, COUNT(*) AS c FROM amplitude_events "
"WHERE created_at >= date('now','-30 days') GROUP BY d ORDER BY d"
).fetchall()
events_by_hour = conn.execute(
"SELECT strftime('%H', created_at) AS h, COUNT(*) AS c FROM amplitude_events "
"GROUP BY h ORDER BY h"
).fetchall()
sessions_by_day = conn.execute(
"SELECT date(created_at) AS d, COUNT(*) AS c FROM amplitude_events "
"WHERE event_type='session_start' AND created_at >= date('now','-30 days') "
"GROUP BY d ORDER BY d"
).fetchall()
all_activity = conn.execute(
"SELECT event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type='activity_stats' ORDER BY id DESC LIMIT 500"
).fetchall()
photon_rows = conn.execute(
"SELECT event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type='photon_pings' ORDER BY id DESC LIMIT 200"
).fetchall()
purchase_rows = conn.execute(
"SELECT event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type='purchased_item' ORDER BY id DESC"
).fetchall()
crash_rows = conn.execute(
"SELECT event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type='crashdump_details' ORDER BY id DESC"
).fetchall()
social_rows = conn.execute(
"SELECT event_type, event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type IN ('sent_text_message','Chat_CreatedThread','sent_game_invite',"
"'received_GameInvite','accepted_game_invite','user_afk','subscribe_to_creator') "
"ORDER BY id DESC"
).fetchall()
network_rows = conn.execute(
"SELECT event_type, event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type IN ('connection_failure','network_masterSwitched','room_joinRequest') "
"ORDER BY id DESC LIMIT 200"
).fetchall()
objective_rows = conn.execute(
"SELECT event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type='dailyobjective_completed' ORDER BY id DESC"
).fetchall()
load_rows = conn.execute(
"SELECT event_type, event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type LIKE '%_load' OR event_type LIKE '%_additive_load' "
"ORDER BY id DESC LIMIT 300"
).fetchall()
levelup_rows = conn.execute(
"SELECT event_props, user_id, created_at FROM amplitude_events "
"WHERE event_type='player_leveled_up' ORDER BY id DESC"
).fetchall()
gift_stats = main_conn.execute(
"SELECT consumed, COUNT(*) AS c FROM gifts GROUP BY consumed"
).fetchall()
return {
"total": total,
"by_type": [dict(r) for r in by_type],
"by_user": [dict(r) for r in by_user],
"recent_activity": [dict(r) for r in recent_activity],
"events_by_day": [dict(r) for r in events_by_day],
"events_by_hour": [dict(r) for r in events_by_hour],
"sessions_by_day": [dict(r) for r in sessions_by_day],
"all_activity": [dict(r) for r in all_activity],
"photon_rows": [dict(r) for r in photon_rows],
"purchase_rows": [dict(r) for r in purchase_rows],
"crash_rows": [dict(r) for r in crash_rows],
"social_rows": [dict(r) for r in social_rows],
"network_rows": [dict(r) for r in network_rows],
"objective_rows": [dict(r) for r in objective_rows],
"load_rows": [dict(r) for r in load_rows],
"levelup_rows": [dict(r) for r in levelup_rows],
"gift_stats": [dict(r) for r in gift_stats],
}
def get_growth_stats(self):
conn = self._get_conn()
reg_by_day = [dict(r) for r in conn.execute(
"SELECT date(created_at) as d, COUNT(*) as c FROM profiles GROUP BY d ORDER BY d"
).fetchall()]
platform_breakdown = [dict(r) for r in conn.execute(
"SELECT platform, COUNT(*) as c FROM platform_accounts GROUP BY platform ORDER BY c DESC"
).fetchall()]
level_dist = [dict(r) for r in conn.execute(
"SELECT level, COUNT(*) as c FROM profiles GROUP BY level ORDER BY level"
).fetchall()]
top_players_xp = [dict(r) for r in conn.execute(
"SELECT id, username, xp, level, tokens, developer FROM profiles "
"ORDER BY xp DESC LIMIT 20"
).fetchall()]
top_players_tokens = [dict(r) for r in conn.execute(
"SELECT id, username, xp, level, tokens FROM profiles "
"ORDER BY tokens DESC LIMIT 20"
).fetchall()]
daily_logins = [dict(r) for r in conn.execute(
"SELECT date_str as d, COUNT(*) as c FROM daily_login_rewards GROUP BY date_str ORDER BY date_str"
).fetchall()]
room_visits_by_day = [dict(r) for r in conn.execute(
"SELECT date(visited_at) as d, COUNT(*) as c FROM room_visits GROUP BY d ORDER BY d"
).fetchall()]
chat_by_day = [dict(r) for r in conn.execute(
"SELECT date(sent_at) as d, COUNT(*) as c FROM chat_messages GROUP BY d ORDER BY d"
).fetchall()]
photos_by_day = [dict(r) for r in conn.execute(
"SELECT date(created_at) as d, COUNT(*) as c FROM saved_images GROUP BY d ORDER BY d"
).fetchall()]
gifts_by_day = [dict(r) for r in conn.execute(
"SELECT date(created_at) as d, COUNT(*) as c FROM gifts GROUP BY d ORDER BY d"
).fetchall()]
gift_rarity = [dict(r) for r in conn.execute(
"SELECT gift_rarity, COUNT(*) as c FROM gifts GROUP BY gift_rarity ORDER BY gift_rarity"
).fetchall()]
unlocked_by_day = [dict(r) for r in conn.execute(
"SELECT date(unlocked_at) as d, COUNT(*) as c FROM unlocked_items GROUP BY d ORDER BY d"
).fetchall()]
bans_by_day = [dict(r) for r in conn.execute(
"SELECT date(created_at) as d, action, COUNT(*) as c "
"FROM ban_log GROUP BY d, action ORDER BY d"
).fetchall()]
admin_by_day = [dict(r) for r in conn.execute(
"SELECT date(created_at) as d, COUNT(*) as c FROM admin_action_log GROUP BY d ORDER BY d"
).fetchall()]
admin_action_types = [dict(r) for r in conn.execute(
"SELECT action, COUNT(*) as c FROM admin_action_log GROUP BY action ORDER BY c DESC LIMIT 15"
).fetchall()]
rooms_by_day = [dict(r) for r in conn.execute(
"SELECT date(created_at) as d, COUNT(*) as c FROM rooms GROUP BY d ORDER BY d"
).fetchall()]
def _one(q):
r = conn.execute(q).fetchone()
return r[0] if r else 0
totals = {
"players": _one("SELECT COUNT(*) FROM profiles"),
"active": _one("SELECT COUNT(*) FROM profiles WHERE banned=0"),
"banned": _one("SELECT COUNT(*) FROM profiles WHERE banned=1"),
"devs": _one("SELECT COUNT(*) FROM profiles WHERE developer=1"),
"total_xp": _one("SELECT COALESCE(SUM(xp),0) FROM profiles"),
"total_tokens": _one("SELECT COALESCE(SUM(tokens),0) FROM profiles"),
"max_level": _one("SELECT COALESCE(MAX(level),0) FROM profiles"),
"avg_level": round(_one("SELECT COALESCE(AVG(level),0) FROM profiles WHERE banned=0"), 1),
"total_gifts": _one("SELECT COUNT(*) FROM gifts"),
"gifts_opened": _one("SELECT COUNT(*) FROM gifts WHERE consumed=1"),
"total_photos": _one("SELECT COUNT(*) FROM saved_images"),
"total_chat": _one("SELECT COUNT(*) FROM chat_messages"),
"total_friends": _one("SELECT COUNT(*) FROM relationships WHERE relationship_type=1"),
"total_rooms": _one("SELECT COUNT(*) FROM rooms"),
"total_unlocked": _one("SELECT COUNT(*) FROM unlocked_items"),
"total_bans": _one("SELECT COUNT(*) FROM ban_log WHERE action='ban'"),
"total_admin_acts":_one("SELECT COUNT(*) FROM admin_action_log"),
"total_logins": _one("SELECT COUNT(*) FROM daily_login_rewards"),
"total_visits": _one("SELECT COUNT(*) FROM room_visits"),
"consumables_held":_one("SELECT COALESCE(SUM(count),0) FROM consumable_inventory"),
}
return {
"reg_by_day": reg_by_day,
"platform_breakdown":platform_breakdown,
"level_dist": level_dist,
"top_players_xp": top_players_xp,
"top_players_tokens":top_players_tokens,
"daily_logins": daily_logins,
"room_visits_by_day":room_visits_by_day,
"chat_by_day": chat_by_day,
"photos_by_day": photos_by_day,
"gifts_by_day": gifts_by_day,
"gift_rarity": gift_rarity,
"unlocked_by_day": unlocked_by_day,
"bans_by_day": bans_by_day,
"admin_by_day": admin_by_day,
"admin_action_types":admin_action_types,
"rooms_by_day": rooms_by_day,
"totals": totals,
}
def record_player_count(self, online, sessions):
conn = self._get_conn()
conn.execute(
"INSERT INTO player_count_history (online, sessions) VALUES (?, ?)",
(online, sessions),
)
conn.commit()
def get_player_count_history(self, hours=48):
conn = self._get_conn()
rows = conn.execute(
"SELECT recorded_at, online, sessions FROM player_count_history "
"WHERE recorded_at >= datetime('now', ? || ' hours') ORDER BY recorded_at",
(f"-{hours}",),
).fetchall()
return [dict(r) for r in rows]
def get_all_chat_threads(self, limit=200):
conn = self._get_conn()
rows = conn.execute(
"""SELECT ct.id, ct.created_at,
COUNT(DISTINCT ctp.profile_id) AS member_count,
COUNT(DISTINCT cm.id) AS message_count,
MAX(cm.sent_at) AS last_message_at
FROM chat_threads ct
LEFT JOIN chat_thread_players ctp ON ctp.thread_id = ct.id
LEFT JOIN chat_messages cm ON cm.thread_id = ct.id
GROUP BY ct.id
HAVING message_count > 0
ORDER BY last_message_at DESC
LIMIT ?""",
(limit,),
).fetchall()
threads = []
for r in rows:
tid = r["id"]
players = conn.execute(
"""SELECT ctp.profile_id, p.username
FROM chat_thread_players ctp
LEFT JOIN profiles p ON p.id = ctp.profile_id
WHERE ctp.thread_id = ?""",
(tid,),
).fetchall()
threads.append({
"id": tid,
"created_at": r["created_at"],
"member_count": r["member_count"],
"message_count": r["message_count"],
"last_message_at": r["last_message_at"],
"players": [{"id": p["profile_id"], "username": p["username"] or f"#{p['profile_id']}"} for p in players],
})
return threads
def get_chat_messages_admin(self, thread_id, limit=100):
conn = self._get_conn()
rows = conn.execute(
"""SELECT cm.id, cm.thread_id, cm.sender_id, cm.contents, cm.sent_at,
p.username AS sender_username
FROM chat_messages cm
LEFT JOIN profiles p ON p.id = cm.sender_id
WHERE cm.thread_id = ?
ORDER BY cm.id DESC
LIMIT ?""",
(thread_id, limit),
).fetchall()
return [dict(r) for r in rows]
def delete_chat_message(self, msg_id):
conn = self._get_conn()
conn.execute("DELETE FROM chat_messages WHERE id = ?", (msg_id,))
conn.commit()
def get_leaderboard_with_usernames(self, objective_type=1, limit=100):
conn = self._get_conn()
rows = conn.execute(
"""SELECT ls.profile_id, ls.all_time, ls.periodic, p.username, p.level
FROM leaderboard_scores ls
LEFT JOIN profiles p ON p.id = ls.profile_id
WHERE ls.objective = ?
ORDER BY ls.all_time DESC
LIMIT ?""",
(objective_type, limit),
).fetchall()
return [dict(r) for r in rows]
def reset_leaderboard_periodic(self, objective_type=None):
conn = self._get_conn()
if objective_type is not None:
conn.execute("UPDATE leaderboard_scores SET periodic = 0 WHERE objective = ?", (objective_type,))
else:
conn.execute("UPDATE leaderboard_scores SET periodic = 0")
conn.commit()
def add_playtime(self, profile_id, seconds):
if seconds <= 0:
return
from datetime import datetime, timezone
now = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
conn = self._get_conn()
conn.execute("""
INSERT INTO player_playtime (profile_id, total_seconds, session_count, last_seen)
VALUES (?, ?, 1, ?)
ON CONFLICT(profile_id) DO UPDATE SET
total_seconds = total_seconds + excluded.total_seconds,
session_count = session_count + 1,
last_seen = excluded.last_seen
""", (profile_id, int(seconds), now))
conn.commit()
def get_playtime(self, profile_id):
conn = self._get_conn()
row = conn.execute(
"SELECT total_seconds, session_count, last_seen FROM player_playtime WHERE profile_id = ?",
(profile_id,),
).fetchone()
if row is None:
return {"total_seconds": 0, "session_count": 0, "last_seen": None}
return dict(row)
def get_total_playtime(self):
conn = self._get_conn()
row = conn.execute("SELECT COALESCE(SUM(total_seconds), 0) AS t FROM player_playtime").fetchone()
return row["t"] if row else 0
def get_playtime_leaderboard(self, limit=20):
conn = self._get_conn()
rows = conn.execute("""
SELECT pt.profile_id, pt.total_seconds, pt.session_count, p.username, p.display_name, p.level
FROM player_playtime pt
LEFT JOIN profiles p ON p.id = pt.profile_id
ORDER BY pt.total_seconds DESC
LIMIT ?
""", (limit,)).fetchall()
return [dict(r) for r in rows]