
    H;i                    ~   d dl mZ d dlmZmZ d dlmZmZ d dlmZ ddl	m
Z
 ddlmZmZ d d	lZd d	lZ ej"                  e      Zd4d
edededee
j*                     fdZd4d
edededee
j.                     fdZd4d
edededee
j2                     fdZd4d
edededee
j6                     fdZd4d
edededee
j:                     fdZd4d
edededee
j>                     fdZ d5d
edee!   dee   dedede"fdZ#d6d
edee!   dedede"f
dZ$d6d
edee!   dedede"f
dZ%d7d
edededee   dee   dee   dee!   de"fdZ&d8d
edededee!   dee   dee   dee   dee'   de"fd Z(	 	 	 	 d9d
edee!   dee   dee   dee   de"fd!Z)	 	 d:d
ededee!   dee   de"f
d"Z*	 	 	 	 d9d
ededee   dee   d#ee!   d$ee!   de"fd%Z+	 	 	 	 d9d
ededee   dee   d#ee!   d$ee!   de"fd&Z,	 	 	 	 	 	 d7d
edededee   dee   dee   d'ee!   de"fd(Z-d;d
edee!   dededee   dee   dee   de"fd)Z.	 d<d
edededed*ej^                  d+e!defd,Z0	 	 	 d=d
ededee   d-ee   dede"fd.Z1	 	 	 	 	 	 d>d
ededee   dee   d/ee!   d0ee!   dedede"fd1Z2	 	 	 d=d
ededee   d-ee   dede"fd2Z3	 	 	 d=d
ededee   d-ee   dede"fd3Z4y	)?    )Session)ListOptional)datetimedatefunc   models   ProductLocationNdbskiplimitreturnc                     | j                  t        j                        j                  |      j	                  |      j                         S )z Get sales orders with pagination)queryr   
SalesOrderoffsetr   allr   r   r   s      L/var/www/html/hubwallet-dev/src/smart_inventory/apps/inventory/controller.pyget_sales_ordersr      s6    88F%%&--d399%@DDFF    c                     | j                  t        j                        j                  |      j	                  |      j                         S )z%Get sales order lines with pagination)r   r   SalesOrderLiner   r   r   r   s      r   get_sales_order_linesr       6    88F))*11$7==eDHHJJr   c                     | j                  t        j                        j                  |      j	                  |      j                         S )z%Get inventory batches with pagination)r   r   InventoryBatchr   r   r   r   s      r   get_inventory_batchesr$      r!   r   c                     | j                  t        j                        j                  |      j	                  |      j                         S )z'Get inventory movements with pagination)r   r   InventoryMovementr   r   r   r   s      r   get_inventory_movementsr'       6    88F,,-44T:@@GKKMMr   c                     | j                  t        j                        j                  |      j	                  |      j                         S )z#Get purchase orders with pagination)r   r   PurchaseOrderr   r   r   r   s      r   get_purchase_ordersr+   &   s6    88F(()006<<UCGGIIr   c                     | j                  t        j                        j                  |      j	                  |      j                         S )z(Get purchase order lines with pagination)r   r   PurchaseOrderLiner   r   r   r   s      r   get_purchase_order_linesr.   ,   r(   r   r   
company_idpageperpagec                    | j                  t        j                  t        j                  t        j
                  t        j                        j                  t        t        j                  j                  t        j                  k(        j                  t        t        j                  j                  t        j                  k(        }|rd	 t        j                  |d      j                         }|j                  t!        j                  t        j                  j"                        |k(        }|,|j                  t        j                  j&                  |k(        }|j)                         }|dz
  |z  }|j+                  |      j-                  |      j/                         }	g }
|	D ]  \  }}}}|j0                  |j&                  |j                  |j                  |j"                  |j2                  |j4                  |j6                  |j8                  |||d}|
j;                  |        |
|||dS # t$        $ r Y w xY w)zGet daily sales with pagination and joined product/store information. If date is provided, returns sales for that specific date. If company_id is provided, filters by company.%Y-%m-%dr
   )idr/   
product_idlocation_id	sale_datequantity_soldtotal_amount
created_at
updated_atproduct_name
brand_namelocation_namedatar0   r1   total)r   r   
DailySalesr   r<   r=   r   r>   joinr5   	outerjoinr6   r   strptimer   filterr	   r7   
ValueErrorr/   countr   r   r   r4   r8   r9   r:   r;   append)r   r   r/   r0   r1   r   target_daterA   r   resultsr@   
daily_saler<   r=   r>   daily_sale_dicts                   r   get_daily_salesrN   2   s    HH	
 d""--1C1CCi&##//83G3GG 
 	"++D*=BBDKLL6+<+<+F+F!G;!VWE V..99ZGH KKME Qh'!Fll6"((1557G D?F %;
L*m--$//$//%11#--'55&33$//$//($*
 	O$%$ 	 E  	 	s   
A#H0 0	H=<H=c                    | j                  t        j                  t        j                  t        j
                  t        j                        j                  t        t        j                  j                  t        j                  k(        j                  t        t        j                  j                  t        j                  k(        }|rQ	 t        j                  |d      j                         }|j                  t        j                  j                  |k(        }|j#                         }|dz
  |z  }|j%                  |      j'                  |      j)                         }g }	|D ]  \  }
}}}|
j*                  |
j                  |
j,                  |
j                  |
j                  |
j.                  |
j0                  |
j2                  |
j4                  |
j6                  |||d}|	j9                  |        |	|||dS # t         $ r Y w xY w)zGet service level data with pagination and joined product/location information. If date is provided, returns service level for that specific date.r3   r
   )r4   r   r/   r6   r5   
