-- Create or modify the users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- Ensure hashed passwords are stored
    status ENUM('online', 'offline') DEFAULT 'offline',
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create or modify the messages table
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    group_id INT NOT NULL,
    message TEXT NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert sample users
INSERT INTO users (username, password, status, last_activity) VALUES
('User1', 'hashed_password1', 'online', NOW()),
('User2', 'hashed_password2', 'offline', NOW() - INTERVAL 5 MINUTE)
ON DUPLICATE KEY UPDATE username=VALUES(username), password=VALUES(password), status=VALUES(status), last_activity=VALUES(last_activity);

-- Insert sample messages
INSERT INTO messages (sender_id, group_id, message, timestamp) VALUES
(1, 1, 'Hello, everyone!', NOW() - INTERVAL 1 MINUTE),
(2, 1, 'Hi there!', NOW())
ON DUPLICATE KEY UPDATE sender_id=VALUES(sender_id), group_id=VALUES(group_id), message=VALUES(message), timestamp=VALUES(timestamp);