LocalFoodAI_lanfr144/app.py

#ident "@(#)$Format:LocalFoodAI_lanfr144:app.py:%an:%ae:%ad:%cn:%ce:%cd:%H:%D:%N$"
import streamlit as st
import extra_streamlit_components as stx
import subprocess
import pymysql
import bcrypt
import random
import string
import time
import os
import pandas as pd
import html
from snmp_notifier import notifier
from unit_converter import UnitConverter
from fpdf import FPDF
import myloginpath
import ollama
import requests
import smtplib
from email.message import EmailMessage
from typing import Optional, List, Dict, Any, Tuple
import threading
import os

def get_active_model() -> str:
    try:
        from dotenv import load_dotenv
        current_dir = os.path.dirname(os.path.abspath(__file__))
        env_path = os.path.join(current_dir, '.env')
        load_dotenv(dotenv_path=env_path, override=True)
    except Exception:
        pass
    return os.environ.get('LLM_MODEL', 'llama3.2:3b')

ACTIVE_MODEL = get_active_model()

def strip_scratchpad(text: str) -> str:
    import re
    # Strip out the XML <scratchpad> tag and everything in between, non-greedily
    clean_text = re.sub(r'<scratchpad>.*?</scratchpad>', '', text, flags=re.DOTALL)
    return clean_text.strip()

def extract_allergens_from_json(data) -> list:
    table_data = []
    
    def add_item(aliment, allergens):
        if not aliment or not allergens:
            return
        if isinstance(allergens, list):
            cleaned = [str(alg).strip().title() for alg in allergens if alg]
            if cleaned:
                table_data.append({
                    "Aliment (Ingredient)": str(aliment).strip().title(),
                    "Allergen Kind(s)": ", ".join(cleaned)
                })
        elif isinstance(allergens, str) and allergens.strip():
            table_data.append({
                "Aliment (Ingredient)": str(aliment).strip().title(),
                "Allergen Kind(s)": allergens.strip().title()
            })

    if isinstance(data, list):
        for entry in data:
            if isinstance(entry, dict):
                aliment = entry.get('aliment') or entry.get('name') or entry.get('food')
                allergens = entry.get('allergen') or entry.get('allergens') or entry.get('kinds') or []
                if aliment and allergens:
                    add_item(aliment, allergens)
                else:
                    for k, v in entry.items():
                        if isinstance(v, (list, str)):
                            add_item(k, v)
            elif isinstance(entry, list) and len(entry) >= 2:
                add_item(entry[0], entry[1])
    elif isinstance(data, dict):
        al_list = data.get('aliment') or data.get('aliments')
        all_list = data.get('allergen') or data.get('allergens') or data.get('kinds')
        
        if isinstance(al_list, list) and isinstance(all_list, list):
            for item in all_list:
                if isinstance(item, dict):
                    name = item.get('name') or item.get('aliment')
                    types = item.get('types') or item.get('type') or item.get('kinds') or item.get('allergen') or item.get('allergens')
                    if name and types:
                        add_item(name, types)
                elif isinstance(item, list) and len(item) >= 2:
                    add_item(item[0], item[1])
            if not table_data and len(al_list) == len(all_list):
                for a, alg in zip(al_list, all_list):
                    add_item(a, alg)
                    
        if not table_data:
            for key, val in data.items():
                if isinstance(val, list):
                    for entry in val:
                        if isinstance(entry, dict):
                            aliment = entry.get('aliment') or entry.get('name') or entry.get('food')
                            allergens = entry.get('allergen') or entry.get('allergens') or entry.get('kinds') or []
                            if aliment and allergens:
                                add_item(aliment, allergens)
                            else:
                                for k, v in entry.items():
                                    if isinstance(v, (list, str)):
                                        add_item(k, v)
                        elif isinstance(entry, list) and len(entry) >= 2:
                            add_item(entry[0], entry[1])
                        elif isinstance(entry, str):
                            if key not in ['aliment', 'aliments', 'allergen', 'allergens', 'kinds', 'types']:
                                add_item(key, val)
                                break
                elif isinstance(val, dict):
                    for k, v in val.items():
                        if isinstance(v, (list, str)):
                            add_item(k, v)
                        elif isinstance(v, dict):
                            add_item(k, v.get('allergen') or v.get('allergens') or [])
                elif isinstance(val, str):
                    if key not in ['aliment', 'aliments', 'allergen', 'allergens', 'kinds', 'types']:
                        add_item(key, val)
        if not table_data:
            for k, v in data.items():
                if k not in ['aliment', 'aliments', 'allergen', 'allergens', 'kinds', 'types']:
                    add_item(k, v)
    return table_data

@st.cache_data(show_spinner=False)
def query_plate_allergens(unique_aliments: list) -> list:
    import ollama
    import json
    table_data = []
    if not unique_aliments:
        return table_data
        
    aliments_str = ", ".join(unique_aliments)
    prompt = f"In these aliments : {aliments_str} are there allergens and if it is the case also said the allergen kinds. Return the answer as json array with two variables aliment and the associate allergen in an array find inside it. focus on the list, do not add or delete aliments."
    
    try:
        response = ollama.chat(
            model=get_active_model(),
            messages=[{'role': 'user', 'content': prompt}],
            format='json'
        )
        res_content = response['message']['content'].strip()
        data = json.loads(res_content)
        table_data = extract_allergens_from_json(data)
    except Exception as e:
        notifier.send_alert(f"LLM query_plate_allergens error: {e}")
        pass
    return table_data

@st.cache_data(show_spinner=False)
def detect_allergens_from_text(name: str, ingredients: str) -> set:
    import re
    import ollama
    import json
    detected = set()
    
    # Extract candidate terms from name and ingredients
    candidates = []
    if ingredients:
        parts = re.split(r'[,;()\[\]\n\r]', ingredients)
        for p in parts:
            p_clean = re.sub(r'[*_\d%]+', '', p).strip()
            if len(p_clean) > 2 and p_clean.lower() not in ['ingredients', 'and', 'contains', 'may contain', 'natural', 'artificial', 'flavors', 'flavor', 'preservative', 'color', 'colors']:
                candidates.append(p_clean)
                
    if name:
        name_clean = re.sub(r'[*_\d%]+', '', name).strip()
        if len(name_clean) > 2:
            candidates.append(name_clean)
            for word in re.split(r'\s+', name_clean):
                w_clean = word.strip()
                if len(w_clean) > 2 and w_clean.lower() not in ['with', 'and', 'for', 'the', 'bar', 'cup', 'can', 'bag', 'mix']:
                    candidates.append(w_clean)
                    
    # Deduplicate candidates while keeping order
    seen = set()
    unique_candidates = []
    for c in candidates:
        c_low = c.lower()
        if c_low not in seen:
            seen.add(c_low)
            unique_candidates.append(c)
            
    if not unique_candidates:
        return detected
        
    # Ask the LLM using the JSON array prompt format
    aliments_str = ", ".join(unique_candidates)
    prompt = f"In these aliments : {aliments_str} are there allergens and if it is the case also said the allergen kinds. Return the answer as json array with two variables aliment and the associate allergen in an array find inside it. focus on the list, do not add or delete aliments."
    
    try:
        response = ollama.chat(
            model=get_active_model(),
            messages=[{'role': 'user', 'content': prompt}],
            format='json'
        )
        res_content = response['message']['content'].strip()
        data = json.loads(res_content)
        parsed = extract_allergens_from_json(data)
        for item in parsed:
            name = item.get("Aliment (Ingredient)")
            if name:
                detected.add(name)
    except Exception as e:
        notifier.send_alert(f"LLM detect_allergens_from_text error: {e}")
        pass
    return detected


def filter_scratchpad_stream(stream, raw_accumulator=None):
    buffer = ""
    in_scratchpad = False
    for chunk in stream:
        content = chunk['message']['content']
        if raw_accumulator is not None:
            raw_accumulator.append(content)
        buffer += content
        
        while True:
            if not in_scratchpad:
                start_idx = buffer.find("<scratchpad>")
                if start_idx != -1:
                    if start_idx > 0:
                        yield buffer[:start_idx]
                    yield "\n\n> 💭 **AI Thinking Process:**\n> "
                    buffer = buffer[start_idx + 12:]
                    in_scratchpad = True
                else:
                    yield_len = len(buffer) - 11
                    if yield_len > 0:
                        yield buffer[:yield_len]
                        buffer = buffer[yield_len:]
                    break
            else:
                end_idx = buffer.find("</scratchpad>")
                if end_idx != -1:
                    scratch_content = buffer[:end_idx]
                    scratch_content_formatted = scratch_content.replace("\n", "\n> ")
                    yield scratch_content_formatted
                    yield "\n\n"
                    buffer = buffer[end_idx + 13:]
                    in_scratchpad = False
                else:
                    yield_len = len(buffer) - 12
                    if yield_len > 0:
                        scratch_content = buffer[:yield_len]
                        scratch_content_formatted = scratch_content.replace("\n", "\n> ")
                        yield scratch_content_formatted
                        buffer = buffer[yield_len:]
                    break
    if buffer:
        if in_scratchpad:
            yield buffer.replace("\n", "\n> ")
        else:
            yield buffer

def pull_model_bg():
    try: ollama.pull(get_active_model())
    except: pass
threading.Thread(target=pull_model_bg, daemon=True).start()

def local_web_search(query: str) -> str:
    try:
        req = requests.get(f'http://127.0.0.1:8080/search', params={'q': query, 'format': 'json'})
        if req.status_code == 200:
            data = req.json()
            results = data.get('results', [])
            if not results: return f"No results found on the web for '{query}'."
            snippets = [f"Source: {r.get('url')}\nContent: {r.get('content')}" for r in results[:3]]
            return "\n\n".join(snippets)
        return "Search engine returned an error."
    except Exception as e: return f"Local search engine unreachable: {e}"

search_tool_schema = {
    'type': 'function',
    'function': {
        'name': 'local_web_search',
        'description': 'Search the internet for info not in DB.',
        'parameters': {'type': 'object', 'properties': {'query': {'type': 'string'}}, 'required': ['query']},
    },
}

