-- set timezone to UTC for all things --

CREATE DATABASE `users`
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

-- @TODO set this somewhere else
-- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '$uP3rm4n!';

GRANT ALL PRIVILEGES ON users.* TO 'root'@'localhost';

USE users;

CREATE TABLE `logs` (
    `id`       MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `logId`    VARCHAR(36)           NOT NULL,
    `userId`   VARCHAR(36)           NOT NULL,
    `teamId`   VARCHAR(36)           NOT NULL,
    `event`    VARCHAR(255)          NOT NULL,
    `object`   JSON                  NOT NULL,
    `metadata` JSON                  NOT NULL,
    `created`  DATETIME                       DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY `id` (`id`),
    KEY `userId` (`userId`),
    KEY `teamId` (`teamId`),
    KEY `logId` (`logId`),
    KEY `event` (`event`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `teams` (
    `id`       MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `teamId`   VARCHAR(36)           NOT NULL,
    `userId`   VARCHAR(36)           NOT NULL,
    `metadata` JSON                  NOT NULL,
    `created`  DATETIME                       DEFAULT CURRENT_TIMESTAMP,
    `updated`  DATETIME                       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY `id` (`id`),
    KEY `userId` (`userId`),
    KEY `teamId` (`teamId`)
)

    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `teamMembers` (
    `id`           MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `userId`       VARCHAR(36)           NOT NULL,
    `teamId`       VARCHAR(255)          NOT NULL,
    `confirmed`    ENUM ('0', '1')                DEFAULT '0',
    `permissions`  JSON                  NOT NULL,
    `metadata`     JSON                  NOT NULL,
    `created`      DATETIME                       DEFAULT CURRENT_TIMESTAMP,
    `updated`      DATETIME                       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY `id` (`id`),
    KEY `userId` (`userId`),
    KEY `teamId` (`teamId`)
)

    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `users` (
    `id`          MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `userId`      VARCHAR(36)           NOT NULL,
    `email`       TEXT                  NOT NULL,
    `pass`        VARCHAR(72)           NOT NULL,
    `confirmed`   ENUM ('0', '1')                DEFAULT '0',
    `isBanned`    ENUM ('0', '1')                DEFAULT '0',
    `permissions` JSON                  NOT NULL,
    `created`     DATETIME                       DEFAULT CURRENT_TIMESTAMP,
    `updated`     DATETIME                       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY `id` (`id`),
    KEY `userId` (`userId`)
)

    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_0900_ai_ci;


CREATE TABLE `mailqueue` (
    `id`          MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `mailId`      VARCHAR(36)           NOT NULL,
    `userId`      VARCHAR(36)           NOT NULL,
    `to`          TEXT                  NOT NULL,
    `from`        TEXT                  NOT NULL,
    `email`       TEXT                  NOT NULL,
    `subject`     TEXT                  NOT NULL,
    `body`        TEXT                  NOT NULL,
    `metadata`    JSON                  NOT NULL,
    `created`     DATETIME                       DEFAULT CURRENT_TIMESTAMP,
    `updated`     DATETIME                       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY `id` (`id`),
    KEY `mailId` (`mailId`),
    KEY `userId` (`userId`)
)

    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_0900_ai_ci;
