# Saimpex Windows POS — Backend Development Planning Document

**Document Version:** 1.0  
**Date:** February 2026  
**Purpose:** Database schema planning for Windows POS integration with existing Saimpex e-commerce backend  
**Reference:** `saimpex-backend` (Laravel) at `/Users/sigosoft/saimpex/saimpex-backend`

---

## 1. Executive Summary

This document outlines the database structure required to complete the **Windows POS / CRM / ERP / HRM** application. The existing Saimpex backend serves the customer and delivery e-commerce app. The same database will be **extended** (not replaced) to support:

- **POS**: Shifts, held transactions, POS invoices, returns, split payments, credit sales, price levels
- **CRM**: Customer categories, zones, complaints, enhanced loyalty
- **ERP**: Suppliers, purchase orders, GRN, warehouses, stock transfers, stock adjustments
- **HRM**: Employees, attendance, payroll
- **Sync**: Offline queue, audit logs, permissions

**Approach:** Keep all existing tables and migrations. Add **new migrations** for alters and new tables only.

---

## 2. Current Database Structure (Existing E-commerce)

### 2.1 Tables Overview

| Table | Purpose |
|-------|---------|
| **Auth & Users** | |
| `roles` | Admin, Vendor, Product Manager, Sales Manager, Delivery Staff, Customer |
| `users` | All users (customers, vendors, delivery staff); links to roles |
| `addresses` | Customer addresses (user_id, address_line1/2, city, state, postal_code, lat/lng) |
| `country_codes` | Country calling codes |
| `otp_verifications` | OTP verification records |
| **Vendors & Subscriptions** | |
| `vendors` | Stores (vendor_type: 1=restaurant, 2=grocery); user_id, subscription_id |
| `subscriptions` | Vendor subscription plans |
| `vendor_subscription_logs` | Subscription history |
| `vendor_working_hours` | Store working hours |
| `vendor_leaves` | Store leave dates |
| **Products** | |
| `grocery_categories` | Product categories |
| `grocery_sub_categories` | Sub-categories |
| `grocery_menus` | Product definitions (name, description, image, unit_type_id) |
| `grocery_menu_items` | Product variants per vendor (price, stock, serial_number, barcode) |
| `restaurant_menus` | Restaurant item definitions |
| `restaurant_menu_items` | Restaurant item variants (price, stock) |
| `unit_type` | Units (e.g. piece, kg, carton) |
| `grocery_attributes`, `restaurant_attributes` | Product attributes |
| **Orders & Payments** | |
| `orders` | Customer orders (user_id, vendor_id, delivery_boy_id, order_type, status, payment_type) |
| `order_items` | Order line items (product_id, quantity, unit_price, total_price) |
| `order_status_logs` | Order status history |
| `order_durations` | Order timing |
| `payments` | Payment records (order_id, amount, payment_method, status) |
| `refunds` | Refund records (order_id, amount, reason) |
| **Cart & Baskets** | |
| `carts` | Shopping carts |
| `cart_items` | Cart line items |
| `baskets` | Basket products |
| `basket_items` | Basket composition |
| `basket_orders` | Basket order links |
| **Delivery** | |
| `delivery_boys` | Delivery staff (links to users) |
| `deliveries` | Delivery assignment per order |
| `driver_locations` | Real-time driver location |
| `delivery_payment_logs` | Cash collected by driver |
| `driver_salaries` | Driver salary records |
| `delivery_boy_earnings` | Driver earnings |
| `delivery_boy_payout` | Driver payout records |
| **Stock** | |
| `product_stock_history` | Generic stock movements (product_type, product_id, quantity, movement_type) |
| `grocery_item_stocklogs` | Grocery item stock logs |
| `restaurant_item_stocklogs` | Restaurant item stock logs |
| **CRM & Loyalty** | |
| `reward_point_logs` | Points earned/redeemed (user_id, order_id, points, type) |
| `customer_wallets` | Wallet balance per user |
| `customer_wallet_transactions` | Wallet transaction history |
| **Coupons & Misc** | |
| `coupons` | Discount codes |
| `coupon_usages` | Coupon usage tracking |
| `general` | Global settings (tax, delivery rates, reward rules) |
| **Support** | |
| `settings` | App settings |
| `contact_us` | Contact submissions |
| `faq`, `terms`, `privacy_policy`, `about` | Static content |
| `return_policy`, `shipping_policy` | Policy text |
| `vendor_payments` | Vendor payout records |
| **Infrastructure** | |
| `cache`, `jobs`, `sessions`, `password_reset_tokens` | Laravel defaults |
| `personal_access_tokens` | Sanctum API tokens |
| `notifications` | Laravel notifications |

### 2.2 Key Relationships

- **users** → `role_id` → roles  
- **orders** → `user_id` (customer), `vendor_id`, `delivery_boy_id` (user)  
- **order_items** → `order_id`, `product_id` (polymorphic: restaurant_menu_items / grocery_menu_items)  
- **payments** → `order_id`, `user_id`, `vendor_id`  
- **addresses** → `user_id`  
- **grocery_menu_items** / **restaurant_menu_items** → `grocery_id` / `restaurant_id` → vendors  
- **vendors** → `user_id` (store owner)

---

### 2.3 Product Structure: Menus + Menu Items (2-Table Model) — Verified

**Current saimpex-backend structure:**
- **grocery_menus:** category_id, sub_category_id, grocery_id, name_*, description_*, unit_type_id, image, approval_status
- **grocery_menu_items:** menu_id, grocery_id, serial_number, grocery_attribute_id, attribute_value, price, discount_price, stock, rating, status, available_status, approval_status
- **restaurant_menus:** category_id, restaurant_id, name_*, description_*, image, is_veg, approval_status
- **restaurant_menu_items:** menu_id, restaurant_id, serial_number, restaurant_attribute_id, attribute_value, price, discount_price, stock, rating, etc.

**Weight/volume (kg, gram, litre):** Use **attributes** — `grocery_attribute_id` / `restaurant_attribute_id` + `attribute_value` on menu_items. `unit_type` table is **not** used for these.

**Wholesale (Carton, Dozen):** Add separately on menu_items: `wholesale_unit` (string), `wholesale_quantity` (int), `wholesale_price` (decimal). No FK to `unit_type`.

---

### 2.4 `unit_type` Table — Existing, Not Used for Wholesale

**Location:** `2025_11_05_101917_create_unit_type_table.php`  
**Structure:** id, name_en, name_ar, name_fr, timestamps

`grocery_menus.unit_type_id` exists (unchanged). Weight/volume units (kg, gram, litre) are now treated as **attributes** via `grocery_attributes` / `restaurant_attributes`.

### 2.5 Additional Existing Tables Reference

**Stock Management:**
- `product_stock_history` - Generic stock movements (product_type, product_id, quantity, movement_type)
- `grocery_item_stocklogs` - Grocery item stock logs (grocery_menu_item_id, quantity, movement_type, user_id)
- `restaurant_item_stocklogs` - Restaurant item stock logs (restaurant_menu_item_id, quantity, movement_type, user_id)

**Customer Management:**
- `customer_wallets` - Wallet balance per user (user_id, balance)
- `customer_wallet_transactions` - Wallet transaction history (user_id, wallet_id, type, amount, order_id)
- `reward_point_logs` - Points earned/redeemed (user_id, order_id, points, type)

**Order Management:**
- `order_status_logs` - Order status history (order_id, status, user_id)
- `order_durations` - Order timing tracking
- `order_earnings` - Order earnings tracking

**Delivery:**
- `delivery_boy_earnings` - Driver earnings (delivery_boy_id, order_id, amount)
- `delivery_boy_payout` - Driver payout records
- `delivery_payment_logs` - Cash collected by driver

**Vendor:**
- `vendor_payments` - Vendor payout records (vendor_id, amount, payment_method, status)
- `vendor_subscription_logs` - Subscription history
- `vendor_working_hours` - Store working hours
- `vendor_leaves` - Store leave dates

