import sys
import json
import re
import os

# Detect available MySQL driver — safe fallback for shared hosting
_driver = None
_driver_name = 'none'

try:
    import mysql.connector
    _driver = mysql.connector
    _driver_name = 'connector'
except ImportError:
    pass

if _driver is None:
    try:
        import pymysql
        _driver = pymysql
        _driver_name = 'pymysql'
    except ImportError:
        pass

if _driver is None:
    try:
        import MySQLdb
        _driver = MySQLdb
        _driver_name = 'MySQLdb'
    except ImportError:
        pass


def connect_db(host='127.0.0.1', port=3306, user='root', password='', database='fms_production'):
    if _driver is None:
        print(json.dumps({"error": "No MySQL driver found. Please install pymysql: pip3 install --user pymysql"}))
        sys.stdout.flush()
        sys.exit(1)
    try:
        conn = _driver.connect(
            host=host,
            port=int(port),
            user=user,
            passwd=password,
            db=database
        )
        return conn
    except Exception as e:
        print(json.dumps({"error": f"DB connection failed ({_driver_name}): {str(e)}"}))
        sys.stdout.flush()
        sys.exit(1)

def clean_text(text):
    """Remove trailing punctuation, extra whitespace, and common query noise."""
    text = re.sub(r'[^\w\s-]', ' ', text).strip()
    # Remove common noise words at the end of queries
    noise_pattern = r'\b(anything|any|done|action|operation|performed|please|today|now|is|are|remains?|status|health)\b'
    text = re.sub(noise_pattern, '', text, flags=re.I).strip()
    return text

def extract_entity(query, keywords):
    """Extract entity by splitting on the last keyword and cleaning noise."""
    # Combine intent keywords with common query helpers
    delimiters = list(set(keywords) | {'how', 'many', 'stock', 'remain', 'remains', 'for', 'of', 'in', 'about', 'did', 'do', 'any', 'anything', 'does'})
    pattern = r'\b(?:' + '|'.join(delimiters) + r')\b'
    parts = re.split(pattern, query, flags=re.I)
    
    if len(parts) > 1:
        # Check parts from right to left for a non-empty entity
        for i in range(len(parts)-1, 0, -1):
            entity = clean_text(parts[i])
            if entity and len(entity) > 1: # Ignore single letter leftovers
                return entity
    
    # Fallback: take the last words
    words = query.split()
    if not words: return ""
    return clean_text(" ".join(words[-2:]) if len(words) > 1 else words[-1])

def handle_all_stock(cursor):
    """Return stock levels for all products."""
    cursor.execute("""
        SELECT p.name, p.sku, p.unit_of_measure, 
               SUM(CASE WHEN sm.type = 'in' THEN sm.quantity ELSE -sm.quantity END) as current_stock
        FROM products p
        LEFT JOIN stock_movements sm ON p.id = sm.product_id
        GROUP BY p.id, p.name, p.sku, p.unit_of_measure
        HAVING current_stock IS NOT NULL
        ORDER BY p.name ASC
    """)
    rows = cursor.fetchall()
    
    if not rows:
        return "There are no products with recorded stock movements in the system."
    
    resp = "📋 **Current Stock Levels (All Products):**\n"
    for name, sku, uom, stock in rows:
        resp += f"- **{name}** ({sku}): **{stock:,.2f} {uom}**\n"
    
    return resp

