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');