
    wh                        d dl mZ d dlmZmZ d dlmZ d dlmZ d dlm	Z	 d dl
mZ d dlmZmZ d dlmZ d d	lmZ d d
lmZ d dlmZmZmZmZ d dlmZmZ d dlmZ 	 	 	 	 	 	 d-dededee   dee   dee   dee   dee   dededeeef   fdZ	 	 	 	 	 	 d-dededee   dee   dee   dee   dee   dededeeef   fdZ 	 	 	 	 	 d.dededee   dee   dee   dee   dee   fdZ!	 	 	 	 	 d.dededee   dee   dee   dee   dee   deeef   fdZ"	 	 	 	 	 	 	 d/dededee   dee   dee   dee   dee   dededeeef   fdZ#ddd d!d"d#d$d%d&d'd(d)d*Z$d0deded+edee   fd,Z%y)1    )Session)funcdistinct)text)datetime)HTTPException)SQLAlchemyError)WordReviewTopic)Feedback)Branch)User)DictAnyOptionalList)r   	timedelta)
DatasourceN	branch_iddbsource
start_dateend_date
min_rating
max_ratingpage	page_sizereturnc
           	      N
   |j                  t              j                  t        j                  | k(  t        j                  |j                  k(        j                         }
|
st        ddddg d      |j                  t        j                  t        j                  t        j                  t        j                  t        j                        j                  d      t        j                  t        j                         j#                  t        t        j$                  t        j$                  k(        j                  t        j                  | k(        }|r"|j                  t        j&                  |k(        }|Q|O|dk  s
|dkD  s||kD  rt        d	dd	d
d      |j                  t        j(                  j+                  ||            }|j                  t        j                  t-        t        j                                    j                  t        j                  | k(        }|r"|j                  t        j&                  |k(        }|1|/|j                  t        j(                  j+                  ||            }|j/                         }|j1                  t        j                  t        j                  t        j                  t        j                  t        j                         j3                         }|sdddg dS |j                  t        j                  t        j$                              j                  t        j                  | k(        }|r"|j                  t        j&                  |k(        }|1|/|j                  t        j(                  j+                  ||            }|j/                         }i }|D ]  }|\  }}}}}}||vr|ddg ddg ddg ddg dddg d||<   ||   dxx   |z  cc<   ||   d   |   dxx   |z  cc<   ||   d   |   d   j5                  |||j7                  d      d       ||   d   j5                  |j7                  d      |d        g }|j9                         D ]u  \  }}|d   }|d   j9                         D ](  \  }}|d   }|dkD  rt;        ||z  dz  d      nd}||d<   * |dkD  rt;        ||z  dz  d      nd|d<   |j5                  |       w t=        |d d      }||dz
  |	z  ||	z   }||	z   dz
  |	z  }dd d!|||||	d"d#S )$zF
    Fetches review word sentiment analysis data with pagination.
      F"Unauthorized access to this branchstatuscodemessagedatastatus_codedetailmentions        0Invalid rating range. Use values between 1 and 5r#   r$   r%     'No records found for the given filters.r   countreviewspositivenegativemixedneutral)wordr*   sentiment_distributionoverall_reviewstrendr;   r3   r4   %Y-%m-%d)r   customer_namefeedback_posting_dater=   dater*   d      
percentager<   c                     | d   S )Nr*    )xs    </var/www/html/hubwallet-dev/src/apps/sentiment/controller.py<lambda>z-get_word_sentiment_analysis.<locals>.<lambda>   s
    *     T)keyreverse   z5Review word sentiment analysis retrieved successfullytotal_recordstotal_pagescurrent_pager   r#   r$   r%   r&   
pagination)queryr   filterr   user_idfirstr   r
   words	sentimentr   r@   r   r3   labeloriginal_contentr?   joinfeedback_idfeedback_sourcefeedback_ratingbetweenr   scalargroup_byallappendstrftimeitemsroundsorted) r   r   userr   r   r   r   r   r   r   branch
