app.py

# Name(s): James Hinson and Joon Kang
# OSU Email(s): hinsonj@oregonstate.edu and kangjoo@oregonstate.edu
# Course: CS340 - Introduction to Databases
# Assignment: Project Step 6
# Due Date: 12/9/2024
# Description: Flask app for managing users, workout sessions, and exercises,
#              with CRUD operations and MySQL database integration. Routes include
#              user and session management, along with exercise tracking.

from flask import Flask, render_template, request, redirect, url_for
from config import Config
import mysql.connector
from datetime import datetime

app = Flask(__name__)
app.config.from_object(Config)


# Database connection function
def get_db_connection():
    return mysql.connector.connect(
        host=app.config["DB_HOST"],
        user=app.config["DB_USER"],
        password=app.config["DB_PASSWORD"],
        database=app.config["DB_NAME"],
        port=app.config["DB_PORT"]
    )


# Route to render the dashboard page
@app.route('/')
def dashboard():
    return render_template('index.html')


# Route to render the users management page
@app.route('/users')
def manage_users():
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute("SELECT user_id, username, contact FROM Users")
    users = cursor.fetchall()
    cursor.close()
    db.close()
    return render_template('users.html', users=users)


# Route to add a new user
@app.route('/users/add', methods=['POST'])
def add_user():
    username = request.form['username']
    contact = request.form['contact']
    
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("INSERT INTO Users (username, contact) VALUES (%s, %s)", (username, contact))
    db.commit()
    cursor.close()
    db.close()
    
    return redirect(url_for('manage_users'))


# Route to delete a user by ID
@app.route('/users/delete/<int:user_id>', methods=['POST'])
def delete_user(user_id):
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("DELETE FROM Users WHERE user_id = %s", (user_id,))
    db.commit()
    cursor.close()
    db.close()
    return redirect(url_for('manage_users'))


# Route to edit user details
@app.route('/users/edit/<int:user_id>', methods=['GET', 'POST'])
def edit_user(user_id):
    db = get_db_connection()

    if request.method == 'POST':
        # Process form submission to update user
        username = request.form['username']
        contact = request.form['contact']
        cursor = db.cursor()
        cursor.execute("UPDATE Users SET username = %s, contact = %s WHERE user_id = %s", (username, contact, user_id))
        db.commit()
        cursor.close()
        db.close()
        return redirect(url_for('manage_users'))
    
    cursor = db.cursor(dictionary=True)
    cursor.execute("SELECT user_id, username, contact FROM Users WHERE user_id = %s", (user_id,))
    user = cursor.fetchone()
    cursor.close()
    db.close()
    return render_template('edit_user.html', user=user)


# Route to display workout sessions
@app.route('/workout-sessions', methods=['GET'])
def manage_workout_sessions():
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute("""
        SELECT workout_session_id, user_id, workout_date, duration_minutes, notes
        FROM WorkoutSessions
        ORDER BY workout_date DESC
    """)
    workout_sessions = cursor.fetchall()
    cursor.close()
    db.close()
    return render_template('workout-sessions.html', workout_sessions=workout_sessions)


# Route to add a new workout session
@app.route('/workout-sessions/add', methods=['POST'])
def add_workout_session():
    user_id = request.form['user_id']
    workout_date = request.form['workout_date']
    duration_minutes = request.form['duration_minutes']
    notes = request.form['notes']

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("""
        INSERT INTO WorkoutSessions (user_id, workout_date, duration_minutes, notes)
        VALUES (%s, %s, %s, %s)
    """, (user_id, workout_date, duration_minutes, notes))
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_workout_sessions'))


