ระบบสต็อกใน Laravel ควรเก็บยอดคงเหลือยังไง ให้ไล่ย้อนได้

Schema::create('stock_movements', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained();
    $table->foreignId('warehouse_id')->constrained();
    $table->string('type');
    $table->integer('quantity');
    $table->decimal('unit_cost', 12, 2)->nullable();
    $table->nullableMorphs('source');
    $table->foreignId('user_id')->nullable()->constrained();
    $table->dateTime('moved_at');
    $table->string('note')->nullable();
    $table->timestamps();

    $table->index(['product_id', 'warehouse_id', 'moved_at']);
});

Schema::create('stock_balances', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained();
    $table->foreignId('warehouse_id')->constrained();
    $table->integer('quantity')->default(0);

    $table->unique(['product_id', 'warehouse_id']);
});

enum MovementType: string
{
    case Receive = 'receive';
    case Sale = 'sale';
    case TransferOut = 'transfer_out';
    case TransferIn = 'transfer_in';
    case Adjust = 'adjust';
    case Reverse = 'reverse';
}

class StockMovement extends Model
{
    protected $guarded = [];

    public function product(): BelongsTo
    {
        return $this->belongsTo(Product::class);
    }

    public function warehouse(): BelongsTo
    {
        return $this->belongsTo(Warehouse::class);
    }

    public function source(): MorphTo
    {
        return $this->morphTo();
    }

    protected function casts(): array
    {
        return [
            'type' => MovementType::class,
            'quantity' => 'integer',
            'unit_cost' => 'float',
            'moved_at' => 'datetime',
        ];
    }
}

class StockBalance extends Model
{
    public $timestamps = false;

    protected $guarded = [];

    public static function lockFor(int $productId, int $warehouseId): self
    {
        $locked = fn () => static::query()
            ->where('product_id', $productId)
            ->where('warehouse_id', $warehouseId)
            ->lockForUpdate();

        $balance = $locked()->first();

        if ($balance !== null) {
            return $balance;
        }

        static::query()->insertOrIgnore([
            'product_id' => $productId,
            'warehouse_id' => $warehouseId,
            'quantity' => 0,
        ]);

        return $locked()->sole();
    }

    protected function casts(): array
    {
        return ['quantity' => 'integer'];
    }
}

class RecordStockMovement
{
    public function handle(
        Product $product,
        Warehouse $warehouse,
        MovementType $type,
        int $quantity,
        ?Model $source = null,
        ?float $unitCost = null,
        ?CarbonInterface $movedAt = null,
        ?string $note = null,
    ): StockMovement {
        return DB::transaction(function () use ($product, $warehouse, $type, $quantity, $source, $unitCost, $movedAt, $note) {
            $balance = StockBalance::lockFor($product->id, $warehouse->id);

            if ($quantity < 0 && $balance->quantity + $quantity < 0) {
                throw new InsufficientStockException(
                    "{$product->name} ที่ {$warehouse->name} เหลือ {$balance->quantity} ชิ้น ตัดออก ".abs($quantity).' ชิ้นไม่ได้'
                );
            }

            $movement = StockMovement::create([
                'product_id' => $product->id,
                'warehouse_id' => $warehouse->id,
                'type' => $type,
                'quantity' => $quantity,
                'unit_cost' => $unitCost,
                'source_type' => $source?->getMorphClass(),
                'source_id' => $source?->getKey(),
                'user_id' => auth()->id(),
                'moved_at' => $movedAt ?? now(),
                'note' => $note,
            ]);

            $balance->increment('quantity', $quantity);

            return $movement;
        }, attempts: 3);
    }
}

class TransferStock
{
    public function __construct(private RecordStockMovement $recordStockMovement) {}

