CREATE TYPE family_role AS ENUM (
    'owner',
    'admin',
    'member',
    'child'
);

CREATE TABLE family_members
(
    id        BIGSERIAL PRIMARY KEY,
    family_id BIGINT      NOT NULL REFERENCES families (id) ON DELETE CASCADE,
    user_id   BIGINT      NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    role      family_role NOT NULL DEFAULT 'member',
    joined_at TIMESTAMP   NOT NULL DEFAULT NOW(),
    UNIQUE (family_id, user_id)
);


-- быстрый поиск всех членов семьи
CREATE INDEX idx_family_members_family_id ON family_members (family_id);

-- быстрый поиск всех семей пользователя
CREATE INDEX idx_family_members_user_id ON family_members (user_id);

-- composite для частых join’ов
CREATE INDEX idx_family_members_user_family ON family_members (user_id, family_id);