PlotDirector/PlotLine/Sql/042_Phase5A_ProjectUserAccess.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.ProjectUserAccess', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ProjectUserAccess
    (
        ProjectUserAccessID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProjectUserAccess PRIMARY KEY,
        ProjectID int NOT NULL,
        UserID int NOT NULL,
        AccessRole nvarchar(50) NOT NULL,
        InvitedByUserID int NULL,
        InvitedDateUTC datetime2 NULL,
        AcceptedDateUTC datetime2 NULL,
        IsActive bit NOT NULL CONSTRAINT DF_ProjectUserAccess_IsActive DEFAULT 1,
        CONSTRAINT FK_ProjectUserAccess_Project FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
        CONSTRAINT FK_ProjectUserAccess_User FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT FK_ProjectUserAccess_InvitedByUser FOREIGN KEY (InvitedByUserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT CK_ProjectUserAccess_AccessRole CHECK (AccessRole IN (N'Owner', N'Collaborator'))
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_ProjectUserAccess_Project_User_Active' AND object_id = OBJECT_ID(N'dbo.ProjectUserAccess'))
    CREATE UNIQUE INDEX UX_ProjectUserAccess_Project_User_Active ON dbo.ProjectUserAccess(ProjectID, UserID) WHERE IsActive = 1;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ProjectUserAccess_User_Project' AND object_id = OBJECT_ID(N'dbo.ProjectUserAccess'))
    CREATE INDEX IX_ProjectUserAccess_User_Project ON dbo.ProjectUserAccess(UserID, ProjectID, IsActive, AccessRole);
GO

DECLARE @LegacyOwnerUserID int;
SELECT TOP (1) @LegacyOwnerUserID = UserID
FROM dbo.AppUser
ORDER BY UserID;

IF @LegacyOwnerUserID IS NOT NULL
BEGIN
    INSERT INTO dbo.ProjectUserAccess (ProjectID, UserID, AccessRole, AcceptedDateUTC, IsActive)
    SELECT p.ProjectID, @LegacyOwnerUserID, N'Owner', SYSUTCDATETIME(), 1
    FROM dbo.Projects p
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM dbo.ProjectUserAccess pua
        WHERE pua.ProjectID = p.ProjectID
            AND pua.UserID = @LegacyOwnerUserID
            AND pua.IsActive = 1
    );
END;
GO

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

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.ProjectUserAccess
        WHERE ProjectID = @ProjectID
            AND UserID = @UserID
            AND IsActive = 1
    )
    BEGIN
        INSERT INTO dbo.ProjectUserAccess (ProjectID, UserID, AccessRole, AcceptedDateUTC, IsActive)
        VALUES (@ProjectID, @UserID, N'Owner', SYSUTCDATETIME(), 1);
    END;
END;
GO

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

    SELECT CAST(CASE WHEN EXISTS
    (
        SELECT 1
        FROM dbo.ProjectUserAccess pua
        INNER JOIN dbo.Projects p ON p.ProjectID = pua.ProjectID
        WHERE pua.ProjectID = @ProjectID
            AND pua.UserID = @UserID
            AND pua.IsActive = 1
            AND p.IsArchived = 0
    )
    THEN 1 ELSE 0 END AS bit);
END;
GO

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

    SELECT CAST(CASE WHEN EXISTS
    (
        SELECT 1
        FROM dbo.ProjectUserAccess pua
        INNER JOIN dbo.Projects p ON p.ProjectID = pua.ProjectID
        WHERE pua.ProjectID = @ProjectID
            AND pua.UserID = @UserID
            AND pua.AccessRole = N'Owner'
            AND pua.IsActive = 1
            AND p.IsArchived = 0
    )
    THEN 1 ELSE 0 END AS bit);
END;
GO

