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

# --- Database Helper Functions ---
def get_db_connection():
    try:
        return mysql.connector.connect(
            user=user, password=password, host=host, port=port, database=database
        )
    except mysql.connector.Error as err:
        st.error(f"Database Error: {err}")
        return None

def load_template(workout_type):
    conn = get_db_connection()
    if not conn: return pd.DataFrame()
    
    cursor = conn.cursor()
    # Map selection to table name safely
    table_map = {'chest': 'chest', 'back': 'back', 'legs': 'legs'}
    table = table_map.get(workout_type, 'chest')
    
    query = f"SELECT * FROM `{table}`"
    cursor.execute(query)
    data = cursor.fetchall()
    columns = cursor.column_names
    cursor.close()
    conn.close()
    
    df = pd.DataFrame(data, columns=columns)
    df['date'] = date.today()
    return df

def save_workout(df):
    conn = get_db_connection()
    if not conn: return
    
    cursor = conn.cursor()
    # Note: Target table uses 'inputdate'
    query = ("INSERT INTO `workouts` "
             "(`inputdate`, `group`, `exercise`, `set`, `weight`, `reps`, `notes`) "
             "VALUES (%s, %s, %s, %s, %s, %s, %s)")
    
    for _, row in df.iterrows():
        cursor.execute(query, (
            row['date'], row['group'], row['exercise'], 
            row['set'], row['weight'], row['reps'], row['notes']
        ))
        
    conn.commit()
    cursor.close()
    conn.close()
    st.success("Workout saved successfully!")

# --- Main Application ---
st.title("Workout Logger")

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

if 'current_workout' not in st.session_state or st.session_state.current_workout != selected_workout:
    st.session_state.current_workout = selected_workout
    st.session_state.workout_df = load_template(selected_workout)
    st.session_state.current_index = 0

df = st.session_state.workout_df

if not df.empty:
    # Navigation Dropdown
    step_labels = [f"{i+1}. {row['exercise']} (Set {row['set']})" for i, row in df.iterrows()]
    selected_step = st.selectbox("Jump to Exercise", options=step_labels, index=st.session_state.current_index)
    st.session_state.current_index = step_labels.index(selected_step)

    # Current Row Data
    idx = st.session_state.current_index
    row = df.iloc[idx]

    st.divider()
    
    # Input Form - Using dynamic key to ensure fresh widgets for each row
    with st.form(key=f"row_form_{idx}"):
        st.subheader(f"{row['exercise']}")
        st.caption(f"Set: {row['set']} | Group: {row['group']}")
        
        c1, c2 = st.columns(2)
        new_weight = c1.number_input("Weight", value=float(row['weight']), step=2.5)
        new_reps = c2.number_input("Reps", value=int(row['reps']), step=1)
        new_notes = st.text_input("Notes", value=str(row['notes']) if row['notes'] else "")
        
        if st.form_submit_button("Save & Next"):
            st.session_state.workout_df.at[idx, 'weight'] = new_weight
            st.session_state.workout_df.at[idx, 'reps'] = new_reps
            st.session_state.workout_df.at[idx, 'notes'] = new_notes
            
            if idx < len(df) - 1:
                st.session_state.current_index += 1
                st.rerun()

    st.divider()
    st.dataframe(st.session_state.workout_df)
    if st.button("Commit Workout to Database"):
        save_workout(st.session_state.workout_df)