--
-- Host: localhost    Database: leaverequest_dev
-- ------------------------------------------------------


--
-- 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`),
  CONSTRAINT `delegations_ibfk_1` FOREIGN KEY (`delegator`) REFERENCES `users` (`service_number`),
  CONSTRAINT `delegations_ibfk_2` FOREIGN KEY (`delegatee`) REFERENCES `users` (`service_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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


--
-- Table structure for table `divisions`
--

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

--
-- Dumping data for table `divisions`
--

INSERT INTO `divisions` VALUES (1,'DO&R','DO&R'),(2,'Transmission & System Operations','TSO'),(3,'SP&R','SP&R'),(4,'Shared Services','SS'),(5,'Retail','RT'),(6,'Finance & Corporate','FNC'),(7,'Office of the MD','OMD'),(8,'Human Resources','HR');

--
-- Table structure for table `holidays`
--

CREATE TABLE `holidays` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(11) DEFAULT NULL,
  `month` tinyint(4) DEFAULT NULL,
  `day` tinyint(4) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`year`,`month`,`day`),
  KEY `idx_year` (`year`),
  KEY `idx_month` (`month`),
  KEY `idx_day` (`day`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `holidays`
--

INSERT INTO `holidays` VALUES (1,2011,1,1,'New Year\'s Day'),(2,2011,1,3,'New Year\'s Day'),(3,2011,1,26,'Australia Day'),(4,2011,3,11,'Sydney Staff Day'),(5,2011,4,22,'Good Friday'),(6,2011,4,23,'Easter Saturday'),(7,2011,4,24,'Easter Sunday'),(8,2011,4,25,'Anzac Day'),(9,2011,4,26,'Easter Monday'),(10,2011,6,13,'Queen\'s Birthday'),(11,2011,10,3,'Labour Day'),(12,2011,11,18,'Hunter Staff'),(13,2011,12,26,'Boxing Day'),(14,2011,12,25,'Christmas Day'),(15,2011,12,27,'Christmas Day'),(16,2012,1,1,'New Year\'s Day'),(17,2012,1,2,'New Year\'s Day'),(18,2012,1,26,'Australia Day'),(19,2012,3,9,'Sydney Staff Day'),(20,2012,4,6,'Good Friday'),(21,2012,4,7,'Easter Saturday'),(22,2012,4,8,'Easter Sunday'),(23,2012,4,9,'Easter Monday'),(24,2012,4,25,'Anzac Day'),(25,2012,6,11,'Queen\'s Birthday'),(26,2012,10,1,'Labour Day'),(27,2012,11,23,'Hunter Staff'),(28,2012,12,25,'Christmas Day'),(29,2012,12,26,'Boxing Day'),(30,2013,1,1,'New Year\'s Day'),(31,2013,1,26,'Australia Day'),(32,2013,3,8,'Sydney Staff Day'),(33,2013,3,29,'Good Friday'),(34,2013,3,30,'Easter Saturday'),(35,2013,3,31,'Easter Sunday'),(36,2013,4,1,'Easter Monday'),(37,2013,4,25,'Anzac Day'),(38,2013,6,10,'Queen\'s Birthday'),(39,2013,10,7,'Labour Day'),(40,2013,11,22,'Hunter Staff'),(41,2013,12,25,'Christmas Day'),(42,2013,12,26,'Boxing Day');

--
-- Table structure for table `login_codes`
--

CREATE TABLE `login_codes` (
  `service_number` int(11) NOT NULL,
  `code` varchar(64) NOT NULL,
  `valid_until` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`service_number`,`code`),
  CONSTRAINT `login_codes_ibfk_1` FOREIGN KEY (`service_number`) REFERENCES `users` (`service_number`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `login_codes`
--


--
-- 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=3 DEFAULT CHARSET=utf8;

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

INSERT INTO `permission_resources` VALUES (1,'requests',NULL),(2,'requests_other',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,'level3_manager',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=11 DEFAULT CHARSET=utf8;

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

INSERT INTO `permission_rules` VALUES (1,'view_all',1,1,''),(2,'edit_all',1,1,''),(3,'view_all',1,2,''),(4,'edit_all',1,2,''),(5,'view_managed',2,1,''),(6,'edit_managed',2,1,''),(7,'view_managed',3,1,''),(8,'edit_managed',3,1,''),(9,'view_managed',3,2,''),(10,'edit_managed',3,2,'');

--
-- Table structure for table `request_accrued_rdos`
--

CREATE TABLE `request_accrued_rdos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `rdo_date` date NOT NULL,
  `been_taken` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`),
  CONSTRAINT `request_accrued_rdos_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `request_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_accrued_rdos`
--

INSERT INTO `request_accrued_rdos` VALUES (1,6,'2012-07-06',0);

--
-- Table structure for table `request_items`
--

CREATE TABLE `request_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_serial_number` tinyint(4) NOT NULL,
  `request_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `idx_request_id` (`request_id`),
  KEY `idx_status_id` (`status_id`),
  CONSTRAINT `request_items_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`),
  CONSTRAINT `request_items_ibfk_2` FOREIGN KEY (`status_id`) REFERENCES `request_statuses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_items`
--

INSERT INTO `request_items` VALUES (1,1,1,1),(2,3,2,1),(3,4,2,1),(4,1,2,1),(5,2,2,1),(6,1,3,1),(7,1,4,1);

--
-- Table structure for table `request_leave_period_details`
--

CREATE TABLE `request_leave_period_details` (
  `leave_period_id` int(11) NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `duration_weeks` tinyint(4) DEFAULT NULL,
  `duration_days` tinyint(4) DEFAULT NULL,
  `duration_hours` tinyint(4) DEFAULT NULL,
  `duration_minutes` tinyint(4) DEFAULT NULL,
  `pay_type_id` int(11) DEFAULT NULL,
  `prepayment_requested` tinyint(4) DEFAULT NULL,
  KEY `idx_leave_period` (`leave_period_id`),
  KEY `idx_pay_type` (`pay_type_id`),
  CONSTRAINT `request_leave_period_details_ibfk_1` FOREIGN KEY (`leave_period_id`) REFERENCES `request_leave_periods` (`id`) ON DELETE CASCADE,
  CONSTRAINT `request_leave_period_details_ibfk_2` FOREIGN KEY (`pay_type_id`) REFERENCES `request_leave_period_pay_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_leave_period_details`
--

INSERT INTO `request_leave_period_details` VALUES (1,'08:00:00','17:00:00',0,3,0,0,NULL,NULL),(2,'08:00:00','17:00:00',3,2,0,0,2,1);

--
-- Table structure for table `request_leave_period_pay_types`
--

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

--
-- Dumping data for table `request_leave_period_pay_types`
--

INSERT INTO `request_leave_period_pay_types` VALUES (2,'Full pay'),(1,'Half pay');

--
-- Table structure for table `request_leave_periods`
--

CREATE TABLE `request_leave_periods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `leave_type_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_leave_type` (`leave_type_id`),
  KEY `idx_item_id` (`item_id`),
  CONSTRAINT `request_leave_periods_ibfk_1` FOREIGN KEY (`leave_type_id`) REFERENCES `request_leave_types` (`id`),
  CONSTRAINT `request_leave_periods_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `request_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_leave_periods`
--

INSERT INTO `request_leave_periods` VALUES (1,1,1,'2012-07-10','2012-07-12'),(2,2,2,'2012-09-03','2012-10-01'),(3,3,9,'2012-10-15','2012-10-19');

--
-- Table structure for table `request_leave_types`
--

CREATE TABLE `request_leave_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(32) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_parent_id` (`parent_id`),
  CONSTRAINT `request_leave_types_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `request_leave_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_leave_types`
--

INSERT INTO `request_leave_types` VALUES (1,'Sick Leave','sick',NULL),(2,'Long Service Leave','long_service',NULL),(3,'Annual Leave','annual',NULL),(4,'Other Leave','other',NULL),(6,'Parental Leave','parental',4),(7,'Career Break','career_break',4),(8,'Personal Career Leave','personal_career',4),(9,'Special Leave','special_leave',4),(10,'Compassion Leave','compassion',4),(11,'Parental Leave without Pay','parental_without_pay',4),(12,'Leave without pay','leave_without_pay',4),(13,'Study Leave','study',4),(14,'Jury Duty','jury_duty',4),(15,'Defence','defence',4);

--
-- Table structure for table `request_rdo_replacements`
--

CREATE TABLE `request_rdo_replacements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `actual_date` date DEFAULT NULL,
  `date_taken` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`),
  CONSTRAINT `request_rdo_replacements_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `request_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_rdo_replacements`
--

INSERT INTO `request_rdo_replacements` VALUES (1,7,'2012-07-06','2012-07-27');

--
-- Table structure for table `request_rdos`
--

CREATE TABLE `request_rdos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `rdo_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`),
  CONSTRAINT `request_rdos_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `request_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_rdos`
--

INSERT INTO `request_rdos` VALUES (1,4,'2012-09-14'),(2,5,'2012-09-28');

--
-- Table structure for table `request_statuses`
--

CREATE TABLE `request_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `code` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_statuses`
--

INSERT INTO `request_statuses` VALUES (1,'Pending','pending'),(2,'Approved','approved'),(3,'Declined','declined'),(4,'Endorsed','endorsed');

--
-- Table structure for table `request_types`
--

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

--
-- Dumping data for table `request_types`
--

INSERT INTO `request_types` VALUES (1,'Leave Request','leave'),(2,'Taking an RDO','taking_rdo'),(3,'Accrued RDO','accrued_rdo');

--
-- Table structure for table `request_ulves`
--

CREATE TABLE `request_ulves` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `ulve_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`),
  CONSTRAINT `request_ulves_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `request_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `request_ulves`
--


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

CREATE TABLE `requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serial_number` tinyint(4) NOT NULL,
  `service_number` int(11) NOT NULL,
  `employee_name` varchar(32) NOT NULL,
  `email_addr` varchar(128) NOT NULL,
  `mobile_phone` varchar(16) NOT NULL,
  `section` varchar(255) NOT NULL,
  `division_id` int(11) NOT NULL,
  `branch` varchar(255) NOT NULL,
  `approver_service_number` int(11) NOT NULL,
  `request_type_id` int(11) NOT NULL,
  `last_altered_by` int(11) DEFAULT NULL,
  `last_altered_date` datetime DEFAULT NULL,
  `endorser_service_number` int(11) DEFAULT NULL,
  `primary_leave_period_id` int(11) DEFAULT NULL,
  `leave_comments` text,
  `reason_declined` text,
  `med_cert_witnessed` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_division` (`division_id`),
  KEY `idx_last_altered_by` (`last_altered_by`),
  KEY `idx_request_type_id` (`request_type_id`),
  KEY `fk_primary_leave_period` (`primary_leave_period_id`),
  CONSTRAINT `fk_primary_leave_period` FOREIGN KEY (`primary_leave_period_id`) REFERENCES `request_leave_periods` (`id`),
  CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`division_id`) REFERENCES `divisions` (`id`),
  CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`last_altered_by`) REFERENCES `users` (`service_number`),
  CONSTRAINT `requests_ibfk_3` FOREIGN KEY (`request_type_id`) REFERENCES `request_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

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

INSERT INTO `requests` VALUES (1,1,47802,'Matthew Clarke','Matthew.Clarke@ausgrid.com.au','0417667681','Business Systems Development',1,'Business Services',43880,1,NULL,NULL,NULL,1,NULL,NULL,0),(2,1,47802,'Matthew Clarke','Matthew.Clarke@ausgrid.com.au','0417667681','Business Systems Development',1,'Business Services',43880,1,NULL,NULL,NULL,3,NULL,NULL,0),(3,1,47357,'Aaron Hughes','ahughes@ausgrid.com.au','0407270082','Transmission Substations - Newcastle',2,'Transmission Substations & Services',43880,3,NULL,NULL,NULL,NULL,NULL,NULL,0),(4,1,47357,'Aaron Hughes','ahughes@ausgrid.com.au','0407270082','Transmission Substations - Newcastle',2,'Transmission Substations & Services',43880,2,NULL,NULL,NULL,NULL,NULL,NULL,0);

--
-- 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_1` FOREIGN KEY (`service_number`) REFERENCES `users` (`service_number`) ON DELETE CASCADE,
  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`
--



