SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.PlotImportance', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PlotImportance
    (
        PlotImportanceID int NOT NULL CONSTRAINT PK_PlotImportance PRIMARY KEY,
        ImportanceName nvarchar(100) NOT NULL,
        SortOrder int NOT NULL,
        IsActive bit NOT NULL CONSTRAINT DF_PlotImportance_IsActive DEFAULT 1,
        CONSTRAINT UQ_PlotImportance_ImportanceName UNIQUE (ImportanceName)
    );
END;
GO

MERGE dbo.PlotImportance AS target
USING (VALUES
    (1, N'Primary', 10),
    (2, N'Secondary', 20),
    (3, N'Minor', 30)
) AS source (PlotImportanceID, ImportanceName, SortOrder)
ON target.PlotImportanceID = source.PlotImportanceID
WHEN MATCHED THEN
    UPDATE SET ImportanceName = source.ImportanceName, SortOrder = source.SortOrder, IsActive = 1
WHEN NOT MATCHED THEN
    INSERT (PlotImportanceID, ImportanceName, SortOrder, IsActive)
    VALUES (source.PlotImportanceID, source.ImportanceName, source.SortOrder, 1);
GO

IF OBJECT_ID(N'dbo.PlotEventTypes', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PlotEventTypes
    (
        PlotEventTypeID int NOT NULL CONSTRAINT PK_PlotEventTypes PRIMARY KEY,
        TypeName nvarchar(100) NOT NULL,
        SortOrder int NOT NULL,
        IsActive bit NOT NULL CONSTRAINT DF_PlotEventTypes_IsActive DEFAULT 1,
        CONSTRAINT UQ_PlotEventTypes_TypeName UNIQUE (TypeName)
    );
END;
GO

MERGE dbo.PlotEventTypes AS target
USING (VALUES
    (1, N'Start', 10),
    (2, N'Progress', 20),
    (3, N'Reveal', 30),
    (4, N'Twist', 40),
    (5, N'Branch', 50),
    (6, N'Split', 60),
    (7, N'Merge', 70),
    (8, N'Resolve', 80),
    (9, N'Abandon', 90)
) AS source (PlotEventTypeID, TypeName, SortOrder)
ON target.PlotEventTypeID = source.PlotEventTypeID
WHEN MATCHED THEN
    UPDATE SET TypeName = source.TypeName, SortOrder = source.SortOrder, IsActive = 1
WHEN NOT MATCHED THEN
    INSERT (PlotEventTypeID, TypeName, SortOrder, IsActive)
    VALUES (source.PlotEventTypeID, source.TypeName, source.SortOrder, 1);
GO

IF COL_LENGTH(N'dbo.PlotLines', N'PlotImportanceID') IS NULL
BEGIN
    ALTER TABLE dbo.PlotLines ADD PlotImportanceID int NULL;
END;
GO

IF COL_LENGTH(N'dbo.PlotLines', N'EmergesFromPlotLineID') IS NULL
BEGIN
    ALTER TABLE dbo.PlotLines ADD EmergesFromPlotLineID int NULL;
END;
GO

IF COL_LENGTH(N'dbo.PlotLines', N'IsMainPlot') IS NOT NULL
BEGIN
    EXEC (N'UPDATE dbo.PlotLines SET PlotImportanceID = CASE WHEN IsMainPlot = 1 THEN 1 ELSE 2 END WHERE PlotImportanceID IS NULL;');
END
ELSE
BEGIN
    UPDATE dbo.PlotLines SET PlotImportanceID = 2 WHERE PlotImportanceID IS NULL;
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE name = N'DF_PlotLines_PlotImportanceID' AND parent_object_id = OBJECT_ID(N'dbo.PlotLines'))
BEGIN
    ALTER TABLE dbo.PlotLines ADD CONSTRAINT DF_PlotLines_PlotImportanceID DEFAULT 2 FOR PlotImportanceID;
END;
GO