CREATE OR ALTER PROCEDURE dbo.ProjectAccess_GetProjectIDForEntity
    @EntityType nvarchar(100),
    @EntityID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int = NULL;

    IF @EntityType = N'Project'
        SELECT @ProjectID = ProjectID FROM dbo.Projects WHERE ProjectID = @EntityID;
    ELSE IF @EntityType = N'Book'
        SELECT @ProjectID = ProjectID FROM dbo.Books WHERE BookID = @EntityID;
    ELSE IF @EntityType = N'Chapter'
        SELECT @ProjectID = b.ProjectID FROM dbo.Chapters c INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE c.ChapterID = @EntityID;
    ELSE IF @EntityType = N'Scene'
        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 = @EntityID;
    ELSE IF @EntityType = N'Character'
        SELECT @ProjectID = ProjectID FROM dbo.Characters WHERE CharacterID = @EntityID;
    ELSE IF @EntityType = N'SceneCharacter'
        SELECT @ProjectID = ch.ProjectID FROM dbo.SceneCharacters sc INNER JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID WHERE sc.SceneCharacterID = @EntityID;
    ELSE IF @EntityType = N'CharacterAttributeEvent'
        SELECT @ProjectID = ch.ProjectID FROM dbo.CharacterAttributeEvents cae INNER JOIN dbo.Characters ch ON ch.CharacterID = cae.CharacterID WHERE cae.CharacterAttributeEventID = @EntityID;
    ELSE IF @EntityType = N'CharacterKnowledge'
        SELECT @ProjectID = ch.ProjectID FROM dbo.CharacterKnowledge ck INNER JOIN dbo.Characters ch ON ch.CharacterID = ck.CharacterID WHERE ck.CharacterKnowledgeID = @EntityID;
    ELSE IF @EntityType = N'CharacterRelationship'
        SELECT @ProjectID = ProjectID FROM dbo.CharacterRelationships WHERE CharacterRelationshipID = @EntityID;
    ELSE IF @EntityType = N'RelationshipEvent'
        SELECT @ProjectID = cr.ProjectID FROM dbo.RelationshipEvents re INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID WHERE re.RelationshipEventID = @EntityID;
    ELSE IF @EntityType = N'StoryAsset'
        SELECT @ProjectID = ProjectID FROM dbo.StoryAssets WHERE StoryAssetID = @EntityID;
    ELSE IF @EntityType = N'AssetEvent'
        SELECT @ProjectID = sa.ProjectID FROM dbo.AssetEvents ae INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID WHERE ae.AssetEventID = @EntityID;
    ELSE IF @EntityType = N'AssetDependency'
        SELECT @ProjectID = sa.ProjectID FROM dbo.AssetDependencies ad INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ad.SourceAssetID WHERE ad.AssetDependencyID = @EntityID;
    ELSE IF @EntityType = N'AssetCustodyEvent'
        SELECT @ProjectID = sa.ProjectID FROM dbo.AssetCustodyEvents ace INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID WHERE ace.AssetCustodyEventID = @EntityID;
    ELSE IF @EntityType = N'Location'
        SELECT @ProjectID = ProjectID FROM dbo.Locations WHERE LocationID = @EntityID;
    ELSE IF @EntityType = N'LocationRelationship'
        SELECT @ProjectID = l.ProjectID FROM dbo.LocationRelationships lr INNER JOIN dbo.Locations l ON l.LocationID = lr.FromLocationID WHERE lr.LocationRelationshipID = @EntityID;
    ELSE IF @EntityType = N'SceneAssetLocation'
        SELECT @ProjectID = l.ProjectID FROM dbo.SceneAssetLocations sal INNER JOIN dbo.Locations l ON l.LocationID = sal.LocationID WHERE sal.SceneAssetLocationID = @EntityID;
    ELSE IF @EntityType = N'PlotLine'
        SELECT @ProjectID = ProjectID FROM dbo.PlotLines WHERE PlotLineID = @EntityID;
    ELSE IF @EntityType = N'PlotThread'
        SELECT @ProjectID = pl.ProjectID FROM dbo.PlotThreads pt INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID WHERE pt.PlotThreadID = @EntityID;
    ELSE IF @EntityType = N'ThreadEvent'
        SELECT @ProjectID = pl.ProjectID FROM dbo.ThreadEvents te INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID WHERE te.ThreadEventID = @EntityID;
    ELSE IF @EntityType = N'SceneDependency'
        SELECT @ProjectID = ProjectID FROM dbo.SceneDependencies WHERE SceneDependencyID = @EntityID;
    ELSE IF @EntityType = N'SceneMetricType'
        SELECT @ProjectID = ProjectID FROM dbo.SceneMetricTypes WHERE MetricTypeID = @EntityID;
    ELSE IF @EntityType = N'TimelineViewPreset'
        SELECT @ProjectID = ProjectID FROM dbo.TimelineViewPresets WHERE TimelineViewPresetID = @EntityID;
    ELSE IF @EntityType = N'ContinuityWarning'
        SELECT @ProjectID = ProjectID FROM dbo.ContinuityWarnings WHERE ContinuityWarningID = @EntityID;
    ELSE IF @EntityType = N'Scenario'
        SELECT @ProjectID = ProjectID FROM dbo.Scenarios WHERE ScenarioID = @EntityID;
    ELSE IF @EntityType = N'ScenarioWarning'
        SELECT @ProjectID = sc.ProjectID FROM dbo.ScenarioWarnings sw INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = sw.ScenarioID WHERE sw.ScenarioWarningID = @EntityID;
    ELSE IF @EntityType = N'ProjectRestorePoint'
        SELECT @ProjectID = ProjectID FROM dbo.ProjectRestorePoints WHERE ProjectRestorePointID = @EntityID;
    ELSE IF @EntityType = N'ChapterGoal'
        SELECT @ProjectID = b.ProjectID FROM dbo.ChapterGoals cg INNER JOIN dbo.Chapters c ON c.ChapterID = cg.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE cg.ChapterGoalID = @EntityID;
    ELSE IF @EntityType = N'SceneWorkflow'
        SELECT @ProjectID = b.ProjectID FROM dbo.SceneWorkflow sw INNER JOIN dbo.Scenes s ON s.SceneID = sw.SceneID INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE sw.SceneWorkflowID = @EntityID;
    ELSE IF @EntityType = N'SceneNote'
        SELECT @ProjectID = b.ProjectID FROM dbo.SceneNotes sn INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE sn.SceneNoteID = @EntityID;
    ELSE IF @EntityType = N'SceneChecklistItem'
        SELECT @ProjectID = b.ProjectID FROM dbo.SceneChecklistItems sci INNER JOIN dbo.Scenes s ON s.SceneID = sci.SceneID INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE sci.SceneChecklistItemID = @EntityID;
    ELSE IF @EntityType = N'SceneAttachment'
        SELECT @ProjectID = b.ProjectID FROM dbo.SceneAttachments sa INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID INNER JOIN dbo.Books b ON b.BookID = c.BookID WHERE sa.SceneAttachmentID = @EntityID;

    SELECT @ProjectID AS ProjectID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ProjectAccess_UserCanAccessEntity
    @EntityType nvarchar(100),
    @EntityID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int;
    DECLARE @Project TABLE (ProjectID int NULL);

    INSERT INTO @Project
    EXEC dbo.ProjectAccess_GetProjectIDForEntity @EntityType = @EntityType, @EntityID = @EntityID;

    SELECT @ProjectID = ProjectID FROM @Project;

    IF @ProjectID IS NULL
    BEGIN
        SELECT CAST(0 AS bit);
        RETURN;
    END;

    EXEC dbo.ProjectAccess_UserCanAccess @ProjectID = @ProjectID, @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Project_ListForUser
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT p.ProjectID, p.ProjectName, p.Description, p.CreatedDate, p.UpdatedDate, p.IsArchived
    FROM dbo.Projects p
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    WHERE p.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1
    ORDER BY p.ProjectName;
