-- Normalize room layout units: divide pixel-scale coordinates by 40.
-- Only runs on rooms that contain at least one element with a coordinate > 50
-- (i.e. still in pixel scale). Once identified, all elements are converted
-- unconditionally — no per-element heuristic needed.
UPDATE rooms
SET layout_json = (
    SELECT json_group_array(
        json_object(
            'id', json_extract(value, '$.id'),
            'label', json_extract(value, '$.label'),
            'x', ROUND(CAST(json_extract(value, '$.x') AS REAL) / 40.0, 2),
            'y', ROUND(CAST(json_extract(value, '$.y') AS REAL) / 40.0, 2),
            'width', ROUND(CAST(json_extract(value, '$.width') AS REAL) / 40.0, 2),
            'height', ROUND(CAST(json_extract(value, '$.height') AS REAL) / 40.0, 2),
            'type', json_extract(value, '$.type')
        )
    )
    FROM json_each(rooms.layout_json)
)
WHERE EXISTS (
    SELECT 1 FROM json_each(rooms.layout_json)
    WHERE json_extract(value, '$.x') > 50
       OR json_extract(value, '$.y') > 50
       OR json_extract(value, '$.width') > 50
       OR json_extract(value, '$.height') > 50
);