**Product Attributes & Tags:**
- `grocery_attributes`, `restaurant_attributes` - Product attributes
- `grocery_category_attributes`, `restaurant_category_attributes` - Category-attribute mapping
- `grocery_tags`, `restaurant_tags` - Product tags
- `grocery_menu_tags`, `restaurant_menu_tags` - Menu-tag mapping

**Note:** These tables are already in use and should not be modified unless specifically required for POS/ERP/CRM functionality.

---

## 3. Required Alterations (Existing Tables)

**Note:** All alterations **add** columns only. No existing fields are changed or removed.

### 3.1 `users` — Add POS/CRM/HRM fields

**Migration:** `add_pos_crm_hrm_fields_to_users_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `customer_category` | enum('retail','wholesale','vip') | YES | Customer price tier (Retail/Wholesale/VIP) |
| `credit_limit` | decimal(12,2) | YES | Credit limit for on-account sales |
| `whatsapp` | string(20) | YES | WhatsApp number |
| `zone` | string(100) | YES | Zone/Area for delivery/segmentation |
| `employee_code` | string(50) | YES | Employee ID (for HRM) |
| `department` | string(100) | YES | Department (HRM) |
| `position` | string(100) | YES | Job position (HRM) |
| `branch_id` | foreignId | YES | FK to branches/warehouses (nullable) |
| `hire_date` | date | YES | Date of hire |
| `salary` | decimal(12,2) | YES | Base salary |
| `is_pos_cashier` | boolean | default 0 | Can operate POS |
| `manager_pin` | string(6) | YES | PIN for manager override (discount, stock override) |

```php
// Example migration snippet
Schema::table('users', function (Blueprint $table) {
    $table->enum('customer_category', ['retail', 'wholesale', 'vip'])->nullable()->after('points');
    $table->decimal('credit_limit', 12, 2)->nullable()->after('customer_category');
    $table->string('whatsapp', 20)->nullable()->after('mobile');
    $table->string('zone', 100)->nullable()->after('address');
    $table->string('employee_code', 50)->nullable();
    $table->string('department', 100)->nullable();
    $table->string('position', 100)->nullable();
    $table->foreignId('branch_id')->nullable()->after('position'); // FK added after branches table
    $table->date('hire_date')->nullable();
    $table->decimal('salary', 12, 2)->nullable();
    $table->boolean('is_pos_cashier')->default(false);
    $table->string('manager_pin', 6)->nullable();
});
```

---

### 3.2 `addresses` — Add zone and customer linkage

**Migration:** `add_pos_fields_to_addresses_table.php`

**Current `addresses` table structure:**
- user_id (bigInteger, unsigned)
- address_line1, address_line2, city, state, postal_code, country
- latitude, longitude
- is_default (tinyInteger, default 0)

**New fields to add:**

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `zone` | string(100) | YES | Zone/Area |
| `is_billing` | boolean | default 0 | Billing address flag |

```php
Schema::table('addresses', function (Blueprint $table) {
    $table->string('zone', 100)->nullable()->after('country');
    $table->boolean('is_billing')->default(false)->after('is_default');
});
```

---

### 3.3 `orders` — Support POS and App orders

**Migration:** `add_pos_fields_to_orders_table.php`

**Current `orders` table structure:**
- order_code, user_id, vendor_id, delivery_boy_id, order_type
- subtotal, discount, coupon_code, delivery_fee, tax, total
- payment_status, payment_type (enum '1','2'), payment_id
- notes, location, latitude, longitude
- status (integer 1-10)
- placed_at, delivered_at, cancelled_at, cancelled_by, cancelled_user
- distance_km, delivery_per_km_amount, delivery_per_order_amount
- read_status

**New fields to add:**

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `source` | enum('app','pos') | default 'app' | Order source: app or POS |
| `pos_shift_id` | foreignId | YES | FK to pos_shifts |
| `cashier_id` | foreignId | YES | User (cashier) who created POS order |
| `is_credit_sale` | boolean | default 0 | Sold on account |
| `cancelled_reason` | string(255) | YES | Reason for cancellation (Duplicate, Wrong amount, etc.) |
| `warehouse_id` | foreignId | YES | Warehouse/branch for POS stock deduction |

**Note:** `payment_type` is currently enum('1','2') for Cash/Online. Consider extending for Card, Mobile, Split. See `pos_invoice_payments` for split support.

```php
Schema::table('orders', function (Blueprint $table) {
    $table->enum('source', ['app', 'pos'])->default('app')->after('order_type');
    $table->foreignId('pos_shift_id')->nullable()->after('source')->constrained('pos_shifts')->nullOnDelete();
    $table->foreignId('cashier_id')->nullable()->after('pos_shift_id')->constrained('users')->nullOnDelete();
    $table->boolean('is_credit_sale')->default(false)->after('payment_status');
    $table->string('cancelled_reason', 255)->nullable()->after('cancelled_user');
    $table->foreignId('warehouse_id')->nullable()->after('vendor_id')->constrained('warehouses')->nullOnDelete();
});
```

---

### 3.4 `order_items` — Item-level discount, return tracking, sale unit (piece/carton)

**Migration:** `add_pos_fields_to_order_items_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `discount_amount` | decimal(10,2) | default 0 | Item-level discount |
| `discount_type` | enum('percent','fixed') | YES | Discount type |
| `tax_amount` | decimal(10,2) | default 0 | Tax for this line |
| `returned_quantity` | integer | default 0 | Quantity returned |
| `return_reason` | string(255) | YES | Reason for return (defective, wrong item, etc.) |
| **Wholesale sale unit** | | | |
| `sale_unit` | enum('base','wholesale') | default 'base' | Sold by base unit or wholesale unit |
| `sale_unit_name` | string(50) | YES | Display unit for invoice: 'Piece', 'Carton', 'Dozen', etc. |
| `quantity_in_base_units` | integer | YES | Total quantity in base units (for stock deduction). E.g. 2 cartons × 12 = 24 |

**Invoice display:** "Milk – 1 Carton" or "Milk – 12 Pieces" using `sale_unit` and `sale_unit_name`. Stock deduction uses `quantity_in_base_units`.

```php
Schema::table('order_items', function (Blueprint $table) {
    $table->decimal('discount_amount', 10, 2)->default(0)->after('total_price');
    $table->enum('discount_type', ['percent', 'fixed'])->nullable()->after('discount_amount');
    $table->decimal('tax_amount', 10, 2)->default(0)->after('discount_type');
    $table->integer('returned_quantity')->default(0)->after('quantity');
    $table->string('return_reason', 255)->nullable()->after('returned_quantity');
    $table->enum('sale_unit', ['base', 'wholesale'])->default('base')->after('return_reason')->comment('base=retail unit, wholesale=carton/dozen');
    $table->string('sale_unit_name', 50)->nullable()->after('sale_unit')->comment('Display: Piece, Carton, Dozen');
    $table->integer('quantity_in_base_units')->nullable()->after('sale_unit_name')->comment('For stock deduction; e.g. 2 cartons × 12 = 24');
});
```

---

### 3.5 `payments` — Support split payments and POS

**Migration:** `add_pos_fields_to_payments_table.php`

**Current `payments` table structure:**
- order_id, user_id, vendor_id
- amount, payment_method (integer: 1=Cash, 2=Online)
- transaction_id, status (integer: 1-4)
- paid_at, added_by

**New fields to add:**

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `payment_type_name` | enum('cash','card','mobile','transfer','other') | YES | Named payment type (extends payment_method) |
| `pos_shift_id` | foreignId | YES | Shift when payment received |

**Note:** Keep `payment_method` (integer) for backward compatibility. Add `payment_type_name` for clarity. For split payments, create multiple `payments` rows per order with different `payment_method`/`payment_type_name`. Alternatively, use `pos_invoice_payments` table for split payment tracking.

```php
Schema::table('payments', function (Blueprint $table) {
    $table->enum('payment_type_name', ['cash', 'card', 'mobile', 'transfer', 'other'])->nullable()->after('payment_method');
    $table->foreignId('pos_shift_id')->nullable()->after('order_id')->constrained('pos_shifts')->nullOnDelete();
});
```

