from flask import Flask, render_template, request, jsonify
import mysql.connector
import requests
from bs4 import BeautifulSoup
import json
from datetime import datetime
import re
import urllib3

# 禁用SSL警�?
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

app = Flask(__name__)

# Database configuration from DB_connection.txt
DB_CONFIG = {
    'host': 'mysql.theaken.com',
    'port': 33306,
    'database': 'db_A019',
    'user': 'A019',
    'password': '9wvKEkxBzVca'
}

def get_db_connection():
    """Establish database connection"""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        return conn
    except mysql.connector.Error as e:
        print(f"Database connection error: {e}")
        return None

def create_menu_table():
    """Create menu_items table if not exists"""
    conn = get_db_connection()
    if conn:
        try:
            cursor = conn.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS menu_items (
                    Id INTEGER AUTO_INCREMENT PRIMARY KEY,
                    main_course VARCHAR(50) NOT NULL,
                    side_dish VARCHAR(50),
                    addon VARCHAR(50),
                    is_active BOOLEAN NOT NULL
                )
            """)
            conn.commit()
            print("Menu table created or already exists")
        except mysql.connector.Error as e:
            print(f"Error creating table: {e}")
        finally:
            conn.close()

def scrape_menu_data():
    """Scrape menu data from the target URL"""
    url = "https://club.panjit.com.tw/back/menu/menu.php?1672970133&indexselectid=1"
    
    try:
        # Bypass SSL verification for the target website
        response = requests.get(url, timeout=10, verify=False)
        response.raise_for_status()
        
        soup = BeautifulSoup(response.content, 'html.parser')
        
        # ?��?實�?網�?結�?�???�單資�?
        menu_items = []
        
        # ?�找?�單表格?��?�?        menu_tables = soup.find_all('table')
        menu_lists = soup.find_all(['ul', 'ol'])
        
        # 如�??�到表格結�?
        if menu_tables:
            for table in menu_tables:
                rows = table.find_all('tr')
                for row in rows:
                    cells = row.find_all('td')
                    if len(cells) >= 2:  # ?��??��?稱�??�格
                        name = cells[0].get_text(strip=True)
                        price_text = cells[1].get_text(strip=True)
                        
                        # ?��??�格?��?
                        price = 0.0
                        try:
                            # 從�?字中?��??��?
                            price_match = re.search(r'\d+', price_text)
                            if price_match:
                                price = float(price_match.group())
                        except:
                            pass
                        
                        if name and price > 0:
                            menu_items.append({
                                'name': name,
                                'category': '主�?',
                                'price': price,
                                'description': f'{name} - 美味餐�?',
                                'image_url': f'/static/images/{name.replace(" ", "_").lower()}.jpg'
                            })
        
        # 如�??�到?�表結�?
        elif menu_lists:
            for menu_list in menu_lists:
                items = menu_list.find_all('li')
                for item in items:
                    text = item.get_text(strip=True)
                    if text and any(char.isdigit() for char in text):
                        # ?�試�???�目?�稱?�價??                        # 尋找?�格模�?
                        price_match = re.search(r'(\$|NT\$|\$)?\s*(\d+)', text)
                        if price_match:
                            price = float(price_match.group(2))
                            name = re.sub(r'(\$|NT\$|\$)?\s*\d+', '', text).strip()
                            
                            if name and price > 0:
                                menu_items.append({
                                    'name': name,
                                    'category': '餐�?',
                                    'price': price,
                                    'description': f'{name} - 精選美�?',
                                    'image_url': f'/static/images/{name.replace(" ", "_").lower()}.jpg'
                                })
        
        # 如�?以�??��??��??�到，使?��??�方案�??��??�含?�單?��??�div?�span
        if not menu_items:
            # 尋找?�能?�含?�單?�目?��?�?            possible_menu_elements = soup.find_all(['div', 'span', 'p'])
            for elem in possible_menu_elements:
                text = elem.get_text(strip=True)
                if text and len(text) > 3 and any(char.isdigit() for char in text):
                    price_match = re.search(r'(\$|NT\$|\$)?\s*(\d+)', text)
                    if price_match:
                        price = float(price_match.group(2))
                        name = re.sub(r'(\$|NT\$|\$)?\s*\d+.*', '', text).strip()
                        
                        if name and price > 0 and len(name) > 1:
                            menu_items.append({
                                'name': name,
                                'category': '餐�?',
                                'price': price,
                                'description': f'{name} - 餐廳?�薦',
                                'image_url': f'/static/images/{name.replace(" ", "_").lower()}.jpg'
                            })
        
        # 如�?仍然沒�??�到?�單?�目，使?��?例�???        if not menu_items:
            menu_items = [
                {
                    'name': '經典?��?�?,
                    'category': '主�?',
                    'price': 120.00,
                    'description': '?�統?�灣?��?麵�?湯頭濃�?',
                    'image_url': '/static/images/beef_noodle.jpg'
                },
                {
                    'name': '?�腿�?,
                    'category': '主�?',
                    'price': 95.00,
                    'description': '香�??�腿?��???,
                    'image_url': '/static/images/chicken_rice.jpg'
                },
                {
                    'name': '?��?沙�?',
                    'category': '?��?',
                    'price': 65.00,
                    'description': '?�鮮?�蔬?��??�製?��?',
                    'image_url': '/static/images/salad.jpg'
                },
                {
                    'name': '紅�??��???,
                    'category': '主�?',
                    'price': 150.00,
                    'description': '?�統江�??��??�質鮮嫩',
                    'image_url': '/static/images/lion_head.jpg'
                },
                {
                    'name': '?��???,
                    'category': '?��?',
                    'price': 60.00,
                    'description': '?�令?��?清�?',
                    'image_url': '/static/images/vegetable.jpg'
                }
            ]
        
        return menu_items
        
    except requests.RequestException as e:
        print(f"Error scraping menu data: {e}")
        # 返�?範�?資�?作為?�用
        return [
            {
                'name': '經典?��?�?,
                'category': '主�?',
                'price': 120.00,
                'description': '?�統?�灣?��?麵�?湯頭濃�?',
                'image_url': '/static/images/beef_noodle.jpg'
            },
            {
                'name': '?�腿�?,
                'category': '主�?',
                'price': 95.00,
                'description': '香�??�腿?��???,
                'image_url': '/static/images/chicken_rice.jpg'
            }
        ]

def insert_menu_data():
    """Insert sample menu items into database"""
    conn = get_db_connection()
    if conn:
        try:
            cursor = conn.cursor()
            
            # Clear existing data
            cursor.execute("DELETE FROM menu_items")
            
            # Insert sample data that matches the new table structure
            sample_menu_items = [
                ('經典?��?�?, '?�工麵�?', '?��?�?, True),
                ('香�??�腿�?, '?�令?��?', '?�米濃湯', True),
                ('紅�??��???, '?�飯', '紫�??�花�?, True),
                ('?��??�麵', None, '?��?�?, True),
                ('?�鮭魚�?', '馬鈴?�泥', '?��?�?, True)
            ]
            
            insert_query = """
                INSERT INTO menu_items (main_course, side_dish, addon, is_active)
                VALUES (%s, %s, %s, %s)
            """
            
            for item in sample_menu_items:
                cursor.execute(insert_query, item)
            
            conn.commit()
            print(f"Inserted {len(sample_menu_items)} menu items")
            
        except mysql.connector.Error as e:
            print(f"Error inserting menu data: {e}")
        finally:
            conn.close()

@app.route('/')
def index():
    """Main page - display menu items"""
    conn = get_db_connection()
    menu_items = []
    
    if conn:
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT * FROM menu_items ORDER BY category, name")
            menu_items = cursor.fetchall()
        except mysql.connector.Error as e:
            print(f"Error fetching menu items: {e}")
        finally:
            conn.close()
    
    return render_template('index.html', menu_items=menu_items)

@app.route('/api/menu')
def api_menu():
    """API endpoint to get menu items"""
    conn = get_db_connection()
    menu_items = []
    
    if conn:
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT * FROM menu_items ORDER BY category, name")
            menu_items = cursor.fetchall()
        except mysql.connector.Error as e:
            print(f"Error fetching menu items: {e}")
        finally:
            conn.close()
    
    return jsonify(menu_items)

@app.route('/init')
def initialize():
    """Initialize database with menu data"""
    create_menu_table()
    insert_menu_data()
    return "Database initialized with sample menu data"

if __name__ == '__main__':
    # Initialize database on startup
    create_menu_table()
    
    # Check if menu items exist, if not, insert sample data
    conn = get_db_connection()
    if conn:
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM menu_items")
            count = cursor.fetchone()[0]
            if count == 0:
                insert_menu_data()
        except mysql.connector.Error as e:
            print(f"Error checking menu items: {e}")
        finally:
            conn.close()
    
    app.run(debug=True, port=5000)