teecup/app/hole_history.py

"""
Spillerens per-hull-historikk (ADR-072, migrasjon 075/ADR-071-tillegg):
"hvordan spiller jeg egentlig dette hullet?" -- aggregerer ALLE tidligere
ganger en spiller har spilt et gitt hull, på tvers av BÅDE frittstående
runder OG org-turneringer (på tvers av ALLE organisasjoner spilleren har
spilt turnering i), begrenset til ordinære (teeoff-/GolfAPI-importerte)
baner. Custom/håndlagde baner har ingen pålitelig felles identitet på
tvers av de to systemene og telles BEVISST ikke med (bekreftet med
bruker via AskUserQuestion).

Delt mellom rounds.py (frittstående kontekst) og individual_tournaments.py
(turnering-kontekst) -- begge kaller inn hit og får samme KOMBINERTE
historikk uansett hvilken side spørringen kom fra ("alle gangene" betyr
alle gangene, ikke bare de av typen man akkurat nå ser på).

Bane-bro mellom de to identitetssystemene (se ADR-072 for utledningen):
  - Teeoff: round.teeoff_facility_slug + round.teeoff_course_id  <->
    course.external_course_ref = f"{facility_slug}:{teeoff_course_id}"
    (course.source = 'official') -- eksakt samme streng-format som
    import_official_course() i courses.py bygger den med.
  - GolfAPI: personal_course.external_golfapi_course_id  <->
    course.external_course_ref (course.source = 'international') --
    begge lagrer samme rå golfapi_course_id, uten prefiks.
Custom personal_course (external_golfapi_course_id IS NULL) og custom
course (source = 'custom') har INGEN bro her -- ekskluderes (resolve-
funksjonene returnerer None, kallerne skjuler da historikk-panelet).

`CourseKey`/de tre `resolve_*_course_key()`-funksjonene gjenbrukes også av
`app/target_points.py` (migrasjon 079, rangefinder for offisielle baner)
-- samme bane-bro, annet formål (koordinater, ikke historikk). Tredje
søster `resolve_match_course_key()` (lag-matchplay: match->session->
course) har INGEN egen hole-history-bruker ennå (kun ADR-072s to
flyter), men følger identisk mønster for symmetriens skyld.

GIR/fairway-formlene speiler round-stats.tsx/rounds.py sin ETABLERTE
`score - putts <= par - 2`-definisjon (IKKE skjema-kommentaren i
020_personal_rounds.sql, som beskriver en avvikende, ubrukt formel --
se ADR-071/ADR-033 Beslutning B for hvorfor avviket er bevisst).
"""

from __future__ import annotations

from dataclasses import dataclass
from datetime import date
from typing import Literal

from asyncpg import Connection
from pydantic import BaseModel

from .db import org_connection, plain_connection

CourseKey = tuple[Literal["teeoff"], str, str] | tuple[Literal["golfapi"], str]


async def resolve_personal_round_course_key(conn: Connection, round_id: str) -> CourseKey | None:
    row = await conn.fetchrow(
        """
        SELECT course_source, teeoff_facility_slug, teeoff_course_id, personal_course_id::text AS personal_course_id
        FROM round WHERE id = $1
        """,
        round_id,
    )
    if row is None:
        return None
    if row["course_source"] == "teeoff":
        return ("teeoff", row["teeoff_facility_slug"], row["teeoff_course_id"])
    golfapi_id = await conn.fetchval(
        "SELECT external_golfapi_course_id FROM personal_course WHERE id = $1",
        row["personal_course_id"],
    )
    if golfapi_id is None:
        return None
    return ("golfapi", golfapi_id)


async def resolve_tournament_round_course_key(conn: Connection, tournament_round_id: str) -> CourseKey | None:
    row = await conn.fetchrow(
        """
        SELECT c.source, c.external_course_ref
        FROM tournament_round tr
        JOIN course c ON c.id = tr.course_id
        WHERE tr.id = $1
        """,
        tournament_round_id,
    )
    if row is None or row["source"] == "custom" or row["external_course_ref"] is None:
        return None
    if row["source"] == "official":
        facility_slug, _, teeoff_course_id = row["external_course_ref"].partition(":")
        return ("teeoff", facility_slug, teeoff_course_id)
    return ("golfapi", row["external_course_ref"])


async def resolve_match_course_key(conn: Connection, match_id: str) -> CourseKey | None:
    row = await conn.fetchrow(
        """
        SELECT c.source, c.external_course_ref
        FROM match m
        JOIN session s ON s.id = m.session_id
        JOIN course c ON c.id = s.course_id
        WHERE m.id = $1
        """,
        match_id,
    )
    if row is None or row["source"] == "custom" or row["external_course_ref"] is None:
        return None
    if row["source"] == "official":
        facility_slug, _, teeoff_course_id = row["external_course_ref"].partition(":")
        return ("teeoff", facility_slug, teeoff_course_id)
    return ("golfapi", row["external_course_ref"])


