SET foreign_key_checks = 0;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  /* 255 utf-8 codepoints is max for InnoDB keys */
  `USERID` VARCHAR(255) NOT NULL,
  `PASSWD` VARCHAR(256) NOT NULL,
  `PIN` VARCHAR(256) NOT NULL,
  `PASSWD_SALT` VARCHAR(256) NOT NULL,
  `PIN_SALT` VARCHAR(256) NOT NULL,
  `HWSIG` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE (`USERID`) )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `user_cert_attr_mappings`;
CREATE TABLE `user_cert_attr_mappings` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `COUNTRY` TEXT,
    `ORGANIZATION` TEXT,
    `EMAIL` TEXT,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`)
        REFERENCES `user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

SET foreign_key_checks = 1;