def handle_stock(cursor, query):
    if any(x in query.lower() for x in ['each', 'all product', 'every product', 'everything']):
        return handle_all_stock(cursor)

    entity = extract_entity(query, ['product', 'of', 'for', 'remains', 'item', 'sku', 'stock'])
    
    # Improved multi-term matching
    terms = entity.split()
    if len(terms) > 1:
        # Try finding exact match first or multi-LIKE
        placeholders = " AND ".join(["name LIKE %s" for _ in terms])
        cursor.execute(f"SELECT id, name, sku, unit_of_measure FROM products WHERE ({placeholders}) OR sku LIKE %s LIMIT 1", 
                       [*[f"%{t}%" for t in terms], f"%{entity}%"])
    else:
        cursor.execute("SELECT id, name, sku, unit_of_measure FROM products WHERE name LIKE %s OR sku LIKE %s LIMIT 1", 
                       (f"%{entity}%", f"%{entity}%"))
    
    product = cursor.fetchone()
    if not product:
        return f"I couldn't find a product matching '{entity}' in the system."
    
    pid, name, sku, uom = product
    cursor.execute("""
        SELECT SUM(CASE WHEN type = 'in' THEN quantity ELSE -quantity END) 
        FROM stock_movements 
        WHERE product_id = %s
    """, (pid,))
    stock = cursor.fetchone()[0] or 0
    
    return f"The current stock for **{name}** (SKU: {sku}) is **{stock:,.2f} {uom}**."

def handle_consumption(cursor, query):
    entity = extract_entity(query, ['product', 'in', 'for', 'of', 'consumption'])
    if not entity: return "Please specify a product name."
    
    cursor.execute("SELECT id, name FROM products WHERE name LIKE %s LIMIT 1", (f"%{entity}%",))
    product = cursor.fetchone()
    if not product: return f"Product '{entity}' not found."
    
    pid, name = product
    cursor.execute("""
        SELECT p.name, bi.quantity, p.unit_of_measure 
        FROM bom_items bi 
        JOIN boms b ON bi.bom_id = b.id 
        JOIN products p ON bi.product_id = p.id 
        WHERE b.product_id = %s AND b.is_active = 1
    """, (pid,))
    items = cursor.fetchall()
    
    if not items: return f"Product **{name}** has no active BOM."
    
    resp = f"Consumption for **{name}**:\n"
    for item_name, qty, uom in items:
        resp += f"- **{item_name}**: {qty:.4f} {uom}\n"
    return resp

def handle_risks(cursor):
    """Analyze production risks, machine health, and critical alerts."""
    # 1. Critical Alerts
    cursor.execute("""
        SELECT type, severity, title, message, created_at 
        FROM production_risk_alerts 
        WHERE severity = 'critical' OR severity = 'warning'
        ORDER BY created_at DESC LIMIT 3
    """)
    alerts = cursor.fetchall()

    # 2. High Risk Productions (Predictions)
    cursor.execute("""
        SELECT p.name, pp.delay_probability, pp.risk_level, pp.predicted_completion_at
        FROM production_predictions pp
        JOIN work_orders wo ON pp.work_order_id = wo.id
        JOIN products p ON wo.product_id = p.id
        WHERE pp.risk_level = 'high'
        ORDER BY pp.delay_probability DESC LIMIT 3
    """)
    high_risk_orders = cursor.fetchall()

    # 3. Critical Machine Health
    cursor.execute("""
        SELECT m.name, mhs.health_score, mhs.failure_probability, mhs.recommendation
        FROM machine_health_scores mhs
        JOIN machines m ON mhs.machine_id = m.id
        WHERE mhs.health_score < 50 OR mhs.failure_probability > 70
        ORDER BY mhs.health_score ASC LIMIT 3
    """)
    critical_machines = cursor.fetchall()

    if not alerts and not high_risk_orders and not critical_machines:
        return "🛡️ **System Status**: All production metrics and assets are currently within safe operating parameters. No major risks detected."

    resp = "⚠️ **Risk Analysis Report:**\n"
    
    if alerts:
        resp += "\n🚨 **Critical Alerts:**\n"
        for rtype, sev, title, msg, date in alerts:
            resp += f"- **[{sev.upper()}] {title}**: {msg} ({date.strftime('%Y-%m-%d')})\n"

    if high_risk_orders:
        resp += "\n📈 **High Risk Work Orders:**\n"
        for name, prob, level, date in high_risk_orders:
            resp += f"- **{name}**: **{prob}%** delay probability. Predicted finish: {date.strftime('%Y-%m-%d')}\n"

    if critical_machines:
        resp += "\n⚙️ **Critical Asset Health:**\n"
        for name, score, prob, rec in critical_machines:
            resp += f"- **{name}**: Health Score: **{score}%** (Failure Prob: {prob}%). *Rec: {rec}*\n"

    return resp

