-- Active: 1783331450344@@127.0.0.1@3306@cmms
-- 1. group_infos (roles) — create first since users references it
CREATE TABLE group_infos (
    group_id    INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255)
);

-- 2. Users — now with a direct group_id column
CREATE TABLE users (
    user_id      INT PRIMARY KEY AUTO_INCREMENT,
    username     VARCHAR(100) NOT NULL UNIQUE,
    email        VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NULL,
    group_id     INT NULL,                 -- nullable if a user can exist with no group yet
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES group_infos(group_id) ON DELETE SET NULL
);

-- 3. Resources / modules
CREATE TABLE resources (
    resource_id   INT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(100) NOT NULL UNIQUE
);

-- 4. Actions
CREATE TABLE actions (
    action_id     INT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(50) NOT NULL UNIQUE
);

-- 5. Group permissions (unchanged)
CREATE TABLE group_permissions (
    group_id      INT NOT NULL,
    resource_id   INT NOT NULL,
    action_id     INT NOT NULL,
    PRIMARY KEY (group_id, resource_id, action_id),
    FOREIGN KEY (group_id)    REFERENCES group_infos(group_id)   ON DELETE CASCADE,
    FOREIGN KEY (resource_id) REFERENCES resources(resource_id) ON DELETE CASCADE,
    FOREIGN KEY (action_id)   REFERENCES actions(action_id) ON DELETE CASCADE
);