SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbo.ManuscriptDocument_UnlinkBook
    @BookID int,
    @DocumentGuid uniqueidentifier = NULL,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ManuscriptDocumentID int;
    DECLARE @ChaptersUnlinked int = 0;
    DECLARE @ScenesUnlinked int = 0;

    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.BookID = @BookID
        AND md.IsActive = 1
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1
        AND (@DocumentGuid IS NULL OR @DocumentGuid = '00000000-0000-0000-0000-000000000000' OR md.DocumentGuid = @DocumentGuid);

    IF @ManuscriptDocumentID IS NULL
        RETURN;

    UPDATE dbo.Chapters
    SET IsLinkedToManuscript = 0,
        ManuscriptDocumentID = NULL,
        ExternalReference = NULL,
        LinkedUtc = NULL,
        UpdatedDate = SYSUTCDATETIME()
    WHERE BookID = @BookID
        AND ManuscriptDocumentID = @ManuscriptDocumentID;

    SET @ChaptersUnlinked = @@ROWCOUNT;

    UPDATE s
    SET IsLinkedToManuscript = 0,
        ManuscriptDocumentID = NULL,
        ExternalReference = NULL,
        LinkedUtc = NULL,
        UpdatedDate = SYSUTCDATETIME()
    FROM dbo.Scenes s
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    WHERE c.BookID = @BookID
        AND s.ManuscriptDocumentID = @ManuscriptDocumentID;

    SET @ScenesUnlinked = @@ROWCOUNT;

    UPDATE dbo.ManuscriptDocuments
    SET IsActive = 0
    WHERE ManuscriptDocumentID = @ManuscriptDocumentID;

    SELECT @BookID AS BookID,
        @ManuscriptDocumentID AS ManuscriptDocumentID,
        @ChaptersUnlinked AS ChaptersUnlinked,
        @ScenesUnlinked AS ScenesUnlinked,
        N'Manuscript unlinked. Chapters, scenes, and story data were kept.' AS Message;
END;
GO