-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------





--
-- Create schema room_booking_dev
--
-- Definition of 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`
--



--
-- Definition of table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `room_id` int(11) NOT NULL,
  `event_date` date NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_room` (`room_id`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`,`title`,`room_id`,`event_date`,`start_time`,`end_time`) VALUES 
 (1,'Event A',1,'2012-12-04','07:30:00','16:00:00'),
 (2,'Event B',2,'2012-12-04','07:30:00','16:00:00'),
 (3,'Event C',3,'2012-12-04','07:30:00','16:00:00'),
 (4,'2B LVFR training',4,'2012-12-04','07:30:00','16:00:00'),
 (5,'Event E',5,'2012-12-04','07:30:00','16:00:00'),
 (6,'Event F',6,'2012-12-04','07:30:00','16:00:00'),
 (7,'Event G',7,'2012-12-04','07:30:00','16:00:00'),
 (8,'Event H',8,'2012-12-04','07:30:00','16:00:00'),
 (9,'Event I',9,'2012-12-04','07:30:00','16:00:00'),
 (10,'Event J',10,'2012-12-04','07:30:00','16:00:00'),
 (11,'Event K',11,'2012-12-04','07:30:00','16:00:00'),
 (12,'Event L',12,'2012-12-04','07:30:00','16:00:00'),
 (13,'Event M',13,'2012-12-04','07:30:00','16:00:00'),
 (14,'Event N',14,'2012-12-04','07:30:00','16:00:00'),
 (15,'Event O',15,'2012-12-04','07:30:00','16:00:00'),
 (16,'Event P',16,'2012-12-04','07:30:00','16:00:00'),
 (17,'Event Q',17,'2012-12-04','07:30:00','16:00:00'),
 (18,'Event R',18,'2012-12-04','07:30:00','16:00:00'),
 (19,'Event S',19,'2012-12-04','07:30:00','16:00:00'),
 (20,'Event T',20,'2012-12-04','07:30:00','16:00:00'),
 (21,'ESR initial',21,'2012-12-04','07:30:00','16:00:00'),
 (22,'Event V',22,'2012-12-04','07:30:00','16:00:00'),
 (23,'Event W',23,'2012-12-04','07:30:00','16:00:00'),
 (24,'Event X',24,'2012-12-04','07:30:00','16:00:00'),
 (25,'Event Y',25,'2012-12-04','07:30:00','16:00:00'),
 (26,'Event Z',26,'2012-12-04','07:30:00','16:00:00'),
 (27,'Event AB',27,'2012-12-04','07:30:00','16:00:00'),
 (28,'Event AC',28,'2012-12-04','07:30:00','16:00:00'),
 (29,'Event AD',29,'2012-12-04','07:30:00','16:00:00'),
 (30,'Event AE',30,'2012-12-04','07:30:00','16:00:00'),
 (31,'Event AF',31,'2012-12-04','07:30:00','16:00:00'),
 (32,'Event AG',32,'2012-12-04','07:30:00','16:00:00'),
 (33,'Event AH',33,'2012-12-04','07:30:00','16:00:00'),
 (34,'Event AI',34,'2012-12-04','07:30:00','16:00:00'),
 (35,'Event AJ',35,'2012-12-04','07:30:00','16:00:00'),
 (36,'Event AK',36,'2012-12-04','07:30:00','16:00:00'),
 (37,'Event AL',37,'2012-12-04','07:30:00','16:00:00'),
 (38,'Event AM',38,'2012-12-04','07:30:00','16:00:00');


--
-- Definition of table `groups`
--

CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `location_id` int(11) NOT NULL,
  `active` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `idx_location` (`location_id`),
  CONSTRAINT `groups_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `groups`
--

INSERT INTO `groups` (`id`,`name`,`location_id`,`active`) VALUES 
 (1,'Ground Level',1,1),
 (2,'Training Lvl 1',1,1),
 (3,'Board/Meeting Lvl 1',1,1),
 (4,'Computer Training Lvl 1',1,1),
 (5,'Near E. Mech Lvl 1',1,1),
 (6,'Trainers Traiing Lvl 1',1,1),
 (7,'Level 2',1,1),
 (8,'Somersby Training',2,1);


--
-- Definition of table `locations`
--

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

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`id`,`code`,`name`,`active`) VALUES 
 (1,'silverwater','Silverwater',1),
 (2,'somersby','Somersby',1);


--
-- Definition of 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`
--



--
-- Definition of 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` (`id`,`name`,`description`) VALUES 
 (1,'groups',NULL),
 (2,'rooms',NULL),
 (3,'events',NULL),
 (4,'locations',NULL);


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

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

INSERT INTO `permission_roles` (`id`,`name`,`description`) VALUES 
 (1,'user',NULL),
 (2,'superuser',NULL);


--
-- Definition of 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=5 DEFAULT CHARSET=utf8;

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

INSERT INTO `permission_rules` (`id`,`name`,`role_id`,`resource_id`,`allowed`) VALUES 
 (1,'edit_managed',1,1,0x01),
 (2,'edit_managed',1,2,0x01),
 (3,'edit_managed',1,3,0x01),
 (4,'edit_all',2,4,0x01);


--
-- Definition of table `rooms`
--

CREATE TABLE `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `group_id` int(11) NOT NULL,
  `active` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `idx_group` (`group_id`),
  CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `rooms`
--

INSERT INTO `rooms` (`id`,`name`,`group_id`,`active`) VALUES 
 (1,'Chatswood',1,1),
 (2,'Ourimbah',1,1),
 (3,'Muswellbrook',1,1),
 (4,'Mackeller',2,1),
 (5,'Sydney',2,1),
 (6,'St George',2,1),
 (7,'Brisbane Waters',2,1),
 (8,'Nesca',2,1),
 (9,'Shortland',2,1),
 (10,'Orion',2,1),
 (11,'Hornsby',2,1),
 (12,'Balmain',2,1),
 (13,'Gosford',2,1),
 (14,'Sutherland',2,1),
 (15,'Bankstown',2,1),
 (16,'Wallsend',3,1),
 (17,'Oatley',3,1),
 (18,'Java',4,1),
 (19,'Syntax',4,1),
 (20,'Binary',4,1),
 (21,'Pixel',4,1),
 (22,'EnergyAustralia',5,1),
 (23,'Leichhardt',5,1),
 (24,'Strathfield',5,1),
 (25,'Maison Dieu',6,1),
 (26,'Jesmond',6,1),
 (27,'Bunnerong',6,1),
 (28,'Argenton',6,1),
 (29,'Kurnell',6,1),
 (30,'Dally Street',6,1),
 (31,'Warratah',6,1),
 (32,'Homebush meeting',7,1),
 (33,'Auditorium',7,1),
 (34,'Noraville Training',8,1),
 (35,'Ourimbah Conference',8,1),
 (36,'Somersby Training',8,1),
 (37,'Umina Training',8,1),
 (38,'West Gosford',8,1);


--
-- Definition of table `user_locations`
--

CREATE TABLE `user_locations` (
  `service_number` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  PRIMARY KEY (`service_number`,`location_id`),
  KEY `idx_location_id` (`location_id`),
  CONSTRAINT `user_locations_ibfk_1` FOREIGN KEY (`service_number`) REFERENCES `users` (`service_number`),
  CONSTRAINT `user_locations_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_locations`
--

INSERT INTO `user_locations` (`service_number`,`location_id`) VALUES 
 (34992,1),
 (43880,1),
 (47187,1),
 (52836,1);


--
-- Definition of 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`
--

INSERT INTO `user_roles` (`service_number`,`role_id`) VALUES 
 (39887,2);




