1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
|
\set database_name teamdraft
\set schema :database_name
\set owner :database_name
\set rw_name :database_name '_website'
\set ro_name :database_name '_ro'
DROP ROLE IF EXISTS ddl;
CREATE ROLE ddl WITH NOLOGIN;
DROP ROLE IF EXISTS dml;
CREATE ROLE dml WITH NOLOGIN;
DROP ROLE IF EXISTS READ_ONLY;
CREATE ROLE READ_ONLY WITH NOLOGIN;
DROP SCHEMA IF EXISTS :schema CASCADE;
DROP DATABASE IF EXISTS :database_name;
DROP ROLE IF EXISTS :owner;
CREATE ROLE :owner WITH LOGIN PASSWORD :'owner';
GRANT ddl TO :owner;
DROP ROLE IF EXISTS :rw_name;
CREATE ROLE :rw_name WITH LOGIN PASSWORD :'rw_name';
GRANT dml TO :rw_name;
DROP ROLE IF EXISTS :ro_name;
CREATE ROLE :ro_name WITH LOGIN PASSWORD :'ro_name';
GRANT READ_ONLY TO :ro_name;
ALTER ROLE :owner SET se arch_path TO :schema, pgsodium, public;
ALTER ROLE :rw_name SET search_path TO :schema, pgsodium, public;
ALTER ROLE :ro_name SET search_path TO :schema, pgsodium, public;
CREATE DATABASE :database_name OWNER :owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE :database_name FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, READ_ONLY;
\c :database_name
CREATE EXTENSION pgsodium;
GRANT EXECUTE ON ROUTINE pgsodium.randombytes_random TO :owner, :rw_name, :ro_name;
SET ROLE :owner;
CREATE SCHEMA :schema AUTHORIZATION :schema;
GRANT USAGE, CREATE ON SCHEMA :schema TO ddl;
GRANT USAGE ON SCHEMA :schema TO dml, READ_ONLY;
GRANT ALL ON SCHEMA pgsodium TO dml;
REVOKE ALL ON DATABASE :database_name FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, READ_ONLY;
ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT
SELECT
, INSERT, UPDATE, DELETE ON TABLES TO dml;
ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT
SELECT
ON TABLES TO READ_ONLY;
ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT USAGE,
SELECT
ON SEQUENCES TO dml, READ_ONLY;
ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT UPDATE ON SEQUENCES TO dml;
ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT EXECUTE ON ROUTINES TO dml, READ_ONLY;
CREATE TABLE :schema.users(
user_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
username text,
PASSWORD text,
name text,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT users_pk PRIMARY KEY (user_id)
);
CREATE TABLE sessions(
session_id text PRIMARY KEY DEFAULT to_hex(pgsodium.randombytes_random()),
user_id integer NOT NULL,
expires timestamptz NOT NULL DEFAULT now() + interval '1 year',
created timestamptz NOT NULL DEFAULT now(),
modified timestamptz NOT NULL DEFAULT now(),
CONSTRAINT sessions_users_fk FOREIGN KEY (user_id) REFERENCES users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TYPE :schema.divisions AS ENUM(
'nfcn',
'nfce',
'nfcs',
'nfcw',
'afcn',
'afce',
'afcs',
'afcw'
);
CREATE TABLE :schema.teams(
team_id smallint NOT NULL GENERATED ALWAYS AS IDENTITY,
name text NOT NULL,
division :schema.divisions NOT NULL,
external_id text,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT teams_pk PRIMARY KEY (team_id),
CONSTRAINT teams_name_ck CHECK (char_length(name) <= 255)
);
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Buffalo', 'afce', '134918');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Miami', 'afce', '134919');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('New England', 'afce', '134920');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('NYJ', 'afce', '134921');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Baltimore', 'afcn', '134922');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Cincinnati', 'afcn', '134923');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Cleveland', 'afcn', '134924');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Pittsburgh', 'afcn', '134925');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Houston', 'afcs', '134926');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Indianapolis', 'afcs', '134927');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Jacksonville', 'afcs', '134928');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Tennessee', 'afcs', '134929');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Denver', 'afcw', '134930');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Kansas City', 'afcw', '134931');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Las Vegas', 'afcw', '134932');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('LAC', 'afcw', '135908');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Dallas', 'nfce', '134934');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('NYG', 'nfce', '134935');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Philadelphia', 'nfce', '134936');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Washington', 'nfce', '134937');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Chicago', 'nfcn', '134938');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Detroit', 'nfcn', '134939');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Green Bay', 'nfcn', '134940');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Minnesota', 'nfcn', '134941');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Atlanta', 'nfcs', '134942');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Carolina', 'nfcs', '134943');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('New Orleans', 'nfcs', '134944');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Tampa Bay', 'nfcs', '134945');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Seattle', 'nfcw', '134949');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('San Francisco', 'nfcw', '134948');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('Arizona', 'nfcw', '134946');
INSERT INTO :schema.teams(name, division, external_id)
VALUES ('LAR', 'nfcw', '135907');
CREATE TABLE :schema.seasons(
season_id smallint NOT NULL GENERATED ALWAYS AS IDENTITY,
started_at timestamptz NOT NULL,
ended_at timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT seasons_pk PRIMARY KEY (season_id)
);
CREATE TABLE :schema.leagues(
league_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
name text,
user_id integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT leagues_pk PRIMARY KEY (league_id),
CONSTRAINT leagues_name_ck CHECK (char_length(name) <= 255),
CONSTRAINT leagues_users_fk FOREIGN KEY (user_id) REFERENCES :schema.users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE :schema.league_seasons(
league_season_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
season_id smallint NOT NULL,
league_id integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (season_id, league_id),
CONSTRAINT league_seasons_pk PRIMARY KEY (league_season_id),
CONSTRAINT league_seasons_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT league_seasons_leagues_fk FOREIGN KEY (league_id) REFERENCES :schema.leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE :schema.league_users(
league_user_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
league_season_id integer NOT NULL,
user_id integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT league_users_pk PRIMARY KEY (league_user_id),
CONSTRAINT league_users_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES :schema.league_seasons(league_season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT league_users_users_fk FOREIGN KEY (user_id) REFERENCES :schema.users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE :schema.rosters(
roster_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
league_user_id bigint NOT NULL,
team_id smallint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT rosters_pk PRIMARY KEY (roster_id),
CONSTRAINT rosters_league_users_fk FOREIGN KEY (league_user_id) REFERENCES :schema.league_users(league_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT rosters_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TYPE :schema.draft_status AS ENUM(
'created',
'running',
'complete'
);
CREATE TABLE :schema.drafts(
draft_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
league_season_id integer NOT NULL,
status :schema.draft_status NOT NULL DEFAULT 'created',
started_at timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT drafts_pk PRIMARY KEY (draft_id),
CONSTRAINT drafts_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES :schema.league_seasons(league_season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE :schema.picks(
pick_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
draft_id integer NOT NULL,
league_user_id integer NOT NULL,
team_id smallint NOT NULL,
auto boolean NOT NULL DEFAULT FALSE,
picked_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (draft_id, team_id),
CONSTRAINT picks_pk PRIMARY KEY (pick_id),
CONSTRAINT picks_drafts_fk FOREIGN KEY (draft_id) REFERENCES :schema.drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT picks_league_users_fk FOREIGN KEY (league_user_id) REFERENCES :schema.league_users(league_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT picks_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE :schema.rankings(
ranking_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
season_id integer NOT NULL,
team_id integer NOT NULL,
rank smallint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT rankings_pk PRIMARY KEY (ranking_id),
CONSTRAINT rankings_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TYPE :schema.score_category AS ENUM(
'win',
'playoffs',
'divisional',
'conference',
'superbowl',
'champion'
);
CREATE TABLE :schema.scores(
score_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
season_id integer NOT NULL,
team_id integer NOT NULL,
week smallint NOT NULL,
category :schema.score_category NOT NULL DEFAULT 'win',
scored_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (season_id, team_id, week, category),
CONSTRAINT scores_pk PRIMARY KEY (score_id),
CONSTRAINT scores_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT scores_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);
-- functions
CREATE OR REPLACE FUNCTION :schema.current_season()
RETURNS int
AS $$
SELECT
season_id
FROM
seasons
WHERE
started_at <= now()
AND ended_at > now()
LIMIT 1;
$$
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION season_id_by_year(year text)
RETURNS int
AS $$
SELECT
season_id
FROM
seasons
WHERE
year::integer >= date_part('year', started_at)
LIMIT 1;
$$
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION category_score(category score_category)
RETURNS smallint
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
BEGIN
RETURN(
SELECT
CASE WHEN category = 'win' THEN
1
WHEN category IN('superbowl', 'conference') THEN
10
ELSE
5
END);
END;
$$;
CREATE OR REPLACE FUNCTION :schema.current_week()
RETURNS smallint
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
current_week smallint;
BEGIN
SELECT
TRUNC(DATE_PART('day', now() - started_at) / 7)::smallint + 1 AS current_week
FROM
seasons
WHERE
season_id = current_season() INTO current_week;
RETURN (IFcurrent_week;
END;
$$;
CREATE OR REPLACE PROCEDURE new_league_season(league_id integer)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
BEGIN
CALL new_league_season(league_id, NULL);
END;
$$;
CREATE OR REPLACE PROCEDURE new_league_season(league_id integer, draft_time timestamptz)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
new_league_season_id integer;
new_draft_id integer;
BEGIN
INSERT INTO league_seasons(season_id, league_id)
VALUES (current_season(), league_id)
RETURNING
league_season_id INTO new_league_season_id;
IF draft_time IS NOT NULL THEN
INSERT INTO drafts(league_season_id, started_at)
VALUES (new_league_season_id, draft_time)
RETURNING
draft_id INTO new_draft_id;
END IF;
END;
$$;
CREATE OR REPLACE FUNCTION pick(_draft_id integer, team_id integer)
RETURNS boolean
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
pick_count smallint;
player_1 integer;
player_2 integer;
BEGIN
INSERT INTO picks (draft_id, league_user_id, team_id) VALUES (_draft_id, current_picker(_draft_id), team_id);
END;
$$;
CREATE OR REPLACE FUNCTION current_picker(_draft_id integer)
RETURNS integer
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
pick_count smallint;
player_1 integer;
player_2 integer;
BEGIN
SELECT
count(1)
FROM
picks
WHERE
draft_id = _draft_id INTO pick_count;
SELECT
league_user_id
FROM
league_users
WHERE
league_season_id =(
SELECT
league_season_id
FROM
drafts
WHERE
draft_id = _draft_id)
ORDER BY
league_user_id
LIMIT 1 INTO player_1;
SELECT
league_user_id
FROM
league_users
WHERE
league_season_id =(
SELECT
league_season_id
FROM
drafts
WHERE
draft_id = _draft_id)
ORDER BY
league_user_id DESC
LIMIT 1 INTO player_2;
IF pick_count % 2 = 0 THEN
RETURN player_1;
ELSE
RETURN player_2;
END IF;
END;
$$;
-- create missing draft picks if any
CREATE OR REPLACE PROCEDURE auto_draft(_draft_id integer)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
latest_pick_time timestamptz;
latest_pick picks%ROWTYPE;
pick_count integer;
next_auto_draft_team integer;
draft drafts%ROWTYPE;
BEGIN
SELECT * FROM drafts WHERE draft_id = _draft_id INTO draft;
/* IF now() >= draft.started_at THEN */
/* UPDATE drafts SET status = 'running', modified_at = now() WHERE draft_id = _draft_id; */
/* END IF; */
latest_pick_time = draft.started_at;
/* SELECT */
/* started_at */
/* FROM */
/* drafts */
/* WHERE */
/* draft_id = _draft_id INTO latest_pick_time; */
IF now() >= latest_pick_time AND draft.status = 'created' THEN
UPDATE drafts SET status = 'running', modified_at = now() WHERE draft_id = _draft_id;
END IF;
LOOP
SELECT
count(1)
FROM
picks
WHERE
draft_id = _draft_id INTO pick_count;
IF pick_count = 32 THEN
UPDATE drafts SET status = 'complete', modified_at = now() WHERE draft_id = _draft_id;
RETURN;
END IF;
SELECT
*
FROM
picks
WHERE
draft_id = _draft_id
ORDER BY
picked_at DESC
LIMIT 1 INTO latest_pick;
IF latest_pick IS NOT NULL THEN
latest_pick_time = latest_pick.picked_at;
END IF;
IF (date_part('epoch', now())::integer - date_part('epoch', latest_pick_time)::integer) / 30 >= 1 THEN
SELECT
current_rankings.team_id
FROM
current_rankings
LEFT JOIN picks ON picks.team_id = current_rankings.team_id
WHERE
pick_id IS NULL
ORDER BY
rank ASC
LIMIT 1 INTO next_auto_draft_team;
INSERT INTO picks(draft_id, league_user_id, team_id, auto, picked_at)
VALUES (_draft_id, current_picker(_draft_id),
(
SELECT
current_rankings.team_id
FROM
current_rankings
LEFT JOIN picks ON picks.team_id = current_rankings.team_id
AND picks.draft_id = _draft_id
WHERE
pick_id IS NULL
ORDER BY
rank ASC
LIMIT 1),
TRUE,
latest_pick_time + interval '30 seconds');
ELSE
EXIT;
END IF;
END LOOP;
END;
$$;
CREATE OR REPLACE FUNCTION current_round(_draft_id integer)
RETURNS smallint
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
time_now timestamptz;
round smallint;
latest_pick picks%ROWTYPE;
pick_count integer;
BEGIN
time_now = now();
SELECT
count(1)
FROM
picks
WHERE
draft_id = _draft_id INTO pick_count;
SELECT
*
FROM
picks
WHERE
draft_id = _draft_id
ORDER BY
created_at DESC
LIMIT 1 INTO latest_pick;
IF latest_pick IS NOT NULL THEN
time_now = latest_pick.created_at;
END IF;
SELECT
(date_part('epoch', time_now)::integer - date_part('epoch', started_at)::integer) / 30
FROM
drafts
WHERE
draft_id = _draft_id INTO round;
IF round >= 32 THEN
RETURN 32;
ELSE
RETURN (round);
END IF;
END;
$$;
CREATE OR REPLACE FUNCTION record_external_score_by_year(_external_id text, year int, week smallint, category score_category, scored_at timestamptz)
RETURNS int
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
team_id integer;
season_id integer;
BEGIN
SELECT seasons.season_id FROM seasons WHERE date_part('year', started_at) = year ORDER BY started_at DESC LIMIT 1 INTO season_id;
SELECT teams.team_id FROM teams WHERE external_id = _external_id INTO team_id;
RETURN (SELECT record_score(team_id, week, season_id, category, scored_at));
END;
$$;
CREATE OR REPLACE FUNCTION record_external_score(_external_id text, season_id int, week smallint, category score_category, scored_at timestamptz)
RETURNS int
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
team_id integer;
BEGIN
SELECT teams.team_id FROM teams WHERE external_id = _external_id INTO team_id;
RETURN (SELECT record_score(team_id, week, season_id, category, scored_at));
END;
$$;
CREATE OR REPLACE FUNCTION record_score(team_id int, week smallint, season_id int, category score_category, scored_at timestamptz)
RETURNS int
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
new_score_id integer;
BEGIN
INSERT INTO scores(season_id, team_id, week, category, scored_at)
VALUES (season_id, team_id, week, category, scored_at)
ON CONFLICT ON CONSTRAINT scores_season_id_team_id_week_category_key
DO UPDATE SET modified_at = NOW(), scored_at = EXCLUDED.scored_at
RETURNING
score_id INTO new_score_id;
RETURN new_score_id;
END;
$$;
CREATE OR REPLACE FUNCTION current_draft_picks(_draft_id int)
RETURNS TABLE (team_id smallint,
rank smallint,
name text,
division divisions,
pick_user text)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
draft_season_id integer;
BEGIN
SELECT league_seasons.season_id FROM drafts JOIN league_seasons USING (league_season_id) WHERE draft_id = _draft_id INTO draft_season_id;
RETURN QUERY
WITH season_rankings AS (
SELECT
teams.team_id,
rankings.rank,
teams.name,
teams.division
FROM
teams
LEFT JOIN rankings ON rankings.team_id = teams.team_id
AND rankings.season_id = draft_season_id
ORDER BY
rank
)
SELECT r.*, users.name AS pick_user
FROM season_rankings r
LEFT JOIN picks ON r.team_id = picks.team_id AND picks.draft_id = _draft_id
LEFT JOIN league_users ON league_users.league_user_id = picks.league_user_id
LEFT JOIN users ON users.user_id = league_users.user_id;
END;
$$;
-- views
CREATE OR REPLACE VIEW draft_info AS
WITH latest_draft_pick AS (
SELECT draft_id, MAX(picked_at) AS picked_at FROM picks GROUP BY draft_id
)
select draft_id, current_picker(draft_id), status,
leagues.name as league,
CASE WHEN status = 'complete' OR status = 'created' THEN 0
ELSE date_part('epoch', (coalesce(latest_draft_pick.picked_at, started_at) + interval '30 seconds') - now())::integer
END AS round_time_remaining,
CASE
WHEN status = 'complete' THEN 'The draft is over, good luck!'
WHEN status = 'created' THEN 'The draft will start in ' || to_char(started_at - now(), 'MI:SS')
WHEN status = 'running' THEN 'It''s ' ||
(SELECT u.name FROM league_users lu JOIN users u ON u.user_id = lu.user_id
WHERE lu.league_user_id = current_picker(draft_id)) || '''s turn!'
END as message,
status = 'running' as can_pick
from drafts
JOIN league_seasons USING(league_season_id)
JOIN leagues USING(league_id)
LEFT JOIN latest_draft_pick USING(draft_id);
CREATE OR REPLACE VIEW league_picks AS
SELECT
league_id,
teams.team_id,
teams.name AS team,
users.name AS player
FROM
picks
JOIN teams USING (team_id)
JOIN league_users USING (league_user_id)
JOIN users USING (user_id)
JOIN league_seasons USING (league_season_id)
ORDER BY
picks.created_at;
CREATE OR REPLACE VIEW season_player_scores AS
WITH season_scores AS (
SELECT
season_id,
team_id,
sum(category_score(category))::integer AS points
FROM
scores
GROUP BY
season_id,
team_id
)
SELECT
season_id,
league_id,
player,
sum(coalesce(points, 0))::integer AS score
FROM
league_picks
LEFT OUTER JOIN season_scores USING (team_id)
GROUP BY
season_id,
league_id,
player
ORDER BY
score DESC;
CREATE OR REPLACE VIEW current_player_scores AS
WITH current_scores AS (
SELECT
team_id,
sum(category_score(category))::integer AS points
FROM
scores
WHERE
season_id = current_season()
GROUP BY
team_id
)
SELECT
league_id,
player,
sum(coalesce(points, 0))::integer AS score
FROM
league_picks
LEFT OUTER JOIN current_scores USING (team_id)
GROUP BY
league_id,
player
ORDER BY
score DESC;
CREATE OR REPLACE VIEW league_scores AS
WITH season_scores AS (
SELECT
season_id,
team_id,
SUM(CASE WHEN category = 'win' THEN
category_score('win')
END)::smallint AS win,
SUM(CASE WHEN category = 'playoffs' THEN
category_score('playoffs')
END)::smallint AS playoffs,
SUM(CASE WHEN category = 'divisional' THEN
category_score('divisional')
END)::smallint AS divisional,
SUM(CASE WHEN category = 'conference' THEN
category_score('conference')
END)::smallint AS conference,
SUM(CASE WHEN category = 'superbowl' THEN
category_score('superbowl')
END)::smallint AS superbowl,
SUM(CASE WHEN category = 'champion' THEN
category_score('champion')
END)::smallint AS champion,
coalesce(SUM(category_score(category)), 0)::smallint AS total
FROM
scores
GROUP BY
season_id,
team_id
)
SELECT
season_id,
league_id,
team,
player,
coalesce(win, 0) AS win,
playoffs,
divisional,
conference,
superbowl,
champion,
coalesce(total, 0) AS total
FROM
league_picks
LEFT OUTER JOIN season_scores USING (team_id);
CREATE OR REPLACE VIEW :schema.current_rankings AS
SELECT
teams.team_id,
rank,
name,
division
FROM
teams
LEFT JOIN rankings ON rankings.team_id = teams.team_id
AND rankings.season_id = current_season()
ORDER BY
rank;
CREATE OR REPLACE FUNCTION random_invite()
RETURNS varchar
LANGUAGE plpgsql
AS $$
DECLARE
base10 bigint;
base36 varchar := '';
intval bigint;
char0z char[] := regexp_split_to_array('0123456789abcdefghijklmnopqrstuvwxyz', '');
BEGIN
SELECT floor(random() * 78_364_164_095 + 2_176_782_336) INTO base10;
intval := abs(base10);
WHILE intval != 0 LOOP
base36 := char0z[(intval % 36)+1] || base36;
intval := intval / 36;
END LOOP;
IF base10 = 0 THEN base36 := '0'; END IF;
RETURN base36;
END;
$$;
|