def handle_user_actions(cursor, query):
    entity = extract_entity(query, ['user', 'from', 'by', 'of', 'did'])
    if not entity: return "Please specify a user name."
    
    cursor.execute("SELECT id, name FROM users WHERE name LIKE %s LIMIT 1", (f"%{entity}%",))
    user = cursor.fetchone()
    if not user: return f"User '{entity}' not found."
    
    uid, name = user
    cursor.execute("SELECT type, quantity, reference_type, created_at FROM stock_movements WHERE user_id = %s ORDER BY created_at DESC LIMIT 5", (uid,))
    actions = cursor.fetchall()
    
    if not actions: return f"No recent operations found for user **{name}**."
    
    resp = f"Recent actions by **{name}**:\n"
    for m_type, qty, ref, date in actions:
        action_str = "Added" if m_type == 'in' else "Removed"
        resp += f"- {date.strftime('%M d, H:i')}: **{action_str}** {qty:,.2f} via {ref}\n"
    return resp

def handle_fleet_load(cursor, query):
    """Analyze products loaded in vehicles."""
    lower_query = query.lower()
    
    # Identify most loaded product by aggregation
    cursor.execute("""
        SELECT p.name, p.sku, SUM(fl.weight) as total_weight, SUM(fl.quantity) as total_qty, p.unit_of_measure
        FROM fleet_loadings fl
        JOIN products p ON fl.product_id = p.id
        GROUP BY p.id, p.name, p.sku, p.unit_of_measure
        ORDER BY total_qty DESC, total_weight DESC
        LIMIT 3
    """)
    top_products = cursor.fetchall()
    
    if not top_products:
        return "No products have been recorded as loaded in vehicles yet."
    
    resp = "🚛 **Top Loaded Products in Fleet:**\n"
    for name, sku, weight, qty, uom in top_products:
        resp += f"- **{name}** ({sku}): **{qty:,.2f} {uom}** (Total Weight: {weight:,.2f} kg)\n"
    
    return resp

def handle_fleet(cursor, query):
    """Analyze fleet status and trip completion."""
    lower_query = query.lower()
    
    if any(x in lower_query for x in ['load', 'cargo', 'shipping', 'transported']):
        return handle_fleet_load(cursor, query)
    
    if any(x in lower_query for x in ['reach', 'destination', 'finish', 'arrival', 'complete']):
        # Find trips that aren't 'Delivered' or 'Completed'
        # Based on schema, 'Pending' is the default not-yet-reached status
        cursor.execute("""
            SELECT f.vehicle_number, f.driver_name, fl.destination, fl.status, fl.load_date
            FROM fleet_loadings fl
            JOIN fleets f ON fl.fleet_id = f.id
            WHERE fl.status NOT IN ('Delivered', 'Completed', 'concluded')
            ORDER BY fl.load_date DESC
        """)
        pending = cursor.fetchall()
        
        if not pending:
            return "All active vehicle trips have successfully reached their destinations."
        
        resp = "🚚 **Vehicles that haven't reached their destination:**\n"
        for vec, driver, dest, status, date in pending:
            resp += f"- **{vec}** ({driver}): Currently **{status}** on way to **{dest}** (Loaded: {date.strftime('%Y-%m-%d')})\n"
        return resp
    
    else:
        # General fleet status
        cursor.execute("SELECT vehicle_number, driver_name, vehicle_type, status, last_location FROM fleets LIMIT 5")
        vehicles = cursor.fetchall()
        if not vehicles: return "No fleet data found."
        
        resp = "🚛 **Current Fleet Overview:**\n"
        for vec, driver, vtype, status, loc in vehicles:
            resp += f"- **{vec}** ({vtype}): **{status}** at {loc} (Driver: {driver})\n"
        return resp

