CREATE DATABASE IF NOT EXISTS `limpioo`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `limpioo`;

CREATE TABLE IF NOT EXISTS `users` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email`          VARCHAR(255) NOT NULL UNIQUE,
    `password_hash`  VARCHAR(255) NOT NULL,
    `email_verified` TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users_databases` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`          INT UNSIGNED   NOT NULL,
    `db_name`          VARCHAR(100)   NOT NULL UNIQUE,
    `db_username`      VARCHAR(100)   NOT NULL UNIQUE,
    `db_password_enc`  TEXT           NOT NULL,
    `iv`               VARBINARY(32)  NOT NULL,
    `tag`              VARBINARY(16)  NOT NULL,
    `api_key`          VARCHAR(255) DEFAULT 'afaec328c001bf09e9cad29057057ff2',
    `enabled`          TINYINT(1)     NOT NULL DEFAULT 1,
    `created_at`       TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_ud_user`
      FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
