PlotDirector/PlotLine/Sql/098_Phase16H_WordCompanionCharacterSuggestions.sql

IF OBJECT_ID(N'dbo.SceneCharacterSuggestions', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SceneCharacterSuggestions
    (
        SceneCharacterSuggestionID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneCharacterSuggestions PRIMARY KEY,
        SceneID int NOT NULL,
        CharacterID int NOT NULL,
        SuggestionSource nvarchar(50) NOT NULL CONSTRAINT DF_SceneCharacterSuggestions_Source DEFAULT (N'Word Companion'),
        DetectedUtc datetime2 NOT NULL CONSTRAINT DF_SceneCharacterSuggestions_DetectedUtc DEFAULT SYSUTCDATETIME(),
        Status nvarchar(20) NOT NULL CONSTRAINT DF_SceneCharacterSuggestions_Status DEFAULT (N'Pending'),
        Confidence nvarchar(20) NOT NULL CONSTRAINT DF_SceneCharacterSuggestions_Confidence DEFAULT (N'High'),
        Reason nvarchar(200) NULL,
        CreatedByUserID int NOT NULL,
        ReviewedByUserID int NULL,
        ReviewedUtc datetime2 NULL,
        CONSTRAINT FK_SceneCharacterSuggestions_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
        CONSTRAINT FK_SceneCharacterSuggestions_Characters FOREIGN KEY (CharacterID) REFERENCES dbo.Characters(CharacterID),
        CONSTRAINT FK_SceneCharacterSuggestions_CreatedByUser FOREIGN KEY (CreatedByUserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT FK_SceneCharacterSuggestions_ReviewedByUser FOREIGN KEY (ReviewedByUserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT CK_SceneCharacterSuggestions_Status CHECK (Status IN (N'Pending', N'Accepted', N'Rejected', N'Dismissed')),
        CONSTRAINT CK_SceneCharacterSuggestions_Confidence CHECK (Confidence IN (N'High', N'Medium', N'Low'))
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_SceneCharacterSuggestions_Pending' AND object_id = OBJECT_ID(N'dbo.SceneCharacterSuggestions'))
    CREATE UNIQUE INDEX UX_SceneCharacterSuggestions_Pending ON dbo.SceneCharacterSuggestions(SceneID, CharacterID) WHERE Status = N'Pending';
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneCharacterSuggestions_SceneStatus' AND object_id = OBJECT_ID(N'dbo.SceneCharacterSuggestions'))
    CREATE INDEX IX_SceneCharacterSuggestions_SceneStatus ON dbo.SceneCharacterSuggestions(SceneID, Status, CharacterID);
GO

CREATE OR ALTER PROCEDURE dbo.WordCompanion_Runtime_CharacterAlias_List
    @UserID int,
    @ProjectID int,
    @DocumentGuid uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;

    IF @DocumentGuid IS NULL
        OR @DocumentGuid = '00000000-0000-0000-0000-000000000000'
        OR @ProjectID <= 0
        RETURN;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.ManuscriptDocuments md
        INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = md.ProjectID
        INNER JOIN dbo.Projects p ON p.ProjectID = md.ProjectID
        WHERE md.DocumentGuid = @DocumentGuid
            AND md.ProjectID = @ProjectID
            AND md.IsActive = 1
            AND p.IsArchived = 0
            AND pua.UserID = @UserID
            AND pua.IsActive = 1
    )
        RETURN;

    SELECT c.CharacterID AS CharacterId,
        c.CharacterName AS Name,
        c.CharacterName AS MatchText,
        CAST(1 AS bit) AS IsDisplayName
    FROM dbo.Characters c
    WHERE c.ProjectID = @ProjectID
        AND c.IsArchived = 0
        AND NULLIF(LTRIM(RTRIM(c.CharacterName)), N'') IS NOT NULL

    UNION ALL

    SELECT c.CharacterID AS CharacterId,
        c.CharacterName AS Name,
        ca.Alias AS MatchText,
        CAST(0 AS bit) AS IsDisplayName
    FROM dbo.Characters c
    INNER JOIN dbo.CharacterAliases ca ON ca.CharacterID = c.CharacterID
    WHERE c.ProjectID = @ProjectID
        AND c.IsArchived = 0
        AND NULLIF(LTRIM(RTRIM(ca.Alias)), N'') IS NOT NULL
    ORDER BY Name, CharacterId, IsDisplayName DESC, MatchText;
END;
GO

CREATE OR ALTER PROCEDURE dbo.WordCompanion_Runtime_CharacterSuggestions_Create
    @UserID int,
    @DocumentGuid uniqueidentifier,
    @SceneID int,
    @CharacterIds nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

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

    DECLARE @ProjectID int;
    DECLARE @ManuscriptDocumentID int;

    SELECT @ProjectID = b.ProjectID,
        @ManuscriptDocumentID = md.ManuscriptDocumentID
    FROM dbo.Scenes s
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
    INNER JOIN dbo.ManuscriptDocuments md ON md.BookID = b.BookID
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = b.ProjectID
    WHERE s.SceneID = @SceneID
        AND s.IsArchived = 0
        AND c.IsArchived = 0
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND md.DocumentGuid = @DocumentGuid
        AND md.IsActive = 1
        AND s.IsLinkedToManuscript = 1
        AND s.ManuscriptDocumentID = md.ManuscriptDocumentID
        AND pua.UserID = @UserID
        AND pua.IsActive = 1;

    IF @ProjectID IS NULL
        RETURN;

    DECLARE @Requested table (CharacterID int NOT NULL PRIMARY KEY);

    INSERT @Requested (CharacterID)
    SELECT DISTINCT TRY_CONVERT(int, LTRIM(RTRIM(value)))
    FROM STRING_SPLIT(COALESCE(@CharacterIds, N''), N',')
    WHERE TRY_CONVERT(int, LTRIM(RTRIM(value))) IS NOT NULL
        AND TRY_CONVERT(int, LTRIM(RTRIM(value))) > 0;

    IF NOT EXISTS (SELECT 1 FROM @Requested)
    BEGIN
        SELECT @SceneID AS SceneId,
            0 AS CreatedCount,
            0 AS SkippedCount,
            N'No character suggestions detected.' AS Message;
        RETURN;
    END;

    IF EXISTS
    (
        SELECT 1
        FROM @Requested requested
        WHERE NOT EXISTS
        (
            SELECT 1
            FROM dbo.Characters ch
            WHERE ch.CharacterID = requested.CharacterID
                AND ch.ProjectID = @ProjectID
                AND ch.IsArchived = 0
        )
    )
        RETURN;

    DECLARE @Inserted table (CharacterID int NOT NULL PRIMARY KEY);

    INSERT dbo.SceneCharacterSuggestions
        (SceneID, CharacterID, SuggestionSource, Status, Confidence, Reason, CreatedByUserID)
    OUTPUT inserted.CharacterID INTO @Inserted
    SELECT @SceneID,
        requested.CharacterID,
        N'Word Companion',
        N'Pending',
        N'High',
        N'Detected in manuscript text',
        @UserID
    FROM @Requested requested
    WHERE NOT EXISTS
        (SELECT 1 FROM dbo.SceneCharacters sc WHERE sc.SceneID = @SceneID AND sc.CharacterID = requested.CharacterID)
        AND NOT EXISTS
        (SELECT 1 FROM dbo.SceneCharacterSuggestions suggestion WHERE suggestion.SceneID = @SceneID AND suggestion.CharacterID = requested.CharacterID AND suggestion.Status = N'Pending');

    SELECT @SceneID AS SceneId,
        (SELECT COUNT(*) FROM @Inserted) AS CreatedCount,
        (SELECT COUNT(*) FROM @Requested) - (SELECT COUNT(*) FROM @Inserted) AS SkippedCount,
        CASE
            WHEN (SELECT COUNT(*) FROM @Inserted) = 1 THEN N'1 character suggestion detected.'
            WHEN (SELECT COUNT(*) FROM @Inserted) > 1 THEN CONCAT((SELECT COUNT(*) FROM @Inserted), N' character suggestions detected.')
            ELSE N'No new character suggestions.'
        END AS Message;
END;
GO