workouts/views/workouts_form.py

import streamlit as st
import mysql.connector
from mysql.connector import errorcode
import pandas as pd
from datetime import datetime, date
from dotenv import load_dotenv
import os

load_dotenv()
user = os.getenv('user')
password = os.getenv('password')
host = os.getenv('host')
database = os.getenv('database')
port = os.getenv('port')

workout_table = ['chest','back','legs']
selected_workout = st.selectbox('Select a Workout',options=workout_table)

try:
    conn = mysql.connector.connect(user=user,password=password, host=host,port=port,database=database)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
    
else:
    cursor = conn.cursor()
    if selected_workout == 'chest':
        query = ("SELECT * FROM `chest`")
    elif selected_workout == 'back':
        query = ("SELECT * FROM `back`")
    else:
        query = ("SELECT * FROM `legs`")
    cursor.execute(query)
    data = cursor.fetchall()
    conn.commit()
    cursor.close()
    conn.close()

today=date.today()
df = pd.DataFrame(data,columns=cursor.column_names)
df['date']=today

# Initialize session state to keep track of the current row index
if 'current_row' not in st.session_state:
    st.session_state.current_row = 0

if 'edited_df' not in st.session_state:
    st.session_state.edited_df = df.copy()

def display_row(row):
    st.text_input("Date", value=row['date'], key='date')
    st.text_input("Group",value=row['group'], key='group')
    st.text_input("Exercise",value=row['exercise'], key='exercise')
    st.number_input("Set",value=row['set'], key='set',format="%i")
    st.number_input("Weight",value=row['weight'], key='weight',format="%0.1f",step=0.5)    
    st.number_input("Reps",value=row['reps'], key='reps',format="%i")
    st.text_input("Notes",value=row['notes'], key='notes')

def save_edited_row(index):
    st.session_state.edited_df.at[index,'group'] = st.session_state.group
    st.session_state.edited_df.at[index,'exercise'] = st.session_state.exercise
    st.session_state.edited_df.at[index,'set'] = st.session_state.set
    st.session_state.edited_df.at[index,'weight'] = st.session_state.weight
    st.session_state.edited_df.at[index,'reps'] = st.session_state.reps
    st.session_state.edited_df.at[index,'notes'] = st.session_state.notes

# Display the current row of data
if st.session_state.current_row < len(df):
    row_data = df.iloc[st.session_state.current_row]
    display_row(row_data)
else:
    st.write("No more rows to display")

# Button to clear form and move to the next row
if st.button('Save and Next'):
    if st.session_state.current_row < len(df):
        save_edited_row(st.session_state.current_row)
        st.session_state.current_row += 1
    st.rerun()  # Rerun the app to display the next row

st.write("")

# Button to save all changes back to the dataframe
if st.button('Save All Changes'):
    df.update(st.session_state.edited_df)
    st.write("All changes saved!")
    st.write(df)
    conn = mysql.connector.connect(user=user,password=password, host=host,port=port,database=database)
    cursor = conn.cursor()
    for index, row in df.iterrows():
        query = ("INSERT INTO `workouts`"
                "(`inputdate`,`group`,`exercise`,`set`,`weight`,`reps`,`notes`) " 
                "VALUES (%s,%s,%s,%s,%s,%s,%s)")
        cursor.execute(query,(row.iloc[1],row.iloc[2],row.iloc[3],row.iloc[4],row.iloc[5],row.iloc[6],row.iloc[7]))
    conn.commit()
    cursor.close()
    conn.close()