src/sheets_backend.py

import uuid
from datetime import datetime, timezone

import gspread
import streamlit as st
from google.oauth2.credentials import Credentials

_PLAYERS_HEADER = ["player_id", "name", "created_at"]
_GAMES_HEADER = ["game_id", "name", "created_at"]
# game_name/player_name stay alongside the id columns (rather than being
# replaced) so rows written before this schema existed keep displaying
# correctly, and so display code never strictly depends on an id lookup
# succeeding.
_GAMES_PLAYED_HEADER = [
    "timestamp",
    "game_id",
    "game_name",
    "session_id",
    "round_number",
    "player_id",
    "player_name",
    "score",
]
_WORKBOOK_TITLE_PREFIX = "BoardGameTracker"

# Sheets API enforces a strict per-minute read quota. Every worksheet lookup
# and every full-table read counts against it, and a Streamlit rerun fires on
# every widget interaction -- so naive per-call reads blow through the quota
# almost immediately during an active game. These two caches are the fix:
# worksheet *handles* (including the one-time header-migration check) are
# cached for the life of the session, and row *data* is cached briefly with
# an explicit invalidation on every write.
_RECORDS_CACHE_TTL = 20  # seconds


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


def _ensure_columns(ws: gspread.Worksheet, required_columns: list[str]) -> list[str]:
    """Appends any missing columns to the end of the header row.

    Existing data is never touched or reordered -- only new columns get
    added at the end -- so sheets created before a schema change keep
    working unmodified; old rows simply have blank cells for the new
    columns.
    """
    header = ws.row_values(1)
    missing = [c for c in required_columns if c not in header]
    if missing:
        header = header + missing
        ws.update([header], "A1")
    return header


def _get_or_create_worksheet(
    wb: gspread.Spreadsheet, title: str, header: list[str]
) -> gspread.Worksheet:
    cache_key = f"_ws_handle_{wb.id}_{title}"
    if cache_key in st.session_state:
        return st.session_state[cache_key]

    try:
        ws = wb.worksheet(title)
        _ensure_columns(ws, header)
    except gspread.exceptions.WorksheetNotFound:
        ws = wb.add_worksheet(title=title, rows=1, cols=len(header))
        ws.append_row(header)

    st.session_state[cache_key] = ws
    return ws


@st.cache_data(ttl=_RECORDS_CACHE_TTL, show_spinner=False)
def _cached_records(_ws: gspread.Worksheet, cache_key: str) -> list[dict]:
    return _ws.get_all_records()


def _get_records(wb: gspread.Spreadsheet, ws: gspread.Worksheet, title: str) -> list[dict]:
    return _cached_records(ws, f"{wb.id}:{title}")


def _invalidate_records_cache() -> None:
    _cached_records.clear()


def _append_row(ws: gspread.Worksheet, row: dict) -> None:
    column_order = ws.row_values(1)
    ws.append_row([row.get(c, "") for c in column_order])
    _invalidate_records_cache()


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:
        sh = client.open_by_key(existing[0]["id"])
    else:
        sh = client.create(title)
        players_ws = sh.sheet1
        players_ws.update_title("players")
        players_ws.append_row(_PLAYERS_HEADER)

    # Idempotent: creates "games" and migrates "games_played" to the current
    # schema for pre-existing workbooks too, not just brand-new ones.
    _get_or_create_worksheet(sh, "players", _PLAYERS_HEADER)
    _get_or_create_worksheet(sh, "games", _GAMES_HEADER)
    _get_or_create_worksheet(sh, "games_played", _GAMES_PLAYED_HEADER)
    return sh


def list_players(wb: gspread.Spreadsheet) -> list[dict]:
    ws = _get_or_create_worksheet(wb, "players", _PLAYERS_HEADER)
    return _get_records(wb, ws, "players")


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

    player_id = uuid.uuid4().hex
    _append_row(
        ws, {"player_id": player_id, "name": name, "created_at": datetime.now(timezone.utc).isoformat()}
    )
    return player_id


def list_games(wb: gspread.Spreadsheet) -> list[dict]:
    ws = _get_or_create_worksheet(wb, "games", _GAMES_HEADER)
    return _get_records(wb, ws, "games")


def get_or_create_game(wb: gspread.Spreadsheet, name: str) -> str:
    ws = _get_or_create_worksheet(wb, "games", _GAMES_HEADER)
    existing = _get_records(wb, ws, "games")
    for row in existing:
        if str(row.get("name", "")).strip().lower() == name.strip().lower():
            return row["game_id"]

    game_id = uuid.uuid4().hex
    _append_row(
        ws, {"game_id": game_id, "name": name, "created_at": datetime.now(timezone.utc).isoformat()}
    )
    return game_id


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


def record_round(
    wb: gspread.Spreadsheet,
    *,
    game_id: str,
    game_name: str,
    session_id: str,
    round_number: int,
    player_id: str,
    player_name: str,
    score: int,
) -> None:
    ws = _get_or_create_worksheet(wb, "games_played", _GAMES_PLAYED_HEADER)
    _append_row(
        ws,
        {
            "timestamp": datetime.now(timezone.utc).isoformat(),
            "game_id": game_id,
            "game_name": game_name,
            "session_id": session_id,
            "round_number": round_number,
            "player_id": player_id,
            "player_name": player_name,
            "score": score,
        },
    )


def list_game_sessions(wb: gspread.Spreadsheet) -> list[dict]:
    """One summary entry per distinct session_id, most recently played first."""
    ws = _get_or_create_worksheet(wb, "games_played", _GAMES_PLAYED_HEADER)
    rows = _get_records(wb, ws, "games_played")

    sessions: dict[str, dict] = {}
    order: list[str] = []
    for row in rows:
        sid = row.get("session_id")
        if not sid:
            continue
        if sid not in sessions:
            sessions[sid] = {
                "game_name": row.get("game_name") or "Untitled",
                "timestamp": row.get("timestamp", ""),
                "players": set(),
                "rounds": set(),
            }
            order.append(sid)
        if row.get("player_name"):
            sessions[sid]["players"].add(row["player_name"])
        sessions[sid]["rounds"].add(row.get("round_number"))

    return [
        {
            "session_id": sid,
            "game_name": sessions[sid]["game_name"],
            "timestamp": sessions[sid]["timestamp"],
            "num_players": len(sessions[sid]["players"]),
            "num_rounds": len(sessions[sid]["rounds"]),
        }
        for sid in reversed(order)
    ]


def get_session_rounds(wb: gspread.Spreadsheet, session_id: str) -> list[dict]:
    """Raw games_played rows for one session, sorted by round number."""
    ws = _get_or_create_worksheet(wb, "games_played", _GAMES_PLAYED_HEADER)
    rows = _get_records(wb, ws, "games_played")
    matching = [r for r in rows if str(r.get("session_id")) == str(session_id)]
    matching.sort(key=lambda r: int(r.get("round_number") or 0))
    return matching