base_querytotal_words	word_datatotal_reviews_querytotal_reviews	word_dictrowr:   rZ   posting_dater*   review_textr?   	word_listr&   total_mentionssentiment_datar3   rE   paginated_word_listrQ   s                                    rI   get_word_sentiment_analysisry      s<   " XXf$$I%$,,& eg 
 #S=akmn
 	
 

&&

4::$$Z0!! dH000 fT^^y()  &&x'?'?6'IJ
*"8>Z!^zJ/F"'Ast   &&x'?'?'G'G
T^'_`
 ((4::htzz&:;<CC)#K !(()A)AV)KL*"8!(()A)A)I)I*V`)ab$$&K ##

DNNH$B$BHD]D]_g_u_u	ce  @	
 	
 ((4::h.B.B#CDKKi' 1889Q9QU[9[\*"81889Q9Q9Y9YZdfp9qr'..0M I NQKixmy *+ ;*+ ;'(R8)*r:	+ $%IdO 	$
#x/#$01)<WEQE$01)<YGNN*%1%:%::%FP
 	 	$ '' ))*5 )
 	3> Ioo' 
dj) *..F)G)M)M)O 	6%I~"7+EESVWEW 6#=qA]^J+5N<(	6 WdfgVg%-)G3(NPQ"Rmn y&=tLI#TAX$:D9<LM*Q.9<K J#(& "	
 rK   c
           	      \
   |j                  t              j                  t        j                  | k(  t        j                  |j                  k(        j                         }
|
st        ddddg d      |j                  t        j                  t        j                  t        j                  t        j                  t        j                        j                  d      t        j                  t        j                         j#                  t        t        j$                  t        j$                  k(        j                  t        j                  | k(        }|r"|j                  t        j&                  |k(        }|Q|O|dk  s
|dkD  s||kD  rt        d	dd	d
d      |j                  t        j(                  j+                  ||            }|j                  t        j                  t-        t        j                                    j                  t        j                  | k(        }|r"|j                  t        j&                  |k(        }|1|/|j                  t        j(                  j+                  ||            }|j/                         }|j1                  t        j                  t        j                  t        j                  t        j                  t        j                         j3                  |	      j5                  |dz
  |	z        j7                         }|sdddg dS |j                  t        j                  t        j$                              j                  t        j                  | k(        }|r"|j                  t        j&                  |k(        }|1|/|j                  t        j(                  j+                  ||            }|j/                         }i }|D ]  }|\  }}}}}}||vr|ddg ddg ddg ddg dddg d||<   ||   dxx   |z  cc<   ||   d   |   dxx   |z  cc<   ||   d   |   d   j9                  ||j;                  d      |d       ||   d   j9                  |j;                  d      |d        g }|j=                         D ]u  \  }}|d   }|d   j=                         D ](  \  }}|d   }|dkD  rt?        ||z  dz  d      nd}||d<   * |dkD  rt?        ||z  dz  d      nd|d<   |j9                  |       w ||	z   dz
  |	z  }ddd|||||	d d!S )"zG
    Fetches review topic sentiment analysis data with pagination.
    r    Fr!   r"   r'   r*   r+   r,   r-   r.   r/   r0   r1   r   r2   r5   )topicr*   r;   r<   r=   r;   r3   r4   r>   )r?   rs   rt   r=   rA   rC   rD   rE   r<   TrN   z6Review topic sentiment analysis retrieved successfullyrO   rS   ) rU   r   rV   r   rW   rX   r   r   
