CREATE OR ALTER PROCEDURE dbo.WordCompanion_Runtime_SceneWordCount_Update
    @UserID int,
    @DocumentGuid uniqueidentifier,
    @BookID int,
    @ChapterID int,
    @SceneID int,
    @WordCount int
AS
BEGIN
    SET NOCOUNT ON;

    IF @DocumentGuid IS NULL
        OR @DocumentGuid = '00000000-0000-0000-0000-000000000000'
        OR @BookID <= 0
        OR @ChapterID <= 0
        OR @SceneID <= 0
        OR @WordCount < 0
        RETURN;

    DECLARE @ManuscriptDocumentID int;

    SELECT @ManuscriptDocumentID = md.ManuscriptDocumentID
    FROM dbo.ManuscriptDocuments md
    INNER JOIN dbo.Books b ON b.BookID = md.BookID
    INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    WHERE md.DocumentGuid = @DocumentGuid
        AND md.BookID = @BookID
        AND md.IsActive = 1
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1;

    IF @ManuscriptDocumentID IS NULL
        RETURN;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.Scenes s
        INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
        WHERE s.SceneID = @SceneID
            AND s.ChapterID = @ChapterID
            AND c.BookID = @BookID
            AND s.IsArchived = 0
            AND c.IsArchived = 0
            AND s.IsLinkedToManuscript = 1
            AND s.ManuscriptDocumentID = @ManuscriptDocumentID
    )
        RETURN;

    DECLARE @LastWorkedOnDate date = CONVERT(date, SYSUTCDATETIME());

    IF EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
    BEGIN
        UPDATE dbo.SceneWorkflow
        SET ActualWordCount = @WordCount,
            LastWorkedOn = @LastWorkedOnDate,
            UpdatedDate = SYSUTCDATETIME()
        WHERE SceneID = @SceneID;
    END
    ELSE
    BEGIN
        INSERT dbo.SceneWorkflow (SceneID, ActualWordCount, LastWorkedOn)
        VALUES (@SceneID, @WordCount, @LastWorkedOnDate);
    END;

    UPDATE dbo.ManuscriptDocuments
    SET LastSyncUtc = SYSUTCDATETIME()
    WHERE ManuscriptDocumentID = @ManuscriptDocumentID;

    SELECT @SceneID AS SceneId,
        @WordCount AS ActualWords,
        @LastWorkedOnDate AS LastWorkedOn,
        N'Word count synced.' AS Message;
END;
GO