    public function handle(Product $product, Warehouse $from, Warehouse $to, int $quantity, ?Model $source = null): void
    {
        DB::transaction(function () use ($product, $from, $to, $quantity, $source) {
            collect([$from, $to])
                ->sortBy('id')
                ->each(fn (Warehouse $warehouse) => StockBalance::lockFor($product->id, $warehouse->id));

            $this->recordStockMovement->handle($product, $from, MovementType::TransferOut, -$quantity, $source);
            $this->recordStockMovement->handle($product, $to, MovementType::TransferIn, $quantity, $source);
        }, attempts: 3);
    }
}

class AdjustStock
{
    public function __construct(private RecordStockMovement $recordStockMovement) {}

    public function handle(Product $product, Warehouse $warehouse, int $counted, string $note): ?StockMovement
    {
        return DB::transaction(function () use ($product, $warehouse, $counted, $note) {
            $difference = $counted - StockBalance::lockFor($product->id, $warehouse->id)->quantity;

            if ($difference === 0) {
                return null;
            }

            return $this->recordStockMovement->handle(
                $product, $warehouse, MovementType::Adjust, $difference, note: $note,
            );
        });
    }
}

class ReverseStockMovements
{
    public function __construct(private RecordStockMovement $recordStockMovement) {}

    public function handle(Model $source): void
    {
        DB::transaction(function () use ($source) {
            StockMovement::query()
                ->whereMorphedTo('source', $source)
                ->where('type', '!=', MovementType::Reverse)
                ->with(['product', 'warehouse'])
                ->get()
                ->each(fn (StockMovement $movement) => $this->recordStockMovement->handle(
                    $movement->product,
                    $movement->warehouse,
                    MovementType::Reverse,
                    -$movement->quantity,
                    $source,
                    unitCost: $movement->unit_cost,
                    note: "ยกเลิกรายการ #{$movement->id}",
                ));
        });
    }
}

Artisan::command('stock:reconcile {--fix : แก้ยอดในตาราง stock_balances ให้ตรงกับรายการเคลื่อนไหว}', function () {
    $key = fn ($row) => "{$row->product_id}:{$row->warehouse_id}";

    $computed = StockMovement::query()
        ->select('product_id', 'warehouse_id')
        ->selectRaw('SUM(quantity) AS quantity')
        ->groupBy('product_id', 'warehouse_id')
        ->get()
        ->keyBy($key);

    $stored = StockBalance::query()->get()->keyBy($key);

    $drifts = $computed->keys()
        ->merge($stored->keys())
        ->unique()
        ->map(fn (string $pair) => [
            'product_id' => (int) str($pair)->before(':')->toString(),
            'warehouse_id' => (int) str($pair)->after(':')->toString(),
            'stored' => (int) ($stored->get($pair)?->quantity ?? 0),
            'computed' => (int) ($computed->get($pair)?->quantity ?? 0),
        ])
        ->reject(fn (array $row) => $row['stored'] === $row['computed']);

    if ($drifts->isEmpty()) {
        $this->components->info('ยอดคงเหลือตรงกับรายการเคลื่อนไหวทุกรายการ');

        return;
    }

    $this->table(['product_id', 'warehouse_id', 'ยอดในตาราง', 'ยอดจากรายการ'], $drifts->all());

    if (! $this->option('fix')) {
        $this->components->warn("ยอดไม่ตรง {$drifts->count()} รายการ รันซ้ำพร้อม --fix เพื่อแก้");

        return;
    }

    $drifts->each(fn (array $row) => DB::transaction(function () use ($row) {
        $balance = StockBalance::lockFor($row['product_id'], $row['warehouse_id']);

        $balance->update([
            'quantity' => (int) StockMovement::query()
                ->where('product_id', $row['product_id'])
                ->where('warehouse_id', $row['warehouse_id'])
                ->sum('quantity'),
        ]);
    }));

    $this->components->info("แก้ยอดแล้ว {$drifts->count()} รายการ");
})->purpose('เทียบยอดคงเหลือกับผลรวมของรายการเคลื่อนไหว');

Schedule::command('stock:reconcile')->dailyAt('02:00');

