app.py

from flask import Flask, render_template, request, redirect, url_for, session, flash , jsonify
import mysql.connector
import os
import google.generativeai as genai
from datetime import date, timedelta
from werkzeug.utils import secure_filename
import math
import json 
import re 
import datetime 

# Load environment variables from a .env file
load_dotenv()

app = Flask(__name__)

# --- CONFIGURATION ---
# Use an environment variable for the secret key, or a fallback for development
app.secret_key = os.getenv('FLASK_SECRET_KEY', 'change_this_key_in_production')
app.config['UPLOAD_FOLDER'] = 'static/uploads'

# --- AI CONFIGURATION (Google Gemini) ---
# Set your API Key in the .env file as GOOGLE_API_KEY
GOOGLE_API_KEY = os.getenv('GOOGLE_API_KEY') 

try:
    if GOOGLE_API_KEY:
        genai.configure(api_key=GOOGLE_API_KEY)
        model = genai.GenerativeModel('gemini-2.5-flash')
        AI_AVAILABLE = True
    else:
        print("AI Key not found. Please set GOOGLE_API_KEY in your .env file.")
        AI_AVAILABLE = False
except Exception as e:
    print(f"Failed to configure AI: {e}")
    AI_AVAILABLE = False

# --- DATABASE CONNECTION ---
def get_db_connection():
    """
    Connects to the MySQL database using credentials from environment variables.
    Make sure to define DB_HOST, DB_USER, DB_PASS, and DB_NAME in your .env file.
    """
    return mysql.connector.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        user=os.getenv('DB_USER', 'root'),
        password=os.getenv('DB_PASS'),
        database=os.getenv('DB_NAME', 'gymai')
    )

def calculate_target_calories(weight, height, goal, gender):
    try:
        bmr = (10 * float(weight)) + (6.25 * float(height)) - (5 * 25) + (5 if gender=='MALE' else -161)
        if goal == 'LOSE_WEIGHT': return int(bmr * 1.2 - 500)
        elif goal == 'GAIN_WEIGHT': return int(bmr * 1.2 + 500)
        else: return int(bmr * 1.2)
    except: return 2000 

def calculate_goal_progress(initial_w, current_w, target_w, goal_type):
    if not initial_w or not current_w or not target_w: return 0
    initial_w = float(initial_w); current_w = float(current_w); target_w = float(target_w)
    total_distance = math.fabs(initial_w - target_w)
    distance_covered = math.fabs(initial_w - current_w)
    if total_distance <= 0: return 0
    progress = (distance_covered / total_distance) * 100
    if (goal_type == 'LOSE_WEIGHT' and current_w > initial_w) or (goal_type == 'GAIN_WEIGHT' and current_w < initial_w): return 0
    return min(100, int(progress))

# =========================================
# HOME, REGISTER, LOGIN, LOGOUT ROUTES
# =========================================

@app.route('/')
def home():
    if 'user_id' in session: return redirect(url_for('dashboard'))
    return render_template('landing.html')

