from sqlalchemy.orm import Session
from fastapi import HTTPException, Depends
from typing import List, Dict, Any, Optional
from datetime import datetime
from sqlalchemy import func
from src.apps.feedback.models import Feedback, Task
from src.apps.feedback.schemas import TaskResponse, SentimentCountResponse
from src.apps.datasource.models import Datasource
from src.apps.stores.models import Branch
from src.utils.db import get_db
from src.apps.auth.controller import get_current_user
from datetime import datetime, timedelta


#           --- SENTIMENT COUNT BY DATE ---
def fetch_sentiment_counts(
    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 sentiment counts grouped by feedback_posting_date for a given branch.
    Ensures the branch belongs to the logged-in user.
    Allows filtering by source, date range, and rating range.
    """
    
    # Check if the branch exists and belongs to the logged-in user
    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")

    # Start building the query
    query = db.query(
        Feedback.feedback_posting_date.label("name"),
        func.count().filter(Feedback.sentiment == "positive").label("positive"),
        func.count().filter(Feedback.sentiment == "negative").label("negative"),
        func.count().filter(Feedback.sentiment == "mixed").label("mixed"),
        func.count().filter(Feedback.sentiment == "neutral").label("neutral")
    ).filter(Feedback.branch_id == branch_id)

    # Apply source filter if provided
    if source:
        query = query.filter(Feedback.feedback_source == source)

    # Apply date range filter
    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"}
        )

    # Apply rating filter if provided
    if min_rating is not None:
        query = query.filter(Feedback.feedback_rating >= min_rating)
    if max_rating is not None:
        query = query.filter(Feedback.feedback_rating <= max_rating)

    # Group by date and order by date
    results = query.group_by(Feedback.feedback_posting_date).order_by(Feedback.feedback_posting_date).all()

    if not results:
        return {
            "status": True,
            "code": 200,
            "message": "No sentiment data found for this branch",
            "data": []
        }

    # Convert query results to required response format
    sentiment_list = [
        {
            "date": row.name.strftime("%Y-%m-%d"),  # Format date as string
            "positive": row.positive or 0,
            "negative": row.negative or 0,
            "mixed": row.mixed or 0,
            "neutral": row.neutral or 0  
        }
        for row in results
    ]

    return {
        "status": True,
        "code": 200,
        "message": "Sentiment analysis data retrieved successfully",
        "data": sentiment_list
    }


#           --- SENTIMENT BREAKDOWN PERCENTAGE ---
def fetch_sentiment_breakdown(
    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 sentiment breakdown percentages for a given branch.
    Ensures the branch belongs to the logged-in user.
    Allows filtering by source, date range, and rating range.
    """

    # Check if the branch exists and belongs to the logged-in user
    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")

    # Start building the query
    query = db.query(Feedback).filter(Feedback.branch_id == branch_id)

    # Apply source filter if provided
    if source:
        query = query.filter(Feedback.feedback_source == source)

    # Apply date range filter
    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"}
        )

    # Apply rating filter if provided
    if min_rating is not None:
        query = query.filter(Feedback.feedback_rating >= min_rating)
    if max_rating is not None:
        query = query.filter(Feedback.feedback_rating <= max_rating)

    # Count total feedbacks after filtering
    total_count = query.with_entities(func.count(Feedback.feedback_id)).scalar()

    if total_count == 0:
        return {
            "status": True,
            "code": 200,
            "message": "No sentiment data found for this branch",
            "data": []
        }

    # Fetch sentiment counts
    sentiment_counts = (
        query.with_entities(
            func.count().filter(Feedback.sentiment == "positive").label("positive"),
            func.count().filter(Feedback.sentiment == "negative").label("negative"),
            func.count().filter(Feedback.sentiment == "mixed").label("mixed"),
            func.count().filter(Feedback.sentiment == "neutral").label("neutral"),
        )
        .first()
    )

    # Calculate percentage values
    sentiment_data = {
        "positive": round((sentiment_counts.positive or 0) / total_count * 100, 2),
        "negative": round((sentiment_counts.negative or 0) / total_count * 100, 2),
        "mixed": round((sentiment_counts.mixed or 0) / total_count * 100, 2),
        "neutral": round((sentiment_counts.neutral or 0) / total_count * 100, 2),
    }

    # Convert to list format for chart representation
    response_data = [
        {"sentiment": key, "percentage": value} for key, value in sentiment_data.items()
    ]

    return {
        "status": True,
        "code": 200,
        "message": "Sentiment breakdown retrieved successfully",
        "data": response_data
    }


