

CREATE TABLE `calendar_profiles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(50) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `calendars` TEXT NOT NULL,
  `active` TINYINT NOT NULL DEFAULT 1, 
  `is_public` TINYINT NOT NULL,
  `max_usage` INT NOT NULL DEFAULT 0,
  `created_by` INT NOT NULL,
  `last_update` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_created_by_idx` (`created_by` ASC),
  CONSTRAINT `fk_created_by`
    FOREIGN KEY (`created_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `power_users` 
ADD COLUMN `label` VARCHAR(45) NOT NULL DEFAULT 'Power user 1' AFTER `id`,
ADD COLUMN `description` TEXT NULL DEFAULT NULL AFTER `label`;
ALTER TABLE `power_users` DROP INDEX `resource_2` ;
;

ALTER TABLE `announcement` 
ADD COLUMN `created_by` INT NULL DEFAULT NULL AFTER `end_date`,
ADD COLUMN `is_active` TINYINT NOT NULL DEFAULT 1 AFTER `created_by`,
ADD INDEX `announcement_ibfk_2_idx` (`created_by` ASC);
;
ALTER TABLE `announcement` 
ADD CONSTRAINT `announcement_ibfk_2`
  FOREIGN KEY (`created_by`)
  REFERENCES `users` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `calendar` ADD COLUMN `shares` VARCHAR(100) NULL DEFAULT NULL AFTER `bulk_seed`;

UPDATE calendar_comment_label SET label = 'Confirmation' WHERE id = 2;
UPDATE calendar_comment_label SET label = 'Update' WHERE id = 3;
UPDATE calendar_comment_label SET label = 'Assistance request' WHERE id = 4;
UPDATE calendar_comment_label SET label = 'Cancellation' WHERE id = 5;
UPDATE calendar_comment_label SET label = 'Assistance cancelled' WHERE id = 6;
UPDATE calendar_comment_label SET label = 'Assistance accepted' WHERE id = 7;

CREATE TABLE `power_users_assoc` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `power_users` INT NULL,
  `user` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_power_user_idx` (`power_users` ASC),
  INDEX `fk_user_idx` (`user` ASC),
  CONSTRAINT `fk_power_user` FOREIGN KEY (`power_users`) REFERENCES `power_users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `resource` ADD COLUMN `min_time_per_slot` INT NOT NULL DEFAULT 0 AFTER `max_time_per_slot`, ADD COLUMN `interval_between_slots` INT NOT NULL DEFAULT 0 AFTER `min_time_per_slot`;
ALTER TABLE `calendar` DROP COLUMN `comments`, DROP COLUMN `ics`, DROP COLUMN `allDay`;

CREATE TABLE `calendar_assistance` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

INSERT INTO calendar_assistance VALUES (NULL, 'Accepted'), (NULL, 'Requested'), (NULL, 'Rejected'), (NULL, 'Cancelled'), (NULL, 'No assistance');
UPDATE calendar_assistance SET id = 0 WHERE label = 'No assistance';

ALTER TABLE `calendar` CHANGE COLUMN `assistance` `assistance` INT(11) NOT NULL DEFAULT '0' , ADD INDEX `calendar_ibfk_6_idx` (`assistance` ASC);
ALTER TABLE `calendar` ADD CONSTRAINT `calendar_ibfk_6` FOREIGN KEY (`assistance`) REFERENCES `calendar_assistance` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO notification_target VALUES (NULL, 'calendar_warnings', 'A calendar booking triggered <b>maximum usage warnings</b>', 1);

ALTER TABLE `recurrent_tag` CHANGE COLUMN `type` `type` VARCHAR(45) NOT NULL DEFAULT 'day' COMMENT '1-daily,2-weekly,3-monthly' ;

UPDATE recurrent_tag SET type = 'day' WHERE type = '1';
UPDATE recurrent_tag SET type = 'week' WHERE type = '2';
UPDATE recurrent_tag SET type = 'month' WHERE type = '3';

INSERT INTO calendar_comment_label VALUES (NULL, 'Assistance rejected');

ALTER TABLE `power_users_assoc` DROP FOREIGN KEY `fk_power_user`;
ALTER TABLE `power_users_assoc` DROP INDEX `fk_power_user_idx` ;

ALTER TABLE `power_users` ADD COLUMN `with_assistance` TINYINT(4) NOT NULL DEFAULT 0 AFTER `discount`, CHANGE COLUMN `label` `label` VARCHAR(100) NOT NULL DEFAULT 'Independent user' , RENAME TO  `custom_permissions` ;
ALTER TABLE `power_users_assoc` CHANGE COLUMN `power_users` `permission` INT(11) NULL DEFAULT NULL , ADD INDEX `fk_perm_idx` (`permission` ASC);
ALTER TABLE  `power_users_assoc` RENAME TO  `custom_permissions_assoc` ;
ALTER TABLE custom_permissions_assoc 
ADD CONSTRAINT `fk_perm`
  FOREIGN KEY (`permission`)
  REFERENCES `custom_permissions` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `resource` ADD COLUMN `billing_rules` TEXT NULL DEFAULT NULL COMMENT 'json format' AFTER `billing_type`;
UPDATE resource SET billing_rules = JSON_OBJECT('type', 1, 'double_charging', 0, 'no_show_fee', JSON_OBJECT('type', 'relative', 'value', no_show_fee), 'no_booking_fee', JSON_OBJECT('type', 'relative', 'value', no_booking_fee));
ALTER TABLE `resource` DROP FOREIGN KEY `resource_ibfk_11`;

ALTER TABLE `resource` DROP COLUMN `no_booking_fee`;
ALTER TABLE `resource` DROP COLUMN `no_show_fee`;
ALTER TABLE `resource` DROP COLUMN `billing_type`;
ALTER TABLE `resource` DROP INDEX `billing_type` ;

UPDATE resource_billing SET name = 'Custom billing' WHERE id = 3;


ALTER TABLE `calendar_profiles` 
    DROP COLUMN `max_usage`, 
    ADD COLUMN `opt` TEXT NULL DEFAULT NULL COMMENT 'json - type: \"bulk/similar/max_usage\",value: int\n' AFTER `is_public`;

CREATE TABLE `calendar_profiles_subscription` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `profile` INT NULL,
    `user` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `profile_idx` (`profile` ASC),
    INDEX `user_idx` (`user` ASC),
    CONSTRAINT `profile_fk`
        FOREIGN KEY (`profile`)
        REFERENCES `calendar_profiles` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `user_fk`
        FOREIGN KEY (`user`)
        REFERENCES `users` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
) ENGINE=INNODB;

INSERT INTO widgets VALUES (NULL, 'my-profiles', 'My calendar profiles', NULL, NULL, '/widgets/index/my-profiles.php', '/index.php');


ALTER TABLE `custom_permissions` ADD COLUMN `is_training` TINYINT NOT NULL DEFAULT 0 AFTER `active`;
INSERT INTO `fields_pricetype` VALUES (NULL, 'Not price related');
UPDATE permissions SET name = 'Custom permission' WHERE id = 9;
UPDATE fields SET pricetype = 3 WHERE resource IN (SELECT id FROM resource WHERE field_billable = 0) ;


ALTER TABLE `fields_share` 
ADD COLUMN `shared` TINYINT NULL DEFAULT 1 AFTER `active`,
ADD COLUMN `last_time_update` DATETIME NULL AFTER `shared`;

INSERT INTO `calendar_comment_label` (`id`, `label`) VALUES ('9', 'Transfer booking accepted');
ALTER TABLE `calendar` CHANGE COLUMN `remote_server` `remote_server` TEXT NULL DEFAULT NULL ;

ALTER TABLE `resource` ADD `end_session` TINYINT(4) NOT NULL DEFAULT '0' AFTER `erp_ref`;
UPDATE resource SET end_time = "24:00:00" WHERE end_time = "00:00:00";

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


ALTER TABLE `custom_permissions` ADD COLUMN `with_notification` TINYINT(4) NOT NULL DEFAULT 0 AFTER `with_assistance`;
INSERT INTO custom_permissions SELECT NULL, 'Training with assistance', 'Permissions for users in training. Assistance will be automatically requested upon creating a new booking. Resource is available during standard business hours', id, 9, 17, 0, 0, 1, 0, 0, 1, 1, 1, 1, NOW() FROM resource;
