SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

/*
    Timeline-focused performance pass.

    Scope:
    - Keep the initial Timeline page from loading full character/asset lane data
      just to render scene-card summaries.
    - Keep project/book filters as early as possible inside heavy Timeline queries.
    - Add narrow supporting indexes for the Timeline warning, character, asset,
      and scene-overview paths.
*/

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ContinuityWarnings_TimelineActiveList' AND object_id = OBJECT_ID(N'dbo.ContinuityWarnings'))
BEGIN
    CREATE INDEX IX_ContinuityWarnings_TimelineActiveList
    ON dbo.ContinuityWarnings(ProjectID, BookID, WarningSeverityID, WarningTypeID, SceneID, LastDetectedDate DESC, ContinuityWarningID DESC)
    INCLUDE (ValidationRunID, ChapterID, EntityType, EntityID, Message, Details, CreatedDate)
    WHERE IsDismissed = 0 AND IsIntentional = 0;
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ContinuityWarnings_TimelineList' AND object_id = OBJECT_ID(N'dbo.ContinuityWarnings'))
BEGIN
    CREATE INDEX IX_ContinuityWarnings_TimelineList
    ON dbo.ContinuityWarnings(ProjectID, BookID, SceneID, EntityType, EntityID, WarningTypeID, WarningSeverityID)
    INCLUDE (IsDismissed, IsIntentional, LastDetectedDate, ValidationRunID, ChapterID, Message, Details, CreatedDate);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneCharacters_SceneID_TimelineOverview' AND object_id = OBJECT_ID(N'dbo.SceneCharacters'))
BEGIN
    CREATE INDEX IX_SceneCharacters_SceneID_TimelineOverview
    ON dbo.SceneCharacters(SceneID, CharacterID)
    INCLUDE (SceneCharacterID, RoleInSceneTypeID, PresenceTypeID, LocationID, EntryLocationID, ExitLocationID, AppearanceNotes, OutfitDescription, PhysicalCondition, EmotionalState, KnowledgeNotes, CreatedDate, UpdatedDate);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Books_Project_Active_Timeline' AND object_id = OBJECT_ID(N'dbo.Books'))
BEGIN
    CREATE INDEX IX_Books_Project_Active_Timeline
    ON dbo.Books(ProjectID, IsArchived, BookID)
    INCLUDE (BookTitle, Subtitle, SortOrder, BookNumber);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Chapters_Book_Active_Timeline' AND object_id = OBJECT_ID(N'dbo.Chapters'))
BEGIN
    CREATE INDEX IX_Chapters_Book_Active_Timeline
    ON dbo.Chapters(BookID, IsArchived, ChapterID)
    INCLUDE (ChapterNumber, ChapterTitle, SortOrder);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Scenes_Chapter_Active_Timeline' AND object_id = OBJECT_ID(N'dbo.Scenes'))
