132 lines
8.0 KiB
Transact-SQL
132 lines
8.0 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Acceptance_Phase1Checklist
|
|
@ProjectID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectCount int = (SELECT COUNT(*) FROM dbo.Projects WHERE IsArchived = 0 AND (@ProjectID IS NULL OR ProjectID = @ProjectID));
|
|
DECLARE @BookCount int = (
|
|
SELECT COUNT(*)
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
WHERE p.IsArchived = 0 AND b.IsArchived = 0 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID)
|
|
);
|
|
DECLARE @ChapterCount int = (
|
|
SELECT COUNT(*)
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID AND p.IsArchived = 0
|
|
WHERE c.IsArchived = 0 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID)
|
|
);
|
|
DECLARE @SceneCount int = (
|
|
SELECT COUNT(*)
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID AND p.IsArchived = 0
|
|
WHERE s.IsArchived = 0 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID)
|
|
);
|
|
DECLARE @EmptyChapterCount int = (
|
|
SELECT COUNT(*)
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID AND p.IsArchived = 0
|
|
WHERE c.IsArchived = 0 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID)
|
|
AND NOT EXISTS (SELECT 1 FROM dbo.Scenes s WHERE s.ChapterID = c.ChapterID AND s.IsArchived = 0)
|
|
);
|
|
DECLARE @PlotLineCount int = (SELECT COUNT(*) FROM dbo.PlotLines WHERE IsArchived = 0 AND (@ProjectID IS NULL OR ProjectID = @ProjectID));
|
|
DECLARE @PlotThreadCount int = (
|
|
SELECT COUNT(*)
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.IsArchived = 0
|
|
WHERE pt.IsArchived = 0 AND (@ProjectID IS NULL OR pl.ProjectID = @ProjectID)
|
|
);
|
|
DECLARE @AssetCount int = (SELECT COUNT(*) FROM dbo.StoryAssets WHERE IsArchived = 0 AND (@ProjectID IS NULL OR ProjectID = @ProjectID));
|
|
DECLARE @CharacterCount int = (SELECT COUNT(*) FROM dbo.Characters WHERE IsArchived = 0 AND (@ProjectID IS NULL OR ProjectID = @ProjectID));
|
|
DECLARE @LocationCount int = (SELECT COUNT(*) FROM dbo.Locations WHERE IsArchived = 0 AND (@ProjectID IS NULL OR ProjectID = @ProjectID));
|
|
DECLARE @ScenarioCount int = (SELECT COUNT(*) FROM dbo.Scenarios WHERE IsArchived = 0 AND (@ProjectID IS NULL OR ProjectID = @ProjectID));
|
|
DECLARE @WarningCount int = (
|
|
SELECT COUNT(*)
|
|
FROM dbo.ContinuityWarnings cw
|
|
WHERE cw.IsDismissed = 0 AND cw.IsIntentional = 0 AND (@ProjectID IS NULL OR cw.ProjectID = @ProjectID)
|
|
);
|
|
DECLARE @ArchivedCount int =
|
|
(SELECT COUNT(*) FROM dbo.Projects WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.Books WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.Chapters c INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE c.IsArchived = 1 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.Scenes s INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE s.IsArchived = 1 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.PlotLines WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.PlotThreads pt INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID WHERE pt.IsArchived = 1 AND (@ProjectID IS NULL OR pl.ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.StoryAssets WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.Characters WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.Locations WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.Scenarios WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID))
|
|
+ (SELECT COUNT(*) FROM dbo.CharacterRelationships WHERE IsArchived = 1 AND (@ProjectID IS NULL OR ProjectID = @ProjectID));
|
|
|
|
SELECT AreaName, CheckName, Status, Detail, SortOrder
|
|
FROM
|
|
(
|
|
SELECT N'Timeline' AS AreaName, N'Project hierarchy' AS CheckName,
|
|
CASE WHEN @ProjectCount > 0 AND @BookCount > 0 AND @ChapterCount > 0 THEN N'Pass' ELSE N'Review' END AS Status,
|
|
CONCAT(@ProjectCount, N' active project(s), ', @BookCount, N' active book(s), ', @ChapterCount, N' active chapter(s).') AS Detail,
|
|
10 AS SortOrder
|
|
UNION ALL
|
|
SELECT N'Timeline', N'Scene columns and empty chapters',
|
|
CASE WHEN @SceneCount > 0 THEN N'Pass' WHEN @ChapterCount > 0 THEN N'Review' ELSE N'Blocked' END,
|
|
CONCAT(@SceneCount, N' active scene(s). ', @EmptyChapterCount, N' empty chapter(s) will render as placeholder timeline columns.'),
|
|
20
|
|
UNION ALL
|
|
SELECT N'Timeline', N'Plot/thread lanes',
|
|
CASE WHEN @PlotLineCount > 0 AND @PlotThreadCount > 0 THEN N'Pass' WHEN @PlotLineCount > 0 THEN N'Review' ELSE N'Review' END,
|
|
CONCAT(@PlotLineCount, N' plot line(s), ', @PlotThreadCount, N' plot thread(s).'),
|
|
30
|
|
UNION ALL
|
|
SELECT N'Timeline', N'Asset and character lanes',
|
|
CASE WHEN @AssetCount > 0 AND @CharacterCount > 0 THEN N'Pass' WHEN @SceneCount > 0 THEN N'Review' ELSE N'Blocked' END,
|
|
CONCAT(@AssetCount, N' asset(s), ', @CharacterCount, N' character(s), ', @LocationCount, N' location(s).'),
|
|
40
|
|
UNION ALL
|
|
SELECT N'CRUD and Navigation', N'Core hierarchy',
|
|
CASE WHEN @ProjectCount > 0 AND @BookCount > 0 AND @ChapterCount > 0 AND @SceneCount > 0 THEN N'Pass' ELSE N'Review' END,
|
|
N'Projects, books, chapters and scenes have create/edit/detail/archive routes and parent navigation.',
|
|
110
|
|
UNION ALL
|
|
SELECT N'CRUD and Navigation', N'Story entities',
|
|
CASE WHEN @AssetCount > 0 OR @CharacterCount > 0 OR @LocationCount > 0 OR @PlotThreadCount > 0 THEN N'Pass' ELSE N'Review' END,
|
|
N'Characters, assets, locations, plot lines and threads are available from project and timeline navigation.',
|
|
120
|
|
UNION ALL
|
|
SELECT N'Archive and Restore', N'Archived item handling',
|
|
N'Pass',
|
|
CONCAT(@ArchivedCount, N' archived item(s). Archive, restore and guarded delete forever routes are available through Archived Items.'),
|
|
210
|
|
UNION ALL
|
|
SELECT N'Warnings and Validation', N'Actionable warnings',
|
|
CASE WHEN @WarningCount = 0 THEN N'Pass' ELSE N'Review' END,
|
|
CONCAT(@WarningCount, N' active warning(s) need review. Warnings link back to scene/timeline context.'),
|
|
310
|
|
UNION ALL
|
|
SELECT N'Writer Workflow', N'Chapter and scene workflow',
|
|
CASE WHEN @SceneCount > 0 THEN N'Pass' ELSE N'Review' END,
|
|
N'Scene workflow, notes, checklist items and attachments are available through the scene inspector and writer workspace.',
|
|
410
|
|
UNION ALL
|
|
SELECT N'Story Bible and Analytics', N'Story review surfaces',
|
|
CASE WHEN @SceneCount > 0 THEN N'Pass' ELSE N'Review' END,
|
|
N'Story Bible, analytics, dynamics, exports and search use active project/story data.',
|
|
510
|
|
UNION ALL
|
|
SELECT N'Scenario Sandbox', N'Scenario readiness',
|
|
CASE WHEN @ScenarioCount > 0 THEN N'Pass' ELSE N'Review' END,
|
|
CONCAT(@ScenarioCount, N' active scenario(s). Scenario creation remains available from project/timeline context.'),
|
|
610
|
|
) checks
|
|
ORDER BY SortOrder;
|
|
END;
|
|
GO
|