# The reports the shop runs every day.
# [name] runs-per-day, then the SQL, then an empty line.

[cash_today] 20
SELECT b.Outlet_id, SUM(p.amount) FROM Payment p
JOIN Bill b ON b.id = p.Bill_id
WHERE p.method = 'cash' AND p.paid_at >= '2026-09-30'
  AND p.paid_at < '2026-10-01'
GROUP BY b.Outlet_id

[customer_history] 200
SELECT b.id, b.arrived, b.payment FROM Customer c
JOIN Bill b ON b.Customer_id = c.id
WHERE c.phone = '0811-0000-2588-7'
ORDER BY b.arrived DESC

[waiting_orders] 100
SELECT id, rack, arrived FROM Bill
WHERE Outlet_id = 'O02-7' AND status IN ('queued', 'finished')
ORDER BY arrived

[unpaid_bills] 30
SELECT b.id, b.payment - IFNULL((SELECT SUM(p.amount) FROM Payment p
                                 WHERE p.Bill_id = b.id), 0) AS due
FROM Bill b
WHERE b.Outlet_id = 'O01-3' AND b.status <> 'departed'

[service_mix] 5
SELECT Service_id, COUNT(*), SUM(payment) FROM Bill
WHERE arrived >= '2026-09-01' AND arrived < '2026-10-01'
GROUP BY Service_id

[bill_timeline] 150
SELECT status, changed_at FROM StatusLog
WHERE Bill_id = 'B2950-12' ORDER BY changed_at

[complaints_week] 3
SELECT Employee_id, COUNT(*) FROM StatusLog
WHERE status = 'complaint' AND changed_at >= '2026-09-24'
GROUP BY Employee_id
