# Phase 2 — Database Design

## ER Overview

```
users ──< activity_logs
users ──< orders (cashier_id)
categories ──< products
products ──< stocks (1:1)
products ──< stock_histories
products ──< order_items
orders ──< order_items
orders ──< payments
orders ──< failed_payments
restaurant_settings (singleton)
printer_settings (singleton)
tax_settings (1:N — supports multiple tax slabs, GST etc.)
```

## Table Specifications

### users
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| name | string | |
| email | string unique | |
| email_verified_at | timestamp nullable | |
| password | string | |
| phone | string nullable | |
| avatar | string nullable | |
| is_active | boolean default true | |
| last_login_at | timestamp nullable | |
| remember_token | string | |
| timestamps | | |
| softDeletes | | |

Roles/permissions are handled by Spatie `roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions` tables (published via `vendor:publish`).

### categories
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| name | string | |
| slug | string unique | |
| description | text nullable | |
| image | string nullable | |
| sort_order | unsignedInteger default 0 | |
| status | enum(active,inactive) default active | |
| timestamps | | |
| softDeletes | | |

Indexes: `slug`, `status`, `sort_order`

### products
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| category_id | foreignId → categories | |
| name | string | |
| slug | string unique | |
| sku | string unique | |
| barcode | string nullable unique | |
| description | text nullable | |
| image | string nullable | |
| type | enum(veg,non_veg,egg) default veg | |
| price | decimal(10,2) | |
| gst_percentage | decimal(5,2) default 5.00 | |
| preparation_time | unsignedInteger (minutes) default 10 | |
| is_featured | boolean default false | |
| is_available | boolean default true | |
| status | enum(active,inactive) default active | |
| timestamps | | |
| softDeletes | | |

Indexes: `category_id`, `sku`, `barcode`, `status`, `is_available`, composite (`status`,`is_available`)

### stocks
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| product_id | foreignId unique → products | 1:1 with product |
| quantity | integer default 0 | |
| unit | enum(pcs,kg,ltr,plate,box) default pcs | |
| low_stock_threshold | integer default 10 | |
| track_stock | boolean default true | some items e.g. beverages may not track |
| timestamps | | |

### stock_histories
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| product_id | foreignId → products | |
| user_id | foreignId nullable → users | who made the adjustment |
| type | enum(in,out,adjustment,order_deduction) | |
| quantity_before | integer | |
| quantity_change | integer | can be negative |
| quantity_after | integer | |
| reason | string nullable | |
| reference_type | string nullable | polymorphic-ish reference (e.g. Order) |
| reference_id | unsignedBigInteger nullable | |
| timestamps | | |

Indexes: `product_id`, `type`, (`reference_type`,`reference_id`)

### orders
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| order_number | string unique | e.g. ORD-20260723-0001 |
| bill_number | string unique nullable | generated after payment success |
| cashier_id | foreignId → users | |
| subtotal | decimal(10,2) | |
| gst_amount | decimal(10,2) | |
| packing_charge | decimal(10,2) default 0 | |
| discount_amount | decimal(10,2) default 0 | |
| grand_total | decimal(10,2) | |
| order_status | enum(pending,paid,preparing,completed,cancelled) default pending | |
| payment_status | enum(pending,paid,failed,refunded) default pending | |
| notes | text nullable | |
| printed_at | timestamp nullable | |
| print_count | unsignedInteger default 0 | |
| cancelled_at | timestamp nullable | |
| cancellation_reason | string nullable | |
| timestamps | | |
| softDeletes | | |

Indexes: `order_number`, `bill_number`, `order_status`, `payment_status`, `cashier_id`, `created_at`

### order_items
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| order_id | foreignId → orders (cascade) | |
| product_id | foreignId → products (restrict) | |
| product_name | string | snapshot at time of order |
| unit_price | decimal(10,2) | snapshot |
| gst_percentage | decimal(5,2) | snapshot |
| quantity | unsignedInteger | |
| notes | string nullable | e.g. "less spicy" |
| total_price | decimal(10,2) | |
| timestamps | | |

### payments
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| order_id | foreignId → orders | |
| razorpay_order_id | string unique | |
| razorpay_payment_id | string nullable unique | |
| razorpay_signature | string nullable | |
| amount | decimal(10,2) | |
| currency | string default INR | |
| method | string nullable | upi, card etc |
| status | enum(pending,paid,failed,refunded) default pending | |
| webhook_verified | boolean default false | |
| webhook_payload | json nullable | raw payload for audit |
| paid_at | timestamp nullable | |
| timestamps | | |

Indexes: `order_id`, `razorpay_order_id`, `razorpay_payment_id`, `status`

### failed_payments
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | |
| order_id | foreignId → orders | |
| razorpay_order_id | string nullable | |
| razorpay_payment_id | string nullable | |
| reason | string nullable | |
| error_code | string nullable | |
| error_description | text nullable | |
| raw_payload | json nullable | |
| timestamps | | |

### restaurant_settings
Singleton row (id = 1 enforced in service layer).
| Column | Type |
|---|---|
| id | bigIncrements |
| name | string |
| address | text nullable |
| phone | string nullable |
| email | string nullable |
| gst_number | string nullable |
| currency | string default INR |
| currency_symbol | string default ₹ |
| timezone | string default Asia/Kolkata |
| logo | string nullable |
| receipt_footer | text nullable |
| timestamps | |

### printer_settings
| Column | Type |
|---|---|
| id | bigIncrements |
| name | string default "Default Printer" |
| connection_type | enum(network,usb) default network |
| ip_address | string nullable |
| port | integer default 9100 |
| usb_path | string nullable |
| paper_width | enum(58mm,80mm) default 80mm |
| auto_print | boolean default true |
| is_active | boolean default true |
| timestamps | |

### tax_settings
| Column | Type |
|---|---|
| id | bigIncrements |
| name | string (e.g. "CGST", "SGST", "GST") |
| percentage | decimal(5,2) |
| is_active | boolean default true |
| applies_to | enum(all,category,product) default all |
| timestamps | |

### activity_logs
Uses `spatie/laravel-activitylog` package conventions (`log_name`, `description`, `subject_type`, `subject_id`, `causer_type`, `causer_id`, `properties` json, `event`, timestamps) — we implement a lightweight custom version described in Phase 4 model.

## Enum Summary (mirrored as PHP backed enums in Phase 3+)

- OrderStatus: pending, paid, preparing, completed, cancelled
- PaymentStatus: pending, paid, failed, refunded
- ProductType: veg, non_veg, egg
- StockMovementType: in, out, adjustment, order_deduction
- Status (generic): active, inactive

## Foreign Key / Delete Strategy

- `products.category_id` → `restrict` (a category with products cannot be deleted; must be deactivated instead)
- `stocks.product_id` → `cascade` (deleting a product removes its stock row)
- `stock_histories.product_id` → `cascade`
- `order_items.order_id` → `cascade`
- `order_items.product_id` → `restrict` (preserve referential integrity; products are soft-deleted, never hard-deleted, once ordered)
- `payments.order_id` → `cascade`
- `failed_payments.order_id` → `cascade`

Next: **Phase 3 — full migration files.**