SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

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

    SELECT COUNT(DISTINCT b.BookID)
    FROM dbo.Books b
    INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    WHERE pua.UserID = @UserID
        AND pua.AccessRole = N'Owner'
        AND pua.IsActive = 1
        AND p.IsArchived = 0
        AND b.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_ChapterCountForBook
    @BookID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT COUNT(1)
    FROM dbo.Chapters
    WHERE BookID = @BookID
        AND IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_SceneCountForBook
    @BookID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT COUNT(1)
    FROM dbo.Scenes s
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    WHERE c.BookID = @BookID
        AND c.IsArchived = 0
        AND s.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_CharacterCountForProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT COUNT(1)
    FROM dbo.Characters
    WHERE ProjectID = @ProjectID
        AND IsArchived = 0;
END;
GO

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

    SELECT COUNT(DISTINCT collaborator.UserID)
    FROM dbo.ProjectUserAccess ownerAccess
    INNER JOIN dbo.Projects p ON p.ProjectID = ownerAccess.ProjectID
    INNER JOIN dbo.ProjectUserAccess collaborator ON collaborator.ProjectID = ownerAccess.ProjectID
    WHERE ownerAccess.UserID = @UserID
        AND ownerAccess.AccessRole = N'Owner'
        AND ownerAccess.IsActive = 1
        AND collaborator.AccessRole = N'Collaborator'
        AND collaborator.IsActive = 1
        AND p.IsArchived = 0;
END;
GO