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 Client, Employee, Enquiry, Event, EventProduct, Expense, Invoice, Payment, StaffAllocation, User from app.utils import event_profitability, money router = APIRouter(prefix="/reports", tags=["reports"]) @router.get("/sales") def sales_report(db: Session = Depends(get_db), _: User = Depends(require_permission("report.sales"))): rows = [] for event in db.query(Event).all(): client = db.query(Client).filter(Client.id == event.client_id).first() enquiry = db.query(Enquiry).filter(Enquiry.id == event.enquiry_id).first() if event.enquiry_id else None profit = event_profitability(db, event.id) conversion = None if enquiry and enquiry.created_at: conversion = (event.created_at.date() - enquiry.created_at.date()).days rows.append( { "event_id": event.public_id, "event_name": event.name, "event_type": event.event_type, "client_id": client.public_id if client else None, "client_name": client.name if client else None, "client_phone": client.phone if client else None, "client_email": client.email if client else None, "event_date": event.event_date, "district": event.district, "city": event.city, "venue_name": event.venue_name, "final_billed_amount": profit["final_billed_amount"], "advance_received": profit["amount_paid"], "balance_amount": profit["balance_amount"], "travel_expense": profit["travel_expense"], "salary": profit["staff_cost"], "other_expenses": profit["other_expenses"], "total_event_cost": profit["total_event_cost"], "net_profit": profit["net_profit"], "profit_margin": profit["profit_margin"], "event_status": event.status, "lead_source": event.lead_source, "booking_channel": event.booking_channel, "conversion_time_days": conversion, } ) return rows @router.get("/products") def product_report(db: Session = Depends(get_db), _: User = Depends(require_permission("report.product"))): rows = [] for item in db.query(EventProduct).all(): event = db.query(Event).filter(Event.id == item.event_id).first() profit = money(item.amount) - money(item.cost) billed = money(item.amount) rows.append( { "product_name": item.name, "event_id": event.public_id if event else None, "event_date": event.event_date if event else None, "quantity": item.quantity, "quoted_amount": item.unit_price, "discount_amount": item.discount, "final_amount": item.amount, "product_cost": item.cost, "net_profit": profit, "profit_margin": money((profit / billed) * 100) if billed else 0, "booking_status": event.status if event else None, "lead_source": event.lead_source if event else None, } ) return rows @router.get("/employees") def employee_report(db: Session = Depends(get_db), _: User = Depends(require_permission("report.employee"))): rows = [] for allocation in db.query(StaffAllocation).all(): employee = db.query(Employee).filter(Employee.id == allocation.employee_id).first() event = db.query(Event).filter(Event.id == allocation.event_id).first() rows.append( { "employee_id": employee.public_id if employee else None, "employee_name": employee.name if employee else None, "role": employee.role if employee else None, "department": employee.department if employee else None, "event_id": event.public_id if event else None, "event_date": event.event_date if event else None, "assigned_role": allocation.assigned_role, "allocation_status": allocation.status, "event_payment": allocation.event_payment, "travel_allowance": allocation.travel_allowance, "other_allowance": allocation.other_allowance, "total_staff_cost": allocation.total_staff_cost, "tasks_completed": allocation.tasks_completed, "performance_rating": allocation.performance_rating, "manager_feedback": allocation.manager_feedback, } ) return rows @router.get("/clients") def client_report(db: Session = Depends(get_db), _: User = Depends(require_permission("report.client"))): from app.routers.crm import _client_stats rows = [] for client in db.query(Client).all(): stats = _client_stats(db, client.id) rows.append( { "client_id": client.public_id, "client_name": client.name, "company_name": client.company_name, "client_type": client.client_type, "phone": client.phone, "email": client.email, "district": client.district, "city": client.city, "lead_source": client.lead_source, "booking_channel": client.booking_channel, "first_enquiry_date": client.first_enquiry_date, "last_enquiry_date": client.last_enquiry_date, "status": client.status, **stats, } ) return rows @router.get("/expenses") def expense_report(db: Session = Depends(get_db), _: User = Depends(require_permission("report.finance"))): return [ { "expense_id": row.public_id, "event_id": row.event_id, "category": row.category, "description": row.description, "amount": row.amount, "expense_date": row.expense_date, "payment_mode": row.payment_mode, "approval_status": row.approval_status, } for row in db.query(Expense).all() ] @router.get("/payments") def payment_report(db: Session = Depends(get_db), _: User = Depends(require_permission("report.finance"))): return [ { "payment_id": row.public_id, "invoice_id": row.invoice_id, "event_id": row.event_id, "client_id": row.client_id, "payment_date": row.payment_date, "amount": row.amount, "payment_mode": row.payment_mode, "transaction_reference": row.transaction_reference, } for row in db.query(Payment).all() ] @router.get("/profit-loss") def profit_loss(db: Session = Depends(get_db), _: User = Depends(require_permission("report.finance"))): revenue = money(0) cost = money(0) for event in db.query(Event).all(): profit = event_profitability(db, event.id) revenue += money(profit["final_billed_amount"]) cost += money(profit["total_event_cost"]) invoices = db.query(Invoice).filter(Invoice.status != "Cancelled").all() paid = sum((money(inv.amount_paid) for inv in invoices), money(0)) return { "total_revenue": revenue, "total_cost": cost, "net_profit": money(revenue - cost), "profit_margin": money(((revenue - cost) / revenue) * 100) if revenue else 0, "amount_paid": paid, "outstanding": money(revenue - paid), }