def handle_workload(cursor):
    """Identify the biggest workload by production line and product."""
    # Busiest Production Line
    cursor.execute("""
        SELECT pl.name, COUNT(wo.id) as active_orders
        FROM production_lines pl
        JOIN work_orders wo ON pl.id = wo.production_line_id
        WHERE wo.status = 'in_progress'
        GROUP BY pl.id
        ORDER BY active_orders DESC
        LIMIT 1
    """)
    busiest_line = cursor.fetchone()
    
    # Product with most active orders
    cursor.execute("""
        SELECT p.name, COUNT(wo.id) as active_orders
        FROM products p
        JOIN work_orders wo ON p.id = wo.product_id
        WHERE wo.status = 'in_progress'
        GROUP BY p.id
        ORDER BY active_orders DESC
        LIMIT 1
    """)
    busiest_product = cursor.fetchone()
    
    if not busiest_line and not busiest_product:
        return "There are currently no active work orders (in_progress) in the system."
    
    resp = "📊 **Current Workload Analysis:**\n"
    if busiest_line:
        resp += f"- **Busiest Production Line**: **{busiest_line[0]}** with **{busiest_line[1]}** active work orders.\n"
    if busiest_product:
        resp += f"- **Most Active Product**: **{busiest_product[0]}** with **{busiest_product[1]}** orders currently in production.\n"
    
    return resp

def handle_performance(cursor, query):
    """Identify best production line or machine."""
    lower_query = query.lower()
    
    if 'machine' in lower_query:
        # Best machine based on health score and total work orders (active + completed)
        cursor.execute("""
            SELECT m.name, m.model, IFNULL(mhs.health_score, 0) as health, COUNT(wo.id) as total_orders
            FROM machines m
            LEFT JOIN machine_health_scores mhs ON m.id = mhs.machine_id
            LEFT JOIN work_orders wo ON m.id = wo.machine_id
            GROUP BY m.id
            ORDER BY total_orders DESC, health DESC
            LIMIT 1
        """)
        best = cursor.fetchone()
        if not best: return "I don't have enough performance data for machines yet."
        return f"The best performing machine is **{best[0]}** ({best[1]}) with **{best[3]}** total work orders and a health score of **{best[2]}%**."
    
    else:
        # Best production line based on total work orders (active + completed)
        cursor.execute("""
            SELECT pl.name, pl.capacity, COUNT(wo.id) as total_orders
            FROM production_lines pl
            LEFT JOIN work_orders wo ON pl.id = wo.production_line_id
            GROUP BY pl.id
            ORDER BY total_orders DESC
            LIMIT 1
        """)
        best = cursor.fetchone()
        if not best: return "I don't have enough performance data for production lines yet."
        return f"The best production line is **{best[0]}** with **{best[2]}** total work orders and an hourly capacity of **{best[1]}** units."

