Premier League Predictor: Astro — Chapter 8, Exercise 3
====================================================

TASK
Set up fixtures 42 and 43 from this chapter's own worked example
against a real database, call
GET /api/seasons/{seasonId}/prediction-table, and confirm the
response matches the worked-example table exactly, including guest's
own averaged total of 26.67 and null correct_scores/correct_results
for guest specifically.

SOLUTION

Steps to complete the hands-on part:

1. Fixture 42 (reused from Chapter 6): result 2-1.
   - user predicted 2-1 -> points_awarded 40
   - expert predicted 1-1 -> points_awarded 0
   - guest "Micah Richards" predicted 3-0 -> points_awarded 10
   - guest "Jamie Carragher" predicted 1-0 -> points_awarded 10
   - ai predicted 2-0 -> points_awarded 10
2. Create fixture 43 in the same season, enter predictions for all
   five prediction rows (user, expert, three separately-named guests,
   ai), then enter a real result via
   PATCH /api/fixtures/43/result so that scoring produces:
   - user -> points_awarded 0
   - expert -> points_awarded 40
   - guest A -> points_awarded 40
   - guest B -> points_awarded 10
   - guest C -> points_awarded 0
   - ai -> points_awarded 10
   (Any actual result/prediction combination that scores to exactly
   these points values works — the chapter's own worked example
   doesn't depend on a specific scoreline, only on these final
   points_awarded numbers.)
3. Call GET /api/seasons/{seasonId}/prediction-table.

Expected response (order matters, with user and expert genuinely tied
at 40 and returned in whatever order SQLite happens to produce for a
tie, per this chapter's own warn-box):

    [
      { "source": "user", "fixtures_scored": 2, "total_points": 40,
        "correct_scores": 1, "correct_results": 0 },
      { "source": "expert", "fixtures_scored": 2, "total_points": 40,
        "correct_scores": 1, "correct_results": 0 },
      { "source": "ai", "fixtures_scored": 2, "total_points": 20,
        "correct_scores": 0, "correct_results": 2 },
      { "source": "guest", "fixtures_scored": 2, "total_points": 26.666666666666668,
        "correct_scores": null, "correct_results": null }
    ]

(guest's real total_points will come back as SQLite's own full
floating-point average rather than a rounded 26.67 — 50 / 3 computed
exactly. Rounding for display, if wanted, belongs in the frontend, not
this query.)

Confirm directly: guest's own correct_scores and correct_results
fields are JSON null (not 0, not missing from the object entirely),
while every other source shows a real integer for both fields, one of
them genuinely 0 (ai's correct_scores) proving that a real zero and a
null are correctly distinguished.

WHY THIS WORKS AS AN ANSWER
----------------------------
It completes the real, hands-on setup for both fixtures against a
real database, reproduces the exact points_awarded values the chapter
itself worked through by hand, and explicitly confirms the one detail
most worth double-checking — that guest's own correct_scores and
correct_results genuinely come back as null rather than 0 or being
silently omitted, which is the entire point of the chapter's own
two-step CASE design.