CREATE SCHEMA Golf_Trips;
SET SEARCH_PATH = 'golf_trips';

CREATE DOMAIN customerID int;
CREATE DOMAIN customerName varchar(20);
CREATE DOMAIN customerSurname varchar(20);
CREATE DOMAIN address varchar(40);
CREATE DOMAIN country varchar(40);
CREATE DOMAIN zip varchar(5);
CREATE DOMAIN  email varchar(40);
CREATE DOMAIN  phone varchar(12);

CREATE TABLE Employee (
  empID int,
  empName varchar(20),
  empSurname varchar(20),
  dateOfBirth date,
  email varchar(40),
  phone varchar(12),
  address varchar(40),
  zip varchar(5),
  position varchar(10),
  courtID int,
  PRIMARY KEY (empID)
);

CREATE INDEX FK_courtID ON  Employee (courtID);

CREATE TABLE Customer (
  customerID customerID,
  custName customerName,
  custSurname customerSurname,
  address address,
  country country,
  zip zip,
  email email,
  phone phone,
  PRIMARY KEY (customerID)
);

CREATE TABLE Golf_Court (
  courtID int,
  courtName varchar(50),
  location varchar(50),
  pricePerHour real,
  noOfHoles smallint,
  carrier boolean,
  trainer boolean,
  clubsRent boolean,
  cartRent boolean,
  PRIMARY KEY (courtID)
);

CREATE TABLE Cart (
  cartID int,
  noOfSeats smallint,
  courtID int,
  PRIMARY KEY (cartID)
);

ALTER TABLE Cart ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID)  ON UPDATE CASCADE ON DELETE SET NULL ;

CREATE TABLE Hole (
  holeID int,
  length real,
  difficulty smallint,
  courtID int,
  PRIMARY KEY (holeID)
);

ALTER TABLE Hole ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID)  ON UPDATE CASCADE ON DELETE SET NULL ;

CREATE TABLE Booking (
  bookingID int,
  arrivalDate timestamp,
  departureDate timestamp,
  additionalService varchar(80),
  courtID int,
  customerID int,
  PRIMARY KEY (bookingID)
);

ALTER TABLE Booking ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID)  ON UPDATE CASCADE ON DELETE SET NULL ;
ALTER TABLE Booking ADD CONSTRAINT FK_customerID FOREIGN KEY (customerID) REFERENCES customer (customerID)  ON UPDATE CASCADE ON DELETE SET NULL ;



CREATE TABLE Clubs (
  clubsID int,
  material varchar(20),
  courtID int,
  PRIMARY KEY (clubsID)
);

ALTER TABLE Clubs ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID)  ON UPDATE CASCADE ON DELETE SET NULL ;


INSERT INTO Customer VALUES (1, 'John', 'Johnson', 'Frederiksgade', 'Denmark', '8700', 'johsenen@gmail.com', '45501142');
INSERT INTO Customer VALUES (2, 'Anders', 'Dernando', 'Amaliegade', 'Denmark', '9000', 'andersdernando@gmail.com', '45501122');
INSERT INTO Customer VALUES (3, 'Joanna', 'Pedersen', 'Sundvej', 'Denmark', '1500', 'joannapedersen@gmail.com', '45513142');
INSERT INTO Customer VALUES (4, 'Mikkel', 'Drytkov', 'Daukanto', 'Lithuania', '20175', 'mikkeldrytkov@gmail.com', '45171142');
INSERT INTO Customer VALUES (5, 'Peter', 'Spoder', 'Fredericiagade', 'Denmark', '5000', 'peterspoder@gmail.com', '45502442');
INSERT INTO Customer VALUES (6, 'Smith', 'Deshen', 'Danskegade', 'Denmark', '1770', 'peterspoder@gmail.com', '45312342');

