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

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_QueueAdminText
    @UserID int,
    @ProjectID int = NULL,
    @BookID int = NULL,
    @ChapterID int = NULL,
    @ChapterNumber decimal(9, 2) = NULL,
    @SourceType nvarchar(50),
    @SourceLabel nvarchar(300),
    @SourceText nvarchar(max),
    @SourceFileName nvarchar(260) = NULL,
    @SourceFileSizeBytes bigint = NULL,
    @SourceWordCount int = NULL,
    @SourceCharacterCount int = NULL,
    @SourceParagraphCount int = NULL,
    @SourceChapterCount int = NULL,
    @Model nvarchar(100),
    @PromptVersionsSummary nvarchar(500)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.StoryIntelligenceRuns
    (
        UserID, ProjectID, BookID, ChapterID, ChapterNumber, Status, SourceType, SourceLabel, SourceText,
        SourceFileName, SourceFileSizeBytes, SourceWordCount, SourceCharacterCount, SourceParagraphCount,
        SourceChapterCount, Model, PromptVersion, PromptVersionsSummary, StartedUtc, CurrentStage, CurrentMessage,
        CompletedScenes, FailedScenes
    )
    VALUES
    (
        @UserID, @ProjectID, @BookID, @ChapterID, @ChapterNumber, N'Pending', @SourceType, @SourceLabel, @SourceText,
        @SourceFileName, @SourceFileSizeBytes, @SourceWordCount, @SourceCharacterCount, @SourceParagraphCount,
        @SourceChapterCount, @Model, @PromptVersionsSummary, @PromptVersionsSummary, SYSUTCDATETIME(), N'Pending',
        N'Queued for Story Intelligence processing.', 0, 0
    );

    SELECT CAST(SCOPE_IDENTITY() AS int) AS StoryIntelligenceRunID;
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.ChapterID,
        r.ChapterNumber,
        r.Status,
        r.SourceType,
        r.SourceText,
        r.SourceFileName,
        r.SourceFileSizeBytes,
        r.SourceWordCount,
        r.SourceCharacterCount,
        r.SourceParagraphCount,
        r.SourceChapterCount,
        r.SourceDetectedImagesCount,
        r.SourceDetectedTablesCount,
        r.SourceDetectedFootnotesCount,
        r.SourceDetectedCommentsCount,
        r.CurrentStage,
        r.CurrentMessage,
        r.TotalDetectedScenes,
        r.CompletedScenes,
        r.FailedScenes,
        r.CancellationRequestedUtc,
        r.CancelledUtc,
        r.PromptVersion,
        r.PromptVersionsSummary,
        r.Model,
        r.StartedUtc,
        r.CompletedUtc,
        r.FailureStage,
        r.TotalInputTokens,
        r.TotalOutputTokens,
        r.TotalTokens,
        r.TotalDurationMs,
        r.EstimatedCostGBP,
        r.EstimatedCostUSD,
        r.ErrorMessage,
        r.ErrorDetail,
        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