Metrics

Copy-- Collected revenue per week (SDG), by stream. Weeks start Monday, Port Sudan time.
WITH money AS (
  SELECT p."createdAt" AS at, 'desk:' || c.kind AS stream, p.amount
  FROM "DeskPayment" p JOIN "DeskCharge" c ON c.id = p."chargeId"
  WHERE p."voidedAt" IS NULL
  UNION ALL
  SELECT cp."paidAt", 'cafe', cp.amount
  FROM "CafePayment" cp
  WHERE cp.status = 'PAID' AND cp.method IN ('CASH', 'BANKAK')
  UNION ALL
  SELECT s."createdAt", 'cafe-tab', s.amount
  FROM "CafeTabSettlement" s
)
SELECT date_trunc('week', at AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum')::date AS week,
       stream,
       sum(amount) AS sdg
FROM money
WHERE at >= now() - interval '12 weeks'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

Copy-- Active and new desk customers per week.
WITH v AS (
  SELECT "customerId",
         date_trunc('week', "checkedInAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum')::date AS week
  FROM "DeskVisit"
),
first_week AS (
  SELECT "customerId", min(week) AS week FROM v GROUP BY 1
)
SELECT v.week,
       count(DISTINCT v."customerId") AS active,
       count(DISTINCT f."customerId") AS new
FROM v
LEFT JOIN first_week f ON f."customerId" = v."customerId" AND f.week = v.week
GROUP BY 1
ORDER BY 1 DESC
LIMIT 12;

Copy-- Booking funnel per creation week: request → paid (Bankak submitted) → confirmed.
SELECT date_trunc('week', "createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum')::date AS week,
       count(*)                                                        AS requests,
       count(*) FILTER (WHERE amount IS NOT NULL)                      AS priced,
       count(*) FILTER (WHERE "bankakReference" IS NOT NULL)           AS paid_online,
       count(*) FILTER (WHERE status IN ('CONFIRMED', 'COMPLETED'))    AS confirmed,
       count(*) FILTER (WHERE status = 'REJECTED')                     AS rejected,
       count(*) FILTER (WHERE status = 'CANCELLED')                    AS cancelled,
       round(100.0 * count(*) FILTER (WHERE status IN ('CONFIRMED', 'COMPLETED'))
             / nullif(count(*), 0), 1)                                 AS confirm_pct
FROM "BookingRequest"
GROUP BY 1
ORDER BY 1 DESC
LIMIT 12;

Copy-- Demand and conversion by product over the last 28 days.
SELECT product,
       count(*) AS requests,
       count(*) FILTER (WHERE status IN ('CONFIRMED', 'COMPLETED')) AS confirmed,
       round(100.0 * count(*) FILTER (WHERE status IN ('CONFIRMED', 'COMPLETED'))
             / nullif(count(*), 0), 1) AS confirm_pct,
       coalesce(sum(amount) FILTER (WHERE status IN ('CONFIRMED', 'COMPLETED')), 0) AS quoted_sdg
FROM "BookingRequest"
WHERE "createdAt" >= now() - interval '28 days'
GROUP BY 1
ORDER BY 2 DESC;

Copy-- Speed: request → guest pays, and guest pays → staff verify (verify ≈ the desk charge's creation).
SELECT count(*) AS paid_online,
       percentile_cont(0.5) WITHIN GROUP (
         ORDER BY extract(epoch FROM b."paymentSubmittedAt" - b."createdAt") / 60) AS median_min_to_pay,
       percentile_cont(0.5) WITHIN GROUP (
         ORDER BY extract(epoch FROM c."createdAt" - b."paymentSubmittedAt") / 60) AS median_min_to_verify
FROM "BookingRequest" b
LEFT JOIN "DeskCharge" c ON c."bookingId" = b.id
WHERE b."paymentSubmittedAt" IS NOT NULL
  AND b."createdAt" >= now() - interval '28 days';

Copy-- Lost demand: priced requests never paid (hold lapsed or auto-cancelled), last 28 days.
SELECT product,
       count(*) AS unpaid_priced,
       sum(amount) AS sdg_left_on_table
FROM "BookingRequest"
WHERE amount IS NOT NULL
  AND "bankakReference" IS NULL
  AND status IN ('PENDING', 'CANCELLED')
  AND "createdAt" >= now() - interval '28 days'
  AND ("holdUntil" IS NULL OR "holdUntil" < now())
GROUP BY 1
ORDER BY 3 DESC NULLS LAST;

Copy-- Ticketed events: fill rate, online vs door, hold conversion, attendance.
SELECT e.title,
       (e."startsAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum')::date AS day,
       e.capacity,
       coalesce(sum(o.quantity) FILTER (WHERE o.status = 'PAID'), 0)                        AS sold,
       coalesce(sum(o.quantity) FILTER (WHERE o.status = 'PAID' AND o.channel = 'DOOR'), 0) AS door,
       round(100.0 * sum(o.quantity) FILTER (WHERE o.status = 'PAID') / nullif(e.capacity, 0), 1) AS fill_pct,
       round(100.0 * count(*) FILTER (WHERE o.channel = 'ONLINE' AND o.status = 'PAID')
             / nullif(count(*) FILTER (WHERE o.channel = 'ONLINE'), 0), 1)                  AS online_paid_pct,
       (SELECT count(*) FROM "Ticket" t JOIN "TicketOrder" o2 ON o2.id = t."orderId"
         WHERE o2."eventId" = e.id AND t."checkedInAt" IS NOT NULL)                         AS admitted,
       coalesce(sum(o.total) FILTER (WHERE o.status = 'PAID'), 0)                           AS revenue_sdg
FROM "Event" e
JOIN "TicketOrder" o ON o."eventId" = e.id
GROUP BY e.id
ORDER BY e."startsAt" DESC
LIMIT 12;

Copy-- Free events (on-site registration): demand vs capacity, waitlist, show-up.
SELECT e.title,
       e.capacity,
       count(r.*) FILTER (WHERE r.status IN ('REGISTERED', 'ATTENDED')) AS seats_taken,
       count(r.*) FILTER (WHERE r.status = 'WAITLISTED')                AS waitlisted,
       count(r.*) FILTER (WHERE r.status = 'ATTENDED')                  AS attended,
       round(100.0 * count(r.*) FILTER (WHERE r.status = 'ATTENDED')
             / nullif(count(r.*) FILTER (WHERE r.status IN ('REGISTERED', 'ATTENDED')), 0), 1) AS show_up_pct
FROM "Event" e
JOIN "EventRegistration" r ON r."eventId" = e.id
GROUP BY e.id
ORDER BY e."startsAt" DESC
LIMIT 12;

Copy-- Program intake: applications per program and how they were decided.
SELECT p.title,
       p.status,
       count(a.*)                                        AS applications,
       count(a.*) FILTER (WHERE a.status = 'SUBMITTED')  AS unreviewed,
       count(a.*) FILTER (WHERE a.status = 'ACCEPTED')   AS accepted,
       round(100.0 * count(a.*) FILTER (WHERE a.status = 'ACCEPTED')
             / nullif(count(a.*) FILTER (WHERE a.status IN ('ACCEPTED', 'REJECTED')), 0), 1) AS accept_pct
FROM "Program" p
LEFT JOIN "ProgramApplication" a ON a."programId" = p.id
GROUP BY p.id
ORDER BY applications DESC;

Copy-- Enquiries by topic (last 28 days) and the backlog older than 48 hours.
SELECT topic,
       count(*) FILTER (WHERE "createdAt" >= now() - interval '28 days')          AS last_28d,
       count(*) FILTER (WHERE status = 'NEW')                                     AS new,
       count(*) FILTER (WHERE status = 'NEW' AND "createdAt" < now() - interval '48 hours') AS stale_new,
       count(*) FILTER (WHERE topic = 'MEMBERSHIP' AND subject IS NOT NULL
                          AND "createdAt" >= now() - interval '28 days')          AS with_plan
FROM "Enquiry"
GROUP BY 1
ORDER BY 2 DESC;

Copy-- Visits per day and time on site, by kind (last 28 days).
SELECT ("checkedInAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum')::date AS day,
       kind,
       count(*) AS visits,
       round(avg(minutes)) AS avg_minutes,
       count(*) FILTER (WHERE note LIKE '%auto check-out%') AS auto_closed
FROM "DeskVisit"
WHERE "checkedInAt" >= now() - interval '28 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

Copy-- Retention: customers seen in the previous 28 days who came back in the last 28.
WITH prev AS (
  SELECT DISTINCT "customerId" FROM "DeskVisit"
  WHERE "checkedInAt" >= now() - interval '56 days' AND "checkedInAt" < now() - interval '28 days'
),
curr AS (
  SELECT DISTINCT "customerId" FROM "DeskVisit"
  WHERE "checkedInAt" >= now() - interval '28 days'
)
SELECT (SELECT count(*) FROM prev) AS prev_customers,
       (SELECT count(*) FROM prev JOIN curr USING ("customerId")) AS returned,
       round(100.0 * (SELECT count(*) FROM prev JOIN curr USING ("customerId"))
             / nullif((SELECT count(*) FROM prev), 0), 1) AS retention_pct;

Copy-- Packages: sales by plan and how much of the bought time is used.
SELECT "planKey",
       count(*) AS sold,
       sum(price) AS sdg,
       round(100.0 * sum("minutesUsed") / nullif(sum("minutesTotal"), 0), 1) AS used_pct,
       count(*) FILTER (WHERE "expiresAt" < now()
                         AND ("minutesTotal" IS NULL OR "minutesUsed" < "minutesTotal")) AS expired_unused
FROM "DeskPackage"
WHERE "createdAt" >= now() - interval '90 days'
GROUP BY 1
ORDER BY 3 DESC;

Copy-- Private offices: occupancy and rent collection.
SELECT (SELECT count(*) FROM "DeskOffice" WHERE "isActive")                         AS offices,
       (SELECT count(DISTINCT "officeId") FROM "OfficeContract" WHERE status = 'ACTIVE') AS occupied,
       (SELECT coalesce(sum(amount - "paidAmount"), 0) FROM "DeskCharge"
         WHERE kind = 'OFFICE_RENT' AND status = 'OPEN' AND "voidedAt" IS NULL)        AS rent_owed_sdg,
       (SELECT coalesce(sum(amount - "paidAmount"), 0) FROM "DeskCharge"
         WHERE kind = 'OFFICE_RENT' AND status = 'OPEN' AND "voidedAt" IS NULL
           AND "dueDate" < now())                                                     AS rent_overdue_sdg;

Copy-- Café per week: orders, QR share, average ticket, cancellations, speed.
SELECT date_trunc('week', "createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum')::date AS week,
       count(*)                                              AS orders,
       round(100.0 * count(*) FILTER (WHERE channel = 'QR') / count(*), 1) AS qr_pct,
       round(avg(total) FILTER (WHERE status <> 'CANCELLED')) AS avg_ticket_sdg,
       round(100.0 * count(*) FILTER (WHERE status = 'CANCELLED') / count(*), 1) AS cancel_pct,
       round((percentile_cont(0.5) WITHIN GROUP (
         ORDER BY extract(epoch FROM "confirmedAt" - "createdAt") / 60)
         FILTER (WHERE channel = 'QR'))::numeric, 1)          AS qr_median_min_to_confirm,
       round((percentile_cont(0.5) WITHIN GROUP (
         ORDER BY extract(epoch FROM "readyAt" - "confirmedAt") / 60))::numeric, 1) AS median_min_to_ready
FROM "CafeOrder"
GROUP BY 1
ORDER BY 1 DESC
LIMIT 12;

Copy-- Café peak hours (Port Sudan time), last 28 days.
SELECT extract(hour FROM "createdAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum') AS hour,
       count(*) AS orders,
       sum(total) FILTER (WHERE status <> 'CANCELLED') AS sdg
FROM "CafeOrder"
WHERE "createdAt" >= now() - interval '28 days'
GROUP BY 1
ORDER BY 1;

Copy-- Café top items by revenue, last 28 days.
SELECT i.names ->> 'en' AS item,
       sum(i.quantity) AS qty,
       sum(i."lineTotal") AS sdg
FROM "CafeOrderItem" i
JOIN "CafeOrder" o ON o.id = i."orderId"
WHERE o.status <> 'CANCELLED'
  AND o."createdAt" >= now() - interval '28 days'
GROUP BY 1
ORDER BY 3 DESC
LIMIT 10;

Copy-- Automation health: did today's routines run, and what is piling up.
SELECT (SELECT "routinesRanOn" FROM "DeskSettings" LIMIT 1)                     AS routines_ran_on,
       (SELECT count(*) FROM "TicketOrder"
         WHERE status = 'PENDING' AND "holdUntil" < now())                       AS ticket_holds_expired_still_pending,
       (SELECT count(*) FROM "BookingRequest"
         WHERE status = 'PENDING' AND "bankakReference" IS NOT NULL)             AS bookings_to_verify,
       (SELECT count(*) FROM "TicketOrder" WHERE status = 'SUBMITTED')           AS ticket_orders_to_verify,
       (SELECT count(*) FROM "DeskVisit"
         WHERE "checkedOutAt" IS NULL
           AND ("checkedInAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Africa/Khartoum')::date
               < (now() AT TIME ZONE 'Africa/Khartoum')::date)                   AS visits_open_from_earlier_days,
       (SELECT count(*) FROM "CafeOrder"
         WHERE status IN ('NEW', 'CONFIRMED', 'PREPARING', 'READY')
           AND "createdAt" < now() - interval '2 hours')                        AS cafe_orders_stuck_2h;