/iEddlZddlmZmZddlmZmZmZddlmZm Z m Z m Z m Z ddl mZddlmZddlmZddlmZdd lmZdd lmZmZmZmZmZmZmZmZm Z m!Z!m"Z"m#Z#m$Z$m%Z%m&Z&ejNe(Z)Gd d Z*y) N)datetime timedelta)OptionalDictTuple)selectfuncand_descor_) AsyncSession) joinedload) Violation)Product)Vendor)DashboardResponse KPISummaryActiveViolationsKPIMonitoredProductsKPIAveragePriceDiscountKPIViolationTrendChartTrendDataPointViolationAnalysisMarketplaceAnalysisVendorAnalysisProductAnalysisHighestDiscountAnalysisFinancialImpactAnalysisRecentViolationsPanelRecentViolationItemceZdZdZededefdZededefdZ edede fdZ edede fdZ ededefdZededeefd Zededeefd Zededeefd Zededeefd Zededefd ZededefdZe ddededefdZedede fdZ!y)DashboardServicez5Service for dashboard data aggregation and analytics.dbreturnc K|jttjtj j tjjdd{}|jxsd}|jttjtjj tjjdd{}|jxsd}|jttjtjtjj ttjjdtjjdd{}|jxsd}|jttjtjtj j ttjjdtj jdtjjdtj"dk(d{}|jxsd}||z} |jttjtjtj$j tjjdd{} | jxsd} |dk\rd} n |dk\rd} nd} t'||| | | S77777Ew) a Get KPI data for active violations. Returns: ActiveViolationsKPI with: - total_active_violations: Count of violations without notification sent - total_detections: Sum of confirmation_count - offending_vendors_count: Distinct vendors with violations - affected_products_count: Distinct products with violations Nr discovered criticalactivelow)total_active_violationstotal_detectionsoffending_vendors_countaffected_products_countstatus)executerr countridwherenotification_sent_atis_scalarsumconfirmation_countdistinct vendor_idr isnot vendor_name source_type product_namer) r#active_violations_result total_activedetections_resultr-offending_vendors_resultoffending_vendorsdiscovered_vendors_resultdiscovered_vendorstotal_offending_vendorsaffected_products_resultaffected_productsr0s D/var/www/html/marco-python-backend/app/services/dashboard_service.pyget_active_violations_kpiz*DashboardService.get_active_violations_kpi#s*, 4::ill+ , 2 293Q3Q3U3UVZ3[ \* $  0668=A #%** 488I889 : @ @A_A_AcAcdhAi j#  -335:*, 4::dmmI,?,?@A B U2266t<''--d3* $  5;;=B+-** 4::dmmI,A,ABC D U2266t<))//5''++D1))\9  + % !7==?D1"36H"H*, 4::dmmI,B,BCD E U91155d; <* $  5;;=B 2 F Q FF"$0-$;$5   k$  $  % $ s^A+M=-M0.BM=0M31BE;?;E=;E=cKtj|d{}tj|d{}tj|d{}t |||S7L717w)zw Get complete KPI summary section. Returns: KPISummary with all KPI cards N)active_violationsmonitored_productsrZ)r"rKrPr[r)r#r]r^rZs rJget_kpi_summaryz DashboardService.get_kpi_summarysh#3"L"LR"PP#3#N#Nr#RR!1!J!J2!NN/1-  QRNs1A.A(A.A*A.A,A.*A.,A.cKtjtdz }|jt t j tjjdt jtjjdjtj|k\jt j tjjt j tjd{}|j}|Dcic]\}}t!||}}}g}t#dD]m}tjtd|z z j }t!|} |j%| d}|j't)| |ot+| S7cc}}ww) z Get 30-day violation trend data. Returns: ViolationTrendChart with daily violation counts for last 30 days )daysdater2Nr)rcviolation_count) trend_data)rutcnowrr1rr rcrviolation_datelabelr2r3r4group_byorder_byallstrrangegetappendrr) r#thirty_days_agorNviolations_by_datercr2violation_dictrfidate_strs rJget_30day_violation_trendz*DashboardService.get_30day_violation_trendsu#//+iR.@@zz  )22399&A 9<<(..w7 U9++> ? Xdii 8 89 : Xdii 8 89 :  $ZZ\?QQ{tU#d)U*QQ r AOO% rAv(>>DDFD4yH"&&x3E   HeD   #j993 Rs%D G&"G#G&<G B G& G&c RK|jttjt j tj jdjttjjdtjjdjtjjtdj!dd{}|j#}|rt%|dxsd|dSy7.w)z Get marketplace with most active violations. Returns: MarketplaceAnalysis with marketplace name and violation count reNrUnknown)marketplace_namere)r1rr marketplacer r2r3rir4r r5r6r<rjrkr limitfirstrr#rNrows rJ$get_marketplace_with_most_violationsz5DashboardService.get_marketplace_with_most_violationsszz %% 9<<(../@A U2266t<))//5 Xi++ , Xd,- . U1X   lln &!$Q!49 #A - sC4D'6D%7/D'c K|jttjt j t jjdjt t jtjk(jtt jjdt jdk(j!tjj#t%dj'dd{}|j)}|rt+|d|dS|jtt j,t j t jjdjtt jjdt j,j/dt jdk(j!t j,j#t%dj'dd{}|j)}|rt+|dxsd|dSy7^71w) z Get vendor with most active violations. Prioritizes registered vendors, but includes discovered vendors. Returns: VendorAnalysis with vendor name and violation count reN registeredrxr)r=rer&ry)r1rrnamer r2rr3rijoinr;r4r r5r6r>rjrkr r|r}rr=r<r~s rJget_most_offending_vendorz*DashboardService.get_most_offending_vendorszz   9<<(../@A T)Y00FII= > U2266t<))\9 Xfkk " Xd,- . U1X  "lln !F #A  zz %% 9<<(../@A U2266t<))//5))\9Xi++ , Xd,- . U1X  "lln !F/i #A a 2 s%DJI;D.J I> /J>JcK|jttjt j tj jdjtjjdjtjjtdjdd{}|j}|rt!|d|dSy7*w)z Get product with most active violations. Returns: ProductAnalysis with product name and violation count reNrxr)r?re)r1rrr?r r2r3rir4r5r6rjrkr r|r}rr~s rJget_most_affected_productz*DashboardService.get_most_affected_product;szz && 9<<(../@A U91155d; < Xi,, - Xd,- . U1X   lln " V #A #  sC C<C:+C<c ~K|jttjtjtj tj jttjjdtjjdjttjjdd{}|j}|r@t!|dt#t%|ddt%|dt%|dSy7Ww)z Get violation with highest discount percentage. Returns: HighestDiscountAnalysis with product info and highest discount NrxrrS)r?highest_discount_percentagemsp scraped_price)r1rrr?rUrrr4r r5r6r<rkr r|r}rrVfloatr~s rJget_highest_discount_detectedz.DashboardService.get_highest_discount_detectedVszz &&// ''   U2266t<3399$? Xd9::; < U1X  "lln * V,1%A-,C#a&M#CFm   3 sC!D=#D;$AD=c K|jttjtj tj tjjttjjdtj jdd{}|j\}}|r t|nd}|xsd}tt!|dd|S7Dw)z Get estimated financial impact. Calculation: Sum of (MSP - Scraped Price) across all active violations Returns: FinancialImpactAnalysis with total impact and violation count NrRrrSUSD)total_estimated_impactcurrencyre)r1rr r8rprice_differencer2r3r4r r5r6r<r}rrrV)r#rN total_impactres rJget_financial_impactz%DashboardService.get_financial_impact{szz 334 9<<( U2266t<..44T:   )/ % o.:u\* ).Q&#(q#9+  #  sB4C=6C;7AC=c`Ktj|d{}tj|d{}tj|d{}tj |d{}tj |d{}t |||||S77m7R777w)z Get complete violation analysis section. Returns: ViolationAnalysis with all analysis insights N) marketplace_with_most_violationsmost_offending_vendormost_affected_producthighest_discount_detectedfinancial_impact)r"rrrrrr)r#r{vendorproducthighest_discountrs rJget_violation_analysisz'DashboardService.get_violation_analysiss-QQRTUU 'AA"EE(BB2FF!1!O!OPR!SS!1!F!Fr!JJ -8"(")&6-   VEFSJsUB.B$B.B&B.B(B.2B*3B.B,B.&B.(B.*B.,B.r|cK|jttjt tj j tjjdjttjj|d{}|jjj}g}|D]}t!|j"|j$|j&|j(xs$|j r|j j*ndt-|j.t-|j0|j2rt-|j2nd|j4rt-|j4nd|jsdnd|j|j6 }|j9||jtt;j<tj"j tjjdd{}|j?xsd}tA||S77(w)a Get recent violations panel. Args: db: Database session limit: Number of recent violations to return (default: 10) Returns: RecentViolationsPanel with recent violations and total count Nopennotified) r3r?r{r= current_price target_msprrUr0rhurlr)recent_violationstotal_violations)!r1rroptionsrrr4r5r6rkr rhr|scalarsuniquerlr r3r?r{r=rrrrrrUrrpr r2r7r) r#r|rN violations recent_items violationitem total_result total_counts rJget_recent_violationsz&DashboardService.get_recent_violationsszz 9  WZ 0 01 2 U91155d; < Xd9334 5 U5\   ^^%,,.224  # &I&<<&33%11%11jyO_O_i6F6F6K6Kei#I$;$;< /FOF`F`y'A'A!Bfj22',I,K,K&L%.%C%Cv(77MMD    %! && ZZ 4::ill+ , 2 293Q3Q3U3UVZ3[ \  #))+0q $*(  G < s%B)I;+I6,F$I;I9&I;9I;cJKtj|d{}tj|d{}tj|d{}tj |dd{}t ||||t jS77d7I7,w)z Get complete dashboard data. Aggregates all dashboard sections into a single response. Returns: DashboardResponse with all dashboard data Nr')r|) kpi_summaryviolation_trendviolation_analysisrlast_refreshed_at)r"r_rvrrrrrg)r#rrrrs rJget_complete_dashboardz'DashboardService.get_complete_dashboards-<R N N%_<%_rs_(((44/%*&$$   8 $_ _ r