PlotDirector/PlotLine/Sql/034_ProjectBackupExport.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

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

    SELECT ProjectID, ProjectName, Description, CreatedDate, UpdatedDate, IsArchived, ArchivedDate, ArchivedReason
    FROM dbo.Projects
    WHERE ProjectID = @ProjectID;

    SELECT BookID, ProjectID, BookTitle, BookNumber, Description, SortOrder, CreatedDate, UpdatedDate,
        IsArchived, ArchivedDate, ArchivedReason
    FROM dbo.Books
    WHERE ProjectID = @ProjectID
    ORDER BY SortOrder, BookNumber, BookID;

    SELECT c.ChapterID, c.BookID, c.ChapterNumber, c.ChapterTitle, c.POVCharacterID, c.Summary, c.SortOrder,
        c.RevisionStatusID, rs.StatusName AS RevisionStatusName, c.CreatedDate, c.UpdatedDate,
        c.IsArchived, c.ArchivedDate, c.ArchivedReason
    FROM dbo.Chapters c
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = c.RevisionStatusID
    WHERE b.ProjectID = @ProjectID
    ORDER BY b.SortOrder, c.SortOrder, c.ChapterID;

    SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID, s.PrimaryLocationID,
        l.LocationName AS PrimaryLocationName, l.LocationName AS PrimaryLocationPath, s.SortOrder, s.TimeModeID, tm.TimeModeName,
        s.StartDateTime, s.EndDateTime, s.DurationAmount, s.DurationUnitID, du.DurationUnitName,
        s.RelativeTimeText, s.TimeConfidenceID, tc.TimeConfidenceName, s.ScenePurposeNotes, s.SceneOutcomeNotes,
        s.RevisionStatusID, rs.StatusName AS RevisionStatusName, s.CreatedDate, s.UpdatedDate,
        s.IsArchived, s.ArchivedDate, s.ArchivedReason
    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.TimeModes tm ON tm.TimeModeID = s.TimeModeID
    INNER JOIN dbo.TimeConfidences tc ON tc.TimeConfidenceID = s.TimeConfidenceID
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
    LEFT JOIN dbo.DurationUnits du ON du.DurationUnitID = s.DurationUnitID
    LEFT JOIN dbo.Locations l ON l.LocationID = s.PrimaryLocationID
    WHERE b.ProjectID = @ProjectID
    ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, s.SceneID;

    SELECT sp.SceneID, sp.ScenePurposeTypeID, spt.PurposeName, spt.SortOrder
    FROM dbo.ScenePurposes sp
    INNER JOIN dbo.ScenePurposeTypes spt ON spt.ScenePurposeTypeID = sp.ScenePurposeTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = sp.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY sp.SceneID, spt.SortOrder;

    SELECT so.SceneOutcomeID, so.SceneID, so.OutcomeDescription, so.OutcomeTypeID, so.CreatedDate, so.UpdatedDate
    FROM dbo.SceneOutcomes so
    INNER JOIN dbo.Scenes s ON s.SceneID = so.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY so.SceneID, so.SceneOutcomeID;

    SELECT mt.MetricTypeID, mt.ProjectID, mt.MetricName, mt.Description, mt.MinValue, mt.MaxValue,
        mt.DefaultValue, COALESCE(ptms.SortOrder, mt.SortOrder) AS SortOrder, mt.IsActive,
        CAST(CASE WHEN mt.IsActive = 1 AND ptms.MetricTypeID IS NOT NULL THEN 1 ELSE 0 END AS bit) AS IsEnabledForProject,
        CAST(CASE WHEN mt.ProjectID = @ProjectID THEN 1 ELSE 0 END AS bit) AS IsCustom,
        STUFF((
            SELECT N',' + gmp.PresetKey
            FROM dbo.GenreMetricPresetMetrics gpmm
            INNER JOIN dbo.GenreMetricPresets gmp ON gmp.GenreMetricPresetID = gpmm.GenreMetricPresetID
            WHERE gpmm.MetricTypeID = mt.MetricTypeID
            ORDER BY gmp.SortOrder, gmp.PresetName
            FOR XML PATH(N''), TYPE).value(N'.', N'nvarchar(max)'), 1, 1, N'') AS GenrePresetKeys,
        STUFF((
            SELECT N', ' + gmp.PresetName
            FROM dbo.GenreMetricPresetMetrics gpmm
            INNER JOIN dbo.GenreMetricPresets gmp ON gmp.GenreMetricPresetID = gpmm.GenreMetricPresetID
            WHERE gpmm.MetricTypeID = mt.MetricTypeID
            ORDER BY gmp.SortOrder, gmp.PresetName
            FOR XML PATH(N''), TYPE).value(N'.', N'nvarchar(max)'), 1, 2, N'') AS GenrePresetNames,
        (
            SELECT COUNT(1)
            FROM dbo.SceneMetricValues smv
            INNER JOIN dbo.Scenes s ON s.SceneID = smv.SceneID
            INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
            INNER JOIN dbo.Books b ON b.BookID = c.BookID
            WHERE smv.MetricTypeID = mt.MetricTypeID AND b.ProjectID = @ProjectID
        ) AS ValueCount
    FROM dbo.SceneMetricTypes mt
    LEFT JOIN dbo.ProjectTimelineMetricSettings ptms ON ptms.ProjectID = @ProjectID AND ptms.MetricTypeID = mt.MetricTypeID
    WHERE mt.ProjectID IS NULL
        OR mt.ProjectID = @ProjectID
        OR EXISTS
        (
            SELECT 1
            FROM dbo.SceneMetricValues smv
            INNER JOIN dbo.Scenes s ON s.SceneID = smv.SceneID
            INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
            INNER JOIN dbo.Books b ON b.BookID = c.BookID
            WHERE smv.MetricTypeID = mt.MetricTypeID AND b.ProjectID = @ProjectID
        )
    ORDER BY COALESCE(ptms.SortOrder, mt.SortOrder), mt.MetricName;

    SELECT smv.SceneID, smv.MetricTypeID, mt.MetricName, mt.Description, mt.MinValue, mt.MaxValue,
        mt.DefaultValue, mt.SortOrder, mt.IsActive, smv.Value, smv.Notes
    FROM dbo.SceneMetricValues smv
    INNER JOIN dbo.SceneMetricTypes mt ON mt.MetricTypeID = smv.MetricTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = smv.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY smv.SceneID, mt.SortOrder;

    SELECT ProjectTimelineSettingsID, ProjectID, ShowSceneCards, ShowMetricShape, ShowPlotLines,
        ShowStoryAssets, ShowCharacterAppearances, ShowWarnings, CreatedDate, UpdatedDate
    FROM dbo.ProjectTimelineSettings
    WHERE ProjectID = @ProjectID;

    SELECT ptms.ProjectID, ptms.MetricTypeID, mt.MetricName, ptms.SortOrder, ptms.CreatedDate
    FROM dbo.ProjectTimelineMetricSettings ptms
    INNER JOIN dbo.SceneMetricTypes mt ON mt.MetricTypeID = ptms.MetricTypeID
    WHERE ptms.ProjectID = @ProjectID
    ORDER BY ptms.SortOrder;

    SELECT TimelineViewPresetID, ProjectID, BookID, PresetName, FilterJson, VisibilityJson, FocusType, FocusID,
        CreatedDate, UpdatedDate, IsArchived
    FROM dbo.TimelineViewPresets
    WHERE ProjectID = @ProjectID
    ORDER BY PresetName;

    SELECT pl.PlotLineID, pl.ProjectID, pl.BookID, b.BookTitle, pl.PlotLineName, pl.PlotLineTypeID,
        plt.TypeName AS PlotLineTypeName, pl.Description, pl.ParentPlotLineID, parent.PlotLineName AS ParentPlotLineName,
        pl.SortOrder, pl.Colour, pl.IsMainPlot, pl.IsVisibleOnTimeline, pl.CreatedDate, pl.UpdatedDate,
        pl.IsArchived, pl.ArchivedDate, pl.ArchivedReason
    FROM dbo.PlotLines pl
    INNER JOIN dbo.PlotLineTypes plt ON plt.PlotLineTypeID = pl.PlotLineTypeID
    LEFT JOIN dbo.Books b ON b.BookID = pl.BookID
    LEFT JOIN dbo.PlotLines parent ON parent.PlotLineID = pl.ParentPlotLineID
    WHERE pl.ProjectID = @ProjectID
    ORDER BY pl.SortOrder, pl.PlotLineName;

    SELECT pt.PlotThreadID, pt.PlotLineID, pl.ProjectID, pl.PlotLineName, pt.ThreadTitle, pt.ThreadTypeID,
        tt.TypeName AS ThreadTypeName, pt.ThreadStatusID, ts.StatusName AS ThreadStatusName, pt.Importance,
        pt.Summary, pt.IntroducedSceneID, pt.PlannedResolutionSceneID, pt.ActualResolutionSceneID,
        pt.CreatedDate, pt.UpdatedDate, pt.IsArchived, pt.ArchivedDate, pt.ArchivedReason
    FROM dbo.PlotThreads pt
    INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
    INNER JOIN dbo.ThreadTypes tt ON tt.ThreadTypeID = pt.ThreadTypeID
    INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
    WHERE pl.ProjectID = @ProjectID
    ORDER BY pl.SortOrder, pt.Importance DESC, pt.ThreadTitle;

    SELECT te.ThreadEventID, te.PlotThreadID, pt.ThreadTitle, pl.PlotLineID, pl.PlotLineName, pl.Colour,
        te.SceneID, te.EventTypeID, tet.TypeName AS EventTypeName, tet.MarkerText, te.EventTitle,
        te.EventDescription, te.CreatedDate, te.UpdatedDate
    FROM dbo.ThreadEvents te
    INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
    INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
    INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
    WHERE pl.ProjectID = @ProjectID
    ORDER BY te.SceneID, te.ThreadEventID;

    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description,
        sa.Importance, sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        l.LocationName AS CurrentLocationName, l.LocationName AS CurrentLocationPath, sa.IsResolved,
        sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived, sa.ArchivedDate, sa.ArchivedReason
    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.Locations l ON l.LocationID = sa.CurrentLocationID
    WHERE sa.ProjectID = @ProjectID
    ORDER BY sa.Importance DESC, sa.AssetName;

    SELECT ast.AssetStateID, ast.ProjectID, ast.AssetKindID, ak.KindName, ast.StateName, ast.SortOrder, ast.Description,
        ast.IsResolvedState, ast.IsActive
    FROM dbo.AssetStates ast
    LEFT JOIN dbo.AssetKinds ak ON ak.AssetKindID = ast.AssetKindID
    WHERE ast.ProjectID IS NULL
        OR ast.ProjectID = @ProjectID
        OR EXISTS (SELECT 1 FROM dbo.StoryAssets sa WHERE sa.ProjectID = @ProjectID AND sa.CurrentStateID = ast.AssetStateID)
        OR EXISTS (SELECT 1 FROM dbo.AssetEvents ae INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID WHERE sa.ProjectID = @ProjectID AND (ae.FromStateID = ast.AssetStateID OR ae.ToStateID = ast.AssetStateID))
        OR EXISTS (SELECT 1 FROM dbo.AssetDependencies ad INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ad.SourceAssetID WHERE sa.ProjectID = @ProjectID AND (ad.SourceRequiredStateID = ast.AssetStateID OR ad.TargetBlockedStateID = ast.AssetStateID))
    ORDER BY ast.SortOrder, ast.StateName;

    SELECT ae.AssetEventID, ae.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName,
        ae.SceneID, ae.AssetEventTypeID, aet.TypeName AS AssetEventTypeName, aet.MarkerText,
        ae.FromStateID, fs.StateName AS FromStateName, ae.ToStateID, ts.StateName AS ToStateName,
        ae.EventTitle, ae.EventDescription, b.BookTitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle,
        ae.CreatedDate, ae.UpdatedDate
    FROM dbo.AssetEvents ae
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    INNER JOIN dbo.AssetEventTypes aet ON aet.AssetEventTypeID = ae.AssetEventTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = ae.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    LEFT JOIN dbo.AssetStates fs ON fs.AssetStateID = ae.FromStateID
    LEFT JOIN dbo.AssetStates ts ON ts.AssetStateID = ae.ToStateID
    WHERE sa.ProjectID = @ProjectID
    ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ae.AssetEventID;

    SELECT ad.AssetDependencyID, ad.SourceAssetID, source.AssetName AS SourceAssetName, ad.TargetAssetID,
        target.AssetName AS TargetAssetName, ad.AssetDependencyTypeID, adt.TypeName AS DependencyTypeName,
        adt.IsValidatingDependency, ad.SourceRequiredStateID, srs.StateName AS SourceRequiredStateName,
        ad.TargetBlockedStateID, tbs.StateName AS TargetBlockedStateName, ad.Description,
        ad.CreatedDate, ad.UpdatedDate, ad.IsArchived
    FROM dbo.AssetDependencies ad
    INNER JOIN dbo.StoryAssets source ON source.StoryAssetID = ad.SourceAssetID
    INNER JOIN dbo.StoryAssets target ON target.StoryAssetID = ad.TargetAssetID
    INNER JOIN dbo.AssetDependencyTypes adt ON adt.AssetDependencyTypeID = ad.AssetDependencyTypeID
    LEFT JOIN dbo.AssetStates srs ON srs.AssetStateID = ad.SourceRequiredStateID
    LEFT JOIN dbo.AssetStates tbs ON tbs.AssetStateID = ad.TargetBlockedStateID
    WHERE source.ProjectID = @ProjectID OR target.ProjectID = @ProjectID
    ORDER BY source.AssetName, target.AssetName;

    SELECT ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID, ace.AssetCustodyEventTypeID,
        acet.TypeName AS CustodyEventTypeName, ace.Description,
        STRING_AGG(COALESCE(ch.CharacterName, acec.CharacterNameText), ', ') AS CustodianSummary,
        b.BookTitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle, ace.CreatedDate, ace.UpdatedDate
    FROM dbo.AssetCustodyEvents ace
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
    INNER JOIN dbo.AssetCustodyEventTypes acet ON acet.AssetCustodyEventTypeID = ace.AssetCustodyEventTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = ace.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    LEFT JOIN dbo.AssetCustodyEventCharacters acec ON acec.AssetCustodyEventID = ace.AssetCustodyEventID
    LEFT JOIN dbo.Characters ch ON ch.CharacterID = acec.CharacterID
    WHERE sa.ProjectID = @ProjectID
    GROUP BY ace.AssetCustodyEventID, ace.StoryAssetID, sa.AssetName, ace.SceneID, ace.AssetCustodyEventTypeID,
        acet.TypeName, ace.Description, b.BookTitle, c.ChapterNumber, s.SceneNumber, s.SceneTitle, ace.CreatedDate, ace.UpdatedDate
    ORDER BY MIN(b.SortOrder), MIN(c.SortOrder), MIN(s.SortOrder), ace.AssetCustodyEventID;

    SELECT acec.AssetCustodyEventCharacterID, acec.AssetCustodyEventID, acec.CharacterID, ch.CharacterName,
        acec.CharacterNameText, acec.CustodyRoleID, cr.RoleName AS CustodyRoleName
    FROM dbo.AssetCustodyEventCharacters acec
    INNER JOIN dbo.AssetCustodyEvents ace ON ace.AssetCustodyEventID = acec.AssetCustodyEventID
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
    INNER JOIN dbo.CustodyRoles cr ON cr.CustodyRoleID = acec.CustodyRoleID
    LEFT JOIN dbo.Characters ch ON ch.CharacterID = acec.CharacterID
    WHERE sa.ProjectID = @ProjectID
    ORDER BY acec.AssetCustodyEventID, acec.AssetCustodyEventCharacterID;

    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart,
        AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar,
        DefaultDescription, CreatedDate, UpdatedDate, IsArchived, ArchivedDate, ArchivedReason
    FROM dbo.Characters
    WHERE ProjectID = @ProjectID
    ORDER BY CharacterImportance DESC, CharacterName;

    SELECT sc.SceneCharacterID, sc.SceneID, sc.CharacterID, ch.ProjectID, ch.CharacterName, ch.ShortName,
        ch.BirthDate, ch.AgeAtSeriesStart, sc.RoleInSceneTypeID, rist.TypeName AS RoleInSceneTypeName,
        sc.PresenceTypeID, pt.TypeName AS PresenceTypeName, sc.LocationID, loc.LocationName,
        loc.LocationName AS LocationPath, sc.EntryLocationID, entryLoc.LocationName AS EntryLocationName,
        sc.ExitLocationID, exitLoc.LocationName AS ExitLocationName, sc.AppearanceNotes, sc.OutfitDescription,
        sc.PhysicalCondition, sc.EmotionalState, sc.KnowledgeNotes, s.SceneNumber, s.SceneTitle,
        c.ChapterNumber, b.BookTitle, sc.CreatedDate, sc.UpdatedDate
    FROM dbo.SceneCharacters sc
    INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
    INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    LEFT JOIN dbo.CharacterRoleInSceneTypes rist ON rist.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
    LEFT JOIN dbo.PresenceTypes pt ON pt.PresenceTypeID = sc.PresenceTypeID
    LEFT JOIN dbo.Locations loc ON loc.LocationID = sc.LocationID
    LEFT JOIN dbo.Locations entryLoc ON entryLoc.LocationID = sc.EntryLocationID
    LEFT JOIN dbo.Locations exitLoc ON exitLoc.LocationID = sc.ExitLocationID
    WHERE ch.ProjectID = @ProjectID
    ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, ch.CharacterName;

    SELECT cat.CharacterAttributeTypeID, cat.ProjectID, cat.AttributeName, cat.DataType, cat.IsNormallyStable,
        cat.SortOrder, cat.IsActive
    FROM dbo.CharacterAttributeTypes cat
    WHERE cat.ProjectID IS NULL
        OR cat.ProjectID = @ProjectID
        OR EXISTS (SELECT 1 FROM dbo.CharacterAttributeEvents cae INNER JOIN dbo.Characters ch ON ch.CharacterID = cae.CharacterID WHERE ch.ProjectID = @ProjectID AND cae.CharacterAttributeTypeID = cat.CharacterAttributeTypeID)
    ORDER BY cat.SortOrder, cat.AttributeName;

    SELECT cae.CharacterAttributeEventID, cae.CharacterID, ch.CharacterName, cae.CharacterAttributeTypeID,
        cat.AttributeName, cae.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle,
        cae.AttributeValue, cae.Description, cae.CreatedDate, cae.UpdatedDate
    FROM dbo.CharacterAttributeEvents cae
    INNER JOIN dbo.Characters ch ON ch.CharacterID = cae.CharacterID
    INNER JOIN dbo.CharacterAttributeTypes cat ON cat.CharacterAttributeTypeID = cae.CharacterAttributeTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = cae.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE ch.ProjectID = @ProjectID
    ORDER BY ch.CharacterName, b.SortOrder, c.SortOrder, s.SortOrder;

    SELECT ck.CharacterKnowledgeID, ck.CharacterID, ch.CharacterName, ck.StoryAssetID, sa.AssetName,
        ck.PlotThreadID, pt.ThreadTitle, ck.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle,
        ck.KnowledgeStateID, ks.StateName AS KnowledgeStateName, ck.SourceEventID, ck.Description,
        ck.CreatedDate, ck.UpdatedDate
    FROM dbo.CharacterKnowledge ck
    INNER JOIN dbo.Characters ch ON ch.CharacterID = ck.CharacterID
    INNER JOIN dbo.KnowledgeStates ks ON ks.KnowledgeStateID = ck.KnowledgeStateID
    INNER JOIN dbo.Scenes s ON s.SceneID = ck.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ck.StoryAssetID
    LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = ck.PlotThreadID
    WHERE ch.ProjectID = @ProjectID
    ORDER BY ch.CharacterName, b.SortOrder, c.SortOrder, s.SortOrder;

    SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, a.CharacterName AS CharacterAName,
        cr.CharacterBID, bch.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
        rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName, rc.SortOrder AS CategorySortOrder,
        rt.SortOrder AS RelationshipTypeSortOrder, cr.IsPermanent, cr.StartSceneID, cr.EndSceneID,
        cr.IsKnownToReader, cr.Notes, cr.IsInitialRelationship, cr.InitialBookID, ib.BookTitle AS InitialBookTitle,
        cr.ReaderInitiallyKnows, cr.InitialRelationshipStateID, irs.StateName AS InitialRelationshipStateName,
        cr.InitialIntensity, cr.IsReciprocal, cr.CreatedDate, cr.UpdatedDate,
        cr.IsArchived, cr.ArchivedDate, cr.ArchivedReason
    FROM dbo.CharacterRelationships cr
    INNER JOIN dbo.Characters a ON a.CharacterID = cr.CharacterAID
    INNER JOIN dbo.Characters bch ON bch.CharacterID = cr.CharacterBID
    INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
    INNER JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
    LEFT JOIN dbo.Books ib ON ib.BookID = cr.InitialBookID
    LEFT JOIN dbo.RelationshipStates irs ON irs.RelationshipStateID = cr.InitialRelationshipStateID
    WHERE cr.ProjectID = @ProjectID
    ORDER BY a.CharacterName, bch.CharacterName, cr.CharacterRelationshipID;

    SELECT re.RelationshipEventID, re.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID,
        a.CharacterName AS CharacterAName, cr.CharacterBID, bch.CharacterName AS CharacterBName,
        rt.TypeName AS RelationshipTypeName, rt.RelationshipCategoryID, rc.CategoryName AS RelationshipCategoryName,
        rc.SortOrder AS CategorySortOrder, rt.SortOrder AS RelationshipTypeSortOrder, cr.IsReciprocal,
        re.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, bk.BookTitle, re.RelationshipStateID,
        rs.StateName AS RelationshipStateName, re.Intensity, re.IsKnownToOtherCharacter, re.Description,
        re.CreatedDate, re.UpdatedDate
    FROM dbo.RelationshipEvents re
    INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
    INNER JOIN dbo.Characters a ON a.CharacterID = cr.CharacterAID
    INNER JOIN dbo.Characters bch ON bch.CharacterID = cr.CharacterBID
    INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
    INNER JOIN dbo.RelationshipCategories rc ON rc.RelationshipCategoryID = rt.RelationshipCategoryID
    INNER JOIN dbo.RelationshipStates rs ON rs.RelationshipStateID = re.RelationshipStateID
    INNER JOIN dbo.Scenes s ON s.SceneID = re.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books bk ON bk.BookID = c.BookID
    WHERE cr.ProjectID = @ProjectID
    ORDER BY bk.SortOrder, c.SortOrder, s.SortOrder, re.RelationshipEventID;

    SELECT l.LocationID, l.ProjectID, l.ParentLocationID, parent.LocationName AS ParentLocationName,
        l.LocationName, l.LocationTypeID, lt.TypeName AS LocationTypeName, l.Description,
        l.LocationName AS LocationPath, CAST(0 AS int) AS Depth, l.ShowInQuickAddBar,
        l.CreatedDate, l.UpdatedDate, l.IsArchived, l.ArchivedDate, l.ArchivedReason
    FROM dbo.Locations l
    LEFT JOIN dbo.Locations parent ON parent.LocationID = l.ParentLocationID
    LEFT JOIN dbo.LocationTypes lt ON lt.LocationTypeID = l.LocationTypeID
    WHERE l.ProjectID = @ProjectID
    ORDER BY l.LocationName;

    SELECT lr.LocationRelationshipID, lr.FromLocationID, fl.LocationName AS FromLocationName,
        lr.ToLocationID, tl.LocationName AS ToLocationName, lr.LocationRelationshipTypeID,
        lrt.TypeName AS RelationshipTypeName, lr.IsBidirectional, lr.Strength,
        lr.CanHearNormalSpeech, lr.CanHearLoudNoise, lr.CanSeeMovement, lr.CanSeeClearly,
        lr.CanTravelDirectly, lr.Notes, lr.CreatedDate, lr.UpdatedDate, lr.IsArchived
    FROM dbo.LocationRelationships lr
    INNER JOIN dbo.Locations fl ON fl.LocationID = lr.FromLocationID
    INNER JOIN dbo.Locations tl ON tl.LocationID = lr.ToLocationID
    INNER JOIN dbo.LocationRelationshipTypes lrt ON lrt.LocationRelationshipTypeID = lr.LocationRelationshipTypeID
    WHERE fl.ProjectID = @ProjectID OR tl.ProjectID = @ProjectID
    ORDER BY fl.LocationName, tl.LocationName;

    SELECT sal.SceneAssetLocationID, sal.SceneID, sal.StoryAssetID, sa.AssetName, sal.LocationID,
        l.LocationName, l.LocationName AS LocationPath, sal.Description, sal.CreatedDate, sal.UpdatedDate
    FROM dbo.SceneAssetLocations sal
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID
    INNER JOIN dbo.Locations l ON l.LocationID = sal.LocationID
    WHERE sa.ProjectID = @ProjectID
    ORDER BY sal.SceneID, sa.AssetName;

    SELECT sd.SceneDependencyID, ISNULL(sd.ProjectID, @ProjectID) AS ProjectID, sd.SourceSceneID, sd.TargetSceneID,
        sd.SceneDependencyTypeID, sdt.TypeName AS SceneDependencyTypeName, sd.Description,
        sd.IsHardDependency, CAST(NULL AS nvarchar(50)) AS DependencyDirection, sb.BookID AS SourceBookID, sb.BookTitle AS SourceBookTitle,
        sc.ChapterID AS SourceChapterID, sc.ChapterNumber AS SourceChapterNumber, ss.SceneNumber AS SourceSceneNumber,
        ss.SceneTitle AS SourceSceneTitle, tb.BookID AS TargetBookID, tb.BookTitle AS TargetBookTitle,
        tc.ChapterID AS TargetChapterID, tc.ChapterNumber AS TargetChapterNumber, ts.SceneNumber AS TargetSceneNumber,
        ts.SceneTitle AS TargetSceneTitle, sd.CreatedDate, sd.UpdatedDate, sd.IsArchived
    FROM dbo.SceneDependencies sd
    INNER JOIN dbo.SceneDependencyTypes sdt ON sdt.SceneDependencyTypeID = sd.SceneDependencyTypeID
    INNER JOIN dbo.Scenes ss ON ss.SceneID = sd.SourceSceneID
    INNER JOIN dbo.Chapters sc ON sc.ChapterID = ss.ChapterID
    INNER JOIN dbo.Books sb ON sb.BookID = sc.BookID
    INNER JOIN dbo.Scenes ts ON ts.SceneID = sd.TargetSceneID
    INNER JOIN dbo.Chapters tc ON tc.ChapterID = ts.ChapterID
    INNER JOIN dbo.Books tb ON tb.BookID = tc.BookID
    WHERE sd.ProjectID = @ProjectID OR sb.ProjectID = @ProjectID OR tb.ProjectID = @ProjectID
    ORDER BY sb.SortOrder, sc.SortOrder, ss.SortOrder, sd.SceneDependencyID;

    SELECT ValidationRunID, ProjectID, BookID, StartedDate, CompletedDate, TriggeredBy, ScopeDescription
    FROM dbo.ValidationRuns
    WHERE ProjectID = @ProjectID
    ORDER BY StartedDate DESC;

    SELECT cw.ContinuityWarningID, cw.ValidationRunID, cw.ProjectID, cw.BookID, b.BookTitle, 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
    ORDER BY cw.LastDetectedDate DESC, cw.ContinuityWarningID;

    SELECT sn.SceneNoteID, sn.SceneID, sn.SceneNoteTypeID, snt.TypeName, sn.NoteTitle, sn.NoteText,
        sn.SortOrder, sn.IsPinned, sn.IsResolved, sn.CreatedDate, sn.UpdatedDate
    FROM dbo.SceneNotes sn
    INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY sn.SceneID, sn.SortOrder, sn.SceneNoteID;

    SELECT sw.SceneWorkflowID, sw.SceneID, sw.DraftStatus, sw.EstimatedWordCount, sw.ActualWordCount,
        sw.DraftedDate, sw.LastWorkedOn, sw.ReadyForDraft, sw.ReadyForRevision, sw.ReadyForPolish,
        sw.IsBlocked, sw.BlockedReason, sw.Priority, sw.CreatedDate, sw.UpdatedDate
    FROM dbo.SceneWorkflow sw
    INNER JOIN dbo.Scenes s ON s.SceneID = sw.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY sw.SceneID;

    SELECT sci.SceneChecklistItemID, sci.SceneID, sci.ItemText, sci.IsCompleted, sci.SortOrder,
        sci.CreatedDate, sci.UpdatedDate
    FROM dbo.SceneChecklistItems sci
    INNER JOIN dbo.Scenes s ON s.SceneID = sci.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY sci.SceneID, sci.SortOrder, sci.SceneChecklistItemID;

    SELECT sa.SceneAttachmentID, sa.SceneID, sa.SceneAttachmentTypeID, sat.TypeName, sa.Title,
        sa.FilePath, sa.ExternalUrl, sa.Notes, sa.CreatedDate, sa.UpdatedDate
    FROM dbo.SceneAttachments sa
    INNER JOIN dbo.SceneAttachmentTypes sat ON sat.SceneAttachmentTypeID = sa.SceneAttachmentTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY sa.SceneID, sa.SceneAttachmentID;

    SELECT cg.ChapterGoalID, cg.ChapterID, cg.GoalSummary, cg.EmotionalGoal, cg.ReaderTakeaway,
        cg.MustInclude, cg.RisksOrConcerns, cg.CreatedDate, cg.UpdatedDate
    FROM dbo.ChapterGoals cg
    INNER JOIN dbo.Chapters c ON c.ChapterID = cg.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    WHERE b.ProjectID = @ProjectID
    ORDER BY b.SortOrder, c.SortOrder;

    SELECT sc.ScenarioID, sc.ProjectID, sc.BookID, sc.ScenarioName, sc.Description, sc.BaseCreatedDate,
        sc.IsAppliedToMain, sc.IsArchived, sc.ArchivedDate, sc.ArchivedReason,
        sc.CreatedDate, sc.UpdatedDate, p.ProjectName, b.BookTitle,
        (SELECT COUNT(1) FROM dbo.ScenarioSceneOrder sso WHERE sso.ScenarioID = sc.ScenarioID) AS SceneCount,
        (SELECT COUNT(1) FROM dbo.ScenarioWarnings sw WHERE sw.ScenarioID = sc.ScenarioID) AS WarningCount
    FROM dbo.Scenarios sc
    INNER JOIN dbo.Projects p ON p.ProjectID = sc.ProjectID
    LEFT JOIN dbo.Books b ON b.BookID = sc.BookID
    WHERE sc.ProjectID = @ProjectID
    ORDER BY sc.IsArchived, sc.UpdatedDate DESC;

    SELECT sso.ScenarioSceneOrderID, sso.ScenarioID, sso.SceneID, sso.ProposedChapterID, sso.ProposedSortOrder,
        sso.Notes, sso.CreatedDate, sso.UpdatedDate
    FROM dbo.ScenarioSceneOrder sso
    INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = sso.ScenarioID
    WHERE sc.ProjectID = @ProjectID
    ORDER BY sso.ScenarioID, sso.ProposedSortOrder;

    SELECT svr.ScenarioValidationRunID, svr.ScenarioID, svr.StartedDate, svr.CompletedDate, svr.WarningCount
    FROM dbo.ScenarioValidationRuns svr
    INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = svr.ScenarioID
    WHERE sc.ProjectID = @ProjectID
    ORDER BY svr.StartedDate DESC;

    SELECT sw.ScenarioWarningID, sw.ScenarioValidationRunID, sw.ScenarioID, sw.SceneID, sw.EntityType,
        sw.EntityID, sw.WarningTypeName, sw.SeverityName, sw.Message, sw.Details, sw.CreatedDate
    FROM dbo.ScenarioWarnings sw
    INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = sw.ScenarioID
    WHERE sc.ProjectID = @ProjectID
    ORDER BY sw.CreatedDate DESC, sw.ScenarioWarningID;

    SELECT GenreMetricPresetID, PresetKey, PresetName, Description, SortOrder, IsActive
    FROM dbo.GenreMetricPresets
    ORDER BY SortOrder, PresetName;

    SELECT gpmm.GenreMetricPresetID, gmp.PresetKey, gmp.PresetName,
        gpmm.MetricTypeID, mt.MetricName, gpmm.SortOrder
    FROM dbo.GenreMetricPresetMetrics gpmm
    INNER JOIN dbo.GenreMetricPresets gmp ON gmp.GenreMetricPresetID = gpmm.GenreMetricPresetID
    INNER JOIN dbo.SceneMetricTypes mt ON mt.MetricTypeID = gpmm.MetricTypeID
    WHERE EXISTS
    (
        SELECT 1
        FROM dbo.SceneMetricTypes projectMetric
        WHERE projectMetric.MetricTypeID = gpmm.MetricTypeID
            AND
            (
                projectMetric.ProjectID IS NULL
                OR projectMetric.ProjectID = @ProjectID
                OR EXISTS
                (
                    SELECT 1
                    FROM dbo.ProjectTimelineMetricSettings ptms
                    WHERE ptms.ProjectID = @ProjectID
                        AND ptms.MetricTypeID = projectMetric.MetricTypeID
                )
            )
    )
    ORDER BY gmp.SortOrder, gpmm.SortOrder, mt.MetricName;
END;
GO