plexpy/plexpy/users.py

# -*- coding: utf-8 -*-

# This file is part of Tautulli.
#
#  Tautulli is free software: you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation, either version 3 of the License, or
#  (at your option) any later version.
#
#  Tautulli is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with Tautulli.  If not, see <http://www.gnu.org/licenses/>.

from urllib.parse import parse_qsl

import arrow
import httpagentparser

import plexpy
from plexpy import common
from plexpy import database
from plexpy import datatables
from plexpy import helpers
from plexpy import libraries
from plexpy import logger
from plexpy import plextv
from plexpy import session


def refresh_users():
    logger.info("Tautulli Users :: Requesting users list refresh...")
    result = plextv.PlexTV().get_full_users_list()

    server_id = plexpy.CONFIG.PMS_IDENTIFIER
    if not server_id:
        logger.error("Tautulli Users :: No PMS identifier, cannot refresh users. Verify server in settings.")
        return

    if result:
        monitor_db = database.MonitorDatabase()

        # Keep track of user_id to update is_active status
        user_ids = [0]  # Local user always considered active
        new_users = []

        for item in result:
            if item.get('shared_libraries'):
                item['shared_libraries'] = ';'.join(item['shared_libraries'])
                # Only append user if libraries are shared
                user_ids.append(helpers.cast_to_int(item['user_id']))
            elif item.get('server_token'):
                libs = libraries.Libraries().get_sections()
                item['shared_libraries'] = ';'.join([str(l['section_id']) for l in libs])
                # Only append user if libraries are shared
                user_ids.append(helpers.cast_to_int(item['user_id']))

            keys_dict = {"user_id": item.pop('user_id')}

            # Check if we've set a custom avatar if so don't overwrite it.
            if keys_dict['user_id']:
                avatar_urls = monitor_db.select("SELECT thumb, custom_avatar_url "
                                                "FROM users WHERE user_id = ?",
                                                [keys_dict['user_id']])
                if avatar_urls:
                    if not avatar_urls[0]['custom_avatar_url'] or \
                            avatar_urls[0]['custom_avatar_url'] == avatar_urls[0]['thumb']:
                        item['custom_avatar_url'] = item['thumb']
                else:
                    item['custom_avatar_url'] = item['thumb']

            # Check if title is the same as the username
            if item['title'] == item['username']:
                item['title'] = None

            # Check if username is blank (Managed Users)
            if not item['username']:
                item['username'] = item['title']

            result = monitor_db.upsert('users', key_dict=keys_dict, value_dict=item)

            if result == 'insert':
                new_users.append(item['username'])

        query = "UPDATE users SET is_active = 0 WHERE user_id NOT IN ({})".format(", ".join(["?"] * len(user_ids)))
        monitor_db.action(query=query, args=user_ids)

        # Add new users to logger username filter
        logger.filter_usernames(new_users)

        logger.info("Tautulli Users :: Users list refreshed.")
        return True
    else:
        logger.warn("Tautulli Users :: Unable to refresh users list.")
        return False


