--
-- Host: localhost    Database: maintainemployment_dev
-- ------------------------------------------------------


--
-- Table structure for table `change_reason_categories`
--

CREATE TABLE `change_reason_categories` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `change_reason_categories`
--

INSERT INTO `change_reason_categories` VALUES (3,'Others'),(2,'Rotational Staff'),(1,'Temporary Assignment');

--
-- Table structure for table `change_reasons`
--

CREATE TABLE `change_reasons` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category_id` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_category` (`category_id`),
  CONSTRAINT `change_reasons_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `change_reason_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `change_reasons`
--

INSERT INTO `change_reasons` VALUES (1,'acting_for','Acting For',1),(2,'on_loan','On Loan',1),(3,'secondment','Secondment',1),(4,'end_of_secondment','End of Secondment',1),(5,'apprentice','Apprentice',2),(6,'cadet','Cadet',2),(7,'graduate','Graduate',2),(8,'training','Training',2),(9,'work_experience','Work Experience',2),(10,'fixed_term','Contract Extension - Fixed Term',3),(11,'internal_hire','Internal Hire',3),(12,'status_change','Change of Employment Status',3);

--
-- Table structure for table `delegations`
--

CREATE TABLE `delegations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `delegator` int(11) NOT NULL,
  `delegatee` int(11) NOT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `active` bit(1) DEFAULT b'1',
  PRIMARY KEY (`id`),
  KEY `fk_users_delegator` (`delegator`),
  KEY `fk_users_delegatee` (`delegatee`),
  KEY `idx_date_from` (`date_from`),
  KEY `idx_date_to` (`date_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `delegations`
--


--
-- Temporary table structure for view `divisions`
--

/*!50001 DROP VIEW IF EXISTS `divisions`*/;
/*!50001 CREATE TABLE `divisions` (
  `id` int(11),
  `name` varchar(255)
) ENGINE=MyISAM */;

--
-- Table structure for table `employee_group_pairings`
--

CREATE TABLE `employee_group_pairings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `subgroup_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_group` (`group_id`),
  KEY `idx_subgroup` (`subgroup_id`),
  CONSTRAINT `employee_group_pairings_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `employee_groups` (`id`),
  CONSTRAINT `employee_group_pairings_ibfk_2` FOREIGN KEY (`subgroup_id`) REFERENCES `employee_subgroups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `employee_group_pairings`
--

INSERT INTO `employee_group_pairings` VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4),(5,1,5),(6,1,6),(7,2,1),(8,2,2),(9,2,3),(10,2,4),(11,2,5),(12,2,6),(13,3,1),(14,3,2),(15,3,3),(16,3,4),(17,3,5),(18,3,6),(19,4,1),(20,4,2),(21,4,3),(22,4,4),(23,4,5),(24,4,6),(25,5,2),(26,5,4),(27,5,5),(28,6,7),(29,6,8),(30,6,9),(31,7,10),(32,7,11),(33,7,12);

--
-- Table structure for table `employee_groups`
--

CREATE TABLE `employee_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `employee_groups`
--

INSERT INTO `employee_groups` VALUES (5,'Casual Employee'),(3,'Fixed term Full-time'),(4,'Fixed term Part-time'),(6,'Indirect Labour'),(7,'Other'),(1,'Permanent Full Time'),(2,'Permanent Part Time');

--
-- Table structure for table `employee_subgroups`
--

CREATE TABLE `employee_subgroups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `employee_subgroups`
--

INSERT INTO `employee_subgroups` VALUES (6,'Commercial Graduate'),(8,'Contracted Services'),(10,'Director'),(4,'Engineers\' Agreement'),(2,'Enterprise Agreement'),(5,'Exec Asst Agreement'),(7,'Labour Agreement'),(9,'Other'),(11,'Paid Internship'),(3,'PM&S Agreement'),(1,'Senior Contract'),(12,'Unpaid Work Exp.');

--
-- Temporary table structure for view `login_codes`
--

/*!50001 DROP VIEW IF EXISTS `login_codes`*/;
/*!50001 CREATE TABLE `login_codes` (
  `service_number` int(11),
  `code` varchar(64),
  `valid_until` timestamp
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `pay_points`
--

/*!50001 DROP VIEW IF EXISTS `pay_points`*/;
/*!50001 CREATE TABLE `pay_points` (
  `id` int(11),
  `acronym` varchar(16),
  `name` varchar(64),
  `rate` decimal(8,2),
  `parent_pay_point` int(11)
) ENGINE=MyISAM */;

--
-- Table structure for table `permission_resources`
--

CREATE TABLE `permission_resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `permission_resources`
--

