/* Evalute this file in the default postgres database as the default postgres user. */
/* You only need to evaluate this file once, if you re-run the file you will reset  */
/* any changes you have made to the database content.                               */


DROP VIEW IF EXISTS uncommissioned;
DROP TABLE IF EXISTS event;
DROP TABLE IF EXISTS maintenance;
DROP TABLE IF EXISTS drone;
DROP DOMAIN IF EXISTS maintenance_types;
DROP DOMAIN IF EXISTS manufacturer_names;
DROP DOMAIN IF EXISTS serial_numbers;
DROP DOMAIN IF EXISTS drone_types;
DROP DOMAIN IF EXISTS employee_ids;
DROP DOMAIN IF EXISTS notes_type;
DROP DOMAIN IF EXISTS e_id_domain;
DROP DOMAIN IF EXISTS c_id_domain;

CREATE DOMAIN maintenance_types AS VARCHAR(13)
CHECK( 
(VALUE = 'commissioning') OR 
(VALUE = 'regular') OR 
(VALUE = 'post-incident')  
);

CREATE DOMAIN manufacturer_names AS VARCHAR(30);
CREATE DOMAIN serial_numbers AS VARCHAR(20);
CREATE DOMAIN drone_types AS VARCHAR(10);

CREATE DOMAIN employee_ids AS CHAR(6) CHECK( 
(LENGTH(VALUE) = 6) AND 
(SUBSTRING(VALUE,1,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,2,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,3,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,4,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,5,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,6,1) IN ('0','1','2','3','4','5','6','7','8','9')) 
);

CREATE DOMAIN notes_type AS VARCHAR(600);

CREATE DOMAIN e_id_domain AS CHAR(6) 
CHECK( 
(LENGTH(VALUE) = 6) AND 
(SUBSTRING(VALUE,1,1) ='E') AND 
(SUBSTRING(VALUE,2,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,3,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,4,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,5,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,6,1) IN ('0','1','2','3','4','5','6','7','8','9'))
);

CREATE DOMAIN c_id_domain AS CHAR(6)
CHECK( 
(LENGTH(VALUE) = 6) AND 
(SUBSTRING(VALUE,1,1) ='C') AND 
(SUBSTRING(VALUE,2,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,3,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,4,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,5,1) IN ('0','1','2','3','4','5','6','7','8','9')) AND 
(SUBSTRING(VALUE,6,1) IN ('0','1','2','3','4','5','6','7','8','9')) 
);


CREATE TABLE drone(
manufacturer manufacturer_names,
serial serial_numbers,
PRIMARY KEY (manufacturer, serial),
type drone_types NOT NULL,
purchase_date DATE NOT NULL
);


CREATE TABLE maintenance(
manufacturer manufacturer_names,
serial serial_numbers,
start_date DATE,
PRIMARY KEY (manufacturer, serial, start_date),
type maintenance_types NOT NULL,
end_date DATE NOT NULL,
CHECK (start_date < end_date),
technician employee_ids NOT NULL,
notes notes_type NOT NULL,
signoff employee_ids NOT NULL,
CHECK (technician <> signoff), 
release_date DATE,
writeoff_date DATE,
CHECK ( ((release_date IS NULL) AND (writeoff_date IS NOT NULL)) OR ((release_date IS NOT NULL) AND (writeoff_date IS NULL))  ),

CONSTRAINT services FOREIGN KEY (manufacturer, serial) REFERENCES drone
/* note if other tables had been developed there                 */
/* would be foreign keys declarations for technician and signoff */
);

CREATE TABLE event(
event_id e_id_domain PRIMARY KEY,
date DATE NOT NULL, 
pilot_id employee_ids NOT NULL, 
observer_id employee_ids NOT NULL,
CHECK (pilot_id <> observer_id), 
manufacturer manufacturer_names NOT NULL,
serial serial_numbers NOT NULL,
client c_id_domain,

CONSTRAINT used_in FOREIGN KEY (manufacturer, serial) REFERENCES drone
/* note if other tables had been developed there would be additional  */
/* foreign keys declarations for pilot, observer and client.          */
);

INSERT INTO drone VALUES ('RotorDyne', 'RD142566243','RX12',  '21/06/2019' );
INSERT INTO drone VALUES ('RotorDyne', 'RD142562324','RX12',  '21/06/2019' );
INSERT INTO drone VALUES ('CyberDyne', 'Model 101','T-800',  '10/05/1984' );
INSERT INTO drone VALUES ('Tyrell', '99544','Nexus-6',  '24/08/2019' );
INSERT INTO drone VALUES ('FlyteSpeed', '1442522','Evoc1',  '20/09/2019' );
INSERT INTO drone VALUES ('MyFly', 'BTTF2','H-Board',  '21/10/2019');

INSERT INTO maintenance VALUES ('CyberDyne', 'Model 101', '12/05/1984', 'commissioning', '13/05/1984', '003451', 'out of the box build - rush request', '025524', '13/05/1984', NULL );
INSERT INTO maintenance VALUES ('CyberDyne', 'Model 101', '14/05/1984', 'post-incident', '15/05/1984', '003451', 'extensive crush damage', '024232', NULL, '15/05/1984' );


