Tyler
90 lines
3.3 KiB
PL/PgSQL
90 lines
3.3 KiB
PL/PgSQL
-- 0091_dashboard_summary_rpc.sql
|
|
--
|
|
-- Dashboard summary RPC for the mobile-first admin v2 dashboard
|
|
-- (`/admin/v2`).
|
|
--
|
|
-- Returns a single JSONB document that powers the four stat cards +
|
|
-- the "Today's stops" timeline + the 7-day mini-chart:
|
|
--
|
|
-- {
|
|
-- "orders_today": <int>, -- count of orders placed today
|
|
-- "revenue_today": <int cents>, -- sum of total_cents (excl. canceled)
|
|
-- "pending_fulfillment": <int>, -- count of orders awaiting pickup/ship
|
|
-- "stops_today": <int>, -- count of stops scheduled for today
|
|
-- "orders_last_7_days": [{date, count}] -- 7-element array, oldest → today
|
|
-- }
|
|
--
|
|
-- Schema notes (vs. the plan's draft):
|
|
-- * `orders` uses `placed_at` (TIMESTAMPTZ) — not `created_at`
|
|
-- * `orders.status` is the legacy enum: 'pending' | 'confirmed' |
|
|
-- 'fulfilled' | 'canceled' (not the v2 vocabulary 'placed'/'ready')
|
|
-- * `orders.total_cents` is INTEGER (NOT NULL DEFAULT 0 in 0001) — no
|
|
-- column-add step is required
|
|
-- * `stops` has a `date DATE` column (not `scheduled_at`); the legacy
|
|
-- stops page (and v2 stops page) both key off `stops.date`
|
|
--
|
|
-- SECURITY DEFINER so the v2 server action can call it without depending
|
|
-- on RLS; brand scoping is enforced inside the function via the
|
|
-- `p_brand_id` parameter (NULL → platform_admin "all brands" scope).
|
|
-- Re-runs are safe — this is `CREATE OR REPLACE FUNCTION`.
|
|
|
|
CREATE OR REPLACE FUNCTION get_dashboard_summary(p_brand_id UUID)
|
|
RETURNS JSONB
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
result JSONB;
|
|
brand_filter TEXT;
|
|
BEGIN
|
|
-- The brand filter is the same text fragment for every SELECT in this
|
|
-- function. NULL = "all brands" (platform_admin scope); non-NULL =
|
|
-- scope to a single brand.
|
|
IF p_brand_id IS NULL THEN
|
|
brand_filter := '';
|
|
ELSE
|
|
brand_filter := format(' AND brand_id = %L', p_brand_id);
|
|
END IF;
|
|
|
|
EXECUTE format(
|
|
$q$
|
|
SELECT jsonb_build_object(
|
|
'orders_today', (
|
|
SELECT COUNT(*) FROM orders
|
|
WHERE placed_at::date = CURRENT_DATE %s
|
|
),
|
|
'revenue_today', (
|
|
SELECT COALESCE(SUM(total_cents), 0)::bigint FROM orders
|
|
WHERE placed_at::date = CURRENT_DATE
|
|
AND status <> 'canceled' %s
|
|
),
|
|
'pending_fulfillment', (
|
|
SELECT COUNT(*) FROM orders
|
|
WHERE status IN ('pending', 'confirmed') %s
|
|
),
|
|
'stops_today', (
|
|
SELECT COUNT(*) FROM stops
|
|
WHERE "date" = CURRENT_DATE %s
|
|
),
|
|
'orders_last_7_days', (
|
|
SELECT COALESCE(jsonb_agg(jsonb_build_object('date', d::date, 'count', COALESCE(o.cnt, 0)) ORDER BY d), '[]'::jsonb)
|
|
FROM generate_series(CURRENT_DATE - INTERVAL '6 days', CURRENT_DATE, '1 day') d
|
|
LEFT JOIN (
|
|
SELECT placed_at::date AS day, COUNT(*) AS cnt
|
|
FROM orders
|
|
WHERE placed_at >= CURRENT_DATE - INTERVAL '7 days' %s
|
|
GROUP BY 1
|
|
) o ON o.day = d::date
|
|
)
|
|
)
|
|
$q$,
|
|
brand_filter, brand_filter, brand_filter, brand_filter, brand_filter
|
|
) INTO result;
|
|
|
|
RETURN result;
|
|
END;
|
|
$$;
|
|
|
|
COMMENT ON FUNCTION get_dashboard_summary(UUID) IS
|
|
'Returns a single JSONB document with the four dashboard stat counts + 7-day order history. p_brand_id = NULL means platform_admin scope (all brands).';
|