DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
DROP TABLE FACILITY_STAFF CASCADE CONSTRAINTS;
DROP TABLE FACILITY CASCADE CONSTRAINTS;
DROP TABLE RESTAURANT CASCADE CONSTRAINTS;
DROP TABLE DELEGATION CASCADE CONSTRAINTS;
DROP TABLE HOTEL_DELEGATION CASCADE CONSTRAINTS;
DROP TABLE ATHLETE CASCADE CONSTRAINTS;
DROP TABLE PARTICIPATES CASCADE CONSTRAINTS;
DROP TABLE EVENT CASCADE CONSTRAINTS;

CREATE TABLE EMPLOYEE (
ID_Number NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Surnames VARCHAR2(50) NOT NULL,
email VARCHAR2(50),
Employee_Type VARCHAR2(50) NOT NULL
);


CREATE TABLE FACILITY_STAFF (
Facility_Venue VARCHAR2(50),
Facility_ID NUMBER,
Employee NUMBER,
CONSTRAINT PK_FS PRIMARY KEY (Facility_Venue, Facility_ID, Employee)
);

CREATE TABLE FACILITY (
Venue VARCHAR2(50),
ID NUMBER,
Capacity FLOAT NOT NULL,
Manager NUMBER NOT NULL,
Facility_Type VARCHAR2(50) NOT NULL,
CONSTRAINT PK_FAC PRIMARY KEY (Venue, ID)
);

CREATE TABLE RESTAURANT (
R_Venue VARCHAR2(50),
R_ID NUMBER,
Food_type VARCHAR2(50) NOT NULL,
CONSTRAINT PK_RES PRIMARY KEY (R_Venue, R_ID)
);

CREATE TABLE DELEGATION (
Country VARCHAR2(50) PRIMARY KEY,
Num_Athletes NUMBER NOT NULL
);

CREATE TABLE HOTEL_DELEGATION (
Hotel_Venue VARCHAR2(50),
Hotel_ID NUMBER,
Delegation VARCHAR2(50),
Checkin_date DATE NOT NULL,
Checkout_date DATE NOT NULL,
CONSTRAINT PK_HD PRIMARY KEY (Hotel_Venue,Hotel_ID,Delegation)
);

CREATE TABLE ATHLETE (
ID_Number NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Surnames VARCHAR2(50) NOT NULL,
DateBirth DATE NOT NULL,
Delegation VARCHAR2(50) NOT NULL
);

CREATE TABLE PARTICIPATES (
Event NUMBER,
Athlete NUMBER,
DateParticipation DATE NOT NULL,
CONSTRAINT PK_PART PRIMARY KEY (Event,Athlete)
);

CREATE TABLE EVENT (
Code NUMBER PRIMARY KEY,
Sport VARCHAR2(50) NOT NULL,
Sport_Complex VARCHAR2(50) NOT NULL,
EventDate DATE NOT NULL,
EventDuration NUMBER NOT NULL,
N_Part NUMBER NOT NULL,
N_Officials NUMBER NOT NULL
);

-- INSERTING SOME DATA

--EMPLOYEE
INSERT INTO EMPLOYEE VALUES(1,'Pepe','Martinez','mart@email.com','suprevisor');
INSERT INTO EMPLOYEE VALUES(2,'Luis','Garcia','gar@email.com','suprevisor');
INSERT INTO EMPLOYEE VALUES(3,'Maria','Perez','per@email.com','manager');
INSERT INTO EMPLOYEE VALUES(4,'Jordi','Cruz','cru@email.com','manager');


--FACILITY
INSERT INTO FACILITY VALUES('Madrid',1,3000,1,'Hotel');
INSERT INTO FACILITY VALUES('Madrid',2,600,2,'Hotel');
INSERT INTO FACILITY VALUES('Barcelona',1,700,1,'Restaurant');
INSERT INTO FACILITY VALUES('Madrid',3,100,4,'Restaurant');

--RESTAURANT
INSERT INTO RESTAURANT VALUES('Madrid',3,'italian');
INSERT INTO RESTAURANT VALUES('Barcelona',1,'italian');

--DELEGATION
INSERT INTO DELEGATION VALUES('Spain',120);
INSERT INTO DELEGATION VALUES('France',240);
INSERT INTO DELEGATION VALUES('Italy',190);

