wiki-tcg/backend/services/database_functions.py

import asyncio
import logging
from datetime import datetime, timedelta

from sqlalchemy import delete, insert
from sqlalchemy.orm import Session

from game.card import _get_cards_async
from core.models import Card as CardModel
from core.models import GameChallenge as GameChallengeModel
from core.models import Notification as NotificationModel
from core.models import TradeProposal as TradeProposalModel
from core.models import User as UserModel
from core.database import SessionLocal

logger = logging.getLogger("app")

## Card pool management

POOL_MINIMUM = 1000
POOL_TARGET = 2000
POOL_BATCH_SIZE = 10
POOL_SLEEP = 4.0
# After this many consecutive empty batches, stop trying and wait for the cooldown.
POOL_MAX_CONSECUTIVE_EMPTY = 5
POOL_CIRCUIT_BREAKER_COOLDOWN = 600.0  # seconds

pool_filling = False
# asyncio monotonic timestamp; 0 means breaker is closed (no cooldown active)
_cb_open_until: float = 0.0

async def fill_card_pool():
  global pool_filling, _cb_open_until

  if pool_filling:
    logger.info("Pool fill already in progress, skipping")
    return

  loop_time = asyncio.get_event_loop().time()
  if loop_time < _cb_open_until:
    remaining = int(_cb_open_until - loop_time)
    logger.warning(f"Card generation circuit breaker open, skipping fill ({remaining}s remaining)")
    return

  pool_filling = True
  db: Session = SessionLocal()
  try:
    unassigned = db.query(CardModel).filter(CardModel.user_id == None, CardModel.ai_used == False).count()
    logger.info(f"Card pool has {unassigned} unassigned cards")
    if unassigned >= POOL_MINIMUM:
      logger.info("Pool sufficiently stocked, skipping fill")
      return

    needed = POOL_TARGET - unassigned
    logger.info(f"Filling pool with {needed} cards")

    fetched = 0
    consecutive_empty = 0
    while fetched < needed:
      batch_size = min(POOL_BATCH_SIZE, needed - fetched)
      cards = await _get_cards_async(batch_size)

      if not cards:
        consecutive_empty += 1
        logger.warning(
          f"Card generation batch returned 0 cards "
          f"({consecutive_empty}/{POOL_MAX_CONSECUTIVE_EMPTY} consecutive empty batches)"
        )
        if consecutive_empty >= POOL_MAX_CONSECUTIVE_EMPTY:
          _cb_open_until = asyncio.get_event_loop().time() + POOL_CIRCUIT_BREAKER_COOLDOWN
          logger.error(
            f"ALERT: Card generation circuit breaker tripped — {consecutive_empty} consecutive "
            f"empty batches. Wikipedia/Wikirank API may be down. "
            f"Next retry in {int(POOL_CIRCUIT_BREAKER_COOLDOWN)}s."
          )
          return
        await asyncio.sleep(POOL_SLEEP)
        continue

      consecutive_empty = 0
      db.execute(insert(CardModel).values([
        dict(
          name=card.name,
          image_link=card.image_link,
          card_rarity=card.card_rarity.name,
          card_type=card.card_type.name,
          text=card.text,
          attack=card.attack,
          defense=card.defense,
          cost=card.cost,
          user_id=None,
        )
        for card in cards
      ]))
      db.commit()
      fetched += len(cards)
      logger.info(f"Pool fill progress: {fetched}/{needed}")
      await asyncio.sleep(POOL_SLEEP)

  finally:
    pool_filling = False
    db.close()

## Booster management

BOOSTER_MAX = 5
BOOSTER_COOLDOWN_HOURS = 5

def check_boosters(user: UserModel, db: Session) -> tuple[int, datetime|None]:
  if user.boosters_countdown is None:
    if user.boosters < BOOSTER_MAX:
      user.boosters = BOOSTER_MAX
      db.commit()
    return (user.boosters, user.boosters_countdown)

  now = datetime.now()
  countdown = user.boosters_countdown

  while user.boosters < BOOSTER_MAX:
    next_tick = countdown + timedelta(hours=BOOSTER_COOLDOWN_HOURS)
    if now >= next_tick:
      user.boosters += 1
      countdown = next_tick
    else:
      break

  user.boosters_countdown = countdown if user.boosters < BOOSTER_MAX else None
  db.commit()
  return (user.boosters, user.boosters_countdown)

## Periodic cleanup

CLEANUP_INTERVAL_SECONDS = 3600  # 1 hour


async def run_cleanup_loop():
  # Brief startup delay so the DB is fully ready before first run
  await asyncio.sleep(60)
  while True:
    try:
      _delete_expired_records()
    except Exception:
      logger.exception("Periodic cleanup job failed")
    await asyncio.sleep(CLEANUP_INTERVAL_SECONDS)


def _delete_expired_records():
  now = datetime.now()
  with SessionLocal() as db:
    for model in (NotificationModel, TradeProposalModel, GameChallengeModel):
      # Notification.expires_at is nullable — skip rows without an expiry.
      # TradeProposal and GameChallenge always have expires_at, but the
      # guard is harmless and makes the intent explicit.
      result = db.execute(
        delete(model).where(
          model.expires_at != None,  # noqa: E711
          model.expires_at < now,
        )
      )
      db.commit()
      logger.info("Cleanup: deleted %d expired %s rows", result.rowcount, model.__tablename__)