from flask import current_app
from sqlite3 import Error
from re import sub


def get_users_points(db, user_id):  # Look up one user's points by user id
    try:
        cursor = db.execute(
            "SELECT points FROM points WHERE id = ?",
            (user_id,)
        )
        return cursor.fetchone()[0]
    except Error as guperror:
        current_app.logger.error(f'Couldn\'t look up points for {user_id}: {guperror.args[0]}')


def get_all_users_by_name(db, username):  # Look up all users' points by username
    try:
        cursor = db.execute(
            "SELECT name, points FROM points WHERE name = ?",
            (username,)
        )
        users = cursor.fetchall()
        return users
    except Error as gaubnerror:
        current_app.logger.error(f'Couldn\'t look up points for {username} by username: {gaubnerror.args[0]}')


def get_all_users_with_user_id(db, user_id):  # Look up all users' points by user id
    try:
        cursor = db.execute(
            "SELECT id, name, points FROM points WHERE id = ?",
            (user_id,)
        )
        users = cursor.fetchall()
        return users
    except Error as gauwuierror:
        current_app.logger.error(f'Couldn\'t look up points for {user_id} by user_id: {gauwuierror.args[0]}')


def get_all_users(db):  # Get all users' details from points database
    try:
        cursor = db.execute(
            "SELECT name, points, id FROM points"
        )
        users = cursor.fetchall()
        return users
    except Error as gauerror:
        current_app.logger.error(f'Couldn\'t get all users\' points: {gauerror.args[0]}')


def award_chat_points(db, user_id, points):  # Award points to user by user id
    try:
        db.execute(
            "UPDATE points SET points = points + ? WHERE id = ?",
            (points, user_id,)
        )
        db.commit()
    except Error as acperror:
        current_app.logger.error(f'Couldn\'t give {points} points to {user_id}: {acperror.args[0]}')


def adjust_points(db, user_id, points):  # For streamer to manually adjust a user's points
    try:
        db.execute(
            "UPDATE points SET points = ? WHERE id = ?",
            (points, user_id,)
        )
        db.commit()
    except Error as aperror:
        current_app.logger.error(f'Couldn\'t adjust points for {user_id}: {aperror.args[0]}')


def spend_points(db, user_id, points):  # A user spends points on a redeem
    try:
        db.execute(
            "UPDATE points SET points = points - ? WHERE id = ?",
            (points, user_id,)
        )
        db.commit()
        return True
    except Error as sperror:
        current_app.logger.error(f'Couldn\'t spend {user_id}\'s {points} points: {sperror.args[0]}')
        return False


def refund_points(db, user_id, points):  # Streamer refunds points for a redeem
    try:
        db.execute(
            "UPDATE points SET points = points + ? WHERE id = ?",
            (points, user_id,)
        )
        db.commit()
        return True
    except Error as rerror:
        current_app.logger.error(f'Couldn\'t refund {points} points for {user_id}: {rerror.args[0]}')
        return False


def user_in_points(db, user_id):  # Check if a user is in the points database
    try:
        cursor = db.execute(
            "SELECT points FROM points WHERE id = ?",
            (user_id,)
        )
        if cursor.fetchone() is None:
            return False
        return True
    except Error as uiperror:
        current_app.logger.error(f'Couldn\'t for {user_id} in points database: {uiperror.args[0]}')


def add_user_to_points(db, user_id, display_name):  # Add a user to the points database
    try:
        cursor = db.execute(
            "SELECT points, name FROM points WHERE id = ?",
            (user_id,)
        )
        user = cursor.fetchone()
        if user is None:
            cursor.execute(
                "INSERT INTO points(id, name, points, user_authed) VALUES(?, ?, 10, 0)",
                (user_id, display_name)
            )
        if user is not None and user[1] is None:
            cursor.execute(
                """UPDATE points
                SET name = ?
                WHERE id = ?""",
                (display_name, user_id)
            )
        db.commit()
    except Error as autperror:
        current_app.logger.error(f'Couldn\'t add {user_id}/{display_name} to points database: {autperror.args[0]}')


def change_name(db, user_id, new_name):  # Change a user name in the points database
    try:
        db.execute(
            "UPDATE points SET name = ? WHERE id = ?",
            (new_name, user_id)
        )
        db.commit()
    except Error as cnerror:
        current_app.logger.error(f'Couldn\'t change name to {new_name} for {user_id}: {cnerror.args[0]}')


def remove_duplicates(db, user_id, username):  # Remove duplicate usernames
    try:
        db.execute(
            """UPDATE points
            SET name = NULL
            WHERE name = ? AND NOT id = ?""",
            (username, user_id)
        )
        db.commit()
    except Error as rderror:
        current_app.logger.error(f'Couldn\'t remove duplicate username {username} for {user_id}: {rderror.args[0]}')