@dataclass
class HoleInstance:
    source: Literal["personal", "tournament"]
    played_at: date | None
    label: str
    score: int
    par: int
    putts: int | None
    tee_shot_result: str | None
    approach_result: str | None
    chip_count: int | None
    bunker_shot_count: int | None
    penalty_strokes: int | None
    first_putt_distance_bucket: str | None


async def fetch_personal_hole_history(user_id: str, key: CourseKey, hole_number: int) -> list[HoleInstance]:
    async with plain_connection() as conn:
        if key[0] == "teeoff":
            _, facility_slug, teeoff_course_id = key
            rows = await conn.fetch(
                """
                SELECT r.played_at, r.name, rh.score, rh.par, rh.putts, rh.tee_shot_result,
                       rh.approach_result, rh.chip_count, rh.bunker_shot_count,
                       rh.penalty_strokes, rh.first_putt_distance_bucket
                FROM round_hole rh
                JOIN round_participant rp ON rp.id = rh.round_participant_id
                JOIN round r ON r.id = rp.round_id
                WHERE rp.user_id = $1 AND rh.hole_number = $2 AND rh.played = true
                  AND rh.score IS NOT NULL
                  AND r.course_source = 'teeoff'
                  AND r.teeoff_facility_slug = $3 AND r.teeoff_course_id = $4
                ORDER BY r.played_at DESC
                """,
                user_id, hole_number, facility_slug, teeoff_course_id,
            )
        else:
            _, golfapi_id = key
            rows = await conn.fetch(
                """
                SELECT r.played_at, r.name, rh.score, rh.par, rh.putts, rh.tee_shot_result,
                       rh.approach_result, rh.chip_count, rh.bunker_shot_count,
                       rh.penalty_strokes, rh.first_putt_distance_bucket
                FROM round_hole rh
                JOIN round_participant rp ON rp.id = rh.round_participant_id
                JOIN round r ON r.id = rp.round_id
                JOIN personal_course pc ON pc.id = r.personal_course_id
                WHERE rp.user_id = $1 AND rh.hole_number = $2 AND rh.played = true
                  AND rh.score IS NOT NULL
                  AND r.course_source = 'custom' AND pc.external_golfapi_course_id = $3
                ORDER BY r.played_at DESC
                """,
                user_id, hole_number, golfapi_id,
            )
    return [
        HoleInstance(
            source="personal",
            played_at=r["played_at"],
            label=r["name"] or "Runde",
            score=r["score"],
            par=r["par"],
            putts=r["putts"],
            tee_shot_result=r["tee_shot_result"],
            approach_result=r["approach_result"],
            chip_count=r["chip_count"],
            bunker_shot_count=r["bunker_shot_count"],
            penalty_strokes=r["penalty_strokes"],
            first_putt_distance_bucket=r["first_putt_distance_bucket"],
        )
        for r in rows
    ]


async def fetch_tournament_hole_history(user_id: str, key: CourseKey, hole_number: int) -> list[HoleInstance]:
    """N+1-per-org (samme mønster som /auth/me): tournament_round_hole er
    org-scopet/RLS-beskyttet, og spilleren kan ha spilt turnering i flere
    organisasjoner -- ett enkelt org_connection()-kall kan derfor aldri
    dekke alle. `player_organizations_for_user()` (migrasjon 015, SAMME
    SECURITY DEFINER-bro /auth/me allerede bruker) gir org-listen trygt
    uten en RLS-omgåelse i selve spørringen."""
    if key[0] == "teeoff":
        _, facility_slug, teeoff_course_id = key
        source_filter = "official"
        external_ref = f"{facility_slug}:{teeoff_course_id}"
    else:
        _, golfapi_id = key
        source_filter = "international"
        external_ref = golfapi_id

    async with plain_connection() as conn:
        org_rows = await conn.fetch(
            "SELECT organization_id::text AS organization_id FROM player_organizations_for_user($1)",
            user_id,
        )

    instances: list[HoleInstance] = []
    for org_row in org_rows:
        async with org_connection(org_row["organization_id"]) as conn:
            rows = await conn.fetch(
                """
                SELECT t.name AS tournament_name, tr.scheduled_at,
                       trh.gross_strokes AS score, h.par, trh.putts, trh.tee_shot_result,
                       trh.approach_result, trh.chip_count, trh.bunker_shot_count,
                       trh.penalty_strokes, trh.first_putt_distance_bucket
                FROM tournament_round_hole trh
                JOIN tournament_round_participant trp ON trp.id = trh.tournament_round_participant_id
                JOIN tournament_round tr ON tr.id = trp.tournament_round_id
                JOIN tournament t ON t.id = tr.tournament_id
                JOIN tournament_participant tp ON tp.id = trp.tournament_participant_id
                JOIN player p ON p.id = tp.player_id
                JOIN course c ON c.id = tr.course_id
                JOIN hole h ON h.course_id = tr.course_id AND h.hole_number = trh.hole_number
                WHERE p.user_id = $1 AND trh.hole_number = $2
                  AND c.source = $3 AND c.external_course_ref = $4
                ORDER BY tr.scheduled_at DESC NULLS LAST
                """,
                user_id, hole_number, source_filter, external_ref,
            )
        for r in rows:
            instances.append(
                HoleInstance(
                    source="tournament",
                    played_at=r["scheduled_at"].date() if r["scheduled_at"] is not None else None,
                    label=r["tournament_name"] or "Turnering",
                    score=r["score"],
                    par=r["par"],
                    putts=r["putts"],
                    tee_shot_result=r["tee_shot_result"],
                    approach_result=r["approach_result"],
                    chip_count=r["chip_count"],
                    bunker_shot_count=r["bunker_shot_count"],
                    penalty_strokes=r["penalty_strokes"],
                    first_putt_distance_bucket=r["first_putt_distance_bucket"],
                )
            )
    return instances


