PlotDirector/PlotLine/Sql/104_Phase16L_WordCompanionAssetSuggestionReview.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAssetSuggestions_ListByScene
    @SceneID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT suggestion.SceneAssetSuggestionID,
        suggestion.SceneID,
        suggestion.AssetID,
        sa.AssetName,
        suggestion.Status,
        suggestion.Confidence,
        suggestion.Reason,
        suggestion.DetectedUtc
    FROM dbo.SceneAssetSuggestions suggestion
    INNER JOIN dbo.Scenes s ON s.SceneID = suggestion.SceneID
    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.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = suggestion.AssetID
    WHERE suggestion.SceneID = @SceneID
        AND suggestion.Status = N'Pending'
        AND s.IsArchived = 0
        AND c.IsArchived = 0
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND sa.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1
    ORDER BY
        CASE suggestion.Confidence WHEN N'High' THEN 0 WHEN N'Medium' THEN 1 ELSE 2 END,
        sa.AssetName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAssetSuggestion_Accept
    @SceneAssetSuggestionID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SceneID int;
    DECLARE @AssetID int;
    DECLARE @AssetEventTypeID int;
    DECLARE @SceneAssetCreated bit = 0;

    SELECT @SceneID = suggestion.SceneID,
        @AssetID = suggestion.AssetID
    FROM dbo.SceneAssetSuggestions suggestion
    INNER JOIN dbo.Scenes s ON s.SceneID = suggestion.SceneID
    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.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = suggestion.AssetID AND sa.ProjectID = p.ProjectID
    WHERE suggestion.SceneAssetSuggestionID = @SceneAssetSuggestionID
        AND suggestion.Status = N'Pending'
        AND s.IsArchived = 0
        AND c.IsArchived = 0
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND sa.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1;

    IF @SceneID IS NULL OR @AssetID IS NULL
        RETURN;

    SELECT TOP (1) @AssetEventTypeID = AssetEventTypeID
    FROM dbo.AssetEventTypes
    WHERE TypeName = N'Used'
        AND IsActive = 1
    ORDER BY SortOrder, AssetEventTypeID;

    IF @AssetEventTypeID IS NULL
        SELECT TOP (1) @AssetEventTypeID = AssetEventTypeID
        FROM dbo.AssetEventTypes
        WHERE IsActive = 1
        ORDER BY SortOrder, AssetEventTypeID;

    IF @AssetEventTypeID IS NULL
        RETURN;

    IF NOT EXISTS (SELECT 1 FROM dbo.AssetEvents WHERE SceneID = @SceneID AND StoryAssetID = @AssetID)
        AND NOT EXISTS (SELECT 1 FROM dbo.SceneAssetLocations WHERE SceneID = @SceneID AND StoryAssetID = @AssetID)
        AND NOT EXISTS (SELECT 1 FROM dbo.AssetCustodyEvents WHERE SceneID = @SceneID AND StoryAssetID = @AssetID)
    BEGIN
        INSERT dbo.AssetEvents (StoryAssetID, SceneID, AssetEventTypeID, FromStateID, ToStateID, EventTitle, EventDescription)
        VALUES (@AssetID, @SceneID, @AssetEventTypeID, NULL, NULL, N'Used', NULL);

        SET @SceneAssetCreated = 1;
    END;

    UPDATE dbo.SceneAssetSuggestions
    SET Status = N'Accepted',
        ReviewedByUserID = @UserID,
        ReviewedUtc = SYSUTCDATETIME()
    WHERE SceneAssetSuggestionID = @SceneAssetSuggestionID
        AND Status = N'Pending';

    SELECT @SceneAssetSuggestionID AS SceneAssetSuggestionID,
        @SceneID AS SceneID,
        @AssetID AS AssetID,
        N'Accepted' AS Status,
        @SceneAssetCreated AS SceneAssetCreated;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAssetSuggestion_Reject
    @SceneAssetSuggestionID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SceneID int;
    DECLARE @AssetID int;

    SELECT @SceneID = suggestion.SceneID,
        @AssetID = suggestion.AssetID
    FROM dbo.SceneAssetSuggestions suggestion
    INNER JOIN dbo.Scenes s ON s.SceneID = suggestion.SceneID
    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.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = suggestion.AssetID AND sa.ProjectID = p.ProjectID
    WHERE suggestion.SceneAssetSuggestionID = @SceneAssetSuggestionID
        AND suggestion.Status = N'Pending'
        AND s.IsArchived = 0
        AND c.IsArchived = 0
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND sa.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1;

    IF @SceneID IS NULL OR @AssetID IS NULL
        RETURN;

    UPDATE dbo.SceneAssetSuggestions
    SET Status = N'Rejected',
        ReviewedByUserID = @UserID,
        ReviewedUtc = SYSUTCDATETIME()
    WHERE SceneAssetSuggestionID = @SceneAssetSuggestionID
        AND Status = N'Pending';

    SELECT @SceneAssetSuggestionID AS SceneAssetSuggestionID,
        @SceneID AS SceneID,
        @AssetID AS AssetID,
        N'Rejected' AS Status,
        CAST(0 AS bit) AS SceneAssetCreated;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAssetSuggestions_CountByChapter
    @ChapterID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.Chapters c
        INNER JOIN dbo.Books b ON b.BookID = c.BookID
        INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
        INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
        WHERE c.ChapterID = @ChapterID
            AND c.IsArchived = 0
            AND b.IsArchived = 0
            AND p.IsArchived = 0
            AND pua.UserID = @UserID
            AND pua.IsActive = 1
    )
        RETURN;

    SELECT s.SceneID,
        COUNT(suggestion.SceneAssetSuggestionID) AS PendingSuggestionCount
    FROM dbo.Scenes s
    INNER JOIN dbo.SceneAssetSuggestions suggestion ON suggestion.SceneID = s.SceneID
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = suggestion.AssetID
    WHERE s.ChapterID = @ChapterID
        AND s.IsArchived = 0
        AND sa.IsArchived = 0
        AND suggestion.Status = N'Pending'
    GROUP BY s.SceneID;
END;
GO