File size: 32,570 Bytes
140a7df
 
ad226b9
140a7df
 
 
 
510ab4d
 
 
ad226b9
140a7df
ad226b9
d3f4497
510ab4d
140a7df
d3f4497
140a7df
 
 
de07700
a3dfc3e
 
de07700
 
a3dfc3e
de07700
a3dfc3e
 
de07700
a3dfc3e
de07700
a3dfc3e
b853b20
de07700
a3dfc3e
d3f4497
 
de07700
 
a3dfc3e
 
de07700
d3f4497
de07700
 
 
2038939
de07700
 
a3dfc3e
 
 
 
d3f4497
a3dfc3e
140a7df
 
 
 
 
a3dfc3e
8df776a
 
 
510ab4d
 
 
 
b853b20
510ab4d
d3f4497
 
510ab4d
 
 
 
 
 
 
 
de07700
 
 
510ab4d
b853b20
de07700
510ab4d
 
 
 
b853b20
de07700
510ab4d
 
d3f4497
510ab4d
a3dfc3e
b853b20
d3f4497
b853b20
 
 
a3dfc3e
d3f4497
b853b20
d3f4497
b853b20
de07700
2038939
 
 
510ab4d
a3dfc3e
 
d3f4497
de07700
2038939
b853b20
 
2038939
 
a3dfc3e
d3f4497
a3dfc3e
510ab4d
 
de07700
510ab4d
a3dfc3e
d3f4497
 
 
a3dfc3e
 
d3f4497
510ab4d
 
be14459
 
 
 
 
 
d3f4497
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2038939
 
 
 
be14459
d3f4497
be14459
7aae18a
 
d3f4497
 
 
2038939
 
d3f4497
 
3c7b93d
2038939
de07700
2038939
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
de07700
140a7df
de07700
140a7df
b853b20
140a7df
 
 
 
 
 
b853b20
 
 
 
 
 
140a7df
 
 
 
 
 
 
 
d3f4497
140a7df
 
 
 
d3f4497
a3dfc3e
 
de07700
d3f4497
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
a3dfc3e
 
b853b20
 
 
a3dfc3e
 
 
 
b853b20
a3dfc3e
 
b853b20
de07700
a3dfc3e
 
b853b20
d3f4497
 
 
b853b20
d3f4497
 
b853b20
d3f4497
 
b853b20
 
 
a3dfc3e
d3f4497
 
 
b853b20
a3dfc3e
 
b853b20
a3dfc3e
 
 
b853b20
de07700
a3dfc3e
 
 
b853b20
a3dfc3e
 
 
 
b853b20
 
 
a3dfc3e
de07700
 
 
 
 
a3dfc3e
d3f4497
 
a3dfc3e
 
 
 
d3f4497
de07700
 
 
 
 
 
 
 
 
 
 
 
 
 
 
a3dfc3e
d3f4497
a3dfc3e
d3f4497
b853b20
d3f4497
b853b20
a3dfc3e
d3f4497
 
 
 
 
 
 
 
 
 
a3dfc3e
b853b20
a3dfc3e
 
 
b853b20
 
 
 
a3dfc3e
b853b20
a3dfc3e
b853b20
de07700
a3dfc3e
 
 
 
b853b20
de07700
a3dfc3e
b853b20
de07700
d3f4497
 
 
 
 
 
b853b20
a3dfc3e
 
b853b20
 
 
140a7df
510ab4d
b853b20
140a7df
b853b20
 
 
 
 
 
 
 
 
 
ad226b9
b853b20
140a7df
 
510ab4d
b853b20
 
 
 
140a7df
b853b20
140a7df
 
 
a3dfc3e
b853b20
 
 
 
 
 
 
 
510ab4d
 
140a7df
b853b20
 
 
7aae18a
140a7df
 
b853b20
140a7df
510ab4d
 
140a7df
 
 
 
 
 
 
 
 
 
510ab4d
1129f23
140a7df
 
 
b853b20
140a7df
 
b853b20
 
 
a3dfc3e
de07700
a3dfc3e
d3f4497
 
a3dfc3e
d3f4497
 
a3dfc3e
b853b20
 
 
 
 
d3f4497
 
 
 
a3dfc3e
d3f4497
b853b20
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
de07700
b853b20
 
 
 
 
 
 
 
 
a3dfc3e
b853b20
 
 
a3dfc3e
 
 
b853b20
 
 
 
a3dfc3e
 
 
b853b20
 
 
a3dfc3e
de07700
a3dfc3e
 
b853b20
a3dfc3e
 
b853b20
a3dfc3e
b853b20
 
 
140a7df
510ab4d
 
8df776a
140a7df
de07700
d3f4497
 
 
 
 
140a7df
d3f4497
b853b20
 
 
 
