-- 人员分组：分组主表 + 成员关联表（mp_user 员工）

CREATE TABLE IF NOT EXISTS mp_staff_group (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid VARCHAR(64) NOT NULL,
    version BIGINT NOT NULL DEFAULT 0,
    create_id BIGINT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_id BIGINT NULL,
    update_time DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    del_flag INT NOT NULL DEFAULT 0,

    group_name VARCHAR(128) NOT NULL COMMENT '分组名称',
    leader_mp_user_id BIGINT NULL COMMENT '负责人（mp_user.id，可为空）',

    KEY idx_mpsg_name (group_name),
    KEY idx_mpsg_leader (leader_mp_user_id)
);

CREATE TABLE IF NOT EXISTS mp_staff_group_member (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid VARCHAR(64) NOT NULL,
    version BIGINT NOT NULL DEFAULT 0,
    create_id BIGINT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_id BIGINT NULL,
    update_time DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    del_flag INT NOT NULL DEFAULT 0,

    group_id BIGINT NOT NULL COMMENT 'mp_staff_group.id',
    mp_user_id BIGINT NOT NULL COMMENT 'mp_user.id（员工）',

    UNIQUE KEY uk_group_user (group_id, mp_user_id),
    KEY idx_msgm_group (group_id),
    KEY idx_msgm_user (mp_user_id)
);