drop table if EXISTS Product CASCADE;
drop table if EXISTS PC CASCADE;
drop table if EXISTS Laptop CASCADE;
drop table if EXISTS Printer CASCADE;

DROP TABLE IF EXISTS Flights CASCADE;
DROP TABLE IF EXISTS Routes CASCADE;


CREATE TABLE Product (
    manufacturer VARCHAR,
    model VARCHAR PRIMARY KEY,
    type VARCHAR
);

CREATE TABLE PC (
    model varchar PRIMARY KEY,
    speed real,
    ram real,
    hdd real,
    screen real,
    price real,
    FOREIGN KEY(model) REFERENCES Product(model)
);

CREATE TABLE laptop (
    model varchar primary key,
    speed real,
    ram real,
    hdd real,
    screen real,
    price real,
    FOREIGN KEY(model) REFERENCES Product(model)
);

create table printer (
    model VARCHAR primary key,
    colour BOOLEAN,
    type VARCHAR,
    price real,
    FOREIGN KEY(model) REFERENCES Product(model)
);

INSERT INTO Product
VALUES ('hp', 'HP Spectre x360', 'laptop'),
('hp', 'HP Pavilion x360', 'laptop'),
('acer', 'Acer Nitro 5 AN517-41', 'laptop'),
('acer', 'Acer Aspire 5', 'laptop'),
('apple', 'MacBook Pro 13', 'laptop'),
('hp', 'HP ENVY Desktop TE01-1150xt', 'pc'),
('apple', 'Mac Mini', 'pc'),
('asus', 'ASUS Mini PC', 'pc'),
('hp', 'HP Pavilion Gaming TG01', 'pc'),
('hp', 'HP Laser 137fnw', 'printer'),
('epson', 'Epson Ecotank ET-3850', 'printer'),
('hp', 'HP Neverstop laser', 'printer'),
('hp', 'HP OfficeJet 200', 'printer');


INSERT INTO PC
VALUES ('HP ENVY Desktop TE01-1150xt', 4.8, 32, 256, 24, 449.99),
('Mac Mini', 3.0, 8, 512, Null, 1279.99),
('ASUS Mini PC', 3.0, 8, 256, Null, 749.99),
('HP Pavilion Gaming TG01', 3.8, 16, 1024, Null, 1499.99);


INSERT INTO laptop
VALUES ('HP Spectre x360', 3.4, 16, 512, 15, 2999.99),
('HP Pavilion x360', 2.7, 8, 512, 14, 599.99),
('Acer Nitro 5 AN517-41', 3.2, 32, 1024, 14, 2499.99),
('Acer Aspire 5', 2.8, 16, 256, 15, 799.99),
('MacBook Pro 13', 2.9, 8, 256, 13, 1499.99);


INSERT INTO printer
VALUES 
('HP Laser 137fnw', false, 'laser', 229.99),
('Epson Ecotank ET-3850', true, 'inc', 499.99),
('HP Neverstop laser', false, 'laser', 309.99),
('HP OfficeJet 200', true, 'inc', 339.99);

CREATE TABLE Flights (
       -- ICAO codes
       origin CHAR(4),
       destination CHAR(4),
       departure_time TIMESTAMP,
       arrival_time TIMESTAMP
);

INSERT INTO Flights VALUES
       ('ENGM', 'ENBR', TIMESTAMP '2023-02-01 07:00:00', TIMESTAMP '2023-02-01 08:15:30'),
       ('ENGM', 'ENDU', TIMESTAMP '2023-02-01 07:00:00', TIMESTAMP '2023-02-01 10:15:30'),
       ('ENDU', 'ENGM', TIMESTAMP '2023-02-01 05:03:00', TIMESTAMP '2023-02-01 06:45:00'),
       ('ENDU', 'ENBR', TIMESTAMP '2023-02-01 05:03:00', TIMESTAMP '2023-02-01 11:30:00');

CREATE TABLE Routes (
       -- ICAO codes
       origin CHAR(4),
       destination CHAR(4)
);

INSERT INTO Routes VALUES
       ('ENGM', 'ENBR'),
       ('ENGM', 'ENDU'),
       ('ENBR', 'ENBS'),
       ('ENBS', 'ENFL'),
       ('ENFL', 'ENGM'),
       ('ENFL', 'ENGK'),
       ('ENDU', 'ENBO');