<?php

namespace App\Http\Controllers;

use App\Models\Category;
use App\Models\OrderItem;
use App\Models\Product;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

class CollectionController extends Controller
{
    /**
     * Special collection slugs
     */
    private const SPECIAL_COLLECTIONS = [
        'best-sellers' => 'Best Sellers',
        'new-arrivals' => 'New Arrivals',
    ];

    /**
     * Get categories with subcategories and product counts
     */
    protected function getCategoriesWithCounts($searchQuery = '', $selectedCategoryIds = [], $collectionType = 'category', bool $onlyStockout = false)
    {
        $rootCategories = Category::active()
            ->root()
            ->orderBy('sort_order')
            ->with(['children' => function ($query) {
                $query->active()->orderBy('sort_order');
            }])
            ->get();

        // Calculate product counts for each category
        foreach ($rootCategories as $category) {
            // Get all category IDs including subcategories
            $categoryIds = [$category->id];
            foreach ($category->children as $child) {
                $categoryIds[] = $child->id;
            }

            // Count products for this category and its children (parents/single only, no variant children)
            $productQuery = Product::whereIn('category_id', $categoryIds)->whereNull('parent_id')->active()->forWebsite();
            
            // If search query exists, apply it
            if (!empty($searchQuery)) {
                $productQuery->where(function ($q) use ($searchQuery) {
                    $q->where('name', 'LIKE', '%' . $searchQuery . '%')
                      ->orWhere('tags', 'LIKE', '%' . $searchQuery . '%');
                });
            }

            if ($onlyStockout) {
                $productQuery->outOfStock();
            } else {
                $productQuery->inStock();
            }
            
            $category->product_count = $productQuery->count();

            // Count products for each subcategory (parents/single only)
            foreach ($category->children as $child) {
                $subProductQuery = Product::where('category_id', $child->id)->whereNull('parent_id')->active()->forWebsite();
                
                if (!empty($searchQuery)) {
                    $subProductQuery->where(function ($q) use ($searchQuery) {
                        $q->where('name', 'LIKE', '%' . $searchQuery . '%')
                          ->orWhere('tags', 'LIKE', '%' . $searchQuery . '%');
                    });
                }

                if ($onlyStockout) {
                    $subProductQuery->outOfStock();
                } else {
                    $subProductQuery->inStock();
                }
                
                $child->product_count = $subProductQuery->count();
            }

            // Remove subcategories that have zero products
            $filteredChildren = $category->children->filter(function ($child) {
                return ($child->product_count ?? 0) > 0;
            })->values();
            $category->setRelation('children', $filteredChildren);
        }

        // Return only categories that have products or children with products
        return $rootCategories->filter(function ($category) {
            return ($category->product_count ?? 0) > 0 || $category->children->count() > 0;
        })->values();
    }

    /**
     * Display collection page (category or special collection)
     */
    public function show(string $slug, Request $request): View
    {
        // Check if it's a special collection
        if (array_key_exists($slug, self::SPECIAL_COLLECTIONS)) {
            return $this->showSpecialCollection($slug, $request);
        }

        // Otherwise, it's a category
        return $this->showCategory($slug, $request);
    }

