-- =============================================================================
-- 04-backfill-member-roles.sql
-- Run AFTER access_roles are seeded (owner login or: npm run seed:access-roles)
-- Maps members.role (owner/admin/member) -> access_roles.slug
-- Safe to run multiple times (only updates NULL access_role_id).
-- =============================================================================

UPDATE `members` m
INNER JOIN `access_roles` ar
  ON ar.owner_id = m.owner_id
  AND ar.slug COLLATE utf8mb4_general_ci = m.role
SET m.access_role_id = ar.id
WHERE m.access_role_id IS NULL;

SELECT
  m.role,
  COUNT(*) AS member_count,
  SUM(m.access_role_id IS NULL) AS missing_access_role_id
FROM members m
GROUP BY m.role;

SELECT COUNT(*) AS total_members_still_missing_role
FROM members
WHERE access_role_id IS NULL;
