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

# --- 從環境變數讀取資料庫連線資訊 ---
DB_CONFIG = {
    'user': os.environ.get('DB_USER', 'A023'),
    'password': os.environ.get('DB_PASSWORD', 'UkrHhx76kCCM'),
    'host': os.environ.get('DB_HOST', 'mysql.theaken.com'),
    'port': int(os.environ.get('DB_PORT', 33306)),
    'database': os.environ.get('DB_NAME', 'db_A023'),
}

# --- 建立應用程式與啟用 CORS ---
app = Flask(__name__)
CORS(app)

# --- 建立資料庫連線池 ---
connection_pool = None
try:
    connection_pool = mysql.connector.pooling.MySQLConnectionPool(
        pool_name="api_pool",
        pool_size=5,
        **DB_CONFIG
    )
    print("資料庫連線池建立成功！")
except mysql.connector.Error as err:
    print(f"資料庫連線池建立失敗: {err}")

# --- 統一回應格式的輔助函式 ---
def create_standard_response(status, data, message, code):
    """建立標準的 JSON 回應，包含 data 欄位"""
    return jsonify({
        "status": status,
        "code": code,
        "message": message,
        "data": data
    }), code

def create_order_success_response(message, order_id, code):
    """建立符合訂單成功需求的特殊 JSON 回應"""
    return jsonify({
        "status": "success",
        "message": message,
        "order_id": order_id
    }), code

def create_error_response(message, code):
    """建立標準的錯誤 JSON 回應"""
    return jsonify({
        "status": "error",
        "code": code,
        "message": message
    }), code

# --- API Endpoint: /menu (GET) ---
@app.route('/menu', methods=['GET'])
def get_menu_by_date():
    menu_date_str = request.args.get('date')

    if not menu_date_str:
        return create_error_response("The 'date' parameter is required.", 400)

    try:
        datetime.strptime(menu_date_str, '%Y-%m-%d')
    except ValueError:
        return create_error_response("Invalid date format. Please use YYYY-MM-DD.", 400)

    if not connection_pool:
        return create_error_response("Database connection not available.", 503)

    conn = None
    cursor = None
    try:
        conn = connection_pool.get_connection()
        cursor = conn.cursor(dictionary=True)

        query = ("SELECT main_course, side_dish, addon "
                 "FROM menu_items WHERE menu_date = %s")
        
        cursor.execute(query, (menu_date_str,))
        menu_items = cursor.fetchall()

        if not menu_items:
            return create_standard_response("success", [], "no menu found", 200)
        
        return create_standard_response("success", menu_items, "Menu retrieved successfully.", 200)

    except mysql.connector.Error as err:
        return create_error_response(f"Database error: {err}", 500)
    except Exception as e:
        return create_error_response(f"An unexpected error occurred: {e}", 500)
    finally:
        if cursor:
            cursor.close()
        if conn and conn.is_connected():
            conn.close()

# --- API Endpoint: /order (POST) ---
@app.route('/order', methods=['POST'])
def create_order():
    """建立一筆新的訂單"""
    if not request.is_json:
        return create_error_response("Invalid content type, must be application/json", 415)

    data = request.get_json()
    
    required_fields = ['employee_id', 'employee_name', 'order_date', 'quantity']
    missing_fields = [field for field in required_fields if field not in data]
    if missing_fields:
        return create_error_response(f"Missing required fields: {', '.join(missing_fields)}", 400)

    try:
        order_date = data['order_date']
        quantity = int(data['quantity'])
        datetime.strptime(order_date, '%Y-%m-%d')
        if quantity <= 0:
            raise ValueError("Quantity must be a positive integer.")
    except (ValueError, TypeError):
        return create_error_response("Invalid data format for date or quantity.", 400)

    if not connection_pool:
        return create_error_response("Database connection not available.", 503)

    conn = None
    cursor = None
    try:
        conn = connection_pool.get_connection()
        cursor = conn.cursor()

        # 設定當前連線的時區為 UTC+8
        cursor.execute("SET time_zone = '+8:00'")

        # 1. 根據 order_date 查詢 menu_item_id
        query_menu = "SELECT id FROM menu_items WHERE menu_date = %s LIMIT 1"
        cursor.execute(query_menu, (order_date,))
        menu_item = cursor.fetchone()

        if not menu_item:
            return create_error_response(f"No menu found for date {order_date}", 404)
        
        menu_item_id = menu_item[0]

        # 2. 將訂單資料寫入資料庫
        # 使用 NOW() 函數，因為連線時區已設定
        add_order_query = ("INSERT INTO employee_order "
                           "(emp_id, name, order_date, menu_item_id, order_qty, update_by, update_at) "
                           "VALUES (%s, %s, %s, %s, %s, %s, NOW())")
        order_data = (
            data['employee_id'],
            data['employee_name'],
            order_date,
            menu_item_id,
            quantity,
            data['employee_id']
        )
        cursor.execute(add_order_query, order_data)
        new_order_id = cursor.lastrowid

        conn.commit()

        return create_order_success_response("新增成功", new_order_id, 201)

    except mysql.connector.Error as err:
        if conn: conn.rollback()
        return create_error_response(f"Database error: {err}", 500)
    except Exception as e:
        if conn: conn.rollback()
        return create_error_response(f"An unexpected error occurred: {e}", 500)
    finally:
        if cursor:
            cursor.close()
        if conn and conn.is_connected():
            conn.close()

# --- 全域錯誤處理 ---
@app.errorhandler(404)
def not_found(error):
    return create_error_response("Resource not found.", 404)

@app.errorhandler(405)
def method_not_allowed(error):
    return create_error_response("Method not allowed.", 405)

# --- 啟動應用程式 ---
if __name__ == '__main__':
    port = int(os.environ.get('PORT', 5000))
    app.run(host='0.0.0.0', port=port, debug=False)