RENAME TABLE hazardous_risks TO hazardous_chem_risks;
RENAME TABLE hazardous_products TO hazardous_chem;
ALTER TABLE hazardous_chem ADD cas_number varchar(100) DEFAULT NULL NULL AFTER link;
ALTER TABLE hazardous_chem CHANGE COLUMN `liquid_waste` `waste_management` TEXT NULL DEFAULT NULL;
RENAME TABLE hazardous_aux TO hazardous_chem_aux;

CREATE TABLE hazardous_exposure (
	id INT auto_increment NOT NULL,
	`user` INT NOT NULL,
	agentid INT NOT NULL,
	origin varchar(100) NOT NULL,
	`start` DATE NOT NULL,
	`end` DATE DEFAULT NULL NULL,
	comment TEXT NULL,
	updated_by INT NOT NULL,
	last_update DATETIME NOT NULL,
	CONSTRAINT hazardous_exposure_pk PRIMARY KEY (id),
	CONSTRAINT hazardous_exposure_FK FOREIGN KEY (`user`) REFERENCES users(id),
	CONSTRAINT hazardous_exposure_FK_1 FOREIGN KEY (updated_by) REFERENCES users(id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

CREATE TABLE hazardous_bio_class (
	id INT auto_increment NOT NULL,
	label varchar(100) NOT NULL,
	`description` TEXT NOT NULL,
	CONSTRAINT hazardous_bio_class_pk PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

CREATE TABLE hazardous_bio_bsl (
	id INT auto_increment NOT NULL,
	label varchar(100) NOT NULL,
	`description` TEXT NOT NULL,
	CONSTRAINT hazardous_bio_bsl_pk PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

CREATE TABLE hazardous_bio (
	id INT auto_increment NOT NULL,
	agent varchar(500) NOT NULL,
	classification INT NOT NULL,
	bsl INT NOT NULL,
	genetically_modified TINYINT DEFAULT 0 NOT NULL,
	active TINYINT DEFAULT 1 NOT NULL,
	link TEXT NULL,
	updated_by INT NOT NULL,
	last_update DATETIME NOT NULL,
	CONSTRAINT hazardous_bio_pk PRIMARY KEY (id),
	CONSTRAINT hazardous_bio_FK FOREIGN KEY (updated_by) REFERENCES users(id),
	CONSTRAINT hazardous_bio_FK_1 FOREIGN KEY (bsl) REFERENCES hazardous_bio_bsl(id),
	CONSTRAINT hazardous_bio_FK_2 FOREIGN KEY (classification) REFERENCES hazardous_bio_class(id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

INSERT INTO hazardous_bio_class VALUES (1, 'Risk Group 1', '(No or low individual and community risk) - A microorganism that is unlikely to cause human disease or animal disease.');
INSERT INTO hazardous_bio_class VALUES (2, 'Risk Group 2', '(Moderate individual risk, low community risk) - A pathogen that can cause human or animal disease but is unlikely to be a serious hazard to laboratory workers, the community, livestock or the environment. Laboratory exposures may cause serious infection, but effective treatment and preventative measures are available and the risk of spread of infection is limited.');
INSERT INTO hazardous_bio_class VALUES (3, 'Risk Group 3', '(High individual risk, low community risk) - A pathogen that usually causes serious human or animal disease but does not ordinarily spread from one infected individual to another. Effective treatment and preventive measures are available.');
INSERT INTO hazardous_bio_class VALUES (4, 'Risk Group 4', '(High individual and community risk) - A pathogen that usually causes serious human or animal disease and that can be readily transmitted from one individual to another, directly or indirectly. Effective treatment and preventive measures are not usually available.');

INSERT INTO hazardous_bio_bsl VALUES (1, 'BSL-1', 'Biosafety Level 1');
INSERT INTO hazardous_bio_bsl VALUES (2, 'BSL-2', 'Biosafety Level 2');
INSERT INTO hazardous_bio_bsl VALUES (3, 'BSL-3', 'Biosafety Level 3');
INSERT INTO hazardous_bio_bsl VALUES (4, 'BSL-4', 'Biosafety Level 4');

ALTER TABLE `hazardous_bio` ADD COLUMN `keywords` TEXT NULL AFTER `genetically_modified`;
ALTER TABLE `hazardous_chem` ADD COLUMN `keywords` TEXT NULL AFTER `cas_number`;
ALTER TABLE `hazardous_chem` CHANGE COLUMN `chemical_agent` `agent` TEXT NOT NULL ;
ALTER TABLE `hazardous_exposure` ADD COLUMN `active` TINYINT NOT NULL DEFAULT 1 AFTER `comment`;
ALTER TABLE `stock` ADD COLUMN `hazardous` TEXT NULL DEFAULT NULL AFTER `low_quantity`;