@app.route('/send_contact_ajax', methods=['POST'])
def send_contact_ajax():

    try:
        data = request.json
        if data is None:
            print("AJAX ERROR: Request JSON is empty or invalid.")
            return jsonify({'status': 'error', 'message': 'Invalid data format submitted.'}), 400
            
        name = data.get('name')
        email = data.get('email')
        message = data.get('message')
        
    except Exception as e:
        print(f"AJAX ERROR: Failed to parse JSON data: {e}")
        return jsonify({'status': 'error', 'message': 'A structural error occurred during data processing.'}), 400

    if not name or not email or not message:
        return jsonify({'status': 'error', 'message': 'Please fill out all required fields.'}), 400

    conn = get_db_connection()
    if conn is None:
        print("DB ERROR: Failed to establish database connection.")
        return jsonify({'status': 'error', 'message': 'Service is temporarily unavailable (DB Error).'}), 503

    cursor = conn.cursor()
    
    try:
        sql = "INSERT INTO contact_messages (name, email, message) VALUES (%s, %s, %s)"
        cursor.execute(sql, (name, email, message))
        conn.commit()
        return jsonify({
            'status': 'success', 
            'message': 'Thank you! Your message has been received successfully.'
        }), 200
        
    except Exception as e:
        conn.rollback()
        print(f"DB EXECUTION ERROR: {e}")
        return jsonify({'status': 'error', 'message': 'A server error occurred while saving the message.'}), 500
    
    finally:
        conn.close()


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE email = %s AND password_hash = SHA2(%s, 256) AND is_active = TRUE", (request.form['email'], request.form['password']))
        user = cursor.fetchone(); conn.close()
        if user:
            session['user_id'] = user['user_id']; session['role'] = user['role']; session['email'] = user['email']
            # 🛑 التوجيه بناءً على الدور (Role)
            if user['role'] == 'ADMIN':
                return redirect(url_for('admin_dashboard'))
            elif user['role'] == 'TRAINER':
                return redirect(url_for('trainer_dashboard'))
            else:
                return redirect(url_for('dashboard')) # MEMBER
        flash('Invalid credentials!')
    return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        email = request.form['email']
        password = request.form['password']
    
        password_regex = r"^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)[A-Za-z\d]{8,}$"
        
        if not re.match(password_regex, password):
            flash('Password must be at least 8 characters long and contain: one uppercase letter, one lowercase letter, and one number.', 'danger')
            return render_template('register.html')
        
        conn = get_db_connection()
        cursor = conn.cursor()
        try:
            phone = request.form['phone']
            dob = request.form['date_of_birth']
            weight_input = request.form['weight'] 
 
            cursor.execute("INSERT INTO users (email, password_hash, role, is_active) VALUES (%s, SHA2(%s, 256), 'MEMBER', TRUE)", (email, password))
            user_id = cursor.lastrowid

            default_target = float(weight_input) + (10 if request.form['goal'] == 'GAIN_WEIGHT' else -10 if request.form['goal'] == 'LOSE_WEIGHT' else 0)

            cursor.execute("""
                INSERT INTO profiles (
                    user_id, full_name, gender, goal_type, subscription_plan, 
                    height_cm, target_weight_kg, 
                    phone, date_of_birth, initial_weight_kg
                ) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                user_id, 
                request.form['full_name'], 
                request.form['gender'], 
                request.form['goal'], 
                request.form['plan'], 
                request.form['height'], 
                default_target, 
                phone, 
                dob, 
                weight_input 
            ))

            cursor.execute("INSERT INTO user_progress (user_id, entry_date, weight_kg, calories_intake) VALUES (%s, CURRENT_DATE, %s, 0)", (user_id, weight_input))
            
            conn.commit()
            flash('Account created! Login now.', 'success')
            return redirect(url_for('login'))
            
        except Exception as e:
            conn.rollback()
            flash(str(e), 'danger')
        finally:
            conn.close()
            
    return render_template('register.html')

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('home'))

# =========================================
# MEMBER ROUTES
# =========================================

@app.route('/mark_exercise_done/<int:exercise_id>', methods=['POST'])
def mark_exercise_done(exercise_id):
    if 'user_id' not in session: return redirect(url_for('login'))
    
    conn = get_db_connection(); cursor = conn.cursor()

    try:
        cursor.execute("UPDATE workout_exercises SET status = 'DONE' WHERE id = %s", (exercise_id,))
        conn.commit()
        flash('Exercise marked as Done.', 'success')
    except Exception as e:
        flash(f'Error logging session: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('dashboard'))

@app.route('/dashboard')
def dashboard():
    if 'user_id' not in session: return redirect(url_for('login'))
    if session['role'] != 'MEMBER': return redirect(url_for('admin_dashboard' if session['role'] == 'ADMIN' else 'trainer_dashboard'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True); user_id = session['user_id']

    cursor.execute("SELECT full_name, profile_pic, height_cm, goal_type, gender, initial_weight_kg, target_weight_kg, subscription_plan, requested_trainer_id FROM profiles WHERE user_id = %s", (user_id,))
    profile = cursor.fetchone()
    cursor.execute("SELECT bmi, weight_kg FROM v_user_progress_bmi WHERE user_id = %s ORDER BY entry_date DESC LIMIT 1", (user_id,)); stats = cursor.fetchone()
    
    bmi = stats['bmi'] if stats else "--"; weight = stats['weight_kg'] if stats else 0
    target_w = profile['target_weight_kg'] if profile['target_weight_kg'] else 0
    
    goal_percent = calculate_goal_progress(profile['initial_weight_kg'], weight, target_w, profile['goal_type'])
    target_calories = calculate_target_calories(weight, profile['height_cm'], profile['goal_type'], profile['gender'])

    cursor.execute("""
        SELECT p.full_name, t.specialization, p.profile_pic 
        FROM trainer_members tm
        JOIN profiles p ON tm.trainer_id = p.user_id
        JOIN trainers t ON tm.trainer_id = t.trainer_id
        WHERE tm.member_id = %s
    """, (user_id,))
    my_trainer = cursor.fetchone()

    cursor.execute("SELECT t.trainer_id, p.full_name, t.specialization, t.years_experience FROM trainers t JOIN profiles p ON t.trainer_id = p.user_id")
    all_trainers = cursor.fetchall()

    requested_trainer_name = None
    if profile['requested_trainer_id']:
        cursor.execute("SELECT full_name FROM profiles WHERE user_id = %s", (profile['requested_trainer_id'],))
        res = cursor.fetchone()
        if res: requested_trainer_name = res['full_name']

    import datetime; today_name = datetime.datetime.now().strftime("%A")
    query = """
        SELECT we.day_name, e.name, we.sets_count, we.reps_target, w.source, we.id, we.status
        FROM workout_exercises we 
        JOIN workouts w ON we.workout_id = w.workout_id 
        JOIN exercises e ON we.exercise_id = e.exercise_id 
        WHERE w.member_id = %s 
          AND we.day_name = %s
          AND w.end_date >= CURRENT_DATE
    """
    cursor.execute(query, (user_id, today_name))
    final_workouts = cursor.fetchall()
 
    diet_query = """
        SELECT dm.day_name, dm.meal_type, dm.description, dm.calories
        FROM diet_meals dm 
        JOIN diet_plans dp ON dm.plan_id = dp.plan_id 
        WHERE dp.member_id = %s 
          AND dm.day_name = %s
        ORDER BY FIELD(dm.meal_type, 'Breakfast', 'Lunch', 'Snack', 'Dinner')
    """
    cursor.execute(diet_query, (user_id, today_name))
    final_diet_meals = cursor.fetchall() 
    
    cursor.execute("SELECT * FROM notifications WHERE user_id = %s AND is_read = FALSE ORDER BY created_at DESC", (user_id,)); notifications = cursor.fetchall(); conn.close()
    
    return render_template('dashboard.html', 
                            user_name=profile['full_name'], profile_pic=profile['profile_pic'], bmi_value=bmi, weight_value=weight, calories_value=target_calories, 
                            workouts=final_workouts, 
                            meals=final_diet_meals, 
                            notifications=notifications, goal_percent=goal_percent, profile=profile, target_w=target_w,
                            my_trainer=my_trainer, all_trainers=all_trainers, requested_trainer_name=requested_trainer_name)
    
@app.route('/update_goal', methods=['POST'])
def update_goal():
    if 'user_id' not in session: return redirect(url_for('login'))
    
    target_weight = request.form['target_weight']
    user_id = session['user_id']
    
    conn = get_db_connection(); cursor = conn.cursor()
    try:
        cursor.execute("UPDATE profiles SET target_weight_kg = %s WHERE user_id = %s", 
                       (target_weight, user_id))
        conn.commit()
        flash('Goal weight updated successfully!', 'success')
    except Exception as e:
        flash(f'Error updating goal: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('dashboard'))

@app.route('/request_trainer', methods=['POST'])
def request_trainer():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']
    trainer_id = request.form.get('trainer_id')
    
    conn = get_db_connection(); cursor = conn.cursor()
    try:
        cursor.execute("UPDATE profiles SET requested_trainer_id = %s WHERE user_id = %s", (trainer_id, user_id))
        
        conn.commit()
        flash('Request sent to Admin! Please wait for approval.', 'success')
    except Exception as e:
        flash(f'Error: {e}', 'danger')
    finally:
        conn.close()
    return redirect(url_for('dashboard'))

@app.route('/admin/assign_trainer', methods=['POST'])
def assign_trainer():
    if session.get('role') != 'ADMIN': return redirect(url_for('login'))
    
    member_id = request.form.get('member_id')
    trainer_id = request.form.get('trainer_id')
    
    if member_id and trainer_id:
        conn = get_db_connection(); cursor = conn.cursor()
        try:
            cursor.execute("DELETE FROM trainer_members WHERE member_id = %s", (member_id,))
            cursor.execute("INSERT INTO trainer_members (trainer_id, member_id) VALUES (%s, %s)", (trainer_id, member_id))
            cursor.execute("UPDATE profiles SET requested_trainer_id = NULL WHERE user_id = %s", (member_id,))
            cursor.execute("INSERT INTO notifications (user_id, message) VALUES (%s, 'Great news! Your personal trainer has been assigned.')", (member_id,))
            cursor.execute("SELECT full_name FROM profiles WHERE user_id=%s", (member_id,))
            member_name = cursor.fetchone()[0]
            cursor.execute("INSERT INTO notifications (user_id, message) VALUES (%s, %s)", (trainer_id, f"New trainee assigned: {member_name}"))
            
            conn.commit()
            flash('Trainer assigned and notifications sent!', 'success')
        except Exception as e:
            flash(f'Error: {e}', 'danger')
        finally:
            conn.close()
            
    return redirect(url_for('manage_users'))

@app.route('/edit_profile', methods=['GET', 'POST'])
def edit_profile():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']; conn = get_db_connection()
    
    if request.method == 'POST':
        cursor = conn.cursor()
 
        full_name = request.form['full_name']
        phone = request.form['phone']
        dob = request.form['date_of_birth'] 
        height = request.form['height']
        goal = request.form['goal']
        sub_plan = request.form['subscription_plan']
        target_w = request.form.get('target_weight_kg')
        initial_w = request.form.get('initial_weight_kg')

        img_sql = ""; params = [full_name, phone, dob, height, goal, sub_plan, target_w, initial_w]
        if 'profile_pic' in request.files and request.files['profile_pic'].filename:
            f = request.files['profile_pic']; fname = secure_filename(f.filename); f.save(os.path.join(app.config['UPLOAD_FOLDER'], fname))
            img_sql = ", profile_pic=%s"; params.append(fname)
        
        params.append(user_id) 

        cursor.execute(f"""
            UPDATE profiles 
            SET full_name=%s, phone=%s, date_of_birth=%s, height_cm=%s, goal_type=%s, subscription_plan=%s, target_weight_kg=%s, initial_weight_kg=%s {img_sql} 
            WHERE user_id=%s
        """, params)

        w = request.form['weight']
        cursor.execute("INSERT INTO user_progress (user_id, entry_date, weight_kg, calories_intake) VALUES (%s, CURRENT_DATE, %s, 0) ON DUPLICATE KEY UPDATE weight_kg=%s", (user_id, w, w))
        
        conn.commit(); conn.close(); flash('Profile Updated!', 'success')
        return redirect(url_for('dashboard'))
    
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM profiles WHERE user_id = %s", (user_id,))
    profile = cursor.fetchone()
    cursor.execute("SELECT weight_kg FROM v_user_progress_bmi WHERE user_id = %s ORDER BY entry_date DESC LIMIT 1", (user_id,))
    w_row = cursor.fetchone(); weight = w_row['weight_kg'] if w_row else 0
    conn.close()
    return render_template('edit_profile.html', profile=profile, email=session['email'], weight=weight)

@app.route('/delete_custom_exercise/<int:ex_id>', methods=['POST'])
def delete_custom_exercise(ex_id):
    if 'user_id' not in session: return redirect(url_for('login'))
    
    conn = get_db_connection(); cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT w.member_id FROM workout_exercises we
            JOIN workouts w ON we.workout_id = w.workout_id
            WHERE we.id = %s
        """, (ex_id,))
        member_id = cursor.fetchone()[0]

        if member_id == session['user_id']:
            cursor.execute("DELETE FROM workout_exercises WHERE id = %s", (ex_id,))
            conn.commit()
            flash('Exercise removed successfully.', 'success')
        else:
            flash('Unauthorized deletion attempt.', 'danger')
            
    except Exception as e:
        flash(f'Error deleting exercise: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('my_workouts'))

@app.route('/edit_custom_exercise/<int:ex_id>', methods=['GET', 'POST'])
def edit_custom_exercise(ex_id):
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)

    if request.method == 'POST':
        sets = request.form['sets']; reps = request.form['reps']; day = request.form['day']; ex_name = request.form['ex_name']
        cursor.execute("""
            UPDATE workout_exercises SET sets_count=%s, reps_target=%s, day_name=%s
            WHERE id=%s AND workout_id IN (SELECT workout_id FROM workouts WHERE member_id=%s)
        """, (sets, reps, day, ex_id, user_id))
        conn.commit()
        flash('Exercise updated successfully!', 'success')
        conn.close()
        return redirect(url_for('my_workouts'))
        
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT we.*, e.name as exercise_name FROM workout_exercises we
        JOIN exercises e ON we.exercise_id = e.exercise_id
        WHERE we.id = %s AND workout_id IN (SELECT workout_id FROM workouts WHERE member_id=%s)
    """, (ex_id, user_id))
    exercise = cursor.fetchone()
    conn.close()
    
    if not exercise:
        flash('Exercise not found or unauthorized.', 'danger')
        return redirect(url_for('my_workouts'))
        
    return render_template('edit_custom_exercise.html', exercise=exercise)

@app.route('/my_workouts')
def my_workouts():
    if 'user_id' not in session: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    user_id = session['user_id']
    
    query = """
        SELECT we.id, we.day_name, e.name, we.sets_count, we.reps_target, w.source
        FROM workout_exercises we 
        JOIN workouts w ON we.workout_id = w.workout_id 
        JOIN exercises e ON we.exercise_id = e.exercise_id 
        WHERE w.member_id = %s AND w.end_date >= CURRENT_DATE 
        ORDER BY FIELD(we.day_name,'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
    """
    cursor.execute(query, (user_id,))
    all_data = cursor.fetchall()
    conn.close()
    
    combined_list = all_data
    trainer_w, ai_w, custom_w = {}, {}, {}
    
    for row in all_data:
        day = row['day_name']
        
        if row['source'] == 'TRAINER':
            if day not in trainer_w: trainer_w[day] = []
            trainer_w[day].append(row)
        elif row['source'] == 'AI':
            if day not in ai_w: ai_w[day] = []
            ai_w[day].append(row)
        else: 
            if day not in custom_w: custom_w[day] = []
            custom_w[day].append(row)
            
    chat_history = session.get('ai_chat_history', [])
    raw_plan_json = chat_history[-1].get('raw_plan') if chat_history and 'raw_plan' in chat_history[-1] else None

    return render_template('workouts.html', 
                            combined_w=combined_list,
                            trainer_w=trainer_w, 
                            ai_w=ai_w, 
                            custom_w=custom_w,
                            raw_plan_json=raw_plan_json)
    
@app.route('/add_workout', methods=['POST'])
def add_workout():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']; conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    source = request.form.get('source', 'CUSTOM')
    cursor.execute("SELECT workout_id FROM workouts WHERE member_id=%s AND source=%s AND end_date >= CURRENT_DATE", (user_id, source))
    workout = cursor.fetchone()
    if not workout:
        import datetime; end = datetime.date.today() + datetime.timedelta(weeks=52)
        cursor.execute("INSERT INTO workouts (member_id, name, goal, start_date, end_date, source) VALUES (%s, 'My Plan', 'MAINTAIN', CURRENT_DATE, %s, %s)", (user_id, end, source))
        conn.commit(); workout_id = cursor.lastrowid
    else: workout_id = workout['workout_id']
    ex_name = request.form['name']
    cursor.execute("SELECT exercise_id FROM exercises WHERE name=%s", (ex_name,)); ex_row = cursor.fetchone()
    if not ex_row: cursor.execute("INSERT INTO exercises (name, type) VALUES (%s, 'WEIGHTS')", (ex_name,)); conn.commit(); ex_id = cursor.lastrowid
    else: ex_id = ex_row['exercise_id']
    cursor.execute("INSERT INTO workout_exercises (workout_id, exercise_id, day_name, sets_count, reps_target) VALUES (%s, %s, %s, %s, %s)", (workout_id, ex_id, request.form['day'], request.form['sets'], request.form['reps'])); conn.commit(); conn.close()
    return redirect(url_for('my_workouts'))

# =========================================
# AI TRAINER ROUTES
# =========================================

@app.route('/ai_chat', methods=['POST'])
def ai_chat():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_input = request.form.get('user_input')
    if 'ai_chat_history' not in session: session['ai_chat_history'] = []
    
    history = session['ai_chat_history']
    
    if AI_AVAILABLE:
        try:
            conn = get_db_connection(); cursor = conn.cursor(dictionary=True); user_id = session['user_id']
           
            cursor.execute("SELECT p.gender, p.goal_type, p.height_cm, up.weight_kg FROM profiles p LEFT JOIN user_progress up ON p.user_id = up.user_id WHERE p.user_id = %s ORDER BY up.entry_date DESC LIMIT 1", (user_id,)); data = cursor.fetchone(); conn.close()

            system_prompt = f"""
            Act as SmartFit Coach. You are specialized in fitness, workouts, diet, and general health.
            User Profile: Gender: {data['gender']}, Weight: {data['weight_kg']}kg, Height: {data['height_cm']}cm, Primary Goal: {data['goal_type']}.

            INSTRUCTIONS:
            1. STRICTLY refuse any input not related to FITNESS, WORKOUTS, DIET, or HEALTH. If the input is irrelevant, reply EXACTLY with: 'I apologize, my focus is strictly on fitness and training plans.'. (Do not answer the irrelevant question).
            2. If the user asks for a WORKOUT plan, respond ONLY with a valid JSON array matching this structure: [{{'day': 'Sunday', 'exercise_name': 'X', 'sets': 3, 'reps': 12}}].
            3. If the user asks for a DIET plan, respond ONLY with a valid JSON array matching this structure: [{{'day': 'Sunday', 'meal_type': 'Breakfast', 'description': 'Oatmeal with berries', 'calories': 350}}].
            4. Otherwise, reply with normal conversational text related to fitness goals.
            """
   
            response = model.generate_content(f"{system_prompt} User: {user_input}")
            bot_reply = response.text

            import re
            is_workout = 'exercise_name' in bot_reply
            is_diet = 'meal_type' in bot_reply

            json_match = re.search(r'\[.*\]', bot_reply, re.DOTALL)
            
            if json_match and (is_workout or is_diet):
                clean_json = json_match.group(0)
                msg_type = 'diet_plan' if is_diet else 'workout_plan'
                msg_text = "Diet Plan Generated! Click Save." if is_diet else "Workout Plan Generated! Click Save."
                
                history.append({'user': user_input, 'bot': msg_text, 'raw_plan': clean_json, 'type': msg_type})
            else:

                history.append({'user': user_input, 'bot': bot_reply.replace('**', '<b>').replace('* ', '<br>• ')})

        except Exception as e: 
            bot_reply = f"Error: {e}"
            history.append({'user': user_input, 'bot': bot_reply})
            
    else: 
        bot_reply = "AI not connected."
        history.append({'user': user_input, 'bot': bot_reply})

    session['ai_chat_history'] = history
    return redirect(url_for('ai_trainer'))

@app.route('/ai_trainer')
def ai_trainer():
    if 'user_id' not in session: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT subscription_plan, full_name FROM profiles WHERE user_id = %s", (session['user_id'],)); user_info = cursor.fetchone(); conn.close()
    
    plan = user_info['subscription_plan']
    has_access = plan in ['Pro', 'Elite']
    chat_history = session.get('ai_chat_history', [])
    
    last_response_is_plan = False
    plan_type = None
    preview_data = None 

    if chat_history and 'raw_plan' in chat_history[-1]: 
        last_response_is_plan = True
        plan_type = chat_history[-1].get('type')
        import json
        try:
            preview_data = json.loads(chat_history[-1].get('raw_plan'))
        except json.JSONDecodeError:
            preview_data = None
            
    return render_template('ai_trainer.html', 
                            has_access=has_access, 
                            user_name=user_info['full_name'], 
                            chat_history=chat_history, 
                            current_plan=plan, 
                            last_response_is_plan=last_response_is_plan, 
                            plan_type=plan_type, 
                            raw_plan_json=chat_history[-1].get('raw_plan') if last_response_is_plan else '',
                            preview_data=preview_data)
    
@app.route('/clear_chat', methods=['POST'])
def clear_chat(): session.pop('ai_chat_history', None); return redirect(url_for('ai_trainer'))

@app.route('/store')
def store():
    if 'user_id' not in session: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM products WHERE is_active = TRUE"); products = cursor.fetchall(); conn.close()
    return render_template('store.html', products=products)

@app.route('/buy_product/<int:product_id>', methods=['POST'])
def buy_product(product_id):
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']; conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("SELECT price, stock_qty, name FROM products WHERE product_id = %s", (product_id,)); product = cursor.fetchone()
        if product and product['stock_qty'] > 0:
            cursor.execute("INSERT INTO orders (user_id, status) VALUES (%s, 'PAID')", (user_id,)); order_id = cursor.lastrowid
            cursor.execute("INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (%s, %s, 1, %s)", (order_id, product_id, product['price'])); cursor.execute("UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = %s", (product_id,)); conn.commit(); flash(f"Purchased {product['name']}!", 'success')
        else: flash("Out of stock.", 'danger')
    except Exception as e: flash(f'Error: {e}', 'danger')
    conn.close(); return redirect(url_for('store'))

@app.route('/save_ai_diet', methods=['POST'])
def save_ai_diet():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']; ai_diet_json = request.form.get('ai_diet_json')
    try:
        import json
        diet_data = json.loads(ai_diet_json)
        conn = get_db_connection(); cursor = conn.cursor(dictionary=True)

        cursor.execute("DELETE FROM diet_plans WHERE member_id = %s", (user_id,)) 
        cursor.execute("INSERT INTO diet_plans (member_id, name) VALUES (%s, 'AI Diet Plan')", (user_id,))
        plan_id = cursor.lastrowid
        
        for item in diet_data:
            day = item.get('day'); meal_type = item.get('meal_type'); desc = item.get('description'); cals = item.get('calories')

            cursor.execute("""
                INSERT INTO diet_meals (plan_id, day_name, meal_type, description, calories, source) 
                VALUES (%s, %s, %s, %s, %s, 'AI')
            """, (plan_id, day, meal_type, desc, cals))
            
        conn.commit(); flash('AI Diet Plan Saved!', 'success')
        return redirect(url_for('nutrition'))
    except Exception as e: flash(f"Error: {e}", 'danger'); return redirect(url_for('ai_trainer'))
   
@app.route('/save_ai_plan', methods=['POST'])
def save_ai_plan():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']; ai_plan_json = request.form.get('ai_plan_json')
    import json
    try:
        plan_data = json.loads(ai_plan_json)
        conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
 
        cursor.execute("SELECT workout_id FROM workouts WHERE member_id=%s AND source='AI' AND end_date >= CURRENT_DATE", (user_id,))
        workout = cursor.fetchone()
        
        if not workout:
            end = date.today() + timedelta(weeks=52)

            cursor.execute("INSERT INTO workouts (member_id, name, goal, start_date, end_date, source) VALUES (%s, 'AI Plan', 'MAINTAIN', CURRENT_DATE(), %s, 'AI')", (user_id, end))
            conn.commit(); workout_id = cursor.lastrowid
        else: 
            workout_id = workout['workout_id']

            cursor.execute("DELETE FROM workout_exercises WHERE workout_id = %s", (workout_id,))
            conn.commit()
        
        for item in plan_data:
            ex_name = item.get('exercise_name'); day = item.get('day'); 
            sets = item.get('sets') if str(item.get('sets','0')).isdigit() else 1
            reps = item.get('reps') if str(item.get('reps','0')).isdigit() else 1
            notes = f"{item.get('reps')} {item.get('unit','')}"
            
            cursor.execute("SELECT exercise_id FROM exercises WHERE name=%s", (ex_name,))
            ex_row = cursor.fetchone()
            if not ex_row: 
                cursor.execute("INSERT INTO exercises (name, type) VALUES (%s, 'WEIGHTS')", (ex_name,))
                conn.commit(); ex_id = cursor.lastrowid
            else: 
                ex_id = ex_row['exercise_id']
                
            cursor.execute("INSERT INTO workout_exercises (workout_id, exercise_id, day_name, sets_count, reps_target, notes, status) VALUES (%s, %s, %s, %s, %s, %s, 'PLANNED')", (workout_id, ex_id, day, sets, reps, notes))
            
        conn.commit(); flash('AI Workout Plan Saved!', 'success')
        return redirect(url_for('my_workouts'))
    except Exception as e: flash(f"Error: {e}", 'danger'); return redirect(url_for('ai_trainer'))
     
@app.route('/mark_read')
def mark_read():
    if 'user_id' in session: conn = get_db_connection(); cursor = conn.cursor(); cursor.execute("UPDATE notifications SET is_read = TRUE WHERE user_id = %s", (session['user_id'],)); conn.commit(); conn.close()
    return redirect(url_for('dashboard'))

# =========================================
# NUTRITION
# =========================================

@app.route('/nutrition')
def nutrition():
    if 'user_id' not in session: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    
    cursor.execute("SELECT height_cm, goal_type, gender, current_weight_kg FROM profiles WHERE user_id = %s", (session['user_id'],))
    profile = cursor.fetchone()
    suggested_cals = 2000
    if profile and profile['current_weight_kg'] and profile['height_cm']:
        suggested_cals = calculate_target_calories(profile['current_weight_kg'], profile['height_cm'], profile['goal_type'], profile['gender'])

    cursor.execute("SELECT entry_date, weight_kg, calories_intake FROM user_progress WHERE user_id=%s AND (weight_kg IS NOT NULL OR calories_intake IS NOT NULL) ORDER BY entry_date DESC LIMIT 30", (session['user_id'],))
    history_raw = cursor.fetchall()
    
    dates = [h['entry_date'].strftime('%b %d') for h in reversed(history_raw)]
    weights = [float(h['weight_kg']) if h['weight_kg'] else 0 for h in reversed(history_raw)]
    calories = [int(h['calories_intake']) if h['calories_intake'] else 0 for h in reversed(history_raw)]

    cursor.execute("SELECT dm.*, dm.source FROM diet_meals dm JOIN diet_plans dp ON dm.plan_id = dp.plan_id WHERE dp.member_id = %s ORDER BY FIELD(dm.day_name,'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'), FIELD(dm.meal_type, 'Breakfast', 'Lunch', 'Snack', 'Dinner')", (session['user_id'],))
    all_meals = cursor.fetchall()
    conn.close()
    
    import datetime; today_name = datetime.date.today().strftime("%A")
    import json
    
    ai_plan = {}; trainer_plan = {}; custom_plan = {}
    ai_cals = {}; trainer_cals = {}; custom_cals = {}
    todays_meals = [] 
    todays_total_cals = 0
    
    for meal in all_meals:
        d = meal['day_name']; cals = meal['calories'] or 0
        src = meal.get('source', 'CUSTOM') or 'CUSTOM'
    
        if d == today_name:
            todays_meals.append(meal)
            todays_total_cals += cals

        if src == 'AI':
            if d not in ai_plan: ai_plan[d] = []; ai_cals[d] = 0
            ai_plan[d].append(meal); ai_cals[d] += cals
        elif src == 'TRAINER':
            if d not in trainer_plan: trainer_plan[d] = []; trainer_cals[d] = 0
            trainer_plan[d].append(meal); trainer_cals[d] += cals
        else:
            if d not in custom_plan: custom_plan[d] = []; custom_cals[d] = 0
            custom_plan[d].append(meal); custom_cals[d] += cals
            
    return render_template('nutrition.html', 
                           suggested_cals=suggested_cals,
                           today_name=today_name,
                           todays_meals=todays_meals,
                           todays_total_cals=todays_total_cals,
                           history=history_raw,
                           dates=json.dumps(dates), 
                           weights=json.dumps(weights), 
                           calories=json.dumps(calories),
                           ai_plan=ai_plan, ai_cals=ai_cals,
                           trainer_plan=trainer_plan, trainer_cals=trainer_cals,
                           custom_plan=custom_plan, custom_cals=custom_cals)

@app.route('/ask_food_ai', methods=['POST'])
def ask_food_ai():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_question = request.form.get('food_question')
    
    if AI_AVAILABLE:
        try:
            system_prompt = (
                "You are an expert Nutritionist AI. Your knowledge includes common international and Arabic/Saudi dishes. "
                "Your task is to analyze the input: "
                "1. If the input is NOT related to food, calories, or nutrition, "
                "reply EXACTLY with: '⚠️ Food questions only!'. "
                "2. Otherwise, provide the estimated calorie and macro breakdown for a standard serving. "
                "Your response must begin with '🍎' followed by the factual answer (max 20 words). Do not use introductory or conversational phrases."
            )
            
            response = model.generate_content(f"{system_prompt} Input: '{user_question}'")
            answer = response.text.replace('**', '').strip()

            if "Food questions only" in answer or "Sorry" in answer or not answer.strip():
                flash(answer.replace('⚠️', '').strip(), 'warning') 
            else:

                flash(answer, 'info') 
                
        except Exception as e:
            flash("AI is currently unavailable.", 'danger')
    else:
        flash("AI not connected.", 'danger')
        
    return redirect(url_for('nutrition'))

@app.route('/nutrition/log', methods=['POST'])
def log_nutrition():
    if 'user_id' not in session: return redirect(url_for('login'))
    cals = request.form.get('calories'); weight = request.form.get('weight'); conn = get_db_connection(); cursor = conn.cursor()
    try: cursor.execute("INSERT INTO user_progress (user_id, entry_date, weight_kg, calories_intake) VALUES (%s, CURRENT_DATE(), %s, %s) ON DUPLICATE KEY UPDATE weight_kg=%s, calories_intake=%s", (session['user_id'], weight, cals, weight, cals)); conn.commit(); flash('Log updated!', 'success')
    except Exception as e: flash(f"Error: {e}", 'danger')
    conn.close(); return redirect(url_for('nutrition'))
    

@app.route('/edit_custom_meal/<int:meal_id>', methods=['GET', 'POST'])
def edit_custom_meal(meal_id):
    if 'user_id' not in session: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    
    if request.method == 'POST':
        day = request.form['day']
        m_type = request.form['type']
        desc = request.form['desc']
        cals = request.form['cals']

        cursor.execute("""
            UPDATE diet_meals dm
            JOIN diet_plans dp ON dm.plan_id = dp.plan_id
            SET dm.day_name=%s, dm.meal_type=%s, dm.description=%s, dm.calories=%s
            WHERE dm.meal_id=%s AND dp.member_id=%s
        """, (day, m_type, desc, cals, meal_id, session['user_id']))
        
        conn.commit()
        flash('Meal updated successfully!', 'success')
        conn.close()
        return redirect(url_for('nutrition'))

    cursor.execute("""
        SELECT dm.* FROM diet_meals dm
        JOIN diet_plans dp ON dm.plan_id = dp.plan_id
        WHERE dm.meal_id = %s AND dp.member_id = %s
    """, (meal_id, session['user_id']))
    meal = cursor.fetchone()
    conn.close()
    
    if not meal:
        flash('Meal not found or unauthorized.', 'danger')
        return redirect(url_for('nutrition'))
        
    return render_template('edit_custom_meal.html', meal=meal)
    
@app.route('/add_custom_meal', methods=['POST'])
def add_custom_meal():
    if 'user_id' not in session: return redirect(url_for('login'))
    user_id = session['user_id']
    conn = get_db_connection(); cursor = conn.cursor()

    cursor.execute("SELECT plan_id FROM diet_plans WHERE member_id=%s", (user_id,))
    plan_row = cursor.fetchone()
    if not plan_row:
        cursor.execute("INSERT INTO diet_plans (member_id, name) VALUES (%s, 'My Plan')", (user_id,))
        plan_id = cursor.lastrowid
    else:
        plan_id = plan_row[0]
        
    cursor.execute("""
        INSERT INTO diet_meals (plan_id, day_name, meal_type, description, calories, source)
        VALUES (%s, %s, %s, %s, %s, 'CUSTOM')
    """, (plan_id, request.form['day'], request.form['type'], request.form['desc'], request.form['cals']))
    
    conn.commit(); conn.close()
    flash('Meal added successfully!', 'success')
    return redirect(url_for('nutrition'))

@app.route('/delete_meal/<int:meal_id>', methods=['POST'])
def delete_meal(meal_id):
    if 'user_id' not in session: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor()
    cursor.execute("DELETE FROM diet_meals WHERE meal_id=%s AND plan_id IN (SELECT plan_id FROM diet_plans WHERE member_id=%s)", (meal_id, session['user_id']))
    conn.commit(); conn.close()
    flash('Meal deleted.', 'success')
    return redirect(url_for('nutrition'))

# =========================================
# trainer_dashboard 
# =========================================

@app.route('/trainer')
def trainer_dashboard():
    if 'user_id' not in session or session['role'] not in ['TRAINER']:
        flash('should be inter as traner', 'danger')
        return redirect(url_for('login'))
        
    trainer_id = session['user_id']
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    cursor.execute("""
        SELECT p.full_name, t.years_experience 
        FROM profiles p
        JOIN trainers t ON p.user_id = t.trainer_id
        WHERE p.user_id = %s
    """, (trainer_id,))
    trainer_info = cursor.fetchone()
    
    trainer_name = trainer_info['full_name'] if trainer_info else "Trainer"
    experience_years = trainer_info['years_experience'] if trainer_info else 0

    trainees_query = """
        SELECT 
            p.user_id, p.full_name, p.goal_type, 
            (SELECT weight_kg FROM user_progress WHERE user_id = p.user_id ORDER BY entry_date DESC LIMIT 1) AS current_weight_kg,
            (SELECT end_date FROM workouts WHERE member_id = p.user_id ORDER BY start_date DESC LIMIT 1) AS plan_end_date
        FROM trainer_members tm
        JOIN profiles p ON tm.member_id = p.user_id
        WHERE tm.trainer_id = %s
    """
    cursor.execute(trainees_query, (trainer_id,))
    trainees = cursor.fetchall()
    
    trainee_count = len(trainees) if trainees else 0
    
    conn.close()
    
    return render_template('trainer_dashboard.html', 
                           trainer_name=trainer_name,
                           trainee_count=trainee_count,
                           experience_years=experience_years,
                           trainees=trainees)

@app.route('/trainer/add_exercise/<int:member_id>', methods=['POST'])
def trainer_add_exercise(member_id):
    if session.get('role') not in ['TRAINER', 'ADMIN']: return redirect(url_for('login'))
    trainer_id = session['user_id']
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)

    if session['role'] == 'TRAINER':
        cursor.execute("SELECT * FROM trainer_members WHERE trainer_id = %s AND member_id = %s", (trainer_id, member_id))
        if not cursor.fetchone():
            conn.close(); flash('Unauthorized access.', 'danger'); return redirect(url_for('trainer_dashboard'))

    try:
        cursor.execute("SELECT workout_id FROM workouts WHERE member_id=%s AND source='TRAINER' AND end_date >= CURRENT_DATE", (member_id,))
        workout = cursor.fetchone()
        
        if not workout:
            end = date.today() + timedelta(weeks=52)
            cursor.execute("INSERT INTO workouts (member_id, name, goal, start_date, end_date, source) VALUES (%s, 'Trainer Plan', 'MAINTAIN', CURRENT_DATE, %s, 'TRAINER')", (member_id, end))
            conn.commit(); workout_id = cursor.lastrowid
        else:
            workout_id = workout['workout_id']
            
        ex_name = request.form['name']

        cursor.execute("SELECT exercise_id FROM exercises WHERE name=%s", (ex_name,)); ex_row = cursor.fetchone()
        if not ex_row: 
            cursor.execute("INSERT INTO exercises (name, type) VALUES (%s, 'WEIGHTS')", (ex_name,)); conn.commit(); ex_id = cursor.lastrowid
        else: 
            ex_id = ex_row['exercise_id']

        cursor.execute("INSERT INTO workout_exercises (workout_id, exercise_id, day_name, sets_count, reps_target) VALUES (%s, %s, %s, %s, %s)", 
                       (workout_id, ex_id, request.form['day'], request.form['sets'], request.form['reps']))
        
        conn.commit()
        flash('Exercise added to member plan!', 'success')
    except Exception as e:
        conn.rollback()
        flash(f'Error adding exercise: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('trainer_manage_plan', member_id=member_id)) 

@app.route('/trainer/delete_exercise/<int:ex_id>/<int:member_id>', methods=['POST'])
def trainer_delete_exercise(ex_id, member_id):
    if session.get('role') not in ['TRAINER', 'ADMIN']: return redirect(url_for('login'))
    trainer_id = session['user_id']
    conn = get_db_connection(); cursor = conn.cursor()
    
    try:
        cursor.execute("""
            SELECT w.member_id FROM workout_exercises we
            JOIN workouts w ON we.workout_id = w.workout_id
            WHERE we.id = %s AND w.member_id = %s AND w.source = 'TRAINER'
        """, (ex_id, member_id))
        
        if cursor.fetchone():
            cursor.execute("DELETE FROM workout_exercises WHERE id = %s", (ex_id,))
            conn.commit()
            flash('Exercise deleted from plan.', 'success')
        else:
            flash('Exercise not found in member\'s Trainer plan.', 'danger')
            
    except Exception as e:
        flash(f'Error deleting exercise: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('trainer_manage_plan', member_id=member_id)) 

@app.route('/trainer/edit_exercise/<int:ex_id>/<int:member_id>', methods=['GET', 'POST'])
def trainer_edit_exercise(ex_id, member_id):
    if session.get('role') not in ['TRAINER', 'ADMIN']: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    
    cursor.execute("""
        SELECT we.*, e.name as exercise_name 
        FROM workout_exercises we
        JOIN exercises e ON we.exercise_id = e.exercise_id
        JOIN workouts w ON we.workout_id = w.workout_id
        WHERE we.id = %s AND w.member_id = %s AND w.source = 'TRAINER'
    """, (ex_id, member_id))
    exercise = cursor.fetchone()
    
    if not exercise:
        conn.close(); flash('Exercise not found or unauthorized.', 'danger'); return redirect(url_for('trainer_manage_plan', member_id=member_id)) # 🛑 تم التعديل هنا

    if request.method == 'POST':
        sets = request.form['sets']; reps = request.form['reps']; day = request.form['day']
        
        try:
            cursor.execute("UPDATE workout_exercises SET sets_count=%s, reps_target=%s, day_name=%s WHERE id=%s", 
                           (sets, reps, day, ex_id))
            conn.commit()
            flash('Exercise updated successfully!', 'success')
            return redirect(url_for('trainer_manage_plan', member_id=member_id)) 
        except Exception as e:
            flash(f'Error updating exercise: {e}', 'danger')
            conn.rollback()
            
    conn.close()
    return render_template('trainer_edit_exercise.html', exercise=exercise, member_id=member_id)

@app.route('/trainer/add_meal/<int:member_id>', methods=['POST'])
def trainer_add_meal(member_id):
    if session.get('role') not in ['TRAINER', 'ADMIN']: return redirect(url_for('login'))
    
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT plan_id FROM diet_plans WHERE member_id=%s AND name='Trainer Plan'", (member_id,))
        plan_row = cursor.fetchone()
        
        if not plan_row:
            cursor.execute("INSERT INTO diet_plans (member_id, name) VALUES (%s, 'Trainer Plan')", (member_id,))
            conn.commit()
            plan_id = cursor.lastrowid
        else:
            plan_id = plan_row[0]
            
        cursor.execute("""
            INSERT INTO diet_meals (plan_id, day_name, meal_type, description, calories, source)
            VALUES (%s, %s, %s, %s, %s, 'TRAINER')
        """, (plan_id, request.form['day'], request.form['type'], request.form['desc'], request.form['cals']))
        
        conn.commit()
        flash('Meal added successfully!', 'success')
        
    except Exception as e:
        conn.rollback()
        flash(f'Error adding meal: {e}', 'danger')
        print(e) 
    finally:
        conn.close()
        
    return redirect(url_for('trainer_manage_plan', member_id=member_id))

@app.route('/trainer/edit_meal/<int:meal_id>/<int:member_id>', methods=['GET', 'POST'])
def trainer_edit_meal(meal_id, member_id):
    if session.get('role') not in ['TRAINER', 'ADMIN']: return redirect(url_for('login'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT dm.* FROM diet_meals dm
        JOIN diet_plans dp ON dm.plan_id = dp.plan_id
        WHERE dm.meal_id = %s AND dp.member_id = %s
    """, (meal_id, member_id))
    meal = cursor.fetchone()
    
    if not meal:
        conn.close()
        flash('Meal not found.', 'danger')
        return redirect(url_for('trainer_manage_plan', member_id=member_id))
        
    if request.method == 'POST':
        day = request.form['day']
        m_type = request.form['type']
        desc = request.form['desc']
        cals = request.form['cals']
        
        try:
            cursor.execute("""
                UPDATE diet_meals 
                SET day_name=%s, meal_type=%s, description=%s, calories=%s, source='TRAINER' 
                WHERE meal_id=%s
            """, (day, m_type, desc, cals, meal_id))
            
            
            conn.commit()
            flash('Meal updated successfully!', 'success')
            return redirect(url_for('trainer_manage_plan', member_id=member_id))
            
        except Exception as e:
            conn.rollback()
            flash(f'Error updating meal: {e}', 'danger')
            print(e) 
            
    conn.close()
    return render_template('trainer_edit_meal.html', meal=meal, member_id=member_id)

