CREATE TABLE `auth` (
  `token` binary(40) NOT NULL,
  `password` binary(60) NOT NULL,
  `when_create` datetime NOT NULL,
  `when_update` datetime DEFAULT NULL,
  PRIMARY KEY (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `auth_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `token` binary(40) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime DEFAULT NULL,
  `when_create` datetime NOT NULL,
  `when_update` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `token` (`token`),
  CONSTRAINT `auth_session_fk_token` FOREIGN KEY (`token`) REFERENCES `auth` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `auth_profile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `token` binary(40) NOT NULL,
  `username` varchar(36) NOT NULL,
  `email` longtext NOT NULL,
  `phone` bigint(20) DEFAULT NULL,
  `when_create` datetime NOT NULL,
  `when_update` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `phone` (`phone`),
  UNIQUE KEY `email` (`email`),
  KEY `token` (`token`),
  CONSTRAINT `auth_profile_fk_token` FOREIGN KEY (`token`) REFERENCES `auth` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `auth_profile_verification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auth_profile` int(11) NOT NULL,
  `type` longtext DEFAULT 'email',
  `verified` int(1) DEFAULT 0,
  `when_create` datetime NOT NULL,
  `when_update` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auth_profile` (`auth_profile`),
  CONSTRAINT `auth_profile_verification_fk_auth_profile` FOREIGN KEY (`auth_profile`) REFERENCES `auth_profile` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `auth_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NOT NULL,
  `when_create` datetime NOT NULL,
  `when_update` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `auth_profile_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auth_profile` int(11) NOT NULL,
  `auth_roles` int(11) NOT NULL,
  `when_create` datetime NOT NULL,
  `when_update` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auth_profile` (`auth_profile`),
  KEY `auth_roles` (`auth_roles`),
  CONSTRAINT `auth_profile_roles_fk_auth_profile` FOREIGN KEY (`auth_profile`) REFERENCES `auth_profile` (`id`),
  CONSTRAINT `auth_profile_roles_fk_auth_roles` FOREIGN KEY (`auth_roles`) REFERENCES `auth_roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;