715 lines
29 KiB
Transact-SQL
715 lines
29 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneNoteTypes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneNoteTypes
|
|
(
|
|
SceneNoteTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneNoteTypes PRIMARY KEY,
|
|
TypeName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_SceneNoteTypes_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_SceneNoteTypes_TypeName UNIQUE (TypeName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneNotes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneNotes
|
|
(
|
|
SceneNoteID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneNotes PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
SceneNoteTypeID int NOT NULL,
|
|
NoteTitle nvarchar(200) NULL,
|
|
NoteText nvarchar(max) NOT NULL,
|
|
SortOrder int NULL,
|
|
IsPinned bit NOT NULL CONSTRAINT DF_SceneNotes_IsPinned DEFAULT 0,
|
|
IsResolved bit NOT NULL CONSTRAINT DF_SceneNotes_IsResolved DEFAULT 0,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneNotes_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneNotes_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_SceneNotes_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_SceneNotes_Types FOREIGN KEY (SceneNoteTypeID) REFERENCES dbo.SceneNoteTypes(SceneNoteTypeID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.ChapterGoals', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.ChapterGoals
|
|
(
|
|
ChapterGoalID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ChapterGoals PRIMARY KEY,
|
|
ChapterID int NOT NULL,
|
|
GoalSummary nvarchar(max) NULL,
|
|
EmotionalGoal nvarchar(max) NULL,
|
|
ReaderTakeaway nvarchar(max) NULL,
|
|
MustInclude nvarchar(max) NULL,
|
|
RisksOrConcerns nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_ChapterGoals_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_ChapterGoals_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_ChapterGoals_Chapters FOREIGN KEY (ChapterID) REFERENCES dbo.Chapters(ChapterID),
|
|
CONSTRAINT UQ_ChapterGoals_ChapterID UNIQUE (ChapterID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneWorkflow', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneWorkflow
|
|
(
|
|
SceneWorkflowID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneWorkflow PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
DraftStatus nvarchar(50) NOT NULL CONSTRAINT DF_SceneWorkflow_DraftStatus DEFAULT N'Planned',
|
|
EstimatedWordCount int NULL,
|
|
ActualWordCount int NULL,
|
|
DraftedDate date NULL,
|
|
LastWorkedOn date NULL,
|
|
ReadyForDraft bit NOT NULL CONSTRAINT DF_SceneWorkflow_ReadyForDraft DEFAULT 0,
|
|
ReadyForRevision bit NOT NULL CONSTRAINT DF_SceneWorkflow_ReadyForRevision DEFAULT 0,
|
|
ReadyForPolish bit NOT NULL CONSTRAINT DF_SceneWorkflow_ReadyForPolish DEFAULT 0,
|
|
IsBlocked bit NOT NULL CONSTRAINT DF_SceneWorkflow_IsBlocked DEFAULT 0,
|
|
BlockedReason nvarchar(max) NULL,
|
|
Priority int NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneWorkflow_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneWorkflow_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_SceneWorkflow_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT UQ_SceneWorkflow_SceneID UNIQUE (SceneID),
|
|
CONSTRAINT CK_SceneWorkflow_DraftStatus CHECK (DraftStatus IN (N'Planned', N'Ready To Draft', N'Drafting', N'Drafted', N'Revising', N'Polishing', N'Complete', N'On Hold')),
|
|
CONSTRAINT CK_SceneWorkflow_Priority CHECK (Priority IS NULL OR Priority BETWEEN 1 AND 10),
|
|
CONSTRAINT CK_SceneWorkflow_WordCounts CHECK ((EstimatedWordCount IS NULL OR EstimatedWordCount >= 0) AND (ActualWordCount IS NULL OR ActualWordCount >= 0))
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneChecklistItems', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneChecklistItems
|
|
(
|
|
SceneChecklistItemID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneChecklistItems PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
ItemText nvarchar(300) NOT NULL,
|
|
IsCompleted bit NOT NULL CONSTRAINT DF_SceneChecklistItems_IsCompleted DEFAULT 0,
|
|
SortOrder int NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneChecklistItems_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneChecklistItems_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_SceneChecklistItems_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneAttachmentTypes', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneAttachmentTypes
|
|
(
|
|
SceneAttachmentTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneAttachmentTypes PRIMARY KEY,
|
|
TypeName nvarchar(100) NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_SceneAttachmentTypes_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_SceneAttachmentTypes_TypeName UNIQUE (TypeName)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.SceneAttachments', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneAttachments
|
|
(
|
|
SceneAttachmentID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneAttachments PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
SceneAttachmentTypeID int NOT NULL,
|
|
Title nvarchar(200) NOT NULL,
|
|
FilePath nvarchar(500) NULL,
|
|
ExternalUrl nvarchar(1000) NULL,
|
|
Notes nvarchar(max) NULL,
|
|
CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneAttachments_CreatedDate DEFAULT SYSUTCDATETIME(),
|
|
UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneAttachments_UpdatedDate DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_SceneAttachments_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_SceneAttachments_Types FOREIGN KEY (SceneAttachmentTypeID) REFERENCES dbo.SceneAttachmentTypes(SceneAttachmentTypeID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
MERGE dbo.SceneNoteTypes AS target
|
|
USING (VALUES
|
|
(N'Drafting Note', 10), (N'Revision Note', 20), (N'Continuity Note', 30), (N'Research Note', 40),
|
|
(N'Emotional Intent', 50), (N'Dialogue Note', 60), (N'Sensory Note', 70), (N'Timeline Note', 80),
|
|
(N'Plot Reminder', 90), (N'Character Reminder', 100), (N'TODO', 110)
|
|
) AS source (TypeName, SortOrder)
|
|
ON target.TypeName = source.TypeName
|
|
WHEN MATCHED THEN UPDATE SET SortOrder = source.SortOrder, IsActive = 1
|
|
WHEN NOT MATCHED THEN INSERT (TypeName, SortOrder, IsActive) VALUES (source.TypeName, source.SortOrder, 1);
|
|
GO
|
|
|
|
MERGE dbo.SceneAttachmentTypes AS target
|
|
USING (VALUES
|
|
(N'Image', 10), (N'Link', 20), (N'Research Reference', 30), (N'Historical Reference', 40),
|
|
(N'Character Reference', 50), (N'Location Reference', 60), (N'Continuity Reference', 70), (N'General File', 80)
|
|
) AS source (TypeName, SortOrder)
|
|
ON target.TypeName = source.TypeName
|
|
WHEN MATCHED THEN UPDATE SET SortOrder = source.SortOrder, IsActive = 1
|
|
WHEN NOT MATCHED THEN INSERT (TypeName, SortOrder, IsActive) VALUES (source.TypeName, source.SortOrder, 1);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneNotes_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneNotes'))
|
|
CREATE INDEX IX_SceneNotes_SceneID ON dbo.SceneNotes(SceneID, IsResolved, IsPinned, SortOrder);
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneChecklistItems_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneChecklistItems'))
|
|
CREATE INDEX IX_SceneChecklistItems_SceneID ON dbo.SceneChecklistItems(SceneID, IsCompleted, SortOrder);
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneAttachments_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneAttachments'))
|
|
CREATE INDEX IX_SceneAttachments_SceneID ON dbo.SceneAttachments(SceneID, SceneAttachmentTypeID);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneNoteType_List
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT SceneNoteTypeID, TypeName, SortOrder, IsActive
|
|
FROM dbo.SceneNoteTypes
|
|
WHERE IsActive = 1
|
|
ORDER BY SortOrder, TypeName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAttachmentType_List
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT SceneAttachmentTypeID, TypeName, SortOrder, IsActive
|
|
FROM dbo.SceneAttachmentTypes
|
|
WHERE IsActive = 1
|
|
ORDER BY SortOrder, TypeName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneWorkflow_Get
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
|
|
BEGIN
|
|
INSERT dbo.SceneWorkflow (SceneID)
|
|
VALUES (@SceneID);
|
|
END;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.SceneChecklistItems WHERE SceneID = @SceneID)
|
|
BEGIN
|
|
INSERT dbo.SceneChecklistItems (SceneID, ItemText, SortOrder)
|
|
VALUES
|
|
(@SceneID, N'Purpose clear', 10),
|
|
(@SceneID, N'Emotional payoff clear', 20),
|
|
(@SceneID, N'Continuity checked', 30),
|
|
(@SceneID, N'POV consistent', 40),
|
|
(@SceneID, N'Dialogue pass complete', 50),
|
|
(@SceneID, N'Sensory detail pass complete', 60),
|
|
(@SceneID, N'Character motivation clear', 70),
|
|
(@SceneID, N'Ending hook strong', 80);
|
|
END;
|
|
|
|
SELECT SceneWorkflowID, SceneID, DraftStatus, EstimatedWordCount, ActualWordCount, DraftedDate, LastWorkedOn,
|
|
ReadyForDraft, ReadyForRevision, ReadyForPolish, IsBlocked, BlockedReason, Priority, CreatedDate, UpdatedDate
|
|
FROM dbo.SceneWorkflow
|
|
WHERE SceneID = @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneWorkflow_Save
|
|
@SceneID int,
|
|
@DraftStatus nvarchar(50),
|
|
@EstimatedWordCount int = NULL,
|
|
@ActualWordCount int = NULL,
|
|
@DraftedDate date = NULL,
|
|
@LastWorkedOn date = NULL,
|
|
@ReadyForDraft bit = 0,
|
|
@ReadyForRevision bit = 0,
|
|
@ReadyForPolish bit = 0,
|
|
@IsBlocked bit = 0,
|
|
@BlockedReason nvarchar(max) = NULL,
|
|
@Priority int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
|
|
BEGIN
|
|
UPDATE dbo.SceneWorkflow
|
|
SET DraftStatus = @DraftStatus,
|
|
EstimatedWordCount = @EstimatedWordCount,
|
|
ActualWordCount = @ActualWordCount,
|
|
DraftedDate = @DraftedDate,
|
|
LastWorkedOn = @LastWorkedOn,
|
|
ReadyForDraft = @ReadyForDraft,
|
|
ReadyForRevision = @ReadyForRevision,
|
|
ReadyForPolish = @ReadyForPolish,
|
|
IsBlocked = @IsBlocked,
|
|
BlockedReason = @BlockedReason,
|
|
Priority = @Priority,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT dbo.SceneWorkflow (SceneID, DraftStatus, EstimatedWordCount, ActualWordCount, DraftedDate, LastWorkedOn, ReadyForDraft, ReadyForRevision, ReadyForPolish, IsBlocked, BlockedReason, Priority)
|
|
VALUES (@SceneID, @DraftStatus, @EstimatedWordCount, @ActualWordCount, @DraftedDate, @LastWorkedOn, @ReadyForDraft, @ReadyForRevision, @ReadyForPolish, @IsBlocked, @BlockedReason, @Priority);
|
|
END;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneNote_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT n.SceneNoteID, n.SceneID, n.SceneNoteTypeID, nt.TypeName, n.NoteTitle, n.NoteText, n.SortOrder,
|
|
n.IsPinned, n.IsResolved, n.CreatedDate, n.UpdatedDate
|
|
FROM dbo.SceneNotes n
|
|
INNER JOIN dbo.SceneNoteTypes nt ON nt.SceneNoteTypeID = n.SceneNoteTypeID
|
|
WHERE n.SceneID = @SceneID
|
|
ORDER BY n.IsPinned DESC, n.IsResolved, ISNULL(n.SortOrder, 9999), n.UpdatedDate DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneNote_Save
|
|
@SceneNoteID int = 0,
|
|
@SceneID int,
|
|
@SceneNoteTypeID int,
|
|
@NoteTitle nvarchar(200) = NULL,
|
|
@NoteText nvarchar(max),
|
|
@SortOrder int = NULL,
|
|
@IsPinned bit = 0,
|
|
@IsResolved bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @SceneNoteID IS NULL OR @SceneNoteID = 0
|
|
BEGIN
|
|
INSERT dbo.SceneNotes (SceneID, SceneNoteTypeID, NoteTitle, NoteText, SortOrder, IsPinned, IsResolved)
|
|
VALUES (@SceneID, @SceneNoteTypeID, @NoteTitle, @NoteText, @SortOrder, @IsPinned, @IsResolved);
|
|
SET @SceneNoteID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.SceneNotes
|
|
SET SceneNoteTypeID = @SceneNoteTypeID,
|
|
NoteTitle = @NoteTitle,
|
|
NoteText = @NoteText,
|
|
SortOrder = @SortOrder,
|
|
IsPinned = @IsPinned,
|
|
IsResolved = @IsResolved,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneNoteID = @SceneNoteID;
|
|
END;
|
|
|
|
SELECT @SceneNoteID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneNote_Delete
|
|
@SceneNoteID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DELETE dbo.SceneNotes WHERE SceneNoteID = @SceneNoteID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneChecklist_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT SceneChecklistItemID, SceneID, ItemText, IsCompleted, SortOrder, CreatedDate, UpdatedDate
|
|
FROM dbo.SceneChecklistItems
|
|
WHERE SceneID = @SceneID
|
|
ORDER BY IsCompleted, ISNULL(SortOrder, 9999), SceneChecklistItemID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneChecklist_Save
|
|
@SceneChecklistItemID int = 0,
|
|
@SceneID int,
|
|
@ItemText nvarchar(300),
|
|
@IsCompleted bit = 0,
|
|
@SortOrder int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @SceneChecklistItemID IS NULL OR @SceneChecklistItemID = 0
|
|
BEGIN
|
|
INSERT dbo.SceneChecklistItems (SceneID, ItemText, IsCompleted, SortOrder)
|
|
VALUES (@SceneID, @ItemText, @IsCompleted, @SortOrder);
|
|
SET @SceneChecklistItemID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.SceneChecklistItems
|
|
SET ItemText = @ItemText, IsCompleted = @IsCompleted, SortOrder = @SortOrder, UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneChecklistItemID = @SceneChecklistItemID;
|
|
END;
|
|
|
|
SELECT @SceneChecklistItemID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneChecklist_Delete
|
|
@SceneChecklistItemID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DELETE dbo.SceneChecklistItems WHERE SceneChecklistItemID = @SceneChecklistItemID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAttachment_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT a.SceneAttachmentID, a.SceneID, a.SceneAttachmentTypeID, at.TypeName, a.Title, a.FilePath, a.ExternalUrl, a.Notes, a.CreatedDate, a.UpdatedDate
|
|
FROM dbo.SceneAttachments a
|
|
INNER JOIN dbo.SceneAttachmentTypes at ON at.SceneAttachmentTypeID = a.SceneAttachmentTypeID
|
|
WHERE a.SceneID = @SceneID
|
|
ORDER BY a.UpdatedDate DESC, a.SceneAttachmentID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAttachment_Save
|
|
@SceneAttachmentID int = 0,
|
|
@SceneID int,
|
|
@SceneAttachmentTypeID int,
|
|
@Title nvarchar(200),
|
|
@FilePath nvarchar(500) = NULL,
|
|
@ExternalUrl nvarchar(1000) = NULL,
|
|
@Notes nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @SceneAttachmentID IS NULL OR @SceneAttachmentID = 0
|
|
BEGIN
|
|
INSERT dbo.SceneAttachments (SceneID, SceneAttachmentTypeID, Title, FilePath, ExternalUrl, Notes)
|
|
VALUES (@SceneID, @SceneAttachmentTypeID, @Title, @FilePath, @ExternalUrl, @Notes);
|
|
SET @SceneAttachmentID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.SceneAttachments
|
|
SET SceneAttachmentTypeID = @SceneAttachmentTypeID,
|
|
Title = @Title,
|
|
FilePath = @FilePath,
|
|
ExternalUrl = @ExternalUrl,
|
|
Notes = @Notes,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneAttachmentID = @SceneAttachmentID;
|
|
END;
|
|
|
|
SELECT @SceneAttachmentID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneAttachment_Delete
|
|
@SceneAttachmentID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DELETE dbo.SceneAttachments WHERE SceneAttachmentID = @SceneAttachmentID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ChapterGoal_Get
|
|
@ChapterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.ChapterGoals WHERE ChapterID = @ChapterID)
|
|
BEGIN
|
|
INSERT dbo.ChapterGoals (ChapterID) VALUES (@ChapterID);
|
|
END;
|
|
|
|
SELECT ChapterGoalID, ChapterID, GoalSummary, EmotionalGoal, ReaderTakeaway, MustInclude, RisksOrConcerns, CreatedDate, UpdatedDate
|
|
FROM dbo.ChapterGoals
|
|
WHERE ChapterID = @ChapterID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ChapterGoal_Save
|
|
@ChapterID int,
|
|
@GoalSummary nvarchar(max) = NULL,
|
|
@EmotionalGoal nvarchar(max) = NULL,
|
|
@ReaderTakeaway nvarchar(max) = NULL,
|
|
@MustInclude nvarchar(max) = NULL,
|
|
@RisksOrConcerns nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF EXISTS (SELECT 1 FROM dbo.ChapterGoals WHERE ChapterID = @ChapterID)
|
|
BEGIN
|
|
UPDATE dbo.ChapterGoals
|
|
SET GoalSummary = @GoalSummary,
|
|
EmotionalGoal = @EmotionalGoal,
|
|
ReaderTakeaway = @ReaderTakeaway,
|
|
MustInclude = @MustInclude,
|
|
RisksOrConcerns = @RisksOrConcerns,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE ChapterID = @ChapterID;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT dbo.ChapterGoals (ChapterID, GoalSummary, EmotionalGoal, ReaderTakeaway, MustInclude, RisksOrConcerns)
|
|
VALUES (@ChapterID, @GoalSummary, @EmotionalGoal, @ReaderTakeaway, @MustInclude, @RisksOrConcerns);
|
|
END;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ChapterWorkflow_Summary
|
|
@ChapterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT s.SceneID, s.SceneNumber, s.SceneTitle,
|
|
COALESCE(sw.DraftStatus, 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
|
|
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,
|
|
COALESCE(sw.DraftStatus, 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
|
|
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.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'ContinueWriting' AND (sb.DraftStatus IN (N'Ready To Draft', N'Drafting') OR sb.ReadyForDraft = 1 OR ISNULL(sb.Priority, 0) >= 8))
|
|
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.DraftStatus IN (N'Drafted', N'Revising') OR sb.ReadyForRevision = 1))
|
|
OR (@Queue = N'PolishingQueue' AND (sb.DraftStatus = N'Polishing' OR sb.ReadyForPolish = 1))
|
|
ORDER BY ISNULL(sb.Priority, 0) DESC, sb.BookSortOrder, sb.ChapterSortOrder, sb.SceneSortOrder, sb.SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WriterDashboard_Reminders
|
|
@ProjectID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (60) N'Chapter Goal' AS Category, N'Info' AS SeverityName,
|
|
CONCAT(N'Chapter ', c.ChapterNumber, N' needs intent') AS Title,
|
|
N'Goal summary is empty.' AS Detail,
|
|
CAST(NULL AS int) AS SceneID, c.ChapterID AS EntityID, N'Chapter' AS EntityType
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
|
|
LEFT JOIN dbo.ChapterGoals cg ON cg.ChapterID = c.ChapterID
|
|
WHERE c.IsArchived = 0 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID)
|
|
AND NULLIF(LTRIM(RTRIM(cg.GoalSummary)), N'') IS NULL
|
|
UNION ALL
|
|
SELECT N'Plot Thread', N'Info', pt.ThreadTitle,
|
|
CONCAT(N'Unresolved thread in ', pl.PlotLineName, N'.'),
|
|
CAST(NULL AS int), pt.PlotThreadID, N'PlotThread'
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.IsArchived = 0
|
|
WHERE pt.IsArchived = 0 AND ts.IsOpenStatus = 1 AND (@ProjectID IS NULL OR pl.ProjectID = @ProjectID)
|
|
UNION ALL
|
|
SELECT N'Writer Note', N'Warning', COALESCE(sn.NoteTitle, s.SceneTitle),
|
|
sn.NoteText, s.SceneID, sn.SceneNoteID, N'SceneNote'
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID AND s.IsArchived = 0
|
|
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
|
|
WHERE sn.IsResolved = 0 AND snt.TypeName IN (N'TODO', N'Continuity Note', N'Revision Note') AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.StoryBible_Search
|
|
@ProjectID int,
|
|
@Query nvarchar(200) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NULLIF(LTRIM(RTRIM(@Query)), N'') IS NULL
|
|
RETURN;
|
|
|
|
DECLARE @Term nvarchar(204) = N'%' + LTRIM(RTRIM(@Query)) + N'%';
|
|
|
|
SELECT N'Character' AS ResultType, ch.CharacterID AS EntityID, ch.CharacterName AS Title,
|
|
ch.DefaultDescription AS Detail, CAST(NULL AS int) AS SceneID
|
|
FROM dbo.Characters ch
|
|
WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0
|
|
AND (ch.CharacterName LIKE @Term OR ch.ShortName LIKE @Term OR ch.DefaultDescription LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Scene', s.SceneID, CONCAT(N'Scene ', s.SceneNumber, N': ', s.SceneTitle),
|
|
s.Summary, s.SceneID
|
|
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.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE s.IsArchived = 0 AND (s.SceneTitle LIKE @Term OR s.Summary LIKE @Term OR s.ScenePurposeNotes LIKE @Term OR s.SceneOutcomeNotes LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Plot Thread', pt.PlotThreadID, pt.ThreadTitle, pt.Summary, CAST(NULL AS int)
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.ProjectID = @ProjectID AND pl.IsArchived = 0
|
|
WHERE pt.IsArchived = 0 AND (pt.ThreadTitle LIKE @Term OR pt.Summary LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Story Asset', sa.StoryAssetID, sa.AssetName, sa.Description, CAST(NULL AS int)
|
|
FROM dbo.StoryAssets sa
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (sa.AssetName LIKE @Term OR sa.Description LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Location', lp.LocationID, lp.LocationPath, lp.Description, CAST(NULL AS int)
|
|
FROM dbo.LocationPaths lp
|
|
WHERE lp.ProjectID = @ProjectID AND (lp.LocationName LIKE @Term OR lp.LocationPath LIKE @Term OR lp.Description LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Relationship', cr.CharacterRelationshipID, CONCAT(ca.CharacterName, N' - ', cb.CharacterName),
|
|
CONCAT(rt.TypeName, N'. ', ISNULL(cr.Notes, N'')), CAST(NULL AS int)
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
WHERE cr.ProjectID = @ProjectID AND cr.IsArchived = 0
|
|
AND (ca.CharacterName LIKE @Term OR cb.CharacterName LIKE @Term OR rt.TypeName LIKE @Term OR cr.Notes LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Warning', cw.ContinuityWarningID, cw.Message, cw.Details, cw.SceneID
|
|
FROM dbo.ContinuityWarnings cw
|
|
WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
|
|
AND (cw.Message LIKE @Term OR cw.Details LIKE @Term OR cw.EntityType LIKE @Term)
|
|
UNION ALL
|
|
SELECT N'Scene Note', sn.SceneNoteID, COALESCE(sn.NoteTitle, CONCAT(snt.TypeName, N' for ', s.SceneTitle)),
|
|
sn.NoteText, sn.SceneID
|
|
FROM dbo.SceneNotes sn
|
|
INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID AND s.IsArchived = 0
|
|
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.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE sn.NoteTitle LIKE @Term OR sn.NoteText LIKE @Term OR snt.TypeName LIKE @Term
|
|
UNION ALL
|
|
SELECT N'Chapter Goal', cg.ChapterGoalID, CONCAT(N'Chapter ', c.ChapterNumber, N': ', c.ChapterTitle),
|
|
CONCAT(ISNULL(cg.GoalSummary, N''), N' ', ISNULL(cg.EmotionalGoal, N''), N' ', ISNULL(cg.ReaderTakeaway, N''), N' ', ISNULL(cg.MustInclude, N''), N' ', ISNULL(cg.RisksOrConcerns, N'')),
|
|
CAST(NULL AS int)
|
|
FROM dbo.ChapterGoals cg
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = cg.ChapterID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE cg.GoalSummary LIKE @Term OR cg.EmotionalGoal LIKE @Term OR cg.ReaderTakeaway LIKE @Term OR cg.MustInclude LIKE @Term OR cg.RisksOrConcerns LIKE @Term
|
|
UNION ALL
|
|
SELECT N'Attachment', sa.SceneAttachmentID, sa.Title,
|
|
CONCAT(ISNULL(sa.ExternalUrl, N''), N' ', ISNULL(sa.FilePath, N''), N' ', ISNULL(sa.Notes, N'')), sa.SceneID
|
|
FROM dbo.SceneAttachments sa
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID AND s.IsArchived = 0
|
|
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.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE sa.Title LIKE @Term OR sa.ExternalUrl LIKE @Term OR sa.FilePath LIKE @Term OR sa.Notes LIKE @Term
|
|
UNION ALL
|
|
SELECT N'Workflow', sw.SceneWorkflowID, CONCAT(N'Workflow: ', s.SceneTitle),
|
|
sw.BlockedReason, s.SceneID
|
|
FROM dbo.SceneWorkflow sw
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sw.SceneID AND s.IsArchived = 0
|
|
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.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
WHERE sw.BlockedReason LIKE @Term OR sw.DraftStatus LIKE @Term
|
|
ORDER BY ResultType, Title;
|
|
END;
|
|
GO
|