"""Conservative Hinglish order engine. All decisions remain reviewable."""
import hashlib
import json
import re
import uuid
from datetime import datetime, timedelta, timezone
from db import audit, now, one, rows
from scheduling import next_delivery
from scheduling import IST

def phone(value):
    value = re.sub(r'[\s+()\-]', '', str(value))
    if not re.fullmatch(r'\d{8,15}', value):
        raise ValueError('Use an international phone number with 8–15 digits')
    return value

def receive(db, number_id, sender, body, external_id=None, kind='text', raw=None, name=None):
    sender = phone(sender)
    number = one(db,'SELECT * FROM whatsapp_numbers WHERE id=? AND business_id=1 AND active=1',(number_id,))
    if not number:
        raise ValueError('Unknown business WhatsApp number')
    external_id = external_id or 'sim-' + uuid.uuid4().hex
    existing = one(db,'SELECT id FROM messages WHERE external_id=?',(external_id,))
    if existing:
        return existing['id']
    db.execute('INSERT OR IGNORE INTO customers(business_id,name,phone) VALUES(1,?,?)',(name or sender,sender))
    customer = one(db,'SELECT * FROM customers WHERE business_id=1 AND phone=?',(sender,))
    db.execute('INSERT OR IGNORE INTO conversations(business_id,customer_id,number_id) VALUES(1,?,?)',(customer['id'],number_id))
    conv = one(db,'SELECT * FROM conversations WHERE customer_id=? AND number_id=?',(customer['id'],number_id))
    db.execute('UPDATE conversations SET last_inbound_at=? WHERE id=?',(now(),conv['id']))
    mid = db.execute('INSERT INTO messages(conversation_id,external_id,direction,kind,body,raw_json,created_at) VALUES(?,?,\'in\',?,?,?,?)',(conv['id'],external_id,kind,str(body)[:10000],json.dumps(raw or {},ensure_ascii=False),now())).lastrowid
    audit(db,'Message Received','messages',mid)
    return mid

def reply(db, conv_id, body):
    mid = db.execute('INSERT INTO messages(conversation_id,direction,body,state,created_at) VALUES(?,\'out\',?,\'queued\',?)',(conv_id,body,now())).lastrowid
    db.execute('INSERT INTO outbox(conversation_id,message_id,payload_json,next_attempt_at) VALUES(?,?,?,?)',(conv_id,mid,json.dumps({'text':body}),now()))
    return mid

def review(db, mid, reason, conv_id=None):
    db.execute('UPDATE messages SET state=\'review\' WHERE id=?',(mid,))
    db.execute('INSERT INTO notifications(business_id,category,body,message_id,created_at) VALUES(1,\'Needs Human Review\',?,?,?)',(reason,mid,now()))
    audit(db,'Human Review Required','messages',mid,{'reason':reason})
    if conv_id:
        reply(db,conv_id,'Aapka message mil gaya. Details verify karne ke liye hamari team aapse baat karegi.')

def find_area(db, text):
    normalized = text.lower().strip(' .!?')
    found = []
    for a in rows(db,'SELECT * FROM areas WHERE business_id=1'):
        names = [a['name']] + a['aliases'].split(',')
        if any(n.strip() and re.search(r'(?<!\w)'+re.escape(n.strip().lower())+r'(?!\w)', normalized) for n in names):
            found.append(a)
    return found[0] if len(found)==1 else None

def classify(text):
    t = text.lower()
    if re.search(r'cancel|रद्द|mat bhej|nahi chahiye',t): return 'ORDER_CANCEL'
    if re.search(r'change|update|badal|बदल',t): return 'ORDER_UPDATE'
    if re.search(r'price|rate|कीमत|kitne ka',t): return 'PRICE_QUERY'
    if re.search(r'payment|paid|पैसे|paisa|bhugtan',t): return 'PAYMENT_QUERY'
    if re.search(r'kab|when|delivery|कब',t): return 'DELIVERY_QUERY'
    if re.search(r'available|stock|product|मिलेगा',t): return 'PRODUCT_QUERY'
    if re.search(r'bhej|chahiye|order|send|repeat|same|maal|चाहिए|भेज|ऑर्डर|pichli|last wala',t) or re.search(r'\d',t): return 'NEW_ORDER'
    if re.search(r'hello|hi\b|namaste|नमस्ते',t): return 'GENERAL_QUERY'
    return 'UNKNOWN'