d3f4497
de07700
d3f4497
b853b20
 
 
 
 
2038939
de07700
 
2038939
b853b20
 
 
 
 
140a7df
de07700
be14459
510ab4d
b853b20
 
 
 
 
 
140a7df
8df776a
be14459
 
510ab4d
8df776a
140a7df
 
de07700
7aae18a
 
 
 
 
 
be14459
7aae18a
de07700
7aae18a
be14459
7aae18a
 
 
be14459
 
de07700
be14459
 
 
de07700
a3dfc3e
510ab4d
d3f4497
 
 
b853b20
2038939
b853b20
 
2038939
 
b853b20
de07700
2038939
 
 
 
140a7df
 
 
b853b20
de07700
2038939
a3dfc3e
 
 
 
 
 
 
 
 
140a7df
 
 
 
 
 
d3f4497
 
 
 
 
de07700
 
2038939
de07700
 
 
2038939
de07700
2038939
de07700
 
 
2038939
de07700
2038939
 
de07700
 
 
 
 
2038939
de07700
2038939
de07700
 
 
2038939
de07700
 
2038939
de07700
 
2038939
de07700
 
 
 
 
2038939
de07700
b853b20
2038939
 
 
de07700
b853b20
2038939
 
b853b20
 
de07700
2038939
b853b20
 
 
dc1be60
b853b20
dc1be60
2038939
 
 
 
dc1be60
2038939
 
 
dc1be60
2038939
 
 
 
 
b853b20
2038939
 
 
 
 
 
 
b853b20
2038939
 
 
 
 
 
b853b20
2038939
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
import os
import sqlite3
import pandas as pd
import json
import uuid
from datetime import datetime
import re
import html
from typing import Optional

import streamlit as st
from groq import Groq

# ================== PAGE CONFIG (set before any UI) ==================
st.set_page_config(page_title="FoodHub Support", page_icon="๐Ÿ•", layout="centered")

# ================== STYLES ==================
st.markdown(
    """
    <style>
        .main { max-width: 480px; margin: 0 auto; }
        .foodhub-card {
            background: linear-gradient(135deg, #fff3e0, #ffe0cc);
            padding: 16px 20px;
            border-radius: 18px;
            border: 1px solid #f5c28c;
            margin-bottom: 16px;
        }
        .foodhub-header-title {
            margin-bottom: 4px;
            color: #e65c2b;
            font-size: 26px;
            font-weight: 800;
        }
        .foodhub-header-subtitle { margin: 0; color: #444; font-size: 14px; }
        .foodhub-assistant-pill {
            display: inline-flex; align-items: center; gap: 6px;
            background: #ffffff; border-radius: 999px;
            padding: 6px 10px; font-size: 11px; color: #555;
            border: 1px solid #ffd3a3; margin-top: 8px;
        }
        .status-dot { width: 8px; height: 8px; border-radius: 50%; background: #2ecc71; }
        .quick-actions-title { font-size: 13px; font-weight: 600; color: #555; margin-top: 4px; margin-bottom: 6px; }
        .bot-bubble {
            background: #eef7f2; border-radius: 12px;
            padding: 10px 12px; font-size: 14px;
            margin-top: 8px; line-height: 1.35;
        }
        .user-label { font-size: 12px; color: #777; margin-top: 10px; margin-bottom: 2px; }
        .bot-label { font-size: 12px; color: #777; margin-top: 10px; margin-bottom: 2px; }
        code {
            background: rgba(0,0,0,0.06);
            padding: 2px 6px;
            border-radius: 8px;
            font-size: 13px;
        }
    </style>
    """,
    unsafe_allow_html=True,
)

# ================== POLITE RESPONSE TEMPLATES ==================
WELCOME_LINE = "Welcome to FoodHub Support! ๐Ÿ‘‹ I'm your virtual assistant โ€” here to help you."
ASK_ORDER_ID_LINE = "Please share your **Order ID** (for example: `O12488`) so I can assist you further. ๐Ÿ’›"

# ================== HELPERS ==================
ORDER_ID_REGEX = r"\b[oO]\d{3,}\b"

def format_for_bubble(text: str) -> str:
    """Safely render markdown-like **bold** and `code` inside HTML chat bubbles."""
    t = html.escape(str(text or ""))
    t = re.sub(r"\*\*(.+?)\*\*", r"<b>\1</b>", t)       # **bold**
    t = re.sub(r"`([^`]+)`", r"<code>\1</code>", t)     # `code`
    t = t.replace("\n", "<br>")
    return t

def extract_order_id(text: str) -> Optional[str]:
    m = re.search(ORDER_ID_REGEX, text or "")
    return m.group(0).upper() if m else None

