Number & Tag Operational Runbook

UPDATE event_participant ep
JOIN tmp_pairing_<event> pr ON pr.tag_id = ep.tag_id
SET ep.number_id = pr.rn_id
WHERE ep.event_id = :EVENT_ID
  AND ep.active = 1
  AND ep.number_id IS NULL;
-- Confirm: SELECT ROW_COUNT();

DROP TABLE IF EXISTS tmp_timing_<event>;
CREATE TABLE tmp_timing_<event> (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  school_type CHAR(2) NOT NULL,
  ep_id BIGINT NULL,
  bib VARCHAR(10) NOT NULL,
  first_name VARCHAR(80),
  last_name VARCHAR(80),
  resolved_number_id BIGINT NULL,
  resolved_tag_id BIGINT NULL,
  resolve_status VARCHAR(30) DEFAULT 'PENDING',
  KEY ix_ep (ep_id),
  KEY ix_school (school_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Then INSERT rows, filtering out every line matching ^Bib, (per-section header)

-- Strict rule: PS files imply 'P' prefix, HS files imply 'H' prefix. No swapping.
UPDATE tmp_timing_<event> ti
JOIN race_number rn ON rn.number = CONCAT(IF(ti.school_type='PS','P','H'), ti.bib)
  AND rn.type_id = :NUMBER_TYPE_ID
SET ti.resolved_number_id = rn.id;

UPDATE tmp_timing_<event> ti
JOIN tmp_pairing_<event> pr ON pr.rn_id = ti.resolved_number_id
SET ti.resolved_tag_id = pr.tag_id
WHERE ti.resolved_number_id IS NOT NULL;

UPDATE tmp_timing_<event> SET resolve_status = CASE
  WHEN ep_id IS NULL THEN 'WALK_IN'
  WHEN resolved_number_id IS NULL THEN 'NO_NUMBER'
  WHEN resolved_tag_id IS NULL THEN 'NO_TAG'
  ELSE 'MATCHED' END;

-- Audit table to capture before-state for the rows about to change
DROP TABLE IF EXISTS tmp_proc1_before_<event>;
CREATE TABLE tmp_proc1_before_<event> (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ep_id BIGINT NOT NULL,
  old_number_id BIGINT NULL,
  old_tag_id BIGINT NULL,
  new_number_id BIGINT NULL,
  new_tag_id BIGINT NULL
) ENGINE=InnoDB;

INSERT INTO tmp_proc1_before_<event> (ep_id, old_number_id, old_tag_id, new_number_id, new_tag_id)
SELECT ep.id, ep.number_id, ep.tag_id, ti.resolved_number_id, ti.resolved_tag_id
FROM event_participant ep
JOIN tmp_timing_<event> ti ON ti.ep_id = ep.id
WHERE ep.event_id = :EVENT_ID
  AND ep.active = 1
  AND ti.resolve_status = 'MATCHED'
  AND (
    COALESCE(ep.number_id,0) <> COALESCE(ti.resolved_number_id,0)
    OR COALESCE(ep.tag_id,0) <> COALESCE(ti.resolved_tag_id,0)
  );

-- Apply
UPDATE event_participant ep
JOIN tmp_timing_<event> ti ON ti.ep_id = ep.id
SET ep.number_id = ti.resolved_number_id,
    ep.tag_id    = ti.resolved_tag_id
WHERE ep.event_id = :EVENT_ID
  AND ep.active = 1
  AND ti.resolve_status = 'MATCHED';

UPDATE race_number rn
JOIN event_participant ep ON ep.number_id = rn.id
JOIN event e ON e.id = ep.event_id
SET rn.person_id = ep.person_id,
    rn.last_used = e.start_date_time
WHERE ep.event_id = :EVENT_ID
  AND ep.active = 1
  AND ep.number_id IS NOT NULL;
-- Confirm: SELECT ROW_COUNT();

UPDATE tag t
JOIN event_participant ep ON ep.tag_id = t.id
JOIN event e ON e.id = ep.event_id
SET t.person_id = ep.person_id,
    t.last_used = e.start_date_time
WHERE ep.event_id = :EVENT_ID
  AND ep.active = 1
  AND ep.tag_id IS NOT NULL;
-- Confirm: SELECT ROW_COUNT();

-- Confirm all assigned numbers/tags now have person_id set
SELECT COUNT(*) AS assigned_without_person
FROM event_participant ep
JOIN race_number rn ON rn.id = ep.number_id
WHERE ep.event_id = :EVENT_ID
  AND ep.active = 1
  AND rn.person_id IS NULL;
-- Should return 0

-- Spot-check last_used is set to the event start time
SELECT rn.last_used, COUNT(*) AS rn_count
FROM event_participant ep JOIN race_number rn ON rn.id = ep.number_id
WHERE ep.event_id = :EVENT_ID AND ep.active = 1
GROUP BY rn.last_used ORDER BY rn_count DESC LIMIT 5;

CREATE TABLE tmp_returned_barcodes_<event> (
  id INT AUTO_INCREMENT PRIMARY KEY,
  barcode VARCHAR(20) NOT NULL,
  UNIQUE KEY uq_barcode (barcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert barcodes (de-duplicated; one per line)
INSERT IGNORE INTO tmp_returned_barcodes_<event> (barcode) VALUES
  ('2403250042567'), ('2403250012638');  -- etc.

-- Truncate to 13 chars if reader gave longer
UPDATE tmp_returned_barcodes_<event> SET barcode = LEFT(barcode, 13)
WHERE LENGTH(barcode) > 13;

-- Verify all barcodes resolve to tags
SELECT rb.barcode,
       t.id AS tag_id, t.number AS tag_number, t.seq, t.person_id, t.last_used
FROM tmp_returned_barcodes_<event> rb
LEFT JOIN tag t ON t.barcode = rb.barcode
ORDER BY rb.barcode;

-- Flag any unresolved barcodes (typos, wrong format)
SELECT rb.barcode AS unresolved_barcode
FROM tmp_returned_barcodes_<event> rb
LEFT JOIN tag t ON t.barcode = rb.barcode
WHERE t.id IS NULL;

DROP TABLE IF EXISTS tmp_proc3_before_<event>;
CREATE TABLE tmp_proc3_before_<event> (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  source_barcode VARCHAR(20),
  tag_id BIGINT, tag_seq INT, tag_type_id INT, tag_person_id BIGINT, tag_last_used DATETIME(3),
  rn_id BIGINT, rn_number VARCHAR(20), rn_sequence INT, rn_type_id INT, rn_person_id BIGINT, rn_last_used DATETIME(3)
) ENGINE=InnoDB;

INSERT INTO tmp_proc3_before_<event>
  (source_barcode, tag_id, tag_seq, tag_type_id, tag_person_id, tag_last_used,
   rn_id, rn_number, rn_sequence, rn_type_id, rn_person_id, rn_last_used)
SELECT rb.barcode, t.id, t.seq, t.type_id, t.person_id, t.last_used,
       rn.id, rn.number, rn.sequence, rn.type_id, rn.person_id, rn.last_used
FROM tmp_returned_barcodes_<event> rb
LEFT JOIN tag t ON t.barcode = rb.barcode
LEFT JOIN tmp_pairing_<event> pr ON pr.tag_id = t.id
LEFT JOIN race_number rn ON rn.id = pr.rn_id;

UPDATE tag t
JOIN tmp_returned_barcodes_<event> rb ON rb.barcode = t.barcode
SET t.person_id = NULL, t.last_used = NULL;
-- Confirm: SELECT ROW_COUNT();

UPDATE race_number rn
JOIN tmp_pairing_<event> pr ON pr.rn_id = rn.id
JOIN tag t ON t.id = pr.tag_id
JOIN tmp_returned_barcodes_<event> rb ON rb.barcode = t.barcode
SET rn.person_id = NULL, rn.last_used = NULL;
-- Confirm: SELECT ROW_COUNT();

-- All returned tags should now have NULL person/last_used
SELECT COUNT(*) AS tags_still_owned
FROM tmp_returned_barcodes_<event> rb
JOIN tag t ON t.barcode = rb.barcode
WHERE t.person_id IS NOT NULL OR t.last_used IS NOT NULL;
-- Should be 0

-- Paired type-78 race_numbers should also be cleared
SELECT COUNT(*) AS rns_still_owned
FROM tmp_returned_barcodes_<event> rb
JOIN tag t ON t.barcode = rb.barcode
JOIN tmp_pairing_<event> pr ON pr.tag_id = t.id
JOIN race_number rn ON rn.id = pr.rn_id
WHERE rn.person_id IS NOT NULL OR rn.last_used IS NOT NULL;
-- Should be 0

-- Event-N (just-completed) EP rows should be UNCHANGED by this procedure
SELECT SUM(number_id IS NOT NULL) AS has_number, SUM(tag_id IS NOT NULL) AS has_tag
FROM event_participant WHERE event_id = :EVENT_ID AND active = 1;
-- Should match the post-Procedure-1 counts

SELECT e.id, e.name, e.start_date_time FROM event e WHERE e.id = :TARGET_EVENT_ID;
-- Confirm number type (e.g., 78) and tag type (e.g., 12) for this event

SELECT COUNT(*) AS numbers,
       SUM(rn.tag_id IS NOT NULL) AS authoritative,
       SUM(rn.tag_id IS NULL)     AS needs_seq_fallback
FROM race_number rn WHERE rn.type_id = :NUMBER_TYPE_ID;

-- Colliding seq values in the tag type. Any row here is ambiguous for the
-- fallback and must be resolved by hand before proceeding.
SELECT t.seq, COUNT(*) AS tags, GROUP_CONCAT(t.id) AS tag_ids
FROM tag t
WHERE t.type_id = :TAG_TYPE_ID AND t.deleted = 'N' AND t.seq IS NOT NULL
GROUP BY t.seq HAVING COUNT(*) > 1 ORDER BY t.seq;

DROP TABLE IF EXISTS tmp_pairing_<event>;
CREATE TABLE tmp_pairing_<event> (
  rn_id      BIGINT NOT NULL PRIMARY KEY,
  tag_id     BIGINT NOT NULL,
  paired_via ENUM('tag_id','seq') NOT NULL,
  UNIQUE KEY uq_tag (tag_id)
) ENGINE=InnoDB;

-- Authoritative side first, so it wins any contest with the fallback.
INSERT INTO tmp_pairing_<event> (rn_id, tag_id, paired_via)
SELECT rn.id, rn.tag_id, 'tag_id'
FROM race_number rn
JOIN tag t ON t.id = rn.tag_id AND t.deleted = 'N'
WHERE rn.type_id = :NUMBER_TYPE_ID
  AND rn.tag_id IS NOT NULL;

-- Fallback, for numbers with no tag_id yet. Do NOT add
-- `ON DUPLICATE KEY UPDATE` or `INSERT IGNORE` here — that is exactly the
-- silent wrong pair this table exists to prevent.
INSERT INTO tmp_pairing_<event> (rn_id, tag_id, paired_via)
SELECT rn.id, t.id, 'seq'
FROM race_number rn
JOIN tag t ON t.seq = rn.sequence
          AND t.type_id = :TAG_TYPE_ID
          AND t.deleted = 'N'
WHERE rn.type_id = :NUMBER_TYPE_ID
  AND rn.tag_id IS NULL;

-- How the pairing was resolved. Review before continuing.
SELECT paired_via, COUNT(*) FROM tmp_pairing_<event> GROUP BY paired_via;

SELECT ep.id AS ep_id, ep.first_name, ep.last_name, ep.person_id,
       ep.number_id AS current_number_id, ep.tag_id AS current_tag_id,
       rn.id AS rn_id, rn.number, rn.last_used,
       t.id AS tag_id, t.barcode, pr.paired_via
FROM event_participant ep
JOIN race_number rn ON rn.person_id = ep.person_id AND rn.type_id = :NUMBER_TYPE_ID
JOIN tmp_pairing_<event> pr ON pr.rn_id = rn.id
JOIN tag t ON t.id = pr.tag_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND (ep.number_id IS NULL OR ep.tag_id IS NULL)
ORDER BY ep.id;

-- Find EPs with partial assignment (tag but no number)
SELECT ep.id, ep.first_name, ep.last_name, ep.tag_id, t.seq,
       rn.id AS paired_rn_id, rn.number AS paired_number, rn.person_id AS rn_person
FROM event_participant ep
JOIN tag t ON t.id = ep.tag_id
LEFT JOIN tmp_pairing_<event> pr ON pr.tag_id = t.id
LEFT JOIN race_number rn ON rn.id = pr.rn_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND ep.number_id IS NULL
  AND ep.tag_id IS NOT NULL;

-- And the reverse: number but no tag
SELECT ep.id, ep.first_name, ep.last_name, ep.number_id, rn.sequence,
       t.id AS paired_tag_id, t.barcode AS paired_barcode
FROM event_participant ep
JOIN race_number rn ON rn.id = ep.number_id
LEFT JOIN tmp_pairing_<event> pr ON pr.rn_id = rn.id
LEFT JOIN tag t ON t.id = pr.tag_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND ep.number_id IS NOT NULL
  AND ep.tag_id IS NULL;

-- Fill in missing number_id based on existing tag_id
UPDATE event_participant ep
JOIN tmp_pairing_<event> pr ON pr.tag_id = ep.tag_id
JOIN race_number rn ON rn.id = pr.rn_id
SET ep.number_id = rn.id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND ep.number_id IS NULL
  AND ep.tag_id IS NOT NULL;

-- Fill in missing tag_id based on existing number_id
UPDATE event_participant ep
JOIN tmp_pairing_<event> pr ON pr.rn_id = ep.number_id
SET ep.tag_id = pr.tag_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND ep.number_id IS NOT NULL
  AND ep.tag_id IS NULL;

SELECT ep.id AS ep_id, ep.person_id,
       rn.id AS rn_id, rn.number, rn.last_used,
       t.id AS tag_id
FROM event_participant ep
JOIN race_number rn ON rn.person_id = ep.person_id AND rn.type_id = :NUMBER_TYPE_ID
JOIN tmp_pairing_<event> pr ON pr.rn_id = rn.id
JOIN tag t ON t.id = pr.tag_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND ep.number_id IS NULL
  AND rn.last_used = (
    SELECT MAX(rn2.last_used)
    FROM race_number rn2
    WHERE rn2.person_id = ep.person_id AND rn2.type_id = :NUMBER_TYPE_ID
  )
ORDER BY ep.id;

DROP TABLE IF EXISTS tmp_carryover_<event>;
CREATE TABLE tmp_carryover_<event> (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ep_id BIGINT NOT NULL,
  rn_id BIGINT NOT NULL,
  tag_id BIGINT NOT NULL,
  UNIQUE KEY uq_ep (ep_id)
) ENGINE=InnoDB;

INSERT INTO tmp_carryover_<event> (ep_id, rn_id, tag_id)
SELECT c.ep_id, c.rn_id, c.tag_id FROM (
  SELECT ep.id AS ep_id, rn.id AS rn_id, pr.tag_id,
         ROW_NUMBER() OVER (
             PARTITION BY ep.id
             ORDER BY rn.last_used IS NULL, rn.last_used DESC, rn.id DESC
         ) AS rnk
  FROM event_participant ep
  JOIN race_number rn ON rn.person_id = ep.person_id
                     AND rn.type_id   = :NUMBER_TYPE_ID
                     AND rn.state    <> 'S'
  JOIN tmp_pairing_<event> pr ON pr.rn_id = rn.id
  WHERE ep.event_id = :TARGET_EVENT_ID
    AND ep.active = 1
    AND (ep.number_id IS NULL OR ep.tag_id IS NULL)
) c WHERE c.rnk = 1;

UPDATE event_participant ep
JOIN tmp_carryover_<event> a ON a.ep_id = ep.id
SET ep.number_id = a.rn_id, ep.tag_id = a.tag_id;
-- Confirm: SELECT ROW_COUNT();

SELECT 'Total active participants' AS metric, COUNT(*) AS cnt
  FROM event_participant WHERE event_id = :TARGET_EVENT_ID AND active = 1
UNION ALL
SELECT 'Assigned (number + tag)', COUNT(*)
  FROM event_participant WHERE event_id = :TARGET_EVENT_ID AND active = 1
  AND number_id IS NOT NULL AND tag_id IS NOT NULL
UNION ALL
SELECT 'Not assigned', COUNT(*)
  FROM event_participant WHERE event_id = :TARGET_EVENT_ID AND active = 1
  AND (number_id IS NULL OR tag_id IS NULL);

DROP TABLE IF EXISTS tmp_id_to_rn;
CREATE TABLE tmp_id_to_rn (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  identity_number VARCHAR(64) NOT NULL,
  person_id BIGINT NOT NULL,
  rn_id BIGINT NOT NULL,
  rn_number VARCHAR(20),
  rn_sequence INT,
  rn_last_used DATETIME(3),
  tag_id BIGINT,
  tag_barcode VARCHAR(20),
  KEY ix_id (identity_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tmp_id_to_rn
  (identity_number, person_id, rn_id, rn_number, rn_sequence, rn_last_used, tag_id, tag_barcode)
SELECT um.meta_value, rn.person_id, rn.id, rn.number, rn.sequence, rn.last_used,
       t.id, t.barcode
FROM race_number rn
JOIN wp_usermeta um ON um.user_id = rn.person_id AND um.meta_key = 'id_number'
JOIN tmp_pairing_<event> pr ON pr.rn_id = rn.id
JOIN tag t ON t.id = pr.tag_id
WHERE rn.type_id = :NUMBER_TYPE_ID
  AND rn.person_id IS NOT NULL
  AND rn.last_used IS NOT NULL
  AND um.meta_value IS NOT NULL AND um.meta_value <> '';

SELECT COUNT(DISTINCT ep.id) AS recoverable
FROM event_participant ep
JOIN tmp_id_to_rn l ON l.identity_number = ep.identity_number AND l.person_id <> ep.person_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND (ep.number_id IS NULL OR ep.tag_id IS NULL)
  AND ep.identity_number IS NOT NULL AND ep.identity_number <> '';

SELECT
  ep.identity_number AS sa_id,
  ep.person_id AS new_person_id,
  CONCAT(ep.first_name, ' ', ep.last_name) AS event_name,
  u_new.user_email AS new_email,
  DATE(u_new.user_registered) AS new_reg_date,
  l.person_id AS old_person_id,
  u_old.display_name AS old_display_name,
  u_old.user_email AS old_email,
  DATE(u_old.user_registered) AS old_reg_date,
  l.rn_number, DATE(l.rn_last_used) AS rn_last_used
FROM event_participant ep
JOIN tmp_id_to_rn l ON l.identity_number = ep.identity_number AND l.person_id <> ep.person_id
LEFT JOIN wp_users u_new ON u_new.ID = ep.person_id
LEFT JOIN wp_users u_old ON u_old.ID = l.person_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND (ep.number_id IS NULL OR ep.tag_id IS NULL)
  AND l.rn_last_used = (
    SELECT MAX(l2.rn_last_used) FROM tmp_id_to_rn l2 WHERE l2.identity_number = ep.identity_number
  )
ORDER BY ep.last_name, ep.first_name;

DROP TABLE IF EXISTS tmp_id_recovery_<event>;
CREATE TABLE tmp_id_recovery_<event> (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ep_id BIGINT NOT NULL,
  rn_id BIGINT NOT NULL,
  tag_id BIGINT NOT NULL,
  UNIQUE KEY uq_ep (ep_id)
) ENGINE=InnoDB;

INSERT INTO tmp_id_recovery_<event> (ep_id, rn_id, tag_id)
SELECT ep.id, l.rn_id, l.tag_id
FROM event_participant ep
JOIN tmp_id_to_rn l ON l.identity_number = ep.identity_number AND l.person_id <> ep.person_id
WHERE ep.event_id = :TARGET_EVENT_ID
  AND ep.active = 1
  AND (ep.number_id IS NULL OR ep.tag_id IS NULL)
  AND ep.id NOT IN (:EXCLUDED_EP_IDS)  -- list raised by Step 5.3 review
  AND l.rn_last_used = (
    SELECT MAX(l2.rn_last_used) FROM tmp_id_to_rn l2 WHERE l2.identity_number = ep.identity_number
  );

UPDATE event_participant ep
JOIN tmp_id_recovery_<event> r ON r.ep_id = ep.id
SET ep.number_id = r.rn_id, ep.tag_id = r.tag_id;
-- Confirm: SELECT ROW_COUNT();

-- Final state for the target event
SELECT 'Total active' AS metric, COUNT(*) AS cnt
  FROM event_participant WHERE event_id = :TARGET_EVENT_ID AND active = 1
UNION ALL
SELECT 'Assigned (number + tag)', COUNT(*)
  FROM event_participant WHERE event_id = :TARGET_EVENT_ID AND active = 1
  AND number_id IS NOT NULL AND tag_id IS NOT NULL;