Production Order & Tracking System

You are an expert full-stack engineer. Build a complete Production Order & Tracking System for a Malaysian manufacturing SME to replace paid MES/ERP software or Excel-based tracking. Use Next.js 14 (App Router) + TypeScript + Tailwind CSS + shadcn/ui + Supabase (PostgreSQL + Auth + RLS), deployed on Vercel.

Factory Name: {{factory_name}}
Production Stages: {{production_stages}}
Product Types: {{product_types}}
Work Shifts: {{work_shifts}}

---

## PART 1: DATABASE SCHEMA (Supabase PostgreSQL)

Create the following tables with RLS enabled on all:

**customers**
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- name text NOT NULL (company name, e.g. ABC Trading Sdn Bhd)
- contact_person text, phone text, email text, address text
- created_at timestamptz DEFAULT now()

**products**
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- sku text UNIQUE NOT NULL
- name_en text NOT NULL, name_zh text
- unit text DEFAULT 'pcs' (pcs / box / roll / kg)
- standard_lead_days integer DEFAULT 7
- notes text, created_at timestamptz DEFAULT now()

**materials** (raw materials / inventory)
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- sku text UNIQUE NOT NULL, name text NOT NULL
- unit text NOT NULL (roll / sheet / kg / litre / pcs)
- unit_cost numeric(10,4) DEFAULT 0 (RM cost per unit)
- stock_quantity numeric(12,4) DEFAULT 0
- reorder_level numeric(12,4) DEFAULT 0
- supplier_name text, created_at timestamptz DEFAULT now()

**bill_of_materials** (BOM — links product to required materials)
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- product_id uuid REFERENCES products(id) ON DELETE CASCADE
- material_id uuid REFERENCES materials(id)
- quantity_per_unit numeric(12,4) NOT NULL (material qty needed per finished product unit)
- unit text NOT NULL, notes text
- CREATE INDEX ON bill_of_materials(product_id)
- CREATE INDEX ON bill_of_materials(material_id)

**workers**
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- name text NOT NULL, employee_id text UNIQUE NOT NULL
- department text, shift text (Morning / Afternoon / Night)
- phone text, is_active boolean DEFAULT true
- created_at timestamptz DEFAULT now()

**production_orders** (the core table)
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- po_number text UNIQUE NOT NULL (auto-generated: PO-YYYY-NNNN)
- customer_id uuid REFERENCES customers(id)
- product_id uuid REFERENCES products(id)
- quantity integer NOT NULL (planned production quantity)
- quantity_good integer DEFAULT 0 (passed QC)
- quantity_rejected integer DEFAULT 0
- status text NOT NULL DEFAULT 'pending' (pending / in_progress / quality_check / completed / cancelled)
- priority text NOT NULL DEFAULT 'normal' (urgent / high / normal / low)
- due_date date NOT NULL
- planned_start date, actual_start timestamptz, actual_end timestamptz
- sale_amount numeric(12,2) (RM value of order)
- notes text
- created_by uuid REFERENCES auth.users(id)
- created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
- CREATE INDEX ON production_orders(status)
- CREATE INDEX ON production_orders(due_date)
- CREATE INDEX ON production_orders(customer_id)
- CREATE INDEX ON production_orders(product_id)

**production_stages** (one row per stage per order)
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- order_id uuid REFERENCES production_orders(id) ON DELETE CASCADE
- stage_name text NOT NULL (matches {{production_stages}} config)
- stage_order integer NOT NULL (1, 2, 3... for sequencing)
- status text NOT NULL DEFAULT 'pending' (pending / in_progress / completed / skipped)
- assigned_worker_id uuid REFERENCES workers(id)
- started_at timestamptz, completed_at timestamptz
- notes text
- CREATE INDEX ON production_stages(order_id)
- CREATE INDEX ON production_stages(status)

**materials_usage** (tracks planned vs actual material consumption per order)
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- order_id uuid REFERENCES production_orders(id)
- material_id uuid REFERENCES materials(id)
- planned_quantity numeric(12,4) (calculated from BOM × order quantity)
- actual_quantity numeric(12,4) DEFAULT 0
- unit text NOT NULL
- recorded_by uuid REFERENCES workers(id)
- recorded_at timestamptz DEFAULT now()

**quality_checks**
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- order_id uuid REFERENCES production_orders(id)
- stage_id uuid REFERENCES production_stages(id)
- check_type text (incoming / in_process / final)
- result text NOT NULL (pass / fail / rework)
- good_quantity integer DEFAULT 0
- rejected_quantity integer DEFAULT 0
- rework_quantity integer DEFAULT 0
- defect_notes text
- checked_by uuid REFERENCES workers(id)
- checked_at timestamptz DEFAULT now()

**shipments**
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- order_id uuid REFERENCES production_orders(id)
- scheduled_date date, actual_date date
- delivery_method text (Self-Collect / Courier / Lorry)
- tracking_number text, recipient_name text, notes text
- created_at timestamptz DEFAULT now()