class Users(object):

    def __init__(self):
        pass

    def get_datatables_list(self, kwargs=None, grouping=None):
        default_return = {'recordsFiltered': 0,
                          'recordsTotal': 0,
                          'draw': 0,
                          'data': []}

        data_tables = datatables.DataTables()

        custom_where = [['users.deleted_user', 0]]

        if grouping is None:
            grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES

        if session.get_session_user_id():
            custom_where.append(['users.user_id', session.get_session_user_id()])

        if kwargs.get('user_id'):
            custom_where.append(['users.user_id', kwargs.get('user_id')])

        group_by = 'session_history.reference_id' if grouping else 'session_history.id'

        columns = ["users.id AS row_id",
                   "users.user_id",
                   "users.username",
                   "(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = '' \
                    THEN users.username ELSE users.friendly_name END) AS friendly_name",
                   "users.title",
                   "users.email",
                   "users.thumb AS user_thumb",
                   "users.custom_avatar_url AS custom_thumb",
                   "COUNT(DISTINCT %s) AS plays" % group_by,
                   "SUM(CASE WHEN session_history.stopped > 0 THEN (session_history.stopped - session_history.started) \
                    ELSE 0 END) - SUM(CASE WHEN session_history.paused_counter IS NULL THEN 0 ELSE \
                    session_history.paused_counter END) AS duration",
                   "MAX(session_history.started) AS last_seen",
                   "MAX(session_history.id) AS history_row_id",
                   "session_history_metadata.full_title AS last_played",
                   "session_history.ip_address",
                   "session_history.platform",
                   "session_history.player",
                   "session_history.rating_key",
                   "session_history_metadata.media_type",
                   "session_history_metadata.thumb",
                   "session_history_metadata.parent_thumb",
                   "session_history_metadata.grandparent_thumb",
                   "session_history_metadata.parent_title",
                   "session_history_metadata.year",
                   "session_history_metadata.media_index",
                   "session_history_metadata.parent_media_index",
                   "session_history_metadata.live",
                   "session_history_metadata.added_at",
                   "session_history_metadata.originally_available_at",
                   "session_history_metadata.guid",
                   "session_history_media_info.transcode_decision",
                   "users.do_notify AS do_notify",
                   "users.keep_history AS keep_history",
                   "users.allow_guest AS allow_guest",
                   "users.is_active AS is_active"
                   ]
        try:
            query = data_tables.ssp_query(table_name='users',
                                          columns=columns,
                                          custom_where=custom_where,
                                          group_by=['users.user_id'],
                                          join_types=['LEFT OUTER JOIN',
                                                      'LEFT OUTER JOIN',
                                                      'LEFT OUTER JOIN'],
                                          join_tables=['session_history',
                                                       'session_history_metadata',
                                                       'session_history_media_info'],
                                          join_evals=[['session_history.user_id', 'users.user_id'],
                                                      ['session_history.id', 'session_history_metadata.id'],
                                                      ['session_history.id', 'session_history_media_info.id']],
                                          kwargs=kwargs)
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_list: %s." % e)
            return default_return

        users = query['result']

        rows = []
        for item in users:
            if item['media_type'] == 'episode' and item['parent_thumb']:
                thumb = item['parent_thumb']
            elif item['media_type'] == 'episode':
                thumb = item['grandparent_thumb']
            else:
                thumb = item['thumb']

            if item['custom_thumb'] and item['custom_thumb'] != item['user_thumb']:
                user_thumb = item['custom_thumb']
            elif item['user_thumb']:
                user_thumb = item['user_thumb']
            else:
                user_thumb = common.DEFAULT_USER_THUMB

            # Rename Mystery platform names
            platform = common.PLATFORM_NAME_OVERRIDES.get(item['platform'], item['platform'])

            row = {'row_id': item['row_id'],
                   'user_id': item['user_id'],
                   'username': item['username'],
                   'friendly_name': item['friendly_name'],
                   'title': item['title'],
                   'email': item['email'],
                   'user_thumb': user_thumb,
                   'plays': item['plays'],
                   'duration': item['duration'],
                   'last_seen': item['last_seen'],
                   'last_played': item['last_played'],
                   'history_row_id': item['history_row_id'],
                   'ip_address': item['ip_address'],
                   'platform': platform,
                   'player': item['player'],
                   'rating_key': item['rating_key'],
                   'media_type': item['media_type'],
                   'thumb': thumb,
                   'parent_title': item['parent_title'],
                   'year': item['year'],
                   'media_index': item['media_index'],
                   'parent_media_index': item['parent_media_index'],
                   'live': item['live'],
                   'originally_available_at': item['originally_available_at'],
                   'guid': item['guid'],
                   'transcode_decision': item['transcode_decision'],
                   'do_notify': item['do_notify'],
                   'keep_history': item['keep_history'],
                   'allow_guest': item['allow_guest'],
                   'is_active': item['is_active']
                   }

            rows.append(row)

        dict = {'recordsFiltered': query['filteredCount'],
                'recordsTotal': query['totalCount'],
                'data': session.friendly_name_to_username(rows),
                'draw': query['draw']
                }

        return dict

    def get_datatables_unique_ips(self, user_id=None, kwargs=None):
        default_return = {'recordsFiltered': 0,
                          'recordsTotal': 0,
                          'draw': 0,
                          'data': []}

        if not session.allow_session_user(user_id):
            return default_return

        data_tables = datatables.DataTables()

        custom_where = ['users.user_id', user_id]

        columns = ["session_history.id AS history_row_id",
                   "MIN(session_history.started) AS first_seen",
                   "MAX(session_history.started) AS last_seen",
                   "session_history.ip_address",
                   "COUNT(session_history.id) AS play_count",
                   "session_history.platform",
                   "session_history.player",
                   "session_history.rating_key",
                   "session_history_metadata.full_title AS last_played",
                   "session_history_metadata.thumb",
                   "session_history_metadata.parent_thumb",
                   "session_history_metadata.grandparent_thumb",
                   "session_history_metadata.media_type",
                   "session_history_metadata.parent_title",
                   "session_history_metadata.year",
                   "session_history_metadata.media_index",
                   "session_history_metadata.parent_media_index",
                   "session_history_metadata.live",
                   "session_history_metadata.added_at",
                   "session_history_metadata.originally_available_at",
                   "session_history_metadata.guid",
                   "session_history_media_info.transcode_decision",
                   "session_history.user",
                   "session_history.user_id as custom_user_id",
                   "(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = '' \
                    THEN users.username ELSE users.friendly_name END) AS friendly_name"
                   ]

        try:
            query = data_tables.ssp_query(table_name='session_history',
                                          columns=columns,
                                          custom_where=[custom_where],
                                          group_by=['ip_address'],
                                          join_types=['JOIN',
                                                      'JOIN',
                                                      'JOIN'],
                                          join_tables=['users',
                                                       'session_history_metadata',
                                                       'session_history_media_info'],
                                          join_evals=[['session_history.user_id', 'users.user_id'],
                                                      ['session_history.id', 'session_history_metadata.id'],
                                                      ['session_history.id', 'session_history_media_info.id']],
                                          kwargs=kwargs)
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_unique_ips: %s." % e)
            return default_return

        results = query['result']

        rows = []
        for item in results:
            if item["media_type"] == 'episode' and item["parent_thumb"]:
                thumb = item["parent_thumb"]
            elif item["media_type"] == 'episode':
                thumb = item["grandparent_thumb"]
            else:
                thumb = item["thumb"]

            # Rename Mystery platform names
            platform = common.PLATFORM_NAME_OVERRIDES.get(item["platform"], item["platform"])

            row = {'history_row_id': item['history_row_id'],
                   'last_seen': item['last_seen'],
                   'first_seen': item['first_seen'],
                   'ip_address': item['ip_address'],
                   'play_count': item['play_count'],
                   'platform': platform,
                   'player': item['player'],
                   'last_played': item['last_played'],
                   'rating_key': item['rating_key'],
                   'thumb': thumb,
                   'media_type': item['media_type'],
                   'parent_title': item['parent_title'],
                   'year': item['year'],
                   'media_index': item['media_index'],
                   'parent_media_index': item['parent_media_index'],
                   'live': item['live'],
                   'originally_available_at': item['originally_available_at'],
                   'guid': item['guid'],
                   'transcode_decision': item['transcode_decision'],
                   'friendly_name': item['friendly_name'],
                   'user_id': item['custom_user_id']
                   }

            rows.append(row)

        dict = {'recordsFiltered': query['filteredCount'],
                'recordsTotal': query['totalCount'],
                'data': session.friendly_name_to_username(rows),
                'draw': query['draw']
                }

        return dict

    def set_config(self, user_id=None, friendly_name='', custom_thumb='', do_notify=1, keep_history=1, allow_guest=1):
        if str(user_id).isdigit():
            monitor_db = database.MonitorDatabase()

            user = monitor_db.select_single('SELECT username FROM users WHERE user_id = ?', [user_id])
            if user.get('username') == friendly_name:
                friendly_name = None

            key_dict = {'user_id': user_id}
            value_dict = {'friendly_name': friendly_name,
                          'custom_avatar_url': custom_thumb,
                          'do_notify': do_notify,
                          'keep_history': keep_history,
                          'allow_guest': allow_guest
                          }
            try:
                monitor_db.upsert('users', value_dict, key_dict)
            except Exception as e:
                logger.warn("Tautulli Users :: Unable to execute database query for set_config: %s." % e)

    def get_details(self, user_id=None, user=None, email=None, include_last_seen=False):
        default_return = {'row_id': 0,
                          'user_id': 0,
                          'username': 'Local',
                          'friendly_name': 'Local',
                          'user_thumb': common.DEFAULT_USER_THUMB,
                          'email': '',
                          'is_active': 1,
                          'is_admin': '',
                          'is_home_user': 0,
                          'is_allow_sync': 0,
                          'is_restricted': 0,
                          'do_notify': 0,
                          'keep_history': 1,
                          'allow_guest': 0,
                          'deleted_user': 0,
                          'shared_libraries': (),
                          'last_seen': None
                          }

        if user_id in (None, '') and not user and not email:
            return default_return

        user_details = self.get_user_details(user_id=user_id, user=user, email=email,
                                             include_last_seen=include_last_seen)

        if user_details:
            return user_details

        else:
            logger.warn("Tautulli Users :: Unable to retrieve user %s from database. Requesting user list refresh."
                        % user_id if user_id else user)
            # Let's first refresh the user list to make sure the user isn't newly added and not in the db yet
            refresh_users()

            user_details = self.get_user_details(user_id=user_id, user=user, email=email,
                                                 include_last_seen=include_last_seen)

            if user_details:
                return user_details

            else:
                logger.warn("Tautulli Users :: Unable to retrieve user %s from database. Returning 'Local' user."
                            % user_id if user_id else user)
                # If there is no user data we must return something
                # Use "Local" user to retain compatibility with PlexWatch database value
                return default_return

    def get_user_details(self, user_id=None, user=None, email=None, include_last_seen=False):
        last_seen = 'NULL'
        join = ''
        if include_last_seen:
            last_seen = "MAX(session_history.started)"
            join = "LEFT OUTER JOIN session_history ON users.user_id = session_history.user_id"

        monitor_db = database.MonitorDatabase()

        try:
            if str(user_id).isdigit():
                where = "users.user_id = ?"
                args = [user_id]
            elif user:
                where = "users.username = ?"
                args = [user]
            elif email:
                where = "users.email = ?"
                args = [email]
            else:
                raise Exception("Missing user_id, username, or email")

            query = "SELECT users.id AS row_id, users.user_id, username, friendly_name, " \
                    "thumb AS user_thumb, custom_avatar_url AS custom_thumb, " \
                    "email, is_active, is_admin, is_home_user, is_allow_sync, is_restricted, " \
                    "do_notify, keep_history, deleted_user, " \
                    "allow_guest, shared_libraries, %s AS last_seen " \
                    "FROM users %s " \
                    "WHERE %s COLLATE NOCASE" % (last_seen, join, where)
            result = monitor_db.select(query, args=args)
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_user_details: %s." % e)
            result = []

        user_details = {}
        if result:
            for item in result:
                if session.get_session_user_id():
                    friendly_name = session.get_session_user()
                elif item['friendly_name']:
                    friendly_name = item['friendly_name']
                else:
                    friendly_name = item['username']

                if item['custom_thumb'] and item['custom_thumb'] != item['user_thumb']:
                    user_thumb = item['custom_thumb']
                elif item['user_thumb']:
                    user_thumb = item['user_thumb']
                else:
                    user_thumb = common.DEFAULT_USER_THUMB

                shared_libraries = tuple(item['shared_libraries'].split(';')) if item['shared_libraries'] else ()

                user_details = {'row_id': item['row_id'],
                                'user_id': item['user_id'],
                                'username': item['username'],
                                'friendly_name': friendly_name,
                                'user_thumb': user_thumb,
                                'email': item['email'],
                                'is_active': item['is_active'],
                                'is_admin': item['is_admin'],
                                'is_home_user': item['is_home_user'],
                                'is_allow_sync': item['is_allow_sync'],
                                'is_restricted': item['is_restricted'],
                                'do_notify': item['do_notify'],
                                'keep_history': item['keep_history'],
                                'deleted_user': item['deleted_user'],
                                'allow_guest': item['allow_guest'],
                                'shared_libraries': shared_libraries,
                                'last_seen': item['last_seen']
                                }
        return user_details

    def get_watch_time_stats(self, user_id=None, grouping=None, query_days=None):
        if not session.allow_session_user(user_id):
            return []

        if grouping is None:
            grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES

        if query_days and query_days is not None:
            query_days = map(helpers.cast_to_int, str(query_days).split(','))
        else:
            query_days = [1, 7, 30, 0]

        timestamp = helpers.timestamp()

        monitor_db = database.MonitorDatabase()

        user_watch_time_stats = []

        group_by = 'reference_id' if grouping else 'id'

        for days in query_days:
            timestamp_query = timestamp - days * 24 * 60 * 60

            try:
                if days > 0:
                    if str(user_id).isdigit():
                        query = "SELECT (SUM(stopped - started) - " \
                                "   SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, " \
                                "COUNT(DISTINCT %s) AS total_plays " \
                                "FROM session_history " \
                                "WHERE stopped >= %s " \
                                "AND user_id = ? " % (group_by, timestamp_query)
                        result = monitor_db.select(query, args=[user_id])
                    else:
                        result = []
                else:
                    if str(user_id).isdigit():
                        query = "SELECT (SUM(stopped - started) - " \
                                "   SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, " \
                                "COUNT(DISTINCT %s) AS total_plays " \
                                "FROM session_history " \
                                "WHERE user_id = ? " % group_by
                        result = monitor_db.select(query, args=[user_id])
                    else:
                        result = []
            except Exception as e:
                logger.warn("Tautulli Users :: Unable to execute database query for get_watch_time_stats: %s." % e)
                result = []

            for item in result:
                if item['total_time']:
                    total_time = item['total_time']
                    total_plays = item['total_plays']
                else:
                    total_time = 0
                    total_plays = 0

                row = {'query_days': days,
                       'total_time': total_time,
                       'total_plays': total_plays
                       }

                user_watch_time_stats.append(row)

        return user_watch_time_stats

    def get_player_stats(self, user_id=None, grouping=None):
        if not session.allow_session_user(user_id):
            return []

        if grouping is None:
            grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES

        monitor_db = database.MonitorDatabase()

        player_stats = []
        result_id = 0

        group_by = 'reference_id' if grouping else 'id'

        try:
            if str(user_id).isdigit():
                query = "SELECT player, COUNT(DISTINCT %s) as total_plays, (SUM(stopped - started) - " \
                        "SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, " \
                        "platform " \
                        "FROM session_history " \
                        "WHERE user_id = ? " \
                        "GROUP BY player " \
                        "ORDER BY total_plays DESC, total_time DESC" % group_by
                result = monitor_db.select(query, args=[user_id])
            else:
                result = []
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_player_stats: %s." % e)
            result = []

        for item in result:
            # Rename Mystery platform names
            platform = common.PLATFORM_NAME_OVERRIDES.get(item['platform'], item['platform'])
            platform_name = next((v for k, v in common.PLATFORM_NAMES.items() if k in platform.lower()), 'default')

            row = {'player_name': item['player'],
                   'platform': platform,
                   'platform_name': platform_name,
                   'total_plays': item['total_plays'],
                   'total_time': item['total_time'],
                   'result_id': result_id
                   }
            player_stats.append(row)
            result_id += 1

        return player_stats

    def get_recently_watched(self, user_id=None, limit='10'):
        if not session.allow_session_user(user_id):
            return []

        monitor_db = database.MonitorDatabase()
        recently_watched = []

        if not limit.isdigit():
            limit = '10'

        try:
            if str(user_id).isdigit():
                query = "SELECT session_history.id, session_history.media_type, guid, " \
                        "session_history.rating_key, session_history.parent_rating_key, session_history.grandparent_rating_key, " \
                        "title, parent_title, grandparent_title, original_title, " \
                        "thumb, parent_thumb, grandparent_thumb, media_index, parent_media_index, " \
                        "year, originally_available_at, added_at, live, started, user " \
                        "FROM session_history_metadata " \
                        "JOIN session_history ON session_history_metadata.id = session_history.id " \
                        "WHERE user_id = ? " \
                        "GROUP BY (CASE WHEN session_history.media_type = 'track' THEN session_history.parent_rating_key " \
                        "   ELSE session_history.rating_key END) " \
                        "ORDER BY MAX(started) DESC LIMIT ?"
                result = monitor_db.select(query, args=[user_id, limit])
            else:
                result = []
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_recently_watched: %s." % e)
            result = []

        for row in result:
            if row['media_type'] == 'episode' and row['parent_thumb']:
                thumb = row['parent_thumb']
            elif row['media_type'] == 'episode':
                thumb = row['grandparent_thumb']
            else:
                thumb = row['thumb']

            recent_output = {'row_id': row['id'],
                             'media_type': row['media_type'],
                             'rating_key': row['rating_key'],
                             'parent_rating_key': row['parent_rating_key'],
                             'grandparent_rating_key': row['grandparent_rating_key'],
                             'title': row['title'],
                             'parent_title': row['parent_title'],
                             'grandparent_title': row['grandparent_title'],
                             'original_title': row['original_title'],
                             'thumb': thumb,
                             'media_index': row['media_index'],
                             'parent_media_index': row['parent_media_index'],
                             'year': row['year'],
                             'originally_available_at': row['originally_available_at'],
                             'live': row['live'],
                             'guid': row['guid'],
                             'time': row['started'],
                             'user': row['user']
                             }
            recently_watched.append(recent_output)

        return recently_watched

    def get_users(self, include_deleted=False):
        monitor_db = database.MonitorDatabase()

        where = '' if include_deleted else 'WHERE deleted_user = 0'

        try:
            query = "SELECT id AS row_id, user_id, username, friendly_name, thumb, custom_avatar_url, email, " \
                    "is_active, is_admin, is_home_user, is_allow_sync, is_restricted, " \
                    "do_notify, keep_history, allow_guest, shared_libraries, " \
                    "filter_all, filter_movies, filter_tv, filter_music, filter_photos " \
                    "FROM users %s" % where
            result = monitor_db.select(query=query)
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_users: %s." % e)
            return []

        users = []
        for item in result:
            shared_libraries = tuple(item['shared_libraries'].split(';')) if item['shared_libraries'] else ()

            user = {'row_id': item['row_id'],
                    'user_id': item['user_id'],
                    'username': item['username'],
                    'friendly_name': item['friendly_name'] or item['username'],
                    'thumb': item['custom_avatar_url'] or item['thumb'],
                    'email': item['email'],
                    'is_active': item['is_active'],
                    'is_admin': item['is_admin'],
                    'is_home_user': item['is_home_user'],
                    'is_allow_sync': item['is_allow_sync'],
                    'is_restricted': item['is_restricted'],
                    'do_notify': item['do_notify'],
                    'keep_history': item['keep_history'],
                    'allow_guest': item['allow_guest'],
                    'shared_libraries': shared_libraries,
                    'filter_all': item['filter_all'],
                    'filter_movies': item['filter_movies'],
                    'filter_tv': item['filter_tv'],
                    'filter_music': item['filter_music'],
                    'filter_photos': item['filter_photos'],
                    }
            users.append(user)

        return users

    def delete(self, user_id=None, row_ids=None, purge_only=False):
        monitor_db = database.MonitorDatabase()

        if row_ids and row_ids is not None:
            row_ids = list(map(helpers.cast_to_int, row_ids.split(',')))

            # Get the user_ids corresponding to the row_ids
            result = monitor_db.select("SELECT user_id FROM users "
                                       "WHERE id IN ({})".format(",".join(["?"] * len(row_ids))), row_ids)

            success = []
            for user in result:
                success.append(self.delete(user_id=user['user_id'],
                                           purge_only=purge_only))
            return all(success)

        elif str(user_id).isdigit():
            delete_success = database.delete_user_history(user_id=user_id)

            if purge_only:
                return delete_success
            else:
                logger.info("Tautulli Users :: Deleting user with user_id %s from database."
                            % user_id)
                try:
                    monitor_db.action("UPDATE users "
                                      "SET deleted_user = 1, keep_history = 0, do_notify = 0 "
                                      "WHERE user_id = ?", [user_id])
                    return delete_success
                except Exception as e:
                    logger.warn("Tautulli Users :: Unable to execute database query for delete: %s." % e)

        else:
            return False

    def undelete(self, user_id=None, username=None):
        monitor_db = database.MonitorDatabase()

        try:
            if user_id and str(user_id).isdigit():
                query = "SELECT * FROM users WHERE user_id = ?"
                result = monitor_db.select(query=query, args=[user_id])
                if result:
                    logger.info("Tautulli Users :: Re-adding user with id %s to database." % user_id)
                    monitor_db.action("UPDATE users "
                                      "SET deleted_user = 0, keep_history = 1, do_notify = 1 "
                                      "WHERE user_id = ?", [user_id])
                    return True
                else:
                    return False

            elif username:
                query = "SELECT * FROM users WHERE username = ?"
                result = monitor_db.select(query=query, args=[username])
                if result:
                    logger.info("Tautulli Users :: Re-adding user with username %s to database." % username)
                    monitor_db.action("UPDATE users "
                                      "SET deleted_user = 0, keep_history = 1, do_notify = 1 "
                                      "WHERE username = ?", [username])
                    return True
                else:
                    return False

        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for undelete: %s." % e)

    # Keep method for PlexWatch/Plexivity import
    def get_user_id(self, user=None):
        if user:
            try:
                monitor_db = database.MonitorDatabase()
                query = "SELECT user_id FROM users WHERE username = ?"
                result = monitor_db.select_single(query, args=[user])
                if result:
                    return result['user_id']
                else:
                    return None
            except:
                return None

        return None

    def get_user_names(self, kwargs=None):
        monitor_db = database.MonitorDatabase()

        user_cond = ''
        if session.get_session_user_id():
            user_cond = "AND user_id = %s " % session.get_session_user_id()

        try:
            query = "SELECT user_id, " \
                    "(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = '' \
                    THEN users.username ELSE users.friendly_name END) AS friendly_name " \
                    "FROM users " \
                    "WHERE deleted_user = 0 %s" % user_cond

            result = monitor_db.select(query)
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_user_names: %s." % e)
            return None

        return session.friendly_name_to_username(result)

    def get_tokens(self, user_id=None):
        tokens = {
            'allow_guest': 0,
            'user_token': '',
            'server_token': ''
        }

        if user_id:
            try:
                monitor_db = database.MonitorDatabase()
                query = "SELECT allow_guest, user_token, server_token FROM users " \
                        "WHERE user_id = ? AND deleted_user = 0"
                result = monitor_db.select_single(query, args=[user_id])
                if result:
                    tokens = {'allow_guest': result['allow_guest'],
                              'user_token': result['user_token'],
                              'server_token': result['server_token']
                              }
                    return tokens
                else:
                    return tokens
            except:
                return tokens

        return tokens

    def get_filters(self, user_id=None):
        if not user_id:
            return {}

        try:
            monitor_db = database.MonitorDatabase()
            query = "SELECT filter_all, filter_movies, filter_tv, filter_music, filter_photos FROM users " \
                    "WHERE user_id = ?"
            result = monitor_db.select_single(query, args=[user_id])
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_filters: %s." % e)
            result = {}

        filters_list = {}
        for k, v in result.items():
            filters = {}

            for f in v.split('|'):
                if 'contentRating=' in f or 'label=' in f:
                    filters.update(dict(parse_qsl(f)))

            filters['content_rating'] = tuple(f for f in filters.pop('contentRating', '').split(',') if f)
            filters['labels'] = tuple(f for f in filters.pop('label', '').split(',') if f)

            filters_list[k] = filters

        return filters_list

    def set_user_login(self, user_id=None, user=None, user_group=None, ip_address=None, host=None,
                       user_agent=None, success=0, expiry=None, jwt_token=None):

        if user_id is None or str(user_id).isdigit():
            monitor_db = database.MonitorDatabase()

            if expiry is not None:
                expiry = helpers.datetime_to_iso(expiry)

            keys = {'timestamp': helpers.timestamp(),
                    'user_id': user_id}

            values = {'user': user,
                      'user_group': user_group,
                      'ip_address': ip_address,
                      'host': host,
                      'user_agent': user_agent,
                      'success': success,
                      'expiry': expiry,
                      'jwt_token': jwt_token}

            try:
                monitor_db.upsert(table_name='user_login', key_dict=keys, value_dict=values)
            except Exception as e:
                logger.warn("Tautulli Users :: Unable to execute database query for set_login_log: %s." % e)

    def get_user_login(self, jwt_token):
        monitor_db = database.MonitorDatabase()
        result = monitor_db.select_single("SELECT * FROM user_login "
                                          "WHERE jwt_token = ?",
                                          [jwt_token])
        return result

    def clear_user_login_token(self, jwt_token=None, row_ids=None):
        monitor_db = database.MonitorDatabase()

        if jwt_token:
            logger.debug("Tautulli Users :: Clearing user JWT token.")
            try:
                monitor_db.action("UPDATE user_login SET jwt_token = NULL "
                                  "WHERE jwt_token = ?",
                                  [jwt_token])
            except Exception as e:
                logger.error("Tautulli Users :: Unable to clear user JWT token: %s.", e)
                return False

        elif row_ids and row_ids is not None:
            row_ids = list(map(helpers.cast_to_int, row_ids.split(',')))
            logger.debug("Tautulli Users :: Clearing JWT tokens for row_ids %s.", row_ids)
            try:
                monitor_db.action("UPDATE user_login SET jwt_token = NULL "
                                  "WHERE id in ({})".format(",".join(["?"] * len(row_ids))),
                                  row_ids)
            except Exception as e:
                logger.error("Tautulli Users :: Unable to clear JWT tokens: %s.", e)
                return False

        return True

    def get_datatables_user_login(self, user_id=None, jwt_token=None, kwargs=None):
        default_return = {'recordsFiltered': 0,
                          'recordsTotal': 0,
                          'draw': 0,
                          'data': []}

        if not session.allow_session_user(user_id):
            return default_return

        data_tables = datatables.DataTables()

        if session.get_session_user_id():
            custom_where = [['user_login.user_id', session.get_session_user_id()]]
        else:
            custom_where = [['user_login.user_id', user_id]] if user_id else []

        columns = ["user_login.id AS row_id",
                   "user_login.timestamp",
                   "user_login.user_id",
                   "user_login.user",
                   "user_login.user_group",
                   "user_login.ip_address",
                   "user_login.host",
                   "user_login.user_agent",
                   "user_login.success",
                   "user_login.expiry",
                   "user_login.jwt_token",
                   "(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = '' \
                    THEN users.username ELSE users.friendly_name END) AS friendly_name"
                   ]

        try:
            query = data_tables.ssp_query(table_name='user_login',
                                          columns=columns,
                                          custom_where=custom_where,
                                          group_by=[],
                                          join_types=['LEFT OUTER JOIN'],
                                          join_tables=['users'],
                                          join_evals=[['user_login.user_id', 'users.user_id']],
                                          kwargs=kwargs)
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for get_datatables_user_login: %s." % e)
            return default_return

        results = query['result']

        rows = []
        for item in results:
            (os, browser) = httpagentparser.simple_detect(item['user_agent'])

            expiry = None
            current = False
            if item['jwt_token'] and item['expiry']:
                _expiry = helpers.iso_to_datetime(item['expiry'])
                if _expiry > arrow.now():
                    expiry = _expiry.strftime('%Y-%m-%d %H:%M:%S')
                current = (item['jwt_token'] == jwt_token)

            row = {'row_id': item['row_id'],
                   'timestamp': item['timestamp'],
                   'user_id': item['user_id'],
                   'user_group': item['user_group'],
                   'ip_address': item['ip_address'],
                   'host': item['host'],
                   'user_agent': item['user_agent'],
                   'os': os,
                   'browser': browser,
                   'success': item['success'],
                   'expiry': expiry,
                   'current': current,
                   'friendly_name': item['friendly_name'] or item['user']
                   }

            rows.append(row)

        dict = {'recordsFiltered': query['filteredCount'],
                'recordsTotal': query['totalCount'],
                'data': session.friendly_name_to_username(rows),
                'draw': query['draw']
                }

        return dict

    def delete_login_log(self):
        monitor_db = database.MonitorDatabase()

        try:
            logger.info("Tautulli Users :: Clearing login logs from database.")
            monitor_db.action('DELETE FROM user_login')
            monitor_db.action('VACUUM')
            return True
        except Exception as e:
            logger.warn("Tautulli Users :: Unable to execute database query for delete_login_log: %s." % e)
            return False