topic_nametopic_sentimentr   r@   r   r3   r[   r\   r?   r]   r^   r_   r`   ra   r   rb   rc   limitoffsetrd   re   rf   rg   rh   )r   r   rj   r   r   r   r   r   r   r   rk   rl   total_topics
topic_dataro   rp   
topic_dictrr   r{   rZ   rs   r*   rt   r?   
topic_listr&   rv   rw   r3   rE   rQ   s                                  rI   get_topic_sentiment_analysisr      sK   " XXf$$I%$,,& eg 
 #S=akmn
 	
 ##&&

;))*00<!! d8#7#77 f[""i/0  &&x'?'?6'IJ
 *"8>Z!^zJ/F"'Ast   &&x'?'?'G'G
T^'_`
 88DJJx0F0F'GHIPP*L #**8+C+Cv+MN*"8#**8+C+C+K+KJXb+cd&&(L $$##&&!! eIvvtax945cce  @	
 	
 ((4::h.B.B#CDKKi' 1889Q9QU[9[\*"81889Q9Q9Y9YZdfp9qr'..0M J ORLy,+}
"*+ ;*+ <'(R8)*r:	+ $%!Ju 	5*%1%523I>wG8SG523I>yIPP*(11*=&R
 	 	5'")) ))*5 +
 	3< J!'')  tj) *..F)G)M)M)O 	6%I~"7+EESVWEW 6#=qA]^J+5N<(	6 WdfgVg%-)G3(NPQ"Rmn$   )+a/I=K K)& "	
 rK   c                    |j                  t              j                  t        j                  | k(  t        j                  |j                  k(        j                         }|st        ddddg d      |r|sSt        j                         j                  d      }t        j                         t        d      z
  j                  d      }|j                  t              j                  t        j                  | k(  t        j                  d	k7        }	|r"|	j                  t        j                  |k(        }	|r^|r\	 t        j                  |d      }t        j                  |d      }|	j                  t        j                  j!                  ||            }	|Q|O|dk  s
|dkD  s||kD  rt        d
dd
dd      |	j                  t        j                  j!                  ||            }	|	j%                  t'        j(                  t        j                              j+                         }
|	j%                  t'        j,                  t        j                              j+                         }|s.dddddt/        dd      D ci c]  }t1        |      d c}g ddS |	j%                  t        j                  t'        j,                  t        j                              j3                  t        j                        j5                         }t/        dd      D ci c]  }t1        |      d }}|D ]  \  }}||t1        t7        |            <    |	j%                  t        j                  j9                  d      t'        j,                  t        j                        j9                  d            j3                  t        j                        j;                  t        j                        j5                         }|D cg c]*  }|j<                  j                  d      |j>                  d, }}dddtA        |
d      |||ddS # t"        $ r t        d
dd
dd      w xY wc c}w c c}w c c}w )aD  
    Fetch feedback rating analysis, including:
    - Average rating
    - Total number of ratings
    - Breakdown of ratings (5-star, 4-star, etc.)
    - Rating count by feedback_posting_date
    Ensures the branch belongs to the logged-in user.
    Allows filtering by source, date range, and feedback rating (stars).
    r    Fr!   r"   r'   r>      daysNr-   #Invalid date format. Use YYYY-MM-DDr/   r+   r,   r.   TrN   z)No feedback ratings found for this branchr      )overall_avg_ratingtotal_rating_countstars_breakdownratings_by_daterB   rating_count)rB   r   z/Feedback rating analysis retrieved successfullyrD   )!rU   r   rV   r   rW   rX   r   r   todayrf   r   r   r`   r_   strptimer@   ra   
ValueErrorwith_entitiesr   avgrb   r3   rangestrrc   rd   intr[   order_byrB   r   rh   )r   r   rj   r   r   r   r   r   rk   rU   r   r   ir   
stars_dictratingr3   r   rr   ratings_lists                       rI   fetch_rating_detailsr   W  s   * XXf$$I%$,,& eg 
 #S=akmn
 	
 X>>#,,Z8nn&);;EEjQ
 HHX%%i'  D(E X55?@h	!**:zBJ((:>HLL!?!?!G!G
T\!]^E *"8>Z!^zJ/F"'Ast  X55==j*UV ,,TXXh6N6N-OPWWY,,TZZ8P8P-QRYY[B&'&'7<Q{#C!CFAI#C#%		

 
	
 	$$JJx//0	
 
