5 files changed, 90 insertions, 0 deletions
diff --git a/test/db/00-setup.sql b/test/db/00-setup.sql
new file mode 100644
index 0000000..93ceb03
--- /dev/null
+++ b/test/db/00-setup.sql
@@ -0,0 +1,10 @@
+SET client_min_messages TO WARNING;
+CREATE EXTENSION IF NOT EXISTS pgtap;
+
+BEGIN;
+SELECT plan(1);
+
+SELECT has_schema('teamdraft');
+
+SELECT * FROM finish();
+ROLLBACK;
diff --git a/test/db/auth.sql b/test/db/auth.sql
new file mode 100644
index 0000000..f19aff1
--- /dev/null
+++ b/test/db/auth.sql
@@ -0,0 +1,18 @@
+BEGIN;
+SELECT plan(8);
+
+SELECT has_table('accounts');
+SELECT columns_are('accounts', ARRAY[ 'account_id', 'status_id', 'email', 'name', 'modified_at', 'created_at']);
+SELECT has_sequence('accounts_account_id_seq');
+SELECT has_index('teamdraft', 'accounts', 'accounts_pk', 'account_id');
+
+SELECT create_account('sadbEASt@sadbeast.com', 'hunter2') new_user_id \gset
+
+SELECT is(0, (select count(1) FROM accounts WHERE email = 'sadbEASt@sadbeast.com')::int);
+SELECT is(1, (select count(1) FROM accounts WHERE email = 'sadbeast@sadbeast.com')::int);
+SELECT is(:'new_user_id', (select account_id FROM accounts WHERE email = 'sadbeast@sadbeast.com')::int);
+
+SELECT has_extension('pgsodium', 'pgsodium', 'pgsodium extension installed');
+
+SELECT * FROM finish();
+ROLLBACK;
diff --git a/test/db/drafts.sql b/test/db/drafts.sql
new file mode 100644
index 0000000..1e63362
--- /dev/null
+++ b/test/db/drafts.sql
@@ -0,0 +1,15 @@
+BEGIN;
+SELECT plan(5);
+
+SELECT has_table('drafts');
+SELECT columns_are('drafts', ARRAY[ 'draft_id', 'season_id', 'draft_status_id', 'name', 'account_id', 'started_at', 'modified_at', 'created_at']);
+SELECT has_sequence('drafts_draft_id_seq');
+SELECT has_index('teamdraft', 'drafts', 'drafts_pk', 'draft_id');
+
+INSERT INTO seasons (league_id, started_at, ended_at) VALUES (1, now() - interval '1 day', now() + interval '3 months') RETURNING season_id new_season_id \gset
+SELECT create_draft(1, 'sadbeast', 'freckles', now() + interval '3 days') new_session_id \gset
+SELECT is(1, (SELECT count(1)::int FROM account_active_session_keys WHERE session_id = :'new_session_id'));
+
+SELECT * FROM finish();
+ROLLBACK;
+
diff --git a/test/db/scores.sql b/test/db/scores.sql
new file mode 100644
index 0000000..2e1fee3
--- /dev/null
+++ b/test/db/scores.sql
@@ -0,0 +1,19 @@
+BEGIN;
+SELECT plan(2);
+
+SELECT has_table('scores');
+SELECT has_table('score_categories');
+
+SELECT league_id AS nfl_league_id FROM leagues WHERE name = 'NFL' LIMIT 1 \gset
+INSERT INTO seasons (league_id, started_at, ended_at) VALUES (:nfl_league_id, now() - interval '1 day', now() + interval '3 months') RETURNING season_id new_season_id \gset
+
+SELECT league_id AS nfl_league_id FROM leagues WHERE name = 'NFL' LIMIT 1 \gset
+SELECT team_id AS seattle FROM teams JOIN league_divisions USING (league_division_id) WHERE teams.name = 'Seattle' AND league_id = :nfl_league_id \gset
+SELECT team_id AS buffalo FROM teams JOIN league_divisions USING (league_division_id) WHERE teams.name = 'Buffalo' AND league_id = :nfl_league_id \gset
+
+SELECT record_score(:seattle, 1, :new_season_id, 1, now());
+SELECT record_score(:seattle, 2, :new_season_id, 1, now());
+SELECT record_score(:buffalo, 2, :new_season_id, 1, now());
+
+SELECT * FROM finish();
+ROLLBACK;
diff --git a/test/db/seasons.sql b/test/db/seasons.sql
new file mode 100644
index 0000000..605f842
--- /dev/null
+++ b/test/db/seasons.sql
@@ -0,0 +1,28 @@
+BEGIN;
+SELECT plan(12);
+
+SELECT has_table('seasons');
+SELECT columns_are('seasons', ARRAY[ 'season_id', 'league_id', 'started_at', 'ended_at', 'created_at', 'modified_at']);
+SELECT has_sequence('seasons_season_id_seq');
+SELECT has_index('teamdraft', 'seasons', 'seasons_pk', 'season_id');
+
+SELECT league_id AS nfl_league_id FROM leagues WHERE name = 'NFL' LIMIT 1 \gset
+SELECT create_season(:nfl_league_id, now() - interval '1 day', now() + interval '3 months') new_season_id \gset
+
+SELECT is(1, (SELECT count(1)::int FROM seasons WHERE season_id = :new_season_id));
+SELECT is(:new_season_id, (SELECT current_season_id(:nfl_league_id)));
+
+-- rankings
+
+SELECT has_table('rankings');
+SELECT columns_are('rankings', ARRAY[ 'ranking_id', 'season_id', 'team_id', 'rank', 'created_at', 'modified_at']);
+SELECT has_sequence('rankings_ranking_id_seq');
+SELECT has_index('teamdraft', 'rankings', 'rankings_pk', 'ranking_id primary key');
+
+SELECT is((SELECT count(1) FROM teams JOIN league_divisions USING(league_division_id) WHERE league_id = :nfl_league_id), (SELECT count(1)::bigint FROM current_rankings WHERE season_id = :new_season_id));
+SELECT results_eq('SELECT season_id, league_id, name FROM current_rankings WHERE season_id = current_season_id(' || :'nfl_league_id' || ') ORDER BY rank LIMIT 2',
+                  'VALUES (' || :'new_season_id' || ',' || :'nfl_league_id' || $$, 'Buffalo'), ($$ || :'new_season_id' || ',' || :'nfl_league_id' || $$, 'Miami')$$,
+                  'current nfl season rankings');
+
+SELECT * FROM finish();
+ROLLBACK;