    /**
     * Display special collection (best-sellers, new-arrivals)
     */
    protected function showSpecialCollection(string $slug, Request $request): View
    {
        $collectionName = self::SPECIAL_COLLECTIONS[$slug];
        $onlyStockout = $request->boolean('only_stockout');

        $query = Product::with(['category', 'variants' => function($query) {
                $query->where('is_active', true)->forWebsite();
            }])->whereNull('parent_id')->active()->forWebsite();

        $sort = $request->query('sort');

        // Handle multiple category filtering from checkboxes
        $selectedCategoryIds = $request->has('categories') && is_array($request->categories) 
            ? $request->categories 
            : [];

        // Expand selected category IDs to include children when parent is selected
        // This ensures checkboxes stay checked after page reload
        $expandedSelectedCategoryIds = !empty($selectedCategoryIds) 
            ? $this->expandSelectedCategoryIds($selectedCategoryIds) 
            : [];

        if (!empty($selectedCategoryIds)) {
            // Include subcategories when parent is selected
            $allCategoryIds = [];
            foreach ($selectedCategoryIds as $catId) {
                $category = Category::find($catId);
                if ($category) {
                    $allCategoryIds[] = $category->id;
                    // Add subcategories
                    $subcategories = Category::where('parent_id', $category->id)->active()->pluck('id');
                    $allCategoryIds = array_merge($allCategoryIds, $subcategories->toArray());
                }
            }
            $allCategoryIds = array_unique($allCategoryIds);
            $query->whereIn('category_id', $allCategoryIds);
        }

        // Apply collection-specific logic
        switch ($slug) {
            case 'best-sellers':
                // Products with most sales
                $topProductIds = OrderItem::select('product_id', DB::raw('SUM(qty) as total_sold'))
                    ->groupBy('product_id')
                    ->orderByDesc('total_sold')
                    ->take(100)
                    ->pluck('product_id');
                
                if ($topProductIds->isEmpty()) {
                    // If no sales yet, show featured products as best sellers
                    $query->featured();
                } else {
                    $query->whereIn('id', $topProductIds);
                }
                break;

            case 'new-arrivals':
                // Latest products
                break;
        }

        if ($onlyStockout) {
            $query->outOfStock();
        } else {
            $query->inStock();
        }

        $products = $this->applySort($query, $sort)
            ->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'])
            ->paginate(20)
            ->withQueryString();

        // Get categories with subcategories and counts
        // Use expanded IDs for view rendering to maintain checkbox state
        $categoriesWithCounts = $this->getCategoriesWithCounts('', $expandedSelectedCategoryIds, 'category', $onlyStockout);

        return view('collection.show', [
            'collectionType' => 'special',
            'collectionSlug' => $slug,
            'collectionName' => $collectionName,
            'products' => $products,
            'categoriesWithCounts' => $categoriesWithCounts,
            'selectedCategoryIds' => $expandedSelectedCategoryIds,
            'category' => null, // Not a category
        ]);
    }

    /**
     * Expand selected category IDs to include all child categories when a parent is selected
     * This ensures the view receives the complete list for checkbox state persistence
     */
    protected function expandSelectedCategoryIds(array $selectedCategoryIds): array
    {
        $expandedIds = [];
        
        foreach ($selectedCategoryIds as $catId) {
            $category = Category::find($catId);
            if ($category) {
                // Add the category itself
                $expandedIds[] = $category->id;
                
                // If it's a parent category (has no parent_id), add all its children
                if (is_null($category->parent_id) || $category->parent_id == 0) {
                    $childIds = Category::where('parent_id', $category->id)
                        ->active()
                        ->pluck('id')
                        ->toArray();
                    $expandedIds = array_merge($expandedIds, $childIds);
                }
            }
        }
        
        // Return unique IDs to avoid duplicates
        return array_values(array_unique($expandedIds));
    }

    /**
     * Get all category IDs including the category itself and all its subcategories recursively
     */
    protected function getCategoryIdsWithSubcategories(Category $category): array
    {
        $categoryIds = [$category->id];
        
        // Recursively get all subcategories
        $this->getSubcategoryIdsRecursive($category->id, $categoryIds);
        
        return $categoryIds;
    }

    /**
     * Recursively get all subcategory IDs for a given parent category ID
     */
    protected function getSubcategoryIdsRecursive(int $parentId, array &$categoryIds): void
    {
        $subcategories = Category::where('parent_id', $parentId)
            ->active()
            ->pluck('id')
            ->toArray();
        
        foreach ($subcategories as $subcategoryId) {
            if (!in_array($subcategoryId, $categoryIds)) {
                $categoryIds[] = $subcategoryId;
                // Recursively get sub-subcategories
                $this->getSubcategoryIdsRecursive($subcategoryId, $categoryIds);
            }
        }
    }

