from datetime import date from calendar import monthrange from fastapi import APIRouter, Depends from sqlalchemy.orm import Session from app.database import get_db from app.deps import require_permission from app.models import Enquiry, EnquiryFollowup, Event, Expense, Invoice, Payment, StaffAllocation, User from app.utils import money router = APIRouter(tags=["dashboard"]) def _month_bounds(year: int, month: int) -> tuple[date, date]: last_day = monthrange(year, month)[1] return date(year, month, 1), date(year, month, last_day) def _shift_month(year: int, month: int, delta: int) -> tuple[int, int]: total = year * 12 + (month - 1) + delta return total // 12, total % 12 + 1 def _conversion_for_event_month(enquiries: list[Enquiry], year: int, month: int) -> dict: start, end = _month_bounds(year, month) month_enquiries = [ e for e in enquiries if e.event_date is not None and start <= e.event_date <= end ] confirmed = [e for e in month_enquiries if e.status == "Confirmed"] total = len(month_enquiries) rate = round((len(confirmed) / total * 100), 2) if total else 0 return { "year": year, "month": month, "total_enquiries": total, "confirmed_enquiries": len(confirmed), "lead_conversion_rate": rate, } def _finance_for_month(invoices: list[Invoice], expenses: list[Expense], year: int, month: int) -> dict: month_invoices = [ inv for inv in invoices if inv.invoice_date and inv.invoice_date.year == year and inv.invoice_date.month == month ] month_expenses = [ exp for exp in expenses if exp.expense_date.year == year and exp.expense_date.month == month ] revenue = sum((money(inv.final_amount) for inv in month_invoices), money(0)) expense_total = sum((money(exp.amount) for exp in month_expenses), money(0)) return { "revenue": revenue, "expenses": expense_total, "profit": money(revenue - expense_total), } def _last_three_months(year: int, month: int) -> list[tuple[int, int]]: # Newest first: current, previous, previous-1 return [_shift_month(year, month, -offset) for offset in range(3)] def _safe_outstanding(inv: Invoice): # Recompute from totals to avoid stale/negative stored balances. balance = money(money(inv.final_amount) - money(inv.amount_paid)) return money(0) if balance < 0 else balance @router.get("/dashboard") def dashboard(db: Session = Depends(get_db), _: User = Depends(require_permission("dashboard.view"))): today = date.today() month = today.month year = today.year enquiries = db.query(Enquiry).all() events = db.query(Event).all() invoices = db.query(Invoice).filter(Invoice.status != "Cancelled").all() expenses = db.query(Expense).all() revenue = sum((money(inv.final_amount) for inv in invoices), money(0)) expense_total = sum((money(exp.amount) for exp in expenses), money(0)) outstanding = sum((_safe_outstanding(inv) for inv in invoices), money(0)) months_data = [] for y, m in _last_three_months(year, month): conversion = _conversion_for_event_month(enquiries, y, m) finance = _finance_for_month(invoices, expenses, y, m) months_data.append( { "year": y, "month": m, "label": date(y, m, 1).strftime("%b %Y"), "is_current": y == year and m == month, "total_enquiries": conversion["total_enquiries"], "confirmed_enquiries": conversion["confirmed_enquiries"], "lead_conversion_rate": conversion["lead_conversion_rate"], "revenue": finance["revenue"], "expenses": finance["expenses"], "profit": finance["profit"], } ) current_month = months_data[0] enquiry_by_id = {e.id: e for e in enquiries} pending_followups = ( db.query(EnquiryFollowup) .filter(EnquiryFollowup.status == "Pending") .order_by(EnquiryFollowup.followup_date.asc(), EnquiryFollowup.id.asc()) .all() ) return { "kpis": { "total_enquiries": len(enquiries), "new_enquiries": len([e for e in enquiries if e.status == "New"]), "confirmed_events": len([e for e in events if e.status in {"Confirmed", "Upcoming", "In Progress"}]), "completed_events": len([e for e in events if e.status == "Completed"]), "total_revenue": revenue, "total_expenses": expense_total, "net_profit": money(revenue - expense_total), "outstanding_payments": outstanding, }, "today_events": [ {"id": e.id, "public_id": e.public_id, "name": e.name, "venue_name": e.venue_name, "status": e.status} for e in events if e.event_date == today ], "upcoming_events": [ {"id": e.id, "public_id": e.public_id, "name": e.name, "event_date": e.event_date, "venue_name": e.venue_name, "status": e.status} for e in sorted(events, key=lambda item: item.event_date) if e.event_date >= today and e.status != "Cancelled" ][:8], "new_enquiries": [ { "id": e.id, "public_id": e.public_id, "client_id": e.client_id, "client_name": e.client_name, "phone": e.phone, "email": e.email, "event_type": e.event_type, "event_date": e.event_date, "location": e.location, "district": e.district, "city": e.city, "venue": e.venue, "requirements": e.requirements, "expected_budget": e.expected_budget, "lead_source": e.lead_source, "booking_channel": e.booking_channel, "status": e.status, } for e in enquiries if e.status == "New" ], "followups_due": [ { "id": f.id, "enquiry_id": f.enquiry_id, "followup_date": f.followup_date, "followup_time": f.followup_time, "followup_type": f.followup_type, "status": f.status, "notes": f.notes, "client_name": enquiry_by_id[f.enquiry_id].client_name if f.enquiry_id in enquiry_by_id else None, "enquiry_public_id": enquiry_by_id[f.enquiry_id].public_id if f.enquiry_id in enquiry_by_id else None, } for f in pending_followups ], "pending_enquiries": [ { "id": e.id, "public_id": e.public_id, "client_name": e.client_name, "phone": e.phone, "event_type": e.event_type, "event_date": e.event_date, "city": e.city, "status": e.status, } for e in enquiries if e.status in {"New", "Contacted", "Follow-up", "Quotation Sent", "Negotiation"} ], "pending_payments": [ { "id": inv.id, "invoice_number": inv.invoice_number, "balance_amount": _safe_outstanding(inv), "final_amount": inv.final_amount, "status": inv.status, "due_date": inv.due_date, } for inv in invoices if _safe_outstanding(inv) > 0 ], "recent_invoices": [ {"id": inv.id, "invoice_number": inv.invoice_number, "final_amount": inv.final_amount, "status": inv.status} for inv in invoices[-5:][::-1] ], "recent_expenses": [ {"id": exp.id, "public_id": exp.public_id, "category": exp.category, "amount": exp.amount} for exp in expenses[-5:][::-1] ], "staff_allocation": [ {"id": a.id, "event_id": a.event_id, "employee_id": a.employee_id, "assigned_role": a.assigned_role, "status": a.status} for a in db.query(StaffAllocation).order_by(StaffAllocation.id.desc()).limit(8) ], "monthly": { "revenue": current_month["revenue"], "expenses": current_month["expenses"], "profit": current_month["profit"], "lead_conversion_rate": current_month["lead_conversion_rate"], "months": months_data, }, }