Data Consolidation Agent

-- Territory summary with aggregated metrics
SELECT 
    t.name AS territory_name,
    COUNT(DISTINCT r.id) AS rep_count,
    SUM(m.revenue_ytd) AS total_revenue_ytd,
    SUM(m.quota_ytd) AS total_quota_ytd,
    CASE 
        WHEN SUM(m.quota_ytd) > 0 
        THEN (SUM(m.revenue_ytd) / SUM(m.quota_ytd) * 100)
        ELSE 0 
    END AS attainment_percent
FROM territories t
LEFT JOIN representatives r ON r.territory_id = t.id
LEFT JOIN (
    SELECT rep_id, revenue AS revenue_ytd, quota AS quota_ytd
    FROM metrics
    WHERE metric_type = 'YTD'
    AND metric_date = (SELECT MAX(metric_date) FROM metrics WHERE metric_type = 'YTD')
) m ON m.rep_id = r.id
GROUP BY t.id, t.name
ORDER BY total_revenue_ytd DESC;

interface DashboardData {
  generated_at: string;
  territories: TerritoryPerformance[];
  top_performers: RepPerformance[];
  pipeline: PipelineSnapshot;
  trends: TrendData[];
}

interface TerritoryPerformance {
  territory_name: string;
  rep_count: number;
  revenue_ytd: number;
  quota_ytd: number;
  attainment_percent: number;
  revenue_mtd: number;
  quota_mtd: number;
}

interface RepPerformance {
  rep_name: string;
  territory: string;
  revenue_ytd: number;
  quota_ytd: number;
  attainment_percent: number;
  rank: number;
}

class DashboardConsolidator {
  async generateDashboard(): Promise<DashboardData> {
    // Execute all queries in parallel for performance
    const [territories, topPerformers, pipeline, trends] = await Promise.all([
      this.getTerritoryPerformance(),
      this.getTopPerformers(5),
      this.getPipelineSnapshot(),
      this.getTrendData(6) // 6 months
    ]);
    
    return {
      generated_at: new Date().toISOString(),
      territories,
      top_performers: topPerformers,
      pipeline,
      trends
    };
  }
}

-- Materialized view for latest metrics (refresh on insert)
CREATE MATERIALIZED VIEW latest_metrics AS
SELECT DISTINCT ON (rep_id, metric_type)
    rep_id,
    metric_type,
    revenue,
    quota,
    metric_date
FROM metrics
ORDER BY rep_id, metric_type, metric_date DESC;

-- Refresh trigger
CREATE TRIGGER refresh_latest_metrics
AFTER INSERT ON metrics
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_materialized_view('latest_metrics');