@app.route('/trainer/delete_meal/<int:meal_id>/<int:member_id>', methods=['POST'])
def trainer_delete_meal(meal_id, member_id):
    if session.get('role') not in ['TRAINER', 'ADMIN']: return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor()
    
    try:
        cursor.execute("""
            DELETE FROM diet_meals 
            WHERE meal_id=%s 
            AND plan_id IN (SELECT plan_id FROM diet_plans WHERE member_id=%s)
        """, (meal_id, member_id))
        
        if cursor.rowcount > 0:
            conn.commit()
            flash('Meal deleted successfully.', 'success')
        else:
            flash('Meal not found or unauthorized.', 'danger')
            
    except Exception as e:
        flash(f'Error deleting meal: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('trainer_manage_plan', member_id=member_id))

@app.route('/trainer/manage_plan/<int:member_id>', methods=['GET'])
def trainer_manage_plan(member_id): 
    if 'user_id' not in session or session['role'] not in ['TRAINER', 'ADMIN']:
        return redirect(url_for('login'))
    
    trainer_id = session['user_id']
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    is_authorized = (session['role'] == 'ADMIN')
    if not is_authorized and session['role'] == 'TRAINER':
        cursor.execute("SELECT * FROM trainer_members WHERE trainer_id = %s AND member_id = %s", (trainer_id, member_id))
        if cursor.fetchone():
            is_authorized = True
    
    if not is_authorized:
        conn.close()
        flash('Access Denied: You are not authorized to manage this member.', 'danger')
        return redirect(url_for('trainer_dashboard'))

    cursor.execute("SELECT full_name FROM profiles WHERE user_id = %s", (member_id,))
    member_name = cursor.fetchone()['full_name']

    workout_query = """
        SELECT we.id, we.day_name, e.name, we.sets_count, we.reps_target, we.status
        FROM workout_exercises we 
        JOIN workouts w ON we.workout_id = w.workout_id 
        JOIN exercises e ON we.exercise_id = e.exercise_id 
        WHERE w.member_id = %s AND w.source = 'TRAINER' AND w.end_date >= CURRENT_DATE
        ORDER BY FIELD(we.day_name,'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
    """
    cursor.execute(workout_query, (member_id,))
    workout_details = cursor.fetchall()
    
    workouts_grouped = {}
    for row in workout_details:
        day = row['day_name']
        if day not in workouts_grouped:
            workouts_grouped[day] = []
        workouts_grouped[day].append(row)

    diet_query = """
        SELECT dm.meal_id AS id, dm.day_name, dm.meal_type, dm.description, dm.calories
        FROM diet_meals dm 
        JOIN diet_plans dp ON dm.plan_id = dp.plan_id 
        WHERE dp.member_id = %s 
        ORDER BY FIELD(dm.day_name,'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'), FIELD(dm.meal_type, 'Breakfast', 'Lunch', 'Snack', 'Dinner')
    """
    cursor.execute(diet_query, (member_id,))
    diet_details = cursor.fetchall()
    
    diet_grouped = {}
    for row in diet_details:
        day = row['day_name']
        if day not in diet_grouped:
            diet_grouped[day] = []
        diet_grouped[day].append(row)

    conn.close()

    return render_template('manage_plant.html', 
                           member_id=member_id,
                           member_name=member_name,
                           workouts=workouts_grouped,
                           diet_meals=diet_grouped)

@app.route('/trainer/view_profile/<int:member_id>', methods=['GET'])
def trainer_view_profile(member_id):
    if 'user_id' not in session or session['role'] not in ['TRAINER', 'ADMIN']:
        return redirect(url_for('login'))
    
    trainer_id = session['user_id']
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    is_authorized = (session['role'] == 'ADMIN')
    if not is_authorized and session['role'] == 'TRAINER':
        cursor.execute("SELECT * FROM trainer_members WHERE trainer_id = %s AND member_id = %s", (trainer_id, member_id))
        if cursor.fetchone():
            is_authorized = True
    
    if not is_authorized:
        conn.close()
        flash('Access Denied: You are not authorized to view this profile.', 'danger')
        return redirect(url_for('trainer_dashboard'))

    cursor.execute("""
        SELECT u.email, p.*, 
               (SELECT weight_kg FROM user_progress WHERE user_id = p.user_id ORDER BY entry_date DESC LIMIT 1) AS current_weight_kg
        FROM profiles p
        JOIN users u ON p.user_id = u.user_id
        WHERE p.user_id = %s
    """, (member_id,))
    member_profile = cursor.fetchone()

    bmi = None
    if member_profile and member_profile['current_weight_kg'] and member_profile['height_cm']:
        weight = float(member_profile['current_weight_kg'])
        height_m = float(member_profile['height_cm']) / 100
        bmi = round(weight / (height_m ** 2), 2)

    conn.close()
    
    if not member_profile:
        flash('Member profile not found.', 'danger')
        return redirect(url_for('trainer_dashboard'))

    return render_template('trainer_view_profile.html', 
                           profile=member_profile, 
                           bmi=bmi, 
                           member_id=member_id)

# =========================================
# PRODUCT MANAGEMENT (STORE)
# =========================================

@app.route('/admin/manage_products')
def manage_products():
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
    
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM products ORDER BY product_id DESC")
    products = cursor.fetchall()
    conn.close()
    return render_template('manage_products.html', products=products)

@app.route('/admin/add_product', methods=['GET', 'POST'])
def add_product():
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))

    if request.method == 'POST':
        name = request.form['name']
        price = request.form['price']
        stock = request.form['stock']

        image_filename = 'default_product.jpg' 
        if 'image' in request.files:
            file = request.files['image']
            if file.filename != '':
                filename = secure_filename(file.filename)
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                image_filename = filename

        conn = get_db_connection()
        cursor = conn.cursor()
        try:
            cursor.execute("""
                INSERT INTO products (name, price, stock_qty, is_active, image_path) 
                VALUES (%s, %s, %s, TRUE, %s)
            """, (name, price, stock, image_filename))
            
            conn.commit()
            flash('Product added successfully!', 'success')
            return redirect(url_for('manage_products'))
        except Exception as e:
            flash(f'Error adding product: {e}', 'danger')
        finally:
            conn.close()
            
    return render_template('add_product.html')

