from fastapi import APIRouter, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import func, and_, case
from datetime import datetime, timedelta
from app.db.session import get_db
from app.db.models import User, Delivery, Notification, Template
from app.api.deps import get_current_user
from app.api.schemas import (
    AnalyticsResponse, 
    AnalyticsOverview, 
    ChannelStats, 
    TemplateStats,
    TimelineStats
)

router = APIRouter(prefix="/analytics", tags=["analytics"])


def calculate_rate(numerator: int, denominator: int) -> float:
    """Calculate percentage rate, handling division by zero"""
    if denominator == 0:
        return 0.0
    return round((numerator / denominator) * 100, 2)


@router.get("", response_model=AnalyticsResponse)
def get_analytics(
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db),
    date_from: datetime | None = Query(None, description="Start date for analytics"),
    date_to: datetime | None = Query(None, description="End date for analytics"),
    channel: str | None = Query(None, description="Filter by channel (email, telegram, vk)"),
    group_by: str = Query("day", description="Timeline grouping: hour, day, week, month")
):
    """
    Get comprehensive analytics for the tenant
    """
    # Set default date range if not provided (last 30 days)
    if not date_to:
        date_to = datetime.utcnow()
    if not date_from:
        date_from = date_to - timedelta(days=30)
    
    # Base query for deliveries in tenant
    base_query = db.query(Delivery).join(Notification).filter(
        Notification.tenant_id == current_user.tenant_id
    )
    
    # Apply date filters
    if date_from:
        base_query = base_query.filter(Notification.created_at >= date_from)
    if date_to:
        base_query = base_query.filter(Notification.created_at <= date_to)
    
    # Apply channel filter
    if channel:
        base_query = base_query.filter(Delivery.channel == channel)
    
    # 1. Overview Statistics
    overview_stats = db.query(
        func.count(Delivery.id).label('total_sent'),
        func.count(case((Delivery.status == 'sent', 1))).label('total_delivered'),
        func.count(case((Delivery.opened_at.isnot(None), 1))).label('total_opened'),
        func.count(case((Delivery.clicked_at.isnot(None), 1))).label('total_clicked')
    ).select_from(Delivery).join(Notification).filter(
        Notification.tenant_id == current_user.tenant_id,
        Notification.created_at >= date_from if date_from else True,
        Notification.created_at <= date_to if date_to else True,
        Delivery.channel == channel if channel else True
    ).first()
    
    total_sent = overview_stats.total_sent or 0
    total_delivered = overview_stats.total_delivered or 0
    total_opened = overview_stats.total_opened or 0
    total_clicked = overview_stats.total_clicked or 0
    
    overview = AnalyticsOverview(
        total_sent=total_sent,
        total_delivered=total_delivered,
        total_opened=total_opened,
        total_clicked=total_clicked,
        delivery_rate=calculate_rate(total_delivered, total_sent),
        open_rate=calculate_rate(total_opened, total_delivered),
        click_rate=calculate_rate(total_clicked, total_opened)
    )
    
    # 2. Statistics by Channel
    channel_stats_raw = db.query(
        Delivery.channel,
        func.count(Delivery.id).label('sent'),
        func.count(case((Delivery.status == 'sent', 1))).label('delivered'),
        func.count(case((Delivery.opened_at.isnot(None), 1))).label('opened'),
        func.count(case((Delivery.clicked_at.isnot(None), 1))).label('clicked')
    ).join(Notification).filter(
        Notification.tenant_id == current_user.tenant_id,
        Notification.created_at >= date_from if date_from else True,
        Notification.created_at <= date_to if date_to else True
    ).group_by(Delivery.channel).all()
    
    by_channel = [
        ChannelStats(
            channel=row.channel,
            sent=row.sent,
            delivered=row.delivered,
            opened=row.opened,
            clicked=row.clicked,
            delivery_rate=calculate_rate(row.delivered, row.sent),
            open_rate=calculate_rate(row.opened, row.delivered),
            click_rate=calculate_rate(row.clicked, row.opened)
        )
        for row in channel_stats_raw
    ]
    
    # 3. Statistics by Template
    template_stats_raw = db.query(
        Template.id,
        Template.name,
        func.count(Delivery.id).label('sent'),
        func.count(case((Delivery.status == 'sent', 1))).label('delivered'),
        func.count(case((Delivery.opened_at.isnot(None), 1))).label('opened'),
        func.count(case((Delivery.clicked_at.isnot(None), 1))).label('clicked')
    ).join(Notification, Template.id == Notification.template_id)\
     .join(Delivery, Notification.id == Delivery.notification_id)\
     .filter(
        Template.tenant_id == current_user.tenant_id,
        Notification.created_at >= date_from if date_from else True,
        Notification.created_at <= date_to if date_to else True,
        Delivery.channel == channel if channel else True
    ).group_by(Template.id, Template.name)\
     .order_by(func.count(Delivery.id).desc())\
     .limit(10)\
     .all()
    
    by_template = [
        TemplateStats(
            id=row.id,
            name=row.name,
            sent=row.sent,
            delivered=row.delivered,
            opened=row.opened,
            clicked=row.clicked,
            delivery_rate=calculate_rate(row.delivered, row.sent),
            open_rate=calculate_rate(row.opened, row.delivered),
            click_rate=calculate_rate(row.clicked, row.opened)
        )
        for row in template_stats_raw
    ]
    
    # 4. Timeline Statistics
    # Define truncate format based on grouping
    date_trunc_format = {
        'hour': 'hour',
        'day': 'day',
        'week': 'week',
        'month': 'month'
    }.get(group_by, 'day')
    
    timeline_stats_raw = db.query(
        func.date_trunc(date_trunc_format, Notification.created_at).label('period'),
        func.count(Delivery.id).label('sent'),
        func.count(case((Delivery.status == 'sent', 1))).label('delivered'),
        func.count(case((Delivery.opened_at.isnot(None), 1))).label('opened'),
        func.count(case((Delivery.clicked_at.isnot(None), 1))).label('clicked')
    ).join(Notification).filter(
        Notification.tenant_id == current_user.tenant_id,
        Notification.created_at >= date_from if date_from else True,
        Notification.created_at <= date_to if date_to else True,
        Delivery.channel == channel if channel else True
    ).group_by(func.date_trunc(date_trunc_format, Notification.created_at))\
     .order_by(func.date_trunc(date_trunc_format, Notification.created_at))\
     .all()
    
    timeline = [
        TimelineStats(
            period=row.period.isoformat() if row.period else "",
            sent=row.sent,
            delivered=row.delivered,
            opened=row.opened,
            clicked=row.clicked
        )
        for row in timeline_stats_raw
    ]
    
    return AnalyticsResponse(
        overview=overview,
        by_channel=by_channel,
        by_template=by_template,
        timeline=timeline
    )


