PlotDirector/PlotLine/Sql/018_Phase1R_Dynamics.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

MERGE dbo.WarningTypes AS target
USING (VALUES
    (N'Knowledge Continuity', N'Character may act on information before a recorded knowledge event.', 220),
    (N'Abrupt Relationship Change', N'Relationship intensity changes sharply between nearby scenes.', 230),
    (N'Long Character Absence', N'Character disappears for a long stretch of narrative scenes.', 240),
    (N'Character Underused', N'Character appears lightly compared with their story connections.', 250),
    (N'Scene Character Overload', N'Scene contains a large number of character appearances.', 260)
) AS source (TypeName, Description, SortOrder)
ON target.TypeName = source.TypeName
WHEN MATCHED THEN UPDATE SET Description = source.Description, SortOrder = source.SortOrder, IsActive = 1
WHEN NOT MATCHED THEN INSERT (TypeName, Description, SortOrder, IsActive) VALUES (source.TypeName, source.Description, source.SortOrder, 1);
GO

CREATE OR ALTER PROCEDURE dbo.Dynamics_CharacterArc
    @CharacterID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int = (SELECT ProjectID FROM dbo.Characters WHERE CharacterID = @CharacterID);

    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM dbo.Characters
    WHERE CharacterID = @CharacterID;

    SELECT sc.SceneID, nso.BookTitle, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle, nso.GlobalSceneIndex,
        CAST(CASE WHEN COALESCE(s.POVCharacterID, c.POVCharacterID) = @CharacterID THEN 1 ELSE 0 END AS bit) AS IsPov,
        MAX(CASE WHEN smt.MetricName = N'Emotional Weight' THEN smv.Value END) AS EmotionalWeight,
        MAX(CASE WHEN smt.MetricName = N'Tension' THEN smv.Value END) AS Tension,
        MAX(CASE WHEN smt.MetricName = N'Romance' THEN smv.Value END) AS Romance,
        MAX(CASE WHEN smt.MetricName = N'Action' THEN smv.Value END) AS Action,
        MAX(CASE WHEN smt.MetricName = N'Darkness' THEN smv.Value END) AS Darkness,
        MAX(CASE WHEN smt.MetricName = N'Hope / Lightness' THEN smv.Value END) AS HopeLightness,
        MAX(CASE WHEN smt.MetricName = N'Overall Intensity' THEN smv.Value END) AS OverallIntensity,
        role.TypeName AS RoleInSceneTypeName,
        presence.TypeName AS PresenceTypeName,
        sc.EmotionalState,
        sc.AppearanceNotes,
        notes.KeyNotes
    FROM dbo.SceneCharacters sc
    INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = sc.SceneID
    INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
    LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
    LEFT JOIN dbo.SceneMetricValues smv ON smv.SceneID = sc.SceneID
    LEFT JOIN dbo.SceneMetricTypes smt ON smt.MetricTypeID = smv.MetricTypeID
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), COALESCE(sn.NoteTitle, sn.NoteText)), N' | ') AS KeyNotes
        FROM dbo.SceneNotes sn
        INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
        WHERE sn.SceneID = sc.SceneID AND sn.IsResolved = 0
            AND snt.TypeName IN (N'Emotional Intent', N'Character Reminder', N'Revision Note', N'TODO')
    ) notes
    WHERE sc.CharacterID = @CharacterID
    GROUP BY sc.SceneID, nso.BookTitle, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle, nso.GlobalSceneIndex,
        s.POVCharacterID, c.POVCharacterID, role.TypeName, presence.TypeName, sc.EmotionalState, sc.AppearanceNotes, notes.KeyNotes
    ORDER BY nso.GlobalSceneIndex;

    EXEC dbo.RelationshipEvent_ListByCharacter @CharacterID;
    EXEC dbo.CharacterKnowledge_ListByCharacter @CharacterID;

    SELECT N'Character Presence' AS Category, ws.SeverityName, cw.Message AS Title, cw.Details AS Detail, cw.SceneID, cw.EntityID, cw.EntityType
    FROM dbo.ContinuityWarnings cw
    INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
    INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
    WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
        AND (
            (cw.EntityType = N'Character' AND cw.EntityID = @CharacterID)
            OR cw.SceneID IN (SELECT SceneID FROM dbo.SceneCharacters WHERE CharacterID = @CharacterID)
        )
        AND wt.TypeName IN (N'Knowledge Continuity', N'Long Character Absence', N'Character Underused', N'Scene Character Overload');
