WITH RECURSIVE
-- 1. Grab all UniqueIDs from the table and split them into individual hex character positions
ParsedIDs AS (
    SELECT
        PersonID,
        UniqueID,
        SUBSTR(UniqueID, 1, 32) AS CoreUUID,
        SUBSTR(UniqueID, 33, 4) AS StoredChecksum
    FROM PersonTable
    WHERE LENGTH(UniqueID) = 36
),
-- 2. Recursively iterate through the 16 bytes of the core UUID
ByteWalker(PersonID, UniqueID, CoreUUID, StoredChecksum, Step, Sum1, Sum2, FinalHex) AS (
    -- Base case: start at byte 1
    SELECT
        PersonID,
        UniqueID,
        CoreUUID,
        StoredChecksum,
        1 AS Step,
        -- Convert the first 2 hex characters of the UUID to an integer (nibble by nibble;
        -- CAST('0x'||... AS INTEGER) does NOT parse hex in SQLite, it silently returns 0)
        (INSTR('0123456789ABCDEF', UPPER(SUBSTR(CoreUUID, 1, 1))) - 1) * 16
            + (INSTR('0123456789ABCDEF', UPPER(SUBSTR(CoreUUID, 2, 1))) - 1) AS Sum1,
        (INSTR('0123456789ABCDEF', UPPER(SUBSTR(CoreUUID, 1, 1))) - 1) * 16
            + (INSTR('0123456789ABCDEF', UPPER(SUBSTR(CoreUUID, 2, 1))) - 1) AS Sum2,
        StoredChecksum
    FROM ParsedIDs

    UNION ALL

    -- Recursive step: process the next byte (advancing by 2 hex chars per step)
    SELECT
        w.PersonID,
        w.UniqueID,
        w.CoreUUID,
        w.StoredChecksum,
        w.Step + 1,
        (w.Sum1 + (INSTR('0123456789ABCDEF', UPPER(SUBSTR(w.CoreUUID, (w.Step * 2) + 1, 1))) - 1) * 16
                + (INSTR('0123456789ABCDEF', UPPER(SUBSTR(w.CoreUUID, (w.Step * 2) + 2, 1))) - 1)
        ) % 256 AS Sum1,
        (w.Sum2 + (w.Sum1 + (INSTR('0123456789ABCDEF', UPPER(SUBSTR(w.CoreUUID, (w.Step * 2) + 1, 1))) - 1) * 16
                + (INSTR('0123456789ABCDEF', UPPER(SUBSTR(w.CoreUUID, (w.Step * 2) + 2, 1))) - 1)
        ) % 256) % 256 AS Sum2,
        w.StoredChecksum
    FROM ByteWalker w
    WHERE w.Step < 16
),
-- 3. Grab the final calculated state (Step 16) and format it back to a 4-character hex string
CalculatedChecksums AS (
    SELECT
        PersonID,
        UniqueID,
        StoredChecksum,
        PRINTF('%02X%02X', Sum1, Sum2) AS CalcChecksum
    FROM ByteWalker
    WHERE Step = 16
)
-- 4. Compare stored vs calculated
SELECT
    PersonID,
    UniqueID,
    StoredChecksum,
    CalcChecksum,
    CASE
        WHEN StoredChecksum = CalcChecksum THEN 'VALID'
        ELSE 'INVALID'
    END AS ChecksumStatus
FROM CalculatedChecksums;