--
-- Host: localhost    Database: comms_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 `document_categories`
--

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

--
-- Dumping data for table `document_categories`
--

INSERT INTO `document_categories` VALUES (1,'safety','Safety'),(2,'communications','Communications');

--
-- Table structure for table `document_types`
--

CREATE TABLE `document_types` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `doc_category_id` tinyint(4) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_doc_category` (`doc_category_id`),
  CONSTRAINT `document_types_ibfk_1` FOREIGN KEY (`doc_category_id`) REFERENCES `document_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `document_types`
--

INSERT INTO `document_types` VALUES (1,1,'Safety Advice'),(2,1,'Safety Alert - Ausgrid'),(3,1,'Safety Alert - Third Party'),(4,1,'Safety Scoop'),(5,1,'Safety Topics'),(6,2,'Weekly Broadcasts'),(7,2,'COO\'s Message'),(8,2,'Network Electricty Reform Program'),(9,2,'Network Operations - Message from the GM'),(10,2,'Network Operations - Team Briefs'),(11,2,'Network Operations - Other divisional comms'),(12,2,'Inside Ausgrid'),(13,2,'Other Communications'),(14,2,'Logistic News');

--
-- 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 `news`
--

CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `release_date` date DEFAULT NULL,
  `exp_date` date DEFAULT NULL,
  `heading` varchar(255) DEFAULT NULL,
  `content` varchar(255) DEFAULT NULL,
  `footer` varchar(255) DEFAULT NULL,
  `creator` int(11) NOT NULL,
  `status_id` tinyint(4) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `news_category_id` tinyint(4) NOT NULL,
  `news_type_id` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status_id`),
  CONSTRAINT `news_ibfk_2` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `news`
--

INSERT INTO `news` VALUES (7,'2013-01-04','2013-01-31','title','content','',47187,1,0,0,NULL),(9,'2013-01-04','2013-01-31','title','content','',47187,1,0,0,NULL),(10,'2013-01-04','2013-01-31','title','content',NULL,21900,1,0,0,NULL),(11,'2013-01-04','2013-01-31','title','some content here.  will be cost centres 1618 and 1619.  two files.',NULL,21900,1,0,0,NULL),(12,'2013-01-05','2013-01-31','t','c','fc',44300,1,0,0,NULL),(13,'2013-01-05','2013-01-31','t','c','fc',44300,1,0,0,NULL),(14,'2013-01-06','2013-01-31','t','c','fc',44300,1,0,0,NULL),(15,'1969-12-31','1969-12-31',NULL,NULL,NULL,44300,1,0,0,NULL),(16,'1969-12-31','1969-12-31','','','',44300,1,0,0,NULL),(17,'1969-12-31','1969-12-31','','','',44300,1,0,0,NULL),(18,'2013-01-07','2013-01-31','title','content','',44300,1,0,0,NULL),(19,'1969-12-31','1969-12-31','','','',44300,1,0,0,NULL),(20,NULL,NULL,NULL,NULL,NULL,44300,1,0,0,NULL),(21,'2013-01-16','2013-01-24','sdfgsdfg','sdfgsdfgsdfg','ghdfghdfh',44300,2,0,1,1),(22,'2013-01-16','2013-01-31','title','content',NULL,43880,1,0,2,NULL),(23,'2013-01-16','2013-01-31','title','test',NULL,43880,1,0,2,NULL),(24,'2013-01-16','2013-01-31','title','content',NULL,43880,1,0,2,NULL),(25,'2013-01-16','2013-01-31','title','content',NULL,47187,1,0,2,NULL),(26,'2013-01-16','2013-01-31','title','content',NULL,43880,1,0,2,NULL);

--
-- Table structure for table `news_categories`
--

CREATE TABLE `news_categories` (
  `id` tinyint(4) NOT NULL,
  `code` varchar(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `news_categories`
--

INSERT INTO `news_categories` VALUES (1,'corporate','Corporate'),(2,'local','Local');

--
-- Table structure for table `news_cost_centres`
--

CREATE TABLE `news_cost_centres` (
  `news_id` int(11) NOT NULL,
  `cost_centre` int(11) NOT NULL,
  PRIMARY KEY (`news_id`,`cost_centre`),
  CONSTRAINT `news_cost_centres_ibfk_1` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `news_cost_centres`
--

INSERT INTO `news_cost_centres` VALUES (10,1618),(10,1619),(11,1618),(11,1619),(22,2101),(23,2101),(24,2101),(25,2101),(26,2101);

--
-- Table structure for table `news_files`
--

CREATE TABLE `news_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `news_id` int(11) NOT NULL,
  `doc_type_id` tinyint(4) DEFAULT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_path` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_news` (`news_id`),
  KEY `idx_doc_type` (`doc_type_id`),
  CONSTRAINT `news_files_ibfk_1` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`),
  CONSTRAINT `news_files_ibfk_2` FOREIGN KEY (`doc_type_id`) REFERENCES `document_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `news_files`
--

INSERT INTO `news_files` VALUES (1,7,NULL,'TV Channels','comm/mdm/TV Channels'),(2,7,NULL,'TV Channels','comm/mdm/TV Channels'),(3,9,NULL,'TV Channels','mdm/TV Channels_2.pdf'),(4,9,NULL,'TV Channels','mdm/TV Channels_3.pdf'),(5,11,NULL,'TV Channels','lcomm/TV Channels.pdf'),(6,11,NULL,'TV Channels','lcomm/TV Channels_1.pdf'),(7,12,NULL,'TV Channels.pdf','/TV Channels.pdf'),(8,14,3,'TV Channels.pdf','/TV Channels_1.pdf'),(9,19,3,'TV Channels.pdf','/TV Channels_2.pdf'),(10,20,4,'TV Channels.pdf','/TV Channels_3.pdf'),(11,22,NULL,'TV Channels.pdf','/TV Channels_4.pdf'),(12,22,NULL,'TV Channels.pdf','/TV Channels_5.pdf'),(13,23,NULL,'TV Channels.pdf','/TV Channels_6.pdf'),(14,23,NULL,'TV Channels.pdf','/TV Channels_7.pdf'),(15,24,NULL,'TV Channels.pdf','/TV Channels_8.pdf'),(16,24,NULL,'TV Channels.pdf','/TV Channels_9.pdf');

--
-- Table structure for table `news_types`
--

CREATE TABLE `news_types` (
  `id` tinyint(4) NOT NULL,
  `code` varchar(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `news_types`
--

INSERT INTO `news_types` VALUES (3,'both','Both'),(2,'document_only','Docuemnt Only'),(1,'news_only','News Only');

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

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


--
-- 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,'supervisor',NULL),(3,'corporate',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 DEFAULT CHARSET=utf8;

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


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

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

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

INSERT INTO `statuses` VALUES (1,'pending','Pending'),(2,'published','Published'),(3,'rejected','Rejected');

--
-- Table structure for 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` VALUES (21900,1618),(21900,1619),(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),(44804,1660),(45151,1732),(45164,1734),(45358,5103),(45358,5108),(45545,3704),(45580,1647),(45907,2011),(45931,1648),(46123,5110),(46229,1668),(46351,1610),(46524,2011),(47070,2006),(47311,1669),(47571,1667),(47573,1666),(48253,1651),(49448,5109),(49953,3704),(54170,1623);

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

INSERT INTO `user_roles` VALUES (47187,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),(44804,2),(45151,2),(45164,2),(45358,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),(48253,2),(49448,2),(49953,2),(54170,2),(44300,3);


