-- Migration to support DMs and Message Editing
ALTER TABLE messages ADD COLUMN updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP;

-- Support for DMs in channels table
ALTER TABLE channels MODIFY COLUMN server_id INT NULL;
ALTER TABLE channels MODIFY COLUMN type ENUM('text', 'voice', 'dm') DEFAULT 'text';

-- Track members in channels (especially for DMs)
CREATE TABLE IF NOT EXISTS channel_members (
    channel_id INT NOT NULL,
    user_id INT NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (channel_id, user_id),
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Notifications: Track last read message per channel per user
CREATE TABLE IF NOT EXISTS channel_last_read (
    channel_id INT NOT NULL,
    user_id INT NOT NULL,
    last_read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (channel_id, user_id),
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);