def handle_forecast(cursor, query):
    """Predict which raw materials will run out soon."""
    # 1. Get all raw materials
    cursor.execute("SELECT id, name, unit_of_measure FROM products WHERE category = 'raw_material'")
    raw_materials = cursor.fetchall()
    
    shortages = []
    
    for pid, name, uom in raw_materials:
        # 2. Get current stock
        cursor.execute("""
            SELECT SUM(CASE WHEN type = 'in' THEN quantity ELSE -quantity END) 
            FROM stock_movements 
            WHERE product_id = %s
        """, (pid,))
        stock = cursor.fetchone()[0] or 0
        
        # 3. Calculate avg daily consumption (last 7 days of 'out' movements)
        cursor.execute("""
            SELECT SUM(quantity) / 7.0 
            FROM stock_movements 
            WHERE product_id = %s AND type = 'out' 
            AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        """, (pid,))
        daily_avg = cursor.fetchone()[0] or 0
        
        # 4. Predict time remaining
        if daily_avg > 0:
            days_left = stock / daily_avg
            if days_left < 3:
                shortages.append({
                    "name": name,
                    "stock": stock,
                    "uom": uom,
                    "days": days_left
                })
        elif stock <= 0:
            # Special case: already empty
            shortages.append({
                "name": name,
                "stock": 0,
                "uom": uom,
                "days": 0
            })
            
    if not shortages:
        return "Good news! All raw materials are well-stocked for the next 3 days based on current consumption rates."
    
    resp = "⚠️ **Raw Material Shortage Alert (3-Day Forecast):**\n"
    for s in shortages:
        if s['days'] == 0:
            resp += f"- **{s['name']}**: **Out of Stock!** (0 {s['uom']} remaining)\n"
        else:
            resp += f"- **{s['name']}**: Finishes in **{s['days']:.1f} days** ({s['stock']:.2f} {s['uom']} left)\n"
    
    resp += "\n*Forecast based on average consumption from the last 7 days.*"
    return resp

def handle_structure(cursor, query):
    """Analyze enterprise structure (companies, factories, branches)."""
    lower_query = query.lower()
    
    if 'company' in lower_query or 'companies' in lower_query:
        cursor.execute("SELECT name, address, status FROM companies")
        rows = cursor.fetchall()
        if not rows: return "No companies found in the system."
        resp = "🏢 **Companies Overview:**\n"
        for name, addr, status in rows:
            resp += f"- **{name}**: {status.capitalize()} ({addr or 'No address'})\n"
        return resp
    
    if 'factory' in lower_query or 'factories' in lower_query:
        cursor.execute("SELECT f.name, c.name, f.status FROM factories f JOIN companies c ON f.company_id = c.id")
        rows = cursor.fetchall()
        if not rows: return "No factories found in the system."
        resp = "🏭 **Factories Overview:**\n"
        for name, company, status in rows:
            resp += f"- **{name}**: {company} - {status.capitalize()}\n"
        return resp

    if 'branch' in lower_query or 'branches' in lower_query:
        cursor.execute("SELECT name, location FROM branches")
        rows = cursor.fetchall()
        if not rows: return "No branches found in the system."
        resp = "📍 **Branches Overview:**\n"
        for name, loc in rows:
            resp += f"- **{name}**: {loc or 'No location'}\n"
        return resp
    
    return "I can tell you about our **companies**, **factories**, or **branches**. Which would you like to know about?"

def handle_maintenance(cursor, query):
    """Analyze maintenance schedules and logs."""
    lower_query = query.lower()
    
    if any(x in lower_query for x in ['schedule', 'next', 'tomorrow', 'upcoming']):
        cursor.execute("""
            SELECT m.name, ms.description, ms.scheduled_date, ms.status 
            FROM maintenance_schedules ms 
            JOIN machines m ON ms.machine_id = m.id 
            WHERE ms.status = 'scheduled' AND ms.scheduled_date >= DATE(NOW())
            ORDER BY ms.scheduled_date ASC LIMIT 5
        """)
        rows = cursor.fetchall()
        if not rows: return "There are no maintenance tasks scheduled for the near future."
        resp = "🛠️ **Upcoming Maintenance:**\n"
        for machine, desc, date, status in rows:
            resp += f"- **{machine}**: {desc} (Scheduled: {date.strftime('%Y-%m-%d')})\n"
        return resp
    
    # Default to last logs
    cursor.execute("""
        SELECT m.name, ml.technician_notes, ml.start_time, ml.end_time 
        FROM maintenance_logs ml 
        JOIN machines m ON ml.machine_id = m.id 
        ORDER BY ml.start_time DESC LIMIT 5
    """)
    rows = cursor.fetchall()
    if not rows: return "No maintenance logs found."
    resp = "📋 **Recent Maintenance Logs:**\n"
    for machine, notes, start, end in rows:
        period = f"{start.strftime('%Y-%m-%d')}"
        resp += f"- **{machine}**: {notes or 'Periodic Maintenance'} ({period})\n"
    return resp

