215 lines
10 KiB
Transact-SQL
215 lines
10 KiB
Transact-SQL
-- Deterministic assessments reuse persisted scene intelligence; no new AI or decision resets.
|
|
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceLocationReview_List @BookID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT c.* FROM dbo.StoryIntelligenceReviewCandidates c
|
|
INNER JOIN dbo.StoryIntelligenceReviewCandidateGenerations g ON g.ReviewCandidateGenerationID = c.ReviewCandidateGenerationID
|
|
WHERE g.BookID = @BookID AND g.Module = N'Locations' AND g.IsCurrent = 1 AND g.Status = N'Ready'
|
|
ORDER BY c.SortOrder, c.ReviewCandidateID;
|
|
END;
|
|
GO
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceLocationReview_BackfillSignificance @BookID int, @AssessmentsJson nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
UPDATE c SET CandidatePayloadJson = JSON_MODIFY(c.CandidatePayloadJson, '$.significance', JSON_QUERY(a.Assessment))
|
|
FROM dbo.StoryIntelligenceReviewCandidates c
|
|
INNER JOIN dbo.StoryIntelligenceReviewCandidateGenerations g ON g.ReviewCandidateGenerationID = c.ReviewCandidateGenerationID
|
|
INNER JOIN OPENJSON(@AssessmentsJson) WITH (StableKey nvarchar(450) '$.key', Assessment nvarchar(max) '$.assessment' AS JSON) a ON a.StableKey = c.StableKey
|
|
WHERE g.BookID = @BookID AND g.Module = N'Locations' AND g.IsCurrent = 1 AND g.Status = N'Ready'
|
|
AND a.Assessment IS NOT NULL;
|
|
END;
|
|
GO
|
|
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceReviewCentre_GetSummary
|
|
@BookID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
;WITH RunSummary AS
|
|
(
|
|
SELECT
|
|
r.BookID,
|
|
COUNT_BIG(*) AS RunCount,
|
|
SUM(CASE WHEN r.Status IN (N'Completed', N'CompletedWithWarnings') THEN 1 ELSE 0 END) AS CompletedRunCount,
|
|
SUM(CASE WHEN r.Status IN (N'Pending', N'Running') THEN 1 ELSE 0 END) AS ActiveRunCount,
|
|
SUM(CASE WHEN r.Status IN (N'Failed', N'Cancelled') THEN 1 ELSE 0 END) AS FailedRunCount,
|
|
SUM(COALESCE(r.TotalDetectedScenes, 0)) AS TotalDetectedScenes,
|
|
SUM(COALESCE(r.CompletedScenes, 0)) AS CompletedScenes,
|
|
SUM(COALESCE(r.FailedScenes, 0)) AS FailedScenes
|
|
FROM dbo.StoryIntelligenceRuns r
|
|
WHERE r.BookID = @BookID
|
|
GROUP BY r.BookID
|
|
),
|
|
CandidateSummary AS
|
|
(
|
|
SELECT
|
|
generation.BookID,
|
|
MAX(CASE WHEN generation.Module = N'Characters' THEN generation.Status END) AS CharacterCandidateGenerationStatus,
|
|
MAX(CASE WHEN generation.Module = N'Characters' THEN generation.CandidateCount END) AS CharacterCandidateCount,
|
|
MAX(CASE WHEN generation.Module = N'Characters' THEN generation.PendingCount END) AS CharacterPendingCount,
|
|
MAX(CASE WHEN generation.Module = N'Locations' THEN generation.Status END) AS LocationCandidateGenerationStatus,
|
|
MAX(CASE WHEN generation.Module = N'Locations' THEN generation.CandidateCount END) AS LocationCandidateCount,
|
|
MAX(CASE WHEN generation.Module = N'Locations' THEN generation.PendingCount END) AS LocationPendingCount,
|
|
MAX(CASE WHEN generation.Module = N'Assets' THEN generation.Status END) AS AssetCandidateGenerationStatus,
|
|
MAX(CASE WHEN generation.Module = N'Assets' THEN generation.CandidateCount END) AS AssetCandidateCount,
|
|
MAX(CASE WHEN generation.Module = N'Assets' THEN generation.PendingCount END) AS AssetPendingCount,
|
|
MAX(CASE WHEN generation.Module = N'Relationships' THEN generation.Status END) AS RelationshipCandidateGenerationStatus,
|
|
MAX(CASE WHEN generation.Module = N'Relationships' THEN generation.CandidateCount END) AS RelationshipCandidateCount,
|
|
MAX(CASE WHEN generation.Module = N'Relationships' THEN generation.PendingCount END) AS RelationshipPendingCount,
|
|
MAX(CASE WHEN generation.Module = N'Knowledge' THEN generation.Status END) AS KnowledgeCandidateGenerationStatus,
|
|
MAX(CASE WHEN generation.Module = N'Knowledge' THEN generation.CandidateCount END) AS KnowledgeCandidateCount,
|
|
MAX(CASE WHEN generation.Module = N'Knowledge' THEN generation.PendingCount END) AS KnowledgePendingCount
|
|
FROM dbo.StoryIntelligenceReviewCandidateGenerations generation
|
|
WHERE generation.BookID = @BookID
|
|
AND generation.IsCurrent = 1
|
|
GROUP BY generation.BookID
|
|
),
|
|
NarrativeSummary AS
|
|
(
|
|
SELECT
|
|
nc.BookID,
|
|
COUNT_BIG(*) AS NarrativeCandidateCount,
|
|
SUM(CASE WHEN nc.Status = N'Pending' THEN 1 ELSE 0 END) AS NarrativePendingCount,
|
|
SUM(CASE WHEN nc.CandidateType = N'Thread' THEN 1 ELSE 0 END) AS NarrativeThreadCount
|
|
FROM dbo.StoryIntelligenceNarrativeCandidates nc
|
|
WHERE nc.BookID = @BookID
|
|
AND nc.WholeBookPlotSynthesisRunID IS NOT NULL
|
|
GROUP BY nc.BookID
|
|
),
|
|
NarrativeEventSummary AS
|
|
(
|
|
SELECT
|
|
nc.BookID,
|
|
COUNT_BIG(*) AS NarrativeEventCount
|
|
FROM dbo.StoryIntelligenceNarrativeCandidateEvents ev
|
|
INNER JOIN dbo.StoryIntelligenceNarrativeCandidates nc
|
|
ON nc.NarrativeCandidateID = ev.NarrativeCandidateID
|
|
WHERE nc.BookID = @BookID
|
|
AND nc.WholeBookPlotSynthesisRunID IS NOT NULL
|
|
GROUP BY nc.BookID
|
|
),
|
|
MetricValueSummary AS
|
|
(
|
|
SELECT
|
|
chapter.BookID,
|
|
COUNT_BIG(*) AS MetricValueCount
|
|
FROM dbo.SceneMetricValues metric
|
|
INNER JOIN dbo.Scenes scene
|
|
ON scene.SceneID = metric.SceneID
|
|
INNER JOIN dbo.Chapters chapter
|
|
ON chapter.ChapterID = scene.ChapterID
|
|
WHERE chapter.BookID = @BookID
|
|
GROUP BY chapter.BookID
|
|
),
|
|
CurrentPlotRun AS
|
|
(
|
|
SELECT TOP (1)
|
|
run.BookID,
|
|
run.WholeBookPlotSynthesisRunID,
|
|
run.Status AS WholeBookPlotSynthesisStatus,
|
|
run.ErrorMessage AS WholeBookPlotSynthesisErrorMessage
|
|
FROM dbo.WholeBookPlotSynthesisRuns run
|
|
WHERE run.BookID = @BookID
|
|
AND run.IsCurrent = 1
|
|
ORDER BY run.WholeBookPlotSynthesisRunID DESC
|
|
)
|
|
SELECT
|
|
p.StoryIntelligenceBookPipelineID,
|
|
p.ProjectID,
|
|
project.ProjectName,
|
|
p.BookID,
|
|
book.BookTitle,
|
|
book.Subtitle AS BookSubtitle,
|
|
p.CurrentStage,
|
|
p.LastCompletedStage,
|
|
p.CurrentReviewStage,
|
|
p.Status,
|
|
p.CompletedUtc,
|
|
p.CharacterReviewCompletedUtc,
|
|
p.LocationReviewCompletedUtc,
|
|
p.AssetReviewCompletedUtc,
|
|
p.RelationshipReviewCompletedUtc,
|
|
p.KnowledgeReviewCompletedUtc,
|
|
p.NarrativeReviewCompletedUtc,
|
|
cs.CharacterCandidateGenerationStatus,
|
|
CAST(cs.CharacterCandidateCount AS int) AS CharacterCandidateCount,
|
|
CAST(cs.CharacterPendingCount AS int) AS CharacterPendingCount,
|
|
COALESCE(ls.SignificantCount, 0) AS LocationSignificantCount,
|
|
COALESCE(ls.UncertainCount, 0) AS LocationUncertainCount,
|
|
cs.LocationCandidateGenerationStatus,
|
|
CAST(cs.LocationCandidateCount AS int) AS LocationCandidateCount,
|
|
CAST(cs.LocationPendingCount AS int) AS LocationPendingCount,
|
|
cs.AssetCandidateGenerationStatus,
|
|
CAST(cs.AssetCandidateCount AS int) AS AssetCandidateCount,
|
|
CAST(cs.AssetPendingCount AS int) AS AssetPendingCount,
|
|
cs.RelationshipCandidateGenerationStatus,
|
|
CAST(cs.RelationshipCandidateCount AS int) AS RelationshipCandidateCount,
|
|
CAST(cs.RelationshipPendingCount AS int) AS RelationshipPendingCount,
|
|
cs.KnowledgeCandidateGenerationStatus,
|
|
CAST(cs.KnowledgeCandidateCount AS int) AS KnowledgeCandidateCount,
|
|
CAST(cs.KnowledgePendingCount AS int) AS KnowledgePendingCount,
|
|
p.LastRunID,
|
|
CAST(COALESCE(rs.RunCount, 0) AS int) AS RunCount,
|
|
CAST(COALESCE(rs.CompletedRunCount, 0) AS int) AS CompletedRunCount,
|
|
CAST(COALESCE(rs.ActiveRunCount, 0) AS int) AS ActiveRunCount,
|
|
CAST(COALESCE(rs.FailedRunCount, 0) AS int) AS FailedRunCount,
|
|
CAST(COALESCE(rs.TotalDetectedScenes, 0) AS int) AS TotalDetectedScenes,
|
|
CAST(COALESCE(rs.CompletedScenes, 0) AS int) AS CompletedScenes,
|
|
CAST(COALESCE(rs.FailedScenes, 0) AS int) AS FailedScenes,
|
|
plot.WholeBookPlotSynthesisRunID,
|
|
plot.WholeBookPlotSynthesisStatus,
|
|
plot.WholeBookPlotSynthesisErrorMessage,
|
|
CAST(COALESCE(ns.NarrativeCandidateCount, 0) AS int) AS NarrativeCandidateCount,
|
|
CAST(COALESCE(ns.NarrativePendingCount, 0) AS int) AS NarrativePendingCount,
|
|
CAST(COALESCE(ns.NarrativeThreadCount, 0) AS int) AS NarrativeThreadCount,
|
|
CAST(COALESCE(nes.NarrativeEventCount, 0) AS int) AS NarrativeEventCount,
|
|
CAST(COALESCE(metrics.MetricValueCount, 0) AS int) AS MetricValueCount
|
|
FROM dbo.StoryIntelligenceBookPipelines p
|
|
INNER JOIN dbo.Projects project
|
|
ON project.ProjectID = p.ProjectID
|
|
INNER JOIN dbo.Books book
|
|
ON book.BookID = p.BookID
|
|
INNER JOIN dbo.ProjectUserAccess access
|
|
ON access.ProjectID = p.ProjectID
|
|
AND access.UserID = @UserID
|
|
AND access.IsActive = 1
|
|
LEFT JOIN RunSummary rs
|
|
ON rs.BookID = p.BookID
|
|
LEFT JOIN CandidateSummary cs
|
|
ON cs.BookID = p.BookID
|
|
LEFT JOIN NarrativeSummary ns
|
|
ON ns.BookID = p.BookID
|
|
LEFT JOIN NarrativeEventSummary nes
|
|
ON nes.BookID = p.BookID
|
|
LEFT JOIN MetricValueSummary metrics
|
|
ON metrics.BookID = p.BookID
|
|
LEFT JOIN CurrentPlotRun plot
|
|
ON plot.BookID = p.BookID
|
|
OUTER APPLY (
|
|
SELECT SUM(CASE WHEN TRY_CONVERT(int, JSON_VALUE(c.CandidatePayloadJson, '$.significance.score')) >= 40 THEN 1 ELSE 0 END) AS SignificantCount,
|
|
SUM(CASE WHEN c.DecisionStatus = N'Pending' AND
|
|
(COALESCE(JSON_VALUE(c.CandidatePayloadJson, '$.confidence'), N'Unknown') <> N'High'
|
|
OR JSON_VALUE(c.CandidatePayloadJson, '$.hasParentReviewIssue') = N'true'
|
|
OR JSON_VALUE(c.CandidatePayloadJson, '$.category') = N'Ambiguous location') THEN 1 ELSE 0 END) AS UncertainCount
|
|
FROM dbo.StoryIntelligenceReviewCandidates c
|
|
INNER JOIN dbo.StoryIntelligenceReviewCandidateGenerations g ON g.ReviewCandidateGenerationID = c.ReviewCandidateGenerationID
|
|
WHERE g.BookID = p.BookID AND g.Module = N'Locations' AND g.IsCurrent = 1 AND g.Status = N'Ready'
|
|
) ls
|
|
WHERE p.BookID = @BookID
|
|
AND book.IsArchived = 0
|
|
AND project.IsArchived = 0;
|
|
END;
|
|
GO
|