Here's the full plan. First the ERD showing all table relationships, then the complete implementation plan below it.---

## Complete migration files

### `2024_01_01_000001_create_delivery_providers_table.php`

```php
Schema::create('delivery_providers', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('slug')->unique();            // 'pathao', 'redx', 'steadfast'
    $table->boolean('is_active')->default(true);
    $table->json('credentials')->nullable();     // encrypted: api_key, secret, base_url
    $table->json('config')->nullable();          // weight_limit, zone_map, extra_params
    $table->unsignedInteger('sort_order')->default(0);
    $table->timestamps();
    $table->softDeletes();
});
```

### `2024_01_01_000002_create_delivery_settings_table.php`

```php
Schema::create('delivery_settings', function (Blueprint $table) {
    $table->id();
    $table->foreignId('default_provider_id')
          ->nullable()
          ->constrained('delivery_providers')
          ->nullOnDelete();
    $table->string('business_name');
    $table->string('address');
    $table->string('district');
    $table->string('postcode');
    $table->string('phone')->nullable();
    $table->decimal('lat', 10, 7)->nullable();
    $table->decimal('lng', 10, 7)->nullable();
    $table->timestamps();
    // Single-row settings table — enforced in the model
});
```

### `2024_01_01_000003_create_product_delivery_settings_table.php`

```php
Schema::create('product_delivery_settings', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained()->cascadeOnDelete();
    $table->foreignId('provider_id')
          ->nullable()
          ->constrained('delivery_providers')
          ->nullOnDelete();     // null = use global default
    $table->boolean('use_global_provider')->default(true);
    $table->decimal('weight_kg', 8, 3)->nullable();
    $table->decimal('length_cm', 8, 2)->nullable();
    $table->decimal('width_cm',  8, 2)->nullable();
    $table->decimal('height_cm', 8, 2)->nullable();
    $table->json('extra')->nullable();           // any provider-specific fields
    $table->timestamps();

    $table->unique('product_id');               // one row per product
});
```

### `2024_01_01_000004_create_delivery_cost_cache_table.php`

```php
Schema::create('delivery_cost_cache', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained()->cascadeOnDelete();
    $table->foreignId('provider_id')->constrained('delivery_providers')->cascadeOnDelete();
    $table->string('destination_district');
    $table->string('destination_postcode')->nullable();
    $table->decimal('cost', 10, 2);
    $table->unsignedSmallInt('estimated_days')->nullable();
    $table->json('meta')->nullable();            // raw API response extras
    $table->timestamp('calculated_at');
    $table->timestamp('expires_at')->nullable();
    $table->timestamps();

    $table->unique(
        ['product_id', 'provider_id', 'destination_district', 'destination_postcode'],
        'delivery_cost_cache_unique'
    );
    $table->index(['product_id', 'provider_id']);
    $table->index('expires_at');
});
```

### `2024_01_01_000005_add_delivery_columns_to_orders_table.php`

```php
Schema::table('orders', function (Blueprint $table) {
    $table->foreignId('delivery_provider_id')
          ->nullable()
          ->after('id')
          ->constrained('delivery_providers')
          ->nullOnDelete();
    $table->string('delivery_tracking_id')->nullable()->after('delivery_provider_id');
    $table->decimal('delivery_cost', 10, 2)->default(0)->after('delivery_tracking_id');
    $table->decimal('extra_delivery_cost', 10, 2)->default(0)->after('delivery_cost');
    $table->string('delivery_status')->default('pending')->after('extra_delivery_cost');
    $table->json('delivery_address')->nullable()->after('delivery_status');
    // district, postcode, area stored in JSON to avoid extra columns
    $table->timestamp('delivery_dispatched_at')->nullable();
    $table->timestamp('delivery_completed_at')->nullable();

    $table->index('delivery_tracking_id');
    $table->index('delivery_status');
});
```

### `2024_01_01_000006_create_delivery_status_logs_table.php`

```php
Schema::create('delivery_status_logs', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained()->cascadeOnDelete();
    $table->foreignId('changed_by')
          ->nullable()
          ->constrained('users')
          ->nullOnDelete();       // null = webhook/system
    $table->string('from_status')->nullable();
    $table->string('to_status');
    $table->enum('source', ['webhook', 'admin', 'system'])->default('system');
    $table->decimal('extra_charge', 10, 2)->default(0);
    $table->text('note')->nullable();
    $table->json('raw_payload')->nullable();     // full webhook body
    $table->timestamp('created_at');

    $table->index('order_id');
});
```

### `2024_01_01_000007_create_delivery_webhook_logs_table.php`

