# Relational Database Schema & Entity Architecture

This document details the relational database architecture, entity relationships, schema migrations, indexing strategies, and transactional locking mechanisms powering Eniceberny Bakery and Culinary Hub.

---

## 1. Entity Relationship Diagram (ERD)

```mermaid
erDiagram
    users ||--o{ orders : places
    users ||--o{ addresses : has
    users ||--o| customer_profiles : has
    categories ||--o{ products : contains
    categories ||--o{ categories : "parent / children"
    products ||--o{ product_variants : has
    products ||--o{ product_options : has
    products ||--o{ product_images : has
    products ||--o{ inventory_movements : tracks
    orders ||--o{ order_items : contains
    orders ||--o{ order_status_histories : logs
    orders ||--o{ payment_transactions : records
    carts ||--o{ cart_items : contains
    catering_services ||--o{ catering_packages : includes
    catering_inquiries ||--o| quotes : generates
    quotes ||--o{ quote_items : contains
    quotes ||--o| orders : converts_to
```

---

## 2. Table Specifications & Column Definitions

### 2.1 Identity, Access & Customers
- **`users`**:
  - `id` (bigint, PK)
  - `name` (string)
  - `email` (string, unique, index)
  - `phone` (string, nullable, index)
  - `password` (string, Bcrypt hash)
  - `role` (string, default: `'customer'`, index)
  - `email_verified_at` (timestamp, nullable)
  - `otp_code` (string, nullable)
  - `otp_expires_at` (timestamp, nullable)
  - `is_active` (boolean, default: `true`, index)
  - `permissions` (json, nullable)
- **`customer_profiles`**:
  - `id` (bigint, PK)
  - `user_id` (bigint, FK -> `users.id`, unique)
  - `total_spent` (decimal 10,2, default: `0.00`)
  - `total_orders` (integer, default: `0`)
  - `notes` (text, nullable)
- **`addresses`**:
  - `id` (bigint, PK)
  - `user_id` (bigint, FK -> `users.id`)
  - `recipient_name` (string)
  - `phone` (string)
  - `street` (string)
  - `city` (string)
  - `region` (string)
  - `landmark` (string, nullable)
  - `digital_address` (string, nullable, GhanaPostGPS code)
  - `is_default` (boolean, default: `false`)

---

### 2.2 Catalog & Inventory
- **`categories`**:
  - `id` (bigint, PK)
  - `name` (string)
  - `slug` (string, unique, index)
  - `description` (text, nullable)
  - `image_path` (string, nullable)
  - `parent_id` (bigint, nullable, FK -> `categories.id`)
  - `sort_order` (integer, default: `0`)
  - `is_active` (boolean, default: `true`, index)
- **`products`**:
  - `id` (bigint, PK)
  - `category_id` (bigint, FK -> `categories.id`, index)
  - `name` (string)
  - `slug` (string, unique, index)
  - `sku` (string, unique, index)
  - `description` (text, nullable)
  - `price` (decimal 10,2)
  - `sale_price` (decimal 10,2, nullable)
  - `track_inventory` (boolean, default: `false`)
  - `stock_quantity` (integer, default: `0`)
  - `is_available` (boolean, default: `true`, index)
  - `is_featured` (boolean, default: `false`, index)
  - `is_bestseller` (boolean, default: `false`, index)
  - `prep_time_minutes` (integer, default: `15`)
- **`product_variants`**:
  - `id` (bigint, PK)
  - `product_id` (bigint, FK -> `products.id`, cascade delete)
  - `name` (string, e.g. "8-inch", "Party Pan")
  - `price` (decimal 10,2)
  - `sku` (string, nullable)
  - `stock_quantity` (integer, default: `0`)
- **`inventory_movements`**:
  - `id` (bigint, PK)
  - `product_id` (bigint, FK -> `products.id`)
  - `user_id` (bigint, nullable, FK -> `users.id`)
  - `movement_type` (string, `'sale'`, `'pos_sale'`, `'restock'`, `'adjustment'`)
  - `quantity_change` (integer, positive or negative)
  - `quantity_after` (integer)
  - `reason` (string, nullable)

---

