<?php

namespace App\Http\Controllers;

use App\Models\Category;
use App\Models\Product;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;
use Illuminate\Support\Str;

class CategoryController extends Controller
{
    // Constants for pagination and file limits
    const PER_PAGE = 20;
    const MAX_FILE_SIZE = 2048; // 2MB
    const EXPORT_CHUNK_SIZE = 1000;
    
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
        try {
            // Start query with relationships
            $query = Category::with([
                'parent:id,name,color',
                'children' => function($query) {
                    $query->select(['id', 'name', 'parent_id', 'color', 'is_active', 'sort_order'])
                        ->withCount('products')
                        ->orderBy('sort_order')
                        ->orderBy('name');
                }
            ])->withCount('products');

            // Apply filters
            $this->applyFilters($query, $request);

            // Apply search
            $this->applySearch($query, $request);

            // Apply sorting
            $this->applySorting($query, $request);

            // Get paginated results
            $perPage = $request->get('per_page', self::PER_PAGE);
            $categories = $query->paginate($perPage)->withQueryString();

            // Get parent categories for tree view
            $parentCategories = Category::whereNull('parent_id')
                ->with(['children' => function($query) {
                    $query->withCount('products')->orderBy('sort_order');
                }])
                ->withCount('products')
                ->orderBy('sort_order')
                ->orderBy('name')
                ->get();

            // Get statistics
            $stats = $this->getCategoryStatistics();

            // Check if request expects JSON (AJAX)
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'data' => $categories,
                    'stats' => $stats,
                    'parent_categories' => $parentCategories,
                ]);
            }

            return view('categories.index', compact('categories', 'parentCategories', 'stats'));

        } catch (\Exception $e) {
            Log::error('CategoryController@index - Error: ' . $e->getMessage(), [
                'trace' => $e->getTraceAsString(),
                'request' => $request->all(),
                'user_id' => auth()->id(),
            ]);

            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to load categories.',
                    'error' => config('app.debug') ? $e->getMessage() : null,
                ], 500);
            }

            return redirect()->route('dashboard')
                ->with('error', 'Failed to load categories. Please try again.')
                ->withInput();
        }
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        try {
            $parentCategories = Category::whereNull('parent_id')
                ->where('is_active', true)
                ->orderBy('sort_order')
                ->orderBy('name')
                ->get(['id', 'name', 'color']);

            // Predefined colors for quick selection
            $predefinedColors = [
                '#667eea', '#764ba2', '#f093fb', '#4facfe', '#00f2fe',
                '#43e97b', '#38f9d7', '#fa709a', '#fee140', '#fa709a',
                '#ff6b6b', '#4ecdc4', '#45b7d1', '#96ceb4', '#ffeaa7',
                '#dda0dd', '#98d8c8', '#f7b731', '#eb3b5a', '#26de81'
            ];

            return view('categories.create', compact('parentCategories', 'predefinedColors'));

        } catch (\Exception $e) {
            Log::error('CategoryController@create - Error: ' . $e->getMessage());
            
            return redirect()->route('categories.index')
                ->with('error', 'Failed to load category creation form.');
        }
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        DB::beginTransaction();
        
        try {
            // Debug: Check what's being sent
            \Log::info('=== CATEGORY STORE START ===');
            \Log::info('Form data received:', $request->all());
            
            // Validate request with simplified color validation
            $validated = $this->validateCategoryRequest($request);
            
            // Debug: Show validated data
            \Log::info('Validated data:', $validated);
            
            // Handle color validation and formatting manually
            if (!empty($validated['color'])) {
                // Remove any # characters for validation
                $color = str_replace('#', '', $validated['color']);
                
                // Validate hex color format
                if (!preg_match('/^[0-9a-fA-F]{3,6}$/', $color)) {
                    throw new \Exception('Invalid color format. Please use a valid hex color (e.g., 667eea or f00).');
                }
                
                // Format color: add # prefix and expand 3-digit to 6-digit
                if (strlen($color) === 3) {
                    // Expand 3-digit to 6-digit hex
                    $color = $color[0] . $color[0] . $color[1] . $color[1] . $color[2] . $color[2];
                }
                
                // Add # prefix
                $validated['color'] = '#' . $color;
            } else {
                // Set default color if not provided
                $validated['color'] = $this->generateColorFromName($validated['name']);
            }
            
            // Generate slug if not provided
            if (empty($validated['slug'])) {
                $validated['slug'] = Str::slug($validated['name']);
                
                // Make slug unique
                $originalSlug = $validated['slug'];
                $count = 1;
                while (Category::where('slug', $validated['slug'])->exists()) {
                    $validated['slug'] = $originalSlug . '-' . $count++;
                }
            }
            
            // Handle image upload
            if ($request->hasFile('image')) {
                $validated['image'] = $this->uploadImage($request->file('image'));
            }
            
            // Set sort order if not provided
            if (!isset($validated['sort_order'])) {
                $maxOrder = Category::where('parent_id', $validated['parent_id'] ?? null)
                    ->max('sort_order');
                $validated['sort_order'] = ($maxOrder ?? 0) + 1;
            }
            
            // Ensure is_active is set
            if (!isset($validated['is_active'])) {
                $validated['is_active'] = true;
            }
            
            // Debug before creating
            \Log::info('Creating category with data:', $validated);
            
            // Create category
            $category = Category::create($validated);
            
            // Debug after creating
            \Log::info('Category created successfully:', [
                'id' => $category->id,
                'name' => $category->name,
                'color' => $category->color,
                'slug' => $category->slug,
            ]);
            
            // Log activity
            $this->logActivity('created', $category, null, $validated);
            
            DB::commit();
            
            \Log::info('=== CATEGORY STORE SUCCESS ===');
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'message' => 'Category created successfully.',
                    'data' => $category->load('parent'),
                    'redirect' => route('categories.show', $category),
                ], 201);
            }
            
            return redirect()->route('categories.show', $category)
                ->with('success', 'Category created successfully.');

        } catch (\Illuminate\Validation\ValidationException $e) {
            DB::rollBack();
            
            \Log::error('Validation failed:', $e->errors());
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Validation failed.',
                    'errors' => $e->errors(),
                ], 422);
            }
            
            return redirect()->back()
                ->withErrors($e->validator)
                ->withInput();

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@store - Error: ' . $e->getMessage(), [
                'trace' => $e->getTraceAsString(),
                'request' => $request->except(['image']),
                'user_id' => auth()->id(),
            ]);
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to create category. Please try again.',
                    'error' => config('app.debug') ? $e->getMessage() : null,
                ], 500);
            }
            
            return redirect()->back()
                ->with('error', $e->getMessage())
                ->withInput();
        }
    }
    
    /**
     * Display the specified resource.
     */