```php
Schema::create('delivery_webhook_logs', function (Blueprint $table) {
    $table->id();
    $table->foreignId('provider_id')
          ->nullable()
          ->constrained('delivery_providers')
          ->nullOnDelete();
    $table->string('tracking_id')->nullable();
    $table->json('headers')->nullable();
    $table->json('payload');
    $table->enum('status', ['received', 'processed', 'failed'])->default('received');
    $table->text('error')->nullable();
    $table->timestamp('received_at')->useCurrent();

    $table->index(['provider_id', 'tracking_id']);
    $table->index('status');
});
```

---

## Full project file structure

```
app/
│
├── Contracts/
│   └── DeliveryProviderContract.php
│
├── DataObjects/
│   ├── DeliveryCostResult.php       // cost, estimated_days, meta
│   ├── DeliveryPayload.php          // weight, dims, origin, destination
│   └── ShipmentResult.php           // tracking_id, label_url, raw
│
├── Drivers/Delivery/
│   ├── PathaoDriver.php
│   ├── RedxDriver.php
│   ├── SteadfastDriver.php
│   ├── SundarbanDriver.php
│   └── ManualDriver.php             // flat-rate fallback
│
├── Services/
│   ├── DeliveryCostCalculationService.php
│   ├── DeliveryProviderResolverService.php
│   └── OrderDeliveryService.php     // status updates, extra charges
│
├── Models/
│   ├── DeliveryProvider.php
│   ├── DeliverySetting.php          // singleton: DeliverySetting::instance()
│   ├── ProductDeliverySetting.php
│   ├── DeliveryCostCache.php
│   ├── DeliveryStatusLog.php
│   └── DeliveryWebhookLog.php
│
├── Http/Controllers/
│   ├── Admin/
│   │   ├── DeliveryProviderController.php   // CRUD, activate, set default
│   │   ├── DeliverySettingController.php    // business location form
│   │   └── OrderDeliveryController.php      // change provider, manual status
│   ├── Api/
│   │   └── DeliveryCostController.php       // GET /delivery/cost?product_id&district&postcode
│   └── Webhook/
│       └── DeliveryCallbackController.php   // POST /webhooks/delivery/{slug}
│
├── Http/Requests/
│   ├── UpdateDeliverySettingRequest.php
│   ├── UpdateProductDeliveryRequest.php
│   └── ManualStatusUpdateRequest.php
│
├── Events/
│   ├── DefaultProviderChanged.php
│   └── DeliveryStatusUpdated.php
│
├── Listeners/
│   ├── DispatchCostRecalculationJob.php
│   └── SendDeliveryStatusNotification.php
│
├── Jobs/
│   └── RecalculateDeliveryCostsJob.php
│
├── Console/Commands/
│   └── PrecalculateDeliveryCosts.php
│
├── Observers/
│   └── DeliverySettingObserver.php   // fires DefaultProviderChanged on update
│
└── Providers/
    └── DeliveryServiceProvider.php   // registers driver map from config
```

---

## Key class implementations

### `DeliveryProviderContract.php`

```php
interface DeliveryProviderContract
{
    public function calculateCost(DeliveryPayload $payload): DeliveryCostResult;
    public function createShipment(Order $order): ShipmentResult;
    public function trackShipment(string $trackingId): TrackingResult;
    public function handleWebhook(Request $request): void;
    public function verifyWebhookSignature(Request $request): bool;
}
```

### `DeliveryCostCalculationService.php`

```php
class DeliveryCostCalculationService
{
    public function calculate(
        int $productId,
        string $district,
        string $postcode,
        ?int $providerId = null
    ): DeliveryCostResult {

        $providerId = $this->resolver->resolve($productId, $providerId);
        $cacheKey   = "dlv:{$productId}:{$providerId}:{$district}:{$postcode}";

        // 1. Check DB cache first (pre-calculated batch)
        $cached = DeliveryCostCache::where([
            'product_id'           => $productId,
            'provider_id'          => $providerId,
            'destination_district' => $district,
            'destination_postcode' => $postcode,
        ])->where('expires_at', '>', now())->first();

        if ($cached) {
            return DeliveryCostResult::fromCache($cached);
        }

        // 2. Live API call, store result in DB cache
        $driver  = app(DeliveryServiceProvider::class)->driver($providerId);
        $payload = $this->buildPayload($productId, $district, $postcode);
        $result  = $driver->calculateCost($payload);

        DeliveryCostCache::updateOrCreate(
            ['product_id' => $productId, 'provider_id' => $providerId,
             'destination_district' => $district, 'destination_postcode' => $postcode],
            ['cost' => $result->cost, 'meta' => $result->meta,
             'calculated_at' => now(), 'expires_at' => now()->addHours(6)]
        );

        return $result;
    }
}
```

