:is(ddlmZmZmZddlmZmZmZddlm Z ddl m Z m Z ddl mZddlmZddlmZddlmZmZdd lmZGd d Zy ) )ListOptionalTuple)selectfuncor_) AsyncSession) HTTPExceptionstatus)Product) Violation)ScrapingResult) ProductCreate ProductUpdate)calculate_pack_pricesc2eZdZedededefdZedededefdZe ddeded ed e d e e de e eeff d Z eded edefdZededed edefdZedededdfdZedededefdZedede edefdZy)ProductServicedb product_idreturncK|jttjtj|k(d{}|j j }|sy|jttjtjjtj|jk(d{}|jxsdS77w)z(Count violations for a specific product.Nr) executerr whereidscalarsfirstrcountr product_namescalar)rrproduct_resultproduct count_results B/var/www/html/marco-python-backend/app/services/product_service.pyget_violation_countz"ProductService.get_violation_counts "zz&/*?*? j@X*YZZ ((*002 ZZ 4::ill+ , 2 2&&'*>*>>   ""$))[ s%AC/C+B C/C-C/-C/r!c"K|jttjtj j tj|jk(d{}|jxsd}||_ |S7!w)zUAdd violation_count to a product object by querying violations matching product name.Nr) rrrrr rrrrviolation_count)rr!r"r&s r#enrich_product_with_violationsz-ProductService.enrich_product_with_violations sv ZZ 4::ill+ , 2 2&&'*>*>>   '--/41"1 sA)B+B ,"BNpagelimitsort_bysearchcK|dz |z}tt}|r2|jtjj d|d}|dk(r |j tj }nw|dk(r |j tj}nR|dk(r.|j tjj}n|j tj}ttjj|j}|j|d{}|j|j|}|j!|d{} | j#j%} g} | D]1} t&j)|| d{} | j+| 3| |fS77`7w)N%msprlast_scraped_date)rr rrilikeorder_byr/ updated_atdescrr select_fromsubqueryroffsetr)rrallrr'append) rr(r)r*r+r7query count_querytotalresultproductsenriched_productsr!s r# get_productszProductService.get_products-s(e#w KK 4 4 : :Qvha= IJE e NN7;;/E  &NN7#7#78E + +NN7#5#5#:#:#<=ENN7#7#78ETZZ\*66u~~7GH ii ,, V$**51zz%((>>#'') .G*II"gVVG  $ $W - .!%''-) Ws7D6G8G98G1G2AG4G5GGG product_incKtt|j}t|j|j |j |j|j|d|d|d|d|d|d|d }|j||jd {|j|d {|S77w) z6Create a new product with auto-calculated pack prices.r- reference_idrbarcoder/r price_1_pack price_2_pack price_3_pack price_4_pack price_5_pack price_6_pack price_12_packN) rfloatr/r rJrrKr addcommitrefresh)rrA pack_prices db_products r#create_productzProductService.create_productWs,E*..,AB #00#00&&$$$Q$Q$Q$Q$Q$Q%b/  ziikjj$$$ $s$B!C#C$C<C=CCc Ktj||d{}|jd}d|vrGt|d}t |}|j |d|d|d|d|d |d |d d |j D]\}}t||||jd{|j|d{|S77!7 w) z8Update product. If MSP changes, recalculate pack prices.NT) exclude_unsetr/r-rCrDrErFrGrH)rLrMrNrOrPrQrR) rget_product_by_id model_dumprSrupdateitemssetattrrUrV) rrrAr! update_datanew_msprWfieldvalues r#update_productzProductService.update_productqs '88ZHH ++$+?  K K./G/8K    +A +A +A +A +A +A!,R  (--/ +LE5 GUE * +iikjj!!!/I* !s4CCBC6C7CCCCCc|K|jttjtj|k(d{}|j j }|sttjd|j|d{|jd{y7q77 wNzProduct not found) status_codedetail) rrr rrrrr r HTTP_404_NOT_FOUNDdeleterUrrr=rXs r#delete_productzProductService.delete_productszz&/"7"7 j8P"QRR^^%++- "55*  ii ###iikS $s7AB<B6AB<B8B<0B:1B<8B<:B<cTK|jttjtj|k(d{}|j j }|sttjdtj||d{}|S7_7wrg) rrr rrrrr r rjrr'rls r#r\z ProductService.get_product_by_idszz&/"7"7 j8P"QRR^^%++- "55*  *HHZXX SYs%AB(B$AB(B&B(&B( products_listc2Kt|}d}d}d}g}|jttjj td{}t |jj}t|dD]\} } | j|vr3|dz }|j| | jd| jddHtt| j} t| j| j| j| j| j | d| d| d| d | d | d | d  } |j#| |j#| j|dz }|dkDr |j+d{|||||d|d|d|ddS7`#t$$r;} |dz }|j| | jdt'| dYd} ~ jd} ~ wt($r;} |dz }|j| | jdt'| dYd} ~ d} ~ wwxYw7#t($rI} |j-d{7||z }d}|jddt'| iYd} ~ d} ~ wwxYww)a6 Bulk create multiple products. Applies the same logic as create_product: - Auto-calculates pack prices based on MSP - Preserves all discount and pack pricing logic - Checks for duplicate barcodes Returns a summary dict with: - total_processed: Number of products in input list - successful: Number of products successfully created - failed: Number of products that failed - skipped_duplicates: Number of duplicate barcodes skipped - errors: List of errors encountered rNr-zDuplicate barcode: z already exists)rowr!errorrCrDrErFrGrHrIzValidation error: zDatabase error: rrzFailed to commit all products: zBulk upload completed: z created, z failed, z duplicates skipped)total_processed successfulfailedskipped_duplicateserrorsmessage)lenrrr rKr5setrr8 enumerater9rrrSr/rJr rT ValueErrorstr ExceptionrUrollback)rrorsrtrurvrwexisting_barcodes_resultexisting_barcodesidxrArWrXes r#bulk_create_productsz#ProductService.bulk_create_productss"m, *, 7?? # / / 8* $   8 @ @ B F F HI(:0 OC/ %%)::&!+&MM"#-#:#:#6z7I7I6J/!Z# 4E*..4IJ %!+!8!8!+!8!8&.."%,,!,Q!,Q!,Q!,Q!,Q!,Q"-b/ z"!%%j&8&89a C0 f > iik!! /$"40 JvhiXjWkk~   G$ P !  )661#a&:  !  )66/Ax8 " kkm##*$  >s1vhG  sAJF4=JAF7JB-F7J III!J7 H=0G60J6 H=0H82J8H==JI J JI!+J JJJ)r- rN)__name__ __module__ __qualname__ staticmethodr intr$r r'r}rrrr@rrYrrermr\dictrr#rr s*l****"   T[  % $ '( '('('( '(  '( tG}c! " '('(R=W2 &)7D :  3 4   L c g  a a )-m)<a a a rrN)typingrrr sqlalchemyrrrsqlalchemy.ext.asyncior fastapir r app.models.productr app.models.violationr app.models.scraping_resultrapp.schemas.productrrapp.services.pricing_servicerrrrr#rs2((((/)&*5<>~ ~ r