<?php

namespace App\Repositories;

use App\Models\Category;
use App\Models\Product;
use App\Models\OrderItem;
use App\ProductRepositoryInterface;
use Illuminate\Support\Facades\DB;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Database\Eloquent\Collection;

class ProductRepository implements ProductRepositoryInterface
{
    public function find(int $id): ?Product
    {
        return Product::find($id);
    }

    public function findBySlug(string $slug): ?Product
    {
        return Product::where('slug', $slug)->first();
    }

    public function findActive(int $id): ?Product
    {
        return Product::active()->forWebsite()->find($id);
    }

    public function findActiveBySlug(string $slug): ?Product
    {
        return Product::active()
            ->forWebsite()
            ->where('slug', $slug)
            ->with(['category', 'unit', 'media'])
            ->first();
    }

    public function getActive(): Collection
    {
        return Product::active()->forWebsite()->get();
    }

    public function getFeatured(int $limit = 8): Collection
    {
        return Product::with(['category', 'unit', 'variants' => function($query) {
                $query->where('is_active', true)->forWebsite();
            }])
            ->whereNull('parent_id') // Only parent products in listings
            ->active()
            ->forWebsite()
            ->featured()
            ->inStock()
            ->latest()
            ->take($limit)
            ->get();
    }

    public function getInStock(int $limit = null): Collection
    {
        $query = Product::whereNull('parent_id') // Only parent products
            ->active()
            ->forWebsite()
            ->inStock();
        
        if ($limit) {
            $query->take($limit);
        }
        
        return $query->get();
    }

    public function getNewArrivals(int $limit = 8): Collection
    {
        return Product::with(['category', 'unit', 'variants' => function($query) {
                $query->where('is_active', true)->forWebsite();
            }])
            ->whereNull('parent_id') // Only parent products
            ->active()
            ->forWebsite()
            ->inStock()
            ->latest()
            ->take($limit)
            ->get();
    }

    public function getOnSale(int $limit = 8): Collection
    {
        return Product::with(['category', 'unit', 'variants' => function($query) {
                $query->where('is_active', true)->forWebsite();
            }])
            ->whereNull('parent_id') // Only parent products
            ->active()
            ->forWebsite()
            ->inStock()
            ->whereColumn('mrp', '>', 'price')
            ->whereNotNull('mrp')
            ->latest()
            ->take($limit)
            ->get();
    }

    public function getByCategory(int $categoryId, array $options = []): LengthAwarePaginator
    {
        $query = Product::with(['category', 'unit', 'variants' => function($query) {
                $query->where('is_active', true)->forWebsite();
            }])
            ->whereNull('parent_id') // Only parent products
            ->where('category_id', $categoryId)
            ->active()
            ->forWebsite();

        if ($options['in_stock'] ?? true) {
            $query->inStock();
        }

        if (isset($options['featured'])) {
            $query->featured();
        }

        $perPage = $options['per_page'] ?? 20;

        return $query->latest()->paginate($perPage);
    }

    public function getByCategories(array $categoryIds, array $options = []): LengthAwarePaginator
    {
        $query = Product::with(['category', 'unit', 'variants' => function ($query) {
                $query->where('is_active', true)->forWebsite();
            }])
            ->whereNull('parent_id') // Only parent products
            ->whereIn('category_id', $categoryIds)
            ->active()
            ->forWebsite();

        if ($options['in_stock'] ?? true) {
            $query->inStock();
        }

        if (isset($options['featured'])) {
            $query->featured();
        }
        
        $perPage = $options['per_page'] ?? 10;

        return $query->latest()->paginate($perPage);
    }

    public function search(string $query, array $filters = []): LengthAwarePaginator
    {
        $productQuery = Product::with(['category', 'unit', 'variants' => function($query) {
                $query->where('is_active', true)->forWebsite();
            }])
            ->whereNull('parent_id') // Only parent products
            ->active()
            ->forWebsite();

        // Apply search query
        if (!empty($query)) {
            $productQuery->where(function ($q) use ($query) {
                $q->where('name', 'LIKE', '%' . $query . '%')
                  ->orWhere('tags', 'LIKE', '%' . $query . '%');
            });
        }

        // Apply category filter
        if (!empty($filters['category_ids'])) {
            $productQuery->whereIn('category_id', $filters['category_ids']);
        }

        // Stock: collections/new-arrivals/all-products use in-stock only unless flags say otherwise.
        // Text product search includes matches even when out of stock (include_out_of_stock).
        if (!empty($filters['only_stockout'])) {
            $productQuery->outOfStock();
        } elseif (!empty($filters['include_out_of_stock'])) {
            // no stock scope
        } else {
            $productQuery->inStock();
        }

        $perPage = $filters['per_page'] ?? 20;

        $this->applySort($productQuery, $filters['sort'] ?? null);

        return $productQuery
            ->orderByRaw('CASE WHEN EXISTS (
                SELECT 1 FROM inventory_balances ib
                INNER JOIN locations l ON l.id = ib.location_id AND l.type = ? AND l.fulfills_online = 1 AND l.is_active = 1
                WHERE ib.product_id = products.id AND (ib.on_hand - ib.reserved - ib.damaged) > 0
            ) OR products.current_stock > 0 THEN 0 ELSE 1 END', ['store'])
            ->latest()
            ->paginate($perPage)
            ->withQueryString();
    }