@app.route('/admin/edit_product/<int:id>', methods=['GET', 'POST'])
def edit_product(id):
    
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
    
    if request.method == 'POST':
        name = request.form['name']
        price = request.form['price']
        stock = request.form['stock']
        
        new_image = None
        if 'image' in request.files:
            file = request.files['image']
            if file.filename != '':
                filename = secure_filename(file.filename)
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                new_image = filename

        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            if new_image:
                cursor.execute("""
                    UPDATE products 
                    SET name=%s, price=%s, stock_qty=%s, image_path=%s 
                    WHERE product_id=%s
                """, (name, price, stock, new_image, id))
            else:
                cursor.execute("""
                    UPDATE products 
                    SET name=%s, price=%s, stock_qty=%s 
                    WHERE product_id=%s
                """, (name, price, stock, id))

            conn.commit()
            flash('Product updated successfully!', 'success')
            return redirect(url_for('manage_products'))
            
        except Exception as e:
            conn.rollback()
            flash(f'Error updating product: {e}', 'danger')
            return redirect(request.url)
        finally:
            conn.close() 

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("SELECT * FROM products WHERE product_id = %s", (id,))
        product = cursor.fetchone()
    finally:
        conn.close()

    if not product:
        flash("Product not found", "danger")
        return redirect(url_for('manage_products'))

    return render_template('edit_product.html', product=product)

