text: -- phpMyAdmin SQL Dump
  -- version 4.9.0.1
  -- https://www.phpmyadmin.net/
  --
  -- Host: 127.0.0.1
  -- Generation Time: Nov 04, 2019 at 07:03 AM
  -- Server version: 10.4.6-MariaDB
  -- PHP Version: 7.3.8
  
  SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  SET AUTOCOMMIT = 0;
  START TRANSACTION;
  SET time_zone = "+00:00";
  
  
  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  /*!40101 SET NAMES utf8mb4 */;
  
  --
  -- Database: `ems`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `administrators`
  --
  
  CREATE TABLE `administrators` (
    `id` int(11) NOT NULL,
    `firstName` varchar(255) NOT NULL,
    `lastName` varchar(255) NOT NULL,
    `username` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL,
    `confirmPassword` varchar(255) NOT NULL,
    `phone` int(11) NOT NULL,
    `emergency_phone` int(11) NOT NULL,
    `job_group` varchar(255) NOT NULL,
    `salary` int(11) NOT NULL,
    `qualifications` varchar(255) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `administrators`
  --
  
  INSERT INTO `administrators` (`id`, `firstName`, `lastName`, `username`, `email`, `password`, `confirmPassword`, `phone`, `emergency_phone`, `job_group`, `salary`, `qualifications`) VALUES
  (1, 'Charity', 'Kemei', 'ckemei', 'ckemei@gmail.com', 'e10adc3949ba59abbe56e057f20f883e', 'e10adc3949ba59abbe56e057f20f883e', 712345678, 734543555, 'Software Engineering', 200000, 'MSc Software Engineering');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `clients`
  --
  
  CREATE TABLE `clients` (
    `id` int(11) NOT NULL,
    `firstName` varchar(255) NOT NULL,
    `lastName` varchar(255) NOT NULL,
    `organization` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `phone` int(11) NOT NULL,
    `pid` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `clients`
  --
  
  INSERT INTO `clients` (`id`, `firstName`, `lastName`, `organization`, `email`, `phone`, `pid`) VALUES
  (1, 'Stacy', 'Kimilu', 'Strathmore University', 'skimilu@gmail.com', 723534345, 1),
  (2, 'Hellen   ', '   Simam', '   Bitwise Solutions', 'Hsimam@mail.me', 776543456, 65432),
  (3, 'Dopple ', ' Ganger', ' More Cows Ltd', 'goangdop@gmail.com', 787654321, 76543);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `departments`
  --
  
  CREATE TABLE `departments` (
    `id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `departments`
  --
  
  INSERT INTO `departments` (`id`, `name`) VALUES
  (1, 'Web Development'),
  (2, 'Android Development'),
  (3, 'Cloud Computing'),
  (4, 'Artificial Intelligence');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `designations`
  --
  
  CREATE TABLE `designations` (
    `id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `designations`
  --
  
  INSERT INTO `designations` (`id`, `name`) VALUES
  (1, 'Front-End Developer'),
  (2, 'Back-End Developer'),
  (3, 'Full-Stack Developer');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `employees`
  --
  
  CREATE TABLE `employees` (
    `id` int(11) NOT NULL,
    `firstName` varchar(255) NOT NULL,
    `lastName` varchar(255) NOT NULL,
    `username` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL DEFAULT 'password',
    `confirmPassword` varchar(255) NOT NULL DEFAULT 'password',
    `phone` int(11) NOT NULL,
    `emergency_phone` int(11) NOT NULL,
    `job_group` varchar(255) NOT NULL,
    `salary` int(11) NOT NULL,
    `qualifications` varchar(255) NOT NULL,
    `joiningDate` date NOT NULL DEFAULT current_timestamp(),
    `department_id` int(11) NOT NULL,
    `designation_id` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `employees`
  --
  
  INSERT INTO `employees` (`id`, `firstName`, `lastName`, `username`, `email`, `password`, `confirmPassword`, `phone`, `emergency_phone`, `job_group`, `salary`, `qualifications`, `joiningDate`, `department_id`, `designation_id`) VALUES
  (26, 'Joe', 'Kadenge', 'kadenge', 'kadenge@mail.com', '5f4dcc3b5aa765d61d8327deb882cf99', '5f4dcc3b5aa765d61d8327deb882cf99', 787968534, 0, '', 0, '', '2019-10-24', 2, 2);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `leave_details`
  --
  
  CREATE TABLE `leave_details` (
    `id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    `duration` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `leave_details`
  --
  
  INSERT INTO `leave_details` (`id`, `name`, `duration`) VALUES
  (1, 'Medical Leave', 30),
  (2, 'Academic Leave', 90),
  (3, 'Paternal Leave', 30),
  (4, 'Maternal Leave', 90),
  (8, 'New', 34);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `managers`
  --
  
  CREATE TABLE `managers` (
    `id` int(11) NOT NULL,
    `firstName` varchar(255) NOT NULL,
    `lastName` varchar(255) NOT NULL,
    `username` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL DEFAULT 'password',
    `confirmPassword` varchar(255) NOT NULL DEFAULT 'password',
    `phone` int(11) NOT NULL,
    `emergency_phone` int(11) NOT NULL,
    `job_group` varchar(255) NOT NULL,
    `salary` int(11) NOT NULL,
    `qualifications` varchar(255) NOT NULL,
    `joiningDate` date NOT NULL DEFAULT current_timestamp(),
    `department_id` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `managers`
  --
  
  INSERT INTO `managers` (`id`, `firstName`, `lastName`, `username`, `email`, `password`, `confirmPassword`, `phone`, `emergency_phone`, `job_group`, `salary`, `qualifications`, `joiningDate`, `department_id`) VALUES
  (14, 'Kevin', 'Strand', 'strand', 'strand@gmail.com', '5f4dcc3b5aa765d61d8327deb882cf99', '5f4dcc3b5aa765d61d8327deb882cf99', 741710274, 787654366, 'Android Development', 100000, 'BSc Computer Science', '2019-10-28', 2);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `on_leave`
  --
  
  CREATE TABLE `on_leave` (
    `id` int(11) NOT NULL,
    `leave_id` int(11) NOT NULL,
    `employee_id` int(11) NOT NULL,
    `reason` varchar(255) NOT NULL,
    `status` tinyint(1) NOT NULL DEFAULT 0,
    `start` date NOT NULL DEFAULT current_timestamp()
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `on_leave`
  --
  
  INSERT INTO `on_leave` (`id`, `leave_id`, `employee_id`, `reason`, `status`, `start`) VALUES
  (1, 2, 26, 'Further Studies', 0, '2019-11-04');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `on_leave_m`
  --
  
  CREATE TABLE `on_leave_m` (
    `id` int(11) NOT NULL,
    `leave_id` int(11) NOT NULL,
    `employee_id` int(11) NOT NULL,
    `reason` varchar(255) NOT NULL,
    `status` tinyint(1) NOT NULL DEFAULT 0,
    `start` date NOT NULL DEFAULT current_timestamp()
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `on_leave_m`
  --
  
  INSERT INTO `on_leave_m` (`id`, `leave_id`, `employee_id`, `reason`, `status`, `start`) VALUES
  (4, 4, 14, 'I am pregnant', 0, '2019-10-29'),
  (5, 2, 14, 'I am going to complete my Post Graduate studies', 0, '2019-10-29');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `projects`
  --
  
  CREATE TABLE `projects` (
    `id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    `client_id` int(11) NOT NULL,
    `start_date` date NOT NULL DEFAULT current_timestamp(),
    `end_date` date NOT NULL,
    `rate` int(11) NOT NULL,
    `rate_interval` varchar(255) NOT NULL,
    `priority` int(11) NOT NULL,
    `manager_id` int(11) NOT NULL,
    `description` varchar(255) NOT NULL,
    `pid` int(11) NOT NULL,
    `status` tinyint(1) NOT NULL DEFAULT 0
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `projects`
  --
  
  INSERT INTO `projects` (`id`, `name`, `client_id`, `start_date`, `end_date`, `rate`, `rate_interval`, `priority`, `manager_id`, `description`, `pid`, `status`) VALUES
  (4, 'Project 1', 1, '2019-10-28', '2019-10-31', 500, 'Monthly', 2, 14, 'Build Website', 6789, 0),
  (5, 'Project 2', 3, '2019-10-28', '2019-12-27', 1000, 'modular', 3, 14, '<p>Open-Ended approach to solving an e-commerce issue</p>\r\n', 45675, 0),
  (6, 'Project 3', 3, '2019-10-28', '2020-01-27', 10, 'hourly', 1, 14, '<p>The user just wants a website that will portray his business in a good light.</p>\r\n', 87654, 0);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `running`
  --
  
  CREATE TABLE `running` (
    `id` int(11) NOT NULL,
    `project_id` int(11) NOT NULL,
    `team_id` int(11) NOT NULL,
    `employee_id` int(11) NOT NULL,
    `manager_id` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `running`
  --
  
  INSERT INTO `running` (`id`, `project_id`, `team_id`, `employee_id`, `manager_id`) VALUES
  (1, 4, 1, 26, 14);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `tasks`
  --
  
  CREATE TABLE `tasks` (
    `id` int(11) NOT NULL,
    `project_id` int(11) NOT NULL,
    `team_id` int(11) NOT NULL,
    `body` varchar(255) NOT NULL,
    `status` tinyint(1) NOT NULL DEFAULT 0
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `tasks`
  --
  
  INSERT INTO `tasks` (`id`, `project_id`, `team_id`, `body`, `status`) VALUES
  (9, 4, 1, 'Create a landing page', 1),
  (10, 5, 2, 'Do the Backend', 0),
  (11, 6, 5, 'Add a div', 1);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `teams`
  --
  
  CREATE TABLE `teams` (
    `id` int(11) NOT NULL,
    `project_id` int(11) NOT NULL,
    `manager_id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `teams`
  --
  
  INSERT INTO `teams` (`id`, `project_id`, `manager_id`, `name`) VALUES
  (1, 4, 14, 'Tigers'),
  (2, 5, 14, 'Panthers'),
  (5, 6, 14, 'Rangers');
  
  --
  -- Indexes for dumped tables
  --
  
  --
  -- Indexes for table `administrators`
  --
  ALTER TABLE `administrators`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `username` (`username`),
    ADD UNIQUE KEY `phone` (`phone`);
  
  --
  -- Indexes for table `clients`
  --
  ALTER TABLE `clients`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `pid` (`pid`);
  
  --
  -- Indexes for table `departments`
  --
  ALTER TABLE `departments`
    ADD PRIMARY KEY (`id`);
  
  --
  -- Indexes for table `designations`
  --
  ALTER TABLE `designations`
    ADD PRIMARY KEY (`id`);
  
  --
  -- Indexes for table `employees`
  --
  ALTER TABLE `employees`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `unique` (`email`,`phone`),
    ADD KEY `designation_id` (`designation_id`),
    ADD KEY `department_id` (`department_id`);
  
  --
  -- Indexes for table `leave_details`
  --
  ALTER TABLE `leave_details`
    ADD PRIMARY KEY (`id`);
  
  --
  -- Indexes for table `managers`
  --
  ALTER TABLE `managers`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `unique` (`email`,`phone`),
    ADD KEY `department_id` (`department_id`);
  
  --
  -- Indexes for table `on_leave`
  --
  ALTER TABLE `on_leave`
    ADD PRIMARY KEY (`id`),
    ADD KEY `leave_id` (`leave_id`),
    ADD KEY `employee_id` (`employee_id`);
  
  --
  -- Indexes for table `on_leave_m`
  --
  ALTER TABLE `on_leave_m`
    ADD PRIMARY KEY (`id`),
    ADD KEY `leave_id` (`leave_id`),
    ADD KEY `employee_id` (`employee_id`);
  
  --
  -- Indexes for table `projects`
  --
  ALTER TABLE `projects`
    ADD PRIMARY KEY (`id`),
    ADD KEY `client_id` (`client_id`),
    ADD KEY `manager_id` (`manager_id`);
  
  --
  -- Indexes for table `running`
  --
  ALTER TABLE `running`
    ADD PRIMARY KEY (`id`),
    ADD KEY `project_id` (`project_id`),
    ADD KEY `team_id` (`team_id`),
    ADD KEY `employee_id` (`employee_id`),
    ADD KEY `manager_id` (`manager_id`);
  
  --
  -- Indexes for table `tasks`
  --
  ALTER TABLE `tasks`
    ADD PRIMARY KEY (`id`),
    ADD KEY `project_id` (`project_id`),
    ADD KEY `team_id` (`team_id`);
  
  --
  -- Indexes for table `teams`
  --
  ALTER TABLE `teams`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `project_id_2` (`project_id`),
    ADD KEY `project_id` (`project_id`),
    ADD KEY `manager_id` (`manager_id`);
  
  --
  -- AUTO_INCREMENT for dumped tables
  --
  
  --
  -- AUTO_INCREMENT for table `administrators`
  --
  ALTER TABLE `administrators`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
  
  --
  -- AUTO_INCREMENT for table `clients`
  --
  ALTER TABLE `clients`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
  
  --
  -- AUTO_INCREMENT for table `departments`
  --
  ALTER TABLE `departments`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
  
  --
  -- AUTO_INCREMENT for table `designations`
  --
  ALTER TABLE `designations`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
  
  --
  -- AUTO_INCREMENT for table `employees`
  --
  ALTER TABLE `employees`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;
  
  --
  -- AUTO_INCREMENT for table `leave_details`
  --
  ALTER TABLE `leave_details`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
  
  --
  -- AUTO_INCREMENT for table `managers`
  --
  ALTER TABLE `managers`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
  
  --
  -- AUTO_INCREMENT for table `on_leave`
  --
  ALTER TABLE `on_leave`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  
  --
  -- AUTO_INCREMENT for table `on_leave_m`
  --
  ALTER TABLE `on_leave_m`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
  
  --
  -- AUTO_INCREMENT for table `projects`
  --
  ALTER TABLE `projects`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
  
  --
  -- AUTO_INCREMENT for table `running`
  --
  ALTER TABLE `running`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  
  --
  -- AUTO_INCREMENT for table `tasks`
  --
  ALTER TABLE `tasks`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
  
  --
  -- AUTO_INCREMENT for table `teams`
  --
  ALTER TABLE `teams`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
  
  --
  -- Constraints for dumped tables
  --
  
  --
  -- Constraints for table `employees`
  --
  ALTER TABLE `employees`
    ADD CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  --
  -- Constraints for table `managers`
  --
  ALTER TABLE `managers`
    ADD CONSTRAINT `managers_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  --
  -- Constraints for table `on_leave`
  --
  ALTER TABLE `on_leave`
    ADD CONSTRAINT `on_leave_ibfk_1` FOREIGN KEY (`leave_id`) REFERENCES `leave_details` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `on_leave_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  --
  -- Constraints for table `on_leave_m`
  --
  ALTER TABLE `on_leave_m`
    ADD CONSTRAINT `on_leave_m_ibfk_1` FOREIGN KEY (`leave_id`) REFERENCES `leave_details` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `on_leave_m_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `managers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  --
  -- Constraints for table `projects`
  --
  ALTER TABLE `projects`
    ADD CONSTRAINT `projects_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `projects_ibfk_3` FOREIGN KEY (`manager_id`) REFERENCES `managers` (`id`);
  
  --
  -- Constraints for table `tasks`
  --
  ALTER TABLE `tasks`
    ADD CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  --
  -- Constraints for table `teams`
  --
  ALTER TABLE `teams`
    ADD CONSTRAINT `teams_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `teams_ibfk_2` FOREIGN KEY (`manager_id`) REFERENCES `managers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  COMMIT;
  
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  