database_schema.sql

--
-- PostgreSQL database dump
--

\restrict hECp0qBiVaglhiawXCLslS7kRdGRy6kQKs7LhKYmd3Jgg85eIyP5arSKMpdh3L5

-- Dumped from database version 18.3
-- Dumped by pg_dump version 18.3

-- Started on 2026-05-06 16:29:55

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_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;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 221 (class 1259 OID 16506)
-- Name: players; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.players (
    player_id integer NOT NULL,
    first_name character varying NOT NULL,
    last_name character varying NOT NULL,
    "position" character varying(2) NOT NULL,
    team_name character varying
);


ALTER TABLE public.players OWNER TO postgres;

--
-- TOC entry 220 (class 1259 OID 16505)
-- Name: players_player_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.players_player_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.players_player_id_seq OWNER TO postgres;

--
-- TOC entry 5045 (class 0 OID 0)
-- Dependencies: 220
-- Name: players_player_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.players_player_id_seq OWNED BY public.players.player_id;


--
-- TOC entry 219 (class 1259 OID 16478)
-- Name: teams; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.teams (
    team_name character varying NOT NULL,
    city character varying,
    nickname character varying,
    conference character varying
);


ALTER TABLE public.teams OWNER TO postgres;

--
-- TOC entry 223 (class 1259 OID 16608)
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.users (
    username character varying NOT NULL,
    email character varying NOT NULL,
    password_hash character varying NOT NULL,
    created_at timestamp without time zone
);


ALTER TABLE public.users OWNER TO postgres;

--
-- TOC entry 225 (class 1259 OID 16621)
-- Name: watchlists; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.watchlists (
    watchlist_id integer NOT NULL,
    username character varying,
    player_id integer,
    target_round integer,
    scouting_notes character varying,
    added_at timestamp without time zone
);


ALTER TABLE public.watchlists OWNER TO postgres;

--
-- TOC entry 224 (class 1259 OID 16620)
-- Name: watchlists_watchlist_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.watchlists_watchlist_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.watchlists_watchlist_id_seq OWNER TO postgres;

--
-- TOC entry 5046 (class 0 OID 0)
-- Dependencies: 224
-- Name: watchlists_watchlist_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.watchlists_watchlist_id_seq OWNED BY public.watchlists.watchlist_id;


--
-- TOC entry 222 (class 1259 OID 16566)
-- Name: weekly_stats; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.weekly_stats (
    player_id integer NOT NULL,
    season integer NOT NULL,
    week integer NOT NULL,
    passing_yards integer,
    passing_tds integer,
    rushing_yards integer,
    rushing_tds integer,
    receptions integer,
    receiving_yards integer,
    receiving_tds integer,
    total_tds integer
);


ALTER TABLE public.weekly_stats OWNER TO postgres;

--
-- TOC entry 4873 (class 2604 OID 16509)
-- Name: players player_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.players ALTER COLUMN player_id SET DEFAULT nextval('public.players_player_id_seq'::regclass);


--
-- TOC entry 4874 (class 2604 OID 16624)
-- Name: watchlists watchlist_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.watchlists ALTER COLUMN watchlist_id SET DEFAULT nextval('public.watchlists_watchlist_id_seq'::regclass);


--
-- TOC entry 4878 (class 2606 OID 16517)
-- Name: players players_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.players
    ADD CONSTRAINT players_pkey PRIMARY KEY (player_id);


--
-- TOC entry 4876 (class 2606 OID 16485)
-- Name: teams teams_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_pkey PRIMARY KEY (team_name);


--
-- TOC entry 4886 (class 2606 OID 16631)
-- Name: watchlists uq_watchlist_user_player; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.watchlists
    ADD CONSTRAINT uq_watchlist_user_player UNIQUE (username, player_id);


--
-- TOC entry 4882 (class 2606 OID 16619)
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_key UNIQUE (email);


--
-- TOC entry 4884 (class 2606 OID 16617)
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (username);


--
-- TOC entry 4888 (class 2606 OID 16629)
-- Name: watchlists watchlists_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.watchlists
    ADD CONSTRAINT watchlists_pkey PRIMARY KEY (watchlist_id);


--
-- TOC entry 4880 (class 2606 OID 16573)
-- Name: weekly_stats weekly_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.weekly_stats
    ADD CONSTRAINT weekly_stats_pkey PRIMARY KEY (player_id, season, week);


--
-- TOC entry 4889 (class 2606 OID 16518)
-- Name: players players_team_name_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.players
    ADD CONSTRAINT players_team_name_fkey FOREIGN KEY (team_name) REFERENCES public.teams(team_name);


--
-- TOC entry 4891 (class 2606 OID 16637)
-- Name: watchlists watchlists_player_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.watchlists
    ADD CONSTRAINT watchlists_player_id_fkey FOREIGN KEY (player_id) REFERENCES public.players(player_id);


--
-- TOC entry 4892 (class 2606 OID 16632)
-- Name: watchlists watchlists_username_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.watchlists
    ADD CONSTRAINT watchlists_username_fkey FOREIGN KEY (username) REFERENCES public.users(username);


--
-- TOC entry 4890 (class 2606 OID 16574)
-- Name: weekly_stats weekly_stats_player_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.weekly_stats
    ADD CONSTRAINT weekly_stats_player_id_fkey FOREIGN KEY (player_id) REFERENCES public.players(player_id);


-- Completed on 2026-05-06 16:29:55

--
-- PostgreSQL database dump complete
--

\unrestrict hECp0qBiVaglhiawXCLslS7kRdGRy6kQKs7LhKYmd3Jgg85eIyP5arSKMpdh3L5