Frosty — Cold Storage ERP

CREATE TABLE chambers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL, -- e.g., 'Chamber A'
    temperature_min DECIMAL(4,2) NOT NULL,
    temperature_max DECIMAL(4,2) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE rows_sectors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    chamber_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL, -- e.g., 'Row 12'
    FOREIGN KEY (chamber_id) REFERENCES chambers(id) ON DELETE CASCADE
);

CREATE TABLE bays (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sector_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL, -- e.g., 'Bay B-10'
    max_pallet_capacity INT UNSIGNED DEFAULT 10,
    current_pallet_count INT UNSIGNED DEFAULT 0,
    status ENUM('active', 'maintenance', 'full') DEFAULT 'active',
    FOREIGN KEY (sector_id) REFERENCES rows_sectors(id) ON DELETE CASCADE
);

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Bay;
use App\Models\Lot;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class BayAllocationController extends Controller
{
    public function allocatePallets(Request $request)
    {
        $validated = $request->validate([
            'bay_id' => 'required|exists:bays,id',
            'lot_id' => 'required|exists:lots,id',
            'pallet_count' => 'required|integer|min:1',
        ]);

        return DB::transaction(function () use ($validated) {
            // Apply row lock to prevent simultaneous updates
            $bay = Bay::where('id', $validated['bay_id'])
                ->lockForUpdate()
                ->firstOrFail();

            $lot = Lot::findOrFail($validated['lot_id']);

            // Validate temperature zones matching
            $chamber = $bay->sector->chamber;
            if ($lot->required_temp < $chamber->temperature_min || 
                $lot->required_temp > $chamber->temperature_max) {
                return response()->json(['error' => 'Chamber temperature invalid for crop type.'], 422);
            }

            // Capacity validation
            if (($bay->current_pallet_count + $validated['pallet_count']) > $bay->max_pallet_capacity) {
                return response()->json(['error' => 'Insufficient space in selected bay.'], 422);
            }

            // Assign stock and increment counter
            $bay->increment('current_pallet_count', $validated['pallet_count']);
            $lot->update([
                'bay_id' => $bay->id,
                'allocated_at' => now(),
            ]);

            return response()->json([
                'success' => true,
                'allocated_bay' => $bay->name,
                'occupancy' => "{$bay->current_pallet_count}/{$bay->max_pallet_capacity}"
            ]);
        });
    }
}

namespace App\Jobs;

use App\Models\Lot;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;

class ProcessLotRent implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $lot;

    public function __construct(Lot $lot)
    {
        $this->lot = $lot;
    }

    public function handle()
    {
        $activeDays = now()->diffInDays($this->lot->created_at);
        $dailyRate = $this->lot->client->billingRules->rate_per_day;
        
        $totalRentAccrued = $activeDays * $dailyRate;
        
        $this->lot->update([
            'rent_accrued' => $totalRentAccrued,
            'last_calculated_at' => now(),
        ]);
    }
}