def handle_products(cursor, query):
    """Analyze product catalog and BOM."""
    lower_query = query.lower()
    
    cursor.execute("SELECT name, sku, category, status FROM products LIMIT 5")
    rows = cursor.fetchall()
    if not rows: return "No products found in the catalog."
    
    resp = "📦 **Product Catalog Highlights:**\n"
    for name, sku, cat, status in rows:
        status_text = "Active" if status else "Inactive"
        resp += f"- **{name}** ({sku}): {cat} - {status_text}\n"
    return resp

def handle_warehouses(cursor, query):
    """Analyze warehouses."""
    cursor.execute("SELECT name, location, type FROM warehouses")
    rows = cursor.fetchall()
    if not rows: return "No warehouses found."
    
    resp = "🏠 **Warehouse Status:**\n"
    for name, loc, wtype in rows:
        resp += f"- **{name}**: {loc or 'Main Site'} ({wtype})\n"
    return resp

def handle_quality(cursor, query):
    """Analyze quality check results."""
    cursor.execute("""
        SELECT p.name, qc.status, qc.checked_quantity, qc.passed_quantity, qc.failed_quantity, qc.created_at, qc.notes 
        FROM quality_checks qc 
        JOIN products p ON qc.product_id = p.id 
        ORDER BY qc.created_at DESC LIMIT 5
    """)
    rows = cursor.fetchall()
    if not rows: return "No quality checks found."
    
    resp = "🛡️ **Recent Quality Inspections:**\n"
    for name, result, checked, passed, failed, date, notes in rows:
        res_emoji = "✅" if result.lower() == 'pass' else "❌"
        resp += f"- {res_emoji} **{name}**: **{result.upper()}** - {passed}/{checked} units passed ({date.strftime('%Y-%m-%d')})\n"
        if notes: resp += f"  - *Note: {notes}*\n"
    return resp

def handle_orders(cursor, query):
    """Analyze Purchase and Sales Orders."""
    lower_query = query.lower()
    
    if 'purchase' in lower_query:
        cursor.execute("""
            SELECT po.po_number, pr.name, po.total_amount, po.status, po.order_date 
            FROM purchase_orders po 
            JOIN partners pr ON po.partner_id = pr.id 
            ORDER BY po.order_date DESC LIMIT 5
        """)
        rows = cursor.fetchall()
        if not rows: return "No purchase orders found."
        resp = "🛒 **Recent Purchase Orders:**\n"
        for num, partner, total, status, date in rows:
            resp += f"- **{num}**: {partner} - ${total:,.2f} (**{status.capitalize()}**) on {date.strftime('%Y-%m-%d')}\n"
        return resp
    
    if 'sales' in lower_query or 'sell' in lower_query:
        cursor.execute("""
            SELECT so.so_number, pr.name, so.total_amount, so.status, so.order_date 
            FROM sales_orders so 
            JOIN partners pr ON so.partner_id = pr.id 
            ORDER BY so.order_date DESC LIMIT 5
        """)
        rows = cursor.fetchall()
        if not rows: return "No sales orders found."
        resp = "📈 **Recent Sales Orders:**\n"
        for num, partner, total, status, date in rows:
            resp += f"- **{num}**: {partner} - ${total:,.2f} (**{status.capitalize()}**) on {date.strftime('%Y-%m-%d')}\n"
        return resp
        
    return "I can help with **Purchase Orders** or **Sales Orders**. Specify which one you need!"