def needs_order_id(message: str) -> bool:
    """
    True if message is asking anything order-specific that must NOT be answered without an Order ID.
    """
    t = (message or "").lower().strip()

    # exclude simple greetings / thanks only
    greetings = {"hi", "hello", "hey", "good morning", "good afternoon", "good evening"}
    thanks = {"thanks", "thank you", "thx", "thankyou"}
    if t in greetings or t in thanks:
        return False

    # If they already provided Order ID, no need to ask.
    if extract_order_id(message):
        return False

    phrases = [
        "where is my order", "track my order", "track order", "order status", "delivery status",
        "status of my order", "when will my order arrive", "when will it arrive", "eta",
        "order delayed", "delay in delivery", "order not delivered", "late delivery",
        "order details", "show me the details", "details of my order", "details for my order",
        "what is in my order", "what's in my order", "items in my order", "my order items",
        "what did i order", "what have i ordered",
        "cancel my order", "cancel order",
        "refund", "payment issue", "payment problem", "payment failed", "charged", "transaction",
        "money back", "return my money",
        "modify my order", "change my order", "postpone my order", "deliver at different address",
        "change address", "different address", "deliver to", "reschedule delivery",
        # extra issue intents
        "order never arrived", "wrong or missing items", "food damage", "quality issue",
        "food safety", "delivery safety", "charged more than once",
        "another order issue"
    ]
    if any(p in t for p in phrases):
        return True

    # Generic "order" mention with an action keyword
    if "order" in t and any(k in t for k in [
        "status", "track", "details", "item", "cancel", "refund", "payment", "where", "when",
        "modify", "change", "postpone", "address", "deliver", "reschedule",
        "missing", "wrong", "quality", "damage", "safety", "arrived"
    ]):
        return True

    return False

def is_third_party_order_request(message: str) -> bool:
    """Detect requests for someone else's order. We refuse for privacy."""
    t = (message or "").lower()
    third_party = [
        "my friend", "friend's", "friends'", "my brother", "my sister",
        "my wife", "my husband", "my mom", "my dad",
        "someone else's", "someone elses", "other person's", "other person",
        "his order", "her order", "their order",
    ]
    order_words = ["order", "delivery", "track", "status", "items", "details", "cancel", "refund", "payment", "address", "modify", "postpone"]
    return any(tp in t for tp in third_party) and any(w in t for w in order_words)

def _is_cancel_intent(message: str) -> bool:
    t = (message or "").lower().strip()
    return ("cancel" in t and "order" in t) or ("cancellation" in t)

def _is_escalation_intent(message: str) -> bool:
    t = (message or "").lower().strip()
    escalation_keywords = [
        "raised the query multiple times",
        "asked multiple times",
        "no resolution",
        "still not resolved",
        "immediate response",
        "urgent",
        "not resolved",
        "what is happening",
        "followed multiple times",
        "followed up multiple times",
        "follow up multiple times",
        "multiple follow ups",
        "multiple follow-ups",
        "i have followed multiple times",
        "i have followed up multiple times",
        "i want an update on my refund",
        "refund update",
        "update on my refund",
        "refund status"
    ]
    return any(k in t for k in escalation_keywords)

def _is_payment_refund_intent(message: str) -> bool:
    t = (message or "").lower().strip()
    keywords = [
        "payment", "refund", "money back", "charged", "chargeback",
        "transaction", "payment failed", "payment issue", "payment problem",
        "upi", "card", "debit", "credit", "wallet",
        "charged more than once", "double charged", "charged twice"
    ]
    return any(k in t for k in keywords)

