import mysql.connector
from flask import Flask, request, jsonify
from flask_cors import CORS

app = Flask(__name__)
CORS(app)  # Enable CORS for all routes

# Database configuration
db_config = {
    'host': 'mysql.theaken.com',
    'port': 33306,
    'user': 'A027',
    'password': 'E1CelfxqlKoj',
    'database': 'db_A027'
}

def get_db_connection():
    """Establishes a new database connection."""
    try:
        conn = mysql.connector.connect(**db_config)
        return conn
    except mysql.connector.Error as err:
        print(f"Database connection error: {err}")
        return None

def make_success_response(data, code, message="Success"):
    """Wrapper for a successful API response."""
    response = {
        "status": "success",
        "code": code,
        "message": message,
    }
    if data is not None:
        response["data"] = data
    return jsonify(response), code

def make_error_response(message, code):
    """Wrapper for an error API response."""
    return jsonify({
        "status": "error",
        "code": code,
        "message": message
    }), code

def query_to_dict(cursor, data):
    """Converts query result to a list of dictionaries."""
    columns = [desc[0] for desc in cursor.description]
    return [dict(zip(columns, row)) for row in data]

# --- Orders API Endpoints ---

@app.route('/v1/orders', methods=['POST'])
def create_order():
    data = request.get_json()
    if not data:
        return make_error_response("Invalid JSON", 400)

    required_fields = ['emp_id', 'emp_name', 'menu_item_id', 'main_course', 'order_date', 'order_qty']
    if not all(field in data for field in required_fields):
        return make_error_response(f"Missing required fields: {required_fields}", 400)

    sql = """
        INSERT INTO orders (emp_id, emp_name, menu_item_id, main_course, order_date, order_qty)
        VALUES (%(emp_id)s, %(emp_name)s, %(menu_item_id)s, %(main_course)s, %(order_date)s, %(order_qty)s)
    """
    
    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)
        
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, data)
        conn.commit()
        new_id = cursor.lastrowid
        cursor.execute("SELECT * FROM orders WHERE id = %s", (new_id,))
        new_order = cursor.fetchone()
        return make_success_response(new_order, 201, "Order created successfully.")
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

@app.route('/v1/orders/<int:order_id>', methods=['GET'])
def get_order_by_id(order_id):
    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)
        
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
        order = cursor.fetchone()
        if order:
            return make_success_response(order, 200)
        else:
            return make_error_response("Order not found", 404)
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

@app.route('/v1/orders', methods=['GET'])
def get_orders():
    query_params = request.args.to_dict()
    
    sql = "SELECT * FROM orders"
    conditions = []
    params = {}

    for key, value in query_params.items():
        # Basic filtering, can be expanded
        conditions.append(f"{key} = %({key})s")
        params[key] = value

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

    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)

    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, params)
        orders = cursor.fetchall()
        
        meta = {
            "total_items": len(orders),
            "query_params": query_params
        }
        data = {
            "orders": orders,
            "meta": meta
        }
        return make_success_response(data, 200)
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()


@app.route('/v1/orders/<int:order_id>', methods=['PATCH'])
def update_order(order_id):
    data = request.get_json()
    if not data:
        return make_error_response("Invalid JSON", 400)

    set_clauses = []
    params = {}
    for key, value in data.items():
        set_clauses.append(f"{key} = %({key})s")
        params[key] = value
    
    if not set_clauses:
        return make_error_response("No fields to update", 400)

    params['id'] = order_id
    sql = f"UPDATE orders SET {', '.join(set_clauses)} WHERE id = %(id)s"

    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)

    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
        if not cursor.fetchone():
            return make_error_response("Order not found", 404)
            
        cursor.execute(sql, params)
        conn.commit()
        
        cursor.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
        updated_order = cursor.fetchone()
        return make_success_response(updated_order, 200, "Order updated successfully.")
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

@app.route('/v1/orders/<int:order_id>', methods=['DELETE'])
def delete_order(order_id):
    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)

    try:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
        if not cursor.fetchone():
            return make_error_response("Order not found", 404)

        cursor.execute("DELETE FROM orders WHERE id = %s", (order_id,))
        conn.commit()
        return make_success_response(None, 204, "Order deleted successfully.")
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

# --- Menu Items API Endpoints ---

@app.route('/v1/menu_items', methods=['POST'])
def create_menu_item():
    data = request.get_json()
    if not data:
        return make_error_response("Invalid JSON", 400)

    required_fields = ['main_course', 'menu_date']
    if not all(field in data for field in required_fields):
        return make_error_response(f"Missing required fields: {required_fields}", 400)

    sql = """
        INSERT INTO menu_items (main_course, side_dish, addon, menu_date)
        VALUES (%(main_course)s, %(side_dish)s, %(addon)s, %(menu_date)s)
    """
    
    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)
        
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, data)
        conn.commit()
        new_id = cursor.lastrowid
        cursor.execute("SELECT * FROM menu_items WHERE id = %s", (new_id,))
        new_item = cursor.fetchone()
        return make_success_response(new_item, 201, "Menu item created successfully.")
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

@app.route('/v1/menu_items/<int:item_id>', methods=['GET'])
def get_menu_item_by_id(item_id):
    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)
        
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM menu_items WHERE id = %s", (item_id,))
        item = cursor.fetchone()
        if item:
            return make_success_response(item, 200)
        else:
            return make_error_response("Menu item not found", 404)
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

@app.route('/v1/menu_items', methods=['GET'])
def get_menu_items():
    query_params = request.args.to_dict()
    
    sql = "SELECT * FROM menu_items"
    conditions = []
    params = {}

    for key, value in query_params.items():
        conditions.append(f"{key} = %({key})s")
        params[key] = value

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

    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)

    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, params)
        items = cursor.fetchall()
        
        meta = {
            "total_items": len(items),
            "query_params": query_params
        }
        data = {
            "menu_items": items,
            "meta": meta
        }
        return make_success_response(data, 200)
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

@app.route('/v1/menu_items/<int:item_id>', methods=['PATCH'])
def update_menu_item(item_id):
    data = request.get_json()
    if not data:
        return make_error_response("Invalid JSON", 400)

    set_clauses = []
    params = {}
    for key, value in data.items():
        set_clauses.append(f"{key} = %({key})s")
        params[key] = value
    
    if not set_clauses:
        return make_error_response("No fields to update", 400)

    params['id'] = item_id
    sql = f"UPDATE menu_items SET {', '.join(set_clauses)} WHERE id = %(id)s"

    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)

    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM menu_items WHERE id = %s", (item_id,))
        if not cursor.fetchone():
            return make_error_response("Menu item not found", 404)
            
        cursor.execute(sql, params)
        conn.commit()
        
        cursor.execute("SELECT * FROM menu_items WHERE id = %s", (item_id,))
        updated_item = cursor.fetchone()
        return make_success_response(updated_item, 200, "Menu item updated successfully.")
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

@app.route('/v1/menu_items/<int:item_id>', methods=['DELETE'])
def delete_menu_item(item_id):
    conn = get_db_connection()
    if not conn:
        return make_error_response("Database connection failed", 500)

    try:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM menu_items WHERE id = %s", (item_id,))
        if not cursor.fetchone():
            return make_error_response("Menu item not found", 404)

        cursor.execute("DELETE FROM menu_items WHERE id = %s", (item_id,))
        conn.commit()
        return make_success_response(None, 204, "Menu item deleted successfully.")
    except mysql.connector.Error as err:
        return make_error_response(f"Database error: {err}", 500)
    finally:
        if conn.is_connected():
            conn.close()

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