BEGIN
    CREATE INDEX IX_Scenes_Chapter_Active_Timeline
    ON dbo.Scenes(ChapterID, IsArchived, SceneID)
    INCLUDE (SceneNumber, SceneTitle, SortOrder, POVCharacterID, PrimaryLocationID, RevisionStatusID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryAssets_Project_TimelineList' AND object_id = OBJECT_ID(N'dbo.StoryAssets'))
BEGIN
    CREATE INDEX IX_StoryAssets_Project_TimelineList
    ON dbo.StoryAssets(ProjectID, IsArchived, Importance DESC, AssetName)
    INCLUDE (AssetKindID, CurrentStateID, CurrentLocationID, IsResolved, ShowInQuickAddBar, ImagePath, ThumbnailPath, CreatedDate, UpdatedDate);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AssetEvents_SceneID_TimelineOverview' AND object_id = OBJECT_ID(N'dbo.AssetEvents'))
BEGIN
    CREATE INDEX IX_AssetEvents_SceneID_TimelineOverview
    ON dbo.AssetEvents(SceneID, StoryAssetID, UpdatedDate DESC)
    INCLUDE (AssetEventTypeID, EventTitle);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AssetStates_Active_Project_Sort' AND object_id = OBJECT_ID(N'dbo.AssetStates'))
BEGIN
    CREATE INDEX IX_AssetStates_Active_Project_Sort
    ON dbo.AssetStates(IsActive, ProjectID, SortOrder)
    INCLUDE (AssetKindID, StateName, Description, IsResolvedState);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_CharacterAttributeTypes_Active_Project_Sort' AND object_id = OBJECT_ID(N'dbo.CharacterAttributeTypes'))
BEGIN
    CREATE INDEX IX_CharacterAttributeTypes_Active_Project_Sort
    ON dbo.CharacterAttributeTypes(IsActive, ProjectID, SortOrder)
    INCLUDE (AttributeName, DataType, IsNormallyStable);
END;
GO

CREATE OR ALTER PROCEDURE dbo.ContinuityWarning_List
    @ProjectID int,
    @BookID int = NULL,
    @SceneID int = NULL,
    @EntityType nvarchar(80) = NULL,
    @EntityID int = NULL,
    @WarningTypeID int = NULL,
    @WarningSeverityID int = NULL,
    @IncludeDismissed bit = 0,
    @IncludeIntentional bit = 0
AS
BEGIN
    SET NOCOUNT ON;

    IF @IncludeDismissed = 0 AND @IncludeIntentional = 0
    BEGIN
        SELECT cw.ContinuityWarningID, cw.ValidationRunID, cw.ProjectID, cw.BookID, b.BookTitle, b.Subtitle AS BookSubtitle,
            cw.ChapterID, c.ChapterNumber, c.ChapterTitle, cw.SceneID, s.SceneNumber, s.SceneTitle, cw.EntityType,
            cw.EntityID, cw.WarningTypeID, wt.TypeName AS WarningTypeName, cw.WarningSeverityID, ws.SeverityName,
            cw.Message, cw.Details, cw.IsDismissed, cw.IsIntentional, cw.CreatedDate, cw.LastDetectedDate
        FROM dbo.ContinuityWarnings cw
        INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
        INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
        LEFT JOIN dbo.Books b ON b.BookID = cw.BookID
        LEFT JOIN dbo.Chapters c ON c.ChapterID = cw.ChapterID
        LEFT JOIN dbo.Scenes s ON s.SceneID = cw.SceneID
        WHERE cw.ProjectID = @ProjectID
            AND cw.IsDismissed = 0
            AND cw.IsIntentional = 0
            AND (@BookID IS NULL OR cw.BookID = @BookID)
            AND (@SceneID IS NULL OR cw.SceneID = @SceneID)
            AND (@EntityType IS NULL OR cw.EntityType = @EntityType)
            AND (@EntityID IS NULL OR cw.EntityID = @EntityID)
            AND (@WarningTypeID IS NULL OR cw.WarningTypeID = @WarningTypeID)
            AND (@WarningSeverityID IS NULL OR cw.WarningSeverityID = @WarningSeverityID)
        ORDER BY ws.SortOrder DESC, cw.LastDetectedDate DESC, cw.ContinuityWarningID DESC
        OPTION (RECOMPILE);

        RETURN;
    END;

    SELECT cw.ContinuityWarningID, cw.ValidationRunID, cw.ProjectID, cw.BookID, b.BookTitle, b.Subtitle AS BookSubtitle,
        cw.ChapterID, c.ChapterNumber, c.ChapterTitle, cw.SceneID, s.SceneNumber, s.SceneTitle, cw.EntityType,
        cw.EntityID, cw.WarningTypeID, wt.TypeName AS WarningTypeName, cw.WarningSeverityID, ws.SeverityName,
        cw.Message, cw.Details, cw.IsDismissed, cw.IsIntentional, cw.CreatedDate, cw.LastDetectedDate
    FROM dbo.ContinuityWarnings cw
    INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
    INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
    LEFT JOIN dbo.Books b ON b.BookID = cw.BookID
    LEFT JOIN dbo.Chapters c ON c.ChapterID = cw.ChapterID
    LEFT JOIN dbo.Scenes s ON s.SceneID = cw.SceneID
    WHERE cw.ProjectID = @ProjectID
        AND (@BookID IS NULL OR cw.BookID = @BookID)
        AND (@SceneID IS NULL OR cw.SceneID = @SceneID)
        AND (@EntityType IS NULL OR cw.EntityType = @EntityType)
        AND (@EntityID IS NULL OR cw.EntityID = @EntityID)
        AND (@WarningTypeID IS NULL OR cw.WarningTypeID = @WarningTypeID)
        AND (@WarningSeverityID IS NULL OR cw.WarningSeverityID = @WarningSeverityID)
        AND (@IncludeDismissed = 1 OR cw.IsDismissed = 0)
        AND (@IncludeIntentional = 1 OR cw.IsIntentional = 0)
    ORDER BY ws.SortOrder DESC, cw.LastDetectedDate DESC, cw.ContinuityWarningID DESC
    OPTION (RECOMPILE);
END;
GO

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

    CREATE TABLE #ProjectLocationPaths
    (
        LocationID int NOT NULL PRIMARY KEY,
        LocationName nvarchar(200) NOT NULL,
        LocationPath nvarchar(max) NOT NULL
    );

    ;WITH ProjectLocationTree AS
    (
        SELECT LocationID, LocationName, CAST(LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations
        WHERE ProjectID = @ProjectID AND ParentLocationID IS NULL AND IsArchived = 0

        UNION ALL

        SELECT child.LocationID, child.LocationName, CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations child
        INNER JOIN ProjectLocationTree parent ON parent.LocationID = child.ParentLocationID
        WHERE child.ProjectID = @ProjectID AND child.IsArchived = 0
    )
    INSERT #ProjectLocationPaths (LocationID, LocationName, LocationPath)
    SELECT LocationID, LocationName, LocationPath
    FROM ProjectLocationTree;

    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, sa.ShowInQuickAddBar, sa.ImagePath, sa.ThumbnailPath, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    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 #ProjectLocationPaths location ON location.LocationID = sa.CurrentLocationID
    WHERE sa.ProjectID = @ProjectID
        AND sa.IsArchived = 0
    ORDER BY sa.Importance DESC, sa.AssetName;
END;
GO

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

    SELECT AssetKindID, KindName, SortOrder, IsActive FROM dbo.AssetKinds WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT AssetStateID, ProjectID, AssetKindID, StateName, SortOrder, Description, IsResolvedState, IsActive
    FROM dbo.AssetStates
    WHERE IsActive = 1
        AND (@ProjectID IS NULL OR ProjectID IS NULL OR ProjectID = @ProjectID)
    ORDER BY SortOrder;
    SELECT AssetEventTypeID, TypeName, MarkerText, SortOrder, IsActive FROM dbo.AssetEventTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT AssetDependencyTypeID, TypeName, SortOrder, IsValidatingDependency, IsActive FROM dbo.AssetDependencyTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT AssetCustodyEventTypeID, TypeName, SortOrder, IsActive FROM dbo.AssetCustodyEventTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT CustodyRoleID, RoleName, SortOrder, IsActive FROM dbo.CustodyRoles WHERE IsActive = 1 ORDER BY SortOrder;
END;
GO

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

    SELECT CharacterRoleInSceneTypeID, TypeName, SortOrder, IsActive FROM dbo.CharacterRoleInSceneTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT PresenceTypeID, TypeName, SortOrder, IsActive FROM dbo.PresenceTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT CharacterAttributeTypeID, ProjectID, AttributeName, DataType, IsNormallyStable, SortOrder, IsActive
    FROM dbo.CharacterAttributeTypes
    WHERE IsActive = 1
        AND (@ProjectID IS NULL OR ProjectID IS NULL OR ProjectID = @ProjectID)
    ORDER BY SortOrder;
    SELECT KnowledgeStateID, StateName, SortOrder, IsActive FROM dbo.KnowledgeStates WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT rt.RelationshipTypeID, rt.TypeName, rt.IsPermanentDefault, rt.SortOrder, rt.IsActive,
        rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, rc.SortOrder AS CategorySortOrder
    FROM dbo.RelationshipTypes rt
    LEFT JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
    WHERE rt.IsActive = 1
    ORDER BY COALESCE(rc.SortOrder, 900), rt.SortOrder, rt.TypeName;
    SELECT RelationshipStateID, StateName, SortOrder, IsActive FROM dbo.RelationshipStates WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT RelationshipCategoryID, CategoryName, SortOrder, IsActive FROM dbo.RelationshipCategories WHERE IsActive = 1 ORDER BY SortOrder, CategoryName;
END;
GO

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

    CREATE TABLE #TimelineScenes
    (
        SceneID int NOT NULL PRIMARY KEY,
        SceneNumber decimal(9,2) NOT NULL,
        SceneTitle nvarchar(200) NOT NULL,
        ChapterNumber decimal(9,2) NOT NULL,
        BookTitle nvarchar(200) NOT NULL,
        BookSubtitle nvarchar(200) NULL,
        BookSortOrder int NOT NULL,
        ChapterSortOrder int NOT NULL,
        SceneSortOrder int NOT NULL
    );

    INSERT #TimelineScenes (SceneID, SceneNumber, SceneTitle, ChapterNumber, BookTitle, BookSubtitle, BookSortOrder, ChapterSortOrder, SceneSortOrder)
    SELECT s.SceneID, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle, b.Subtitle, b.SortOrder, ch.SortOrder, s.SortOrder
    FROM dbo.Books b
    INNER JOIN dbo.Chapters ch ON ch.BookID = b.BookID
    INNER JOIN dbo.Scenes s ON s.ChapterID = ch.ChapterID
    WHERE b.ProjectID = @ProjectID
        AND b.IsArchived = 0
        AND ch.IsArchived = 0
        AND s.IsArchived = 0
        AND (@BookID IS NULL OR b.BookID = @BookID);

    CREATE TABLE #TimelineCharacters
    (
        CharacterID int NOT NULL PRIMARY KEY,
        ProjectID int NOT NULL,
        CharacterName nvarchar(200) NOT NULL,
        ShortName nvarchar(100) NULL,
        Sex nvarchar(50) NULL,
        BirthDate date NULL,
        AgeAtSeriesStart int NULL,
        AgeReferenceSceneID int NULL,
        Height nvarchar(50) NULL,
        EyeColour nvarchar(50) NULL,
        CharacterImportance int NULL,
        ShowInQuickAddBar bit NOT NULL,
        DefaultDescription nvarchar(max) NULL,
        CreatedDate datetime2 NOT NULL,
        UpdatedDate datetime2 NOT NULL,
        IsArchived bit NOT NULL
    );

    INSERT #TimelineCharacters (CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived)
    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM dbo.Characters
    WHERE ProjectID = @ProjectID AND IsArchived = 0;

    CREATE TABLE #ProjectLocationPaths
    (
        LocationID int NOT NULL PRIMARY KEY,
        LocationName nvarchar(200) NOT NULL,
        LocationPath nvarchar(max) NOT NULL
    );

    ;WITH ProjectLocationTree AS
    (
        SELECT LocationID, LocationName, CAST(LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations
        WHERE ProjectID = @ProjectID AND ParentLocationID IS NULL AND IsArchived = 0

        UNION ALL

        SELECT child.LocationID, child.LocationName, CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations child
        INNER JOIN ProjectLocationTree parent ON parent.LocationID = child.ParentLocationID
        WHERE child.ProjectID = @ProjectID AND child.IsArchived = 0
    )
    INSERT #ProjectLocationPaths (LocationID, LocationName, LocationPath)
    SELECT LocationID, LocationName, LocationPath
    FROM ProjectLocationTree;

    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM #TimelineCharacters
    ORDER BY ISNULL(CharacterImportance, 0) DESC, CharacterName;

    SELECT sc.SceneCharacterID, sc.SceneID, sc.CharacterID, c.ProjectID, c.CharacterName, c.ShortName, c.BirthDate, c.AgeAtSeriesStart,
        sc.RoleInSceneTypeID, role.TypeName AS RoleInSceneTypeName, sc.PresenceTypeID, presence.TypeName AS PresenceTypeName,
        sc.LocationID, location.LocationName, location.LocationPath, sc.EntryLocationID, entry.LocationName AS EntryLocationName,
        sc.ExitLocationID, exitLocation.LocationName AS ExitLocationName, sc.AppearanceNotes, sc.OutfitDescription,
        sc.PhysicalCondition, sc.EmotionalState, sc.KnowledgeNotes, s.SceneNumber, s.SceneTitle, s.ChapterNumber, s.BookTitle, s.BookSubtitle,
        sc.CreatedDate, sc.UpdatedDate
    FROM #TimelineScenes s
    INNER JOIN dbo.SceneCharacters sc ON sc.SceneID = s.SceneID
    INNER JOIN #TimelineCharacters c ON c.CharacterID = sc.CharacterID
    LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
    LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
    LEFT JOIN #ProjectLocationPaths location ON location.LocationID = sc.LocationID
    LEFT JOIN #ProjectLocationPaths entry ON entry.LocationID = sc.EntryLocationID
    LEFT JOIN #ProjectLocationPaths exitLocation ON exitLocation.LocationID = sc.ExitLocationID
    ORDER BY s.BookSortOrder, s.ChapterSortOrder, s.SceneSortOrder, c.CharacterName;
END;
GO

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

    CREATE TABLE #TimelineScenes
    (
        SceneID int NOT NULL PRIMARY KEY,
        BookSortOrder int NOT NULL,
        ChapterSortOrder int NOT NULL,
        SceneSortOrder int NOT NULL
    );

    INSERT #TimelineScenes (SceneID, BookSortOrder, ChapterSortOrder, SceneSortOrder)
    SELECT s.SceneID, b.SortOrder, c.SortOrder, s.SortOrder
    FROM dbo.Books b
    INNER JOIN dbo.Chapters c ON c.BookID = b.BookID
    INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID
    WHERE b.ProjectID = @ProjectID
        AND b.IsArchived = 0
        AND c.IsArchived = 0
        AND s.IsArchived = 0
        AND (@BookID IS NULL OR b.BookID = @BookID);

    CREATE TABLE #ProjectLocationPaths
    (
        LocationID int NOT NULL PRIMARY KEY,
        LocationName nvarchar(200) NOT NULL,
        LocationPath nvarchar(max) NOT NULL
    );

    ;WITH ProjectLocationTree AS
    (
        SELECT LocationID, LocationName, CAST(LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations
        WHERE ProjectID = @ProjectID AND ParentLocationID IS NULL AND IsArchived = 0

        UNION ALL

        SELECT child.LocationID, child.LocationName, CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations child
        INNER JOIN ProjectLocationTree parent ON parent.LocationID = child.ParentLocationID
        WHERE child.ProjectID = @ProjectID AND child.IsArchived = 0
    )
    INSERT #ProjectLocationPaths (LocationID, LocationName, LocationPath)
    SELECT LocationID, LocationName, LocationPath
    FROM ProjectLocationTree;

    SELECT ts.SceneID,
        sc.CharacterID,
        ch.CharacterName,
        CONVERT(bit, CASE WHEN role.TypeName LIKE N'%POV%' THEN 1 ELSE 0 END) AS IsPov,
        CONVERT(int, NULL) AS StoryAssetID,
        CONVERT(nvarchar(200), NULL) AS AssetName,
        CONVERT(int, NULL) AS AssetImportance,
        CONVERT(nvarchar(100), NULL) AS AssetEventTypeName,
        location.LocationPath AS LocationName
    FROM #TimelineScenes ts
    INNER JOIN dbo.SceneCharacters sc ON sc.SceneID = ts.SceneID
    INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID AND ch.ProjectID = @ProjectID AND ch.IsArchived = 0
    LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
    LEFT JOIN #ProjectLocationPaths location ON location.LocationID = sc.LocationID

    UNION ALL

    SELECT SceneID, CharacterID, CharacterName, IsPov, StoryAssetID, AssetName, AssetImportance, AssetEventTypeName, LocationName
    FROM
    (
        SELECT ts.SceneID,
            CONVERT(int, NULL) AS CharacterID,
            CONVERT(nvarchar(200), NULL) AS CharacterName,
            CONVERT(bit, 0) AS IsPov,
            sa.StoryAssetID,
            sa.AssetName,
            sa.Importance AS AssetImportance,
            aet.TypeName AS AssetEventTypeName,
            CONVERT(nvarchar(max), NULL) AS LocationName,
            ROW_NUMBER() OVER (PARTITION BY ts.SceneID, sa.StoryAssetID ORDER BY ae.UpdatedDate DESC, ae.AssetEventID DESC) AS rn
        FROM #TimelineScenes ts
        INNER JOIN dbo.AssetEvents ae ON ae.SceneID = ts.SceneID
        INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID AND sa.ProjectID = @ProjectID AND sa.IsArchived = 0
        INNER JOIN dbo.AssetEventTypes aet ON aet.AssetEventTypeID = ae.AssetEventTypeID
    ) assetRows
    WHERE rn = 1
    ORDER BY SceneID;
END;
GO