(**	+	  &+1a[1#a&!)1J1( -',
3s6{#$-
 	**008JJx//066~F	
 
(00	1	(00	1	   #
  HH%%j1,,	
L  D"'(:A">"4)+	
	
 
E  	"'Afg 	4 $D  2 s   ;AP; >QQ4/Q#;Qc                 T   |j                  t              j                  t        j                  | k(  t        j                  |j                  k(        j                         }|st        ddddg d      |r|sSt        j                         j                  d      }t        j                         t        d      z
  j                  d      }|j                  t              j                  t        j                  | k(        }	|	j                         }
|r"|	j                  t        j                  |k(        }	|r^|r\	 t        j                  |d      }t        j                  |d      }|	j                  t        j                  j!                  ||            }	|Q|O|dk  s
|dkD  s||kD  rt        d	dd	dd      |	j                  t        j$                  j!                  ||            }	|	j                         }|dk(  r@ddd|
ddt'        dd      D ci c]  }t)        |      d c}dddddddddddddddS |	j+                  t-        j.                  t        j$                              j1                         }|rt3        |d      nd}|	j+                  t        j$                  t-        j                  t        j$                              j5                  t        j$                        j7                         }t'        dd      D ci c]  }t)        |      d }}|D ]  \  }}||t)        |      <    |	j+                  t        j8                  t-        j                  t        j8                              j5                  t        j8                        j7                         }ddddd}|D ]
  \  }}|||<    |j;                         D ci c]  \  }}|||dkD  rt3        ||z  d      ndd! }}}ddd|
||||ddS # t"        $ r t        d	dd	d
d      w xY wc c}w c c}w c c}}w )a  
    Fetches a summary of feedbacks for a given branch, including:
    - Total review count
    - Average rating
    - Rating breakdown (how many 5-star, 4-star, etc.)
    - Sentiment breakdown (count and percentage of positive, negative, mixed, and neutral sentiments)

    Filters:
    - Source (`feedback_source`)
    - Date range (`feedback_posting_date`)
    - Rating range (`feedback_rating`)
    r    Fr!   r"   r'   r>   r   r   r-   r   r/   r+   r,   r.   r   TrN   )No feedbacks found for the given filters.r   )r3   rE   r5   )gross_total_reviewrp   average_ratingrating_breakdownsentiment_breakdownrD   z'Feedback summary retrieved successfully)gross_total_reviewsrp   r   r   r   )rU   r   rV   r   rW   rX   r   r   r   rf   r   r   r3   r_   r   r@   ra   r   r`   r   r   r   r   r   rb   rh   rc   rd   rZ   rg   )r   r   rj   r   r   r   r   r   rk   feedback_queryr   rp   r   
avg_ratingrating_breakdown_datar   r   r3   sentiment_breakdown_datasentiment_countrZ   rw   s                         rI   fetch_feedback_summaryr     s$   0 XXf$$I%$,,& eg 
 #S=akmn
 	
 X>>#,,Z8nn&);;EEjQ
 XXh'..x/A/AY/NON'--/'..x/G/G6/QRh		!**:zBJ((:>H+2283Q3Q3Y3YZdfn3opN *"8>Z!^zJ/F"'Ast  (..x/G/G/O/OPZ\f/gh #((*MB&8!""#8=a$D1SVQY$D*+1 =*+1 ='(:)*!<	(	
 	
&  --dhhx7O7O.PQXXZJ)3z1%J 	$$$$JJx//0	
 
(**	+	  (-Q{3!CFAI3L3. *$)S[!*
 	$$JJx))*	
 
($$	%	  $%!aANO4 +	5%*	"+ !0 5 5 7
 Iu	 	@MPQ@Q%!6!<WX
 	
N  <#5*( ,#1
	 [  	"'Afg 	4 %E0 4&s   9AO< PP$P$<Pc
                 >   |j                  t              j                  t        j                  | k(  t        j                  |j                  k(        j                         }
|
st        ddddg d      |r|sSt        j                         j                  d      }t        j                         t        d      z
  j                  d      }|j                  t              j                  t        j                  | k(        }|r"|j                  t        j                  |k(        }|r^|r\	 t        j                  |d      }t        j                  |d      }|j                  t        j                  j                  ||            }|Q|O|dk  s
|dkD  s||kD  rt        d	dd	dd      |j                  t        j"                  j                  ||            }|j%                         }||	z   dz
  |	z  }|j'                  t        j                        j)                  |dz
  |	z        j+                  |	      j-                         }|sdddi ||	||ddS i }|D ]  }t/        |j                        }|j                  t0        j2                        j                  t0        j4                  |j4                  k(        j-                         }|D ch c]  }|d   	 }}|j                  t6        j8                        j                  t6        j4                  |j4                  k(        j-                         }|D ch c]  }|d   	 }}|j4                  |j                  |j:                  |j<                  |j>                  |j                  |j"                  |j@                  |jB                  |jD                  |jF                  |jH                  ||d}||vrg ||<   ||   jK                  |        ddd|||	||ddS # t         $ r t        d	dd	d
