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





--
-- Create schema portal_comms_dev
--

CREATE DATABASE IF NOT EXISTS portal_comms_dev;
USE portal_comms_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 `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 DEFAULT CHARSET=utf8;

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



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

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

INSERT INTO `permission_roles` (`id`,`name`,`description`) VALUES 
 (1,'superuser',NULL),
 (2,'supervisor',NULL),
 (3,'corporate',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 DEFAULT CHARSET=utf8;

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



--
-- Definition of table `user_cost_centres`
--

CREATE TABLE `user_cost_centres` (
  `service_number` int(11) NOT NULL,
  `cost_centre` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`service_number`,`cost_centre`),
  CONSTRAINT `user_cost_centres_ibfk_1` FOREIGN KEY (`service_number`) REFERENCES `users` (`service_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_cost_centres`
--

INSERT INTO `user_cost_centres` (`service_number`,`cost_centre`) VALUES 
 (21900,1618),
 (24727,1916),
 (26654,1617),
 (27256,1762),
 (27732,1761),
 (28598,1608),
 (29478,1629),
 (29535,4718),
 (31563,1762),
 (31584,1912),
 (33219,5002),
 (33423,1644),
 (33514,1630),
 (33629,4720),
 (34475,1639),
 (34969,1637),
 (35880,1641),
 (36592,1632),
 (36592,1635),
 (36759,1665),
 (37805,1636),
 (37827,1633),
 (38050,1619),
 (38886,1620),
 (39713,1631),
 (40026,1640),
 (40055,1616),
 (40140,1621),
 (40154,1607),
 (41122,1622),
 (42027,1915),
 (42357,1609),
 (43319,3704),
 (43391,5105),
 (43720,1649),
 (43880,2101),
 (44067,1761),
 (44067,1762),
 (44804,1660),
 (45151,1732),
 (45164,1734),
 (45358,5103),
 (45358,5108),
 (45545,3714),
 (45580,1647),
 (45907,2011),
 (45931,1648),
 (46123,5110),
 (46229,1668),
 (46351,1610),
 (46524,2011),
 (47070,2006),
 (47311,1669),
 (47571,1667),
 (47573,5105),
 (47575,1666),
 (48253,1651),
 (49448,5109),
 (49953,3701),
 (49953,3717),
 (54170,1623);


--
-- 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,1),
 (47187,1),
 (51264,1),
 (57342,1),
 (21900,2),
 (24727,2),
 (26654,2),
 (27256,2),
 (27732,2),
 (28598,2),
 (29478,2),
 (29535,2),
 (31563,2),
 (31584,2),
 (33219,2),
 (33423,2),
 (33514,2),
 (33629,2),
 (34475,2),
 (34969,2),
 (35880,2),
 (36592,2),
 (36759,2),
 (37805,2),
 (37827,2),
 (38050,2),
 (38886,2),
 (39713,2),
 (40026,2),
 (40055,2),
 (40140,2),
 (40154,2),
 (41122,2),
 (42027,2),
 (42357,2),
 (43319,2),
 (43391,2),
 (43720,2),
 (43880,2),
 (44067,2),
 (44804,2),
 (45151,2),
 (45164,2),
 (45358,2),
 (45388,2),
 (45545,2),
 (45580,2),
 (45907,2),
 (45931,2),
 (46123,2),
 (46229,2),
 (46351,2),
 (46524,2),
 (47070,2),
 (47311,2),
 (47571,2),
 (47573,2),
 (47575,2),
 (48253,2),
 (49448,2),
 (49953,2),
 (54170,2),
 (44300,3);




