PlotDirector/PlotLine/Sql/023_TimelinePerformanceIndexes.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

/*
    Timeline performance pass for imported/large projects.

    Focus:
    - Keep indexes narrow.
    - Support the actual timeline/project load path.
    - Avoid duplicating existing core indexes on Books, Chapters, Scenes,
      PlotLines, StoryAssets, SceneCharacters(SceneID), AssetEvents(SceneID),
      and ContinuityWarnings(ProjectID, BookID, SceneID...).
*/

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ThreadEvents_PlotThreadID_SceneID' AND object_id = OBJECT_ID(N'dbo.ThreadEvents'))
BEGIN
    -- Supports plot-thread filters and thread-event reads starting from a thread.
    CREATE INDEX IX_ThreadEvents_PlotThreadID_SceneID
    ON dbo.ThreadEvents(PlotThreadID, SceneID, EventTypeID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AssetEvents_StoryAssetID_SceneID' AND object_id = OBJECT_ID(N'dbo.AssetEvents'))
BEGIN
    -- Supports asset focus/filter lanes and asset-event reads starting from an asset.
    CREATE INDEX IX_AssetEvents_StoryAssetID_SceneID
    ON dbo.AssetEvents(StoryAssetID, SceneID, AssetEventTypeID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneCharacters_CharacterID_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneCharacters'))
BEGIN
    -- Supports character focus/filter lanes and character detail appearance lookups.
    CREATE INDEX IX_SceneCharacters_CharacterID_SceneID
    ON dbo.SceneCharacters(CharacterID, SceneID, RoleInSceneTypeID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Characters_ProjectID_Importance_Name' AND object_id = OBJECT_ID(N'dbo.Characters'))
BEGIN
    -- Supports CharacterTimeline_GetByProject ordering by importance, then name.
    CREATE INDEX IX_Characters_ProjectID_Importance_Name
    ON dbo.Characters(ProjectID, IsArchived, CharacterImportance DESC, CharacterName);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Locations_Project_Parent_Name' AND object_id = OBJECT_ID(N'dbo.Locations'))
BEGIN
    -- Supports project location lists and LocationPaths roots by project.
    CREATE INDEX IX_Locations_Project_Parent_Name
    ON dbo.Locations(ProjectID, IsArchived, ParentLocationID, LocationName);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Locations_ParentLocationID' AND object_id = OBJECT_ID(N'dbo.Locations'))
BEGIN
    -- Supports recursive LocationPaths expansion from parent to child locations.
    CREATE INDEX IX_Locations_ParentLocationID
    ON dbo.Locations(ParentLocationID, IsArchived, LocationName);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneCharacters_LocationID_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneCharacters'))
BEGIN
    -- Supports timeline location filters that include character appearances at a location.
    CREATE INDEX IX_SceneCharacters_LocationID_SceneID
    ON dbo.SceneCharacters(LocationID, SceneID, CharacterID)
    WHERE LocationID IS NOT NULL;
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneAssetLocations_LocationID_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneAssetLocations'))
BEGIN
    -- Supports timeline location filters that include assets placed at a location.
    CREATE INDEX IX_SceneAssetLocations_LocationID_SceneID
    ON dbo.SceneAssetLocations(LocationID, SceneID, StoryAssetID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneAssetLocations_SceneID_LocationID' AND object_id = OBJECT_ID(N'dbo.SceneAssetLocations'))
BEGIN
    -- Supports scene-level location/asset reads from the scene inspector and timeline focus.
    CREATE INDEX IX_SceneAssetLocations_SceneID_LocationID
    ON dbo.SceneAssetLocations(SceneID, LocationID, StoryAssetID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ContinuityWarnings_ActiveSceneCounts' AND object_id = OBJECT_ID(N'dbo.ContinuityWarnings'))
BEGIN
    -- Supports active warning counts shown on timeline scene cards.
    CREATE INDEX IX_ContinuityWarnings_ActiveSceneCounts
    ON dbo.ContinuityWarnings(ProjectID, BookID, SceneID)
    WHERE SceneID IS NOT NULL AND IsDismissed = 0 AND IsIntentional = 0;
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneDependencies_Project_SourceScene' AND object_id = OBJECT_ID(N'dbo.SceneDependencies'))
BEGIN
    -- Supports dependency counts and source-scene dependency lookups.
    CREATE INDEX IX_SceneDependencies_Project_SourceScene
    ON dbo.SceneDependencies(ProjectID, IsArchived, SourceSceneID, TargetSceneID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneDependencies_Project_TargetScene' AND object_id = OBJECT_ID(N'dbo.SceneDependencies'))
BEGIN
    -- Supports dependency counts and target-scene dependency lookups.
    CREATE INDEX IX_SceneDependencies_Project_TargetScene
    ON dbo.SceneDependencies(ProjectID, IsArchived, TargetSceneID, SourceSceneID);
END;
GO

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

    ;WITH DependencyScenes AS
    (
        SELECT sd.SceneDependencyID, sd.SourceSceneID AS SceneID
        FROM dbo.SceneDependencies sd
        WHERE sd.ProjectID = @ProjectID AND sd.IsArchived = 0

        UNION ALL

        SELECT sd.SceneDependencyID, sd.TargetSceneID AS SceneID
        FROM dbo.SceneDependencies sd
        WHERE sd.ProjectID = @ProjectID
            AND sd.IsArchived = 0
            AND sd.TargetSceneID <> sd.SourceSceneID
    )
    SELECT scene.SceneID, COUNT(*) AS DependencyCount
    FROM dbo.NarrativeSceneOrder scene
    INNER JOIN DependencyScenes dependencyScene ON dependencyScene.SceneID = scene.SceneID
    WHERE scene.ProjectID = @ProjectID AND (@BookID IS NULL OR scene.BookID = @BookID)
    GROUP BY scene.SceneID;
END;
GO