<?php

namespace App\Repositories\Warehouse;

use App\Repositories\BaseRepository;
use App\Models\Warehouse\Inventory;
use Illuminate\Database\Eloquent\Builder;
use Carbon\Carbon;

class InventoryRepository extends BaseRepository
{
    /**
     * Create a new repository instance.
     */
    public function __construct(Inventory $model)
    {
        $this->model = $model;
    }

    /**
     * Find inventory by item ID.
     */
    public function findByItemId(int $itemId): ?Inventory
    {
        return $this->model->where('item_id', $itemId)->first();
    }

    /**
     * Find inventory by item and location.
     */
    public function findByItemAndLocation(int $itemId, string $location): ?Inventory
    {
        return $this->model->where('item_id', $itemId)
                          ->where('location', $location)
                          ->first();
    }

    /**
     * Get inventory by location.
     */
    public function getByLocation(string $location)
    {
        return $this->model->where('location', $location)
                          ->with(['item', 'item.category'])
                          ->get();
    }

    /**
     * Get low stock items.
     */
    public function getLowStockItems()
    {
        return $this->model->join('items', 'inventories.item_id', '=', 'items.id')
                          ->whereColumn('inventories.quantity_available', '<=', 'items.reorder_point')
                          ->where('items.reorder_point', '>', 0)
                          ->where('items.status', 'active')
                          ->with(['item', 'item.category'])
                          ->get();
    }

    /**
     * Get aging inventory.
     */
    public function getAgingInventory(int $days = 90)
    {
        $date = Carbon::now()->subDays($days);

        return $this->model->where('updated_at', '<=', $date)
                          ->where('quantity_available', '>', 0)
                          ->with(['item', 'item.category', 'division'])
                          ->get();
    }

    /**
     * Get inventory movements for item.
     */
    public function getMovementsByItem(int $itemId, array $filters = [])
    {
        $query = $this->model->join('stock_movements', 'inventories.item_id', '=', 'stock_movements.item_id')
                            ->where('inventories.item_id', $itemId)
                            ->select('stock_movements.*');

        if (isset($filters['date_from'])) {
            $query->where('stock_movements.created_at', '>=', $filters['date_from']);
        }

        if (isset($filters['date_to'])) {
            $query->where('stock_movements.created_at', '<=', $filters['date_to']);
        }

        if (isset($filters['type'])) {
            $query->where('stock_movements.type', $filters['type']);
        }

        return $query->orderBy('stock_movements.created_at', 'desc')->get();
    }

    /**
     * Get inventory with reserved quantities.
     */
    public function getWithReservations()
    {
        return $this->model->where('quantity_reserved', '>', 0)
                          ->with(['item', 'item.category'])
                          ->get();
    }

    /**
     * Get inventory summary by location.
     */
    public function getSummaryByLocation()
    {
        return $this->model->selectRaw('
            location,
            COUNT(*) as total_items,
            SUM(quantity_available) as total_quantity,
            SUM(quantity_reserved) as total_reserved,
            SUM(quantity_available * unit_cost) as total_value
        ')
        ->groupBy('location')
        ->get();
    }

    /**
     * Apply inventory-specific search filters.
     */
    protected function applySearchFilter(Builder $query, $search)
    {
        return $query->whereHas('item', function ($q) use ($search) {
            $q->where('name', 'LIKE', "%{$search}%")
              ->orWhere('item_code', 'LIKE', "%{$search}%")
              ->orWhere('sku', 'LIKE', "%{$search}%");
        });
    }

    /**
     * Apply inventory-specific filters.
     */
    protected function applyFilters(Builder $query, array $filters)
    {
        foreach ($filters as $key => $value) {
            if ($value === null || $value === '') {
                continue;
            }

            switch ($key) {
                case 'location':
                    $query->where('location', $value);
                    break;
                case 'item_id':
                    $query->where('item_id', $value);
                    break;
                case 'low_stock':
                    if ($value) {
                        $query->join('items', 'inventories.item_id', '=', 'items.id')
                              ->whereColumn('inventories.quantity_available', '<=', 'items.reorder_point')
                              ->where('items.reorder_point', '>', 0);
                    }
                    break;
                case 'zero_stock':
                    if ($value) {
                        $query->where('quantity_available', 0);
                    }
                    break;
                case 'has_reserved':
                    if ($value) {
                        $query->where('quantity_reserved', '>', 0);
                    }
                    break;
                case 'updated_from':
                    $query->where('updated_at', '>=', $value);
                    break;
                case 'updated_to':
                    $query->where('updated_at', '<=', $value);
                    break;
                case 'search':
                    $this->applySearchFilter($query, $value);
                    break;
            }
        }

        return $query;
    }

    /**
     * Get inventory statistics.
     */
    public function getStatistics()
    {
        return [
            'total_locations' => $this->model->distinct('location')->count('location'),
            'total_items' => $this->model->count(),
            'total_quantity' => $this->model->sum('quantity_available'),
            'total_reserved' => $this->model->sum('quantity_reserved'),
            'total_value' => $this->model->selectRaw('SUM(quantity_available * unit_cost) as total_value')->value('total_value') ?? 0,
            'low_stock_items' => $this->getLowStockItems()->count(),
        ];
    }
}