3613 lines
151 KiB
Python
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]
|