Meeting Room Booking System

You are a full-stack developer specialising in office management systems for Malaysian SMEs. Build a complete Meeting Room Booking System for "{{company_name}}" that replaces WhatsApp-based room booking and sticky notes on doors.

## Tech Stack
- Next.js 14 (App Router) + TypeScript
- Tailwind CSS + shadcn/ui component library
- Supabase (PostgreSQL + Auth + Row Level Security)
- Vercel deployment
- date-fns for date handling, custom calendar grid component

## Database Schema (Supabase / PostgreSQL)

### Table 1: rooms
sql
create table rooms (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  capacity int not null,
  floor text,
  description text,
  photo_url text,
  is_active boolean default true,
  has_projector boolean default false,
  has_whiteboard boolean default false,
  has_video_conf boolean default false,
  has_phone boolean default false,
  created_at timestamptz default now()
);
create index idx_rooms_is_active on rooms(is_active);


### Table 2: bookings
sql
create table bookings (
  id uuid primary key default gen_random_uuid(),
  room_id uuid references rooms(id) on delete cascade,
  booker_name text not null,
  booker_email text not null,
  title text not null,
  start_time timestamptz not null,
  end_time timestamptz not null,
  attendee_count int default 1,
  attendee_names text[],
  status text default 'confirmed',           -- confirmed / tentative / cancelled
  recurrence text default 'none',            -- none / daily / weekly / monthly
  recurrence_end_date date,
  parent_booking_id uuid references bookings(id),
  checked_in_at timestamptz,
  notes text,
  created_at timestamptz default now()
);
create index idx_bookings_room_id on bookings(room_id);
create index idx_bookings_start_time on bookings(start_time);
create index idx_bookings_status on bookings(status);
create index idx_bookings_booker_email on bookings(booker_email);


### Table 3: visitors
sql
create table visitors (
  id uuid primary key default gen_random_uuid(),
  booking_id uuid references bookings(id) on delete cascade,
  name text not null,
  company text,
  email text,
  phone text,
  id_number text,
  checked_in_at timestamptz,
  created_at timestamptz default now()
);
create index idx_visitors_booking_id on visitors(booking_id);


### Table 4: booking_rules
sql
create table booking_rules (
  id uuid primary key default gen_random_uuid(),
  max_duration_hours int default 4,
  advance_booking_days int default 30,
  no_show_release_minutes int default 15,
  allow_recurring boolean default true,
  updated_at timestamptz default now()
);


### RLS Policies
- rooms: anon role can SELECT where is_active = true
- bookings: anon can INSERT and SELECT; UPDATE/DELETE restricted to authenticated users or the original booker matched by booker_email
- visitors: same policy as bookings
- Always wrap auth checks as (select auth.uid()) — never bare auth.uid() — to prevent per-row function calls

## Core Features to Build

### 1. Weekly Calendar Grid
- X-axis: time slots across {{operating_hours}}, 30-minute intervals
- Y-axis: room rows — {{rooms}}
- Colour coding: blue = confirmed, yellow = tentative, grey = blocked, green = available
- Hover tooltip: meeting title, booker name, time range, attendee count
- Click any empty slot to open New Booking form pre-filled with that room and time
- Navigation arrows for previous/next week, Today button

### 2. Booking Creation with Conflict Detection
- Form fields: room selector, date, start/end time, meeting title, booker name and email, attendee count, equipment checklist, notes
- Before submit, run conflict query: SELECT count(*) FROM bookings WHERE room_id = $1 AND status != 'cancelled' AND start_time < $end_time AND end_time > $start_time
- If conflict found: show alert and suggest alternative available rooms with similar capacity on the same floor
- Enforce max duration rule from booking_rules (default {{booking_rules}} hours)
- Validate end_time > start_time and both within operating hours

### 3. Room Filtering
- Filter panel: minimum capacity slider, equipment checkboxes (projector, whiteboard, video conferencing, phone), floor selector
- Quick filter: "Available Now" shows rooms free for the next 60 minutes
- Filter state persisted in URL search params for shareability

### 4. Recurring Bookings
- Recurrence options: none, daily, weekly, monthly
- Set recurrence end date
- On submit: batch-check every occurrence for conflicts, display a conflict summary list and ask user to confirm or skip conflicting dates
- On edit: prompt "Edit this booking only" or "Edit all future bookings in this series"
- Store series link via parent_booking_id