### `DeliveryProviderResolverService.php`

```php
class DeliveryProviderResolverService
{
    public function resolve(int $productId, ?int $overrideId = null): int
    {
        // Order-level override wins first
        if ($overrideId) return $overrideId;

        // Product-level override next
        $productSetting = ProductDeliverySetting::where('product_id', $productId)
            ->where('use_global_provider', false)
            ->first();

        if ($productSetting?->provider_id) {
            return $productSetting->provider_id;
        }

        // Global default last
        return DeliverySetting::instance()->default_provider_id;
    }
}
```

### `RecalculateDeliveryCostsJob.php`

```php
class RecalculateDeliveryCostsJob implements ShouldQueue
{
    use Dispatchable, Queueable;

    public function __construct(
        public readonly int $providerId,
        public readonly bool $flushAll = false
    ) {}

    public function handle(DeliveryCostCalculationService $service): void
    {
        // Bust old cache rows for this provider
        DeliveryCostCache::where('provider_id', $this->providerId)->delete();

        // Re-calculate for all active products in chunks
        Product::active()->with('deliverySetting')
            ->chunkById(100, function ($products) use ($service) {
                foreach ($products as $product) {
                    foreach (config('delivery.precalculate_zones') as $zone) {
                        rescue(fn() => $service->calculate(
                            $product->id,
                            $zone['district'],
                            $zone['postcode'],
                            $this->providerId
                        ));
                    }
                }
            });
    }
}
```

### `DeliveryCallbackController.php`

```php
class DeliveryCallbackController extends Controller
{
    public function __invoke(Request $request, string $slug): JsonResponse
    {
        $provider = DeliveryProvider::where('slug', $slug)->firstOrFail();
        $driver   = app(DeliveryServiceProvider::class)->driverForModel($provider);

        // Log raw payload immediately, before any processing
        $log = DeliveryWebhookLog::create([
            'provider_id' => $provider->id,
            'tracking_id' => $request->input('tracking_id'),
            'headers'     => $request->headers->all(),
            'payload'     => $request->all(),
            'received_at' => now(),
        ]);

        if (! $driver->verifyWebhookSignature($request)) {
            $log->update(['status' => 'failed', 'error' => 'Signature mismatch']);
            return response()->json(['ok' => false], 401);
        }

        try {
            $driver->handleWebhook($request);
            $log->update(['status' => 'processed']);
        } catch (\Throwable $e) {
            $log->update(['status' => 'failed', 'error' => $e->getMessage()]);
            report($e);
        }

        return response()->json(['ok' => true]);
    }
}
```

### `OrderDeliveryService.php`

```php
class OrderDeliveryService
{
    public function updateStatus(
        Order $order,
        string $toStatus,
        string $source = 'system',
        ?float $extraCharge = null,
        ?User $changedBy = null,
        array $rawPayload = []
    ): void {
        $from = $order->delivery_status;

        DeliveryStatusLog::create([
            'order_id'     => $order->id,
            'changed_by'   => $changedBy?->id,
            'from_status'  => $from,
            'to_status'    => $toStatus,
            'source'       => $source,
            'extra_charge' => $extraCharge ?? 0,
            'raw_payload'  => $rawPayload,
        ]);

        $order->update([
            'delivery_status'     => $toStatus,
            'extra_delivery_cost' => $order->extra_delivery_cost + ($extraCharge ?? 0),
        ]);

        event(new DeliveryStatusUpdated($order, $from, $toStatus, $extraCharge));
    }

    public function changeProvider(Order $order, int $newProviderId): void
    {
        $order->update(['delivery_provider_id' => $newProviderId]);

        // Recalculate and update delivery_cost on the order
        $result = app(DeliveryCostCalculationService::class)->calculate(
            $order->product_id,          // adjust for multi-product orders
            $order->delivery_address['district'],
            $order->delivery_address['postcode'],
            $newProviderId
        );

        $order->update(['delivery_cost' => $result->cost]);
    }
}
```

---

## Artisan command

