#
# Structure for the `Agent` table : 
#

CREATE TABLE `Agent` (
  `AgentId` int(11) NOT NULL auto_increment,
  `Username` varchar(20) NOT NULL default '',
  `FirstName` varchar(20) NOT NULL default '',
  `LastName` varchar(20) NOT NULL default '',
  `Password` varchar(60) NOT NULL default '',
  `Email` varchar(40) NOT NULL default '',
  `Type` tinyint(1) NOT NULL default '1',
  `Active` tinyint(1) NOT NULL default '0',
  `AvailableForTicket` tinyint(1) NOT NULL default '1',
  `AvailableForBug` tinyint(1) NOT NULL default '1',
  `HireDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `ReviewDate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`AgentId`),
  UNIQUE KEY `AgentId` (`AgentId`),
  UNIQUE KEY `Username_2` (`Username`),
  KEY `Username` (`Username`),
  KEY `AgentPassword` (`Username`(5),`Password`(5)),
  KEY `FirstName` (`FirstName`(10)),
  KEY `LastName` (`LastName`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `Customer` table : 
#

CREATE TABLE `Customer` (
  `CustomerId` int(11) NOT NULL auto_increment,
  `Name` varchar(60) NOT NULL default '',
  `Active` tinyint(1) NOT NULL default '1',
  `UserCode` varchar(6) NOT NULL default '',
  PRIMARY KEY  (`CustomerId`),
  UNIQUE KEY `CustomerId` (`CustomerId`),
  KEY `UserCode` (`UserCode`),
  KEY `Active` (`Active`),
  KEY `Name` (`Name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `Priority` table : 
#

CREATE TABLE `Priority` (
  `PriorityId` int(11) NOT NULL auto_increment,
  `Priority` varchar(16) NOT NULL default '',
  `Color` varchar(6) NOT NULL default '',
  `Days` tinyint(4) NOT NULL default '3',
  `Override` tinyint(1) NOT NULL default '0',
  `Main` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`PriorityId`),
  UNIQUE KEY `PriorityId` (`PriorityId`),
  KEY `Priority` (`Priority`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `BugStatus` table : 
#

CREATE TABLE `BugStatus` (
  `BugStatusId` int(11) NOT NULL auto_increment,
  `Status` varchar(20) NOT NULL default '',
  `Completed` tinyint(1) NOT NULL default '0',
  `Color` varchar(6) NOT NULL default '',
  PRIMARY KEY  (`BugStatusId`),
  UNIQUE KEY `BugStatusId` (`BugStatusId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `BugType` table : 
#

CREATE TABLE `BugType` (
  `BugTypeId` int(11) NOT NULL auto_increment,
  `Type` varchar(20) NOT NULL default '0',
  PRIMARY KEY  (`BugTypeId`),
  UNIQUE KEY `BugTypeId` (`BugTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `Bug` table : 
#

CREATE TABLE `Bug` (
  `BugId` int(11) NOT NULL auto_increment,
  `ProblemId` int(11) NOT NULL default '0',
  `IssueId` int(11) NOT NULL default '0',
  `CustomerId` int(11) NOT NULL default '0',
  `PriorityId` int(11) NOT NULL default '0',
  `BugStatusId` int(11) NOT NULL default '0',
  `BugTypeId` int(11) NOT NULL default '0',
  `BugSeverityId` int(11) NOT NULL default '0',
  `ReproducibilityId` int(11) NOT NULL default '0',
  `Synopsis` varchar(70) NOT NULL default '',
  `Description` text NOT NULL,
  `Program` text NOT NULL,
  `Source` text NOT NULL,
  `Closed` tinyint(1) NOT NULL default '0',
  `Tested` tinyint(1) NOT NULL default '0',
  `ClosedDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `Entered` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`BugId`),
  UNIQUE KEY `BugId` (`BugId`),
  KEY `ProblemId` (`ProblemId`),
  KEY `IssueId` (`IssueId`),
  KEY `CustomerId` (`CustomerId`),
  KEY `BugStatusId` (`BugStatusId`),
  KEY `BugTypeId` (`BugTypeId`),
  KEY `BugSeverityId` (`BugSeverityId`),
  KEY `PriorityId` (`PriorityId`),
  KEY `ReproducibilityId` (`ReproducibilityId`),
  KEY `ClosedDate` (`ClosedDate`),
  KEY `Entered` (`Entered`),
  KEY `Description` (`Description`(50)),
  KEY `Program` (`Program`(50)),
  KEY `Source` (`Source`(50)),
  KEY `Synopsis` (`Synopsis`(20)),
  KEY `BugForeign` (`ProblemId`,`IssueId`,`CustomerId`,`PriorityId`,`BugStatusId`,`BugTypeId`,`BugSeverityId`,`ReproducibilityId`),
  KEY `BugInfo` (`BugId`,`ProblemId`,`IssueId`,`CustomerId`,`PriorityId`,`BugStatusId`,`BugTypeId`,`BugSeverityId`,`ReproducibilityId`),
  CONSTRAINT `Bug_ibfk_1` FOREIGN KEY (`ProblemId`) REFERENCES `Problem` (`ProblemId`) ON UPDATE CASCADE,
  CONSTRAINT `Bug_ibfk_2` FOREIGN KEY (`CustomerId`) REFERENCES `Customer` (`CustomerId`) ON UPDATE CASCADE,
  CONSTRAINT `Bug_ibfk_3` FOREIGN KEY (`PriorityId`) REFERENCES `Priority` (`PriorityId`) ON UPDATE CASCADE,
  CONSTRAINT `Bug_ibfk_4` FOREIGN KEY (`BugStatusId`) REFERENCES `BugStatus` (`BugStatusId`) ON UPDATE CASCADE,
  CONSTRAINT `Bug_ibfk_5` FOREIGN KEY (`BugTypeId`) REFERENCES `BugType` (`BugTypeId`) ON UPDATE CASCADE,
  CONSTRAINT `Bug_ibfk_6` FOREIGN KEY (`BugSeverityId`) REFERENCES `BugSeverity` (`BugSeverityId`) ON UPDATE CASCADE,
  CONSTRAINT `Bug_ibfk_7` FOREIGN KEY (`ReproducibilityId`) REFERENCES `Reproducibility` (`ReproducibilityId`) ON UPDATE CASCADE,
  CONSTRAINT `Bug_ibfk_8` FOREIGN KEY (`IssueId`) REFERENCES `Issue` (`IssueId`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `Revision` table : 
#

CREATE TABLE `Revision` (
  `RevisionId` int(11) NOT NULL auto_increment,
  `Revision` varchar(20) NOT NULL default '',
  `Supported` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`RevisionId`),
  UNIQUE KEY `RevisionId` (`RevisionId`),
  KEY `Revision` (`Revision`(10)),
  KEY `Supported` (`Supported`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `BugAgent` table : 
#

CREATE TABLE `BugAgent` (
  `BugAgentId` int(11) NOT NULL auto_increment,
  `BugId` int(11) NOT NULL default '0',
  `AgentId` int(11) NOT NULL default '0',
  `AgentRoleId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`BugAgentId`),
  UNIQUE KEY `BugAgentId` (`BugAgentId`),
  KEY `BugId` (`BugId`),
  KEY `AgentId` (`AgentId`),
  KEY `AgentRoleId` (`AgentRoleId`),
  KEY `BugAgentRole` (`BugId`,`AgentId`,`AgentRoleId`),
  CONSTRAINT `BugAgent_ibfk_1` FOREIGN KEY (`BugId`) REFERENCES `Bug` (`BugId`) ON UPDATE CASCADE,
  CONSTRAINT `BugAgent_ibfk_2` FOREIGN KEY (`AgentId`) REFERENCES `Agent` (`AgentId`) ON UPDATE CASCADE,
  CONSTRAINT `BugAgent_ibfk_3` FOREIGN KEY (`AgentRoleId`) REFERENCES `AgentRole` (`AgentRoleId`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `BugRev` table : 
#

CREATE TABLE `BugRev` (
  `BugRevId` int(11) NOT NULL auto_increment,
  `BugId` int(11) NOT NULL default '0',
  `BugStatusId` int(11) NOT NULL default '0',
  `RevisionId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`BugRevId`),
  UNIQUE KEY `BugRevId` (`BugRevId`),
  KEY `BugId` (`BugId`),
  KEY `BugStatusId` (`BugStatusId`),
  KEY `RevisionId` (`RevisionId`),
  CONSTRAINT `BugRev_ibfk_1` FOREIGN KEY (`BugId`) REFERENCES `Bug` (`BugId`) ON UPDATE CASCADE,
  CONSTRAINT `BugRev_ibfk_2` FOREIGN KEY (`BugStatusId`) REFERENCES `BugStatus` (`BugStatusId`) ON UPDATE CASCADE,
  CONSTRAINT `BugRev_ibfk_3` FOREIGN KEY (`RevisionId`) REFERENCES `Revision` (`RevisionId`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;