    public function getRelated(int $productId, int $categoryId, int $limit = 4): Collection
    {
        $baseQuery = function () {
            return Product::whereNull('parent_id')
                ->with(['category', 'variants' => function ($query) {
                    $query->where('is_active', true)->forWebsite();
                }])
                ->active()
                ->forWebsite()
                ->inStock()
                ->latest();
        };

        $fromSameCategory = $baseQuery()
            ->where('category_id', $categoryId)
            ->where('id', '!=', $productId)
            ->take($limit)
            ->get();

        if ($fromSameCategory->count() >= $limit) {
            return $fromSameCategory;
        }

        $excludeIds = $fromSameCategory->pluck('id')->push($productId)->all();
        $need = $limit - $fromSameCategory->count();
        $category = Category::find($categoryId);

        $fallbackCategoryIds = [];
        if ($category) {
            if ($category->parent_id) {
                $fallbackCategoryIds = Category::active()
                    ->where('parent_id', $category->parent_id)
                    ->where('id', '!=', $categoryId)
                    ->pluck('id')
                    ->all();
                if (empty($fallbackCategoryIds)) {
                    $fallbackCategoryIds = [$category->parent_id];
                }
            } else {
                $fallbackCategoryIds = Category::active()
                    ->root()
                    ->where('id', '!=', $categoryId)
                    ->pluck('id')
                    ->all();
            }
        }

        if (empty($fallbackCategoryIds)) {
            return $fromSameCategory;
        }

        $additional = $baseQuery()
            ->whereIn('category_id', $fallbackCategoryIds)
            ->whereNotIn('id', $excludeIds)
            ->take($need)
            ->get();

        return $fromSameCategory->merge($additional)->take($limit);
    }

    public function getRecent(int $excludeProductId, int $limit = 4): Collection
    {
        return Product::whereNull('parent_id')
            ->where('id', '!=', $excludeProductId)
            ->with(['category', 'variants' => function ($query) {
                $query->where('is_active', true)->forWebsite();
            }])
            ->active()
            ->forWebsite()
            ->inStock()
            ->latest()
            ->take($limit)
            ->get();
    }

    protected function applySort($query, ?string $sort)
    {
        $sort = in_array($sort, ['price_asc', 'price_desc', 'newest', 'most_sale']) ? $sort : null;

        switch ($sort) {
            case 'price_asc':
                $query->orderByRaw('(SELECT COALESCE(MIN(p2.price), products.price) FROM products p2 WHERE p2.parent_id = products.id AND p2.is_active = 1 AND p2.channel_visibility IN (\'both\', \'website_only\')) ASC');
                break;
            case 'price_desc':
                $query->orderByRaw('(SELECT COALESCE(MIN(p2.price), products.price) FROM products p2 WHERE p2.parent_id = products.id AND p2.is_active = 1 AND p2.channel_visibility IN (\'both\', \'website_only\')) DESC');
                break;
            case 'most_sale':
                $salesSubquery = OrderItem::select('product_id', DB::raw('SUM(qty) as total_sold'))
                    ->groupBy('product_id');

                $query->leftJoinSub($salesSubquery, 'sales', 'products.id', '=', 'sales.product_id')
                    ->select('products.*', DB::raw('COALESCE(sales.total_sold, 0) as total_sold'))
                    ->orderByDesc(DB::raw('COALESCE(sales.total_sold, 0)'))
                    ->orderByDesc('products.created_at');
                break;
            case 'newest':
            default:
                $query->orderByDesc('products.created_at');
                break;
        }

        return $query;
    }

    public function create(array $data): Product
    {
        return Product::create($data);
    }

    public function update(Product $product, array $data): bool
    {
        return $product->update($data);
    }

    public function delete(Product $product): bool
    {
        return $product->delete();
    }
}