demand_qtyfulfilled_qtylost_sales_qtyservice_levelr:   r<   r=   r>   r?   )r   r   ServiceLevelDailyr   r<   r=   r   r>   rC   r5   rD   r6   r   rE   r   rF   rG   rH   r   r   r   r4   r/   rP   rQ   rR   rS   r:   rI   )r   r   r0   r1   r   rJ   rA   r   rK   r@   rS   r<   r=   r>   service_level_dicts                  r   get_service_levelrV   q   s    HH  	
 d))448J8JJi&**66(:N:NN 
 	"++D*=BBDKLL!9!9!>!>+!MNE KKME Qh'!Fll6"((1557G DBI (>|Z""!&&'22(44'22'22*88+::*88'22($*
 	&'!(& 	 ?  		s   
AG: :	HHc                    | j                  t        j                  t        j                  t        j
                  t        j                        j                  t        t        j                  j                  t        j                  k(        j                  t        t        j                  j                  t        j                  k(        }|rQ	 t        j                  |d      j                         }|j                  t        j                  j                   |k(        }|j%                         }|dz
  |z  }|j'                  |      j)                  |      j+                         }g }	|D ]  \  }
}}}|
j,                  |
j                   |
j.                  |
j                  |
j                  |
j0                  |
j2                  |
j4                  |
j6                  |||d}|	j9                  |        |	|||dS # t"        $ r Y w xY w)zGet inventory snapshot data with pagination and joined product/location information. If date is provided, returns snapshots for that specific date.r3   r
   )r4   snapshot_dater/   r6   r5   on_hand_qtyinbound_qtyoutbound_qtyr:   r<   r=   r>   r?   )r   r   InventorySnapshotDailyr   r<   r=   r   r>   rC   r5   rD   r6   r   rE   r   rF   rX   rG   rH   r   r   r   r4   r/   rY   rZ   r[   r:   rI   )r   r   r0   r1   r   rJ   rA   r   rK   r@   snapshotr<   r=   r>   snapshot_dicts                  r   get_inventory_snapshotsr_      s    HH%%	
 d..99W=O=OOi&//;;x?S?SS 
 	"++D*=BBDKLL!>!>!L!LP[![\E KKME Qh'!Fll6"((1557G D=D #9,
M++%33"--#//"--#//#//$11"--($*
 	M"#$ 	 =  		s   
AG/ /	G;:G;r6   r5   statusc                 X   ddl m}m} ddlm}	 ddlm}
m} | j                  |j                  |j                  |j                  |j                        j                  ||j                  |j                  k(        }|r|j                  |j                  |k(        }|r|j                  |j                  |k(        }|r|j                  |j                  |k(        }|j                         }|j!                  |dz
  |z        j#                  |      }|j%                         }g }|D ]  }|j                  }|j                  }|j                  }|j                  }| j                   |
j&                  |	j(                  j*                              j                  |	j(                  j                  |k(  |	j(                  j                  |k(  |	j(                  j                  |k(  |	j(                  j,                  dk(        j/                         xs d}| j                  |	j0                        j                  |	j0                  j                  |k(  |	j0                  j                  |k(  |	j0                  j                  |k(        j3                   ||	j0                  j4                              j7                         }d	}d
}|r|j8                  rd}|j:                  }d}d}d}|dk(  rd}n||k  rd}|dk(  rd}n|dk(  r|dkD  rd}|r||k7  r|j=                  |||||||||d	         ||||dS )zJGet inventory planning data with stock levels, reorder points, and status.r   )r   ProductLocationr
   r   r   r	   descactive        FNTin_stockout_of_stock	low_stockdead
