src/sheets_backend.py

import uuid
from datetime import datetime, timezone

import gspread
from google.oauth2.credentials import Credentials

_PLAYERS_HEADER = ["player_id", "name", "created_at"]
_GAMES_HEADER = ["timestamp", "game_name", "session_id", "round_number", "player_name", "score"]
_WORKBOOK_TITLE_PREFIX = "BoardGameTracker"


def _get_user_client(access_token: str) -> gspread.Client:
    creds = Credentials(token=access_token)
    return gspread.authorize(creds)


def get_or_create_user_workbook(access_token: str, email: str) -> gspread.Spreadsheet:
    """Returns the user's own Sheets workbook, creating it in their Drive on first use.

    Uses the user's own OAuth access token (drive.file scope), so the file is
    owned by them directly -- no service account or shared storage involved.
    """
    client = _get_user_client(access_token)
    title = f"{_WORKBOOK_TITLE_PREFIX} - {email}"

    existing = client.list_spreadsheet_files(title=title)
    if existing:
        return client.open_by_key(existing[0]["id"])

    sh = client.create(title)
    players_ws = sh.sheet1
    players_ws.update_title("players")
    players_ws.append_row(_PLAYERS_HEADER)
    games_ws = sh.add_worksheet(title="games_played", rows=1, cols=len(_GAMES_HEADER))
    games_ws.append_row(_GAMES_HEADER)
    return sh


def list_players(wb: gspread.Spreadsheet) -> list[dict]:
    ws = wb.worksheet("players")
    return ws.get_all_records()


def list_game_names(wb: gspread.Spreadsheet) -> list[str]:
    """Distinct game names played, most-recently-used first."""
    ws = wb.worksheet("games_played")
    names = [row["game_name"] for row in ws.get_all_records() if row.get("game_name")]
    return list(dict.fromkeys(reversed(names)))


def add_player(wb: gspread.Spreadsheet, name: str) -> str:
    ws = wb.worksheet("players")
    existing = ws.get_all_records()
    for row in existing:
        if row.get("name", "").strip().lower() == name.strip().lower():
            return row["player_id"]

    player_id = uuid.uuid4().hex
    ws.append_row([player_id, name, datetime.now(timezone.utc).isoformat()])
    return player_id


def record_round(
    wb: gspread.Spreadsheet,
    *,
    game_name: str,
    session_id: str,
    round_number: int,
    player_name: str,
    score: int,
) -> None:
    ws = wb.worksheet("games_played")
    ws.append_row(
        [
            datetime.now(timezone.utc).isoformat(),
            game_name,
            session_id,
            round_number,
            player_name,
            score,
        ]
    )