def search_nutrition_db(query: str, user_eav=None) -> str:
    conn = get_db_connection('app_reader')
    if not conn: return "Database connection failed."
    try:
        with conn.cursor() as cursor:
            # Dynamically build strictly-enforced clinical SQL filters
            clinical_filters = ""
            if user_eav:
                for p in user_eav:
                    name = p['name'].lower()
                    val = p['value'].lower()
                    if name in ['condition', 'illness']:
                        if val == 'diabetes': clinical_filters += " AND m.sugars_100g < 5.0"
                        elif 'kidney' in val: clinical_filters += " AND m.proteins_100g < 15.0"
                        elif 'hypertension' in val: clinical_filters += " AND m.sodium_100g < 0.2"
                    elif name in ['diet', 'religious', 'preference']:
                        if val == 'kosher': clinical_filters += " AND c.ingredients_text NOT LIKE '%pork%' AND c.ingredients_text NOT LIKE '%shellfish%'"
                        elif val == 'halal': clinical_filters += " AND c.ingredients_text NOT LIKE '%pork%' AND c.ingredients_text NOT LIKE '%wine%' AND c.ingredients_text NOT LIKE '%alcohol%'"
                        elif val in ['christian', 'good friday', 'ash wednesday']: clinical_filters += " AND c.ingredients_text NOT LIKE '%meat%' AND c.ingredients_text NOT LIKE '%beef%' AND c.ingredients_text NOT LIKE '%chicken%' AND c.ingredients_text NOT LIKE '%pork%'"

            sql = f"""
                SELECT c.code, c.product_name, m.proteins_100g, m.fat_100g, m.carbohydrates_100g, m.sugars_100g 
                FROM food_db.products_core c
                LEFT JOIN food_db.products_macros m ON c.code = m.code
                WHERE MATCH(c.product_name, c.ingredients_text) AGAINST(%s IN BOOLEAN MODE)
                AND c.product_name IS NOT NULL AND c.product_name != '' AND c.product_name != 'None'
                {clinical_filters}
            """
            bool_query = " ".join([f"+{w}" for w in query.split()])
            cursor.execute(sql, (bool_query,))
            results = cursor.fetchall()
            if not results: return f"No database records found for '{query}'."
            
            snippets = []
            for r in results:
                pro = float(r['proteins_100g'] or 0)
                fat = float(r['fat_100g'] or 0)
                carb = float(r['carbohydrates_100g'] or 0)
                sug = float(r['sugars_100g'] or 0)
                snippets.append(f"- {r['product_name']}: Protein {pro:.2f}g, Fat {fat:.2f}g, Carbs {carb:.2f}g, Sugars {sug:.2f}g (per 100g)")
            return "\n".join(snippets)
    except Exception as e:
        return f"Database query failed: {e}"
    finally:
        conn.close()