ALTER TABLE dbo.PlotLines ALTER COLUMN PlotImportanceID int NOT NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_PlotLines_PlotImportance')
BEGIN
    ALTER TABLE dbo.PlotLines WITH CHECK ADD CONSTRAINT FK_PlotLines_PlotImportance
        FOREIGN KEY (PlotImportanceID) REFERENCES dbo.PlotImportance(PlotImportanceID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_PlotLines_EmergesFromPlotLine')
BEGIN
    ALTER TABLE dbo.PlotLines WITH CHECK ADD CONSTRAINT FK_PlotLines_EmergesFromPlotLine
        FOREIGN KEY (EmergesFromPlotLineID) REFERENCES dbo.PlotLines(PlotLineID);
END;
GO

IF COL_LENGTH(N'dbo.PlotLines', N'IsMainPlot') IS NOT NULL
BEGIN
    DECLARE @DropIsMainPlotDefault nvarchar(max);
    SELECT @DropIsMainPlotDefault = N'ALTER TABLE dbo.PlotLines DROP CONSTRAINT ' + QUOTENAME(dc.name)
    FROM sys.default_constraints dc
    INNER JOIN sys.columns c ON c.default_object_id = dc.object_id
    WHERE dc.parent_object_id = OBJECT_ID(N'dbo.PlotLines') AND c.name = N'IsMainPlot';

    IF @DropIsMainPlotDefault IS NOT NULL EXEC (@DropIsMainPlotDefault);
    ALTER TABLE dbo.PlotLines DROP COLUMN IsMainPlot;
END;
GO

IF COL_LENGTH(N'dbo.ThreadEvents', N'PlotEventTypeID') IS NULL
BEGIN
    ALTER TABLE dbo.ThreadEvents ADD PlotEventTypeID int NULL;
END;
GO

IF COL_LENGTH(N'dbo.ThreadEvents', N'TargetPlotLineID') IS NULL
BEGIN
    ALTER TABLE dbo.ThreadEvents ADD TargetPlotLineID int NULL;
END;
GO

UPDATE dbo.ThreadEvents
SET PlotEventTypeID = 2
WHERE PlotEventTypeID IS NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE name = N'DF_ThreadEvents_PlotEventTypeID' AND parent_object_id = OBJECT_ID(N'dbo.ThreadEvents'))
BEGIN
    ALTER TABLE dbo.ThreadEvents ADD CONSTRAINT DF_ThreadEvents_PlotEventTypeID DEFAULT 2 FOR PlotEventTypeID;
END;
GO

ALTER TABLE dbo.ThreadEvents ALTER COLUMN PlotEventTypeID int NOT NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_ThreadEvents_PlotEventTypes')
BEGIN
    ALTER TABLE dbo.ThreadEvents WITH CHECK ADD CONSTRAINT FK_ThreadEvents_PlotEventTypes
        FOREIGN KEY (PlotEventTypeID) REFERENCES dbo.PlotEventTypes(PlotEventTypeID);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_ThreadEvents_TargetPlotLines')
BEGIN
    ALTER TABLE dbo.ThreadEvents WITH CHECK ADD CONSTRAINT FK_ThreadEvents_TargetPlotLines
        FOREIGN KEY (TargetPlotLineID) REFERENCES dbo.PlotLines(PlotLineID);
END;
GO

