create table vehicle(vehicleid int(10) not null auto_increment primary key,regno varchar(10),vehicletype varchar(100));

create table office(officeid int(10) not null auto_increment primary key,officename varchar(200));

create table transactionnumber(transactionnumberid int(10) not null auto_increment primary key,transactionnumber int(10),usersid int(10),FOREIGN KEY(usersid) REFERENCES users(usersid) ON UPDATE CASCADE ON DELETE NO ACTION);

create table registerparcel(registerparcelid int(10) not null auto_increment primary key,sendername varchar(200),sendertelno varchar(20),receivername varchar(200),receivertelno varchar(20),parcelname varchar(500),cost double(10,2),mpesarefno varchar(20),dated timestamp,transactionstatus int(5),transactionnumberid int(10),officetoid int(10),usersid int(10),FOREIGN KEY(transactionnumberid) REFERENCES transactionnumber(transactionnumberid) ON UPDATE CASCADE ON DELETE NO ACTION,FOREIGN KEY(officetoid ) REFERENCES office(officeid) ON UPDATE CASCADE ON DELETE NO ACTION,FOREIGN KEY(usersid) REFERENCES users(usersid) ON UPDATE CASCADE ON DELETE NO ACTION);

create table loading(loadingid int(10) not null auto_increment primary key,loaddate timestamp,loadstatus int(5),vehicleid int(10),registerparcelid int(10),usersid int(10),FOREIGN KEY(registerparcelid) REFERENCES registerparcel(registerparcelid) ON UPDATE CASCADE ON DELETE NO ACTION,FOREIGN KEY(vehicleid) REFERENCES vehicle(vehicleid) ON UPDATE CASCADE ON DELETE NO ACTION,FOREIGN KEY(usersid) REFERENCES users(usersid) ON UPDATE CASCADE ON DELETE NO ACTION);

create table offloadparcel(offloadparcelid int(10) not null auto_increment primary key,offloaddate timestamp,offloadstatus int(5),loadingid int(10),usersid int(10),FOREIGN KEY(loadingid) REFERENCES loading(loadingid) ON UPDATE CASCADE ON DELETE NO ACTION,FOREIGN KEY(usersid) REFERENCES users(usersid) ON UPDATE CASCADE ON DELETE NO ACTION);

create table parcelcollection(parcelcollectionid int(10) not null auto_increment primary key,collectiondate timestamp,collectoridno varchar(10),collectionstatus int(5),offloadparcelid int(10),usersid int(10),FOREIGN KEY(offloadparcelid) REFERENCES offloadparcel(offloadparcelid) ON UPDATE CASCADE ON DELETE NO ACTION,FOREIGN KEY(usersid) REFERENCES users(usersid) ON UPDATE CASCADE ON DELETE NO ACTION);

insert into office(officename) values ('NAIROBI'),('CHUKA'),('RUNYENJES'),('EMBU'),('MERU');

drop table parcelcollection;
drop table offloadparcel;
drop table loading;
drop table registerparcel;
drop table transactionnumber;
drop table office;
drop table vehicle;

select rp.registerparcelid,rp.parcelname,o.officename from registerparcel rp,loading ld,office o where rp.registerparcelid = ld.registerparcelid and rp.officetoid = o.officeid and rp.transactionstatus = 1 and rp.transactionnumberid is not null and ld.loadstatus = 1 and ld.loadingid not in (select loadingid from offloadparcel where offloadstatus = 1);