Pharmacy Inventory & Prescription System

You are a professional full-stack software engineer specialising in building compliant management systems for Malaysian community pharmacies. Build a complete Pharmacy Inventory & Prescription Management System to the following specification.

## Role & Context

This system is designed for Malaysian community pharmacies and must comply with:
- Poisons Act 1952 (and amendments) — poison group classification and record-keeping
- Ministry of Health (KKM) pharmacy licensing and record requirements
- Sale of Drugs Act 1952
- Control of Drugs and Cosmetics Regulations 1984

Pharmacy name: {{pharmacy_name}}
Drug categories: {{drug_categories}}
Poison categories: {{poison_categories}}
Expiry alert threshold: {{expiry_alert_days}} days

## Tech Stack

- **Framework**: Next.js 14 (App Router) + TypeScript
- **Styling**: Tailwind CSS + shadcn/ui (new-york style, light mode)
- **Database**: Supabase (PostgreSQL + Row Level Security)
- **Auth**: Supabase Auth (pharmacist login)
- **Deployment**: Vercel
- **Fonts**: Inter + Noto Sans SC via next/font
- **Icons**: lucide-react

## Database Schema

### 1. drugs
sql
CREATE TABLE drugs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(200) NOT NULL,
  name_zh VARCHAR(200),
  generic_name VARCHAR(200),
  brand VARCHAR(200),
  category VARCHAR(50) NOT NULL CHECK (category IN ('prescription','otc','supplement','medical_device','traditional')),
  poison_group VARCHAR(10) CHECK (poison_group IN ('A','B','C','D','none')),
  dosage_form VARCHAR(100),
  strength VARCHAR(100),
  unit VARCHAR(50),
  barcode VARCHAR(100) UNIQUE,
  reorder_level INTEGER DEFAULT 10,
  reorder_quantity INTEGER DEFAULT 50,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_drugs_category ON drugs(category);
CREATE INDEX idx_drugs_poison_group ON drugs(poison_group);
CREATE INDEX idx_drugs_barcode ON drugs(barcode);
CREATE INDEX idx_drugs_fts ON drugs USING GIN(to_tsvector('simple', name || ' ' || COALESCE(generic_name,'')));


### 2. suppliers
sql
CREATE TABLE suppliers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(200) NOT NULL,
  contact_person VARCHAR(100),
  phone VARCHAR(20),
  email VARCHAR(100),
  address TEXT,
  license_number VARCHAR(100),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);


### 3. drug_inventory (batch-level)
sql
CREATE TABLE drug_inventory (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  drug_id UUID NOT NULL REFERENCES drugs(id),
  batch_number VARCHAR(100) NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0,
  expiry_date DATE NOT NULL,
  cost_price DECIMAL(10,2) NOT NULL,
  selling_price DECIMAL(10,2) NOT NULL,
  supplier_id UUID REFERENCES suppliers(id),
  received_date DATE NOT NULL DEFAULT CURRENT_DATE,
  purchase_order_number VARCHAR(100),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(drug_id, batch_number)
);
CREATE INDEX idx_inventory_drug_id ON drug_inventory(drug_id);
CREATE INDEX idx_inventory_expiry ON drug_inventory(expiry_date);
CREATE INDEX idx_inventory_supplier ON drug_inventory(supplier_id);