    /**
     * Display category collection
     */
    protected function showCategory(string $slug, Request $request): View
    {
        $onlyStockout = $request->boolean('only_stockout');

        $category = Category::where('slug', $slug)
            ->active()
            ->with(['children' => function ($query) {
                $query->active()->orderBy('sort_order');
            }])
            ->firstOrFail();

        // Check if this is a parent category (parent_id is null or 0)
        $isParentCategory = is_null($category->parent_id) || $category->parent_id == 0;
        
        // Get direct subcategories for display
        $subcategories = $category->children()->active()->orderBy('sort_order')->get();

        // Handle multiple category filtering from checkboxes (subcategory filtering)
        $selectedCategoryIds = $request->has('categories') && is_array($request->categories) 
            ? $request->categories 
            : [];

        // Expand selected category IDs to include children when parent is selected
        // This ensures checkboxes stay checked after page reload
        $expandedSelectedCategoryIds = !empty($selectedCategoryIds) 
            ? $this->expandSelectedCategoryIds($selectedCategoryIds) 
            : [];

        // Build query for products (parents/single only, no variant children)
        $query = Product::with(['category', 'variants' => function($query) {
                $query->where('is_active', true)->forWebsite();
            }])->whereNull('parent_id')->active()->forWebsite();

        if (!empty($selectedCategoryIds)) {
            // User selected specific subcategories, filter by those
            // Include subcategories of selected categories recursively
            $allCategoryIds = [];
            foreach ($selectedCategoryIds as $catId) {
                $selectedCat = Category::find($catId);
                if ($selectedCat) {
                    // Verify the selected category belongs to this parent category tree
                    if ($selectedCat->id == $category->id || 
                        ($isParentCategory && $this->isCategoryInTree($selectedCat, $category->id))) {
                        $allCategoryIds[] = $selectedCat->id;
                        // Add subcategories of selected category recursively
                        $subIds = $this->getCategoryIdsWithSubcategories($selectedCat);
                        $allCategoryIds = array_merge($allCategoryIds, $subIds);
                    } else {
                        // If category doesn't belong to this tree, still include it if it's a valid category
                        // This allows filtering across different category trees
                        $allCategoryIds[] = $selectedCat->id;
                        $subIds = $this->getCategoryIdsWithSubcategories($selectedCat);
                        $allCategoryIds = array_merge($allCategoryIds, $subIds);
                    }
                }
            }
            $allCategoryIds = array_unique($allCategoryIds);
            
            if (!empty($allCategoryIds)) {
                $query->whereIn('category_id', $allCategoryIds);
            } else {
                // If invalid selection, show nothing
                $query->whereRaw('1 = 0');
            }
        } else {
            // No specific subcategory filters - show all products from category and its subcategories
            if ($isParentCategory) {
                // Parent category: include parent + all subcategories recursively
                $categoryIds = $this->getCategoryIdsWithSubcategories($category);
            } else {
                // Subcategory: show only this subcategory (but include its subcategories if any)
                $categoryIds = $this->getCategoryIdsWithSubcategories($category);
            }
            $query->whereIn('category_id', $categoryIds);
        }

        if ($onlyStockout) {
            $query->outOfStock();
        } else {
            $query->inStock();
        }

        $sort = $request->query('sort');
        $products = $this->applySort($query, $sort)
            ->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'])
            ->paginate(12)
            ->withQueryString();

        // Get categories with subcategories and counts for sidebar
        // Use expanded IDs for view rendering to maintain checkbox state
        $categoriesWithCounts = $this->getCategoriesWithCounts('', $expandedSelectedCategoryIds, 'category', $onlyStockout);

        return view('collection.show', [
            'collectionType' => 'category',
            'collectionSlug' => $slug,
            'collectionName' => $category->name,
            'category' => $category,
            'isParentCategory' => $isParentCategory,
            'subcategories' => $subcategories,
            'products' => $products,
            'categoriesWithCounts' => $categoriesWithCounts,
            'selectedCategoryIds' => $expandedSelectedCategoryIds,
        ]);
    }

    /**
     * Check if a category is within the tree of a parent category
     */
    protected function isCategoryInTree(Category $category, int $parentId): bool
    {
        if ($category->id == $parentId) {
            return true;
        }
        
        $current = $category;
        while ($current->parent_id) {
            if ($current->parent_id == $parentId) {
                return true;
            }
            $current = Category::find($current->parent_id);
            if (!$current) {
                break;
            }
        }
        
        return false;
    }

    /**
     * Apply sorting to the products query based on sort parameter
     */
    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;
    }
}