# Route to edit an existing workout session
@app.route('/edit_workout_session/<int:workout_session_id>', methods=['GET', 'POST'])
def edit_workout_session(workout_session_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    if request.method == 'POST':
        user_id = request.form['user_id']
        workout_date = request.form['workout_date']
        duration_minutes = request.form['duration_minutes']
        notes = request.form['notes']

        cursor.execute("""
            UPDATE WorkoutSessions
            SET user_id = %s, workout_date = %s, duration_minutes = %s, notes = %s
            WHERE workout_session_id = %s
        """, (user_id, workout_date, duration_minutes, notes, workout_session_id))
        db.commit()
        cursor.close()
        db.close()

        return redirect(url_for('manage_workout_sessions'))

    cursor.execute("SELECT * FROM WorkoutSessions WHERE workout_session_id = %s", (workout_session_id,))
    session = cursor.fetchone()
    if session:
        session['workout_date'] = session['workout_date'].strftime('%Y-%m-%dT%H:%M')
    cursor.close()
    db.close()

    return render_template('edit_workout-sessions.html', session=session)

# Route to delete a workout session
@app.route('/workout-sessions/delete/<int:workout_session_id>', methods=['POST'])
def delete_workout_session(workout_session_id):
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("DELETE FROM WorkoutSessions WHERE workout_session_id = %s", (workout_session_id,))
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_workout_sessions'))


# Route to display session exercises
@app.route('/session-exercises', methods=['GET'])
def manage_session_exercises():
    """Display all session exercises."""
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute("""
        SELECT session_exercise_id, workout_session_id, exercise_id, sets, reps, weight
        FROM SessionExercises
        ORDER BY session_exercise_id
    """)
    session_exercises = cursor.fetchall()
    cursor.close()
    db.close()
    return render_template('session-exercises.html', session_exercises=session_exercises)


# Route to add session exercises
@app.route('/session-exercises/add', methods=['POST'])
def add_session_exercise():
    """Add a new session exercise."""
    workout_session_id = request.form['workout_session_id']
    exercise_id = request.form['exercise_id']
    sets = request.form['sets']
    reps = request.form['reps']
    weight = request.form['weight']

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("""
        INSERT INTO SessionExercises (workout_session_id, exercise_id, sets, reps, weight)
        VALUES (%s, %s, %s, %s, %s)
    """, (workout_session_id, exercise_id, sets, reps, weight))
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_session_exercises'))


# Route to edit session exercises
@app.route('/session-exercises/edit/<int:session_exercise_id>', methods=['GET', 'POST'])
def edit_session_exercise(session_exercise_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    if request.method == 'POST':
        # Handle form submission for updating session exercise
        workout_session_id = request.form['workout_session_id']
        exercise_id = request.form['exercise_id']
        sets = request.form['sets']
        reps = request.form['reps']
        weight = request.form['weight']

        # Update the record in the database
        query = """
            UPDATE SessionExercises
            SET workout_session_id = %s, exercise_id = %s, sets = %s, reps = %s, weight = %s
            WHERE session_exercise_id = %s
        """
        cursor.execute(query, (workout_session_id, exercise_id, sets, reps, weight, session_exercise_id))
        db.commit()
        cursor.close()
        db.close()

        return redirect(url_for('manage_session_exercises'))

    else:
        # Fetch the session exercise details for the edit form
        query = "SELECT * FROM SessionExercises WHERE session_exercise_id = %s"
        cursor.execute(query, (session_exercise_id,))
        session_exercise = cursor.fetchone()
        cursor.close()
        db.close()

        if not session_exercise:
            return "Session Exercise not found", 404

        return render_template('edit_session-exercises.html', session_exercise=session_exercise)


# Route to delete session exercises
@app.route('/session-exercises/delete/<int:session_exercise_id>', methods=['POST'])
def delete_session_exercise(session_exercise_id):
    """Delete a session exercise."""
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("DELETE FROM SessionExercises WHERE session_exercise_id = %s", (session_exercise_id,))
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_session_exercises'))


# Route to display exercises
@app.route('/exercises', methods=['GET'])
def manage_exercises():
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute("SELECT exercise_id, name, description, category FROM ListExercises")
    exercises = cursor.fetchall()
    cursor.close()
    db.close()
    return render_template('exercises.html', exercises=exercises)


# Route to add exercises
@app.route('/exercises/add', methods=['POST'])
def add_exercise():
    exercise_name = request.form['exercise_name']
    exercise_description = request.form.get('exercise_description', '')  # Optional
    exercise_category = request.form['exercise_category']

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO ListExercises (name, description, category) VALUES (%s, %s, %s)",
        (exercise_name, exercise_description, exercise_category)
    )
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_exercises'))


# Route to edit exercises
@app.route('/exercises/edit/<int:exercise_id>', methods=['GET', 'POST'])
def edit_exercise(exercise_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    if request.method == 'POST':
        exercise_name = request.form['exercise_name']
        exercise_description = request.form.get('exercise_description', '')
        exercise_category = request.form['exercise_category']

        query = """
            UPDATE ListExercises
            SET name = %s, description = %s, category = %s
            WHERE exercise_id = %s
        """
        cursor.execute(query, (exercise_name, exercise_description, exercise_category, exercise_id))
        db.commit()
        cursor.close()
        db.close()

        return redirect(url_for('manage_exercises'))

    else:
        query = "SELECT * FROM ListExercises WHERE exercise_id = %s"
        cursor.execute(query, (exercise_id,))
        exercise = cursor.fetchone()
        cursor.close()
        db.close()

        if not exercise:
            return "Exercise not found", 404

        return render_template('edit_exercises.html', exercise=exercise)

# Route to delete exercises
@app.route('/exercises/delete/<int:exercise_id>', methods=['POST'])
def delete_exercise(exercise_id):
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("DELETE FROM ListExercises WHERE exercise_id = %s", (exercise_id,))
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_exercises'))


# Route to display the metrics page
@app.route('/metrics', methods=['GET'])
def manage_metrics():
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute("""
        SELECT metric_id, user_id, metric_type, value, date_recorded
        FROM Metrics
        ORDER BY date_recorded DESC
    """)
    metrics = cursor.fetchall()
    cursor.close()
    db.close()
    return render_template('metrics.html', metrics=metrics)


# Route to log a new metric
@app.route('/metrics/add', methods=['POST'])
def add_metric():
    user_id = request.form['metric_user_id']
    metric_type = request.form['metric_type']
    metric_value = request.form['metric_value']
    metric_date_recorded = request.form['metric_date_recorded']

    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("""
        INSERT INTO Metrics (user_id, metric_type, value, date_recorded)
        VALUES (%s, %s, %s, %s)
    """, (user_id, metric_type, metric_value, metric_date_recorded))
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_metrics'))


# Route to edit an existing metric
@app.route('/metrics/edit/<int:metric_id>', methods=['GET', 'POST'])
def edit_metric(metric_id):
    db = get_db_connection()  
    cursor = db.cursor(dictionary=True)

    if request.method == 'POST':
        # Get form data
        metric_type = request.form['metric_type']
        value = request.form['value']
        
        try:
            value = float(value)  # Ensure value is a valid number
        except ValueError:
            return "Invalid value. Must be a number.", 400

        # Update query for Metrics table
        query = """
            UPDATE Metrics
            SET metric_type = %s, value = %s
            WHERE metric_id = %s
        """
        cursor.execute(query, (metric_type, value, metric_id))
        db.commit()
        cursor.close()
        db.close()

        # Redirect to metrics management page
        return redirect(url_for('manage_metrics'))

    else:
        # Fetch the existing metric data to populate the form
        query = "SELECT metric_id, metric_type, value FROM Metrics WHERE metric_id = %s"
        cursor.execute(query, (metric_id,))
        metric = cursor.fetchone()
        cursor.close()
        db.close()

        if not metric:
            return "Metric not found", 404

        return render_template('edit_metrics.html', metric=metric)

# Route to delete a metric
@app.route('/metrics/delete/<int:metric_id>', methods=['POST'])
def delete_metric(metric_id):
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("DELETE FROM Metrics WHERE metric_id = %s", (metric_id,))
    db.commit()
    cursor.close()
    db.close()

    return redirect(url_for('manage_metrics'))

# Search for users
@app.route('/users/search', methods=['GET'])
def search_users():
    search_query = request.args.get('q', '')

    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    try:
        cursor.execute("SELECT user_id, username, contact FROM Users WHERE username LIKE %s", (f"%{search_query}%",))
        users = cursor.fetchall()
    except Exception as e:
        print(f"Error searching users: {e}")
        return "An error occurred while searching users.", 500
    finally:
        cursor.close()
        db.close()

    return render_template('users.html', users=users)

# Search for exercises
@app.route('/exercises/search', methods=['GET'])
def search_exercises():
    search_query = request.args.get('q', '')  # Retrieve the search query

    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    try:
        # Query to search exercises by name using a LIKE clause
        cursor.execute("SELECT exercise_id, name, description, category FROM ListExercises WHERE name LIKE %s", (f"%{search_query}%",))
        exercises = cursor.fetchall()
    except Exception as e:
        print(f"Error searching exercises: {e}")
        return "An error occurred while searching exercises.", 500
    finally:
        cursor.close()
        db.close()

    # Render the exercises.html template with the filtered exercises
    return render_template('exercises.html', exercises=exercises)

# Search for workout sessions
@app.route('/workout_sessions/search', methods=['GET'])
def search_workout_sessions():
    search_query = request.args.get('q', '').strip()  # Retrieve the search query

    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    try:
        if search_query.isdigit():  # Ensure query is numeric
            # Search by User ID
            cursor.execute("""
                SELECT * 
                FROM WorkoutSessions 
                WHERE user_id = %s
            """, (int(search_query),))
            workout_sessions = cursor.fetchall()
        else:
            # If query is not numeric, return an empty list
            workout_sessions = []
    except Exception as e:
        print(f"Error searching workout sessions: {e}")
        return "An error occurred while searching workout sessions.", 500
    finally:
        cursor.close()
        db.close()

    return render_template('workout-sessions.html', workout_sessions=workout_sessions)

# Search for metrics
@app.route('/metrics/search', methods=['GET'])
def search_metrics():
    search_query = request.args.get('q', '').strip()  # Retrieve the search query

    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    try:
        if search_query.isdigit():  # If query is numeric, search by metric ID
            cursor.execute("""
                SELECT * 
                FROM Metrics 
                WHERE metric_id = %s
            """, (int(search_query),))
        else:  # Otherwise, search by metric type
            cursor.execute("""
                SELECT * 
                FROM Metrics 
                WHERE metric_type LIKE %s
            """, (f"%{search_query}%",))

        metrics = cursor.fetchall()
    except Exception as e:
        print(f"Error searching metrics: {e}")
        return "An error occurred while searching metrics.", 500
    finally:
        cursor.close()
        db.close()

    return render_template('metrics.html', metrics=metrics, search_query=search_query)

@app.route('/session_exercises/search', methods=['GET'])
def search_session_exercises():
    search_query = request.args.get('q', '').strip()  # Retrieve the search query

    db = get_db_connection()
    cursor = db.cursor(dictionary=True)

    try:
        if search_query.isdigit():  # If query is numeric, search by session ID
            cursor.execute("""
                SELECT * 
                FROM SessionExercises 
                WHERE session_exercise_id = %s
            """, (int(search_query),))
        else:  # Otherwise, search by exercise name
            cursor.execute("""
                SELECT * 
                FROM SessionExercises 
                WHERE exercise_id LIKE %s
            """, (f"%{search_query}%",))

        session_exercises = cursor.fetchall()
    except Exception as e:
        print(f"Error searching session exercises: {e}")
        return "An error occurred while searching session exercises.", 500
    finally:
        cursor.close()
        db.close()

    return render_template('session-exercises.html', session_exercises=session_exercises, search_query=search_query)


@app.template_filter('datetime_to_local')
def datetime_to_local(value):
    """Convert a datetime object to 'YYYY-MM-DDTHH:MM' format for <input type="datetime-local">"""
    if isinstance(value, datetime):
        return value.strftime('%Y-%m-%dT%H:%M')
    return value


if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=42187)