250 lines
8.2 KiB
Python
250 lines
8.2 KiB
Python
import json
|
|
import sqlite3
|
|
import time
|
|
|
|
from web.config import Config
|
|
|
|
|
|
class RosterVersionService:
|
|
@staticmethod
|
|
def _connect_web(web_db_path=None):
|
|
db = sqlite3.connect(web_db_path or Config.DB_WEB_PATH, timeout=30)
|
|
db.row_factory = sqlite3.Row
|
|
db.execute('PRAGMA foreign_keys = ON')
|
|
return db
|
|
|
|
@staticmethod
|
|
def _normalize_ids(player_ids):
|
|
seen = set()
|
|
result = []
|
|
for value in player_ids or []:
|
|
steam_id = str(value).strip()
|
|
if steam_id and steam_id not in seen:
|
|
seen.add(steam_id)
|
|
result.append(steam_id)
|
|
return result
|
|
|
|
@staticmethod
|
|
def ensure_initial_version(web_db_path=None, l2_db_path=None):
|
|
web = RosterVersionService._connect_web(web_db_path)
|
|
try:
|
|
current = web.execute(
|
|
"""
|
|
SELECT id
|
|
FROM team_roster_versions
|
|
WHERE is_current = 1
|
|
LIMIT 1
|
|
"""
|
|
).fetchone()
|
|
if current:
|
|
return int(current['id'])
|
|
|
|
lineup = web.execute(
|
|
"""
|
|
SELECT player_ids_json
|
|
FROM team_lineups
|
|
WHERE is_active = 1
|
|
ORDER BY created_at DESC, id DESC
|
|
LIMIT 1
|
|
"""
|
|
).fetchone()
|
|
if not lineup:
|
|
return None
|
|
try:
|
|
player_ids = RosterVersionService._normalize_ids(
|
|
json.loads(lineup['player_ids_json'] or '[]')
|
|
)
|
|
except (TypeError, json.JSONDecodeError):
|
|
return None
|
|
if not player_ids:
|
|
return None
|
|
|
|
effective_from = int(time.time())
|
|
l2_path = l2_db_path or Config.DB_L2_PATH
|
|
l2 = sqlite3.connect(l2_path)
|
|
try:
|
|
placeholders = ','.join('?' for _ in player_ids)
|
|
row = l2.execute(
|
|
f"""
|
|
SELECT MIN(m.start_time)
|
|
FROM fact_match_players p
|
|
JOIN fact_matches m ON m.match_id = p.match_id
|
|
WHERE p.steam_id_64 IN ({placeholders})
|
|
""",
|
|
player_ids,
|
|
).fetchone()
|
|
if row and row[0]:
|
|
effective_from = int(row[0])
|
|
finally:
|
|
l2.close()
|
|
|
|
cursor = web.execute(
|
|
"""
|
|
INSERT INTO team_roster_versions (
|
|
name, effective_from, is_current, notes
|
|
) VALUES (?, ?, 1, ?)
|
|
""",
|
|
['2.0.0 Beta Initial Roster', effective_from, 'Bootstrapped from active lineup'],
|
|
)
|
|
version_id = int(cursor.lastrowid)
|
|
web.executemany(
|
|
"""
|
|
INSERT INTO team_roster_members (
|
|
roster_version_id, steam_id_64, member_role, position_order
|
|
) VALUES (?, ?, 'member', ?)
|
|
""",
|
|
[
|
|
(version_id, steam_id, index)
|
|
for index, steam_id in enumerate(player_ids)
|
|
],
|
|
)
|
|
web.commit()
|
|
return version_id
|
|
finally:
|
|
web.close()
|
|
|
|
@staticmethod
|
|
def snapshot_roster(
|
|
player_ids,
|
|
name=None,
|
|
effective_from=None,
|
|
roles=None,
|
|
web_db_path=None,
|
|
):
|
|
normalized_ids = RosterVersionService._normalize_ids(player_ids)
|
|
if not normalized_ids:
|
|
raise ValueError('Roster version requires at least one player')
|
|
|
|
roles = roles or {}
|
|
effective_from = int(effective_from or time.time())
|
|
web = RosterVersionService._connect_web(web_db_path)
|
|
try:
|
|
current = web.execute(
|
|
"""
|
|
SELECT *
|
|
FROM team_roster_versions
|
|
WHERE is_current = 1
|
|
LIMIT 1
|
|
"""
|
|
).fetchone()
|
|
if current:
|
|
current_ids = [
|
|
str(row[0]) for row in web.execute(
|
|
"""
|
|
SELECT steam_id_64
|
|
FROM team_roster_members
|
|
WHERE roster_version_id = ?
|
|
ORDER BY position_order, steam_id_64
|
|
""",
|
|
[current['id']],
|
|
)
|
|
]
|
|
if current_ids == normalized_ids:
|
|
return int(current['id']), False
|
|
web.execute(
|
|
"""
|
|
UPDATE team_roster_versions
|
|
SET is_current = 0, effective_to = ?
|
|
WHERE id = ?
|
|
""",
|
|
[effective_from - 1, current['id']],
|
|
)
|
|
|
|
cursor = web.execute(
|
|
"""
|
|
INSERT INTO team_roster_versions (
|
|
name, effective_from, is_current
|
|
) VALUES (?, ?, 1)
|
|
""",
|
|
[
|
|
name or f'Roster {time.strftime("%Y-%m-%d")}',
|
|
effective_from,
|
|
],
|
|
)
|
|
version_id = int(cursor.lastrowid)
|
|
values = []
|
|
for index, steam_id in enumerate(normalized_ids):
|
|
role = roles.get(steam_id, 'member')
|
|
if role not in {'starter', 'substitute', 'member'}:
|
|
role = 'member'
|
|
values.append((version_id, steam_id, role, index))
|
|
web.executemany(
|
|
"""
|
|
INSERT INTO team_roster_members (
|
|
roster_version_id, steam_id_64, member_role, position_order
|
|
) VALUES (?, ?, ?, ?)
|
|
""",
|
|
values,
|
|
)
|
|
web.commit()
|
|
return version_id, True
|
|
except Exception:
|
|
web.rollback()
|
|
raise
|
|
finally:
|
|
web.close()
|
|
|
|
@staticmethod
|
|
def list_versions(web_db_path=None):
|
|
web = RosterVersionService._connect_web(web_db_path)
|
|
try:
|
|
rows = web.execute(
|
|
"""
|
|
SELECT
|
|
version.*,
|
|
COUNT(member.steam_id_64) AS member_count
|
|
FROM team_roster_versions version
|
|
LEFT JOIN team_roster_members member
|
|
ON member.roster_version_id = version.id
|
|
GROUP BY version.id
|
|
ORDER BY version.effective_from DESC, version.id DESC
|
|
"""
|
|
).fetchall()
|
|
return [dict(row) for row in rows]
|
|
finally:
|
|
web.close()
|
|
|
|
@staticmethod
|
|
def get_current_members(web_db_path=None):
|
|
web = RosterVersionService._connect_web(web_db_path)
|
|
try:
|
|
rows = web.execute(
|
|
"""
|
|
SELECT member.*, version.name AS version_name
|
|
FROM team_roster_members member
|
|
JOIN team_roster_versions version
|
|
ON version.id = member.roster_version_id
|
|
WHERE version.is_current = 1
|
|
ORDER BY member.position_order, member.steam_id_64
|
|
"""
|
|
).fetchall()
|
|
return [dict(row) for row in rows]
|
|
finally:
|
|
web.close()
|
|
|
|
@staticmethod
|
|
def update_current_member_role(steam_id, member_role, web_db_path=None):
|
|
if member_role not in {'starter', 'substitute', 'member'}:
|
|
raise ValueError('Invalid roster member role')
|
|
web = RosterVersionService._connect_web(web_db_path)
|
|
try:
|
|
cursor = web.execute(
|
|
"""
|
|
UPDATE team_roster_members
|
|
SET member_role = ?
|
|
WHERE steam_id_64 = ?
|
|
AND roster_version_id = (
|
|
SELECT id
|
|
FROM team_roster_versions
|
|
WHERE is_current = 1
|
|
LIMIT 1
|
|
)
|
|
""",
|
|
[member_role, str(steam_id)],
|
|
)
|
|
if cursor.rowcount != 1:
|
|
raise ValueError('Player is not in the current roster version')
|
|
web.commit()
|
|
finally:
|
|
web.close()
|