SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.ProjectInvitation', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ProjectInvitation
    (
        ProjectInvitationID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProjectInvitation PRIMARY KEY,
        ProjectID int NOT NULL,
        EmailAddress nvarchar(256) NOT NULL,
        AccessRole nvarchar(50) NOT NULL,
        InvitationToken uniqueidentifier NOT NULL CONSTRAINT DF_ProjectInvitation_InvitationToken DEFAULT NEWID(),
        InvitedByUserID int NOT NULL,
        InvitedDateUTC datetime2 NOT NULL CONSTRAINT DF_ProjectInvitation_InvitedDateUTC DEFAULT SYSUTCDATETIME(),
        AcceptedDateUTC datetime2 NULL,
        AcceptedByUserID int NULL,
        IsAccepted bit NOT NULL CONSTRAINT DF_ProjectInvitation_IsAccepted DEFAULT 0,
        IsCancelled bit NOT NULL CONSTRAINT DF_ProjectInvitation_IsCancelled DEFAULT 0,
        CancelledDateUTC datetime2 NULL,
        CONSTRAINT FK_ProjectInvitation_Project FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
        CONSTRAINT FK_ProjectInvitation_InvitedByUser FOREIGN KEY (InvitedByUserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT FK_ProjectInvitation_AcceptedByUser FOREIGN KEY (AcceptedByUserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT CK_ProjectInvitation_AccessRole CHECK (AccessRole IN (N'Collaborator'))
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ProjectInvitation_Project_Status' AND object_id = OBJECT_ID(N'dbo.ProjectInvitation'))
    CREATE INDEX IX_ProjectInvitation_Project_Status ON dbo.ProjectInvitation(ProjectID, IsAccepted, IsCancelled, InvitedDateUTC DESC);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ProjectInvitation_Email_Status' AND object_id = OBJECT_ID(N'dbo.ProjectInvitation'))
    CREATE INDEX IX_ProjectInvitation_Email_Status ON dbo.ProjectInvitation(EmailAddress, IsAccepted, IsCancelled);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_ProjectInvitation_Project_Email_Pending' AND object_id = OBJECT_ID(N'dbo.ProjectInvitation'))
    CREATE UNIQUE INDEX UX_ProjectInvitation_Project_Email_Pending ON dbo.ProjectInvitation(ProjectID, EmailAddress) WHERE IsAccepted = 0 AND IsCancelled = 0;
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,
        pua.AccessRole
    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,
        pua.AccessRole
    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.ProjectCollaboration_GetOwner
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (1) pua.UserID
    FROM dbo.ProjectUserAccess pua
    WHERE pua.ProjectID = @ProjectID
        AND pua.AccessRole = N'Owner'
        AND pua.IsActive = 1
    ORDER BY pua.ProjectUserAccessID;
END;
GO

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

    SELECT pua.ProjectUserAccessID, pua.ProjectID, pua.UserID, u.Email, u.DisplayName,
        pua.AccessRole, pua.InvitedByUserID, invitedBy.DisplayName AS InvitedByDisplayName,
        pua.InvitedDateUTC, pua.AcceptedDateUTC, pua.IsActive
    FROM dbo.ProjectUserAccess pua
    INNER JOIN dbo.AppUser u ON u.UserID = pua.UserID
    LEFT JOIN dbo.AppUser invitedBy ON invitedBy.UserID = pua.InvitedByUserID
    WHERE pua.ProjectID = @ProjectID
        AND pua.AccessRole = N'Collaborator'
        AND pua.IsActive = 1
    ORDER BY u.DisplayName, u.Email;
END;
GO

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

    SELECT pi.ProjectInvitationID, pi.ProjectID, pi.EmailAddress, pi.AccessRole, pi.InvitationToken,
        pi.InvitedByUserID, invitedBy.DisplayName AS InvitedByDisplayName, pi.InvitedDateUTC,
        pi.AcceptedDateUTC, pi.AcceptedByUserID, pi.IsAccepted, pi.IsCancelled, pi.CancelledDateUTC
    FROM dbo.ProjectInvitation pi
    INNER JOIN dbo.AppUser invitedBy ON invitedBy.UserID = pi.InvitedByUserID
    WHERE pi.ProjectID = @ProjectID
        AND pi.IsAccepted = 0
        AND pi.IsCancelled = 0
    ORDER BY pi.InvitedDateUTC DESC;
END;
GO

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

    IF EXISTS
    (
        SELECT 1
        FROM dbo.ProjectUserAccess
        WHERE ProjectID = @ProjectID
            AND UserID = @UserID
            AND AccessRole = N'Collaborator'
    )
    BEGIN
        UPDATE dbo.ProjectUserAccess
        SET IsActive = 1,
            InvitedByUserID = @InvitedByUserID,
            InvitedDateUTC = COALESCE(InvitedDateUTC, SYSUTCDATETIME()),
            AcceptedDateUTC = COALESCE(AcceptedDateUTC, SYSUTCDATETIME())
        WHERE ProjectID = @ProjectID
            AND UserID = @UserID
            AND AccessRole = N'Collaborator';
    END
    ELSE
    BEGIN
        INSERT INTO dbo.ProjectUserAccess (ProjectID, UserID, AccessRole, InvitedByUserID, InvitedDateUTC, AcceptedDateUTC, IsActive)
        VALUES (@ProjectID, @UserID, N'Collaborator', @InvitedByUserID, SYSUTCDATETIME(), SYSUTCDATETIME(), 1);
    END;

    UPDATE pi
    SET IsAccepted = 1,
        AcceptedDateUTC = COALESCE(AcceptedDateUTC, SYSUTCDATETIME()),
        AcceptedByUserID = @UserID
    FROM dbo.ProjectInvitation pi
    INNER JOIN dbo.AppUser u ON u.Email = pi.EmailAddress
    WHERE pi.ProjectID = @ProjectID
        AND u.UserID = @UserID
        AND pi.IsAccepted = 0
        AND pi.IsCancelled = 0;

    SELECT CAST(1 AS bit);
END;
GO

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

    UPDATE dbo.ProjectUserAccess
    SET IsActive = 0
    WHERE ProjectID = @ProjectID
        AND UserID = @UserID
        AND AccessRole = N'Collaborator'
        AND IsActive = 1;

    SELECT CAST(CASE WHEN @@ROWCOUNT = 1 THEN 1 ELSE 0 END AS bit);
END;
GO

CREATE OR ALTER PROCEDURE dbo.ProjectCollaboration_CreatePendingInvitation
    @ProjectID int,
    @EmailAddress nvarchar(256),
    @InvitedByUserID int
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS
    (
        SELECT 1
        FROM dbo.ProjectInvitation
        WHERE ProjectID = @ProjectID
            AND EmailAddress = @EmailAddress
            AND IsAccepted = 0
            AND IsCancelled = 0
    )
    BEGIN
        SELECT TOP (1) ProjectInvitationID
        FROM dbo.ProjectInvitation
        WHERE ProjectID = @ProjectID
            AND EmailAddress = @EmailAddress
            AND IsAccepted = 0
            AND IsCancelled = 0
        ORDER BY ProjectInvitationID DESC;
        RETURN;
    END;

    INSERT INTO dbo.ProjectInvitation (ProjectID, EmailAddress, AccessRole, InvitedByUserID)
    VALUES (@ProjectID, @EmailAddress, N'Collaborator', @InvitedByUserID);

    SELECT CAST(SCOPE_IDENTITY() AS int);
END;
GO

CREATE OR ALTER PROCEDURE dbo.ProjectCollaboration_CollaboratorCountsForOwner
    @OwnerUserID int,
    @CollaboratorUserID int
AS
BEGIN
    SET NOCOUNT ON;

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