-- Reinterpret the existing FloorPlanFloors.SortOrder integer as user-facing floor Level.
-- This preserves current relative order for legacy 10-step SortOrder data without adding schema.

IF OBJECT_ID(N'dbo.FloorPlanFloors', N'U') IS NOT NULL
BEGIN
    ;WITH LegacyPlans AS
    (
        SELECT FloorPlanID
        FROM dbo.FloorPlanFloors
        GROUP BY FloorPlanID
        HAVING COUNT(*) > 0
            AND MIN(SortOrder) >= 10
            AND SUM(CASE WHEN SortOrder % 10 = 0 THEN 0 ELSE 1 END) = 0
    ),
    OrderedFloors AS
    (
        SELECT
            fpf.FloorPlanFloorID,
            ROW_NUMBER() OVER (PARTITION BY fpf.FloorPlanID ORDER BY fpf.SortOrder, fpf.Name, fpf.FloorPlanFloorID) - 1 AS FloorLevel
        FROM dbo.FloorPlanFloors fpf
        INNER JOIN LegacyPlans lp ON lp.FloorPlanID = fpf.FloorPlanID
    )
    UPDATE fpf
    SET
        SortOrder = ordered.FloorLevel,
        ModifiedDate = SYSUTCDATETIME()
    FROM dbo.FloorPlanFloors fpf
    INNER JOIN OrderedFloors ordered ON ordered.FloorPlanFloorID = fpf.FloorPlanFloorID
    WHERE fpf.SortOrder <> ordered.FloorLevel;
END;
GO