---

### 3.6 `refunds` — Support item-level returns and reasons

**Migration:** `add_pos_fields_to_refunds_table.php`

**Current `refunds` table structure:**
- order_id, amount, reason, transaction_id

**New fields to add:**

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `order_item_id` | foreignId | YES | Specific item refunded |
| `return_reason` | string(255) | YES | Defective, wrong item, damaged, etc. (extends existing 'reason') |
| `return_type` | enum('refund','exchange') | default 'refund' | Refund or exchange |
| `processed_by` | foreignId | YES | User who processed return |
| `refund_method` | string(50) | YES | Cash, Card, Exchange Product |

```php
Schema::table('refunds', function (Blueprint $table) {
    $table->foreignId('order_item_id')->nullable()->after('order_id')->constrained('order_items')->nullOnDelete();
    $table->string('return_reason', 255)->nullable()->after('reason')->comment('Detailed reason: Defective, wrong item, damaged, etc.');
    $table->enum('return_type', ['refund', 'exchange'])->default('refund')->after('return_reason');
    $table->foreignId('processed_by')->nullable()->after('return_type')->constrained('users')->nullOnDelete();
    $table->string('refund_method', 50)->nullable()->after('transaction_id');
});
```

---

### 3.7 `grocery_menu_items` — Barcode, SKU, reorder level, cost, expiry, wholesale (Carton/Dozen)

**Migration:** `add_pos_fields_to_grocery_menu_items_table.php`

**Current fields (unchanged):** menu_id, grocery_id, serial_number, grocery_attribute_id, attribute_value, price, discount_price, stock, rating, status, available_status, approval_status.

**Note:** Weight/volume (kg, gram, litre) use `grocery_attribute_id` + `attribute_value`. No `unit_type` reference for wholesale.

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `barcode` | string(50) | YES | Barcode (unique per vendor) |
| `sku` | string(50) | YES | Stock Keeping Unit |
| `reorder_level` | integer | default 0 | Low stock threshold |
| `purchase_cost` | decimal(10,2) | YES | Cost price for profit calc |
| `tax_rate` | decimal(5,2) | default 0 | Tax % (TVA) |
| `expiry_date` | date | YES | Expiry (for food) |
| `batch_number` | string(100) | YES | Lot/batch tracking |
| **Wholesale (Carton/Dozen)** | | | |
| `wholesale_unit` | string(50) | YES | Wholesale unit name: 'Carton', 'Dozen', 'Box', etc. |
| `wholesale_quantity` | integer | YES | 1 wholesale unit = X base units. E.g. 1 Carton = 12 Pieces → 12 |
| `wholesale_price` | decimal(10,2) | YES | Price per wholesale unit (e.g. price per carton) |

**Wholesale logic:**
- Stock stored in base units. `price` = retail per base; `wholesale_price` = per carton/dozen.
- Selling 1 carton deducts `wholesale_quantity` (12) from stock.
- `wholesale_unit` and `wholesale_quantity` set per product at add/edit (flexible: 12, 18, 24, etc.).

```php
Schema::table('grocery_menu_items', function (Blueprint $table) {
    $table->string('barcode', 50)->nullable()->after('serial_number');
    $table->string('sku', 50)->nullable()->after('barcode');
    $table->integer('reorder_level')->default(0)->after('stock');
    $table->decimal('purchase_cost', 10, 2)->nullable()->after('discount_price');
    $table->decimal('tax_rate', 5, 2)->default(0)->after('purchase_cost');
    $table->date('expiry_date')->nullable()->after('tax_rate');
    $table->string('batch_number', 100)->nullable()->after('expiry_date');
    // Wholesale: Carton, Dozen, etc. — separate from unit_type; no FK
    $table->string('wholesale_unit', 50)->nullable()->after('batch_number')->comment('Carton, Dozen, Box, etc.');
    $table->integer('wholesale_quantity')->nullable()->after('wholesale_unit')->comment('1 wholesale unit = X base units. E.g. 12');
    $table->decimal('wholesale_price', 10, 2)->nullable()->after('wholesale_quantity')->comment('Price per wholesale unit');
});
```

---

### 3.8 `restaurant_menu_items` — Same POS fields as grocery

**Migration:** `add_pos_fields_to_restaurant_menu_items_table.php`

Same columns as `grocery_menu_items`. No `unit_type` reference. Weight/volume via `restaurant_attribute_id` + `attribute_value`.

```php
Schema::table('restaurant_menu_items', function (Blueprint $table) {
    $table->string('barcode', 50)->nullable()->after('serial_number');
    $table->string('sku', 50)->nullable()->after('barcode');
    $table->integer('reorder_level')->default(0)->after('stock');
    $table->decimal('purchase_cost', 10, 2)->nullable()->after('discount_price');
    $table->decimal('tax_rate', 5, 2)->default(0)->after('purchase_cost');
    $table->date('expiry_date')->nullable()->after('tax_rate');
    $table->string('batch_number', 100)->nullable()->after('expiry_date');
    $table->string('wholesale_unit', 50)->nullable()->after('batch_number');
    $table->integer('wholesale_quantity')->nullable()->after('wholesale_unit');
    $table->decimal('wholesale_price', 10, 2)->nullable()->after('wholesale_quantity');
});
```

---

### 3.9 `grocery_menus` — Sub-category optional for bulk import

**Migration:** `add_sub_category_nullable_to_grocery_menus_table.php` (optional)

**Current structure:** `sub_category_id` is unsignedBigInteger, NOT NULL, with FK to grocery_sub_categories.

**Change needed:**

| Column | Change | Description |
|--------|--------|-------------|
| `sub_category_id` | nullable | Sub-category not mandatory for bulk import. Category required. |

**Note:** Only add if bulk import allows products without sub-category. The current migration shows `sub_category_id` is NOT NULL, so this alteration is needed if we want to allow null sub-categories.

```php
Schema::table('grocery_menus', function (Blueprint $table) {
    $table->unsignedBigInteger('sub_category_id')->nullable()->change();
    // Note: Foreign key constraint may need to be dropped and re-added if changing to nullable
});
```

**Alternative:** If FK constraint prevents nullable change, consider:
1. Drop FK constraint
2. Change column to nullable
3. Re-add FK constraint with `nullOnDelete()`

---

### Wholesale (Carton/Dozen) — Design Summary

| Step | Where | What |
|------|-------|------|
| **Product add/edit** | grocery_menu_items, restaurant_menu_items | Enter `wholesale_unit` (Carton, Dozen, Box), `wholesale_quantity` (12, 18, 24), `wholesale_price`. Retail `price` = price per base unit. Weight (kg, gram) via `grocery_attribute_id` + `attribute_value`. |
| **POS sale** | User selects unit | Sell by base unit or wholesale unit. Different prices applied. |
| **Stock deduction** | Backend logic | Selling 1 carton deducts `wholesale_quantity` from stock. E.g. 2 cartons × 12 = 24. |
| **Invoice display** | order_items | "Milk – 1 Carton" via `sale_unit` + `sale_unit_name` + `quantity`. |
| **Bulk import** | API | Import products by category; sub_category optional. |

---

### 3.10 `vendors` — Warehouse/branch and POS branding

**Migration:** `add_pos_fields_to_vendors_table.php`

**Note:** Current `vendors` table already has:
- `restaurant_category_id` (foreignId, nullable) - FK to restaurant_categories
- `grocery_category_id` (foreignId, nullable) - FK to grocery_categories

**New fields to add:**

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `logo` | string(255) | YES | Store logo URL |
| `phone` | string(20) | YES | Store phone |
| `tax_id` | string(50) | YES | VAT/Tax registration (separate from gst_no) |
| `is_branch` | boolean | default 0 | Is a branch of parent vendor |
| `parent_vendor_id` | foreignId | YES | Parent vendor for branches |
| `warehouse_code` | string(20) | YES | Warehouse/branch code |
| `warehouse_type` | enum('store','warehouse','branch') | YES | Type of location |

