CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `primary_identity` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `identity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `type` varchar(50) NOT NULL,
  `typed_id` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `surname` varchar(255) DEFAULT NULL,
  `institution` varchar(255) DEFAULT NULL,
  `eppn` varchar(255) DEFAULT NULL,
  `tou_accepted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `user` ADD FOREIGN KEY (`primary_identity`) REFERENCES `identity`(`id`);
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE `login_token` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `token` varchar(255) NOT NULL,
  `user_id` text,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `join_request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `token` varchar(255) NOT NULL,
  `applicant_user_id` bigint(20) NOT NULL,
  `target_user_id` bigint(20) NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`applicant_user_id`) REFERENCES `user`(`id`),
  FOREIGN KEY (`target_user_id`) REFERENCES `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE EVENT login_tokens_cleanup
    ON SCHEDULE
      EVERY 1 MINUTE
    COMMENT 'Remove expired login tokens'
    DO
      DELETE FROM login_token WHERE CURRENT_TIMESTAMP > TIMESTAMPADD(MINUTE,1,creation_time);