DROP SCHEMA discgolfdb CASCADE;
CREATE SCHEMA discgolfdb;
SET SEARCH_PATH TO discgolfdb;

CREATE TABLE players (
	player_id  SERIAL,
	lastname   VARCHAR(30) NOT NULL,
	firstname  VARCHAR(30) NOT NULL,
	email      VARCHAR(120) NOT NULL,
	password   VARCHAR(200) NOT NULL,
	PRIMARY KEY (player_id)
);

CREATE TABLE courses (
	course_id   SERIAL,
	course_name VARCHAR(200) NOT NULL,
	address     VARCHAR(200),
	hole_count  SMALLINT,
	description VARCHAR(5000),
	url         VARCHAR(500),
	gpscoords   VARCHAR(100),
	PRIMARY KEY (course_id)
);

CREATE TABLE holes (
	hole_id     SERIAL,
	course_id   INTEGER,
	hole_label  VARCHAR(50),
	yards       SMALLINT,
	par         SMALLINT,
	notes       VARCHAR(5000),
	FOREIGN KEY (course_id) REFERENCES courses(course_id) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (hole_id)
);

CREATE TABLE score_cards (
	score_card_id SERIAL,
	player_id     INTEGER,
	course_id     INTEGER,
	datetime      TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
	notes         VARCHAR(5000),
	FOREIGN KEY (course_id) REFERENCES courses(course_id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (player_id) REFERENCES players(player_id) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (score_card_id)
);

CREATE TABLE scores (
	score_card_id  INTEGER,
	hole_id        INTEGER,
	score          SMALLINT,
	FOREIGN KEY (score_card_id) REFERENCES score_cards(score_card_id) 
		ON UPDATE CASCADE ON DELETE CASCADE, 
	FOREIGN KEY (hole_id) REFERENCES holes(hole_id) 
		ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (score_card_id,hole_id)
);

INSERT INTO courses (course_name,hole_count) VALUES ('Cal Young Front Nine',9);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'1',330,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'2',297,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'3',213,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'4',250,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'5',322,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'6',316,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'7',255,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'8',215,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (1,'9',392,3);

INSERT INTO courses (course_name,hole_count) VALUES ('Cal Young Back Nine',9);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'10',312,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'11',285,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'12',330,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'13',225,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'14',223,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'15',213,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'16',225,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'17',274,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (2,'18',258,3);

INSERT INTO courses (course_name,hole_count) VALUES ('Will Hair Short',10);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'1s',270,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'2s',210,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'3s',000,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'4s',240,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'5s',240,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'6s',285,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'7s',200,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'8s',261,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'9s',185,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (3,'10s',186,3);

INSERT INTO courses (course_name,hole_count) VALUES ('Will Hair Long',10);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'1L',326,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'2L',309,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'3L',320,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'4L',401,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'5L',480,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'6L',368,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'7L',280,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'8L',470,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'9L',257,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (4,'10L',233,3);

INSERT INTO courses (course_name,hole_count) VALUES ('Will Hair Aux',5);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (5,'A',000,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (5,'B',000,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (5,'C',000,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (5,'D',000,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (5,'E',000,3);

INSERT INTO courses (course_name,hole_count) VALUES ('ACU Pro Par',9);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'1',415,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'2',325,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'3',450,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'4',285,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'5',650,4);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'6',340,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'7',250,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'8',230,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (6,'9',405,3);

INSERT INTO courses (course_name,hole_count) VALUES ('ACU Amateur Par',9);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'1',415,4);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'2',325,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'3',450,4);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'4',285,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'5',650,5);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'6',340,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'7',250,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'8',230,3);
INSERT INTO holes (course_id,hole_label,yards,par) VALUES (7,'9',405,4);
