312 lines
11 KiB
Transact-SQL
312 lines
11 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'SourceLabel') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD SourceLabel nvarchar(300) NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'SourceText') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD SourceText nvarchar(max) NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'CurrentStage') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD CurrentStage nvarchar(80) NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'CurrentMessage') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD CurrentMessage nvarchar(400) NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'TotalDetectedScenes') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD TotalDetectedScenes int NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'CompletedScenes') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD CompletedScenes int NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'FailedScenes') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD FailedScenes int NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'CancellationRequestedUtc') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD CancellationRequestedUtc datetime2 NULL;
|
|
GO
|
|
IF COL_LENGTH(N'dbo.StoryIntelligenceRuns', N'CancelledUtc') IS NULL
|
|
ALTER TABLE dbo.StoryIntelligenceRuns ADD CancelledUtc datetime2 NULL;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_QueueAdminText
|
|
@UserID int,
|
|
@ProjectID int = NULL,
|
|
@BookID int = NULL,
|
|
@SourceType nvarchar(50),
|
|
@SourceLabel nvarchar(300),
|
|
@SourceText nvarchar(max),
|
|
@SourceWordCount int = NULL,
|
|
@SourceCharacterCount int = NULL,
|
|
@SourceChapterCount int = NULL,
|
|
@Model nvarchar(100),
|
|
@PromptVersionsSummary nvarchar(500)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT dbo.StoryIntelligenceRuns
|
|
(
|
|
UserID, ProjectID, BookID, Status, SourceType, SourceLabel, SourceText,
|
|
SourceWordCount, SourceCharacterCount, SourceChapterCount, Model,
|
|
PromptVersion, PromptVersionsSummary, StartedUtc, CurrentStage, CurrentMessage,
|
|
CompletedScenes, FailedScenes
|
|
)
|
|
VALUES
|
|
(
|
|
@UserID, @ProjectID, @BookID, N'Pending', @SourceType, @SourceLabel, @SourceText,
|
|
@SourceWordCount, @SourceCharacterCount, @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_ClaimNextPending
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
DECLARE @StoryIntelligenceRunID int;
|
|
|
|
SELECT TOP (1) @StoryIntelligenceRunID = StoryIntelligenceRunID
|
|
FROM dbo.StoryIntelligenceRuns WITH (UPDLOCK, READPAST)
|
|
WHERE Status = N'Pending'
|
|
ORDER BY CreatedUtc;
|
|
|
|
IF @StoryIntelligenceRunID IS NULL
|
|
BEGIN
|
|
SELECT TOP (0)
|
|
StoryIntelligenceRunID, UserID, ProjectID, BookID, Status, SourceType, SourceLabel,
|
|
SourceText, SourceWordCount, SourceCharacterCount, SourceChapterCount, PromptVersion,
|
|
PromptVersionsSummary, Model, StartedUtc, CompletedUtc, FailureStage, TotalInputTokens,
|
|
TotalOutputTokens, TotalTokens, TotalDurationMs, EstimatedCostGBP, EstimatedCostUSD,
|
|
ErrorMessage, ErrorDetail, CurrentStage, CurrentMessage, TotalDetectedScenes,
|
|
CompletedScenes, FailedScenes, CancellationRequestedUtc, CancelledUtc, CreatedUtc, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceRuns;
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.StoryIntelligenceRuns
|
|
SET Status = N'Running',
|
|
StartedUtc = SYSUTCDATETIME(),
|
|
CurrentStage = N'ChapterStructure',
|
|
CurrentMessage = N'Running Chapter Structure analysis.',
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
|
|
AND Status = N'Pending';
|
|
|
|
SELECT
|
|
StoryIntelligenceRunID, UserID, ProjectID, BookID, Status, SourceType, SourceLabel,
|
|
SourceText, SourceWordCount, SourceCharacterCount, SourceChapterCount, PromptVersion,
|
|
PromptVersionsSummary, Model, StartedUtc, CompletedUtc, FailureStage, TotalInputTokens,
|
|
TotalOutputTokens, TotalTokens, TotalDurationMs, EstimatedCostGBP, EstimatedCostUSD,
|
|
ErrorMessage, ErrorDetail, CurrentStage, CurrentMessage, TotalDetectedScenes,
|
|
CompletedScenes, FailedScenes, CancellationRequestedUtc, CancelledUtc, CreatedUtc, UpdatedUtc
|
|
FROM dbo.StoryIntelligenceRuns
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_UpdateProgress
|
|
@StoryIntelligenceRunID int,
|
|
@Status nvarchar(50) = NULL,
|
|
@CurrentStage nvarchar(80) = NULL,
|
|
@CurrentMessage nvarchar(400) = NULL,
|
|
@TotalDetectedScenes int = NULL,
|
|
@CompletedScenes int = NULL,
|
|
@FailedScenes int = NULL,
|
|
@TotalInputTokens int = NULL,
|
|
@TotalOutputTokens int = NULL,
|
|
@TotalTokens int = NULL,
|
|
@TotalDurationMs bigint = NULL,
|
|
@EstimatedCostGBP decimal(19,6) = NULL,
|
|
@EstimatedCostUSD decimal(19,6) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceRuns
|
|
SET Status = COALESCE(@Status, Status),
|
|
CurrentStage = COALESCE(@CurrentStage, CurrentStage),
|
|
CurrentMessage = COALESCE(@CurrentMessage, CurrentMessage),
|
|
TotalDetectedScenes = COALESCE(@TotalDetectedScenes, TotalDetectedScenes),
|
|
CompletedScenes = COALESCE(@CompletedScenes, CompletedScenes),
|
|
FailedScenes = COALESCE(@FailedScenes, FailedScenes),
|
|
TotalInputTokens = COALESCE(@TotalInputTokens, TotalInputTokens),
|
|
TotalOutputTokens = COALESCE(@TotalOutputTokens, TotalOutputTokens),
|
|
TotalTokens = COALESCE(@TotalTokens, TotalTokens),
|
|
TotalDurationMs = COALESCE(@TotalDurationMs, TotalDurationMs),
|
|
EstimatedCostGBP = COALESCE(@EstimatedCostGBP, EstimatedCostGBP),
|
|
EstimatedCostUSD = COALESCE(@EstimatedCostUSD, EstimatedCostUSD),
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
|
|
AND Status NOT IN (N'Completed', N'CompletedWithWarnings', N'Failed', N'Cancelled');
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_Complete
|
|
@StoryIntelligenceRunID int,
|
|
@Status nvarchar(50),
|
|
@TotalInputTokens int = NULL,
|
|
@TotalOutputTokens int = NULL,
|
|
@TotalTokens int = NULL,
|
|
@TotalDurationMs bigint = NULL,
|
|
@EstimatedCostGBP decimal(19,6) = NULL,
|
|
@EstimatedCostUSD decimal(19,6) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceRuns
|
|
SET Status = @Status,
|
|
CompletedUtc = SYSUTCDATETIME(),
|
|
CurrentStage = N'Completed',
|
|
CurrentMessage = CASE WHEN @Status = N'CompletedWithWarnings'
|
|
THEN N'Story Intelligence completed with warnings.'
|
|
ELSE N'Story Intelligence completed.'
|
|
END,
|
|
TotalInputTokens = COALESCE(@TotalInputTokens, TotalInputTokens),
|
|
TotalOutputTokens = COALESCE(@TotalOutputTokens, TotalOutputTokens),
|
|
TotalTokens = COALESCE(@TotalTokens, TotalTokens),
|
|
TotalDurationMs = COALESCE(@TotalDurationMs, TotalDurationMs),
|
|
EstimatedCostGBP = COALESCE(@EstimatedCostGBP, EstimatedCostGBP),
|
|
EstimatedCostUSD = COALESCE(@EstimatedCostUSD, EstimatedCostUSD),
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
|
|
AND Status NOT IN (N'Completed', N'CompletedWithWarnings', N'Failed', N'Cancelled');
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_Fail
|
|
@StoryIntelligenceRunID int,
|
|
@FailureStage nvarchar(80),
|
|
@ErrorMessage nvarchar(max),
|
|
@ErrorDetail nvarchar(max) = NULL,
|
|
@TotalDurationMs bigint = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceRuns
|
|
SET Status = N'Failed',
|
|
CompletedUtc = SYSUTCDATETIME(),
|
|
FailureStage = @FailureStage,
|
|
ErrorMessage = @ErrorMessage,
|
|
ErrorDetail = @ErrorDetail,
|
|
CurrentStage = @FailureStage,
|
|
CurrentMessage = N'Story Intelligence failed.',
|
|
TotalDurationMs = COALESCE(@TotalDurationMs, TotalDurationMs),
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
|
|
AND Status NOT IN (N'Completed', N'CompletedWithWarnings', N'Failed', N'Cancelled');
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_RequestCancel
|
|
@StoryIntelligenceRunID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceRuns
|
|
SET CancellationRequestedUtc = COALESCE(CancellationRequestedUtc, SYSUTCDATETIME()),
|
|
CurrentMessage = CASE WHEN Status = N'Pending'
|
|
THEN N'Cancellation requested before processing.'
|
|
ELSE N'Cancellation requested. Processing will stop at the next safe checkpoint.'
|
|
END,
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
|
|
AND Status IN (N'Pending', N'Running');
|
|
|
|
UPDATE dbo.StoryIntelligenceRuns
|
|
SET Status = N'Cancelled',
|
|
CompletedUtc = SYSUTCDATETIME(),
|
|
CancelledUtc = SYSUTCDATETIME(),
|
|
CurrentStage = N'Cancelled',
|
|
CurrentMessage = N'Story Intelligence run was cancelled before processing.',
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
|
|
AND Status = N'Pending';
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceRun_Cancel
|
|
@StoryIntelligenceRunID int,
|
|
@TotalDurationMs bigint = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.StoryIntelligenceRuns
|
|
SET Status = N'Cancelled',
|
|
CompletedUtc = SYSUTCDATETIME(),
|
|
CancelledUtc = SYSUTCDATETIME(),
|
|
CurrentStage = N'Cancelled',
|
|
CurrentMessage = N'Story Intelligence run was cancelled.',
|
|
TotalDurationMs = COALESCE(@TotalDurationMs, TotalDurationMs),
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE StoryIntelligenceRunID = @StoryIntelligenceRunID
|
|
AND Status NOT IN (N'Completed', N'CompletedWithWarnings', N'Failed', N'Cancelled');
|
|
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.SourceFileName,
|
|
r.SourceFileSizeBytes,
|
|
r.SourceWordCount,
|
|
r.SourceCharacterCount,
|
|
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
|