# ================== QUICK BUTTON RESPONSE MAP (EXACT TEXT YOU APPROVED) ==================
# These are used only when quick buttons are clicked (so your outputs are consistent and perfect).
QUICK_BUTTON_EXACT_REPLIES = {
    "Order never arrived.": (
        "Welcome to FoodHub Support! ๐Ÿ‘‹\n"
        "Iโ€™m really sorry to hear that your order hasnโ€™t arrived โ€” I understand how frustrating that can be. ๐Ÿ’›\n\n"
        "To help you right away, could you please share your Order ID (for example: O12488)?\n"
        "Once I have the Order ID, Iโ€™ll quickly check the delivery status, identify what went wrong, and help you with the next steps, "
        "including a replacement or refund if applicable."
    ),
    "Food damage or quality issue.": (
        "Welcome to FoodHub Support! ๐Ÿ‘‹\n"
        "Iโ€™m really sorry to hear that youโ€™re facing a food damage or quality issue โ€” thatโ€™s not the experience we want you to have. ๐Ÿ’›\n\n"
        "Please share your Order ID (for example: O12488) so I can review the order details and assist you with a replacement or refund "
        "as per our quality policy."
    ),
    "My card was charged more than once.": (
        "Welcome to FoodHub Support! ๐Ÿ‘‹\n"
        "I understand how concerning it can be to see your card charged more than once โ€” Iโ€™m here to help. ๐Ÿ’›\n\n"
        "Could you please share your Order ID (for example: O12488)?\n"
        "Once I have it, Iโ€™ll verify the payment records and, if a duplicate charge is confirmed, initiate a refund "
        "(usually processed within 48 hours)."
    ),
    "Wrong or missing items.": (
        "Welcome to FoodHub Support! ๐Ÿ‘‹\n"
        "Iโ€™m sorry to hear that your order had wrong or missing items. Thank you for letting us know. ๐Ÿ’›\n\n"
        "Please share your Order ID (for example: O12488) so I can review the order and help arrange a replacement or refund as per our policy."
    ),
    "Another order issue.": (
        "Welcome to FoodHub Support! ๐Ÿ‘‹\n"
        "Iโ€™m here to help with your order-related concern. ๐Ÿ’›\n\n"
        "It looks like you may have more than one order associated with your account. To assist you correctly, please share the Order ID "
        "(for example: O12488) for the order youโ€™re referring to.\n\n"
        "Once I have the correct Order ID, Iโ€™ll review the details and help resolve the issue as quickly as possible."
    ),
    "Delivery or food safety issue.": (
        "Welcome to FoodHub Support! ๐Ÿ‘‹\n"
        "Iโ€™m really sorry to hear that youโ€™re facing a delivery or food safety issue. Your safety and satisfaction are extremely important to us. ๐Ÿ’›\n\n"
        "Please share your Order ID (for example: O12488).\n"
        "Once I have it, Iโ€™ll review the delivery handling and food safety details and assist you with a replacement or refund as per our safety policy."
    ),
    "I want an update on my refund.": (
        "Welcome to FoodHub Support! ๐Ÿ‘‹\n"
        "I understand youโ€™re looking for an update on your refund, and Iโ€™ll be happy to check that for you. ๐Ÿ’›\n\n"
        "Please share your Order ID (for example: O12488).\n"
        "Once I have it, Iโ€™ll confirm whether the refund has been initiated, the refund amount, and the expected timeline for it to reflect in your account "
        "(usually within 48 hours, depending on your bank)."
    ),
}

# ================== DB CONFIG (ALIGNED WITH YOUR NOTEBOOK) ==================
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
CLEAN_DB_PATH = os.path.join(BASE_DIR, "orders_clean.db")  # must match your notebook output

# Startup sanity check
try:
    with sqlite3.connect(CLEAN_DB_PATH) as _conn:
        _cur = _conn.cursor()
        _cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
        _tables = [r[0] for r in _cur.fetchall()]
    if "orders_clean" not in _tables:
        st.error(
            f"Connected to {CLEAN_DB_PATH!r}, but table 'orders_clean' was not found.\n\n"
            "Ensure your notebook saved df_eng like:\n"
            "df_eng.to_sql('orders_clean', conn, if_exists='replace', index=False)\n\n"
            "and that orders_clean.db is present next to app.py."
        )
        st.stop()
except Exception as e:
    st.error(f"Could not open database at {CLEAN_DB_PATH!r}: {e}")
    st.stop()

# ================== AUTH ==================
groq_api_key = os.environ.get("GROQ_API_KEY")
if not groq_api_key:
    st.error("Missing GROQ_API_KEY โ€” Add it inside Hugging Face Space โ†’ Settings โ†’ Secrets")
    st.stop()

client = Groq(api_key=groq_api_key)

# ================== SCHEMA (for LLM prompt only) ==================
ORDERS_SCHEMA = """
Table: orders_clean

Columns:
- order_id (TEXT)
- cust_id (TEXT)
- order_time (DATETIME/TEXT)
- preparing_eta (DATETIME/TEXT)
- prepared_time (DATETIME/TEXT)
- delivery_eta (DATETIME/TEXT)
- delivery_time (DATETIME/TEXT)
- order_status_std (TEXT)
- payment_status_std (TEXT)
- item_in_order (TEXT)
- item_count (INTEGER)
- prep_duration_min (REAL)
- delivery_duration_min (REAL)
- total_duration_min (REAL)
- on_time_delivery (INTEGER 0/1)
- is_delivered (INTEGER 0/1)
- is_canceled (INTEGER 0/1)
"""

# =====================================================================
# SQL SAFETY FIREWALL
# =====================================================================
def is_safe_sql(sql: str) -> bool:
    if not isinstance(sql, str):
        return False
    s = sql.lower().strip()

    if not s.startswith("select"):
        return False

    # block stacked queries like: SELECT ...; DROP ...
    if re.search(r";\s*\S", s):
        return False

    forbidden = ["drop", "delete", "update", "insert", "alter", "truncate", "create"]
    if any(k in s for k in forbidden):
        return False

    if any(tok in s for tok in ["--", "/*", "*/"]):
        return False

    return True