d      w xY wc c}w c c}w )aU  
    Fetch paginated feedbacks grouped by `feedback_posting_date` for a given branch ID.
    Includes keywords and relevant topics from `Word` and `ReviewTopic` tables.

    Filters:
    - Source (`feedback_source`)
    - Date range (`feedback_posting_date`)
    - Rating range (`feedback_rating`)
    - Pagination (`page`, `page_size`)
    r    Fr!   r"   r'   r>   r   r   r-   r   r/   r+   r,   r.   TrN   r   )r   r   rQ   rP   rS   r   )r^   r   datasource_idr?   rt   r_   r`   rZ   confidence_scoreemotionarousal
created_atkeywordstopicsz Feedbacks retrieved successfully)&rU   r   rV   r   rW   rX   r   r   r   rf   r   r   r_   r   r@   ra   r   r`   r3   r   r   r~   rd   r   r
   rY   r^   r   r|   r   r?   r\   rZ   r   r   r   r   re   )r   r   rj   r   r   r   r   r   r   r   rk   r   rP   rQ   	feedbacksfeedback_by_datefeedbackfeedback_dater   kr   tfeedback_datas                          rI   fetch_all_reviews_by_dater   k  s    0 XXf$$I%$,,& eg 
 #S=akmn
 	
 X>>#,,Z8nn&);;EEjQ
 XXh'..x/A/AY/NON'..x/G/G6/QRh	!**:zBJ((:>H+2283Q3Q3Y3YZdfn3opN *"8>Z!^zJ/F"'Ast  (..x/G/G/O/OPZ\f/gh #((*M 9,q0Y>K 		(00	1	Y&	'	y		  B&*!.	
 	
  >H::; 88DJJ'..t/?/?8CWCW/WX\\^"*+QAaD++ +001889P9PT\ThTh9himmo &'1!A$'' $//!++%33%33#44'77'77!++ ( 9 9''''"-- 
"  00.0]+'..}=?>D 5 "&*	
 Y  	"'Afg 	` , (s   )AO7 
P3P7PJanFebMarAprMayJunJulAugSepOctNovDec)r+   rD         r,   r         	   
         comparison_frequencyc           
        ! | j                  t              j                  t        j                  |k(  t        j                  |j                  k(        j                         }|st        ddddi d      t        j                         }|j                  |j                  dz
  }}|j                  |j                  dkD  r|j                  dz
  nd}
}	|j                  dkD  r|n|}t        |	   t        |
   }}|dk(  rAt        |dd      }d	}||d
}|r
|dz  }||d<   |dz  }t        |      t        |      dg g d}ne|dk(  rS|j                  d      }|t        d      z
  j                  d      }d}||d}|r
