# backend.py

from flask import Flask, request, jsonify
from flask_cors import CORS
import re
import random
from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime, Boolean, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os

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

# Set up the database with SQLAlchemy
Base = declarative_base()

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    order_name = Column(String(200), nullable=False)
    order_type = Column(String(100), nullable=False)
    weight_kg = Column(Float, nullable=False)
    pickup_location = Column(String(255), nullable=False)
    delivery_location = Column(String(255), nullable=False)
    shipping_date = Column(String(50), nullable=False)
    payment_method = Column(String(50), nullable=False)
    dimensions = Column(String(100))
    special_instructions = Column(Text)
    created_at = Column(DateTime, default=datetime.now)
    completed = Column(Boolean, default=False)
    
    def __repr__(self):
        return f"<Order(id={self.id}, name={self.order_name}, type={self.order_type})>"

# Initialize database connection
def init_database():
    # MySQL connection string - change these values to match your MySQL setup
    # Format: mysql+pymysql://username:password@hostname/database_name
    connection_string = os.environ.get(
        'DATABASE_URL', 
        'mysql+pymysql://transport_user:transport_pass@localhost/transport_orders'
    )
    
    # Fall back to SQLite if MySQL connection fails
    try:
        engine = create_engine(connection_string)
        Base.metadata.create_all(engine)
    except Exception as e:
        print(f"MySQL connection failed: {e}. Falling back to SQLite.")
        connection_string = "sqlite:///transport_orders.db"
        engine = create_engine(connection_string)
        Base.metadata.create_all(engine)
        
    Session = sessionmaker(bind=engine)
    return Session()

# Initialize database session
db_session = init_database()

# Enhanced location extraction patterns
CITY_NAMES = [
    'New York', 'Los Angeles', 'Chicago', 'Houston', 'Phoenix', 'Philadelphia',
    'San Antonio', 'San Diego', 'Dallas', 'San Jose', 'Austin', 'Jacksonville',
    'Fort Worth', 'Columbus', 'San Francisco', 'Charlotte', 'Indianapolis',
    'Seattle', 'Denver', 'Boston', 'Detroit', 'Nashville', 'Portland', 'Memphis',
    'Atlanta', 'Miami', 'Las Vegas', 'Washington', 'Baltimore', 'Milwaukee'
]

# Create a regex pattern from the city names
CITIES_PATTERN = '|'.join([re.escape(city) for city in CITY_NAMES])