@app.route('/admin/delete_product/<int:id>', methods=['GET', 'POST'])
def delete_product(id):
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
        
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM products WHERE product_id = %s", (id,))
        conn.commit()
        flash('Product deleted successfully.', 'success')
    except Exception as e:
        flash(f'Error deleting product: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('manage_products'))


# =========================================
# TRAINER MANAGEMENT
# =========================================

@app.route('/admin/manage_trainers')
def manage_trainers():
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
    
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT u.user_id, u.email, p.full_name, p.phone, t.specialization, t.years_experience 
        FROM users u
        JOIN profiles p ON u.user_id = p.user_id
        JOIN trainers t ON u.user_id = t.trainer_id
        WHERE u.role = 'TRAINER'
    """)
    trainers = cursor.fetchall()
    conn.close()
    return render_template('manage_trainers.html', trainers=trainers)

@app.route('/admin/add_trainer', methods=['GET', 'POST'])
def add_trainer():
    
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))

    if request.method == 'POST':
        full_name = request.form.get('full_name')
        email = request.form.get('email')
        password = request.form.get('password')
        phone = request.form.get('phone')
        spec = request.form.get('specialization')
        exp = request.form.get('experience') 

        if not all([full_name, email, password, phone, spec, exp]):
            flash("All fields are required!", "danger")
            return redirect(request.url)

        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute("INSERT INTO users (email, password_hash, role, is_active) VALUES (%s, SHA2(%s, 256), 'TRAINER', TRUE)", (email, password))
            user_id = cursor.lastrowid
            
            cursor.execute("INSERT INTO profiles (user_id, full_name, phone, gender, goal_type) VALUES (%s, %s, %s, 'MALE', 'MAINTAIN')", (user_id, full_name, phone))
            
            cursor.execute("INSERT INTO trainers (trainer_id, specialization, years_experience, phone) VALUES (%s, %s, %s, %s)", (user_id, spec, exp, phone))
            
            conn.commit()
            flash('Trainer added successfully!', 'success')
            return redirect(url_for('manage_trainers'))
            
        except Exception as e:
            conn.rollback()
            flash(f'Error adding trainer: {e}', 'danger')
            return redirect(request.url)
            
        finally:
            conn.close()

    return render_template('add_trainer.html')

@app.route('/admin/edit_trainer/<int:id>', methods=['GET', 'POST'])
def edit_trainer(id):
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
        
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    
    if request.method == 'POST':
        full_name = request.form['full_name']
        phone = request.form['phone']
        spec = request.form['specialization']
        exp = request.form['experience']
        
        try:
            cursor.execute("UPDATE profiles SET full_name=%s, phone=%s WHERE user_id=%s", (full_name, phone, id))
            cursor.execute("UPDATE trainers SET specialization=%s, years_experience=%s, phone=%s WHERE trainer_id=%s", (spec, exp, phone, id))
            conn.commit()
            flash('Trainer details updated successfully!', 'success')
            return redirect(url_for('manage_trainers'))
        except Exception as e:
            flash(f'Error updating: {e}', 'danger')
            
    cursor.execute("""
        SELECT u.email, p.full_name, p.phone, t.specialization, t.years_experience, u.user_id
        FROM users u
        JOIN profiles p ON u.user_id = p.user_id
        JOIN trainers t ON u.user_id = t.trainer_id
        WHERE u.user_id = %s
    """, (id,))
    trainer = cursor.fetchone()
    conn.close()
    return render_template('edit_trainer.html', trainer=trainer)

@app.route('/admin/delete_trainer/<int:id>', methods=['GET', 'POST'])
def delete_trainer(id):
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
        
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM users WHERE user_id = %s AND role = 'TRAINER'", (id,))
        conn.commit()
        flash('Trainer deleted successfully.', 'success')
    except Exception as e:
        flash(f'Error deleting trainer: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('manage_trainers'))

# =========================================
# USER/MEMBER MANAGEMENT
# =========================================

@app.route('/admin/manage_users')
def manage_users():
    if session.get('role') != 'ADMIN': return redirect(url_for('login'))
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)
    
    cursor.execute("""
        SELECT u.user_id, u.email, p.full_name, p.subscription_plan, p.requested_trainer_id,
               (SELECT p2.full_name FROM trainer_members tm JOIN profiles p2 ON tm.trainer_id = p2.user_id WHERE tm.member_id = u.user_id LIMIT 1) as trainer_name,
               (SELECT p3.full_name FROM profiles p3 WHERE p3.user_id = p.requested_trainer_id) as requested_trainer_name
        FROM users u 
        JOIN profiles p ON u.user_id = p.user_id 
        WHERE u.role = 'MEMBER'
    """)
    users = cursor.fetchall()
    
    cursor.execute("SELECT u.user_id, p.full_name FROM users u JOIN profiles p ON u.user_id = p.user_id WHERE u.role = 'TRAINER'")
    trainers_list = cursor.fetchall()
    conn.close()
    return render_template('manage_users.html', users=users, trainers_list=trainers_list)

@app.route('/admin/add_user', methods=['GET', 'POST'])
def add_user():
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))

    if request.method == 'POST':
        first_name = request.form.get('first_name')
        last_name = request.form.get('last_name')
        email = request.form.get('email')
        phone = request.form.get('phone')
        dob = request.form.get('date_of_birth')
        gender = request.form.get('gender')
        password = request.form.get('password')
        
        height = request.form.get('height')
        weight = request.form.get('weight') 
        goal = request.form.get('goal')
        plan = request.form.get('plan') 

        if not all([first_name, last_name, email, phone, dob, password, weight, height]):
            flash("All fields are required!", "danger")
            return redirect(request.url)

        conn = get_db_connection() 
        cursor = conn.cursor()

        try:
            full_name = f"{first_name} {last_name}"

            cursor.execute("INSERT INTO users (email, password_hash, role, is_active) VALUES (%s, SHA2(%s, 256), 'MEMBER', TRUE)", (email, password))
            user_id = cursor.lastrowid 

            default_target = float(weight) + (10 if goal == 'GAIN_WEIGHT' else -10 if goal == 'LOSE_WEIGHT' else 0)

            cursor.execute("""
                INSERT INTO profiles (
                    user_id, full_name, phone, gender, goal_type, subscription_plan, 
                    height_cm, current_weight_kg, target_weight_kg, date_of_birth
                ) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                user_id, full_name, phone, gender, goal, plan, 
                height, weight, default_target, dob
            ))

            cursor.execute("INSERT INTO user_progress (user_id, entry_date, weight_kg, calories_intake) VALUES (%s, CURRENT_DATE, %s, 0)", (user_id, weight))

            conn.commit()
            flash("User added successfully!", "success")
            return redirect(url_for('manage_users'))

        except Exception as e:
            conn.rollback()
            flash(f"Error adding user: {e}", "danger")
            return redirect(request.url)
        
        finally:
            conn.close()

    return render_template('add_user.html')

