eifddlZddlZddlmZmZmZmZmZddlm Z ddl m Z ddl m Z  ddlmZddlmZmZej*eZd Zd e fd Z dd e d ededeeeeefd ef dZde ded eeeeefeffdZ dd e d ededeeeefdedeeeeefd efdZd e deeeefd efdZ dde ded ededededeed eeeffdZ y#e$r ddlmZYwxYw)N)DictAnyListOptionalTuple)Session)text) AzureOpenAI)settings)chat_session_servicechat_history_servicea ## Database Schema for Smart Inventory ### companies - id: INTEGER (Primary Key) - company_id: INTEGER (External company ID) - company_name: VARCHAR(255) ### products - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - fk_product_category_id: INTEGER (Foreign Key -> categories.id) - product_id: INTEGER (External product ID) - product_name: VARCHAR(500) - short_name: VARCHAR(255) - description: TEXT - brand_name: VARCHAR(255) - sku: VARCHAR(255) - is_perishable: BOOLEAN - image_path: VARCHAR(1000) ### categories - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - category_id: INTEGER (External category ID) - category_name: VARCHAR(255) ### locations - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - location_id: INTEGER (External location ID) - location_name: VARCHAR(255) ### vendors - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - vendor_id: INTEGER (External vendor ID) - vendor_name: VARCHAR(255) - vendor_code: VARCHAR(100) ### sales_orders - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - location_id: INTEGER (Foreign Key -> locations.id) - sold_at: TIMESTAMP WITH TIME ZONE - channel: VARCHAR(50) -- e.g., 'store', 'online' - order_date: TIMESTAMP WITH TIME ZONE - ref_number: VARCHAR(100) ### sales_order_lines - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - sales_order_id: INTEGER (Foreign Key -> sales_orders.id) - product_id: INTEGER (Foreign Key -> products.id) - quantity: INTEGER - unit_price: FLOAT - promotion_id: INTEGER (Foreign Key -> discounts.id, nullable) ### inventory_batches - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - batch_ref: VARCHAR(100) (Unique) - quantity_on_hand: INTEGER - expiry_date: TIMESTAMP WITH TIME ZONE (nullable) - received_date: TIMESTAMP WITH TIME ZONE - status: ENUM ('active', 'sold_out', 'expired', 'disposed', 'donated') ### inventory_movements - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - batch_id: INTEGER (Foreign Key -> inventory_batches.id, nullable) - movement_type: ENUM ('sale', 'receipt', 'sale_return', 'purchase_return', 'adjustment', 'transfer_in', 'transfer_out') - quantity_delta: INTEGER (positive for increase, negative for decrease) - reference: VARCHAR(100) - created_at: TIMESTAMP WITH TIME ZONE ### purchase_orders - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - supplier_id: INTEGER - location_id: INTEGER (Foreign Key -> locations.id) - status: ENUM ('draft', 'pending_approval', 'sent', 'received', 'returned', 'closed') - expected_delivery_date: TIMESTAMP WITH TIME ZONE - order_date: TIMESTAMP WITH TIME ZONE - ref_number: VARCHAR(100) ### purchase_order_lines - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - purchase_order_id: INTEGER (Foreign Key -> purchase_orders.id) - product_id: INTEGER (Foreign Key -> products.id) - ordered_qty: INTEGER - received_qty: INTEGER - unit_cost: FLOAT ### daily_sales - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - sale_date: DATE - quantity_sold: INTEGER - total_amount: FLOAT ### service_level_daily - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - date: DATE - demand_qty: INTEGER - fulfilled_qty: INTEGER - lost_sales_qty: INTEGER - service_level: FLOAT (0.0 - 1.0) ### inventory_snapshot_daily - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - snapshot_date: DATE - on_hand_qty: FLOAT - inbound_qty: FLOAT - outbound_qty: FLOAT ### slow_mover_snapshot (for slow/fast moving products analysis) - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - snapshot_date: DATE - on_hand_qty: FLOAT - total_sold_7d: FLOAT (units sold in last 7 days) - total_sold_30d: FLOAT (units sold in last 30 days) - total_sold_90d: FLOAT (units sold in last 90 days) - ads_7d: FLOAT (Average Daily Sales - 7 days) - ads_30d: FLOAT (Average Daily Sales - 30 days) - ads_90d: FLOAT (Average Daily Sales - 90 days) - doh_90d: FLOAT (Days of Inventory on Hand based on ADS_90d) - days_since_last_sale: INTEGER - is_slow_mover: BOOLEAN - slow_mover_severity: VARCHAR(20) -- 'watchlist', 'slow', 'dead' - slow_mover_reason: VARCHAR(255) ### inventory_planning_snapshot (for reorder and stock planning) - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - snapshot_date: DATE - avg_daily_demand: FLOAT - sigma_daily_demand: FLOAT - lead_time_days: INTEGER - review_period_days: INTEGER - service_level_target: FLOAT - current_safety_stock: FLOAT - current_reorder_point: FLOAT - forecast_avg_daily_demand_90d: FLOAT - forecast_safety_stock_90d: FLOAT - forecasted_reorder_point_90d: FLOAT - on_hand_qty: FLOAT - inbound_qty: FLOAT - available_stock: FLOAT - min_target: FLOAT - max_target: FLOAT - stock_status: VARCHAR(20) -- 'out_of_stock', 'low_stock', 'in_stock', 'overstock' - recommended_order_qty: FLOAT - should_reorder: BOOLEAN - days_of_cover: FLOAT - days_until_stockout: FLOAT - is_urgent: BOOLEAN - urgency_score: FLOAT ### monthly_forecasts - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - target_month: DATE (First day of the forecasted month, e.g., 2026-01-01 for January 2026) - forecast_qty: FLOAT (Predicted quantity for the entire month) - model_version: VARCHAR(50) - forecast_date: DATE (When the forecast was generated) - created_at: TIMESTAMP WITH TIME ZONE ### product_prices - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - product_id: INTEGER (Foreign Key -> products.id) - location_id: INTEGER (Foreign Key -> locations.id) - cost_price_per_unit: FLOAT - retail_price_excl_tax: FLOAT - compare_at_price: FLOAT ### discounts - id: INTEGER (Primary Key) - company_id: INTEGER (Foreign Key -> companies.id) - discount_id: INTEGER (External discount ID) - discount_name: VARCHAR(255) - discount_code: VARCHAR(100) - discount_type: VARCHAR(50) -- e.g., 'percentage', 'fixed' - value: FLOAT ## Important Relationships: - All tables have company_id for multi-tenant filtering - products -> categories via fk_product_category_id - sales_order_lines -> sales_orders via sales_order_id - inventory_movements -> inventory_batches via batch_id - purchase_order_lines -> purchase_orders via purchase_order_id returncrttjtjtjS)z=Initialize Azure OpenAI client with settings from environment)api_key api_versionazure_endpoint)r r OPENAI_API_KEYOPENAI_API_VERSIONOPENAI_API_ENDPOINTK/var/www/html/hubwallet-dev/src/smart_inventory/apps/chat_bot/controller.pyget_openai_clientrs* ''//33 rclientquestion company_idconversation_historycdtd|d|d}d|dg}|re|D]`}|ddk(r|jdd |d d&|dd k(s/|d }t|d kDr|d d dz}|jd d|db|jdd|d |jjj t j|dd}|jdjjj} | jdr| dd } | jdr| dd } | jdr| d d} | jS#t$r'} tj!dt#| d } ~ wwxYw)z Agent 1: Convert natural language question to SQL query Uses conversation history for context in follow-up questions zYou are a SQL query generator for a PostgreSQL inventory management database. Your task is to convert natural language questions into valid SQL queries. z4 IMPORTANT RULES: 1. ALWAYS filter by company_id = a> in your queries to ensure data isolation 2. Use proper JOINs when relating tables 3. Return ONLY the SQL query, no explanations or markdown 4. Use PostgreSQL syntax 5. Limit results to 100 rows maximum unless specifically asked for more 6. For date comparisons, use CURRENT_DATE or specific dates in 'YYYY-MM-DD' format 7. Always include relevant column names in SELECT (avoid SELECT *) 8. Use table aliases for readability 9. Handle NULL values appropriately 10. For aggregations, include appropriate GROUP BY clauses CONVERSATION CONTEXT: - You may receive conversation history for follow-up questions - If user refers to "that product", "the first one", "those items", etc., use context from previous messages - If user says "tell me more about X" or "what about location Y", build on previous context CRITICAL - Product-Location Data: - Tables like slow_mover_snapshot and inventory_planning_snapshot store data PER PRODUCT-LOCATION COMBINATION - When asked for "top products" or similar, you should: a) Get the LATEST snapshot_date first (use subquery or MAX) b) Include location_id and location_name in results c) Order by the appropriate metric (ads_30d for fast, ads_90d for slow/stagnant, urgency_score for urgent) QUERY PATTERNS FOR COMMON REQUESTS: For "top N fastest/fast-moving products": - Use slow_mover_snapshot table - Filter by latest snapshot_date - Order by ads_30d DESC (highest daily sales = fastest) - Include product_name, location_name, ads_30d, total_sold_30d For "top N slowest/slow-moving products": - Use slow_mover_snapshot table - Filter by is_slow_mover = TRUE and latest snapshot_date - Order by ads_90d ASC (lowest daily sales = slowest) - Include product_name, location_name, ads_90d, days_since_last_sale, slow_mover_severity For "most stagnant products" or "dead stock": - Use slow_mover_snapshot table - Filter by latest snapshot_date, optionally slow_mover_severity = 'dead' - Order by days_since_last_sale DESC, ads_90d ASC - Include on_hand_qty to show excess stock For "most urgent orders" or "products needing reorder": - Use inventory_planning_snapshot table - Filter by latest snapshot_date AND is_urgent = TRUE or should_reorder = TRUE - Order by urgency_score DESC - Include product_name, location_name, on_hand_qty, days_of_cover, recommended_order_qty For "out of stock" or "stockouts": - Use inventory_planning_snapshot table - Filter by stock_status = 'out_of_stock' and latest snapshot_date For "low stock" products: - Use inventory_planning_snapshot table - Filter by stock_status = 'low_stock' and latest snapshot_date For "overstock" products: - Use inventory_planning_snapshot table - Filter by stock_status = 'overstock' and latest snapshot_date LATEST SNAPSHOT PATTERN: Use this subquery pattern to get latest date: WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM table_name WHERE company_id = z/) Return ONLY the raw SQL query, nothing else.systemrolecontentr"userzPrevious question: r# assistantN...zPrevious answer summary: z&Convert this question to a SQL query: g?imodelmessages temperaturemax_completion_tokensrz```sqlz```zError generating SQL query: )DATABASE_SCHEMAappendlenchat completionscreater OPENAI_DEPLOYMENTchoicesmessager#strip startswithendswith Exceptionloggererrorstr) rrrr system_promptr*msgr#response sql_queryes rgenerate_sql_queryrEs "#-?.U~V`T`a-KG0MT"m<=H' iC6{f$>QRUV_R`Qar?)rFrC sql_upperdangerous_keywordskeywordresultrowscolumnsdatarowrow_dicticolvaluerDs rexecute_sql_queryrems: OO%++- ##H->? ?l) QG)# #EgY!OPP QDO, ++- "CH#G, &3A5+.!OO-E %  & KK ! "SY  23q6(;< sAC5 B*C55 D%>"D  D%r_ data_countcd}t|dkDr|ddn|}tj|dt}d|dg} |rd|dd} | D]Z} | d d k(r| j d | d d#| d d k(s,| d } t| d kDr| dd dz} | j d | d\d|d|d|d} | j d | d |j j jtj| dd}|jdjjjS#t$r'}tj!dt|d}~wwxYw)z Agent 2: Answer the user's question directly based on the data Uses conversation history for contextual follow-up answers aYou are a helpful inventory assistant for a business owner. Your job is to DIRECTLY ANSWER their question using the data provided - not to summarize or explain what the data shows. IMPORTANT GUIDELINES: 1. **Answer the question directly** - If they ask "what are the top 10 products?", just list them clearly 2. **Be concise** - Give the answer first, keep explanations brief 3. **Use simple language** - No technical jargon like "query", "database", "records" 4. **Format for easy reading** - Use numbered lists, bullet points when listing items 5. **Include relevant numbers** - Show quantities, percentages rounded nicely 6. **Add a brief insight** - One or two sentences of helpful context after the answer 7. **Be direct** - Start with the answer, not "Based on the data..." or "Here's what I found..." 8. **Use conversation context** - If this is a follow-up question, connect your answer to the previous conversation Approach: "Your top 10 fast-moving products are: 1. Product A - 1,500 units sold 2. Product B - 1,200 units sold ..." If duplicates appear in the data, consolidate them and mention it briefly. If there's no data, simply say "I don't have any data matching that request." Keep your response SHORT and FOCUSED - just answer what they asked.2N)indentdefaultr r!ir"r$r#r%i,r'z Question: "z " Data (z items): z- Answer the question directly and concisely.333333?ir(rzError summarizing results: )r2jsondumpsr?r1r3r4r5r r6r7r8r#r9r<r=r>)rrrCr_rfrr@ data_sampledata_strr*recent_historyrAr# user_promptrBrDs rsummarize_resultsrssGM2 #4y2~$s)4Kzz+a=H"m<=H-bc2! KC6{f$C N KLV +i.w<#%%dsme3G IJ K"(,l  ,/K OOV <= ;;**11,,"& 2 "**2288::  23q6(;< sA'D)) E2"EEr*cd}d}|D]4}|ddk(rdnd}|d}t|dkDr|d dd z}||d |d z }6 |jjjtj d |ddd|dgdd}|j djjjS#t$r+}tjdt|Yd }~yd }~wwxYw)zR Agent 3: Summarize older conversation history when context gets too long a^You are a conversation summarizer. Your task is to create a concise summary of the conversation between a user and an inventory assistant. GUIDELINES: 1. Capture the key questions asked and main insights provided 2. Include any specific products, locations, or metrics mentioned 3. Note any decisions or conclusions reached 4. Keep the summary under 500 words 5. Focus on information that would be useful for follow-up questions Format: Write in third person, past tense. Example: "The user asked about top-selling products. The assistant reported that Product X at Location Y had the highest sales..." r"r$User Assistantr#r&Nr'z: z r r!zSummarize this conversation: rliXr(rz Error summarizing conversation: z4Previous conversation about inventory data analysis.)r2r3r4r5r r6r7r8r#r9r<r=r>r?) rr*r@conversation_textrAr"r#rBrDs rsummarize_conversation_historyrys M6V.vKi. w<# dsme+GvRy55 6F;;**11,,!m<.NO`Na,bc"%2 "**2288:: F 7Ax@AEFsA2B22 C&;!C!!C&user_idstore_id branch_idchat_session_idc p t}d}|Otj|}d}g} tj d|t j|||||||ntj |} | t j||} | Ntj|}d}tj d|t j|||||||g} ntj|t j||d} tj d |n=t j||d} tj d |d t| d |stj|rtj d tj |} | r`| jdg} t|| } tj|| dtj|} tj dtj d|t|||| }tj d|tj dt!||\}}tj d|dtj dt#|||||| }tj$|d|tj$|d||ddt j$||d|t j$||d|||dd|dS#t&$rp}tj)dt+||1tj|}t j||||||||ddt+|dcYd}~Sd}~wt,$r'}tj/dt+|d}~wwxYw) a Main function to process a chat query with conversation continuity: 1. Get or create session (Redis for in-memory, DB for persistence) 2. Check if history needs summarization 3. Generate SQL from natural language (with context from last 5 Q&A) 4. Execute the SQL query 5. Summarize the results (with context) 6. Save to session history (both Redis and DB) FNTzCreated new chat session: )rFr}rrzr{r|first_questionz Session not found, created new: )nz!Redis expired, restored from DB: zUsing existing session: z with z messages from DBz-Conversation history too long, summarizing...r*) keep_last_nz&Context summarized and history trimmedzGenerating SQL for question: zGenerated SQL: zExecuting SQL query...zQuery returned z recordszSummarizing results...r$r%)raw_datad)r}ranswerzQuery validation error: z!I couldn't process your request: zError processing chat query: )rr create_sessionr=infor get_sessionget_session_by_chat_idget_last_n_messagesr2needs_summarizationgetryset_context_summaryget_conversation_historyrErers add_messagerSwarningr?r<r>)rFrrrzr{r|r}ris_new_sessionr session_data db_session old_messagessummaryrCr_rfrrDs rprocess_chat_queryrs{"$  "2AA*MO!N#% KK4_4EF G ! / / /%!#' 0;;OLL#1HH_] %&:&I&I*&UO%)NKK"B?BS TU)77(7#- '!)"+'/,.()77 C+?+S+STVXgkm+n(KK"COCT UV(<'O'OPRTcgi'j$ 66GvcRfNgMhhyz{"6"J"J?"[ KKG H/;;OLL+// B? 8N$88'_`a';'T'TUd'e$ DE  3H:>?&vxEYZ  oi[12  ,-,R;j oj\:;  ,-"68YjRfg ((&(K((+vX\]`^`Xab ((_fhO((_k6R /Tc    1#a&:;  "2AA*MO / / /%!#'  /9#a&B   4SVH=> s+L L N5A%N<N5 N5"N00N5)N)!loggingrmtypingrrrrrsqlalchemy.ormr sqlalchemyr openair src.utils.settingsr ImportErrorutils.settingsservicesr r getLogger__name__r=r0rr?intrErersryrrrrrs 33"(+A   8 $Tn;OSw{wcwsw-5d4S>6J-KwWZwt$'$c$eDc3hAJ,4T$sCx.5I,JJVYJZ+F;+F$tCQTH~BV+F[^+F`9=G7GGsG #G/2G?BG(0 GAEc3hGK('(sC C*)C*