AgendaPro Dev
d796538fd9
- Rebrand all user-facing text to AgendaMax - Fix sidebar scrolling: card container no longer overflows html (flex h-full min-h-0) - Fix calendar toolbar hover: active buttons keep readable contrast - Sticky calendar toolbar (month/week/day always visible on scroll) - Seed guarantees 2-4 appointments per day for current week (Mon-Sat)
407 lines
15 KiB
TypeScript
407 lines
15 KiB
TypeScript
import { Router } from "express";
|
|
import { db } from "../db.ts";
|
|
import type { AuthedRequest } from "../lib/auth.ts";
|
|
import { ownerOnly } from "../lib/auth.ts";
|
|
import {
|
|
bizDateISO,
|
|
bizDayBoundsSqlite,
|
|
bizDayBoundsIso,
|
|
toIsoUtc,
|
|
} from "../lib/time.ts";
|
|
import { cancelRate, noShowRate, completionRate } from "../lib/metrics.ts";
|
|
|
|
export const dashboardRouter = Router();
|
|
|
|
dashboardRouter.use(ownerOnly);
|
|
|
|
const DEFAULT_TZ = "America/Mexico_City";
|
|
|
|
/** Fetch the business timezone once per handler. */
|
|
function bizTz(bid: number | null): string {
|
|
const row = db.prepare(`SELECT timezone t FROM businesses WHERE id = ?`).get(bid) as { t?: string } | undefined;
|
|
return row?.t || DEFAULT_TZ;
|
|
}
|
|
|
|
/** Clamp the range query param into [7, 120], default 30. */
|
|
function clampRange(raw: unknown): number {
|
|
const n = Number(raw);
|
|
if (!Number.isFinite(n) || n <= 0) return 30;
|
|
return Math.min(120, Math.max(7, Math.round(n)));
|
|
}
|
|
|
|
dashboardRouter.get("/overview", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const scalar = (sql: string, ...args: any[]) => (db.prepare(sql).get(...args) as any)?.v ?? 0;
|
|
|
|
// Business-local windows, expressed as canonical SQLite UTC strings.
|
|
// created_at is stored in a MIXED format (ISO-Z from seed, space from app default),
|
|
// so we wrap it with datetime() for correct chronological comparison. start_at is
|
|
// consistently ISO-Z, so it compares directly against ISO-Z bounds.
|
|
const todaySql = bizDayBoundsSqlite(tz, 0);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start; // range days ago, biz-local midnight
|
|
const prevStart = bizDayBoundsSqlite(tz, -range * 2).start; // preceding equal-length window
|
|
const todayIso = bizDayBoundsIso(tz, 0);
|
|
const weekStartIso = bizDayBoundsIso(tz, -7).start;
|
|
const rangeStartIso = bizDayBoundsIso(tz, -range).start;
|
|
const nowIso = toIsoUtc(new Date());
|
|
|
|
const revenue_today = scalar(
|
|
`SELECT COALESCE(SUM(amount+tip),0) v FROM tickets
|
|
WHERE business_id = ? AND datetime(created_at) >= ? AND datetime(created_at) <= ?`,
|
|
bid, todaySql.start, todaySql.end
|
|
);
|
|
const revenue_range = scalar(
|
|
`SELECT COALESCE(SUM(amount+tip),0) v FROM tickets WHERE business_id = ? AND datetime(created_at) >= ?`,
|
|
bid, rangeStart
|
|
);
|
|
const revenue_prev = scalar(
|
|
`SELECT COALESCE(SUM(amount+tip),0) v FROM tickets
|
|
WHERE business_id = ? AND datetime(created_at) >= ? AND datetime(created_at) < ?`,
|
|
bid, prevStart, rangeStart
|
|
);
|
|
const revenue_change_pct =
|
|
revenue_prev > 0 ? Math.round(((revenue_range - revenue_prev) / revenue_prev) * 1000) / 10 : 0;
|
|
|
|
const appts_today = scalar(
|
|
`SELECT COUNT(*) v FROM appointments WHERE business_id = ? AND start_at >= ? AND start_at <= ?`,
|
|
bid, todayIso.start, todayIso.end
|
|
);
|
|
const appts_week = scalar(
|
|
`SELECT COUNT(*) v FROM appointments WHERE business_id = ? AND start_at >= ?`,
|
|
bid, weekStartIso
|
|
);
|
|
const appts_upcoming = scalar(
|
|
`SELECT COUNT(*) v FROM appointments WHERE business_id = ? AND status = 'scheduled' AND start_at >= ?`,
|
|
bid, nowIso
|
|
);
|
|
const active_clients = scalar(
|
|
`SELECT COUNT(DISTINCT client_id) v FROM tickets WHERE business_id = ? AND datetime(created_at) >= ?`,
|
|
bid, bizDayBoundsSqlite(tz, -30).start
|
|
);
|
|
const avg_ticket = scalar(
|
|
`SELECT COALESCE(AVG(amount+tip),0) v FROM tickets WHERE business_id = ? AND datetime(created_at) >= ?`,
|
|
bid, rangeStart
|
|
);
|
|
|
|
// Rates over appointments scheduled (start_at) in the window.
|
|
const apptWindowTotal = scalar(
|
|
`SELECT COUNT(*) v FROM appointments WHERE business_id = ? AND start_at >= ?`,
|
|
bid, rangeStartIso
|
|
);
|
|
const cancelledCount = scalar(
|
|
`SELECT COUNT(*) v FROM appointments WHERE business_id = ? AND status = 'cancelled' AND start_at >= ?`,
|
|
bid, rangeStartIso
|
|
);
|
|
const noShowCount = scalar(
|
|
`SELECT COUNT(*) v FROM appointments WHERE business_id = ? AND status = 'no_show' AND start_at >= ?`,
|
|
bid, rangeStartIso
|
|
);
|
|
const completedCount = scalar(
|
|
`SELECT COUNT(*) v FROM appointments WHERE business_id = ? AND status = 'completed' AND start_at >= ?`,
|
|
bid, rangeStartIso
|
|
);
|
|
const cancel_rate = cancelRate(cancelledCount, apptWindowTotal);
|
|
const no_show_rate = noShowRate(noShowCount, apptWindowTotal);
|
|
const completion_pct = completionRate(completedCount, apptWindowTotal);
|
|
|
|
res.json({
|
|
revenue_today: Math.round(revenue_today * 100) / 100,
|
|
revenue_range: Math.round(revenue_range * 100) / 100,
|
|
revenue_prev: Math.round(revenue_prev * 100) / 100,
|
|
revenue_change_pct,
|
|
appts_today,
|
|
appts_week,
|
|
appts_upcoming,
|
|
active_clients,
|
|
avg_ticket: Math.round(avg_ticket * 100) / 100,
|
|
cancel_rate,
|
|
no_show_rate,
|
|
completion_pct,
|
|
range,
|
|
});
|
|
});
|
|
|
|
dashboardRouter.get("/best-employees", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start;
|
|
const ratingSince = bizDayBoundsSqlite(tz, -90).start;
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT e.id, e.name, e.color, e.role,
|
|
COUNT(t.id) appts,
|
|
COALESCE(SUM(t.amount+t.tip),0) revenue,
|
|
COALESCE((SELECT AVG(rating) FROM reviews WHERE employee_id = e.id AND datetime(created_at) >= ?), e.rating) avg_rating
|
|
FROM employees e
|
|
LEFT JOIN tickets t ON t.employee_id = e.id AND datetime(t.created_at) >= ?
|
|
WHERE e.business_id = ?
|
|
GROUP BY e.id
|
|
ORDER BY revenue DESC`
|
|
)
|
|
.all(ratingSince, rangeStart, bid) as any[];
|
|
const totalAppts = rows.reduce((a, r) => a + r.appts, 0);
|
|
const out = rows.map((r) => ({
|
|
employee: { id: r.id, name: r.name, color: r.color, role: r.role },
|
|
appointments: r.appts,
|
|
revenue: Math.round(r.revenue * 100) / 100,
|
|
avg_rating: Math.round(r.avg_rating * 10) / 10,
|
|
share_pct: totalAppts > 0 ? Math.round((r.appts / totalAppts) * 100) : 0,
|
|
}));
|
|
res.json({ employees: out });
|
|
});
|
|
|
|
dashboardRouter.get("/best-services", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start;
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT s.id, s.name, s.category, s.color,
|
|
COUNT(t.id) count,
|
|
COALESCE(SUM(t.amount+t.tip),0) revenue
|
|
FROM services s
|
|
LEFT JOIN tickets t ON t.service_id = s.id AND datetime(t.created_at) >= ?
|
|
WHERE s.business_id = ?
|
|
GROUP BY s.id
|
|
ORDER BY revenue DESC`
|
|
)
|
|
.all(rangeStart, bid) as any[];
|
|
const out = rows.map((r) => ({
|
|
service: { id: r.id, name: r.name, category: r.category, color: r.color },
|
|
count: r.count,
|
|
revenue: Math.round(r.revenue * 100) / 100,
|
|
}));
|
|
res.json({ services: out });
|
|
});
|
|
|
|
dashboardRouter.get("/top-tickets", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const limit = Math.min(50, Number(req.query.limit) || 10);
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start;
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT t.*, s.name service_name, s.category service_category, s.color service_color,
|
|
e.name employee_name, e.color employee_color,
|
|
c.name client_name
|
|
FROM tickets t
|
|
JOIN services s ON s.id = t.service_id
|
|
JOIN employees e ON e.id = t.employee_id
|
|
JOIN clients c ON c.id = t.client_id
|
|
WHERE t.business_id = ? AND datetime(t.created_at) >= ?
|
|
ORDER BY (t.amount + t.tip) DESC
|
|
LIMIT ?`
|
|
)
|
|
.all(bid, rangeStart, limit) as any[];
|
|
const out = rows.map((t) => ({
|
|
ticket: {
|
|
id: t.id,
|
|
business_id: t.business_id,
|
|
appointment_id: t.appointment_id,
|
|
client_id: t.client_id,
|
|
employee_id: t.employee_id,
|
|
service_id: t.service_id,
|
|
amount: t.amount,
|
|
tip: t.tip,
|
|
payment_method: t.payment_method,
|
|
created_at: t.created_at,
|
|
service: { id: t.service_id, name: t.service_name, category: t.service_category, color: t.service_color },
|
|
employee: { id: t.employee_id, name: t.employee_name, color: t.employee_color },
|
|
client: { id: t.client_id, name: t.client_name },
|
|
},
|
|
}));
|
|
res.json({ tickets: out });
|
|
});
|
|
|
|
dashboardRouter.get("/top-clients", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const limit = Math.min(50, Number(req.query.limit) || 10);
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start;
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT c.id, c.name, c.tags, c.phone,
|
|
COUNT(t.id) visits,
|
|
COALESCE(SUM(t.amount+t.tip),0) total,
|
|
MAX(t.created_at) last_visit
|
|
FROM clients c
|
|
LEFT JOIN tickets t ON t.client_id = c.id AND datetime(t.created_at) >= ?
|
|
WHERE c.business_id = ?
|
|
GROUP BY c.id
|
|
HAVING visits > 0
|
|
ORDER BY total DESC
|
|
LIMIT ?`
|
|
)
|
|
.all(rangeStart, bid, limit) as any[];
|
|
const out = rows.map((r) => ({
|
|
client: { id: r.id, name: r.name, tags: r.tags, phone: r.phone },
|
|
visits: r.visits,
|
|
total_spent: Math.round(r.total * 100) / 100,
|
|
last_visit: r.last_visit,
|
|
}));
|
|
res.json({ clients: out });
|
|
});
|
|
|
|
dashboardRouter.get("/frequent-clients", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const limit = Math.min(50, Number(req.query.limit) || 10);
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start;
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT c.id, c.name, c.tags, c.phone,
|
|
COUNT(t.id) visits,
|
|
COALESCE(SUM(t.amount+t.tip),0) total,
|
|
MAX(t.created_at) last_visit
|
|
FROM clients c
|
|
JOIN tickets t ON t.client_id = c.id AND datetime(t.created_at) >= ?
|
|
WHERE c.business_id = ?
|
|
GROUP BY c.id
|
|
ORDER BY visits DESC, total DESC
|
|
LIMIT ?`
|
|
)
|
|
.all(rangeStart, bid, limit) as any[];
|
|
const out = rows.map((r) => ({
|
|
client: { id: r.id, name: r.name, tags: r.tags, phone: r.phone },
|
|
visits: r.visits,
|
|
total_spent: Math.round(r.total * 100) / 100,
|
|
last_visit: r.last_visit,
|
|
}));
|
|
res.json({ clients: out });
|
|
});
|
|
|
|
dashboardRouter.get("/revenue-trend", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const days = Math.min(120, Math.max(1, Number(req.query.days) || 30));
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -days).start;
|
|
// Pull raw tickets and bucket by BUSINESS-LOCAL date in JS (SQLite date() would use UTC,
|
|
// misattributing the evening rush to the next day).
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT created_at, amount, tip FROM tickets
|
|
WHERE business_id = ? AND datetime(created_at) >= ?`
|
|
)
|
|
.all(bid, rangeStart) as { created_at: string; amount: number; tip: number }[];
|
|
const map = new Map<string, { revenue: number; appts: number }>();
|
|
for (const r of rows) {
|
|
// Stored created_at may be "YYYY-MM-DD HH:MM:SS" (space) or "YYYY-MM-DDTHH:MM:SSZ";
|
|
// normalize to an ISO UTC instant before converting to the biz date.
|
|
const iso = typeof r.created_at === "string" && r.created_at.includes("T")
|
|
? r.created_at
|
|
: String(r.created_at).replace(" ", "T") + "Z";
|
|
const key = bizDateISO(new Date(iso), tz);
|
|
const cur = map.get(key) ?? { revenue: 0, appts: 0 };
|
|
cur.revenue += Number(r.amount) + Number(r.tip);
|
|
cur.appts += 1;
|
|
map.set(key, cur);
|
|
}
|
|
const out: { date: string; revenue: number; appts: number }[] = [];
|
|
const now = new Date();
|
|
for (let i = days - 1; i >= 0; i--) {
|
|
const d = new Date(now.getTime() - i * 86400000);
|
|
const key = bizDateISO(d, tz);
|
|
const row = map.get(key);
|
|
out.push({
|
|
date: key,
|
|
revenue: row ? Math.round(row.revenue * 100) / 100 : 0,
|
|
appts: row ? row.appts : 0,
|
|
});
|
|
}
|
|
res.json({ points: out });
|
|
});
|
|
|
|
dashboardRouter.get("/revenue-by-category", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start;
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT s.category, COUNT(t.id) count, COALESCE(SUM(t.amount+t.tip),0) revenue
|
|
FROM tickets t
|
|
JOIN services s ON s.id = t.service_id
|
|
WHERE t.business_id = ? AND datetime(t.created_at) >= ?
|
|
GROUP BY s.category
|
|
ORDER BY revenue DESC`
|
|
)
|
|
.all(bid, rangeStart) as any[];
|
|
const out = rows.map((r) => ({
|
|
category: r.category,
|
|
count: r.count,
|
|
revenue: Math.round(r.revenue * 100) / 100,
|
|
}));
|
|
res.json({ slices: out });
|
|
});
|
|
|
|
dashboardRouter.get("/tickets", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const limit = Math.min(200, Number(req.query.limit) || 50);
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT t.*, s.name service_name, s.category service_category, s.color service_color,
|
|
e.name employee_name, e.color employee_color,
|
|
c.name client_name
|
|
FROM tickets t
|
|
JOIN services s ON s.id = t.service_id
|
|
JOIN employees e ON e.id = t.employee_id
|
|
JOIN clients c ON c.id = t.client_id
|
|
WHERE t.business_id = ?
|
|
ORDER BY t.created_at DESC
|
|
LIMIT ?`
|
|
)
|
|
.all(bid, limit) as any[];
|
|
const out = rows.map((t) => ({
|
|
id: t.id,
|
|
business_id: t.business_id,
|
|
appointment_id: t.appointment_id,
|
|
client_id: t.client_id,
|
|
employee_id: t.employee_id,
|
|
service_id: t.service_id,
|
|
amount: t.amount,
|
|
tip: t.tip,
|
|
commission: t.commission,
|
|
payment_method: t.payment_method,
|
|
created_at: t.created_at,
|
|
service: { id: t.service_id, name: t.service_name, category: t.service_category, color: t.service_color },
|
|
employee: { id: t.employee_id, name: t.employee_name, color: t.employee_color },
|
|
client: { id: t.client_id, name: t.client_name },
|
|
}));
|
|
res.json({ tickets: out });
|
|
});
|
|
|
|
dashboardRouter.get("/commissions", (req: AuthedRequest, res) => {
|
|
const bid = req.user!.business_id;
|
|
const range = clampRange(req.query.range);
|
|
const tz = bizTz(bid);
|
|
const rangeStart = bizDayBoundsSqlite(tz, -range).start;
|
|
const rows = db
|
|
.prepare(
|
|
`SELECT e.id, e.name, e.color, e.role,
|
|
COUNT(t.id) sales,
|
|
COALESCE(SUM(t.amount + t.tip), 0) revenue,
|
|
COALESCE(SUM(t.commission), 0) commission
|
|
FROM employees e
|
|
LEFT JOIN tickets t ON t.employee_id = e.id AND datetime(t.created_at) >= ?
|
|
WHERE e.business_id = ?
|
|
GROUP BY e.id
|
|
ORDER BY commission DESC`
|
|
)
|
|
.all(rangeStart, bid) as any[];
|
|
const out = rows.map((r) => ({
|
|
employee: { id: r.id, name: r.name, color: r.color, role: r.role },
|
|
sales: r.sales,
|
|
revenue: Math.round(r.revenue * 100) / 100,
|
|
commission: Math.round(r.commission * 100) / 100,
|
|
}));
|
|
const total = out.reduce((a, r) => a + r.commission, 0);
|
|
res.json({ rows: out, total: Math.round(total * 100) / 100 });
|
|
});
|