1bdd5c3372
- SnookerSimulator: Monte Carlo simulation of the 32-player World Championship bracket using per-frame Bernoulli win probabilities derived from Elo ratings (ELO_DIVISOR=700). Two paths: bracket- populated (respects completed matches) and pre-bracket (simulates qualifying, then seeds full draw). - Admin route /sports-seasons/:id/elo-ratings: bulk-import and per- player Elo entry with fuzzy name matching; saves ratings and auto- runs the simulation in one action. - schema: add snooker_bracket simulator type, source_elo column on participant_expected_values, unique index on (participant_id, sports_season_id). - Fix batchSaveSourceElos to use INSERT ... ON CONFLICT DO UPDATE instead of N+1 SELECT+UPDATE loop. - Fix existingElos returned as plain Record (Map doesn't survive JSON serialization through useLoaderData). - Fix "How It Works" formula to show correct divisor (700, not 400). Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
15 lines
702 B
SQL
15 lines
702 B
SQL
-- Remove duplicate participant_expected_values rows before adding unique constraint.
|
|
-- For each (participant_id, sports_season_id) group with duplicates, keep the row
|
|
-- that has a non-null source_elo (preferred) or the most recently updated row.
|
|
DELETE FROM participant_expected_values
|
|
WHERE id NOT IN (
|
|
SELECT DISTINCT ON (participant_id, sports_season_id) id
|
|
FROM participant_expected_values
|
|
ORDER BY participant_id, sports_season_id,
|
|
(source_elo IS NOT NULL) DESC,
|
|
updated_at DESC
|
|
);
|
|
|
|
-- Now add unique constraint to prevent future duplicates.
|
|
CREATE UNIQUE INDEX "participant_ev_participant_season_unique"
|
|
ON "participant_expected_values" ("participant_id", "sports_season_id");
|