DROP TABLE IF EXISTS staff;
create table staff (
`id` int(11) unsigned not null AUTO_INCREMENT,
`username` varchar(30),
`staffName` varchar(150),
`dob` date default null,
`phoneNumber` varchar(16),
`gender`  varchar(7),
`maritalStatus` varchar(9),
`address` text,
`dp` varchar(100),
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS client;
create table client (
`id` int(11) unsigned not null AUTO_INCREMENT,
`cName` varchar(200),
`cPerson` varchar(150),
`cPhone` varchar(16),
`cFax` varchar(16),
`cMail`  varchar(100),
`cChargeType` varchar(10),
`cRelSatff` varchar(150),
`address` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS vendor;
create table vendor (
`id` int(11) unsigned not null AUTO_INCREMENT,
`vName` varchar(200),
`vPerson` varchar(150),
`vPhone` varchar(16),
`vFax` varchar(16),
`vMail`  varchar(100),
`cRelSatff` varchar(150),
`address` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS ofiice;
create table office (
`id` int(11) unsigned not null AUTO_INCREMENT,
`oCity` varchar(200),
`oName` varchar(150),
`oPhone` varchar(16),
`oTime` varchar(16),
`oMail`  varchar(100),
`oRelSatff` varchar(150),
`address` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS city;
create table city (
`id` int(11) unsigned not null AUTO_INCREMENT,
`cRegionName` varchar(200),
`cName` varchar(150),
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS region;
create table region (
`id` int(11) unsigned not null AUTO_INCREMENT,
`rName` varchar(200),
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS charge;
create table charge (
`id` int(11) unsigned not null AUTO_INCREMENT,
`cName` varchar(200),
`cChargeType` varchar(50),
`chargeType` varchar(50),
`cRegion` varchar(100),
`cCity`  varchar(100),
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS invoice;
create table invoice (
`id` int(11) unsigned not null AUTO_INCREMENT,
  `inv_client_id` bigint(20) unsigned DEFAULT NULL,
  `inv_star_date` datetime DEFAULT NULL,
  `inv_end_date` datetime DEFAULT NULL,
  `inv_gen_date` datetime DEFAULT NULL,
  `inv_amount` double DEFAULT NULL,
  `inv_paid` tinyint(1) DEFAULT NULL,
  `inv_paid_date` datetime DEFAULT NULL,
  `inv_gen_id` bigint(20) unsigned DEFAULT NULL,
  `inv_paid_id` bigint(20) unsigned DEFAULT NULL,
  `inv_paid_ref` varchar(50) DEFAULT NULL,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS payment;
create table payment (
`id` int(11) unsigned not null AUTO_INCREMENT,
`iRefNumber` varchar(50),
`cName` varchar(200),
`iDate` date default null,
`iAmount` varchar(100),
`iBankRef`  varchar(100),
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS fault;
create table fault (
`id` int(11) unsigned not null AUTO_INCREMENT,
`aType` varchar(200),
`aCategory` varchar(50),
`aName` varchar(50),
`aFaultRepDate` date default null,
`aUser`  varchar(100),
`aComment` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS repair;
create table repair (
`id` int(11) unsigned not null AUTO_INCREMENT,
`vName` varchar(200),
`rControlStaff` varchar(100),
`rPickupdate` date default null,
`rCost`  varchar(100),
`rDescription` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS automobile;
create table automobile (
`id` int(11) unsigned not null AUTO_INCREMENT,
`aCatName` varchar(200),
`aRegNumber` varchar(50),
`aNumber` varchar(50),
`aChasisNumber` varchar(50),
`aEngineNumber` varchar(50),
`autoName` varchar(100),
`aStatus` varchar(50),
`aOfficeLocation` varchar(100),
`aStaff` varchar(100),
`acquisitionDate` date default null,
`remark` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS autoCategory;
create table autoCategory (
`id` int(11) unsigned not null AUTO_INCREMENT,
`aCatName` varchar(200),
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS assets;
create table assets (
`id` int(11) unsigned not null AUTO_INCREMENT,
`aClassName` varchar(200),
`aSerialNumber` varchar(50),
`aModel` varchar(50),
`assName` varchar(100),
`assStatus` varchar(50),
`assOfficeLoacation` varchar(100),
`assStaff` varchar(100),
`assAcquisitionDate` date default null,
`assRemarks` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS assetClass;
create table assetClass (
`id` int(11) unsigned not null AUTO_INCREMENT,
`assClassName` varchar(200),
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS consignment;
create table consignment (
`id` int(11) unsigned not null AUTO_INCREMENT,
`awbNumber` varchar(30),
`cName` varchar(50),
`sendersName` varchar(100),
`recieversName` varchar(100),
`recieversPhone` varchar(15),
`terminatingCity` varchar(100),
`pickupDate` date default null,
`typeofConsignment`  varchar(100),
`weight` varchar(10),
`refNumber` varchar(10),
`quantity` varchar(10),
`bookingMode` varchar(7),
`modeofShipment` varchar(10),
`dispatchTime` varchar(20),
`sendingOffice` varchar(100),
`destinatioOffice` varchar(100),
`description` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS sendConsignment;
create table sendConsignment (
`id` int(11) unsigned not null AUTO_INCREMENT,
`awbNumbers` varchar (200),
`sendingType` varchar(10),
`sendingOffice` varchar(100),
`recievingOffice` varchar(100),
`sendingStaff` varchar(100),
`sendingDate` date default null,
`comments` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS recievConsignment;
create table recievConsignment (
`id` int(11) unsigned not null AUTO_INCREMENT,
`recieveType` varchar(10),
`awbNumbers` varchar(30),
`receivedOffice` varchar(100),
`receivingStaff` varchar(100),
`receivingDate` date default null,
`comments` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS recordDelivery;
create table recordDelivery (
`id` int(11) unsigned not null AUTO_INCREMENT,
`awbNumbers` varchar(30),
`recipientName` varchar(100),
`receiversName` varchar(100),
`collectionOffice` varchar(100),
`releasingStaff` varchar(100),
`deliveryDate` date default null,
`comments` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS recordMovement;
create table recordMovement (
`id` int(11) unsigned not null AUTO_INCREMENT,
`staffName` varchar(100),
`authorisingStaff` varchar(100),
`visitingAddress` text,
`timeOf` varchar(30),
`projectedReturnTime` varchar(30),
`movementDate` date default null,
`comments` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS recordReturn;
create table recordReturn (
`id` int(11) unsigned not null AUTO_INCREMENT,
`staffName` varchar(100),
`authorisingStaff` varchar(100),
`visitedAddress` text,
`expectedReturnTime` varchar(30),
`returnTime` varchar(30),
`confirmStaff` varchar(100),
`movementDate` date default null,
`status` varchar(50),
`comments` text,
`createdBy` varchar(30),
`last_updated` date default null,
primary key (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `active_guests` (
  `ip` varchar(15) NOT NULL,
  `timestamp` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `active_users` (
  `username` varchar(30) NOT NULL,
  `timestamp` int(11) unsigned NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `banned_users` (
  `username` varchar(30) NOT NULL,
  `timestamp` int(11) unsigned NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `users` (
  `username` varchar(30) NOT NULL,
  `password` varchar(32) DEFAULT NULL,
  `userid` varchar(32) DEFAULT NULL,
  `userlevel` tinyint(1) unsigned NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `timestamp` int(11) unsigned NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;