import pyodbc
import hashlib

def connect_to_database(db_name:str="K9Data"):
    conn_str = (
        r"Driver={ODBC Driver 17 for SQL Server};"
        r"Server=localhost\SQLEXPRESS;"
        f"Database={db_name};"
        "Trusted_Connection=Yes;"
    )
    return pyodbc.connect(conn_str)

# Hash a password and relate the hash to a specific user input
def hash_password(password):
    password_hash = hashlib.sha256()
    password_hash.update(password.encode('utf-8'))
    storable_password = password_hash.hexdigest()
    return storable_password


def authenticate_user(username, password):
    conn = None
    try:
        conn = connect_to_database("K9Data")
        cursor = conn.cursor()

        # Query to find the user
        cursor.execute("SELECT passwordhash FROM Users WHERE username = ?", (username,))
        result = cursor.fetchone()
    
        if result:
            # User exists, check the password
            stored_password_hash = result[0]
            password_hash = hash_password(password)
            
            if stored_password_hash == password_hash:
                return {"success": True, "message": "User authenticated"}
            else:
                return {"success": False, "message": "Invalid password"}
        else:
            return {"success": False, "message": "User not found"}
    
    except Exception as e:
        print("Error:", e)
        return {"success": False, "message": "An error occurred during authentication"}
    
    finally:
        if conn != None:
            conn.close()