@router.get("/overview", response_model=AnalyticsOverview)
def get_overview(
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db),
    date_from: datetime | None = Query(None),
    date_to: datetime | None = Query(None)
):
    """Get overview statistics only"""
    # Set default date range
    if not date_to:
        date_to = datetime.utcnow()
    if not date_from:
        date_from = date_to - timedelta(days=30)
    
    stats = db.query(
        func.count(Delivery.id).label('total_sent'),
        func.count(case((Delivery.status == 'sent', 1))).label('total_delivered'),
        func.count(case((Delivery.opened_at.isnot(None), 1))).label('total_opened'),
        func.count(case((Delivery.clicked_at.isnot(None), 1))).label('total_clicked')
    ).select_from(Delivery).join(Notification).filter(
        Notification.tenant_id == current_user.tenant_id,
        Notification.created_at >= date_from,
        Notification.created_at <= date_to
    ).first()
    
    total_sent = stats.total_sent or 0
    total_delivered = stats.total_delivered or 0
    total_opened = stats.total_opened or 0
    total_clicked = stats.total_clicked or 0
    
    return AnalyticsOverview(
        total_sent=total_sent,
        total_delivered=total_delivered,
        total_opened=total_opened,
        total_clicked=total_clicked,
        delivery_rate=calculate_rate(total_delivered, total_sent),
        open_rate=calculate_rate(total_opened, total_delivered),
        click_rate=calculate_rate(total_clicked, total_opened)
    )