warringtond/sports-division
Template
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
master
sports-division/test-data.sql
warringtond ddb2d58e2f many bug fixes
2026-06-27 13:14:39 -04:00

353 lines
18 KiB
PL/PgSQL
Raw Permalink Blame History

-- =============================================================================
-- test-data.sql
-- Bulk sample data for the Dominica Sports Division app.
--
-- Adds, for BOTH seeded tournaments (1 = Inter-Zone Championship 2026,
-- 2 = National Championship 2026):
-- * 60 students (30 male / 30 female) spread across the secondary schools
-- * Under-16 Boys & Under-16 Girls event levels for EVERY individual
-- (non-relay) event in the catalogue
-- * ~16 registrations per event level
-- * Results recorded in the form each discipline actually uses:
-- - Field events: a Score per athlete (mark -> WA points, placement, points)
-- - High jump: a per-bar attempt grid (clears / fails), then a Score
-- - Track: a preliminary Heat round (heats of <= 8) feeding an
-- 8-athlete Final; placement points are awarded ONLY from
-- the final.
--
-- Marks are VARIED per event level (a deterministic per-registration jitter and
-- a per-level base offset) so no two competitions look identical.
--
-- The students / event levels / registrations are inserted idempotently. The
-- scoring artifacts (Scores, heats, heights, attempts) are rebuilt on every run
-- (deterministically), so re-running reproduces the same data -- but any manual
-- scoring done on these U16 test event levels via the UI will be overwritten.
--
-- Field / jump marks are in METRES. Relays are skipped (they need RelayTeams).
--
-- Run with:
-- PGPASSWORD=... psql -h localhost -U postgres -d sportsdivision -f test-data.sql
-- =============================================================================
BEGIN;
-- ---------------------------------------------------------------------------
-- Single source of truth: every individual event to populate, with the base
-- mark for the winner and the step between consecutive places.
-- is_track = true -> seconds, lower is better
-- is_track = false -> metres, higher is better
-- ---------------------------------------------------------------------------
CREATE TEMP TABLE _ev (ename text, base numeric, step numeric, is_track boolean) ON COMMIT DROP;
INSERT INTO _ev (ename, base, step, is_track) VALUES
-- Track (seconds)
('80m', 10.00, 0.15, true),
('100m', 11.50, 0.18, true),
('150m', 17.50, 0.25, true),
('200m', 23.50, 0.30, true),
('300m', 38.00, 0.45, true),
('400m', 52.00, 0.70, true),
('800m', 125.00, 1.50, true),
('1200m', 200.00, 2.50, true),
('1500m', 255.00, 3.00, true),
('3000m', 560.00, 6.00, true),
('5000m', 980.00, 10.0, true),
('80mH', 13.00, 0.25, true),
-- Field (metres)
('Long Jump', 6.40, 0.15, false),
('Triple Jump', 13.00, 0.30, false),
('Shot Put 3kg', 12.50, 0.40, false),
('Shot Put 4kg', 11.50, 0.38, false),
('Shot Put 5kg', 10.50, 0.35, false),
('Shot Put 6kg', 9.50, 0.33, false),
('Discus 1kg', 38.00, 1.20, false),
('Discus 1.25kg', 34.00, 1.10, false),
('Discus 1.5kg', 30.00, 1.00, false),
('Discus 1.75kg', 27.00, 0.90, false),
('Javelin 400g', 42.00, 1.50, false),
('Javelin 500g', 48.00, 1.60, false),
('Javelin 600g', 45.00, 1.50, false),
('Javelin 700g', 40.00, 1.40, false),
('Javelin 800g', 38.00, 1.30, false),
('Throwing the Cricket Ball', 65.00, 2.00, false),
-- High jump (metres)
('High Jump', 1.85, 0.04, false);
-- ---------------------------------------------------------------------------
-- 1. STUDENTS (60: TD-0001 .. TD-0060; first 30 Male, next 30 Female)
-- ---------------------------------------------------------------------------
WITH secids AS (
SELECT array_agg("SchoolId" ORDER BY "SchoolId") AS ids, count(*)::int AS c
FROM "Schools"
WHERE "SchoolLevel" = 'Secondary' AND "IsActive"
)
INSERT INTO "Students"
("ExistingStudentId", "FirstName", "LastName", "DateOfBirth", "Sex", "SchoolId", "IsActive")
SELECT
'TD-' || lpad(g::text, 4, '0'),
(ARRAY['Liam','Noah','Ethan','Mason','Logan','Lucas','Jack','Aiden','Caleb','Isaac',
'Ava','Mia','Zoe','Lily','Emma','Sofia','Maya','Chloe','Ella','Grace'])[1 + (g % 20)],
(ARRAY['Joseph','Charles','Baptiste','Pierre','Williams','John','Thomas','James','Henry','George',
'Edwards','Francis','Lewis','Daniel','Peters','Roberts','Andrew','Paul','Mark','Joseph'])[1 + ((g * 7) % 20)],
make_date(2010 + (g % 2), 1 + (g % 12), 1 + (g % 27)),
CASE WHEN g <= 30 THEN 'Male' ELSE 'Female' END,
secids.ids[1 + (g % secids.c)],
true
FROM generate_series(1, 60) AS g
CROSS JOIN secids
WHERE NOT EXISTS (
SELECT 1 FROM "Students" s WHERE s."ExistingStudentId" = 'TD-' || lpad(g::text, 4, '0')
);
-- ---------------------------------------------------------------------------
-- 2. TOURNAMENT EVENT LEVELS (every _ev event x U16 Boys/Girls x 2 tournaments)
-- Age restriction waived so the U16 sample athletes are always eligible.
-- ---------------------------------------------------------------------------
WITH combos AS (
SELECT t.tid, e."EventId" AS eid, el."EventLevelId" AS lid
FROM (VALUES (1), (2)) AS t(tid)
CROSS JOIN _ev
CROSS JOIN (VALUES ('Under 16 Boys'), ('Under 16 Girls')) AS lv(name)
JOIN "Events" e ON e."Name" = _ev.ename
JOIN "EventLevels" el ON el."Name" = lv.name
)
INSERT INTO "TournamentEventLevels"
("TournamentId", "EventId", "EventLevelId", "AgeRestrictionWaived")
SELECT c.tid, c.eid, c.lid, true
FROM combos c
WHERE NOT EXISTS (
SELECT 1 FROM "TournamentEventLevels" x
WHERE x."TournamentId" = c.tid AND x."EventId" = c.eid AND x."EventLevelId" = c.lid
);
-- ---------------------------------------------------------------------------
-- 3. REGISTRATIONS (~16 sample students per event level, sex-matched)
-- ---------------------------------------------------------------------------
WITH testtels AS (
SELECT tel."TournamentEventLevelId" AS tid, el."Sex" AS sex
FROM "TournamentEventLevels" tel
JOIN "EventLevels" el ON el."EventLevelId" = tel."EventLevelId"
JOIN "Events" e ON e."EventId" = tel."EventId"
WHERE tel."TournamentId" IN (1, 2)
AND el."Name" IN ('Under 16 Boys', 'Under 16 Girls')
AND e."Name" IN (SELECT ename FROM _ev)
),
studs AS (
SELECT "StudentId", "Sex",
row_number() OVER (PARTITION BY "Sex" ORDER BY "StudentId") AS rn
FROM "Students"
WHERE "ExistingStudentId" LIKE 'TD-%'
)
INSERT INTO "EventRegistrations"
("TournamentEventLevelId", "StudentId", "RegisteredBy", "RegisteredAt")
SELECT t.tid, s."StudentId", 'seed@sportsdivision.dm', TIMESTAMP WITH TIME ZONE '2026-06-01 09:00:00+00'
FROM testtels t
JOIN studs s ON s."Sex" = t.sex
WHERE ((s.rn + t.tid) % 30) < 16 -- 16 of the 30 per level, varied per TEL
AND NOT EXISTS (
SELECT 1 FROM "EventRegistrations" r
WHERE r."TournamentEventLevelId" = t.tid AND r."StudentId" = s."StudentId"
);
-- ===========================================================================
-- SCORING (rebuilt from scratch each run for the U16 test event levels)
-- ===========================================================================
-- The test event levels we own (U16 Boys/Girls, both tournaments, _ev events).
CREATE TEMP TABLE _tel ON COMMIT DROP AS
SELECT tel."TournamentEventLevelId" AS tid, e."EventId" AS eid, e."Name" AS ename, e."Category" AS cat
FROM "TournamentEventLevels" tel
JOIN "EventLevels" el ON el."EventLevelId" = tel."EventLevelId"
JOIN "Events" e ON e."EventId" = tel."EventId"
WHERE tel."TournamentId" IN (1, 2)
AND el."Name" IN ('Under 16 Boys', 'Under 16 Girls')
AND e."Name" IN (SELECT ename FROM _ev);
-- Clear any existing scoring artifacts for those event levels (FK-safe order).
DELETE FROM "HeatLanes" WHERE "HeatId" IN (
SELECT h."HeatId" FROM "Heats" h JOIN "Rounds" r ON r."RoundId" = h."RoundId"
WHERE r."TournamentEventLevelId" IN (SELECT tid FROM _tel));
DELETE FROM "Heats" WHERE "RoundId" IN (
SELECT "RoundId" FROM "Rounds" WHERE "TournamentEventLevelId" IN (SELECT tid FROM _tel));
DELETE FROM "Rounds" WHERE "TournamentEventLevelId" IN (SELECT tid FROM _tel);
DELETE FROM "HighJumpAttempts" WHERE "HighJumpHeightId" IN (
SELECT "HighJumpHeightId" FROM "HighJumpHeights" WHERE "TournamentEventLevelId" IN (SELECT tid FROM _tel));
DELETE FROM "HighJumpHeights" WHERE "TournamentEventLevelId" IN (SELECT tid FROM _tel);
DELETE FROM "Scores" WHERE "EventRegistrationId" IN (
SELECT er."EventRegistrationId" FROM "EventRegistrations" er WHERE er."TournamentEventLevelId" IN (SELECT tid FROM _tel));
-- Per-athlete performance, VARIED: a per-level base offset (+/-6% by tid) and a
-- deterministic per-registration jitter shuffle finishing order and marks, so
-- different athletes win different events and no two competitions are identical.
CREATE TEMP TABLE _perf ON COMMIT DROP AS
WITH b AS (
SELECT er."EventRegistrationId" AS erid, t.tid, t.eid, t.ename, t.cat,
p.base, p.step, p.is_track,
(p.base * (1 + (((t.tid % 7) - 3) * 0.02))) AS base_v,
((er."EventRegistrationId"::bigint * 48271) % 100000) AS seed,
((((er."EventRegistrationId"::bigint * 1103515245 + 12345) % 1000)::numeric / 1000.0) - 0.5) AS jit
FROM _tel t
JOIN "EventRegistrations" er ON er."TournamentEventLevelId" = t.tid AND er."StudentId" IS NOT NULL
JOIN _ev p ON p.ename = t.ename
),
o AS (SELECT *, row_number() OVER (PARTITION BY tid ORDER BY seed) AS pos0 FROM b),
m AS (
SELECT *,
CASE WHEN is_track THEN base_v + step * (pos0 - 1) + jit * step
ELSE base_v - step * (pos0 - 1) + jit * step END AS raw0
FROM o
)
SELECT erid, tid, eid, ename, cat, is_track, jit,
round(raw0::numeric, 2) AS raw,
row_number() OVER (PARTITION BY tid ORDER BY CASE WHEN is_track THEN raw0 ELSE -raw0 END) AS pos,
count(*) OVER (PARTITION BY tid) AS n
FROM m;
-- ---------------------------------------------------------------------------
-- 4a. FIELD events (single competition; everyone placed, top 8 score points)
-- ---------------------------------------------------------------------------
INSERT INTO "Scores"
("EventRegistrationId", "RawPerformance", "CalculatedPoints", "Placement", "PlacementPoints", "RecordedBy", "RecordedAt")
SELECT pf.erid, pf.raw,
COALESCE(CASE WHEN sc."A" IS NOT NULL AND (pf.raw - sc."B") > 0
THEN floor(sc."A" * power(pf.raw - sc."B", sc."C"))::int ELSE 0 END, 0),
pf.pos, COALESCE(ppc."Points", 0),
'seed@sportsdivision.dm', TIMESTAMP WITH TIME ZONE '2026-06-02 14:00:00+00'
FROM _perf pf
LEFT JOIN "ScoringConstants" sc ON sc."EventId" = pf.eid
LEFT JOIN "PlacementPointConfigs" ppc ON ppc."Placement" = pf.pos
WHERE pf.cat = 'Field' AND pf.raw > 0;
-- ---------------------------------------------------------------------------
-- 4b. HIGH JUMP (per-level bar ladder, jittered clearances with ties)
-- ---------------------------------------------------------------------------
CREATE TEMP TABLE _hj ON COMMIT DROP AS
SELECT pf.erid, pf.tid, pf.eid, pf.pos,
(1.30 + (pf.tid % 6) * 0.05)::numeric AS start_h,
GREATEST(0, LEAST(7,
(round((pf.n - pf.pos)::numeric / pf.n * 7) + round(pf.jit * 2))::int)) AS cidx
FROM _perf pf
WHERE pf.cat = 'HighJump';
-- bar ladder: 8 bars at 0.05 spacing from the per-level start height
INSERT INTO "HighJumpHeights" ("TournamentEventLevelId", "Height", "SortOrder")
SELECT s.tid, round((s.start_h + 0.05 * k)::numeric, 2), k + 1
FROM (SELECT DISTINCT tid, start_h FROM _hj) s
CROSS JOIN generate_series(0, 7) AS k;
-- attempts: clear every bar up to the athlete's height, fail (XXX) the next one
INSERT INTO "HighJumpAttempts" ("HighJumpHeightId", "EventRegistrationId", "Attempt1", "Attempt2", "Attempt3")
SELECT hh."HighJumpHeightId", a.erid,
CASE WHEN hh."Height" <= a.cleared THEN 'Clear' ELSE 'Fail' END,
CASE WHEN hh."Height" > a.cleared THEN 'Fail' END,
CASE WHEN hh."Height" > a.cleared THEN 'Fail' END
FROM (SELECT erid, tid, round((start_h + 0.05 * cidx)::numeric, 2) AS cleared FROM _hj) a
JOIN "HighJumpHeights" hh
ON hh."TournamentEventLevelId" = a.tid
AND hh."Height" <= a.cleared + 0.05;
-- high jump scores: rank by cleared height (ties broken by finishing order)
INSERT INTO "Scores"
("EventRegistrationId", "RawPerformance", "CalculatedPoints", "Placement", "PlacementPoints", "RecordedBy", "RecordedAt")
SELECT z.erid, z.cleared,
COALESCE(CASE WHEN sc."A" IS NOT NULL AND (z.cleared - sc."B") > 0
THEN floor(sc."A" * power(z.cleared - sc."B", sc."C"))::int ELSE 0 END, 0),
z.place, COALESCE(ppc."Points", 0),
'seed@sportsdivision.dm', TIMESTAMP WITH TIME ZONE '2026-06-02 14:00:00+00'
FROM (
SELECT h.erid, h.eid, round((h.start_h + 0.05 * h.cidx)::numeric, 2) AS cleared,
row_number() OVER (PARTITION BY h.tid ORDER BY (h.start_h + 0.05 * h.cidx) DESC, h.pos ASC) AS place
FROM _hj h
) z
LEFT JOIN "ScoringConstants" sc ON sc."EventId" = z.eid
LEFT JOIN "PlacementPointConfigs" ppc ON ppc."Placement" = z.place;
-- ---------------------------------------------------------------------------
-- 4c. TRACK (prelim Heat round of <=8-lane heats -> 8-athlete Final)
-- Placement points are awarded only from the Final round.
-- ---------------------------------------------------------------------------
CREATE TEMP TABLE _trk ON COMMIT DROP AS
SELECT pf.erid, pf.tid, pf.eid, pf.pos, pf.n,
pf.raw AS prelim_time,
round((pf.raw * 0.985)::numeric, 2) AS final_time,
ceil(pf.n / 8.0)::int AS heats
FROM _perf pf
WHERE pf.cat = 'Track';
-- prelim round (Top 3 per heat + 2 fastest losers advance to the final)
INSERT INTO "Rounds" ("TournamentEventLevelId", "RoundType", "RoundOrder", "AdvanceTopN", "AdvanceFastestLosers", "Status")
SELECT DISTINCT tid, 'Heat', 1, 3, 2, 'Completed' FROM _trk;
-- final round
INSERT INTO "Rounds" ("TournamentEventLevelId", "RoundType", "RoundOrder", "AdvanceTopN", "AdvanceFastestLosers", "Status")
SELECT DISTINCT tid, 'Final', 2, NULL::int, NULL::int, 'Completed' FROM _trk;
-- prelim heats: ceil(n/8) heats so no heat exceeds 8 lanes
INSERT INTO "Heats" ("RoundId", "HeatNumber", "Status")
SELECT r."RoundId", hn.n, 'Completed'
FROM "Rounds" r
JOIN (SELECT DISTINCT tid, heats FROM _trk) d ON d.tid = r."TournamentEventLevelId"
CROSS JOIN LATERAL generate_series(1, d.heats) AS hn(n)
WHERE r."RoundOrder" = 1;
-- final heat (single heat of <= 8)
INSERT INTO "Heats" ("RoundId", "HeatNumber", "Status")
SELECT r."RoundId", 1, 'Completed'
FROM "Rounds" r
JOIN (SELECT DISTINCT tid FROM _trk) d ON d.tid = r."TournamentEventLevelId"
WHERE r."RoundOrder" = 2;
-- prelim lanes: round-robin athletes into the heats; fastest 8 overall advance
INSERT INTO "HeatLanes"
("HeatId", "EventRegistrationId", "LaneNumber", "Time", "IsAdvanced", "AdvanceReason",
"IsDNS", "IsDNF", "IsDQ", "RecordedBy", "RecordedAt")
SELECT h."HeatId", t.erid,
row_number() OVER (PARTITION BY h."HeatId" ORDER BY t.prelim_time),
t.prelim_time,
(t.pos <= 8),
CASE WHEN t.pos <= 6 THEN 'TopN' WHEN t.pos <= 8 THEN 'FastestLoser' ELSE NULL END,
false, false, false, 'seed@sportsdivision.dm', TIMESTAMP WITH TIME ZONE '2026-06-02 14:00:00+00'
FROM _trk t
JOIN "Rounds" r ON r."TournamentEventLevelId" = t.tid AND r."RoundOrder" = 1
JOIN "Heats" h ON h."RoundId" = r."RoundId" AND h."HeatNumber" = ((t.pos - 1) % t.heats) + 1;
-- final lanes: the 8 finalists, lane = final placement
INSERT INTO "HeatLanes"
("HeatId", "EventRegistrationId", "LaneNumber", "Time", "IsAdvanced", "AdvanceReason",
"IsDNS", "IsDNF", "IsDQ", "RecordedBy", "RecordedAt")
SELECT h."HeatId", t.erid, t.pos, t.final_time, false, NULL,
false, false, false, 'seed@sportsdivision.dm', TIMESTAMP WITH TIME ZONE '2026-06-02 14:00:00+00'
FROM _trk t
JOIN "Rounds" r ON r."TournamentEventLevelId" = t.tid AND r."RoundOrder" = 2
JOIN "Heats" h ON h."RoundId" = r."RoundId"
WHERE t.pos <= 8;
-- track scores: ONLY the 8 finalists, placement & points from the final
INSERT INTO "Scores"
("EventRegistrationId", "RawPerformance", "CalculatedPoints", "Placement", "PlacementPoints", "RecordedBy", "RecordedAt")
SELECT t.erid, t.final_time,
COALESCE(CASE WHEN sc."A" IS NOT NULL AND (sc."B" - t.final_time) > 0
THEN floor(sc."A" * power(sc."B" - t.final_time, sc."C"))::int ELSE 0 END, 0),
t.pos, COALESCE(ppc."Points", 0),
'seed@sportsdivision.dm', TIMESTAMP WITH TIME ZONE '2026-06-02 14:00:00+00'
FROM _trk t
LEFT JOIN "ScoringConstants" sc ON sc."EventId" = t.eid
LEFT JOIN "PlacementPointConfigs" ppc ON ppc."Placement" = t.pos
WHERE t.pos <= 8;
COMMIT;
-- ---------------------------------------------------------------------------
-- Summary (informational; safe to run repeatedly)
-- ---------------------------------------------------------------------------
SELECT 'students (TD-)' AS metric, count(*) AS value FROM "Students" WHERE "ExistingStudentId" LIKE 'TD-%'
UNION ALL SELECT 'event levels (t1)', count(*) FROM "TournamentEventLevels" WHERE "TournamentId" = 1
UNION ALL SELECT 'event levels (t2)', count(*) FROM "TournamentEventLevels" WHERE "TournamentId" = 2
UNION ALL SELECT 'registrations (total)', count(*) FROM "EventRegistrations"
UNION ALL SELECT 'scores (total)', count(*) FROM "Scores"
UNION ALL SELECT 'track heat lanes', count(*) FROM "HeatLanes"
UNION ALL SELECT 'high jump heights', count(*) FROM "HighJumpHeights"
UNION ALL SELECT 'high jump attempts', count(*) FROM "HighJumpAttempts"
UNION ALL SELECT 'individual events scored',
count(DISTINCT tel."EventId")
FROM "Scores" s
JOIN "EventRegistrations" er ON er."EventRegistrationId" = s."EventRegistrationId"
JOIN "TournamentEventLevels" tel ON tel."TournamentEventLevelId" = er."TournamentEventLevelId";
Reference in New Issue View Git Blame Copy Permalink