/i(ddlmZddlZddlmZddlmZmZmZm Z m Z ddl m Z ddl Z ddlmZmZddlmZmZmZmZddlmZdd lmZdd lmZdd lmZdd lmZdd l m!Z!ejDe#Z$dZ%dZ&dZ'dZ(d%dZ)d&dZ*d'dZ+ddd(dZ,d)dZ- d* d+dZ.d,dZ/dd d-dZ0d.dZ1d/dZ2dddd d0dZ3ddd  d1d!Z4 d2d"Z5 d3 d4d#Z6 d5d$Z7y)6) annotationsN)BytesIO)AnyIterableListOptionalTuple)UUID) HTTPExceptionstatus)funcselectupdatedelete) AsyncSession)Select)Vendor ViolationScrapingResult)settingsu7Price Violation Notice – Immediate Attention RequiredzProduct Name: {{PRODUCT_NAME}} Minimum Selling Price (MSP): {{MSP}} Current Selling Price: {{SCRAPED_PRICE}} Product URL: {{PRODUCT_URL}} Detected On: {{VIOLATION_DATE}}aDear {{VENDOR_NAME}}, We hope you are doing well. We have identified that one or more of your product listings are currently being sold below the agreed Minimum Selling Price (MSP). This may lead to pricing inconsistencies across the market and impact brand positioning. Below are the details of the identified violation: {{VIOLATIONS}} We kindly request you to review the above listing(s) and take corrective action at the earliest to ensure compliance with the pricing policy. If you believe this has been flagged incorrectly or need further clarification, please feel free to reach out to us. We appreciate your cooperation and prompt action in resolving this matter. Best regards, {{COMPANY_NAME}} {{SUPPORT_EMAIL}}u Price Violation Notice

⚠ Price Violation Notice

Hello {VENDOR_NAME},

We have identified a pricing violation for one of your listed products associated with the brand {BRAND_NAME}.

Violation Details:

Product Name: {PRODUCT_NAME}
Minimum Selling Price (MSP): ₹{MSP}
Detected Selling Price: ₹{SCRAPED_PRICE}
Price Difference: ₹{PRICE_DIFFERENCE}
Violation Detected On: {VIOLATION_DATE}
Product URL: View Listing

As per the agreed pricing policy, selling below the MSP is not permitted. We request you to review and correct the pricing at the earliest.

If you believe this notification was sent in error, Please contact us at {SUPPORT_EMAIL}.

We appreciate your prompt attention to this matter.

Regards,
{COMPANY_NAME}

© 2026 {COMPANY_NAME}. All rights reserved.
This is an automated notification — please do not reply directly to this email.
cK|jttjtj|k(d{}|j rt tjd| t|}t|||||}|j||jd{|j|d{|S77!7 w)NzEmail already registered status_codedetail)name brand_nameemail website_url is_active) executerrwhererscalar_one_or_noner r HTTP_409_CONFLICTstraddcommitrefresh)dbrrrr r!existingvendors A/var/www/html/marco-python-backend/app/services/vendor_service.py create_vendorr.sZZv 4 4V\\U5J KLLH""$(@(@Icdd+& *E{fo pFFF6N ))+ **V  MMs7ACCA0C5C6CCCCCcK|jttjtj|k(d{}|j }|st tjd|S73w)NzVendor not foundr) r"rrr#idr$r r HTTP_404_NOT_FOUND)r* vendor_idresultr,s r- get_vendorr4s_::fVn22699 3IJK KF  & & (F (A(AJ\]] M LsAA:A84A:c Kt||d{}|jttj tj |k(d{}|j j}||fS7l7(w)zuReturn the vendor and a list of all its violations. Raises HTTPException(404) if the vendor does not exist. r2N)r4r"rrr#r2scalarsall)r*r2r,r3 violationss r-get_vendor_with_violationsr:sl bI6 6F::fY/55i6I6IY6VWX XF!%%'J : 7 Xs"BBABB'BB) changed_bycKt||d{}i}d|vr|dt|d|d<|jD]8\}}t||st ||}||k7s%||f||<t |||:|rUt jdt|||d|jd{|j|d{|S77!7 w)Nr6r zvendor updated)r2changesr;)extra) r4r&itemshasattrgetattrsetattrloggerinfor(r)) r*r2datar;r,updateskeyvalueolds r- update_vendorrJsbI6 6FGm!4!@!$}"56]jjl, U 63 &#&Ce| #U| U+ ,  $#i.U\lv,w xiikjj   M!7  sACCACC)AC8C9CCCCCc`Kddlm}ddlm} |j t |j |j|k(d{tjd||j t |j |j|k(d{tjd||j t tj tj|k(d{|jd{tjd|dy777:7$#t$rf}|jd{7tjd t!|t#t$j&d t!| d}~wwxYww) zKDelete a vendor and cascade-delete related scraping results and violations.rrrNz4[Vendor Delete] Deleted scraping results for vendor z.[Vendor Delete] Deleted violations for vendor uendor Delete] ✓ Vendor z& and related data deleted successfullyu-[Vendor Delete] ✗ Failed to delete vendor: zFailed to delete vendor: r)app.models.scraping_resultrapp.models.violationrr"rr#r2loggingrDrr0r( Exceptionrollbackerrorr&r r HTTP_500_INTERNAL_SERVER_ERROR)r*r2rres r- delete_vendorrTs\9. jj/55n6N6NR[6[\]]] KI;WXjj *001D1D 1QRSSS Ei[QRjj--fii9.DEFFFiik 29+=cde ^ T G  kkm Ec!fXNO==.s1vh7   s F.9D< D4 AD<D6 AD<=D8>D<D:D<3F.4D<6D<8D<:D<< F+F&EA F&&F++F.c$Ktt}|r2|jtjj d|d}tt j jt}|r2|jtjj d|d}|j|d{j}|dk(r!|jtj}n#|dk(rq|jtjtjjt j tjj}n|dk(rt j t j"tj$tj&z tj$z d}|jtjtjj|j}|dz |z} |j)| j+|}|j|d{} | j-j/j1} | |fS777w)N%rviolation_countaverage_discountr)rrr#riliker count select_fromr" scalar_oneorder_by outerjoinrgroup_byr0desccoalesceavgmsp scraped_priceoffsetlimitr7uniquer8) r*page page_sizesort_bysearchstmt count_stmt total_countavg_exprrfr3vendorss r- list_vendorsrrs&>D zz&++++axqM:; %11&9J %%fkk&7&7!F81 &FG J//;;=K&}}V[[) % % NN9 % Xfii Xdjj.335 6 & &==9==9;R;R+RV_VcVc*c!dfgh NN9 % Xfii Xhmmo & Qh) #F ;;v  $ $Y /D::d# #Fnn%%'++-G K 50.$s%BJ ?FJJ5JJc K|jttjtj tj tjtjtjz tjz dzdjtj|j k(d{}|j\}}t|xsdt|xsdfS75w)Ndrg)r"rr r[rr0rbrcrdrer#r2oneintfloat)r*r,r3r[rcs r-compute_metricsrx(s:: JJy|| $ MM$((IMMI4K4K$Ky}}#\_b#bcef g  % ##vyy0 1 F JE3 uz?E#*- --sCC>C<6C>) vendor_idscKtttjt}|r::d# #F ::I * ?iik$* s%A7E9E:CE E EEctj}tj}tj}tj}tj }|r|r|s t dd}t|dD]\}} t| jd} | jrt| jdnd} | jr+t| jt| jz nd} | d} |d|d| jd | d | d | d | jjd | jdz }d|jdt!|dk(rdn t!|ddt!|dk(rdndd|j"dt!|d|dt!|dk(rdndd|xsdd}ddlm}|}d t!|d!t!|dk(rd"nd#|d$<||d%<|j(|d&<|j+d'|j-|d()ddl}|j1||5}|j3|j5|j3|r@|r>|j7|j9|j;d!dj9|j=|dddy#1swYyxYw)*NSMTP configuration incompleterrY.3fN/Arzp