dead_stockslow
slow_mover)	r/   r6   r5   r<   rY   reorder_pointreorder_qtyr`   slow_mover_severityr?   )products.modelsr   rb    r   
sqlalchemyr	   rd   r   r/   r6   r5   r<   rC   rF   rH   r   r   r   sumr#   quantity_on_handr`   scalarSlowMoverSnapshotorder_byrX   firstis_slow_moverrp   rI   )r   r0   r1   r/   r6   r5   r`   r   rb   r   r	   rd   r   rA   product_locsrK   plc_idl_idp_idp_nameon_handsm_snapshotis_slowseverityrn   ro   item_statuss                               r   get_inventory_planningr      s   :% HH""##""	 d++w/A/AA 
 _77:EF_88KGH_77:EF KKMELL$(g-.44W=E99;LG
  C}}~~}} ((8488F$9$9$J$JKLSS!!,,4!!--5!!,,4!!((H4	

 &(
  	 hhv778??$$//47$$00D8$$//47
 (400>>?
@	 	 ;44G"66H  !a<(K%%K v&K !+ kV+""*&!#+

 
	sCT 	 r   rz   c                    ddl m}m}	m}
 ddlm} ddlm} | j                  |j                  |j                  |j                  j                  d      |	j                  |
j                        j                  ||j                  j                  |j                  k(        j                  |	|j                  j                   |	j                   k(        j                  |
|j                  j"                  |
j"                  k(        }|r)|j%                  |j                  j&                  |k(        }n6|j%                  |j                  j&                  |j)                         k(        }|r(|j%                  |j                  j"                  |k(        }|r(|j%                  |j                  j                   |k(        }|r(|j%                  |j                  j                  |k(        }|(|j%                  |j                  j*                  |k(        }|j-                         }|j/                  |dz
  |z        j1                  |      }|j3                         }g }|D ]  }|\  }}}}}i d	|j4                  d
|j&                  d|j"                  d|d|j                   d|d|j                  d|dt7        |      d|j8                  d|j:                  d|j<                  t?        |j<                        ndd|j@                  t?        |j@                        ndd|jB                  d|j*                  d|jD                  d|jF                  d|jH                  i}|jK                  |        ||||dS )5Get slow mover snapshots with pagination and filters.r   r   r   Companyr
   r   r   r   product_skuNr4   rX   r/   company_namer6   r>   r5   r<   rY   total_sold_90dads_90ddoh_90ddays_since_last_salerz   rp   slow_mover_reasonr:   itemsr0   r1   rA   )&rq   r   r   r   rr   r   r   r   r   rw   r<   r5   labelr>   r   rC   r6   r/   rF   rX   todayrz   rH   r   r   r   r4   strrY   r   r   intr   r   rp   r   r:   rI   )r   r0   r1   r   r/   r6   r5   rz   r   r   r   r   	date_typer   rA   rK   mapped_resultsrowr]   r<   r   r>   r   	item_datas                           r   get_slow_moversr   ]  s   <<*HH    / d))448J8JJ
d&**66(:N:NN
d))448J8JJ 
 V55CCtKLV55CCyGXXYV55@@JNOV55AA[PQV55@@JNO V55CC}TU KKME LL$(g-.44W=EiikG N )KNH,]L
(++
X33
 (--
 L	

 8//
 ]
 (--
 L
 3{+
 8//
 h55
 0@0@0Ls8++,RV
 0@0@0Ls8++,RV
 #H$A$A
 X33
  "8#?#?!
"  !;!;#
$ (--%
	* 	i(3)8  	 r   c                 v   ddl m}m}m} ddlm} ddlm}	 |s|	j                         j                  d      }| j                  |j                        j                  |j                  j                  dk(        }
|
j                  |j                  j                  |k(        }
|r(|
j                  |j                  j                  |k(        }
|r(|
j                  |j                  j                   |k(        }
|r(|
j                  |j                  j"                  |k(        }
|
j%                         }| j                  |j&                        }
|
j                  |j&                  j                  |k(        }
|r(|
j                  |j&                  j                  |k(        }
|r(|
j                  |j&                  j                   |k(        }
|r(|
j                  |j&                  j"                  |k(        }
|
j)                         }d}d}|D ],  }|j*                  d	k(  r|dz  }|j*                  d
k(  s(|dz  }. |||dS )r   r   r   r
   r   r   r   r3   TOverstockedUnderstocked)slow_mover_countover_stock_countunder_stock_count)rq   r   r   r   rr   r   r   r   r   strftimer   rw   rF   rz   rX   r/   r6   r5   rH   InventoryPlanningSnapshotr   stock_status)r   r   r/   r6   r5   r   r   r   r   
date_classr   r   rowsr   r   r   s                   r   get_analytics_overviewr     s    =<+ !**:6HH  f  ..$6 
 LL11??4GHEV55@@JNOV55AA[PQV55@@JNO {{}HH((E LL99GG4OPEV==HHJVWV==II[XYV==HHJVW99;D #},!/"	# -,. r   c           	      T   ddl m} ddlm} d}|r%	 t	        j
                  |d      j                         }| j                   |j                  |j                  j                              j                  |j                  j                  |k(        }|r)|j                  |j                  j                  |k(        }n| j                   |j                  |j                  j                              j                  |j                  j                  |k(        j                         }|r(|j                  |j                  j                  |k(        }|r(|j                  |j                  j                   |k(        }|j                         xs d}	| j                  |j"                        j                  |j"                  j                  |k(        }
d}|r+|
j                  |j"                  j$                  |k(        }
|}n| j                   |j                  |j"                  j$                              j                  |j"                  j                  |k(        j                         }|r(|
j                  |j"                  j$                  |k(        }
|r(|
j                  |j"                  j                   |k(        }
|
j'                         }d}d}d}t)        |      }|D ]@  }|j*                  dk(  r|dz  }n|j*                  d	k(  r|dz  }|j,                  dk  s<|dz  }B | j                   |j.                  |j0                  j2                              j                  |j0                  j                  |k(  |j0                  j4                  d
k(        }|r)|j                  |j0                  j$                  |k(        }n| j                   |j                  |j0                  j$                              j                  |j0                  j                  |k(        j                         }|r(|j                  |j0                  j$                  |k(        }|r(|j                  |j0                  j                   |k(        }|j                         xs d}| j                   |j                  |j0                  j6                              j                  |j0                  j                  |k(        }|r)|j                  |j0                  j$                  |k(        }n*r(|j                  |j0                  j$                  |k(        }|r(|j                  |j0                  j                   |k(        }|j                         }|t9        |      }|t9        |	      ||||||dS # t        $ r Y pw xY w)a/  
    Get KPI summary for frontend dashboard cards.
    
    Returns:
        - avg_service_level: Average service level across all products/locations
        - stockouts_count: Number of products currently out of stock
        - overstock_count: Number of overstocked products
        - understock_count: Number of understocked products
        - slow_mover_count: Number of slow-moving products
        - total_products_tracked: Total number of products being tracked
        - avg_days_on_hand: Average days of inventory on hand (stock turns indicator)
    r
   r   r   r   Nr3   rf   r   r   T)rX   avg_service_levelstockouts_countoverstock_countunderstock_countr   total_products_trackedavg_days_on_hand)rr   r   rs   r	   r   rE   r   rG   r   avgrT   rS   rF   r/   maxrv   r6   r   rX   r   lenr   rY   rH   rw   r4   rz   r   float)r   r/   r   r6   r   r	   rJ   service_level_querylatest_dater   planning_querylatest_snapshot_dateplanning_rowsr   r   r   r   r   slow_mover_querylatest_slow_mover_dater   	doh_queryr   s                          r   get_kpi_summaryr     s`   $  K	"++D*=BBDK
 (())778f  ++z9  188$$))[8

 hhxtxx(@(@(E(EFGNN$$//:=

&( 	 "5"<"<((--<# 188$$00K?
 ,224; XXf>>?FF((33zAN
  '..,,::kI
  +  "xxDHHV55CCD 

&,,77:E

&(	 	
  +2200>>BVVN '..,,88KG
 #&&(MOO / !},q O/!??aq O! xx


6+C+C+F+F GHOO  ++z9  ..$6
 +22$$22kA

 "$DHHV--;;<"

&$$//:=

&(	 	
 "/66((66:PP  +22$$00K?
 (..05A ))112f  ++z9  $$$$22kA
	 "!((((66:PPI $$$$00K?
	 !'')# !12 ."#45**,,"8,	 	I  		s   $V 	V'&V'
month_frommonth_toc           
       
   ddl m }m} ddlm} ddlm}	m}
 ddlm}  |j                         j                         }|r3	  |j                  |d      j                         j                  d      }n| |d
      z
  j                  d      }|rI	  |j                  |d      j                         }|j                  d       |d      z    |d      z
  }n|}||kD  rt        d      | j                   |	j                  d|j                  j                         j#                  d       |	j                  d|j                  j                         j#                  d       |	j$                  |j                  j&                        j#                  d       |	j$                  |j                  j(                        j#                  d       |	j*                  |j                  j,                        j#                  d            j/                  |j                  j0                  |k(  |j                  j                   |k\  |j                  j                   |k        }|(|j/                  |j                  j2                  |k(        }|(|j/                  |j                  j4                  |k(        }|j7                   |	j                  d|j                  j                          |	j                  d|j                  j                               j9                   |	j                  d|j                  j                          |	j                  d|j                  j                               }|j;                         }i }|D ]}  }|\  }}}}} |t=        |      t=        |      d      }|j?                  d      }t=        tA        tC        |xs d                  t=        tA        tC        |xs d                  d||<    |jD                  |jD                  z
  dz  |jF                  |jF                  z
  z   dz   }g }|j                  d      }tI        |      D ]Z  }|j?                  d      }||v r"|jK                  |||   d   ||   d   d       n|jK                  |ddd       | |d      z   }\ ||||j?                  d      |j?                  d      |dS # t        $ r t        d	      w xY w# t        $ r t        d      w xY w)a  Get month-wise stock vs demand analysis.
    
    Returns aggregated data by month:
    - avg_daily_demand: Average of avg_daily_demand for all dates in that month
    - available_stock: Average of available_stock for all dates in that month
    
    Args:
    - month_from: Start month in YYYY-MM format (optional, defaults to 11 months ago)
    - month_to: End month in YYYY-MM format (optional, defaults to current month)
    
    Filters:
    - If location_id provided: aggregate only for that location
    - If product_id provided: aggregate only for that product
    - If both provided: aggregate for that specific location+product combination
    - If neither: aggregate across all locations and products
    r   )r   	timedeltarelativedelta)r	   extractr
   r   %Y-%mday&Invalid month_from format. Use YYYY-MM   monthsdays$Invalid month_to format. Use YYYY-MMz#month_from cannot be after month_toyearmonthavg_daily_demandavailable_stockdata_points)r   r      )r   r   r   r/   r6   r5   r   r   monthly_data)&r   r   dateutil.relativedeltar   rs   r	   r   rr   r   nowr   rE   replacerG   r   r   rX   r   r   r   r   rH   r4   rF   r/   r6   r5   group_byrx   r   r   r   roundr   r   r   rangerI   )r   r/   r6   r5   r   r   r   r   r   r	   r   r   r   