|dz  }||d<   |dz  }||dg g d}nt        dd      	 | j                  t!        |      |      }|j#                         }|D ]	  }|j$                  }t'        |d         }t'        |d         }t'        |j)                  dd            }|d   }t'        |d         }|dk(  rNt        |   !||k(  r|d   n|d   }t+        !fd |D        d       }|s!ddddd!}|j-                  |       |||<   |dk(  s||	k(  r| d"| n| d"| !||	k(  r|d   n|d   }t+        !fd#|D        d       }|s!ddddd!}|j-                  |       |||<    d$d%d&|dS # t.        $ r } t        d'dd'd(t        |       d)      d } ~ ww xY w)*Nr    Fr!   r"   r'   r+   r   yearlya`  
            SELECT 
                EXTRACT(MONTH FROM feedback_posting_date) AS period,
                EXTRACT(YEAR FROM feedback_posting_date) AS year,
                sentiment,
                COUNT(*) AS count
            FROM feedbacks
            WHERE branch_id = :branch_id 
            AND feedback_posting_date >= :start_last_year
        )r   start_last_yearz AND feedback_source = :sourcer   z]
            GROUP BY year, period, sentiment
            ORDER BY year DESC, period
        )current_comp_nodeprevious_comp_node)comp_frequencyr   r   monthly)dayr   a  
            SELECT 
                EXTRACT(DAY FROM feedback_posting_date) AS period,
                EXTRACT(MONTH FROM feedback_posting_date) AS month,
                EXTRACT(YEAR FROM feedback_posting_date) AS year,
                sentiment,
                COUNT(*) AS count
            FROM feedbacks
            WHERE branch_id = :branch_id 
            AND feedback_posting_date >= :start_last_month
        )r   start_last_monthzp
            GROUP BY year, month, period, sentiment
            ORDER BY year DESC, month DESC, period
        r-   z-Invalid frequency. Use 'yearly' or 'monthly'.periodyearmonthr   rZ   r3   r   r   c              3   4   K   | ]  }|d    k(  s|  yw)r   NrG   .0item
period_keys     rI   	<genexpr>z%compare_sentiments.<locals>.<genexpr>q  s     #`TDMU_D_D#`   )rB   r6   r7   r8   r9    c              3   4   K   | ]  }|d    k(  s|  yw)rB   NrG   r   s     rI   r   z%compare_sentiments.<locals>.<genexpr>|  s     !]4$v,R\B\$!]r   TrN   z*Feedback comparison retrieved successfullyi  zDatabase error)r#   r$   r%   error)rU   r   rV   r   rW   rX   r   r   r   r   r   MONTH_NAMESr   replacer   executer   fetchall_mappingr   getnextre   r	   )"r   r   r   rj   r   rk   r   current_yearprevious_yearcurrent_monthprevious_monthprevious_month_yearcurrent_month_nameprevious_month_namer   rU   paramsrw   start_this_monthr   resultsentiment_countsrr   row_mappingr   r   r   rZ   r3   target_listmonth_entry	day_entryer   s"                                    @rI   compare_sentimentsr     s   XXf$$I%$,,& eg 
 #S=akmn
 	

 NNE"'**ejj1n-L$)KKEKKRSOqY[>M*/++/,}.9-.H+VdJe+x'"=!Q7	  )_M55E%F8  	 &)%6&)-&8 "$"$
 
	* ==Q=/,ya/@@IIaIP
  )>NO55E%F8  	 &8&9 "$"$
 4cdd-
DK0!??,# 	-C,,KX./F{6*+D34E#K0IG,-E#x/(0
EI\EYn-@A_m  oC  `D"#`[#`bfg"+51RS^_lm"nK&&{3).I&%2AF-AW 231VH=`s_ttuv|u}]~
EJmE[n-@Aao  qE  bF !];!]_cd	 )3PQ\]jk lI&&y1',	)$9	-> C"	
 	
  
#S=MX[\]X^_
 	

s    !CK" A K" "	L+LL)NNNNr+   r   )NNNNN)NNNNNr+   r   )N)&sqlalchemy.ormr   
sqlalchemyr   r   sqlalchemy.sqlr   r   fastapir   sqlalchemy.excr	   src.apps.sentiment.modelsr
   r   src.apps.feedback.modelsr   src.apps.stores.modelsr   src.apps.users.modelsr   typingr   r   r   r   r   src.apps.datasource.modelsr   r   r   ry   r   r   r   r   r   r   rG   rK   rI   <module>r	     sc   " %   ! * 7 - ) & , , ( 1 !%" $ $___ SM	_
 _ sm_ _ _ _ _ 
#s(^_R !%" $ $___ SM	_
 _ sm_ _ _ _ _ 
#s(^_N ! $" $ $@@@ SM	@
 @ sm@ @ @P ! $" $ $MMM SM	M
 M smM M M 
#s(^Mh ! $" $ $JJJ SM	J
 J smJ J J J J 
#s(^Jd 5U5U%UL
7 L
s L
# L
]efi]j L
rK   