```php
Schema::table('vendors', function (Blueprint $table) {
    $table->string('logo', 255)->nullable()->after('address');
    $table->string('phone', 20)->nullable()->after('logo');
    $table->string('tax_id', 50)->nullable()->after('gst_no');
    $table->boolean('is_branch')->default(false)->after('shop_type');
    $table->foreignId('parent_vendor_id')->nullable()->constrained('vendors')->nullOnDelete();
    $table->string('warehouse_code', 20)->nullable()->after('parent_vendor_id');
    $table->enum('warehouse_type', ['store', 'warehouse', 'branch'])->nullable()->after('warehouse_code');
});
```

---

### 3.11 `roles` — Add POS roles

**Migration:** `add_pos_roles_to_roles_table.php`

**Action:** Seeder or migration to insert:
- `Cashier`
- `Manager`
- `Stock`
- `Employee` (for HRM)

Existing: Admin, Vendor, Product Manager, Sales Manager, Delivery Staff, Customer.

---

### 3.12 `coupons` — Vendor scope for POS

**Migration:** `add_vendor_scope_to_coupons_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| `vendor_id` | foreignId | YES | Coupon valid for specific vendor only |

```php
Schema::table('coupons', function (Blueprint $table) {
    $table->foreignId('vendor_id')->nullable()->after('id')->constrained('vendors')->nullOnDelete();
});
```

---

## 4. New Tables

### 4.1 POS Module

#### `pos_shifts`

**Migration:** `create_pos_shifts_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | Store |
| cashier_id | foreignId | NO | User (cashier) |
| opened_at | timestamp | NO | Shift start |
| closed_at | timestamp | YES | Shift end |
| opening_cash | decimal(10,2) | default 0 | Opening cash |
| closing_cash | decimal(10,2) | YES | Actual cash counted |
| expected_cash | decimal(10,2) | YES | System expected cash |
| cash_variance | decimal(10,2) | YES | Difference |
| total_sales | decimal(12,2) | default 0 | Total sales this shift |
| total_transactions | integer | default 0 | Number of transactions |
| status | enum('open','closed') | default 'open' | Shift status |
| closed_by | foreignId | YES | User who closed |
| variance_reason | string(255) | YES | Reason for variance |
| timestamps | | | |

```php
Schema::create('pos_shifts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('cashier_id')->constrained('users')->cascadeOnDelete();
    $table->timestamp('opened_at');
    $table->timestamp('closed_at')->nullable();
    $table->decimal('opening_cash', 10, 2)->default(0);
    $table->decimal('closing_cash', 10, 2)->nullable();
    $table->decimal('expected_cash', 10, 2)->nullable();
    $table->decimal('cash_variance', 10, 2)->nullable();
    $table->decimal('total_sales', 12, 2)->default(0);
    $table->integer('total_transactions')->default(0);
    $table->enum('status', ['open', 'closed'])->default('open');
    $table->foreignId('closed_by')->nullable()->constrained('users')->nullOnDelete();
    $table->string('variance_reason', 255)->nullable();
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
    $table->index(['cashier_id', 'opened_at']);
});
```

---

#### `pos_held_transactions`

**Migration:** `create_pos_held_transactions_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | Store |
| cashier_id | foreignId | NO | User |
| customer_id | foreignId | YES | User (customer) |
| held_at | timestamp | NO | When held |
| cart_data | json | YES | Serialized cart (items, totals) |
| total_amount | decimal(12,2) | default 0 | Cart total |
| item_count | integer | default 0 | Number of items |
| status | enum('held','resumed','expired','deleted') | default 'held' | |
| resumed_at | timestamp | YES | When resumed |
| resumed_order_id | foreignId | YES | Order created on resume |
| timestamps | | | |

```php
Schema::create('pos_held_transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('cashier_id')->constrained('users')->cascadeOnDelete();
    $table->foreignId('customer_id')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamp('held_at');
    $table->json('cart_data')->nullable();
    $table->decimal('total_amount', 12, 2)->default(0);
    $table->integer('item_count')->default(0);
    $table->enum('status', ['held', 'resumed', 'expired', 'deleted'])->default('held');
    $table->timestamp('resumed_at')->nullable();
    $table->foreignId('resumed_order_id')->nullable()->constrained('orders')->nullOnDelete();
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
});
```

---

#### `pos_invoice_payments` (Split payments per order)

**Migration:** `create_pos_invoice_payments_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| order_id | foreignId | NO | Order/invoice |
| payment_method | enum('cash','card','mobile','transfer','other') | NO | |
| amount | decimal(10,2) | NO | Amount for this method |
| reference | string(100) | YES | Transaction ref |
| timestamps | | | |

```php
Schema::create('pos_invoice_payments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained('orders')->cascadeOnDelete();
    $table->enum('payment_method', ['cash', 'card', 'mobile', 'transfer', 'other']);
    $table->decimal('amount', 10, 2);
    $table->string('reference', 100)->nullable();
    $table->timestamps();
    $table->index('order_id');
});
```

---

#### `customer_balances` (Credit sales / on-account)

**Migration:** `create_customer_balances_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| user_id | foreignId | NO | Customer |
| vendor_id | foreignId | NO | Store |
| balance | decimal(12,2) | default 0 | Outstanding balance (+ = owes) |
| last_transaction_at | timestamp | YES | Last payment/invoice |
| timestamps | | | |

```php
Schema::create('customer_balances', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->decimal('balance', 12, 2)->default(0);
    $table->timestamp('last_transaction_at')->nullable();
    $table->timestamps();
    $table->unique(['user_id', 'vendor_id']);
});
```

---

#### `customer_balance_transactions`

**Migration:** `create_customer_balance_transactions_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| user_id | foreignId | NO | Customer |
| vendor_id | foreignId | NO | Store |
| type | enum('invoice','payment','adjustment','refund') | NO | |
| amount | decimal(12,2) | NO | + for debit, - for credit |
| order_id | foreignId | YES | Related order |
| payment_id | foreignId | YES | Related payment |
| notes | text | YES | |
| created_by | foreignId | YES | User who recorded |
| timestamps | | | |

```php
Schema::create('customer_balance_transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->enum('type', ['invoice', 'payment', 'adjustment', 'refund']);
    $table->decimal('amount', 12, 2);
    $table->foreignId('order_id')->nullable()->constrained('orders')->nullOnDelete();
    $table->foreignId('payment_id')->nullable();
    $table->text('notes')->nullable();
    $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamps();
    $table->index(['user_id', 'created_at']);
});
```

---

#### `price_levels`

**Migration:** `create_price_levels_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | Store |
| name | string(50) | NO | Retail, Wholesale, VIP |
| discount_percent | decimal(5,2) | default 0 | e.g. 10 for -10% |
| is_default | boolean | default 0 | Default for new customers |
| timestamps | | | |

```php
Schema::create('price_levels', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->string('name', 50);
    $table->decimal('discount_percent', 5, 2)->default(0);
    $table->boolean('is_default')->default(false);
    $table->timestamps();
    $table->index('vendor_id');
});
```

---

### 4.2 CRM Module

#### `customer_complaints`

**Migration:** `create_customer_complaints_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| user_id | foreignId | NO | Customer |
| vendor_id | foreignId | NO | Store |
| subject | string(255) | NO | |
| description | text | YES | |
| order_id | foreignId | YES | Related order |
| status | enum('open','in_progress','resolved','closed') | default 'open' | |
| created_by | foreignId | YES | User who created |
| resolved_by | foreignId | YES | User who resolved |
| resolved_at | timestamp | YES | |
| timestamps | | | |

```php
Schema::create('customer_complaints', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->string('subject', 255);
    $table->text('description')->nullable();
    $table->foreignId('order_id')->nullable()->constrained('orders')->nullOnDelete();
    $table->enum('status', ['open', 'in_progress', 'resolved', 'closed'])->default('open');
    $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
    $table->foreignId('resolved_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamp('resolved_at')->nullable();
    $table->timestamps();
    $table->index(['user_id', 'status']);
});
```

---

### 4.3 ERP Module

