CREATE OR ALTER PROCEDURE dbo.Scene_MoveRelative
    @SceneID int,
    @AnchorSceneID int,
    @Position nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    IF @SceneID = @AnchorSceneID
        RETURN;

    DECLARE @SourceChapterID int = (SELECT ChapterID FROM dbo.Scenes WHERE SceneID = @SceneID);
    DECLARE @TargetChapterID int;
    DECLARE @AnchorSortOrder int;
    DECLARE @NewSortOrder int;

    SELECT @TargetChapterID = ChapterID, @AnchorSortOrder = SortOrder
    FROM dbo.Scenes
    WHERE SceneID = @AnchorSceneID AND IsArchived = 0;

    IF @TargetChapterID IS NULL
        RETURN;

    IF @Position = N'Before'
    BEGIN
        SELECT @NewSortOrder = (ISNULL(MAX(SortOrder), @AnchorSortOrder - 20) + @AnchorSortOrder) / 2
        FROM dbo.Scenes
        WHERE ChapterID = @TargetChapterID
            AND IsArchived = 0
            AND SceneID NOT IN (@SceneID, @AnchorSceneID)
            AND SortOrder < @AnchorSortOrder;

        IF @NewSortOrder >= @AnchorSortOrder
            SET @NewSortOrder = @AnchorSortOrder - 5;
    END
    ELSE
    BEGIN
        SELECT @NewSortOrder = (@AnchorSortOrder + ISNULL(MIN(SortOrder), @AnchorSortOrder + 20)) / 2
        FROM dbo.Scenes
        WHERE ChapterID = @TargetChapterID
            AND IsArchived = 0
            AND SceneID NOT IN (@SceneID, @AnchorSceneID)
            AND SortOrder > @AnchorSortOrder;

        IF @NewSortOrder <= @AnchorSortOrder
            SET @NewSortOrder = @AnchorSortOrder + 5;
    END;

    UPDATE dbo.Scenes
    SET ChapterID = @TargetChapterID,
        SortOrder = @NewSortOrder,
        UpdatedDate = SYSUTCDATETIME()
    WHERE SceneID = @SceneID;

    EXEC dbo.Scene_NormalizeSortOrder @SourceChapterID;
    EXEC dbo.Scene_NormalizeSortOrder @TargetChapterID;
END;
GO