init.sql

SET @MYSQLPUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN = 0;

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';

CREATE DATABASE IF NOT EXISTS `USERS_SERVICE`;

CREATE TABLE `USERS_SERVICE`.`USERS`(
        `ID` INT NOT NULL AUTO_INCREMENT,
        `EMAIL` varchar(50) UNIQUE NOT NULL,
        `PASSWORD` BINARY(60) NOT NULL,
        `FIRST_NAME` varchar(50) NOT NULL,
        `LAST_NAME` varchar(50) NOT NULL,
        `BLOCKED` boolean NOT NULL DEFAULT FALSE,
        `VERIFIED_EMAIL` boolean NOT NULL DEFAULT FALSE,
        `IS_STUDENT` boolean NOT NULL,
        `IS_TEACHER` boolean NOT NULL,
        `PROFILE_PICTURE_URL` varchar(255) DEFAULT NULL,
        `WITH_GOOGLE` boolean NOT NULL DEFAULT FALSE,
        PRIMARY KEY(`ID`),
        INDEX (`EMAIL`)
);

CREATE TABLE `USERS_SERVICE`.`ADMINS`(
        `ID` int NOT NULL AUTO_INCREMENT,
        `EMAIL` varchar(50) UNIQUE NOT NULL,
        `PASSWORD` BINARY(60) NOT NULL,
        `FIRST_NAME` varchar(50) NOT NULL,
        `LAST_NAME` varchar(50) NOT NULL,
        PRIMARY KEY(`ID`),
        INDEX (`EMAIL`)
);

CREATE TABLE `USERS_SERVICE`.`USERS_LOGIN_ATTEMPTS` (
        `ID` INT PRIMARY KEY AUTO_INCREMENT,
        `USER_ID` INT NOT NULL,
        `TIMESTAMP` TIMESTAMP NOT NULL,
        `SUCCESS` BOOLEAN DEFAULT TRUE,
        INDEX (`USER_ID`, `TIMESTAMP`),
        FOREIGN KEY (`USER_ID`) REFERENCES USERS(`ID`)
);

CREATE TABLE `USERS_SERVICE`.`USER_VERIFICATION_DATA` (
        `ID` INT PRIMARY KEY AUTO_INCREMENT,
        `USER_ID` INT NOT NULL UNIQUE,
        `CODE` VARCHAR(64),
        `TYPE` INT NOT NULL,
        `EXPIRATION` TIMESTAMP NOT NULL,
        INDEX (`USER_ID`),
        FOREIGN KEY (`USER_ID`) REFERENCES USERS(`ID`) ON DELETE CASCADE
);

CREATE TABLE `USERS_SERVICE`.`APPROVED_COURSES` (
    `ID` INT AUTO_INCREMENT,
    `USER_ID` INT NOT NULL,
    `COURSE_ID` CHAR(24) NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX (`USER_ID`),
    FOREIGN KEY (`USER_ID`) REFERENCES `USERS`(`ID`)
);

CREATE TABLE `USERS_SERVICE`.`FCM_TOKENS` (
    USER_ID   INT NOT NULL,
    TOKEN     VARCHAR(512) NOT NULL,
    PRIMARY KEY (USER_ID, TOKEN),
    FOREIGN KEY (USER_ID) REFERENCES USERS(ID) ON DELETE CASCADE
);

CREATE TABLE `USERS_SERVICE`.`USER_NOTIFICATION_PREFERENCES` (
   USER_ID       INT           NOT NULL,
   EVENT_TYPE    VARCHAR(50)   NOT NULL,
   PUSH_ENABLED  BOOLEAN       NOT NULL,
   EMAIL_ENABLED BOOLEAN       NOT NULL,
   PRIMARY KEY (USER_ID, EVENT_TYPE),
   FOREIGN KEY (USER_ID) REFERENCES USERS(ID) ON DELETE CASCADE
);

CREATE TABLE `USERS_SERVICE`.`NOTIFICATION_LOGS` (
     `ID`             INT                  NOT NULL AUTO_INCREMENT,
     `USER_ID`        INT                  NOT NULL,
     `EVENT_TYPE`     VARCHAR(50)          NOT NULL,
     `CHANNEL`        ENUM('push','email') NOT NULL,
     `ERROR_MESSAGE`  TEXT                 NULL,
     `SENT_AT`        TIMESTAMP            NOT NULL,
     PRIMARY KEY (`ID`),
     INDEX (`USER_ID`),
     FOREIGN KEY (`USER_ID`) REFERENCES `USERS`(`ID`) ON DELETE CASCADE
);

CREATE TABLE `USERS_SERVICE`.`AUDIT_LOGS` (
    `ID`         INT                  NOT NULL AUTO_INCREMENT,
    `USER_ID`     INT                 NOT NULL,
    `ROLE_CHANGE` BOOLEAN             NOT NULL DEFAULT  FALSE,
    `BLOCK_USER`    BOOLEAN           NOT NULL DEFAULT  FALSE,
    `UNBLOCK_USER` BOOLEAN            NOT NULL DEFAULT  FALSE,
    `DATE`        TIMESTAMP           NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX (`USER_ID`),
    FOREIGN KEY (`USER_ID`) REFERENCES `USERS`(`ID`) ON DELETE CASCADE
);

-- Create backoffice admin user
INSERT INTO `USERS_SERVICE`.`ADMINS` (`EMAIL`,`PASSWORD`,`FIRST_NAME`,`LAST_NAME`)
VALUES ('jumdiaz@fi.uba.ar', '$2y$12$UwHkBgRYcAcXM4aL5lT25.DuZfMEDLQfkIkFytZZ/aOJNmhtQm/vy', 'Admin', 'Root');