from flask import Flask, request, jsonify, render_template
from flask_cors import CORS
import mysql.connector
from mysql.connector import Error
from datetime import datetime
import re

app = Flask(__name__)
CORS(app)  # 啟用CORS，允許本機前端訪問

# 資料庫連接設定 - 從DB_connection.txt獲取
DB_CONFIG = {
    'host': 'mysql.theaken.com',
    'database': 'db_A019',
    'user': 'A019',
    'password': '9wvKEkxBzVca',
    'port': 33306
}

def get_db_connection():
    """建立資料庫連接"""
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        return connection
    except Error as e:
        print(f"資料庫連接錯誤: {e}")
        return None

def validate_email(email):
    """驗證email格式"""
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    return re.match(pattern, email) is not None

def validate_age(age):
    """驗證年齡範圍"""
    try:
        age_int = int(age)
        return 1 <= age_int <= 120
    except ValueError:
        return False

@app.route('/v1/menu_items', methods=['GET'])
def get_menu_items():
    """
    GET /v1/menu_items - 查詢menu_items資料表
    支援查詢參數: ?main_course, ?side_dish, ?addon, ?Order_Date, ?is_active
    """
    try:
        # 獲取查詢參數
        main_course = request.args.get('main_course')
        side_dish = request.args.get('side_dish')
        addon = request.args.get('addon')
        order_date = request.args.get('Order_Date')
        is_active = request.args.get('is_active')
        
        connection = get_db_connection()
        if connection is None:
            return jsonify({
                'status': 'error',
                'code': 500,
                'message': '無法連接資料庫'
            }), 500
        
        cursor = connection.cursor(dictionary=True)
        
        # 建立查詢條件
        query = "SELECT * FROM menu_items WHERE 1=1"
        params = []
        
        if main_course:
            query += " AND main_course LIKE %s"
            params.append(f"%{main_course}%")
        
        if side_dish:
            query += " AND side_dish LIKE %s"
            params.append(f"%{side_dish}%")
        
        if addon:
            query += " AND addon LIKE %s"
            params.append(f"%{addon}%")
        
        if order_date:
            query += " AND Order_Date = %s"
            params.append(order_date)
        
        if is_active is not None:
            query += " AND is_active = %s"
            params.append(is_active.lower() in ['true', '1', 'yes'])
        
        # 執行查詢
        cursor.execute(query, params)
        results = cursor.fetchall()
        
        # 獲取總數用於meta資訊
        count_query = "SELECT COUNT(*) as total FROM menu_items WHERE 1=1" + query.split('WHERE 1=1')[1]
        cursor.execute(count_query, params)
        total_count = cursor.fetchone()['total']
        
        cursor.close()
        connection.close()
        
        return jsonify({
            'status': 'success',
            'code': 200,
            'message': '查詢成功',
            'data': results,
            'meta': {
                'total': total_count,
                'page': 1,
                'per_page': len(results),
                'has_more': False
            }
        })
        
    except Error as e:
        return jsonify({
            'status': 'error',
            'code': 500,
            'message': f'資料庫查詢錯誤: {str(e)}'
        }), 500

@app.route('/v1/menu_items/<int:item_id>', methods=['GET'])
def get_menu_item(item_id):
    """GET /v1/menu_items/<id> - 根據ID獲取單個menu_item"""
    try:
        connection = get_db_connection()
        if connection is None:
            return jsonify({
                'status': 'error',
                'code': 500,
                'message': '無法連接資料庫'
            }), 500
        
        cursor = connection.cursor(dictionary=True)
        cursor.execute("SELECT * FROM menu_items WHERE Id = %s", (item_id,))
        result = cursor.fetchone()
        
        cursor.close()
        connection.close()
        
        if result is None:
            return jsonify({
                'status': 'error',
                'code': 404,
                'message': '找不到指定的menu_item'
            }), 404
        
        return jsonify({
            'status': 'success',
            'code': 200,
            'message': '查詢成功',
            'data': result
        })
        
    except Error as e:
        return jsonify({
            'status': 'error',
            'code': 500,
            'message': f'資料庫查詢錯誤: {str(e)}'
        }), 500

