Runbook: wp_users / wp_usermeta surgical restore (WPCA UID-collision incident)

mkdir -p recon/logs/wp-users-restore
awk -F, 'FNR>1 && $1!="" {print $1}' \
  input/123-participants.csv input/125-participants.csv input/126-participants.csv \
  | sort -un > recon/logs/wp-users-restore/affected_uids.txt
wc -l recon/logs/wp-users-restore/affected_uids.txt   # expect 519

-- Pre-flight: how many UIDs exist in live wp_users?
SELECT COUNT(*) FROM wp_users WHERE ID IN (<519>);   -- expect 467

-- Pre-flight: any admin / system accounts?
SELECT ID, user_login FROM wp_users
WHERE ID IN (<519>) AND (ID <= 10 OR user_login IN ('admin','administrator'));
-- If any row returns, STOP and re-plan.

SELECT COUNT(*) FROM wp_restore_260417.wp_users;         -- expect ~33780
SELECT COUNT(*) FROM wp_restore_260417.wp_usermeta;      -- expect ~410726

-- Sentinel: source untouched
SELECT COUNT(*) FROM wpca_prod.wp_users;                 -- unchanged vs pre-load
SELECT COUNT(*) FROM wpca_prod.wp_usermeta;              -- unchanged vs pre-load

START TRANSACTION;

DELETE rr FROM race_result rr
  JOIN result_set rs ON rs.id = rr.result_set_id
  WHERE rs.event_id IN (119, 120, 121);
-- expect ~696 rows

DELETE FROM event_participant WHERE event_id IN (119, 120, 121);
-- expect ~792 rows

SELECT
  (SELECT COUNT(*) FROM race_result rr JOIN result_set rs ON rs.id = rr.result_set_id
   WHERE rs.event_id IN (119,120,121)) AS rr_remaining,
  (SELECT COUNT(*) FROM event_participant WHERE event_id IN (119,120,121)) AS ep_remaining,
  (SELECT COUNT(*) FROM result_set WHERE event_id IN (119,120,121)) AS rs_preserved;
-- expect 0, 0, 30

COMMIT;

CREATE TABLE wp_users_prerestore_20260422 LIKE wp_users;
INSERT INTO wp_users_prerestore_20260422 SELECT * FROM wp_users WHERE ID IN (<519>);
-- expect 467 rows

CREATE TABLE wp_usermeta_prerestore_20260422 LIKE wp_usermeta;
INSERT INTO wp_usermeta_prerestore_20260422 SELECT * FROM wp_usermeta WHERE user_id IN (<519>);
-- expect 10422 rows

for e in 119 120 121; do
  curl -X PUT -H "X-API-KEY: $ADMIN_KEY" \
    -F "file=@input/${e}-participants-typed.xlsx" \
    "https://admin-service.idealogic.co.za/api/event-participants/import\
?eventId=${e}&sheetIndex=0&createCustom1=true&createCustom2=true&createCustom3=true"
done

SELECT um.meta_value AS id_number, COUNT(DISTINCT um.user_id) AS users
FROM event_participant ep
JOIN wp_usermeta um ON um.user_id = ep.person_id AND um.meta_key='id_number' AND um.meta_value<>''
WHERE ep.event_id IN (119,120,121)
GROUP BY um.meta_value HAVING users > 1;
-- Expected: 0 rows

curl -X PUT -H "X-API-KEY: $ADMIN_KEY" \
  -F "file=@input/119-ResultExport.csv" \
  "https://admin-service.idealogic.co.za/api/result-sets/import-bulk\
?eventId=119&participantIdMode=regid&pointsCalculator=wpca-road-league"

curl -X PUT -H "X-API-KEY: $ADMIN_KEY" \
  -F "file=@input/120-ResultExport-fixed.csv" \
  "https://admin-service.idealogic.co.za/api/result-sets/import-bulk\
?eventId=120&participantIdMode=regid&pointsCalculator=wpca-road-league"

curl -X PUT -H "X-API-KEY: $ADMIN_KEY" \
  -F "file=@input/121-ResultExport-fixed.csv" \
  "https://admin-service.idealogic.co.za/api/result-sets/import-bulk\
?eventId=121&participantIdMode=regid&pointsCalculator=wpca-road-league"

DELETE rr FROM race_result rr
  JOIN result_set rs ON rs.id = rr.result_set_id
  JOIN race r ON r.id = rs.race_id
  JOIN event_category ec ON ec.id = r.event_category_id
  JOIN event_participant ep ON ep.id = rr.event_participant_id
WHERE rs.event_id = 121
  AND ep.registration_id = '30274'
  AND ec.name = 'Category 4'
  AND rr.status IS NULL AND rr.position IS NULL;
-- expect 1 row