CREATE TABLE user_roles (
    role_id TINYINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    role_name ENUM('administrator', 'company', 'guest')
);

CREATE TABLE user_statuses (
    status_id TINYINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    status_name ENUM('activated', 'not_activated', 'banned')
);

CREATE TABLE users (
    user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_first_name VARCHAR(50) NOT NULL,
    user_last_name VARCHAR(50) NOT NULL,
    user_login VARCHAR(50) NOT NULL UNIQUE,
    user_email VARCHAR(70) NOT NULL UNIQUE,
    user_password_hash VARCHAR(255) NOT NULL,
    user_registration_date DATE NOT NULL,
    role_id TINYINT UNSIGNED NOT NULL,
    status_id TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY (role_id) REFERENCES user_roles(role_id),
    FOREIGN KEY (status_id) REFERENCES user_statuses(status_id)
);

CREATE TABLE companies (
    company_id BIGINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL UNIQUE,
    company_address VARCHAR(200) NOT NULL,
    company_unp TINYINT UNSIGNED NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    FOREIGN KEY (user_id) REFERENCES users (user_id)
);

CREATE TABLE user_roles (
    role_id TINYINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    role_name ENUM('administrator', 'user', 'guest')
);

CREATE TABLE user_statuses (
    status_id TINYINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    status_name ENUM('activated', 'not_activated', 'banned')
);

CREATE TABLE users (
    user_id BIGINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    user_first_name VARCHAR(50) NOT NULL,
    user_last_name VARCHAR(50) NOT NULL,
    user_login VARCHAR(50) NOT NULL UNIQUE,
    user_email VARCHAR(70) NOT NULL UNIQUE,
    user_password_hash VARCHAR(255) NOT NULL,
    user_registration_date DATE NOT NULL,
    role_id TINYINT UNSIGNED NOT NULL,
    status_id TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY (role_id) REFERENCES user_roles(role_id),
    FOREIGN KEY (status_id) REFERENCES user_statuses(status_id)
);

CREATE TABLE companies (
    company_id BIGINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL UNIQUE,
    company_address VARCHAR(200) NOT NULL,
    company_unp TINYINT UNSIGNED NOT NULL UNIQUE
);

CREATE TABLE forwarders (
    forwarder_id BIGINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    company_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (company_id) REFERENCES companies(company_id)
);