Number & Tag Operational Runbook

-- Load barcodes into a temporary table for batch processing
CREATE TEMPORARY TABLE tmp_returned_barcodes (
  barcode VARCHAR(20) NOT NULL
);

-- Insert barcodes (repeat for each barcode, or use LOAD DATA INFILE)
INSERT INTO tmp_returned_barcodes (barcode) VALUES
  ('2403250042567'),
  ('2403250012638');
  -- ... etc

-- Truncate to 13 chars if longer (RFID readers output 16 chars)
UPDATE tmp_returned_barcodes 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 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 rb
LEFT JOIN tag t ON t.barcode = rb.barcode
WHERE t.id IS NULL;

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

-- Match via seq/sequence and type
UPDATE race_number rn
JOIN tag t ON t.seq = rn.sequence AND rn.type_id = :NUMBER_TYPE_ID  -- e.g. 78
JOIN tmp_returned_barcodes rb ON rb.barcode = t.barcode
SET rn.person_id = NULL, rn.last_used = NULL;
-- Confirm: SELECT ROW_COUNT();

-- Find and clear EP assignments for future events that use returned numbers
UPDATE event_participant ep
JOIN event e ON e.id = ep.event_id
JOIN tag t ON t.id = ep.tag_id
JOIN tmp_returned_barcodes rb ON rb.barcode = t.barcode
SET ep.number_id = NULL, ep.tag_id = NULL
WHERE e.start_date_time > NOW();
-- Confirm: SELECT ROW_COUNT();

-- Also check race_number side (in case tag_id was null but number_id was set)
UPDATE event_participant ep
JOIN event e ON e.id = ep.event_id
JOIN race_number rn ON rn.id = ep.number_id
JOIN tag t ON t.seq = rn.sequence AND rn.type_id = :NUMBER_TYPE_ID
JOIN tmp_returned_barcodes rb ON rb.barcode = t.barcode
SET ep.number_id = NULL, ep.tag_id = NULL
WHERE e.start_date_time > NOW();

DROP TEMPORARY TABLE tmp_returned_barcodes;

-- Example staging table structure
CREATE TABLE IF NOT EXISTS tmp_timing_import (
  event_id BIGINT NOT NULL,
  ep_id BIGINT NOT NULL,
  bib VARCHAR(10),
  chip VARCHAR(20),
  school_type VARCHAR(2),
  status VARCHAR(20) DEFAULT 'PENDING'
);

-- Match bib to race_number (by number prefix + bib value)
-- Match chip to tag (by barcode)
-- Flag walk-ins, unresolved entries

-- Update event_participant.number_id and tag_id from resolved timing data
UPDATE event_participant ep
JOIN tmp_timing_import ti ON ti.ep_id = ep.id
JOIN race_number rn ON rn.number = CONCAT(:PREFIX, ti.bib) AND rn.type_id = :NUMBER_TYPE_ID
JOIN tag t ON t.barcode = ti.chip
SET ep.number_id = rn.id, ep.tag_id = t.id
WHERE ti.status = 'MATCHED';

-- For each number assigned to an EP in the completed event,
-- set the race_number's person_id to the EP's person_id
-- and last_used to the event start time
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

-- Find the target event and its number/tag types
-- These are determined by the event configuration
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

-- Find EPs missing either number or tag, where the person has a carry-over candidate
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
FROM event_participant ep
JOIN race_number rn ON rn.person_id = ep.person_id AND rn.type_id = :NUMBER_TYPE_ID
JOIN tag t ON t.seq = rn.sequence AND t.type_id = :TAG_TYPE_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 race_number rn ON rn.sequence = t.seq AND rn.type_id = :NUMBER_TYPE_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 tag t ON t.seq = rn.sequence AND t.type_id = :TAG_TYPE_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 tag t ON t.id = ep.tag_id
JOIN race_number rn ON rn.sequence = t.seq AND rn.type_id = :NUMBER_TYPE_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 race_number rn ON rn.id = ep.number_id
JOIN tag t ON t.seq = rn.sequence AND t.type_id = :TAG_TYPE_ID
SET ep.tag_id = t.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;

-- When a person has multiple numbers of the same type, pick the most recently used
-- This query finds the best number per person
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 tag t ON t.seq = rn.sequence AND t.type_id = :TAG_TYPE_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_assign;
CREATE TABLE tmp_carryover_assign AS
SELECT ep.id AS ep_id, rn.id AS rn_id, 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 tag t ON t.seq = rn.sequence AND t.type_id = :TAG_TYPE_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 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
    AND rn2.last_used IS NOT NULL
  );

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

-- Summary of assignment state
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);