#### `suppliers`

**Migration:** `create_suppliers_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | Store (supplier belongs to vendor) |
| name | string(255) | NO | |
| code | string(50) | YES | Supplier code |
| contact_person | string(255) | YES | |
| phone | string(20) | YES | |
| email | string(255) | YES | |
| address | text | YES | |
| tax_id | string(50) | YES | VAT number |
| payment_terms | string(100) | YES | e.g. Net 30 |
| credit_limit | decimal(12,2) | YES | |
| bank_name | string(100) | YES | |
| account_number | string(50) | YES | |
| notes | text | YES | |
| status | enum('active','inactive') | default 'active' | |
| timestamps | | | |

```php
Schema::create('suppliers', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->string('name', 255);
    $table->string('code', 50)->nullable();
    $table->string('contact_person', 255)->nullable();
    $table->string('phone', 20)->nullable();
    $table->string('email', 255)->nullable();
    $table->text('address')->nullable();
    $table->string('tax_id', 50)->nullable();
    $table->string('payment_terms', 100)->nullable();
    $table->decimal('credit_limit', 12, 2)->nullable();
    $table->string('bank_name', 100)->nullable();
    $table->string('account_number', 50)->nullable();
    $table->text('notes')->nullable();
    $table->enum('status', ['active', 'inactive'])->default('active');
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
});
```

---

#### `purchase_orders`

**Migration:** `create_purchase_orders_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | Store |
| supplier_id | foreignId | NO | Supplier |
| po_number | string(50) | NO | Unique PO number |
| status | enum('draft','sent','partial','received','cancelled') | default 'draft' | |
| order_date | date | NO | |
| expected_date | date | YES | Expected delivery |
| subtotal | decimal(12,2) | default 0 | |
| tax | decimal(10,2) | default 0 | |
| total | decimal(12,2) | default 0 | |
| notes | text | YES | |
| created_by | foreignId | YES | |
| approved_by | foreignId | YES | |
| timestamps | | | |

```php
Schema::create('purchase_orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('supplier_id')->constrained('suppliers')->cascadeOnDelete();
    $table->string('po_number', 50)->unique();
    $table->enum('status', ['draft', 'sent', 'partial', 'received', 'cancelled'])->default('draft');
    $table->date('order_date');
    $table->date('expected_date')->nullable();
    $table->decimal('subtotal', 12, 2)->default(0);
    $table->decimal('tax', 10, 2)->default(0);
    $table->decimal('total', 12, 2)->default(0);
    $table->text('notes')->nullable();
    $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
    $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
});
```

---

#### `purchase_order_items`

**Migration:** `create_purchase_order_items_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| purchase_order_id | foreignId | NO | |
| product_type | enum('grocery','restaurant') | NO | |
| product_id | bigint | NO | grocery_menu_item_id or restaurant_menu_item_id |
| quantity | integer | NO | |
| unit_cost | decimal(10,2) | NO | |
| total | decimal(10,2) | NO | |
| received_quantity | integer | default 0 | Qty received so far |
| timestamps | | | |

```php
Schema::create('purchase_order_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('purchase_order_id')->constrained('purchase_orders')->cascadeOnDelete();
    $table->enum('product_type', ['grocery', 'restaurant']);
    $table->unsignedBigInteger('product_id');
    $table->integer('quantity');
    $table->decimal('unit_cost', 10, 2);
    $table->decimal('total', 10, 2);
    $table->integer('received_quantity')->default(0);
    $table->timestamps();
    $table->index(['purchase_order_id', 'product_type', 'product_id']);
});
```

---

#### `goods_received_notes` (GRN)

**Migration:** `create_goods_received_notes_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| purchase_order_id | foreignId | NO | |
| grn_number | string(50) | NO | Unique |
| received_date | date | NO | |
| status | enum('draft','approved') | default 'draft' | |
| notes | text | YES | |
| received_by | foreignId | YES | |
| approved_by | foreignId | YES | |
| approved_at | timestamp | YES | |
| timestamps | | | |

```php
Schema::create('goods_received_notes', function (Blueprint $table) {
    $table->id();
    $table->foreignId('purchase_order_id')->constrained('purchase_orders')->cascadeOnDelete();
    $table->string('grn_number', 50)->unique();
    $table->date('received_date');
    $table->enum('status', ['draft', 'approved'])->default('draft');
    $table->text('notes')->nullable();
    $table->foreignId('received_by')->nullable()->constrained('users')->nullOnDelete();
    $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamp('approved_at')->nullable();
    $table->timestamps();
    $table->index('purchase_order_id');
});
```

---

#### `grn_items`

**Migration:** `create_grn_items_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| goods_received_note_id | foreignId | NO | |
| purchase_order_item_id | foreignId | NO | |
| quantity_received | integer | NO | |
| batch_number | string(100) | YES | |
| expiry_date | date | YES | |
| notes | text | YES | |
| timestamps | | | |

```php
Schema::create('grn_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('goods_received_note_id')->constrained('goods_received_notes')->cascadeOnDelete();
    $table->foreignId('purchase_order_item_id')->constrained('purchase_order_items')->cascadeOnDelete();
    $table->integer('quantity_received');
    $table->string('batch_number', 100)->nullable();
    $table->date('expiry_date')->nullable();
    $table->text('notes')->nullable();
    $table->timestamps();
});
```

---

#### `warehouses` (Branches / stores — optional if vendors serve as branches)

**Migration:** `create_warehouses_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | Parent vendor |
| name | string(255) | NO | |
| code | string(20) | NO | Unique code |
| type | enum('store','warehouse','branch') | default 'store' | |
| address | text | YES | |
| manager_id | foreignId | YES | User (manager) |
| phone | string(20) | YES | |
| status | enum('active','inactive') | default 'active' | |
| timestamps | | | |

**Note:** If `vendors` already represents stores/branches with `parent_vendor_id`, you may use `vendors` as warehouses. This table provides explicit warehouse/branch entities for stock-by-location.

```php
Schema::create('warehouses', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->string('name', 255);
    $table->string('code', 20)->unique();
    $table->enum('type', ['store', 'warehouse', 'branch'])->default('store');
    $table->text('address')->nullable();
    $table->foreignId('manager_id')->nullable()->constrained('users')->nullOnDelete();
    $table->string('phone', 20)->nullable();
    $table->enum('status', ['active', 'inactive'])->default('active');
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
});
```

---

#### `warehouse_stock` (Stock per warehouse)

**Migration:** `create_warehouse_stock_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| warehouse_id | foreignId | NO | |
| product_type | enum('grocery','restaurant') | NO | |
| product_id | bigint | NO | |
| quantity | integer | default 0 | |
| reorder_level | integer | default 0 | |
| timestamps | | | |

```php
Schema::create('warehouse_stock', function (Blueprint $table) {
    $table->id();
    $table->foreignId('warehouse_id')->constrained('warehouses')->cascadeOnDelete();
    $table->enum('product_type', ['grocery', 'restaurant']);
    $table->unsignedBigInteger('product_id');
    $table->integer('quantity')->default(0);
    $table->integer('reorder_level')->default(0);
    $table->timestamps();
    $table->unique(['warehouse_id', 'product_type', 'product_id']);
});
```

---

#### `stock_transfers`

**Migration:** `create_stock_transfers_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | |
| from_warehouse_id | foreignId | NO | |
| to_warehouse_id | foreignId | NO | |
| transfer_number | string(50) | NO | Unique |
| status | enum('draft','sent','received','cancelled') | default 'draft' | |
| transfer_date | date | NO | |
| received_at | timestamp | YES | |
| notes | text | YES | |
| created_by | foreignId | YES | |
| received_by | foreignId | YES | |
| timestamps | | | |

```php
Schema::create('stock_transfers', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('from_warehouse_id')->constrained('warehouses')->cascadeOnDelete();
    $table->foreignId('to_warehouse_id')->constrained('warehouses')->cascadeOnDelete();
    $table->string('transfer_number', 50)->unique();
    $table->enum('status', ['draft', 'sent', 'received', 'cancelled'])->default('draft');
    $table->date('transfer_date');
    $table->timestamp('received_at')->nullable();
    $table->text('notes')->nullable();
    $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
    $table->foreignId('received_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
});
```