# =====================================================================
# run_sql_query()
# =====================================================================
def run_sql_query(sql: str) -> pd.DataFrame:
    if not isinstance(sql, str):
        return pd.DataFrame([{"message": "๐Ÿšซ Invalid SQL type (expected string)."}])
    sql = sql.strip()

    if not is_safe_sql(sql):
        return pd.DataFrame([{"message": "๐Ÿšซ Blocked unsafe or unsupported SQL."}])

    try:
        with sqlite3.connect(CLEAN_DB_PATH) as conn:
            df = pd.read_sql_query(sql, conn)

        # convert timestamp columns (if present)
        for col in ["order_time", "preparing_eta", "prepared_time", "delivery_eta", "delivery_time"]:
            if col in df.columns:
                df[col] = pd.to_datetime(df[col], errors="coerce")

        return df
    except Exception as e:
        return pd.DataFrame([{"message": f"โš ๏ธ SQL execution error: {str(e)}"}])

# =====================================================================
# llm_to_sql()
# =====================================================================
def llm_to_sql(user_message: str) -> str:
    """
    Convert natural language to a safe SELECT query for orders_clean.
    - Fast-path when Order ID exists.
    - If order_id required but missing: return NEED_ORDER_ID.
    """
    msg = user_message or ""
    text = msg.lower().strip()

    oid = extract_order_id(msg)
    if oid:
        sql = f"SELECT * FROM orders_clean WHERE LOWER(order_id) = LOWER('{oid}')"
        return sql if is_safe_sql(sql) else "SELECT 'Unable to answer safely.' AS message;"

    # If the message is order-specific without Order ID, force NEED_ORDER_ID
    must_have_order_id_intents = [
        "where is my order", "track my order", "track order", "order status", "delivery status",
        "when will my order arrive", "when will it arrive", "eta",
        "order delayed", "delay in delivery", "order not delivered", "late delivery",
        "order details", "show me the details", "details of my order", "details for my order",
        "cancel my order", "cancel order",
        "refund", "payment",
        "modify my order", "change my order", "postpone my order",
        "deliver at different address", "change address", "reschedule delivery",
        "order never arrived", "wrong or missing items", "food damage", "quality issue",
        "food safety", "delivery safety", "charged more than once",
        "another order issue"
    ]
    if any(p in text for p in must_have_order_id_intents) or needs_order_id(msg):
        return "SELECT 'NEED_ORDER_ID' AS message;"

    system_prompt = f"""
You are an expert SQLite assistant for a food delivery company.

You ONLY generate valid SQLite SELECT queries using this schema:

{ORDERS_SCHEMA}

RULES:
- Output ONLY the SQL query (no markdown, no comments, no explanation).
- Allowed table name: orders_clean
- Allowed operation: SELECT only
- NEVER invent placeholders like 'your_order_id'.
- If the query requires an order_id but it is missing, return exactly:
  SELECT 'NEED_ORDER_ID' AS message;
- If unsure:
  SELECT 'Unable to answer with available data.' AS message;
"""

    response = client.chat.completions.create(
        model="llama-3.3-70b-versatile",
        temperature=0.1,
        messages=[
            {"role": "system", "content": system_prompt},
            {"role": "user", "content": msg},
        ],
    )

    sql = (response.choices[0].message.content or "").strip()

    # strip ``` fences if any
    if sql.startswith("```"):
        sql = re.sub(r"^```sql", "", sql, flags=re.IGNORECASE).strip()
        sql = re.sub(r"^```", "", sql).strip()
        sql = sql.replace("```", "").strip()

    # Force FROM orders_clean
    sql = re.sub(r"\bfrom\s+\w+\b", "FROM orders_clean", sql, flags=re.IGNORECASE)

    # Normalize order_id filter
    sql = re.sub(
        r"where\s+order_id\s*=\s*'([^']+)'",
        r"WHERE LOWER(order_id) = LOWER('\1')",
        sql,
        flags=re.IGNORECASE,
    )

    return sql if is_safe_sql(sql) else "SELECT 'Unable to answer safely.' AS message;"

