190 lines
7.2 KiB
Transact-SQL
190 lines
7.2 KiB
Transact-SQL
IF OBJECT_ID(N'dbo.SceneAssetSuggestions', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.SceneAssetSuggestions
|
|
(
|
|
SceneAssetSuggestionID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneAssetSuggestions PRIMARY KEY,
|
|
SceneID int NOT NULL,
|
|
AssetID int NOT NULL,
|
|
SuggestionSource nvarchar(50) NOT NULL CONSTRAINT DF_SceneAssetSuggestions_Source DEFAULT (N'Word Companion'),
|
|
DetectedUtc datetime2 NOT NULL CONSTRAINT DF_SceneAssetSuggestions_DetectedUtc DEFAULT SYSUTCDATETIME(),
|
|
Status nvarchar(20) NOT NULL CONSTRAINT DF_SceneAssetSuggestions_Status DEFAULT (N'Pending'),
|
|
Confidence nvarchar(20) NOT NULL CONSTRAINT DF_SceneAssetSuggestions_Confidence DEFAULT (N'High'),
|
|
Reason nvarchar(200) NULL,
|
|
CreatedByUserID int NOT NULL,
|
|
ReviewedByUserID int NULL,
|
|
ReviewedUtc datetime2 NULL,
|
|
CONSTRAINT FK_SceneAssetSuggestions_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
|
|
CONSTRAINT FK_SceneAssetSuggestions_StoryAssets FOREIGN KEY (AssetID) REFERENCES dbo.StoryAssets(StoryAssetID),
|
|
CONSTRAINT FK_SceneAssetSuggestions_CreatedByUser FOREIGN KEY (CreatedByUserID) REFERENCES dbo.AppUser(UserID),
|
|
CONSTRAINT FK_SceneAssetSuggestions_ReviewedByUser FOREIGN KEY (ReviewedByUserID) REFERENCES dbo.AppUser(UserID),
|
|
CONSTRAINT CK_SceneAssetSuggestions_Status CHECK (Status IN (N'Pending', N'Accepted', N'Rejected', N'Dismissed')),
|
|
CONSTRAINT CK_SceneAssetSuggestions_Confidence CHECK (Confidence IN (N'High', N'Medium', N'Low'))
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_SceneAssetSuggestions_Pending' AND object_id = OBJECT_ID(N'dbo.SceneAssetSuggestions'))
|
|
CREATE UNIQUE INDEX UX_SceneAssetSuggestions_Pending ON dbo.SceneAssetSuggestions(SceneID, AssetID) WHERE Status = N'Pending';
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneAssetSuggestions_SceneStatus' AND object_id = OBJECT_ID(N'dbo.SceneAssetSuggestions'))
|
|
CREATE INDEX IX_SceneAssetSuggestions_SceneStatus ON dbo.SceneAssetSuggestions(SceneID, Status, AssetID);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Runtime_AssetAlias_List
|
|
@UserID int,
|
|
@ProjectID int,
|
|
@DocumentGuid uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @DocumentGuid IS NULL
|
|
OR @DocumentGuid = '00000000-0000-0000-0000-000000000000'
|
|
OR @ProjectID <= 0
|
|
RETURN;
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.ManuscriptDocuments md
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = md.ProjectID
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = md.ProjectID
|
|
WHERE md.DocumentGuid = @DocumentGuid
|
|
AND md.ProjectID = @ProjectID
|
|
AND md.IsActive = 1
|
|
AND p.IsArchived = 0
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
)
|
|
RETURN;
|
|
|
|
SELECT sa.StoryAssetID AS AssetId,
|
|
sa.AssetName AS Name,
|
|
sa.AssetName AS MatchText,
|
|
CAST(1 AS bit) AS IsDisplayName
|
|
FROM dbo.StoryAssets sa
|
|
WHERE sa.ProjectID = @ProjectID
|
|
AND sa.IsArchived = 0
|
|
AND NULLIF(LTRIM(RTRIM(sa.AssetName)), N'') IS NOT NULL
|
|
|
|
UNION ALL
|
|
|
|
SELECT sa.StoryAssetID AS AssetId,
|
|
sa.AssetName AS Name,
|
|
aa.Alias AS MatchText,
|
|
CAST(0 AS bit) AS IsDisplayName
|
|
FROM dbo.StoryAssets sa
|
|
INNER JOIN dbo.AssetAliases aa ON aa.StoryAssetID = sa.StoryAssetID
|
|
WHERE sa.ProjectID = @ProjectID
|
|
AND sa.IsArchived = 0
|
|
AND NULLIF(LTRIM(RTRIM(aa.Alias)), N'') IS NOT NULL
|
|
ORDER BY Name, AssetId, IsDisplayName DESC, MatchText;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Runtime_AssetSuggestions_Create
|
|
@UserID int,
|
|
@DocumentGuid uniqueidentifier,
|
|
@SceneID int,
|
|
@AssetIds nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF @DocumentGuid IS NULL
|
|
OR @DocumentGuid = '00000000-0000-0000-0000-000000000000'
|
|
OR @SceneID <= 0
|
|
RETURN;
|
|
|
|
DECLARE @ProjectID int;
|
|
DECLARE @ManuscriptDocumentID int;
|
|
|
|
SELECT @ProjectID = b.ProjectID,
|
|
@ManuscriptDocumentID = md.ManuscriptDocumentID
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.ManuscriptDocuments md ON md.BookID = b.BookID
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = b.ProjectID
|
|
WHERE s.SceneID = @SceneID
|
|
AND s.IsArchived = 0
|
|
AND c.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
AND p.IsArchived = 0
|
|
AND md.DocumentGuid = @DocumentGuid
|
|
AND md.IsActive = 1
|
|
AND s.IsLinkedToManuscript = 1
|
|
AND s.ManuscriptDocumentID = md.ManuscriptDocumentID
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
|
|
IF @ProjectID IS NULL
|
|
RETURN;
|
|
|
|
DECLARE @Requested table (AssetID int NOT NULL PRIMARY KEY);
|
|
|
|
INSERT @Requested (AssetID)
|
|
SELECT DISTINCT TRY_CONVERT(int, LTRIM(RTRIM(value)))
|
|
FROM STRING_SPLIT(COALESCE(@AssetIds, N''), N',')
|
|
WHERE TRY_CONVERT(int, LTRIM(RTRIM(value))) IS NOT NULL
|
|
AND TRY_CONVERT(int, LTRIM(RTRIM(value))) > 0;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM @Requested)
|
|
BEGIN
|
|
SELECT @SceneID AS SceneId,
|
|
0 AS CreatedCount,
|
|
0 AS SkippedCount,
|
|
N'No asset suggestions detected.' AS Message;
|
|
RETURN;
|
|
END;
|
|
|
|
IF EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM @Requested requested
|
|
WHERE NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.StoryAssets sa
|
|
WHERE sa.StoryAssetID = requested.AssetID
|
|
AND sa.ProjectID = @ProjectID
|
|
AND sa.IsArchived = 0
|
|
)
|
|
)
|
|
RETURN;
|
|
|
|
DECLARE @Inserted table (AssetID int NOT NULL PRIMARY KEY);
|
|
|
|
INSERT dbo.SceneAssetSuggestions
|
|
(SceneID, AssetID, SuggestionSource, Status, Confidence, Reason, CreatedByUserID)
|
|
OUTPUT inserted.AssetID INTO @Inserted
|
|
SELECT @SceneID,
|
|
requested.AssetID,
|
|
N'Word Companion',
|
|
N'Pending',
|
|
N'High',
|
|
N'Detected in manuscript text',
|
|
@UserID
|
|
FROM @Requested requested
|
|
WHERE NOT EXISTS
|
|
(SELECT 1 FROM dbo.AssetEvents ae WHERE ae.SceneID = @SceneID AND ae.StoryAssetID = requested.AssetID)
|
|
AND NOT EXISTS
|
|
(SELECT 1 FROM dbo.SceneAssetLocations sal WHERE sal.SceneID = @SceneID AND sal.StoryAssetID = requested.AssetID)
|
|
AND NOT EXISTS
|
|
(SELECT 1 FROM dbo.AssetCustodyEvents ace WHERE ace.SceneID = @SceneID AND ace.StoryAssetID = requested.AssetID)
|
|
AND NOT EXISTS
|
|
(SELECT 1 FROM dbo.SceneAssetSuggestions suggestion WHERE suggestion.SceneID = @SceneID AND suggestion.AssetID = requested.AssetID);
|
|
|
|
SELECT @SceneID AS SceneId,
|
|
(SELECT COUNT(*) FROM @Inserted) AS CreatedCount,
|
|
(SELECT COUNT(*) FROM @Requested) - (SELECT COUNT(*) FROM @Inserted) AS SkippedCount,
|
|
CASE
|
|
WHEN (SELECT COUNT(*) FROM @Inserted) = 1 THEN N'1 asset suggestion detected.'
|
|
WHEN (SELECT COUNT(*) FROM @Inserted) > 1 THEN CONCAT((SELECT COUNT(*) FROM @Inserted), N' asset suggestions detected.')
|
|
ELSE N'No new asset suggestions.'
|
|
END AS Message;
|
|
END;
|
|
GO
|