Build an Inventory System in Lovable

Build a staff-only inventory management system. Stack: React + Vite + TypeScript + Tailwind + shadcn/ui (already scaffolded), React Router, Supabase JS client against Lovable Cloud.

## Database schema (create one migration)

Create 5 tables with RLS enabled on ALL of them:

1. `products` — id (uuid pk default gen_random_uuid()), sku (text unique not null), name (text not null), description (text), reorder_point (int not null default 0, check reorder_point >= 0), unit (text default 'each'), image_path (text, Storage path), archived (bool default false), created_at (timestamptz default now()).

2. `locations` — id (uuid pk default gen_random_uuid()), name (text not null), address (text), created_at (timestamptz default now()).

3. `stock` — id (uuid pk default gen_random_uuid()), product_id (uuid not null references products(id) on delete cascade), location_id (uuid not null references locations(id) on delete cascade), qty (int not null default 0, CHECK (qty >= 0)), updated_at (timestamptz default now()). UNIQUE(product_id, location_id).

4. `movements` — id (uuid pk default gen_random_uuid()), product_id (uuid not null references products(id)), location_id (uuid not null references locations(id)), delta (int not null), reason (text not null, check reason in ('receipt','sale','transfer_in','transfer_out','adjustment','return','damage')), reference (text), actor_id (uuid references auth.users(id)), created_at (timestamptz default now()).

5. `low_stock_alerts` — id (uuid pk default gen_random_uuid()), product_id (uuid references products(id)), location_id (uuid references locations(id)), triggered_at (timestamptz default now()), resolved_at (timestamptz), qty_at_trigger (int), reorder_point_at_trigger (int).

Create a SECURITY DEFINER plpgsql function `has_role(check_role text) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER STABLE` that reads `(auth.jwt() -> 'app_metadata' ->> 'role')` and returns whether it matches. NEVER use SQL-language — it gets inlined.

Create the critical SECURITY DEFINER plpgsql function `record_movement(p_product_id uuid, p_location_id uuid, p_delta int, p_reason text, p_reference text DEFAULT NULL) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS`:
```sql
BEGIN
  -- Insert the movement row
  INSERT INTO movements(product_id, location_id, delta, reason, reference, actor_id)
  VALUES (p_product_id, p_location_id, p_delta, p_reason, p_reference, auth.uid());

  -- Atomically UPSERT stock with row-level lock on existing row
  INSERT INTO stock(product_id, location_id, qty, updated_at)
  VALUES (p_product_id, p_location_id, p_delta, now())
  ON CONFLICT (product_id, location_id) DO UPDATE
    SET qty = stock.qty + p_delta,
        updated_at = now();
  -- The CHECK (qty >= 0) on the stock table rejects oversells at DB level

  -- Alert if newly below reorder_point and no open alert exists
  IF (SELECT qty FROM stock WHERE product_id = p_product_id AND location_id = p_location_id) <
     (SELECT reorder_point FROM products WHERE id = p_product_id)
  THEN
    IF NOT EXISTS (
      SELECT 1 FROM low_stock_alerts
      WHERE product_id = p_product_id AND location_id = p_location_id AND resolved_at IS NULL
    ) THEN
      INSERT INTO low_stock_alerts(product_id, location_id, qty_at_trigger, reorder_point_at_trigger)
      SELECT p_product_id, p_location_id,
             (SELECT qty FROM stock WHERE product_id = p_product_id AND location_id = p_location_id),
             (SELECT reorder_point FROM products WHERE id = p_product_id);
    END IF;
  ELSE
    -- Auto-resolve alert if back above reorder_point
    UPDATE low_stock_alerts
    SET resolved_at = now()
    WHERE product_id = p_product_id AND location_id = p_location_id AND resolved_at IS NULL;
  END IF;
END;
```

