"""
Business Intelligence endpoints for analytics and reporting
"""
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from sqlalchemy import func, desc, case, and_, or_
from typing import List, Dict, Any, Optional
from datetime import datetime, timedelta, date

from app.core.database import get_db
from app.core.dependencies import get_current_company_id
from app.models.branch import Branch
from app.models.inventory import Product, UnitOfMeasure
from app.models.sales import Sale, SaleItem, Customer
from app.models.purchases import PurchaseOrder, Purchase, Supplier
from app.models.user import User
from app.models.accounting import JournalEntry

from app.utils.logger import get_logger

logger = get_logger(__name__)

router = APIRouter()

_SALE_EXCLUDE = ("cancelled", "void", "reversed", "draft", "pending")


def _branch_ids(db: Session, company_id: Optional[str]) -> Optional[List[str]]:
    if not company_id:
        return None
    return [r[0] for r in db.query(Branch.id).filter(Branch.company_id == company_id).all()]


def _sale_ts():
    return func.coalesce(Sale.date, Sale.sale_time, Sale.created_at)


def _scope_sales(q, branch_ids):
    q = q.filter(~func.lower(Sale.status).in_(_SALE_EXCLUDE))
    if branch_ids is not None:
        q = q.filter(Sale.branch_id.in_(branch_ids))
    return q


def _scope_products(q, branch_ids):
    if branch_ids is not None:
        q = q.filter(Product.branch_id.in_(branch_ids))
    return q.filter(Product.active == True)


def _sum_sales(db, branch_ids, start, end) -> float:
    q = db.query(func.coalesce(func.sum(Sale.total_amount), 0)).filter(
        _sale_ts() >= start, _sale_ts() < end
    )
    return float(_scope_sales(q, branch_ids).scalar() or 0)

