"""
Forecast Service - Revenue forecasting and run-rate calculations.
Implements: get_forecast_data, calculate_run_rate
"""
from datetime import datetime, timezone, timedelta
from sqlalchemy import func
from ..models import db, Forecast, KPIValue, Company
def get_forecast_data(company_id, period='monthly'):
"""Get forecast data for a company, grouped by period."""
forecasts = Forecast.query.filter_by(
company_id=company_id,
period=period
).order_by(Forecast.period_start).all()
result = []
for f in forecasts:
result.append({
'id': f.id,
'period_start': f.period_start,
'period_end': f.period_end,
'projected': f.projected_value,
'actual': f.actual_value,
'variance': f.variance,
'variance_pct': f.variance_pct,
'confidence': f.confidence,
})
return result
def calculate_run_rate(company_id):
"""Calculate current run-rate vs target based on YTD data."""
now = datetime.now(timezone.utc)
start_of_year = now.replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0)
# Get actual revenue YTD
kpi_values = KPIValue.query.filter_by(
company_id=company_id,
kpi_name='total_revenue'
).filter(
KPIValue.period_start >= start_of_year
).all()
ytd_revenue = sum(kv.value for kv in kpi_values if kv.value)
# Get target annual revenue
company = db.session.get(Company, company_id)
target = company.target_revenue if company else None
if not target:
return {
'ytd_revenue': ytd_revenue,
'annual_target': target,
'run_rate': None,
'months_elapsed': 0,
'months_remaining': 0,
'pace': None,
}
months_elapsed = (now.month - 1) + (now.day / 30)
months_remaining = 12 - months_elapsed
# Annualized run rate
run_rate = (ytd_revenue / months_elapsed * 12) if months_elapsed > 0 else 0
# Required monthly pace to hit target
remaining_needed = target - ytd_revenue
required_monthly = (remaining_needed / months_remaining) if months_remaining > 0 else 0
return {
'ytd_revenue': ytd_revenue,
'annual_target': target,
'run_rate': round(run_rate, 2),
'months_elapsed': round(months_elapsed, 1),
'months_remaining': round(months_remaining, 1),
'pace': 'on_track' if run_rate >= target else 'behind',
'required_monthly': round(required_monthly, 2),
'remaining_needed': round(remaining_needed, 2),
}
def get_company_pipeline(company_id):
"""Get pipeline stages and values for a company."""
# Get forecast data as proxy for pipeline
forecasts = Forecast.query.filter_by(
company_id=company_id,
forecast_type='pipeline'
).order_by(Forecast.period_start.desc()).limit(12).all()
pipeline_stages = [
{'name': 'Qualified', 'value': 0, 'count': 0},
{'name': 'Proposal', 'value': 0, 'count': 0},
{'name': 'Negotiation', 'value': 0, 'count': 0},
{'name': 'Closed Won', 'value': 0, 'count': 0},
]
# Aggregate from forecasts
for f in forecasts:
if f.metadata_json:
stage_data = f.metadata_json.get('stages', {})
for key, value in stage_data.items():
for stage in pipeline_stages:
if stage['name'].lower() in key.lower():
stage['value'] += value.get('value', 0)
stage['count'] += value.get('count', 0)
total_pipeline = sum(s['value'] for s in pipeline_stages)
total_deals = sum(s['count'] for s in pipeline_stages)
return {
'stages': pipeline_stages,
'total_value': total_pipeline,
'total_deals': total_deals,
}