def extract(db, text):
    """Only exact master/alias matches; ambiguous and unconsumed content is reviewed."""
    products = rows(db,'SELECT * FROM products WHERE business_id=1 AND active=1')
    segments = re.split(r'\s+(?:aur|and|और|te)\s+|[,;\n]',text.lower())
    items, issues = [], []
    for segment in segments:
        candidates = []
        for p in products:
            aliases = [p['name']] + p['aliases'].split(',')
            for alias in aliases:
                alias = alias.strip().lower()
                if alias and re.search(r'(?<!\w)'+re.escape(alias)+r'(?!\w)',segment):
                    candidates.append(p)
                    break
        if not candidates:
            if segment.strip(): issues.append('Unknown product or unparsed text: '+segment)
            continue
        if len(candidates)!=1:
            issues.append('Ambiguous product: '+segment)
            continue
        qty = re.findall(r'(?<!\w)(\d+(?:\.\d+)?)(?!\w)',segment)
        if len(qty)!=1 or not 0<float(qty[0])<=100000:
            issues.append('Please specify one positive quantity: '+segment)
            continue
        p=candidates[0]
        units=re.findall(r'\b(box(?:es)?|cartons?|pcs|pieces?|bottles?|kg|litres?|liters?|pack(?:s)?)\b',segment)
        unit=units[0] if len(units)==1 else p['unit']
        unit={'boxes':'box','cartons':'carton','pieces':'pcs','bottles':'bottle','packs':'pack'}.get(unit,unit)
        if unit!=p['unit']:
            issues.append('Unit '+unit+' does not match catalogue unit '+p['unit']+' for '+p['name'])
            continue
        remainder=segment
        for alias in sorted([p['name']]+p['aliases'].split(','),key=len,reverse=True):
            if alias.strip(): remainder=re.sub(r'(?<!\w)'+re.escape(alias.strip().lower())+r'(?!\w)',' ',remainder)
        for a in rows(db,'SELECT name,aliases FROM areas WHERE business_id=1'):
            for alias in [a['name']]+a['aliases'].split(','):
                if alias.strip(): remainder=re.sub(r'(?<!\w)'+re.escape(alias.strip().lower())+r'(?!\w)',' ',remainder)
        remainder=re.sub(r'\b\d+(?:\.\d+)?\b|\b(?:box(?:es)?|cartons?|pcs|pieces?|bottles?|kg|litres?|liters?|packs?)\b',' ',remainder)
        remainder=re.sub(r'\b(?:sir|bhai|please|pls|bhej|bhejna|dena|do|de|chahiye|send|order|book|kar|kardo|karna|hai|me|mein|to|at|on|ji|maal|aap|mujhe|kal|today|tomorrow|wala|wali|gadi|gaadi|ka|ki|ke|laga)\b|चाहिए|भेज|देना|दो|सर',' ',remainder)
        if re.sub(r'[\s.!?]+','',remainder):
            issues.append('Unclear text requires verification: '+remainder.strip())
            continue
        # Every product segment must identify exactly one known item and quantity.
        items.append({'product_id':p['id'],'quantity':float(qty[0]),'unit':p['unit']})
    if len({i['product_id'] for i in items})!=len(items):
        issues.append('Product appears more than once; staff must combine quantities')
    return items,issues

def validated_items(db, items):
    if not isinstance(items,list) or not items or len(items)>100:
        raise ValueError('Add at least one product (maximum 100)')
    result=[]
    for i in items:
        p=one(db,'SELECT * FROM products WHERE id=? AND business_id=1 AND active=1',(int(i['product_id']),))
        q=float(i['quantity'])
        if not p or not 0<q<=100000: raise ValueError('Unknown product or invalid quantity')
        if i.get('unit',p['unit'])!=p['unit']: raise ValueError('Product unit mismatch')
        result.append({'product_id':p['id'],'quantity':q,'unit':p['unit'],'price':p['price']})
    if len({i['product_id'] for i in result})!=len(result): raise ValueError('Combine duplicate products')
    return result