### 5. Door Display Tablet View (Kiosk Mode)
- Route: /room/[room-id]/display
- Full-screen, large typography layout — no login required
- Show current booking: title, booker, time remaining as a progress bar
- Show next booking: title, start time, booker
- If room is free: green background, "Available" in large text, list of today's remaining bookings
- Auto-refresh every 60 seconds using a lightweight polling or Supabase Realtime subscription
- Designed for 10-inch landscape tablet mounted beside the door

### 6. Visitor Pre-Registration
- When creating a booking, add external visitors: name, company, contact number
- Reception staff can search visitors by name or booking ID on arrival day
- Mark visitor as checked in — record checked_in_at timestamp
- Visitor log retained for security audit purposes

### 7. Equipment Reservation
- Equipment checkboxes tied to each booking
- If the selected room lacks a requested item, system checks movable equipment inventory and reserves it
- Equipment availability shown as part of room filter results

### 8. Cancellation and Modification with Notification
- Anyone can cancel via booking ID or email magic link (no login required for self-service)
- Modification re-triggers conflict detection
- On cancel or modify: send email to booker and all listed attendees via Supabase Edge Function + Resend
- Cancellation must be at least 30 minutes before start time (admins can override)

### 9. Auto-Release (No-Show)
- Supabase Edge Function on a cron schedule (every 5 minutes): scan bookings that have started but have no checked_in_at value
- If start_time + no_show_release_minutes < now(): set status = 'cancelled', free the room immediately
- Send notification email to booker: "Your booking was auto-released due to no check-in"
- Log the auto-release event for analytics

## UI/UX Design Specifications

### Colour Palette
- Navigation bar: dark navy #0F172A, white text
- Page background: white; card borders: slate-200
- Yellow accent #FCD34D: primary CTA buttons, selected state highlight, today column indicator
- Status pills: blue-100/blue-700 (confirmed), yellow-100/yellow-700 (tentative), slate-100/slate-500 (cancelled)
- Typography: Inter for English/numbers, Noto Sans SC for Chinese labels

### Page Structure
- /booking: main weekly calendar view + top stats bar (Rooms Available Now X/Y, Today's Bookings N, Starting Soon N)
- /booking/new: full booking form with live conflict check
- /booking/[id]: booking detail, check-in button, visitor list, cancel/edit actions
- /room/[id]/display: kiosk door display view
- /admin: protected admin area (Supabase Auth)
- /admin/rooms: room CRUD, photo upload via Supabase Storage
- /admin/analytics: utilisation dashboard

### Responsive Behaviour
- Desktop (lg+): sidebar filter panel + full calendar grid
- Mobile: vertical list grouped by date, collapsible day sections, floating "Book Now" button fixed at bottom

## Analytics Dashboard (/admin/analytics)

Build with parallel Supabase queries using Promise.all — no sequential waterfalls. Display:
- Room utilisation rate per room: booked hours / total available hours this month (as percentage bar)
- Weekly peak hours heatmap (hour of day × day of week, intensity = booking count)
- Most used and least used room ranking
- Average meeting duration
- No-show rate (auto-released bookings / total bookings)
- All charts use Recharts or shadcn/ui chart primitives

## Business Rules Summary
1. No double-booking enforced at database level with a unique partial index and at the API layer
2. Maximum single booking duration: {{booking_rules}} hours (read from booking_rules table)
3. Bookings allowed up to 30 days in advance
4. Auto-release rooms after 15 minutes of no check-in
5. Recurring bookings checked for conflicts on every occurrence before saving
6. Cancellations must be at least 30 minutes before start (admin bypass allowed)
7. Operating hours: {{operating_hours}} — reject bookings outside this window

## Build Order
1. Generate full database migration SQL with all tables, indexes, and RLS policies
2. Build the weekly calendar grid component (rooms × time slots, colour-coded, hover tooltips)
3. Implement the booking creation form with real-time conflict detection
4. Add room filtering sidebar
5. Build the door display kiosk view
6. Add visitor pre-registration to the booking form
7. Implement recurring booking logic
8. Build the admin analytics dashboard

Start with step 1 (migration SQL) and step 2 (calendar grid) in the same response so I can see the data model and core UI together.