Dashboards

-- Step 1: Add permission to enum (if not exists)
alter type supasheet.app_permission add value if not exists 'public.task_summary:select';

-- Step 2: Create view
create or replace view public.task_summary
with(security_invoker = true) as
select
  count(*) as value,
  'list-todo' as icon,
  'active tasks' as label
from tasks
where status != 'completed';

-- Step 3: Grant permissions
revoke all on public.task_summary from authenticated, service_role;
grant select on public.task_summary to authenticated;

-- Step 4: Add metadata comment
comment on view public.task_summary is '{"type": "dashboard_widget", "name": "Task Summary", "description": "Summary of active tasks", "widget_type": "card_1"}';

-- Step 5: Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_summary:select');

-- Create view for card_2 (primary/secondary values)
create or replace view public.task_completion_rate as
select
  count(*) filter (where status = 'completed') as primary,
  count(*) filter (where status != 'completed') as secondary,
  'Completed' as primary_label,
  'Active' as secondary_label
from tasks;

-- Grant permissions
revoke all on public.task_completion_rate from authenticated, service_role;
grant select on public.task_completion_rate to authenticated;

-- Add metadata comment
comment on view public.task_completion_rate is '{"type": "dashboard_widget", "name": "Task Completion Rate", "description": "Completed vs Active tasks", "widget_type": "card_2"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_completion_rate:select');

-- Create view for card_3 (value and percent)
create or replace view public.tasks_by_status as
select
  count(*) filter (where status = 'completed') as value,
  case
    when count(*) > 0
    then round((count(*) filter (where status = 'completed')::numeric / count(*)::numeric) * 100, 1)
    else 0
  end as percent
from tasks;

-- Grant permissions
revoke all on public.tasks_by_status from authenticated, service_role;
grant select on public.tasks_by_status to authenticated;

-- Add metadata comment
comment on view public.tasks_by_status is '{"type": "dashboard_widget", "name": "Tasks by Status", "description": "Completed tasks stats", "widget_type": "card_3"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.tasks_by_status:select');

-- Create view for card_4 (progress layout)
create or replace view public.task_urgent_count as
select
  count(*) filter (where status != 'completed' and priority in ('high', 'urgent')) as current,
  count(*) filter (where status != 'completed') as total,
  json_build_array(
    json_build_object('label', 'Urgent', 'value', count(*) filter (where priority = 'urgent' and status != 'completed')),
    json_build_object('label', 'High', 'value', count(*) filter (where priority = 'high' and status != 'completed')),
    json_build_object('label', 'Overdue', 'value', count(*) filter (where due_date < current_timestamp and status != 'completed'))
  ) as segments
from tasks;

-- Grant permissions
revoke all on public.task_urgent_count from authenticated, service_role;
grant select on public.task_urgent_count to authenticated;

-- Add metadata comment
comment on view public.task_urgent_count is '{"type": "dashboard_widget", "name": "Task Urgent Count", "description": "High priority tasks", "widget_type": "card_4"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_urgent_count:select');

-- Create view for table_1 (2-3 columns)
create or replace view public.task_list_simple as
select
  title,
  status,
  priority,
  completion
from tasks
order by created_at desc
limit 10;

-- Grant permissions
revoke all on public.task_list_simple from authenticated, service_role;
grant select on public.task_list_simple to authenticated;

-- Add metadata comment
comment on view public.task_list_simple is '{"type": "dashboard_widget", "name": "Recent Tasks", "description": "Latest tasks in the system", "widget_type": "table_1"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_list_simple:select');

-- Create view for table_2 (4-5 columns)
create or replace view public.task_list_detailed as
select
  title,
  status,
  priority,
  completion,
  duration,
  to_char(created_at, 'MM/DD HH24:MI') as created,
  case
    when due_date < current_timestamp and status != 'completed' then 'Overdue'
    when due_date is null then '-'
    else to_char(due_date, 'MM/DD')
  end as due
from tasks
order by created_at desc
limit 10;

-- Grant permissions
revoke all on public.task_list_detailed from authenticated, service_role;
grant select on public.task_list_detailed to authenticated;

-- Add metadata comment
comment on view public.task_list_detailed is '{"type": "dashboard_widget", "name": "Task Overview", "description": "Detailed task listing", "widget_type": "table_2"}';

-- Grant permission to role
insert into supasheet.role_permissions (role, permission)
values ('user', 'public.task_list_detailed:select');

CREATE VIEW public.revenue_today AS
SELECT
  COALESCE(SUM(total), 0) as amount,
  'Today''s Revenue' as label,
  'currency' as format
FROM orders
WHERE DATE(created_at) = CURRENT_DATE;

COMMENT ON VIEW public.revenue_today IS '{"type": "dashboard_widget", "name": "Today''s Revenue", "description": "Revenue for current day", "widget_type": "card_1"}';

CREATE VIEW public.recent_orders AS
SELECT
  o.id,
  o.created_at,
  u.email as customer,
  o.total,
  o.status
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 10;

COMMENT ON VIEW public.recent_orders IS '{"type": "dashboard_widget", "name": "Recent Orders", "description": "Latest orders in the system", "widget_type": "table_1"}';

CREATE VIEW public.sales_by_category AS
SELECT
  p.category,
  COUNT(oi.id) as items_sold,
  SUM(oi.quantity * oi.price) as revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.category
ORDER BY revenue DESC;

COMMENT ON VIEW public.sales_by_category IS '{"type": "dashboard_widget", "name": "Sales by Category", "description": "Revenue breakdown by product category", "widget_type": "table_2"}';

