dbCode.py

#Nick Guyette
#4/17/25
import pymysql
import creds 
from flask import Flask, request
from flask import render_template
from flask import render_template, request, redirect, url_for, flash,session
import pymysql
import creds
import boto3
def get_conn():
    conn = pymysql.connect(
        host= creds.host,
        user= creds.user, 
        password = creds.password,
        db=creds.db,
        )
    return conn

def execute_query(query, args=()):
    cur = get_conn().cursor()
    cur.execute(query, args)
    rows = cur.fetchall()
    cur.close()
    return rows
dynamodb = boto3.resource('dynamodb', region_name='us-east-1')  # change region as needed
table = dynamodb.Table('Users')
app = Flask(__name__)
app.secret_key = 'your_secret_key' # this is an artifact for using flash displays; 
                                   # it is required, but you can leave this alone
username=""
def add_Website_account(username,password,email):#function for making the login accounts for the website
    response = table.put_item(
                Item={
                    'username': username,
                    'Password': password,
                    'Email': email
                }
            )

def check_passwd(username, passwd):#verifies the password the user entered.
    response = table.get_item(Key={'username': username})
    
    # Check if the item exists and if the password matches
    if 'Item' in response and passwd and response['Item'].get('Password') == passwd:
        session['username'] = username
        return True#if correct, return true, the user will be logged in
    
    return False#otherwise fail, the user is not signed in
def delete_web_acct():#function for deleting the Website Account used to sign into the site
    username=session.get('username')
    response = table.delete_item(
        Key={
                'username': username
            }
        )
def get_ban(user_id):#retrieves the ban information for a particular player.  
    try:
        conn = get_conn()
        cursor = conn.cursor()#the query gets the ban id for the user if the ban is active. 
        cursor.execute("""
                        SELECT ban_id FROM bans
                        WHERE user_id = %s AND is_active = 1
                    """, (user_id,))
        existing_ban = cursor.fetchone()
        conn.commit()
        cursor.close()
        conn.close()
        return existing_ban#return the ban so it can be displayed to the user.  
    except:
        conn.rollback()#if the function fails, revert changes so corruption does not occur
def create_ban(user_id,ban_reason,current_user_id):#adds a ban record for a user. 
    try:
        conn = get_conn()
        cursor = conn.cursor()#The below query inseted a new ban into the table
        cursor.execute("""
                            INSERT INTO bans (user_id, reason, banned_by, is_active)
                            VALUES (%s, %s, %s, 1)
                        """, (user_id, ban_reason, current_user_id))
        conn.commit()
        cursor.close()
        conn.close()
    except:
        conn.rollback()
def update_ban(ban_reason,user_id):#changes the ban reason for a user.  
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("""
                            UPDATE bans SET reason = %s
                            WHERE user_id = %s AND is_active = 1
                        """, (ban_reason, user_id))
        conn.commit()
        cursor.close()
        conn.close()
    except:
        conn.rollback()
def unban_user(user_id):#removes a ban record from a user.  
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("""
                        UPDATE bans SET is_active = 0
                        WHERE user_id = %s AND is_active = 1
                    """, (user_id,))
        conn.commit()
        cursor.close()
        conn.close()
    except:
        conn.rollback()
def get_current_user(current_username):#returns the user object given a username.  
#Used for determining the Admin that banned a player-This data is not revealed by the website.  
    try:
        conn = get_conn()
        cursor = conn.cursor()
        result=cursor.execute("SELECT user_id FROM users WHERE username = %s", (current_username,))
        result=cursor.fetchone()
        conn.commit()
        cursor.close()
        conn.close()
        return result
    except:
        conn.rollback()
def create_game_user(username,email,first_name,last_name,school_id_number):#creates a user for the game section.  this is a SQL operation
    #add to users
    conn = get_conn()
    cursor = conn.cursor()#inserts the user into the user table
    query = """INSERT INTO users (username, email, first_name, last_name, school_id_number) 
          VALUES (%s, %s, %s, %s, %s)"""
    cursor.execute(query, (username, email, first_name, last_name, school_id_number))
    conn.commit()
    cursor.execute("SELECT LAST_INSERT_ID()")#get the ID of the new user.  This is needed to initialize the user in the bans table
    user_id = cursor.fetchone()[0]
    conn.close()

    #add to bans table
    ban_query = """INSERT INTO bans (user_id, reason, banned_by, is_active) 
             VALUES (%s, %s, %s, %s)"""
    execute_query(ban_query, (user_id, "Default entry - not banned", 1, 0))#The user must be in the table, but will not be banned upon creation

def delete_game_user(username):#deletes a user account from the game account system
    conn = get_conn()
    cursor = conn.cursor()
    
    # verify user exists
    check_query = "SELECT COUNT(*) FROM users WHERE username = %s"
    delete_bans_query = "DELETE FROM bans WHERE user_id = (SELECT user_id FROM users WHERE username = %s)"
    delete_query = "DELETE FROM users WHERE username = %s"
    cursor.execute(check_query, (username,))
    user_count = cursor.fetchone()[0]
    if user_count > 0:#if found, delete
        try:
            cursor.execute(delete_bans_query, (username,))
            cursor.execute(delete_query, (username,))
            conn.commit()
            result = '1' #1 =success
            print(f"User {username} deleted successfully")
        except Exception as e:
            print(f"Error during deletion: {e}")
            conn.rollback()
            result = '0'  # 0=Failure
    else:
        # User doesn't exist
        result = '0'  # can't delete nonexistent account
        print(f"User {username} doesn't exist")
    
    conn.close()
    return result
def display_users_db():#displays all of the game accounts in the SQL database.  
    users_list = execute_query("""SELECT u.username, u.email, u.first_name, u.last_name,
    CASE WHEN b.ban_id IS NOT NULL AND b.is_active = 1 THEN 'Yes' ELSE 'No' END AS ban_status
    FROM users u
    LEFT JOIN bans b ON u.user_id = b.user_id
    LIMIT 500""")
    return users_list
def get_user_data(username):#function for retrieving data about a user.  Used in the display users section
    user_data = execute_query("""
        SELECT u.username, u.email, u.first_name, u.last_name, u.user_id,
        CASE WHEN b.ban_id IS NOT NULL AND b.is_active = 1 THEN 1 ELSE 0 END AS is_banned,
        COALESCE(b.reason, 'Not banned') as ban_reason
        FROM users u
        LEFT JOIN bans b ON u.user_id = b.user_id AND b.is_active = 1
        WHERE u.username = %s
    """, (username,))
    return user_data