-- 
-- Database: `time-tracker`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `categories`
-- 

CREATE TABLE IF NOT EXISTS `categories` (
  `CategoryID` int(11) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL,
  `ProjectID` int(11) NOT NULL,
  `ParentCategoryID` int(11) default '0',
  `Abbreviation` varchar(255) default NULL,
  `EstimateDuration` float(10,2) default '0.00',
  PRIMARY KEY  (`CategoryID`),
  UNIQUE KEY `UniqueNamePerProject` (`Name`,`ProjectID`),
  KEY `ProjectID` (`ProjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `categories`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `project`
-- 

CREATE TABLE IF NOT EXISTS `project` (
  `ProjectID` int(11) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL,
  `Description` varchar(255) default NULL,
  `CreationDate` datetime NOT NULL,
  `CompletionDate` datetime NOT NULL,
  `Disabled` tinyint(1) NOT NULL default '0',
  `EstimateDuration` float(10,2) NOT NULL default '0.00',
  `CreatorID` varchar(50) NOT NULL,
  `ManagerID` varchar(50) default NULL,
  PRIMARY KEY  (`ProjectID`),
  KEY `Name` (`Name`),
  KEY `CreatorID` (`CreatorID`),
  KEY `ManagerID` (`ManagerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `project`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `project_members`
-- 

CREATE TABLE IF NOT EXISTS `project_members` (
  `UserID` varchar(50) NOT NULL,
  `ProjectID` int(11) NOT NULL,
  PRIMARY KEY  (`UserID`,`ProjectID`),
  KEY `ProjectID` (`ProjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `project_members`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `role_types`
-- 

CREATE TABLE IF NOT EXISTS `role_types` (
  `RoleType` varchar(50) NOT NULL,
  `Description` varchar(255) NOT NULL,
  PRIMARY KEY  (`RoleType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `role_types`
-- 

INSERT INTO `role_types` (`RoleType`, `Description`) VALUES ('admin', 'Project administrator may additionally view the list of all users.'),
('consultant', 'Consultant may log time entries only.'),
('manager', 'Project manager may additionally edit all projects and view reports.');

-- --------------------------------------------------------

-- 
-- Table structure for table `signon`
-- 

CREATE TABLE IF NOT EXISTS `signon` (
  `SessionToken` varchar(32) NOT NULL,
  `Username` varchar(50) NOT NULL,
  `LastSignOnDate` datetime NOT NULL,
  PRIMARY KEY  (`SessionToken`),
  KEY `Username` (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `signon`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `time_entry`
-- 

CREATE TABLE IF NOT EXISTS `time_entry` (
  `EntryID` int(11) NOT NULL auto_increment,
  `EntryCreated` datetime NOT NULL,
  `Duration` float(10,2) NOT NULL default '0.00',
  `Description` varchar(1000) default NULL,
  `CategoryID` int(11) NOT NULL default '0',
  `EntryDate` datetime default NULL,
  `CreatorID` varchar(50) NOT NULL,
  `UserID` varchar(50) NOT NULL,
  PRIMARY KEY  (`EntryID`),
  KEY `CategoryID` (`CategoryID`),
  KEY `CreatorID` (`CreatorID`),
  KEY `UserID` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `time_entry`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `user_roles`
-- 

CREATE TABLE IF NOT EXISTS `user_roles` (
  `UserID` varchar(50) NOT NULL,
  `RoleType` varchar(50) NOT NULL,
  PRIMARY KEY  (`UserID`,`RoleType`),
  KEY `RoleType` (`RoleType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `user_roles`
-- 

INSERT INTO `user_roles` (`UserID`, `RoleType`) VALUES ('admin', 'admin'),
('admin', 'consultant'),
('consultant', 'consultant'),
('manager', 'consultant'),
('admin', 'manager'),
('manager', 'manager');

-- --------------------------------------------------------

-- 
-- Table structure for table `users`
-- 

CREATE TABLE IF NOT EXISTS `users` (
  `Username` varchar(50) NOT NULL,
  `Password` varchar(50) NOT NULL,
  `EmailAddress` varchar(100) NOT NULL,
  `Disabled` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `users`
-- 

INSERT INTO `users` (`Username`, `Password`, `EmailAddress`, `Disabled`) VALUES ('admin', '21232f297a57a5a743894a0e4a801fc3', 'admin@prado.local', 0),
('consultant', '7adfa4f2ba9323e6c1e024de375434b0', 'consultant@prado.local', 0),
('manager', '1d0258c2440a8d19e716292b231e3190', 'manager@prado.local', 0);

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `categories`
-- 
ALTER TABLE `categories`
  ADD CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`ProjectID`) REFERENCES `project` (`ProjectID`) ON DELETE CASCADE;

-- 
-- Constraints for table `project`
-- 
ALTER TABLE `project`
  ADD CONSTRAINT `project_ibfk_6` FOREIGN KEY (`ManagerID`) REFERENCES `users` (`Username`),
  ADD CONSTRAINT `project_ibfk_5` FOREIGN KEY (`CreatorID`) REFERENCES `users` (`Username`);

-- 
-- Constraints for table `project_members`
-- 
ALTER TABLE `project_members`
  ADD CONSTRAINT `project_members_ibfk_6` FOREIGN KEY (`ProjectID`) REFERENCES `project` (`ProjectID`) ON DELETE CASCADE,
  ADD CONSTRAINT `project_members_ibfk_5` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`) ON DELETE CASCADE;

-- 
-- Constraints for table `signon`
-- 
ALTER TABLE `signon`
  ADD CONSTRAINT `signon_ibfk_1` FOREIGN KEY (`Username`) REFERENCES `users` (`Username`);

-- 
-- Constraints for table `time_entry`
-- 
ALTER TABLE `time_entry`
  ADD CONSTRAINT `time_entry_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`),
  ADD CONSTRAINT `time_entry_ibfk_6` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`CategoryID`) ON DELETE CASCADE,
  ADD CONSTRAINT `time_entry_ibfk_7` FOREIGN KEY (`CreatorID`) REFERENCES `users` (`Username`);

-- 
-- Constraints for table `user_roles`
-- 
ALTER TABLE `user_roles`
  ADD CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`RoleType`) REFERENCES `role_types` (`RoleType`),
  ADD CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`);