from sqlalchemy.orm import Session from sqlalchemy import func, distinct from sqlalchemy.sql import text from datetime import datetime from fastapi import HTTPException from sqlalchemy.exc import SQLAlchemyError from src.apps.sentiment.models import Word, ReviewTopic from src.apps.feedback.models import Feedback from src.apps.stores.models import Branch from src.apps.users.models import User from typing import Dict, Any, Optional, List from datetime import datetime, timedelta from src.apps.datasource.models import Datasource # ------- API FOR RELEVENT WORD SENTIMENT ANALYSIS ----------- def get_word_sentiment_analysis( branch_id: int, db: Session, user, source: Optional[str], start_date: Optional[str] = None, # Kept for compatibility but unused end_date: Optional[str] = None, # Kept for compatibility but unused min_rating: Optional[int] = None, max_rating: Optional[int] = None, page: int = 1, page_size: int = 10 ) -> Dict[str, Any]: """ Fetches review word sentiment analysis data with pagination. """ # Verify if the user has access to the branch branch = db.query(Branch).filter( Branch.branch_id == branch_id, Branch.user_id == user.user_id ).first() if not branch: raise HTTPException( status_code=403, detail={"status": False, "code": 403, "message": "Unauthorized access to this branch", "data": []} ) # Base query for words base_query = db.query( Word.words, Word.sentiment, Feedback.feedback_posting_date, func.count(Word.words).label("mentions"), Feedback.original_content, Feedback.customer_name ).join( Feedback, Word.feedback_id == Feedback.feedback_id ).filter(Word.branch_id == branch_id) # Apply filters if source: base_query = base_query.filter(Feedback.feedback_source == source) if min_rating is not None and max_rating is not None: if min_rating < 1 or max_rating > 5 or min_rating > max_rating: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid rating range. Use values between 1 and 5"} ) base_query = base_query.filter(Feedback.feedback_rating.between(min_rating, max_rating)) # Count total unique words total_words = db.query(func.count(distinct(Word.words))).filter( Word.branch_id == branch_id ) if source: total_words = total_words.filter(Feedback.feedback_source == source) if min_rating is not None and max_rating is not None: total_words = total_words.filter(Feedback.feedback_rating.between(min_rating, max_rating)) total_words = total_words.scalar() # Fetch all matching word data (grouped) word_data = base_query.group_by( Word.words, Word.sentiment, Feedback.feedback_posting_date, Feedback.original_content, Feedback.customer_name ).all() if not word_data: return { "status": False, "code": 404, "message": "No records found for the given filters.", "data": [] } # Total review count total_reviews_query = db.query(func.count(Feedback.feedback_id)).filter( Feedback.branch_id == branch_id ) if source: total_reviews_query = total_reviews_query.filter(Feedback.feedback_source == source) if min_rating is not None and max_rating is not None: total_reviews_query = total_reviews_query.filter(Feedback.feedback_rating.between(min_rating, max_rating)) total_reviews = total_reviews_query.scalar() # Organize word data word_dict = {} for row in word_data: word, sentiment, posting_date, mentions, review_text, customer_name = row if word not in word_dict: word_dict[word] = { "word": word, "mentions": 0, "sentiment_distribution": { "positive": {"count": 0, "reviews": []}, "negative": {"count": 0, "reviews": []}, "mixed": {"count": 0, "reviews": []}, "neutral": {"count": 0, "reviews": []} }, "overall_reviews": 0, "trend": [] } word_dict[word]["mentions"] += mentions word_dict[word]["sentiment_distribution"][sentiment]["count"] += mentions word_dict[word]["sentiment_distribution"][sentiment]["reviews"].append({ "text": review_text, "customer_name": customer_name, "feedback_posting_date": posting_date.strftime("%Y-%m-%d") }) word_dict[word]["trend"].append({ "date": posting_date.strftime("%Y-%m-%d"), "mentions": mentions }) # Convert to list and sort by number of mentions word_list = [] for word, data in word_dict.items(): total_mentions = data["mentions"] # Convert sentiment counts to percentage format (*100) for sentiment, sentiment_data in data["sentiment_distribution"].items(): count = sentiment_data["count"] percentage = round((count / total_mentions) * 100, 2) if total_mentions > 0 else 0 sentiment_data["percentage"] = percentage # Calculate overall review percentage (*100) data["overall_reviews"] = round((total_mentions / total_reviews) * 100, 2) if total_reviews > 0 else 0 word_list.append(data) # Sort and paginate word_list = sorted(word_list, key=lambda x: x["mentions"], reverse=True) paginated_word_list = word_list[(page - 1) * page_size: page * page_size] total_pages = (total_words + page_size - 1) // page_size return { "status": True, "code": 200, "message": "Review word sentiment analysis retrieved successfully", "data": paginated_word_list, "pagination": { "total_records": total_words, "total_pages": total_pages, "current_page": page, "page_size": page_size } } # ------- API FOR RELEVENT TOPIC SENTIMENT ANALYSIS ----------- def get_topic_sentiment_analysis( branch_id: int, db: Session, user, source: Optional[str], start_date: Optional[str] = None, # kept for compatibility but unused end_date: Optional[str] = None, # kept for compatibility but unused min_rating: Optional[int] = None, max_rating: Optional[int] = None, page: int = 1, page_size: int = 10 ) -> Dict[str, Any]: """ Fetches review topic sentiment analysis data with pagination. """ # Verify if the user has access to the branch branch = db.query(Branch).filter( Branch.branch_id == branch_id, Branch.user_id == user.user_id ).first() if not branch: raise HTTPException( status_code=403, detail={"status": False, "code": 403, "message": "Unauthorized access to this branch", "data": []} ) # Base query for topics base_query = db.query( ReviewTopic.topic_name, ReviewTopic.topic_sentiment, Feedback.feedback_posting_date, func.count(ReviewTopic.topic_name).label("mentions"), Feedback.original_content, Feedback.customer_name ).join( Feedback, ReviewTopic.feedback_id == Feedback.feedback_id ).filter(ReviewTopic.branch_id == branch_id) # Apply source filter if source: base_query = base_query.filter(Feedback.feedback_source == source) # Apply rating filter if min_rating is not None and max_rating is not None: if min_rating < 1 or max_rating > 5 or min_rating > max_rating: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid rating range. Use values between 1 and 5"} ) base_query = base_query.filter(Feedback.feedback_rating.between(min_rating, max_rating)) # Count total unique topics total_topics = db.query(func.count(distinct(ReviewTopic.topic_name))).filter( ReviewTopic.branch_id == branch_id ) if source: total_topics = total_topics.filter(Feedback.feedback_source == source) if min_rating is not None and max_rating is not None: total_topics = total_topics.filter(Feedback.feedback_rating.between(min_rating, max_rating)) total_topics = total_topics.scalar() # Apply pagination topic_data = base_query.group_by( ReviewTopic.topic_name, ReviewTopic.topic_sentiment, Feedback.feedback_posting_date, Feedback.original_content, Feedback.customer_name ).limit(page_size).offset((page - 1) * page_size).all() if not topic_data: return { "status": False, "code": 404, "message": "No records found for the given filters.", "data": [] } # Fetch total review count (for percentage calculation) total_reviews_query = db.query(func.count(Feedback.feedback_id)).filter( Feedback.branch_id == branch_id ) if source: total_reviews_query = total_reviews_query.filter(Feedback.feedback_source == source) if min_rating is not None and max_rating is not None: total_reviews_query = total_reviews_query.filter(Feedback.feedback_rating.between(min_rating, max_rating)) total_reviews = total_reviews_query.scalar() # Organizing topic data topic_dict = {} for row in topic_data: topic, sentiment, posting_date, mentions, review_text, customer_name = row if topic not in topic_dict: topic_dict[topic] = { "topic": topic, "mentions": 0, "sentiment_distribution": { "positive": {"count": 0, "reviews": []}, "negative": {"count": 0, "reviews": []}, "mixed": {"count": 0, "reviews": []}, "neutral": {"count": 0, "reviews": []} }, "overall_reviews": 0, "trend": [] } topic_dict[topic]["mentions"] += mentions topic_dict[topic]["sentiment_distribution"][sentiment]["count"] += mentions topic_dict[topic]["sentiment_distribution"][sentiment]["reviews"].append({ "customer_name": customer_name, "posting_date": posting_date.strftime("%Y-%m-%d"), "review_text": review_text }) topic_dict[topic]["trend"].append({ "date": posting_date.strftime("%Y-%m-%d"), "mentions": mentions }) topic_list = [] for topic, data in topic_dict.items(): total_mentions = data["mentions"] # Convert sentiment counts to percentage format (now in %) for sentiment, sentiment_data in data["sentiment_distribution"].items(): count = sentiment_data["count"] percentage = round((count / total_mentions) * 100, 2) if total_mentions > 0 else 0 sentiment_data["percentage"] = percentage # Calculate overall review percentage (now in %) data["overall_reviews"] = round((total_mentions / total_reviews) * 100, 2) if total_reviews > 0 else 0 topic_list.append(data) total_pages = (total_topics + page_size - 1) // page_size return { "status": True, "code": 200, "message": "Review topic sentiment analysis retrieved successfully", "data": topic_list, "pagination": { "total_records": total_topics, "total_pages": total_pages, "current_page": page, "page_size": page_size } } # ------- API FOR RATING SENTIMENT ANALYSIS ----------- def fetch_rating_details( branch_id: int, db: Session, user, source: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None, min_rating: Optional[int] = None, max_rating: Optional[int] = None ): """ 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). """ # Verify branch access branch = db.query(Branch).filter( Branch.branch_id == branch_id, Branch.user_id == user.user_id ).first() if not branch: raise HTTPException( status_code=403, detail={"status": False, "code": 403, "message": "Unauthorized access to this branch", "data": []} ) # Set default date range to the last 30 days if not provided if not start_date or not end_date: end_date = datetime.today().strftime("%Y-%m-%d") start_date = (datetime.today() - timedelta(days=30)).strftime("%Y-%m-%d") # Base query for feedback ratings query = db.query(Feedback).filter( Feedback.branch_id == branch_id, Feedback.feedback_rating != None # Ignore NULL ratings ) # Apply filters if source: query = query.filter(Feedback.feedback_source == source) if start_date and end_date: try: start_date = datetime.strptime(start_date, "%Y-%m-%d") end_date = datetime.strptime(end_date, "%Y-%m-%d") query = query.filter(Feedback.feedback_posting_date.between(start_date, end_date)) except ValueError: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid date format. Use YYYY-MM-DD"} ) if min_rating is not None and max_rating is not None: if min_rating < 1 or max_rating > 5 or min_rating > max_rating: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid rating range. Use values between 1 and 5"} ) query = query.filter(Feedback.feedback_rating.between(min_rating, max_rating)) # Calculate overall statistics overall_avg_rating = query.with_entities(func.avg(Feedback.feedback_rating)).scalar() total_rating_count = query.with_entities(func.count(Feedback.feedback_rating)).scalar() if not total_rating_count: # If no ratings found return { "status": True, "code": 200, "message": "No feedback ratings found for this branch", "data": { "overall_avg_rating": 0, "total_rating_count": 0, "stars_breakdown": {str(i): 0 for i in range(1, 6)}, # Initialize breakdown with zero counts "ratings_by_date": [] } } # Fetch breakdown of ratings (how many 5-star, 4-star, etc.) stars_breakdown = ( query.with_entities( Feedback.feedback_rating, func.count(Feedback.feedback_rating) ) .group_by(Feedback.feedback_rating) .all() ) # Convert breakdown data into dictionary stars_dict = {str(i): 0 for i in range(1, 6)} # Initialize with zero for all stars for rating, count in stars_breakdown: stars_dict[str(int(rating))] = count # Store count in dictionary # Fetch count of ratings per feedback_posting_date ratings_by_date = ( query.with_entities( Feedback.feedback_posting_date.label("date"), func.count(Feedback.feedback_rating).label("rating_count") ) .group_by(Feedback.feedback_posting_date) .order_by(Feedback.feedback_posting_date) .all() ) # Format response data ratings_list = [ { "date": row.date.strftime("%Y-%m-%d"), "rating_count": row.rating_count } for row in ratings_by_date ] return { "status": True, "code": 200, "message": "Feedback rating analysis retrieved successfully", "data": { "overall_avg_rating": round(overall_avg_rating, 2), "total_rating_count": total_rating_count, "stars_breakdown": stars_dict, # e.g., {"5": 20, "4": 15, "3": 10, "2": 5, "1": 2} "ratings_by_date": ratings_list # e.g., [{"date": "2024-02-20", "rating_count": 5}, ...] } } # ------- API FOR REVIEW SUMMARY SENTIMENT ANALYSIS ----------- def fetch_feedback_summary( branch_id: int, db: Session, user, source: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None, min_rating: Optional[int] = None, max_rating: Optional[int] = None ) -> Dict[str, Any]: """ 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`) """ # Verify if the user has access to the branch branch = db.query(Branch).filter( Branch.branch_id == branch_id, Branch.user_id == user.user_id ).first() if not branch: raise HTTPException( status_code=403, detail={"status": False, "code": 403, "message": "Unauthorized access to this branch", "data": []} ) # Set default date range to the last 30 days if not provided if not start_date or not end_date: end_date = datetime.today().strftime("%Y-%m-%d") start_date = (datetime.today() - timedelta(days=30)).strftime("%Y-%m-%d") # Base query for feedbacks with filters applied feedback_query = db.query(Feedback).filter(Feedback.branch_id == branch_id) gross_total_review = feedback_query.count() if source: feedback_query = feedback_query.filter(Feedback.feedback_source == source) if start_date and end_date: try: start_date = datetime.strptime(start_date, "%Y-%m-%d") end_date = datetime.strptime(end_date, "%Y-%m-%d") feedback_query = feedback_query.filter(Feedback.feedback_posting_date.between(start_date, end_date)) # not doing date filter to so a except ValueError: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid date format. Use YYYY-MM-DD"} ) if min_rating is not None and max_rating is not None: if min_rating < 1 or max_rating > 5 or min_rating > max_rating: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid rating range. Use values between 1 and 5"} ) feedback_query = feedback_query.filter(Feedback.feedback_rating.between(min_rating, max_rating)) # Fetch total review count **(after applying filters)** total_reviews = feedback_query.count() if total_reviews == 0: return { "status": True, "code": 200, "message": "No feedbacks found for the given filters.", "data": { "gross_total_review": gross_total_review, "total_reviews": 0, "average_rating": 0, "rating_breakdown": {str(i): 0 for i in range(1, 6)}, # 1-star to 5-star "sentiment_breakdown": { "positive": {"count": 0, "percentage": 0}, "negative": {"count": 0, "percentage": 0}, "mixed": {"count": 0, "percentage": 0}, "neutral": {"count": 0, "percentage": 0} } } } # Calculate average rating **(after applying filters)** avg_rating = feedback_query.with_entities(func.avg(Feedback.feedback_rating)).scalar() avg_rating = round(avg_rating, 2) if avg_rating else 0 # Ensure rounding # Get rating breakdown **(after applying filters)** rating_breakdown_data = ( feedback_query.with_entities( Feedback.feedback_rating, func.count(Feedback.feedback_rating) ) .group_by(Feedback.feedback_rating) .all() ) rating_count = {str(i): 0 for i in range(1, 6)} # Initialize rating count (1-star to 5-star) for rating, count in rating_breakdown_data: rating_count[str(rating)] = count # Get sentiment breakdown **(after applying filters)** sentiment_breakdown_data = ( feedback_query.with_entities( Feedback.sentiment, func.count(Feedback.sentiment) ) .group_by(Feedback.sentiment) .all() ) sentiment_count = {"positive": 0, "negative": 0, "mixed": 0, "neutral": 0} for sentiment, count in sentiment_breakdown_data: sentiment_count[sentiment] = count # Convert sentiment breakdown to percentages **(whole number format)** sentiment_data = { sentiment: { "count": count, "percentage": round((count / total_reviews) , 2) if total_reviews > 0 else 0 } for sentiment, count in sentiment_count.items() } return { "status": True, "code": 200, "message": "Feedback summary retrieved successfully", "data": { "gross_total_reviews": gross_total_review, "total_reviews": total_reviews, "average_rating": avg_rating, "rating_breakdown": rating_count, "sentiment_breakdown": sentiment_data } } # ---------- API FOR DETAILED REVIEW LISTING BY DATE ------------ def fetch_all_reviews_by_date( branch_id: int, db: Session, user, source: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None, min_rating: Optional[int] = None, max_rating: Optional[int] = None, page: int = 1, page_size: int = 10 ) -> Dict[str, Any]: """ 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`) """ # ✅ Verify if the user has access to the branch branch = db.query(Branch).filter( Branch.branch_id == branch_id, Branch.user_id == user.user_id ).first() if not branch: raise HTTPException( status_code=403, detail={"status": False, "code": 403, "message": "Unauthorized access to this branch", "data": []} ) # Set default date range to the last 30 days if not provided if not start_date or not end_date: end_date = datetime.today().strftime("%Y-%m-%d") start_date = (datetime.today() - timedelta(days=30)).strftime("%Y-%m-%d") # Base query with filters applied feedback_query = db.query(Feedback).filter(Feedback.branch_id == branch_id) if source: feedback_query = feedback_query.filter(Feedback.feedback_source == source) if start_date and end_date: try: start_date = datetime.strptime(start_date, "%Y-%m-%d") end_date = datetime.strptime(end_date, "%Y-%m-%d") feedback_query = feedback_query.filter(Feedback.feedback_posting_date.between(start_date, end_date)) except ValueError: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid date format. Use YYYY-MM-DD"} ) if min_rating is not None and max_rating is not None: if min_rating < 1 or max_rating > 5 or min_rating > max_rating: raise HTTPException( status_code=400, detail={"status": False, "code": 400, "message": "Invalid rating range. Use values between 1 and 5"} ) feedback_query = feedback_query.filter(Feedback.feedback_rating.between(min_rating, max_rating)) # Pagination setup total_records = feedback_query.count() total_pages = (total_records + page_size - 1) // page_size # Calculate total pages feedbacks = ( feedback_query .order_by(Feedback.feedback_posting_date) .offset((page - 1) * page_size) .limit(page_size) .all() ) if not feedbacks: return { "status": True, "code": 200, "message": "No feedbacks found for the given filters.", "data": {}, "pagination": { "page": page, "page_size": page_size, "total_pages": total_pages, "total_records": total_records } } # Convert feedback objects into a dictionary grouped by `feedback_posting_date` feedback_by_date = {} for feedback in feedbacks: feedback_date = str(feedback.feedback_posting_date) # Fetch keywords from the Word table keywords = db.query(Word.words).filter(Word.feedback_id == feedback.feedback_id).all() keywords = {k[0] for k in keywords} # Convert to a set # Fetch topics from the ReviewTopic table topics = db.query(ReviewTopic.topic_name).filter(ReviewTopic.feedback_id == feedback.feedback_id).all() topics = {t[0] for t in topics} # Convert to a set feedback_data = { "feedback_id": feedback.feedback_id, "branch_id": feedback.branch_id, "datasource_id": feedback.datasource_id, "customer_name": feedback.customer_name, "review_text": feedback.original_content, "feedback_source": feedback.feedback_source, "feedback_rating": feedback.feedback_rating, "sentiment": feedback.sentiment, "confidence_score": feedback.confidence_score, "emotion": feedback.emotion, "arousal": feedback.arousal, "created_at": feedback.created_at, "keywords": keywords, "topics": topics } if feedback_date not in feedback_by_date: feedback_by_date[feedback_date] = [] feedback_by_date[feedback_date].append(feedback_data) return { "status": True, "code": 200, "message": "Feedbacks retrieved successfully", "data": feedback_by_date, "pagination": { "page": page, "page_size": page_size, "total_pages": total_pages, "total_records": total_records } } # --------- SENTIMENT COMPARISON API --------------- # Month names dictionary MONTH_NAMES = { 1: "Jan", 2: "Feb", 3: "Mar", 4: "Apr", 5: "May", 6: "Jun", 7: "Jul", 8: "Aug", 9: "Sep", 10: "Oct", 11: "Nov", 12: "Dec" } def compare_sentiments(db: Session, branch_id: int, comparison_frequency: str, user, source: Optional[str] = None): # Verify user access branch = db.query(Branch).filter( Branch.branch_id == branch_id, Branch.user_id == user.user_id ).first() if not branch: raise HTTPException( status_code=403, detail={"status": False, "code": 403, "message": "Unauthorized access to this branch", "data": {}} ) today = datetime.today() current_year, previous_year = today.year, today.year - 1 current_month, previous_month = today.month, today.month - 1 if today.month > 1 else 12 previous_month_year = current_year if today.month > 1 else previous_year current_month_name, previous_month_name = MONTH_NAMES[current_month], MONTH_NAMES[previous_month] if comparison_frequency == "yearly": start_last_year = datetime(previous_year, 1, 1) query = """ 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 """ params = {"branch_id": branch_id, "start_last_year": start_last_year} if source: query += " AND feedback_source = :source" params["source"] = source query += """ GROUP BY year, period, sentiment ORDER BY year DESC, period """ # Include years dynamically sentiment_data = { "comp_frequency": { "current_comp_node": str(current_year), "previous_comp_node": str(previous_year) }, "current_comp_node": [], "previous_comp_node": [] } elif comparison_frequency == "monthly": start_this_month = today.replace(day=1) start_last_month = (start_this_month - timedelta(days=1)).replace(day=1) query = """ 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 """ params = {"branch_id": branch_id, "start_last_month": start_last_month} if source: query += " AND feedback_source = :source" params["source"] = source query += """ GROUP BY year, month, period, sentiment ORDER BY year DESC, month DESC, period """ # Include months dynamically sentiment_data = { "comp_frequency": { "current_comp_node": current_month_name, "previous_comp_node": previous_month_name }, "current_comp_node": [], "previous_comp_node": [] } else: raise HTTPException(status_code=400, detail="Invalid frequency. Use 'yearly' or 'monthly'.") try: result = db.execute(text(query), params) sentiment_counts = result.fetchall() for row in sentiment_counts: row_mapping = row._mapping period = int(row_mapping["period"]) # Month (yearly) or Day (monthly) year = int(row_mapping["year"]) month = int(row_mapping.get("month", 0)) sentiment = row_mapping["sentiment"] count = int(row_mapping["count"]) if comparison_frequency == "yearly": period_key = MONTH_NAMES[period] target_list = sentiment_data["current_comp_node"] if year == current_year else sentiment_data["previous_comp_node"] month_entry = next((item for item in target_list if item["month"] == period_key), None) if not month_entry: month_entry = {"date": period_key, "positive": 0, "negative": 0, "mixed": 0, "neutral": 0} target_list.append(month_entry) month_entry[sentiment] = count elif comparison_frequency == "monthly": period_key = f"{current_month_name} {period}" if month == current_month else f"{previous_month_name} {period}" target_list = sentiment_data["current_comp_node"] if month == current_month else sentiment_data["previous_comp_node"] day_entry = next((item for item in target_list if item["date"] == period_key), None) if not day_entry: day_entry = {"date": period_key, "positive": 0, "negative": 0, "mixed": 0, "neutral": 0} target_list.append(day_entry) day_entry[sentiment] = count return { "status": True, "code": 200, "message": "Feedback comparison retrieved successfully", "data": sentiment_data } except SQLAlchemyError as e: raise HTTPException( status_code=500, detail={"status": False, "code": 500, "message": "Database error", "error": str(e)} )