#           --- REVIEW COUNT BY DATE ---
def fetch_review_count(
    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 review count by feedback_posting_date, average reviews per day, and total reviews.
    Ensures the branch belongs to the logged-in user.
    Allows filtering by source, date range, and feedback rating (stars).
    """

    # Check if the branch exists and belongs to the logged-in user
    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": []}
        )

    # Handle date range: set defaults if not provided
    if not start_date or not end_date:
        end_date_dt = datetime.today()
        start_date_dt = end_date_dt - timedelta(days=30)
    else:
        try:
            start_date_dt = datetime.strptime(start_date, "%Y-%m-%d")
            end_date_dt = datetime.strptime(end_date, "%Y-%m-%d")
        except ValueError:
            raise HTTPException(
                status_code=400,
                detail={"status": False, "code": 400, "message": "Invalid date format. Use YYYY-MM-DD"}
            )

    date_diff = (end_date_dt - start_date_dt).days

    # Start building the query
    query = db.query(Feedback).filter(Feedback.branch_id == branch_id)

    # Apply filters
    if source:
        query = query.filter(Feedback.feedback_source == source)

    query = query.filter(Feedback.feedback_posting_date.between(start_date_dt, end_date_dt))

    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))

    # Total review count
    total_reviews = query.with_entities(func.count(Feedback.feedback_id)).scalar()

    if total_reviews == 0:
        return {
            "status": True,
            "code": 200,
            "message": "No reviews found for this branch",
            "data": {
                "total_reviews": 0,
                "avg_reviews_per_day": 0,
                "reviews_by_date": []
            }
        }

    # Grouping: Daily if <= 30 days, Monthly otherwise
    if date_diff <= 30:
        reviews_by_date = (
            query.with_entities(
                Feedback.feedback_posting_date.label("date"),
                func.count(Feedback.feedback_id).label("review_count")
            )
            .group_by(Feedback.feedback_posting_date)
            .order_by(Feedback.feedback_posting_date)
            .all()
        )
    else:
        reviews_by_date = (
            query.with_entities(
                func.date_trunc('month', Feedback.feedback_posting_date).label("date"),
                func.count(Feedback.feedback_id).label("review_count")
            )
            .group_by(func.date_trunc('month', Feedback.feedback_posting_date))
            .order_by(func.date_trunc('month', Feedback.feedback_posting_date))
            .all()
        )

    # Calculate average reviews per day
    total_days = len(reviews_by_date)
    avg_reviews_per_day = round(total_reviews / total_days, 2) if total_days > 0 else 0

    # Format response
    review_list = [
        {
            "date": row.date.strftime("%Y-%m-%d") if date_diff <= 30 else row.date.strftime("%B %Y"),
            "review_count": row.review_count
        }
        for row in reviews_by_date
    ]

    return {
        "status": True,
        "code": 200,
        "message": "Review count analysis retrieved successfully",
        "data": {
            "total_reviews": total_reviews,
            "avg_reviews_per_day": avg_reviews_per_day,
            "reviews_by_date": review_list
        }
    }

#           --- RATING COUNT BY DATE ---

def fetch_feedback_rating(
    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 average feedback rating by feedback_posting_date and overall average rating.
    Ensures the branch belongs to the logged-in user.
    Allows filtering by source, date range, and feedback rating (stars).
    """

    # Check if the branch exists and belongs to the logged-in user
    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": []}
        )

    # Handle date range: default to last 30 days if not provided
    if not start_date or not end_date:
        end_date_dt = datetime.today()
        start_date_dt = end_date_dt - timedelta(days=30)
    else:
        try:
            start_date_dt = datetime.strptime(start_date, "%Y-%m-%d")
            end_date_dt = datetime.strptime(end_date, "%Y-%m-%d")
        except ValueError:
            raise HTTPException(
                status_code=400,
                detail={"status": False, "code": 400, "message": "Invalid date format. Use YYYY-MM-DD"}
            )

    date_diff = (end_date_dt - start_date_dt).days

    # Start building the query
    query = db.query(Feedback).filter(
        Feedback.branch_id == branch_id,
        Feedback.feedback_rating != None
    )

    if source:
        query = query.filter(Feedback.feedback_source == source)

    query = query.filter(Feedback.feedback_posting_date.between(start_date_dt, end_date_dt))

    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))

    # Overall average feedback rating
    overall_avg_rating = query.with_entities(func.avg(Feedback.feedback_rating)).scalar()

    if not overall_avg_rating:
        return {
            "status": True,
            "code": 200,
            "message": "No feedback ratings found for this branch",
            "data": {
                "overall_avg_rating": 0,
                "feedback_ratings_by_date": []
            }
        }

    # Grouped results by day or month
    if date_diff <= 30:
        feedback_ratings_by_date = (
            query.with_entities(
                Feedback.feedback_posting_date.label("date"),
                func.avg(Feedback.feedback_rating).label("avg_rating")
            )
            .group_by(Feedback.feedback_posting_date)
            .order_by(Feedback.feedback_posting_date)
            .all()
        )
    else:
        feedback_ratings_by_date = (
            query.with_entities(
                func.date_trunc('month', Feedback.feedback_posting_date).label("date"),
                func.avg(Feedback.feedback_rating).label("avg_rating")
            )
            .group_by(func.date_trunc('month', Feedback.feedback_posting_date))
            .order_by(func.date_trunc('month', Feedback.feedback_posting_date))
            .all()
        )

    # Format response
    feedback_list = [
        {
            "date": row.date.strftime("%Y-%m-%d") if date_diff <= 30 else row.date.strftime("%B %Y"),
            "avg_rating": round(row.avg_rating, 2)
        }
        for row in feedback_ratings_by_date
    ]

    return {
        "status": True,
        "code": 200,
        "message": "Average feedback rating analysis retrieved successfully",
        "data": {
            "overall_avg_rating": round(overall_avg_rating, 2),
            "feedback_ratings_by_date": feedback_list
        }
    }


