from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required
from db.db import get_db_connection
from config.auth import role_required
import mysql.connector

variation_bp = Blueprint('variation', __name__)

# ✅ CREATE VARIATION
@variation_bp.route('/create_variation', methods=['POST'])
@jwt_required()
@role_required('admin')
def create_variation():
    conn = None
    cursor = None
    try:
        data = request.get_json()
        
        # Validate request data
        if not data:
            return jsonify({"error": "No data provided"}), 400
        
        name = data.get('name')
        variation_types = data.get('variation_types', [])
        
        # Debug logging
        print(f"Received data: {data}")
        print(f"Name: {name}")
        print(f"Types: {variation_types}")
        
        # Validation
        if not name or name.strip() == "":
            return jsonify({"error": "Variation name is required"}), 400
        
        if not variation_types or len(variation_types) == 0:
            return jsonify({"error": "At least one variation type is required"}), 400
        
        # Database operations
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Insert variation
        insert_variation_query = "INSERT INTO variations (name) VALUES (%s)"
        cursor.execute(insert_variation_query, (name.strip(),))
        variation_id = cursor.lastrowid
        
        # Insert variation types
        insert_type_query = """
            INSERT INTO variation_types (variation_id, type_name, sort_order) 
            VALUES (%s, %s, %s)
        """
        
        for index, type_name in enumerate(variation_types):
            if type_name and type_name.strip():  # Only insert non-empty types
                cursor.execute(insert_type_query, (variation_id, type_name.strip(), index))
        
        conn.commit()
        
        return jsonify({
            "message": "Variation created successfully",
            "variation_id": variation_id
        }), 201
        
    except mysql.connector.Error as err:
        if conn:
            conn.rollback()
        print(f"Database Error: {err}")
        return jsonify({"error": f"Database error: {str(err)}"}), 500
        
    except Exception as e:
        if conn:
            conn.rollback()
        print(f"Server Error: {e}")
        return jsonify({"error": f"Server error: {str(e)}"}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ✅ VIEW ALL VARIATIONS
@variation_bp.route('/variations', methods=['GET'])
@jwt_required()
def get_all_variations():
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Get all variations with their types
        query = """
            SELECT 
                v.id,
                v.name,
                v.created_at,
                GROUP_CONCAT(vt.type_name ORDER BY vt.sort_order SEPARATOR ', ') as variation_types
            FROM variations v
            LEFT JOIN variation_types vt ON v.id = vt.variation_id
            GROUP BY v.id, v.name, v.created_at
            ORDER BY v.created_at DESC
        """
        
        cursor.execute(query)
        variations = cursor.fetchall()
        
        return jsonify({"variations": variations}), 200
        
    except mysql.connector.Error as err:
        print(f"Database Error: {err}")
        return jsonify({"error": f"Database error: {str(err)}"}), 500
        
    except Exception as e:
        print(f"Server Error: {e}")
        return jsonify({"error": f"Server error: {str(e)}"}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ✅ GET SINGLE VARIATION BY ID
@variation_bp.route('/variation/<int:variation_id>', methods=['GET'])
@jwt_required()
def get_variation_by_id(variation_id):
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # Get variation details
        cursor.execute("SELECT * FROM variations WHERE id = %s", (variation_id,))
        variation = cursor.fetchone()
        
        if not variation:
            return jsonify({"error": "Variation not found"}), 404
        
        # Get variation types
        cursor.execute(
            "SELECT id, type_name FROM variation_types WHERE variation_id = %s ORDER BY sort_order",
            (variation_id,)
        )
        types = cursor.fetchall()
        
        variation['types'] = types
        
        return jsonify({"variation": variation}), 200
        
    except mysql.connector.Error as err:
        print(f"Database Error: {err}")
        return jsonify({"error": f"Database error: {str(err)}"}), 500
        
    except Exception as e:
        print(f"Server Error: {e}")
        return jsonify({"error": f"Server error: {str(e)}"}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()





# ✅ EDIT/UPDATE VARIATION
@variation_bp.route('/edit_variation/<int:variation_id>', methods=['PUT'])
@jwt_required()
@role_required('admin')
def edit_variation(variation_id):
    conn = None
    cursor = None
    try:
        data = request.get_json()
        
        if not data:
            return jsonify({"error": "No data provided"}), 400
        
        name = data.get('name')
        variation_types = data.get('variation_types', [])
        
        # Debug logging
        print(f"Updating variation {variation_id}")
        print(f"New name: {name}")
        print(f"New types: {variation_types}")
        
        # Validation
        if not name or name.strip() == "":
            return jsonify({"error": "Variation name is required"}), 400
        
        if not variation_types or len(variation_types) == 0:
            return jsonify({"error": "At least one variation type is required"}), 400
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Check if variation exists
        cursor.execute("SELECT id FROM variations WHERE id = %s", (variation_id,))
        if not cursor.fetchone():
            return jsonify({"error": "Variation not found"}), 404
        
        # Update variation name
        update_query = "UPDATE variations SET name = %s WHERE id = %s"
        cursor.execute(update_query, (name.strip(), variation_id))
        
        # Delete old variation types
        delete_query = "DELETE FROM variation_types WHERE variation_id = %s"
        cursor.execute(delete_query, (variation_id,))
        
        # Insert new variation types
        insert_query = """
            INSERT INTO variation_types (variation_id, type_name, sort_order) 
            VALUES (%s, %s, %s)
        """
        
        for index, type_name in enumerate(variation_types):
            if type_name and type_name.strip():
                cursor.execute(insert_query, (variation_id, type_name.strip(), index))
        
        conn.commit()
        
        return jsonify({"message": "Variation updated successfully"}), 200
        
    except mysql.connector.Error as err:
        if conn:
            conn.rollback()
        print(f"Database Error: {err}")
        return jsonify({"error": f"Database error: {str(err)}"}), 500
        
    except Exception as e:
        if conn:
            conn.rollback()
        print(f"Server Error: {e}")
        return jsonify({"error": f"Server error: {str(e)}"}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ✅ DELETE VARIATION
@variation_bp.route('/delete_variation/<int:variation_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin')
def delete_variation(variation_id):
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Check if variation exists
        cursor.execute("SELECT id FROM variations WHERE id = %s", (variation_id,))
        if not cursor.fetchone():
            return jsonify({"error": "Variation not found"}), 404
        
        # Delete variation (types will be deleted automatically due to CASCADE)
        delete_query = "DELETE FROM variations WHERE id = %s"
        cursor.execute(delete_query, (variation_id,))
        
        conn.commit()
        
        return jsonify({"message": "Variation deleted successfully"}), 200
        
    except mysql.connector.Error as err:
        if conn:
            conn.rollback()
        print(f"Database Error: {err}")
        return jsonify({"error": f"Database error: {str(err)}"}), 500
        
    except Exception as e:
        if conn:
            conn.rollback()
        print(f"Server Error: {e}")
        return jsonify({"error": f"Server error: {str(e)}"}), 500
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()