@app.route('/v1/menu_items', methods=['POST'])
def create_menu_item():
    """POST /v1/menu_items - 創建新的menu_item"""
    try:
        data = request.get_json()
        
        # 驗證必要欄位
        required_fields = ['name', 'email', 'age']
        for field in required_fields:
            if field not in data or not data[field]:
                return jsonify({
                    'status': 'error',
                    'code': 400,
                    'message': f'缺少必要欄位: {field}'
                }), 400
        
        # 驗證email格式
        if not validate_email(data['email']):
            return jsonify({
                'status': 'error',
                'code': 400,
                'message': 'email格式不正確'
            }), 400
        
        # 驗證年齡範圍
        if not validate_age(data['age']):
            return jsonify({
                'status': 'error',
                'code': 400,
                'message': '年齡必須是1-120之間的數字'
            }), 400
        
        connection = get_db_connection()
        if connection is None:
            return jsonify({
                'status': 'error',
                'code': 500,
                'message': '無法連接資料庫'
            }), 500
        
        cursor = connection.cursor()
        
        # 使用參數化查詢避免SQL注入
        query = """
            INSERT INTO menu_items (main_course, side_dish, addon, Order_Date, is_active)
            VALUES (%s, %s, %s, %s, %s)
        """
        
        # 這裡使用範例資料，您可以根據實際需求調整
        params = (
            data.get('main_course', '預設主餐'),
            data.get('side_dish', '預設副餐'),
            data.get('addon', '預設湯品'),
            datetime.now().strftime('%Y-%m-%d'),
            True
        )
        
        cursor.execute(query, params)
        connection.commit()
        
        # 獲取新創建的資料ID
        new_id = cursor.lastrowid
        
        cursor.close()
        connection.close()
        
        return jsonify({
            'status': 'success',
            'code': 201,
            'message': '創建成功',
            'data': {
                'id': new_id,
                'main_course': params[0],
                'side_dish': params[1],
                'addon': params[2],
                'Order_Date': params[3],
                'is_active': params[4]
            }
        }), 201
        
    except Error as e:
        return jsonify({
            'status': 'error',
            'code': 500,
            'message': f'資料庫操作錯誤: {str(e)}'
        }), 500

@app.route('/v1/menu_items/<int:item_id>', methods=['PATCH'])
def update_menu_item(item_id):
    """PATCH /v1/menu_items/<id> - 更新menu_item的任一欄位"""
    try:
        data = request.get_json()
        
        # 檢查是否有可更新的欄位
        allowed_fields = ['main_course', 'side_dish', 'addon', 'Order_Date', 'is_active']
        update_fields = {}
        
        for field in allowed_fields:
            if field in data:
                update_fields[field] = data[field]
        
        if not update_fields:
            return jsonify({
                'status': 'error',
                'code': 400,
                'message': '沒有提供可更新的欄位'
            }), 400
        
        connection = get_db_connection()
        if connection is None:
            return jsonify({
                'status': 'error',
                'code': 500,
                'message': '無法連接資料庫'
            }), 500
        
        cursor = connection.cursor(dictionary=True)
        
        # 檢查資料是否存在
        cursor.execute("SELECT * FROM menu_items WHERE Id = %s", (item_id,))
        if cursor.fetchone() is None:
            cursor.close()
            connection.close()
            return jsonify({
                'status': 'error',
                'code': 404,
                'message': '找不到指定的menu_item'
            }), 404
        
        # 建立更新查詢
        set_clause = ", ".join([f"{field} = %s" for field in update_fields.keys()])
        query = f"UPDATE menu_items SET {set_clause} WHERE Id = %s"
        
        params = list(update_fields.values())
        params.append(item_id)
        
        cursor.execute(query, params)
        connection.commit()
        
        # 獲取更新後的資料
        cursor.execute("SELECT * FROM menu_items WHERE Id = %s", (item_id,))
        updated_data = cursor.fetchone()
        
        cursor.close()
        connection.close()
        
        return jsonify({
            'status': 'success',
            'code': 200,
            'message': '更新成功',
            'data': updated_data
        })
        
    except Error as e:
        return jsonify({
            'status': 'error',
            'code': 500,
            'message': f'資料庫操作錯誤: {str(e)}'
        }), 500

@app.route('/v1/menu_items/<int:item_id>', methods=['DELETE'])
def delete_menu_item(item_id):
    """DELETE /v1/menu_items/<id> - 刪除menu_item"""
    try:
        connection = get_db_connection()
        if connection is None:
            return jsonify({
                'status': 'error',
                'code': 500,
                'message': '無法連接資料庫'
            }), 500
        
        cursor = connection.cursor()
        
        # 檢查資料是否存在
        cursor.execute("SELECT * FROM menu_items WHERE Id = %s", (item_id,))
        if cursor.fetchone() is None:
            cursor.close()
            connection.close()
            return jsonify({
                'status': 'error',
                'code': 404,
                'message': '找不到指定的menu_item'
            }), 404
        
        # 執行刪除
        cursor.execute("DELETE FROM menu_items WHERE Id = %s", (item_id,))
        connection.commit()
        
        cursor.close()
        connection.close()
        
        return '', 204
        
    except Error as e:
        return jsonify({
            'status': 'error',
            'code': 500,
            'message': f'資料庫操作錯誤: {str(e)}'
        }), 500

@app.errorhandler(404)
def not_found(error):
    return jsonify({
        'status': 'error',
        'code': 404,
        'message': 'API端點不存在'
    }), 404

@app.errorhandler(500)
def internal_error(error):
    return jsonify({
        'status': 'error',
        'code': 500,
        'message': '伺服器內部錯誤'
    }), 500

@app.route('/menu-form')
def menu_form():
    return render_template('menu_form.html')

if __name__ == '__main__':
    print("啟動Flask API伺服器...")
    print("可用端點:")
    print("  GET    /v1/menu_items")
    print("  GET    /v1/menu_items/<id>")
    print("  POST   /v1/menu_items")
    print("  PATCH  /v1/menu_items/<id>")
    print("  DELETE /v1/menu_items/<id>")
    app.run(debug=True, host='0.0.0.0', port=5000)