from fastapi import FastAPI, HTTPException from fastapi.middleware.cors import CORSMiddleware from fastapi.responses import FileResponse from pydantic import BaseModel from openai import OpenAI from datetime import datetime from pathlib import Path import requests import json import re import os app = FastAPI( title="Store Product Search API", description=( "Three-step RAG pipeline for natural-language product search:\n" " 1. User message → NVIDIA Llama 3.1 70B (SQL generator)\n" " 2. Generated SQL → PHP DB layer (execution)\n" " 3. Query results + user message → NVIDIA Llama 3.1 70B (natural language reply)" ), ) # ── CORS ─────────────────────────────────────────────────────────────────────── app.add_middleware( CORSMiddleware, allow_origins=["*"], allow_credentials=True, allow_methods=["*"], allow_headers=["*"], ) # ── Configuration ────────────────────────────────────────────────────────────── NVIDIA_API_KEY = os.environ.get("NVIDIA_API_KEY", "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") # Set as env/secret PHP_DB_URL = os.environ.get("PHP_DB_URL", "https://ctkart.com/api/db_api.php") INTERNAL_SECRET = os.environ.get("INTERNAL_SECRET", "change_this_secret_in_production") # Output directory for SQL query results OUTPUT_DIR = Path("./sql_results") OUTPUT_DIR.mkdir(exist_ok=True) # NVIDIA LLM Configuration (for SQL generation and natural language replies) LLM_URL = "https://integrate.api.nvidia.com/v1" LLM_MODEL = "meta/llama-3.1-70b-instruct" # Validate API key if not NVIDIA_API_KEY: raise RuntimeError( "❌ NVIDIA_API_KEY is not set!\n" "Set it with: export NVIDIA_API_KEY='your_actual_key'\n" "Or add to .env: NVIDIA_API_KEY=your_actual_key" ) # Initialize OpenAI client with NVIDIA base URL (OpenAI-compatible API) client = OpenAI( base_url=LLM_URL, api_key=NVIDIA_API_KEY ) print(f"✓ NVIDIA API configured with model: {LLM_MODEL}") # Anthropic kept for reference (can be removed if fully migrating to NVIDIA) # ANTHROPIC_URL = "https://api.anthropic.com/v1/messages" # ANTHROPIC_MODEL = "claude-sonnet-4-20250514" # ANTHROPIC_HEADERS = {...} PHP_HEADERS = { "Content-Type": "application/json", "X-Internal-Secret": INTERNAL_SECRET, } # ── Database schema (injected into the SQL-generation prompt) ───────────────── DB_SCHEMA = """ Table: item_master (main product table) Columns: id INT Primary key, auto-increment item_name VARCHAR(1000) Product name (e.g. 'Bag-B27-BLACK', 'LADIES PURSE B-55', 'WS-253 BLACK PU-SLIPPER') category_id INT FK — category (see known category IDs below) subcategory_id INT FK — subcategory BrandID INT FK — brands VendorID INT FK — vendor store_id INT Always 1 in this dataset mrp DOUBLE MRP price in INR (e.g. 496, 410, 599) hsn VARCHAR(50) HSN/tax code (e.g. '4202' for bags, '6405' for footwear, '61112000' for kids clothes, '5407' for sarees) size_dimension VARCHAR(45) Physical dimensions or clothing size (e.g. 'W-24 X H-17.5 X B-6', '4, 5, 6, 7', '26, 28, 30') weight DECIMAL(11,2) Weight in grams color VARCHAR(45) Hex color code (e.g. '#000000' for black, '#ff0000' for red, '#1b1b18' for near-black) packingtype VARCHAR(30) Packing type: 'PCS', 'Box', 'Other' packingtime INT Days to pack tax_p DOUBLE Tax percentage (18 for bags/footwear, 5 for clothing) reorder_qty INT Reorder quantity threshold Qty INT Current stock quantity (filter Qty >= 0 for in-stock) stock_movement VARCHAR(50) Always 'FIFO' saleprice DOUBLE Actual selling price in INR (usually mrp/2; use this as the display price) dis_p DOUBLE Discount percentage (0–100; if > 0 item is on discount) description TEXT Product description status VARCHAR(20) 'Active' or 'Inactive' — always filter WHERE status = 'Active' ongoing_offer VARCHAR(11) 'yes' or 'no' discount_percentage VARCHAR(11) Additional discount label Known category_id values (approximate — use LIKE on item_name for category filtering): 1 = Girls' Frocks / Baby Frocks 3 = Sarees (chiffon, dola silk) 9 = Men's Footwear (slippers) 10 = Women's Footwear (sandals, slippers, heels, flip-flops) 11 = Kids' Sets / Combo (boy/girl outfit sets, kids' purses) 13 = Bags (backpacks, shoulder bags, sling bags, combo bags) 19 = Shoulder Bags / Sling Bags (women) 20 = Ladies Purses / Clutches / Wallets 21 = Mobile Side Bags / Cross Body Bags 22 = Sling Bags / Cross Body Bags 23 = Handbags / Clutches / Ladies Sling Bags Color notes: color is stored as hex (#000000 = Black, #ffffff = White). For color search by name, use item_name LIKE '%COLOR%' (e.g. item_name LIKE '%BLACK%'). Table: product_images (product photos) Columns: image_id INT Primary key product_id INT FK → item_master.id path_url VARCHAR(100) Filename (e.g. '83_1769753521_01.avif') — prepend your base image URL default_img VARCHAR(1) 'y' = primary/default image, 'n' = additional image img_seq INT Display order (1 = first shown) To get the images for a product: LEFT JOIN product_images ON item_master.id = product_images.product_id Table: brands (brand information) Columns: BrandID INT Primary key Brand VARCHAR(100) Brand name (e.g. 'Nike', 'Adidas', 'Reebok') Table: category (product categories) Columns: category_id INT Primary key category_name VARCHAR(100) Category name (e.g. 'Men's Footwear', '') Table: subcategory (product subcategories) Columns: subcategoryid INT Primary key subcategory VARCHAR(100) Subcategory name (e.g. 'Slippers', 'Sarees', 'Kids Sets') Table: vendor (vendor information) Columns: ID INT Primary key Name VARCHAR(100) Vendor name (e.g. 'Vendor A', 'Vendor B') """ # ── System prompts ───────────────────────────────────────────────────────────── SQL_SYSTEM_PROMPT = f"""You are an expert MySQL query generator for an online fashion store. DATABASE SCHEMA: {DB_SCHEMA} Your task: - Analyse the user's natural language product search request. - Generate a single valid MySQL SELECT query that retrieves matching products. - ALWAYS include these columns: id, name, category, brand, color, size, gender, price, discount_pct, stock, rating, description - Apply WHERE filters based on what the user asked (price, color, size, category, gender, etc.) - Use LIKE for partial text matches on name, category, brand, color. - For price constraints: "within 500", "under 500", "below 500" → price <= 500; "above 500" → price >= 500; "between X and Y" → price BETWEEN X AND Y - For size: match exactly. Map "XXL" → size = 'XXL', "extra large" → size = 'XL', etc. - For color: use LIKE '%Blue%' (case-insensitive intent). - For product image: use LEFT JOIN product_images ON item_master.id = product_images.product_id AND product_images.default_img = 'y' to get the main image URL. - Always filter WHERE stock >= 0 (only show in-stock items). - Add ORDER BY rating DESC, price ASC to surface best value first. - LIMIT results to 10 rows maximum. - Return ONLY the raw SQL query — no markdown fences, no explanation, no preamble. Just the SQL. Rules: - Never use DROP, DELETE, INSERT, UPDATE, ALTER, TRUNCATE, or any DML/DDL. - Only SELECT from the item_master table and joined tables. - If the user's request is vague (e.g. "I want a nice dress"), generate a broad query that returns popular/relevant items (e.g. SELECT ... WHERE category LIKE '%dress%' OR name LIKE '%dress%' ORDER BY rating DESC LIMIT 10). - Don't include Qty column of item_master in WHERE clause. - If the request is ambiguous, generate a broad but relevant query. """ RESPONSE_SYSTEM_PROMPT = """You are a helpful, friendly online fashion store assistant named "ShopBot". Your task: - You receive the user's original search query and a JSON array of matching products from the database. - Respond in a warm, conversational tone — like a knowledgeable sales assistant. - Summarize what was found, highlight 2–3 standout products with specific details (name, price, color, size, rating). - If no products are found, suggest alternatives or ask clarifying questions. - Keep responses concise (3–5 sentences max unless listing products). - Format product listings clearly: use "→" bullet style. - Always mention price in ₹ (Indian Rupees). - Do NOT mention SQL, databases, or internal systems to the user. - End with a helpful follow-up question or offer (e.g., "Want me to filter by a specific color?"). """ # ── Request / Response models ────────────────────────────────────────────────── class SearchRequest(BaseModel): message: str conversation_history: list = [] # list of {"role": "user"|"assistant", "content": "..."} class SearchResponse(BaseModel): reply: str products: list generated_sql: str row_count: int # ── Helpers ─────────────────────────────────────────────────────────────────── def generate_sql(user_message: str) -> str: """Step 1: Ask NVIDIA Llama to generate SQL from the user's natural language query.""" try: response = client.chat.completions.create( model=LLM_MODEL, messages=[ {"role": "system", "content": SQL_SYSTEM_PROMPT}, {"role": "user", "content": f"User search query: {user_message}\n\nGenerate the SQL SELECT query."}, ], temperature=0.2, top_p=0.7, max_tokens=512, ) raw = response.choices[0].message.content.strip() except Exception as e: raise HTTPException(status_code=502, detail=f"NVIDIA SQL-gen error: {str(e)}") print("SQL-gen response:", raw) # Strip any accidental markdown fences raw = re.sub(r"```sql\s*", "", raw, flags=re.IGNORECASE) raw = re.sub(r"```\s*", "", raw).strip() if not raw.upper().startswith("SELECT"): raise HTTPException( status_code=502, detail=f"SQL generator did not return a SELECT statement. Got: {raw[:200]}", ) print("Generated SQL:", raw) return raw def execute_sql(sql: str) -> dict: """Step 2: Send SQL to the PHP DB layer and get results.""" try: resp = requests.post( PHP_DB_URL, headers=PHP_HEADERS, json={"sql": sql}, timeout=15, ) resp.raise_for_status() except requests.exceptions.RequestException as e: raise HTTPException(status_code=502, detail=f"PHP DB layer error: {str(e)}") result = resp.json() print("DB result:", result) if not result.get("success", False): raise HTTPException( status_code=422, detail=f"Query failed: {result.get('error', 'Unknown DB error')}", ) # Save results to file with timestamp timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") filename = OUTPUT_DIR / f"sql_result_{timestamp}.json" output_data = { "timestamp": datetime.now().isoformat(), "sql_query": sql, "results": result.get("results", []), "row_count": result.get("row_count", 0), "execution_status": "success" if result.get("success") else "failed" } try: with open(filename, "w", encoding="utf-8") as f: json.dump(output_data, f, ensure_ascii=False, indent=2) print(f"✓ Results saved to: {filename}") except Exception as e: print(f"⚠️ Failed to save results to file: {str(e)}") return result def generate_reply(user_message: str, products: list, conversation_history: list) -> str: """Step 3: Ask NVIDIA Llama to turn the query results into a friendly response.""" products_json = json.dumps(products, ensure_ascii=False, indent=2) # Build messages: system + history + current turn messages = [{"role": "system", "content": RESPONSE_SYSTEM_PROMPT}] # Add conversation history messages.extend(conversation_history) # Add current user query messages.append({ "role": "user", "content": ( f"User query: {user_message}\n\n" f"Product search results ({len(products)} items found):\n{products_json}\n\n" f"Please give a helpful, friendly response to the user." ), }) try: response = client.chat.completions.create( model=LLM_MODEL, messages=messages, temperature=0.2, top_p=0.7, max_tokens=1024, ) reply = response.choices[0].message.content.strip() except Exception as e: raise HTTPException(status_code=502, detail=f"NVIDIA reply-gen error: {str(e)}") print("Reply response:", reply) if not reply: raise HTTPException(status_code=502, detail="LLM returned an empty reply") return reply # ── Endpoint ─────────────────────────────────────────────────────────────────── @app.post("/search", response_model=SearchResponse) async def product_search(req: SearchRequest): """ Natural-language product search endpoint. Pipeline: 1. user message → Claude (SQL generator) → SQL SELECT statement 2. SQL → PHP DB layer → JSON product rows 3. products JSON + user message → Claude (ShopBot) → friendly reply Body: - message: the user's search text (e.g. "I need a blue XXL shirt under 500") - conversation_history: optional list of prior turns for context """ if not req.message.strip(): raise HTTPException(status_code=400, detail="Message cannot be empty") # Step 1: Generate SQL sql = generate_sql(req.message) # Step 2: Execute against DB db_result = execute_sql(sql) products = db_result.get("results", []) row_count = db_result.get("row_count", 0) # Step 3: Generate friendly response reply = generate_reply(req.message, products, req.conversation_history) return SearchResponse( reply=reply, products=products, generated_sql=sql, row_count=row_count, ) @app.get("/health") async def health(): return {"status": "healthy", "model": LLM_MODEL, "provider": "NVIDIA"} @app.get("/results") async def list_results(): """List all saved SQL query results.""" try: files = sorted(OUTPUT_DIR.glob("sql_result_*.json"), reverse=True) results_info = [] for f in files[:20]: # Last 20 results results_info.append({ "filename": f.name, "path": str(f), "created": f.stat().st_mtime }) return {"status": "success", "results": results_info, "total": len(results_info)} except Exception as e: raise HTTPException(status_code=500, detail=f"Failed to list results: {str(e)}") @app.get("/results/{filename}") async def get_result(filename: str): """Retrieve a specific saved SQL query result.""" filepath = OUTPUT_DIR / filename if not filepath.exists() or not filepath.suffix == ".json": raise HTTPException(status_code=404, detail="Result file not found") try: return FileResponse(filepath, media_type="application/json", filename=filename) except Exception as e: raise HTTPException(status_code=500, detail=f"Failed to retrieve result: {str(e)}") @app.get("/") async def root(): return FileResponse("index.html") if __name__ == "__main__": import uvicorn uvicorn.run("app:app", host="0.0.0.0", port=8000, reload=True)