public function show(Request $request, Category $category)
{
    try {
        // Simple loading without complex relationships
        $category->load([
            'parent:id,name',
            'children' => function($query) {
                $query->select(['id', 'name', 'parent_id', 'color', 'is_active', 'sort_order'])
                    ->orderBy('sort_order')
                    ->orderBy('name');
            }
        ]);
        
        // Manually set products count
        $category->products_count = $category->products()->count();
        
        // Get paginated products with your actual columns
        $products = $category->products()
            ->select([
                'id', 'name', 'sku', 'barcode', 
                'sale_price', 'cost_price', 'wholesale_price',
                'available_stock', 'stock_status', 'status', 'category_id',
                'is_on_sale', 'is_featured', 'has_vat'
            ])
            ->latest()
            ->paginate(10);
        
        // Get category statistics
        $categoryStats = $this->getCategoryDetailedStatistics($category);
        
        // Get sibling categories
        $siblingCategories = Category::where('parent_id', $category->parent_id)
            ->where('id', '!=', $category->id)
            ->where('is_active', true)
            ->orderBy('sort_order')
            ->orderBy('name')
            ->get(['id', 'name', 'color']);
        
        if ($request->expectsJson() || $request->ajax()) {
            return response()->json([
                'success' => true,
                'data' => [
                    'category' => $category,
                    'statistics' => $categoryStats,
                    'products' => $products,
                    'sibling_categories' => $siblingCategories,
                ],
            ]);
        }
        
        return view('categories.show', compact(
            'category', 
            'products', 
            'categoryStats', 
            'siblingCategories'
        ));

    } catch (\Exception $e) {
        Log::error('CategoryController@show - Error: ' . $e->getMessage(), [
            'category_id' => $category->id,
            'error' => $e->getMessage(),
            'trace' => $e->getTraceAsString()
        ]);
        
        if ($request->expectsJson() || $request->ajax()) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to load category details.',
                'error' => config('app.debug') ? $e->getMessage() : null,
            ], 500);
        }
        
        return redirect()->route('categories.index')
            ->with('error', 'Failed to load category details.');
    }
}

    /**
     * Show the form for editing the specified resource.
     */
    public function edit(Category $category)
    {
        try {
            $parentCategories = Category::whereNull('parent_id')
                ->where('id', '!=', $category->id) // Don't allow self as parent
                ->where('is_active', true)
                ->orderBy('sort_order')
                ->orderBy('name')
                ->get(['id', 'name', 'color']);
            
            // Predefined colors
            $predefinedColors = [
                '#667eea', '#764ba2', '#f093fb', '#4facfe', '#00f2fe',
                '#43e97b', '#38f9d7', '#fa709a', '#fee140', '#fa709a',
                '#ff6b6b', '#4ecdc4', '#45b7d1', '#96ceb4', '#ffeaa7',
                '#dda0dd', '#98d8c8', '#f7b731', '#eb3b5a', '#26de81'
            ];
            
            // Get child categories to prevent circular reference
            $childCategoryIds = $category->children()->pluck('id')->toArray();
            
            return view('categories.edit', compact(
                'category', 
                'parentCategories', 
                'predefinedColors',
                'childCategoryIds'
            ));

        } catch (\Exception $e) {
            Log::error('CategoryController@edit - Error: ' . $e->getMessage(), [
                'category_id' => $category->id,
            ]);
            
            return redirect()->route('categories.index')
                ->with('error', 'Failed to load category edit form.');
        }
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, Category $category)
    {
        DB::beginTransaction();
        
        try {
            // Store old data for logging
            $oldData = $category->toArray();
            
            // Validate request
            $validated = $this->validateCategoryRequest($request, $category->id);
            
            // Prevent circular reference (category cannot be its own parent)
            if (isset($validated['parent_id']) && $validated['parent_id'] == $category->id) {
                throw new \Exception('A category cannot be its own parent.');
            }
            
            // Prevent setting parent to one of its children
            $childCategoryIds = $category->children()->pluck('id')->toArray();
            if (isset($validated['parent_id']) && in_array($validated['parent_id'], $childCategoryIds)) {
                throw new \Exception('Cannot set parent to a child category.');
            }
            
            // Handle color validation and formatting manually
            if (!empty($validated['color'])) {
                // Remove any # characters for validation
                $color = str_replace('#', '', $validated['color']);
                
                // Validate hex color format
                if (!preg_match('/^[0-9a-fA-F]{3,6}$/', $color)) {
                    throw new \Exception('Invalid color format. Please use a valid hex color (e.g., 667eea or f00).');
                }
                
                // Format color: add # prefix and expand 3-digit to 6-digit
                if (strlen($color) === 3) {
                    // Expand 3-digit to 6-digit hex
                    $color = $color[0] . $color[0] . $color[1] . $color[1] . $color[2] . $color[2];
                }
                
                // Add # prefix
                $validated['color'] = '#' . $color;
            } else {
                // Keep existing color if not provided
                $validated['color'] = $category->color;
            }
            
            // Generate slug if not provided and name changed
            if (empty($validated['slug']) && $category->isDirty('name')) {
                $validated['slug'] = Str::slug($validated['name']);
                
                // Make slug unique (excluding current category)
                $originalSlug = $validated['slug'];
                $count = 1;
                while (Category::where('slug', $validated['slug'])
                        ->where('id', '!=', $category->id)
                        ->exists()) {
                    $validated['slug'] = $originalSlug . '-' . $count++;
                }
            } elseif (!$category->isDirty('name')) {
                // Keep existing slug if name hasn't changed
                $validated['slug'] = $category->slug;
            }
            
            // Handle image upload
            if ($request->hasFile('image')) {
                // Delete old image if exists
                if ($category->image && Storage::disk('public')->exists($category->image)) {
                    Storage::disk('public')->delete($category->image);
                }
                $validated['image'] = $this->uploadImage($request->file('image'));
            } elseif ($request->has('remove_image') && $request->remove_image == '1') {
                // Remove existing image
                if ($category->image && Storage::disk('public')->exists($category->image)) {
                    Storage::disk('public')->delete($category->image);
                }
                $validated['image'] = null;
            } else {
                // Keep existing image
                $validated['image'] = $category->image;
            }
            
            // Ensure is_active is set
            if (!isset($validated['is_active'])) {
                $validated['is_active'] = $category->is_active;
            }
            
            // Update category
            $category->update($validated);
            
            // Log activity with changes
            $this->logActivity('updated', $category, $oldData, $validated);
            
            DB::commit();
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'message' => 'Category updated successfully.',
                    'data' => $category->fresh(['parent']),
                    'redirect' => route('categories.show', $category),
                ]);
            }
            
            return redirect()->route('categories.show', $category)
                ->with('success', 'Category updated successfully.');

        } catch (\Illuminate\Validation\ValidationException $e) {
            DB::rollBack();
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Validation failed.',
                    'errors' => $e->errors(),
                ], 422);
            }
            
            return redirect()->back()
                ->withErrors($e->validator)
                ->withInput();

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@update - Error: ' . $e->getMessage(), [
                'category_id' => $category->id,
                'trace' => $e->getTraceAsString(),
                'request' => $request->except(['image']),
                'user_id' => auth()->id(),
            ]);
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $e->getMessage(),
                    'error' => config('app.debug') ? $e->getMessage() : null,
                ], 500);
            }
            
            return redirect()->back()
                ->with('error', $e->getMessage())
                ->withInput();
        }
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(Request $request, Category $category)
    {
        DB::beginTransaction();
        
        try {
            // Check if category has any products
            if ($category->products()->exists()) {
                throw new \Exception('Cannot delete category with existing products. Please reassign or delete products first.');
            }
            
            // Check if category has subcategories
            if ($category->children()->exists()) {
                throw new \Exception('Cannot delete category with subcategories. Please delete or reassign subcategories first.');
            }
            
            // Store category data for logging before deletion
            $categoryData = $category->toArray();
            
            // Delete image if exists
            if ($category->image && Storage::disk('public')->exists($category->image)) {
                Storage::disk('public')->delete($category->image);
            }
            
            // Log activity before deletion
            $this->logActivity('deleted', $category, $categoryData, null);
            
            // Delete category
            $category->delete();
            
            DB::commit();
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'message' => 'Category deleted successfully.',
                    'redirect' => route('categories.index'),
                ]);
            }
            
            return redirect()->route('categories.index')
                ->with('success', 'Category deleted successfully.');

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@destroy - Error: ' . $e->getMessage(), [
                'category_id' => $category->id,
                'user_id' => auth()->id(),
            ]);
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $e->getMessage(),
                ], 422);
            }
            
            return redirect()->route('categories.index')
                ->with('error', $e->getMessage());
        }
    }

    /**
     * Archive (soft delete) a category.
     */
    public function archive(Request $request, Category $category)
    {
        DB::beginTransaction();
        
        try {
            // Check if category is already archived
            if ($category->trashed()) {
                throw new \Exception('Category is already archived.');
            }
            
            // Store old data for logging
            $oldData = $category->toArray();
            
            // Archive category (soft delete)
            $category->delete();
            
            // Log activity
            $this->logActivity('archived', $category, $oldData, null);
            
            DB::commit();
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'message' => 'Category archived successfully.',
                ]);
            }
            
            return redirect()->route('categories.index')
                ->with('success', 'Category archived successfully.');

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@archive - Error: ' . $e->getMessage(), [
                'category_id' => $category->id,
            ]);
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $e->getMessage(),
                ], 422);
            }
            
            return redirect()->route('categories.index')
                ->with('error', $e->getMessage());
        }
    }

    /**
     * Restore an archived category.
     */
    public function restore(Request $request, $id)
    {
        DB::beginTransaction();
        
        try {
            // Find trashed category
            $category = Category::withTrashed()->findOrFail($id);
            
            // Check if category is not archived
            if (!$category->trashed()) {
                throw new \Exception('Category is not archived.');
            }
            
            // Restore category
            $category->restore();
            
            // Log activity
            $this->logActivity('restored', $category, null, $category->toArray());
            
            DB::commit();
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'message' => 'Category restored successfully.',
                    'data' => $category,
                    'redirect' => route('categories.show', $category),
                ]);
            }
            
            return redirect()->route('categories.show', $category)
                ->with('success', 'Category restored successfully.');

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@restore - Error: ' . $e->getMessage(), [
                'category_id' => $id,
            ]);
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $e->getMessage(),
                ], 422);
            }
            
            return redirect()->route('categories.index')
                ->with('error', $e->getMessage());
        }
    }

    /**
     * Force delete an archived category.
     */
    public function forceDelete(Request $request, $id)
    {
        DB::beginTransaction();
        
        try {
            // Find trashed category
            $category = Category::withTrashed()->findOrFail($id);
            
            // Store data for logging
            $categoryData = $category->toArray();
            
            // Check if category has any products (including trashed)
            if ($category->products()->withTrashed()->exists()) {
                throw new \Exception('Cannot delete category with existing products.');
            }
            
            // Delete image if exists
            if ($category->image && Storage::disk('public')->exists($category->image)) {
                Storage::disk('public')->delete($category->image);
            }
            
            // Force delete
            $category->forceDelete();
            
            // Log activity
            $this->logActivity('force_deleted', $category, $categoryData, null);
            
            DB::commit();
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'message' => 'Category permanently deleted.',
                ]);
            }
            
            return redirect()->route('categories.index', ['filter' => 'archived'])
                ->with('success', 'Category permanently deleted.');

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@forceDelete - Error: ' . $e->getMessage(), [
                'category_id' => $id,
            ]);
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $e->getMessage(),
                ], 422);
            }
            
            return redirect()->route('categories.index', ['filter' => 'archived'])
                ->with('error', $e->getMessage());
        }
    }

    /**
     * Search categories for AJAX requests.
     */
    public function search(Request $request): JsonResponse
    {
        try {
            $search = $request->input('q', '');
            $exclude = $request->input('exclude', []);
            $limit = $request->input('limit', 10);
            
            $query = Category::where('is_active', true);
            
            if (!empty($search)) {
                $query->where(function($q) use ($search) {
                    $q->where('name', 'like', "%{$search}%")
                      ->orWhere('description', 'like', "%{$search}%")
                      ->orWhere('slug', 'like', "%{$search}%");
                });
            }
            
            if (!empty($exclude)) {
                $query->whereNotIn('id', (array) $exclude);
            }
            
            $categories = $query->select(['id', 'name', 'color', 'parent_id'])
                ->with('parent:id,name')
                ->limit($limit)
                ->get()
                ->map(function($category) {
                    return [
                        'id' => $category->id,
                        'text' => $category->name . ($category->parent ? ' (' . $category->parent->name . ')' : ''),
                        'name' => $category->name,
                        'color' => $category->color,
                        'parent' => $category->parent ? $category->parent->name : null,
                    ];
                });
            
            return response()->json([
                'success' => true,
                'results' => $categories,
                'pagination' => ['more' => false]
            ]);

        } catch (\Exception $e) {
            Log::error('CategoryController@search - Error: ' . $e->getMessage(), [
                'search' => $request->input('q'),
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to search categories.',
                'results' => []
            ], 500);
        }
    }

    /**
     * Export categories to CSV/Excel.
     */
    public function export(Request $request)
    {
        try {
            $format = $request->input('format', 'csv');
            $columns = $request->input('columns', ['id', 'name', 'description', 'parent', 'products_count', 'status', 'created_at']);
            
            $query = Category::with('parent')->withCount('products');
            
            // Apply filters if any
            $this->applyFilters($query, $request);
            
            $fileName = 'categories-' . date('Y-m-d-H-i-s') . '.' . $format;
            
            if ($format === 'csv') {
                return $this->exportToCsv($query, $fileName, $columns);
            } elseif ($format === 'excel') {
                return $this->exportToExcel($query, $fileName, $columns);
            } elseif ($format === 'json') {
                return $this->exportToJson($query, $fileName);
            }
            
            throw new \Exception('Unsupported export format.');

        } catch (\Exception $e) {
            Log::error('CategoryController@export - Error: ' . $e->getMessage());
            
            return redirect()->route('categories.index')
                ->with('error', 'Failed to export categories.');
        }
    }

    /**
     * Bulk delete categories.
     */
    public function bulkDelete(Request $request): JsonResponse
    {
        DB::beginTransaction();
        
        try {
            $validated = $request->validate([
                'ids' => 'required|array|min:1',
                'ids.*' => 'exists:categories,id',
            ]);
            
            $categories = Category::whereIn('id', $validated['ids'])->get();
            
            // Check for constraints
            $errors = [];
            
            foreach ($categories as $category) {
                if ($category->products()->exists()) {
                    $errors[] = "Category '{$category->name}' has products and cannot be deleted.";
                }
                
                if ($category->children()->exists()) {
                    $errors[] = "Category '{$category->name}' has subcategories and cannot be deleted.";
                }
            }
            
            if (!empty($errors)) {
                return response()->json([
                    'success' => false,
                    'message' => 'Cannot delete some categories due to constraints.',
                    'errors' => $errors,
                ], 422);
            }
            
            // Log activity for each category
            foreach ($categories as $category) {
                $this->logActivity('bulk_deleted', $category, $category->toArray(), null);
            }
            
            // Delete categories
            $deletedCount = Category::whereIn('id', $validated['ids'])->delete();
            
            DB::commit();
            
            return response()->json([
                'success' => true,
                'message' => "{$deletedCount} categories deleted successfully.",
                'count' => $deletedCount,
            ]);

        } catch (\Illuminate\Validation\ValidationException $e) {
            DB::rollBack();
            
            return response()->json([
                'success' => false,
                'message' => 'Validation failed.',
                'errors' => $e->errors(),
            ], 422);
            
        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@bulkDelete - Error: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to delete categories.',
                'error' => config('app.debug') ? $e->getMessage() : null,
            ], 500);
        }
    }

    /**
     * Bulk update categories.
     */
    public function bulkUpdate(Request $request): JsonResponse
    {
        DB::beginTransaction();
        
        try {
            $validated = $request->validate([
                'ids' => 'required|array|min:1',
                'ids.*' => 'exists:categories,id',
                'data' => 'required|array',
                'data.is_active' => 'sometimes|boolean',
                'data.parent_id' => 'sometimes|nullable|exists:categories,id',
            ]);
            
            // Prevent circular references
            if (isset($validated['data']['parent_id'])) {
                foreach ($validated['ids'] as $categoryId) {
                    if ($categoryId == $validated['data']['parent_id']) {
                        throw new \Exception('A category cannot be its own parent.');
                    }
                    
                    $childIds = Category::where('parent_id', $categoryId)
                        ->pluck('id')
                        ->toArray();
                    
                    if (in_array($validated['data']['parent_id'], $childIds)) {
                        throw new \Exception('Cannot set parent to a child category.');
                    }
                }
            }
            
            // Update categories
            $updatedCount = Category::whereIn('id', $validated['ids'])
                ->update($validated['data']);
            
            // Log activity
            $categories = Category::whereIn('id', $validated['ids'])->get();
            foreach ($categories as $category) {
                $this->logActivity('bulk_updated', $category, null, $validated['data']);
            }
            
            DB::commit();
            
            return response()->json([
                'success' => true,
                'message' => "{$updatedCount} categories updated successfully.",
                'count' => $updatedCount,
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@bulkUpdate - Error: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => $e->getMessage(),
            ], 422);
        }
    }

    /**
     * Get category statistics for dashboard.
     */
    public function statistics(): JsonResponse
    {
        try {
            $stats = $this->getCategoryStatistics();
            
            return response()->json([
                'success' => true,
                'data' => $stats,
            ]);

        } catch (\Exception $e) {
            Log::error('CategoryController@statistics - Error: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to load statistics.',
            ], 500);
        }
    }

    /**
     * Get categories for dropdown.
     */
    public function dropdown(Request $request): JsonResponse
    {
        try {
            $includeInactive = $request->boolean('include_inactive', false);
            
            $query = Category::with(['children' => function($query) use ($includeInactive) {
                if (!$includeInactive) {
                    $query->where('is_active', true);
                }
                $query->orderBy('sort_order')->orderBy('name');
            }]);
            
            if (!$includeInactive) {
                $query->where('is_active', true);
            }
            
            $categories = $query->whereNull('parent_id')
                ->orderBy('sort_order')
                ->orderBy('name')
                ->get(['id', 'name', 'color', 'is_active']);
            
            return response()->json([
                'success' => true,
                'data' => $categories,
            ]);

        } catch (\Exception $e) {
            Log::error('CategoryController@dropdown - Error: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to load categories.',
            ], 500);
        }
    }

    /**
     * Get products for a specific category.
     */
    public function products(Request $request, Category $category)
    {
        try {
            $products = $category->products()
                ->select(['id', 'name', 'sku', 'price', 'stock_quantity', 'status', 'category_id'])
                ->latest()
                ->paginate(20);
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'data' => $products,
                    'category' => $category->only(['id', 'name', 'color']),
                ]);
            }
            
            return view('categories.products', compact('category', 'products'));

        } catch (\Exception $e) {
            Log::error('CategoryController@products - Error: ' . $e->getMessage());
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to load category products.',
                ], 500);
            }
            
            return redirect()->route('categories.show', $category)
                ->with('error', 'Failed to load category products.');
        }
    }

    /**
     * Display category tree view.
     */
    public function tree(Request $request)
    {
        try {
            $categories = Category::with(['children' => function($query) {
                $query->withCount('products')
                    ->orderBy('sort_order')
                    ->orderBy('name');
            }])
            ->withCount('products')
            ->whereNull('parent_id')
            ->orderBy('sort_order')
            ->orderBy('name')
            ->get();
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'data' => $categories,
                ]);
            }
            
            return view('categories.tree', compact('categories'));

        } catch (\Exception $e) {
            Log::error('CategoryController@tree - Error: ' . $e->getMessage());
            
            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to load category tree.',
                ], 500);
            }
            
            return redirect()->route('categories.index')
                ->with('error', 'Failed to load category tree view.');
        }
    }

    /**
     * Reorder categories.
     */
    public function reorder(Request $request): JsonResponse
    {
        DB::beginTransaction();
        
        try {
            // Fixed typo: changed 'validatcolore' to 'validate'
            $validated = $request->validate([
                'items' => 'required|array',
                'items.*.id' => 'required|exists:categories,id',
                'items.*.sort_order' => 'required|integer|min:0',
                'items.*.parent_id' => 'nullable|exists:categories,id',
            ]);
            
            foreach ($validated['items'] as $item) {
                Category::where('id', $item['id'])->update([
                    'sort_order' => $item['sort_order'],
                    'parent_id' => $item['parent_id'] ?? null,
                ]);
            }
            
            DB::commit();
            
            return response()->json([
                'success' => true,
                'message' => 'Categories reordered successfully.',
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            
            Log::error('CategoryController@reorder - Error: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => 'Failed to reorder categories.',
            ], 500);
        }
    }

    /**
     * Validate category request data.
     */
    private function validateCategoryRequest(Request $request, $categoryId = null): array
    {
        $rules = [
            'name' => [
                'required',
                'string',
                'max:255',
                Rule::unique('categories')->ignore($categoryId),
            ],
            'description' => 'nullable|string|max:500',
            'parent_id' => 'nullable|exists:categories,id',
            'is_active' => 'boolean',
            'color' => 'nullable|string|max:7', // Simple validation - we'll handle format manually
            'image' => 'nullable|image|mimes:jpeg,png,jpg,gif,svg|max:' . self::MAX_FILE_SIZE,
            'sort_order' => 'nullable|integer|min:0|max:999',
            'slug' => 'nullable|string|max:255|unique:categories,slug,' . $categoryId,
        ];
        
        return $request->validate($rules);
    }

    /**
     * Apply filters to query.
     */
    private function applyFilters($query, Request $request): void
    {
        if ($request->has('filter')) {
            switch ($request->filter) {
                case 'with-products':
                    $query->has('products');
                    break;
                case 'without-products':
                    $query->doesntHave('products');
                    break;
                case 'with-children':
                    $query->has('children');
                    break;
                case 'without-children':
                    $query->doesntHave('children');
                    break;
                case 'active':
                    $query->where('is_active', true);
                    break;
                case 'inactive':
                    $query->where('is_active', false);
                    break;
                case 'archived':
                    $query->onlyTrashed();
                    break;
                case 'parent':
                    $query->whereNull('parent_id');
                    break;
                case 'child':
                    $query->whereNotNull('parent_id');
                    break;
            }
        }
        
        // Filter by parent category
        if ($request->has('parent_id')) {
            if ($request->parent_id === 'null') {
                $query->whereNull('parent_id');
            } else {
                $query->where('parent_id', $request->parent_id);
            }
        }
    }

    /**
     * Apply search to query.
     */
    private function applySearch($query, Request $request): void
    {
        if ($request->has('search') && !empty($request->search)) {
            $search = $request->input('search');
            $query->where(function($q) use ($search) {
                $q->where('name', 'like', "%{$search}%")
                  ->orWhere('description', 'like', "%{$search}%")
                  ->orWhere('slug', 'like', "%{$search}%")
                  ->orWhereHas('parent', function($parentQuery) use ($search) {
                      $parentQuery->where('name', 'like', "%{$search}%");
                  });
            });
        }
    }

    /**
     * Apply sorting to query.
     */
    private function applySorting($query, Request $request): void
    {
        $sortBy = $request->get('sort_by', 'sort_order');
        $sortOrder = $request->get('sort_order', 'asc');
        
        $allowedSortColumns = ['name', 'products_count', 'created_at', 'updated_at', 'sort_order'];
        if (in_array($sortBy, $allowedSortColumns)) {
            $query->orderBy($sortBy, $sortOrder);
        } else {
            $query->orderBy('sort_order', 'asc')->orderBy('name', 'asc');
        }
    }

    /**
     * Get category statistics.
     */
    private function getCategoryStatistics(): array
    {
        return [
            'total' => Category::count(),
            'active' => Category::where('is_active', true)->count(),
            'inactive' => Category::where('is_active', false)->count(),
            'with_products' => Category::has('products')->count(),
            'without_products' => Category::doesntHave('products')->count(),
            'parent_categories' => Category::whereNull('parent_id')->count(),
            'child_categories' => Category::whereNotNull('parent_id')->count(),
            'archived' => Category::onlyTrashed()->count(),
            'total_products' => DB::table('products')->whereNull('deleted_at')->count(),
            'top_categories' => Category::withCount('products')
                ->orderBy('products_count', 'desc')
                ->limit(5)
                ->get(['id', 'name', 'products_count']),
        ];
    }

    /**
     * Get detailed statistics for a specific category.
     */
/**
 * Get detailed statistics for a specific category.
 */
private function getCategoryDetailedStatistics(Category $category): array
{
    $stats = [
        'total_products' => $category->products()->count(),
        'active_products' => $category->products()->where('status', 'active')->count(),
        'inactive_products' => $category->products()->where('status', 'inactive')->count(),
        'subcategories_count' => $category->children()->count(),
        'total_products_recursive' => $category->total_products_recursive ?? 0,
    ];
    
    // Stock statistics for your retail shop
    $stats['out_of_stock_products'] = $category->products()
        ->where('available_stock', '<=', 0)
        ->count();
        
    $stats['total_available_stock'] = $category->products()
        ->sum('available_stock');
        
    $stats['total_stock_value'] = $category->products()
        ->sum(DB::raw('COALESCE(sale_price, 0) * COALESCE(available_stock, 0)'));
    
    $stats['low_stock_products'] = $category->products()
        ->whereColumn('available_stock', '<=', 'minimum_stock')
        ->count();
    
    // Price statistics using your actual columns
    $stats['average_sale_price'] = $category->products()->avg('sale_price') ?? 0;
    $stats['average_cost_price'] = $category->products()->avg('cost_price') ?? 0;
    $stats['average_wholesale_price'] = $category->products()->avg('wholesale_price') ?? 0;
    
    // Calculate profit margin for retail
    $profitMargin = $category->products()
        ->whereNotNull('cost_price')
        ->whereNotNull('sale_price')
        ->where('cost_price', '>', 0)
        ->avg(DB::raw('((sale_price - cost_price) / cost_price) * 100')) ?? 0;
    
    $stats['average_profit_margin_percent'] = round($profitMargin, 2);
    
    // Total revenue from sales
    $stats['total_revenue'] = $category->products()->sum('total_revenue') ?? 0;
    
    // Total sold items
    $stats['total_items_sold'] = $category->products()->sum('total_sold') ?? 0;
    
    // VAT calculations for Kenya (use your has_vat column)
    $stats['vat_products'] = $category->products()
        ->where('has_vat', true)
        ->count();
    
    // Products on sale
    $stats['products_on_sale'] = $category->products()
        ->where('is_on_sale', true)
        ->count();
    
    // Featured products
    $stats['featured_products'] = $category->products()
        ->where('is_featured', true)
        ->count();
    
    // Products needing reorder
    $stats['need_reorder_products'] = $category->products()
        ->whereColumn('available_stock', '<=', 'reorder_point')
        ->count();
    
    return $stats;
}

    /**
     * Upload image and return path.
     */
    private function uploadImage($file): string
    {
        $fileName = 'category-' . time() . '-' . uniqid() . '.' . $file->getClientOriginalExtension();
        return $file->storeAs('categories', $fileName, 'public');
    }

    /**
     * Generate unique slug from name.
     */
    private function generateUniqueSlug(string $name): string
    {
        $slug = Str::slug($name);
        $originalSlug = $slug;
        $count = 1;
        
        while (Category::where('slug', $slug)->exists()) {
            $slug = $originalSlug . '-' . $count++;
        }
        
        return $slug;
    }

    /**
     * Generate color from category name.
     */
    private function generateColorFromName(string $name): string
    {
        $colors = [
            '#667eea', '#764ba2', '#f093fb', '#4facfe', '#00f2fe',
            '#43e97b', '#38f9d7', '#fa709a', '#fee140', '#fa709a',
            '#ff6b6b', '#4ecdc4', '#45b7d1', '#96ceb4', '#ffeaa7',
            '#dda0dd', '#98d8c8', '#f7b731', '#eb3b5a', '#26de81'
        ];
        
        $hash = crc32($name);
        $index = abs($hash) % count($colors);
        
        return $colors[$index];
    }

    /**
     * Log activity for category actions.
     */
    private function logActivity(string $action, Category $category, ?array $oldData = null, ?array $newData = null): void
    {
        $properties = [];
        
        if ($oldData && $newData) {
            $properties = [
                'old' => $oldData,
                'new' => $newData,
                'changes' => array_diff_assoc($newData, $oldData),
            ];
        } elseif ($oldData) {
            $properties = ['old' => $oldData];
        } elseif ($newData) {
            $properties = ['new' => $newData];
        }
        
        activity('category')
            ->causedBy(auth()->user())
            ->performedOn($category)
            ->withProperties($properties)
            ->log($action);
    }

    /**
     * Export to CSV.
     */
    private function exportToCsv($query, string $fileName, array $columns)
    {
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename=$fileName",
            'Pragma' => 'no-cache',
            'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
            'Expires' => '0',
        ];
        
        $callback = function() use ($query, $columns) {
            $file = fopen('php://output', 'w');
            
            // Add BOM for UTF-8
            fwrite($file, "\xEF\xBB\xBF");
            
            // Headers
            fputcsv($file, $this->getExportHeaders($columns));
            
            // Data in chunks to handle large datasets
            $query->chunk(self::EXPORT_CHUNK_SIZE, function($categories) use ($file, $columns) {
                foreach ($categories as $category) {
                    fputcsv($file, $this->getExportRow($category, $columns));
                }
            });
            
            fclose($file);
        };
        
        return response()->stream($callback, 200, $headers);
    }

    /**
     * Export to Excel (placeholder - install Laravel Excel package).
     */
    private function exportToExcel($query, string $fileName, array $columns)
    {
        // If you have Laravel Excel installed:
        // return Excel::download(new CategoriesExport($query, $columns), $fileName);
        
        // For now, fall back to CSV
        return $this->exportToCsv($query, str_replace('.xlsx', '.csv', $fileName), $columns);
    }

    /**
     * Export to JSON.
     */
    private function exportToJson($query, string $fileName)
    {
        $data = $query->get()->map(function($category) {
            return [
                'id' => $category->id,
                'name' => $category->name,
                'description' => $category->description,
                'parent' => $category->parent ? $category->parent->name : null,
                'products_count' => $category->products_count,
                'is_active' => $category->is_active,
                'color' => $category->color,
                'created_at' => $category->created_at->toISOString(),
                'updated_at' => $category->updated_at->toISOString(),
            ];
        });
        
        $headers = [
            'Content-Type' => 'application/json',
            'Content-Disposition' => "attachment; filename=$fileName",
        ];
        
        return response()->json($data, 200, $headers, JSON_PRETTY_PRINT);
    }

    /**
     * Get export headers based on selected columns.
     */
    private function getExportHeaders(array $columns): array
    {
        $headers = [];
        $columnMap = [
            'id' => 'ID',
            'name' => 'Name',
            'description' => 'Description',
            'parent' => 'Parent Category',
            'products_count' => 'Total Products',
            'status' => 'Status',
            'color' => 'Color',
            'created_at' => 'Created At',
            'updated_at' => 'Updated At',
        ];
        
        foreach ($columns as $column) {
            if (isset($columnMap[$column])) {
                $headers[] = $columnMap[$column];
            }
        }
        
        return $headers;
    }

    /**
     * Get export row data.
     */
    private function getExportRow(Category $category, array $columns): array
    {
        $row = [];
        
        foreach ($columns as $column) {
            switch ($column) {
                case 'id':
                    $row[] = $category->id;
                    break;
                case 'name':
                    $row[] = $category->name;
                    break;
                case 'description':
                    $row[] = $category->description ?? '';
                    break;
                case 'parent':
                    $row[] = $category->parent ? $category->parent->name : '';
                    break;
                case 'products_count':
                    $row[] = $category->products_count ?? 0;
                    break;
                case 'status':
                    $row[] = $category->is_active ? 'Active' : 'Inactive';
                    break;
                case 'color':
                    $row[] = $category->color ?? '';
                    break;
                case 'created_at':
                    $row[] = $category->created_at->format('Y-m-d H:i:s');
                    break;
                case 'updated_at':
                    $row[] = $category->updated_at->format('Y-m-d H:i:s');
                    break;
                default:
                    $row[] = '';
            }
        }
        
        return $row;
    }

    /**
 * Get categories for POS (API endpoint)
 */
// In CategoryController.php
public function apiGetCategories(Request $request): JsonResponse
{
    try {
        $categories = \App\Models\Category::where('is_active', true)
            ->orderBy('sort_order')
            ->orderBy('name')
            ->get(['id', 'name', 'color', 'slug']);
        
        return response()->json([
            'success' => true,
            'categories' => $categories
        ]);
        
    } catch (\Exception $e) {
        \Log::error('Categories API error: ' . $e->getMessage());
        return response()->json([
            'success' => false,
            'message' => 'Failed to load categories',
            'error' => config('app.debug') ? $e->getMessage() : null
        ], 500);
    }
}


public function getCategories() {
    try {
        $categories = Category::all();
        return response()->json([
            'success' => true,
            'categories' => $categories
        ]);
    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Failed to load categories'
        ], 500);
    }
}
}