INSERT INTO `permission_resources` VALUES (1,'requests',NULL),(2,'users',NULL),(3,'managed_requests',NULL),(4,'applied_requests',NULL);

--
-- Table structure for table `permission_roles`
--

CREATE TABLE `permission_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `permission_roles`
--

INSERT INTO `permission_roles` VALUES (1,'superuser',NULL),(2,'manager',NULL),(3,'hr_admin',NULL);

--
-- Table structure for table `permission_rules`
--

CREATE TABLE `permission_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `resource_id` int(11) NOT NULL,
  `allowed` bit(1) DEFAULT b'1',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `fk_role_id` (`role_id`),
  KEY `fk_permission_resource_id` (`resource_id`),
  CONSTRAINT `permission_rules_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `permission_roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `permission_rules_ibfk_2` FOREIGN KEY (`resource_id`) REFERENCES `permission_resources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `permission_rules`
--

INSERT INTO `permission_rules` VALUES (1,'view',1,1,''),(2,'edit',1,1,''),(3,'mark_status_all',1,1,''),(4,'edit',1,2,''),(5,'view',2,3,''),(6,'edit',2,3,''),(7,'mark_status_approved',2,3,''),(8,'mark_status_declined_manager',2,3,''),(9,'view',2,4,''),(10,'view',3,1,''),(11,'edit',3,1,''),(12,'mark_status_completed',3,1,''),(13,'mark_status_declined_hr',3,1,'');

--
-- Table structure for table `request_work_hours`
--

CREATE TABLE `request_work_hours` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL,
  `day_of_week` tinyint(4) NOT NULL COMMENT 'Monday through Sunday (1-7)',
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_request_day` (`request_id`,`day_of_week`),
  CONSTRAINT `request_work_hours_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_work_hours`
--


--
-- Table structure for table `requests`
--

CREATE TABLE `requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_service_number` int(11) NOT NULL,
  `applicant_name` varchar(255) NOT NULL,
  `employee_service_number` int(11) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `approver_service_number` int(11) NOT NULL,
  `status_id` tinyint(4) NOT NULL DEFAULT '1',
  `last_status_changer` int(11) DEFAULT NULL,
  `status_reason` varchar(255) DEFAULT NULL,
  `date_submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `current_position_id` varchar(32) NOT NULL,
  `current_position_title` varchar(64) NOT NULL,
  `change_reason_id` tinyint(4) NOT NULL,
  `incumbent_name` varchar(255) DEFAULT NULL,
  `new_position_id` varchar(32) DEFAULT NULL,
  `new_position_title` varchar(64) DEFAULT NULL,
  `position_location` varchar(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  `cost_centre_id` varchar(255) DEFAULT NULL,
  `cost_centre_name` varchar(255) DEFAULT NULL,
  `cost_centre` varchar(255) DEFAULT NULL,
  `pay_point_id` int(11) DEFAULT NULL,
  `division_id` int(11) DEFAULT NULL,
  `branch` varchar(255) DEFAULT NULL,
  `section` varchar(255) DEFAULT NULL,
  `org_unit_name` varchar(255) DEFAULT NULL,
  `payslip_location` varchar(255) DEFAULT NULL,
  `work_schedule_rule_id` int(11) DEFAULT NULL,
  `tt_headquarters` varchar(255) DEFAULT NULL,
  `employee_group_id` int(11) DEFAULT NULL,
  `employee_subgroup_id` int(11) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `trp` tinyint(4) DEFAULT NULL,
  `work_phone` varchar(16) DEFAULT NULL,
  `work_mobile` varchar(16) DEFAULT NULL,
  `fax_extension` varchar(16) DEFAULT NULL,
  `rdo_date` date DEFAULT NULL,
  `is_shift_work` tinyint(1) DEFAULT NULL,
  `create_vacancy` tinyint(1) DEFAULT NULL,
  `delimit_vacancy` tinyint(1) DEFAULT NULL,
  `hours_per_week` smallint(6) DEFAULT NULL,
  `hours_per_month` smallint(6) DEFAULT NULL,
  `is_part_time` tinyint(1) DEFAULT NULL,
  `part_time_percent` tinyint(4) DEFAULT NULL,
  `comments` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status_id`),
  KEY `idx_change_reason` (`change_reason_id`),
  KEY `idx_division` (`division_id`),
  KEY `idx_approver` (`approver_service_number`),
  KEY `idx_last_changer` (`last_status_changer`),
  KEY `idx_work_sched_rule` (`work_schedule_rule_id`),
  KEY `idx_employee_group` (`employee_group_id`),
  KEY `idx_employee_subgroup` (`employee_subgroup_id`),
  CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`),
  CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`change_reason_id`) REFERENCES `change_reasons` (`id`),
  CONSTRAINT `requests_ibfk_6` FOREIGN KEY (`work_schedule_rule_id`) REFERENCES `work_schedule_rules` (`id`),
  CONSTRAINT `requests_ibfk_7` FOREIGN KEY (`employee_group_id`) REFERENCES `employee_groups` (`id`),
  CONSTRAINT `requests_ibfk_8` FOREIGN KEY (`employee_subgroup_id`) REFERENCES `employee_subgroups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `requests`
