PlotDirector/PlotLine/Sql/052_RevisionStatusWriterWorkflow.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

DELETE rs
FROM dbo.RevisionStatuses rs
WHERE rs.StatusName IN (N'Ready To Draft', N'Drafting', N'Revising', N'Polishing', N'Complete', N'On Hold')
    AND NOT EXISTS (SELECT 1 FROM dbo.Scenes s WHERE s.RevisionStatusID = rs.RevisionStatusID)
    AND NOT EXISTS (SELECT 1 FROM dbo.Chapters c WHERE c.RevisionStatusID = rs.RevisionStatusID);
GO

CREATE OR ALTER PROCEDURE dbo.ChapterWorkflow_Summary
    @ChapterID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT s.SceneID, s.SceneNumber, s.SceneTitle,
        s.RevisionStatusID,
        rs.StatusName AS RevisionStatusName,
        COALESCE(sw.DraftStatus, rs.StatusName, N'Planned') AS DraftStatus,
        sw.Priority,
        COALESCE(sw.ReadyForDraft, 0) AS ReadyForDraft,
        COALESCE(sw.ReadyForRevision, 0) AS ReadyForRevision,
        COALESCE(sw.ReadyForPolish, 0) AS ReadyForPolish,
        COALESCE(sw.IsBlocked, 0) AS IsBlocked,
        sw.BlockedReason,
        ISNULL(warnings.WarningCount, 0) AS WarningCount,
        ISNULL(checklist.RemainingChecklistCount, 0) AS RemainingChecklistCount,
        ISNULL(notes.UnresolvedNoteCount, 0) AS UnresolvedNoteCount
    FROM dbo.Scenes s
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
    LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
    OUTER APPLY
    (
        SELECT COUNT(*) AS WarningCount
        FROM dbo.ContinuityWarnings cw
        WHERE cw.SceneID = s.SceneID AND cw.IsDismissed = 0
    ) warnings
    OUTER APPLY
    (
        SELECT COUNT(*) AS RemainingChecklistCount
        FROM dbo.SceneChecklistItems sci
        WHERE sci.SceneID = s.SceneID AND sci.IsCompleted = 0
    ) checklist
    OUTER APPLY
    (
        SELECT COUNT(*) AS UnresolvedNoteCount
        FROM dbo.SceneNotes sn
        WHERE sn.SceneID = s.SceneID AND sn.IsResolved = 0
    ) notes
    WHERE s.ChapterID = @ChapterID AND s.IsArchived = 0
    ORDER BY s.SortOrder, s.SceneID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.WriterDashboard_Scenes
    @ProjectID int = NULL,
    @Queue nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    WITH SceneBase AS
    (
        SELECT p.ProjectID, p.ProjectName, b.BookID, b.BookTitle, c.ChapterID, c.ChapterNumber, c.ChapterTitle,
            s.SceneID, s.SceneNumber, s.SceneTitle, s.Summary,
            s.RevisionStatusID,
            rs.StatusName AS RevisionStatusName,
            COALESCE(sw.DraftStatus, rs.StatusName, N'Planned') AS DraftStatus,
            sw.Priority,
            COALESCE(sw.IsBlocked, 0) AS IsBlocked,
            sw.BlockedReason,
            COALESCE(sw.ReadyForDraft, 0) AS ReadyForDraft,
            COALESCE(sw.ReadyForRevision, 0) AS ReadyForRevision,
            COALESCE(sw.ReadyForPolish, 0) AS ReadyForPolish,
            b.SortOrder AS BookSortOrder,
            c.SortOrder AS ChapterSortOrder,
            s.SortOrder AS SceneSortOrder
        FROM dbo.Projects p
        INNER JOIN dbo.Books b ON b.ProjectID = p.ProjectID AND b.IsArchived = 0
        INNER JOIN dbo.Chapters c ON c.BookID = b.BookID AND c.IsArchived = 0
        INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
        INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
        LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
        WHERE p.IsArchived = 0 AND (@ProjectID IS NULL OR p.ProjectID = @ProjectID)
    )
    SELECT TOP (40) sb.ProjectID, sb.ProjectName, sb.BookID, sb.BookTitle, sb.ChapterID, sb.ChapterNumber, sb.ChapterTitle,
        sb.SceneID, sb.SceneNumber, sb.SceneTitle, sb.Summary, sb.RevisionStatusID, sb.RevisionStatusName,
        sb.DraftStatus, sb.Priority, sb.IsBlocked, sb.BlockedReason,
        ISNULL(warnings.WarningCount, 0) AS WarningCount,
        ISNULL(checklist.RemainingChecklistCount, 0) AS RemainingChecklistCount,
        ISNULL(notes.UnresolvedNoteCount, 0) AS UnresolvedNoteCount,
        threads.ImportantThreads,
        pinned.PinnedNotes
    FROM SceneBase sb
    OUTER APPLY
    (
        SELECT COUNT(*) AS WarningCount
        FROM dbo.ContinuityWarnings cw
        WHERE cw.SceneID = sb.SceneID AND cw.IsDismissed = 0
    ) warnings
    OUTER APPLY
    (
        SELECT COUNT(*) AS RemainingChecklistCount
        FROM dbo.SceneChecklistItems sci
        WHERE sci.SceneID = sb.SceneID AND sci.IsCompleted = 0
    ) checklist
    OUTER APPLY
    (
        SELECT COUNT(*) AS UnresolvedNoteCount
        FROM dbo.SceneNotes sn
        INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
        WHERE sn.SceneID = sb.SceneID AND sn.IsResolved = 0
            AND (@Queue <> N'NeedsAttention' OR snt.TypeName IN (N'TODO', N'Continuity Note'))
    ) notes
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), pt.ThreadTitle), N', ') AS ImportantThreads
        FROM
        (
            SELECT DISTINCT TOP (5) pt.ThreadTitle, pt.Importance
            FROM dbo.ThreadEvents te
            INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
            WHERE te.SceneID = sb.SceneID
            ORDER BY pt.Importance DESC, pt.ThreadTitle
        ) pt
    ) threads
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), COALESCE(sn.NoteTitle, sn.NoteText)), N' | ') AS PinnedNotes
        FROM dbo.SceneNotes sn
        WHERE sn.SceneID = sb.SceneID AND sn.IsPinned = 1 AND sn.IsResolved = 0
    ) pinned
    WHERE
        (@Queue = N'WriteNext' AND sb.RevisionStatusName = N'Outlined')
        OR (@Queue = N'ContinueWriting' AND sb.RevisionStatusName = N'Drafted')
        OR (@Queue = N'NeedsAttention' AND (sb.IsBlocked = 1 OR ISNULL(warnings.WarningCount, 0) > 0 OR ISNULL(notes.UnresolvedNoteCount, 0) > 0))
        OR (@Queue = N'RevisionQueue' AND sb.RevisionStatusName = N'Needs Work')
        OR (@Queue = N'PolishingQueue' AND sb.RevisionStatusName = N'Revised')
    ORDER BY ISNULL(sb.Priority, 0) DESC, sb.BookSortOrder, sb.ChapterSortOrder, sb.SceneSortOrder, sb.SceneID;
END;
GO