--ATHLETE
INSERT INTO ATHLETE VALUES(1,'Luis','Díaz Merino',to_date('15/10/1990','dd/mm/yyyy'),'Spain');
INSERT INTO ATHLETE VALUES(2,'Marco','Mendoza Martínez',to_date('10/09/2000','dd/mm/yyyy'),'Spain');
INSERT INTO ATHLETE VALUES(3,'Pedro','Perez Garcia',to_date('23/06/1995','dd/mm/yyyy'),'Spain');
INSERT INTO ATHLETE VALUES(4,'Jean Pierre','Garson',to_date('10/04/1997','dd/mm/yyyy'),'France');
INSERT INTO ATHLETE VALUES(5,'Marie','Curie',to_date('09/03/2001','dd/mm/yyyy'),'France');
INSERT INTO ATHLETE VALUES(6,'Francoise','Ozon',to_date('17/07/1992','dd/mm/yyyy'),'France');
INSERT INTO ATHLETE VALUES(7,'Giancarlo','Benedetti',to_date('05/05/1998','dd/mm/yyyy'),'Italy');

--EVENT
INSERT INTO EVENT VALUES(1,'Baskeball','B Stadium', to_date('25/07/2021','dd/mm/yyyy'),90,10,3);
INSERT INTO EVENT VALUES(2,'Soccer','S Stadium', to_date('24/07/2021','dd/mm/yyyy'),90,22,4);
INSERT INTO EVENT VALUES(3,'Judo','J Stadium', to_date('23/07/2021','dd/mm/yyyy'),120,15,5);
INSERT INTO EVENT VALUES(4,'Karate','K Stadium', to_date('22/07/2021','dd/mm/yyyy'),180,29,7);

--FACILITY_STAFF
INSERT INTO FACILITY_STAFF VALUES('Madrid',1,1);
INSERT INTO FACILITY_STAFF VALUES('Madrid',2,2);
INSERT INTO FACILITY_STAFF VALUES('Madrid',3,4);
INSERT INTO FACILITY_STAFF VALUES('Barcelona',1,3);
INSERT INTO FACILITY_STAFF VALUES('Barcelona',1,5);

-- HOTEL_DELEGATION
INSERT INTO HOTEL_DELEGATION VALUES('Madrid',1,'Spain',to_date('22/07/2021','dd/mm/yyyy'),to_date('26/07/2021','dd/mm/yyyy'));
INSERT INTO HOTEL_DELEGATION VALUES('Madrid',2,'France',to_date('20/07/2021','dd/mm/yyyy'),to_date('26/07/2021','dd/mm/yyyy'));
INSERT INTO HOTEL_DELEGATION VALUES('Madrid',1,'Italy',to_date('19/07/2021','dd/mm/yyyy'),to_date('26/07/2021','dd/mm/yyyy'));

-- PARTICIPATES
INSERT INTO PARTICIPATES VALUES(1,1,to_date('25/07/2021','dd/mm/yyyy'));
INSERT INTO PARTICIPATES VALUES(1,2,to_date('25/07/2021','dd/mm/yyyy'));
INSERT INTO PARTICIPATES VALUES(1,3,to_date('25/07/2021','dd/mm/yyyy'));
INSERT INTO PARTICIPATES VALUES(2,4,to_date('24/07/2021','dd/mm/yyyy'));
INSERT INTO PARTICIPATES VALUES(2,5,to_date('24/07/2021','dd/mm/yyyy'));
INSERT INTO PARTICIPATES VALUES(2,6,to_date('24/07/2021','dd/mm/yyyy'));
INSERT INTO PARTICIPATES VALUES(3,7,to_date('23/07/2021','dd/mm/yyyy'));


-- FORIGN KEYS TABLE FACILITY_STAFF
ALTER TABLE FACILITY_STAFF ADD CONSTRAINT FK_FS_TO_EMPLOYEE FOREIGN KEY (Employee) REFERENCES EMPLOYEE(ID_Number);
ALTER TABLE FACILITY_STAFF ADD CONSTRAINT FK_FS_TO_FACILITY FOREIGN KEY (Facility_Venue, Facility_ID) REFERENCES FACILITY(Venue,ID);

-- FOREIGN KEYS RESTAURANT
ALTER TABLE RESTAURANT ADD CONSTRAINT FK_REST_TO_FACILITY FOREIGN KEY (R_Venue, R_ID) REFERENCES FACILITY(Venue,ID);