@app.route('/admin/delete_user/<int:id>', methods=['GET', 'POST'])
def delete_user(id):
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
        
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM users WHERE user_id = %s AND role = 'MEMBER'", (id,))
        conn.commit()
        flash('Member account deleted successfully.', 'success')
    except Exception as e:
        flash(f'Error deleting user: {e}', 'danger')
    finally:
        conn.close()
        
    return redirect(url_for('manage_users'))

@app.route('/admin/edit_user/<int:user_id>', methods=['GET', 'POST'])
def edit_user(user_id):
    if 'user_id' not in session or session['role'] != 'ADMIN':
        flash('Access denied. Admins only.', 'danger')
        return redirect(url_for('login'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    if request.method == 'POST':
        full_name = request.form['full_name']
        phone = request.form['phone']
        dob = request.form['date_of_birth']       
        height = request.form['height']
        current_w = request.form['weight']
        gender = request.form['gender']
        goal = request.form['goal']
        
        try:
            cursor.execute("""
                UPDATE profiles 
                SET full_name=%s, phone=%s, date_of_birth=%s, height_cm=%s, gender=%s, goal_type=%s
                WHERE user_id=%s
            """, (full_name, phone, dob, height, gender, goal, user_id))

            cursor.execute("""
                INSERT INTO user_progress (user_id, entry_date, weight_kg, calories_intake)
                VALUES (%s, CURRENT_DATE, %s, 0)
                ON DUPLICATE KEY UPDATE weight_kg=%s
            """, (user_id, current_w, current_w))
            
            conn.commit()
            flash(f'User updated successfully!', 'success')
            return redirect(url_for('manage_users'))
            
        except Exception as e:
            conn.rollback()
            flash(f'Error updating user: {e}', 'danger')
        finally:
            conn.close()

    query = """
        SELECT u.user_id, u.email, 
               p.full_name, p.phone, p.date_of_birth, p.height_cm, p.gender, p.goal_type,
               (SELECT weight_kg FROM user_progress WHERE user_id = u.user_id ORDER BY entry_date DESC LIMIT 1) AS current_weight_kg
        FROM users u
        LEFT JOIN profiles p ON u.user_id = p.user_id 
        WHERE u.user_id = %s
    """
    cursor.execute(query, (user_id,))
    user_data = cursor.fetchone()
    conn.close()
    
    if not user_data:
        flash('User not found.', 'danger')
        return redirect(url_for('admin_dashboard'))

    return render_template('edit_user.html', user=user_data)

# =========================================
# ADMIN DASHBOARD & REPORTS
# =========================================

@app.route('/admin')
def admin_dashboard(): 
    if 'user_id' not in session: 
        flash('Admin login required.', 'danger')
        return redirect(url_for('login'))
        
    if session.get('role') != 'ADMIN':
        flash('Access denied. Admins only.', 'danger')
        return redirect(url_for('dashboard'))
        
    conn = get_db_connection()
    if conn is None:
        flash('Database connection failed.', 'error')
        return redirect(url_for('dashboard'))
        
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("SELECT COUNT(CASE WHEN role = 'MEMBER' THEN 1 END) AS total_members, COUNT(CASE WHEN role = 'TRAINER' THEN 1 END) AS total_trainers FROM users")
        user_counts = cursor.fetchone()
        
        cursor.execute("SELECT COUNT(product_id) AS total_products FROM products")
        product_count = cursor.fetchone()
    
        cursor.execute("SELECT COUNT(user_id) AS new_members_week FROM users WHERE role = 'MEMBER' AND created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)")
        new_members = cursor.fetchone()

        stats = {
            'total_members': user_counts['total_members'],
            'total_trainers': user_counts['total_trainers'],
            'total_products': product_count['total_products'],
            'new_members_week': new_members['new_members_week'],
        }

        cursor.execute("""
            SELECT u.email, u.created_at, p.full_name 
            FROM users u
            LEFT JOIN profiles p ON u.user_id = p.user_id
            WHERE u.role = 'MEMBER'
            ORDER BY u.created_at DESC LIMIT 5
        """)
        recent_signups = cursor.fetchall()

        cursor.execute("""
            SELECT id, name, email, message, created_at
            FROM contact_messages
            ORDER BY created_at DESC LIMIT 10
        """)
        contact_messages = cursor.fetchall()
        
    except Exception as e:
        stats = {k: 0 for k in ['total_members', 'total_trainers', 'total_products', 'new_members_week']}
        recent_signups = []
        contact_messages = []
        print(f"Error fetching admin data: {e}")
        flash('An error occurred while loading dashboard data.', 'error')
    
    conn.close()
    
    return render_template('admin_dashboard.html', 
                           stats=stats, 
                           recent_signups=recent_signups,
                           contact_messages=contact_messages)

@app.route('/admin/reports')
def admin_reports():
    if 'user_id' not in session or session['role'] != 'ADMIN':
        return redirect(url_for('login'))
        
    conn = get_db_connection(); cursor = conn.cursor(dictionary=True)

    cursor.execute("SELECT SUM(qty * unit_price) as total_revenue FROM order_items")
    revenue = cursor.fetchone()['total_revenue'] or 0
    
    cursor.execute("""
        SELECT p.name, SUM(oi.qty) as total_sold 
        FROM order_items oi JOIN products p ON oi.product_id = p.product_id 
        GROUP BY p.product_id ORDER BY total_sold DESC LIMIT 1
    """)
    top_product = cursor.fetchone()
    
    cursor.execute("SELECT COUNT(*) as count FROM users WHERE role='MEMBER'")
    total_members = cursor.fetchone()['count']
    
    cursor.execute("SELECT COUNT(*) as count FROM users WHERE role='TRAINER'")
    total_trainers = cursor.fetchone()['count']
    
    cursor.execute("SELECT SUM(stock_qty) as total_stock FROM products")
    stock_result = cursor.fetchone()

    products_in_stock = stock_result['total_stock'] if stock_result and stock_result['total_stock'] else 0

    cursor.execute("""
        SELECT u.email, u.created_at, p.full_name, p.gender
        FROM users u
        LEFT JOIN profiles p ON u.user_id = p.user_id
        WHERE u.role = 'MEMBER'
        ORDER BY u.created_at DESC LIMIT 10
    """)
    recent_members = cursor.fetchall()
    
    stats = {
        'revenue': revenue,
        'top_product': top_product['name'] if top_product else 'N/A',
        'total_members': total_members,
        'total_trainers': total_trainers,
        'products_in_stock': int(products_in_stock) 
    }
    
    conn.close()
    return render_template('reports.html', stats=stats, recent_members=recent_members)

@app.route('/admin/contact/delete/<int:message_id>', methods=['POST', 'GET'])
def delete_contact_message(message_id):
    if 'user_id' not in session or session.get('role') != 'ADMIN':
        flash('Access denied. Admins only.', 'danger')
        return redirect(url_for('dashboard'))

    conn = get_db_connection()
    if conn is None:
        flash('Database connection failed.', 'error')
        return redirect(url_for('admin_dashboard'))
        
    cursor = conn.cursor()

    try:
        sql = "DELETE FROM contact_messages WHERE id = %s"
        rows_affected = cursor.execute(sql, (message_id,))
        conn.commit()

        if cursor.rowcount == 0:
            flash(f'Error: Message ID {message_id} not found or already deleted.', 'error')
        else:
            flash(f'Message ID {message_id} deleted successfully.', 'success')
            
    except Exception as e:
        conn.rollback()
        print(f"Delete Error for message {message_id}: {e}")
        flash('An error occurred during deletion.', 'error')

    finally:
        conn.close()

    return redirect(url_for('admin_dashboard'))

if __name__ == '__main__':
    app.run(debug=True, port=5000)