Appointment Booking System

You are a senior full-stack engineer. Build a complete appointment booking system for "{business_name}".
This is a {business_type} based in Malaysia with {num_staff} staff members, operating {operating_hours}.

Tech stack: Next.js 16 (App Router) + Tailwind CSS 4 + Supabase (Postgres + Auth + RLS + Realtime)

---

## 1. Data Model (Supabase Postgres)

### 1.1 businesses
- id (uuid, PK), name, type, phone, address, operating_hours (jsonb), booking_interval ({booking_interval} minutes), cancellation_hours (how many hours before can cancel), primary_color ({primary_color}), created_at

### 1.2 staff
- id (uuid, PK), business_id (FK), name, phone, email, avatar_url, bio, is_active, sort_order, created_at
- Index on business_id

### 1.3 staff_schedules
- id (uuid, PK), staff_id (FK), day_of_week (0-6), start_time (time), end_time (time), is_working (boolean)
- One record per staff per day, indicating whether they work and what hours
- Composite index on staff_id + day_of_week

### 1.4 staff_breaks
- id (uuid, PK), staff_id (FK), day_of_week, break_start (time), break_end (time)

### 1.5 staff_off_days
- id (uuid, PK), staff_id (FK), off_date (date), reason
- Index on staff_id + off_date

### 1.6 services
- id (uuid, PK), business_id (FK), name_zh, name_en, description_zh, description_en, duration_minutes, price (numeric, MYR), category, is_active, sort_order, created_at

### 1.7 staff_services (many-to-many)
- staff_id (FK), service_id (FK), PRIMARY KEY (staff_id, service_id)

### 1.8 customers
- id (uuid, PK), business_id (FK), name, phone (format +60xxxxxxxxx), email, visit_count (default 0), total_spend (default 0), notes, first_visit_at, last_visit_at, created_at
- Unique index on phone + business_id (no duplicate phone per business)

### 1.9 bookings
- id (uuid, PK), business_id (FK), customer_id (FK), staff_id (FK), service_id (FK)
- booking_date (date), start_time (time), end_time (time — auto-calculated: start_time + service.duration_minutes)
- status: 'pending' | 'confirmed' | 'completed' | 'cancelled' | 'no_show'
- total_price (numeric), notes, cancelled_at, cancel_reason
- created_at, updated_at
- Index: staff_id + booking_date + start_time (core anti-collision index)
- Index: customer_id, booking_date
- RLS: anon can INSERT (create booking), admin has full CRUD

### 1.10 booking_events (audit log)
- id (uuid, PK), booking_id (FK), event_type ('created'|'confirmed'|'cancelled'|'completed'|'reminder_sent'), metadata (jsonb), created_at

---

## 2. Core Business Logic

### Slot Availability Algorithm (Most Critical!)
1. Input: service_id, staff_id, target_date
2. Query staff_schedules for the staff member's working hours on that day. If is_working=false, return empty.
3. Query staff_off_days to confirm the staff member is not on leave that day.
4. Generate all possible slot start times using {booking_interval}-minute intervals within working hours.
5. For each slot: check whether start_time to end_time (start + service duration) conflicts with:
   - Existing bookings (status IN ('pending','confirmed')) — use SQL range overlap: NOT (end_time <= existing.start_time OR start_time >= existing.end_time)
   - Staff breaks (staff_breaks table)
