# 🏗️ Smart MVP Database Schema - Implementation Summary

**Status**: ✅ Complete Implementation
**Date**: February 28, 2026
**Enum Handling**: All ENUMs converted to string columns (managed in code via Constants class)

---

## 📋 What Was Implemented

### ✅ Migrations (19 total)

#### Core Tables
1. **users** - Authentication + system access
   - role (string: admin, zonal, state, local, advert)
   - partner_id (FK → partners)
   - is_active (boolean)

2. **partner_tiers** - Business rules per tier
   - discount percentages (paint, plaster, polystyrene)
   - return percentages
   - logistics_subsidy_percent
   - registration_fee

3. **partners** - Business entity structure
   - Supports hierarchy (parent_partner_id)
   - Geographic data (state, lga, city, address)
   - Status tracking (pending, approved, suspended)

4. **products** - Full product catalog
   - category (string: paint, plasterboard, polystyrene)
   - base_price
   - is_active flag

5. **warehouses** - Inventory ownership nodes
   - partner_id (nullable for admin warehouse)
   - location

6. **inventories** - Current stock levels
   - warehouse_id + product_id (unique constraint)
   - quantity
   - Prevents negative stock

7. **inventory_transactions** - Audit trail
   - type (string: in, out, adjustment)
   - reference_type (string: sale, manual, transfer)
   - Complete audit history

8. **sales** - Transaction header
   - partner_id + warehouse_id + sale_date
   - total_amount

9. **sale_items** - Line items per sale
   - Product-level detail
   - Enables best-seller & dead stock analytics

10. **yearly_targets** - Separate target management
    - partner_id + year (unique)
    - Category-wise targets

11. **weekly_reports** - Compliance tracking only
    - partner_id + week_number + year (unique)
    - submitted_at timestamp

12. **partner_returns** - Finalized yearly results
    - partner_id + year (unique)
    - total_sales, total_return_amount, achieved_percent
    - finalized flag (prevents recalculation)

13. **promotions** - Time-limited incentives
    - product_id (nullable for category-wide promotions)
    - category (string: nullable for product-specific)
    - extra_return_percent

#### Legacy Tables (Kept for Reference)
- **sales_reports** - Old aggregated weekly data (deprecated)

#### System Tables
- password_reset_tokens, personal_access_tokens, cache, jobs (Laravel defaults)

---

## 📦 Eloquent Models (14 total)

**Core Models**:
- `User` - Authentication, role-based access
- `Partner` - Business entities with hierarchy support
- `PartnerTier` - Tier definitions
- `Product` - Product catalog
- `Warehouse` - Inventory locations
- `Inventory` - Stock levels
- `InventoryTransaction` - Stock audit trail
- `Sale` - Transaction headers
- `SaleItem` - Transaction line items
- `YearlyTarget` - Performance targets
- `WeeklyReport` - Compliance reporting
- `PartnerReturn` - Yearly finalized returns
- `Promotion` - Marketing incentives

**Relationships Configured**:
- Partner → Sales (hasMany)
- Partner → Warehouses (hasMany)
- Partner → YearlyTargets (hasMany)
- Partner → WeeklyReports (hasMany)
- Partner → PartnerReturns (hasMany)
- Warehouse → Inventories (hasMany)
- Product → Inventories, SaleItems, Transactions, Promotions
- Sale → SaleItems (hasMany)
- User → Partner (belongsTo)

---

## 🔤 Enum-to-Code Migration

### Constants File
**Location**: `app/Constants/Constants.php`

All enum-like values are now centralized here. This allows you to:
- ✅ Add/remove options without touching database
- ✅ Change validation rules easily
- ✅ Use display labels consistently

**Examples**:
```php
Constants::ROLE_ADMIN
Constants::ROLE_ZONAL
Constants::ROLE_STATE
Constants::ROLE_LOCAL
Constants::ROLE_ADVERT

Constants::STATUS_PENDING
Constants::STATUS_APPROVED
Constants::STATUS_SUSPENDED

Constants::CATEGORY_PAINT
Constants::CATEGORY_PLASTERBOARD
Constants::CATEGORY_POLYSTYRENE

Constants::TRANSACTION_TYPE_IN
Constants::TRANSACTION_TYPE_OUT
Constants::TRANSACTION_TYPE_ADJUSTMENT
```

**Display Labels**:
```php
Constants::getRoleLabel('admin')              // "Administrator"
Constants::getStatusLabel('pending')          // "Pending Approval"
Constants::getCategoryLabel('paint')          // "Paint"
Constants::getTransactionTypeLabel('in')      // "Stock In"
```

---

## 🌱 Seeders

**ProductSeeder** creates:
- 8 paint products with realistic SKUs & prices
- 3 plasterboard variants
- 1 polystyrene product

**PartnerTierSeeder** creates:
- Zonal tier (8-6-5% discounts, 3-2-1.5% returns)
- State tier (6-5-4% discounts, 2.5-1.5-1% returns)
- Local tier (4-3-2% discounts, 1.5-1-0.5% returns)

**DatabaseSeeder** orchestrates both seeders + creates admin user in local environment

---

## 🗂️ File Structure

