""" API endpoints for fetching invoice data and statistics. """ from fastapi import APIRouter, Depends, HTTPException, Query from sqlalchemy.orm import Session from sqlalchemy import func, extract from datetime import datetime, date from typing import Dict, Optional import time from app.database import get_db from app.models import Invoice, SyncMetadata from app.routers.auth import get_current_user, get_user_permissions from app.models import User from app.schemas import BranchDataResponse, BranchMarketingStats, DepartmentStats, OverviewResponse, SyncStatusResponse, SyncStatusItem from app.config import settings from app.config.xero_config import is_xero_enabled from app.config.config_storage import get_config_storage from app.services.sheets_service import sheets_service from app.services.kpi_sheets_service import kpi_sheets_service from app.services.time_utilisation_service import load_time_utilisation_for_branch from app.services.avg_time_to_invoice_service import get_avg_time_to_invoice_for_branch from app.services.job_efficiency_service import load_job_efficiency_for_branch from app.services.wip_jobs_service import get_wip_jobs_for_branch from app.services.simpro_job_stats_service import get_job_stats_for_company from app.services.simpro_quotes_service import get_quote_data_for_branch from app.services.google_reviews_service import get_google_review_counts_for_month import logging logger = logging.getLogger(__name__) # One computation per (year, month) per process (overview calls branch data N times). _google_review_totals_cache: Optional[tuple] = None # ((year, month), Dict[str, int]) def _get_google_review_totals_by_branch(year: int, month: int) -> Dict[str, int]: global _google_review_totals_cache key = (year, month) if _google_review_totals_cache and _google_review_totals_cache[0] == key: return _google_review_totals_cache[1] try: raw = get_google_review_counts_for_month(year, month) out = {b: int(d.get("total") or 0) for b, d in raw.items()} except Exception as e: logger.warning("Could not load Google review totals for marketing: %s", e) out = {} _google_review_totals_cache = (key, out) return out try: from app.services.xero_service import xero_service XERO_AVAILABLE = True except ImportError as e: logger.warning("Xero service not available. Xero integration disabled.") XERO_AVAILABLE = False xero_service = None router = APIRouter(prefix="/api", tags=["invoices"]) def _parse_date(value: Optional[str]) -> Optional[date]: """Parse YYYY-MM-DD string to date. Returns None if invalid.""" if not value: return None try: return datetime.strptime(value, "%Y-%m-%d").date() except ValueError: return None @router.get("/branches/{branch_id}/data", response_model=BranchDataResponse) async def get_branch_data( branch_id: str, month: int = Query(..., ge=1, le=12), year: int = Query(..., ge=2000), from_date: Optional[str] = Query(None, description="Start date YYYY-MM-DD (optional, for date range)"), to_date: Optional[str] = Query(None, description="End date YYYY-MM-DD (optional, for date range)"), db: Session = Depends(get_db), user: User = Depends(get_current_user), ): """ Get invoice data and statistics for a specific branch. When from_date and to_date are both provided, revenue and GP violations use that date range. Otherwise uses the full month from month/year. Uses live Xero data for branches with Xero enabled (currently Bunbury). Other branches will return zero until Xero is configured for them. Args: branch_id: Branch identifier (branch1, branch2, branch3) month: Month number (1-12) year: Year from_date: Optional start date (YYYY-MM-DD) for date range to_date: Optional end date (YYYY-MM-DD) for date range db: Database session Returns: Branch data with department statistics """ if branch_id not in settings.BRANCHES: raise ValueError(f"Invalid branch_id: {branch_id}") use_date_range = False from_dt = _parse_date(from_date) if from_date else None to_dt = _parse_date(to_date) if to_date else None if from_dt and to_dt and from_dt <= to_dt: use_date_range = True branch_config = settings.BRANCHES[branch_id] departments = [] # Check if Xero is enabled for this branch use_xero = (XERO_AVAILABLE and is_xero_enabled(branch_id)) # Get GP margin for the branch (if Xero enabled) branch_gp_margin = None gp_metrics_by_company = {} if use_xero: try: if use_date_range: gp_metrics_by_company = xero_service.get_business_group_gp_metrics_for_date_range(from_dt, to_dt, branch_id) else: gp_metrics_by_company = xero_service.get_business_group_gp_metrics(year, month, branch_id) if gp_metrics_by_company: total_gp = sum((m.get("gross_profit", 0.0) or 0.0) for m in gp_metrics_by_company.values()) total_revenue = sum((m.get("total_revenue", 0.0) or 0.0) for m in gp_metrics_by_company.values()) if total_revenue > 0: branch_gp_margin = (total_gp / total_revenue) * 100 except Exception as e: logger.warning(f"⚠️ Could not fetch GP margin for {branch_id}: {e}") # Track total GP violations for the branch total_gp_violations = 0 if use_xero: period_label = f"{from_dt} to {to_dt}" if use_date_range else f"{month}/{year}" logger.info(f"🔄 Using live Xero API for {branch_config['name']} ({period_label})") try: if use_date_range: xero_revenue = xero_service.get_revenue_for_date_range_by_company(from_dt, to_dt, branch_id) else: xero_revenue = xero_service.get_revenue_for_month(year, month, branch_id) # Map Xero revenue to departments for company_id, company_info in branch_config["companies"].items(): total_revenue = xero_revenue.get(company_id, 0.0) # Don't show negative revenue - display as $0 if below 0 total_revenue = max(0, total_revenue) logger.info(f"📊 Xero {branch_id} - {company_info['name']}: ${total_revenue:.2f} for {month}/{year}") # Get GP metrics (margin and dollars) and color for this department gp_color = None dept_gp_margin = None dept_gross_profit = None if gp_metrics_by_company: gp_metrics = gp_metrics_by_company.get(company_id, {}) dept_gp_margin = gp_metrics.get("gross_profit_margin") raw_gp = gp_metrics.get("gross_profit") dept_gross_profit = float(raw_gp) if raw_gp is not None else None if dept_gp_margin is not None: storage = get_config_storage() gp_color = storage.get_gp_color(company_info["name"], dept_gp_margin) # Fetch GP violations from Google Sheets (each branch+dept has its own tab) if "Air" in company_info["name"]: dept_type = "Air" elif "Residential" in company_info["name"] or "Electrical" in company_info["name"]: dept_type = "Electrics" elif "Commercial" in company_info["name"]: dept_type = "Commercial" elif "Solar" in company_info["name"]: dept_type = "Solar" else: dept_type = None gp_violations = 0 if dept_type: try: if use_date_range: gp_violations = sheets_service.get_gp_violations_for_date_range( from_date=from_dt, to_date=to_dt, branch=branch_config["name"], department=dept_type ) else: gp_violations = sheets_service.get_gp_violations_for_month( year=year, month=month, branch=branch_config["name"], department=dept_type ) total_gp_violations += gp_violations except Exception as e: logger.warning(f"⚠️ Could not fetch GP violations for {branch_config['name']} {dept_type}: {e}") departments.append(DepartmentStats( name=company_info["name"], current=total_revenue, breakeven=company_info["breakeven"], target=company_info["target"], gross_profit_margin=dept_gp_margin, gross_profit=dept_gross_profit, gp_color=gp_color, gp_violations=gp_violations )) except Exception as e: logger.error(f"❌ Error fetching Xero data for {branch_id}: {e}", exc_info=True) # For Xero-enabled branches, return zero if Xero fails (no fallback to CSV) # This ensures we know when Xero is having issues for company_id, company_info in branch_config["companies"].items(): departments.append(DepartmentStats( name=company_info["name"], current=0.0, breakeven=company_info["breakeven"], target=company_info["target"], gross_profit_margin=None, gross_profit=None )) else: # Branches without Xero enabled return zero # CSV/database import is retired - all data comes from Xero logger.info(f"📊 {branch_config['name']} - Xero not enabled, returning zero values") for company_id, company_info in branch_config["companies"].items(): departments.append(DepartmentStats( name=company_info["name"], current=0.0, breakeven=company_info["breakeven"], target=company_info["target"] )) # Marketing numbers from KPI Seeder "Marketing Numbers" sheet (by month/year) marketing = None try: marketing_by_location = kpi_sheets_service.get_marketing_numbers(year, month) raw = marketing_by_location.get(branch_config["name"]) if raw: marketing = BranchMarketingStats( google_cost=raw.get("google_cost", 0.0), google_clicks=int(raw.get("google_clicks", 0)), google_leads=int(raw.get("google_leads", 0)), google_cost_per_lead=float(raw.get("google_cost_per_lead", 0.0)), meta_cost=raw.get("meta_cost", 0.0), meta_clicks=int(raw.get("meta_clicks", 0)), meta_leads=int(raw.get("meta_leads", 0)), meta_cost_per_lead=float(raw.get("meta_cost_per_lead", 0.0)), total_quote_value=raw.get("total_quote_value", 0.0), total_sales_value=raw.get("total_sales_value", 0.0), ) except Exception as e: logger.warning("Could not fetch marketing numbers for %s: %s", branch_id, e) google_review_totals = _get_google_review_totals_by_branch(year, month) return BranchDataResponse( id=branch_id, name=branch_config["name"], departments=departments, gross_profit_margin=branch_gp_margin, gp_violations_total=total_gp_violations, marketing=marketing, google_review_totals_by_branch=google_review_totals, ) @router.get("/overview", response_model=OverviewResponse) async def get_overview( month: int = Query(..., ge=1, le=12), year: int = Query(..., ge=2000), from_date: Optional[str] = Query(None, description="Start date YYYY-MM-DD (optional)"), to_date: Optional[str] = Query(None, description="End date YYYY-MM-DD (optional)"), db: Session = Depends(get_db), user: User = Depends(get_current_user), ): """ Get overview data across all branches. When from_date and to_date are both provided, revenue and GP violations use that date range. """ branches = [] total_revenue = 0.0 total_gp = 0.0 google_review_totals_by_branch = _get_google_review_totals_by_branch(year, month) for branch_id in settings.BRANCHES.keys(): try: branch_data = await get_branch_data( branch_id, month, year, from_date=from_date, to_date=to_date, db=db, user=user, ) except Exception as e: # One failing branch (e.g. Xero error for Busselton) should not break the whole overview branch_config = settings.BRANCHES[branch_id] logger.exception( "Overview: failed to load branch %s (%s), returning zeros for that branch: %s", branch_id, branch_config.get("name", branch_id), e, ) branch_data = BranchDataResponse( id=branch_id, name=branch_config["name"], departments=[ DepartmentStats( name=company_info["name"], current=0.0, breakeven=company_info["breakeven"], target=company_info["target"], ) for company_info in branch_config["companies"].values() ], gross_profit_margin=None, gp_violations_total=0, google_review_totals_by_branch=google_review_totals_by_branch, ) branches.append(branch_data) # Calculate total revenue and GP for overall margin branch_revenue = sum(dept.current for dept in branch_data.departments) total_revenue += branch_revenue if branch_data.gross_profit_margin is not None and branch_revenue > 0: # Calculate GP from margin: GP = Revenue * (GP% / 100) branch_gp = branch_revenue * (branch_data.gross_profit_margin / 100) total_gp += branch_gp # Calculate overall GP margin and color (using Electrical thresholds for company-wide) overall_gp_margin = None overall_gp_color = None if total_revenue > 0: overall_gp_margin = (total_gp / total_revenue) * 100 try: storage = get_config_storage() overall_gp_color = storage.get_gp_color("Electrical", overall_gp_margin) except Exception as e: logger.warning(f"Could not compute overall GP color: {e}") return OverviewResponse( branches=branches, month=month, year=year, gross_profit_margin=overall_gp_margin, overall_gp_color=overall_gp_color ) # Cache for gp-violations: (month, year) or (from_date, to_date) -> (timestamp, response) _gp_violations_cache: dict = {} _GP_VIOLATIONS_CACHE_TTL = 55 # seconds - return cached if requested within 55s @router.get("/gp-violations") async def get_gp_violations( month: int = Query(..., ge=1, le=12), year: int = Query(..., ge=2000), from_date: Optional[str] = Query(None, description="Start date YYYY-MM-DD (optional)"), to_date: Optional[str] = Query(None, description="End date YYYY-MM-DD (optional)"), user: User = Depends(get_current_user), ): """ Get GP violation counts from Google Sheets only (no Xero). When from_date and to_date are both provided, violations are filtered by that date range. """ now = time.time() use_date_range = False from_dt = _parse_date(from_date) if from_date else None to_dt = _parse_date(to_date) if to_date else None if from_dt and to_dt and from_dt <= to_dt: use_date_range = True cache_key = (f"dr:{from_date}:{to_date}" if use_date_range else (month, year)) if cache_key in _gp_violations_cache: cached_at, cached_resp = _gp_violations_cache[cache_key] if now - cached_at < _GP_VIOLATIONS_CACHE_TTL: return cached_resp branches = [] company_total = 0 for branch_id in settings.BRANCHES.keys(): branch_config = settings.BRANCHES[branch_id] total_gp_violations = 0 dept_violations = [] for company_id, company_info in branch_config["companies"].items(): if "Air" in company_info["name"]: dept_type = "Air" elif "Residential" in company_info["name"] or "Electrical" in company_info["name"]: dept_type = "Electrics" elif "Commercial" in company_info["name"]: dept_type = "Commercial" elif "Solar" in company_info["name"]: dept_type = "Solar" else: continue gp_violations = 0 try: if use_date_range: gp_violations = sheets_service.get_gp_violations_for_date_range( from_date=from_dt, to_date=to_dt, branch=branch_config["name"], department=dept_type ) else: gp_violations = sheets_service.get_gp_violations_for_month( year=year, month=month, branch=branch_config["name"], department=dept_type ) total_gp_violations += gp_violations except Exception as e: logger.warning(f"⚠️ Could not fetch GP violations for {branch_config['name']} {dept_type}: {e}") dept_violations.append({"name": company_info["name"], "gp_violations": gp_violations}) company_total += total_gp_violations branches.append({ "id": branch_id, "gp_violations_total": total_gp_violations, "departments": dept_violations }) resp = { "month": month, "year": year, "from_date": from_date if use_date_range else None, "to_date": to_date if use_date_range else None, "branches": branches, "company_total": company_total } _gp_violations_cache[cache_key] = (time.time(), resp) return resp @router.get("/kpi") async def get_kpi_for_month( month: int = Query(..., ge=1, le=12), year: int = Query(..., ge=2000), user: User = Depends(get_current_user), ): """ Get KPI actuals for a single month from the KPI Google Sheet. Returns { branch: { department: { metricKey: value } } }. Only populated when GOOGLE_KPI_SHEETS_ID is set and the sheet is shared with the service account. """ return kpi_sheets_service.get_kpi_for_month(year=year, month=month) @router.get("/kpi/quarter") async def get_kpi_quarter( month: int = Query(..., ge=1, le=12), year: int = Query(..., ge=2000), user: User = Depends(get_current_user), ): """ Get KPI actuals for the quarter containing the given month. Reads the three month sheets (e.g. January, February, March for Q1) and returns segment actuals: { branch: { department: { metricKey: [m1, m2, m3] } } }. Frontend uses this to fill segments[].actual and compute quarter totals. """ return kpi_sheets_service.get_kpi_quarter(year=year, month=month) @router.get("/kpi-targets") async def get_kpi_targets_public(user: User = Depends(get_current_user)): """ Get KPI targets for the dashboard (authenticated). Returns defaults deep-merged with any admin overrides. """ storage = get_config_storage() return storage.get_kpi_targets() # Branch ID -> display name for misc data BRANCH_ID_TO_NAME = { "branch1": "Busselton", "branch2": "Bunbury", "branch3": "Mandurah", } def _user_has_branch_access(db, user: User, branch_id: str) -> bool: """Check if user has access to branch (directors, department, kpi, or branch tab permission).""" perms = get_user_permissions(db, user) if "directors.access" in perms: return True if f"branch.{branch_id}" in perms or f"kpi.{branch_id}" in perms: return True if any(p.startswith(f"department.{branch_id}_") for p in perms): return True return False @router.get("/misc-data/{branch_id}") async def get_misc_data( branch_id: str, db: Session = Depends(get_db), user: User = Depends(get_current_user), from_date: Optional[date] = Query(None, description="Quote cohort start YYYY-MM-DD (with to_date)"), to_date: Optional[date] = Query(None, description="Quote cohort end YYYY-MM-DD (with from_date)"), ): """ Get branch extras: quote data, subscriber count, time utilisation, job efficiency. Used by Sales and Efficiency tabs on branch KPI dashboard. Requires directors.access, kpi.{branch_id}, or any department.{branch_id}_* permission. Simpro-backed fields (WIP, quotes) use server-side cache (SIMPRO_API_CACHE_TTL_SECONDS, default 10 minutes). """ if branch_id not in settings.BRANCHES: raise HTTPException(status_code=404, detail=f"Branch {branch_id} not found") if not _user_has_branch_access(db, user, branch_id): raise HTTPException(status_code=403, detail="Access denied to this branch") branch_name = BRANCH_ID_TO_NAME.get(branch_id, settings.BRANCHES[branch_id]["name"]) time_utilisation = load_time_utilisation_for_branch(branch_name) job_efficiency = load_job_efficiency_for_branch(branch_name) wip_jobs = await get_wip_jobs_for_branch(branch_id) or {} try: subscriber_totals = kpi_sheets_service.get_subscriber_totals() subscriber_count = subscriber_totals.get(branch_name, 0) except Exception as e: logger.warning(f"Could not fetch subscriber totals for misc-data: {e}") subscriber_count = 0 quote_data = await get_quote_data_for_branch( branch_id, from_date if from_date is not None and to_date is not None else None, to_date if from_date is not None and to_date is not None else None, ) avg_time_to_invoice = get_avg_time_to_invoice_for_branch(branch_name) return { "quote_data": quote_data, "subscriber_count": subscriber_count, "time_utilisation": time_utilisation, "job_efficiency": job_efficiency, "wip_jobs": wip_jobs, "avg_time_to_invoice": avg_time_to_invoice, } @router.get("/branches/{branch_id}/job-stats") async def get_branch_department_job_stats( branch_id: str, department: str = Query(..., description="Department name e.g. Commercial"), db: Session = Depends(get_db), user: User = Depends(get_current_user), ): """ Get Simpro job stats for a branch department: jobs completed, total pending jobs, pending jobs with due dates. Currently supported: Bunbury Commercial (branch2, department=Commercial). Requires branch/department or kpi/directors access. """ if branch_id not in settings.BRANCHES: raise HTTPException(status_code=404, detail=f"Branch {branch_id} not found") if not _user_has_branch_access(db, user, branch_id): raise HTTPException(status_code=403, detail="Access denied to this branch") branch_config = settings.BRANCHES[branch_id] company_id = None for cid, cinfo in branch_config["companies"].items(): if cinfo.get("name", "").strip().lower() == department.strip().lower(): company_id = cid break if company_id is None: raise HTTPException( status_code=404, detail=f"Department '{department}' not found for branch {branch_id}", ) try: result = await get_job_stats_for_company(branch_id, company_id) except Exception as e: logger.warning("Job stats fetch failed for %s %s: %s", branch_id, department, e) raise HTTPException(status_code=502, detail="Failed to fetch job stats from Simpro") if not result: raise HTTPException(status_code=502, detail="Failed to fetch job stats from Simpro") return result @router.get("/sync/status", response_model=SyncStatusResponse) async def get_sync_status( db: Session = Depends(get_db), user: User = Depends(get_current_user), ): """ Get the current sync status for all branches and companies. Returns: Sync status including last sync times and invoice counts """ total_invoices = db.query(func.count(Invoice.id)).scalar() or 0 branch_statuses = [] latest_sync = None for branch_id, branch_config in settings.BRANCHES.items(): for company_id, company_info in branch_config["companies"].items(): sync_meta = db.query(SyncMetadata).filter( SyncMetadata.branch_id == branch_id, SyncMetadata.company_id == company_id ).first() if sync_meta: if not latest_sync or sync_meta.last_sync_timestamp > latest_sync: latest_sync = sync_meta.last_sync_timestamp branch_statuses.append(SyncStatusItem( branch_id=branch_id, branch_name=branch_config["name"], company_id=company_id, company_name=company_info["name"], last_sync_timestamp=sync_meta.last_sync_timestamp, last_sync_date=sync_meta.last_sync_date, invoice_count=sync_meta.invoice_count )) else: branch_statuses.append(SyncStatusItem( branch_id=branch_id, branch_name=branch_config["name"], company_id=company_id, company_name=company_info["name"], invoice_count=0 )) return SyncStatusResponse( total_invoices=total_invoices, branches=branch_statuses, last_sync_run=latest_sync ) @router.post("/sync/trigger") async def trigger_sync(): """ Manually trigger a sync operation. Useful for initial data migration or force refresh. """ from app.services.sync_service import sync_service import asyncio # Run sync in background task asyncio.create_task(sync_service.sync_all_branches()) return {"message": "Sync triggered", "status": "started"}