From 332ec93366315fa1ed7b4acd7a3407c96e8ddfa7 Mon Sep 17 00:00:00 2001 From: sadbeast Date: Wed, 31 Jan 2024 17:47:56 -0800 Subject: initial mistake --- db/migrate/20240129044207_create_rodauth.rb | 47 ++ db/schema.rb | 126 ++++ db/seeds.rb | 9 + db/structure.sql | 1074 +++++++++++++++++++++++++++ 4 files changed, 1256 insertions(+) create mode 100644 db/migrate/20240129044207_create_rodauth.rb create mode 100644 db/schema.rb create mode 100644 db/seeds.rb create mode 100644 db/structure.sql (limited to 'db') 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'); + -- cgit v1.2.3