### 4. patients
sql
CREATE TABLE patients (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name VARCHAR(200) NOT NULL,
  ic_number VARCHAR(20) UNIQUE NOT NULL,
  date_of_birth DATE,
  phone VARCHAR(20),
  email VARCHAR(100),
  address TEXT,
  allergies TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_patients_ic ON patients(ic_number);
CREATE INDEX idx_patients_fts ON patients USING GIN(to_tsvector('simple', full_name));


### 5. prescriptions
sql
CREATE TABLE prescriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prescription_number VARCHAR(100) UNIQUE NOT NULL,
  patient_id UUID REFERENCES patients(id),
  patient_name VARCHAR(200) NOT NULL,
  patient_ic VARCHAR(20) NOT NULL,
  doctor_name VARCHAR(200) NOT NULL,
  doctor_reg_number VARCHAR(100),
  clinic_name VARCHAR(200),
  clinic_address TEXT,
  prescription_date DATE NOT NULL,
  dispensed_by VARCHAR(100),
  dispensed_at TIMESTAMPTZ,
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','dispensed','partial','cancelled')),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_rx_patient_ic ON prescriptions(patient_ic);
CREATE INDEX idx_rx_date ON prescriptions(prescription_date);
CREATE INDEX idx_rx_status ON prescriptions(status);


### 6. prescription_items
sql
CREATE TABLE prescription_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prescription_id UUID NOT NULL REFERENCES prescriptions(id) ON DELETE CASCADE,
  drug_id UUID NOT NULL REFERENCES drugs(id),
  drug_name VARCHAR(200) NOT NULL,
  quantity INTEGER NOT NULL,
  dosage_instructions TEXT,
  dispensed_quantity INTEGER DEFAULT 0,
  batch_number VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_rx_items_prescription ON prescription_items(prescription_id);
CREATE INDEX idx_rx_items_drug ON prescription_items(drug_id);


### 7. sales
sql
CREATE TABLE sales (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  receipt_number VARCHAR(100) UNIQUE NOT NULL,
  patient_id UUID REFERENCES patients(id),
  prescription_id UUID REFERENCES prescriptions(id),
  subtotal DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) DEFAULT 0,
  total DECIMAL(10,2) NOT NULL,
  payment_method VARCHAR(20) CHECK (payment_method IN ('cash','card','ewallet','insurance')),
  served_by VARCHAR(100),
  sold_at TIMESTAMPTZ DEFAULT NOW(),
  notes TEXT
);
CREATE INDEX idx_sales_date ON sales(sold_at);
CREATE INDEX idx_sales_receipt ON sales(receipt_number);


### 8. sale_items
sql
CREATE TABLE sale_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sale_id UUID NOT NULL REFERENCES sales(id) ON DELETE CASCADE,
  drug_id UUID NOT NULL REFERENCES drugs(id),
  drug_name VARCHAR(200) NOT NULL,
  batch_number VARCHAR(100),
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL
);
CREATE INDEX idx_sale_items_sale ON sale_items(sale_id);
CREATE INDEX idx_sale_items_drug ON sale_items(drug_id);


### 9. stock_movements
sql
CREATE TABLE stock_movements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  drug_id UUID NOT NULL REFERENCES drugs(id),
  batch_number VARCHAR(100),
  movement_type VARCHAR(20) CHECK (movement_type IN ('purchase','sale','adjustment','return','expired','transfer')),
  quantity_change INTEGER NOT NULL,
  reference_id UUID,
  reference_type VARCHAR(50),
  performed_by VARCHAR(100),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_movements_drug ON stock_movements(drug_id);
CREATE INDEX idx_movements_date ON stock_movements(created_at);