RLS Policies:
- All tables: authenticated users can SELECT / INSERT / UPDATE / DELETE
- production_orders, customers, products: anon role can SELECT (for floor worker read-only view)
- Wrap all auth.uid() checks as (SELECT auth.uid()) for performance (avoids per-row function calls)

---

## PART 2: CORE FEATURES

### 2.1 Production Order Creation (Server Action)
- Form fields: customer, product, quantity, due_date, priority, planned_start, sale_amount, notes
- On product selection: auto-fetch BOM from bill_of_materials and calculate required materials (quantity × quantity_per_unit for each material)
- Display materials checklist: show planned_quantity vs current stock_quantity; highlight in red if stock < planned_quantity
- Auto-generate PO number: query MAX(po_number) for current year, increment, format as PO-YYYY-NNNN
- On submit: create production_orders row + create production_stages rows (one per stage in {{production_stages}}, with stage_order 1,2,3...) + create materials_usage rows with planned quantities
- Suggest due_date = planned_start + standard_lead_days when product is selected
- Use react-hook-form + zod for validation; all errors shown inline

### 2.2 Kanban Board (Client Component with Realtime)
- Columns = {{production_stages}} (one column per stage)
- Each order card shows: po_number, product name, customer name, quantity, due_date, priority color strip (urgent=red / high=orange / normal=blue / low=gray), progress bar (completed stages / total stages × 100%), overdue badge if due_date < today
- Drag-and-drop using @dnd-kit/core: dragging a card to next column calls a Server Action that sets current stage completed_at = now(), next stage started_at = now(), updates production_orders.status
- Enable Supabase Realtime on production_orders and production_stages so multiple users see live updates
- Click any card to open a slide-over Sheet panel (order detail — see 2.3)
- Column header shows stage name + count badge

### 2.3 Order Detail Panel (Sheet / Slide-over)
- Full order info: customer, product, quantity, priority badge, status badge, sale amount (RM)
- Stage timeline: vertical stepper showing each production stage with status icon, assigned worker name, started_at / completed_at timestamps
- BOM materials list: material name, planned qty, actual qty (editable inline), variance highlighted
- Quality checks log: table of all QC records for this order (date, stage, result, good/rejected/rework qty, notes)
- Shipment info: scheduled date, actual date, delivery method, tracking number
- Action buttons: [Update Stage Status] [Record Quality Check] [Schedule Shipment] [Print Work Order]
- Print Work Order generates a printer-friendly page with all order details, BOM, and stage checklist

### 2.4 Material Requirements & Stock Check
- When viewing or creating an order, show a materials panel:
  - Material name | Unit | Planned Qty | Current Stock | Gap (Stock - Planned)
  - Gap negative = red highlight + warning icon
- Dashboard widget: count of active orders that have at least one under-stocked material
- After order completion: deduct actual_quantity from materials.stock_quantity via Server Action

### 2.5 Worker Assignment
- Each production_stages row can have assigned_worker_id
- In order detail panel, each stage row has a worker dropdown (filtered by shift if shift context is set)
- Worker list page (/admin/workers): shows each worker's name, shift, current active stage assignments count
- Simplified floor worker view (/floor): worker selects their name from a list, sees their assigned stages, taps [Start] / [Complete] to update stage status — large touch-friendly buttons, no login required for read/update own stages

### 2.6 Quality Check Recording
- Record QC dialog: select order, select stage, check_type, enter good_qty, rejected_qty, rework_qty, defect_notes
- If result = rework: reset that stage status back to 'in_progress' and add a note
- Auto-calculate yield rate = good_qty / (good_qty + rejected_qty) × 100% and display
- Update production_orders.quantity_good and quantity_rejected aggregates
- QC records are immutable once saved (append-only for audit trail)

### 2.7 Production Gantt Timeline View
- Show next 14 days as columns, each active production order as a row
- Bar spans from planned_start to due_date; color = status (blue=in progress, orange=due within 3 days, red=overdue, green=completed)
- Hover tooltip: po_number, product, customer, current stage
- Toggle between 14-day and monthly view
- Built with pure Tailwind CSS grid (no heavy Gantt library needed for v1)

### 2.8 Shipment Scheduling
- When order enters the final stage ('Ready to Ship' or last stage in {{production_stages}}), show a prominent [Schedule Shipment] prompt
- Shipment form: scheduled_date, delivery_method (dropdown), recipient_name, tracking_number, notes
- Shipments calendar view: week/month grid showing scheduled and actual shipment dates
- Completing a shipment sets production_orders.status = 'completed' and actual_end = now()

---

## PART 3: UI/UX DESIGN SPEC