# IMPROVED Helper function to extract data from text input
def extract_order_details(text):
    # Initialize an empty details dictionary
    details = {
        'order_name': None,
        'order_type': None,
        'weight_kg': None,
        'pickup_location': None,
        'delivery_location': None,
        'shipping_date': None,
        'payment_method': None,
        'dimensions': None,
        'special_instructions': None
    }
    
    # Extract customer name - look for common name patterns
    name_match = re.search(r'([A-Z][a-z]+(?: [A-Z][a-z]+)*) (?:has |is |placed |ordered)', text)
    if name_match:
        customer_name = name_match.group(1).strip()
        # Use customer name as part of order name
        details['order_name'] = f"{customer_name}'s Order"
    
    # Extract order type with more flexible pattern
    product_patterns = [
        r'order for(?: a| an)? (.+?)(?:weighing|weight|\.|,)',
        r'ordered(?: a| an)? (.+?)(?:weighing|weight|\.|,)',
        r'shipping(?: a| an)? (.+?)(?:weighing|weight|\.|,)',
        r'ship(?: a| an)? (.+?)(?:weighing|weight|from|to|\.|,)',
        r'send(?: a| an)? (.+?)(?:weighing|weight|from|to|\.|,)',
        r'need to ship(?: a| an)? (.+?)(?:weighing|weight|from|to|\.|,)'
    ]
    
    for pattern in product_patterns:
        order_match = re.search(pattern, text, re.IGNORECASE)
        if order_match:
            details['order_type'] = order_match.group(1).strip()
            break
    
    # Extract weight with more flexible patterns
    weight_patterns = [
        r'weighing (?:approximately |about |around |)(\d+(?:\.\d+)?)(?: kg| kilograms| kilos)',
        r'weight (?:of |is |approximately |about |)(\d+(?:\.\d+)?)(?: kg| kilograms| kilos)',
        r'(\d+(?:\.\d+)?)(?:kg|kilograms|kilos)',
        r'weighs (?:approximately |about |around |)(\d+(?:\.\d+)?)(?: kg| kilograms| kilos)',
        r'weighs about (\d+(?:\.\d+)?)(?: kg| kilograms| kilos)',
        r'about (\d+(?:\.\d+)?)(?:kg|kilograms|kilos)'
    ]
    
    for pattern in weight_patterns:
        weight_match = re.search(pattern, text, re.IGNORECASE)
        if weight_match:
            try:
                details['weight_kg'] = float(weight_match.group(1))
                break
            except:
                pass
    
    # IMPROVED: Extract pickup location with stronger patterns
    pickup_patterns = [
        r'pickup from (.+?)(?:and|will|to|\.|$)',
        r'pick up from (.+?)(?:and|will|to|\.|$)',
        r'shipping from (.+?)(?:and|will|to|\.|$)',
        r'ship from (.+?)(?:and|will|to|\.|$)',
        r'send from (.+?)(?:and|will|to|\.|$)',
        r'from ([^,\.]*?(?:' + CITIES_PATTERN + ')[^,\.]*?)(?:and will|will|to|and|,|\.|$)',
        r'from (.+?)(?:and will be delivered to|to deliver to|and delivery to|to be delivered to|to|\.|$)'
    ]
    
    for pattern in pickup_patterns:
        pickup_match = re.search(pattern, text, re.IGNORECASE)
        if pickup_match:
            pickup_loc = pickup_match.group(1).strip()
            # Make sure pickup location ends with a city name if possible
            for city in CITY_NAMES:
                if city.lower() in pickup_loc.lower():
                    # Ensure the city name is at the end
                    if not pickup_loc.lower().endswith(city.lower()):
                        city_index = pickup_loc.lower().find(city.lower())
                        pickup_loc = pickup_loc[:city_index + len(city)]
            details['pickup_location'] = pickup_loc
            break
    
    # IMPROVED: Extract delivery location with stronger patterns
    delivery_patterns = [
        r'delivered to (.+?)(?:The|\.|\,|$)',
        r'delivery to (.+?)(?:The|\.|\,|$)',
        r'deliver to (.+?)(?:The|\.|\,|$)',
        r'to be delivered to (.+?)(?:The|\.|\,|$)',
        r'to ([^,\.]*?(?:' + CITIES_PATTERN + ')[^,\.]*?)(?:The|\.|,|$)',
        r'to (.+?)(?:The requested shipping|The shipping|The package|\.|,|$)'
    ]
    
    for pattern in delivery_patterns:
        delivery_match = re.search(pattern, text, re.IGNORECASE)
        if delivery_match:
            delivery_loc = delivery_match.group(1).strip()
            # Make sure delivery location ends with a city name if possible
            for city in CITY_NAMES:
                if city.lower() in delivery_loc.lower():
                    # Ensure the city name is at the end
                    if not delivery_loc.lower().endswith(city.lower()):
                        city_index = delivery_loc.lower().find(city.lower())
                        delivery_loc = delivery_loc[:city_index + len(city)]
            details['delivery_location'] = delivery_loc
            break
    
    # Extract shipping date with more flexible patterns
    date_patterns = [
        r'shipping date is (.+?)(?:,|\.|$)',
        r'shipping date (.+?)(?:,|\.|$)',
        r'shipped on (.+?)(?:,|\.|$)',
        r'delivered by (.+?)(?:,|\.|$)',
        r'delivery date is (.+?)(?:,|\.|$)',
        r'delivery date (.+?)(?:,|\.|$)',
        r'deliver by (.+?)(?:,|\.|$)',
        r'ship on (.+?)(?:,|\.|$)',
        r'shipping tomorrow',  # Special case for "tomorrow"
        r'shipping today'      # Special case for "today"
    ]
    
    for pattern in date_patterns:
        date_match = re.search(pattern, text, re.IGNORECASE)
        if date_match:
            if "tomorrow" in pattern:
                # Calculate tomorrow's date
                tomorrow = (datetime.now() + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
                details['shipping_date'] = tomorrow
            elif "today" in pattern:
                # Use today's date
                details['shipping_date'] = datetime.now().strftime("%Y-%m-%d")
            else:
                details['shipping_date'] = date_match.group(1).strip()
            break
    
    # Extract payment method with more flexible patterns
    payment_patterns = [
        r'payment method (?:is |selected is |chosen is |)(.+?)(?:\.|\,|The|$)',
        r'paying (?:with |by |using |via |)(.+?)(?:\.|\,|The|$)',
        r'paid (?:with |by |using |via |)(.+?)(?:\.|\,|The|$)',
        r'pay (?:with |by |using |via |)(.+?)(?:\.|\,|The|$)',
        r'payment is (.+?)(?:\.|\,|The|$)'
    ]
    
    for pattern in payment_patterns:
        payment_match = re.search(pattern, text, re.IGNORECASE)
        if payment_match:
            details['payment_method'] = payment_match.group(1).strip()
            break
    
    # Extract dimensions with more flexible patterns
    dimension_patterns = [
        r'dimensions (?:are |is |of |)(.+?)(?:ensuring|to ensure|for|\.|$)',
        r'size (?:of |is |)(.+?)(?:ensuring|to ensure|for|\.|$)',
        r'measuring (.+?)(?:ensuring|to ensure|for|\.|$)',
        r'(\d+(?:\.\d+)?)[xX×](\d+(?:\.\d+)?)[xX×](\d+(?:\.\d+)?)(?: cm| centimeters| inches| in| mm)'
    ]
    
    for pattern in dimension_patterns:
        dim_match = re.search(pattern, text, re.IGNORECASE)
        if dim_match:
            if len(dim_match.groups()) == 3:  # Direct dimensions format like 10x20x30
                details['dimensions'] = f"{dim_match.group(1)}x{dim_match.group(2)}x{dim_match.group(3)}"
            else:
                details['dimensions'] = dim_match.group(1).strip()
            break
    
    # Extract special instructions
    instructions_patterns = [
        r'special instructions,? (.+?)\.',
        r'instructions to (.+?)\.',
        r'requesting (.+?)\.',
        r'requested (.+?)\.',
        r'please (.+?)\.',
        r'handle with care(?: as| because| since)? (.+?)\.',
        r'handle with care'  # Just the phrase itself
    ]
    
    for pattern in instructions_patterns:
        instr_match = re.search(pattern, text, re.IGNORECASE)
        if instr_match:
            if pattern == 'handle with care':
                details['special_instructions'] = "Handle with care"
            else:
                details['special_instructions'] = instr_match.group(1).strip()
            break
    
    # Generate an order name if none provided
    if not details.get('order_name'):
        current_date = datetime.now().strftime("%Y%m%d")
        random_suffix = str(random.randint(1000, 9999))
        details['order_name'] = f"ORDER-{current_date}-{random_suffix}"
    
    return details

# Function to save order to database with validation
def save_order(details):
    try:
        # Check for required fields
        required_fields = ['order_name', 'order_type', 'weight_kg', 
                          'pickup_location', 'delivery_location', 
                          'shipping_date', 'payment_method']
        
        missing_fields = [field for field in required_fields if not details.get(field)]
        
        if missing_fields:
            print(f"Missing required fields: {', '.join(missing_fields)}")
            return None
        
        # Create new order with supplied details
        new_order = Order(
            order_name=details.get('order_name'),
            order_type=details.get('order_type'),
            weight_kg=details.get('weight_kg'),
            pickup_location=details.get('pickup_location'),
            delivery_location=details.get('delivery_location'),
            shipping_date=details.get('shipping_date'),
            payment_method=details.get('payment_method'),
            dimensions=details.get('dimensions'),
            special_instructions=details.get('special_instructions'),
            completed=False
        )
        
        db_session.add(new_order)
        db_session.commit()
        return new_order.id
    except Exception as e:
        db_session.rollback()
        print(f"Error saving order: {e}")
        return None

# Function to get all orders
def get_all_orders():
    orders = db_session.query(Order).all()
    orders_data = []
    
    if orders:
        for order in orders:
            orders_data.append({
                "ID": order.id,
                "Name": order.order_name,
                "Type": order.order_type,
                "Weight (kg)": order.weight_kg,
                "From": order.pickup_location,
                "To": order.delivery_location,
                "Date": order.shipping_date,
                "Payment": order.payment_method,
                "Dimensions": order.dimensions,
                "Instructions": order.special_instructions,
                "Created": order.created_at.strftime("%Y-%m-%d %H:%M") if order.created_at else "Unknown"
            })
    
    return orders_data

# API routes
@app.route('/api/extract', methods=['POST'])
def extract_details():
    data = request.json
    if not data or 'text' not in data:
        return jsonify({"error": "No text provided"}), 400
    
    extracted = extract_order_details(data['text'])
    return jsonify({"details": extracted})

@app.route('/api/save_order', methods=['POST'])
def api_save_order():
    data = request.json
    if not data:
        return jsonify({"error": "No order details provided"}), 400
    
    # Validate required fields
    required_fields = ['order_name', 'order_type', 'weight_kg', 
                      'pickup_location', 'delivery_location', 
                      'shipping_date', 'payment_method']
    
    missing_fields = [field for field in required_fields if not data.get(field)]
    
    if missing_fields:
        return jsonify({
            "error": "Missing required fields", 
            "missing_fields": missing_fields
        }), 400
    
    order_id = save_order(data)
    if order_id:
        return jsonify({"success": True, "order_id": order_id})
    else:
        return jsonify({"error": "Failed to save order"}), 500

@app.route('/api/orders', methods=['GET'])
def api_get_orders():
    orders = get_all_orders()
    return jsonify({"orders": orders})

@app.route('/api/update_field', methods=['POST'])
def update_field():
    data = request.json
    if not data or 'order_id' not in data or 'field' not in data or 'value' not in data:
        return jsonify({"error": "Missing required data"}), 400
    
    try:
        order = db_session.query(Order).filter(Order.id == data['order_id']).first()
        if not order:
            return jsonify({"error": "Order not found"}), 404
        
        # Update the specified field exactly as provided
        setattr(order, data['field'], data['value'])
        db_session.commit()
        return jsonify({"success": True})
    except Exception as e:
        db_session.rollback()
        return jsonify({"error": str(e)}), 500

# Add a debugging endpoint to help diagnose extraction issues
@app.route('/api/debug_extract', methods=['POST'])
def debug_extract():
    data = request.json
    if not data or 'text' not in data:
        return jsonify({"error": "No text provided"}), 400
    
    # Extract details
    extracted = extract_order_details(data['text'])
    
    # Return both the extracted details and the original text for comparison
    return jsonify({
        "original_text": data['text'],
        "extracted_details": extracted,
        "missing_fields": [k for k, v in extracted.items() if v is None]
    })

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