#                    ------ SENTIMENT SCORE --------
def fetch_sentiment_score(
    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
):
    """
    Calculate the overall sentiment score for a given branch.
    Ensures the branch belongs to the logged-in user.
    Allows filtering by source, date range, and feedback rating (stars).
    """

    # Check if the branch exists and belongs to the logged-in user
    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"}
        )
    
   # 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")

    # Start building the query
    query = db.query(
        func.count().filter(Feedback.sentiment == "positive").label("positive"),
        func.count().filter(Feedback.sentiment == "negative").label("negative"),
        func.count().filter(Feedback.sentiment == "mixed").label("mixed"),
        func.count(Feedback.feedback_id).label("total")
    ).filter(Feedback.branch_id == branch_id)

    # Apply source filter if provided
    if source:
        query = query.filter(Feedback.feedback_source == source)

    # Apply date range filter if provided
    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"}
            )

    # Apply feedback rating (stars) filter if provided
    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))

    # Fetch sentiment counts
    sentiment_data = query.first()

    # If no feedback found, return 0 as sentiment score
    if not sentiment_data or sentiment_data.total == 0:
        return {
            "status": True,
            "code": 200,
            "message": "No sentiment data found for this branch",
            "data": {"sentiment_score": 0}
        }

    # Calculate sentiment fractions (instead of percentages)
    positive_frac = (sentiment_data.positive or 0) / sentiment_data.total
    negative_frac = (sentiment_data.negative or 0) / sentiment_data.total
    mixed_frac = (sentiment_data.mixed or 0) / sentiment_data.total

    # Apply the correct formula with fractions
    sentiment_score = ((positive_frac - negative_frac) + (0.5 * mixed_frac) + 1) / 2

    # Ensure score stays between 0 and 1 (edge case handling)
    sentiment_score = ((max(0, min(sentiment_score, 1)))*100)

    return {
        "status": True,
        "code": 200,
        "message": "Sentiment score calculated successfully",
        "data": {"sentiment_score": round(sentiment_score, 4)}
    }


'''


def fetch_tasks(
    branch_id: int, 
    datasource_id: int, 
    db: Session, 
    user=Depends(get_current_user)
) -> Dict[str, Any]:
    """
    Fetch tasks for a given branch and datasource, ensuring the branch belongs to the logged-in user.
    """

    # Validate if branch exists and belongs to the current user
    branch_exists = db.query(Branch).filter(
        Branch.branch_id == branch_id, 
        Branch.user_id == user.user_id  # Ensuring the branch belongs to the logged-in user
    ).first()
    
    if not branch_exists:
        raise HTTPException(
            status_code=404,
            detail={"status": False, "code": 404, "message": "Branch not found or does not belong to the user", "data": []}
        )

    # Validate if datasource exists
    datasource_exists = db.query(Datasource).filter(Datasource.ds_id == datasource_id).first()
    
    if not datasource_exists:
        raise HTTPException(
            status_code=404,
            detail={"status": False, "code": 404, "message": "Datasource not found", "data": []}
        )

    # Fetch tasks for the given branch_id and datasource_id
    tasks = db.query(Task).filter(
        Task.branch_id == branch_id,
        Task.datasource_id == datasource_id
    ).all()

    if not tasks:
        return {
            "status": True,
            "code": 200,
            "message": "No tasks found for this branch and datasource",
            "data": []
        }

    # Convert ORM models to Pydantic response format
    task_list = [TaskResponse.model_validate(task) for task in tasks]

    return {
        "status": True,
        "code": 200,
        "message": "Tasks retrieved successfully",
        "data": task_list
    }
'''