def handle_partners(cursor, query):
    """Analyze partner directory."""
    cursor.execute("SELECT name, type, email, status FROM partners LIMIT 5")
    rows = cursor.fetchall()
    if not rows: return "No partners found."
    
    resp = "🤝 **Partner Directory Overview:**\n"
    for name, ptype, email, status in rows:
        status_str = "Active" if status else "Inactive"
        resp += f"- **{name}** ({ptype}): {email or 'No email'} - {status_str}\n"
    return resp

def handle_help():
    """Provide a summary of capabilities."""
    return """
👋 **How can I assist you today?** I'm your ERP AI Agent. You can ask me about:

📊 **Inventory**: "stock of each product", "consumption for Iron Ore"
📊 **Inventory**: "stock of each product", "warehouse locations"
🔮 **Forecasting**: "materials that will finish in 3 days"
🏭 **Production**: "busiest production line", "product catalog"
⚠️ **Risks**: "what is the most risk?", "system alerts"
🛠️ **Maintenance**: "next maintenance schedule", "recent machine logs"
🛡️ **Quality**: "recent quality checks", "failed inspections"
🤝 **Supply Chain**: "show our partners", "recent sales orders"
🚛 **Fleet**: "vehicle status", "most loaded products"
🏢 **Structure**: "show me all factories" or "company status"
👤 **Users**: "what did admin do today?"

Just type your question naturally!
"""

def handle_greeting(query):
    """Handle small talk and greetings."""
    lower_query = query.lower()
    # Use word boundaries to avoid matching 'hi' in 'this'
    if any(re.search(rf'\b{x}\b', lower_query) for x in ['hello', 'hi', 'hey', 'morning', 'afternoon', 'evening']):
        return "Hello! I'm your Enterprise Knowledge Assistant. How can I help you manage the plant today? 👋"
    if any(x in lower_query for x in ['how are you', 'how is it going']):
        return "I'm performing at peak efficiency! Ready to analyze any data you need. 🚀"
    return None

def handle_email(query, cursor):
    """Detect email intent and prepare data for Laravel to send."""
    lower_query = query.lower()
    
    # 1. Extract Email Recipient
    email_match = re.search(r'[\w\.-]+@[\w\.-]+\.\w+', query)
    recipient = email_match.group(0) if email_match else None
    
    if not recipient:
        return "I couldn't find a valid email address in your request. Please specify who I should send the mail to (e.g., 'send mail to test@example.com')."

    # 2. Extract Body / Content
    # Remove the core keywords and the email itself to find the content
    content = query
    # Remove common phrases
    for phrase in [r'send\s+mail\s+to', r'email\s+to', r'mail\s+to', r'send\s+email\s+to', r'email']:
        content = re.sub(phrase, '', content, flags=re.IGNORECASE).strip()
    
    # Remove the email address
    content = content.replace(recipient, '').strip()
    
    # Remove connecting words at start/end
    content = re.sub(r'^(?:saying|about|that|is|to)\s+', '', content, flags=re.IGNORECASE).strip()
    content = re.sub(r'\s+(?:to|saying|about)$', '', content, flags=re.IGNORECASE).strip()

    if not content or len(content) < 3:
        content = "System status update requested."

    # 3. System Summary enrichment for short content
    if len(content) < 15 or 'status' in content.lower():
        cursor.execute("SELECT COUNT(*) FROM work_orders WHERE status='in_progress'")
        active_wo = cursor.fetchone()[0]
        cursor.execute("SELECT COUNT(*) FROM production_risk_alerts WHERE is_read=0")
        unread_alerts = cursor.fetchone()[0]
        summary = f"System Update:\n- Active Work Orders: {active_wo}\n- Unread Risk Alerts: {unread_alerts}"
        content = f"{summary}\n\nNote: {content}"

    return {
        "action": "send_email",
        "recipient": recipient,
        "subject": "AI Assistant: System Notification",
        "body": content,
        "confirmation": f"I've prepared the email for **{recipient}**. Sending it now! 📧"
    }

