CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(64) UNIQUE NOT NULL,
    user_password VARCHAR(255) NOT NULL,
    user_displayname VARCHAR(128),
    user_mail VARCHAR(128) UNIQUE,
    user_age INT,
    user_icon TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE chats (
    chat_id INT AUTO_INCREMENT PRIMARY KEY,
    chat_name VARCHAR(128),
    creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_group BOOLEAN DEFAULT 0
);

CREATE TABLE chat_members (
    chat_id INT NOT NULL,
    user_id INT NOT NULL,
    user_role VARCHAR(32) DEFAULT "member",
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (chat_id, user_id),
    FOREIGN KEY (chat_id) REFERENCES chats(chat_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE messages (
    message_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    chat_id INT NOT NULL,
    user_id INT NOT NULL,
    enc_content TEXT NOT NULL,
    sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    integrity_hash VARCHAR(128),
    is_deleted BOOLEAN DEFAULT 0,
    FOREIGN KEY (chat_id) REFERENCES chats(chat_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);