670 lines
32 KiB
Transact-SQL
670 lines
32 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.GenreMetricPresets', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.GenreMetricPresets
|
|
(
|
|
GenreMetricPresetID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_GenreMetricPresets PRIMARY KEY,
|
|
PresetKey nvarchar(50) NOT NULL,
|
|
PresetName nvarchar(100) NOT NULL,
|
|
Description nvarchar(500) NULL,
|
|
SortOrder int NOT NULL,
|
|
IsActive bit NOT NULL CONSTRAINT DF_GenreMetricPresets_IsActive DEFAULT 1,
|
|
CONSTRAINT UQ_GenreMetricPresets_PresetKey UNIQUE(PresetKey)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.GenreMetricPresetMetrics', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.GenreMetricPresetMetrics
|
|
(
|
|
GenreMetricPresetID int NOT NULL,
|
|
MetricTypeID int NOT NULL,
|
|
SortOrder int NOT NULL,
|
|
CONSTRAINT PK_GenreMetricPresetMetrics PRIMARY KEY(GenreMetricPresetID, MetricTypeID),
|
|
CONSTRAINT FK_GenreMetricPresetMetrics_Presets FOREIGN KEY(GenreMetricPresetID) REFERENCES dbo.GenreMetricPresets(GenreMetricPresetID),
|
|
CONSTRAINT FK_GenreMetricPresetMetrics_MetricTypes FOREIGN KEY(MetricTypeID) REFERENCES dbo.SceneMetricTypes(MetricTypeID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
MERGE dbo.GenreMetricPresets AS target
|
|
USING (VALUES
|
|
(N'Custom', N'Custom / No preset', N'Use the core PlotDirector metrics only.', 10),
|
|
(N'CrimeThriller', N'Crime / Thriller', N'Suspense, danger, investigation and clues.', 20),
|
|
(N'Romance', N'Romance', N'Emotional intimacy, conflict, trust, longing and chemistry.', 30),
|
|
(N'Fantasy', N'Fantasy', N'Wonder, world building, magic, politics and quest progress.', 40),
|
|
(N'Horror', N'Horror', N'Dread, fear, isolation, gore and helplessness.', 50),
|
|
(N'HistoricalFiction', N'Historical Fiction', N'Immersion, authenticity and social or political pressure.', 60),
|
|
(N'LiteraryFiction', N'Literary Fiction', N'Character growth, internal conflict, subtext and theme.', 70)
|
|
) AS source (PresetKey, PresetName, Description, SortOrder)
|
|
ON target.PresetKey = source.PresetKey
|
|
WHEN MATCHED THEN
|
|
UPDATE SET PresetName = source.PresetName,
|
|
Description = source.Description,
|
|
SortOrder = source.SortOrder,
|
|
IsActive = 1
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (PresetKey, PresetName, Description, SortOrder, IsActive)
|
|
VALUES (source.PresetKey, source.PresetName, source.Description, source.SortOrder, 1);
|
|
GO
|
|
|
|
MERGE dbo.SceneMetricTypes AS target
|
|
USING (VALUES
|
|
(N'Suspense', N'Rising uncertainty and anticipation of danger.', 5, 210),
|
|
(N'Danger', N'Immediate physical, emotional, or strategic risk.', 5, 220),
|
|
(N'Investigation Progress', N'How far the truth-finding or case-solving thread advances.', 5, 230),
|
|
(N'Clue Density', N'How much meaningful clue material appears in the scene.', 4, 240),
|
|
(N'Threat Proximity', N'How close the active threat feels to the characters.', 5, 250),
|
|
(N'Emotional Intimacy', N'Emotional closeness, vulnerability, and connection.', 5, 310),
|
|
(N'Relationship Conflict', N'Tension, friction, or opposing needs within the relationship.', 5, 320),
|
|
(N'Trust', N'How much trust is present, earned, tested, or repaired.', 5, 330),
|
|
(N'Longing', N'Yearning, desire, separation, or unresolved emotional pull.', 5, 340),
|
|
(N'Chemistry', N'Spark, attraction, and interpersonal charge.', 5, 350),
|
|
(N'Wonder', N'Sense of awe, strangeness, discovery, or enchantment.', 5, 410),
|
|
(N'World Building', N'How much setting, culture, history, or rules are developed.', 4, 420),
|
|
(N'Magic Presence', N'How strongly magic or the fantastic shapes the scene.', 4, 430),
|
|
(N'Political Intrigue', N'Power manoeuvring, faction pressure, and strategic schemes.', 5, 440),
|
|
(N'Quest Progress', N'How much the central journey or mission advances.', 5, 450),
|
|
(N'Dread', N'Slow-building unease and expectation of something terrible.', 5, 510),
|
|
(N'Fear', N'Immediate fear or panic experienced by characters or reader.', 5, 520),
|
|
(N'Isolation', N'How alone, cut off, or unsupported characters feel.', 5, 530),
|
|
(N'Gore', N'Graphic bodily harm or visceral horror content.', 1, 540),
|
|
(N'Helplessness', N'Loss of control, trapped choices, or inability to act.', 5, 550),
|
|
(N'Historical Immersion', N'How strongly the scene evokes its period and setting.', 5, 610),
|
|
(N'Social Pressure', N'Expectations, class, family, reputation, and public constraint.', 5, 620),
|
|
(N'Political Tension', N'Period power pressure, public conflict, or institutional risk.', 5, 630),
|
|
(N'Authenticity', N'How heavily the scene relies on period-specific detail or accuracy.', 5, 640),
|
|
(N'Cultural Conflict', N'Conflict arising from customs, values, identity, or social change.', 5, 650),
|
|
(N'Character Growth', N'Visible internal change, self-knowledge, or development.', 5, 710),
|
|
(N'Internal Conflict', N'Inner contradiction, dilemma, doubt, or self-opposition.', 5, 720),
|
|
(N'Subtext', N'Unspoken meaning, implication, and tension beneath dialogue or action.', 5, 730),
|
|
(N'Theme Strength', N'How strongly the scene expresses or complicates the work''s themes.', 5, 740),
|
|
(N'Emotional Resonance', N'Aftertaste, depth, and lingering emotional impact.', 5, 750)
|
|
) AS source (MetricName, Description, DefaultValue, SortOrder)
|
|
ON target.ProjectID IS NULL AND target.MetricName = source.MetricName
|
|
WHEN MATCHED THEN
|
|
UPDATE SET Description = source.Description,
|
|
MinValue = 1,
|
|
MaxValue = 10,
|
|
DefaultValue = source.DefaultValue,
|
|
SortOrder = source.SortOrder,
|
|
IsActive = 1
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (ProjectID, MetricName, Description, MinValue, MaxValue, DefaultValue, SortOrder, IsActive)
|
|
VALUES (NULL, source.MetricName, source.Description, 1, 10, source.DefaultValue, source.SortOrder, 1);
|
|
GO
|
|
|
|
DELETE gpmm
|
|
FROM dbo.GenreMetricPresetMetrics gpmm
|
|
INNER JOIN dbo.GenreMetricPresets gmp ON gmp.GenreMetricPresetID = gpmm.GenreMetricPresetID;
|
|
GO
|
|
|
|
INSERT dbo.GenreMetricPresetMetrics (GenreMetricPresetID, MetricTypeID, SortOrder)
|
|
SELECT gmp.GenreMetricPresetID, smt.MetricTypeID, rows.SortOrder
|
|
FROM
|
|
(
|
|
VALUES
|
|
(N'CrimeThriller', N'Suspense', 210),
|
|
(N'CrimeThriller', N'Danger', 220),
|
|
(N'CrimeThriller', N'Investigation Progress', 230),
|
|
(N'CrimeThriller', N'Clue Density', 240),
|
|
(N'CrimeThriller', N'Threat Proximity', 250),
|
|
(N'Romance', N'Emotional Intimacy', 310),
|
|
(N'Romance', N'Relationship Conflict', 320),
|
|
(N'Romance', N'Trust', 330),
|
|
(N'Romance', N'Longing', 340),
|
|
(N'Romance', N'Chemistry', 350),
|
|
(N'Fantasy', N'Wonder', 410),
|
|
(N'Fantasy', N'World Building', 420),
|
|
(N'Fantasy', N'Magic Presence', 430),
|
|
(N'Fantasy', N'Political Intrigue', 440),
|
|
(N'Fantasy', N'Quest Progress', 450),
|
|
(N'Horror', N'Dread', 510),
|
|
(N'Horror', N'Fear', 520),
|
|
(N'Horror', N'Isolation', 530),
|
|
(N'Horror', N'Gore', 540),
|
|
(N'Horror', N'Helplessness', 550),
|
|
(N'HistoricalFiction', N'Historical Immersion', 610),
|
|
(N'HistoricalFiction', N'Social Pressure', 620),
|
|
(N'HistoricalFiction', N'Political Tension', 630),
|
|
(N'HistoricalFiction', N'Authenticity', 640),
|
|
(N'HistoricalFiction', N'Cultural Conflict', 650),
|
|
(N'LiteraryFiction', N'Character Growth', 710),
|
|
(N'LiteraryFiction', N'Internal Conflict', 720),
|
|
(N'LiteraryFiction', N'Subtext', 730),
|
|
(N'LiteraryFiction', N'Theme Strength', 740),
|
|
(N'LiteraryFiction', N'Emotional Resonance', 750)
|
|
) rows (PresetKey, MetricName, SortOrder)
|
|
INNER JOIN dbo.GenreMetricPresets gmp ON gmp.PresetKey = rows.PresetKey
|
|
INNER JOIN dbo.SceneMetricTypes smt ON smt.ProjectID IS NULL AND smt.MetricName = rows.MetricName
|
|
WHERE NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.GenreMetricPresetMetrics existing
|
|
WHERE existing.GenreMetricPresetID = gmp.GenreMetricPresetID
|
|
AND existing.MetricTypeID = smt.MetricTypeID
|
|
);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.GenreMetricPreset_List
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT GenreMetricPresetID, PresetKey, PresetName, Description, SortOrder, IsActive
|
|
FROM dbo.GenreMetricPresets
|
|
WHERE IsActive = 1
|
|
ORDER BY SortOrder, PresetName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectMetricPreset_Apply
|
|
@ProjectID int,
|
|
@GenreMetricPresetKey nvarchar(50) = N'Custom'
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.Projects WHERE ProjectID = @ProjectID AND IsArchived = 0)
|
|
RETURN;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.ProjectTimelineSettings WHERE ProjectID = @ProjectID)
|
|
BEGIN
|
|
INSERT dbo.ProjectTimelineSettings
|
|
(ProjectID, ShowSceneCards, ShowMetricShape, ShowPlotLines, ShowStoryAssets, ShowCharacterAppearances, ShowWarnings)
|
|
VALUES
|
|
(@ProjectID, 1, 1, 1, 1, 1, 1);
|
|
END;
|
|
|
|
DECLARE @PresetID int =
|
|
(
|
|
SELECT GenreMetricPresetID
|
|
FROM dbo.GenreMetricPresets
|
|
WHERE PresetKey = COALESCE(NULLIF(LTRIM(RTRIM(@GenreMetricPresetKey)), N''), N'Custom')
|
|
AND IsActive = 1
|
|
);
|
|
|
|
DELETE FROM dbo.ProjectTimelineMetricSettings
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
INSERT dbo.ProjectTimelineMetricSettings (ProjectID, MetricTypeID, SortOrder)
|
|
SELECT @ProjectID, mt.MetricTypeID, mt.SortOrder
|
|
FROM dbo.SceneMetricTypes mt
|
|
WHERE mt.ProjectID IS NULL
|
|
AND mt.IsActive = 1
|
|
AND mt.MetricName IN
|
|
(
|
|
N'Overall Intensity', N'Tension', N'Emotional Weight', N'Action', N'Darkness',
|
|
N'Romance', N'Sexual Charge', N'Violence', N'Hope / Lightness', N'Comedy', N'Mystery'
|
|
)
|
|
UNION
|
|
SELECT @ProjectID, mt.MetricTypeID, gpmm.SortOrder
|
|
FROM dbo.GenreMetricPresetMetrics gpmm
|
|
INNER JOIN dbo.SceneMetricTypes mt ON mt.MetricTypeID = gpmm.MetricTypeID
|
|
WHERE gpmm.GenreMetricPresetID = @PresetID
|
|
AND mt.IsActive = 1
|
|
AND (mt.ProjectID IS NULL OR mt.ProjectID = @ProjectID);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Project_Save
|
|
@ProjectID int = NULL,
|
|
@ProjectName nvarchar(200),
|
|
@Description nvarchar(max) = NULL,
|
|
@GenreMetricPresetKey nvarchar(50) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @ProjectID IS NULL OR @ProjectID = 0
|
|
BEGIN
|
|
INSERT dbo.Projects (ProjectName, Description)
|
|
VALUES (@ProjectName, @Description);
|
|
|
|
SET @ProjectID = CAST(SCOPE_IDENTITY() AS int);
|
|
EXEC dbo.ProjectMetricPreset_Apply @ProjectID = @ProjectID, @GenreMetricPresetKey = @GenreMetricPresetKey;
|
|
|
|
SELECT @ProjectID AS ProjectID;
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.Projects
|
|
SET ProjectName = @ProjectName,
|
|
Description = @Description,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE ProjectID = @ProjectID;
|
|
|
|
SELECT @ProjectID AS ProjectID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneMetricType_List
|
|
@ProjectID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @HasMetricSettings bit =
|
|
CASE WHEN @ProjectID IS NOT NULL AND EXISTS (SELECT 1 FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID) THEN 1 ELSE 0 END;
|
|
|
|
SELECT MetricTypeID, ProjectID, MetricName, Description, MinValue, MaxValue, DefaultValue, SortOrder, IsActive
|
|
FROM dbo.SceneMetricTypes mt
|
|
WHERE mt.IsActive = 1
|
|
AND (mt.ProjectID IS NULL OR mt.ProjectID = @ProjectID)
|
|
AND
|
|
(
|
|
@ProjectID IS NULL
|
|
OR (@HasMetricSettings = 1 AND EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ProjectTimelineMetricSettings ptms
|
|
WHERE ptms.ProjectID = @ProjectID AND ptms.MetricTypeID = mt.MetricTypeID
|
|
))
|
|
OR (@HasMetricSettings = 0 AND mt.MetricName IN
|
|
(
|
|
N'Overall Intensity', N'Tension', N'Emotional Weight', N'Action', N'Darkness',
|
|
N'Romance', N'Sexual Charge', N'Violence', N'Hope / Lightness', N'Comedy', N'Mystery'
|
|
))
|
|
)
|
|
ORDER BY mt.SortOrder, mt.MetricName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneMetric_EnsureDefaults
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ProjectID int;
|
|
|
|
SELECT @ProjectID = b.ProjectID
|
|
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.SceneID = @SceneID;
|
|
|
|
DECLARE @HasMetricSettings bit =
|
|
CASE WHEN EXISTS (SELECT 1 FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID) THEN 1 ELSE 0 END;
|
|
|
|
INSERT dbo.SceneMetricValues (SceneID, MetricTypeID, Value, Notes)
|
|
SELECT @SceneID, mt.MetricTypeID, mt.DefaultValue, NULL
|
|
FROM dbo.SceneMetricTypes mt
|
|
WHERE mt.IsActive = 1
|
|
AND (mt.ProjectID IS NULL OR mt.ProjectID = @ProjectID)
|
|
AND
|
|
(
|
|
(@HasMetricSettings = 1 AND EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ProjectTimelineMetricSettings ptms
|
|
WHERE ptms.ProjectID = @ProjectID AND ptms.MetricTypeID = mt.MetricTypeID
|
|
))
|
|
OR (@HasMetricSettings = 0 AND mt.MetricName IN
|
|
(
|
|
N'Overall Intensity', N'Tension', N'Emotional Weight', N'Action', N'Darkness',
|
|
N'Romance', N'Sexual Charge', N'Violence', N'Hope / Lightness', N'Comedy', N'Mystery'
|
|
))
|
|
)
|
|
AND NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.SceneMetricValues smv
|
|
WHERE smv.SceneID = @SceneID AND smv.MetricTypeID = mt.MetricTypeID
|
|
);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneMetric_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
EXEC dbo.SceneMetric_EnsureDefaults @SceneID = @SceneID;
|
|
|
|
DECLARE @ProjectID int;
|
|
|
|
SELECT @ProjectID = b.ProjectID
|
|
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.SceneID = @SceneID;
|
|
|
|
DECLARE @HasMetricSettings bit =
|
|
CASE WHEN EXISTS (SELECT 1 FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID) THEN 1 ELSE 0 END;
|
|
|
|
SELECT mt.MetricTypeID, mt.ProjectID, mt.MetricName, mt.Description, mt.MinValue, mt.MaxValue,
|
|
mt.DefaultValue, mt.SortOrder, mt.IsActive, smv.SceneID, smv.Value, smv.Notes
|
|
FROM dbo.SceneMetricTypes mt
|
|
INNER JOIN dbo.SceneMetricValues smv ON smv.MetricTypeID = mt.MetricTypeID AND smv.SceneID = @SceneID
|
|
WHERE mt.IsActive = 1
|
|
AND
|
|
(
|
|
(@HasMetricSettings = 1 AND EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ProjectTimelineMetricSettings ptms
|
|
WHERE ptms.ProjectID = @ProjectID AND ptms.MetricTypeID = mt.MetricTypeID
|
|
))
|
|
OR (@HasMetricSettings = 0 AND mt.MetricName IN
|
|
(
|
|
N'Overall Intensity', N'Tension', N'Emotional Weight', N'Action', N'Darkness',
|
|
N'Romance', N'Sexual Charge', N'Violence', N'Hope / Lightness', N'Comedy', N'Mystery'
|
|
))
|
|
)
|
|
ORDER BY mt.SortOrder, mt.MetricName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Analytics_StoryHealth_Remainder
|
|
@ProjectID int,
|
|
@BookID int = NULL,
|
|
@StartChapterNumber decimal(10,2) = NULL,
|
|
@EndChapterNumber decimal(10,2) = NULL,
|
|
@StartSceneNumber decimal(10,2) = NULL,
|
|
@EndSceneNumber decimal(10,2) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
;WITH EventScope AS
|
|
(
|
|
SELECT pt.PlotThreadID, MAX(nso.GlobalSceneIndex) AS LastTouchedIndex, MAX(nso.SceneID) AS LastTouchedSceneID,
|
|
MAX(CONCAT(N'Ch ', nso.ChapterNumber, N' / Scene ', nso.SceneNumber, N': ', nso.SceneTitle)) AS LastTouchedSceneLabel
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.ThreadEvents te ON te.PlotThreadID = pt.PlotThreadID
|
|
INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = te.SceneID
|
|
WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
GROUP BY pt.PlotThreadID
|
|
)
|
|
SELECT pt.PlotThreadID, pt.ThreadTitle, pl.PlotLineID, pl.PlotLineName, pt.Importance,
|
|
ts.StatusName AS ThreadStatusName, ts.IsOpenStatus, ts.IsResolvedStatus,
|
|
pt.IntroducedSceneID, pt.PlannedResolutionSceneID, pt.ActualResolutionSceneID,
|
|
EventScope.LastTouchedSceneID, EventScope.LastTouchedSceneLabel,
|
|
CASE WHEN ts.IsOpenStatus = 1 AND pt.Importance >= 8 THEN 1 ELSE 0 END AS IsHighImportanceOpen,
|
|
CASE WHEN ts.IsOpenStatus = 1 AND EventScope.LastTouchedIndex IS NOT NULL
|
|
AND (SELECT MAX(GlobalSceneIndex) FROM dbo.NarrativeSceneOrder WHERE ProjectID = @ProjectID AND (@BookID IS NULL OR BookID = @BookID)) - EventScope.LastTouchedIndex > 8 THEN 1 ELSE 0 END AS IsDormant,
|
|
CASE WHEN EXISTS (
|
|
SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
|
|
WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName = N'Clue Planted'
|
|
) AND NOT EXISTS (
|
|
SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
|
|
WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName IN (N'Payoff', N'Question Answered', N'Revealed', N'Resolved')
|
|
) THEN 1 ELSE 0 END AS HasClueWithoutPayoff,
|
|
CASE WHEN EXISTS (
|
|
SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
|
|
WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName = N'Question Raised'
|
|
) AND NOT EXISTS (
|
|
SELECT 1 FROM dbo.ThreadEvents te INNER JOIN dbo.ThreadEventTypes tet ON tet.ThreadEventTypeID = te.EventTypeID
|
|
WHERE te.PlotThreadID = pt.PlotThreadID AND tet.TypeName = N'Question Answered'
|
|
) THEN 1 ELSE 0 END AS HasQuestionWithoutAnswer
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
|
|
INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
|
|
LEFT JOIN EventScope ON EventScope.PlotThreadID = pt.PlotThreadID
|
|
WHERE pl.ProjectID = @ProjectID AND pt.IsArchived = 0 AND pl.IsArchived = 0
|
|
AND (@BookID IS NULL OR pl.BookID IS NULL OR pl.BookID = @BookID)
|
|
ORDER BY ts.IsOpenStatus DESC, pt.Importance DESC, pl.PlotLineName, pt.ThreadTitle;
|
|
|
|
;WITH LastAssetEvent AS
|
|
(
|
|
SELECT ae.StoryAssetID, MAX(nso.GlobalSceneIndex) AS LastEventIndex, MAX(ae.SceneID) AS LastEventSceneID,
|
|
MAX(CONCAT(N'Ch ', nso.ChapterNumber, N' / Scene ', nso.SceneNumber, N': ', nso.SceneTitle)) AS LastEventSceneLabel
|
|
FROM dbo.AssetEvents ae
|
|
INNER JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = ae.SceneID
|
|
WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
GROUP BY ae.StoryAssetID
|
|
)
|
|
SELECT sa.StoryAssetID, sa.AssetName, ak.KindName, sa.Importance, sa.CurrentStateID,
|
|
ast.StateName AS CurrentStateName, sa.CurrentLocationID, location.LocationPath AS CurrentLocationPath,
|
|
sa.IsResolved, LastAssetEvent.LastEventSceneID, LastAssetEvent.LastEventSceneLabel,
|
|
CASE WHEN EXISTS (SELECT 1 FROM dbo.AssetDependencies ad WHERE ad.IsArchived = 0 AND (ad.SourceAssetID = sa.StoryAssetID OR ad.TargetAssetID = sa.StoryAssetID)) THEN 1 ELSE 0 END AS HasDependencies,
|
|
CASE WHEN EXISTS (
|
|
SELECT 1 FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
WHERE cw.ProjectID = @ProjectID AND cw.EntityType IN (N'StoryAsset', N'AssetDependency') AND cw.EntityID IN (sa.StoryAssetID)
|
|
AND wt.TypeName LIKE N'Asset%' AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
|
|
) THEN 1 ELSE 0 END AS HasDependencyWarnings
|
|
FROM dbo.StoryAssets sa
|
|
INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
|
|
LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = sa.CurrentLocationID
|
|
LEFT JOIN LastAssetEvent ON LastAssetEvent.StoryAssetID = sa.StoryAssetID
|
|
WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0
|
|
ORDER BY sa.IsResolved, sa.Importance DESC, sa.AssetName;
|
|
|
|
SELECT ws.SeverityName, COUNT(*) AS WarningCount
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
WHERE cw.ProjectID = @ProjectID AND (@BookID IS NULL OR cw.BookID = @BookID)
|
|
AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
|
|
GROUP BY ws.SeverityName, ws.SortOrder
|
|
ORDER BY ws.SortOrder DESC;
|
|
|
|
SELECT TOP (12) wt.TypeName AS WarningTypeName, COUNT(*) AS WarningCount
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
WHERE cw.ProjectID = @ProjectID AND (@BookID IS NULL OR cw.BookID = @BookID)
|
|
AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
|
|
GROUP BY wt.TypeName
|
|
ORDER BY COUNT(*) DESC, wt.TypeName;
|
|
|
|
SELECT TOP (12) cw.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle,
|
|
COUNT(*) AS WarningCount, MAX(ws.SortOrder) AS HighestSeveritySort
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = cw.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
WHERE cw.ProjectID = @ProjectID AND cw.SceneID IS NOT NULL AND (@BookID IS NULL OR cw.BookID = @BookID)
|
|
AND cw.IsDismissed = 0 AND cw.IsIntentional = 0
|
|
GROUP BY cw.SceneID, s.SceneNumber, s.SceneTitle, c.ChapterNumber, b.BookTitle
|
|
ORDER BY COUNT(*) DESC, MAX(ws.SortOrder) DESC;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle,
|
|
COALESCE(s.POVCharacterID, c.POVCharacterID) AS POVCharacterID
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = nso.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = nso.ChapterID
|
|
WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
)
|
|
SELECT COALESCE(ch.CharacterName, N'No POV') AS POVName, ss.POVCharacterID, COUNT(*) AS SceneCount
|
|
FROM ScopeScenes ss
|
|
LEFT JOIN dbo.Characters ch ON ch.CharacterID = ss.POVCharacterID
|
|
GROUP BY ss.POVCharacterID, ch.CharacterName
|
|
ORDER BY COUNT(*) DESC, POVName;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.ChapterID
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
)
|
|
SELECT c.ChapterID, c.ChapterNumber, c.ChapterTitle, COUNT(DISTINCT COALESCE(s.POVCharacterID, c.POVCharacterID)) AS POVCount
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID
|
|
WHERE c.ChapterID IN (SELECT ChapterID FROM ScopeScenes)
|
|
GROUP BY c.ChapterID, c.ChapterNumber, c.ChapterTitle
|
|
HAVING COUNT(DISTINCT COALESCE(s.POVCharacterID, c.POVCharacterID)) > 1
|
|
ORDER BY c.ChapterNumber;
|
|
|
|
SELECT ch.CharacterID, ch.CharacterName, COUNT(DISTINCT sc.SceneID) AS SceneCount
|
|
FROM dbo.Characters ch
|
|
LEFT JOIN dbo.SceneCharacters sc ON sc.CharacterID = ch.CharacterID
|
|
LEFT JOIN dbo.NarrativeSceneOrder nso ON nso.SceneID = sc.SceneID
|
|
WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0 AND (@BookID IS NULL OR nso.BookID = @BookID OR sc.SceneID IS NULL)
|
|
GROUP BY ch.CharacterID, ch.CharacterName
|
|
ORDER BY COUNT(DISTINCT sc.SceneID) DESC, ch.CharacterName;
|
|
|
|
;WITH SceneCharacterCounts AS
|
|
(
|
|
SELECT nso.SceneID, nso.SceneNumber, nso.SceneTitle, nso.ChapterNumber, COUNT(sc.SceneCharacterID) AS CharacterCount
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
LEFT JOIN dbo.SceneCharacters sc ON sc.SceneID = nso.SceneID
|
|
WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
GROUP BY nso.SceneID, nso.SceneNumber, nso.SceneTitle, nso.ChapterNumber
|
|
)
|
|
SELECT TOP (12) * FROM SceneCharacterCounts WHERE CharacterCount >= 5 ORDER BY CharacterCount DESC, ChapterNumber, SceneNumber;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, s.PrimaryLocationID
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = nso.SceneID
|
|
WHERE nso.ProjectID = @ProjectID AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
)
|
|
SELECT COALESCE(lp.LocationName, N'No location') AS LocationName, lp.LocationID, COALESCE(lp.LocationPath, N'No location') AS LocationPath,
|
|
COUNT(*) AS SceneCount
|
|
FROM ScopeScenes ss
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = ss.PrimaryLocationID
|
|
GROUP BY lp.LocationID, lp.LocationName, lp.LocationPath
|
|
ORDER BY COUNT(*) DESC, LocationName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Analytics_StoryHealth
|
|
@ProjectID int,
|
|
@BookID int = NULL,
|
|
@StartChapterNumber decimal(10,2) = NULL,
|
|
@EndChapterNumber decimal(10,2) = NULL,
|
|
@StartSceneNumber decimal(10,2) = NULL,
|
|
@EndSceneNumber decimal(10,2) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @HasMetricSettings bit =
|
|
CASE WHEN EXISTS (SELECT 1 FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID) THEN 1 ELSE 0 END;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.*
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
)
|
|
SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.ChapterID, ss.ChapterNumber, ss.ChapterTitle,
|
|
ss.SceneNumber, ss.SceneTitle, ss.GlobalSceneIndex, ss.BookSceneIndex, smt.MetricTypeID,
|
|
smt.MetricName, smv.Value, smt.MinValue, smt.MaxValue,
|
|
CASE WHEN smt.MaxValue = smt.MinValue THEN 0 ELSE CAST(((smv.Value - smt.MinValue) * 100.0) / (smt.MaxValue - smt.MinValue) AS int) END AS [Percent]
|
|
FROM ScopeScenes ss
|
|
INNER JOIN dbo.SceneMetricValues smv ON smv.SceneID = ss.SceneID
|
|
INNER JOIN dbo.SceneMetricTypes smt ON smt.MetricTypeID = smv.MetricTypeID
|
|
WHERE smt.IsActive = 1
|
|
AND
|
|
(
|
|
(@HasMetricSettings = 1 AND EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ProjectTimelineMetricSettings ptms
|
|
WHERE ptms.ProjectID = @ProjectID AND ptms.MetricTypeID = smt.MetricTypeID
|
|
))
|
|
OR (@HasMetricSettings = 0 AND smt.MetricName IN
|
|
(
|
|
N'Overall Intensity', N'Tension', N'Emotional Weight', N'Action', N'Romance',
|
|
N'Violence', N'Sexual Charge', N'Mystery', N'Hope / Lightness', N'Comedy', N'Darkness'
|
|
))
|
|
)
|
|
ORDER BY smt.SortOrder, ss.GlobalSceneIndex;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.ChapterID
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
)
|
|
SELECT rs.RevisionStatusID, rs.StatusName, COUNT(s.SceneID) AS SceneCount,
|
|
SUM(CASE WHEN c.ChapterID IS NOT NULL THEN 1 ELSE 0 END) AS ChapterCount
|
|
FROM dbo.RevisionStatuses rs
|
|
LEFT JOIN dbo.Scenes s ON s.RevisionStatusID = rs.RevisionStatusID AND s.SceneID IN (SELECT SceneID FROM ScopeScenes)
|
|
LEFT JOIN dbo.Chapters c ON c.RevisionStatusID = rs.RevisionStatusID AND c.ChapterID IN (SELECT DISTINCT ChapterID FROM ScopeScenes)
|
|
GROUP BY rs.RevisionStatusID, rs.StatusName, rs.SortOrder
|
|
ORDER BY rs.SortOrder;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
)
|
|
SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.ChapterID, ss.ChapterNumber, ss.SceneNumber, ss.SceneTitle,
|
|
rs.StatusName AS RevisionStatusName
|
|
FROM ScopeScenes ss
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ss.SceneID
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
WHERE rs.StatusName IN (N'Needs Work', N'Cut Candidate', N'Drafted')
|
|
ORDER BY rs.SortOrder, ss.BookID, ss.ChapterNumber, ss.SceneNumber;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
),
|
|
Total AS (SELECT COUNT(*) AS TotalScenes FROM ScopeScenes)
|
|
SELECT spt.ScenePurposeTypeID, spt.PurposeName, COUNT(sp.SceneID) AS SceneCount,
|
|
CASE WHEN Total.TotalScenes = 0 THEN 0 ELSE CAST(COUNT(sp.SceneID) * 100.0 / Total.TotalScenes AS decimal(5,1)) END AS PercentOfScenes
|
|
FROM dbo.ScenePurposeTypes spt
|
|
CROSS JOIN Total
|
|
LEFT JOIN dbo.ScenePurposes sp ON sp.ScenePurposeTypeID = spt.ScenePurposeTypeID AND sp.SceneID IN (SELECT SceneID FROM ScopeScenes)
|
|
WHERE spt.IsArchived = 0
|
|
GROUP BY spt.ScenePurposeTypeID, spt.PurposeName, spt.SortOrder, Total.TotalScenes
|
|
ORDER BY spt.SortOrder;
|
|
|
|
;WITH ScopeScenes AS
|
|
(
|
|
SELECT nso.SceneID, nso.BookID, nso.BookTitle, nso.ChapterID, nso.ChapterNumber, nso.SceneNumber, nso.SceneTitle
|
|
FROM dbo.NarrativeSceneOrder nso
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = nso.SceneID
|
|
WHERE nso.ProjectID = @ProjectID
|
|
AND (@BookID IS NULL OR nso.BookID = @BookID)
|
|
AND (@StartChapterNumber IS NULL OR nso.ChapterNumber >= @StartChapterNumber)
|
|
AND (@EndChapterNumber IS NULL OR nso.ChapterNumber <= @EndChapterNumber)
|
|
AND (@StartSceneNumber IS NULL OR nso.SceneNumber >= @StartSceneNumber)
|
|
AND (@EndSceneNumber IS NULL OR nso.SceneNumber <= @EndSceneNumber)
|
|
AND (
|
|
NOT EXISTS (SELECT 1 FROM dbo.ScenePurposes sp WHERE sp.SceneID = nso.SceneID)
|
|
OR ISNULL(NULLIF(LTRIM(RTRIM(s.SceneOutcomeNotes)), N''), N'') = N''
|
|
)
|
|
)
|
|
SELECT ss.SceneID, ss.BookID, ss.BookTitle, ss.ChapterID, ss.ChapterNumber, ss.SceneNumber, ss.SceneTitle,
|
|
CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.ScenePurposes sp WHERE sp.SceneID = ss.SceneID) THEN 1 ELSE 0 END AS MissingPurpose,
|
|
CASE WHEN ISNULL(NULLIF(LTRIM(RTRIM(s.SceneOutcomeNotes)), N''), N'') = N'' THEN 1 ELSE 0 END AS MissingOutcome
|
|
FROM ScopeScenes ss
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = ss.SceneID
|
|
ORDER BY ss.BookID, ss.ChapterNumber, ss.SceneNumber;
|
|
|
|
EXEC dbo.Analytics_StoryHealth_Remainder @ProjectID, @BookID, @StartChapterNumber, @EndChapterNumber, @StartSceneNumber, @EndSceneNumber;
|
|
END;
|
|
GO
|