# =====================================================================
# analyze_sentiment_and_escalation()
# =====================================================================
def analyze_sentiment_and_escalation(user_message: str) -> dict:
    user_message = "" if user_message is None else str(user_message).strip()

    system_prompt = """
You are a classifier for a food delivery chatbot.

Return ONLY JSON (no extra text):
{
  "sentiment": "calm" | "neutral" | "frustrated" | "angry",
  "escalate": true or false
}

Escalate=true when user is frustrated/angry, mentions repeated attempts, or demands urgent help.
If uncertain: {"sentiment":"neutral","escalate":false}
"""

    response = client.chat.completions.create(
        model="llama-3.3-70b-versatile",
        temperature=0.0,
        messages=[
            {"role": "system", "content": system_prompt},
            {"role": "user", "content": user_message},
        ],
    )

    raw = (response.choices[0].message.content or "").strip()
    try:
        info = json.loads(raw)
        if not isinstance(info, dict):
            raise ValueError("Not dict")
        if "sentiment" not in info or "escalate" not in info:
            raise ValueError("Missing keys")
        info["sentiment"] = str(info["sentiment"]).strip().lower()
        info["escalate"] = bool(info["escalate"])
        if info["sentiment"] not in {"calm", "neutral", "frustrated", "angry"}:
            raise ValueError("Bad label")
        return info
    except Exception:
        return {"sentiment": "neutral", "escalate": False}

# =====================================================================
# create_ticket()
# =====================================================================
def create_ticket(user_message: str, sentiment: str, ticket_type: str = "general", order_id: Optional[str] = None) -> str:
    ticket_id = "TKT-" + uuid.uuid4().hex[:8].upper()
    created_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    with sqlite3.connect(CLEAN_DB_PATH) as conn:
        cur = conn.cursor()
        cur.execute("""
            CREATE TABLE IF NOT EXISTS tickets (
                ticket_id TEXT PRIMARY KEY,
                ticket_type TEXT,
                sentiment TEXT,
                message TEXT,
                order_id TEXT,
                status TEXT DEFAULT 'Open',
                created_at TEXT
            );
        """)
        cur.execute("""
            INSERT INTO tickets (ticket_id, ticket_type, sentiment, message, order_id, status, created_at)
            VALUES (?, ?, ?, ?, ?, 'Open', ?);
        """, (ticket_id, ticket_type, sentiment, user_message, order_id, created_at))
        conn.commit()

    return ticket_id

# =====================================================================
# sql_result_to_response()
# =====================================================================
def sql_result_to_response(user_message: str, df: pd.DataFrame, max_rows_to_list: int = 5) -> str:
    # handle firewall/SQL execution errors and special messages
    if isinstance(df, pd.DataFrame) and "message" in df.columns and len(df) == 1:
        msg = str(df.iloc[0]["message"])
        if msg.strip().upper() == "NEED_ORDER_ID":
            return f"{WELCOME_LINE}\n{ASK_ORDER_ID_LINE}"
        return msg

    if df.empty:
        return (
            f"{WELCOME_LINE}\n"
            "I couldnโ€™t find a matching order for the details provided.\n"
            f"{ASK_ORDER_ID_LINE}"
        )

    df_for_llm = df.head(max_rows_to_list).copy()
    result_data = df_for_llm.to_dict(orient="records")

    system_prompt = f"""
You are a polite and professional customer support chatbot for FoodHub.

Always start with EXACTLY:
"{WELCOME_LINE}"

VERY IMPORTANT POLICY:
- If ANY matching record exists in the data I give you โ†’ The order is CONFIRMED.
- NEVER tell the user to re-check their order ID if matching rows exist.
- NEVER say "order not found", "unable to locate order", or similar messages
  when there is at least one matching record.
- Instead, confidently acknowledge the order and use available data.

CRITICAL RULES:
- NEVER invent or guess items, ETA, status, payment, or any order details.
- Use ONLY the data provided in "Matching order data".
- If information is missing, say it's unavailable in tracking.

DELIVERED TEMPLATE (use when order_status_std indicates delivered OR is_delivered == 1):
- Use this meaning and style (you may include the order id if available):
  "I've checked on your order, and I'm happy to inform you that it has been delivered โœ…. You should have received your order.
   If you have any further concerns or issues, please let me know. If you need anything else, Iโ€™m here to help! ๐Ÿ’›"

If NOT delivered:
- Provide the latest known status.
- If delivery_time is missing but delivery_eta exists: share ETA.
- If both delivery_time and delivery_eta missing: apologize for limited tracking.

Keep it concise and helpful.
"""

    content = f"User question: {user_message}\n\nMatching order data (up to {max_rows_to_list} rows):\n{result_data}"

    resp = client.chat.completions.create(
        model="llama-3.3-70b-versatile",
        temperature=0.2,
        messages=[
            {"role": "system", "content": system_prompt},
            {"role": "user", "content": content},
        ],
    )
    return (resp.choices[0].message.content or "").strip()

# =====================================================================
# answer_user_question()
# =====================================================================
def answer_user_question(user_message: str) -> str:
    # Safety net: never answer order intents without Order ID
    if needs_order_id(user_message) and not extract_order_id(user_message):
        return f"{WELCOME_LINE}\n{ASK_ORDER_ID_LINE}"

    sql = llm_to_sql(user_message)
    df = run_sql_query(sql)
    return sql_result_to_response(user_message, df, max_rows_to_list=5)