INSERT INTO Employee values
(1,'Vladimir','Petkov','01-03-1943','vladimirpetkov@gmail.com','0416235','Flowers Street 4231','4213','Trainer',1),
(2,'Igor','Chernyi','05-07-1966','igorchernyi@gmail.com','0423152','Castle Street 4231','4213','Trainer',1),
(3,'Vasiliy','Beliy','09-02-1988','vasilyibeliy@gmail.com','0425123','Glen Park 3215','4213','Carrier',1),
(4,'Viktor','Zelenyi','02-10-1999','viktorzelenyi@gmail.com','0429182','Jefersson Motel 3215','4111','Trainer',2),
(5,'Sasha','Seriy','02-22-1988','sashaseriy@gmail.com','0411123','El Corona 3215','4912','Trainer',2),
(6,'Kiril','Tarakan','09-02-1812','kiriltarakan@gmail.com','0423123','Idlewood 3215','4213','Carrier',2),
(7,'Gosha','Rubchinskiy','09-12-1712','gosharubchinskyi@gmail.com','0465423','Grove Street 3215','4213','Trainer',3),
(8,'Komar','Beliy','09-02-1988','komarbeliy@gmail.com','0425123','Glen Park 2231','4213','Carrier',3);

INSERT INTO Golf_Court VALUES (1, 'Golden Dragon Court', 'Tokyo', 250.0, 14, true, true, true, true);
INSERT INTO Golf_Court VALUES (2, 'Virgin Beach Court', 'Hawaii', 430.4, 14, true, false, true, true);
INSERT INTO Golf_Court VALUES (3, 'Stormy Sea Court', 'Copenhagen', 130.0, 14, true, true, false, true);
INSERT INTO Golf_Court VALUES (4, 'Privjat Russia Court', 'St.Peterburg', 607.5, 14, false, false, false, false);

INSERT INTO Cart VALUES (1, 2, 1);
INSERT INTO Cart VALUES (2, 2, 2);
INSERT INTO Cart VALUES (3, 4, 3);
INSERT INTO Cart VALUES (4, 6, 1);
INSERT INTO Cart VALUES (5, 2, 2);
INSERT INTO Cart VALUES (6, 4, 3);

INSERT INTO Hole VALUES (1, 275.5, 3, 1);
INSERT INTO Hole VALUES (2, 100.0, 5, 2);
INSERT INTO Hole VALUES (3, 125.9, 8, 3);
INSERT INTO Hole VALUES (4, 367.0, 4, 1);
INSERT INTO Hole VALUES (5, 525.4, 2, 2);
INSERT INTO Hole VALUES (6, 232.4, 10, 3);

INSERT INTO Clubs VALUES (1, 'Wood', 1);
INSERT INTO Clubs VALUES (2, 'Steel', 2);
INSERT INTO Clubs VALUES (3, 'Plastic', 3);
INSERT INTO Clubs VALUES (4, 'Steel', 1);
INSERT INTO Clubs VALUES (5, 'Steel', 2);
INSERT INTO Clubs VALUES (6, 'Steel', 3);

INSERT INTO Booking VALUES (1, '05-12-2018','05-17-2018','club',1,1);
INSERT INTO Booking VALUES (2, '05-15-2018','05-21-2018','cart',2,2);
INSERT INTO Booking VALUES (3, '06-07-2018','06-10-2018','null',3,3);
INSERT INTO Booking VALUES (4, '06-12-2018','06-15-2018','cart',1,4);
INSERT INTO Booking VALUES (5, '06-20-2018','06-25-2018','null',2,5);
INSERT INTO Booking VALUES (6, '07-02-2018','07-07-2018','club cart',3,6);

-- rich clients want to see list of the golf courts  with descending price
SELECT courtID,
    courtname,
    location,
    priceperhour
FROM golf_court
WHERE priceperhour <= 720
ORDER BY priceperhour DESC;

-- client wants to see holes and difficulties of the court (we can select 1 court {in WHERE} AND golf_court.courtID = 2)
SELECT golf_court.courtID,
    courtname,
    hole.difficulty
FROM golf_court, hole
WHERE golf_court.courtID = hole.courtID
GROUP BY golf_court.courtID, hole.difficulty;

--Selecting a relation with a simple rule
SELECT *
FROM golf_court
WHERE priceperhour > 300;

--Ordering in descension of courtid
SELECT *
FROM golf_court
ORDER BY courtid DESC;

--Selecting one column of a relation
SELECT COUNT(courtid), location
FROM golf_court
GROUP BY location;

--Displays what employees are working on a specific court
SELECT golf_court.courtname, employee.empname
FROM employee
INNER JOIN golf_court ON employee.courtid = golf_court.courtid;