/*
DROP TABLE Scientist CASCADE CONSTRAINTS;
DROP TABLE Site CASCADE CONSTRAINTS;
DROP TABLE Measurement_Type CASCADE CONSTRAINTS;
DROP TABLE Sample CASCADE CONSTRAINTS;
DROP TABLE Measurement CASCADE CONSTRAINTS;

DROP VIEW Grantson_Samples;
DROP VIEW Site_Outlier_Incidents;
DROP VIEW Region_Temperatures;

PURGE RECYCLEBIN;
*/

CREATE TABLE Scientist (
  Scientist_Num VARCHAR2(7) NOT NULL,
  Surname VARCHAR2(50) NOT NULL,
  Other_Names VARCHAR2(100) NOT NULL,
  Email VARCHAR2(255) NOT NULL,
  Mobile_Phone VARCHAR2(20) NOT NULL,

  PRIMARY KEY (Scientist_Num)
);



CREATE TABLE Site (
  Site_ID VARCHAR2(5) NOT NULL,
  Region VARCHAR2(9) NOT NULL CHECK (Region IN ('Dunedin', 'Alexandra', 'Tekapo')),
  Description VARCHAR2(255) NOT NULL,
  Latitude DOUBLE PRECISION NOT NULL CHECK (Latitude BETWEEN -50 AND -40),
  Longitude DOUBLE PRECISION NOT NULL CHECK (Longitude BETWEEN 165 AND 175),
  Catchment_Area NUMERIC(5) CHECK (Catchment_Area > 0),
  Catchment_Height NUMERIC(5),
  Altitude NUMERIC(5),

  PRIMARY KEY (Site_ID),
  CHECK ((Catchment_Area IS NULL AND Catchment_Height IS NULL) OR (Catchment_Area IS NOT NULL AND Catchment_Height IS NOT NULL))
);



CREATE TABLE Sample (
  Site_ID VARCHAR2(5) NOT NULL,
  Recorded_On DATE NOT NULL,

  Scientist_Num VARCHAR2(7) NOT NULL,
  Comments CLOB,

  PRIMARY KEY (Site_ID, Recorded_On),
  FOREIGN KEY (Scientist_Num) REFERENCES Scientist(Scientist_Num),
  FOREIGN KEY (Site_ID) REFERENCES Site(Site_ID)
);



CREATE TABLE Measurement_Type (
  Name VARCHAR2(50) NOT NULL,
  Units VARCHAR2(10),

  PRIMARY KEY (Name)
);



CREATE TABLE Measurement (
  Site_ID VARCHAR2(5) NOT NULL,
  Recorded_On DATE NOT NULL,
  Name VARCHAR2(50) NOT NULL,

  Value NUMERIC(7, 3),
  Outlier_Indicator VARCHAR2(10),

  PRIMARY KEY (Site_ID, Recorded_On, Name),
  FOREIGN KEY (Site_ID, Recorded_On) REFERENCES Sample(Site_ID, Recorded_On),
  FOREIGN KEY (Name) REFERENCES Measurement_Type (Name),
  
  CHECK ((Value IS NULL AND Outlier_Indicator IS NOT NULL) OR (Value IS NOT NULL AND Outlier_Indicator IS NULL))
);










--INSERT STATEMENTS
/*
INSERT INTO Measurement_Type (Name, Units) VALUES ('Clarity', 'm');
INSERT INTO Measurement_Type (Name, Units) VALUES ('Discharge', 'cub.m/s');
INSERT INTO Measurement_Type (Name, Units) VALUES ('E. coli', '/100mL');
INSERT INTO Measurement_Type (Name, Units) VALUES ('Nitrogen', 'mg/cub.m');
INSERT INTO Measurement_Type (Name, Units) VALUES ('Oxygen', 'mg/cub.m');
INSERT INTO Measurement_Type (Name) VALUES ('pH');
INSERT INTO Measurement_Type (Name, Units) VALUES ('Phosphorus', 'mg/cub.m');
INSERT INTO Measurement_Type (Name, Units) VALUES ('Temperature', 'C');

INSERT INTO Scientist (Scientist_Num, Surname, Other_Names, Email, Mobile_Phone)
VALUES ('31415', 'Granston', 'Richard', 'bigrich@email.com', '+64 21 2926537');

INSERT INTO Site (Site_ID, Region, Description, Latitude, Longitude, Catchment_Area, Catchment_Height, Altitude)
VALUES ('DN11', 'Dunedin', 'Leith River by Clyde St. bridge', -45.867093, 170.516081, 42, 380, 3);

INSERT INTO Sample (Scientist_Num, Site_ID, Recorded_On, Comments) 
VALUES ('31415', 'DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'Noticed a dead badger trapped in drain grate next to sample site. Odd given that badgers are not native to New Zealand. Possible contamination to samples as a result.\n\nHeavy rain two days before sampling produced flooding that caused severe damage to plants and soils on banks. This may inflate some readings.');

INSERT INTO Measurement (Site_ID, Recorded_On, Name, Value) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'Temperature', 16);
INSERT INTO Measurement (Site_ID, Recorded_On, Name, Value) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'Clarity', 1.3);
INSERT INTO Measurement (Site_ID, Recorded_On, Name, Value) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'Discharge', 1.6);
INSERT INTO Measurement (Site_ID, Recorded_On, Name, Value) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'pH', 7.2);
INSERT INTO Measurement (Site_ID, Recorded_On, Name, Outlier_Indicator) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'E. coli', '> 2419.2');
INSERT INTO Measurement (Site_ID, Recorded_On, Name, Value) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'Nitrogen', 295);
INSERT INTO Measurement (Site_ID, Recorded_On, Name, Value) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'Oxygen', 9.5);
INSERT INTO Measurement (Site_ID, Recorded_On, Name, Value) 
VALUES ('DN11', to_date('25-Nov-2016 11:30', 'DD-MON-YYYY HH24:MI'), 'Phosphorus', 31);
*/






CREATE OR REPLACE VIEW Grantson_Samples AS
SELECT Scientist_Num, Other_Names, Surname, Site_ID, Description, Recorded_On, Name, Value, Outlier_Indicator, Units
FROM Measurement
INNER JOIN Sample USING (Site_ID, Recorded_On)
INNER JOIN Scientist USING (Scientist_Num)
INNER JOIN Site USING (Site_ID)
INNER JOIN Measurement_Type USING (Name)
WHERE Scientist_Num = '31415';


CREATE OR REPLACE VIEW Site_Outlier_Incidents AS
SELECT Site_ID, Name, Outlier_Indicator, COUNT(*) AS Number_of_Incidents
FROM Measurement
WHERE Outlier_Indicator IS NOT NULL
GROUP BY Site_ID, Name, Outlier_Indicator
ORDER BY Site_ID, Name, Outlier_Indicator;


CREATE OR REPLACE VIEW Region_Temperatures AS
SELECT Region, 
    to_char(Recorded_On, 'yyyy-mm') AS Recorded_Month, 
    AVG(Value) AS Avg_Temperature, COUNT(*) AS Number_Of_Readings
FROM Measurement INNER JOIN Sample USING (Site_ID, Recorded_On)
INNER JOIN Site USING (Site_ID)
WHERE Name = 'Temperature'
GROUP BY Region, to_char(Recorded_On, 'yyyy-mm')
ORDER BY Region, to_char(Recorded_On, 'yyyy-mm');