---

#### `stock_transfer_items`

**Migration:** `create_stock_transfer_items_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| stock_transfer_id | foreignId | NO | |
| product_type | enum('grocery','restaurant') | NO | |
| product_id | bigint | NO | |
| quantity | integer | NO | |
| timestamps | | | |

```php
Schema::create('stock_transfer_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('stock_transfer_id')->constrained('stock_transfers')->cascadeOnDelete();
    $table->enum('product_type', ['grocery', 'restaurant']);
    $table->unsignedBigInteger('product_id');
    $table->integer('quantity');
    $table->timestamps();
    $table->index(['stock_transfer_id', 'product_type', 'product_id']);
});
```

---

#### `stock_adjustments`

**Migration:** `create_stock_adjustments_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | |
| warehouse_id | foreignId | YES | Nullable if single store |
| product_type | enum('grocery','restaurant') | NO | |
| product_id | bigint | NO | |
| quantity_before | integer | NO | |
| quantity_after | integer | NO | |
| adjustment_type | enum('increase','decrease') | NO | |
| reason | string(255) | YES | Damage, theft, found, etc. |
| notes | text | YES | |
| status | enum('pending','approved','rejected') | default 'pending' | |
| created_by | foreignId | YES | |
| approved_by | foreignId | YES | |
| approved_at | timestamp | YES | |
| timestamps | | | |

```php
Schema::create('stock_adjustments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('warehouse_id')->nullable()->constrained('warehouses')->nullOnDelete();
    $table->enum('product_type', ['grocery', 'restaurant']);
    $table->unsignedBigInteger('product_id');
    $table->integer('quantity_before');
    $table->integer('quantity_after');
    $table->enum('adjustment_type', ['increase', 'decrease']);
    $table->string('reason', 255)->nullable();
    $table->text('notes')->nullable();
    $table->enum('status', ['pending', 'approved', 'rejected'])->default('pending');
    $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
    $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamp('approved_at')->nullable();
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
});
```

---

#### `supplier_returns`

**Migration:** `create_supplier_returns_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | |
| supplier_id | foreignId | NO | |
| return_number | string(50) | NO | Unique |
| status | enum('draft','sent','received','cancelled') | default 'draft' | |
| return_date | date | NO | |
| notes | text | YES | |
| created_by | foreignId | YES | |
| timestamps | | | |

```php
Schema::create('supplier_returns', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('supplier_id')->constrained('suppliers')->cascadeOnDelete();
    $table->string('return_number', 50)->unique();
    $table->enum('status', ['draft', 'sent', 'received', 'cancelled'])->default('draft');
    $table->date('return_date');
    $table->text('notes')->nullable();
    $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamps();
    $table->index(['vendor_id', 'status']);
});
```

#### `supplier_return_items`

**Migration:** `create_supplier_return_items_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| supplier_return_id | foreignId | NO | |
| product_type | enum('grocery','restaurant') | NO | |
| product_id | bigint | NO | |
| quantity | integer | NO | |
| unit_cost | decimal(10,2) | NO | |
| total | decimal(10,2) | NO | |
| reason | string(255) | YES | |
| timestamps | | | |

```php
Schema::create('supplier_return_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('supplier_return_id')->constrained('supplier_returns')->cascadeOnDelete();
    $table->enum('product_type', ['grocery', 'restaurant']);
    $table->unsignedBigInteger('product_id');
    $table->integer('quantity');
    $table->decimal('unit_cost', 10, 2);
    $table->decimal('total', 10, 2);
    $table->string('reason', 255)->nullable();
    $table->timestamps();
    $table->index(['supplier_return_id', 'product_type', 'product_id']);
});
```

---

### 4.4 Cash Management

#### `cash_transactions`

**Migration:** `create_cash_transactions_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | |
| pos_shift_id | foreignId | YES | Shift when transaction occurred |
| type | enum('in','out') | NO | Cash in or out |
| amount | decimal(10,2) | NO | |
| reason | string(255) | YES | Petty cash, expense, etc. |
| category | string(100) | YES | Expense category |
| notes | text | YES | |
| created_by | foreignId | YES | |
| timestamps | | | |

```php
Schema::create('cash_transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('pos_shift_id')->nullable()->constrained('pos_shifts')->nullOnDelete();
    $table->enum('type', ['in', 'out']);
    $table->decimal('amount', 10, 2);
    $table->string('reason', 255)->nullable();
    $table->string('category', 100)->nullable();
    $table->text('notes')->nullable();
    $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
    $table->timestamps();
    $table->index(['vendor_id', 'created_at']);
});
```

---

### 4.5 HRM Module

#### `departments`

**Migration:** `create_departments_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | |
| name | string(100) | NO | |
| code | string(20) | YES | |
| timestamps | | | |

```php
Schema::create('departments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->string('name', 100);
    $table->string('code', 20)->nullable();
    $table->timestamps();
    $table->index(['vendor_id', 'code']);
});
```

---

#### `employee_attendance`

**Migration:** `create_employee_attendance_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| user_id | foreignId | NO | Employee |
| vendor_id | foreignId | NO | |
| date | date | NO | |
| check_in | time | YES | |
| check_out | time | YES | |
| status | enum('present','absent','leave','half_day') | default 'present' | |
| notes | text | YES | |
| timestamps | | | |

```php
Schema::create('employee_attendance', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->date('date');
    $table->time('check_in')->nullable();
    $table->time('check_out')->nullable();
    $table->enum('status', ['present', 'absent', 'leave', 'half_day'])->default('present');
    $table->text('notes')->nullable();
    $table->timestamps();
    $table->unique(['user_id', 'date']);
});
```

---

#### `payroll_records`

**Migration:** `create_payroll_records_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| vendor_id | foreignId | NO | |
| user_id | foreignId | NO | Employee |
| period_start | date | NO | |
| period_end | date | NO | |
| base_salary | decimal(12,2) | default 0 | |
| overtime | decimal(10,2) | default 0 | |
| bonuses | decimal(10,2) | default 0 | |
| deductions | decimal(10,2) | default 0 | |
| net_pay | decimal(12,2) | default 0 | |
| status | enum('draft','approved','paid') | default 'draft' | |
| paid_at | timestamp | YES | |
| timestamps | | | |

```php
Schema::create('payroll_records', function (Blueprint $table) {
    $table->id();
    $table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
    $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
    $table->date('period_start');
    $table->date('period_end');
    $table->decimal('base_salary', 12, 2)->default(0);
    $table->decimal('overtime', 10, 2)->default(0);
    $table->decimal('bonuses', 10, 2)->default(0);
    $table->decimal('deductions', 10, 2)->default(0);
    $table->decimal('net_pay', 12, 2)->default(0);
    $table->enum('status', ['draft', 'approved', 'paid'])->default('draft');
    $table->timestamp('paid_at')->nullable();
    $table->timestamps();
    $table->index(['vendor_id', 'user_id', 'period_start']);
});
```

---

### 4.6 Sync & Audit

#### `sync_queue`