# =====================================================================
# chatbot_response() โ€“ main orchestration
# =====================================================================
def chatbot_response(user_message: str) -> str:
    text = (user_message or "").strip()
    t = text.lower().strip()
    order_id = extract_order_id(text)

    # Greetings / thanks
    greetings = ["hi", "hello", "hey", "good morning", "good afternoon", "good evening"]
    thanks_words = ["thank you", "thanks", "thx", "thankyou"]

    if any(t == g or t.startswith(g + " ") for g in greetings):
        return f"{WELCOME_LINE}\n{ASK_ORDER_ID_LINE}"

    if any(w in t for w in thanks_words):
        return (
            "Youโ€™re most welcome! ๐Ÿ’›\n"
            "If you need help with an order, please share your Order ID (like `O12488`)."
        )

    # Third-party privacy
    if is_third_party_order_request(text):
        return (
            f"{WELCOME_LINE}\n"
            "๐Ÿ˜” I canโ€™t help with someone elseโ€™s order details for privacy reasons.\n\n"
            "Please ask them to contact FoodHub Support directly, or have them share their **Order ID** themselves. ๐Ÿ’›"
        )

    # Hacking / misuse (polite privacy rejection)
    # (kept from your policy, but simplified to avoid toolchain edits)
    if "hack" in t or "hacker" in t or "all orders" in t or "dump" in t or "database" in t:
        return (
            f"{WELCOME_LINE}\n"
            "๐Ÿ˜” Sorry, I canโ€™t assist with that.\n"
            "For privacy and security reasons, I can only help with your own order using a valid Order ID. ๐Ÿ’›"
        )

    # Escalation intent
    if _is_escalation_intent(text):
        senti = analyze_sentiment_and_escalation(text)
        ticket_id = create_ticket(
            user_message=text,
            sentiment=senti.get("sentiment", "frustrated"),
            ticket_type="escalation",
            order_id=order_id
        )
        return (
            f"{WELCOME_LINE}\n"
            "๐Ÿ’› Iโ€™m really sorry youโ€™ve had to follow up multiple times.\n\n"
            "Iโ€™ve escalated this to a senior support agent so it gets immediate attention.\n"
            f"๐Ÿ“Œ Ticket ID: **{ticket_id}**\n\n"
            f"{ASK_ORDER_ID_LINE if not order_id else 'If you need anything else, Iโ€™m here to help! ๐Ÿ’›'}"
        )

    # Payment / Refund intent
    if _is_payment_refund_intent(text) and not order_id:
        return (
            "๐Ÿ’› Iโ€™m sorry youโ€™re facing an issue with the payment or refund. Iโ€™ll help you with this right away.\n\n"
            "In order to serve you better, could you please share your **Order ID** "
            "(for example: `O12488`) so I can check the payment status and update you? "
            "If the payment was received, the refund will be processed within 48 hours."
        )

    # Cancel intent without Order ID
    if _is_cancel_intent(text) and not order_id:
        return (
            "๐Ÿ’› Iโ€™m sorry to hear youโ€™d like to cancel your order โ€” I completely understand and Iโ€™m here to help. "
            "Could you please share your **Order ID** (for example: `O12488`) so I can check the details and help "
            "process the cancellation for you?"
        )

    # Global rule: any order-related request without Order ID
    if needs_order_id(text) and not order_id:
        return f"{WELCOME_LINE}\n{ASK_ORDER_ID_LINE}"

    # Normal path
    return answer_user_question(text)

# ================== SESSION STATE ==================
if "welcome_shown" not in st.session_state:
    st.session_state["welcome_shown"] = False

if "messages" not in st.session_state:
    st.session_state["messages"] = []  # [{"role":"user"/"assistant","content":"..."}]

if "pending_message" not in st.session_state:
    st.session_state["pending_message"] = None

# Special: if pending is from a quick button, we want exact response
if "pending_quick_exact" not in st.session_state:
    st.session_state["pending_quick_exact"] = None

# ================== UI HEADER ==================
st.markdown(
    """
    <div class="foodhub-card">
        <div style="display:flex; align-items:flex-start; gap:12px;">
            <div style="font-size:32px;">๐Ÿ•</div>
            <div style="flex:1;">
                <div class="foodhub-header-title">FoodHub AI Support</div>
                <p class="foodhub-header-subtitle">
                    Ask about order status, delivery updates, cancellations, or payment/refund issues.
                </p>
                <div class="foodhub-assistant-pill">
                    <span class="status-dot"></span>
                    <span>FoodHub virtual assistant ยท Online</span>
                </div>
            </div>
        </div>
    </div>
    """,
    unsafe_allow_html=True,
)

if not st.session_state["welcome_shown"]:
    st.success("Welcome to FoodHub Support! Iโ€™m your virtual assistant for order, delivery, and payment queries.")
    st.caption("Because great food deserves great service ๐Ÿ’›")
    st.session_state["welcome_shown"] = True