class HoleHistoryInstanceOut(BaseModel):
    source: Literal["personal", "tournament"]
    played_at: date | None
    label: str
    score: int
    par: int
    putts: int | None
    tee_shot_result: str | None
    approach_result: str | None
    chip_count: int | None
    bunker_shot_count: int | None
    penalty_strokes: int | None
    first_putt_distance_bucket: str | None


class HoleHistoryOut(BaseModel):
    times_played: int
    times_personal: int
    times_tournament: int
    average_score: float
    average_score_vs_par: float
    # None = ingen av instansene har putter registrert (rene strokes_only-
    # historikk) -- IKKE det samme som 0%.
    gir_percent: float | None
    # Kun meningsfullt for par 4/5 -- None hvis ingen slike instanser har
    # utslagsretning registrert.
    fairway_hit_percent: float | None
    average_putts: float | None
    best_score: int
    worst_score: int
    instances: list[HoleHistoryInstanceOut]


def summarize_hole_history(instances: list[HoleInstance]) -> HoleHistoryOut | None:
    if not instances:
        return None

    ordered = sorted(instances, key=lambda i: i.played_at or date.min, reverse=True)
    times_played = len(ordered)
    times_personal = sum(1 for i in ordered if i.source == "personal")
    scores = [i.score for i in ordered]

    gir_rows = [i for i in ordered if i.putts is not None]
    fairway_rows = [i for i in ordered if i.par != 3 and i.tee_shot_result is not None]
    putts_values = [i.putts for i in ordered if i.putts is not None]

    return HoleHistoryOut(
        times_played=times_played,
        times_personal=times_personal,
        times_tournament=times_played - times_personal,
        average_score=sum(scores) / times_played,
        average_score_vs_par=sum(i.score - i.par for i in ordered) / times_played,
        gir_percent=(
            100 * sum(1 for i in gir_rows if i.score - i.putts <= i.par - 2) / len(gir_rows)
            if gir_rows
            else None
        ),
        fairway_hit_percent=(
            100 * sum(1 for i in fairway_rows if i.tee_shot_result == "fairway") / len(fairway_rows)
            if fairway_rows
            else None
        ),
        average_putts=sum(putts_values) / len(putts_values) if putts_values else None,
        best_score=min(scores),
        worst_score=max(scores),
        instances=[
            HoleHistoryInstanceOut(
                source=i.source,
                played_at=i.played_at,
                label=i.label,
                score=i.score,
                par=i.par,
                putts=i.putts,
                tee_shot_result=i.tee_shot_result,
                approach_result=i.approach_result,
                chip_count=i.chip_count,
                bunker_shot_count=i.bunker_shot_count,
                penalty_strokes=i.penalty_strokes,
                first_putt_distance_bucket=i.first_putt_distance_bucket,
            )
            for i in ordered
        ],
    )


async def hole_history_for_user(user_id: str, key: CourseKey | None, hole_number: int) -> HoleHistoryOut | None:
    """Toppnivå-inngang brukt av begge routerne: løser INGENTING selv (nøkkel
    må allerede være resolvet av kalleren, som kjenner sin egen kontekst) --
    henter fra begge kilder og slår sammen. Returnerer None både når nøkkelen
    er None (custom bane) OG når spilleren ikke har noen historikk ennå --
    kalleren skiller ikke mellom disse to, begge betyr "ikke vis panelet".
    """
    if key is None:
        return None
    personal = await fetch_personal_hole_history(user_id, key, hole_number)
    tournament = await fetch_tournament_hole_history(user_id, key, hole_number)
    return summarize_hole_history(personal + tournament)