### 10. poison_book
sql
CREATE TABLE poison_book (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entry_date DATE NOT NULL DEFAULT CURRENT_DATE,
  drug_id UUID NOT NULL REFERENCES drugs(id),
  drug_name VARCHAR(200) NOT NULL,
  poison_group VARCHAR(5) NOT NULL,
  quantity INTEGER NOT NULL,
  patient_name VARCHAR(200),
  patient_ic VARCHAR(20),
  doctor_name VARCHAR(200),
  prescription_number VARCHAR(100),
  dispensed_by VARCHAR(100) NOT NULL,
  purpose VARCHAR(200),
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_poison_book_date ON poison_book(entry_date);
CREATE INDEX idx_poison_book_drug ON poison_book(drug_id);


### Row Level Security

Enable RLS on all tables. Public (anon) role gets SELECT on drugs only. All write operations and sensitive reads require authenticated users. Use `(SELECT auth.uid())` pattern (not bare `auth.uid()`) in every RLS policy to avoid per-row evaluation overhead.

## File Structure


src/
├── app/
│   ├── layout.tsx
│   ├── page.tsx                    # Redirects to /dashboard
│   ├── dashboard/page.tsx          # Main dashboard
│   ├── inventory/
│   │   ├── page.tsx                # Inventory overview
│   │   ├── [drugId]/page.tsx       # Drug detail + batch list
│   │   └── add/page.tsx            # Add/edit drug
│   ├── prescriptions/
│   │   ├── page.tsx                # Prescription list
│   │   ├── new/page.tsx            # New prescription
│   │   └── [id]/page.tsx           # Prescription detail
│   ├── pos/page.tsx                # Point-of-sale terminal
│   ├── expiry/page.tsx             # Expiry management
│   ├── poison-book/page.tsx        # Poison register
│   ├── reports/page.tsx            # KKM compliance reports
│   └── api/
│       ├── drugs/route.ts
│       ├── inventory/route.ts
│       ├── prescriptions/route.ts
│       ├── sales/route.ts
│       └── reports/route.ts
├── components/
│   ├── ui/                         # shadcn/ui primitives
│   ├── layout/
│   │   ├── sidebar.tsx
│   │   └── header.tsx
│   ├── inventory/
│   │   ├── drug-table.tsx          # Searchable, filterable table
│   │   ├── batch-list.tsx          # Expandable batch rows
│   │   ├── expiry-badge.tsx        # Colour-coded expiry chip
│   │   └── stock-form.tsx          # Add/receive stock form
│   ├── prescription/
│   │   ├── rx-form.tsx             # Prescription entry form
│   │   ├── patient-search.tsx      # IC number lookup
│   │   └── dispensing-panel.tsx    # Dispense against Rx
│   ├── pos/
│   │   ├── pos-interface.tsx
│   │   ├── cart.tsx
│   │   └── receipt.tsx
│   └── dashboard/
│       ├── stats-cards.tsx
│       ├── expiry-alerts.tsx
│       └── sales-chart.tsx
├── lib/
│   ├── supabase/
│   │   ├── client.ts               # Browser client
│   │   ├── server.ts               # Server client (cookies)
│   │   └── admin.ts                # Service role client
│   ├── fefo.ts                     # FEFO batch allocation
│   ├── barcode.ts                  # Barcode lookup helper
│   └── reports.ts                  # KKM report generators
└── types/pharmacy.ts


## Core Feature Implementation

### Feature 1 — Drug Database & Poison Classification

The drug form must include a poison group selector (A / B / C / D / None). On selection:
- Group A: system shows warning "Prescription required — poison book entry will be created automatically"
- Group B: system shows "Pharmacist supervision required"
- Group C/D: standard flow

Poison group is stored on the `drugs` table and propagated to every sale and dispensing event.

### Feature 2 — FEFO Batch Management

Implement `lib/fefo.ts`:
typescript
export async function allocateBatchesFEFO(
  drugId: string,
  requiredQty: number,
  supabase: SupabaseClient
): Promise<BatchAllocation[]> {
  const { data: batches, error } = await supabase
    .from('drug_inventory')
    .select('id, batch_number, quantity, expiry_date')
    .eq('drug_id', drugId)
    .gt('quantity', 0)
    .gt('expiry_date', new Date().toISOString().split('T')[0])
    .order('expiry_date', { ascending: true });

  if (error) throw error;

  const allocations: BatchAllocation[] = [];
  let remaining = requiredQty;
  for (const batch of batches ?? []) {
    if (remaining <= 0) break;
    const take = Math.min(batch.quantity, remaining);
    allocations.push({ batchId: batch.id, batchNumber: batch.batch_number, quantity: take, expiryDate: batch.expiry_date });
    remaining -= take;
  }
  if (remaining > 0) throw new Error(`Insufficient stock: ${remaining} units short`);
  return allocations;
}


### Feature 3 — Expiry Colour Coding

Implement `components/inventory/expiry-badge.tsx` using `date-fns`:
- Expired (past): red destructive badge — "Expired"
- < 30 days: red badge — "X days left"
- 30–{{expiry_alert_days}} days: yellow/amber badge — "X days left"
- > {{expiry_alert_days}} days: green muted badge — formatted date

### Feature 4 — Prescription Entry

Rx form fields: Patient IC lookup (auto-fill name/DOB/allergies if exists), Doctor Name, MMC Registration Number, Clinic Name, Prescription Date, drug line items (drug search, quantity, dosage instructions). On save:
1. Create `prescriptions` record
2. Create `prescription_items` records
3. If any drug is Group A, auto-create `poison_book` entry

### Feature 5 — POS Terminal

Left panel: barcode scan input + drug search typeahead. Right panel: cart with quantity controls, FEFO batch auto-selected, subtotal per line. Bottom: payment method selector, total, print receipt button. Business rule: if cart contains Group A or B drug without a linked prescription, block checkout and prompt to attach Rx.

### Feature 6 — Low Stock & Expiry Alerts on Dashboard

Dashboard Server Component fetches in parallel via `Promise.all`:
- Total inventory value (SUM of qty * cost_price)
- Drugs below reorder_level
- Batches expiring within {{expiry_alert_days}} days
- Today's total sales (RM)

Display as four stat cards. Below: two tables — "Expiry Alerts" sorted by soonest expiry, "Low Stock" sorted by furthest below reorder level.

### Feature 7 — KKM Compliance Reports

Reports page generates:
1. **Monthly Poison Return** — Group A & B drugs: opening stock, purchases, sales, closing stock per drug
2. **Controlled Substance Dispensing Log** — all poison_book entries for selected month, printable as PDF via `window.print()`
3. **Stock Valuation Report** — all active batches with cost and retail value, grouped by category
4. **Expiry Disposal Report** — expired/near-expiry batches awaiting destruction sign-off

## Business Rules

1. Group A drugs: sale BLOCKED without linked prescription; poison_book entry auto-created on every dispensing
2. Group B drugs: checkout shows pharmacist confirmation dialog; pharmacist name recorded on sale
3. Expired batches: flagged read-only in inventory; cannot be added to cart; trigger disposal workflow
4. FEFO enforcement: system always pre-selects earliest-expiry batch; pharmacist can override with audit trail
5. Negative stock guard: if FEFO allocation cannot fulfil full quantity, throw error and show which batches are available
6. Receipt compliance: every receipt prints pharmacy name, registration number ({{pharmacy_name}}), pharmacist name and APC number, date/time, and GST/SST line if applicable

## UI / UX Specifications

### Colour Palette
- Nav background: `#0F172A` (dark navy)
- Content background: `#FFFFFF`
- Accent / primary CTA: `#FCD34D` (yellow-gold, used sparingly)
- Danger / low stock: `#EF4444`
- Warning / near-expiry: `#F59E0B`
- Success / healthy stock: `#10B981`
- Body text: `#1E293B` (slate-800)

### Layout
- Fixed left sidebar (240 px) with nav links: Dashboard, Inventory, Prescriptions, POS, Expiry, Poison Book, Reports, Suppliers, Settings
- Top header: pharmacy name, logged-in pharmacist, notifications bell (badge count = low stock + expiry alerts)
- Main content: full-width with 24 px padding

### Inventory Table
- Columns: Drug Name, Generic Name, Category, Poison Group (colour-coded chip), Total Stock, Reorder Level (red if below), Nearest Expiry (colour badge), Selling Price (RM), Actions
- Row expandable to show all batches with individual expiry dates
- Search by name/barcode, filter by category/poison group, sort by expiry
- Bulk export to CSV

## Deliverables

Generate all of the following with complete, runnable code:
1. All database migration SQL files with RLS policies and indexes
2. Complete Next.js 14 page and component files
3. Supabase client/server/admin initialisation in `lib/supabase/`
4. `lib/fefo.ts` with full TypeScript types
5. `types/pharmacy.ts` — all entity types, no `any`
6. `.env.example` with all required environment variables
7. `components/inventory/expiry-badge.tsx` with correct colour logic

All TypeScript must be strictly typed (no implicit `any`). All prices display in RM format (`RM 0.00`). All dates use Malaysian locale (`en-MY`). The system must be deployable to Vercel with zero configuration beyond environment variables.