/**
 * This file sets up the schema for news being stored in the database
*/

CREATE TABLE statuses (
	id TINYINT AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(32) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

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

CREATE TABLE news (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	release_date DATE,
	exp_date DATE,
	heading VARCHAR(255),
	content VARCHAR(255),
	footer VARCHAR(255),
	creator INT NOT NULL,
	status_id TINYINT DEFAULT 1,
	is_deleted TINYINT(1) DEFAULT 0,
	FOREIGN KEY idx_comm_type (comm_type_id) REFERENCES comm_types (id)
	FOREIGN KEY idx_status (status_id) REFERENCES statuses (id)
) ENGINE=InnoDB;

CREATE TABLE news_cost_centres (
	news_id INT NOT NULL,
	cost_centre INT NOT NULL,
	PRIMARY KEY (news_id, cost_centre),
	FOREIGN KEY idx_news (news_id) REFERENCES news (id)
) ENGINE=InnoDB;

CREATE TABLE document_categories (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(16) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL
);

INSERT INTO document_categories (id, code, name) VALUES
	(1, 'safety', 'Safety'),
	(2, 'communications', 'Communications');

CREATE TABLE document_types (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	doc_category_id TINYINT NOT NULL,
	name VARCHAR(255) NOT NULL,
	FOREIGN KEY idx_doc_category (doc_category_id) REFERENCES document_categories (id)
) ENGINE=InnoDB;

INSERT INTO document_types (id, doc_category_id, name) 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');

CREATE TABLE news_files (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	news_id INT NOT NULL,
	doc_type_id TINYINT DEFAULT NULL,
	file_name VARCHAR(255) DEFAULT NULL,
	file_path VARCHAR(255) NOT NULL,
	FOREIGN KEY idx_news (news_id) REFERENCES news (id)
	FOREIGN KEY idx_doc_type (doc_type_id) REFERENCES document_types (id)
) ENGINE=InnoDB;