END;
GO

CREATE OR ALTER PROCEDURE dbo.Dynamics_RelationshipEvolution
    @CharacterRelationshipID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
        cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
        cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes, cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
    FROM dbo.CharacterRelationships cr
    INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
    INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
    INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
    WHERE cr.CharacterRelationshipID = @CharacterRelationshipID;

    SELECT re.RelationshipEventID, re.CharacterRelationshipID, re.SceneID, nso.BookTitle, nso.ChapterNumber, nso.SceneNumber,
        nso.SceneTitle, nso.GlobalSceneIndex, rs.StateName AS RelationshipStateName, re.Intensity, re.IsKnownToOtherCharacter, re.Description
    FROM dbo.RelationshipEvents re
    INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
    INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = re.SceneID
    WHERE re.CharacterRelationshipID = @CharacterRelationshipID
    ORDER BY nso.GlobalSceneIndex, re.RelationshipEventID;

    SELECT N'Relationship Continuity' AS Category, ws.SeverityName, cw.Message AS Title, cw.Details AS Detail, cw.SceneID, cw.EntityID, cw.EntityType
    FROM dbo.ContinuityWarnings cw
    INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
    INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
    WHERE cw.IsDismissed = 0 AND cw.EntityType = N'Relationship' AND cw.EntityID = @CharacterRelationshipID
        AND wt.TypeName = N'Abrupt Relationship Change';
END;
GO

CREATE OR ALTER PROCEDURE dbo.Dynamics_KnowledgeTimeline
    @CharacterID int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ProjectID int = (SELECT ProjectID FROM dbo.Characters WHERE CharacterID = @CharacterID);

    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM dbo.Characters
    WHERE CharacterID = @CharacterID;

    EXEC dbo.CharacterKnowledge_ListByCharacter @CharacterID;

    SELECT N'Knowledge Continuity' AS Category, ws.SeverityName, cw.Message AS Title, cw.Details AS Detail, cw.SceneID, cw.EntityID, cw.EntityType
    FROM dbo.ContinuityWarnings cw
    INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
    INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
    WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
        AND cw.EntityType = N'Character' AND cw.EntityID = @CharacterID
        AND wt.TypeName = N'Knowledge Continuity';
END;
GO

