create table city(
	CityID serial,
    Title  varchar(50) not null,
    constraint pkcity primary key (CityID)
);

create table users(
    UID    serial,
    fkCity int not null,
    Uname  varchar(50) not null,
    Mail   varchar(50) not null,
    Age    int not null,
    CONSTRAINT userpk PRIMARY KEY (UID),
    CONSTRAINT fkCity FOREIGN KEY (fkCity) REFERENCES City(CityID)
    ON UPDATE CASCADE ON DELETE CASCADE
);

create table airplane(
	AID    serial  PRIMARY KEY NOT NULL,
    fkCity int not null,
    Title  varchar(50) not null,
    CONSTRAINT fkCity FOREIGN KEY (fkCity) REFERENCES City(CityID)
    ON UPDATE CASCADE ON DELETE CASCADE
);

create table pilot(
	PID   serial PRIMARY KEY NOT NULL,
    Pname varchar(50) not null
);

create table staff(
	SID   serial PRIMARY KEY NOT NULL,
    Panem varchar(50) not null
);

create table hop(
	hopID   serial PRIMARY KEY NOT NULL,
    fkAir   int not null,
    fkCity  int not null,
    fkUser  int not null,
    fkPilot int not null,
    fkStaff int not null,
    CONSTRAINT fkAir FOREIGN KEY (fkAir) REFERENCES airplane(AID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fkCity FOREIGN KEY (fkCity) REFERENCES City(CityID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fkUser FOREIGN KEY (fkUser) REFERENCES Users(UID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fkPilot FOREIGN KEY (fkPilot) REFERENCES pilot(PID)
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fkStaff FOREIGN KEY (fkStaff) REFERENCES staff(SID)
    ON UPDATE CASCADE ON DELETE CASCADE
);

insert into City(Title) VALUES ('Almaty'),('Astana'),
                                ('Havana'),('Toronto'),
                                ('New York'),('Amsterdam'),
                                ('Samara'),('Sydney'),
                                ('Monaco'),('LA');

insert into users(fkCity, Uname, Mail, Age) VALUES (1,'Daulet','d@gmail.com',19),(2,'Zhanco','z@gmail.com',20),
							(3,'Delilah','da@gmail.com',17),(4,'Aray','ar@gmail.com',18),
							(5,'Santiago','cuba@gmail.cu',25),(6,'Dilara','DI@gmai.com',20),
                            (7,'Sonya','sonya@mail.ru',18),(8,'Sydneyman','sydney@gmail.au',45),
                            (9,'Monacogirl','mongi@monaco.com',15),(10,'Desmond','dasmond@la.com',25);

insert into airplane(fkCity, title) VALUES (1,'BOEING 727'),(2,'AIRBUS A320')
                                            ,(3,'GYROCOPTER 7.07'),(4,'FLYING COURIER'),
                                            (5,'Fly Emirates'),(6,'3Xtrim 452'),
                                            (7,'AERO BOERO 785'),(8,'BELL X2')
                                            ,(9,'Light ZX'),(10,'Sunrise 2334');

insert into pilot(pname) values('IAN'),('Dost'),
                                ('Clark'),('Kent'),
                                ('Yerassyl'),('Arnold'),
                                ('Gomez'),('Isco'),
                                ('Suarez'),('Remirez');

insert into staff(Panem) values('Sara'),('Aigerim'),
                                ('Samanta'),('Sam'),
                                ('Lisa'),('Merph'),
                                ('Karina'),('Milana'),
                                ('Anara'),('Elena');

ALTER TABLE hop add column to
insert into hop(fkCity, fkUser, fkPilot, fkStaff) values(1,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,);
/*
 fkAir   int not null,
    fkCity  int not null,
    fkUser  int not null,
    fkPilot int not null,
    fkStaff int not null,
*/