-- Create view for order analytics
create or replace view public.order_analytics as
select
  e.id as event_id,
  e.name as event_name,
  e.organizer_id,
  count(distinct o.id) as total_orders,
  sum(o.total_amount) as gross_revenue,
  sum(o.tax_amount) as total_tax,
  count(distinct case when o.payment_status = 'completed' then o.id end) as completed_orders,
  count(distinct case when c.fulfillment_status = 'fulfilled' then c.id end) as fulfilled_chits,
  count(distinct c.id) as total_chits
from public.events e
left join public.orders o on o.event_id = e.id
left join public.chits c on c.event_id = e.id
group by e.id, e.name, e.organizer_id;

-- Create view for popular items
create or replace view public.popular_items as
select
  mi.id as item_id,
  mi.event_id,
  mi.name as item_name,
  mi.category_id,
  c.name as category_name,
  count(ch.id) as times_ordered,
  sum(ch.quantity) as total_quantity,
  sum(ch.price * ch.quantity) as total_revenue
from public.menu_items mi
left join public.chits ch on ch.menu_item_id = mi.id
left join public.categories c on c.id = mi.category_id
group by mi.id, mi.event_id, mi.name, mi.category_id, c.name
order by times_ordered desc;