INSERT INTO maintenance VALUES ('RotorDyne', 'RD142566243', '30/06/2019' , 'commissioning','1/07/2019' ,'025524','terminal connections strengthened following manufacturer advisory notice','003451', '1/07/1984', NULL);

INSERT INTO maintenance VALUES ('RotorDyne', 'RD142566243', '20/08/2019' , 'regular','22/08/2019' ,'024232','4 rotors replaced - general wear, battery terminal connections replaced','003451', '23/08/1984', NULL);
INSERT INTO maintenance VALUES ('RotorDyne', 'RD142566243', '02/09/2019' , 'post-incident','08/09/2019' ,'024232','body damage repairs, 4 rotors replaced, aerial replaced','003451', '10/09/2019', NULL);
INSERT INTO maintenance VALUES ('RotorDyne', 'RD142566243', '20/10/2019' , 'regular','22/10/2019' ,'024232','body repairs still ok, aerial housing replaced','025524', '22/10/2019', NULL);


INSERT INTO maintenance VALUES ('RotorDyne', 'RD142562324', '23/06/2019', 'commissioning', '27/06/2019' ,'002347','manufacturer advisory notice - replacement battery','024232', '29/06/2019', NULL);

INSERT INTO maintenance VALUES ('RotorDyne', 'RD142562324', '26/08/2019', 'regular', '27/08/2019' ,'002347','no action taken','024232', '27/08/2019', NULL);
INSERT INTO maintenance VALUES ('RotorDyne', 'RD142562324', '29/08/2019', 'regular', '30/08/2019' ,'024232','manufacturer advisory notice - ref 335321 - firmware refresh; full diagnostic - no issues','025524', '30/08/2019', NULL);
INSERT INTO maintenance VALUES ('RotorDyne', 'RD142562324', '10/10/2019', 'post-incident', '8/12/2019' ,'025524','fly-away reported - full diagnostics - returned to manufacturer, for motherboard tests - cleared for use by manufacturer','002347', '14/12/2019', NULL);
INSERT INTO maintenance VALUES ('RotorDyne', 'RD142562324', '06/02/2020', 'regular', '07/02/2020' ,'003451','no action taken','002341', '08/02/2020', NULL);


INSERT INTO maintenance VALUES ('Tyrell', '99544', '29/08/2019', 'commissioning','30/08/2019' , '025524','Mis-alignment checked and resolved - reported to manufacturer','003451', '30/08/2019', NULL);

INSERT INTO maintenance VALUES ('Tyrell', '99544', '30/10/2019', 'regular','31/10/2019' , '002347','no action taken','002341', '1/11/2019', NULL);
INSERT INTO maintenance VALUES ('Tyrell', '99544', '28/12/2019', 'regular','29/12/2019' , '002347','4 rotors replaced','002341', '03/01/2020', NULL);
INSERT INTO maintenance VALUES ('Tyrell', '99544', '05/01/2020', 'regular','09/01/2020' , '002341','out of specification behaviour reported - C-beam alignment lost, irrepairable','002347', NULL, '11/01/2020');

INSERT INTO maintenance VALUES ('FlyteSpeed', '1442522', '25/09/2019', 'commissioning', '29/09/2019' ,'003451','no advisory notices - out of the box build','025524', '30/09/1984', NULL);

INSERT INTO maintenance VALUES ('FlyteSpeed', '1442522', '25/11/2019', 'regular', '26/11/2019' ,'003451','no action taken','025524', '28/11/2019', NULL);
INSERT INTO maintenance VALUES ('FlyteSpeed', '1442522', '30/01/2020', 'regular', '31/01/2020' ,'025524','4 rotor replacement, firmware upgraded v3.45.24','003451', '02/02/2020', NULL);
INSERT INTO maintenance VALUES ('FlyteSpeed', '1442522', '20/02/2020', 'post-incident', '27/02/2020' ,'024232','uncontrolled descent reported - rebuild landing struts, 4 rotor replacement, loose battery terminal fixed','003451', '27/02/2020', NULL);


INSERT INTO event VALUES ('E00282', '10/01/2020', '002341', '002346', 'RotorDyne', 'RD142562324', 'C23452' );
INSERT INTO event VALUES ('E00275', '1/02/2020', '002330', '002346', 'RotorDyne', 'RD142562324', 'C23452');
INSERT INTO event VALUES ('E00271', '1/02/2020', '002330', '002341', 'FlyteSpeed', '1442522', 'C22432');
INSERT INTO event VALUES ('E00283', '10/01/2020', '002330', '002347', 'RotorDyne', 'RD142566243', 'C23452');
INSERT INTO event VALUES ('E00284', '11/01/2020', '002341', '002346', 'RotorDyne', 'RD142566243', 'C23224');
INSERT INTO event VALUES ('E00285', '11/01/2020', '002330', '002347', 'FlyteSpeed', '1442522', 'C23452');
INSERT INTO event VALUES ('E00286', '16/01/2020', '002341', '002346', 'FlyteSpeed', '1442522', NULL);
INSERT INTO event VALUES ('E00290', '15/01/2020', '002341', '002346', 'FlyteSpeed', '1442522', 'C25523');
INSERT INTO event VALUES ('E00291', '17/01/2020', '002330', '002341', 'FlyteSpeed', '1442522', 'C23460');