PlotDirector/PlotLine/Sql/115_Phase20I_StoryIntelligenceResultPersistence.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.StoryIntelligenceRuns', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.StoryIntelligenceRuns
    (
        StoryIntelligenceRunID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_StoryIntelligenceRuns PRIMARY KEY,
        UserID int NOT NULL,
        ProjectID int NULL,
        BookID int NULL,
        Status nvarchar(50) NOT NULL,
        SourceType nvarchar(50) NOT NULL,
        PromptVersion nvarchar(200) NOT NULL,
        Model nvarchar(100) NOT NULL,
        StartedUtc datetime2 NOT NULL,
        CompletedUtc datetime2 NULL,
        TotalInputTokens int NULL,
        TotalOutputTokens int NULL,
        TotalTokens int NULL,
        TotalDurationMs bigint NULL,
        ErrorMessage nvarchar(max) NULL,
        CreatedUtc datetime2 NOT NULL CONSTRAINT DF_StoryIntelligenceRuns_CreatedUtc DEFAULT SYSUTCDATETIME(),
        UpdatedUtc datetime2 NOT NULL CONSTRAINT DF_StoryIntelligenceRuns_UpdatedUtc DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_StoryIntelligenceRuns_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID)
    );
END;
GO

IF OBJECT_ID(N'dbo.StoryIntelligenceChapterResults', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.StoryIntelligenceChapterResults
    (
        ChapterResultID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_StoryIntelligenceChapterResults PRIMARY KEY,
        StoryIntelligenceRunID int NOT NULL,
        ProjectID int NULL,
        BookID int NULL,
        ChapterID int NULL,
        ChapterNumber decimal(9,2) NULL,
        SourceLabel nvarchar(300) NOT NULL,
        PromptVersion nvarchar(100) NOT NULL,
        Model nvarchar(100) NOT NULL,
        RawResponseJson nvarchar(max) NOT NULL,
        OutputTextJson nvarchar(max) NOT NULL,
        ParsedJson nvarchar(max) NOT NULL,
        ValidationErrorsCount int NOT NULL CONSTRAINT DF_StoryIntelligenceChapterResults_ValidationErrorsCount DEFAULT 0,
        ValidationWarningsCount int NOT NULL CONSTRAINT DF_StoryIntelligenceChapterResults_ValidationWarningsCount DEFAULT 0,
        InputTokens int NULL,
        OutputTokens int NULL,
        TotalTokens int NULL,
        DurationMs bigint NULL,
        CreatedUtc datetime2 NOT NULL CONSTRAINT DF_StoryIntelligenceChapterResults_CreatedUtc DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_StoryIntelligenceChapterResults_Run FOREIGN KEY (StoryIntelligenceRunID) REFERENCES dbo.StoryIntelligenceRuns(StoryIntelligenceRunID)
    );
END;
GO

IF OBJECT_ID(N'dbo.StoryIntelligenceSceneResults', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.StoryIntelligenceSceneResults
    (
        SceneResultID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_StoryIntelligenceSceneResults PRIMARY KEY,
        StoryIntelligenceRunID int NOT NULL,
        ChapterResultID int NULL,
        ProjectID int NULL,
        BookID int NULL,
        ChapterID int NULL,
        SceneID int NULL,
        TemporarySceneNumber int NOT NULL,
        StartParagraph int NULL,
        EndParagraph int NULL,
        SourceLabel nvarchar(300) NOT NULL,
        PromptVersion nvarchar(100) NOT NULL,
        Model nvarchar(100) NOT NULL,
        RawResponseJson nvarchar(max) NOT NULL,
        OutputTextJson nvarchar(max) NOT NULL,
        ParsedJson nvarchar(max) NOT NULL,
        ValidationErrorsCount int NOT NULL CONSTRAINT DF_StoryIntelligenceSceneResults_ValidationErrorsCount DEFAULT 0,
        ValidationWarningsCount int NOT NULL CONSTRAINT DF_StoryIntelligenceSceneResults_ValidationWarningsCount DEFAULT 0,
        InputTokens int NULL,
        OutputTokens int NULL,
        TotalTokens int NULL,
        DurationMs bigint NULL,
        CreatedUtc datetime2 NOT NULL CONSTRAINT DF_StoryIntelligenceSceneResults_CreatedUtc DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_StoryIntelligenceSceneResults_Run FOREIGN KEY (StoryIntelligenceRunID) REFERENCES dbo.StoryIntelligenceRuns(StoryIntelligenceRunID),
        CONSTRAINT FK_StoryIntelligenceSceneResults_ChapterResult FOREIGN KEY (ChapterResultID) REFERENCES dbo.StoryIntelligenceChapterResults(ChapterResultID)
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryIntelligenceRuns_CreatedUtc' AND object_id = OBJECT_ID(N'dbo.StoryIntelligenceRuns'))
    CREATE INDEX IX_StoryIntelligenceRuns_CreatedUtc ON dbo.StoryIntelligenceRuns(CreatedUtc DESC);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryIntelligenceRuns_ProjectBook' AND object_id = OBJECT_ID(N'dbo.StoryIntelligenceRuns'))
    CREATE INDEX IX_StoryIntelligenceRuns_ProjectBook ON dbo.StoryIntelligenceRuns(ProjectID, BookID, CreatedUtc DESC);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryIntelligenceChapterResults_Run' AND object_id = OBJECT_ID(N'dbo.StoryIntelligenceChapterResults'))
    CREATE INDEX IX_StoryIntelligenceChapterResults_Run ON dbo.StoryIntelligenceChapterResults(StoryIntelligenceRunID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_StoryIntelligenceSceneResults_Run' AND object_id = OBJECT_ID(N'dbo.StoryIntelligenceSceneResults'))
    CREATE INDEX IX_StoryIntelligenceSceneResults_Run ON dbo.StoryIntelligenceSceneResults(StoryIntelligenceRunID, TemporarySceneNumber);
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_Save
    @UserID int,
    @ProjectID int = NULL,
    @BookID int = NULL,
    @Status nvarchar(50),
    @SourceType nvarchar(50),
    @PromptVersion nvarchar(200),
    @Model nvarchar(100),
    @StartedUtc datetime2,
    @CompletedUtc datetime2 = NULL,
    @TotalInputTokens int = NULL,
    @TotalOutputTokens int = NULL,
    @TotalTokens int = NULL,
    @TotalDurationMs bigint = NULL,
    @ErrorMessage nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.StoryIntelligenceRuns
    (
        UserID, ProjectID, BookID, Status, SourceType, PromptVersion, Model,
        StartedUtc, CompletedUtc, TotalInputTokens, TotalOutputTokens, TotalTokens,
        TotalDurationMs, ErrorMessage
    )
    VALUES
    (
        @UserID, @ProjectID, @BookID, @Status, @SourceType, @PromptVersion, @Model,
        @StartedUtc, @CompletedUtc, @TotalInputTokens, @TotalOutputTokens, @TotalTokens,
        @TotalDurationMs, @ErrorMessage
    );

    SELECT CAST(SCOPE_IDENTITY() AS int) AS StoryIntelligenceRunID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceChapterResult_Save
    @StoryIntelligenceRunID int,
    @ProjectID int = NULL,
    @BookID int = NULL,
    @ChapterID int = NULL,
    @ChapterNumber decimal(9,2) = NULL,
    @SourceLabel nvarchar(300),
    @PromptVersion nvarchar(100),
    @Model nvarchar(100),
    @RawResponseJson nvarchar(max),
    @OutputTextJson nvarchar(max),
    @ParsedJson nvarchar(max),
    @ValidationErrorsCount int,
    @ValidationWarningsCount int,
    @InputTokens int = NULL,
    @OutputTokens int = NULL,
    @TotalTokens int = NULL,
    @DurationMs bigint = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.StoryIntelligenceChapterResults
    (
        StoryIntelligenceRunID, ProjectID, BookID, ChapterID, ChapterNumber,
        SourceLabel, PromptVersion, Model, RawResponseJson, OutputTextJson, ParsedJson,
        ValidationErrorsCount, ValidationWarningsCount, InputTokens, OutputTokens, TotalTokens, DurationMs
    )
    VALUES
    (
        @StoryIntelligenceRunID, @ProjectID, @BookID, @ChapterID, @ChapterNumber,
        @SourceLabel, @PromptVersion, @Model, @RawResponseJson, @OutputTextJson, @ParsedJson,
        @ValidationErrorsCount, @ValidationWarningsCount, @InputTokens, @OutputTokens, @TotalTokens, @DurationMs
    );

    SELECT CAST(SCOPE_IDENTITY() AS int) AS ChapterResultID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceSceneResult_Save
    @StoryIntelligenceRunID int,
    @ChapterResultID int = NULL,
    @ProjectID int = NULL,
    @BookID int = NULL,
    @ChapterID int = NULL,
    @SceneID int = NULL,
    @TemporarySceneNumber int,
    @StartParagraph int = NULL,
    @EndParagraph int = NULL,
    @SourceLabel nvarchar(300),
    @PromptVersion nvarchar(100),
    @Model nvarchar(100),
    @RawResponseJson nvarchar(max),
    @OutputTextJson nvarchar(max),
    @ParsedJson nvarchar(max),
    @ValidationErrorsCount int,
    @ValidationWarningsCount int,
    @InputTokens int = NULL,
    @OutputTokens int = NULL,
    @TotalTokens int = NULL,
    @DurationMs bigint = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.StoryIntelligenceSceneResults
    (
        StoryIntelligenceRunID, ChapterResultID, ProjectID, BookID, ChapterID, SceneID,
        TemporarySceneNumber, StartParagraph, EndParagraph, SourceLabel, PromptVersion,
        Model, RawResponseJson, OutputTextJson, ParsedJson, ValidationErrorsCount,
        ValidationWarningsCount, InputTokens, OutputTokens, TotalTokens, DurationMs
    )
    VALUES
    (
        @StoryIntelligenceRunID, @ChapterResultID, @ProjectID, @BookID, @ChapterID, @SceneID,
        @TemporarySceneNumber, @StartParagraph, @EndParagraph, @SourceLabel, @PromptVersion,
        @Model, @RawResponseJson, @OutputTextJson, @ParsedJson, @ValidationErrorsCount,
        @ValidationWarningsCount, @InputTokens, @OutputTokens, @TotalTokens, @DurationMs
    );

    SELECT CAST(SCOPE_IDENTITY() AS int) AS SceneResultID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_ListAdmin
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        r.StoryIntelligenceRunID,
        r.CreatedUtc,
        r.UserID,
        r.ProjectID,
        p.ProjectName AS ProjectTitle,
        r.BookID,
        b.BookTitle,
        r.Status,
        COUNT(sr.SceneResultID) AS SceneCount,
        r.TotalTokens,
        r.TotalDurationMs,
        COALESCE(cr.ValidationErrorsCount, 0) + COALESCE(SUM(sr.ValidationErrorsCount), 0) AS ValidationErrorsCount,
        COALESCE(cr.ValidationWarningsCount, 0) + COALESCE(SUM(sr.ValidationWarningsCount), 0) AS ValidationWarningsCount
    FROM dbo.StoryIntelligenceRuns r
    LEFT JOIN dbo.Projects p ON p.ProjectID = r.ProjectID
    LEFT JOIN dbo.Books b ON b.BookID = r.BookID
    LEFT JOIN dbo.StoryIntelligenceChapterResults cr ON cr.StoryIntelligenceRunID = r.StoryIntelligenceRunID
    LEFT JOIN dbo.StoryIntelligenceSceneResults sr ON sr.StoryIntelligenceRunID = r.StoryIntelligenceRunID
    GROUP BY
        r.StoryIntelligenceRunID, r.CreatedUtc, r.UserID, r.ProjectID, p.ProjectName,
        r.BookID, b.BookTitle, r.Status, r.TotalTokens, r.TotalDurationMs,
        cr.ValidationErrorsCount, cr.ValidationWarningsCount
    ORDER BY r.CreatedUtc DESC;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_GetAdmin
    @StoryIntelligenceRunID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        r.StoryIntelligenceRunID,
        r.UserID,
        r.ProjectID,
        p.ProjectName AS ProjectTitle,
        r.BookID,
        b.BookTitle,
        r.Status,
        r.SourceType,
        r.PromptVersion,
        r.Model,
        r.StartedUtc,
        r.CompletedUtc,
        r.TotalInputTokens,
        r.TotalOutputTokens,
        r.TotalTokens,
        r.TotalDurationMs,
        r.ErrorMessage,
        r.CreatedUtc,
        r.UpdatedUtc
    FROM dbo.StoryIntelligenceRuns r
    LEFT JOIN dbo.Projects p ON p.ProjectID = r.ProjectID
    LEFT JOIN dbo.Books b ON b.BookID = r.BookID
    WHERE r.StoryIntelligenceRunID = @StoryIntelligenceRunID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceChapterResult_GetByRun
    @StoryIntelligenceRunID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (1)
        ChapterResultID,
        StoryIntelligenceRunID,
        ProjectID,
        BookID,
        ChapterID,
        ChapterNumber,
        SourceLabel,
        PromptVersion,
        Model,
        RawResponseJson,
        OutputTextJson,
        ParsedJson,
        ValidationErrorsCount,
        ValidationWarningsCount,
        InputTokens,
        OutputTokens,
        TotalTokens,
        DurationMs,
        CreatedUtc
    FROM dbo.StoryIntelligenceChapterResults
    WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
    ORDER BY ChapterResultID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceSceneResult_ListByRun
    @StoryIntelligenceRunID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        SceneResultID,
        StoryIntelligenceRunID,
        ChapterResultID,
        ProjectID,
        BookID,
        ChapterID,
        SceneID,
        TemporarySceneNumber,
        StartParagraph,
        EndParagraph,
        SourceLabel,
        PromptVersion,
        Model,
        RawResponseJson,
        OutputTextJson,
        ParsedJson,
        ValidationErrorsCount,
        ValidationWarningsCount,
        InputTokens,
        OutputTokens,
        TotalTokens,
        DurationMs,
        CreatedUtc
    FROM dbo.StoryIntelligenceSceneResults
    WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
    ORDER BY TemporarySceneNumber, SceneResultID;
END;
GO