Violation #aF

Product Name: u 
Minimum Selling Price (MSP): ₹u
Detected Selling Price: ₹u
Price Difference: ₹a
Violation Detected On: a
Product URL: View Listing
u Price Violation Notice

⚠ Price Violation Notice

Hello z,,

We have identified za pricing violationz pricing violations for zone of your listed productszyour listed productsz, associated with the brand z|.

Total Violations: z
z

As per the agreed pricing policy, selling below the MSP is not permitted. We request you to review and correct the pricing at the earliest.

If you believe zthis notificationzany of these notificationsz: was sent in error, please contact us at support@company.comu.

We appreciate your prompt attention to this matter.

Regards,
Your Company

© 2026 Your Company. All rights reserved.
This is an automated notification — please do not reply directly to this email.
 EmailMessagezPrice Violation Notice -  rr{SubjectFromToz+This email requires an HTML capable client.html)subtype)r SMTP_HOST SMTP_PORT SMTP_USER SMTP_PASS SMTP_FROM RuntimeError enumeraterwrdrerrrrrrr email.messagerr set_contentadd_alternativesmtplibSMTPehlostarttlsloginstripreplace send_message)r,r9hostport smtp_user smtp_passsenderviolation_items_htmlidxr msp_formattedscraped_price_formattedprice_differenceprice_difference_formattedrrmsgrsmtps r-rrsJ   D   D""I""I   F t6:;;#J2' Y /4 MVMdMdU9+B+B%CC$HjoT]TkTk5/% 8O8O2PPqr(8'="%KKN%PST]SiSiRjkVWdUdeVWnUnoVWqUqrST]SkSkSuSuSwRxy" #,--1 7 ! ' TMZ }69_5I1RUV`RaQbbuOvww|_bcm_nrs_s~[yO}PP++1+<+<*=> !_-.  03:!/C+Iefg&&/&H3H%IJEv Dp+ .C0Z0ARUV`RaefRf;lxByzC NCK CIOOABf- dD !T    JJy()*;*;C*D*J*J*L M # s /BJ<<K)r2 product_id status_filtercKtt}|r"|jtj|k(}|r"|jtj|k(}|r"|jtj |k(}|j tjj}|j|d{}|jj}tj}|j}d|_gd} |j!| |D]} |j!| j"| j$xsd| j&xsd| j(| j*t-| j.| j0rt-| j0nd| j2rt-| j2nd| j4rt-| j4nd| j | j6| jj9g t;} |j=| | j?S7w)z Export scraping results to XLSX.NScraping Results r~zProduct ReferenceBarcoder|z Vendor URLrrrzPercentage DifferenceStatus Source Typez Scraped Atrr) rrr#r2rcompliance_statusr^ scraped_atrar"r7r8rrrrrrrbarcode vendor_name vendor_urlrwrdrerpercentage_difference source_typerrrr) r*r2rrrmr3rrrrscraping_resultrs r-export_scraping_resultsrBs . !Dzz.22i?@zz.33zABzz.::mKL ==22779 :D::d# #F >>    !D    B B!BH GIIg   ( (  ( ( .B  # # )r  ' '  & & /%% &4C4Q4QE/// 0W\7F7W7WE/22 3]bxsd|j@|jBt+|j,|j.rt+|j.nd|jDrt+|jDnd|jFrt+|jFnd|jH|j2|jJj7g tM}| jO||jQS767w)zAExport combined report with both violations and scraping results.Nr{) r|r}r~rrrrrzConfirmation CountrrzLast Confirmedrrrrr))rrrrr#r2r"r8rrr7rrrrrrrrrrrwrdreconfirmation_countrrrlast_confirmed_dater create_sheetrrrrrrrrrr)r*r2rviolation_stmtviolation_resultr9 scraping_stmtrscraping_resultsr ws_violationsviolation_headersrr, ws_resultsresult_headerssrrs r-export_combined_reportrsIv.33F;N'--i.A.AY.NO ZZ77!%%'J>*M%++N,D,D ,QR %++N,E,E,ST JJ}55O&..0446    BIIM&M*+'  6 KK     " "  $ $ *  " " (b )-- ))) * )--)"9"99 :  ( (  ! !  $ $ . . 09B9V9VI ) ) 3 3 5\^ MM   $!34J Nn%  OO OO !r JJ " NN MM "&&M')'7'7E""" #U*,*=*=E"%% &5/1/G/GE"** +U  NN MM # # %   YFGGFO ?? u86s&A#N!%N&A?N!%N&J6N!N!cKtttjtj tj j t|}|j|d{}|j}|sttjdi}|D]#\}}|j |vs|||j <%d}t|dk(r&t|jdj}|t t"t||jD cgc]} | j$c} t|dkDrddSddS7cc} ww)aX Get pre-fill data for the notification modal. Returns: - recipient_email: Auto-fetched from vendor (None if multiple vendors) - default_subject: Default subject template - default_message_body: Default message template - violations_count: Number of violations - vendor_names: List of vendor names involved NrrrYrzMultiple vendors detected)recipient_emaildefault_subjectdefault_message_bodyviolations_count vendor_namesnote)rrrrr#r0rrr"r8r r r1rvaluesrDEFAULT_NOTIFICATION_SUBJECTDEFAULT_NOTIFICATION_MESSAGEr) r*rrmr3r vendors_setrr,rvs r-get_violations_prefill_datars: )V $ ) )& 1 7 7 8H8HmI\8] ^D::d# #FjjlG (A(AJmnnK$, 6 99K '%+K ", O ;1{1134Q7==+7 <L)4););)=>A>/2;/?!/C+   JN  %$.?s,A1E3E4A E>A.E,E ?EEc dt|d} |rdt|dnd} ||| | ||||d} |} | jD] \} }| j| t|} "| S)a5 Substitute template variables in message body. Handles both plain text and HTML content. Supported variables: - {{VENDOR_NAME}} - {{PRODUCT_NAME}} - {{MSP}} - {{SCRAPED_PRICE}} - {{PRODUCT_URL}} - {{VIOLATION_DATE}} - {{COMPANY_NAME}} - {{SUPPORT_EMAIL}} u₹rr)z{{VENDOR_NAME}}z{{PRODUCT_NAME}}z{{MSP}}z{{SCRAPED_PRICE}}z{{PRODUCT_URL}}z{{VIOLATION_DATE}}z{{COMPANY_NAME}}z{{SUPPORT_EMAIL}})rwr?rr&) message_bodyrrrdre product_urlr company_name support_emailrr substitutionsr3 placeholderrHs r-_substitute_template_variablesr s4%*S)*MBOE-$8#=>UZ'( 4&,(* MF+1139 U SZ89 Mcp Kddlm}ddlm}ddl}tj dtj dt |tj d|tj d|r|dd nd tj d |r t|ndd |r;tj d |ddtj dd|vxrd|v|r|jdk(rttjd|r|jdk(rttjdtttjtj!tj"j%t |}|j'|d{} | j)} | sttj*dtj d||r#t-|t.r.|jdk(rttjdtj d|t0j2} t0j4} t0j6} t0j8}t0j:}| r| r|s t=dg}| D]\}}|j?|| r| ddnd}tA||r |jBnddddddtEt0dd| xsd }d!|d"|d#}|}||d$<||d%<||d&<|jG|d'd()tj d*|tj d+|tj d,t|d-tj d.t|tj d/|dd0d1tj d2|jItj d3tK|jM |jO| | 5}|jQ|jS|jQ| r@|r>|jU| j|jWd4dj|jY|dddtj d5|d6t|d7|D]}|j[|_.|j_d{y7q#1swYfxYw7#|j`$rA}tjcd8t/|ttjdd9d}~w|jf$rM}tjcd:t/|ttjdd;t/|d}~wth$rM}tjcdaH Send custom violation notification email to specified recipient. If recipient_email is not provided, automatically fetches vendor email. If multiple violations from different vendors without recipient_email, raises error. The message_body supports template variables: - {{VENDOR_NAME}}, {{PRODUCT_NAME}}, {{MSP}}, {{SCRAPED_PRICE}} - {{PRODUCT_URL}}, {{VIOLATION_DATE}}, {{COMPANY_NAME}}, {{SUPPORT_EMAIL}} If multiple violations are provided, each violation will be sent with individually substituted variables for that violation. rrrNz:[send_custom_violation_notification] Function called with:z - violation_ids: z - recipient_email: z - subject: rtEMPTYz - message_body length: z charsz, - message_body preview (first 300 chars): i,z% - message_body contains HTML tags: <>rzMessage body cannot be emptyrzSubject cannot be emptyrz([send_custom] recipient_email received: zgrecipient_email is required. Please provide the email address where you want to send this notification.z%[send_custom] Email will be sent to: rrYr COMPANY_NAME Your Companyr) rrrrdrerrrrz< zT z rrrrzutf-8)rcharsetz[Email Debug] Sending to: z[Email Debug] Subject: zmail Debug] HTML length: z charactersz [Email Debug] Total violations: z$[Email Debug] Message body preview: z...z#[Email Debug] Content-Type header: z[Email Debug] Full headers: ru ✓ Custom notification sent to rz violation(s)u ✗ SMTP Authentication failed: z@SMTP Authentication failed. Please check your email credentials.u✗ SMTP Error: z SMTP error: u(✗ Failed to send custom notification: zFailed to send notification: )5rrrrrCrDrrrr r HTTP_400_BAD_REQUESTrrrrr#r0rr"r8r1 isinstancer&rrrrrrrrr rrArget_content_typedictr?rrrrrrrrr(SMTPAuthenticationErrorrQrR SMTPExceptionrO)r*rrsubjectrrrrrmr3rrrrrrviolations_listrr, first_vendorcombined_html_body html_contentrrrSs r-"send_custom_violation_notificationrJs*"* KKLN KK%d=&9%:;< KK''89: KK- gFGH KK+C ,=ST+UU[\] B? KK)'34 KK-c,.?-@ LM KK233G2HIJ KK6|DS7I6J#NO KK5c6J6J6L5MNO KK.tCIIK/@.ABC" \\$ % # IIK MMO IIKY 9??,i.?.?R.H.N.N.PQ   c "  #  66GuSQ`MaLbbopq) ?I-5__->I * ?iikM$p # #   * *  7Ax@A==U      'Ax01==!#a&*     ?AxHI==23q6(;   swF1X63T$4I7X6,T5>BT'AT5T3T5#X6'T0,T55X3r8s]"77)33/!$*5$   8 $ Y#  .n b"qu( < ) ) ) )  )  )  ) X .(,..%. .b:~F!% $#' ;;; ; ! ;  ;B!% $ iii i  iX**"* *j'.....  .  .  .... .bk k "k # k  k  k  k r