def create_order(db, customer_id, items, number_id=None, message_id=None, notes='', actor='system', override=None, allow_duplicate=False):
    # Serialize duplicate checks with insertion across concurrent staff requests.
    if not db.in_transaction:
        db.execute('BEGIN IMMEDIATE')
    c=one(db,'SELECT * FROM customers WHERE id=? AND business_id=1',(customer_id,))
    if not c or not c['area_id']: raise ValueError('Customer needs a saved delivery area')
    stop=one(db,'SELECT * FROM route_stops WHERE area_id=?',(c['area_id'],))
    if not stop: raise ValueError('Area has no route')
    route_id=int((override or {}).get('route_id') or stop['route_id'])
    route=one(db,'SELECT id FROM routes WHERE id=? AND business_id=1 AND active=1',(route_id,))
    if not route: raise ValueError('Route is unavailable')
    date=(override or {}).get('delivery_date') or next_delivery(db,route_id)
    if not re.fullmatch(r'\d{4}-\d{2}-\d{2}',date): raise ValueError('Delivery date must be YYYY-MM-DD')
    if datetime.fromisoformat(date).date()<datetime.now(IST).date(): raise ValueError('Delivery date cannot be in the past')
    items=validated_items(db,items)
    fp=hashlib.sha256(json.dumps(sorted([(i['product_id'],i['quantity'],i['unit']) for i in items]),sort_keys=True).encode()).hexdigest()
    window=int(one(db,"SELECT value FROM settings WHERE business_id=1 AND key='duplicate_minutes'")['value'])
    duplicate=one(db,'SELECT id FROM orders WHERE customer_id=? AND fingerprint=? AND created_at>=? AND status!=\'Cancelled\'',(customer_id,fp,(datetime.now(timezone.utc)-timedelta(minutes=window)).isoformat()))
    if duplicate and not allow_duplicate: raise ValueError('Possible Duplicate Order #'+str(duplicate['id'])+'; staff approval required')
    oid=db.execute('INSERT INTO orders(business_id,customer_id,number_id,message_id,area_id,route_id,delivery_date,source,notes,fingerprint,created_at) VALUES(1,?,?,?,?,?,?,?,?,?,?)',(customer_id,number_id,message_id,c['area_id'],route_id,date,'WhatsApp' if number_id else 'manual',notes,fp,now())).lastrowid
    for i in items:
        db.execute('INSERT INTO order_items(order_id,product_id,quantity,unit,price) VALUES(?,?,?,?,?)',(oid,i['product_id'],i['quantity'],i['unit'],i['price']))
    db.execute('INSERT INTO deliveries(order_id,updated_at) VALUES(?,?)',(oid,now()))
    audit(db,'Order Created','orders',oid,{'delivery_date':date,'route_id':route_id,'duplicate_override':allow_duplicate},actor)
    if number_id:
        conv=one(db,'SELECT id FROM conversations WHERE customer_id=? AND number_id=?',(customer_id,number_id))
        area=one(db,'SELECT name FROM areas WHERE id=?',(c['area_id'],))
        template=one(db,"SELECT body FROM message_templates WHERE business_id=1 AND name='confirmation'")['body']
        body=template.format(customer_name=c['name'],order_id=oid,area=area['name'],delivery_day=datetime.fromisoformat(date).strftime('%A'),delivery_date=date)
        mid=reply(db,conv['id'],body)
        if actor!='system':
            db.execute('UPDATE outbox SET payload_json=? WHERE message_id=?',(json.dumps({'text':body,'manual':True}),mid))
    return oid