# Register a new user
def register_user(username, full_name, email, password, security_question, security_answer):
    conn = None
    try:
        conn = connect_to_database()
        cursor = conn.cursor()

        # Check if the username already exists
        cursor.execute("SELECT COUNT(*) FROM Users WHERE username = ?", (username,))
        result = cursor.fetchone()
        if result[0] > 0:
            return {"success": False, "message": "User already exists."}
        # Check if the email already exists
        cursor.execute("SELECT COUNT(*) FROM Users WHERE email = ?", (email,))
        if cursor.fetchone()[0] > 0:
            return {"success": False, "message": "Email already exists."}

        # Validate password and username length
        if len(password) < 8:
            return {"success": False, "message": "Password must be at least 8 characters long."}
        if len(username) < 4:
            return {"success": False, "message": "Username must be at least 4 characters long."}

        # Hash the password and security answer
        password_hash = hash_password(password)
        securityanswer = hash_password(security_answer)

        # Insert the new user into the database
        cursor.execute("""
            INSERT INTO Users (username, fullname, email, passwordhash, securityquestion, securityanswer)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (username, full_name, email, password_hash, security_question, securityanswer))

        conn.commit()  # Commit the transaction

        return {"success": True, "message": "Registration successful."}

    except Exception as e:
        print("Error:", e)  # Print the specific error
        return {"success": False, "message": str(e)}  # Return the error message

    finally:
        if conn != None:
            conn.close()

def reset_password(username, new_password, confirm_new_password):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Check if the user exists
    cursor.execute("SELECT passwordhash FROM users WHERE username = ?", (username,))
    current_user = cursor.fetchone()

    if current_user is None:
        return {"success": False, "message": "User not found."}

    if new_password != confirm_new_password:
        return {"success": False, "message": "Passwords do not match."}

    if len(new_password) < 8:
        return {"success": False, "message": "New password must be at least 8 characters long."}

    new_password_hash = hash_password(new_password)

    # Check if the new password is the same as the old password
    if current_user[0] == new_password_hash:
        return {"success": False, "message": "New password cannot be the same as the old password."}

    # Update the password in the database
    cursor.execute("UPDATE Users SET passwordhash = ? WHERE username = ?", (new_password_hash, username))
    conn.commit()
    conn.close()

    return {"success": True, "message": "Password successfully changed."}

def retrieve_security_question(username):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Query to find the user's security question
    cursor.execute("SELECT SecurityQuestion FROM Users WHERE Username = ?", (username,))
    result = cursor.fetchone()

    conn.close()

    if result:
        return {"success": True, "securityQuestion": result[0]}
    return {"success": False, "message": "User does not exist."}

def retrieve_email(username):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Query to find the user's security question
    cursor.execute("SELECT email FROM Users WHERE username = ?", (username,))
    result = cursor.fetchone()

    conn.close()

    if result:
        return {"success": True, "email": result[0]}
    return {"success": False, "message": "User does not exist."}

def verify_security_answer(username, security_answer):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Hash the provided security answer
    security_answer_hash = hash_password(security_answer)

    # Query to find the user's security answer
    cursor.execute("SELECT securityanswer FROM Users WHERE username = ?", (username,))
    result = cursor.fetchone()

    conn.close()

    if result and result[0] == security_answer_hash:
        return {"success": True, "message": "Security answer verified."}
    return {"success": False, "message": "Security answer not verified."}

def delete_user(username):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Execute the DELETE statement
    cursor.execute("DELETE FROM Users WHERE username = ?", (username,))
    conn.commit()  # Commit the changes

    # Check if any row was deleted
    if cursor.rowcount > 0:
        conn.close()
        return "User deleted successfully."
    else:
        conn.close()
        return "User not found."
    
def reactivate_user(username):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Update the user's active status
    cursor.execute("UPDATE Users SET isactive = ? WHERE username = ?", (True, username))
    conn.commit()  # Commit the changes

    # Check if any row was updated
    if cursor.rowcount > 0:
        conn.close()
        return "User reactivated successfully."
    else:
        conn.close()
        return "User not found."
    
# Deactivate a user account
def deactivate_user(username):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Update the user's active status
    cursor.execute("UPDATE Users SET isactive = ? WHERE username = ?", (False, username))
    conn.commit()  # Commit the changes

    # Check if any row was updated
    if cursor.rowcount > 0:
        conn.close()
        return "User deactivated successfully."
    else:
        conn.close()
        return "User not found."
    
def update_user(cur_username, username=None, email=None, password=None, confirmpassword=None, securityquestion=None, securityanswer=None):
    conn = connect_to_database()
    cursor = conn.cursor()

    # Check if the user exists
    cursor.execute("SELECT passwordhash, SecurityQuestion, SecurityAnswer FROM Users WHERE Username = ?", (cur_username,))
    current_user = cursor.fetchone()

    if current_user is None:
        return {"success": False, "message": "User not found."}

    # Update username if provided
    if username:
        if len(username) > 50:
            return {"success": False, "message": "Username must be at most 50 characters long."}
        
        # Check if the username already exists
        cursor.execute("SELECT COUNT(*) FROM Users WHERE Username = ?", (username,))

        result = cursor.fetchone()
        if result[0] > 0:
            return {"success": False, "message": "Username already exists."}
        
        cursor.execute("UPDATE Users SET username = ? WHERE username = ?", (username, cur_username))
        conn.commit()
        cur_username = username  # Update the username variable to the new username
    
    # Update email if provided
    if email:
        # Check if the email already exists
        cursor.execute("SELECT COUNT(*) FROM Users WHERE Email = ?", (email,))
        result = cursor.fetchone()
        if result[0] > 0:
            return {"success": False, "message": "Email already exists."}
        cursor.execute("UPDATE users SET email = ? WHERE username = ?", (email, cur_username))
        conn.commit()
    
    # Update password if provided
    if password:
        if password != confirmpassword:
            return {"success": False, "message": "Passwords do not match."}

        if len(password) < 8:
            return {"success": False, "message": "New password must be at least 8 characters long."}

        new_password_hash = hash_password(password)

        # Check if the new password is the same as the old password
        if current_user[0] == new_password_hash:
            return {"success": False, "message": "New password cannot be the same as the old password."}
        
        cursor.execute("UPDATE Users SET PasswordHash = ? WHERE Username = ?", (new_password_hash, cur_username))
        conn.commit()

    if securityquestion:
        if securityquestion != current_user[1]:
            cursor.execute("UPDATE Users SET SecurityQuestion = ? WHERE Username = ?", (securityquestion, cur_username))
            print("I updated this mf secuerity question")
            conn.commit()
            if securityanswer:
                if securityanswer != current_user[2]:
                    security_answer_hash = hash_password(securityanswer)
                    cursor.execute("UPDATE Users SET SecurityAnswer = ? WHERE Username = ?", (security_answer_hash, cur_username))
                    conn.commit()
                else:
                    return {"success": False, "message": "Security answer cannot be the same as before."}
            else: 
                return {"success": False, "message": "Security answer must be provided."}
    
    elif securityanswer:
        if securityanswer != current_user[2]:
            security_answer_hash = hash_password(securityanswer)
            cursor.execute("UPDATE Users SET SecurityAnswer = ? WHERE Username = ?", (security_answer_hash, cur_username))
            conn.commit()
        else:
            return {"success": False, "message": "Security ."}

    conn.close()

    return {"success": True, "message": "User information successfully updated."}