xG_Master_Bot / db.py
Serge017's picture
Claude Opus 4.7 (1M context)
rewrite: xG Master Bot v2 — clean modular architecture + channel publishing
ccad19a
Raw History Blame Contribute Delete
14 kB
"""
db.py — PostgreSQL (Neon) / SQLite async схема xG Master Bot.
Таблицы:
users — банк + настройки пользователя (id = Telegram user_id)
signals — прогнозы модели (публикуются в канал)
bets — сделанные пользователем ставки (из скриншотов)
"""
from __future__ import annotations
import json
import logging
from datetime import datetime, timezone
from typing import Optional
from sqlalchemy import (
BigInteger, Boolean, Column, DateTime, Float, Integer, String, Text,
func, select, update,
)
from sqlalchemy.ext.asyncio import (
AsyncEngine, AsyncSession, async_sessionmaker, create_async_engine,
)
from sqlalchemy.orm import DeclarativeBase
from config import config
log = logging.getLogger(__name__)
class Base(DeclarativeBase):
pass
def utcnow() -> datetime:
return datetime.now(timezone.utc).replace(tzinfo=None)
class User(Base):
__tablename__ = "users"
id = Column(BigInteger, primary_key=True) # telegram user_id
username = Column(String(64))
first_name = Column(String(128))
bank = Column(Float, default=0.0)
initial_bank = Column(Float, default=0.0)
settings = Column(Text, default="{}") # JSON
created_at = Column(DateTime, default=utcnow)
updated_at = Column(DateTime, default=utcnow, onupdate=utcnow)
class Signal(Base):
__tablename__ = "signals"
id = Column(Integer, primary_key=True, autoincrement=True)
match = Column(String(255), nullable=False)
league = Column(String(64))
kickoff = Column(DateTime)
market = Column(String(64), default="1X2") # 1X2 / totals / btts
pick = Column(String(128), nullable=False) # "Победа Реал" / "ТБ 2.5" ...
book_odds = Column(Float, nullable=False)
fair_odds = Column(Float, nullable=False)
probability = Column(Float, nullable=False) # 0..1
confidence = Column(Integer, default=0) # 0..100
edge = Column(Float, default=0.0) # prob*book_odds - 1
reasoning = Column(Text)
risks = Column(Text)
home_form = Column(String(32))
away_form = Column(String(32))
recommended_stake = Column(Float, default=0.0)
channel_message_id = Column(BigInteger)
status = Column(String(16), default="pending") # pending/win/loss/void
created_at = Column(DateTime, default=utcnow)
class Bet(Base):
__tablename__ = "bets"
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(BigInteger, nullable=False, index=True)
signal_id = Column(Integer) # опциональная связь с сигналом
match = Column(String(255))
pick = Column(String(128))
league = Column(String(64))
odds = Column(Float, nullable=False)
stake = Column(Float, nullable=False)
status = Column(String(16), default="pending") # pending/win/loss/void
profit = Column(Float, default=0.0)
bank_before = Column(Float)
bank_after = Column(Float)
source = Column(String(32), default="manual") # manual / screenshot
note = Column(Text)
created_at = Column(DateTime, default=utcnow)
closed_at = Column(DateTime)
# ────────────────────────────────────────────────────────────────
# Engine / session
# ────────────────────────────────────────────────────────────────
_engine: Optional[AsyncEngine] = None
_SessionMaker: Optional[async_sessionmaker[AsyncSession]] = None
async def init_db() -> None:
global _engine, _SessionMaker
url = config.database_url
safe_url = url.split("@", 1)[1] if "@" in url else url
log.info("DB connect → %s", safe_url)
_engine = create_async_engine(url, pool_pre_ping=True, pool_recycle=300)
_SessionMaker = async_sessionmaker(_engine, expire_on_commit=False)
async with _engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
log.info("DB schema ready")
def session() -> AsyncSession:
if _SessionMaker is None:
raise RuntimeError("init_db() не был вызван")
return _SessionMaker()
# ────────────────────────────────────────────────────────────────
# CRUD: users
# ────────────────────────────────────────────────────────────────
async def ensure_user(user_id: int, username: str = "", first_name: str = "") -> User:
async with session() as s:
u = await s.get(User, user_id)
if u is None:
u = User(id=user_id, username=username, first_name=first_name, bank=0.0)
s.add(u)
await s.commit()
await s.refresh(u)
return u
async def get_bank(user_id: int) -> float:
async with session() as s:
u = await s.get(User, user_id)
return float(u.bank) if u else 0.0
async def set_bank(user_id: int, amount: float) -> None:
amount = round(max(0.0, amount), 2)
async with session() as s:
u = await s.get(User, user_id)
if u is None:
u = User(id=user_id, bank=amount, initial_bank=amount)
s.add(u)
else:
if not u.initial_bank:
u.initial_bank = amount
u.bank = amount
u.updated_at = utcnow()
await s.commit()
async def adjust_bank(user_id: int, delta: float) -> float:
async with session() as s:
u = await s.get(User, user_id)
if u is None:
u = User(id=user_id, bank=max(0.0, delta))
s.add(u)
await s.commit()
return u.bank
u.bank = round(max(0.0, float(u.bank) + delta), 2)
u.updated_at = utcnow()
await s.commit()
return float(u.bank)
async def get_settings(user_id: int) -> dict:
async with session() as s:
u = await s.get(User, user_id)
if not u or not u.settings:
return {}
try:
return json.loads(u.settings)
except Exception:
return {}
async def update_settings(user_id: int, patch: dict) -> dict:
async with session() as s:
u = await s.get(User, user_id)
if u is None:
u = User(id=user_id, settings=json.dumps(patch))
s.add(u)
await s.commit()
return patch
data = {}
try:
data = json.loads(u.settings or "{}")
except Exception:
data = {}
data.update(patch)
u.settings = json.dumps(data)
u.updated_at = utcnow()
await s.commit()
return data
# ────────────────────────────────────────────────────────────────
# CRUD: signals
# ────────────────────────────────────────────────────────────────
async def save_signal(**kwargs) -> int:
async with session() as s:
sig = Signal(**kwargs)
s.add(sig)
await s.commit()
await s.refresh(sig)
return sig.id
async def get_signal(signal_id: int) -> Optional[Signal]:
async with session() as s:
return await s.get(Signal, signal_id)
async def set_signal_message_id(signal_id: int, message_id: int) -> None:
async with session() as s:
await s.execute(
update(Signal).where(Signal.id == signal_id).values(channel_message_id=message_id)
)
await s.commit()
async def list_signals(limit: int = 10) -> list[Signal]:
async with session() as s:
res = await s.execute(
select(Signal).order_by(Signal.created_at.desc()).limit(limit)
)
return list(res.scalars().all())
async def list_todays_signals() -> list[Signal]:
async with session() as s:
today = utcnow().date()
res = await s.execute(
select(Signal).where(func.date(Signal.created_at) == today)
.order_by(Signal.created_at.desc())
)
return list(res.scalars().all())
async def find_signal_by_match(query: str, limit: int = 10) -> list[Signal]:
q = f"%{query.lower()}%"
async with session() as s:
res = await s.execute(
select(Signal).where(func.lower(Signal.match).like(q))
.order_by(Signal.created_at.desc()).limit(limit)
)
return list(res.scalars().all())
# ────────────────────────────────────────────────────────────────
# CRUD: bets
# ────────────────────────────────────────────────────────────────
async def add_bet(
user_id: int, match: str, pick: str, odds: float, stake: float,
league: str = "", signal_id: Optional[int] = None, source: str = "manual",
note: str = "",
) -> int:
"""Создаёт ставку и СРАЗУ списывает stake из банка (деньги «в игре»)."""
stake = round(stake, 2)
odds = round(odds, 3)
async with session() as s:
user = await s.get(User, user_id)
if user is None:
user = User(id=user_id, bank=0.0)
s.add(user)
await s.flush()
bank_before = float(user.bank)
user.bank = round(max(0.0, bank_before - stake), 2)
user.updated_at = utcnow()
b = Bet(
user_id=user_id, signal_id=signal_id, match=match, pick=pick,
league=league, odds=odds, stake=stake,
status="pending", source=source, note=note,
bank_before=bank_before, bank_after=user.bank,
)
s.add(b)
await s.commit()
await s.refresh(b)
return b.id
async def close_bet(bet_id: int, result: str) -> Optional[Bet]:
"""result ∈ {'win','loss','void'}. Возвращает обновлённый Bet."""
assert result in {"win", "loss", "void"}
async with session() as s:
b = await s.get(Bet, bet_id)
if b is None or b.status != "pending":
return b
user = await s.get(User, b.user_id)
if user is None:
user = User(id=b.user_id, bank=0.0)
s.add(user)
# stake уже списан при add_bet. Нужно вернуть на банк:
# win → stake * odds (вся выплата)
# loss → 0
# void → stake (возврат)
if result == "win":
payout = round(b.stake * b.odds, 2)
profit = round(payout - b.stake, 2)
elif result == "void":
payout = round(b.stake, 2)
profit = 0.0
else:
payout = 0.0
profit = round(-b.stake, 2)
user.bank = round(float(user.bank) + payout, 2)
user.updated_at = utcnow()
b.status = result
b.profit = profit
b.closed_at = utcnow()
b.bank_after = user.bank
await s.commit()
await s.refresh(b)
return b
async def list_bets(user_id: int, limit: int = 20) -> list[Bet]:
async with session() as s:
res = await s.execute(
select(Bet).where(Bet.user_id == user_id)
.order_by(Bet.created_at.desc()).limit(limit)
)
return list(res.scalars().all())
async def stats_for_user(user_id: int) -> dict:
async with session() as s:
user = await s.get(User, user_id)
bank = float(user.bank) if user else 0.0
rows = (await s.execute(
select(Bet.status, func.count(), func.sum(Bet.stake), func.sum(Bet.profit), func.avg(Bet.odds))
.where(Bet.user_id == user_id, Bet.status != "pending")
.group_by(Bet.status)
)).all()
tally = {"win": [0, 0.0, 0.0, 0.0], "loss": [0, 0.0, 0.0, 0.0], "void": [0, 0.0, 0.0, 0.0]}
for status, cnt, staked, profit, avg_odds in rows:
tally[status] = [cnt or 0, staked or 0.0, profit or 0.0, avg_odds or 0.0]
total = tally["win"][0] + tally["loss"][0] + tally["void"][0]
staked = tally["win"][1] + tally["loss"][1] + tally["void"][1]
profit = tally["win"][2] + tally["loss"][2] # void не влияет
decided = tally["win"][0] + tally["loss"][0]
avg_odds = (
(tally["win"][0] * tally["win"][3] + tally["loss"][0] * tally["loss"][3]) / decided
) if decided else 0.0
return {
"bank": round(bank, 2),
"total": total,
"wins": tally["win"][0],
"losses": tally["loss"][0],
"voids": tally["void"][0],
"win_rate": round(tally["win"][0] / decided * 100, 1) if decided else 0.0,
"roi": round(profit / staked * 100, 1) if staked else 0.0,
"profit": round(profit, 2),
"avg_odds": round(avg_odds, 2),
"avg_stake": round(staked / total, 2) if total else 0.0,
}
async def retro_report(user_id: int, limit: int = 20) -> list[Bet]:
"""Последние закрытые ставки для ретро-отчёта."""
async with session() as s:
res = await s.execute(
select(Bet).where(Bet.user_id == user_id, Bet.status != "pending")
.order_by(Bet.closed_at.desc().nullslast()).limit(limit)
)
return list(res.scalars().all())