IF OBJECT_ID(N'dbo.PlotEventTargetPlotLines', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PlotEventTargetPlotLines
    (
        PlotEventID int NOT NULL,
        PlotLineID int NOT NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_PlotEventTargetPlotLines_CreatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT PK_PlotEventTargetPlotLines PRIMARY KEY (PlotEventID, PlotLineID),
        CONSTRAINT FK_PlotEventTargetPlotLines_ThreadEvents FOREIGN KEY (PlotEventID) REFERENCES dbo.ThreadEvents(ThreadEventID) ON DELETE CASCADE,
        CONSTRAINT FK_PlotEventTargetPlotLines_PlotLines FOREIGN KEY (PlotLineID) REFERENCES dbo.PlotLines(PlotLineID)
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PlotLines_PlotImportanceID' AND object_id = OBJECT_ID(N'dbo.PlotLines'))
    CREATE INDEX IX_PlotLines_PlotImportanceID ON dbo.PlotLines(ProjectID, PlotImportanceID, IsArchived);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ThreadEvents_PlotEventTypeID' AND object_id = OBJECT_ID(N'dbo.ThreadEvents'))
    CREATE INDEX IX_ThreadEvents_PlotEventTypeID ON dbo.ThreadEvents(PlotEventTypeID, TargetPlotLineID);
GO

CREATE OR ALTER PROCEDURE dbo.PlotLookup_All
AS
BEGIN
    SET NOCOUNT ON;
    SELECT PlotLineTypeID, TypeName, SortOrder, IsActive FROM dbo.PlotLineTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT PlotImportanceID, ImportanceName, SortOrder, IsActive FROM dbo.PlotImportance WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT ThreadTypeID, TypeName, SortOrder, IsActive FROM dbo.ThreadTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT ThreadStatusID, StatusName, SortOrder, IsOpenStatus, IsResolvedStatus, IsActive FROM dbo.ThreadStatuses WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT ThreadEventTypeID, TypeName, MarkerText, SortOrder, IsActive FROM dbo.ThreadEventTypes WHERE IsActive = 1 ORDER BY SortOrder;
    SELECT PlotEventTypeID, TypeName, SortOrder, IsActive FROM dbo.PlotEventTypes WHERE IsActive = 1 ORDER BY SortOrder;
END;
GO

CREATE OR ALTER PROCEDURE dbo.PlotLine_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT pl.PlotLineID, pl.ProjectID, pl.BookID, b.BookTitle, pl.PlotLineName, pl.PlotLineTypeID,
        plt.TypeName AS PlotLineTypeName, pl.PlotImportanceID, pi.ImportanceName AS PlotImportanceName,
        pl.Description, pl.ParentPlotLineID, parent.PlotLineName AS ParentPlotLineName,
        pl.EmergesFromPlotLineID, emerges.PlotLineName AS EmergesFromPlotLineName,
        pl.SortOrder, pl.Colour, pl.IsVisibleOnTimeline, pl.CreatedDate, pl.UpdatedDate, pl.IsArchived
    FROM dbo.PlotLines pl
    INNER JOIN dbo.PlotLineTypes plt ON plt.PlotLineTypeID = pl.PlotLineTypeID
    INNER JOIN dbo.PlotImportance pi ON pi.PlotImportanceID = pl.PlotImportanceID
    LEFT JOIN dbo.Books b ON b.BookID = pl.BookID
    LEFT JOIN dbo.PlotLines parent ON parent.PlotLineID = pl.ParentPlotLineID
    LEFT JOIN dbo.PlotLines emerges ON emerges.PlotLineID = pl.EmergesFromPlotLineID
    WHERE pl.ProjectID = @ProjectID AND pl.IsArchived = 0
    ORDER BY pl.SortOrder, pl.PlotLineName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.PlotLine_Get
    @PlotLineID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT pl.PlotLineID, pl.ProjectID, pl.BookID, b.BookTitle, pl.PlotLineName, pl.PlotLineTypeID,
        plt.TypeName AS PlotLineTypeName, pl.PlotImportanceID, pi.ImportanceName AS PlotImportanceName,
        pl.Description, pl.ParentPlotLineID, parent.PlotLineName AS ParentPlotLineName,
        pl.EmergesFromPlotLineID, emerges.PlotLineName AS EmergesFromPlotLineName,
        pl.SortOrder, pl.Colour, pl.IsVisibleOnTimeline, pl.CreatedDate, pl.UpdatedDate, pl.IsArchived
    FROM dbo.PlotLines pl
    INNER JOIN dbo.PlotLineTypes plt ON plt.PlotLineTypeID = pl.PlotLineTypeID
    INNER JOIN dbo.PlotImportance pi ON pi.PlotImportanceID = pl.PlotImportanceID
    LEFT JOIN dbo.Books b ON b.BookID = pl.BookID
    LEFT JOIN dbo.PlotLines parent ON parent.PlotLineID = pl.ParentPlotLineID
    LEFT JOIN dbo.PlotLines emerges ON emerges.PlotLineID = pl.EmergesFromPlotLineID
    WHERE pl.PlotLineID = @PlotLineID AND pl.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.PlotLine_Save
    @PlotLineID int = NULL,
    @ProjectID int,
    @BookID int = NULL,
    @PlotLineName nvarchar(200),
    @PlotLineTypeID int,
    @PlotImportanceID int = 2,
    @Description nvarchar(max) = NULL,
    @ParentPlotLineID int = NULL,
    @EmergesFromPlotLineID int = NULL,
    @SortOrder int = NULL,
    @Colour nvarchar(20),
    @IsVisibleOnTimeline bit
AS
BEGIN
    SET NOCOUNT ON;
    IF @PlotLineID IS NULL OR @PlotLineID = 0
    BEGIN
        DECLARE @NextSortOrder int = COALESCE(@SortOrder, ISNULL((SELECT MAX(SortOrder) FROM dbo.PlotLines WHERE ProjectID = @ProjectID), 0) + 10);
        INSERT dbo.PlotLines (ProjectID, BookID, PlotLineName, PlotLineTypeID, PlotImportanceID, Description, ParentPlotLineID, EmergesFromPlotLineID, SortOrder, Colour, IsVisibleOnTimeline)
        VALUES (@ProjectID, @BookID, @PlotLineName, @PlotLineTypeID, @PlotImportanceID, @Description, @ParentPlotLineID, @EmergesFromPlotLineID, @NextSortOrder, @Colour, @IsVisibleOnTimeline);
        SELECT CAST(SCOPE_IDENTITY() AS int);
        RETURN;
    END;

    UPDATE dbo.PlotLines
    SET BookID = @BookID,
        PlotLineName = @PlotLineName,
        PlotLineTypeID = @PlotLineTypeID,
        PlotImportanceID = @PlotImportanceID,
        Description = @Description,
        ParentPlotLineID = @ParentPlotLineID,
        EmergesFromPlotLineID = @EmergesFromPlotLineID,
        SortOrder = COALESCE(@SortOrder, SortOrder),
        Colour = @Colour,
        IsVisibleOnTimeline = @IsVisibleOnTimeline,
        UpdatedDate = SYSUTCDATETIME()
    WHERE PlotLineID = @PlotLineID;

    SELECT @PlotLineID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ThreadEvent_ListByScene
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT te.ThreadEventID, te.PlotThreadID, pt.ThreadTitle, pt.PlotLineID, pl.PlotLineName, pl.Colour,
        te.SceneID, te.EventTypeID, tet.TypeName AS EventTypeName, tet.MarkerText,
        te.PlotEventTypeID, pet.TypeName AS PlotEventTypeName, te.TargetPlotLineID, target.PlotLineName AS TargetPlotLineName,
        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
    INNER JOIN dbo.PlotEventTypes pet ON pet.PlotEventTypeID = te.PlotEventTypeID
    LEFT JOIN dbo.PlotLines target ON target.PlotLineID = te.TargetPlotLineID
    WHERE te.SceneID = @SceneID AND pt.IsArchived = 0 AND pl.IsArchived = 0
    ORDER BY pl.SortOrder, pt.Importance DESC, te.CreatedDate;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ThreadEvent_ListByPlotThread
    @PlotThreadID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT te.ThreadEventID, te.PlotThreadID, pt.ThreadTitle, pt.PlotLineID, pl.PlotLineName, pl.Colour,
        te.SceneID, te.EventTypeID, tet.TypeName AS EventTypeName, tet.MarkerText,
        te.PlotEventTypeID, pet.TypeName AS PlotEventTypeName, te.TargetPlotLineID, target.PlotLineName AS TargetPlotLineName,
        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
    INNER JOIN dbo.PlotEventTypes pet ON pet.PlotEventTypeID = te.PlotEventTypeID
    LEFT JOIN dbo.PlotLines target ON target.PlotLineID = te.TargetPlotLineID
    WHERE te.PlotThreadID = @PlotThreadID
    ORDER BY te.CreatedDate;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ThreadEvent_Save
    @ThreadEventID int = NULL,
    @PlotThreadID int,
    @SceneID int,
    @EventTypeID int,
    @PlotEventTypeID int = 2,
    @TargetPlotLineID int = NULL,
    @EventTitle nvarchar(200),
    @EventDescription nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF @ThreadEventID IS NULL OR @ThreadEventID = 0
    BEGIN
        INSERT dbo.ThreadEvents (PlotThreadID, SceneID, EventTypeID, PlotEventTypeID, TargetPlotLineID, EventTitle, EventDescription)
        VALUES (@PlotThreadID, @SceneID, @EventTypeID, @PlotEventTypeID, @TargetPlotLineID, @EventTitle, @EventDescription);
        SET @ThreadEventID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.ThreadEvents
        SET PlotThreadID = @PlotThreadID,
            SceneID = @SceneID,
            EventTypeID = @EventTypeID,
            PlotEventTypeID = @PlotEventTypeID,
            TargetPlotLineID = @TargetPlotLineID,
            EventTitle = @EventTitle,
            EventDescription = @EventDescription,
            UpdatedDate = SYSUTCDATETIME()
        WHERE ThreadEventID = @ThreadEventID;
    END;

    DECLARE @EventTypeName nvarchar(100);
    SELECT @EventTypeName = TypeName FROM dbo.ThreadEventTypes WHERE ThreadEventTypeID = @EventTypeID;

    IF @EventTypeName = N'Introduced'
    BEGIN
        UPDATE pt
        SET IntroducedSceneID = COALESCE(IntroducedSceneID, @SceneID),
            ThreadStatusID = CASE WHEN ts.StatusName = N'Planned' THEN introduced.ThreadStatusID ELSE pt.ThreadStatusID END,
            UpdatedDate = SYSUTCDATETIME()
        FROM dbo.PlotThreads pt
        INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
        CROSS APPLY (SELECT ThreadStatusID FROM dbo.ThreadStatuses WHERE StatusName = N'Introduced') introduced
        WHERE pt.PlotThreadID = @PlotThreadID;
    END;

    IF @EventTypeName IN (N'Resolved', N'Payoff')
    BEGIN
        UPDATE pt
        SET ActualResolutionSceneID = COALESCE(ActualResolutionSceneID, @SceneID),
            ThreadStatusID = CASE WHEN @EventTypeName = N'Resolved' THEN resolved.ThreadStatusID ELSE pt.ThreadStatusID END,
            UpdatedDate = SYSUTCDATETIME()
        FROM dbo.PlotThreads pt
        CROSS APPLY (SELECT ThreadStatusID FROM dbo.ThreadStatuses WHERE StatusName = N'Resolved') resolved
        WHERE pt.PlotThreadID = @PlotThreadID;
    END;

    SELECT @ThreadEventID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Timeline_GetByProject
    @ProjectID int,
    @BookID int = NULL,
    @IncludeMetrics bit = 1,
    @SelectedMetricTypeIDs nvarchar(max) = NULL,
    @IncludePlotLines bit = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MetricSelection TABLE (MetricTypeID int NOT NULL PRIMARY KEY);
    INSERT @MetricSelection (MetricTypeID)
    SELECT DISTINCT TRY_CONVERT(int, value)
    FROM string_split(COALESCE(@SelectedMetricTypeIDs, N''), N',')
    WHERE TRY_CONVERT(int, value) IS NOT NULL;

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

    SELECT BookID, ProjectID, BookTitle, BookNumber, Description, SortOrder, CreatedDate, UpdatedDate, IsArchived
    FROM dbo.Books
    WHERE ProjectID = @ProjectID AND IsArchived = 0 AND (@BookID IS NULL OR BookID = @BookID)
    ORDER BY SortOrder, BookNumber;

    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
    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 AND b.IsArchived = 0 AND c.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
    ORDER BY b.SortOrder, c.SortOrder, c.ChapterNumber;

    SELECT s.SceneID, s.ChapterID, s.SceneNumber, s.SceneTitle, s.Summary, s.POVCharacterID,
        s.PrimaryLocationID, location.LocationName AS PrimaryLocationName, location.LocationPath 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
    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
    LEFT JOIN dbo.DurationUnits du ON du.DurationUnitID = s.DurationUnitID
    INNER JOIN dbo.TimeConfidences tc ON tc.TimeConfidenceID = s.TimeConfidenceID
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
    LEFT JOIN dbo.LocationPaths location ON location.LocationID = s.PrimaryLocationID
    WHERE b.ProjectID = @ProjectID AND b.IsArchived = 0 AND c.IsArchived = 0 AND s.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
    ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, s.SceneNumber;

    SELECT sp.SceneID, spt.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 AND b.IsArchived = 0 AND c.IsArchived = 0 AND s.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
    ORDER BY sp.SceneID, spt.SortOrder;

    SELECT MetricTypeID, ProjectID, MetricName, Description, MinValue, MaxValue, DefaultValue, SortOrder, IsActive
    FROM dbo.SceneMetricTypes
    WHERE @IncludeMetrics = 1
        AND IsActive = 1
        AND (ProjectID IS NULL OR ProjectID = @ProjectID)
        AND (NOT EXISTS (SELECT 1 FROM @MetricSelection) OR MetricTypeID IN (SELECT MetricTypeID FROM @MetricSelection))
    ORDER BY SortOrder, MetricName;

    SELECT s.SceneID, mt.MetricTypeID, mt.MetricName, COALESCE(smv.Value, mt.DefaultValue) AS Value, smv.Notes
    FROM dbo.Scenes s
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    CROSS JOIN dbo.SceneMetricTypes mt
    LEFT JOIN dbo.SceneMetricValues smv ON smv.SceneID = s.SceneID AND smv.MetricTypeID = mt.MetricTypeID
    WHERE b.ProjectID = @ProjectID
        AND @IncludeMetrics = 1
        AND b.IsArchived = 0
        AND c.IsArchived = 0
        AND s.IsArchived = 0
        AND mt.IsActive = 1
        AND (mt.ProjectID IS NULL OR mt.ProjectID = @ProjectID)
        AND (@BookID IS NULL OR b.BookID = @BookID)
        AND (NOT EXISTS (SELECT 1 FROM @MetricSelection) OR mt.MetricTypeID IN (SELECT MetricTypeID FROM @MetricSelection))
    ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, mt.SortOrder;

    SELECT pl.PlotLineID, pl.ProjectID, pl.BookID, b.BookTitle, pl.PlotLineName, pl.PlotLineTypeID,
        plt.TypeName AS PlotLineTypeName, pl.PlotImportanceID, pi.ImportanceName AS PlotImportanceName,
        pl.Description, pl.ParentPlotLineID, parent.PlotLineName AS ParentPlotLineName,
        pl.EmergesFromPlotLineID, emerges.PlotLineName AS EmergesFromPlotLineName,
        pl.SortOrder, pl.Colour, pl.IsVisibleOnTimeline, pl.CreatedDate, pl.UpdatedDate, pl.IsArchived
    FROM dbo.PlotLines pl
    INNER JOIN dbo.PlotLineTypes plt ON plt.PlotLineTypeID = pl.PlotLineTypeID
    INNER JOIN dbo.PlotImportance pi ON pi.PlotImportanceID = pl.PlotImportanceID
    LEFT JOIN dbo.Books b ON b.BookID = pl.BookID
    LEFT JOIN dbo.PlotLines parent ON parent.PlotLineID = pl.ParentPlotLineID
    LEFT JOIN dbo.PlotLines emerges ON emerges.PlotLineID = pl.EmergesFromPlotLineID
    WHERE @IncludePlotLines = 1
        AND pl.ProjectID = @ProjectID
        AND pl.IsArchived = 0
        AND pl.IsVisibleOnTimeline = 1
        AND (@BookID IS NULL OR pl.BookID IS NULL OR pl.BookID = @BookID)
    ORDER BY pl.SortOrder, pl.PlotLineName;

    SELECT te.ThreadEventID, te.PlotThreadID, pt.ThreadTitle, pt.PlotLineID, pl.PlotLineName, pl.Colour,
        te.SceneID, te.EventTypeID, tet.TypeName AS EventTypeName, tet.MarkerText,
        te.PlotEventTypeID, pet.TypeName AS PlotEventTypeName, te.TargetPlotLineID, target.PlotLineName AS TargetPlotLineName,
        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
    INNER JOIN dbo.PlotEventTypes pet ON pet.PlotEventTypeID = te.PlotEventTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = te.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    LEFT JOIN dbo.PlotLines target ON target.PlotLineID = te.TargetPlotLineID
    WHERE @IncludePlotLines = 1
        AND b.ProjectID = @ProjectID
        AND b.IsArchived = 0
        AND c.IsArchived = 0
        AND s.IsArchived = 0
        AND pl.IsArchived = 0
        AND pl.IsVisibleOnTimeline = 1
        AND pt.IsArchived = 0
        AND (@BookID IS NULL OR b.BookID = @BookID)
    ORDER BY pl.SortOrder, b.SortOrder, c.SortOrder, s.SortOrder, te.CreatedDate;
END;
GO


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

    IF @UserID IS NOT NULL
        AND NOT EXISTS
        (
            SELECT 1
            FROM dbo.ProjectUserAccess
            WHERE ProjectID = @ProjectID
                AND UserID = @UserID
                AND AccessRole = N'Owner'
                AND IsActive = 1
        )
        SET @ProjectID = -1;

    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.PlotImportanceID, pi.ImportanceName AS PlotImportanceName,
        pl.Description, pl.ParentPlotLineID, parent.PlotLineName AS ParentPlotLineName,
        pl.EmergesFromPlotLineID, emerges.PlotLineName AS EmergesFromPlotLineName,
        pl.SortOrder, pl.Colour, 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
    INNER JOIN dbo.PlotImportance pi ON pi.PlotImportanceID = pl.PlotImportanceID
    LEFT JOIN dbo.Books b ON b.BookID = pl.BookID
    LEFT JOIN dbo.PlotLines parent ON parent.PlotLineID = pl.ParentPlotLineID
    LEFT JOIN dbo.PlotLines emerges ON emerges.PlotLineID = pl.EmergesFromPlotLineID
    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.PlotEventTypeID, pet.TypeName AS PlotEventTypeName, te.TargetPlotLineID, target.PlotLineName AS TargetPlotLineName,
        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
    INNER JOIN dbo.PlotEventTypes pet ON pet.PlotEventTypeID = te.PlotEventTypeID
    LEFT JOIN dbo.PlotLines target ON target.PlotLineID = te.TargetPlotLineID
    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;

    SELECT pua.ProjectUserAccessID, pua.ProjectID, pua.UserID, u.Email, u.DisplayName,
        pua.AccessRole, pua.InvitedByUserID, invitedBy.Email AS InvitedByEmail,
        pua.InvitedDateUTC, pua.AcceptedDateUTC, pua.IsActive
    FROM dbo.ProjectUserAccess pua
    INNER JOIN dbo.AppUser u ON u.UserID = pua.UserID
    LEFT JOIN dbo.AppUser invitedBy ON invitedBy.UserID = pua.InvitedByUserID
    WHERE pua.ProjectID = @ProjectID
        AND pua.IsActive = 1
    ORDER BY CASE WHEN pua.AccessRole = N'Owner' THEN 0 ELSE 1 END, u.Email;

    SELECT UserFileID, UserID, ProjectID, EntityType, EntityID, FileName, OriginalFileName, ContentType,
        FileSizeBytes, StoragePath, UploadedDateUTC, DeletedDateUTC, IsDeleted
    FROM dbo.UserFile
    WHERE ProjectID = @ProjectID
    ORDER BY IsDeleted, UploadedDateUTC, UserFileID;
END;
GO