--

INSERT INTO `requests` VALUES (1,39887,'Peter Terry',39887,'Peter Terry',43880,1,NULL,NULL,'2013-05-03 22:55:22','some pos id','some pos titel',11,NULL,'sdfasdfasdfasd','sd','ghjjj','jjj',NULL,NULL,'kkkk',NULL,6,'jjjj','kkk','dfgh','some.addr@fake.ausgrid.com.au',9,'1',NULL,NULL,'2013-05-03','2013-05-10',55,'4453456','346456456','555','2013-05-03',1,0,NULL,NULL,NULL,NULL,NULL,NULL),(2,39887,'Peter Terry',39887,'Peter Terry',43880,1,NULL,NULL,'2013-05-03 22:56:32','Old Pos ID','Old Pos Title',10,NULL,'New Pos ID','New Pos Title',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,2,'2013-06-01','2014-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

--
-- Table structure for table `statuses`
--

CREATE TABLE `statuses` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `requires_reason` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `statuses`
--

INSERT INTO `statuses` VALUES (1,'pending','Pending',0),(2,'declined_manager','Declined',1),(3,'approved','Approved',0),(4,'completed','Completed',0),(5,'declined_hr','Declined',1);

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

CREATE TABLE `user_roles` (
  `service_number` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`service_number`,`role_id`),
  KEY `fk_roles_role_id` (`role_id`),
  CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `permission_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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


--
-- Temporary table structure for view `users`
--

/*!50001 DROP VIEW IF EXISTS `users`*/;
/*!50001 CREATE TABLE `users` (
  `service_number` int(11),
  `first_name` varchar(255),
  `last_name` varchar(255),
  `email_addr` varchar(128),
  `pin` varchar(16),
  `location` varchar(255),
  `level` varchar(255),
  `staff_type` varchar(255),
  `division` varchar(255),
  `branch` varchar(255),
  `section` varchar(255),
  `position` varchar(255),
  `cost_centre` int(11),
  `phone` varchar(16),
  `fax` varchar(16),
  `mobile` varchar(16),
  `mgmt_level` tinyint(4),
  `manager_service_number` int(11),
  `manager_mgmt_level` tinyint(4),
  `is_active` tinyint(1),
  `date_created` timestamp,
  `date_updated` timestamp
) ENGINE=MyISAM */;

--
-- Table structure for table `work_schedule_rules`
--

CREATE TABLE `work_schedule_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `work_schedule_rules`
--

INSERT INTO `work_schedule_rules` VALUES (1,'15 Hour Week'),(2,'16 Hour Week'),(3,'18 Hour Week'),(4,'19 Day Month'),(5,'20 Hour Week'),(6,'24 Hour Week'),(7,'25 Hour Week'),(8,'25.2 Hour Week'),(9,'32 Hour Week'),(10,'6 Day Shift'),(11,'7 Day Shift'),(12,'7.2 Hours 3 Day Week'),(13,'7.2 hours 4 day week'),(14,'7.2hr *2 Days'),(15,'8 Hour Week'),(16,'9 HR 4 Day (ULM)'),(17,'9DF Major RDO Fri'),(18,'9DF Major RDO Mon'),(19,'9DF Major RDO Wed'),(20,'9DF Minor RDO Fri'),(21,'9DF Minor RDO Mon'),(22,'9DF Minor RDO Thu'),(23,'9DF Minor RDO Tue'),(24,'9DF Minor RDO Wed'),(25,'CONT 7.30ST 15:30ET'),(26,'DIRECTOR'),(27,'Full time 35 Hour Week'),(28,'Full Time 36 hr week'),(29,'Hrs Booked Unlimited'),(30,'HRSULM 35 Hour Week');

--
-- Final view structure for view `divisions`
--

/*!50001 DROP TABLE IF EXISTS `divisions`*/;
/*!50001 DROP VIEW IF EXISTS `divisions`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `divisions` AS select `authportal_dev`.`divisions`.`id` AS `id`,`authportal_dev`.`divisions`.`name` AS `name` from `authportal_dev`.`divisions` */;

--
-- Final view structure for view `login_codes`
--

/*!50001 DROP TABLE IF EXISTS `login_codes`*/;
/*!50001 DROP VIEW IF EXISTS `login_codes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `login_codes` AS select `authportal_dev`.`login_codes`.`service_number` AS `service_number`,`authportal_dev`.`login_codes`.`code` AS `code`,`authportal_dev`.`login_codes`.`valid_until` AS `valid_until` from `authportal_dev`.`login_codes` */;

--
-- Final view structure for view `pay_points`
--

/*!50001 DROP TABLE IF EXISTS `pay_points`*/;
/*!50001 DROP VIEW IF EXISTS `pay_points`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `pay_points` AS select `authportal_dev`.`pay_points`.`id` AS `id`,`authportal_dev`.`pay_points`.`acronym` AS `acronym`,`authportal_dev`.`pay_points`.`name` AS `name`,`authportal_dev`.`pay_points`.`rate` AS `rate`,`authportal_dev`.`pay_points`.`parent_pay_point` AS `parent_pay_point` from `authportal_dev`.`pay_points` */;

--
-- Final view structure for view `users`
--

/*!50001 DROP TABLE IF EXISTS `users`*/;
/*!50001 DROP VIEW IF EXISTS `users`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `users` AS select `authportal_dev`.`users`.`service_number` AS `service_number`,`authportal_dev`.`users`.`first_name` AS `first_name`,`authportal_dev`.`users`.`last_name` AS `last_name`,`authportal_dev`.`users`.`email_addr` AS `email_addr`,`authportal_dev`.`users`.`pin` AS `pin`,`authportal_dev`.`users`.`location` AS `location`,`authportal_dev`.`users`.`level` AS `level`,`authportal_dev`.`users`.`staff_type` AS `staff_type`,`authportal_dev`.`users`.`division` AS `division`,`authportal_dev`.`users`.`branch` AS `branch`,`authportal_dev`.`users`.`section` AS `section`,`authportal_dev`.`users`.`position` AS `position`,`authportal_dev`.`users`.`cost_centre` AS `cost_centre`,`authportal_dev`.`users`.`phone` AS `phone`,`authportal_dev`.`users`.`fax` AS `fax`,`authportal_dev`.`users`.`mobile` AS `mobile`,`authportal_dev`.`users`.`mgmt_level` AS `mgmt_level`,`authportal_dev`.`users`.`manager_service_number` AS `manager_service_number`,`authportal_dev`.`users`.`manager_mgmt_level` AS `manager_mgmt_level`,`authportal_dev`.`users`.`is_active` AS `is_active`,`authportal_dev`.`users`.`date_created` AS `date_created`,`authportal_dev`.`users`.`date_updated` AS `date_updated` from `authportal_dev`.`users` */;