def query_sql_readonly(sql: str) -> str:
    """Executes a read-only SELECT query against the food_db MySQL database."""
    sql_clean = sql.strip().strip(";").strip()
    if not any(sql_clean.upper().startswith(kw) for kw in ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN"]):
        return "Error: Only read-only queries (SELECT, SHOW, DESCRIBE, EXPLAIN) are permitted."
    
    conn = get_db_connection('app_reader')
    if not conn:
        return "Error: Database connection failed."
    try:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            results = cursor.fetchall()
            if not results:
                return "Query returned 0 rows."
            
            # Form custom markdown table
            headers = list(results[0].keys())
            lines = []
            lines.append("| " + " | ".join(headers) + " |")
            lines.append("| " + " | ".join(["---"] * len(headers)) + " |")
            
            # Cap at 10 results to stay within model context bounds
            row_limit = 10
            for row in results[:row_limit]:
                vals = [str(row[h]).replace("\n", " ").replace("|", "\\|") for h in headers]
                lines.append("| " + " | ".join(vals) + " |")
            
            truncated = f"\n*(Showing first {row_limit} of {len(results)} results)*" if len(results) > row_limit else ""
            return "\n".join(lines) + truncated
    except Exception as e:
        return f"Database query error: {str(e)}"
    finally:
        conn.close()

def query_web_searxng(query: str) -> str:
    """Performs an anonymous local web search using the SearXNG search engine."""
    try:
        searxng_url = os.environ.get("SEARXNG_HOST", "http://searxng:8080")
        resp = requests.get(f"{searxng_url}/search", params={'q': query, 'format': 'json'}, timeout=5)
        if resp.status_code == 200:
            results = resp.json().get('results', [])
            if results:
                snippets = []
                for r in results[:4]:
                    snippets.append(f"- **{r.get('title')}**: {r.get('content')} (Source: {r.get('url')})")
                return "\n".join(snippets)
        return "No web results found."
    except Exception as e:
        return f"Web search tool error: {str(e)}"

db_search_tool_schema = {
    'type': 'function',
    'function': {
        'name': 'search_nutrition_db',
        'description': 'Search the local medical nutrition database for product macros and ingredients. ALWAYS prioritize this over web search.',
        'parameters': {'type': 'object', 'properties': {'query': {'type': 'string', 'description': 'The product or food name to search for (e.g. apple, chicken, bread)'}}, 'required': ['query']},
    },
}

def get_db_connection(login_path):
    try:
        import os
        db_host = os.environ.get('DB_HOST')
        # Check if environment variables exist for this login path
        db_user = os.environ.get(f'{login_path.upper()}_USER') or os.environ.get('DB_USER')
        db_pass = os.environ.get(f'{login_path.upper()}_PASS') or os.environ.get('DB_PASS')

        if db_host and db_user and db_pass:
            return pymysql.connect(
                host=db_host,
                user=db_user,
                password=db_pass,
                database='food_db',
                cursorclass=pymysql.cursors.DictCursor
            )
            
        conf = myloginpath.parse(login_path)
        if not conf or not conf.get('user'):
            st.error(f"⚠️ MySQL configuration missing for `{login_path}`. If you are testing locally on Windows, this app must be run on the Ubuntu server where `mysql_config_editor` is properly configured.")
            notifier.send_alert(f"MySQL Config Missing: {login_path}")
            return None
            
        return pymysql.connect(
            host=conf.get('host', '127.0.0.1'),
            user=conf.get('user'),
            password=conf.get('password'),
            database='food_db',
            cursorclass=pymysql.cursors.DictCursor
        )
    except Exception as e:
        st.error(f"Connection Failed: {e}")
        notifier.send_alert(f"Database Connection Failed ({login_path}): {e}")
        return None

from contextlib import contextmanager

@contextmanager
def db_cursor(login_path: str):
    conn = get_db_connection(login_path)
    if not conn:
        yield None
        return
    try:
        with conn.cursor() as cursor:
            yield cursor
        conn.commit()
    except Exception as e:
        conn.rollback()
        st.error(f"Database query error: {e}")
        notifier.send_alert(f"Database Query Error ({login_path}): {e}")
        raise e
    finally:
        conn.close()

def verify_login(username: str, password: str) -> bool:
    with db_cursor('app_auth') as cursor:
        if not cursor: return False
        cursor.execute("SELECT password_hash FROM users WHERE username = %s", (username,))
        result = cursor.fetchone()
        if result: return bcrypt.checkpw(password.encode('utf-8'), result['password_hash'].encode('utf-8'))
    return False

def get_user_id(username: str) -> Optional[int]:
    with db_cursor('app_auth') as cursor:
        if not cursor: return None
        cursor.execute("SELECT id FROM users WHERE username = %s", (username,))
        result = cursor.fetchone()
        return result['id'] if result else None

def get_eav_profile(username: str) -> List[Dict[str, Any]]:
    uid = get_user_id(username)
    if not uid: return []
    with db_cursor('app_auth') as cursor:
        if not cursor: return []
        cursor.execute("SELECT id, illness_health_condition_diet_dislikes_name as name, illness_health_condition_diet_dislikes_value as value FROM user_health_profiles WHERE user_id = %s", (uid,))
        return cursor.fetchall()

def get_user_limit(username: str) -> str:
    with db_cursor('app_auth') as cursor:
        if not cursor: return "50"
        cursor.execute("SELECT search_limit FROM users WHERE username = %s", (username,))
        result = cursor.fetchone()
        return result['search_limit'] if (result and result['search_limit']) else "50"

def register_user(username: str, password: str, email: str) -> bool:
    hashed = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
    try:
        with db_cursor('app_auth') as cursor:
            if not cursor: return False
            cursor.execute("INSERT INTO users (username, password_hash, email) VALUES (%s, %s, %s)", (username, hashed, email))
        send_email(email, "Welcome to Local Food AI", f"Hello {username}, your account was securely created!", to_name=username.title())
        return True
    except pymysql.err.IntegrityError:
        return False

def send_email(to_email: str, subject: str, body: str, to_name: str = "User") -> Any:
    msg = EmailMessage()
    msg.set_content(body)
    msg['Subject'] = subject
    msg['From'] = '"Clinical Food AI System" <security@localfoodai.com>'
    msg['To'] = f'"{to_name}" <{to_email}>'
    
    for attempt in range(5):
        try:
            s = smtplib.SMTP('localhost', 25)
            s.send_message(msg)
            s.quit()
            return True
        except Exception as e:
            if attempt == 4:
                return f"SMTP Delivery Failed: {str(e)}"
            time.sleep(2)
    return "Unknown Error Occurred"

def reset_password(username: str, email: str) -> Any:
    with db_cursor('app_auth') as cursor:
        if not cursor: return False
        cursor.execute("SELECT id, email FROM users WHERE username = %s", (username,))
        user = cursor.fetchone()
        if user and user['email'] == email:
            new_pass = ''.join(random.choices(string.ascii_letters + string.digits, k=10))
            hashed = bcrypt.hashpw(new_pass.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
            cursor.execute("UPDATE users SET password_hash = %s WHERE id = %s", (hashed, user['id']))
            status = send_email(email, "Password Reset", f"Your new temporary password is: {new_pass}", to_name=username.title())
            return True if status is True else status
    return False

# UI Theming
def is_valid_image_url(url):
    if not url or not isinstance(url, str):
        return False
    url = url.strip()
    if not url.startswith(('http://', 'https://')):
        return False
    if 'invalid' in url.lower():
        return False
    return True

def reformat_git_date(date_str):
    from datetime import datetime
    try:
        import email.utils
        dt = email.utils.parsedate_to_datetime(date_str)
        return dt.strftime("%Y/%m/%d %H:%M:%S")
    except Exception:
        try:
            dt = datetime.strptime(date_str.strip(), "%a %b %d %H:%M:%S %Y %z")
            return dt.strftime("%Y/%m/%d %H:%M:%S")
        except Exception:
            return date_str

def render_version():
    st.markdown("---")
    git_version = None
    git_hash = None
    
    # 1. Parse from the smudged ident header in app.py
    try:
        current_dir = os.path.dirname(os.path.abspath(__file__))
        app_path = os.path.join(current_dir, 'app.py')
        if os.path.exists(app_path):
            with open(app_path, 'r', encoding='utf-8') as f:
                import re
                for _ in range(15):
                    line = f.readline()
                    if not line:
                        break
                    if "$Form" + "at:" in line:
                        match = re.search(r'\$For' + r'mat:(.*?)\$', line)
                        if match:
                            parts = match.group(1).split(':')
                            if len(parts) >= 13 and not parts[2].startswith('%an'):
                                git_version = f"{parts[9]}:{parts[10]}:{parts[11]}"
                                h = parts[12]
                                git_hash = f"{h[:2]}{h[-2:]}" if h else ""
                                break
    except Exception:
        pass
        
    # 2. Fallback using git log command
    if not git_version or not git_hash:
        try:
            git_version = subprocess.check_output(
                ['git', 'log', '-1', '--date=format:%Y/%m/%d %H:%M:%S', '--format=%cd', 'app.py'],
                stderr=subprocess.DEVNULL
            ).decode('utf-8').strip()
            git_hash_full = subprocess.check_output(
                ['git', 'log', '-1', '--format=%H', 'app.py'],
                stderr=subprocess.DEVNULL
            ).decode('utf-8').strip()
            if git_hash_full:
                git_hash = f"{git_hash_full[:2]}{git_hash_full[-2:]}"
        except Exception:
            pass

    st.caption(f"🚀 Version: {git_version}")
    st.caption(f"📅 Git ID: {git_version} {git_hash}")
    st.caption(f"Model: {get_active_model()}")

st.set_page_config(page_title="Food AI Explorer", page_icon="🍔", layout="wide")

cookie_manager = stx.CookieManager(key="cookie_manager")

# Wait for cookies to load
cookies = cookie_manager.get_all()
if cookies is None:
    st.stop()

# If the cookie has auth_user, set/restore session state
cookie_user = cookie_manager.get(cookie="auth_user")
if st.session_state.get("logged_out"):
    st.session_state["authenticated_user"] = None
    if not cookie_user:
        st.session_state["logged_out"] = False
else:
    if cookie_user:
        st.session_state["authenticated_user"] = cookie_user
    elif "authenticated_user" not in st.session_state:
        st.session_state["authenticated_user"] = None

st.markdown("""
<style>
    @import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;600&display=swap');
    html, body, [class*="css"]  { font-family: 'Inter', sans-serif; background-color: #0b192c; color: #e2e8f0; }
    h1, h2, h3 { color: #38bdf8 !important; font-weight: 600; letter-spacing: 0.5px; }
    div[data-testid="stSidebar"] { background: rgba(11, 25, 44, 0.95) !important; backdrop-filter: blur(10px); border-right: 1px solid #1e293b; }
    .stButton>button { background: linear-gradient(135deg, #0ea5e9, #0284c7); color: white; border: none; border-radius: 6px; }
    .stButton>button:hover { transform: scale(1.02); }
    .stTextInput>div>div>input, .stNumberInput>div>div>input, .stSelectbox>div>div>div { background-color: #0f172a; color: #f8fafc; border: 1px solid #38bdf8; caret-color: #f8fafc !important; }
</style>
""", unsafe_allow_html=True)

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

with st.sidebar:
    st.title("User Portal 🔐")
    render_version()
    
    with st.expander("ℹ️ Welcome"):
        st.info("Welcome to the secure Local Food AI environment.")
            
    if st.session_state["authenticated_user"]:
        st.success(f"Logged in as: {st.session_state['authenticated_user']}")
        if st.button("Logout"):
            st.session_state["logged_out"] = True
            st.session_state["authenticated_user"] = None
            cookie_manager.delete("auth_user")
            time.sleep(0.5)
            st.rerun()
            
        eav_data = get_eav_profile(st.session_state["authenticated_user"])
        uid = get_user_id(st.session_state["authenticated_user"])
        user_lim = get_user_limit(st.session_state["authenticated_user"])
        
        with st.expander("⚙️ Account Preferences"):
            opts = ["10", "20", "50", "100", "All"]
            idx = opts.index(user_lim) if user_lim in opts else 2
            new_lim = st.selectbox("Default Search Limit", opts, index=idx)
            if new_lim != user_lim:
                conn = get_db_connection('app_auth')
                with conn.cursor() as c:
                    c.execute("UPDATE users SET search_limit = %s WHERE id = %s", (new_lim, uid))
                    conn.commit()
                st.rerun()

        with st.expander("➕ Add Condition / Diet"):
            new_cat = st.selectbox("Category", ["Condition", "Illness", "Diet", "Dislike", "Allergy"])
            
            if new_cat == "Condition":
                new_val = st.selectbox("Value", ["Pregnant", "Breastfeeding", "Low Fat"])
            elif new_cat == "Illness":
                new_val = st.selectbox("Value", ["Diabetes", "Hypertension", "Kidney Disease", "Osteoporosis", "Scurvy", "Anemia"])
            elif new_cat == "Diet":
                new_val = st.selectbox("Value", ["Vegan", "Vegetarian", "Kosher", "Halal", "Christian", "Good Friday", "Ash Wednesday", "Keto", "Paleo"])
            else:
                new_val = st.text_input("Value (e.g. 'peanuts', 'broccoli')").strip()
                
            new_val_clean = new_val.lower()
            
            if st.button("Add to Profile") and new_val_clean and uid:
                conn = get_db_connection('app_auth')
                with conn.cursor() as c:
                    c.execute("INSERT INTO user_health_profiles (user_id, illness_health_condition_diet_dislikes_name, illness_health_condition_diet_dislikes_value) VALUES (%s, %s, %s)", (uid, new_cat.lower(), new_val_clean))
                    conn.commit()
                st.rerun()
                
        if eav_data:
            st.markdown("#### Active Flags")
            for e in eav_data:
                col1, col2 = st.columns([4, 1])
                col1.info(f"**{e['name']}:** {e['value'].title()}")
                if col2.button("X", key=f"del_eav_{e['id']}"):
                    conn = get_db_connection('app_auth')
                    with conn.cursor() as c:
                        c.execute("DELETE FROM user_health_profiles WHERE id = %s", (e['id'],))
                        conn.commit()
                    st.rerun()
    else:
        tab1, tab2, tab3 = st.tabs(["Login", "Register", "Reset"])
        with tab1:
            l_user = st.text_input("Username", key="l_user").strip()
            l_pass = st.text_input("Password", type="password", key="l_pass")
            if st.button("Login"):
                if verify_login(l_user, l_pass):
                    notifier.send_alert(f"User Login Success: {l_user}")
                    st.session_state["authenticated_user"] = l_user
                    import datetime
                    # Set cookie with 30 days expiration
                    cookie_manager.set(
                        "auth_user",
                        l_user,
                        expires_at=datetime.datetime.now() + datetime.timedelta(days=30)
                    )
                    time.sleep(0.2)
                    st.rerun()
                else:
                    notifier.send_alert(f"User Login Failed: {l_user}")
                    st.error("Invalid login.")
        with tab2:
            r_user = st.text_input("Username", key="r_user")
            r_email = st.text_input("Email Address", key="r_email")
            r_pass = st.text_input("Password", type="password", key="r_pass")
            if st.button("Register"):
                if len(r_pass) < 6: st.error("Password too short.")
                elif register_user(r_user, r_pass, r_email): st.success("Registered safely!")
                else: st.error("Username exists.")
        with tab3:
            f_user = st.text_input("Username", key="f_user")
            f_email = st.text_input("Registered Email", key="f_email")
            if st.button("Send Reset Link"):
                status = reset_password(f_user, f_email)
                if status is True: 
                    st.success("Password reset emailed.")
                else: 
                    st.error(f"Failed: {status}")

if not st.session_state["authenticated_user"]:
    st.title("🍔 Food AI Medical Explorer")
    st.info("Please login to interrogate the Clinical Data.")
    st.stop()

st.title("🍔 Food AI Clinical Explorer")
conn_reader = get_db_connection('app_reader')

tab_chat, tab_explore, tab_plate, tab_planner = st.tabs(["💬 AI Chat", "🔬 Clinical Search", "🍽️ My Plate Builder", "🤖 AI Meal Planner"])

import re

with tab_chat:
    c1, c2 = st.columns([4, 1])
    c1.subheader("Chat with the Context")
    if c2.button("🧹 Clear Chat"):
        st.session_state["messages"] = [{"role": "assistant", "content": "How can I help you analyze the food data today?"}]
        st.rerun()
    st.info("""
    ℹ️ **How to use this feature (Examples)**
    **Your active conditions (e.g. Pregnant, Diabetic) are automatically sent to the AI in the background. You do not need to type them out.**
    
    *Examples:*
    1. "I am pregnant, diabetic, and have kidney problems. Can I eat sushi?"
    2. "What is a safe snack to stabilize my blood sugar without hurting my kidneys?"
    3. "Can I drink milk? I need calcium for the baby."
    4. "Is it safe to eat a large steak for iron?"
    5. "What foods are strictly forbidden for me?"
    """)
    if "messages" not in st.session_state:
        st.session_state["messages"] = [{"role": "assistant", "content": "How can I help you analyze the food data today?"}]

    # Display chat history, filtering out TOOL_CALLS and XML tags
    for msg in st.session_state.messages:
        if msg["role"] == "tool": continue
        # Clean both old format [TOOL_CALLS] and new XML tags <sql_query>/<web_search>/<scratchpad>
        display_text = re.sub(r'\[TOOL_CALLS\]\s*\[.*?\]', '', msg["content"], flags=re.DOTALL)
        display_text = re.sub(r'<(sql_query|web_search|scratchpad)>.*?</\1>', '', display_text, flags=re.DOTALL).strip()
        if display_text:
            st.chat_message(msg["role"]).write(display_text)

    if prompt := st.chat_input("Ask a clinical question about your food..."):
        st.session_state.messages.append({"role": "user", "content": prompt})
        st.chat_message("user").write(prompt)
        
        user_eav = get_eav_profile(st.session_state["authenticated_user"])
        profile_text = ", ".join([f"{p['name']}: {p['value']}" for p in user_eav]) if user_eav else "None"
        
        sys_prompt = f"""You are a helpful, autonomous clinical dietitian and food analyst AI.
You have access to a local MySQL database 'food_db' and a web search tool.
You MUST determine dynamically when to query the database or search the web. Do not ask the user for permission.

DATABASE SCHEMA (MySQL database 'food_db'):
- Table 'products_core':
  * 'code' (VARCHAR(255) PRIMARY KEY): barcode/unique key
  * 'product_name' (LONGTEXT): name of product
  * 'generic_name' (LONGTEXT): description
  * 'brands' (LONGTEXT): brand
  * 'ingredients_text' (LONGTEXT): text ingredient list
- Table 'products_macros':
  * 'code' (VARCHAR(255) PRIMARY KEY): joins products_core.code
  * 'energy-kcal_100g' (DOUBLE): calories per 100g
  * 'proteins_100g', 'fat_100g', 'carbohydrates_100g', 'sugars_100g', 'fiber_100g', 'sodium_100g', 'salt_100g', 'cholesterol_100g' (all DOUBLE)
- Table 'products_vitamins':
  * 'code' (VARCHAR(255) PRIMARY KEY): joins products_core.code
  * `vitamin-a_100g`, `vitamin-b1_100g`, `vitamin-b2_100g`, `vitamin-pp_100g`, `vitamin-b6_100g`, `vitamin-b9_100g`, `vitamin-b12_100g`, `vitamin-c_100g`, `vitamin-d_100g`, `vitamin-e_100g`, `vitamin-k_100g` (all DOUBLE)
- Table 'products_minerals':
  * 'code' (VARCHAR(255) PRIMARY KEY): joins products_core.code
  * 'calcium_100g', 'iron_100g', 'magnesium_100g', 'potassium_100g', 'zinc_100g' (all DOUBLE)
- Table 'products_allergens':
  * 'code' (VARCHAR(255) PRIMARY KEY): joins products_core.code
  * 'allergens' (LONGTEXT): comma-separated allergens list

TOOL TAGS (Generate these inside your response when you need details):
1. To run a read-only SELECT query, write:
   <sql_query>SELECT ...</sql_query>
   Make sure to join tables on `code`. Optimize queries using the FULLTEXT indexes on `products_core` for maximum speed.
   Available FULLTEXT indexes:
   * `idx_prod_name` on `product_name`
   * `idx_ing_text` on `ingredients_text`
   * `idx_prod_ing` on `product_name, ingredients_text`
   Use MATCH() AGAINST('+word' IN BOOLEAN MODE) for searching. For example:
   <sql_query>SELECT c.product_name, m.proteins_100g FROM products_core c JOIN products_macros m ON c.code = m.code WHERE MATCH(c.product_name) AGAINST('+apple' IN BOOLEAN MODE) LIMIT 5;</sql_query>
   
2. To run a web search for information not in the database, write:
   <web_search>query</web_search>

CRITICAL CLINICAL RULES:
- When asked about any food's nutritional profile, you MUST provide its typical amino acid profile (such as tryptophan, lysine, methionine, threonine, valine, leucine, isoleucine, phenylalanine, histidine, etc.) by default. If the database does not contain exact values, calculate/estimate them based on the food's protein content using standard clinical formulas and your clinical knowledge.
- Health profile constraints: {profile_text}. If the user's health profile indicates restrictions (e.g. low sodium for hypertension, low sugar for diabetes, low protein for kidney issues), analyze the foods they query against these criteria and issue a clear clinical warning (⚠️ or 💚).
- Do not micromanage or guess if you can query the database. Query the database first for exact product data whenever possible!

INSTRUCTIONS:
- First think in a <scratchpad> about what tools (if any) you need.
- If you need database or web data, output the tool tag. The system will run it and give you the result.
- Once you have the context or if you don't need any tool, output your final answer directly. Keep your reasoning brief and focus on details.
"""

        # We start the loop for autonomous tool calling
        max_turns = 4
        current_turn = 0
        
        # Build messages list filtering system prompts
        temp_messages = [{"role": "system", "content": sys_prompt}]
        for m in st.session_state.messages:
            if m["role"] == "system": continue
            temp_messages.append({"role": m["role"], "content": m["content"]})
            
        try:
            full_assistant_reply = ""
            start_llm = time.time()
            
            with st.chat_message("assistant"):
                placeholder = st.empty()
                status_container = st.container()
                
                while current_turn < max_turns:
                    current_turn += 1
                    
                    # Stream the response chunk by chunk
                    response_stream = ollama.chat(model=get_active_model(), messages=temp_messages, stream=True)
                    turn_reply = ""
                    
                    # Clean display text (strip scratchpad and tool tags while streaming)
                    for chunk in response_stream:
                        content = chunk['message']['content']
                        turn_reply += content
                        
                        # Show stream output in real time
                        display_text = re.sub(r'<(sql_query|web_search|scratchpad)>.*?</\1>', '', turn_reply, flags=re.DOTALL)
                        display_text = re.sub(r'<(sql_query|web_search|scratchpad)>.*$', '', display_text, flags=re.DOTALL).strip()
                        if display_text:
                            placeholder.markdown(display_text)
                            
                    full_assistant_reply += "\n" + turn_reply
                    
                    # Check if there are tool tags in this turn's response
                    sql_match = re.search(r'<sql_query>(.*?)</sql_query>', turn_reply, re.DOTALL)
                    web_match = re.search(r'<web_search>(.*?)</web_search>', turn_reply, re.DOTALL)
                    
                    if sql_match:
                        sql_query = sql_match.group(1).strip()
                        with status_container.expander(f"🔍 Querying database: {sql_query}", expanded=True):
                            db_results = query_sql_readonly(sql_query)
                            st.write(db_results)
                        
                        # Add assistant output and tool result to context
                        temp_messages.append({"role": "assistant", "content": turn_reply})
                        temp_messages.append({"role": "user", "content": f"Database Results:\n{db_results}"})
                    elif web_match:
                        web_query = web_match.group(1).strip()
                        with status_container.expander(f"🌐 Searching the web: {web_query}", expanded=True):
                            web_results = query_web_searxng(web_query)
                            st.write(web_results)
                            
                        temp_messages.append({"role": "assistant", "content": turn_reply})
                        temp_messages.append({"role": "user", "content": f"Web Search Results:\n{web_results}"})
                    else:
                        # No tool calls generated. This is the final answer!
                        break
                
                # Render the final cleaned reply to ensure it's displayed completely
                final_clean_reply = re.sub(r'<(sql_query|web_search|scratchpad)>.*?</\1>', '', turn_reply, flags=re.DOTALL).strip()
                placeholder.markdown(final_clean_reply)
                st.caption(f"⏱️ AI response generated in {time.time() - start_llm:.2f} seconds")
                
            st.session_state.messages.append({"role": "assistant", "content": turn_reply})
            
        except Exception as e: 
            error_msg = str(e)
            if "not found" in error_msg.lower() or "404" in error_msg.lower():
                ai_reply = f"Hold on! Engine execution fault: {e}. Please check the LLM_MODEL variable in your .env file."
            else:
                ai_reply = f"Hold on! Engine execution fault: {e}"
            st.session_state.messages.append({"role": "assistant", "content": ai_reply})
            st.chat_message("assistant").write(ai_reply)

def highlight_medical_warnings(row):
    try:
        val = str(row.get('Medical Warning', ''))
        if '⚠️' in val: return ['background-color: rgba(255, 0, 0, 0.4); color: white;'] * len(row)
        if '💚' in val: return ['background-color: rgba(0, 255, 0, 0.3); color: white;'] * len(row)
    except: pass
    return [''] * len(row)

with tab_explore:
    st.subheader("Clinical Data Search")
    st.info("""
    ℹ️ **How to use this feature (Examples)**
    **Your active conditions are automatically flagged (⚠️ or 💚) in the search results.**
    
    *Example Searches:*
    1. `Cereal` *(Checks for high sugar & hidden phosphorus)*
    2. `Cheese` *(Checks for unpasteurized pregnancy risks & high sodium)*
    3. `Fruit Juice` *(Checks for high sugar spikes)*
    4. `Deli Meat` *(Checks for Listeria risk & extreme sodium)*
    5. `White Rice` *(Safe for kidneys but flags high glycemic index)*
    """)
    with st.form("explore_search_form"):
        sq = st.text_input("Search Product Name or Ingredient")
        cols = st.columns(5)
        min_pro = cols[0].number_input("Min Protein (g)", 0, 1000, 0)
        min_fat = cols[1].number_input("Min Fat (g)", 0, 1000, 0)
        min_carb = cols[2].number_input("Min Carbs (g)", 0, 1000, 0)
        max_sug = cols[3].number_input("Max Sugar (g)", 0, 1000, 1000)
        
        # Load dynamically fetched limit to prevent Pandas Styler crash
        pd.set_option("styler.render.max_elements", 5000000)
        opts = [10, 50, 100, 500, 1000]
        
        user_lim_str = get_user_limit(st.session_state["authenticated_user"])
        user_lim_val = 1000 if user_lim_str == "All" else int(user_lim_str)
        if user_lim_val not in opts: user_lim_val = 50
        idx = opts.index(user_lim_val)
        limit_rc = cols[4].selectbox("Limit Results", opts, index=idx)
        
        submit_search = st.form_submit_button("Search Database")
        if submit_search:
            st.session_state["trigger_search"] = True
            
    if st.session_state.get("trigger_search", False) and sq and conn_reader:
        notifier.send_alert(f"Medical DB Search Executed: {sq}")
        with st.spinner("Processing massive clinical query..."):
            try:
                with conn_reader.cursor() as cursor:
                    l_str = "" if limit_rc == "All" else f"LIMIT {limit_rc}"
                    query = f"""
                        SELECT c.code, c.product_name, c.generic_name, c.brands, c.ingredients_text,
                               c.url, c.image_url, c.image_small_url, c.image_ingredients_url, 
                               c.image_ingredients_small_url, c.image_nutrition_url, c.image_nutrition_small_url,
                               a.allergens,
                               m.`energy-kcal_100g`, m.proteins_100g, m.fat_100g, m.carbohydrates_100g, m.sugars_100g, m.fiber_100g, m.sodium_100g, m.salt_100g, m.cholesterol_100g,
                               v.`vitamin-a_100g`, v.`vitamin-b1_100g`, v.`vitamin-b2_100g`, v.`vitamin-pp_100g`, v.`vitamin-b6_100g`, v.`vitamin-b9_100g`, v.`vitamin-b12_100g`, v.`vitamin-c_100g`, v.`vitamin-d_100g`, v.`vitamin-e_100g`, v.`vitamin-k_100g`,
                               min.calcium_100g, min.iron_100g, min.magnesium_100g, min.potassium_100g, min.zinc_100g
                        FROM (
                            SELECT code, product_name, generic_name, brands, ingredients_text,
                                   url, image_url, image_small_url, image_ingredients_url, 
                                   image_ingredients_small_url, image_nutrition_url, image_nutrition_small_url
                            FROM food_db.products_core
                            WHERE (MATCH(product_name, ingredients_text) AGAINST(%s IN BOOLEAN MODE) OR product_name LIKE %s)
                            AND product_name IS NOT NULL AND product_name != '' AND product_name != 'None'
                            ORDER BY MATCH(product_name) AGAINST(%s IN BOOLEAN MODE) DESC, MATCH(ingredients_text) AGAINST(%s IN BOOLEAN MODE) DESC
                            {l_str}
                        ) c
                        LEFT JOIN food_db.products_allergens a ON c.code = a.code
                        LEFT JOIN food_db.products_macros m ON c.code = m.code
                        LEFT JOIN food_db.products_vitamins v ON c.code = v.code
                        LEFT JOIN food_db.products_minerals min ON c.code = min.code
                        WHERE (m.proteins_100g >= %s OR m.proteins_100g IS NULL)
                        AND (m.fat_100g >= %s OR m.fat_100g IS NULL)
                        AND (m.carbohydrates_100g >= %s OR m.carbohydrates_100g IS NULL)
                        AND (m.sugars_100g <= %s OR m.sugars_100g IS NULL)
                    """
                    sq_bool = " ".join([f"+{w}" for w in sq.split()])
                    sq_like = f"%{sq}%"
                    start_time = time.time()
                    cursor.execute(query, (sq_bool, sq_like, sq_bool, sq_bool, min_pro, min_fat, min_carb, max_sug))
                    results = cursor.fetchall()
                    elapsed = time.time() - start_time
                    st.caption(f"⏱️ Execution Trace: Module=MySQL | Time={elapsed:.3f} seconds")
                    
                    if results:
                        # Fetch EAV Medical Profile
                        eav_profile = get_eav_profile(st.session_state["authenticated_user"])
                        df = pd.DataFrame(results)
                        df.replace(r'^\s*$', None, regex=True, inplace=True)
                        for col in df.columns:
                            if col.endswith('_100g'):
                                df[col] = pd.to_numeric(df[col], errors='coerce')
                        
                        st.markdown("### 🛠️ Dynamic Column Display")
                        default_columns = [
                            'code', 'product_name', 'generic_name', 'brands', 'image_small_url', 'allergens', 'ingredients_text',
                            'proteins_100g', 'fat_100g', 'carbohydrates_100g', 'sugars_100g', 'sodium_100g', 'energy-kcal_100g',
                            'vitamin-c_100g', 'iron_100g', 'calcium_100g'
                        ]
                        all_fetched_cols = list(df.columns)
                        valid_defaults = [c for c in default_columns if c in all_fetched_cols]
                        
                        if "selected_columns" not in st.session_state or st.button("Reset Default Columns"):
                            st.session_state["selected_columns"] = valid_defaults
                            st.rerun()
                            
                        chosen_cols = st.multiselect("Customize Dataset View", all_fetched_cols, default=st.session_state["selected_columns"], key="multi_cols")
                        st.session_state["selected_columns"] = chosen_cols
                        
                        # Filter dataframe gracefully, but we retain a copy for background analytics
                        df_display = df[chosen_cols].copy()
                        warnings_col = []
                        
                        for idx, row in df.iterrows():
                            warns = []
                            ing_text = str(row['ingredients_text']).lower()
                            all_text = str(row['allergens']).lower()
                            
                            for param in eav_profile:
                                cat = param['name'].lower()
                                val = param['value']
                                
                                # Disease Analytics
                                if cat == 'illness':
                                    if val == 'diabetes' and pd.notnull(row.get('sugars_100g')) and float(row['sugars_100g']) > 10.0:
                                        warns.append("⚠️ High Sugar (Diabetes)")
                                    if (val == 'hypertension' or val == 'high bp') and pd.notnull(row.get('sodium_100g')) and float(row['sodium_100g']) > 1.5:
                                        warns.append("⚠️ High Salt (Hypertension)")
                                    if val == 'scurvy' and pd.notnull(row.get('vitamin-c_100g')) and float(row['vitamin-c_100g']) > 0.005:
                                        warns.append("💚 High Vitamin C (Scurvy Recommended)")
                                    if val == 'anemia' and pd.notnull(row.get('iron_100g')) and float(row['iron_100g']) > 0.002:
                                        warns.append("💚 High Iron (Anemia Recommended)")
                                        
                                # Condition Analytics
                                if cat == 'condition':
                                    if val == 'pregnant':
                                        if ('cru' in ing_text or 'raw' in ing_text or 'viande crue' in ing_text):
                                            warns.append("⚠️ Raw Foods (Pregnancy Toxoplasmosis)")
                                        if pd.notnull(row.get('iron_100g')) and float(row['iron_100g']) > 0.002:
                                            warns.append("💚 Med-High Iron (Pregnancy Health)")
                                    if val == 'low fat' and pd.notnull(row.get('fat_100g')) and float(row['fat_100g']) > 20.0:
                                        warns.append("⚠️ High Fat")
                                    if val == 'osteoporosis' and pd.notnull(row.get('calcium_100g')) and float(row['calcium_100g']) > 0.1:
                                        warns.append("💚 High Calcium (Bone Health)")
                                        
                            if eav_data:
                                ing_text = str(row.get('ingredients_text', '')).lower()
                                all_text = str(row.get('allergens', '')).lower()
                                product_name_text = str(row.get('product_name', '')).lower()
                                
                                for e in eav_data:
                                    cat = str(e['name']).lower()
                                    val = str(e['value']).lower()
                                    
                                    # Clinical Trace Checks...
                                    if cat == 'condition' and (val == 'pregnant' or val == 'pregnancy' or val == 'breastfeeding'):
                                        # Forbidden / High Risk (Toxoplasmosis & Listeria)
                                        if any(x in ing_text or x in product_name_text for x in ['cru', 'raw', 'viande crue', 'sushi', 'sashimi', 'poisson cru']):
                                            warns.append("⚠️ Forbidden: Raw Meat/Fish (Toxoplasmosis/Parasite Risk)")
                                        if any(x in ing_text or x in product_name_text for x in ['lait cru', 'unpasteurized', 'non pasteurisé']):
                                            warns.append("⚠️ Forbidden: Unpasteurized Dairy (Listeria Risk)")
                                        if any(x in ing_text or x in product_name_text for x in ['alcool', 'wine', 'alcohol', 'beer']):
                                            warns.append("⚠️ Forbidden: Contains Alcohol")
                                            
                                        # Recommended (Iron & Calcium)
                                        if float(row.get('iron_100g', 0) or 0) > 0.003:
                                            warns.append("💚 Recommended: High Iron (Pregnancy Health)")
                                        if float(row.get('calcium_100g', 0) or 0) > 0.120:
                                            warns.append("💚 Recommended: High Calcium (Bone Health / Breastfeeding)")
                                    
                                    if cat == 'illness' and val == 'osteoporosis':
                                        if float(row.get('calcium_100g', 0) or 0) < 0.120:
                                            warns.append("⚠️ Low Calcium (Osteoporosis Risk)")
                                        else:
                                            warns.append("💚 Recommended (High Calcium)")
                                            
                                    if cat == 'illness' and val == 'scurvy':
                                        if float(row.get('vitamin-c_100g', 0) or 0) < 0.010:
                                            warns.append("⚠️ Low Vitamin C (Scurvy Risk)")
                                        else:
                                            warns.append("💚 Recommended (High Vitamin C)")
                                            
                                    if cat == 'diet' and val in ['vegan', 'vegetarian']:
                                        if any(x in ing_text for x in ['meat', 'beef', 'chicken', 'fish', 'gelatin', 'whey', 'pork', 'porc', 'poulet']):
                                            warns.append("⚠️ Contains Animal Products")
                                    if cat == 'diet' and val == 'halal':
                                        if any(x in ing_text for x in ['pork', 'pig', 'porc', 'wine', 'alcohol', 'beer', 'vin']):
                                            warns.append("⚠️ Probable Haram Ingredients (e.g. Pork/Wine)")
                                            
                                    if cat in ['dislike', 'allergy']:
                                        if val in ing_text or val in all_text or val in product_name_text:
                                            warns.append(f"⚠️ Contains: {val.upper()}")
                                            
                            warnings_col.append(" | ".join(list(set(warns))) if warns else "✅ Safe for Profile")
                            
                        df_display.insert(0, 'Medical Warning', warnings_col)
                        # Clean image URLs in df_display before displaying
                        for col in df_display.columns:
                            if 'image' in col.lower():
                                df_display[col] = df_display[col].apply(lambda x: x if is_valid_image_url(x) else "")
                        df_display.replace(to_replace=[None, 'None', 'nan', 'NaN'], value='\u00a0', inplace=True)
                        # Only fillna with empty string on object columns to avoid Arrow float64 conversion errors
                        for col in df_display.columns:
                            if df_display[col].dtype == 'object':
                                df_display[col] = df_display[col].fillna("")
                        df_display.index = range(1, len(df_display) + 1)
                        styled_df = df_display.style.apply(highlight_medical_warnings, axis=1)

                        col_configs = {}
                        for col in df_display.columns:
                            if 'image' in col.lower():
                                col_configs[col] = st.column_config.ImageColumn(col.replace('_', ' ').title())

                        st.success(f"Analysed {len(results)} records utilizing dynamic Partitions!")
                        st.dataframe(styled_df, column_config=col_configs, use_container_width=True, hide_index=True)
                        
                        if st.button("🤖 Ask AI to Evaluate This Table"):
                            with st.spinner("AI is dynamically evaluating these records against your profile..."):
                                user_eav = get_eav_profile(st.session_state["authenticated_user"])
                                profile_text = ", ".join([f"{p['name']}: {p['value']}" for p in user_eav]) if user_eav else "None"
                                start_eval = time.time()
                                minimal_records = df_display[['product_name', 'Medical Warning']].head(10).to_dict('records')
                                eval_prompt = f"The user has this profile: {profile_text}. Evaluate these top foods and state which are highly recommended or strictly forbidden: {minimal_records}. Be extremely precise regarding carbohydrate content and do not hallucinate any values. Provide a direct, readable clinical summary. Do not output raw JSON."
                                try:
                                    response = ollama.chat(model=get_active_model(), messages=[{'role': 'user', 'content': eval_prompt}], stream=True)
                                    st.write_stream(chunk['message']['content'] for chunk in response)
                                    elapsed_eval = time.time() - start_eval
                                    st.caption(f"⏱️ Execution Trace: Module=Ollama | Time={elapsed_eval:.2f} seconds")
                                except Exception as e:
                                    error_msg = str(e).lower()
                                    if "404" in error_msg or "not found" in error_msg:
                                        st.warning("⚠️ The AI engine is currently downloading its core models in the background. Please wait a minute and try again!")
                                    else:
                                        st.error(f"AI Evaluation Failed: {e}")
                    else:
                        st.warning("No products found matching those strict terms.")
            except Exception as e: st.error(f"SQL/Pandas Error: {e}")

with tab_plate:
    st.subheader("🍽️ My Plate Builder")
    st.info("""
    ℹ️ **How to use this feature (Examples & Logic)**
    **Plate Builder Logic:**
    1. Create a New Plate.
    2. Search for exact food words (e.g. 'chicken', 'egg').
    3. Add the food with a specific portion (e.g. '150g').
    4. The system calculates the combined macros.
    5. Use the 🗑️ buttons to delete incorrect items or entire plates.
    
    *Example Plates:*
    1. `add White Rice use 150g then add Chicken Breast use 50g add Green Beans use 100g`
    2. `add Potatoes use 200g then add Tomatoes use 100g add Beef use 100g`
    3. `add Spinach Salad use 100g then add Feta Cheese use 50g`
    4. `add Lentils use 200g then add Quinoa use 100g`
    5. `add Apple use 100g then add Almonds use 30g`
    """)
    uid = get_user_id(st.session_state["authenticated_user"])
    conn = get_db_connection('app_auth')
    if conn and uid:
        with conn.cursor() as cursor:
            cursor.execute("SELECT id, plate_name FROM plates WHERE user_id = %s", (uid,))
            plates = cursor.fetchall()
            
            st.markdown("#### ➕ Create a New Plate")
            col_p1, col_p2 = st.columns([3, 1])
            new_plate_name = col_p1.text_input("Plate Name (e.g., 'Spaghetti Bolognese')", key="new_plate")
            if col_p2.button("Create Plate", use_container_width=True) and new_plate_name:
                cursor.execute("INSERT INTO plates (user_id, plate_name) VALUES (%s, %s)", (uid, new_plate_name))
                conn.commit()
                st.session_state["active_plate"] = new_plate_name
                st.rerun()
            
            st.markdown("---")

            if plates:
                colA, colB = st.columns([4, 1])
                plate_names = [p['plate_name'] for p in plates]
                default_idx = plate_names.index(st.session_state["active_plate"]) if "active_plate" in st.session_state and st.session_state["active_plate"] in plate_names else 0
                selected_plate = colA.selectbox("Select Active Plate to Edit Ingredients", plate_names, index=default_idx)
                st.session_state["active_plate"] = selected_plate
                active_p_id = next(p['id'] for p in plates if p['plate_name'] == selected_plate)
                
                if colB.button("🗑️ Delete Plate"):
                    cursor.execute("DELETE FROM plates WHERE id = %s", (active_p_id,))
                    conn.commit()
                    if "active_plate" in st.session_state: del st.session_state["active_plate"]
                    st.rerun()
                
                cursor.execute("""
                    SELECT i.id, i.product_code, MAX(i.quantity_grams) as quantity_grams, 
                           MAX(p.product_name) as product_name, MAX(p.ingredients_text) as ingredients_text,
                           MAX(m.proteins_100g) as proteins_100g, MAX(m.fat_100g) as fat_100g, MAX(m.carbohydrates_100g) as carbohydrates_100g, 
                           MAX(m.sodium_100g) as sodium_100g, MAX(m.sugars_100g) as sugars_100g, MAX(m.fiber_100g) as fiber_100g,
                           MAX(v.`vitamin-a_100g`) as vitamin_a_100g, MAX(v.`vitamin-b1_100g`) as vitamin_b1_100g, 
                           MAX(v.`vitamin-b2_100g`) as vitamin_b2_100g, MAX(v.`vitamin-pp_100g`) as vitamin_pp_100g, 
                           MAX(v.`vitamin-b6_100g`) as vitamin_b6_100g, MAX(v.`vitamin-b9_100g`) as vitamin_b9_100g, 
                           MAX(v.`vitamin-b12_100g`) as vitamin_b12_100g, MAX(v.`vitamin-c_100g`) as vitamin_c_100g, 
                           MAX(v.`vitamin-d_100g`) as vitamin_d_100g, MAX(v.`vitamin-e_100g`) as vitamin_e_100g, 
                           MAX(v.`vitamin-k_100g`) as vitamin_k_100g,
                           MAX(min.calcium_100g) as calcium_100g, MAX(min.iron_100g) as iron_100g, 
                           MAX(min.magnesium_100g) as magnesium_100g, MAX(min.potassium_100g) as potassium_100g, 
                           MAX(min.zinc_100g) as zinc_100g,
                           GROUP_CONCAT(DISTINCT a.allergens SEPARATOR ', ') as allergens
                    FROM plate_items i 
                    LEFT JOIN products_core p ON i.product_code = p.code 
                    LEFT JOIN products_macros m ON i.product_code = m.code 
                    LEFT JOIN products_vitamins v ON i.product_code = v.code
                    LEFT JOIN products_minerals min ON i.product_code = min.code
                    LEFT JOIN products_allergens a ON i.product_code = a.code
                    WHERE i.plate_id = %s
                    GROUP BY i.id, i.product_code
                """, (active_p_id,))
                items = cursor.fetchall()
                if items:
                    for i in items:
                        c1, c2 = st.columns([5, 1])
                        pro = float(i['proteins_100g'] or 0) * (float(i['quantity_grams'])/100.0)
                        fat = float(i['fat_100g'] or 0) * (float(i['quantity_grams'])/100.0)
                        carb = float(i['carbohydrates_100g'] or 0) * (float(i['quantity_grams'])/100.0)
                        c1.markdown(f"<li><b>{i['quantity_grams']}g</b> of {i['product_name']} (Pro: {pro:.2f}g | Fat: {fat:.2f}g | Carb: {carb:.2f}g)</li>", unsafe_allow_html=True)
                        if c2.button("🗑️", key=f"del_item_{i['id']}"):
                            cursor.execute("DELETE FROM plate_items WHERE id = %s", (i['id'],))
                            conn.commit()
                            st.rerun()
                            
                    totals = {
                        "Total Protein (g)": sum((float(i['proteins_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Total Fat (g)": sum((float(i['fat_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Total Carbs (g)": sum((float(i['carbohydrates_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Sodium (g)": sum((float(i['sodium_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Sugars (g)": sum((float(i['sugars_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Fiber (g)": sum((float(i['fiber_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin A (g)": sum((float(i['vitamin_a_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin B1 (g)": sum((float(i['vitamin_b1_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin B2 (g)": sum((float(i['vitamin_b2_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin B3/PP (g)": sum((float(i['vitamin_pp_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin B6 (g)": sum((float(i['vitamin_b6_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin B9 (g)": sum((float(i['vitamin_b9_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin B12 (g)": sum((float(i['vitamin_b12_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin C (g)": sum((float(i['vitamin_c_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin D (g)": sum((float(i['vitamin_d_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin E (g)": sum((float(i['vitamin_e_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Vitamin K (g)": sum((float(i['vitamin_k_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Calcium (g)": sum((float(i['calcium_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Iron (g)": sum((float(i['iron_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Magnesium (g)": sum((float(i['magnesium_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Potassium (g)": sum((float(i['potassium_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                        "Zinc (g)": sum((float(i['zinc_100g'] or 0) * (float(i['quantity_grams'])/100.0)) for i in items),
                    }
                    
                    st.markdown("---")
                    st.markdown("### Plate Totals")
                    metrics = list(totals.items())
                    for idx in range(0, len(metrics), 3):
                        cols = st.columns(3)
                        for j in range(3):
                            if idx + j < len(metrics):
                                name, val = metrics[idx + j]
                                cols[j].metric(name, f"{val:.5f}" if val < 0.1 else f"{val:.2f}")

                    aliments_list = []
                    for i in items:
                        prod_name = str(i.get('product_name') or '').strip()
                        if prod_name and prod_name.lower() not in ['none', '']:
                            aliments_list.append(prod_name)
                        ing_text = str(i.get('ingredients_text') or '').strip()
                        if ing_text:
                            import re
                            parts = re.split(r'[,;()\[\]\n\r]', ing_text)
                            for p in parts:
                                p_clean = re.sub(r'[*_\d%]+', '', p).strip()
                                if len(p_clean) > 2 and p_clean.lower() not in ['ingredients', 'and', 'contains', 'may contain', 'natural', 'artificial', 'flavors', 'flavor', 'preservative', 'color', 'colors']:
                                    aliments_list.append(p_clean)
                                    
                    seen = set()
                    unique_aliments = []
                    for a in aliments_list:
                        a_low = a.lower()
                        if a_low not in seen:
                            seen.add(a_low)
                            unique_aliments.append(a)
                            
                    table_data = query_plate_allergens(unique_aliments)
                    
                    st.markdown("---")
                    if table_data:
                        st.warning("⚠️ **Plate Allergens Detected:**")
                        import pandas as pd
                        st.table(pd.DataFrame(table_data))
                    else:
                        st.success("✅ **No Allergens Detected**")
                
                st.markdown("---")
                st.markdown("#### ➕ Add Food to Plate")
                with st.form("plate_add_form"):
                    add_search = st.text_input("Search Exact Product Name (e.g. 'chicken', 'egg')")
                    
                    col_scope, col_comp = st.columns(2)
                    search_scope = col_scope.radio("Search Scope", ["Auto (Cascaded)", "Product Name Only", "Both (Product & Ingredients)", "Ingredients Only"], horizontal=True)
                    comp_reqs = col_comp.multiselect("Require Nutrients (Sorts by highest)", ["Iron", "Vitamin C", "Calcium", "Proteins", "Fiber"])
                    raw_ingredient_filter = col_scope.radio("Raw Ingredient Only?", ["No", "Yes"], horizontal=True)
                    
                    submit_add_search = st.form_submit_button("Search Food")
                
                if add_search and submit_add_search:
                    bool_search = " ".join([f"+{w}" for w in add_search.split()])
                    start_time = time.time()
                    
                    def execute_search(match_col_override=None):
                        m_col = "product_name"
                        if match_col_override: m_col = match_col_override
                        elif "Both" in search_scope: m_col = "product_name, ingredients_text"
                        elif "Ingredients" in search_scope: m_col = "ingredients_text"
                        
                        join_min = "LEFT JOIN food_db.products_minerals min ON c.code = min.code" if any(n in comp_reqs for n in ["Iron", "Calcium"]) else ""
                        join_vit = "LEFT JOIN food_db.products_vitamins v ON c.code = v.code" if "Vitamin C" in comp_reqs else ""
                        
                        r_clauses, o_clauses = [], []
                        if "Iron" in comp_reqs: r_clauses.append("min.iron_100g > 0"); o_clauses.append("min.iron_100g DESC")
                        if "Vitamin C" in comp_reqs: r_clauses.append("v.`vitamin-c_100g` > 0"); o_clauses.append("v.`vitamin-c_100g` DESC")
                        if "Calcium" in comp_reqs: r_clauses.append("min.calcium_100g > 0"); o_clauses.append("min.calcium_100g DESC")
                        if "Proteins" in comp_reqs: r_clauses.append("m.proteins_100g > 0"); o_clauses.append("m.proteins_100g DESC")
                        if "Fiber" in comp_reqs: r_clauses.append("m.fiber_100g > 0"); o_clauses.append("m.fiber_100g DESC")
                        
                        wh_comp = " AND " + " AND ".join(r_clauses) if r_clauses else ""
                        order_by = "ORDER BY " + ", ".join(o_clauses) if o_clauses else ""
                        
                        raw_clause = ""
                        # Note: raw ingredient filter is bypassed since image columns are omitted on the server schema
                        
                        sql = f"""
                            SELECT c.code, c.product_name, c.image_small_url, c.image_ingredients_small_url, c.image_nutrition_small_url
                            FROM (
                                SELECT code, product_name, image_small_url, image_ingredients_small_url, image_nutrition_small_url
                                FROM food_db.products_core
                                WHERE MATCH({m_col}) AGAINST(%s IN BOOLEAN MODE)
                                AND product_name IS NOT NULL AND product_name != '' AND product_name != 'None'
                                {raw_clause}
                                ORDER BY LENGTH(product_name) ASC
                            ) c
                            JOIN food_db.products_macros m ON c.code = m.code
                            {join_min}
                            {join_vit}
                            WHERE m.proteins_100g IS NOT NULL AND m.fat_100g IS NOT NULL AND m.carbohydrates_100g IS NOT NULL
                            {wh_comp}
                            {order_by}
                        """
                        cursor.execute(sql, (bool_search,))
                        return cursor.fetchall()

                    search_res = execute_search()
                    
                    if not search_res and search_scope == "Auto (Cascaded)":
                        st.warning("No product found in names, so I am looking into the ingredients...")
                        search_res = execute_search("ingredients_text")
                        
                    elapsed = time.time() - start_time
                    st.caption(f"⏱️ Execution Trace: Module=MySQL | Time={elapsed:.3f} seconds")
                    st.session_state['plate_search_res'] = search_res

                if st.session_state.get('plate_search_res'):
                    search_res = st.session_state['plate_search_res']
                    
                    # Select Product Table Gallery
                    st.markdown("##### 🔍 Found Products Preview")
                    df_rows = []
                    for r in search_res:
                        df_rows.append({
                            "Code": r['code'],
                            "Product Name": r['product_name'],
                            "Image": r.get('image_small_url') if is_valid_image_url(r.get('image_small_url')) else "",
                            "Ingredients Image": r.get('image_ingredients_small_url') if is_valid_image_url(r.get('image_ingredients_small_url')) else "",
                            "Nutrition Image": r.get('image_nutrition_small_url') if is_valid_image_url(r.get('image_nutrition_small_url')) else "",
                        })
                    gallery_df = pd.DataFrame(df_rows)
                    gallery_df.replace(to_replace=[None, 'None', 'nan', 'NaN'], value='\u00a0', inplace=True)
                    gallery_df.index = range(1, len(gallery_df) + 1)
                    event = st.dataframe(
                        gallery_df,
                        column_config={
                            "Image": st.column_config.ImageColumn("Image"),
                            "Ingredients Image": st.column_config.ImageColumn("Ingredients"),
                            "Nutrition Image": st.column_config.ImageColumn("Nutrition"),
                        },
                        use_container_width=True,
                        on_select="rerun",
                        selection_mode="single-row",
                        key="product_preview_table"
                    )
                    
                    selected_row_idx = None
                    if event and hasattr(event, "selection"):
                        sel = event.selection
                        if isinstance(sel, dict) and sel.get("rows"):
                            selected_row_idx = sel["rows"][0]
                        elif hasattr(sel, "get") and sel.get("rows"):
                            selected_row_idx = sel.get("rows")[0]
                    
                    options = {f"{r['product_name']} ({r['code']})": r for r in search_res}
                    options_list = list(options.keys())
                    
                    default_idx = 0
                    if selected_row_idx is not None and selected_row_idx < len(options_list):
                        default_idx = selected_row_idx
                        
                    selected_str = st.selectbox("Select Product", options_list, index=default_idx)
                    selected_product = options[selected_str]
                    
                    add_amount_str = st.text_input("Portion Quantity (e.g., '100g', '2 tbsp', '1.5 cups', '1 pinch')", value="100g")
                    
                    if st.button("Add Item to Plate"):
                        start_add = time.time()
                        # Use UnitConverter to parse
                        grams = UnitConverter.parse_and_convert(add_amount_str, product_name=selected_product['product_name'])
                        if grams is not None:
                            cursor.execute("INSERT INTO plate_items (plate_id, product_code, quantity_grams) VALUES (%s, %s, %s)", 
                                          (active_p_id, selected_product['code'], grams))
                            conn.commit()
                            st.success(f"Added {grams}g of {selected_product['product_name']}!")
                            elapsed_add = time.time() - start_add
                            st.caption(f"⏱️ Execution Trace: Module=UnitConverter, MySQL | Time={elapsed_add:.3f} seconds")
                            st.session_state.pop('plate_search_res', None)
                            st.rerun()
                        else:
                            st.error("Could not parse unit. Please use format like '100g' or '1 cup'.")
                elif add_search and submit_add_search:
                    st.warning("No products found.")

with tab_planner:
    st.subheader("🤖 AI Meal Planner")
    st.info("""
    ℹ️ **How to use this feature (Examples)**
    **Your active conditions are automatically applied to the generated menu.**
    
    *Example Prompts:*
    1. "Generate a full day meal plan for me. I am pregnant, diabetic, and have kidney disease."
    2. "Plan a pregnancy-safe dinner that won't spike my blood sugar."
    3. "I need a high-iron lunch that is safe for my kidneys."
    4. "Plan a breakfast without dairy that is kidney-friendly."
    5. "Give me a 3-day meal prep plan ensuring no raw fish, controlled protein portions, and steady complex carbs."
    """)
    p_col1, p_col2, p_col3 = st.columns(3)
    target_cal = p_col1.number_input("Target Daily Calories (kcal)", 1000, 5000, 2000, 50)
    diet_pref = p_col2.selectbox("Dietary Preference", ["Omnivore", "Vegetarian", "Vegan", "Keto", "Paleo"])
    meal_count = p_col3.slider("Number of Meals", 1, 6, 3)
    extra_notes = st.text_input("Any additional allergies or goals?")
    
    if st.button("Generate Professional Menu"):
        st.session_state.pop("generated_meal_plan", None)
        st.session_state.pop("meal_plan_pdf_data", None)
        st.session_state.pop("meal_plan_elapsed", None)
        
        with st.spinner("Executing Lightning-Fast Context RAG..."):
            user_eav = get_eav_profile(st.session_state["authenticated_user"])
            profile_text = ", ".join([f"{p['name']}: {p['value']}" for p in user_eav]) if user_eav else "None"
            
            # Pre-fetch database context directly without using AI tools!
            # Enforce the strict clinical constraints directly via SQL
            db_context = search_nutrition_db(diet_pref, user_eav)
            
            meal_names = ["Breakfast", "Lunch", "Dinner", "Morning Snack", "Afternoon Snack", "Evening Snack"]
            selected_meals = ", ".join(meal_names[:int(meal_count)])
            
            sys_prompt = f"""You are a professional clinical Dietitian planner. Target: {target_cal}kcal. 
            You MUST generate EXACTLY {meal_count} meals and NO MORE. Failure to respect the meal count is a critical clinical error.
            The allowed meal(s) are strictly: {selected_meals}.
            Dietary constraint: {diet_pref}. Additional notes: {extra_notes}.
            Health profile: {profile_text}. 
            
            - Under no circumstances should you hallucinate any nutritional values. No hallucinations. 
            - Base all calculations and values strictly on the database context provided: {db_context}.
            
            COGNITIVE SCRATCHPAD INSTRUCTIONS:
            - You MUST perform all your intermediate thinking, unit conversions (e.g. converting cups, tablespoons, or ounces to exact metric grams based on food density), and calorie/protein mathematical additions inside a `<scratchpad>` tag.
            - Format:
              <scratchpad>
              Calculations:
              - 1.5 cups of Cheese = X grams (density Y). Calories = A, Protein = B, Carbs = C, Fat = D.
              - 2 tbsp of Peanut Butter = Z grams (density C). Calories = D, Protein = E, Carbs = F, Fat = G.
              - Summation: Total Calories = A + D = Z kcal (vs target {target_cal}kcal). Total Protein = B + E = Fg.
              - </scratchpad>
              | Meal Time | Exact Food | Portion Size | Calories | Protein | Carbs | Fat |
              | --- | --- | --- | --- | --- | --- | --- |
              ...
              | Global Total | All Meals | | Total Calories | Total Protein | Total Carbs | Total Fat |
            
            CRITICAL FORMATTING INSTRUCTIONS:
            - After the </scratchpad> closing tag, you MUST strictly output the menu formatted as a Markdown Table.
            - The table MUST contain exactly 7 columns separated by pipes (|): | Meal Time | Exact Food | Portion Size | Calories | Protein | Carbs | Fat |
            - The Portion Size MUST be reported in exactly metric grams (e.g. 200g) and NEVER in cups or oz.
            - The items in the table MUST be selected strictly from: {db_context}
            - Do NOT output JSON. Do NOT use tool calls. Skip pleasantries.
            """
            
            st.info("🧠 AI is analyzing nutritional synergies and generating your plan...")
            
            # Stream the response instantly!
            try:
                start_llm = time.time()
                placeholder = st.empty()
                response = ollama.chat(model=get_active_model(), messages=[
                    {'role': 'system', 'content': sys_prompt},
                    {'role': 'user', 'content': 'Generate my meal plan as a markdown table.'}
                ], stream=True)
                raw_chunks = []
                clean_stream = filter_scratchpad_stream(response, raw_chunks)
                ai_reply = placeholder.write_stream(clean_stream)
                raw_reply = "".join(raw_chunks)
                
                # Strip scratchpad and calculate totals
                clean_reply = strip_scratchpad(raw_reply)
                
                def add_total_row_to_markdown_table(text):
                    import re
                    lines = text.split('\n')
                    table_start = -1
                    table_end = -1
                    for i, line in enumerate(lines):
                        line_s = line.strip()
                        if line_s.startswith('|') and line_s.endswith('|'):
                            if 'Meal Time' in line or 'Exact Food' in line or 'Portion' in line:
                                if table_start == -1:
                                    table_start = i
                            if table_start != -1:
                                table_end = i
                    if table_start == -1 or table_end == -1:
                        return text
                    total_cal, total_pro, total_carb, total_fat = 0.0, 0.0, 0.0, 0.0
                    for idx in range(table_start + 2, table_end + 1):
                        line = lines[idx].strip()
                        if not (line.startswith('|') and line.endswith('|')):
                            continue
                        cols = [c.strip() for c in line.strip('|').split('|')]
                        if len(cols) < 7:
                            continue
                        if any(w in cols[0].lower() or w in cols[1].lower() for w in ['total', 'summation', 'global']):
                            continue
                        def clean_num(val):
                            nums = re.findall(r'([0-9]+(?:\.[0-9]+)?)', val)
                            return float(nums[-1]) if nums else 0.0
                        total_cal += clean_num(cols[3])
                        total_pro += clean_num(cols[4])
                        total_carb += clean_num(cols[5])
                        total_fat += clean_num(cols[6])
                    total_row = f"| Total Summary | All Meals | - | {total_cal:.1f} kcal | {total_pro:.1f}g | {total_carb:.1f}g | {total_fat:.1f}g |"
                    lines.insert(table_end + 1, total_row)
                    return "\n".join(lines)

                final_reply = add_total_row_to_markdown_table(clean_reply)
                
                # Align numeric columns on the right
                def align_markdown_table(text):
                    lines = text.split('\n')
                    for i, line in enumerate(lines):
                        line_s = line.strip()
                        if line_s.startswith('|') and line_s.endswith('|') and '---' in line_s:
                            parts = [p.strip() for p in line_s.strip('|').split('|')]
                            if len(parts) >= 7:
                                new_parts = []
                                for idx, part in enumerate(parts):
                                    if idx < 3:
                                        new_parts.append('---')
                                    else:
                                        new_parts.append('---:')
                                lines[i] = '| ' + ' | '.join(new_parts) + ' |'
                                break
                    return '\n'.join(lines)
                
                final_reply = align_markdown_table(final_reply)
                
                # PDF Generation
                def generate_pdf(text):
                    import re
                    # Aggressive sanitization: if a table row has 4 columns and the last contains a comma or space before 'g', split it
                    sanitized_lines = []
                    for line in text.split('\n'):
                        line = line.strip()
                        if line.startswith('|') and line.endswith('|') and '---' not in line:
                            cols = [c.strip() for c in line.strip('|').split('|')]
                            # If exactly 4 columns and the last one contains calories and protein merged
                            if len(cols) == 4 and any(char.isdigit() for char in cols[3]):
                                # Attempt to split by comma or 'kcal'
                                if ',' in cols[3]:
                                    split_last = cols[3].split(',', 1)
                                    cols = cols[:3] + [split_last[0].strip(), split_last[1].strip()]
                                elif 'kcal' in cols[3].lower():
                                    split_last = re.split(r'(?<=kcal)\s+', cols[3], flags=re.IGNORECASE, maxsplit=1)
                                    if len(split_last) == 2:
                                        cols = cols[:3] + [split_last[0].strip(), split_last[1].strip()]
                            sanitized_lines.append('| ' + ' | '.join(cols) + ' |')
                        else:
                            sanitized_lines.append(line)
                    text = '\n'.join(sanitized_lines)

                    pdf = FPDF()
                    pdf.add_page()
                    pdf.set_font("Helvetica", 'B', 16)
                    pdf.cell(0, 10, "Strict Clinical Meal Plan", new_x="LMARGIN", new_y="NEXT", align='C')
                    pdf.ln(h=5)
                    table_data = []
                    
                    def flush_table():
                        if not table_data: return
                        pdf.set_font("Helvetica", size=9)
                        # Auto-calculate col_widths based on 5 columns if present
                        cw = (20, 40, 15, 10, 15) if len(table_data[0]) == 5 else (20, 30, 15, 10, 10, 10, 10) if len(table_data[0]) >= 7 else None
                        alignments = ("LEFT", "LEFT", "LEFT", "RIGHT", "RIGHT", "RIGHT", "RIGHT") if len(table_data[0]) >= 7 else ("LEFT", "LEFT", "LEFT", "RIGHT", "RIGHT") if len(table_data[0]) == 5 else "LEFT"
                        try:
                            with pdf.table(text_align=alignments, col_widths=cw) as table:
                                for row_data in table_data:
                                    row = table.row()
                                    for datum in row_data:
                                        row.cell(str(datum).encode('latin-1', 'replace').decode('latin-1'))
                        except Exception as e:
                            pdf.multi_cell(0, 8, "Table Render Error: " + str(e))
                        table_data.clear()
                        pdf.ln(h=5)

                    for line in text.split('\n'):
                        line = line.strip()
                        if not line:
                            flush_table()
                            pdf.ln(h=2)
                            continue
                        
                        if line.startswith('|') or ('|' in line and 'Total' in line):
                            if not line.endswith('|'): line += ' |'
                            if not line.startswith('|'): line = '| ' + line
                            
                            if '---' in line: continue
                            cols = [col.strip() for col in line.strip('|').split('|')]
                            
                            # Normalize column length to prevent FPDF table crashing
                            if table_data:
                                target_len = len(table_data[0])
                                while len(cols) < target_len: cols.append("")
                                cols = cols[:target_len]
                                
                            table_data.append(cols)
                        else:
                            flush_table()
                            pdf.set_font("Helvetica", size=11)
                            clean_line = str(line).encode('latin-1', 'replace').decode('latin-1')
                            pdf.multi_cell(0, 8, clean_line)
                            
                    flush_table()
                            
                    current_dir = os.path.dirname(os.path.abspath(__file__))
                    tmp_dir = os.path.join(current_dir, "tmp")
                    os.makedirs(tmp_dir, exist_ok=True)
                    pdf_path = os.path.join(tmp_dir, "meal_plan.pdf")
                    pdf.output(pdf_path)
                    with open(pdf_path, "rb") as f:
                        return f.read()

                st.session_state['generated_meal_plan'] = final_reply
                st.session_state['meal_plan_pdf_data'] = generate_pdf(final_reply)
                st.session_state['meal_plan_elapsed'] = time.time() - start_llm
                st.rerun()
                
            except Exception as e:
                error_msg = str(e).lower()
                if "404" in error_msg or "not found" in error_msg:
                    st.warning("⚠️ The AI engine is currently downloading its core models in the background. Please wait a minute and try again!")
                else:
                    st.error(f"AI Generation Failed: {e}")

    # Outside the button, render the session state if exists
    if 'generated_meal_plan' in st.session_state:
        st.info("🧠 AI is analyzing nutritional synergies and generating your plan...")
        st.markdown(st.session_state['generated_meal_plan'])
        if st.session_state.get('meal_plan_elapsed'):
            st.caption(f"⏱️ Execution Trace: Module=Ollama, MySQL | Time={st.session_state['meal_plan_elapsed']:.2f} seconds")
            
        st.download_button(
            label="📄 Download PDF Export",
            data=st.session_state['meal_plan_pdf_data'],
            file_name="Clinical_Meal_Plan.pdf",
            mime="application/pdf",
            type="primary"
        )

if conn_reader: conn_reader.close()