Schema::create('stock_value_reports', function (Blueprint $table) {
    $table->id();
    $table->date('month');
    $table->foreignId('product_id')->constrained();
    $table->integer('opening_qty');
    $table->decimal('opening_value', 14, 2);
    $table->integer('received_qty');
    $table->decimal('received_value', 14, 2);
    $table->integer('issued_qty');
    $table->integer('closing_qty');
    $table->decimal('average_cost', 12, 4);
    $table->decimal('closing_value', 14, 2);
    $table->timestamps();

    $table->unique(['month', 'product_id']);
});

class CalculateStockValueReport implements ShouldQueue
{
    use Queueable;

    public int $tries = 10;

    public int $maxExceptions = 2;

    public function __construct(public ?string $fromMonth = null) {}

    public function middleware(): array
    {
        return [(new WithoutOverlapping('stock-value-report'))->releaseAfter(30)->expireAfter(600)];
    }

    public function handle(): void
    {
        $firstMovedAt = StockMovement::query()->min('moved_at');

        if ($firstMovedAt === null) {
            return;
        }

        $month = CarbonImmutable::parse($this->fromMonth ?? $firstMovedAt)->startOfMonth();

        while ($month->lte(now())) {
            $this->calculateMonth($month);
            $month = $month->addMonthNoOverflow();
        }
    }

    private function calculateMonth(CarbonImmutable $month): void
    {
        $previous = StockValueReport::query()
            ->whereDate('month', $month->subMonthNoOverflow()->toDateString())
            ->get()
            ->keyBy('product_id');

        $movements = StockMovement::query()
            ->whereBetween('moved_at', [$month->startOfMonth(), $month->endOfMonth()])
            ->groupBy('product_id')
            ->select('product_id')
            ->selectRaw('SUM(CASE WHEN unit_cost IS NOT NULL THEN quantity ELSE 0 END) AS received_qty')
            ->selectRaw('SUM(CASE WHEN unit_cost IS NOT NULL THEN quantity * unit_cost ELSE 0 END) AS received_value')
            ->selectRaw('SUM(CASE WHEN unit_cost IS NULL THEN -quantity ELSE 0 END) AS issued_qty')
            ->get()
            ->keyBy('product_id');

        $reports = $previous->keys()
            ->merge($movements->keys())
            ->unique()
            ->map(function (int $productId) use ($month, $previous, $movements) {
                $openingQty = $previous->get($productId)?->closing_qty ?? 0;
                $openingValue = (float) ($previous->get($productId)?->closing_value ?? 0);
                $receivedQty = (int) ($movements->get($productId)?->received_qty ?? 0);
                $receivedValue = (float) ($movements->get($productId)?->received_value ?? 0);
                $issuedQty = (int) ($movements->get($productId)?->issued_qty ?? 0);

                $availableQty = $openingQty + $receivedQty;
                $averageCost = $availableQty > 0 ? ($openingValue + $receivedValue) / $availableQty : 0;
                $closingQty = $availableQty - $issuedQty;

                return [
                    'month' => $month->toDateString(),
                    'product_id' => $productId,
                    'opening_qty' => $openingQty,
                    'opening_value' => $openingValue,
                    'received_qty' => $receivedQty,
                    'received_value' => $receivedValue,
                    'issued_qty' => $issuedQty,
                    'closing_qty' => $closingQty,
                    'average_cost' => round($averageCost, 4),
                    'closing_value' => round($closingQty * $averageCost, 2),
                ];
            })
            ->reject(fn (array $row) => $row['closing_qty'] === 0 && $row['received_qty'] === 0 && $row['issued_qty'] === 0)
            ->values();

        DB::transaction(function () use ($month, $reports) {
            StockValueReport::upsert($reports->all(), uniqueBy: ['month', 'product_id']);

            StockValueReport::query()
                ->whereDate('month', $month->toDateString())
                ->whereNotIn('product_id', $reports->pluck('product_id'))
                ->delete();
        });
    }
}

Schedule::job(new CalculateStockValueReport)->dailyAt('03:00');