CREATE TABLE `bookedrooms` (
  `empid` varchar(5) NOT NULL,
  `roomid` int(10) NOT NULL,
  `startDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `endDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  KEY `roomid` (`roomid`),
  KEY `empId` (`empid`),
  CONSTRAINT `empId` FOREIGN KEY (`empid`) REFERENCES `signup` (`empid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `roomId` FOREIGN KEY (`roomid`) REFERENCES `roomlist` (`roomid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `equipmentlist` (
  `equipmentId` int(10) NOT NULL,
  `equipmentName` varchar(20) NOT NULL,
  PRIMARY KEY  (`equipmentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `equipreq` (
  `meetingid` int(11) NOT NULL,
  `empid` int(11) NOT NULL,
  `equipid` int(11) NOT NULL,
  KEY `meetingid` (`meetingid`),
  KEY `equipid` (`equipid`),
  CONSTRAINT `equipid` FOREIGN KEY (`equipid`) REFERENCES `equipmentlist` (`equipmentId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `meetingid` FOREIGN KEY (`meetingid`) REFERENCES `scheduledmeetings` (`meetingid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `roomlist` (
  `roomid` int(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `location` varchar(100) NOT NULL,
  PRIMARY KEY  (`roomid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `scheduledmeetings` (
  `meetingid` int(100) NOT NULL AUTO_INCREMENT,
  `startDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `roomid` int(10) DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  `endDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `empid` varchar(5) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  `Agenda` varchar(250) DEFAULT NULL,
  PRIMARY KEY  (`meetingid`,`empid`),
  KEY `room` (`roomid`),
  KEY `emp` (`empid`),
  CONSTRAINT `emp` FOREIGN KEY (`empid`) REFERENCES `signup` (`empid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `room` FOREIGN KEY (`roomid`) REFERENCES `roomlist` (`roomid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `signup` (
  `empid` varchar(5) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(40) NOT NULL,
  `isAdmin` int(1) NOT NULL,
  PRIMARY KEY  (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