```php
// php artisan delivery:precalculate {--provider=all} {--chunk=100} {--zones=}

class PrecalculateDeliveryCosts extends Command
{
    protected $signature = 'delivery:precalculate
                            {--provider=all : provider slug or "all"}
                            {--chunk=100    : products per chunk}
                            {--flush        : delete existing cache first}';

    public function handle(DeliveryCostCalculationService $service): int
    {
        $providers = $this->option('provider') === 'all'
            ? DeliveryProvider::active()->get()
            : DeliveryProvider::where('slug', $this->option('provider'))->get();

        $zones = config('delivery.precalculate_zones'); // array of {district, postcode}

        foreach ($providers as $provider) {
            if ($this->option('flush')) {
                DeliveryCostCache::where('provider_id', $provider->id)->delete();
            }

            $bar = $this->output->createProgressBar(Product::active()->count());

            Product::active()->chunkById((int) $this->option('chunk'), function ($products) use ($service, $provider, $zones, $bar) {
                foreach ($products as $product) {
                    foreach ($zones as $zone) {
                        rescue(fn() => $service->calculate(
                            $product->id, $zone['district'], $zone['postcode'], $provider->id
                        ));
                    }
                    $bar->advance();
                }
            });

            $bar->finish();
            $this->newLine();
        }

        return self::SUCCESS;
    }
}
```

---

## Config file — `config/delivery.php`

```php
return [
    'default' => env('DELIVERY_DEFAULT_PROVIDER', 'manual'),

    'drivers' => [
        'pathao'    => App\Drivers\Delivery\PathaoDriver::class,
        'redx'      => App\Drivers\Delivery\RedxDriver::class,
        'steadfast' => App\Drivers\Delivery\SteadfastDriver::class,
        'sundarban' => App\Drivers\Delivery\SundarbanDriver::class,
        'manual'    => App\Drivers\Delivery\ManualDriver::class,
    ],

    'cache_ttl_hours' => env('DELIVERY_CACHE_TTL', 6),

    // Zones pre-calculated by the artisan command
    'precalculate_zones' => [
        ['district' => 'Dhaka',     'postcode' => '1000'],
        ['district' => 'Chittagong','postcode' => '4000'],
        ['district' => 'Sylhet',    'postcode' => '3100'],
        // add more as needed
    ],

    'webhook_route_prefix' => 'webhooks/delivery',

    'status_map' => [
        // normalize each provider's native statuses to your internal enum
        'pathao' => [
            'Pending'   => 'pending',
            'Pickup'    => 'picked_up',
            'In Transit'=> 'in_transit',
            'Delivered' => 'delivered',
            'Cancelled' => 'failed',
        ],
        'redx' => [
            'created'   => 'pending',
            'picked'    => 'picked_up',
            'in-transit'=> 'in_transit',
            'delivered' => 'delivered',
            'returned'  => 'returned',
        ],
    ],
];
```

---

## Routes — `routes/web.php` and `routes/api.php`

```php
// Admin routes
Route::prefix('admin')->middleware(['auth', 'admin'])->group(function () {
    Route::resource('delivery/providers', DeliveryProviderController::class);
    Route::post('delivery/providers/{provider}/set-default', [DeliveryProviderController::class, 'setDefault']);
    Route::get('delivery/settings', [DeliverySettingController::class, 'edit']);
    Route::put('delivery/settings', [DeliverySettingController::class, 'update']);
    Route::put('orders/{order}/delivery', [OrderDeliveryController::class, 'update']);
    Route::post('orders/{order}/delivery/status', [OrderDeliveryController::class, 'updateStatus']);
});

// Webhook endpoint (no auth, provider signs the payload)
Route::post('webhooks/delivery/{slug}', DeliveryCallbackController::class)
     ->name('webhook.delivery');

// API for web/app cost lookup
Route::get('api/delivery/cost', [DeliveryCostController::class, 'show'])
     ->middleware('throttle:60,1');
```

---

## Implementation phases

| Phase | What you build                                                                                                       | Milestone                                    |
| ----- | -------------------------------------------------------------------------------------------------------------------- | -------------------------------------------- |
| 1     | Migrations, models, `DeliverySetting` admin UI, product override panel                                               | Settings save and load                       |
| 2     | `DeliveryProviderContract`, `ManualDriver` (flat rate), `DeliveryCostCalculationService` wired up                    | End-to-end cost calculation with no real API |
| 3     | Real drivers one at a time (Pathao first) behind the contract                                                        | Live API cost on orders                      |
| 4     | DB cost cache, `PrecalculateDeliveryCosts` command, observer firing `RecalculateDeliveryCostsJob` on provider change | Zero live API calls at display time          |
| 5     | Webhook controller + `DeliveryWebhookLog`, `OrderDeliveryService::updateStatus`, manual admin status override        | Full status lifecycle                        |
| 6     | Web + app API endpoint, extra delivery charge flow, notifications on status change                                   | Production-ready                             |