Color palette: dark navy nav (#0F172A), white content area, yellow accent (#FCD34D) for CTAs and highlights, slate-600 body text.

Top Navigation:
- Left: factory logo placeholder + {{factory_name}}
- Center: nav links — Dashboard / Kanban / Orders / Gantt / Shipments / Analytics / Settings
- Right: notification bell (badge = count of overdue orders) + current user name

Dashboard (6 KPI cards in a 3×2 grid):
1. Active Orders (count with status in_progress)
2. Due Today (orders with due_date = today)
3. On-Time Delivery Rate this week (% of completed orders delivered on or before due_date)
4. Today's Yield Rate (avg yield across all QC checks today)
5. Low Stock Alerts (count of materials below reorder_level)
6. This Month's Output (sum of quantity_good across completed orders this month)

Kanban columns: rounded white cards with left-side priority color border, yellow progress bar fill, red overdue badge, drag handle icon top-right of card.

Mobile / floor worker view:
- Single-column list view; swipe left/right to change stage filter
- Cards show large text for product name and PO number
- [Start Stage] and [Complete Stage] are full-width yellow buttons at card bottom
- Minimal navigation; no admin features visible

---

## PART 4: BUSINESS RULES & AUTOMATION

1. PO Number generation: SELECT COALESCE(MAX(CAST(SPLIT_PART(po_number, '-', 3) AS INT)), 0) + 1 FROM production_orders WHERE po_number LIKE 'PO-{year}-%'; format result as zero-padded 4-digit string
2. Overdue detection: flag any order where due_date < CURRENT_DATE AND status NOT IN ('completed', 'cancelled') — show throughout UI with red indicators
3. Yield rate formula: yield_rate = quantity_good / NULLIF(quantity_good + quantity_rejected, 0) * 100
4. Stage advancement notification: after dragging/updating a stage to completed, display a toast notification: "PO-XXXX has moved to: {next_stage_name}"
5. Stock deduction: when production_orders.status changes to 'completed', run UPDATE materials SET stock_quantity = stock_quantity - actual_quantity FROM materials_usage WHERE order_id = $1
6. Priority color system: urgent=#EF4444 (red), high=#F97316 (orange), normal=#3B82F6 (blue), low=#9CA3AF (gray) — applied consistently as left border on cards, badge background in lists
7. Lead time suggestion: on product select in order form, set due_date = today + standard_lead_days
8. BOM shortage warning: if any material has (stock_quantity - planned_quantity) < 0, show a yellow alert banner on the order creation page and a dashboard widget count

---

## PART 5: ANALYTICS & REPORTS PAGE

All reports support date range filter (today / this week / this month / custom range). Use recharts library for charts.

- **Production Output Report**: daily/weekly bar chart of quantity_good produced, grouped by product type. Summary table with totals.
- **On-Time Delivery Rate**: line chart showing % of orders completed on time per week. KPI card with current month rate.
- **Quality Report**: yield rate per product (bar chart), defect reason distribution (pie chart from defect_notes categories), rework frequency table per stage.
- **Material Consumption Report**: table of material name, planned total, actual total, variance, cost variance (RM). Line chart of top 5 materials by usage over time.
- **Worker Efficiency Report**: table of worker name, stages completed, avg stage duration (hours), orders touched.
- All report tables have [Export CSV] button that downloads data as a CSV file using client-side Blob.

---

## PART 6: ADMIN PANEL (/admin — requires Supabase Auth login)

Routes:
- /admin → redirect to /admin/orders
- /admin/orders → full table of all orders with filters (status, priority, date range, customer), [New Order] button, row actions (Edit, Cancel, View)
- /admin/products → product list + BOM editor (add/remove materials and quantities per product)
- /admin/materials → raw materials inventory table with [Adjust Stock] inline action, reorder level config
- /admin/customers → customer CRM table, add/edit customer drawer
- /admin/workers → worker roster, add/edit worker, show current assignments
- /admin/analytics → full analytics page (Part 5)
- /admin/settings → configure production stage names, shift times, PO number prefix, factory name

Admin layout: sidebar navigation + top bar with logged-in user email + logout button. Protect all /admin routes by checking Supabase Auth session in middleware.ts; redirect to /admin/login if unauthenticated.

---

## PART 7: TECHNICAL IMPLEMENTATION NOTES

- Server Components for all data-display pages (Kanban initial load, order list, analytics)
- Client Components for: KanbanBoard (drag-and-drop + realtime), OrderDetailSheet, QualityCheckDialog, ShipmentForm, all admin forms
- Use Promise.all for all dashboard data fetches to avoid sequential waterfalls
- Supabase Realtime: subscribe to production_orders and production_stages on the Kanban page for live updates
- All Server Actions return { success: boolean; data?: T; error?: string } — never throw, always return
- Form validation: react-hook-form + zod schemas co-located with their form components
- File structure follows CLAUDE.md conventions exactly
- Use cn() from lib/utils for all conditional Tailwind class merging
- Font: Inter for Latin text, Noto Sans SC for any Chinese labels — loaded via next/font in lib/fonts.ts
- Error boundaries: error.tsx at app root and app/admin level; loading.tsx with Skeleton placeholders on all main routes
- Generate database migration SQL file at supabase/migrations/001_production_system.sql covering all CREATE TABLE, CREATE INDEX, and RLS policy statements

Build module by module in this order: (1) Database migration SQL, (2) Supabase client/server/admin lib files, (3) TypeScript types, (4) Server Actions, (5) Admin CRUD pages, (6) Kanban board, (7) Dashboard, (8) Analytics. Confirm completion of each module before proceeding.