# ================== QUICK BUTTONS ==================
st.markdown('<div class="quick-actions-title">Quick help options</div>', unsafe_allow_html=True)

# Original 4
col1, col2 = st.columns(2)
if col1.button("๐Ÿ“ Track my order", use_container_width=True):
    st.session_state["pending_message"] = "Where is my order?"
if col2.button("๐Ÿ’ณ Payment issue", use_container_width=True):
    st.session_state["pending_message"] = "I have an issue with my payment / refund."

col3, col4 = st.columns(2)
if col3.button("โŒ Cancel order", use_container_width=True):
    st.session_state["pending_message"] = "I want to cancel my order."
if col4.button("๐Ÿงพ Order details", use_container_width=True):
    st.session_state["pending_message"] = "Show me the details for my order."

# Extra options with EXACT responses
st.markdown('<div class="quick-actions-title">More issue options</div>', unsafe_allow_html=True)

a1, a2 = st.columns(2)
if a1.button("๐Ÿ“ฆ Order never arrived", use_container_width=True):
    st.session_state["pending_quick_exact"] = "Order never arrived."
if a2.button("๐Ÿงพ Wrong or missing items", use_container_width=True):
    st.session_state["pending_quick_exact"] = "Wrong or missing items."

b1, b2 = st.columns(2)
if b1.button("๐Ÿฅก Food damage or quality issue", use_container_width=True):
    st.session_state["pending_quick_exact"] = "Food damage or quality issue."
if b2.button("โ“ Another order issue", use_container_width=True):
    st.session_state["pending_quick_exact"] = "Another order issue."

c1, c2 = st.columns(2)
if c1.button("๐Ÿ’ณ My card was charged more than once", use_container_width=True):
    st.session_state["pending_quick_exact"] = "My card was charged more than once."
if c2.button("๐Ÿ›ก๏ธ Delivery or food safety issue", use_container_width=True):
    st.session_state["pending_quick_exact"] = "Delivery or food safety issue."

d1, d2 = st.columns(2)
if d1.button("๐Ÿ” Refund status", use_container_width=True):
    st.session_state["pending_quick_exact"] = "I want an update on my refund."
if d2.button("๐Ÿง‘โ€๐Ÿ’ป Followed up many times", use_container_width=True):
    st.session_state["pending_message"] = "I have followed multiple times about my order."

st.markdown("---")

# ================== CHAT HISTORY ==================
def render_chat_history():
    for m in st.session_state["messages"]:
        if m["role"] == "user":
            user_html = format_for_bubble(f"๐Ÿ’ฌ {m['content']}")
            st.markdown('<div class="user-label">You</div>', unsafe_allow_html=True)
            st.markdown(f"<div class='bot-bubble' style='background:#fff;'>{user_html}</div>", unsafe_allow_html=True)
        else:
            bot_html = format_for_bubble(f"๐Ÿค– {m['content']}")
            st.markdown('<div class="bot-label">FoodHub Assistant</div>', unsafe_allow_html=True)
            st.markdown(f"<div class='bot-bubble'>{bot_html}</div>", unsafe_allow_html=True)

render_chat_history()

# ================== SEND LOGIC ==================
def append_user_and_bot(user_text: str, bot_text: str):
    st.session_state["messages"].append({"role": "user", "content": user_text})
    st.session_state["messages"].append({"role": "assistant", "content": bot_text})

def send_message(user_text: str):
    user_text = (user_text or "").strip()
    if not user_text:
        st.warning("Please enter a message to continue.")
        return
    st.session_state["messages"].append({"role": "user", "content": user_text})
    reply = chatbot_response(user_text)
    st.session_state["messages"].append({"role": "assistant", "content": reply})

# 1) Quick buttons with EXACT replies
if st.session_state.get("pending_quick_exact"):
    q = st.session_state["pending_quick_exact"]
    st.session_state["pending_quick_exact"] = None
    exact = QUICK_BUTTON_EXACT_REPLIES.get(q, f"{WELCOME_LINE}\n{ASK_ORDER_ID_LINE}")
    append_user_and_bot(q, exact)
    st.rerun()

# 2) Normal quick buttons
if st.session_state.get("pending_message"):
    pm = st.session_state["pending_message"]
    st.session_state["pending_message"] = None
    send_message(pm)
    st.rerun()

# ================== INPUT (FORM) ==================
with st.form("chat_form", clear_on_submit=True):
    query = st.text_input(
        "๐Ÿ’ฌ Type your question here:",
        key="chat_input",
        placeholder="e.g. Where is my order O12488?",
    )
    send_clicked = st.form_submit_button("Send", use_container_width=True)

if send_clicked:
    send_message(query)
    st.rerun()