init.sql

-- Create groups table
CREATE TABLE groups (
                        id SERIAL PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        status VARCHAR(50) NOT NULL DEFAULT 'empty',
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create users table
CREATE TABLE users (
                       id SERIAL PRIMARY KEY,
                       name VARCHAR(255) NOT NULL,
                       email VARCHAR(255) NOT NULL UNIQUE,
                       status VARCHAR(50) NOT NULL DEFAULT 'pending',
                       group_id INTEGER REFERENCES groups(id) ON DELETE SET NULL,
                       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert initial data into groups table
INSERT INTO groups (name, status) VALUES ('Admins', 'notEmpty');
INSERT INTO groups (name, status) VALUES ('Users', 'notEmpty');
INSERT INTO groups (name, status) VALUES ('Guest', 'empty');

-- Insert initial data into users table
INSERT INTO users (name, email, status, group_id) VALUES ('John Doe', 'john@example.com', 'active', 1);
INSERT INTO users (name, email, status, group_id) VALUES ('Jane Smith', 'jane@example.com', 'active', 2);
INSERT INTO users (name, email, status, group_id) VALUES ('Bob Wilson', 'bob@example.com', 'pending', NULL);