--Tables for user role, user, department, message_type
CREATE TABLE user (
    id INT SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    updated_by_id INT DEFAULT NULL,
    department_id INT DEFAULT NULL,
    role_id INT DEFAULT NULL,
    -- FOREIGN KEY (created_by_id) REFERENCES user(id),
    -- FOREIGN KEY (updated_by_id) REFERENCES user(id),
    -- FOREIGN KEY (department_id) REFERENCES department(department_id),
    -- FOREIGN KEY (role_id) REFERENCES role(role_id)
);

CREATE TABLE po_chat (
    id INT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    title VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP DEFAULT NULL,
    created_by_id INT DEFAULT NULL,
    updated_by_id INT DEFAULT NULL,
    is_archived BOOL DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES user(id),
    -- FOREIGN KEY (created_by_id) REFERENCES user(id),
    -- FOREIGN KEY (updated_by_id) REFERENCES user(id)
);

CREATE TABLE po_message (
    id INT PRIMARY KEY,
    chat_id INT NOT NULL,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    message TEXT,
    message_type VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (chat_id) REFERENCES po_chat(chat_id),
    FOREIGN KEY (sender_id) REFERENCES user(id),
    FOREIGN KEY (receiver_id) REFERENCES user(id)
);

