from fastapi import APIRouter, Depends from sqlalchemy.orm import Session from sqlalchemy import text from app.core.database.db_session import get_db from app.core.permissions.RoleChecker import get_current_user from typing import Any, Dict import datetime router = APIRouter(prefix="/api/v1/dashboard", tags=["Dashboard"]) @router.get("/stats", response_model=Dict[str, Any]) def get_dashboard_stats( db: Session = Depends(get_db), current_user: Any = Depends(get_current_user), ): """ Returns real-time KPI metrics for the admin dashboard: - Catalog counts (users, orders, products, brands, models, categories) - Revenue totals (all time, last 30 days, last 7 days) - Order status breakdown - Revenue by day for chart (last 30 days) - Top products by revenue - Recent orders """ # ── Core Catalog Counts ────────────────────────────────────────────────── total_users = db.execute(text("SELECT COUNT(*) FROM ecom_customers")).scalar() or 0 total_orders = db.execute(text("SELECT COUNT(*) FROM orders")).scalar() or 0 total_products = db.execute(text("SELECT COUNT(*) FROM products")).scalar() or 0 total_brands = db.execute(text("SELECT COUNT(*) FROM brands")).scalar() or 0 total_device_models = db.execute(text("SELECT COUNT(*) FROM device_models")).scalar() or 0 total_categories = db.execute(text("SELECT COUNT(*) FROM categories")).scalar() or 0 total_device_series = db.execute(text("SELECT COUNT(*) FROM device_series")).scalar() or 0 # ── Revenue ────────────────────────────────────────────────────────────── revenue_all = db.execute(text( "SELECT COALESCE(SUM(final_amount), 0) FROM orders WHERE status != 'cancelled'" )).scalar() or 0 revenue_30d = db.execute(text( "SELECT COALESCE(SUM(final_amount), 0) FROM orders " "WHERE status != 'cancelled' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" )).scalar() or 0 revenue_7d = db.execute(text( "SELECT COALESCE(SUM(final_amount), 0) FROM orders " "WHERE status != 'cancelled' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" )).scalar() or 0 # ── Order Status Breakdown ─────────────────────────────────────────────── order_statuses = db.execute(text( "SELECT status, COUNT(*) as cnt FROM orders GROUP BY status" )).fetchall() orders_by_status = {row[0]: row[1] for row in order_statuses} # ── Revenue by Day (last 30 days) for chart ────────────────────────────── daily_revenue_rows = db.execute(text(""" SELECT DATE(created_at) AS day, COALESCE(SUM(final_amount), 0) AS revenue, COUNT(*) AS order_count FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND status != 'cancelled' GROUP BY DATE(created_at) ORDER BY day ASC """)).fetchall() # Build a complete 30-day series filling zeros for missing days today = datetime.date.today() day_map = {row[0]: {"revenue": float(row[1]), "orders": int(row[2])} for row in daily_revenue_rows} revenue_chart = [] for i in range(29, -1, -1): d = today - datetime.timedelta(days=i) revenue_chart.append({ "date": d.strftime("%d %b"), "revenue": day_map.get(d, {}).get("revenue", 0), "orders": day_map.get(d, {}).get("orders", 0), }) # ── Revenue by Month (last 12 months) for chart ────────────────────────── monthly_revenue_rows = db.execute(text(""" SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, DATE_FORMAT(created_at, '%b %Y') AS label, COALESCE(SUM(final_amount), 0) AS revenue, COUNT(*) AS order_count FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH) AND status != 'cancelled' GROUP BY DATE_FORMAT(created_at, '%Y-%m'), DATE_FORMAT(created_at, '%b %Y') ORDER BY month ASC """)).fetchall() revenue_monthly_chart = [ { "label": row[1], "revenue": float(row[2]), "orders": int(row[3]), } for row in monthly_revenue_rows ] # ── Top Products by Revenue ────────────────────────────────────────────── top_products = db.execute(text(""" SELECT oi.product_name, SUM(oi.quantity) AS total_sold, SUM(oi.total_price) AS total_revenue FROM order_items oi INNER JOIN orders o ON o.order_id = oi.order_id WHERE o.status != 'cancelled' GROUP BY oi.product_name ORDER BY total_revenue DESC LIMIT 5 """)).fetchall() top_products_list = [ { "name": row[0], "total_sold": int(row[1]), "total_revenue": float(row[2]), } for row in top_products ] # ── Recent Orders ──────────────────────────────────────────────────────── recent_orders = db.execute(text(""" SELECT o.order_no, o.final_amount, o.status, o.payment_status, o.created_at, c.first_name, c.last_name, c.email FROM orders o LEFT JOIN ecom_customers c ON c.customer_id = o.customer_id ORDER BY o.created_at DESC LIMIT 5 """)).fetchall() recent_orders_list = [ { "order_no": row[0], "amount": float(row[1]), "status": row[2], "payment_status": row[3], "created_at": row[4].isoformat() if row[4] else None, "customer_name": f"{row[5] or ''} {row[6] or ''}".strip() or row[7] or "Guest", "customer_email": row[7], } for row in recent_orders ] # ── Inventory Summary ──────────────────────────────────────────────────── low_stock_count = db.execute(text(""" SELECT COUNT(*) FROM product_variants pv LEFT JOIN ( SELECT variant_id, SUM(qty) as stock FROM inventory_ledger GROUP BY variant_id ) l ON pv.variant_id = l.variant_id WHERE COALESCE(l.stock, 0) <= pv.low_stock_threshold AND COALESCE(l.stock, 0) >= 0 """)).scalar() or 0 out_of_stock_count = db.execute(text(""" SELECT COUNT(*) FROM product_variants pv LEFT JOIN ( SELECT variant_id, SUM(qty) as stock FROM inventory_ledger GROUP BY variant_id ) l ON pv.variant_id = l.variant_id WHERE COALESCE(l.stock, 0) = 0 """)).scalar() or 0 total_variant_stock = db.execute(text(""" SELECT COALESCE(SUM(qty), 0) FROM inventory_ledger """)).scalar() or 0 return { # Counts "total_users": total_users, "total_orders": total_orders, "total_products": total_products, "total_brands": total_brands, "total_device_models": total_device_models, "total_device_series": total_device_series, "total_categories": total_categories, # Revenue "revenue_all_time": float(revenue_all), "revenue_last_30_days": float(revenue_30d), "revenue_last_7_days": float(revenue_7d), # Orders breakdown "orders_by_status": orders_by_status, # Charts "revenue_chart_daily": revenue_chart, "revenue_chart_monthly": revenue_monthly_chart, # Lists "top_products": top_products_list, "recent_orders": recent_orders_list, # Inventory "low_stock_variants": low_stock_count, "out_of_stock_variants": out_of_stock_count, "total_stock_units": int(total_variant_stock), }