END;
GO

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

    SELECT p.ProjectID, p.ProjectName, p.Description, p.CreatedDate, p.UpdatedDate, p.IsArchived
    FROM dbo.Projects p
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    WHERE p.ProjectID = @ProjectID
        AND p.IsArchived = 0
        AND pua.UserID = @UserID
        AND pua.IsActive = 1;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Project_Save
    @ProjectID int = NULL,
    @ProjectName nvarchar(200),
    @Description nvarchar(max) = NULL,
    @GenreMetricPresetKey nvarchar(50) = NULL,
    @UserID int = 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);

        IF @UserID IS NOT NULL
            EXEC dbo.ProjectAccess_EnsureOwner @ProjectID = @ProjectID, @UserID = @UserID;

        EXEC dbo.ProjectMetricPreset_Apply @ProjectID = @ProjectID, @GenreMetricPresetKey = @GenreMetricPresetKey;

        SELECT @ProjectID AS ProjectID;
        RETURN;
    END;

    UPDATE p
    SET ProjectName = @ProjectName,
        Description = @Description,
        UpdatedDate = SYSUTCDATETIME()
    FROM dbo.Projects p
    WHERE p.ProjectID = @ProjectID
        AND
        (
            @UserID IS NULL
            OR EXISTS
            (
                SELECT 1
                FROM dbo.ProjectUserAccess pua
                WHERE pua.ProjectID = p.ProjectID
                    AND pua.UserID = @UserID
                    AND pua.IsActive = 1
            )
        );

    SELECT CASE WHEN @@ROWCOUNT = 1 THEN @ProjectID ELSE 0 END AS ProjectID;
END;
GO

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

    UPDATE p
    SET IsArchived = 1,
        UpdatedDate = SYSUTCDATETIME()
    FROM dbo.Projects p
    WHERE p.ProjectID = @ProjectID
        AND EXISTS
        (
            SELECT 1
            FROM dbo.ProjectUserAccess pua
            WHERE pua.ProjectID = p.ProjectID
                AND pua.UserID = @UserID
                AND pua.AccessRole = N'Owner'
                AND pua.IsActive = 1
        );
END;
GO

CREATE OR ALTER PROCEDURE dbo.Archive_ListForUser
    @UserID int,
    @ProjectID int = NULL,
    @EntityType nvarchar(80) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Archived TABLE
    (
        EntityType nvarchar(80),
        EntityID int,
        DisplayName nvarchar(500),
        ProjectID int,
        ProjectName nvarchar(200),
        BookID int NULL,
        BookTitle nvarchar(200) NULL,
        ArchivedDate datetime2 NULL,
        ArchivedReason nvarchar(500) NULL,
        UpdatedDate datetime2
    );

    INSERT INTO @Archived
    EXEC dbo.Archive_List @ProjectID = @ProjectID, @EntityType = @EntityType;

    SELECT a.EntityType, a.EntityID, a.DisplayName, a.ProjectID, a.ProjectName, a.BookID, a.BookTitle,
        a.ArchivedDate, a.ArchivedReason, a.UpdatedDate
    FROM @Archived a
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = a.ProjectID
    WHERE pua.UserID = @UserID
        AND pua.IsActive = 1
    ORDER BY ISNULL(a.ArchivedDate, a.UpdatedDate) DESC, a.EntityType, a.DisplayName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Archive_ProjectFilterListForUser
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT p.ProjectID, p.ProjectName, p.Description, p.CreatedDate, p.UpdatedDate, p.IsArchived
    FROM dbo.Projects p
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    WHERE pua.UserID = @UserID
        AND pua.IsActive = 1
    ORDER BY p.IsArchived, p.ProjectName;
END;
GO