-- Count how many records would be affected
SELECT 
    COUNT(*) as total_records_to_jitter,
    COUNT(DISTINCT date_trunc('milliseconds', "lastUpdated")) as unique_timestamp_groups
FROM "Observation" 
WHERE date_trunc('milliseconds', "lastUpdated") IN (
    SELECT date_trunc('milliseconds', "lastUpdated")
    FROM "Observation"
    GROUP BY date_trunc('milliseconds', "lastUpdated")
    HAVING COUNT(*) > 250
);

WITH jittered_updates AS (
    SELECT 
        pr.id,
        pr."lastUpdated" as original_timestamp,
        -- Generate MD5 hash from ID and timestamp, then convert to numeric offset
        -- Use the first 8 characters of MD5 hash as hex, convert to decimal
        -- Then modulo by 1000 to get milliseconds offset (0-999ms)
        (('x' || substr(md5(pr.id || pr."lastUpdated"::text), 1, 8))::bit(32)::int % 1000) as jitter_ms,
        -- Calculate the new jittered timestamp
        pr."lastUpdated" + INTERVAL '1 millisecond' * (('x' || substr(md5(pr.id || pr."lastUpdated"::text), 1, 8))::bit(32)::int % 1000) as jittered_timestamp
    FROM "Observation" pr
    WHERE date_trunc('milliseconds', pr."lastUpdated") IN (
        SELECT date_trunc('milliseconds', "lastUpdated")
        FROM "Observation"
        GROUP BY date_trunc('milliseconds', "lastUpdated")
        HAVING COUNT(*) > 250
    )
    ORDER BY pr.id  -- Consistent ordering for chunking
    LIMIT 5000  -- Process first 5000 records
),

-- Update both the lastUpdated column and the JSON content
updated_records AS (
    UPDATE "Observation" 
    SET 
        "lastUpdated" = ju.jittered_timestamp,
        "content" = CASE 
            WHEN ("content"::json -> 'meta' ->> 'lastUpdated') IS NOT NULL THEN
                -- Replace the existing lastUpdated value in JSON with the jittered timestamp
                replace(
                    "content",
                    '"lastUpdated":"' || ("content"::json -> 'meta' ->> 'lastUpdated') || '"',
                    '"lastUpdated":"' || to_char(ju.jittered_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') || '"'
                )
            ELSE
                "content"
            END
    FROM jittered_updates ju
    WHERE "Observation".id = ju.id
    RETURNING "Observation".id, ju.original_timestamp, ju.jitter_ms, "Observation"."lastUpdated" as new_timestamp
)
SELECT 
    COUNT(*) as records_updated_chunk_1,
    MIN(jitter_ms) as min_jitter_ms,
    MAX(jitter_ms) as max_jitter_ms,
    AVG(jitter_ms) as avg_jitter_ms
FROM updated_records;