**Migration:** `create_sync_queue_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| device_id | string(100) | YES | POS device identifier |
| vendor_id | foreignId | YES | |
| entity_type | string(50) | NO | order, sale, stock, etc. |
| entity_id | string(100) | YES | Local/remote ID |
| payload | json | YES | Data to sync |
| action | enum('create','update','delete') | NO | |
| status | enum('pending','synced','failed') | default 'pending' | |
| attempts | integer | default 0 | |
| last_error | text | YES | |
| synced_at | timestamp | YES | |
| timestamps | | | |

```php
Schema::create('sync_queue', function (Blueprint $table) {
    $table->id();
    $table->string('device_id', 100)->nullable();
    $table->foreignId('vendor_id')->nullable()->constrained('vendors')->nullOnDelete();
    $table->string('entity_type', 50);
    $table->string('entity_id', 100)->nullable();
    $table->json('payload')->nullable();
    $table->enum('action', ['create', 'update', 'delete']);
    $table->enum('status', ['pending', 'synced', 'failed'])->default('pending');
    $table->integer('attempts')->default(0);
    $table->text('last_error')->nullable();
    $table->timestamp('synced_at')->nullable();
    $table->timestamps();
    $table->index(['status', 'entity_type']);
});
```

---

#### `audit_logs`

**Migration:** `create_audit_logs_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| user_id | foreignId | YES | |
| vendor_id | foreignId | YES | |
| action | string(100) | NO | create, update, delete, cancel, etc. |
| entity_type | string(50) | NO | order, invoice, stock, etc. |
| entity_id | bigint | YES | |
| old_values | json | YES | |
| new_values | json | YES | |
| ip_address | string(45) | YES | |
| user_agent | text | YES | |
| timestamps | | | |

```php
Schema::create('audit_logs', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->nullable()->constrained('users')->nullOnDelete();
    $table->foreignId('vendor_id')->nullable()->constrained('vendors')->nullOnDelete();
    $table->string('action', 100);
    $table->string('entity_type', 50);
    $table->unsignedBigInteger('entity_id')->nullable();
    $table->json('old_values')->nullable();
    $table->json('new_values')->nullable();
    $table->string('ip_address', 45)->nullable();
    $table->text('user_agent')->nullable();
    $table->timestamps();
    $table->index(['user_id', 'created_at']);
    $table->index(['entity_type', 'entity_id']);
});
```

---

#### `role_permissions`

**Migration:** `create_role_permissions_table.php`

| Column | Type | Nullable | Description |
|--------|------|----------|-------------|
| id | bigint PK | | |
| role_id | foreignId | NO | |
| permission | string(100) | NO | e.g. pos.discount.5, pos.discount.20 |
| value | string(255) | YES | Optional value |
| timestamps | | | |

```php
Schema::create('role_permissions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('role_id')->constrained('roles')->cascadeOnDelete();
    $table->string('permission', 100);
    $table->string('value', 255)->nullable();
    $table->timestamps();
    $table->unique(['role_id', 'permission']);
});
```

---

## 5. Bulk Import Products

**API:** `POST /products/bulk-import` or `POST /grocery-menu-items/bulk-import`

| Field | Required | Description |
|-------|----------|-------------|
| category_id | YES | grocery_categories.id |
| sub_category_id | NO | grocery_sub_categories.id — optional |
| vendor_id / grocery_id | YES | Target vendor |
| products | YES | Array of { name, sku, barcode, price, wholesale_unit, wholesale_quantity, wholesale_price, stock, ... } |

Import creates `grocery_menus` + `grocery_menu_items` rows. Sub-category can be null if not provided.

---

## 6. Migration Order (Dependencies)

Execute migrations in this order to satisfy foreign keys:

### Phase 1: Base Tables (No Dependencies)
1. `warehouses` (if using; no new FK from existing tables initially)
2. `departments` (only depends on vendors)

### Phase 2: Vendor & User Alterations
3. `add_pos_fields_to_vendors_table` (parent_vendor_id self-ref; must be after vendors table exists)
4. `add_pos_crm_hrm_fields_to_users_table` (branch_id → warehouses; run after warehouses)

### Phase 3: POS Core Tables
5. `price_levels` (depends on vendors)
6. `pos_shifts` (depends on vendors, users)
7. `pos_held_transactions` (depends on vendors, users, orders)
8. `pos_invoice_payments` (depends on orders)
9. `customer_balances` (depends on users, vendors)
10. `customer_balance_transactions` (depends on customer_balances, orders, payments)

### Phase 4: CRM Tables
11. `customer_complaints` (depends on users, vendors, orders)

### Phase 5: ERP Tables
12. `suppliers` (depends on vendors)
13. `purchase_orders` (depends on vendors, suppliers, users)
14. `purchase_order_items` (depends on purchase_orders)
15. `goods_received_notes` (depends on purchase_orders, users)
16. `grn_items` (depends on goods_received_notes, purchase_order_items)
17. `warehouse_stock` (depends on warehouses)
18. `stock_transfers` (depends on vendors, warehouses, users)
19. `stock_transfer_items` (depends on stock_transfers)
20. `stock_adjustments` (depends on vendors, warehouses, users)
21. `supplier_returns` (depends on vendors, suppliers, users)
22. `supplier_return_items` (depends on supplier_returns)

### Phase 6: Cash Management
23. `cash_transactions` (depends on vendors, pos_shifts, users)

### Phase 7: HRM Tables
24. `employee_attendance` (depends on users, vendors)
25. `payroll_records` (depends on vendors, users)

### Phase 8: Sync & Audit
26. `sync_queue` (depends on vendors)
27. `audit_logs` (depends on users, vendors)
28. `role_permissions` (depends on roles)

### Phase 9: Table Alterations (After All New Tables)
29. `add_pos_fields_to_order_items_table` (depends on order_items)
30. `add_pos_fields_to_orders_table` (depends on orders, pos_shifts, users, warehouses)
31. `add_pos_fields_to_payments_table` (depends on payments, pos_shifts)
32. `add_pos_fields_to_refunds_table` (depends on refunds, order_items, users)
33. `add_pos_fields_to_grocery_menu_items_table` (depends on grocery_menu_items)
34. `add_pos_fields_to_restaurant_menu_items_table` (depends on restaurant_menu_items)
35. `add_sub_category_nullable_to_grocery_menus_table` (optional; depends on grocery_menus)
36. `add_pos_fields_to_addresses_table` (depends on addresses)
37. `add_vendor_scope_to_coupons_table` (depends on coupons, vendors)
38. `add_pos_roles_to_roles_table` (seeder or migration; depends on roles)

---

## 7. Summary Matrix

| Category | Alters | New Tables |
|----------|--------|------------|
| **POS** | users, orders, order_items, payments, refunds, vendors, coupons, roles | pos_shifts, pos_held_transactions, pos_invoice_payments, customer_balances, customer_balance_transactions, price_levels, cash_transactions |
| **CRM** | users, addresses | customer_complaints |
| **ERP** | grocery_menu_items, restaurant_menu_items, grocery_menus, vendors | suppliers, purchase_orders, purchase_order_items, goods_received_notes, grn_items, warehouses, warehouse_stock, stock_transfers, stock_transfer_items, stock_adjustments, supplier_returns, supplier_return_items |
| **HRM** | users | departments, employee_attendance, payroll_records |
| **Sync/Audit** | — | sync_queue, audit_logs, role_permissions |

## 7.1 Current Database Notes

### Existing Tables Already in Use:
- **users**: Has role_id, points, fcm, notification_enabled, reg_status, phone_verified, preferred_locale, added_by, updated_by, softDeletes
- **orders**: Has order_code, read_status, distance_km, delivery_per_km_amount, delivery_per_order_amount
- **payments**: Has payment_method (integer 1/2), transaction_id, status (integer 1-4), paid_at, added_by
- **refunds**: Simple structure with order_id, amount, reason, transaction_id
- **vendors**: Has restaurant_category_id, grocery_category_id foreign keys (already exist)
- **addresses**: Has is_default (tinyInteger), no zone or is_billing yet
- **coupons**: Has name_en/ar/fr, code, type (integer), discount_value, count, valid_upto, status, softDeletes
- **grocery_menu_items**: Has serial_number (unique), grocery_attribute_id, attribute_value, price, discount_price, stock, rating, status, available_status, approval_status
- **restaurant_menu_items**: Has preparation_time, serial_number (unique), restaurant_attribute_id, attribute_value, price, discount_price, stock, rating, available_status, approval_status, status

