PlotDirector/PlotLine/Sql/010_Phase1I_Analytics.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbo.Analytics_StoryHealth
    @ProjectID int,
    @BookID int = NULL,
    @StartChapterNumber decimal(10,2) = NULL,
    @EndChapterNumber decimal(10,2) = NULL,
    @StartSceneNumber decimal(10,2) = NULL,
    @EndSceneNumber decimal(10,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH ScopeScenes AS
    (
        SELECT nso.*
        FROM dbo.NarrativeSceneOrder nso
        WHERE nso.ProjectID = @ProjectID
            AND (@BookID IS NULL OR nso.BookID = @BookID)
            AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
            AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
            AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
            AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
    )
    SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.ChapterID, ss.ChapterNumber, ss.ChapterTitle,
        ss.SceneNumber, ss.SceneTitle, ss.GlobalSceneIndex, ss.BookSceneIndex, smt.MetricTypeID,
        smt.MetricName, smv.Value, smt.MinValue, smt.MaxValue,
        CASE WHEN smt.MaxValue = smt.MinValue THEN 0 ELSE CAST(((smv.Value - smt.MinValue) * 100.0) / (smt.MaxValue - smt.MinValue) AS int) END AS [Percent]
    FROM ScopeScenes ss
    INNER JOIN dbo.SceneMetricValues smv ON smv.SceneID = ss.SceneID
    INNER JOIN dbo.SceneMetricTypes smt ON smt.MetricTypeID = smv.MetricTypeID
    WHERE smt.IsActive = 1
        AND smt.MetricName IN (N'Overall Intensity', N'Tension', N'Emotional Weight', N'Action', N'Romance', N'Violence', N'Sexual Charge', N'Mystery', N'Hope / Lightness', N'Comedy', N'Darkness')
    ORDER BY smt.SortOrder, ss.GlobalSceneIndex;

    ;WITH ScopeScenes AS
    (
        SELECT nso.SceneID, nso.ChapterID
        FROM dbo.NarrativeSceneOrder nso
        WHERE nso.ProjectID = @ProjectID
            AND (@BookID IS NULL OR nso.BookID = @BookID)
            AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
            AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
            AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
            AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
    )
    SELECT rs.RevisionStatusID, rs.StatusName, COUNT(s.SceneID) AS SceneCount,
        SUM(CASE WHEN c.ChapterID IS NOT NULL THEN 1 ELSE 0 END) AS ChapterCount
    FROM dbo.RevisionStatuses rs
    LEFT JOIN dbo.Scenes s ON s.RevisionStatusID = rs.RevisionStatusID AND s.SceneID IN (SELECT SceneID FROM ScopeScenes)
    LEFT JOIN dbo.Chapters c ON c.RevisionStatusID = rs.RevisionStatusID AND c.ChapterID IN (SELECT DISTINCT ChapterID FROM ScopeScenes)
    GROUP BY rs.RevisionStatusID, rs.StatusName, rs.SortOrder
    ORDER BY rs.SortOrder;

    ;WITH ScopeScenes AS
    (
        SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle
        FROM dbo.NarrativeSceneOrder nso
        WHERE nso.ProjectID = @ProjectID
            AND (@BookID IS NULL OR nso.BookID = @BookID)
            AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
            AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
            AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
            AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
    )
    SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.ChapterID, ss.ChapterNumber, ss.SceneNumber, ss.SceneTitle,
        rs.StatusName AS RevisionStatusName
    FROM ScopeScenes ss
    INNER JOIN dbo.Scenes s ON s.SceneID = ss.SceneID
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
    WHERE rs.StatusName IN (N'Needs Work', N'Cut Candidate', N'Drafted')
    ORDER BY rs.SortOrder, ss.BookID, ss.ChapterNumber, ss.SceneNumber;

    ;WITH ScopeScenes AS
    (
        SELECT nso.SceneID
        FROM dbo.NarrativeSceneOrder nso
        WHERE nso.ProjectID = @ProjectID
            AND (@BookID IS NULL OR nso.BookID = @BookID)
            AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
            AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
            AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
            AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
    ),
    Total AS (SELECT COUNT(*) AS TotalScenes FROM ScopeScenes)
    SELECT spt.ScenePurposeTypeID, spt.PurposeName, COUNT(sp.SceneID) AS SceneCount,
        CASE WHEN Total.TotalScenes = 0 THEN 0 ELSE CAST(COUNT(sp.SceneID) * 100.0 / Total.TotalScenes AS decimal(5,1)) END AS PercentOfScenes
    FROM dbo.ScenePurposeTypes spt
    CROSS JOIN Total
    LEFT JOIN dbo.ScenePurposes sp ON sp.ScenePurposeTypeID = spt.ScenePurposeTypeID AND sp.SceneID IN (SELECT SceneID FROM ScopeScenes)
    WHERE spt.IsArchived = 0
    GROUP BY spt.ScenePurposeTypeID, spt.PurposeName, spt.SortOrder, Total.TotalScenes
    ORDER BY spt.SortOrder;

    ;WITH ScopeScenes AS
    (
        SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle
        FROM dbo.NarrativeSceneOrder nso
        INNER JOIN dbo.Scenes s ON s.SceneID = nso.SceneID
        WHERE nso.ProjectID = @ProjectID
            AND (@BookID IS NULL OR nso.BookID = @BookID)
            AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
            AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
            AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
            AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
            AND (
                NOT EXISTS (SELECT 1 FROM dbo.ScenePurposes sp WHERE sp.SceneID = nso.SceneID)
                OR ISNULL(NULLIF(LTRIM(RTRIM(s.SceneOutcomeNotes)), N''), N'') = N''
            )
    )
    SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.ChapterID, ss.ChapterNumber, ss.SceneNumber, ss.SceneTitle,
        CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.ScenePurposes sp WHERE sp.SceneID = ss.SceneID) THEN 1 ELSE 0 END AS MissingPurpose,
        CASE WHEN ISNULL(NULLIF(LTRIM(RTRIM(s.SceneOutcomeNotes)), N''), N'') = N'' THEN 1 ELSE 0 END AS MissingOutcome
    FROM ScopeScenes ss
    INNER JOIN dbo.Scenes s ON s.SceneID = ss.SceneID
    ORDER BY ss.BookID, ss.ChapterNumber, ss.SceneNumber;

    ;WITH EventScope AS
    (
        SELECT pt.PlotThreadID, MAX(nso.GlobalSceneIndex) AS LastTouchedIndex, MAX(nso.SceneID) AS LastTouchedSceneID,
            MAX(CONCAT(N'Ch ', nso.ChapterNumber, N' / Scene ', nso.SceneNumber, N': ', nso.SceneTitle)) AS LastTouchedSceneLabel
        FROM dbo.PlotThreads pt
        INNER JOIN dbo.ThreadEvents te ON te.PlotThreadID = pt.PlotThreadID
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = te.SceneID
        WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
        GROUP BY pt.PlotThreadID
    )
    SELECT pt.PlotThreadID, pt.ThreadTitle, pl.PlotLineID, pl.PlotLineName, pt.Importance,
        ts.StatusName AS ThreadStatusName, ts.IsOpenStatus, ts.IsResolvedStatus,
        pt.IntroducedSceneID, pt.PlannedResolutionSceneID, pt.ActualResolutionSceneID,
        EventScope.LastTouchedSceneID, EventScope.LastTouchedSceneLabel,
        CASE WHEN ts.IsOpenStatus = 1 AND pt.Importance >= 8 THEN 1 ELSE 0 END AS IsHighImportanceOpen,
        CASE WHEN ts.IsOpenStatus = 1 AND EventScope.LastTouchedIndex IS NOT NULL
            AND (SELECT MAX(GlobalSceneIndex) FROM dbo.NarrativeSceneOrder WHERE ProjectID = @ProjectID AND (@BookID IS NULL OR BookID = @BookID)) - EventScope.LastTouchedIndex > 8 THEN 1 ELSE 0 END AS IsDormant,
        CASE WHEN EXISTS (
            SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
            WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName = N'Clue Planted'
        ) AND NOT EXISTS (
            SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
            WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName IN (N'Payoff', N'Question Answered', N'Revealed', N'Resolved')
        ) THEN 1 ELSE 0 END AS HasClueWithoutPayoff,
        CASE WHEN EXISTS (
            SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
            WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName = N'Question Raised'
        ) AND NOT EXISTS (
            SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
            WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName = N'Question Answered'
        ) THEN 1 ELSE 0 END AS HasQuestionWithoutAnswer
    FROM dbo.PlotThreads pt
    INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
    INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
    LEFT JOIN EventScope ON EventScope.PlotThreadID = pt.PlotThreadID
    WHERE pl.ProjectID = @ProjectID AND pt.IsArchived = 0 AND pl.IsArchived = 0
        AND (@BookID IS NULL OR pl.BookID IS NULL OR pl.BookID = @BookID)
    ORDER BY ts.IsOpenStatus DESC, pt.Importance DESC, pl.PlotLineName, pt.ThreadTitle;

    ;WITH LastAssetEvent AS
    (
        SELECT ae.StoryAssetID, MAX(nso.GlobalSceneIndex) AS LastEventIndex, MAX(ae.SceneID) AS LastEventSceneID,
            MAX(CONCAT(N'Ch ', nso.ChapterNumber, N' / Scene ', nso.SceneNumber, N': ', nso.SceneTitle)) AS LastEventSceneLabel
        FROM dbo.AssetEvents ae
        INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = ae.SceneID
        WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
        GROUP BY ae.StoryAssetID
    )
    SELECT sa.StoryAssetID, sa.AssetName, ak.KindName, sa.Importance, sa.CurrentStateID,
        ast.StateName AS CurrentStateName, sa.CurrentLocationID, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, LastAssetEvent.LastEventSceneID, LastAssetEvent.LastEventSceneLabel,
        CASE WHEN EXISTS (SELECT 1 FROM dbo.AssetDependencies ad WHERE ad.IsArchived = 0 AND (ad.SourceAssetID = sa.StoryAssetID OR ad.TargetAssetID = sa.StoryAssetID)) THEN 1 ELSE 0 END AS HasDependencies,
        CASE WHEN EXISTS (
            SELECT 1 FROM dbo.ContinuityWarnings cw
            INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
            WHERE cw.ProjectID = @ProjectID AND cw.EntityType IN (N'StoryAsset', N'AssetDependency') AND cw.EntityID IN (sa.StoryAssetID)
                AND wt.TypeName LIKE N'Asset%' AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
        ) THEN 1 ELSE 0 END AS HasDependencyWarnings
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    LEFT JOIN dbo.LocationPaths location ON location.LocationID = sa.CurrentLocationID
    LEFT JOIN LastAssetEvent ON LastAssetEvent.StoryAssetID = sa.StoryAssetID
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0
    ORDER BY sa.IsResolved, sa.Importance DESC, sa.AssetName;

    SELECT ws.SeverityName, COUNT(*) AS WarningCount
    FROM dbo.ContinuityWarnings cw
    INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
    WHERE cw.ProjectID = @ProjectID AND (@BookID IS NULL OR cw.BookID = @BookID)
        AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
    GROUP BY ws.SeverityName, ws.SortOrder
    ORDER BY ws.SortOrder DESC;

    SELECT TOP (12) wt.TypeName AS WarningTypeName, COUNT(*) AS WarningCount
    FROM dbo.ContinuityWarnings cw
    INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
    WHERE cw.ProjectID = @ProjectID AND (@BookID IS NULL OR cw.BookID = @BookID)
        AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
    GROUP BY wt.TypeName
    ORDER BY COUNT(*) DESC, wt.TypeName;

    SELECT TOP (12) cw.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle,
        COUNT(*) AS WarningCount, MAX(ws.SortOrder) AS HighestSeveritySort
    FROM dbo.ContinuityWarnings cw
    INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
    INNER JOIN dbo.Scenes s ON s.SceneID = cw.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE cw.ProjectID = @ProjectID AND cw.SceneID IS NOT NULL AND (@BookID IS NULL OR cw.BookID = @BookID)
        AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
    GROUP BY cw.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle
    ORDER BY COUNT(*) DESC, MAX(ws.SortOrder) DESC;

    ;WITH ScopeScenes AS
    (
        SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle,
            COALESCE(s.POVCharacterID, c.POVCharacterID) AS POVCharacterID
        FROM dbo.NarrativeSceneOrder nso
        INNER JOIN dbo.Scenes s ON s.SceneID = nso.SceneID
        INNER JOIN dbo.Chapters c ON c.ChapterID = nso.ChapterID
        WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
            AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
            AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
            AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
            AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
    )
    SELECT COALESCE(ch.CharacterName, N'No POV') AS POVName, ss.POVCharacterID, COUNT(*) AS SceneCount
    FROM ScopeScenes ss
    LEFT JOIN dbo.Characters ch ON ch.CharacterID = ss.POVCharacterID
    GROUP BY ss.POVCharacterID, ch.CharacterName
    ORDER BY COUNT(*) DESC, POVName;

    ;WITH ScopeScenes AS
    (
        SELECT nso.SceneID, nso.ChapterID
        FROM dbo.NarrativeSceneOrder nso
        WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
    )
    SELECT c.ChapterID, c.ChapterNumber, c.ChapterTitle, COUNT(DISTINCT COALESCE(s.POVCharacterID, c.POVCharacterID)) AS POVCount
    FROM dbo.Chapters c
    INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID
    WHERE c.ChapterID IN (SELECT ChapterID FROM ScopeScenes)
    GROUP BY c.ChapterID, c.ChapterNumber, c.ChapterTitle
    HAVING COUNT(DISTINCT COALESCE(s.POVCharacterID, c.POVCharacterID)) > 1
    ORDER BY c.ChapterNumber;

    SELECT ch.CharacterID, ch.CharacterName, COUNT(DISTINCT sc.SceneID) AS SceneCount
    FROM dbo.Characters ch
    LEFT JOIN dbo.SceneCharacters sc ON sc.CharacterID = ch.CharacterID
    LEFT JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = sc.SceneID
    WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0 AND (@BookID IS NULL OR nso.BookID = @BookID OR sc.SceneID IS NULL)
    GROUP BY ch.CharacterID, ch.CharacterName
    ORDER BY COUNT(DISTINCT sc.SceneID) DESC, ch.CharacterName;

    ;WITH SceneCharacterCounts AS
    (
        SELECT nso.SceneID, nso.SceneNumber, nso.SceneTitle, nso.ChapterNumber, COUNT(sc.SceneCharacterID) AS CharacterCount
        FROM dbo.NarrativeSceneOrder nso
        LEFT JOIN dbo.SceneCharacters sc ON sc.SceneID = nso.SceneID
        WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
        GROUP BY nso.SceneID, nso.SceneNumber, nso.SceneTitle, nso.ChapterNumber
    )
    SELECT TOP (12) * FROM SceneCharacterCounts WHERE CharacterCount >= 5 ORDER BY CharacterCount DESC, ChapterNumber, SceneNumber;

    ;WITH ScopeScenes AS
    (
        SELECT nso.SceneID, s.PrimaryLocationID
        FROM dbo.NarrativeSceneOrder nso
        INNER JOIN dbo.Scenes s ON s.SceneID = nso.SceneID
        WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
    )
    SELECT COALESCE(lp.LocationName, N'No location') AS LocationName, lp.LocationID, COALESCE(lp.LocationPath, N'No location') AS LocationPath,
        COUNT(*) AS SceneCount
    FROM ScopeScenes ss
    LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = ss.PrimaryLocationID
    GROUP BY lp.LocationID, lp.LocationName, lp.LocationPath
    ORDER BY COUNT(*) DESC, LocationName;
END;
GO