-- Create users table
CREATE TABLE users (
    id CHAR(8) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    surname VARCHAR(50),
    email VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL CHECK (char_length(password_hash) >= 5),
    country VARCHAR(50) NOT NULL,
    language VARCHAR(20) DEFAULT 'English',
    profile_photo VARCHAR(255),
    address VARCHAR(150),
    phone VARCHAR(30),
    status VARCHAR(20) DEFAULT 'active',
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    role VARCHAR(10) NOT NULL CHECK (role IN ('admin', 'user'))
);

-- Create companies table (for admin users)
CREATE TABLE companies (
    id VARCHAR(16) PRIMARY KEY, -- {userid}_1, {userid}_2, {userid}_3
    user_id CHAR(8) REFERENCES users(id),
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    country VARCHAR(50),
    language VARCHAR(20) DEFAULT 'English',
    profile_photo VARCHAR(255),
    address VARCHAR(150),
    phone VARCHAR(30)
);
