-- Groups gain their own settings: a manual flag (manual groups persist; auto
-- groups are garbage-collected when empty), a visibility, and collaborators
-- whose access cascades to every repository in the group's subtree.
ALTER TABLE groups ADD COLUMN manual INTEGER NOT NULL DEFAULT 0;
-- Auto/intermediate groups default to 'public' so they impose no visibility
-- ceiling; only a deliberately-restricted group locks the repos beneath it.
ALTER TABLE groups ADD COLUMN visibility TEXT NOT NULL DEFAULT 'public';

CREATE TABLE group_collaborators (
    id         TEXT PRIMARY KEY,
    group_id   TEXT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permission TEXT NOT NULL,
    created_at BIGINT NOT NULL
);
CREATE UNIQUE INDEX idx_group_collab ON group_collaborators (group_id, user_id);