-- Sales rep dashboard
CREATE VIEW public.my_sales AS
SELECT
  COUNT(*) as my_orders,
  SUM(total) as my_revenue,
  AVG(total) as my_avg_order
FROM orders
WHERE sales_rep_id = auth.uid();

COMMENT ON VIEW public.my_sales IS '{"type": "dashboard_widget", "name": "My Sales", "description": "Personal sales metrics", "widget_type": "card_2"}';

-- Customer dashboard
CREATE VIEW public.my_account AS
SELECT
  COUNT(*) as total_orders,
  SUM(total) as total_spent,
  MAX(created_at) as last_order_date
FROM orders
WHERE customer_id = auth.uid();

COMMENT ON VIEW public.my_account IS '{"type": "dashboard_widget", "name": "My Account", "description": "Personal account summary", "widget_type": "card_3"}';

CREATE VIEW public.stock_alerts AS
SELECT
  name,
  stock_quantity,
  reorder_level,
  CASE
    WHEN stock_quantity = 0 THEN 'critical'
    WHEN stock_quantity <= reorder_level THEN 'warning'
    ELSE 'normal'
  END as status
FROM products
WHERE stock_quantity <= reorder_level
ORDER BY stock_quantity ASC;

COMMENT ON VIEW public.stock_alerts IS '{"type": "dashboard_widget", "name": "Stock Alerts", "description": "Low stock inventory alerts", "widget_type": "table_2"}';

-- Enable realtime for the underlying table
ALTER PUBLICATION supabase_realtime ADD TABLE orders;

-- Create view
CREATE VIEW public.live_orders AS
SELECT
  id,
  customer_id,
  total,
  status,
  created_at
FROM orders
WHERE created_at >= NOW() - INTERVAL '1 hour'
ORDER BY created_at DESC;

CREATE VIEW public.revenue_comparison AS
SELECT
  -- This week
  (SELECT COALESCE(SUM(total), 0)
   FROM orders
   WHERE created_at >= DATE_TRUNC('week', NOW())) as this_week,

  -- Last week
  (SELECT COALESCE(SUM(total), 0)
   FROM orders
   WHERE created_at >= DATE_TRUNC('week', NOW() - INTERVAL '1 week')
     AND created_at < DATE_TRUNC('week', NOW())) as last_week,

  -- % change
  (SELECT ROUND(
    ((SELECT COALESCE(SUM(total), 0) FROM orders WHERE created_at >= DATE_TRUNC('week', NOW()))::NUMERIC /
     NULLIF((SELECT COALESCE(SUM(total), 1) FROM orders WHERE created_at >= DATE_TRUNC('week', NOW() - INTERVAL '1 week') AND created_at < DATE_TRUNC('week', NOW())), 0) - 1) * 100,
    2
  )) as percent_change;

CREATE MATERIALIZED VIEW public.complex_metrics AS
SELECT
  -- expensive aggregations
  category,
  COUNT(*) as count,
  SUM(revenue) as total_revenue
FROM large_table
GROUP BY category;

-- Refresh function
CREATE OR REPLACE FUNCTION refresh_dashboard_metrics()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW public.complex_metrics;
END;
$$ LANGUAGE plpgsql;

-- Manual refresh or schedule with pg_cron
SELECT cron.schedule('refresh-metrics', '*/15 * * * *', 'SELECT refresh_dashboard_metrics()');

-- Index for date filtering
CREATE INDEX orders_created_at_idx ON orders(created_at DESC);

-- Index for aggregations
CREATE INDEX order_items_product_idx ON order_items(product_id);

-- Only last 90 days
CREATE VIEW public.recent_metrics AS
SELECT
  DATE(created_at) as date,
  COUNT(*) as orders,
  SUM(total) as revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY date;

-- Overview metrics
CREATE VIEW public.overview AS
SELECT
  (SELECT COUNT(*) FROM users) as total_users,
  (SELECT COUNT(*) FROM orders) as total_orders,
  (SELECT SUM(total) FROM orders) as total_revenue,
  (SELECT COUNT(DISTINCT customer_id) FROM orders WHERE created_at >= NOW() - INTERVAL '30 days') as active_customers;

-- Revenue trend (last 30 days)
CREATE VIEW public.revenue_trend AS
SELECT
  DATE(created_at) as date,
  SUM(total) as revenue,
  COUNT(*) as order_count
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY date
ORDER BY date;

-- Top products
CREATE VIEW public.top_products AS
SELECT
  p.name,
  COUNT(oi.id) as orders,
  SUM(oi.quantity) as quantity_sold,
  SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 10;

-- Recent orders
CREATE VIEW public.recent_orders_dashboard AS
SELECT
  o.id,
  o.created_at,
  u.email as customer,
  o.total,
  o.status
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 10;

-- Customer segments
CREATE VIEW public.customer_segments AS
SELECT
  CASE
    WHEN total_spent >= 1000 THEN 'VIP'
    WHEN total_spent >= 500 THEN 'High Value'
    WHEN total_spent >= 100 THEN 'Regular'
    ELSE 'New'
  END as segment,
  COUNT(*) as customer_count,
  SUM(total_spent) as segment_revenue
FROM (
  SELECT
    customer_id,
    SUM(total) as total_spent
  FROM orders
  GROUP BY customer_id
) customer_totals
GROUP BY segment
ORDER BY segment_revenue DESC;

-- Users see their own data
CREATE VIEW public.my_stats AS
SELECT
  COUNT(*) as my_orders,
  SUM(total) as my_spent
FROM orders
WHERE customer_id = auth.uid();