Charts

-- Pie chart: deals grouped by stage.
create or replace view crm.deals_by_stage_pie
with (security_invoker = true) as
select
  stage::text as label,
  count(*)    as value
from crm.deals
group by stage;

comment on view crm.deals_by_stage_pie is '{
  "type": "chart",
  "name": "Deals by stage",
  "chart_type": "pie"
}';

-- Line chart: weekly pipeline trend, last 8 weeks.
create or replace view crm.pipeline_trend_line
with (security_invoker = true) as
select
  to_char(date_trunc('week', created_at), 'Mon DD') as date,
  count(*)                                          as deals,
  coalesce(sum(value), 0)::bigint                   as pipeline
from crm.deals
where created_at >= current_date - interval '8 weeks'
group by date_trunc('week', created_at)
order by date_trunc('week', created_at);

comment on view crm.pipeline_trend_line is '{
  "type": "chart",
  "name": "Pipeline trend",
  "chart_type": "line"
}';