def main():
    if len(sys.argv) < 2:
        print(json.dumps({"error": "No query provided"}))
        return

    user_query = sys.argv[1].strip()

    # DB credentials passed as CLI args: message host port database username password
    db_host     = sys.argv[2] if len(sys.argv) > 2 else '127.0.0.1'
    db_port     = sys.argv[3] if len(sys.argv) > 3 else '3306'
    db_database = sys.argv[4] if len(sys.argv) > 4 else 'fms_production'
    db_username = sys.argv[5] if len(sys.argv) > 5 else 'root'
    db_password = sys.argv[6] if len(sys.argv) > 6 else ''

    db = connect_db(host=db_host, port=db_port, user=db_username, password=db_password, database=db_database)
    cursor = db.cursor()

    try:
        lower_query = user_query.lower()
        
        # 1. Greetings / Small Talk
        greeting = handle_greeting(user_query)
        if greeting:
            result = greeting
        
        # 2. Intent Detection Logic
        elif any(x in lower_query for x in ['mail', 'email', 'send to']):
            result = handle_email(user_query, cursor)
        elif any(x in lower_query for x in ['help', 'can you', 'capability', 'what are you']):
            result = handle_help()
        elif any(x in lower_query for x in ['maintenance', 'schedule', 'cmms', 'repair', 'broken']):
            result = handle_maintenance(cursor, user_query)
        elif any(x in lower_query for x in ['risk', 'alert', 'warning', 'critical', 'danger']):
            result = handle_risks(cursor)
        elif any(x in lower_query for x in ['quality', 'inspection', 'check', 'pass', 'fail']):
            result = handle_quality(cursor, user_query)
        elif any(x in lower_query for x in ['company', 'companies', 'factory', 'factories', 'branch', 'structure', 'zone']):
            result = handle_structure(cursor, user_query)
        elif any(x in lower_query for x in ['product', 'sku', 'catalog', 'item']):
            result = handle_products(cursor, user_query)
        elif any(x in lower_query for x in ['warehouse', 'storage', 'site']):
            result = handle_warehouses(cursor, user_query)
        elif any(x in lower_query for x in ['partner', 'supplier', 'customer', 'directory']):
            result = handle_partners(cursor, user_query)
        elif any(x in lower_query for x in ['purchase', 'sales', 'order']):
            result = handle_orders(cursor, user_query)
        elif any(x in lower_query for x in ['vehicle', 'fleet', 'truck', 'driver', 'destination', 'load', 'cargo']):
            result = handle_fleet(cursor, user_query)
        elif any(x in lower_query for x in ['workload', 'busy', 'busiest', 'load']):
            result = handle_workload(cursor)
        elif any(x in lower_query for x in ['best', 'performing', 'performance', 'efficiency', 'top']):
            result = handle_performance(cursor, user_query)
        elif any(x in lower_query for x in ['finish', 'shortage', 'run out', 'less than', 'prediction', 'forecast']):
            result = handle_forecast(cursor, user_query)
        elif any(x in lower_query for x in ['stock', 'remain', 'how many', 'inventory']):
            result = handle_stock(cursor, user_query)
        elif any(x in lower_query for x in ['consume', 'material', 'bom', 'consumption']):
            result = handle_consumption(cursor, user_query)
        elif any(x in lower_query for x in ['action', 'operation', 'user', 'did']):
            result = handle_user_actions(cursor, user_query)
        else:
            result = "I'm not exactly sure about that yet. Try asking about **stock**, **maintenance**, **quality**, or **fleet status**. Type **'help'** for more info!"
        
        # Always return JSON now to handle structured actions
        if isinstance(result, str):
            print(json.dumps({"response": result}))
        else:
            print(json.dumps(result))
            
    finally:
        cursor.close()
        db.close()

if __name__ == "__main__":
    main()