CREATE TABLE IF NOT EXISTS `groups` (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uuid        CHAR(36)        NOT NULL,
    name        VARCHAR(100)    NOT NULL,
    description TEXT            DEFAULT NULL,
    avatar_path VARCHAR(500)    DEFAULT NULL,
    invite_link VARCHAR(32)     NOT NULL,
    created_by  BIGINT UNSIGNED NOT NULL,
    max_members SMALLINT UNSIGNED DEFAULT 500,
    is_public   TINYINT(1)      DEFAULT 0,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_uuid (uuid),
    UNIQUE KEY uq_invite (invite_link),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS group_members (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    group_id    BIGINT UNSIGNED NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    role        ENUM('member','moderator','admin','owner') DEFAULT 'member',
    permissions JSON            DEFAULT NULL,
    added_by    BIGINT UNSIGNED DEFAULT NULL,
    joined_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    left_at     DATETIME        DEFAULT NULL,
    UNIQUE KEY uq_group_user (group_id, user_id),
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_group (group_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;