--liquibase formatted sql

--changeset alex:user
--comment: core table
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` varchar(30) DEFAULT NULL,
  `version` int NOT NULL DEFAULT '0',
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedBy` varchar(30) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `username` varchar(30) NOT NULL,
  `password` varchar(60) DEFAULT NULL,
  `locked` tinyint(1) NOT NULL DEFAULT '0',
  `expiryDate` date DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `locale` varchar(5) DEFAULT NULL,
  `fullname` varchar(90) DEFAULT NULL,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(30) DEFAULT NULL,
  `title` varchar(60) DEFAULT NULL,
  `department` varchar(60) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone1` varchar(30) DEFAULT NULL,
  `phone2` varchar(30) DEFAULT NULL,
  `remarks` varchar(600) DEFAULT NULL,
  `lotusNotesUser` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `user`(`name`,`username`, `password`)VALUES ('2fi','2fi','$2a$10$65S7/AhKn8MldlYmvFN5JOfr1yaULwFNDIhTskLTuUCKgbbs8sFAi');
INSERT INTO `user`(`name`,`username`, `password`,`lotusNotesUser`)VALUES ('user1','user1',null,1);

CREATE TABLE `authority` (
  `id` int NOT NULL AUTO_INCREMENT,
  `authority` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `module` varchar(50) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `authority` (`authority`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `authority` VALUES (1,'MAINTAIN_USER','Maintain User',NULL,NULL),(2,'MAINTAIN_GROUP','Maintain group',NULL,NULL),(3,'VIEW_USER','view user',NULL,NULL),(4,'VIEW_GROUP','view group',NULL,NULL);
CREATE TABLE `user_authority` (
  `userId` int NOT NULL,
  `authId` int NOT NULL,
  PRIMARY KEY (`userId`,`authId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user_authority` VALUES (1,3);

--changeset alex:group
--comment: group table
CREATE TABLE `group` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdBy` varchar(30) DEFAULT NULL,
  `version` int NOT NULL DEFAULT '0',
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedBy` varchar(30) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `user_group` (
  `groupId` int NOT NULL,
  `userId` int NOT NULL,
  PRIMARY KEY (`groupId`,`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `group_authority` (
  `groupId` int NOT NULL,
  `authId` int NOT NULL,
  PRIMARY KEY (`groupId`,`authId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
