PlotDirector/PlotLine/Sql/107_Phase16O_WordCompanionLocationSuggestionReview.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

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

    SELECT suggestion.SceneLocationSuggestionID,
        suggestion.SceneID,
        suggestion.LocationID,
        location.LocationName,
        location.LocationPath,
        suggestion.Status,
        suggestion.Confidence,
        suggestion.Reason,
        suggestion.DetectedUtc
    FROM dbo.SceneLocationSuggestions 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.LocationPaths location ON location.LocationID = suggestion.LocationID
    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 location.ProjectID = p.ProjectID
        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,
        location.LocationPath;
END;
GO

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

    DECLARE @SceneID int;
    DECLARE @LocationID int;
    DECLARE @SceneLocationCreated bit = 0;
    DECLARE @PrimaryLocationUpdated bit = 0;

    SELECT @SceneID = suggestion.SceneID,
        @LocationID = suggestion.LocationID
    FROM dbo.SceneLocationSuggestions 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.Locations location ON location.LocationID = suggestion.LocationID AND location.ProjectID = p.ProjectID
    WHERE suggestion.SceneLocationSuggestionID = @SceneLocationSuggestionID
        AND suggestion.Status = N'Pending'
        AND s.IsArchived = 0
        AND c.IsArchived = 0
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND location.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1;

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

    IF NOT EXISTS (SELECT 1 FROM dbo.WordCompanionSceneLocations WHERE SceneID = @SceneID AND LocationID = @LocationID)
    BEGIN
        INSERT dbo.WordCompanionSceneLocations (SceneID, LocationID)
        VALUES (@SceneID, @LocationID);

        SET @SceneLocationCreated = 1;
    END;

    UPDATE dbo.Scenes
    SET PrimaryLocationID = @LocationID,
        UpdatedDate = SYSUTCDATETIME()
    WHERE SceneID = @SceneID
        AND PrimaryLocationID IS NULL;

    IF @@ROWCOUNT > 0
        SET @PrimaryLocationUpdated = 1;

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

    SELECT @SceneLocationSuggestionID AS SceneLocationSuggestionID,
        @SceneID AS SceneID,
        @LocationID AS LocationID,
        N'Accepted' AS Status,
        @SceneLocationCreated AS SceneLocationCreated,
        @PrimaryLocationUpdated AS PrimaryLocationUpdated;
END;
GO

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

    DECLARE @SceneID int;
    DECLARE @LocationID int;

    SELECT @SceneID = suggestion.SceneID,
        @LocationID = suggestion.LocationID
    FROM dbo.SceneLocationSuggestions 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.Locations location ON location.LocationID = suggestion.LocationID AND location.ProjectID = p.ProjectID
    WHERE suggestion.SceneLocationSuggestionID = @SceneLocationSuggestionID
        AND suggestion.Status = N'Pending'
        AND s.IsArchived = 0
        AND c.IsArchived = 0
        AND b.IsArchived = 0
        AND p.IsArchived = 0
        AND location.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1;

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

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

    SELECT @SceneLocationSuggestionID AS SceneLocationSuggestionID,
        @SceneID AS SceneID,
        @LocationID AS LocationID,
        N'Rejected' AS Status,
        CAST(0 AS bit) AS SceneLocationCreated,
        CAST(0 AS bit) AS PrimaryLocationUpdated;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneLocationSuggestions_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.SceneLocationSuggestionID) AS PendingSuggestionCount
    FROM dbo.Scenes s
    INNER JOIN dbo.SceneLocationSuggestions suggestion ON suggestion.SceneID = s.SceneID
    INNER JOIN dbo.Locations location ON location.LocationID = suggestion.LocationID
    WHERE s.ChapterID = @ChapterID
        AND s.IsArchived = 0
        AND location.IsArchived = 0
        AND suggestion.Status = N'Pending'
    GROUP BY s.SceneID;
END;
GO