namespace CollabDAL.Query.CollabInvite;

public static class CollabInviteQB
{
    // Index: UX_CollabInvite_Token
    public const string GET_INVITE_BY_TOKEN = @"
        SELECT i.InviteId, i.SessionId, s.Title AS SessionTitle,
               i.InviteToken, i.InvitedUserId, i.OfferedRole,
               i.Status, i.ExpiresAt, i.CreatedBy, i.CreatedDate,
               i.DatabaseName
        FROM   collab.CollabSessionInvite i
        JOIN   collab.CollabSession       s ON s.SessionId = i.SessionId
        WHERE  i.InviteToken = @InviteToken";

    // Index: IX_CollabInvite_Session_User
    public const string GET_PENDING_INVITES_FOR_USER = @"
        SELECT i.InviteId, i.SessionId, s.Title AS SessionTitle,
               i.InviteToken, i.InvitedUserId, i.OfferedRole,
               i.Status, i.ExpiresAt, i.CreatedBy, i.CreatedDate,
               i.DatabaseName
        FROM   collab.CollabSessionInvite i
        JOIN   collab.CollabSession       s ON s.SessionId = i.SessionId
        WHERE  i.InvitedUserId = @UserId
        AND    i.Status        = 'Pending'
        AND    i.ExpiresAt     > GETUTCDATE()
        AND    i.DatabaseName  = @DatabaseName
        ORDER BY i.CreatedDate DESC";

    // OUTPUT INSERTED.InviteId returns the new GUID primary key
    public const string CREATE_INVITE = @"
        INSERT INTO collab.CollabSessionInvite
               (SessionId, InviteToken, InvitedUserId, OfferedRole,
                Status, ExpiresAt, CreatedBy, CreatedDate, DatabaseName)
        OUTPUT INSERTED.InviteId
        VALUES (@SessionId, @InviteToken, @InvitedUserId, @OfferedRole,
                'Pending', @ExpiresAt, @CreatedBy, GETUTCDATE(), @DatabaseName)";

    public const string UPDATE_INVITE_STATUS = @"
        UPDATE collab.CollabSessionInvite
        SET    Status       = @Status
        WHERE  InviteId     = @InviteId
        AND    DatabaseName = @DatabaseName";

    // Index: IX_CollabInvite_ExpiresAt — run by cleanup service
    public const string EXPIRE_STALE_INVITES = @"
        UPDATE collab.CollabSessionInvite
        SET    Status     = 'Expired'
        WHERE  Status     = 'Pending'
        AND    ExpiresAt  < GETUTCDATE()";
}