SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbo.SceneCharacter_Save
    @SceneCharacterID int = NULL, @SceneID int, @CharacterID int, @RoleInSceneTypeID int = NULL, @PresenceTypeID int = NULL,
    @LocationID int = NULL, @EntryLocationID int = NULL, @ExitLocationID int = NULL,
    @AppearanceNotes nvarchar(max) = NULL, @OutfitDescription nvarchar(max) = NULL, @PhysicalCondition nvarchar(max) = NULL,
    @EmotionalState nvarchar(max) = NULL, @KnowledgeNotes nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF @SceneCharacterID IS NULL OR @SceneCharacterID = 0
    BEGIN
        SELECT @SceneCharacterID = SceneCharacterID
        FROM dbo.SceneCharacters
        WHERE SceneID = @SceneID AND CharacterID = @CharacterID;
    END

    IF @SceneCharacterID IS NULL OR @SceneCharacterID = 0
    BEGIN
        INSERT dbo.SceneCharacters (SceneID, CharacterID, RoleInSceneTypeID, PresenceTypeID, LocationID, EntryLocationID, ExitLocationID,
            AppearanceNotes, OutfitDescription, PhysicalCondition, EmotionalState, KnowledgeNotes)
        VALUES (@SceneID, @CharacterID, @RoleInSceneTypeID, @PresenceTypeID, @LocationID, @EntryLocationID, @ExitLocationID,
            @AppearanceNotes, @OutfitDescription, @PhysicalCondition, @EmotionalState, @KnowledgeNotes);

        SET @SceneCharacterID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.SceneCharacters
        SET SceneID = @SceneID,
            CharacterID = @CharacterID,
            RoleInSceneTypeID = @RoleInSceneTypeID,
            PresenceTypeID = @PresenceTypeID,
            LocationID = @LocationID,
            EntryLocationID = @EntryLocationID,
            ExitLocationID = @ExitLocationID,
            AppearanceNotes = @AppearanceNotes,
            OutfitDescription = @OutfitDescription,
            PhysicalCondition = @PhysicalCondition,
            EmotionalState = @EmotionalState,
            KnowledgeNotes = @KnowledgeNotes,
            UpdatedDate = SYSUTCDATETIME()
        WHERE SceneCharacterID = @SceneCharacterID;
    END

    SELECT @SceneCharacterID;
END;
GO