### Tables Not Mentioned in Planning Doc (Existing):
- `product_stock_history` - Generic stock movements
- `grocery_item_stocklogs` - Grocery item stock logs
- `restaurant_item_stocklogs` - Restaurant item stock logs
- `customer_wallets` - Wallet balance per user
- `customer_wallet_transactions` - Wallet transaction history
- `reward_point_logs` - Points earned/redeemed
- `vendor_payments` - Vendor payout records
- `order_status_logs` - Order status history
- `order_durations` - Order timing
- `order_earnings` - Order earnings tracking
- `delivery_boy_earnings` - Driver earnings
- `delivery_boy_payout` - Driver payout records
- `baskets`, `basket_items`, `basket_orders` - Basket products
- `carts`, `cart_items` - Shopping carts
- `chat_conversations`, `chat_messages` - Chat system
- `notifications` - Laravel notifications
- `product_rating_logs`, `vendor_rating_logs`, `delivery_staff_rating_logs` - Rating logs
- `restaurant_item_working_hours`, `restaurant_item_time_slots` - Restaurant item timing
- `vendor_working_hours`, `vendor_leaves` - Vendor timing
- `sliders` - Homepage sliders
- `general` - Global settings
- `faq`, `terms`, `privacy_policy`, `about`, `return_policy`, `shipping_policy` - Static content
- `contact_us` - Contact submissions
- `splash_screen` - App splash screen
- `delivery_boy_requests` - Delivery staff requests
- `personal_access_tokens` - Sanctum API tokens

---

## 8. Data Type Consistency Guidelines

### 8.1 Decimal Precision

**Money/Price Fields:**
- Prices, costs: `decimal(10, 2)` - Up to 99,999,999.99
- Large totals (orders, balances): `decimal(12, 2)` - Up to 9,999,999,999.99
- Percentages, discounts: `decimal(5, 2)` - Up to 999.99%
- Tax rates: `decimal(5, 2)` - Up to 999.99%

**Examples:**
- `price`, `discount_price`, `unit_price`: `decimal(10, 2)`
- `total`, `subtotal`, `balance`: `decimal(12, 2)`
- `discount_percent`, `tax_rate`: `decimal(5, 2)`

### 8.2 Integer Types

- Quantities, counts: `integer()` - Standard integer
- Status codes: `integer()` or `enum()` - Use enum for named statuses
- Stock levels: `integer()` - Can be negative for adjustments

### 8.3 String Lengths

- Names, titles: `string(255)` - Standard length
- Codes (SKU, barcode, PO number): `string(50)` - Short codes
- Short codes (warehouse, department): `string(20)` - Very short
- URLs, paths: `string(255)` or `text()` - For long paths
- Notes, descriptions: `text()` - Unlimited length
- PINs, passwords: `string(6)` or `string(255)` - PINs short, passwords hashed

### 8.4 Foreign Keys

**Naming Convention:**
- Use `foreignId('table_name_id')` for automatic FK creation
- Use `constrained()` for automatic constraint naming
- Use `nullOnDelete()` for nullable FKs
- Use `cascadeOnDelete()` for required FKs

**Examples:**
```php
$table->foreignId('vendor_id')->constrained('vendors')->cascadeOnDelete();
$table->foreignId('user_id')->nullable()->constrained('users')->nullOnDelete();
```

### 8.5 Indexes

**Common Index Patterns:**
- Single column: `$table->index('column_name')`
- Composite: `$table->index(['vendor_id', 'status'])`
- Unique: `$table->unique(['user_id', 'vendor_id'])`

**Indexes to Add:**
- All foreign keys (automatic in Laravel)
- Status columns for filtering
- Date columns for sorting
- Composite indexes for common query patterns

## 9. Additional Migration Notes

### 8.1 Foreign Key Constraints

**Important:** When adding foreign keys in alteration migrations, ensure:
- Referenced tables exist before creating the FK
- Use `->constrained()` for automatic FK naming
- Use `->nullOnDelete()` for nullable FKs that should not cascade
- Use `->cascadeOnDelete()` for required FKs that should cascade

### 8.2 Indexes

Add indexes for frequently queried columns:
- `vendor_id` + `status` combinations
- `user_id` + `created_at` for audit trails
- `product_type` + `product_id` for polymorphic queries
- `order_id` for order-related tables
- `pos_shift_id` for POS-related queries

### 8.3 Enum Values

**Payment Types:** Current system uses integers (1=Cash, 2=Online). New `payment_type_name` enum provides clarity while maintaining backward compatibility.

**Order Status:** Current system uses integers (1-10). Consider keeping for backward compatibility or mapping to enum values.

**Product Types:** Use enum('grocery','restaurant') consistently across all new tables.

### 8.4 Soft Deletes

**Tables with softDeletes (existing):**
- users, vendors, grocery_menus, restaurant_menus, coupons

**New tables that may need softDeletes:**
- suppliers, warehouses, departments (for data retention)

**Tables that should NOT have softDeletes:**
- pos_shifts, pos_held_transactions (historical records)
- audit_logs, sync_queue (audit trail)
- payments, refunds (financial records)

### 8.5 Timestamps

All new tables should include `timestamps()` (created_at, updated_at).

**Additional timestamp fields:**
- `opened_at`, `closed_at` for shifts
- `resumed_at` for held transactions
- `approved_at` for approvals
- `paid_at` for payments
- `synced_at` for sync queue

## 10. Migration Checklist

### Pre-Migration Checklist
- [ ] Review all existing migrations to understand current structure
- [ ] Verify foreign key dependencies
- [ ] Check for any conflicting column names
- [ ] Ensure migration timestamps are sequential
- [ ] Test migrations on development database first

### Migration Creation Checklist
- [ ] Use proper migration naming convention
- [ ] Include all required columns with correct data types
- [ ] Add foreign key constraints with proper cascade rules
- [ ] Add indexes for frequently queried columns
- [ ] Include timestamps() on all new tables
- [ ] Add comments for complex fields
- [ ] Test migration up() and down() methods

### Post-Migration Checklist
- [ ] Run migrations in correct order
- [ ] Verify all tables created successfully
- [ ] Check foreign key constraints
- [ ] Verify indexes created
- [ ] Test rollback functionality
- [ ] Update Eloquent models
- [ ] Create/update seeders
- [ ] Update API documentation

## 11. Next Steps

1. Create migration files in `saimpex-backend/database/migrations/` following the order in Section 6.
2. Use consistent naming: `YYYY_MM_DD_HHMMSS_description.php`
3. Run `php artisan migrate` after each phase to verify.
4. Create/update Eloquent models for new tables.
5. Implement API endpoints for POS, CRM, ERP, HRM as per REQUIREMENTS_DOCUMENTATION.md.
6. Add seeders for:
   - Default price levels (Retail 0%, Wholesale 10%, VIP 15%)
   - Role permissions (Cashier, Manager, Stock, Employee)
   - Default departments (Sales, Warehouse, Admin, etc.)
7. Update existing models (Order, User, Payment, etc.) with new relationships and attributes.
8. Add validation rules for new fields in request classes.
9. Update API documentation with new endpoints and fields.

---

## 12. Migration File Naming Convention

All new migrations should follow Laravel's naming convention:
- Format: `YYYY_MM_DD_HHMMSS_description.php`
- Example: `2026_02_10_143000_add_pos_fields_to_orders_table.php`
- Example: `2026_02_10_143100_create_pos_shifts_table.php`

**Recommended timestamp prefixes for new migrations:**
- Phase 1-2: `2026_02_10_140000_*` (Base tables and alterations)
- Phase 3: `2026_02_10_141000_*` (POS core)
- Phase 4: `2026_02_10_142000_*` (CRM)
- Phase 5: `2026_02_10_143000_*` (ERP)
- Phase 6: `2026_02_10_144000_*` (Cash Management)
- Phase 7: `2026_02_10_145000_*` (HRM)
- Phase 8: `2026_02_10_146000_*` (Sync & Audit)
- Phase 9: `2026_02_10_147000_*` (Table alterations)

## 13. References

- **Existing backend:** `/Users/sigosoft/saimpex/saimpex-backend`
- **Design & features:** `FEATURE_CHECKLIST.md`, `REQUIREMENTS_DOCUMENTATION.md`
- **Frontend design:** `saimpex-exe-design` (Vue 3 + Electron)
- **Current migrations:** `database/migrations/` directory