DROP table if exists ohrm_employee_device_token; -- TODO remove this line
CREATE TABLE `ohrm_employee_device_token`
(
    `id`           BIGINT AUTO_INCREMENT,
    `device_token` VARCHAR(512) NOT NULL,
    `emp_number`   INT(7)        NOT NULL,
    `user_id` INT NOT NULL,
    `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `modified_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_ohrm_employee_device_token`(`device_token`),
    CONSTRAINT `fk_employee_device_token_emp_number` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_employee_device_token_user_id` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP table if exists ohrm_push_notification_queue; -- TODO remove this line
CREATE TABLE `ohrm_push_notification_queue`
(
    `id`           BIGINT AUTO_INCREMENT,
    `device_token` VARCHAR(512) NOT NULL,
    `subject` VARCHAR(100) NOT NULL, -- 100 Char is the maximum
    `body` VARCHAR(1024) NOT NULL, -- 1024 char is the maximum
    `link` VARCHAR(1024) ,  -- 1024 char is the maximum
    `status` VARCHAR(20) NOT NULL,  -- 1024 char is the maximum
    `type` varchar(100) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    `served_by_process_id` INT(5),
    `attempt` TINYINT(1),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_device_token_employee_device_token` FOREIGN KEY (`device_token`) REFERENCES `ohrm_employee_device_token` (`device_token`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- TODO get approval
ALTER table ohrm_notification_subscriber_subscription_template
    add column `link` VARCHAR(1050) default null AFTER `body`;