6. Filter out past times (for today's date).
7. Return array of available slots: [{start: '10:00', end: '10:45'}, ...]

### Double-Booking Prevention
- Either use an EXCLUDE constraint on bookings table, or use SELECT ... FOR UPDATE row locking in Server Action before INSERT.
- The booking creation Server Action MUST execute inside a transaction: lock-check-then-insert.

### Cancellation Policy
- Cannot cancel within cancellation_hours before appointment time.
- Cancellation automatically frees the slot.
- Log cancel reason to booking_events.

### Customer Tracking
- On each booking completion (status → completed): update customers.visit_count +1, total_spend + booking.total_price, last_visit_at via Server Action or Supabase trigger.

---

## 3. Pages & UI/UX

### A. Customer Booking Page /book (public, no login required)
Use {primary_color} as accent. Clean white background.

**Step 1 — Select Service**
- Service card grid showing: service name (bilingual CN/EN), duration, price (RM)
- Click to select, highlight border

**Step 2 — Select Staff**
- Show staff members who can perform the selected service (avatar + name)
- Option: "No preference (any available staff)"

**Step 3 — Pick Date & Time**
- Calendar component to pick date (disable past dates and business-wide off days)
- After selecting date, show available time slot grid ({booking_interval}-minute intervals)
- Full slots greyed out and unclickable, available slots clickable

**Step 4 — Fill Contact Info**
- Name, phone (+60 prefix auto-filled), email (optional), notes
- Malaysian phone number validation

**Step 5 — Confirm Booking**
- Summary card: service, staff, date/time, price
- Confirm button
- On success: show booking reference number, "Add to Calendar" button (generate .ics file), simulated WhatsApp notification (display the message that would be sent)

### B. Admin Dashboard /admin (login required)

**B1. Dashboard /admin/dashboard**
- Today's appointments list (timeline style, sorted by time)
- Weekly calendar view (X-axis = days, Y-axis = time slots, one column per staff, bookings shown as colored blocks)
- Stat cards: today's bookings, this week's revenue, this month's revenue, new customers count

**B2. Revenue Reports /admin/revenue**
- By service: booking count and total revenue per service
- By staff: booking count and total revenue per staff member
- Date range picker
- CSV export

**B3. Booking Management /admin/bookings**
- Table: date, time, customer, service, staff, status
- Filters: date range, status, staff
- Actions: confirm, complete, cancel, mark as no-show

**B4. Service Management /admin/services**
- CRUD for services
- Assign which staff members can perform each service

**B5. Staff Management /admin/staff**
- CRUD for staff
- Edit weekly schedule per staff member (visual timetable editor)
- Manage break times and special off-days

**B6. Customer Management /admin/customers**
- Customer list: name, phone, visit count, total spend, last visit
- Sort by: spend amount, visit count
- Click to view full booking history for that customer

### C. Global UI Rules
- Language toggle at top: 中文 / English
- Currency always displayed as RM (Malaysian Ringgit)
- Phone numbers always in +60 format
- Mobile-first responsive design
- Booking calendar uses {booking_interval}-minute minimum units

---

## 4. Sample Data

Pre-populate with this demo data (Malaysian hair salon):

**Business**: {business_name}, {business_type}, operating {operating_hours}

**Staff ({num_staff} members)**:
1. Ah Mei 阿美 — Senior Stylist, Mon-Sat 10:00-19:00
2. Jason 杰森 — Stylist, Tue-Sat 11:00-20:00
3. Siti — Nail Artist & Wash Assistant, Mon-Fri 10:00-18:00

**Services**:
- Ladies Cut & Blow 女士洗剪吹 — 60min — RM68 — Ah Mei, Jason
- Men's Cut 男士理发 — 30min — RM38 — Ah Mei, Jason
- Hair Colouring 染发 — 120min — RM180 — Ah Mei, Jason
- Scalp Treatment 头皮护理 — 45min — RM88 — Ah Mei, Siti
- Manicure 美甲 — 45min — RM58 — Siti

**Sample Bookings**: Generate 5 bookings for this week covering different staff, services, and statuses (confirmed, completed, pending, cancelled, no_show).

---

## 5. Implementation Priority
1. Database schema + RLS policies + seed data
2. Customer booking flow (5 steps)
3. Slot availability API
4. Admin dashboard
5. Staff schedule management
6. Customer history & revenue reports
7. Calendar file export + simulated WhatsApp notification