"""
Real-time branch sales monitoring API endpoint.
Optimized for high-traffic scenarios with caching and efficient queries.
"""

from fastapi import APIRouter, Query, Depends, HTTPException
from sqlalchemy import text
from sqlalchemy.orm import Session
from datetime import datetime, timedelta
from typing import List, Dict, Any, Optional
from pydantic import BaseModel
import time

from app.core.database import get_db
from app.core.security import get_current_user
from app.models.user import User
from app.services.branch_sales_monitoring_service import (
    assert_branch_visible,
    resolve_monitoring_scope,
    sale_timestamp_sql,
)

from app.utils.logger import get_logger

logger = get_logger(__name__)

router = APIRouter()

class BranchSalesMetric(BaseModel):
    branch_id: str
    branch_name: str
    total_sales_today: float
    total_sales_this_month: float
    transaction_count_today: int
    average_transaction: float
    active_now: int
    last_updated: str

class BranchSalesDetail(BaseModel):
    branch_id: str
    branch_name: str
    total_sales: float
    transaction_count: int
    avg_transaction: float
    hours_covered: int
    last_sale_time: Optional[str] = None
    last_sale_amount: Optional[float] = None
    payment_breakdown: Dict[str, float]
    hourly_data: List[Dict[str, Any]]
    recent_sales: List[Dict[str, Any]] = []

class RealtimeSalesData(BaseModel):
    timestamp: str
    branches: List[BranchSalesMetric]
    total_network_sales: float
    active_branches: int
    monitoring_mode: str = "network"
    is_head_office: bool = False
    total_branches: int = 0

# Cache configuration
CACHE_TTL = 5  # 5 seconds for real-time data
_cache = {}
_cache_timestamps = {}

def get_cached_data(key: str, ttl: int = CACHE_TTL) -> Optional[Any]:
    """Get cached data if still valid"""
    if key in _cache:
        cache_time = _cache_timestamps.get(key, 0)
        if time.time() - cache_time < ttl:
            return _cache[key]
        else:
            del _cache[key]
            del _cache_timestamps[key]
    return None

def set_cache(key: str, data: Any) -> None:
    """Cache data with timestamp"""
    _cache[key] = data
    _cache_timestamps[key] = time.time()

@router.get("/branch-sales/realtime", response_model=RealtimeSalesData)
async def get_realtime_branch_sales(
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
    exclude_empty: bool = Query(False, description="Exclude branches with no sales"),
):
    """
    Real-time sales for all branches (HQ / admin) or the user's branch only.
    """
    scope = resolve_monitoring_scope(db, current_user)
    cache_key = f"realtime_sales_{scope.get('company_id')}_{scope.get('branch_id')}_{exclude_empty}"
    cached = get_cached_data(cache_key, ttl=3)

    if cached:
        return cached

    try:
        now = datetime.now()
        today_start = now.replace(hour=0, minute=0, second=0, microsecond=0)
        month_start = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        ts = sale_timestamp_sql("s")

        company_clause = ""
        branch_clause = ""
        params: Dict[str, Any] = {
            "today_start": today_start,
            "month_start": month_start,
        }
        if scope.get("company_id"):
            company_clause = "AND b.company_id = :company_id"
            params["company_id"] = scope["company_id"]
        if scope.get("branch_id"):
            branch_clause = "AND b.id = :branch_id"
            params["branch_id"] = scope["branch_id"]

        query = text(f"""
            SELECT
                b.id,
                b.name,
                COUNT(CASE WHEN {ts} >= :today_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN 1 END) as sales_today,
                COUNT(CASE WHEN {ts} >= :month_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN 1 END) as sales_month,
                COALESCE(SUM(CASE WHEN {ts} >= :today_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN s.total_amount ELSE 0 END), 0) as amount_today,
                COALESCE(SUM(CASE WHEN {ts} >= :month_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN s.total_amount ELSE 0 END), 0) as amount_month
            FROM branches b
            LEFT JOIN sales s ON b.id = s.branch_id
            WHERE (b.active IS TRUE OR b.active = 1)
            {company_clause}
            {branch_clause}
            GROUP BY b.id, b.name
            ORDER BY amount_today DESC
        """)

        results = db.execute(query, params).fetchall()

        branches_data = []
        total_network_sales = 0
        active_branches_count = 0

        for row in results:
            branch_id, branch_name, sales_today, sales_month, amount_today, amount_month = row

            if exclude_empty and sales_today == 0:
                continue

            avg_transaction = float(amount_today / sales_today) if sales_today > 0 else 0

            metric = BranchSalesMetric(
                branch_id=branch_id,
                branch_name=branch_name,
                total_sales_today=float(amount_today),
                total_sales_this_month=float(amount_month),
                transaction_count_today=int(sales_today),
                average_transaction=avg_transaction,
                active_now=int(sales_today),  # Simplified: use daily count as proxy for active
                last_updated=now.isoformat()
            )

            branches_data.append(metric)
            total_network_sales += float(amount_today)
            if sales_today > 0:
                active_branches_count += 1

        response = RealtimeSalesData(
            timestamp=now.isoformat(),
            branches=branches_data,
            total_network_sales=total_network_sales,
            active_branches=active_branches_count,
            monitoring_mode=scope.get("monitoring_mode", "network"),
            is_head_office=bool(scope.get("is_head_office")),
            total_branches=len(branches_data),
        )

        set_cache(cache_key, response)
        return response

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error fetching realtime sales: {str(e)}")