-- FOREIGN KEYS HOTEL_DELEGATION
ALTER TABLE HOTEL_DELEGATION ADD CONSTRAINT FK_HD_TO_FACILITY FOREIGN KEY (Hotel_Venue, Hotel_ID) REFERENCES FACILITY(Venue,ID);
ALTER TABLE HOTEL_DELEGATION ADD CONSTRAINT FK_HD_TO_DELEGATION FOREIGN KEY (Delegation) REFERENCES DELEGATION(Country);

-- FOREIGN KEYS ATHLETE
ALTER TABLE ATHLETE ADD CONSTRAINT FK_ATH_TO_DELEGATION FOREIGN KEY (Delegation) REFERENCES DELEGATION(Country);

-- FOREIGN KEYS PARTICIPATES
ALTER TABLE PARTICIPATES ADD CONSTRAINT FK_PAR_TO_ATHLETE FOREIGN KEY (Athlete) REFERENCES ATHLETE(ID_Number);
ALTER TABLE PARTICIPATES ADD CONSTRAINT FK_PAR_TO_EVENT FOREIGN KEY (Event) REFERENCES EVENT(Code);

--Queries

-- 1. Obtain the average number of athletes per delegation.
-----  Sol 1
SELECT AVG(num_athletes) FROM DELEGATION;

-----  Sol 2
SELECT AVG(COUNT(*)) AS avg_n_athletes FROM ATHLETE GROUP BY Delegation;

-- 2. Get the number of employees per restaurant for those restaurants serving ‘italian food’. 
-- The query must return the identifier of the restaurant, its capacity and the number of employees.

SELECT F.Venue AS Rvenue, F.ID AS RID, COUNT(*) AS ne, F.Capacity FROM FACILITY_STAFF FS, FACILITY F, RESTAURANT R
WHERE R.food_type = 'italian' AND
FS.Facility_Venue = F.Venue AND FS.Facility_ID = F.ID AND
F.Venue = R.R_Venue AND F.ID = R.R_ID
GROUP BY F.Venue, F.ID, F.Capacity;

-- 3. List the events ordered by the number of athletes taking part on them. 
--    The query must return the event identifier, the sport and the number of athletes.

SELECT E.Code, E.Sport, COUNT(*) AS n_athletes FROM PARTICIPATES P, EVENT E
WHERE P.Event = E.Code
GROUP BY E.Code, E.Sport
ORDER BY n_athletes DESC;

-- 4. Get the number of athletes that have been hosted per hotel between June 2021 and September 2021

SELECT D.Country AS Delegation,D.Num_athletes 
FROM DELEGATION D, HOTEL_DELEGATION HD
WHERE HD.Checkin_date > '01/06/2021' AND 
HD.Checkout_date < '01/09/2021' AND
D.Country = HD.Delegation;


-- TRIGGERS: Include all the triggers needed to calculate the attribute number of athletes in table DELEGATION.

CREATE OR REPLACE TRIGGER numAthelets1 
BEFORE INSERT OR DELETE OR UPDATE OF Delegation ON ATHLETE 
FOR EACH ROW 
DECLARE Del VARCHAR2(50); 
BEGIN

IF Inserting THEN 
	UPDATE DELEGATION SET Num_Athletes = Num_Athletes + 1 WHERE Country = :NEW.Delegation;
ELSE
	IF Deleting THEN 
	  UPDATE DELEGATION SET Num_Athletes = Num_Athletes - 1 WHERE Country = :NEW.Delegation;
	ELSE
		IF Updating THEN
			UPDATE DELEGATION SET Num_Athletes = Num_Athletes - 1 WHERE Country = :OLD.Delegation;
			UPDATE DELEGATION SET Num_Athletes = Num_Athletes + 1 WHERE Country = :NEW.Delegation;
		END IF;
	END IF;
END IF;
 
END;

-- Queries to test the trigger

INSERT INTO ATHLETE VALUES(8,'Marco','Grasso',to_date('02/01/1999','dd/mm/yyyy'),'Italy');

DELETE FROM ATHLETE WHERE id_number = 8;

UPDATE ATHLETE SET delegation = 'France' WHERE id_number = 8;

SELECT * FROM DELEGATION;

SELECT * FROM ATHLETE;