

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 `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, NOW() FROM resource;

ALTER TABLE `resource` ADD COLUMN `billing_rules` TEXT NULL DEFAULT NULL COMMENT 'json format' AFTER `billing_type`;
UPDATE resource_billing SET name = 'Custom billing' WHERE id = 3;
