initial mistakeHEADmain

4 files changed, 1256 insertions, 0 deletions

diff --git a/db/migrate/20240129044207_create_rodauth.rb b/db/migrate/20240129044207_create_rodauth.rb
new file mode 100644
index 0000000..9c38cbf
--- /dev/null
+++ b/db/migrate/20240129044207_create_rodauth.rb
@@ -0,0 +1,47 @@
+class CreateRodauth < ActiveRecord::Migration[7.1]
+  def change
+    enable_extension "citext"
+
+    create_table :accounts do |t|
+      t.integer :status, null: false, default: 1
+      t.citext :email, null: false
+      t.index :email, unique: true, where: "status IN (1, 2)"
+      t.string :password_hash
+    end
+
+    # Used by the password reset feature
+    create_table :account_password_reset_keys, id: false do |t|
+      t.bigint :id, primary_key: true
+      t.foreign_key :accounts, column: :id
+      t.string :key, null: false
+      t.datetime :deadline, null: false
+      t.datetime :email_last_sent, null: false, default: -> { "CURRENT_TIMESTAMP" }
+    end
+
+    # Used by the account verification feature
+    create_table :account_verification_keys, id: false do |t|
+      t.bigint :id, primary_key: true
+      t.foreign_key :accounts, column: :id
+      t.string :key, null: false
+      t.datetime :requested_at, null: false, default: -> { "CURRENT_TIMESTAMP" }
+      t.datetime :email_last_sent, null: false, default: -> { "CURRENT_TIMESTAMP" }
+    end
+
+    # Used by the verify login change feature
+    create_table :account_login_change_keys, id: false do |t|
+      t.bigint :id, primary_key: true
+      t.foreign_key :accounts, column: :id
+      t.string :key, null: false
+      t.string :login, null: false
+      t.datetime :deadline, null: false
+    end
+
+    # Used by the remember me feature
+    create_table :account_remember_keys, id: false do |t|
+      t.bigint :id, primary_key: true
+      t.foreign_key :accounts, column: :id
+      t.string :key, null: false
+      t.datetime :deadline, null: false
+    end
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
new file mode 100644
index 0000000..fbe0b4e
--- /dev/null
+++ b/db/schema.rb
@@ -0,0 +1,126 @@
+# This file is auto-generated from the current state of the database. Instead
+# of editing this file, please use the migrations feature of Active Record to
+# incrementally modify your database, and then regenerate this schema definition.
+#
+# This file is the source Rails uses to define your schema when running `bin/rails
+# db:schema:load`. When creating a new database, `bin/rails db:schema:load` tends to
+# be faster and is potentially less error prone than running all of your
+# migrations from scratch. Old migrations may fail to apply correctly if those
+# migrations use external dependencies or application code.
+#
+# It's strongly recommended that you check this file into your version control system.
+
+ActiveRecord::Schema[7.1].define(version: 0) do
+  create_schema "teamdraft"
+
+  # These are extensions that must be enabled in order to support this database
+  enable_extension "plpgsql"
+
+  # Custom types defined in this database.
+  # Note that some types may not work with other database engines. Be careful if changing database.
+  create_enum "divisions", ["nfcn", "nfce", "nfcs", "nfcw", "afcn", "afce", "afcs", "afcw"]
+  create_enum "score_category", ["win", "playoffs", "divisional", "conference", "superbowl", "champion"]
+
+  create_table "drafts", primary_key: "draft_id", id: :integer, default: nil, force: :cascade do |t|
+    t.integer "league_season_id", null: false
+    t.timestamptz "started_at", null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  create_table "league_seasons", primary_key: "league_season_id", id: :integer, default: nil, force: :cascade do |t|
+    t.integer "season_id", limit: 2, null: false
+    t.integer "league_id", null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  create_table "league_users", primary_key: "league_user_id", id: :bigint, default: nil, force: :cascade do |t|
+    t.integer "league_season_id", null: false
+    t.integer "user_id", null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  create_table "leagues", primary_key: "league_id", id: :integer, default: nil, force: :cascade do |t|
+    t.text "name"
+    t.integer "user_id", null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+    t.check_constraint "char_length(name) <= 255", name: "leagues_name_ck"
+  end
+
+  create_table "picks", primary_key: "pick_id", id: :integer, default: nil, force: :cascade do |t|
+    t.integer "draft_id", null: false
+    t.integer "league_user_id", null: false
+    t.integer "team_id", limit: 2, null: false
+    t.boolean "auto", default: false, null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  create_table "rankings", primary_key: "ranking_id", id: :integer, default: nil, force: :cascade do |t|
+    t.integer "season_id", null: false
+    t.integer "team_id", null: false
+    t.integer "rank", limit: 2, null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  create_table "rosters", primary_key: "roster_id", id: :integer, default: nil, force: :cascade do |t|
+    t.bigint "league_user_id", null: false
+    t.integer "team_id", limit: 2, null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  create_table "scores", primary_key: "score_id", id: :integer, default: nil, force: :cascade do |t|
+    t.integer "season_id", null: false
+    t.integer "team_id", null: false
+    t.integer "week", limit: 2, null: false
+    t.enum "category", default: "win", null: false, enum_type: "score_category"
+    t.timestamptz "scored_at", default: -> { "now()" }, null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+
+    t.unique_constraint ["season_id", "team_id", "week", "category"], name: "scores_season_id_team_id_week_category_key"
+  end
+
+  create_table "seasons", primary_key: "season_id", id: { type: :integer, limit: 2, default: nil }, force: :cascade do |t|
+    t.timestamptz "started_at", null: false
+    t.timestamptz "ended_at", null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  create_table "teams", primary_key: "team_id", id: { type: :integer, limit: 2, default: nil }, force: :cascade do |t|
+    t.text "name", null: false
+    t.enum "division", null: false, enum_type: "divisions"
+    t.text "external_id"
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+    t.check_constraint "char_length(name) <= 255", name: "teams_name_ck"
+  end
+
+  create_table "users", primary_key: "user_id", id: :integer, default: nil, force: :cascade do |t|
+    t.text "name"
+    t.text "password", null: false
+    t.timestamptz "created_at", default: -> { "now()" }, null: false
+    t.timestamptz "modified_at", default: -> { "now()" }, null: false
+  end
+
+  add_foreign_key "drafts", "league_seasons", primary_key: "league_season_id", name: "drafts_league_seasons_fk"
+  add_foreign_key "league_seasons", "leagues", primary_key: "league_id", name: "league_seasons_leagues_fk"
+  add_foreign_key "league_seasons", "seasons", primary_key: "season_id", name: "league_seasons_seasons_fk"
+  add_foreign_key "league_users", "league_seasons", primary_key: "league_season_id", name: "league_users_league_seasons_fk"
+  add_foreign_key "league_users", "users", primary_key: "user_id", name: "league_users_users_fk"
+  add_foreign_key "leagues", "users", primary_key: "user_id", name: "leagues_users_fk"
+  add_foreign_key "picks", "drafts", primary_key: "draft_id", name: "picks_drafts_fk"
+  add_foreign_key "picks", "league_users", primary_key: "league_user_id", name: "picks_league_users_fk"
+  add_foreign_key "picks", "teams", primary_key: "team_id", name: "picks_teams_fk"
+  add_foreign_key "rankings", "seasons", primary_key: "season_id", name: "rankings_seasons_fk"
+  add_foreign_key "rosters", "league_users", primary_key: "league_user_id", name: "rosters_league_users_fk"
+  add_foreign_key "rosters", "teams", primary_key: "team_id", name: "rosters_teams_fk"
+  add_foreign_key "scores", "seasons", primary_key: "season_id", name: "scores_seasons_fk"
+  add_foreign_key "scores", "teams", primary_key: "team_id", name: "scores_teams_fk"
+end
diff --git a/db/seeds.rb b/db/seeds.rb
new file mode 100644
index 0000000..4fbd6ed
--- /dev/null
+++ b/db/seeds.rb
@@ -0,0 +1,9 @@
+# This file should ensure the existence of records required to run the application in every environment (production,
+# development, test). The code here should be idempotent so that it can be executed at any point in every environment.
+# The data can then be loaded with the bin/rails db:seed command (or created alongside the database with db:setup).
+#
+# Example:
+#
+#   ["Action", "Comedy", "Drama", "Horror"].each do |genre_name|
+#     MovieGenre.find_or_create_by!(name: genre_name)
+#   end
diff --git a/db/structure.sql b/db/structure.sql
new file mode 100644
index 0000000..a4668f9
--- /dev/null
+++ b/db/structure.sql
@@ -0,0 +1,1074 @@
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET idle_in_transaction_session_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SELECT pg_catalog.set_config('search_path', '', false);
+SET check_function_bodies = false;
+SET xmloption = content;
+SET client_min_messages = warning;
+SET row_security = off;
+
+--
+-- Name: public; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA public;
+
+
+--
+-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: -
+--
+
+COMMENT ON SCHEMA public IS 'standard public schema';
+
+
+--
+-- Name: teamdraft; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA teamdraft;
+
+
+--
+-- Name: divisions; Type: TYPE; Schema: teamdraft; Owner: -
+--
+
+CREATE TYPE teamdraft.divisions AS ENUM (
+    'nfcn',
+    'nfce',
+    'nfcs',
+    'nfcw',
+    'afcn',
+    'afce',
+    'afcs',
+    'afcw'
+);
+
+
+--
+-- Name: score_category; Type: TYPE; Schema: teamdraft; Owner: -
+--
+
+CREATE TYPE teamdraft.score_category AS ENUM (
+    'win',
+    'playoffs',
+    'divisional',
+    'conference',
+    'superbowl',
+    'champion'
+);
+
+
+--
+-- Name: category_score(teamdraft.score_category); Type: FUNCTION; Schema: teamdraft; Owner: -
+--
+
+CREATE FUNCTION teamdraft.category_score(category teamdraft.score_category) RETURNS smallint
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+score smallint;
+BEGIN
+    SELECT CASE
+        WHEN category = 'win' THEN 1
+        WHEN category IN ('superbowl', 'conference') THEN 10
+        ELSE 5 END
+    INTO score;
+
+    RETURN score;
+END;
+$$;
+
+
+--
+-- Name: create_league(text, timestamp with time zone); Type: FUNCTION; Schema: teamdraft; Owner: -
+--
+
+CREATE FUNCTION teamdraft.create_league(league_name text, start_at timestamp with time zone) RETURNS integer
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+new_user_id integer;
+new_league_id integer;
+new_league_season_id integer;
+new_draft_id integer;
+BEGIN
+    INSERT INTO users (password) VALUES ('test') RETURNING user_id INTO new_user_id;
+    INSERT INTO leagues (name, user_id) VALUES (league_name, new_user_id) RETURNING league_id INTO new_league_id;
+    INSERT INTO league_seasons (season_id, league_id) VALUES ((SELECT season_id FROM seasons LIMIT 1), new_league_id) RETURNING league_season_id INTO new_league_season_id;
+    INSERT INTO league_users (league_season_id, user_id) VALUES (new_league_season_id, new_user_id);
+    INSERT INTO drafts (league_season_id, started_at) VALUES (new_league_season_id, start_at) RETURNING draft_id INTO new_draft_id;
+    RETURN new_draft_id;
+END;
+$$;
+
+
+--
+-- Name: current_season(); Type: FUNCTION; Schema: teamdraft; Owner: -
+--
+
+CREATE FUNCTION teamdraft.current_season() RETURNS integer
+    LANGUAGE sql
+    AS $$
+    SELECT season_id FROM teamdraft.seasons WHERE started_at <= now() AND ended_at > now() LIMIT 1;
+$$;
+
+
+--
+-- Name: current_week(); Type: FUNCTION; Schema: teamdraft; Owner: -
+--
+
+CREATE FUNCTION teamdraft.current_week() RETURNS smallint
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+current_week smallint;
+BEGIN
+    SELECT TRUNC(DATE_PART('day', now() - started_at) / 7)::smallint + 1 AS current_week
+    FROM teamdraft.seasons WHERE season_id = teamdraft.current_season() INTO current_week;
+
+    RETURN current_week;
+END;
+$$;
+
+
+--
+-- Name: record_score(integer, smallint, teamdraft.score_category, timestamp with time zone); Type: FUNCTION; Schema: teamdraft; Owner: -
+--
+
+CREATE FUNCTION teamdraft.record_score(team_id integer, week smallint, category teamdraft.score_category, scored_at timestamp with time zone) RETURNS integer
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+new_score_id integer;
+
+BEGIN
+    INSERT INTO scores (season_id, team_id, week, category, scored_at) 
+        VALUES (teamdraft.current_season(), team_id, week, category, scored_at) ON CONFLICT ON CONSTRAINT scores_season_id_team_id_week_category_key DO 
+            --UPDATE SET scored_at = EXCLUDED.scored_at, modified_at = now()
+            NOTHING
+        RETURNING score_id INTO new_score_id;
+    RETURN new_score_id;
+END;
+$$;
+
+
+SET default_tablespace = '';
+
+SET default_table_access_method = heap;
+
+--
+-- Name: account_login_change_keys; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.account_login_change_keys (
+    id bigint NOT NULL,
+    key character varying NOT NULL,
+    login character varying NOT NULL,
+    deadline timestamp(6) without time zone NOT NULL
+);
+
+
+--
+-- Name: account_login_change_keys_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+CREATE SEQUENCE teamdraft.account_login_change_keys_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: account_login_change_keys_id_seq; Type: SEQUENCE OWNED BY; Schema: teamdraft; Owner: -
+--
+
+ALTER SEQUENCE teamdraft.account_login_change_keys_id_seq OWNED BY teamdraft.account_login_change_keys.id;
+
+
+--
+-- Name: account_password_reset_keys; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.account_password_reset_keys (
+    id bigint NOT NULL,
+    key character varying NOT NULL,
+    deadline timestamp(6) without time zone NOT NULL,
+    email_last_sent timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
+);
+
+
+--
+-- Name: account_password_reset_keys_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+CREATE SEQUENCE teamdraft.account_password_reset_keys_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: account_password_reset_keys_id_seq; Type: SEQUENCE OWNED BY; Schema: teamdraft; Owner: -
+--
+
+ALTER SEQUENCE teamdraft.account_password_reset_keys_id_seq OWNED BY teamdraft.account_password_reset_keys.id;
+
+
+--
+-- Name: account_remember_keys; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.account_remember_keys (
+    id bigint NOT NULL,
+    key character varying NOT NULL,
+    deadline timestamp(6) without time zone NOT NULL
+);
+
+
+--
+-- Name: account_remember_keys_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+CREATE SEQUENCE teamdraft.account_remember_keys_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: account_remember_keys_id_seq; Type: SEQUENCE OWNED BY; Schema: teamdraft; Owner: -
+--
+
+ALTER SEQUENCE teamdraft.account_remember_keys_id_seq OWNED BY teamdraft.account_remember_keys.id;
+
+
+--
+-- Name: account_verification_keys; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.account_verification_keys (
+    id bigint NOT NULL,
+    key character varying NOT NULL,
+    requested_at timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    email_last_sent timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
+);
+
+
+--
+-- Name: account_verification_keys_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+CREATE SEQUENCE teamdraft.account_verification_keys_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: account_verification_keys_id_seq; Type: SEQUENCE OWNED BY; Schema: teamdraft; Owner: -
+--
+
+ALTER SEQUENCE teamdraft.account_verification_keys_id_seq OWNED BY teamdraft.account_verification_keys.id;
+
+
+--
+-- Name: accounts; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.accounts (
+    id bigint NOT NULL,
+    status integer DEFAULT 1 NOT NULL,
+    email teamdraft.citext NOT NULL,
+    password_hash character varying
+);
+
+
+--
+-- Name: accounts_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+CREATE SEQUENCE teamdraft.accounts_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: teamdraft; Owner: -
+--
+
+ALTER SEQUENCE teamdraft.accounts_id_seq OWNED BY teamdraft.accounts.id;
+
+
+--
+-- Name: ar_internal_metadata; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.ar_internal_metadata (
+    key character varying NOT NULL,
+    value character varying,
+    created_at timestamp(6) without time zone NOT NULL,
+    updated_at timestamp(6) without time zone NOT NULL
+);
+
+
+--
+-- Name: drafts; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.drafts (
+    draft_id integer NOT NULL,
+    league_season_id integer NOT NULL,
+    started_at timestamp with time zone NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: drafts_draft_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.drafts ALTER COLUMN draft_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.drafts_draft_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: league_seasons; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.league_seasons (
+    league_season_id integer NOT NULL,
+    season_id smallint NOT NULL,
+    league_id integer NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: league_users; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.league_users (
+    league_user_id bigint NOT NULL,
+    league_season_id integer NOT NULL,
+    user_id integer NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: picks; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.picks (
+    pick_id integer NOT NULL,
+    draft_id integer NOT NULL,
+    league_user_id integer NOT NULL,
+    team_id smallint NOT NULL,
+    auto boolean DEFAULT false NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: teams; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.teams (
+    team_id smallint NOT NULL,
+    name text NOT NULL,
+    division teamdraft.divisions NOT NULL,
+    external_id text,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL,
+    CONSTRAINT teams_name_ck CHECK ((char_length(name) <= 255))
+);
+
+
+--
+-- Name: users; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.users (
+    user_id integer NOT NULL,
+    name text,
+    password text NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: league_picks; Type: VIEW; Schema: teamdraft; Owner: -
+--
+
+CREATE VIEW teamdraft.league_picks AS
+ SELECT league_seasons.league_id,
+    teams.team_id,
+    teams.name AS team,
+    users.name AS player
+   FROM ((((teamdraft.picks
+     JOIN teamdraft.teams USING (team_id))
+     JOIN teamdraft.league_users USING (league_user_id))
+     JOIN teamdraft.users USING (user_id))
+     JOIN teamdraft.league_seasons USING (league_season_id))
+  ORDER BY picks.created_at;
+
+
+--
+-- Name: scores; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.scores (
+    score_id integer NOT NULL,
+    season_id integer NOT NULL,
+    team_id integer NOT NULL,
+    week smallint NOT NULL,
+    category teamdraft.score_category DEFAULT 'win'::teamdraft.score_category NOT NULL,
+    scored_at timestamp with time zone DEFAULT now() NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: league_pick_scores; Type: VIEW; Schema: teamdraft; Owner: -
+--
+
+CREATE VIEW teamdraft.league_pick_scores AS
+ WITH current_scores AS (
+         SELECT scores.team_id,
+            COALESCE(sum(
+                CASE
+                    WHEN (scores.category = 'win'::teamdraft.score_category) THEN teamdraft.category_score('win'::teamdraft.score_category)
+                    ELSE NULL::smallint
+                END), (0)::bigint) AS win,
+            COALESCE(sum(
+                CASE
+                    WHEN (scores.category = 'playoffs'::teamdraft.score_category) THEN teamdraft.category_score('playoffs'::teamdraft.score_category)
+                    ELSE NULL::smallint
+                END), (0)::bigint) AS playoffs,
+            COALESCE(sum(
+                CASE
+                    WHEN (scores.category = 'divisional'::teamdraft.score_category) THEN teamdraft.category_score('divisional'::teamdraft.score_category)
+                    ELSE NULL::smallint
+                END), (0)::bigint) AS divisional,
+            COALESCE(sum(
+                CASE
+                    WHEN (scores.category = 'conference'::teamdraft.score_category) THEN teamdraft.category_score('conference'::teamdraft.score_category)
+                    ELSE NULL::smallint
+                END), (0)::bigint) AS conference,
+            COALESCE(sum(
+                CASE
+                    WHEN (scores.category = 'superbowl'::teamdraft.score_category) THEN teamdraft.category_score('superbowl'::teamdraft.score_category)
+                    ELSE NULL::smallint
+                END), (0)::bigint) AS superbowl,
+            COALESCE(sum(
+                CASE
+                    WHEN (scores.category = 'champion'::teamdraft.score_category) THEN teamdraft.category_score('champion'::teamdraft.score_category)
+                    ELSE NULL::smallint
+                END), (0)::bigint) AS champion,
+            COALESCE(sum(teamdraft.category_score(scores.category)), (0)::bigint) AS total
+           FROM teamdraft.scores
+          WHERE (scores.season_id = teamdraft.current_season())
+          GROUP BY scores.team_id
+        )
+ SELECT league_picks.league_id,
+    league_picks.team,
+    league_picks.player,
+    COALESCE(current_scores.win, (0)::bigint) AS win,
+    COALESCE(current_scores.playoffs, (0)::bigint) AS playoffs,
+    COALESCE(current_scores.divisional, (0)::bigint) AS divisional,
+    COALESCE(current_scores.conference, (0)::bigint) AS conference,
+    COALESCE(current_scores.superbowl, (0)::bigint) AS superbowl,
+    COALESCE(current_scores.champion, (0)::bigint) AS champion,
+    COALESCE(current_scores.total, (0)::bigint) AS total
+   FROM (teamdraft.league_picks
+     LEFT JOIN current_scores USING (team_id));
+
+
+--
+-- Name: league_seasons_league_season_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.league_seasons ALTER COLUMN league_season_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.league_seasons_league_season_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: league_users_league_user_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.league_users ALTER COLUMN league_user_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.league_users_league_user_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: leagues; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.leagues (
+    league_id integer NOT NULL,
+    name text,
+    user_id integer NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL,
+    CONSTRAINT leagues_name_ck CHECK ((char_length(name) <= 255))
+);
+
+
+--
+-- Name: leagues_league_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.leagues ALTER COLUMN league_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.leagues_league_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: picks_pick_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.picks ALTER COLUMN pick_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.picks_pick_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: player_scores; Type: VIEW; Schema: teamdraft; Owner: -
+--
+
+CREATE VIEW teamdraft.player_scores AS
+ WITH current_scores AS (
+         SELECT scores.team_id,
+            (sum(teamdraft.category_score(scores.category)))::integer AS points
+           FROM teamdraft.scores
+          WHERE (scores.season_id = teamdraft.current_season())
+          GROUP BY scores.team_id
+        )
+ SELECT league_picks.league_id,
+    league_picks.player,
+    (sum(COALESCE(current_scores.points, 0)))::integer AS score
+   FROM (teamdraft.league_picks
+     LEFT JOIN current_scores USING (team_id))
+  GROUP BY league_picks.league_id, league_picks.player;
+
+
+--
+-- Name: rankings; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.rankings (
+    ranking_id integer NOT NULL,
+    season_id integer NOT NULL,
+    team_id integer NOT NULL,
+    rank smallint NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: rankings_ranking_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.rankings ALTER COLUMN ranking_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.rankings_ranking_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: rosters; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.rosters (
+    roster_id integer NOT NULL,
+    league_user_id bigint NOT NULL,
+    team_id smallint NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: rosters_roster_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.rosters ALTER COLUMN roster_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.rosters_roster_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: schema_migrations; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.schema_migrations (
+    version text NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: scores_score_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.scores ALTER COLUMN score_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.scores_score_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: seasons; Type: TABLE; Schema: teamdraft; Owner: -
+--
+
+CREATE TABLE teamdraft.seasons (
+    season_id smallint NOT NULL,
+    started_at timestamp with time zone NOT NULL,
+    ended_at timestamp with time zone NOT NULL,
+    created_at timestamp with time zone DEFAULT now() NOT NULL,
+    modified_at timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+--
+-- Name: seasons_season_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.seasons ALTER COLUMN season_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.seasons_season_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: teams_team_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.teams ALTER COLUMN team_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.teams_team_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: users_user_id_seq; Type: SEQUENCE; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE teamdraft.users ALTER COLUMN user_id ADD GENERATED ALWAYS AS IDENTITY (
+    SEQUENCE NAME teamdraft.users_user_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1
+);
+
+
+--
+-- Name: account_login_change_keys id; Type: DEFAULT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_login_change_keys ALTER COLUMN id SET DEFAULT nextval('teamdraft.account_login_change_keys_id_seq'::regclass);
+
+
+--
+-- Name: account_password_reset_keys id; Type: DEFAULT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_password_reset_keys ALTER COLUMN id SET DEFAULT nextval('teamdraft.account_password_reset_keys_id_seq'::regclass);
+
+
+--
+-- Name: account_remember_keys id; Type: DEFAULT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_remember_keys ALTER COLUMN id SET DEFAULT nextval('teamdraft.account_remember_keys_id_seq'::regclass);
+
+
+--
+-- Name: account_verification_keys id; Type: DEFAULT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_verification_keys ALTER COLUMN id SET DEFAULT nextval('teamdraft.account_verification_keys_id_seq'::regclass);
+
+
+--
+-- Name: accounts id; Type: DEFAULT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.accounts ALTER COLUMN id SET DEFAULT nextval('teamdraft.accounts_id_seq'::regclass);
+
+
+--
+-- Name: account_login_change_keys account_login_change_keys_pkey; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_login_change_keys
+    ADD CONSTRAINT account_login_change_keys_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: account_password_reset_keys account_password_reset_keys_pkey; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_password_reset_keys
+    ADD CONSTRAINT account_password_reset_keys_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: account_remember_keys account_remember_keys_pkey; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_remember_keys
+    ADD CONSTRAINT account_remember_keys_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: account_verification_keys account_verification_keys_pkey; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_verification_keys
+    ADD CONSTRAINT account_verification_keys_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: accounts accounts_pkey; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.accounts
+    ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: ar_internal_metadata ar_internal_metadata_pkey; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.ar_internal_metadata
+    ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key);
+
+
+--
+-- Name: drafts drafts_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.drafts
+    ADD CONSTRAINT drafts_pk PRIMARY KEY (draft_id);
+
+
+--
+-- Name: league_seasons league_seasons_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.league_seasons
+    ADD CONSTRAINT league_seasons_pk PRIMARY KEY (league_season_id);
+
+
+--
+-- Name: league_users league_users_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.league_users
+    ADD CONSTRAINT league_users_pk PRIMARY KEY (league_user_id);
+
+
+--
+-- Name: leagues leagues_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.leagues
+    ADD CONSTRAINT leagues_pk PRIMARY KEY (league_id);
+
+
+--
+-- Name: picks picks_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.picks
+    ADD CONSTRAINT picks_pk PRIMARY KEY (pick_id);
+
+
+--
+-- Name: rankings rankings_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.rankings
+    ADD CONSTRAINT rankings_pk PRIMARY KEY (ranking_id);
+
+
+--
+-- Name: rosters rosters_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.rosters
+    ADD CONSTRAINT rosters_pk PRIMARY KEY (roster_id);
+
+
+--
+-- Name: scores scores_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.scores
+    ADD CONSTRAINT scores_pk PRIMARY KEY (score_id);
+
+
+--
+-- Name: scores scores_season_id_team_id_week_category_key; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.scores
+    ADD CONSTRAINT scores_season_id_team_id_week_category_key UNIQUE (season_id, team_id, week, category);
+
+
+--
+-- Name: seasons seasons_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.seasons
+    ADD CONSTRAINT seasons_pk PRIMARY KEY (season_id);
+
+
+--
+-- Name: teams teams_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.teams
+    ADD CONSTRAINT teams_pk PRIMARY KEY (team_id);
+
+
+--
+-- Name: users users_pk; Type: CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.users
+    ADD CONSTRAINT users_pk PRIMARY KEY (user_id);
+
+
+--
+-- Name: index_accounts_on_email; Type: INDEX; Schema: teamdraft; Owner: -
+--
+
+CREATE UNIQUE INDEX index_accounts_on_email ON teamdraft.accounts USING btree (email) WHERE (status = ANY (ARRAY[1, 2]));
+
+
+--
+-- Name: drafts drafts_league_seasons_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.drafts
+    ADD CONSTRAINT drafts_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES teamdraft.league_seasons(league_season_id);
+
+
+--
+-- Name: account_login_change_keys fk_rails_18962144a4; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_login_change_keys
+    ADD CONSTRAINT fk_rails_18962144a4 FOREIGN KEY (id) REFERENCES teamdraft.accounts(id);
+
+
+--
+-- Name: account_verification_keys fk_rails_2e3b612008; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_verification_keys
+    ADD CONSTRAINT fk_rails_2e3b612008 FOREIGN KEY (id) REFERENCES teamdraft.accounts(id);
+
+
+--
+-- Name: account_remember_keys fk_rails_9b2f6d8501; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_remember_keys
+    ADD CONSTRAINT fk_rails_9b2f6d8501 FOREIGN KEY (id) REFERENCES teamdraft.accounts(id);
+
+
+--
+-- Name: account_password_reset_keys fk_rails_ccaeb37cea; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.account_password_reset_keys
+    ADD CONSTRAINT fk_rails_ccaeb37cea FOREIGN KEY (id) REFERENCES teamdraft.accounts(id);
+
+
+--
+-- Name: league_seasons league_seasons_leagues_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.league_seasons
+    ADD CONSTRAINT league_seasons_leagues_fk FOREIGN KEY (league_id) REFERENCES teamdraft.leagues(league_id);
+
+
+--
+-- Name: league_seasons league_seasons_seasons_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.league_seasons
+    ADD CONSTRAINT league_seasons_seasons_fk FOREIGN KEY (season_id) REFERENCES teamdraft.seasons(season_id);
+
+
+--
+-- Name: league_users league_users_league_seasons_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.league_users
+    ADD CONSTRAINT league_users_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES teamdraft.league_seasons(league_season_id);
+
+
+--
+-- Name: league_users league_users_users_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.league_users
+    ADD CONSTRAINT league_users_users_fk FOREIGN KEY (user_id) REFERENCES teamdraft.users(user_id);
+
+
+--
+-- Name: leagues leagues_users_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.leagues
+    ADD CONSTRAINT leagues_users_fk FOREIGN KEY (user_id) REFERENCES teamdraft.users(user_id);
+
+
+--
+-- Name: picks picks_drafts_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.picks
+    ADD CONSTRAINT picks_drafts_fk FOREIGN KEY (draft_id) REFERENCES teamdraft.drafts(draft_id);
+
+
+--
+-- Name: picks picks_league_users_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.picks
+    ADD CONSTRAINT picks_league_users_fk FOREIGN KEY (league_user_id) REFERENCES teamdraft.league_users(league_user_id);
+
+
+--
+-- Name: picks picks_teams_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.picks
+    ADD CONSTRAINT picks_teams_fk FOREIGN KEY (team_id) REFERENCES teamdraft.teams(team_id);
+
+
+--
+-- Name: rankings rankings_seasons_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.rankings
+    ADD CONSTRAINT rankings_seasons_fk FOREIGN KEY (season_id) REFERENCES teamdraft.seasons(season_id);
+
+
+--
+-- Name: rosters rosters_league_users_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.rosters
+    ADD CONSTRAINT rosters_league_users_fk FOREIGN KEY (league_user_id) REFERENCES teamdraft.league_users(league_user_id);
+
+
+--
+-- Name: rosters rosters_teams_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.rosters
+    ADD CONSTRAINT rosters_teams_fk FOREIGN KEY (team_id) REFERENCES teamdraft.teams(team_id);
+
+
+--
+-- Name: scores scores_seasons_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.scores
+    ADD CONSTRAINT scores_seasons_fk FOREIGN KEY (season_id) REFERENCES teamdraft.seasons(season_id);
+
+
+--
+-- Name: scores scores_teams_fk; Type: FK CONSTRAINT; Schema: teamdraft; Owner: -
+--
+
+ALTER TABLE ONLY teamdraft.scores
+    ADD CONSTRAINT scores_teams_fk FOREIGN KEY (team_id) REFERENCES teamdraft.teams(team_id);
+
+
+--
+-- PostgreSQL database dump complete
+--
+
+SET search_path TO teamdraft,public;
+
+INSERT INTO "schema_migrations" (version) VALUES
+('20240129044207');
+