start_date	end_monthend_dater   rK   data_by_monthr   r   r   
avg_demand	avg_stockr   
month_date	month_keytotal_monthsr   current_month_s                                  r   get_analytics_stock_vs_demandr     s   0 -4(HLLN!E 	G***:w?DDFNNSTNUJ m266??A?F
 	E)))(G<AACI!))a)0=3JJY\]M^^H  H>?? HHVV==KKLRRSYZWf>>LLMSST[\11BBCIIJ\]11AABHHIZ[

633667==mL f((33zA((66*D((66(B 
 V==II[XYV==HHJVW NNVV==KKLWf>>LLM hVV==KKLWf>>LLM 
 iikG M 
:=7eZKc$iUQ7
''0	 #E%
a*@$A B"5y~A)>#?@$
i 	
 MMJOO3r9X^^jN^N^=^_bccL L&&1&-M<  @!**73	 %"$1)$<=O$P#0#;<M#N!  "$%#$!  &Q(??%@* !"  ))'2%%g.$ q  	GEFF	G  	ECDD	Es   1S AS( S%(S=c                 ,   ddl m } ddlm} ddlm} ddlm}	 |r2	 |j                  |d      j                         j                  d      }
n8|j                         j                         j                  d       |d
      z
  }
|r2	 |j                  |d      j                         j                  d      }n.|j                         j                         j                  d      }||
k  rt        d      | j                   |j                  d|	j                  j                        j                  d       |j                  d|	j                  j                        j                  d       |j                    |j"                  |	j                  j$                        d      j                  d       |j                    |j"                  |	j                  j&                        d      j                  d       |j                    |j"                  |	j                  j(                        d      j                  d            j+                  |	j                  j,                  |k(        }| |d      z    |d      z
  }|j+                  |	j                  j                  |
k\        }|j+                  |	j                  j                  |k        }|(|j+                  |	j                  j.                  |k(        }|(|j+                  |	j                  j0                  |k(        }|j3                   |j                  d|	j                  j                         |j                  d|	j                  j                              j5                   |j                  d|	j                  j                        j7                          |j                  d|	j                  j                        j7                               }|j9                         }i }|D ]  }t;        |j<                        }t;        |j>                        }| d|d}tA        |j$                  xs d      }tA        |j&                  xs d      }tA        |j(                  xs d      }|dk  rd}n||z  }t;        tC        |            t;        tC        |            t;        tC        |            tC        |d      d||<    g }|
}||k  rW|jE                  d      }||v r|jG                  d|i||          n|jG                  |ddddd       | |d      z   }||k  rW||||
jE                  d      |jE                  d      |dS # t        $ r t        d	      w xY w# t        $ r t        d      w xY w)a  Get month-wise service level vs demand analytics.
    
    Args:
        db: Database session
        company_id: Company ID (required)
        location_id: Filter by location (optional)
        product_id: Filter by product (optional)
        month_from: Start month in YYYY-MM format (optional, defaults to 11 months ago)
        month_to: End month in YYYY-MM format (optional, defaults to current month)
    
    Returns:
        Dictionary with monthly aggregated service level and demand data
    r   r   r   r   r
   r   r   r   r   r   r   r   zmonth_to must be >= month_fromr   r   rf   rP   rQ   rR   r   -02dg      ?   )rP   rQ   rR   rS   )r   rP   rQ   rR   rS   r   )$r   r   r   rs   r	   rr   r   rE   r   r   rG   r   r   r   rT   r   coalescert   rP   rQ   rR   rF   r/   r6   r5   r   rx   ascr   r   r   r   r   r   r   rI   )r   r/   r6   r5   r   r   dtr   r	   r   start_monthr   r   end_month_last_dayr   	data_dictr   r   r   r   demand	fulfilledlostservice_level_valr   currents                             r   %get_analytics_service_level_vs_demandr   "  s   * (4 	G++j':??AIIaIPK
 vvx}}..1.5R8PP	EHg6;;=EE!ELI
 FFHMMO+++2	;9:: 	DLL!9!9!>!>?EEfMDLL&":":"?"?@FFwODMM($((6#;#;#F#FGMSST`aDMM($((6#;#;#I#IJCPVVWfgDMM($((6#;#;#J#JKSQWWXhi	
 
((33zA	B 
 $m1&==TUAVVLL1166+EFELL1166:LLME V55AA[PQV55@@JNO NNVV55::;Wf66;;< hVV55::;??AWf66;;<@@B 
 99;D I 
388}CIIfAeC[)	s~~,-#++2s3	S''.3/ Q; # )F 2 eFm, y!12!%+."#4a8	 
	)
. LG
Y
$$W-		!!I&!  "!""#!$!  M33% Y
* !" !**73&&w/$ K  	GEFF	G  	ECDD	Es   0U& 0U> &U;>Vstatus_filterc                 l   ddl m}m}m}	m}
m} ddlm} ddlm	}m
}m} | j                  |j                  |j                  |j                  |j                   |j"                  |
j$                  |j&                  |j(                  |	j*                   |j,                   |j.                  |j0                  j2                        d      j5                  d       |j6                  |j0                  j8                        j5                  d       |j:                  |j0                  j<                        j5                  d	            j?                  ||j                  |j                  k(        j?                  ||j                  |j                  k(        j?                  |	|j                  |	j                  k(        jA                  |
|jB                  |
jD                  k(        jA                  |j0                  |j0                  j                  |j                  k(  |j0                  j                  |j                  k(  z  |j0                  j                  |j                  k(  z  |j0                  jF                  d
k(  z        jI                  |j                  |j                  |j                  |j                   |j"                  |
j$                  |j&                  |j(                  |	j*                  	      }||jK                  |j                  |k(        }||jK                  |j                  |k(        }||jK                  |j                  |k(        }|dz
  |z  }|jM                  |      jO                  |      jQ                         }|j7                         }|sg |||dS g }|D ]  }|j                  }|j                  }|j                  }| j                  |jR                        jK                  |jR                  j                  |k(  |jR                  j                  |k(  |jR                  j                  |k(        jU                   ||jR                  jV                              jY                         }| j                  |jZ                        jK                  |jZ                  j                  |k(  |jZ                  j                  |k(  |jZ                  j                  |k(        jU                   ||jZ                  jV                              jY                         }| j                  |j\                        jK                  |j\                  j                  |k(  |j\                  j                  |k(  |j\                  j                  |k(        jU                   ||j\                  jV                              jY                         }| j                  |j^                        jK                  |j^                  j                  |k(  |j^                  j                  |k(  |j^                  j                  |k(        jU                   ||j^                  j`                              jY                         }tc        |jd                  xs d      }d}|dk(  rd}n|r|jf                  r||jf                  k  rd}|r4|jh                  r(|jj                  dk(  rd}n|jj                  dk(  r|dkD  rd}|r||k7  rIi d|d|j*                  d|d|j(                  d|d|j                   d|j"                  d|j$                  d|j&                  d|d|jl                  d	|jn                  d|d|rtc        |jp                        ndd|r|jV                  ndd |r|jh                  nd!d"|r|jj                  ndi d#|r!|jr                  rtc        |jr                        nd$d%|r!|jt                  rtc        |jt                        nd$d&|r|jv                  ndd'|r!|jf                  rtc        |jf                        nd$d(|r!|jx                  rtc        |jx                        nd$d)|r|jz                  nd!d*|r!|j|                  rtc        |j|                        nd$d+|r|jV                  ndd,|rtc        |j~                        nd$d-|rtc        |j                        nd$d.|r|j                  ndd/|r|j                  ndd0|rtc        |j                        nd1d2|rtc        |j                        nd$d3|rtc        |j                        nd$d4|rtc        |j                        nd$d5|rtc        |jp                        nd$|rtc        |j                        nd$|rtc        |j                        nd$|rtc        |j                        nd$|rtc        |j                        nd$|r|j                  nd|r!|j                  rtc        |j                        nd|r|j`                  ndd6}|j                  |        ||||dS )7zFGet comprehensive inventory details with joins across multiple tables.r   )r   r   r   Categoryrb   r
   r   r   )r	   rd   casecurrent_stockactive_batches_countearliest_expiryre   Nr   rg   rh   ri   rj   rk   rl   rm   r/   r   r6   r>   r5   r<   r=   category_nameis_perishabler`   snapshot_stockrX   rz   Frp   avg_daily_sales_90drf   days_on_hand_90dr   rn   safety_stockshould_reorderrecommended_order_qtyplanning_snapshot_dater   sigma_daily_demandlead_time_daysreview_period_daysservice_level_targetgffffff?forecast_avg_daily_demand_90dforecast_safety_stock_90dforecasted_reorder_point_90dplanning_on_hand_qty)rZ   r   
min_target
max_targetr   latest_service_levelservice_level_date)Nrq   r   r   r   r   rb   rr   r   rs   r	   rd   r   r   r/   r6   r5   r<   r=   r  r  r>   r   r   rt   r#   ru   r   rH   r4   minexpiry_daterC   rD   fk_product_category_idcategory_idr`   r   rF   r   r   r   r\   rx   rX   ry   rw   r   rT   r   r   r   current_reorder_pointrz   rp   r   r   rY   r   r   r   current_safety_stockr  r  r   r
  r  r  r  r  r  r  rZ   r   r  r  r   rS   rI   ) r   r0   r1   r/   r6   r5   r   r   r   r   r   rb   r   r	   rd   r   
base_queryr   base_resultsrA   enriched_resultsresultcompany_id_vallocation_id_valproduct_id_vallatest_snapshotlatest_slow_moverlatest_planninglatest_servicecurrent_stock_valr`   enriched_items                                    r   get_inventory_detailsr)    s1
    XW++ ""##"" 	 	 	 	hdhhv44EEFJPPQ`a

6((++,223IJ&&22399:KL+, d++w/A/AA
d/--1E1EE
d++w/A/AAi'00H4H4HHi				)	)_-G-G	G				*	*o.I.I	I	K				)	)_-G-G	G	I 
			%	%	1	3 h""##""
K d &&'A'AZ'OP
&&'B'Bk'QR
&&'A'AZ'OP
 Qh'!F$$V,227;??AL E 	
 	
  o/** ,,** ((6#@#@AHH))44F))55H))44F
 (455CCD
Eeeg	 	 HHV%=%=>EE$$//>A$$00OC$$//>A
 (400>>?
@	 	 ((6#C#CDKK,,77>I,,88OK,,77>I
 (488FFG
H	 	 &":":;BB$$//>A$$00OC$$//>A
 (400556
7	 	 "&"6"6";!<!#F!F!F O$I$II$ !2!@!@ 44>%"66&@EVYZEZ% V}46
.6
F//6
 ?6
 V11	6

 .6
 F//6
 &++6
 V116
 V116
 .6
 #F$?$?6
 v556
 f6
$ OeO$?$?@Y]%6
& o_::SW'6
, @Q.<<W\-6
. "L]#4#H#Hcg/6
0 "GX]n]v]v5):)B)B#C|16
2 DUZkZsZs&7&?&? @y|36
4 #N_$5$J$Jei56
: _ap  bG  bGU?#H#HI  MP;6
< ?_n  `D  `DE/"F"FG  JM=6
> o<<UZ?6
@ $Udix  jO  jOU?+P+P%Q  UXA6
F %o&C&C\`G6
H ?o&F&F G`cI6
J !%(J(J"KdgK6
L o<<UVM6
N !/"D"D]^O6
P #SbE/*N*N$OhlQ6
R ,etU?3`3`-az}S6
T (]l/X/X)YruU6
V +crE/2^2^,_x{W6
X #/E/*E*E$F_bY6
Z BQ5!<!<=VYIXu_%D%DE^a?N% : :;TW?N% : :;TW<KO88QU LZ^l^z^zE.*F*F$G  AE9G."5"5Tk6
p 	._o/h "	 r   c           	      v   | j                  t        j                        }|rQ	 t        j                  |d      j                         }|j                  t        j                  j                  |k(        }|,|j                  t        j                  j                  |k(        }|,|j                  t        j                  j                  |k(        }|,|j                  t        j                  j                  |k(        }|j                         }	|dz
  |z  }
|j                  |
      j                  |      j                         }g }|D ]U  }|j                   j"                  D ci c]#  }|j$                  t'        ||j$                        % }}|j)                  |       W ||||	dS # t        $ r Y ?w xY wc c}w )ztGet inventory planning snapshot data with pagination. If date is provided, returns snapshots for that specific date.r3   r
   r?   )r   r   r   r   rE   r   rF   rX   rG   r/   r6   r5   rH   r   r   r   	__table__columnsnamegetattrrI   )r   r   r0   r1   r/   r6   r5   r   rJ   rA   r   rK   r@   r]   cr^   s                   r    get_inventory_planning_snapshotsr0    s    HHV556E	"++D*=BBDKLL!A!A!O!OS^!^_E
 V==HHJVWV==II[XYV==HHJVW KKME Qh'!Fll6"((1557G D #DLDVDVD^D^_q166!::__M"# 	 7  		0 `s   AF& "(F6&	F32F3df_predictionsmodel_versionc                 <   ||j                   rt        d      d|j                  vsd|j                  vrt        d      t        j                         }	 |d   j                  d       j                         }t        |      }t        |      }	| j                  t        j                        j                  t        j                  j                  |k(  t        j                  j                  |k(  t        j                  j                  |k(  t        j                  j                   |k\  t        j                  j                   |	k        j#                  d      }
|
d	kD  r't$        j'                  d
|
 d| d| d| d| d|	        g }|j)                         D ]c  \  }}t        j                  ||||t+        |d   d      r|d   j                         n|d   t-        |d         |      }|j/                  |       e | j1                  |       | j3                          t$        j'                  dt5        |       d| d| d|        t5        |      S # t6        $ r@}| j9                          t$        j;                  d| d| d| dt=        |               d}~ww xY w)a  
    Save demand forecast predictions to database.
    Deletes existing forecasts for the same company/location/product/date range before inserting new ones.
    Uses database transaction to ensure atomicity (all-or-nothing operation).
    
    Args:
        db: Database session
        company_id: Company ID
        location_id: Location ID
        product_id: Product ID
        df_predictions: DataFrame with columns: date, forecast_qty
        model_version: Model version identifier
        
    Returns:
        Number of forecast records saved
        
    Raises:
        ValueError: If predictions DataFrame is empty or invalid
        Exception: If database operation fails
    NzPredictions DataFrame is emptyr   forecast_qtyzDPredictions DataFrame must contain 'date' and 'forecast_qty' columnsc                 >    t        | d      r| j                         S | S )Nr   )hasattrr   )xs    r   <lambda>z&save_demand_forecast.<locals>.<lambda>  s    '!V"4affh ! r   F)synchronize_sessionr   zDeleted z  existing forecasts for company=z, location=z
, product=z, date_range=z to )r/   r6   r5   forecast_daterJ   r4  r2  zSaved z new forecasts for company=z%Failed to save forecasts for company=z: )emptyrG   r,  r   r   applytolistr  r   r   r   DemandForecastrF   r/   r6   r5   rJ   deleteloggerinfoiterrowsr6  r   rI   bulk_save_objectscommitr   	Exceptionrollbackerrorr   )r   r/   r6   r5   r1  r2  r   target_datesmin_datemax_datedeleted_countforecast_objsr   r   fces                   r   save_demand_forecastrO    s   : !5!59::^+++~^E[E[/[_``JJLE:%f-33;

&( 	 |$|$ !6!67>>!!,,
:!!--<!!,,
:!!--9!!--9
 &U&
+ 	 1KK=/)I* V'=
:,mH:UYZbYce $--/ 
	%FAs&&%'%#29#f+v2NCK,,.TWX^T_"3~#67+B   $
	% 	]+ 			S'((CJ< P#}Jzl<	

 =!! 
3J< @#}Jzl"SVHF	
 	s   G;I 	J;JJr  c                    ddl m}m}m}m}m}	 ddlm}
 ddlm	}m
} |dk  s|dkD  rt        d      | j                   |j                  |
j                              j                  |
j                   |k(        }|r|j                  |
j"                  |k(        }|j%                         }|sd	g d
dS | j                  |
||||      j'                  ||
j(                  |j(                  k(        j'                  ||
j"                  |j"                  k(        j+                  ||j,                  |j.                  k(        j+                  |	|j(                  |	j(                  k(        j+                  ||	j0                  |j0                  k(        j                  |
j                   |k(  |
j                  |k(        }|r|j                  |
j"                  |k(        }|r|j                  |j,                  |k(        }|j3                   ||
j4                              }|j7                  |      }|j9                         }g }t;        |d      D ]  \  }\  }}}}}|j=                  ||j(                  |j>                  |j@                  d|j"                  |jB                  d|r|j.                  |jD                  dnd|r0|r|j0                  nd|r|jF                  nd|r|jH                  nddndtK        tM        |jN                        d      tK        tM        |jP                        d      tK        tM        |jR                        d      tK        tM        |jT                        d      tK        tM        |jV                        d      tK        tM        |j4                        d      tK        tM        |jX                        d      |j                  d        d	|dt[        |       ddS )z
    Get fastest moving products based on highest sales velocity.
    Returns raw data that will be transformed by FastAPI response_model.
    r   r   r   r   VendorProductVendorr
   )rw   r   rc   d   Limit must be between 1 and 100T4No snapshot data available for the specified filterssuccessr@   messagestartr4   r-  skur4   r-  Nr4   r-  code)rankproductlocationcategoryvendorrY   total_sold_7dtotal_sold_30dr   ads_7dads_30dr   rX   Top z/ fastest moving products retrieved successfully).rq   r   r   r   rR  rS  r   rw   rs   r	   rd   rG   r   r   rX   rF   r/   r6   rv   rC   r5   rD   r  r  	vendor_idrx   ri  r   r   	enumeraterI   r<   
short_namer>   r  vendor_namevendor_coder   r   rY   rf  rg  r   rh  r   r   )r   r/   r6   r  r   r   r   r   rR  rS  rw   r	   rd   latest_date_queryr   r   rK   fastest_movingra  r]   rb  rc  rd  re  s                           r   get_fastest_moving_productsrr    s    UT)% qyECK:;; *;*I*I!JKRR$$
2 -44))[8
 $**,KM
 	
 HH d"--1C1CC
d#//83G3GGi'00H4H4HHiw))]-E-EEi''6+;+;;f$$
2'';6! 
, .::kIJW;;{JK NN4 1 9 9:;E KKEiikG NAJ7Z[A\ ==x(Hf((,,)) ** ..  ** ..  $
  +1f&&d.4**$.4**$ " x';';!<a@"5)?)?#@!D#E(*A*A$BAF#E(*A*A$BAFE(//2A6U8#3#34a8U8#3#34a8%337
 	@ #n-..]^ r   	date_fromdate_toc                    ddl m}m}	 ddlm}
 ddlm} | j                  |
j                  |j                  |	j                        j                  ||
j                  j                  |j                  k(        j                  |	|
j                  j                  |	j                  k(        j                  |
j                  j                  |k(        }|(|j                  |
j                  j                  |k(        }|(|j                  |
j                  j                  |k(        }|rJ	  |j                  |d      j!                         }|j                  |
j                  j"                  |k\        }|rJ	  |j                  |d      j!                         }|j                  |
j                  j"                  |k        }|j'                  |
j                  j"                  j)                         |
j                  j*                  j-                               }|j/                         }|dz
  |z  }|j1                  |      j3                  |      j5                         }g }|D ]{  \  }}}|j7                  |j8                  |j                  |j                  ||j                  ||j*                  |j"                  t;        |j<                        |j>                  d

       } ||||dS # t$        $ r t%        d      w xY w# t$        $ r t%        d	      w xY w)a0  
    Get demand forecast data with pagination and filters.
    
    Args:
        db: Database session
        company_id: Company ID (required)
        location_id: Filter by location (optional)
        product_id: Filter by product (optional)
        date_from: Start date for target_date in YYYY-MM-DD format (optional)
        date_to: End date for target_date in YYYY-MM-DD format (optional)
        page: Page number (default: 1)
        perpage: Items per page (default: 100)
    
    Returns:
        Dictionary with forecast data, pagination info
    r   r   r
   r   r   r   r3   z(Invalid date_from format. Use YYYY-MM-DDz&Invalid date_to format. Use YYYY-MM-DD)
r4   r/   r6   r>   r5   r<   r:  rJ   r4  r:   r?   ) rq   r   r   rr   r   r   r   r>  r<   r>   rC   r5   r6   rF   r/   rE   r   rJ   rG   rx   r   r:  rd   rH   r   r   r   rI   r4   r   r4  r:   )r   r/   r6   r5   rs  rt  r0   r1   r   r   r   r   r   r   r   rA   r   rK   forecast_itemsforecastr<   r>   s                         r   get_demand_forecastsrx    s   4 4! HH d&&11W5G5GG
d&''33x7K7KKf((J6 
 V22>>+MNV22==KL 	I***9jAFFHJLL!6!6!B!Bj!PQE 	G(x((*=BBDHLL!6!6!B!Bh!NOE
 NN))--/++002E KKME Qh'!Fll6"((1557G N18 -,++"--#//*"--(%33#//!("7"78"--
 	 	 O  	IGHH	I  	GEFF	Gs   %A	K 1A	K7 K47Lc                 $
   ddl m}m}m}m}m}	 ddlm}
m}m	} ddl
m} |dk  s|dkD  rt        d      | j                   |j                  |
j                              j!                  |
j"                  |k(        }|r|j!                  |
j$                  |k(        }|j'                         }|sd	g d
dS | j                  |
||||||      j)                  ||
j*                  |j*                  k(        j)                  ||
j$                  |j$                  k(        j-                  ||j.                  |j0                  k(        j-                  |	|j*                  |	j*                  k(        j-                  ||	j2                  |j2                  k(        j-                  ||j"                  |
j"                  k(  |j$                  |
j$                  k(  z  |j*                  |
j*                  k(  z        j-                  ||j"                  |
j"                  k(  |j$                  |
j$                  k(  z  |j*                  |
j*                  k(  z  |j                  |
j                  k(  z        j!                  |
j"                  |k(  |
j                  |k(        }|r|j!                  |
j$                  |k(        }|r|j!                  |j.                  |k(        }|j5                  |
j6                  j9                               }|j;                  |      }|j=                         }g }t?        |d      D ]  \  }\  }}}}}}}|r|j@                  r|j@                  }n|r|j@                  }nd}|j6                  |z  }|r|jB                  r|jB                  }n|dz  }t        d|jD                  |z
        }|jG                  ||j*                  |jH                  d|j$                  |jJ                  d|r|j0                  |jL                  dnd|r0|r|j2                  nd|r|jN                  nd|r|jP                  nddndtS        tU        |jD                        d      tS        tU        |      d      tS        tU        |j6                        d      tS        tU        |      d      |jV                  tS        tU        |jV                        d      nd|jX                  tS        tU        |jX                        d      nd|j                  d        d	|dt[        |       ddS )z
    Get most stagnant (dead/slow moving) products based on lowest sales velocity.
    Returns raw data that will be transformed by FastAPI response_model.
    r   rQ  r
   )rw   ReorderPolicyr   r   r   rT  rU  TrV  rW  rZ     r^  Nr_  )ra  rb  rc  rd  re  rY   lead_time_demandr   excess_stock_levelr   r   rX   rj  z. most stagnant products retrieved successfully).rq   r   r   r   rR  rS  r   rw   rz  r   rs   r	   rG   r   r   rX   rF   r/   r6   rv   rC   r5   rD   r  r  rk  rx   r   r   r   r   rl  r  r  rY   rI   r<   r>   r  rn  ro  r   r   r   r   r   )r   r/   r6   r  r   r   r   r   rR  rS  rw   rz  r   r	   rp  r   r   rK   stagnant_productsra  r]   rb  rc  rd  re  reorder_policyplanning_snapshotr  r|  rn   r}  s                                  r   get_most_stagnant_productsr    s    UTSS qyECK:;; *;*I*I!JKRR$$
2 -44))[8
 $**,KM
 	
 HH! d"--1C1CC
d#//83G3GGi'00H4H4HHiw))]-E-EEi''6+;+;;i		!	!%6%A%A	A		"	"&7&C&C	C	E		!	!%6%A%A	A	C
 i!	"	-	-1B1M1M	M	"	.	.2C2O2O	O	Q	"	-	-1B1M1M	M	O 
#	0	04E4S4S	S	U f$$
2'';6; 
F .::kIJW;;{JK NN,4488:;E KKEiikG dmnu}~d /``x(HfnN_ !2!A!A.==N+::NN#++n< !2!H!H-CCM,q0M H$8$8=$HI  ((,,
 ** ..  ** ..  $
  +1f&&d.4**$.4**$ " x';';!<a@ %e,<&=q AU8#3#34a8"'.@(A1"EV^VsVsVE%0M0M*NPQ$R  FJ<D<L<L<XuU8#3#34a8^b%333"
 	+/d !#/011_` r   c                    ddl m}m}m}m}m}	 ddlm}
 ddlm	}m
} |dk  s|dkD  rt        d      | j                   |j                  |
j                              j                  |
j                   |k(        }|r|j                  |
j"                  |k(        }|j%                         }|sd	g d
dS | j                  |
||||      j'                  ||
j(                  |j(                  k(        j'                  ||
j"                  |j"                  k(        j+                  ||j,                  |j.                  k(        j+                  |	|j(                  |	j(                  k(        j+                  ||	j0                  |j0                  k(        j                  |
j                   |k(  |
j                  |k(  |
j2                  d	k(  |
j4                  dkD        }|r|j                  |
j"                  |k(        }|r|j                  |j,                  |k(        }|j7                   ||
j8                              }|j;                  |      }|j=                         }g }t?        |d      D ]X  \  }\  }}}}}|jA                  ||j(                  |jB                  |jD                  d|j"                  |jF                  d|r|j.                  |jH                  dnd|r0|r|j0                  nd|r|jJ                  nd|r|jL                  nddndtO        tQ        |jR                        d      tO        tQ        |jT                        d      |jV                  tO        tQ        |jV                        d      ndtO        tQ        |j4                        d      tO        tQ        |j8                        d      |j                  d       [ d	|dtY        |       ddS )z
    Get most urgent order requirements based on urgency score.
    Returns products that need immediate ordering based on days of cover vs lead time.
    r   rQ  r
   )r   r   rc   rT  rU  Tz>No inventory planning data available for the specified filtersrW  rZ  r\  r^  Nr_  rf   )ra  rb  rc  rd  re  rY   rZ   days_of_coverr  urgency_scorerX   rj  z6 most urgent order requirements retrieved successfully)-rq   r   r   r   rR  rS  r   r   rs   r	   rd   rG   r   r   rX   rF   r/   r6   rv   rC   r5   rD   r  r  rk  	is_urgentr  rx   r  r   r   rl  rI   r<   rm  r>   r  rn  ro  r   r   rY   rZ   r  r   )r   r/   r6   r  r   r   r   r   rR  rS  r   r	   rd   rp  r   r   rK   urgent_productsra  planningrb  rc  rd  re  s                           r   get_most_urgent_productsr    s    UT1% qyECK:;; *C*Q*Q!RSZZ!,,
: -44%11[@
 $**,KW
 	
 HH! d*559K9KK
d+778;O;OOi'00H4H4HHiw))]-E-EEi''6+;+;;f!,,
:!//;>!++t3!77!;	! 
0 6BBkQRW;;{JK NN4 9 G GHIE KKEiikG OAJ7Z[A\ ==x(Hf((,,)) ** ..  ** ..  $
  +1f&&d.4**$.4**$ " x';';!<a@ x';';!<a@HPH^H^HjU5)?)?#@!Dps%*51O1O+PRS%T"5)?)?#@!D%333 
 	< #o.//ef r   )r   rT  )NNr
   
   )Nr
   r  )r
   rT  NNNN)r
   rT  NNNNN)NNNN)NN)Nr
   rT  NNN)v1)NNr  )NNNNr
   rT  )5sqlalchemy.ormr   typingr   r   r   r   rs   r	   rr   r   rq   r   r   loggingpandaspd	getLogger__name__r@  r   r   r   r   r    r#   r$   r&   r'   r*   r+   r-   r.   r   dictrN   rV   r_   r   boolr   r   r   r   r   r)  r0  	DataFramerO  rr  rx  r  r   r   r   <module>r     s   " ! #   /  			8	$G G G Gd6K\K\F] GKg KS KS K4PVPePeKf KKg KS KS K4PVPePeKf KN Ns Ns NTRXRjRjMk NJG J3 J3 JfNbNbIc JN N N NdSYSkSkNl N; ;x} ;RU ;eh ;wz ;  EI ;~8' 8# 8S 8WZ 8dh 8x7 7x} 73 7]` 7jn 7vqw qc q qW_`cWd q  {C  DG  {H q  ]e  fi  ]j q  {C  DG  {H q  TX qjP Ps P P(SV- Pltuxly P  PX  Y\  P] P  rz  {~  r P  W_  `d  We P  qu Ph  $!% $AA
3-A A #	A
 A 
AN !%	ggg 3-g #	g
 
gZ "& $ $"BBB #B 	B
 B smB 
BP "& $ $"JJJ #J 	J
 J smJ 
J^  $!% $#'VV
V V 	V
 #V V C=V 
Vt+ + +TW +fi +  ~F  GJ  ~K +  ai  jm  an +  CK  LO  CP +  \` +h ___ _ 	_
 LL_ _ 	_J "&!%ppp #p #	p
 p 
pl "& $#!ccc #c 	c
 }c c]c c c 
cR "&!%OOO #O #	O
 O 
Oj "&!%ppp #p #	p
 p 
pr   