PlotDirector/PlotLine/Sql/019_Phase1S_Scenarios.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.Scenarios', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Scenarios
    (
        ScenarioID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Scenarios PRIMARY KEY,
        ProjectID int NOT NULL,
        BookID int NULL,
        ScenarioName nvarchar(200) NOT NULL,
        Description nvarchar(max) NULL,
        BaseCreatedDate datetime2 NOT NULL CONSTRAINT DF_Scenarios_BaseCreatedDate DEFAULT SYSUTCDATETIME(),
        IsAppliedToMain bit NOT NULL CONSTRAINT DF_Scenarios_IsAppliedToMain DEFAULT 0,
        IsArchived bit NOT NULL CONSTRAINT DF_Scenarios_IsArchived DEFAULT 0,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_Scenarios_CreatedDate DEFAULT SYSUTCDATETIME(),
        UpdatedDate datetime2 NOT NULL CONSTRAINT DF_Scenarios_UpdatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_Scenarios_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
        CONSTRAINT FK_Scenarios_Books FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID)
    );
END;
GO

IF OBJECT_ID(N'dbo.ScenarioSceneOrder', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ScenarioSceneOrder
    (
        ScenarioSceneOrderID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ScenarioSceneOrder PRIMARY KEY,
        ScenarioID int NOT NULL,
        SceneID int NOT NULL,
        ProposedChapterID int NULL,
        ProposedSortOrder int NOT NULL,
        Notes nvarchar(max) NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_ScenarioSceneOrder_CreatedDate DEFAULT SYSUTCDATETIME(),
        UpdatedDate datetime2 NOT NULL CONSTRAINT DF_ScenarioSceneOrder_UpdatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_ScenarioSceneOrder_Scenarios FOREIGN KEY (ScenarioID) REFERENCES dbo.Scenarios(ScenarioID),
        CONSTRAINT FK_ScenarioSceneOrder_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
        CONSTRAINT FK_ScenarioSceneOrder_Chapters FOREIGN KEY (ProposedChapterID) REFERENCES dbo.Chapters(ChapterID),
        CONSTRAINT UQ_ScenarioSceneOrder_ScenarioScene UNIQUE (ScenarioID, SceneID)
    );
END;
GO

IF OBJECT_ID(N'dbo.ScenarioValidationRuns', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ScenarioValidationRuns
    (
        ScenarioValidationRunID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ScenarioValidationRuns PRIMARY KEY,
        ScenarioID int NOT NULL,
        StartedDate datetime2 NOT NULL CONSTRAINT DF_ScenarioValidationRuns_StartedDate DEFAULT SYSUTCDATETIME(),
        CompletedDate datetime2 NULL,
        WarningCount int NOT NULL CONSTRAINT DF_ScenarioValidationRuns_WarningCount DEFAULT 0,
        CONSTRAINT FK_ScenarioValidationRuns_Scenarios FOREIGN KEY (ScenarioID) REFERENCES dbo.Scenarios(ScenarioID)
    );
END;
GO

IF OBJECT_ID(N'dbo.ScenarioWarnings', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ScenarioWarnings
    (
        ScenarioWarningID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ScenarioWarnings PRIMARY KEY,
        ScenarioValidationRunID int NOT NULL,
        ScenarioID int NOT NULL,
        SceneID int NULL,
        EntityType nvarchar(80) NOT NULL,
        EntityID int NULL,
        WarningTypeName nvarchar(150) NOT NULL,
        SeverityName nvarchar(50) NOT NULL,
        Message nvarchar(500) NOT NULL,
        Details nvarchar(max) NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_ScenarioWarnings_CreatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_ScenarioWarnings_Runs FOREIGN KEY (ScenarioValidationRunID) REFERENCES dbo.ScenarioValidationRuns(ScenarioValidationRunID),
        CONSTRAINT FK_ScenarioWarnings_Scenarios FOREIGN KEY (ScenarioID) REFERENCES dbo.Scenarios(ScenarioID),
        CONSTRAINT FK_ScenarioWarnings_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID)
    );
END;
GO

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

    SELECT sc.ScenarioID, sc.ProjectID, sc.BookID, sc.ScenarioName, sc.Description, sc.BaseCreatedDate,
        sc.IsAppliedToMain, sc.IsArchived, sc.CreatedDate, sc.UpdatedDate,
        p.ProjectName, b.BookTitle,
        COUNT(DISTINCT sso.SceneID) AS SceneCount,
        COUNT(DISTINCT sw.ScenarioWarningID) 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
    LEFT JOIN dbo.ScenarioSceneOrder sso ON sso.ScenarioID = sc.ScenarioID
    LEFT JOIN dbo.ScenarioWarnings sw ON sw.ScenarioID = sc.ScenarioID
    WHERE sc.ProjectID = @ProjectID AND sc.IsArchived = 0
    GROUP BY sc.ScenarioID, sc.ProjectID, sc.BookID, sc.ScenarioName, sc.Description, sc.BaseCreatedDate,
        sc.IsAppliedToMain, sc.IsArchived, sc.CreatedDate, sc.UpdatedDate, p.ProjectName, b.BookTitle
    ORDER BY sc.UpdatedDate DESC, sc.ScenarioName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_Get
    @ScenarioID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT sc.ScenarioID, sc.ProjectID, sc.BookID, sc.ScenarioName, sc.Description, sc.BaseCreatedDate,
        sc.IsAppliedToMain, sc.IsArchived, sc.CreatedDate, sc.UpdatedDate,
        p.ProjectName, b.BookTitle,
        (SELECT COUNT(*) FROM dbo.ScenarioSceneOrder WHERE ScenarioID = sc.ScenarioID) AS SceneCount,
        (SELECT COUNT(*) FROM dbo.ScenarioWarnings WHERE 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.ScenarioID = @ScenarioID AND sc.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_Create
    @ProjectID int,
    @BookID int = NULL,
    @ScenarioName nvarchar(200),
    @Description nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.Scenarios (ProjectID, BookID, ScenarioName, Description)
    VALUES (@ProjectID, @BookID, @ScenarioName, @Description);

    DECLARE @ScenarioID int = CAST(SCOPE_IDENTITY() AS int);

    INSERT dbo.ScenarioSceneOrder (ScenarioID, SceneID, ProposedChapterID, ProposedSortOrder)
    SELECT @ScenarioID, nso.SceneID, nso.ChapterID,
        ROW_NUMBER() OVER (ORDER BY nso.GlobalSceneIndex) * 10
    FROM dbo.NarrativeSceneOrder nso
    WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
    ORDER BY nso.GlobalSceneIndex;

    SELECT @ScenarioID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_Timeline
    @ScenarioID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int = (SELECT ProjectID FROM dbo.Scenarios WHERE ScenarioID = @ScenarioID);
    DECLARE @BookID int = (SELECT BookID FROM dbo.Scenarios WHERE ScenarioID = @ScenarioID);

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

    SELECT sso.ScenarioSceneOrderID, sso.ScenarioID, sso.SceneID, s.ChapterID AS OriginalChapterID,
        ISNULL(sso.ProposedChapterID, s.ChapterID) AS ProposedChapterID, sso.ProposedSortOrder, sso.Notes,
        ch.ChapterNumber, ch.ChapterTitle, s.SceneNumber, s.SceneTitle, s.Summary, rs.StatusName AS RevisionStatusName,
        COALESCE(NULLIF(s.RelativeTimeText, N''), CONVERT(nvarchar(20), s.StartDateTime, 106), tm.TimeModeName) AS TimeLabel,
        threadData.ThreadLabels, assetData.AssetLabels, characterData.CharacterLabels, loc.LocationPath AS LocationLabel,
        (SELECT COUNT(*) FROM dbo.ScenarioWarnings sw WHERE sw.ScenarioID = @ScenarioID AND sw.SceneID = s.SceneID) AS WarningCount
    FROM dbo.ScenarioSceneOrder sso
    INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
    INNER JOIN dbo.Chapters ch ON ch.ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID)
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
    INNER JOIN dbo.TimeModes tm ON tm.TimeModeID = s.TimeModeID
    LEFT JOIN dbo.LocationPaths loc ON loc.LocationID = s.PrimaryLocationID
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), pt.ThreadTitle), N', ') AS ThreadLabels
        FROM dbo.ThreadEvents te INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
        WHERE te.SceneID = s.SceneID
    ) threadData
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), sa.AssetName), N', ') AS AssetLabels
        FROM dbo.AssetEvents ae INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
        WHERE ae.SceneID = s.SceneID
    ) assetData
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), c.CharacterName), N', ') AS CharacterLabels
        FROM dbo.SceneCharacters sc INNER JOIN dbo.Characters c ON c.CharacterID = sc.CharacterID
        WHERE sc.SceneID = s.SceneID
    ) characterData
    WHERE sso.ScenarioID = @ScenarioID
    ORDER BY ch.SortOrder, ch.ChapterNumber, sso.ProposedSortOrder, s.SceneID;

    SELECT ScenarioWarningID, ScenarioValidationRunID, ScenarioID, SceneID, EntityType, EntityID, WarningTypeName, SeverityName, Message, Details, CreatedDate
    FROM dbo.ScenarioWarnings
    WHERE ScenarioID = @ScenarioID
    ORDER BY CASE SeverityName WHEN N'Critical' THEN 4 WHEN N'Warning' THEN 3 WHEN N'Info' THEN 2 ELSE 1 END DESC, CreatedDate DESC;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_MoveScene
    @ScenarioID int,
    @SceneID int,
    @AnchorSceneID int = NULL,
    @TargetChapterID int = NULL,
    @Position nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ChapterID int;
    DECLARE @NewSortOrder int;
    DECLARE @AnchorSortOrder int;

    IF @AnchorSceneID IS NOT NULL
    BEGIN
        SELECT @ChapterID = ISNULL(ProposedChapterID, s.ChapterID), @AnchorSortOrder = ProposedSortOrder
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
        WHERE sso.ScenarioID = @ScenarioID AND sso.SceneID = @AnchorSceneID;

        IF @Position = N'Before'
            SELECT @NewSortOrder = ISNULL(MAX(ProposedSortOrder), @AnchorSortOrder - 20) + ((@AnchorSortOrder - ISNULL(MAX(ProposedSortOrder), @AnchorSortOrder - 20)) / 2)
            FROM dbo.ScenarioSceneOrder
            WHERE ScenarioID = @ScenarioID AND ISNULL(ProposedChapterID, 0) = ISNULL(@ChapterID, 0) AND ProposedSortOrder < @AnchorSortOrder;
        ELSE
            SELECT @NewSortOrder = (@AnchorSortOrder + ISNULL(MIN(ProposedSortOrder), @AnchorSortOrder + 20)) / 2
            FROM dbo.ScenarioSceneOrder
            WHERE ScenarioID = @ScenarioID AND ISNULL(ProposedChapterID, 0) = ISNULL(@ChapterID, 0) AND ProposedSortOrder > @AnchorSortOrder;
    END
    ELSE
    BEGIN
        SET @ChapterID = @TargetChapterID;
        SELECT @NewSortOrder = ISNULL(MAX(ProposedSortOrder), 0) + 10
        FROM dbo.ScenarioSceneOrder
        WHERE ScenarioID = @ScenarioID AND ProposedChapterID = @ChapterID;
    END;

    UPDATE dbo.ScenarioSceneOrder
    SET ProposedChapterID = @ChapterID,
        ProposedSortOrder = ISNULL(@NewSortOrder, ProposedSortOrder),
        UpdatedDate = SYSUTCDATETIME()
    WHERE ScenarioID = @ScenarioID AND SceneID = @SceneID;

    ;WITH ordered AS
    (
        SELECT ScenarioSceneOrderID,
            ROW_NUMBER() OVER (PARTITION BY ProposedChapterID ORDER BY ProposedSortOrder, ScenarioSceneOrderID) * 10 AS NextSortOrder
        FROM dbo.ScenarioSceneOrder
        WHERE ScenarioID = @ScenarioID
    )
    UPDATE sso
    SET ProposedSortOrder = ordered.NextSortOrder,
        UpdatedDate = SYSUTCDATETIME()
    FROM dbo.ScenarioSceneOrder sso
    INNER JOIN ordered ON ordered.ScenarioSceneOrderID = sso.ScenarioSceneOrderID;

    UPDATE dbo.Scenarios SET UpdatedDate = SYSUTCDATETIME() WHERE ScenarioID = @ScenarioID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_Validate
    @ScenarioID int
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM dbo.ScenarioWarnings WHERE ScenarioID = @ScenarioID;

    INSERT dbo.ScenarioValidationRuns (ScenarioID)
    VALUES (@ScenarioID);

    DECLARE @RunID int = CAST(SCOPE_IDENTITY() AS int);

    ;WITH ScenarioOrder AS
    (
        SELECT sso.SceneID, sso.ScenarioID, ISNULL(sso.ProposedChapterID, s.ChapterID) AS ChapterID,
            ch.BookID, ROW_NUMBER() OVER (ORDER BY b.SortOrder, ch.SortOrder, sso.ProposedSortOrder, s.SceneID) AS ScenarioIndex,
            s.SceneTitle, s.StartDateTime
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
        INNER JOIN dbo.Chapters ch ON ch.ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID)
        INNER JOIN dbo.Books b ON b.BookID = ch.BookID
        WHERE sso.ScenarioID = @ScenarioID
    )
    INSERT dbo.ScenarioWarnings (ScenarioValidationRunID, ScenarioID, SceneID, EntityType, EntityID, WarningTypeName, SeverityName, Message, Details)
    SELECT @RunID, @ScenarioID, targetScene.SceneID, N'SceneDependency', sd.SceneDependencyID, N'Scene Dependency Broken', N'Warning',
        N'Scenario warning: a required scene dependency is out of order.',
        CONCAT(sourceScene.SceneTitle, N' should occur before ', targetScene.SceneTitle, N'.')
    FROM dbo.SceneDependencies sd
    INNER JOIN dbo.SceneDependencyTypes sdt ON sdt.SceneDependencyTypeID = sd.SceneDependencyTypeID
    INNER JOIN ScenarioOrder sourceScene ON sourceScene.SceneID = sd.SourceSceneID
    INNER JOIN ScenarioOrder targetScene ON targetScene.SceneID = sd.TargetSceneID
    WHERE sd.IsArchived = 0 AND sdt.TypeName = N'Must Occur Before' AND sourceScene.ScenarioIndex >= targetScene.ScenarioIndex;

    ;WITH ScenarioOrder AS
    (
        SELECT sso.SceneID, sso.ScenarioID, ROW_NUMBER() OVER (ORDER BY b.SortOrder, ch.SortOrder, sso.ProposedSortOrder, s.SceneID) AS ScenarioIndex,
            s.SceneTitle, s.StartDateTime
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
        INNER JOIN dbo.Chapters ch ON ch.ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID)
        INNER JOIN dbo.Books b ON b.BookID = ch.BookID
        WHERE sso.ScenarioID = @ScenarioID
    )
    INSERT dbo.ScenarioWarnings (ScenarioValidationRunID, ScenarioID, SceneID, EntityType, EntityID, WarningTypeName, SeverityName, Message, Details)
    SELECT @RunID, @ScenarioID, later.SceneID, N'Scene', later.SceneID, N'Date / Order Conflict', N'Info',
        N'Scenario warning: an exact scene date appears earlier than a previous scenario scene.',
        CONCAT(later.SceneTitle, N' is dated before ', earlier.SceneTitle, N'.')
    FROM ScenarioOrder earlier
    INNER JOIN ScenarioOrder later ON later.ScenarioIndex = earlier.ScenarioIndex + 1
    WHERE earlier.StartDateTime IS NOT NULL AND later.StartDateTime IS NOT NULL AND later.StartDateTime < earlier.StartDateTime;

    ;WITH ScenarioOrder AS
    (
        SELECT sso.SceneID, ROW_NUMBER() OVER (ORDER BY b.SortOrder, ch.SortOrder, sso.ProposedSortOrder, s.SceneID) AS ScenarioIndex
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
        INNER JOIN dbo.Chapters ch ON ch.ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID)
        INNER JOIN dbo.Books b ON b.BookID = ch.BookID
        WHERE sso.ScenarioID = @ScenarioID
    ),
    ThreadEventsOrdered AS
    (
        SELECT te.PlotThreadID, te.ThreadEventID, te.SceneID, pt.ThreadTitle, tet.TypeName,
            so.ScenarioIndex,
            MIN(CASE WHEN tet.TypeName LIKE N'%Setup%' THEN so.ScenarioIndex END) OVER (PARTITION BY te.PlotThreadID) AS FirstSetupIndex
        FROM dbo.ThreadEvents te
        INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
        INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
        INNER JOIN ScenarioOrder so ON so.SceneID = te.SceneID
    )
    INSERT dbo.ScenarioWarnings (ScenarioValidationRunID, ScenarioID, SceneID, EntityType, EntityID, WarningTypeName, SeverityName, Message, Details)
    SELECT @RunID, @ScenarioID, SceneID, N'PlotThread', PlotThreadID, N'Plot Thread Order', N'Warning',
        N'Scenario warning: a reveal or payoff may occur before its setup.',
        CONCAT(ThreadTitle, N' has ', TypeName, N' before the first setup beat.')
    FROM ThreadEventsOrdered
    WHERE (TypeName LIKE N'%Reveal%' OR TypeName LIKE N'%Payoff%')
        AND FirstSetupIndex IS NOT NULL AND ScenarioIndex < FirstSetupIndex;

    ;WITH ScenarioOrder AS
    (
        SELECT sso.SceneID, ROW_NUMBER() OVER (ORDER BY b.SortOrder, ch.SortOrder, sso.ProposedSortOrder, s.SceneID) AS ScenarioIndex
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
        INNER JOIN dbo.Chapters ch ON ch.ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID)
        INNER JOIN dbo.Books b ON b.BookID = ch.BookID
        WHERE sso.ScenarioID = @ScenarioID
    ),
    AssetFirstSeen AS
    (
        SELECT ae.StoryAssetID, MIN(so.ScenarioIndex) AS FirstSeenIndex
        FROM dbo.AssetEvents ae
        INNER JOIN ScenarioOrder so ON so.SceneID = ae.SceneID
        GROUP BY ae.StoryAssetID
    )
    INSERT dbo.ScenarioWarnings (ScenarioValidationRunID, ScenarioID, SceneID, EntityType, EntityID, WarningTypeName, SeverityName, Message, Details)
    SELECT @RunID, @ScenarioID, NULL, N'AssetDependency', ad.AssetDependencyID, N'Asset Dependency Order', N'Info',
        N'Scenario warning: an asset appears before a validating dependency source appears.',
        CONCAT(target.AssetName, N' appears before ', source.AssetName, N'.')
    FROM dbo.AssetDependencies ad
    INNER JOIN dbo.AssetDependencyTypes adt ON adt.AssetDependencyTypeID = ad.AssetDependencyTypeID
    INNER JOIN dbo.StoryAssets source ON source.StoryAssetID = ad.SourceAssetID
    INNER JOIN dbo.StoryAssets target ON target.StoryAssetID = ad.TargetAssetID
    INNER JOIN AssetFirstSeen sourceSeen ON sourceSeen.StoryAssetID = ad.SourceAssetID
    INNER JOIN AssetFirstSeen targetSeen ON targetSeen.StoryAssetID = ad.TargetAssetID
    WHERE ad.IsArchived = 0 AND adt.IsValidatingDependency = 1 AND targetSeen.FirstSeenIndex < sourceSeen.FirstSeenIndex;

    ;WITH ScenarioOrder AS
    (
        SELECT sso.SceneID, ROW_NUMBER() OVER (ORDER BY b.SortOrder, ch.SortOrder, sso.ProposedSortOrder, s.SceneID) AS ScenarioIndex
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
        INNER JOIN dbo.Chapters ch ON ch.ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID)
        INNER JOIN dbo.Books b ON b.BookID = ch.BookID
        WHERE sso.ScenarioID = @ScenarioID
    ),
    MetricSteps AS
    (
        SELECT smv.SceneID, smt.MetricName, smv.Value, so.ScenarioIndex,
            LAG(smv.Value) OVER (PARTITION BY smt.MetricName ORDER BY so.ScenarioIndex) AS PreviousValue
        FROM dbo.SceneMetricValues smv
        INNER JOIN dbo.SceneMetricTypes smt ON smt.MetricTypeID = smv.MetricTypeID
        INNER JOIN ScenarioOrder so ON so.SceneID = smv.SceneID
        WHERE smt.MetricName IN (N'Overall Intensity', N'Tension', N'Emotional Weight')
    )
    INSERT dbo.ScenarioWarnings (ScenarioValidationRunID, ScenarioID, SceneID, EntityType, EntityID, WarningTypeName, SeverityName, Message, Details)
    SELECT @RunID, @ScenarioID, SceneID, N'Scene', SceneID, N'Pacing Jump', N'Info',
        N'Scenario warning: a pacing/emotional metric changes sharply after this move.',
        CONCAT(MetricName, N' changes from ', PreviousValue, N' to ', Value, N'.')
    FROM MetricSteps
    WHERE PreviousValue IS NOT NULL AND ABS(Value - PreviousValue) >= 6;

    DECLARE @WarningCount int = (SELECT COUNT(*) FROM dbo.ScenarioWarnings WHERE ScenarioValidationRunID = @RunID);
    UPDATE dbo.ScenarioValidationRuns SET CompletedDate = SYSUTCDATETIME(), WarningCount = @WarningCount WHERE ScenarioValidationRunID = @RunID;
    SELECT @WarningCount;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_Compare
    @ScenarioID int
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH MainOrder AS
    (
        SELECT nso.SceneID, nso.GlobalSceneIndex,
            CONCAT(nso.BookTitle, N' / Ch ', nso.ChapterNumber, N' / Scene ', nso.SceneNumber) AS MainPosition
        FROM dbo.NarrativeSceneOrder nso
        INNER JOIN dbo.ScenarioSceneOrder sso ON sso.SceneID = nso.SceneID AND sso.ScenarioID = @ScenarioID
    ),
    ScenarioOrder AS
    (
        SELECT sso.SceneID,
            ROW_NUMBER() OVER (ORDER BY b.SortOrder, ch.SortOrder, sso.ProposedSortOrder, s.SceneID) AS ScenarioIndex,
            CONCAT(b.BookTitle, N' / Ch ', ch.ChapterNumber, N' / Scene ', s.SceneNumber) AS ScenarioPosition,
            s.SceneTitle
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenes s ON s.SceneID = sso.SceneID
        INNER JOIN dbo.Chapters ch ON ch.ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID)
        INNER JOIN dbo.Books b ON b.BookID = ch.BookID
        WHERE sso.ScenarioID = @ScenarioID
    )
    SELECT so.SceneID, so.SceneTitle, mo.MainPosition, so.ScenarioPosition,
        mo.GlobalSceneIndex AS MainIndex, so.ScenarioIndex, CAST(CASE WHEN mo.GlobalSceneIndex <> so.ScenarioIndex OR mo.MainPosition <> so.ScenarioPosition THEN 1 ELSE 0 END AS bit) AS HasMoved
    FROM ScenarioOrder so
    INNER JOIN MainOrder mo ON mo.SceneID = so.SceneID
    WHERE mo.GlobalSceneIndex <> so.ScenarioIndex OR mo.MainPosition <> so.ScenarioPosition
    ORDER BY so.ScenarioIndex;

    SELECT ScenarioWarningID, ScenarioValidationRunID, ScenarioID, SceneID, EntityType, EntityID, WarningTypeName, SeverityName, Message, Details, CreatedDate
    FROM dbo.ScenarioWarnings
    WHERE ScenarioID = @ScenarioID
    ORDER BY CASE SeverityName WHEN N'Critical' THEN 4 WHEN N'Warning' THEN 3 WHEN N'Info' THEN 2 ELSE 1 END DESC, CreatedDate DESC;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_ApplyToMain
    @ScenarioID int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET ChapterID = ISNULL(sso.ProposedChapterID, s.ChapterID),
        SortOrder = sso.ProposedSortOrder,
        UpdatedDate = SYSUTCDATETIME()
    FROM dbo.Scenes s
    INNER JOIN dbo.ScenarioSceneOrder sso ON sso.SceneID = s.SceneID
    WHERE sso.ScenarioID = @ScenarioID;

    ;WITH affected AS
    (
        SELECT DISTINCT ChapterID FROM dbo.Scenes s INNER JOIN dbo.ScenarioSceneOrder sso ON sso.SceneID = s.SceneID WHERE sso.ScenarioID = @ScenarioID
    ),
    ordered AS
    (
        SELECT s.SceneID, ROW_NUMBER() OVER (PARTITION BY s.ChapterID ORDER BY s.SortOrder, s.SceneID) * 10 AS NextSortOrder
        FROM dbo.Scenes s
        INNER JOIN affected a ON a.ChapterID = s.ChapterID
        WHERE s.IsArchived = 0
    )
    UPDATE s
    SET SortOrder = ordered.NextSortOrder,
        UpdatedDate = SYSUTCDATETIME()
    FROM dbo.Scenes s
    INNER JOIN ordered ON ordered.SceneID = s.SceneID;

    UPDATE dbo.Scenarios
    SET IsAppliedToMain = 1,
        UpdatedDate = SYSUTCDATETIME()
    WHERE ScenarioID = @ScenarioID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Scenario_Archive
    @ScenarioID int
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Scenarios SET IsArchived = 1, UpdatedDate = SYSUTCDATETIME() WHERE ScenarioID = @ScenarioID;
END;
GO