-- credits: André Restivo https://web.fe.up.pt/~arestivo
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS Flight;
DROP TABLE IF EXISTS Plane;
DROP TABLE IF EXISTS Airport;
DROP TABLE IF EXISTS Model;

CREATE TABLE Airport (
    airportcod INTEGER PRIMARY KEY,
    name TEXT,
    city TEXT,
    country TEXT
);

CREATE TABLE Plane (
    planecod INTEGER PRIMARY KEY,
    name TEXT,
    modelcod INTEGER REFERENCES Model
);

CREATE TABLE Model (
    modelcod INTEGER PRIMARY KEY,
    make TEXT,
    version TEXT,
    engines INTEGER
);

CREATE TABLE Flight (
    flightcod INTEGER PRIMARY KEY,
    fromairportcod INTEGER REFERENCES Airport,
    toairportcod INTEGER REFERENCES Airport,
    company TEXT,
    duration INTEGER,
    planecod INTEGER REFERENCES Plane
);

INSERT INTO Airport (airportcod, name, city, country) VALUES  (1,'Sa Carneiro','Porto','Portugal');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (3,'Portela','Lisboa','Portugal');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (5,'Faro','Faro','Portugal');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (2,'Madeira','Funchal','Portugal');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (4,'Ponta Delgada','S. Miguel','Portugal');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (9,'Orly','Paris','France');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (8,'Charles de Gaule','Paris','France');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (11,'Heathrow','Londres','United Kingdom');
INSERT INTO Airport (airportcod, name, city, country) VALUES  (12,'Gatwick','Londres','United Kingdom');

INSERT INTO Model (modelcod, make, version, engines) VALUES  (1,'Douglas','DC-10','3');
INSERT INTO Model (modelcod, make, version, engines) VALUES  (2,'Boeing','737','2');
INSERT INTO Model (modelcod, make, version, engines) VALUES  (3,'Boeing','747','4');
INSERT INTO Model (modelcod, make, version, engines) VALUES  (4,'Airbus','A300','2');
INSERT INTO Model (modelcod, make, version, engines) VALUES  (5,'Airbus','A340','4');

INSERT INTO Plane (planecod, name, modelcod) VALUES  (1,'Scott Adams',1);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (2,'Milo Manara',1);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (4,'Henki Bilal',3);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (5,'Gary Larson',4);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (6,'Bill Waterson',4);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (7,'J R R Tolkien',3);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (8,'Franquin',3);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (9,'Douglas Adams',1);
INSERT INTO Plane (planecod, name, modelcod) VALUES  (3,'Serpieri',5);

INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1001,1,2,'TAP',2,1);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1002,2,3,'TAP',1,2);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1010,12,4,'BA',3,3);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1008,3,12,'Portugalia',3,4);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1007,5,1,'TAP',1,5);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1009,1,3,'Portugalia',1,2);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1005,9,2,'AirFrance',2,3);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1003,2,12,'BA',2,5);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1006,8,11,'BA',1,5);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1004,4,3,'SATA',3,6);
INSERT INTO Flight (flightcod, fromairportcod, toairportcod, company, duration, planecod) VALUES (1111,1,3,'TAP',2,3);
