<?php

namespace App\Models;

use CodeIgniter\Model;

class StockBalanceModel extends Model
{
    protected $table         = 'stock_balances';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $dateFormat    = 'datetime';

    protected $allowedFields = [
        'product_id', 'warehouse_id', 'quantity', 'reserved_qty', 'updated_by',
    ];

    public function scopeForList(?string $search = null, ?int $warehouseId = null, ?int $productId = null, bool $lowStockOnly = false): self
    {
        $this->select('stock_balances.*, products.name AS product_name, products.sku, products.reorder_level, warehouses.name AS warehouse_name, warehouses.code AS warehouse_code, units_of_measure.code AS unit_code')
            ->join('products', 'products.id = stock_balances.product_id')
            ->join('warehouses', 'warehouses.id = stock_balances.warehouse_id')
            ->join('units_of_measure', 'units_of_measure.id = products.unit_id', 'left');

        if ($search) {
            $this->groupStart()
                ->like('products.name', $search)
                ->orLike('products.sku', $search)
                ->orLike('warehouses.name', $search)
                ->groupEnd();
        }

        if ($warehouseId) {
            $this->where('stock_balances.warehouse_id', $warehouseId);
        }

        if ($productId) {
            $this->where('stock_balances.product_id', $productId);
        }

        if ($lowStockOnly) {
            $this->where('stock_balances.quantity <= products.reorder_level', null, false)
                ->where('products.reorder_level >', 0);
        }

        return $this;
    }

    public function findByProductAndWarehouse(int $productId, int $warehouseId): ?array
    {
        return $this->where('product_id', $productId)
            ->where('warehouse_id', $warehouseId)
            ->first();
    }

    public function adjustQuantity(int $productId, int $warehouseId, float $delta, ?int $actingUserId = null): array
    {
        $row = $this->findByProductAndWarehouse($productId, $warehouseId);

        if ($row) {
            $newQty = (float) $row['quantity'] + $delta;
            if ($newQty < 0) {
                return ['success' => false, 'error' => 'Insufficient stock for this adjustment.'];
            }

            $this->update($row['id'], [
                'quantity'   => $newQty,
                'updated_by' => $actingUserId,
            ]);

            return ['success' => true, 'id' => (int) $row['id'], 'quantity' => $newQty];
        }

        if ($delta < 0) {
            return ['success' => false, 'error' => 'No stock balance exists for this product and warehouse.'];
        }

        $id = $this->insert([
            'product_id'   => $productId,
            'warehouse_id' => $warehouseId,
            'quantity'     => $delta,
            'reserved_qty' => 0,
            'updated_by'   => $actingUserId,
        ], true);

        return ['success' => true, 'id' => $id, 'quantity' => $delta];
    }
}
