import os
from flask import jsonify, request
from db_config import get_db_connection
from werkzeug.utils import secure_filename
from PIL import Image

def get_exercises():
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        query = """
            SELECT 
                e.name, 
                COALESCE(GROUP_CONCAT(DISTINCT m.name SEPARATOR ', '), 'No Muscles Found') AS muscle,
                 COALESCE(GROUP_CONCAT(DISTINCT m.display_name SEPARATOR ', '), 'No Muscles Found') AS muscle_display,
                COALESCE(eq.name, 'No Equipment') AS equipment,
                e.description,
                e.image_url
            FROM exercises e
            LEFT JOIN exercise_muscle em ON e.id = em.exercise_id
            LEFT JOIN muscles m ON em.muscle_id = m.id
            LEFT JOIN equipments eq ON e.equipment_id = eq.id
            GROUP BY e.id;
        """

        cursor.execute(query)
        exercises = cursor.fetchall()

        for ex in exercises:
            ex["description"] = ex.get("description") or "No description available"
            ex["image_url"] = ex.get("image_url") or "https://img.freepik.com/premium-vector/barbell-gym-equipment-bodybuilding_316839-7742.jpg"

        cursor.close()
        conn.close()
        return jsonify(exercises)

    except Exception as e:
        return jsonify({"error": str(e)}), 500

def create_exercise():
    try:
        data = request.json
        name = data.get("name")
        equipment = data.get("equipment")
        description = data.get("description")
        image_url = data.get("image_url")

        if not name:
            return jsonify({"error": "Exercise name is required"}), 400

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

        cursor.execute(
            "INSERT INTO exercises (name, equipment_id, description, image_url) VALUES (%s, (SELECT id FROM equipments WHERE name = %s), %s, %s)",
            (name, equipment, description, image_url)
        )
        conn.commit()

        cursor.close()
        conn.close()
        return jsonify({"status": "exercise created"}), 201

    except Exception as e:
        return jsonify({"error": str(e)}), 500

def delete_exercise():
    try:
        data = request.json
        name = data.get("name")

        if not name:
            return jsonify({"error": "Exercise name is required"}), 400

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

        cursor.execute("DELETE FROM exercises WHERE name = %s", (name,))
        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({"status": "exercise deleted"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500


def update_exercise_field():
    try:
        data = request.json
        name = data.get("exerciseName")
        field = data.get("field")
        value = data.get("value")

        if not name or not field:
            return jsonify({"error": "Missing exerciseName or field"}), 400

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

        cursor.execute(f"UPDATE exercises SET {field} = %s WHERE name = %s", (value, name))
        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({"status": "success"}), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

def filter_exercises():
    try:
        muscle = request.args.get("muscle")
        equipment = request.args.get("equipment")

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

        query = """
            SELECT 
                e.name, 
                COALESCE(GROUP_CONCAT(DISTINCT m.name SEPARATOR ', '), 'No Muscles Found') AS muscle,
                COALESCE(GROUP_CONCAT(DISTINCT m.display_name SEPARATOR ', '), 'No Muscles Found') AS muscle_display,
                COALESCE(eq.name, 'No Equipment') AS equipment,
                e.description,
                e.image_url
            FROM exercises e
            LEFT JOIN exercise_muscle em ON e.id = em.exercise_id
            LEFT JOIN muscles m ON em.muscle_id = m.id  
            LEFT JOIN equipments eq ON e.equipment_id = eq.id
        """

        conditions = []
        values = []

        if muscle:
            conditions.append("m.name = %s")
            values.append(muscle)

        if equipment:
            conditions.append("eq.name = %s")
            values.append(equipment)

        if conditions:
            query += " WHERE " + " AND ".join(conditions)

        query += " GROUP BY e.id"

        cursor.execute(query, tuple(values))
        results = cursor.fetchall()

        for ex in results:
            ex["description"] = ex.get("description") or "No description available"
            ex["image_url"] = ex.get("image_url") or "https://img.freepik.com/premium-vector/barbell-gym-equipment-bodybuilding_316839-7742.jpg"

        cursor.close()
        conn.close()
        return jsonify(results)

    except Exception as e:
        return jsonify({"error": str(e)}), 500

EXERCISE_UPLOAD_FOLDER = "/var/www/wisegym.app/api/data/exercises"
ALLOWED_EXTENSIONS = {"png", "jpg", "jpeg", "gif"}

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

def upload_exercise_image():
    try:
        if 'file' not in request.files or 'name' not in request.form:
            return jsonify({"error": "Missing file or exercise name"}), 400

        file = request.files['file']
        name = request.form['name']

        if file.filename == '' or not allowed_file(file.filename):
            return jsonify({"error": "Invalid file"}), 400
        if not file.mimetype.startswith('image/'):
            return jsonify({"error": "Only images allowed"}), 400

        ext = file.filename.rsplit('.', 1)[1].lower()
        safe_base = secure_filename(f"{name}_exercise")
        filename = f"{safe_base}.{ext}"
        save_path = os.path.join(EXERCISE_UPLOAD_FOLDER, filename)

        # Overwrite old formats
        for existing_ext in ALLOWED_EXTENSIONS:
            old_path = os.path.join(EXERCISE_UPLOAD_FOLDER, f"{safe_base}.{existing_ext}")
            if os.path.exists(old_path):
                os.remove(old_path)

        file.save(save_path)

        # Check image size
        with Image.open(save_path) as img:
            width, height = img.size
            if width < 100 or height < 100:
                os.remove(save_path)
                return jsonify({"error": "Image too small (min 100x100px)"}), 400
            if width > 1000 or height > 1000:
                os.remove(save_path)
                return jsonify({"error": "Image too large (max 1000x1000px)"}), 400

        # Construct image URL
        public_url = f"https://api.wisegym.app/data/exercises/{filename}"

        # ✅ Update database
        try:
            conn = get_db_connection()
            cursor = conn.cursor()
            cursor.execute("UPDATE exercises SET image_url = %s WHERE name = %s", (public_url, name))
            conn.commit()
            cursor.close()
            conn.close()
        except Exception as db_err:
            os.remove(save_path)
            return jsonify({"error": "Failed to update image_url in DB", "details": str(db_err)}), 500

        return jsonify({"status": "success", "url": public_url}), 200

    except Exception as e:
        return jsonify({"error": "Internal error", "details": str(e)}), 500