```
app/
├── Models/
│   ├── User.php                  (Updated: role + partner_id)
│   ├── Partner.php               (Updated: new fields + relationships)
│   ├── PartnerTier.php          (Updated: new discount/return fields)
│   ├── Product.php              (New)
│   ├── Warehouse.php            (New)
│   ├── Inventory.php            (New)
│   ├── InventoryTransaction.php (New)
│   ├── Sale.php                 (New)
│   ├── SaleItem.php             (New)
│   ├── YearlyTarget.php         (New)
│   ├── WeeklyReport.php         (New)
│   ├── PartnerReturn.php        (New)
│   └── Promotion.php            (New)
│
├── Constants/
│   └── Constants.php            (New: All enum values + helpers)
│
database/
├── migrations/
│   ├── 2024_01_01_000004_create_partner_tiers_table.php       (Updated)
│   ├── 2024_01_01_000005_create_partners_table.php            (Updated)
│   ├── 2024_01_01_000008_create_products_table.php            (New)
│   ├── 2024_01_01_000009_create_warehouses_table.php          (New)
│   ├── 2024_01_01_000010_create_inventories_table.php         (New)
│   ├── 2024_01_01_000011_create_inventory_transactions_table.php (New)
│   ├── 2024_01_01_000012_create_sales_table.php              (New)
│   ├── 2024_01_01_000013_create_sale_items_table.php         (New)
│   ├── 2024_01_01_000014_create_yearly_targets_table.php     (New)
│   ├── 2024_01_01_000015_create_weekly_reports_table.php     (New)
│   ├── 2024_01_01_000016_create_partner_returns_table.php    (New)
│   └── 2024_01_01_000017_create_promotions_table.php         (New)
│
└── seeders/
    ├── PartnerTierSeeder.php     (Updated)
    ├── ProductSeeder.php         (New)
    └── DatabaseSeeder.php        (Updated)
```

---

## 🚀 How to Use

### Run All Migrations
```bash
php artisan migrate
# Or with seeders:
php artisan migrate:seed
```

### Create a New Sale (Example)
```php
$sale = Sale::create([
    'partner_id' => $partner->id,
    'warehouse_id' => $warehouse->id,
    'sale_date' => now()->date(),
    'total_amount' => 50000,
]);

foreach ($items as $item) {
    SaleItem::create([
        'sale_id' => $sale->id,
        'product_id' => $item['product_id'],
        'quantity' => $item['quantity'],
        'unit_price' => $item['unit_price'],
        'total_price' => $item['quantity'] * $item['unit_price'],
    ]);

    // Deduct from inventory
    $inventory = Inventory::updateOrCreate(
        ['warehouse_id' => $warehouse->id, 'product_id' => $item['product_id']],
        ['quantity' => DB::raw("quantity - {$item['quantity']}")]
    );

    // Log transaction
    InventoryTransaction::create([
        'warehouse_id' => $warehouse->id,
        'product_id' => $item['product_id'],
        'type' => Constants::TRANSACTION_TYPE_OUT,
        'quantity' => $item['quantity'],
        'reference_type' => Constants::REFERENCE_TYPE_SALE,
        'reference_id' => $sale->id,
    ]);
}
```

### Validate Role (Example)
```php
if ($user->role === Constants::ROLE_ADMIN) {
    // Admin-only action
}

if ($user->role === Constants::ROLE_ZONAL) {
    // Zonal-specific logic
}
```

### Get Partner Status (Example)
```php
if ($partner->isApproved()) {
    // Can access sales features
}

if ($partner->isSuspended()) {
    // Restricted access
}
```

---

## 🔍 Key Features Supported

✅ Warehouse inventory deduction
✅ Prevent negative stock (via App logic)
✅ Product performance analytics (via sale_items)
✅ Best-selling product (GROUP BY product_id, ORDER BY SUM(quantity))
✅ Dead stock detection (zero sales over period)
✅ Target % tracking (sales vs yearly_targets)
✅ Projection engine (historical sales trends)
✅ Return simulation (tier_id → return percentages)
✅ Weekly compliance (submitted_at tracking)
✅ Admin performance ranking (partner hierarchy analysis)
✅ Category revenue breakdown (sale_items with product.category)
✅ Inventory turnover rate (transactions / average_stock)
✅ Stock days remaining (quantity / avg_daily_consumption)
✅ Downline hierarchy (parent_partner_id relationships)
✅ Tier discount logic (PartnerTier percentages)

---

## 🧹 Cleanup Notes

1. **sales_reports table**: Kept for backward compatibility but deprecated
2. **Old migrations**: 2024_01_01_000007 is now a no-op (targets moved to new table)
3. **User model**: Updated from `is_admin` to `role` enum (string)
4. **Partner fields**: Simplified from payment/territory model to geographic/status model

---

## ✨ Next Steps

1. ✅ Run migrations: `php artisan migrate --seed`
2. ✅ Create API controllers for sales, inventory, targets
3. ✅ Build analytics queries (best-seller, compliance, performance)
4. ✅ Implement return calculation engine (yearly_targets + sales data)
5. ✅ Create inventory management endpoints (stock in/out)

---

**Questions or adjustments?** The schema is flexible — all enum-like values are in the Constants class, making it easy to adapt without database changes.