CREATE TABLE IF NOT EXISTS conversations (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uuid            CHAR(36)        NOT NULL,
    type            ENUM('direct','group') NOT NULL DEFAULT 'direct',
    group_id        BIGINT UNSIGNED DEFAULT NULL,
    created_by      BIGINT UNSIGNED NOT NULL,
    last_message_id BIGINT UNSIGNED DEFAULT NULL,
    last_activity   DATETIME        DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_uuid (uuid),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conversation_members (
    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    conversation_id  BIGINT UNSIGNED NOT NULL,
    user_id          BIGINT UNSIGNED NOT NULL,
    role             ENUM('member','admin') DEFAULT 'member',
    is_muted         TINYINT(1)      DEFAULT 0,
    is_pinned        TINYINT(1)      DEFAULT 0,
    is_archived      TINYINT(1)      DEFAULT 0,
    last_read_msg_id BIGINT UNSIGNED DEFAULT NULL,
    joined_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    left_at          DATETIME        DEFAULT NULL,
    UNIQUE KEY uq_conv_user (conversation_id, user_id),
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;