def process(db, mid):
    m=one(db,'SELECT * FROM messages WHERE id=?',(mid,))
    if not m or m['state']!='pending' or m['direction']!='in': return
    conv=one(db,'SELECT * FROM conversations WHERE id=?',(m['conversation_id'],))
    c=one(db,'SELECT * FROM customers WHERE id=?',(conv['customer_id'],))
    automation=one(db,"SELECT value FROM settings WHERE business_id=1 AND key='automation'")['value']
    if conv['takeover'] or automation!='true':
        db.execute("UPDATE messages SET state='human' WHERE id=?",(mid,)); return
    if m['kind']!='text':
        review(db,mid,'Media/location message retained; manual verification required',conv['id']); return
    text=m['body'].strip()
    pending=json.loads(conv['pending_json']) if conv['pending_json'] else None
    intent=classify(text)
    analysis=None
    import os
    if not pending and os.environ.get('OLLAMA_MODEL'):
        from llm import understand
        try:
            analysis=understand(db,text)
            intent=analysis['intent']
        except Exception:
            review(db,mid,'AI service failed or returned invalid data; raw message retained',conv['id']); return
        if analysis['confidence']<float(os.environ.get('AI_CONFIDENCE_THRESHOLD','0.90')) or analysis['issues']:
            db.execute('UPDATE messages SET intent=?,confidence=? WHERE id=?',(intent,analysis['confidence'],mid))
            review(db,mid,'AI uncertainty: '+'; '.join(analysis['issues']),conv['id']); return
    confidence=analysis['confidence'] if analysis else (0.95 if intent!='UNKNOWN' else 0.2)
    db.execute('UPDATE messages SET intent=?,confidence=? WHERE id=?',(intent,confidence,mid))
    audit(db,'Message Classification','messages',mid,{'intent':intent,'mode':'AI' if analysis else 'rules'})
    if intent in ('ORDER_CANCEL','ORDER_UPDATE'):
        db.execute('UPDATE conversations SET pending_json=NULL WHERE id=?',(conv['id'],))
        review(db,mid,'Customer requests an order change/cancellation',conv['id']); return
    if pending and pending.get('stage')=='area':
        a=find_area(db,text)
        if not a:
            reply(db,conv['id'],'Area catalogue mein match nahi hua. Kripya exact area batayein.'); review(db,mid,'Unknown or ambiguous delivery area'); return
        db.execute('UPDATE customers SET area_id=? WHERE id=?',(a['id'],c['id']))
        pending['stage']='confirm' if c['address'] else 'address'
    elif pending and pending.get('stage')=='address':
        if len(text)<8 or len(text)>2000:
            reply(db,conv['id'],'Kripya poora shop address aur landmark bhejein.')
            db.execute("UPDATE messages SET state='done' WHERE id=?",(mid,));return
        db.execute('UPDATE customers SET address=? WHERE id=?',(text,c['id']))
        pending['stage']='confirm'
    elif pending and pending.get('stage')=='confirm':
        if text.lower() in ('yes','y','haan','han','ha','हाँ','confirm','ok','okay'):
            try:
                oid=create_order(db,c['id'],pending['items'],conv['number_id'],mid,notes=pending.get('notes',''))
                db.execute('UPDATE conversations SET pending_json=NULL WHERE id=?',(conv['id'],))
                db.execute("UPDATE messages SET state='done' WHERE id=?",(mid,)); return oid
            except ValueError as e:
                db.execute('UPDATE conversations SET pending_json=NULL WHERE id=?',(conv['id'],))
                review(db,mid,str(e),conv['id']); return
        elif text.lower() in ('no','nahi','nahin','नहीं'):
            db.execute('UPDATE conversations SET pending_json=NULL WHERE id=?',(conv['id'],))
            reply(db,conv['id'],'Draft cancelled. Naya order bhej sakte hain.')
            db.execute("UPDATE messages SET state='done' WHERE id=?",(mid,)); return
        else:
            review(db,mid,'Reply is not explicit confirmation; pending draft retained',conv['id']); return
    else:
        if intent=='NEW_ORDER':
            if (analysis and analysis['repeat']) or re.search(r'same|repeat|last wala|pichli|पिछल',text.lower()):
                previous=one(db,"SELECT id FROM orders WHERE customer_id=? AND status IN ('Confirmed','Scheduled','Out for Delivery','Delivered','Rescheduled') ORDER BY id DESC LIMIT 1",(c['id'],))
                if not previous:
                    reply(db,conv['id'],'Previous order nahi mila. Product aur quantity bhejein.'); review(db,mid,'Repeat requested with no previous order'); return
                items=rows(db,'SELECT product_id,quantity,unit FROM order_items WHERE order_id=?',(previous['id'],))
                issues=[]
            else:
                items,issues=(analysis['items'],analysis['issues']) if analysis else extract(db,text)
            if issues or not items:
                review(db,mid,'; '.join(issues) or 'Missing products/quantities',conv['id']); return
            a=one(db,'SELECT * FROM areas WHERE id=? AND business_id=1',(analysis['area_id'],)) if analysis and analysis['area_id'] else find_area(db,text)
            if a:
                db.execute('UPDATE customers SET area_id=? WHERE id=?',(a['id'],c['id']))
                c['area_id']=a['id']
            pending={'stage':('confirm' if c['address'] else 'address') if c['area_id'] else 'area','items':items,'notes':text}
        elif intent=='DELIVERY_QUERY':
            order=one(db,'SELECT * FROM orders WHERE customer_id=? ORDER BY id DESC LIMIT 1',(c['id'],))
            reply(db,conv['id'],f"Order #{order['id']}: {order['status']}. Expected delivery {order['delivery_date']}." if order else 'Abhi koi order nahi mila.')
        elif intent=='PRICE_QUERY':
            catalogue=rows(db,'SELECT name,price,unit FROM products WHERE business_id=1 AND active=1 LIMIT 20')
            reply(db,conv['id'],'\n'.join(f"{p['name']}: INR {p['price']:g}/{p['unit']}" for p in catalogue) or 'Team price list share karegi.')
        elif intent=='GENERAL_QUERY':
            reply(db,conv['id'],'Namaste! Order ke liye product naam aur quantity bhejein.')
        else:
            review(db,mid,'Requires staff response: '+intent,conv['id']); return
    if pending:
        if pending['stage']=='area':
            reply(db,conv['id'],'Sure. Aapka delivery area/location kya hai?')
        elif pending['stage']=='address':
            reply(db,conv['id'],'Kripya poora shop address aur landmark bhejein. Address save ho jayega.')
        else:
            try:
                validated_items(db,pending['items'])
                customer=one(db,'SELECT * FROM customers WHERE id=?',(c['id'],))
                stop=one(db,'SELECT route_id FROM route_stops WHERE area_id=?',(customer['area_id'],))
                if not stop: raise ValueError('Area has no delivery route')
                date=next_delivery(db,stop['route_id'])
                summary=[]
                for i in pending['items']:
                    p=one(db,'SELECT name FROM products WHERE id=?',(i['product_id'],))
                    summary.append(f"{i['quantity']:g} {i['unit']} × {p['name']}")
                reply(db,conv['id'],'Please confirm:\n'+'\n'.join(summary)+'\nExpected delivery: '+date+'\nReply YES / HAAN to book, NO to cancel.')
            except ValueError as e:
                review(db,mid,str(e),conv['id']); return
        db.execute('UPDATE conversations SET pending_json=? WHERE id=?',(json.dumps(pending),conv['id']))
    db.execute("UPDATE messages SET state='done' WHERE id=?",(mid,))

def ingest_event(db, payload):
    for entry in payload.get('entry',[]):
        for change in entry.get('changes',[]):
            value=change.get('value',{})
            pid=value.get('metadata',{}).get('phone_number_id')
            number=one(db,'SELECT * FROM whatsapp_numbers WHERE phone_number_id=? AND active=1',(pid,))
            for status in value.get('statuses',[]):
                out=one(db,'SELECT * FROM outbox WHERE external_id=?',(status.get('id'),))
                if out:
                    db.execute('UPDATE messages SET state=? WHERE id=?',(status.get('status','unknown'),out['message_id']))
                    audit(db,'WhatsApp Delivery Status','messages',out['message_id'],status)
            if value.get('messages') and not number:
                raise ValueError('Received event for unconfigured phone_number_id '+str(pid))
            for msg in value.get('messages',[]):
                kind=msg.get('type','unknown')
                body=msg.get('text',{}).get('body') if kind=='text' else json.dumps(msg.get(kind,{}),ensure_ascii=False)
                contacts=value.get('contacts',[])
                name=contacts[0].get('profile',{}).get('name') if contacts else None
                receive(db,number['id'],msg['from'],body or '',msg['id'],kind,msg,name)