RLS policies:
- products: SELECT and UPDATE by has_role('staff') OR has_role('admin'); INSERT/DELETE by has_role('admin') only
- locations: SELECT by has_role('staff') OR has_role('admin'); INSERT/DELETE by has_role('admin')
- stock: SELECT by has_role('staff') OR has_role('admin'); NO INSERT/UPDATE/DELETE policies for authenticated users — all writes go through record_movement() RPC only
- movements: SELECT by has_role('staff') OR has_role('admin'); INSERT only via record_movement() function (no direct INSERT policy)
- low_stock_alerts: SELECT by has_role('staff') OR has_role('admin'); INSERT/UPDATE by service role only (handled inside record_movement)

Seed data: insert 5 products (different SKUs and units), 2 locations (e.g., 'Main Warehouse', 'Retail Store'), and opening stock for each product × location via calls to record_movement() with reason='receipt'.

## Layout

Create `src/layouts/AppLayout.tsx` — fixed left sidebar (220px) with nav links (Stock, Products, Locations, Movements, Alerts, Import, Settings) and a bottom user menu. Main area scrolls. Location filter pill in top-right header (defaults to 'All locations').

Create `src/components/StaffGuard.tsx` — calls supabase.auth.getUser(), reads app_metadata.role, shows a centered spinner while resolving, redirects to /unauthorized if role NOT IN ('admin','staff').

Create `src/components/AdminGuard.tsx` — same pattern but requires has_role('admin'); used for Products edit, Locations, Import, Settings.

## Pages and routes

Create these routes:
- `/stock` — current stock matrix grouped by product × location. QuantityBadge shows green if qty >= reorder_point, amber if 1 ≤ qty < reorder_point, red if qty = 0. Location filter pill at top filters the matrix. Sortable by product name or qty.
- `/products` — product catalog table (sku, name, unit, reorder_point, archived toggle). Admin-only edit via Sheet: name, sku, price (not stored — display only), reorder_point, unit, image upload (Storage bucket 'product-images', 40×40 thumbnail in table).
- `/locations` — location list + Add location form. Admin-only.
- `/movements` — chronological ledger with filter by product, location, reason, and date range. Each row shows: product name, location, delta (green positive / red negative), reason badge, reference, actor, timestamp.
- `/alerts` — open (resolved_at IS NULL) and resolved tabs. Each alert shows product, location, qty_at_trigger vs reorder_point. 'Mark resolved' button updates resolved_at directly (staff can resolve manually, even though the RPC auto-resolves).
- `/import` — CSV file picker, parse client-side with PapaParse, show preview table (CSVImportPreview with green valid / red error rows), Confirm button calls import-csv Edge Function (wired in follow-up #2). Admin-only.
- `/settings` — default location picker + alert recipient emails list. Admin-only.
- `/unauthorized` — one-line message with Sign out button.

## Components

Create:
- `src/components/QuantityBadge.tsx` — pill with emerald-100/emerald-700 (good), amber-100/amber-700 (low), red-100/red-700 (zero). Accepts qty and reorderPoint props.
- `src/components/MovementForm.tsx` — shadcn Dialog. Fields: product picker (combobox), location picker (Select), delta with +/- buttons and numeric input, reason dropdown (receipt/sale/adjustment/return/damage), reference text. On submit, calls `supabase.rpc('record_movement', {p_product_id, p_location_id, p_delta, p_reason, p_reference})`. Shows current qty for the selected product+location below the form.
- `src/components/StockTable.tsx` — shadcn Table in a 32px-row compact layout. Columns: SKU, Product Name, Location, Qty (rendered as QuantityBadge), Reorder Point, Last Movement date. Sortable headers.
- `src/components/CSVImportPreview.tsx` — renders parsed CSV rows as a table with validation error highlights (red row background + error column for invalid SKU, missing delta, invalid reason).
- `src/hooks/useRecordMovement.ts` — TanStack Query useMutation that calls record_movement RPC and invalidates the stock and movements queries on success.

## Styling

Light mode default with optional dark mode via shadcn ThemeProvider. Primary color: slate-900. Quantity badges as described above. 32px compact table rows. Product images as 40×40 square thumbnails with object-cover.

Generate the migration first (confirm it includes the full record_movement function SQL), then the layout + guards, then pages in the order listed above.