### 2.3 Orders & Commerce Ledger
- **`orders`**:
  - `id` (bigint, PK)
  - `order_number` (string, unique, index, e.g. `EB-20260927-1402`)
  - `user_id` (bigint, nullable, FK -> `users.id`, index)
  - `customer_name` (string)
  - `customer_email` (string)
  - `customer_phone` (string, index)
  - `source` (string, `'storefront'`, `'pos'`, `'catering'`, index)
  - `order_type` (string, `'delivery'`, `'pickup'`, `'dine_in'`, `'takeaway'`)
  - `status` (string, `'pending'`, `'confirmed'`, `'preparing'`, `'ready_for_pickup'`, `'out_for_delivery'`, `'delivered'`, `'cancelled'`, index)
  - `payment_status` (string, `'pending'`, `'paid'`, `'failed'`, `'refunded'`, index)
  - `payment_method` (string, `'cash'`, `'momo'`, `'paystack'`)
  - `subtotal` (decimal 10,2)
  - `delivery_fee` (decimal 10,2, default: `0.00`)
  - `discount_amount` (decimal 10,2, default: `0.00`)
  - `tax_amount` (decimal 10,2, default: `0.00`)
  - `total` (decimal 10,2)
  - `delivery_address_json` (json, nullable)
- **`order_items`**:
  - `id` (bigint, PK)
  - `order_id` (bigint, FK -> `orders.id`, cascade delete)
  - `product_id` (bigint, nullable, FK -> `products.id`)
  - `product_name` (string)
  - `variant_name` (string, nullable)
  - `unit_price` (decimal 10,2)
  - `quantity` (integer)
  - `subtotal` (decimal 10,2)
  - `custom_options` (json, nullable)
- **`payment_transactions`**:
  - `id` (bigint, PK)
  - `order_id` (bigint, FK -> `orders.id`)
  - `gateway` (string, `'cash'`, `'momo'`, `'paystack'`, index)
  - `reference` (string, unique, index)
  - `amount` (decimal 10,2)
  - `currency` (string, default: `'GHS'`)
  - `status` (string, `'pending'`, `'success'`, `'failed'`)
  - `provider_response` (json, nullable)

---

### 2.4 Catering & Quotation Subsystem
- **`catering_inquiries`**:
  - `id` (bigint, PK)
  - `name` (string)
  - `email` (string)
  - `phone` (string)
  - `event_type` (string)
  - `event_date` (date)
  - `guest_count` (integer)
  - `budget` (decimal 10,2, nullable)
  - `message` (text, nullable)
  - `status` (string, `'pending'`, `'under_review'`, `'quoted'`, `'confirmed'`, `'declined'`, index)
- **`quotes`**:
  - `id` (bigint, PK)
  - `quote_number` (string, unique, index, e.g. `QT-260927-4401`)
  - `catering_inquiry_id` (bigint, nullable, FK -> `catering_inquiries.id`)
  - `customer_name` (string)
  - `customer_email` (string)
  - `customer_phone` (string)
  - `event_date` (date)
  - `headcount` (integer)
  - `subtotal` (decimal 10,2)
  - `discount_amount` (decimal 10,2, default: `0.00`)
  - `tax_amount` (decimal 10,2, default: `0.00`)
  - `total` (decimal 10,2)
  - `status` (string, `'draft'`, `'sent'`, `'accepted'`, `'converted'`, `'expired'`, index)
  - `terms` (text, nullable)
- **`quote_items`**:
  - `id` (bigint, PK)
  - `quote_id` (bigint, FK -> `quotes.id`, cascade delete)
  - `description` (string)
  - `quantity` (integer)
  - `unit_price` (decimal 10,2)
  - `subtotal` (decimal 10,2)

---

## 3. Database Indexes & Performance Optimizations

1. **Composite & Foreign Indexes**:
   - `orders(status, created_at)`: Powers the executive dashboard live orders table and daily aggregation metrics.
   - `products(category_id, is_available)`: Optimizes public storefront shop filtering and category page rendering.
2. **Transaction Isolation**:
   - Critical checkout and stock deduction routines are locked within `DB::transaction()` to eliminate race conditions and overselling during simultaneous online and counter POS sales.