Reports

-- Full deal list with company, contact, and activity rollups.
create or replace view crm.deals_report
with (security_invoker = true) as
select
  d.id, d.name, d.value, d.stage, d.close_date,
  c.name                    as company,
  ct.full_name              as primary_contact,
  count(a.id)               as activity_count,
  max(a.created_at)         as last_activity_at
from crm.deals d
left join crm.companies   c  on c.id = d.company_id
left join crm.contacts    ct on ct.id = d.primary_contact_id
left join crm.activities  a  on a.deal_id = d.id
group by d.id, c.name, ct.full_name;

comment on view crm.deals_report is '{
  "type": "report",
  "name": "Deals report",
  "description": "Full deal list with related rollups"
}';