@router.get("/dashboard/overview")
async def get_dashboard_overview(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Get high-level KPIs for BI dashboard"""
    try:
        _prod_filter = []
        if company_id:
            _branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id).subquery()
            _prod_filter = [Product.branch_id.in_(_branch_ids_sq)]

        if company_id:
            _branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id).subquery()
            _prod_filter = [Product.branch_id.in_(_branch_ids_sq)]

        # Total products
        total_products = db.query(func.count(Product.id)).filter(*_prod_filter).scalar() or 0
        
        # Products by UOM category (tenant-scoped)
        _pbc = db.query(
            UnitOfMeasure.category,
            func.count(Product.id).label('count')
        ).join(Product, Product.unit_of_measure_id == UnitOfMeasure.id)
        if _prod_filter:
            _pbc = _pbc.filter(*_prod_filter)
        products_by_category = _pbc.group_by(UnitOfMeasure.category).all()
        
        category_stats = {cat: count for cat, count in products_by_category}
        
        # Total active UOMs
        total_uoms = db.query(func.count(UnitOfMeasure.id)).filter(
            UnitOfMeasure.is_active == True
        ).scalar() or 0
        
        # UOM usage (products per unit)
        uom_usage = db.query(
            UnitOfMeasure.id,
            UnitOfMeasure.name,
            UnitOfMeasure.abbreviation,
            UnitOfMeasure.category,
            func.count(Product.id).label('product_count')
        ).outerjoin(
            Product, Product.unit_of_measure_id == UnitOfMeasure.id
        ).filter(
            UnitOfMeasure.is_active == True
        ).group_by(
            UnitOfMeasure.id,
            UnitOfMeasure.name,
            UnitOfMeasure.abbreviation,
            UnitOfMeasure.category
        ).order_by(
            desc('product_count')
        ).limit(10).all()
        
        top_uoms = [
            {
                "unit_name": f"{row.name} ({row.abbreviation})",
                "category": row.category,
                "product_count": row.product_count
            }
            for row in uom_usage
        ]
        
        return {
            "success": True,
            "data": {
                "total_products": total_products,
                "total_active_uoms": total_uoms,
                "products_by_category": category_stats,
                "top_used_uoms": top_uoms
            }
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/uom/category-distribution")
async def get_uom_category_distribution(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Get distribution of UOMs across categories"""
    try:
        _prod_cond = None
        if company_id:
            _bids = db.query(Branch.id).filter(Branch.company_id == company_id).subquery()
            _prod_cond = Product.branch_id.in_(_bids)
        distribution = db.query(
            UnitOfMeasure.category,
            UnitOfMeasure.subcategory,
            func.count(UnitOfMeasure.id).label('unit_count'),
            func.count(Product.id).label('product_count')
        ).outerjoin(
            Product, and_(Product.unit_of_measure_id == UnitOfMeasure.id, _prod_cond) if _prod_cond is not None else Product.unit_of_measure_id == UnitOfMeasure.id
        ).filter(
            UnitOfMeasure.is_active == True
        ).group_by(
            UnitOfMeasure.category,
            UnitOfMeasure.subcategory
        ).order_by(
            UnitOfMeasure.category,
            UnitOfMeasure.subcategory
        ).all()
        
        result = [
            {
                "category": row.category,
                "subcategory": row.subcategory,
                "unit_count": row.unit_count,
                "product_count": row.product_count,
                "utilization_rate": round((row.product_count / row.unit_count * 100), 2) if row.unit_count > 0 else 0
            }
            for row in distribution
        ]
        
        return {"success": True, "data": result}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/products/uom-analysis")
async def get_product_uom_analysis(
    category: Optional[str] = None,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Detailed analysis of products by UOM"""
    try:
        query = db.query(
            Product.id,
            Product.name,
            Product.sku,
            Product.selling_price,
            UnitOfMeasure.name.label('unit_name'),
            UnitOfMeasure.abbreviation.label('unit_abbr'),
            UnitOfMeasure.category,
            UnitOfMeasure.subcategory,
            UnitOfMeasure.decimal_places
        ).join(
            UnitOfMeasure, Product.unit_of_measure_id == UnitOfMeasure.id
        )

        if company_id:
            _bids = db.query(Branch.id).filter(Branch.company_id == company_id)
            query = query.filter(Product.branch_id.in_(_bids))
        if category:
            query = query.filter(UnitOfMeasure.category == category)
        
        products = query.order_by(UnitOfMeasure.category, Product.name).all()
        
        result = [
            {
                "product_id": p.id,
                "product_name": p.name,
                "sku": p.sku,
                "selling_price": float(p.selling_price) if p.selling_price else 0,
                "unit": f"{p.unit_name} ({p.unit_abbr})",
                "category": p.category,
                "subcategory": p.subcategory,
                "decimal_places": p.decimal_places
            }
            for p in products
        ]
        
        return {"success": True, "data": result, "total": len(result)}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/uom/precision-analysis")
async def get_precision_analysis(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Analyze precision requirements across products"""
    try:
        _prec_q = db.query(
            UnitOfMeasure.category,
            UnitOfMeasure.decimal_places,
            func.count(Product.id).label('product_count')
        ).join(
            Product, Product.unit_of_measure_id == UnitOfMeasure.id
        )
        if company_id:
            _bids = db.query(Branch.id).filter(Branch.company_id == company_id)
            _prec_q = _prec_q.filter(Product.branch_id.in_(_bids))
        precision_stats = _prec_q.filter(
            UnitOfMeasure.is_active == True
        ).group_by(
            UnitOfMeasure.category,
            UnitOfMeasure.decimal_places
        ).order_by(
            UnitOfMeasure.category,
            UnitOfMeasure.decimal_places
        ).all()
        
        result = [
            {
                "category": row.category,
                "decimal_places": row.decimal_places,
                "product_count": row.product_count
            }
            for row in precision_stats
        ]
        
        return {"success": True, "data": result}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/inventory/category-value")
async def get_inventory_value_by_category(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Get inventory value grouped by UOM category"""
    try:
        _cv_q = db.query(
            UnitOfMeasure.category,
            func.count(Product.id).label('product_count'),
            func.sum(func.coalesce(Product.quantity, 0) * func.coalesce(Product.cost_price, 0)).label('total_value'),
            func.avg(func.coalesce(Product.cost_price, 0)).label('avg_price')
        ).join(
            Product, Product.unit_of_measure_id == UnitOfMeasure.id
        )
        if company_id:
            _bids = db.query(Branch.id).filter(Branch.company_id == company_id)
            _cv_q = _cv_q.filter(Product.branch_id.in_(_bids))
        category_values = _cv_q.filter(
            UnitOfMeasure.is_active == True,
        ).group_by(
            UnitOfMeasure.category
        ).order_by(
            desc('total_value')
        ).all()
        
        result = [
            {
                "category": row.category,
                "product_count": row.product_count,
                "total_value": float(row.total_value or 0),
                "average_price": float(row.avg_price or 0)
            }
            for row in category_values
        ]
        
        return {"success": True, "data": result}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/uom/system-vs-custom")
async def get_system_vs_custom_analysis(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Compare system units vs custom units usage"""
    try:
        _prod_cond2 = None
        if company_id:
            _bids2 = db.query(Branch.id).filter(Branch.company_id == company_id).subquery()
            _prod_cond2 = Product.branch_id.in_(_bids2)
        analysis = db.query(
            UnitOfMeasure.is_system_unit,
            UnitOfMeasure.category,
            func.count(func.distinct(UnitOfMeasure.id)).label('unit_count'),
            func.count(Product.id).label('product_count')
        ).outerjoin(
            Product, and_(Product.unit_of_measure_id == UnitOfMeasure.id, _prod_cond2) if _prod_cond2 is not None else Product.unit_of_measure_id == UnitOfMeasure.id
        ).filter(
            UnitOfMeasure.is_active == True
        ).group_by(
            UnitOfMeasure.is_system_unit,
            UnitOfMeasure.category
        ).order_by(
            UnitOfMeasure.is_system_unit.desc(),
            UnitOfMeasure.category
        ).all()
        
        result = [
            {
                "unit_type": "System" if row.is_system_unit else "Custom",
                "category": row.category,
                "unit_count": row.unit_count,
                "product_count": row.product_count
            }
            for row in analysis
        ]
        
        return {"success": True, "data": result}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/trends/product-creation")
async def get_product_creation_trends(
    days: int = 30,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Analyze product creation trends by UOM category"""
    try:
        start_date = datetime.utcnow() - timedelta(days=days)

        _trend_q = db.query(Product)
        if company_id:
            _tbids = db.query(Branch.id).filter(Branch.company_id == company_id)
            _trend_q = _trend_q.filter(Product.branch_id.in_(_tbids))

        _trend_filter = [Product.created_at >= start_date]
        if company_id:
            _trend_filter.append(Product.branch_id.in_(_tbids))
        trends = db.query(
            func.date(Product.created_at).label('date'),
            UnitOfMeasure.category,
            func.count(Product.id).label('count')
        ).join(
            UnitOfMeasure, Product.unit_of_measure_id == UnitOfMeasure.id
        ).filter(
            *_trend_filter
        ).group_by(
            func.date(Product.created_at),
            UnitOfMeasure.category
        ).order_by(
            'date',
            UnitOfMeasure.category
        ).all()
        
        result = [
            {
                "date": row.date.isoformat() if row.date else None,
                "category": row.category,
                "product_count": row.count
            }
            for row in trends
        ]
        
        return {"success": True, "data": result, "period_days": days}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/executive-summary")
async def get_executive_summary(
    days: int = 30,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Real KPIs: revenue, growth, low stock, top category from live ERP data."""
    try:
        days = max(7, min(days, 365))
        branch_ids = _branch_ids(db, company_id)
        now = datetime.utcnow()
        start = now - timedelta(days=days)
        prev_start = start - timedelta(days=days)

        current_revenue = _sum_sales(db, branch_ids, start, now)
        previous_revenue = _sum_sales(db, branch_ids, prev_start, start)
        growth_rate = (
            round((current_revenue - previous_revenue) / previous_revenue * 100, 1)
            if previous_revenue > 0
            else (100.0 if current_revenue > 0 else 0.0)
        )
        avg_daily_sales = round(current_revenue / days, 2)

        low_q = db.query(func.count(Product.id)).filter(Product.active == True)
        low_q = _scope_products(low_q, branch_ids)
        low_q = low_q.filter(
            or_(
                and_(Product.reorder_point > 0, func.coalesce(Product.quantity, 0) <= Product.reorder_point),
                and_(
                    func.coalesce(Product.reorder_point, 0) <= 0,
                    Product.minimum_stock_level > 0,
                    func.coalesce(Product.quantity, 0) <= Product.minimum_stock_level,
                ),
            )
        )
        low_stock_count = int(low_q.scalar() or 0)

        top_cat_row = (
            db.query(
                func.coalesce(SaleItem.category, Product.category, UnitOfMeasure.category).label("cat"),
                func.sum(func.coalesce(SaleItem.total_amount, SaleItem.quantity * SaleItem.selling_price, 0)).label("amt"),
            )
            .join(Sale, SaleItem.sale_id == Sale.id)
            .join(Product, SaleItem.product_id == Product.id)
            .outerjoin(UnitOfMeasure, Product.unit_of_measure_id == UnitOfMeasure.id)
            .filter(_sale_ts() >= start, _sale_ts() < now)
        )
        if branch_ids is not None:
            top_cat_row = top_cat_row.filter(Sale.branch_id.in_(branch_ids))
        cat_col = func.coalesce(SaleItem.category, Product.category, UnitOfMeasure.category)
        top_cat_row = (
            top_cat_row.filter(~func.lower(Sale.status).in_(_SALE_EXCLUDE))
            .group_by(cat_col)
            .order_by(desc("amt"))
            .first()
        )
        top_category = (top_cat_row.cat if top_cat_row and top_cat_row.cat else None) or "—"

        purchase_q = db.query(func.coalesce(func.sum(Purchase.total_amount), 0)).filter(
            Purchase.purchase_date >= start.date(),
            Purchase.purchase_date <= now.date(),
        )
        if branch_ids is not None:
            purchase_q = purchase_q.filter(Purchase.branch_id.in_(branch_ids))
        purchase_total = float(purchase_q.scalar() or 0)
        profit_margin = (
            round((current_revenue - purchase_total) / current_revenue * 100, 1)
            if current_revenue > 0
            else 0.0
        )

        return {
            "success": True,
            "data": {
                "period_days": days,
                "growth_rate": growth_rate,
                "avg_daily_sales": avg_daily_sales,
                "total_revenue": round(current_revenue, 2),
                "purchase_total": round(purchase_total, 2),
                "profit_margin": profit_margin,
                "low_stock_count": low_stock_count,
                "top_category": top_category,
            },
        }
    except Exception as e:
        logger.exception("executive-summary failed")
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/sales-trend")
async def get_sales_trend(
    days: int = 30,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Daily sales revenue and purchase totals for trend charts."""
    try:
        days = max(7, min(days, 365))
        branch_ids = _branch_ids(db, company_id)
        start = datetime.utcnow() - timedelta(days=days)

        sales_rows = (
            db.query(func.date(_sale_ts()).label("d"), func.coalesce(func.sum(Sale.total_amount), 0).label("rev"))
            .filter(_sale_ts() >= start)
        )
        sales_rows = _scope_sales(sales_rows, branch_ids).group_by("d").order_by("d").all()

        purch_rows = db.query(
            Purchase.purchase_date.label("d"),
            func.coalesce(func.sum(Purchase.total_amount), 0).label("amt"),
        ).filter(Purchase.purchase_date >= start.date())
        if branch_ids is not None:
            purch_rows = purch_rows.filter(Purchase.branch_id.in_(branch_ids))
        purch_rows = purch_rows.group_by(Purchase.purchase_date).order_by(Purchase.purchase_date).all()

        sales_map = {str(r.d): float(r.rev) for r in sales_rows if r.d}
        purch_map = {str(r.d): float(r.amt) for r in purch_rows if r.d}

        labels, revenue, purchases = [], [], []
        for i in range(days):
            d = (start + timedelta(days=i + 1)).date()
            key = str(d)
            labels.append(d.strftime("%d %b"))
            revenue.append(round(sales_map.get(key, 0.0), 2))
            purchases.append(round(purch_map.get(key, 0.0), 2))

        return {"success": True, "data": {"labels": labels, "revenue": revenue, "purchases": purchases, "period_days": days}}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/inventory-alerts")
async def get_inventory_alerts(
    limit: int = 10,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Products at or below reorder / minimum stock for optimization chart."""
    try:
        branch_ids = _branch_ids(db, company_id)
        q = db.query(Product).filter(Product.active == True)
        q = _scope_products(q, branch_ids)
        q = q.filter(
            or_(
                and_(Product.reorder_point > 0, func.coalesce(Product.quantity, 0) <= Product.reorder_point),
                and_(
                    func.coalesce(Product.reorder_point, 0) <= 0,
                    Product.minimum_stock_level > 0,
                    func.coalesce(Product.quantity, 0) <= Product.minimum_stock_level,
                ),
            )
        ).order_by(func.coalesce(Product.quantity, 0)).limit(max(1, min(limit, 25)))

        items = []
        for p in q.all():
            qty = int(p.quantity or 0)
            reorder = int(p.reorder_point or p.minimum_stock_level or 0)
            optimal = int(p.maximum_stock_level or max(reorder * 2, reorder + 10, qty + 10))
            items.append({
                "name": p.name or p.sku or "Product",
                "sku": p.sku,
                "current_stock": qty,
                "reorder_point": reorder,
                "optimal_stock": optimal,
            })
        return {"success": True, "data": items}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/demand-forecast")
async def get_demand_forecast(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Simple moving-average forecast from historical daily unit sales (last 4 weeks)."""
    try:
        branch_ids = _branch_ids(db, company_id)
        start = datetime.utcnow() - timedelta(days=28)
        rows = (
            db.query(func.date(_sale_ts()).label("d"), func.coalesce(func.sum(SaleItem.quantity), 0).label("units"))
            .join(Sale, SaleItem.sale_id == Sale.id)
            .filter(_sale_ts() >= start)
        )
        if branch_ids is not None:
            rows = rows.filter(Sale.branch_id.in_(branch_ids))
        rows = rows.filter(~func.lower(Sale.status).in_(_SALE_EXCLUDE)).group_by("d").order_by("d").all()

        daily = [float(r.units) for r in rows]
        labels_hist = [f"W{(i // 7) + 1}D{(i % 7) + 1}" for i in range(len(daily))]
        while len(labels_hist) < 28:
            labels_hist.append(f"D{len(labels_hist) + 1}")
            daily.append(0.0)

        hist = daily[-28:]
        hist_labels = labels_hist[-28:] if len(labels_hist) >= 28 else [f"D{i+1}" for i in range(28)]
        avg7 = sum(hist[-7:]) / 7.0 if hist else 0.0
        forecast = [round(avg7 * (1 + 0.02 * i), 1) for i in range(8)]
        fc_labels = [f"F+{i+1}d" for i in range(8)]

        return {
            "success": True,
            "data": {
                "history_labels": hist_labels,
                "history_units": [round(x, 1) for x in hist],
                "forecast_labels": fc_labels,
                "forecast_units": forecast,
                "method": "7-day moving average (from live sales)",
            },
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/supply-chain")
async def get_supply_chain(
    days: int = 90,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Purchase order fulfilment and top suppliers from live data."""
    try:
        days = max(30, min(days, 365))
        branch_ids = _branch_ids(db, company_id)
        since = datetime.utcnow().date() - timedelta(days=days)

        po_q = db.query(PurchaseOrder).filter(PurchaseOrder.date >= since)
        if branch_ids is not None:
            po_q = po_q.filter(PurchaseOrder.branch_id.in_(branch_ids))
        pos = po_q.all()

        closed = {"received", "completed", "closed", "delivered", "fulfilled"}
        with_expected = [p for p in pos if p.expected_delivery_date]
        on_time = sum(
            1 for p in with_expected
            if (p.status or "").lower() in closed
            and p.date
            and p.expected_delivery_date
            and p.date <= p.expected_delivery_date
        )
        on_time_pct = round(on_time / len(with_expected) * 100, 1) if with_expected else None

        open_pos = [p for p in pos if (p.status or "").lower() not in closed]
        lead_days = []
        for p in open_pos:
            if p.date:
                lead_days.append((datetime.utcnow().date() - p.date).days)
        avg_lead = round(sum(lead_days) / len(lead_days), 1) if lead_days else None

        sup_q = (
            db.query(Supplier.name, func.count(Purchase.id).label("cnt"), func.coalesce(func.sum(Purchase.total_amount), 0).label("amt"))
            .join(Purchase, Purchase.supplier_id == Supplier.id)
            .filter(Purchase.purchase_date >= since)
        )
        if branch_ids is not None:
            sup_q = sup_q.filter(Purchase.branch_id.in_(branch_ids))
        suppliers = sup_q.group_by(Supplier.name).order_by(desc("amt")).limit(8).all()
        max_amt = max((float(s.amt) for s in suppliers), default=1.0) or 1.0
        supplier_perf = [
            {"name": s.name, "orders": int(s.cnt), "spend": float(s.amt), "score": round(float(s.amt) / max_amt * 100, 1)}
            for s in suppliers
        ]

        return {
            "success": True,
            "data": {
                "on_time_delivery_pct": on_time_pct,
                "avg_lead_time_days": avg_lead,
                "open_po_count": len(open_pos),
                "total_po_count": len(pos),
                "suppliers": supplier_perf,
            },
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/financial-monthly")
async def get_financial_monthly(
    months: int = 6,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Monthly revenue vs purchases for financial performance chart."""
    try:
        months = max(3, min(months, 24))
        branch_ids = _branch_ids(db, company_id)
        start = datetime.utcnow() - timedelta(days=months * 31)

        sales_q = db.query(_sale_ts(), Sale.total_amount).filter(_sale_ts() >= start)
        sales_q = _scope_sales(sales_q, branch_ids)
        rev_map: Dict[str, float] = {}
        for ts, amt in sales_q.all():
            if not ts:
                continue
            ym = ts.strftime("%Y-%m") if hasattr(ts, "strftime") else str(ts)[:7]
            rev_map[ym] = rev_map.get(ym, 0.0) + float(amt or 0)

        purch_q = db.query(Purchase.purchase_date, Purchase.total_amount).filter(
            Purchase.purchase_date >= start.date()
        )
        if branch_ids is not None:
            purch_q = purch_q.filter(Purchase.branch_id.in_(branch_ids))
        exp_map: Dict[str, float] = {}
        for d, amt in purch_q.all():
            if not d:
                continue
            ym = d.strftime("%Y-%m") if hasattr(d, "strftime") else str(d)[:7]
            exp_map[ym] = exp_map.get(ym, 0.0) + float(amt or 0)

        labels, revenue, expenses, profit = [], [], [], []
        cursor = datetime.utcnow().replace(day=1)
        for _ in range(months):
            ym = cursor.strftime("%Y-%m")
            labels.insert(0, cursor.strftime("%b %Y"))
            r = round(rev_map.get(ym, 0.0), 2)
            e = round(exp_map.get(ym, 0.0), 2)
            revenue.insert(0, r)
            expenses.insert(0, e)
            profit.insert(0, round(r - e, 2))
            if cursor.month == 1:
                cursor = cursor.replace(year=cursor.year - 1, month=12)
            else:
                cursor = cursor.replace(month=cursor.month - 1)

        return {"success": True, "data": {"labels": labels, "revenue": revenue, "expenses": expenses, "profit": profit}}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/insights")
async def get_analytics_insights(
    days: int = 30,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Rule-based recommendations from live ERP metrics (not placeholder text)."""
    try:
        summary = (await get_executive_summary(days=days, db=db, company_id=company_id))["data"]
        alerts = (await get_inventory_alerts(limit=5, db=db, company_id=company_id))["data"]
        insights: List[Dict[str, str]] = []

        if summary["low_stock_count"] > 0:
            names = ", ".join(a["name"] for a in alerts[:3])
            insights.append({
                "level": "warning",
                "title": "Reorder needed",
                "message": f"{summary['low_stock_count']} product(s) at or below reorder level."
                + (f" Includes: {names}." if names else ""),
            })
        if summary["growth_rate"] > 5:
            insights.append({
                "level": "success",
                "title": "Sales growth",
                "message": f"Revenue up {summary['growth_rate']}% vs the previous {days} days.",
            })
        elif summary["growth_rate"] < -5:
            insights.append({
                "level": "warning",
                "title": "Sales decline",
                "message": f"Revenue down {abs(summary['growth_rate'])}% vs the previous {days} days — review pricing and promotions.",
            })
        if summary["total_revenue"] == 0:
            insights.append({
                "level": "info",
                "title": "No sales yet",
                "message": "No completed sales in this period. POS and invoice data will appear here automatically.",
            })
        if summary["top_category"] and summary["top_category"] != "—":
            insights.append({
                "level": "info",
                "title": "Top category",
                "message": f"Highest sales value category in the last {days} days: {summary['top_category']}.",
            })
        if summary["profit_margin"] < 10 and summary["total_revenue"] > 0:
            insights.append({
                "level": "primary",
                "title": "Margin watch",
                "message": f"Estimated gross margin is {summary['profit_margin']}% (sales minus purchases). Review COGS and supplier costs.",
            })
        if not insights:
            insights.append({
                "level": "info",
                "title": "All clear",
                "message": "No critical alerts. Metrics are based on your live ERP transactions.",
            })
        return {"success": True, "data": insights}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/analytics/data-status")
async def get_data_status(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Live record counts and freshness — replaces placeholder warehousing stats."""
    try:
        branch_ids = _branch_ids(db, company_id)

        def _count(model, branch_col="branch_id"):
            q = db.query(func.count(model.id))
            if branch_ids is not None and hasattr(model, branch_col):
                q = q.filter(getattr(model, branch_col).in_(branch_ids))
            return int(q.scalar() or 0)

        last_sale = (
            db.query(func.max(_sale_ts()))
            .filter(~func.lower(Sale.status).in_(_SALE_EXCLUDE))
        )
        if branch_ids is not None:
            last_sale = last_sale.filter(Sale.branch_id.in_(branch_ids))
        last_sale_at = last_sale.scalar()

        total_records = (
            _count(Product) + _count(Sale) + _count(Purchase) + _count(Customer) + _count(JournalEntry)
        )

        products_with_uom = db.query(func.count(Product.id)).filter(Product.unit_of_measure_id.isnot(None))
        if branch_ids is not None:
            products_with_uom = products_with_uom.filter(Product.branch_id.in_(branch_ids))
        pw = int(products_with_uom.scalar() or 0)
        pt = max(_count(Product), 1)
        data_quality = round(pw / pt * 100, 1)

        return {
            "success": True,
            "data": {
                "sync_status": "live",
                "records_processed": total_records,
                "last_update": last_sale_at.isoformat() if last_sale_at else None,
                "data_quality_pct": data_quality,
                "counts": {
                    "products": _count(Product),
                    "sales": _count(Sale),
                    "purchases": _count(Purchase),
                    "customers": _count(Customer),
                    "journal_entries": _count(JournalEntry),
                },
            },
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))
