-- Restore the FK from group_members to tenant_members that 018 dropped when
-- membership moved into grants: a group member must be a tenant member, and
-- revoking membership now cascades group memberships away too. SQLite can't add
-- a constraint in place, so rebuild the table.
CREATE TABLE group_members_new (
    tenant     TEXT NOT NULL,
    group_path TEXT NOT NULL,
    user_id    TEXT NOT NULL,
    PRIMARY KEY (tenant, group_path, user_id),
    FOREIGN KEY (tenant, group_path) REFERENCES groups(tenant, path) ON DELETE CASCADE,
    FOREIGN KEY (tenant, user_id) REFERENCES tenant_members(tenant, user) ON DELETE CASCADE
) STRICT;

-- Carry over only rows whose user is a tenant member; any orphan violates the
-- restored invariant and is dropped rather than failing the migration.
INSERT INTO group_members_new (tenant, group_path, user_id)
SELECT gm.tenant, gm.group_path, gm.user_id
FROM group_members gm
WHERE EXISTS (SELECT 1 FROM tenant_members m WHERE m.tenant = gm.tenant AND m.user = gm.user_id);

DROP TABLE group_members;
ALTER TABLE group_members_new RENAME TO group_members;
CREATE INDEX idx_group_members_user ON group_members(user_id);