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


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_email_code(db, user_id):  # Get user's reg_code
    try:
        cursor = db.execute(
            "SELECT code FROM points WHERE id = ?",
            (user_id,)
        )
        return cursor.fetchone()[0]
    except Error as gecerror:
        current_app.logger.error(f'Couldn\'t look up points for {user_id}: {gecerror.args[0]}')


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


def get_all_users_by_name(db, username):  # Look up all users' points by username
    try:
        cursor = db.execute(
            "SELECT * 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 * 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 * 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()
        return True
    except Error as acperror:
        current_app.logger.error(f'Couldn\'t give {points} points to {user_id}: {acperror.args[0]}')
        return False


def set_email_code(db, user_id, reg_code):  # Award points to user by user id
    try:
        db.execute(
            "UPDATE points SET code = ? WHERE id = ?",
            (reg_code, user_id,)
        )
        db.commit()
        return True
    except Error as secerror:
        current_app.logger.error(f'Couldn\'t set reg code \"{reg_code}\" for {user_id}: {secerror.args[0]}')
        return False


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()
        return True
    except Error as aperror:
        current_app.logger.error(f'Couldn\'t adjust points for {user_id}: {aperror.args[0]}')
        return False


def delete_user(db, user_id):
    try:
        db.execute(
            "DELETE FROM points WHERE id = ?",
            (user_id,)
            )
        db.commit()
        return True
    except Error as du_error:
        current_app.logger.error(f'Couldn\'t change delete {user_id} from the database: {du_error.args[0]}')
        return False


def change_email(db, user_id, new_email):  # For streamer to manually adjust a user's points
    try:
        ids = get_id_by_email(db, new_email)
        for user in ids:
            if user[0] != user_id:  # If the found email belongs to a different user
                if 'temp' in user[0]:  # If the email address belongs to a temp user
                    existing_points = get_users_points(db, user_id)
                    new_points = existing_points + user[1]
                    if adjust_points(db, user_id, new_points):  # Add points to correct user
                        current_app.logger.info(f'Email was already in the database as a {user[0]}. Added points to {user_id}')
                        if delete_user(db, user[0]):  # Delete temp user
                            current_app.logger.info(f'Removed temp user {user_id} from the database.')
            elif new_email is not None:
                current_app.logger.error(f'Couldn\'t change email address for {user_id}. {new_email} already belongs to {user[0]}')
        db.execute(
            "UPDATE points SET email = ? WHERE id = ?",
            (new_email, user_id,)
        )
        db.commit()
        return True
    except Error as ce_error:
        current_app.logger.error(f'Couldn\'t change email address for {user_id}: {ce_error.args[0]}')
        return False


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_email_to_points(db, email, points):  # Add an anonymous user and points to the database
    try:
        id = f'temp{random.randint(10000, 99999)}'  # Create random id with "temp" prepended
        db.execute(
            "INSERT INTO points(id, points, user_authed, email) VALUES(?, ?, 0, ?)",
            (id, points, email)
        )
        db.commit()
        return True
    except Error as aetperror:
        current_app.logger.error(f'Couldn\'t add {email} to points database: {aetperror.args[0]}')
        return False


def add_user_to_points(db, user_id, display_name, authed):  # Add a user to the points database
    try:
        cursor = db.execute(
            "SELECT points, name, user_authed FROM points WHERE id = ?",
            (user_id,)
        )
        user = cursor.fetchone()
        if user is None:  # Add the user if they're not in the database
            cursor.execute(
                "INSERT INTO points(id, name, points, user_authed) VALUES(?, ?, 10, ?)",
                (user_id, display_name, authed)
            )
        if user is not None and user[1] is None:  # If their name has changed, change name in the database
            cursor.execute(
                "UPDATE points SET name = ?, user_authed = ? WHERE id = ?",
                (display_name, authed, user_id)
            )
        if user is not None and user[2] != authed:  # If they've authenticated, update the database
            cursor.execute(
                "UPDATE points SET user_authed = ? WHERE id = ?",
                (authed, user_id)
            )
        db.commit()
        return True
    except Error as autperror:
        current_app.logger.error(f'Couldn\'t add {user_id}/{display_name}/{authed} to database: {autperror.args[0]}')
        return False


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]}')