CREATE OR ALTER PROCEDURE dbo.Dynamics_StoryDashboard
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH Pairings AS
    (
        SELECT a.CharacterID AS CharacterAID, ca.CharacterName AS CharacterAName,
            b.CharacterID AS CharacterBID, cb.CharacterName AS CharacterBName,
            a.SceneID, nso.GlobalSceneIndex,
            CONCAT(nso.BookTitle, N' / Ch ', nso.ChapterNumber, N' / Scene ', nso.SceneNumber, N': ', nso.SceneTitle) AS SceneLabel
        FROM dbo.SceneCharacters a
        INNER JOIN dbo.SceneCharacters b ON b.SceneID = a.SceneID AND b.CharacterID > a.CharacterID
        INNER JOIN dbo.Characters ca ON ca.CharacterID = a.CharacterID
        INNER JOIN dbo.Characters cb ON cb.CharacterID = b.CharacterID
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = a.SceneID
        WHERE ca.ProjectID = @ProjectID AND cb.ProjectID = @ProjectID AND ca.IsArchived = 0 AND cb.IsArchived = 0
    ),
    Ranked AS
    (
        SELECT CharacterAID, CharacterAName, CharacterBID, CharacterBName, COUNT(*) AS SharedSceneCount,
            MIN(GlobalSceneIndex) AS FirstIndex, MAX(GlobalSceneIndex) AS LastIndex
        FROM Pairings
        GROUP BY CharacterAID, CharacterAName, CharacterBID, CharacterBName
    )
    SELECT TOP (40) r.CharacterAID, r.CharacterAName, r.CharacterBID, r.CharacterBName, r.SharedSceneCount,
        firstScene.SceneLabel AS FirstSceneLabel,
        lastScene.SceneLabel AS LastSceneLabel
    FROM Ranked r
    OUTER APPLY (SELECT TOP (1) SceneLabel FROM Pairings p WHERE p.CharacterAID = r.CharacterAID AND p.CharacterBID = r.CharacterBID ORDER BY p.GlobalSceneIndex) firstScene
    OUTER APPLY (SELECT TOP (1) SceneLabel FROM Pairings p WHERE p.CharacterAID = r.CharacterAID AND p.CharacterBID = r.CharacterBID ORDER BY p.GlobalSceneIndex DESC) lastScene
    ORDER BY r.SharedSceneCount DESC, r.CharacterAName, r.CharacterBName;

    SELECT Category, SeverityName, Title, Detail, SceneID, EntityID, EntityType
    FROM
    (
        SELECT N'Continuity' AS Category, ws.SeverityName, cw.Message AS Title, cw.Details AS Detail, cw.SceneID, cw.EntityID, cw.EntityType
        FROM dbo.ContinuityWarnings cw
        INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
        INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
        WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
            AND wt.TypeName IN (N'Knowledge Continuity', N'Abrupt Relationship Change', N'Long Character Absence', N'Character Underused', N'Scene Character Overload')
    ) issues
    ORDER BY SeverityName DESC, Title;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Dynamics_RunContinuityChecks
    @ProjectID int,
    @BookID int = NULL,
    @SceneID int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH RelationshipSteps AS
    (
        SELECT cr.CharacterRelationshipID, cr.ProjectID, nso.BookID, nso.ChapterID, re.SceneID,
            ca.CharacterName AS CharacterAName, cb.CharacterName AS CharacterBName, re.Intensity,
            LAG(re.Intensity) OVER (PARTITION BY cr.CharacterRelationshipID ORDER BY nso.GlobalSceneIndex, re.RelationshipEventID) AS PreviousIntensity,
            LAG(nso.SceneTitle) OVER (PARTITION BY cr.CharacterRelationshipID ORDER BY nso.GlobalSceneIndex, re.RelationshipEventID) AS PreviousSceneTitle
        FROM dbo.RelationshipEvents re
        INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID AND cr.IsArchived = 0
        INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
        INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = re.SceneID
        WHERE cr.ProjectID = @ProjectID
    )
    INSERT dbo.ContinuityWarnings (ProjectID, BookID, ChapterID, SceneID, EntityType, EntityID, WarningTypeID, WarningSeverityID, Message, Details)
    SELECT rs.ProjectID, rs.BookID, rs.ChapterID, rs.SceneID, N'Relationship', rs.CharacterRelationshipID,
        wt.WarningTypeID, ws.WarningSeverityID,
        N'Possible emotional transition issue. Relationship intensity changes sharply between scenes.',
        CONCAT(rs.CharacterAName, N' / ', rs.CharacterBName, N' changes from ', rs.PreviousIntensity, N' to ', rs.Intensity, N'. Previous scene: ', rs.PreviousSceneTitle, N'.')
    FROM RelationshipSteps rs
    CROSS JOIN dbo.WarningTypes wt
    CROSS JOIN dbo.WarningSeverities ws
    WHERE wt.TypeName = N'Abrupt Relationship Change' AND ws.SeverityName = N'Info'
        AND rs.Intensity IS NOT NULL AND rs.PreviousIntensity IS NOT NULL AND ABS(rs.Intensity - rs.PreviousIntensity) >= 5
        AND (@BookID IS NULL OR rs.BookID = @BookID) AND (@SceneID IS NULL OR rs.SceneID = @SceneID)
        AND NOT EXISTS (SELECT 1 FROM dbo.ContinuityWarnings existing WHERE existing.ProjectID = rs.ProjectID AND existing.SceneID = rs.SceneID AND existing.EntityType = N'Relationship' AND existing.EntityID = rs.CharacterRelationshipID AND existing.WarningTypeID = wt.WarningTypeID AND existing.IsDismissed = 0 AND existing.IsIntentional = 0);

    ;WITH Appearances AS
    (
        SELECT sc.CharacterID, ch.CharacterName, nso.ProjectID, nso.BookID, nso.ChapterID, sc.SceneID, nso.GlobalSceneIndex,
            LAG(nso.GlobalSceneIndex) OVER (PARTITION BY sc.CharacterID ORDER BY nso.GlobalSceneIndex) AS PreviousIndex
        FROM dbo.SceneCharacters sc
        INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = sc.SceneID
        WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0
    )
    INSERT dbo.ContinuityWarnings (ProjectID, BookID, ChapterID, SceneID, EntityType, EntityID, WarningTypeID, WarningSeverityID, Message, Details)
    SELECT ProjectID, BookID, ChapterID, SceneID, N'Character', CharacterID, wt.WarningTypeID, ws.WarningSeverityID,
        CONCAT(N'Possible review: ', CharacterName, N' is absent for ', GlobalSceneIndex - PreviousIndex - 1, N' narrative scenes.'),
        N'Long absences are sometimes useful, but this may be worth checking after a major beat.'
    FROM Appearances
    CROSS JOIN dbo.WarningTypes wt
    CROSS JOIN dbo.WarningSeverities ws
    WHERE wt.TypeName = N'Long Character Absence' AND ws.SeverityName = N'Info'
        AND PreviousIndex IS NOT NULL AND GlobalSceneIndex - PreviousIndex - 1 >= 12
        AND (@BookID IS NULL OR BookID = @BookID) AND (@SceneID IS NULL OR SceneID = @SceneID)
        AND NOT EXISTS (SELECT 1 FROM dbo.ContinuityWarnings existing WHERE existing.ProjectID = Appearances.ProjectID AND existing.SceneID = Appearances.SceneID AND existing.EntityType = N'Character' AND existing.EntityID = Appearances.CharacterID AND existing.WarningTypeID = wt.WarningTypeID AND existing.IsDismissed = 0 AND existing.IsIntentional = 0);

    ;WITH CharacterUsage AS
    (
        SELECT ch.ProjectID, ch.CharacterID, ch.CharacterName,
            COUNT(DISTINCT sc.SceneID) AS AppearanceCount,
            COUNT(DISTINCT cr.CharacterRelationshipID) AS RelationshipCount
        FROM dbo.Characters ch
        LEFT JOIN dbo.SceneCharacters sc ON sc.CharacterID = ch.CharacterID
        LEFT JOIN dbo.CharacterRelationships cr ON cr.ProjectID = ch.ProjectID
            AND cr.IsArchived = 0
            AND ch.CharacterID IN (cr.CharacterAID, cr.CharacterBID)
        WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0 AND @SceneID IS NULL
        GROUP BY ch.ProjectID, ch.CharacterID, ch.CharacterName
        HAVING COUNT(DISTINCT cr.CharacterRelationshipID) >= 2 AND COUNT(DISTINCT sc.SceneID) <= 1
    )
    INSERT dbo.ContinuityWarnings (ProjectID, EntityType, EntityID, WarningTypeID, WarningSeverityID, Message, Details)
    SELECT ProjectID, N'Character', CharacterID, wt.WarningTypeID, ws.WarningSeverityID,
        CONCAT(N'Possible review: ', CharacterName, N' has several relationship links but very few scene appearances.'),
        N'This may be intentional, but the character could be underused relative to their story connections.'
    FROM CharacterUsage
    CROSS JOIN dbo.WarningTypes wt
    CROSS JOIN dbo.WarningSeverities ws
    WHERE wt.TypeName = N'Character Underused' AND ws.SeverityName = N'Info'
        AND NOT EXISTS (SELECT 1 FROM dbo.ContinuityWarnings existing WHERE existing.ProjectID = CharacterUsage.ProjectID AND existing.EntityType = N'Character' AND existing.EntityID = CharacterUsage.CharacterID AND existing.WarningTypeID = wt.WarningTypeID AND existing.IsDismissed = 0 AND existing.IsIntentional = 0);

    ;WITH SceneCounts AS
    (
        SELECT nso.ProjectID, nso.BookID, nso.ChapterID, nso.SceneID, nso.SceneTitle, COUNT(*) AS CharacterCount
        FROM dbo.SceneCharacters sc
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = sc.SceneID
        WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID) AND (@SceneID IS NULL OR nso.SceneID = @SceneID)
        GROUP BY nso.ProjectID, nso.BookID, nso.ChapterID, nso.SceneID, nso.SceneTitle
        HAVING COUNT(*) >= 7
    )
    INSERT dbo.ContinuityWarnings (ProjectID, BookID, ChapterID, SceneID, EntityType, EntityID, WarningTypeID, WarningSeverityID, Message, Details)
    SELECT ProjectID, BookID, ChapterID, SceneID, N'Scene', SceneID, wt.WarningTypeID, ws.WarningSeverityID,
        CONCAT(N'Possible review: this scene has ', CharacterCount, N' character appearances.'),
        N'Busy scenes can work well, but may need extra clarity around focus and blocking.'
    FROM SceneCounts
    CROSS JOIN dbo.WarningTypes wt
    CROSS JOIN dbo.WarningSeverities ws
    WHERE wt.TypeName = N'Scene Character Overload' AND ws.SeverityName = N'Info'
        AND NOT EXISTS (SELECT 1 FROM dbo.ContinuityWarnings existing WHERE existing.ProjectID = SceneCounts.ProjectID AND existing.SceneID = SceneCounts.SceneID AND existing.EntityType = N'Scene' AND existing.EntityID = SceneCounts.SceneID AND existing.WarningTypeID = wt.WarningTypeID AND existing.IsDismissed = 0 AND existing.IsIntentional = 0);

    ;WITH Knowledge AS
    (
        SELECT ck.CharacterID, ch.CharacterName, ck.StoryAssetID, ck.PlotThreadID, ck.SceneID, nso.ProjectID, nso.BookID, nso.ChapterID, nso.GlobalSceneIndex,
            MIN(nso.GlobalSceneIndex) OVER (PARTITION BY ck.CharacterID, ISNULL(ck.StoryAssetID, 0), ISNULL(ck.PlotThreadID, 0)) AS FirstKnownIndex
        FROM dbo.CharacterKnowledge ck
        INNER JOIN dbo.Characters ch ON ch.CharacterID = ck.CharacterID
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = ck.SceneID
        WHERE ch.ProjectID = @ProjectID
    ),
    Candidate AS
    (
        SELECT DISTINCT sc.CharacterID, ch.CharacterName, te.PlotThreadID, ae.StoryAssetID, nso.ProjectID, nso.BookID, nso.ChapterID, sc.SceneID, nso.GlobalSceneIndex
        FROM dbo.SceneCharacters sc
        INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = sc.SceneID
        LEFT JOIN dbo.ThreadEvents te ON te.SceneID = sc.SceneID
        LEFT JOIN dbo.AssetEvents ae ON ae.SceneID = sc.SceneID
        WHERE ch.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID) AND (@SceneID IS NULL OR sc.SceneID = @SceneID)
            AND (te.PlotThreadID IS NOT NULL OR ae.StoryAssetID IS NOT NULL)
    )
    INSERT dbo.ContinuityWarnings (ProjectID, BookID, ChapterID, SceneID, EntityType, EntityID, WarningTypeID, WarningSeverityID, Message, Details)
    SELECT c.ProjectID, c.BookID, c.ChapterID, c.SceneID, N'Character', c.CharacterID, wt.WarningTypeID, ws.WarningSeverityID,
        CONCAT(N'Possible review: ', c.CharacterName, N' appears in a scene involving information without an earlier knowledge event.'),
        N'This is advisory only. Add a knowledge event if the character is meant to know this, or mark the warning intentional.'
    FROM Candidate c
    CROSS JOIN dbo.WarningTypes wt
    CROSS JOIN dbo.WarningSeverities ws
    WHERE wt.TypeName = N'Knowledge Continuity' AND ws.SeverityName = N'Info'
        AND NOT EXISTS
        (
            SELECT 1 FROM Knowledge k
            WHERE k.CharacterID = c.CharacterID
                AND ((c.StoryAssetID IS NOT NULL AND k.StoryAssetID = c.StoryAssetID) OR (c.PlotThreadID IS NOT NULL AND k.PlotThreadID = c.PlotThreadID))
                AND k.FirstKnownIndex <= c.GlobalSceneIndex
        )
        AND NOT EXISTS (SELECT 1 FROM dbo.ContinuityWarnings existing WHERE existing.ProjectID = c.ProjectID AND existing.SceneID = c.SceneID AND existing.EntityType = N'Character' AND existing.EntityID = c.CharacterID AND existing.WarningTypeID = wt.WarningTypeID AND existing.IsDismissed = 0 AND existing.IsIntentional = 0);
END;
GO