################################################
# ADD ALL TABLES:
################################################


drop table if exists employees;
create table employees (
    empID         varchar(20) not null,
    email         varchar(50),
    firstName       varchar(50) not null,
    lastName       varchar(50) not null,
    
    phoneWork       varchar(16),
    phoneHome       varchar(16),
    phoneMobile       varchar(16),  
    
    dateTerminated     date,
    reasonTerminated   varchar(16),

    password       varchar(10),
    bertAdminLevel     int,
    
  ## STATUS INFO:
  #  atWork         boolean,
  #  status         tinytext,
  #  timeDeparture     datetime,
  #  timeExpectedBack   datetime,
  
  #  department       text not null,
  
    primary key (empID)
);


drop table rooms;
create table rooms (
    roomID         varchar(20) not null,
    roomType       varchar(50),
    description       text,
    #timeMaxLease     time,
    
    primary key (roomID)
);
drop table room_bookings;
create table room_bookings (
    roomBookingID     int unsigned not null   AUTO_INCREMENT,
    empID         varchar(20) not null,
    roomID         varchar(20) not null,
    
    timeStart       datetime,
    timeEnd         datetime,
    recurring       int,
    
    #nameOfRequester     varchar(100),
    #  #NOTE: set recurring to 0 if not reoccurring.
    
    primary key (roomBookingID),
    foreign key (empID) references empoyees(empID),
    foreign key (roomID) references rooms(roomID)
);

drop table vehicles ;
create table vehicles (
    regNumber       varchar(20) not null,
    type         varchar(50),
    modelNumber       text,
    description       text,
    #timeMaxLease     time,
    
    primary key (regNumber)
);
drop table vehicle_bookings;
create table vehicle_bookings (
    vehicleBookingID   int unsigned not null   AUTO_INCREMENT,
    empID         varchar(20) not null,
    regNumber       varchar(20) not null,
    
    timeStart       datetime,
    timeEnd         datetime,
    recurring       int,
    
    primary key (vehicleBookingID),
    foreign key (empID) references empoyees(empID),
    foreign key (regNumber) references vehicles(regNumber)
);


drop table equipment;
create table equipment (
    equipmentID       varchar(20) not null,
    type         varchar(50),
    modelNumber       text,
    description       text,
    requireAck       bool,
    value         text,
    #timeMaxLease     time,
    #department       text not null,
  
    primary key (equipmentID)
);
drop table equipment_bookings;
create table equipment_bookings (
    equipmentBookingID   int unsigned not null   AUTO_INCREMENT,
    empID         varchar(20) not null,
    equipmentID       varchar(20) not null,
    
    timeStart       datetime,
    timeEnd         datetime,
    recurring       int,
    
    primary key (equipmentBookingID),
    foreign key (empID) references empoyees(empID),
    foreign key (equipmentID) references equipment(equipmentID)
);








################################################
# POPULATE TABLES WITH TEST DATA:
################################################

DELETE FROM employees;
INSERT INTO employees VALUES ('jc127052','cheryl.thomas@jcu.edu.au','Cheryl','Thomas', '1111','40111111',NULL,NULL,NULL, 'cheryl', 1);
INSERT INTO employees VALUES ('jc125786','benjamin.ingram@jcu.edu.au','Benjamin','Ingram', '2222','40222222','0422222222',NULL,NULL, 'benjamin', 0);
INSERT INTO employees VALUES ('jc130551','andrew_noske@yahoo.com','Andrew','Noske', NULL,'40222222','0422222222',NULL,NULL, 'andrew', 0);
INSERT INTO employees VALUES ('jc123950','timothy.anderson@jcu.edu.au','Timothy','Anderson', '4444','4044444',NULL,'2003-08-01','Retrenched', 'timothy', 0);

DELETE FROM rooms;
INSERT INTO rooms VALUES ('E1.015','conference','has white boards & most software');
INSERT INTO rooms VALUES ('E1.017','conference','feels like a prison');
INSERT INTO rooms VALUES ('B1.104','tele-conference','good as an interrogation room');

DELETE FROM room_bookings;
INSERT INTO room_bookings VALUES ( NULL,'jc127052','E1.015','2003-08-10 13:00:00','2003-08-10 15:30:00',0);
INSERT INTO room_bookings VALUES ( NULL,'jc130551','E1.015','2003-08-17 09:00:00','2003-08-17 11:00:00',0);
INSERT INTO room_bookings VALUES ( NULL,'jc130551','E1.017','2003-08-17 13:00:00','2003-08-17 15:00:00',0);
INSERT INTO room_bookings VALUES ( NULL,'jc127052','E1.015','2003-08-24 13:00:00','2003-08-24 16:00:00',1);

DELETE FROM vehicles;
INSERT INTO vehicles VALUES ('007eye','toyota','1983 edition landcruiser','we call it the beast');
INSERT INTO vehicles VALUES ('123fly','holden','1995 edition sadan','drives like a treat');

DELETE FROM vehicle_bookings;
INSERT INTO vehicle_bookings VALUES ( NULL,'jc130551','007eye','2003-08-17 9:00:00','2003-08-17 20:00:00',0);
INSERT INTO vehicle_bookings VALUES ( NULL,'jc123950','123fly','2003-08-18 12:00:00','2003-08-18 15:00:00',0);

DELETE FROM equipment;
INSERT INTO equipment VALUES ('100','digital camera','kodak 3120','3 mega pixels',0,'$1000');
INSERT INTO equipment VALUES ('102','video projector','bulklybrand 1900','needs repair',0,'$60');

DELETE FROM equipment_bookings;
INSERT INTO equipment_bookings VALUES (NULL,'jc123950','100','2003-08-18 12:00:00','2003-08-18 15:00:00',0);
INSERT INTO equipment_bookings VALUES (NULL,'jc130551','100','2003-08-18 16:00:00','2003-08-18 18:00:00',0);