database.sql

CREATE DATABASE quarkus;
USE quarkus;


CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    last_connection TIMESTAMP,
    active ENUM('YES', 'NO') DEFAULT 'YES',
    role ENUM('REGULAR', 'ADMIN') NOT NULL,
    PRIMARY KEY (id)
);


CREATE TABLE device (
    id INT NOT NULL AUTO_INCREMENT,
    affected_user_id INT,
    state ENUM('WORKING', 'MAINTENANCE', 'ORDERED') NOT NULL,
    config TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY (affected_user_id) REFERENCES user(id) ON DELETE SET NULL
);

CREATE TABLE ticket (
    id INT NOT NULL AUTO_INCREMENT,
    impact ENUM('MINOR', 'MAJOR', 'BLOCKING') NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    state ENUM('OPEN', 'SOLVED', 'CLOSED') NOT NULL,
    user_id INT,
    device_id INT,
    type VARCHAR(100),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES device(id) ON DELETE SET NULL
);