@router.get("/branch-sales/network-trend")
async def get_network_sales_trend(
    hours: int = Query(24, ge=1, le=168),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """Hourly network sales trend for HQ dashboard chart."""
    from app.models.sales import Sale
    from app.models.branch import Branch
    from sqlalchemy import func

    scope = resolve_monitoring_scope(db, current_user)
    if not scope.get("view_all_branches"):
        raise HTTPException(status_code=403, detail="Network trend requires head office access")

    now = datetime.now()
    lookback = now - timedelta(hours=hours)
    ts_col = func.coalesce(Sale.date, Sale.created_at)

    q = db.query(
        func.date_trunc("hour", ts_col).label("hour"),
        func.count(Sale.id).label("cnt"),
        func.coalesce(func.sum(Sale.total_amount), 0).label("amount"),
    ).join(Branch, Branch.id == Sale.branch_id).filter(
        ts_col >= lookback,
        Sale.status.notin_(["cancelled", "void", "refunded"]),
    )
    if scope.get("company_id"):
        q = q.filter(Branch.company_id == scope["company_id"])

    rows = q.group_by(func.date_trunc("hour", ts_col)).order_by(func.date_trunc("hour", ts_col)).all()

    return {
        "timestamp": now.isoformat(),
        "hours": hours,
        "points": [
            {
                "hour": (r.hour.isoformat() if hasattr(r.hour, "isoformat") else str(r.hour)),
                "sales_count": int(r.cnt or 0),
                "sales_amount": float(r.amount or 0),
            }
            for r in rows
        ],
    }


@router.get("/branch-sales/{branch_id}/detail", response_model=BranchSalesDetail)
async def get_branch_sales_detail(
    branch_id: str,
    hours: int = Query(24, ge=1, le=168, description="Hours to look back (1-168)"),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """Detailed sales for one branch. HQ/admin may open any branch in their company."""
    from app.models.sales import Sale
    from app.models.branch import Branch
    from app.models.sales import Customer
    from sqlalchemy import func

    scope = resolve_monitoring_scope(db, current_user)
    branch = assert_branch_visible(db, scope, branch_id)

    cache_key = f"branch_detail_{branch_id}_{hours}_{scope.get('company_id')}"
    cached = get_cached_data(cache_key, ttl=5)
    if cached:
        return cached

    try:
        now = datetime.now()
        lookback_time = now - timedelta(hours=hours)
        ts_col = func.coalesce(Sale.date, Sale.created_at)

        sales_query = db.query(
            Sale.id,
            Sale.date,
            Sale.created_at,
            Sale.total_amount,
            Sale.payment_method,
            Sale.customer_id,
            Sale.reference,
        ).filter(
            Sale.branch_id == branch_id,
            ts_col >= lookback_time,
            Sale.status.notin_(["cancelled", "void", "refunded"]),
        ).all()

        def _sale_ts(s):
            return s.date or s.created_at

        total_sales = sum(float(s.total_amount or 0) for s in sales_query)
        transaction_count = len(sales_query)
        avg_transaction = total_sales / transaction_count if transaction_count > 0 else 0

        payment_breakdown: Dict[str, float] = {}
        for sale in sales_query:
            method = sale.payment_method or "Unknown"
            payment_breakdown[method] = payment_breakdown.get(method, 0) + float(sale.total_amount or 0)

        hourly_data = []
        for i in range(hours, 0, -1):
            hour_start = (now - timedelta(hours=i)).replace(minute=0, second=0, microsecond=0)
            hour_end = hour_start + timedelta(hours=1)
            hour_sales = [s for s in sales_query if hour_start <= _sale_ts(s) < hour_end]
            hourly_data.append({
                "hour": hour_start.isoformat(),
                "sales_count": len(hour_sales),
                "sales_amount": sum(float(s.total_amount or 0) for s in hour_sales),
            })

        last_sale = max(sales_query, key=_sale_ts, default=None)
        last_sale_time = _sale_ts(last_sale).isoformat() if last_sale else None
        last_sale_amount = float(last_sale.total_amount or 0) if last_sale else None

        recent_sorted = sorted(sales_query, key=_sale_ts, reverse=True)[:20]
        recent_sales = []
        for sale in recent_sorted:
            customer = (
                db.query(Customer).filter(Customer.id == sale.customer_id).first()
                if sale.customer_id
                else None
            )
            ts = _sale_ts(sale)
            recent_sales.append({
                "sale_id": sale.id,
                "receipt_number": sale.reference or sale.id[:8],
                "customer_name": customer.name if customer else "Walk-in",
                "amount": float(sale.total_amount or 0),
                "payment_method": sale.payment_method or "Unknown",
                "timestamp": ts.isoformat() if ts else None,
            })

        detail = BranchSalesDetail(
            branch_id=branch_id,
            branch_name=branch.name,
            total_sales=total_sales,
            transaction_count=transaction_count,
            avg_transaction=avg_transaction,
            hours_covered=hours,
            last_sale_time=last_sale_time,
            last_sale_amount=last_sale_amount,
            payment_breakdown=payment_breakdown,
            hourly_data=hourly_data,
            recent_sales=recent_sales,
        )

        set_cache(cache_key, detail)
        return detail

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error fetching branch sales detail: {str(e)}")


@router.get("/branch-sales/comparison", response_model=Dict[str, Any])
async def get_branch_sales_comparison(
    branch_ids: str = Query(..., description="Comma-separated branch IDs"),
    metric: str = Query("daily", regex="^(hourly|daily|weekly)$"),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """Compare sales metrics across branches (HQ / admin)."""
    from app.models.sales import Sale
    from app.models.branch import Branch
    from sqlalchemy import func

    scope = resolve_monitoring_scope(db, current_user)
    if not scope.get("view_all_branches"):
        raise HTTPException(status_code=403, detail="Branch comparison requires head office access")

    cache_key = f"comparison_{branch_ids}_{metric}_{scope.get('company_id')}"
    cached = get_cached_data(cache_key, ttl=10)
    if cached:
        return cached

    try:
        branch_list = [b.strip() for b in branch_ids.split(",") if b.strip()]
        comparison_data = {}
        now = datetime.now()
        ts_col = func.coalesce(Sale.date, Sale.created_at)

        if metric == "hourly":
            time_range = now - timedelta(hours=24)
        elif metric == "daily":
            time_range = now - timedelta(days=30)
        else:
            time_range = now - timedelta(weeks=12)

        for bid in branch_list:
            branch = db.query(Branch).filter(Branch.id == bid).first()
            if not branch:
                continue
            if scope.get("company_id") and branch.company_id != scope["company_id"]:
                continue

            sales = db.query(func.sum(Sale.total_amount), func.count(Sale.id)).filter(
                Sale.branch_id == bid,
                ts_col >= time_range,
                Sale.status.notin_(["cancelled", "void", "refunded"]),
            ).first()

            total, count = sales if sales else (0, 0)
            comparison_data[branch.name] = {
                "total_sales": float(total or 0),
                "transaction_count": int(count or 0),
                "average": float((total or 0) / (count or 1)) if count > 0 else 0,
            }

        result = {
            "metric": metric,
            "timestamp": now.isoformat(),
            "branches": comparison_data,
        }
        set_cache(cache_key, result)
        return result

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error comparing branch sales: {str(e)}")
