USE ggn_famlynk;

-- User member numbering for permanent family member IDs
ALTER TABLE users
  ADD COLUMN member_number INT NULL;

-- Backfill member_number sequentially per family by creation/id order.
SET @prev_family := NULL;
SET @row_num := 0;

UPDATE users u
JOIN (
  SELECT
    id,
    family_id,
    (@row_num := IF(@prev_family = family_id, @row_num + 1, 1)) AS seq_num,
    (@prev_family := family_id) AS _family_tracker
  FROM users
  ORDER BY family_id, created_at, id
) x ON x.id = u.id
SET u.member_number = x.seq_num
WHERE u.member_number IS NULL;

ALTER TABLE users
  MODIFY member_number INT NOT NULL,
  ADD UNIQUE INDEX uq_users_family_member_number (family_id, member_number);