<?php

namespace App\Http\Controllers\Api\V1\frontned;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\Category;
use App\Models\Product;
use App\Models\ProductLike;
use App\Models\Bid;
use App\Models\CartItem;
use App\Models\User;
use App\Models\ShippingDetails;
use App\Models\ProductReceipt;
use App\Models\Notification;
use App\Models\Payment;
use App\Models\Orders;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use App\Jobs\ProductAddSellerEmail;
use Illuminate\Support\Facades\DB;
use App\Models\Country;
use Illuminate\Support\Facades\URL;
use Intervention\Image\Facades\Image;

class ProductController extends Controller
{
    public function getCategoryList(Request $request){
        $locals = $request->header('Locals');
        // $now = Carbon::now($request->timezone);
        $now = Carbon::now('UTC');

        $product_type = $request->product_type;

        $nameField = 'name'; 
        if ($locals === 'ar') {
            $nameField = 'name_ar';
        }

        $category_data = Category::select('id', DB::raw("{$nameField} as name"), 'image', 'status', 'bid_increment_gap', 'quantity_level')
            ->withCount([
            'products as products_count' => function ($query) use ($now, $product_type, $request) {
                $query->where('is_sell', 0);
                if ($product_type === 'Sell') {
                    $query->where('product_type', 2)
                        ->whereIn('status', ['Approved']);
                } elseif ($product_type === 'Auction') {
                    $query->where('product_type', 1)
                        ->where('finish_date', '>=', $now)
                        ->where('live_date', '<=', $now)
                        ->whereIn('status', ['Approved']);
                }

                // $query->whereIn('sku', function ($subQuery) use ($request) {
                //     $subQuery->select('product_sku')
                //         ->from('shipping_details');
                    
                //     if ($request->user_country_id) {
                //         $subQuery->where('country_id', $request->user_country_id);
                //     }
                // });
            }
        ])
        ->where('status', 1)
        ->orderBy('id', 'desc')
        ->get();
        
        $notification_count = Notification::where('user_id', Auth::id())->where('is_read', 0)->count();
    
        return response()->json([
            'success' => true,
            'message' => 'Category list retrieved successfully',
            'data' => $category_data,
            'notification_count' => $notification_count,
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }

    public function createProduct(Request $request){
        $product = new Product();
        $product->name = $request->name;
        $product->name_ar = $request->name_ar;
        $product->sku = generateSKU();
        $product->description = $request->description;
        $product->description_ar = $request->description_ar;
        $product->price = $request->price;
        $product->product_quantity = $request->product_quantity;
        // $product->purchase_price = $request->purchase_price;
        $product->commission_percentage = getGeneralSettingData('seller_commission_percentage');
        $product->cat_id = $request->cat_id;
        $product->user_id = Auth::id(); // seller's ID

        if($request->product_type == 1){
            $product->live_date = $request->live_date;
            $product->duration_day = $request->duration_day;
            $product->finish_date = Carbon::parse($request->live_date)->addDays($request->duration_day);
            $product->bidding_incremented_price = $request->bidding_incremented_price;
        }

        $product->status = $request->status; // Draft, Save
        $product->condition = $request->condition; // 1: New, 2: Used 
        $product->product_type = $request->product_type;
    
        // Paths
        $tempPath  = public_path('uploads/tamp_product_image/');
        $finalPath = public_path('uploads/product_cover_image/');

        // Ensure destination folder exists
        if (!file_exists($finalPath)) {
            mkdir($finalPath, 0777, true);
        }

        // ---------- Main Cover Image ----------
        if (!empty($request->cover_image)) {
            $coverImageName = basename($request->cover_image);
            $oldPath = $tempPath . $coverImageName;
            $newPath = $finalPath . $coverImageName;

            if (file_exists($oldPath)) {
                rename($oldPath, $newPath);
                $product->cover_image = "uploads/product_cover_image/$coverImageName";
            }
        }

        // ---------- Cover Thumb 73x68 ----------
        if (!empty($request->cover_image_thumb_73X68)) {
            $thumb73x68 = basename($request->cover_image_thumb_73X68);

            $oldPath = $tempPath . $thumb73x68;
            $newPath = $finalPath . $thumb73x68;

            if (file_exists($oldPath)) {
                rename($oldPath, $newPath);
                $product->cover_image_thumb_73X68 = "uploads/product_cover_image/$thumb73x68";
            }
        }

        // ---------- Cover Thumb 375x233 ----------
        if (!empty($request->cover_image_thumb_375X233)) {
            $thumb375x233 = basename($request->cover_image_thumb_375X233);

            $oldPath = $tempPath . $thumb375x233;
            $newPath = $finalPath . $thumb375x233;

            if (file_exists($oldPath)) {
                rename($oldPath, $newPath);
                $product->cover_image_thumb_375X233 = "uploads/product_cover_image/$thumb375x233";
            }
        }
    
        $product->save();

        // if (!empty($request->images)) {
        //     foreach ($request->images as $value) {
        //         $imageName = basename($value['image']);
        //         $thumb_image_73x68 = basename($value['thumb_73x68']);
        //         $thumb_image_375x233 = basename($value['thumb_375x233']);

        //         if($value['is_cover'] != 1){
        //             $oldImagePath = public_path("uploads/tamp_product_image/$imageName");
        //             $newImagePath = public_path("uploads/product_image/$imageName");
        
        //             if (file_exists($oldImagePath) && rename($oldImagePath, $newImagePath)) {
        //                 $product->images()->create([
        //                     'image' => "uploads/product_image/$imageName"
        //                 ]);
        //             }

        //             // Move thumbnails
        //             $oldThumb73X68Path = public_path("uploads/tamp_product_image/$thumb_image_73x68");
        //             $newThumb73X68Path = public_path("uploads/product_image/$thumb_image_73x68");
        //             if (file_exists($oldThumb73X68Path) && rename($oldThumb73X68Path, $newThumb73X68Path)) {
        //                 $product->images()->create([
        //                     'image_73_68' => "uploads/product_image/$thumb_image_73x68"
        //                 ]);
        //             }
        //             $oldThumb375X233Path = public_path("uploads/tamp_product_image/$thumb_image_375x233");
        //             $newThumb375X233Path = public_path("uploads/product_image/$thumb_image_375x233");
        //             if (file_exists($oldThumb375X233Path) && rename($oldThumb375X233Path, $newThumb375X233Path)) {
        //                 $product->images()->create([
        //                     'image_375_233' => "uploads/product_image/$thumb_image_375x233"
        //                 ]);
        //             }
        //         }
        //     }
        // }

        if (!empty($request->images)) {
            $tempPath = public_path('uploads/tamp_product_image/');
            $finalPath = public_path('uploads/product_image/');

            // Ensure destination folder exists
            if (!file_exists($finalPath)) {
                mkdir($finalPath, 0777, true);
            }

            foreach ($request->images as $value) {
                // Skip cover image if needed
                if (!empty($value['is_cover']) && $value['is_cover'] == 1) {
                    continue;
                }

                $imageName = basename($value['image']);
                $thumb73x68 = basename($value['thumb_73x68'] ?? '');
                $thumb375x233 = basename($value['thumb_375x233'] ?? '');

                // ---------- Move main image ----------
                if (file_exists($tempPath . $imageName)) {
                    rename(
                        $tempPath . $imageName,
                        $finalPath . $imageName
                    );
                }

                // ---------- Move thumbnails ----------
                if ($thumb73x68 && file_exists($tempPath . $thumb73x68)) {
                    rename(
                        $tempPath . $thumb73x68,
                        $finalPath . $thumb73x68
                    );
                }

                if ($thumb375x233 && file_exists($tempPath . $thumb375x233)) {
                    rename(
                        $tempPath . $thumb375x233,
                        $finalPath . $thumb375x233
                    );
                }

                // ---------- Save SINGLE DB record ----------
                $product->images()->create([
                    'image' => "uploads/product_image/$imageName",
                    'image_73_68' => $thumb73x68 ? "uploads/product_image/$thumb73x68" : null,
                    'image_375_233' => $thumb375x233 ? "uploads/product_image/$thumb375x233" : null
                ]);
            }
        }

        if (!empty($request->documents)) {
            foreach ($request->documents as $document) {
                $document_name = basename($document['file_path']);

                $old_document_path = public_path("uploads/tamp_product_documents/$document_name");
                $new_documents_path = public_path("uploads/product_documents/$document_name");

                if (file_exists($old_document_path) && rename($old_document_path, $new_documents_path)) {
                    $product->documents()->create([
                        'name' => $document['document_name'],
                        'file_name' => "uploads/product_documents/$document_name"
                    ]);
                }
            }
        }

        if (!empty($request->shipping_details)) {
            foreach ($request->shipping_details as $shipping_detail) {
                ShippingDetails::create([
                    'product_sku' => $product->sku,
                    'country_id' => $shipping_detail['country_id'],
                    'duration_day' => $shipping_detail['duration_day'],
                    'shipping_fee' => $shipping_detail['shipping_fee'],
                ]);
            }
        }

        $product = $product->load('user', 'category');

        dispatch(new ProductAddSellerEmail($product));

        if($request->status == 'Draft'){
            $message = "Your product $request->name is currently in draft. Complete the details to submit it for review.";

            Notification::create([
                'user_id' => Auth::id(),
                'title' => "Product Status Change",
                'message' => $message,
            ]);
        }

        if($request->status == 'Pending'){
            $message = "Your product $request->name is pending approval. We will notify you once it has been reviewed.";
            
            Notification::create([
                'user_id' => Auth::id(),
                'title' => "Product Status Change",
                'message' => $message
            ]);
        }

        pushNotification(Auth::id(), $message);
    
        return response()->json([
            'status' => 'success',
            'message' => 'Your product created successfully'
        ]);
    }

    public function uploadProductImage(Request $request){
        if($request->image_type == "full_image"){
            if ($request->hasFile('image')) {
                $imageName = time().'_'.rand().'.'.$request->file('image')->getClientOriginalExtension();
                $request->file('image')->move(public_path('uploads/tamp_product_image'), $imageName);

                $directory = 'uploads/tamp_product_image/' . $imageName;
            }    
        } else {
            $imageData = base64_decode(preg_replace('#^data:image/\w+;base64,#i', '', $request->image));
        
            if ($imageData === false) {
                return response()->json(['error' => 'Invalid Base64 string.'], 400);
            }
            
            $imageName =  time().'_'.rand(). '.png';
            $directory = 'uploads/tamp_product_image/' . $imageName;

            file_put_contents(public_path($directory), $imageData);
        }
        
        $resize100 = Image::make($directory)->resize(73, 68, function ($constraint) {
            $constraint->aspectRatio();
            $constraint->upsize();
        });

        $thumb100 = 'thumb_' .time().'_'.rand(). '_73x68.png';
        $resize100->save(public_path('uploads/tamp_product_image') . '/' . $thumb100);

        $resize500 = Image::make($directory)->resize(375, 233, function ($constraint) {
            $constraint->aspectRatio();
            $constraint->upsize();
        });

        $thumb500 = 'thumb_' .time().'_'.rand(). '_375x233.png';
        $resize500->save(public_path('uploads/tamp_product_image') . '/' . $thumb500);

        return response()->json([
            'image_url' => $directory,
            'thumb_73x68' => 'uploads/tamp_product_image/' . $thumb100,
            'thumb_375x233' => 'uploads/tamp_product_image/' . $thumb500,
        ], 200);
    }

    public function uploadProductDocument(Request $request){
        $file = $request->file('documents');

        $documents_name = time().'_'.rand().'.'.$file->getClientOriginalExtension();
        $file->move(public_path('uploads/tamp_product_documents'), $documents_name);

        return response()->json([
            'documents' => 'uploads/tamp_product_documents/' . $documents_name
        ]);
    }

    public function updateProduct(Request $request){  
        $product = Product::where('sku', $request->sku)->firstOrFail();

        $product->name = $request->name;
        $product->name_ar = $request->name_ar;
        $product->description = $request->description;
        $product->description_ar = $request->description_ar;
        $product->price = $request->price;
        $product->product_quantity = $request->product_quantity;
        // $product->purchase_price = $request->purchase_price;
        $product->cat_id = $request->cat_id;
        
        if($request->product_type == 1){
            $product->live_date = $request->live_date;
            $product->duration_day = $request->duration_day;
            $product->finish_date = Carbon::parse($request->live_date)->addDays($request->duration_day);
            $product->bidding_incremented_price = $request->bidding_incremented_price;
        }
        
        if(isset($request->status)){
            $product->status = $request->status;
        }

        $product->condition = $request->condition; // 1: New, 2: Used 
        $product->product_type = $request->product_type;
        
        // ---------- Main Cover Image ----------
        if (!empty($request->cover_image)) {
            $coverImageName = basename($request->cover_image);

            $oldCoverPath = public_path("uploads/tamp_product_image/$coverImageName");
            $newCoverPath = public_path("uploads/product_cover_image/$coverImageName");

            if (file_exists($oldCoverPath) && rename($oldCoverPath, $newCoverPath)) {
                $product->cover_image = "uploads/product_cover_image/$coverImageName";
            }
        }

        // ---------- Cover Thumb 73x68 ----------
        if (!empty($request->cover_image_thumb_73X68)) {
            $thumb73x68 = basename($request->cover_image_thumb_73X68);

            $oldCoverPath = public_path("uploads/tamp_product_image/$thumb73x68");
            $newCoverPath = public_path("uploads/product_cover_image/$thumb73x68");

            if (file_exists($oldCoverPath) && rename($oldCoverPath, $newCoverPath)) {
                $product->cover_image_thumb_73X68 = "uploads/product_cover_image/$thumb73x68";
            }
        }

        // ---------- Cover Thumb 375x233 ----------
        if (!empty($request->cover_image_thumb_375X233)) {
            $thumb375x233 = basename($request->cover_image_thumb_375X233);

            $oldCoverPath = public_path("uploads/tamp_product_image/$thumb375x233");
            $newCoverPath = public_path("uploads/product_cover_image/$thumb375x233");

            if (file_exists($oldCoverPath) && rename($oldCoverPath, $newCoverPath)) {
                $product->cover_image_thumb_375X233 = "uploads/product_cover_image/$thumb375x233";
            }
        }

        if (!empty($request->images)) {
            $tempPath    = public_path('uploads/tamp_product_image/');
            $productPath = public_path('uploads/product_image/');
            $coverPath   = public_path('uploads/product_cover_image/');

            foreach ([$productPath, $coverPath] as $dir) {
                if (!file_exists($dir)) {
                    mkdir($dir, 0777, true);
                }
            }

            // Delete old DB records
            $product->images()->delete();

            foreach ($request->images as $img) {

                $imageName    = basename($img['image']);
                $thumb73x68   = basename($img['thumb_73x68']);
                $thumb375x233 = basename($img['thumb_375x233']);

                /* =========================
                    COVER IMAGE
                ========================= */
                if ($img['is_cover'] == 1) {
                    $old = public_path(ltrim($img['image'], '/'));
                    $new = $coverPath . $imageName;

                    if (file_exists($old)) {
                        rename($old, $new);
                        $product->cover_image = "uploads/product_cover_image/$imageName";
                    }

                    if ($thumb73x68) {
                        $old = public_path(ltrim($img['thumb_73x68'], '/'));
                        $new = $coverPath . $thumb73x68;

                        if (file_exists($old)) {
                            rename($old, $new);
                            $product->cover_image_thumb_73X68 = "uploads/product_cover_image/$thumb73x68";
                        }
                    }

                    if ($thumb375x233) {
                        $old = public_path(ltrim($img['thumb_375x233'], '/'));
                        $new = $coverPath . $thumb375x233;

                        if (file_exists($old)) {
                            rename($old, $new);
                            $product->cover_image_thumb_375X233 = "uploads/product_cover_image/$thumb375x233";
                        }
                    }

                    continue;
                }

                /* =========================
                    NORMAL PRODUCT IMAGE
                ========================= */

                // CASE 1: New image from temp
                if ($img['isNew'] == true) {

                    $old = public_path(ltrim($img['image'], '/'));
                    $new = $productPath . $imageName;

                    if (file_exists($old)) {
                        rename($old, $new);
                    }

                    if ($thumb73x68) {
                        $old = public_path(ltrim($img['thumb_73x68'], '/'));
                        $new = $productPath . $thumb73x68;
                        if (file_exists($old)) rename($old, $new);
                    }

                    if ($thumb375x233) {
                        $old = public_path(ltrim($img['thumb_375x233'], '/'));
                        $new = $productPath . $thumb375x233;
                        if (file_exists($old)) rename($old, $new);
                    }
                }

                // 🔥 CASE 2: OLD image but currently in COVER folder
                else {

                    $folder = explode('/', trim($img['image'], '/'))[1]; 
                    // uploads/product_cover_image/xxx.png

                    if ($folder === 'product_cover_image') {

                        $old = public_path("uploads/product_cover_image/$imageName");
                        $new = $productPath . $imageName;

                        if (file_exists($old)) {
                            rename($old, $new);
                        }

                        if ($thumb73x68) {
                            $old = public_path("uploads/product_cover_image/$thumb73x68");
                            $new = $productPath . $thumb73x68;
                            if (file_exists($old)) rename($old, $new);
                        }

                        if ($thumb375x233) {
                            $old = public_path("uploads/product_cover_image/$thumb375x233");
                            $new = $productPath . $thumb375x233;
                            if (file_exists($old)) rename($old, $new);
                        }
                    }
                }

                // ✅ Insert product image DB record
                $product->images()->create([
                    'image'         => "uploads/product_image/$imageName",
                    'image_73_68'   => $thumb73x68 ? "uploads/product_image/$thumb73x68" : null,
                    'image_375_233' => $thumb375x233 ? "uploads/product_image/$thumb375x233" : null,
                ]);
            }

            $product->save();
        }

        // if (!empty($request->images)) {
        //     $product->images()->delete();

        //     foreach ($request->images as $image) {
        //         $imageName = basename($image['image']);

        //         if($image['is_cover'] != 1){
        //             if($image['isNew'] == true){
        //                 $oldImagePath = public_path("uploads/tamp_product_image/$imageName");
        //                 $newImagePath = public_path("uploads/product_image/$imageName");
                        
        //                 if (file_exists($oldImagePath) && rename($oldImagePath, $newImagePath)) {
        //                     $product->images()->create([
        //                         'image' => "uploads/product_image/$imageName"
        //                     ]);
        //                 }

        //             } else {
        //                 $image_path = $image['image'];
        //                 $folderName = explode('/', $image_path)[2]; // Index 2 = 'product_cover_image'

        //                 if($folderName == 'product_cover_image'){
        //                     $oldImagePath = public_path("uploads/product_cover_image/$imageName");
        //                     $newImagePath = public_path("uploads/product_image/$imageName");
                            
        //                     if (file_exists($oldImagePath) && rename($oldImagePath, $newImagePath)) {
        //                         $product->images()->create([
        //                             'image' => "uploads/product_image/$imageName"
        //                         ]);
        //                     }
        //                 } else {
        //                     $product->images()->create([
        //                         'image' => "uploads/product_image/$imageName"
        //                     ]);
        //                 }
        //             }
        //         } else {
        //             $oldImagePath = public_path("uploads/product_image/$imageName");
        //             $newImagePath = public_path("uploads/product_cover_image/$imageName");

        //             if (file_exists($oldImagePath) && rename($oldImagePath, $newImagePath)) {
        //                 $product->cover_image = "uploads/product_cover_image/$imageName";
        //             }    
        //         }
        //     }
        // }

        $product->save();

        if (!empty($request->documents)) {
            // foreach ($product->documents as $existingDoc) {
            //     $old_file_path = public_path($existingDoc->file_name);
            //     if (file_exists($old_file_path)) {
            //         unlink($old_file_path);
            //     }
            // }

            $product->documents()->delete();

            foreach ($request->documents as $document) {
                $document_name = basename($document['file_path']);

                if($document['isNew'] == true){
                    $old_document_path = public_path("uploads/tamp_product_documents/$document_name");
                    $new_documents_path = public_path("uploads/product_documents/$document_name");

                    if (file_exists($old_document_path) && rename($old_document_path, $new_documents_path)) {
                        $product->documents()->create([
                            'name' => $document['document_name'],
                            'file_name' => "uploads/product_documents/$document_name"
                        ]);
                    }
                } else {
                    $product->documents()->create([
                        'name' => $document['document_name'],
                        'file_name' => "uploads/product_documents/$document_name"
                    ]);    
                }
            }
        }

        if ($request->filled('shipping_details')) {
            ShippingDetails::where('product_sku', $product->sku)->delete();

            foreach ($request->shipping_details as $shipping_detail) {
                ShippingDetails::create([
                    'product_sku' => $product->sku,
                    'country_id' => $shipping_detail['country_id'],
                    'duration_day' => $shipping_detail['duration_day'],
                    'shipping_fee' => $shipping_detail['shipping_fee'],
                ]);
            }
        }

        // if ($request->filled('shipping_details')) {
        //     foreach ($request->shipping_details as $shipping_detail) {
        //         ShippingDetails::updateOrCreate(
        //             [
        //                 'product_sku' => $product->sku,
        //                 'country_id'  => $shipping_detail['country_id'],
        //             ],
        //             [
        //                 'duration_day' => $shipping_detail['duration_day'],
        //                 'shipping_fee' => $shipping_detail['shipping_fee'],
        //             ]
        //         );
        //     }
        // }

        return response()->json([
            'status' => 'success',
            'message' => 'Your product updated successfully'
        ]);
    }

    public function getProductsList(Request $request){
        // \DB::enableQueryLog();

        $userCountryId = $request->user_country_id;
        // $now = Carbon::now($request->timezone);
        $now = Carbon::now('UTC');

        $query = Product::with([
            'user:id,first_name,last_name', 
            'category:id,name',
            'likes:user_id,product_sku',
            'cartitem:user_id,product_sku',
        ]);
        
        $query->select('id', 'cat_id', 'name', 'name_ar', 'sku', 'purchase_price', 'price', 'highest_bid_amount', 'commission_percentage', 'user_id', 'cover_image', 'cover_image_thumb_73X68', 'live_date', 'duration_day', 'finish_date', 'status', 'condition', 'bidding_incremented_price', 'product_type', 'created_at');
        $query->addSelect(DB::raw('(select count(*) from bids where bids.product_sku = products.sku) as bids_count'));

        $query->where('is_sell', 0);

        // if($userCountryId){
        //     $query->whereExists(function ($subQuery) use ($userCountryId) {
        //         $subQuery->select(DB::raw(1))
        //             ->from('shipping_details')
        //             ->whereColumn('shipping_details.product_sku', 'products.sku')
        //             ->where('shipping_details.country_id', $userCountryId);
        //     });
        // }

        if ($request->has('product_name') && !empty($request->product_name)) {
            $searchKeyword = '%' . $request->product_name . '%';

            $query->where(function ($innerQuery) use ($searchKeyword) {
                $innerQuery->where('name', 'LIKE', $searchKeyword)
                           ->orWhere('name_ar', 'LIKE', $searchKeyword)
                           ->orWhere('description', 'LIKE', $searchKeyword)
                           ->orWhere('description_ar', 'LIKE', $searchKeyword);
            });
        }

        if ($request->has('cat_id') && !empty($request->cat_id)) {
            $query->where('cat_id', $request->cat_id);
        }

        if ($request->status == 'Pending') {
            $query->where('live_date', '>', $now)
                ->where('status', 'Approved')
                ->orderBy('live_date', 'asc');
                // ->whereIn('status', ['Approved', 'Save']);
        } else if ($request->status == 'Approved') {
            $query->where(function ($q) use ($now) {
                $q->where('status', 'Approved')
                    ->where('live_date', '<', $now)
                    ->where('finish_date', '>', $now);
            })
        	->orderBy(DB::raw('GREATEST(live_date, finish_date)'), 'asc');
        } else if($request->status == 2){
            $query->where('product_type', 2)
                ->where('status', 'Approved')
                ->where('product_quantity', '>', 0)
        	    ->orderBy('id', 'desc');
        }

        $minPrice = $request->input('min_price');
        $maxPrice = $request->input('max_price');

        if ($minPrice !== null && $maxPrice !== null) {
            $query->whereBetween('price', [$minPrice, $maxPrice]);
        } elseif ($minPrice !== null) {
            $query->where('price', '>=', $minPrice);
        } elseif ($maxPrice !== null) {
            $query->where('price', '<=', $maxPrice);
        }

        // if ($request->has('sort_by')) {
            // if ($request->sort_by == 'relevant') {
                // $query->withCount('likes')->orderBy('likes_count', 'desc');
            // } elseif ($request->sort_by == 'newest') {
                // $query->orderBy('created_at', 'desc');
            // }
        // } else {
            // $query->orderBy('created_at', 'desc');
        // }

        if ($request->has('condition')) {
            if ($request->condition === 1) {
                $query->where('condition', '=', 1);
            } elseif ($request->condition === 2) {
                $query->where('condition', '=', 2);
            }
        }

        $products_data = $query->paginate(10);

        // dd(\DB::getQueryLog());

        $products_data->getCollection()->transform(function ($product) use ($userCountryId, $request){
            $product->cover_image = $product->cover_image_thumb_73X68 != '' ? $product->cover_image_thumb_73X68 : $product->cover_image;

            $country_details = getCountrydetailsById($userCountryId);
            if(isset($userCountryId) && !empty($userCountryId)){
                $product->exchange_price = getExchangePrice($country_details->currency, $product->price);
            } else {
                $product->exchange_price = 0;
            }

            /* ---------------- HIGHEST BIDDER COUNTRY ---------------- */
            $product->highest_bidders_country_details = null;
            if($request->status == 'Approved'){
                $highest_bidders_country_flag = $product->bids()->orderByDesc('amount')->first();

                if(isset($highest_bidders_country_flag) && !empty($highest_bidders_country_flag)){
                    $highest_bidders_country_id = User::select('country_id')
                        ->where('id', $highest_bidders_country_flag->user_id)
                        ->first()->country_id;

                    $highest_bidders_country_details = getCountrydetailsById($highest_bidders_country_id);
                    $product->highest_bidders_country_details = $highest_bidders_country_details;
                }
            }

            /* ---------------- BID INDICATORS ---------------- */
            $product->is_outbid = null;
            $userId = $request->user_id;

            if (!empty($userId)) {
                // Check if user has placed any bid on this product
                $userHasBid = $product->bids()->where('user_id', $userId)->exists();

                // If user never bid → keep NULL
                if ($userHasBid) {
                    // Get highest bid of product
                    $highestBid = $product->bids()->orderByDesc('amount')->first();

                    if ($highestBid) {
                        // TRUE = user is winning, FALSE = user losing
                        $product->is_outbid = ($highestBid->user_id == $userId);
                    }
                }
            }

            return $product;
        });

        $minMaxPrice = null;
        if ($request->has('cat_id') && !empty($request->cat_id)) {
            $minMaxPrice = Product::where('cat_id', $request->cat_id)
                ->selectRaw('MIN(price) as min_price, MAX(price) as max_price')
                ->first();
        }

        return response()->json([
            'success' => true,
            'message' => 'Product list retrieved successfully',
            'data' => $products_data,
            'min_max_price' => $minMaxPrice,
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }

    public function sellerMyProductList(Request $request){
        $user = $request->user();
        // $now = Carbon::now($request->timezone);
        $now = Carbon::now('UTC');

        $query = Product::with([
            'category:id,name',
            'likes:user_id,product_sku',
            'cartitem:user_id,product_sku'
        ]);
        
        $query->select('id', 'cat_id', 'name', 'name_ar', 'sku', 'purchase_price', 'price', 'product_quantity', 'highest_bid_amount', 'commission_percentage', 'user_id', 'cover_image', 'cover_image_thumb_73X68', 'live_date', 'duration_day', 'finish_date', 'status', 'condition', 'bidding_incremented_price', 'product_type', 'is_sell', 'created_at');

        $query->addSelect(DB::raw('(select count(*) from bids where bids.product_sku = products.sku) as bids_count'));

        if(isset($request->seller_id)){
            $query->where('user_id', $request->seller_id);
        } else {
            $query->where('user_id', $user->id);
        }

        if ($request->filter == 'Paid' && $request->product_type == 1) {
            $query->where('product_type', 1)
                ->where('is_sell', 1);
        } else if ($request->filter == 'Reserved' && $request->product_type == 1) {
            $query->where('product_type', 1) // only auction type products
                ->where('is_sell', 0) // not yet marked as sold
                ->whereIn('sku', function ($sub) {
                    $sub->select('bids.product_sku')
                        ->from('bids')
                        ->where('bids.is_crow', 1) // user won the auction
                        ->whereNotIn('bids.product_sku', function ($sub2) {
                            $sub2->select('orders.product_sku')
                                ->from('orders'); // payment not done
                        });
                });
        } else if ($request->status == 'Pending') {
            $query->where('live_date', '>', $now)
                ->whereIn('status', ['Approved'])
                ->where('is_sell', '=', 0);
        } else if ($request->filter == 'Currently' || $request->status == 'Approved') {
            $query->where(function ($q) use ($now) {
                $q->whereIn('status', ['Approved'])
                    ->where('live_date', '<', $now)
                    ->where('finish_date', '>', $now)
                    ->where('is_sell', '=', 0);
            });
        } else if($request->status == 2 || $request->product_type == 2){
            $query->where('product_type', 2);

            if($request->product_type == 2){
                if ($request->filter == 'Approved') {
                    // No sales done
                    $query->where('status', 'Approved')
                        ->whereNotIn('sku', function ($sub) {
                                $sub->select('product_sku')
                                    ->from('orders');
                        });
                } else if ($request->filter == 'Partially Sold') {
                    // Some quantity sold but not all
                    $query->where('product_quantity', '>', 0)
                        ->whereIn('sku', function ($sub) {
                                $sub->select('product_sku')
                                    ->from('orders');
                        });
                } else if ($request->filter == 'Sold') {
                    // Sold qty == total qty
                    $query->where('product_quantity', 0);
                }
            } else if($request->status == 2){
                $query->where('is_sell', '=', 0);    
            }
        }

        if ($request->has('product_type') && !empty($request->product_type)) {
            $query->where('product_type', $request->product_type);
        }

        $products_data = $query->orderBy('created_at', 'desc')->paginate(10);

        $products_data->getCollection()->transform(function ($product) use($request, $user, $now) {
            $product->cover_image = $product->cover_image_thumb_73X68 != '' ? $product->cover_image_thumb_73X68 : $product->cover_image;

            $bidCount = Bid::where('product_sku', $product->sku)->count();
            $product_receipt = ProductReceipt::where(['product_sku' => $product->sku])->first();

            if ($product->product_type == 1 && $product->status == 'Approved') {
                if ($now->between($product->live_date, $product->finish_date)) {
                    $product->status = 'Live'; // between live date and finish date
                } elseif ($now->lt($product->live_date)) {
                    $product->status = 'Up Coming'; // before live date
                } elseif ($product->is_sell == 1) {
                    if($product_receipt && !empty($product_receipt->file_path)) {
                        $product->status = 'Sold'; // paid and receipt uploaded
                    } else {
                        $product->status = 'Attach Receipt'; // paid but receipt not yet uploaded
                    }
                } else {
                    $product->status = $bidCount == 0 ? 'Expired' : 'Process to Pay'; // after finish date with/without bids
                }
            } else if ($product->product_type == 2 && $product->status == 'Approved') {
                $check_status = Orders::where(['product_sku' => $product->sku])->count();
                
                if($product->product_quantity == 0){
                    $product->status = 'Sold'; // all quantity sold
                } else if($check_status > 0){
                    $product->status = 'Partially Sold'; // some quantity sold
                } else if($product_receipt && empty($product_receipt->file_path)) {
                    $product->status = 'Attach Receipt'; // paid but receipt not yet uploaded
                } else {
                    $product->status = 'Approved'; // no quantity sold yet
                }
            }

            $country_details = getCountrydetailsById($user->country_id);
            $product->exchange_price = getExchangePrice($country_details->currency, $product->price);

            /* ---------------- HIGHEST BIDDER COUNTRY ---------------- */
            $product->highest_bidders_country_details = null;
            if($request->product_type == 1 || $request->status == 'Approved'){
                $highest_bidders_country_flag = $product->bids()->orderByDesc('amount')->first();

                if(isset($highest_bidders_country_flag) && !empty($highest_bidders_country_flag)){
                    $highest_bidders_country_id = User::select('country_id')
                        ->where('id', $highest_bidders_country_flag->user_id)
                        ->first()->country_id;

                    $highest_bidders_country_details = getCountrydetailsById($highest_bidders_country_id);
                    $product->highest_bidders_country_details = $highest_bidders_country_details;
                }
            }

            return $product;
        });

        $seller_details = [];
        if(isset($request->seller_id)){
            $seller_details = User::select('id', 'first_name', 'last_name', 'seller_description', 'seller_profile_image', 'seller_cover_image')->where('id', $request->seller_id)->first()->toArray();
        }

        return response()->json([
            'success' => true,
            'message' => 'Product list retrieved successfully',
            'seller_details' => $seller_details,
            'data' => $products_data
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }

    public function productLikeUnlike(Request $request){
        $user_id = auth()->id();
        $product_sku = $request->product_sku;

        $like = ProductLike::where('user_id', $user_id)->where('product_sku', $product_sku)->first();

        if ($like) {
            $like->delete();

            return response()->json(['status' => 'unliked']);
        } else {
            ProductLike::create([
                'user_id' => $user_id,
                'product_sku' => $product_sku
            ]);

            return response()->json(['status' => 'liked']);
        }
    }

    public function getProductsDetails(Request $request){
        $locals = $request->header('Locals');
        $user_id = $request->user_id;
        // $now = Carbon::now($request->timezone);
        $now = Carbon::now('UTC');

        $product = Product::with('user:id,first_name,last_name,seller_profile_image', 'images:product_id,image,image_73_68,image_375_233')->where('sku', $request->product_sku)->first();

        $liked = ProductLike::where('user_id', $user_id)->where('product_sku', $request->product_sku)->exists();

        $cartitem = CartItem::where('user_id', $user_id)->where('product_sku', $request->product_sku)->exists();
            
        $bidCount = Bid::where('product_sku', $request->product_sku)->count();

        $shipping_details = ShippingDetails::where('product_sku', $request->product_sku)->get();

        $product_receipt = ProductReceipt::where(['product_sku' => $request->product_sku, 'user_id' => $user_id])->first();

        $product_receipt_count = Orders::where('product_sku', $request->product_sku)
            ->whereDoesntHave('productReceipt', function ($q) {
                $q->whereNotNull('file_path');
            })
            ->count();
        
        $environment_data = getEnvironmentData();
        $wait_for_cash_payment_days = $environment_data['WAIT_FOR_CASH_PAYMENT_DAYS'];

        $payment_options = $environment_data['PAYMENT_OPTIONS'];
        $cashPaymentEnabled = data_get($payment_options, 'value', 0);

        $seller_id = null;
        $seller_name = null;
        $seller_image = null;
        if(isset($product->user)){
            $seller_id = $product->user->id;
            $seller_name = $product->user->first_name . ' ' . $product->user->last_name;
            $seller_image = $product->user->seller_profile_image;
        }

        $product_receipt_file_path = null;
        if(isset($product_receipt)){
            $product_receipt_file_path = $product_receipt->file_path;
        }

        /* ---------------- HIGHEST BIDDER COUNTRY ---------------- */
        $highest_bidders_country_details = null;
        $highest_bidders_country_flag = $product->bids()->orderByDesc('amount')->first();
        if(isset($highest_bidders_country_flag) && !empty($highest_bidders_country_flag)){
            $highest_bidders_country_id = User::select('country_id')
                ->where('id', $highest_bidders_country_flag->user_id)
                ->first()->country_id;

            $highest_bidders_country_details = getCountrydetailsById($highest_bidders_country_id);
        }

        /* ---------------- BID INDICATORS ---------------- */
        $is_outbid = null;        
        if (!empty($user_id)) {
            // Check if user has placed any bid on this product
            $userHasBid = $product->bids()->where('user_id', $user_id)->exists();

            // If user never bid → keep NULL
            if ($userHasBid) {
                // Get highest bid of product
                $highestBid = $product->bids()->orderByDesc('amount')->first();

                if ($highestBid) {
                    // TRUE = user is winning, FALSE = user losing
                    $is_outbid = ($highestBid->user_id == $user_id);
                }
            }
        }

        $link = null;
        if($user_id != 'undefined'){
        // if(isset($user_id) && !empty($user_id)){
            $country_id = User::select('country_id')->where('id', $user_id)->first()->country_id;
            $country_details = getCountrydetailsById($country_id);
            $product->exchange_price = getExchangePrice($country_details->currency, $product->price);
  
            if($now < $product->finish_date){
                $link = null;
            } else {
                // $finishDate = Carbon::parse($product->finish_date)->addHours(24);
                $PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS = $environment_data['PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS'];
                $finishDate = Carbon::parse($product->finish_date)->addDays($PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS)->format('Y-m-d H:i');
                if($now < $finishDate){
                    $highestBid = Bid::where('id', $request->bid_id)->where('is_crow', 1)->orderByDesc('amount')->first();

                    if(isset($highestBid) && !empty($highestBid)){
                        if($cashPaymentEnabled == 1){
                            $payment_link_1 = URL::temporarySignedRoute(
                                'admin.payment.select_payment_option', // named route
                                now()->addDays($PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS)->endOfDay(), // 15th day 11:59 PM
                                [
                                    'user_id' => $highestBid->user_id,
                                    'product_sku' => $highestBid->product_sku,
                                ]
                            );
                        } else {
                            $payment_link_1 = URL::temporarySignedRoute(
                                'admin.payment.process_to_buy_product', // named route
                                now()->addDays($PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS)->endOfDay(), // 15th day 11:59 PM
                                [
                                    'user_id' => $highestBid->user_id,
                                    'product_sku' => $highestBid->product_sku,
                                ]
                            );  
                        }

                        $link = $payment_link_1;
                    } else {
                        $link = "";    
                    }
                } else {
                    $link = "";
                }
            }
        } else {
            $product->exchange_price = 0;
        }

        if ($product->product_type == 1 && $product->status == 'Approved') {
            if ($now->between($product->live_date, $product->finish_date)) {
                $status = 'Approved'; // between live date and finish date
            } elseif ($now->lt($product->live_date)) {
                $status = 'Approved'; // before live date
            } elseif ($product->is_sell == 1) {
                $status = 'Approved'; // sold after auction
            } else {
                $status = $bidCount == 0 ? 'Approved' : 'Process to Pay'; // after finish date with/without bids
            }
        } else if ($product->product_type == 2 && $product->status == 'Approved') {
            $status = 'Approved';
        } else {
            $status = $product->status;
        }

        // if($product->cover_image_thumb_73X68 != ''){
            $images = $product->images->map(function ($image) {
                return [
                    'image' => asset($image->image),
                    'thumb_73x68' => $image->image_73_68 != '' ? asset($image->image_73_68) : asset($image->image),
                    'thumb_375x233' => $image->image_375_233 != '' ? asset($image->image_375_233) : asset($image->image),
                ];
            });
        // } else {
        //     $images = $product->images
        //         ->pluck('image')
        //         ->map(fn ($image) => asset($image))
        //         ->values();
        // }

        $product_details = [
            'id' => $product->id,
            'cat_id' => $product->cat_id,
            'sku' => $product->sku,
            'name' => $product->name,
            'name_ar' => $product->name_ar,
            'description' => $product->description,
            'description_ar' => $product->description_ar,
            'price' => $product->price,
            'product_type' => $product->product_type,
            'product_quantity' => $product->product_quantity,
            'exchange_price' => $product->exchange_price,
            'highest_bid_amount' => $product->highest_bid_amount,
            'commission_percentage' => $product->commission_percentage,
            'cover_image' => asset($product->cover_image),
            'cover_image_thumb_73X68' => $product->cover_image_thumb_73X68 != '' ? asset($product->cover_image_thumb_73X68) : asset($product->cover_image),
            'cover_image_thumb_375X233' => $product->cover_image_thumb_375x233 != '' ? asset($product->cover_image_thumb_375x233) : asset($product->cover_image),
            // 'images' => $product->images->map(function ($image) {
            //     return [
            //         'image' => asset($image->image),
            //         'thumb_73x68' => $image->image_73_68 != '' ? asset($image->image_73_68) : asset($image->image),
            //         'thumb_375x233' => $image->image_375_233 != '' ? asset($image->image_375_233) : asset($image->image),
            //     ];
            // }),
            'images' => $images,
            'seller' => [
                'id' =>  $seller_id,
                'name' => $seller_name,
                'image' => $seller_image
            ],
            'documents' => $product->documents->map(function ($document) {
                return [
                    'name' => $document->name,
                    'file_name' => asset($document->file_name),
                ];
            }),
            'cartitem' => $cartitem,
            // 'live_date' => optional($product->live_date)->toISOString(),
            // 'finish_date' => optional($product->finish_date)->toISOString(),
            'live_date' => $product->live_date,
            'finish_date' => $product->finish_date,
            'duration_day' => $product->duration_day,
            'status' => $status,
            'condition' => $product->condition,
            'liked' => $liked,
            // 'bidding_price' => getGeneralSettingData('bidding_price'),
            'bid_count' => $bidCount,
            'bidding_incremented_price' => $product->bidding_incremented_price,
            'product_type' => $product->product_type,
            'is_sell' => $product->is_sell,
            'shipping_details' => $shipping_details->map(function ($shipping_detail) {
                return [
                    'id' => $shipping_detail->id,
                    'product_sku' => $shipping_detail->product_sku,
                    'country_id' => $shipping_detail->country_id,
                    'duration_day' => $shipping_detail->duration_day,
                    'shipping_fee' => $shipping_detail->shipping_fee,
                ];
            }),
            'product_receipt_file_path' => $product_receipt_file_path,
            'product_receipt_count' => $product_receipt_count,
            'created_at' => $product->created_at,
            'payment_link' => $link,
            'wait_for_cash_payment_days' => $wait_for_cash_payment_days,
            'payment_options' => $cashPaymentEnabled,
            'highest_bidders_country_details' => $highest_bidders_country_details,
            'is_outbid' => $is_outbid
        ];

        return response()->json([
            'status' => 'success',
            'data' => $product_details
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }

    public function productFavoritesList(Request $request){
        $user = $request->user();
        $now = Carbon::now($request->timezone);
        $now = Carbon::now('UTC');

        $productsQuery = Product::select('products.id', 'products.user_id', 'products.name', 'products.name_ar', 'products.sku', 'products.price', 'products.highest_bid_amount', 'products.cover_image', 'products.cover_image_thumb_73X68', 'products.condition', 'products.status', 'products.live_date', 'products.finish_date');

        $productsQuery->addSelect(DB::raw('(select count(*) from bids where bids.product_sku = products.sku) as bids_count'));

        $productsQuery->join('product_likes', 'product_likes.product_sku', '=', 'products.sku')
            ->where('product_likes.user_id', $user->id);

        if ($request->status == 'Pending') {
            $productsQuery->where('products.live_date', '>', $now)
                        ->where('products.status', 'Approved');
        } else if ($request->status == 'Approved') {
            $productsQuery->where(function ($q) use ($now) {
                $q->where('products.status', 'Approved')
                    ->where('products.live_date', '<', $now)
                    ->where('products.finish_date', '>', $now);
            });
        } else if($request->status == 2){
            $productsQuery->where('products.product_type', $request->status);
        }

        $products = $productsQuery->paginate(10);

        $products->getCollection()->transform(function ($product) use ($user, $request, $now){
            $product->cover_image = $product->cover_image_thumb_73X68 != '' ? $product->cover_image_thumb_73X68 : $product->cover_image;
            $country_details = getCountrydetailsById($user->country_id);
            $product->exchange_price = getExchangePrice($country_details->currency, $product->price);

            /* ---------------- HIGHEST BIDDER COUNTRY ---------------- */
            $product->highest_bidders_country_details = null;
            if($request->status == 'Approved'){
                $highest_bidders_country_flag = $product->bids()->orderByDesc('amount')->first();

                if(isset($highest_bidders_country_flag) && !empty($highest_bidders_country_flag)){
                    $highest_bidders_country_id = User::select('country_id')
                        ->where('id', $highest_bidders_country_flag->user_id)
                        ->first()->country_id;

                    $highest_bidders_country_details = getCountrydetailsById($highest_bidders_country_id);
                    $product->highest_bidders_country_details = $highest_bidders_country_details;
                }
            }

            /* ---------------- BID INDICATORS ---------------- */
            $product->is_outbid = null;
            $user_id = $user->id;
            if (!empty($user_id)) {
                // Check if user has placed any bid on this product
                $userHasBid = $product->bids()->where('user_id', $user_id)->exists();

                // If user never bid → keep NULL
                if ($userHasBid) {
                    // Get highest bid of product
                    $highestBid = $product->bids()->orderByDesc('amount')->first();

                    if ($highestBid) {
                        // TRUE = user is winning, FALSE = user losing
                        $product->is_outbid = ($highestBid->user_id == $user_id);
                    }
                }
            }

            return $product;
        });

        return response()->json([
            'status' => 'success',
            'data' => $products,
            'now' => $now
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }

    public function addBid(Request $request){
        Bid::create([
            'user_id' => Auth::id(),
            'product_sku' => $request->product_sku,
            'amount' => $request->amount
        ]);

        Product::where('sku', $request->product_sku)->update(['highest_bid_amount' => $request->amount]);

        return response()->json([
            'status' => 'success',
            'message' => 'Bid placed successfully.'
        ]);
    }

    public function oldgetHighestBidsList(Request $request){
        $user = $request->user();

        $now = Carbon::now($request->timezone);
        
        $bidCounts = Bid::select('product_sku', DB::raw('COUNT(*) as bids_count'))
        ->groupBy('product_sku');

        $bids = Bid::join('products', 'products.sku', '=', 'bids.product_sku')
            ->joinSub($bidCounts, 'bid_counts', function ($join) {
                $join->on('bids.product_sku', '=', 'bid_counts.product_sku');
            })
            ->where('bids.user_id', $user->id);
            // ->where('products.finish_date', '>', $now)

            if ($request->type == "won") {
                $bids->where('bids.is_crow', 1); // Won items
            } else if ($request->type == "lost") {
                $bids->where('bids.is_crow', 0)
                    ->where('products.finish_date', '<', $now); // Lost items
            } else if ($request->type == "ongoing") {
                $bids->where('products.live_date', '<', $now)
                    ->where('products.finish_date', '>', $now); // Ongoing bids
            }

            $bids = $bids->select(
                'bids.id',
                'bids.product_sku',
                'bids.amount',
                'bids.user_id',
                'bids.created_at',
                'products.user_id',
                'products.name as product_name',
                'products.name_ar as product_name_ar',
                'products.cover_image',
                'products.cover_image_thumb_73X68',
                'products.finish_date',
                'products.price',
                'products.live_date',
                'products.duration_day',
                'bid_counts.bids_count'
            )
            ->orderBy('bids.created_at', 'desc')
            ->paginate(10);

            $bids->getCollection()->transform(function ($product) use($request, $user, $now) {
                $product->cover_image = $product->cover_image_thumb_73X68 != '' ? $product->cover_image_thumb_73X68 : $product->cover_image;
                $product->live_date = $product->live_date ? Carbon::parse($product->live_date, 'UTC')->toISOString() : null;
                $product->finish_date = $product->finish_date ? Carbon::parse($product->finish_date, 'UTC')->toISOString() : null;

                $country_details = getCountrydetailsById($user->country_id);
                $product->exchange_price = getExchangePrice($country_details->currency, $product->price);
                $product->exchange_amount = getExchangePrice($country_details->currency, $product->amount);
                $product->win_price = Bid::where('product_sku', $product->product_sku)->where('is_crow', 1)->orderByDesc('amount')->value('amount');

                /* ---------------- HIGHEST BIDDER COUNTRY ---------------- */
                $product->highest_bidders_country_details = null;
                if($request->type == "won" || $request->type == "lost" || $request->type == "ongoing"){
                    $highest_bidders_country_flag = Bid::where('product_sku', $product->product_sku)
                        ->orderByDesc('amount')->first();

                    if(isset($highest_bidders_country_flag) && !empty($highest_bidders_country_flag)){
                        $highest_bidders_country_id = User::select('country_id')
                            ->where('id', $highest_bidders_country_flag->user_id)
                            ->first()->country_id;

                        $highest_bidders_country_details = getCountrydetailsById($highest_bidders_country_id);
                        $product->highest_bidders_country_details = $highest_bidders_country_details;
                    }
                }

                /* ---------------- BID INDICATORS ---------------- */
                $product->is_outbid = null;
                $userId = $user->id;

                if (!empty($userId)) {
                    // Check if user has placed any bid on this product
                    $userHasBid = Bid::where('product_sku', $product->product_sku)->where('user_id', $userId)->exists();

                    // If user never bid → keep NULL
                    if ($userHasBid) {

                        /* ================= AUCTION RUNNING ================= */
                        if ($now->between($product->live_date, $product->finish_date)) {
                            // Get highest bid of product
                            $highestBid = Bid::where('product_sku', $product->product_sku)->orderByDesc('amount')->first();

                            if ($highestBid) {
                                // TRUE = user is winning, FALSE = user losing
                                $product->is_outbid = ($highestBid->user_id == $userId);
                            }
                        }  
                        /* ================= AUCTION ENDED ================= */
                        else if ($now->greaterThanOrEqualTo($product->finish_date)) {
                            // Winner based on is_crow = 1
                            $crowBid = Bid::where('product_sku', $product->product_sku)->where('is_crow', 1)->first();

                            if ($crowBid) {
                                // TRUE = auction winner
                                // FALSE = lost auction
                                $product->is_outbid = ($crowBid->user_id == $userId);
                            }
                        }
                    }
                }

                return $product;
            });

        return response()->json([
            'status' => 'success',
            'message' => 'Active product bids with details retrieved successfully.',
            'data' => $bids
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }

    public function getHighestBidsList(Request $request){
        $user = $request->user();
        // $now  = Carbon::now($request->timezone);
        $now = Carbon::now('UTC');

        /*
        |--------------------------------------------------------------------------
        | STEP 1: Logged-in user ni HIGHEST bid per product
        |--------------------------------------------------------------------------
        */
        $userHighestBids = Bid::select(
                'product_sku',
                DB::raw('MAX(amount) as user_max_amount')
            )
            ->where('user_id', $user->id)
            ->groupBy('product_sku');

        /*
        |--------------------------------------------------------------------------
        | STEP 2: Main query (ek product = ek row)
        |--------------------------------------------------------------------------
        */
        $bids = Bid::joinSub($userHighestBids, 'user_bids', function ($join) {
                    $join->on('bids.product_sku', '=', 'user_bids.product_sku')
                        ->on('bids.amount', '=', 'user_bids.user_max_amount');
                })
                ->join('products', 'products.sku', '=', 'bids.product_sku')
                ->where('bids.user_id', $user->id);

        /*
        |--------------------------------------------------------------------------
        | STEP 3: Filters (won / lost / ongoing)
        |--------------------------------------------------------------------------
        */
        if ($request->type == "won") {
            $bids->where('bids.is_crow', 1);
        }
        else if ($request->type == "lost") {
            $bids->where('bids.is_crow', 0)
                ->where('products.finish_date', '<', $now);
        }
        else if ($request->type == "ongoing") {
            $bids->where('products.live_date', '<', $now)
                ->where('products.finish_date', '>', $now);
        }

        /*
        |--------------------------------------------------------------------------
        | STEP 4: Select & paginate
        |--------------------------------------------------------------------------
        */
        $bids = $bids->select(
                'bids.id',
                'bids.product_sku',
                'bids.amount',
                'bids.user_id',
                'bids.created_at',
                'products.user_id as seller_id',
                'products.name as product_name',
                'products.name_ar as product_name_ar',
                'products.cover_image',
                'products.cover_image_thumb_73X68',
                'products.finish_date',
                'products.live_date',
                'products.duration_day',
                'products.price'
            )
            ->orderBy('bids.created_at', 'desc')
            ->paginate(10);

        /*
        |--------------------------------------------------------------------------
        | STEP 5: Transform response
        |--------------------------------------------------------------------------
        */
        $bids->getCollection()->transform(function ($product) use ($user, $now) {

            // Image
            $product->cover_image = $product->cover_image_thumb_73X68 ?: $product->cover_image;

            // Dates
            $product->live_date   = $product->live_date
                                    ? Carbon::parse($product->live_date, 'UTC')->toISOString()
                                    : null;

            $product->finish_date = $product->finish_date
                                    ? Carbon::parse($product->finish_date, 'UTC')->toISOString()
                                    : null;

            // Currency exchange
            $country_details = getCountrydetailsById($user->country_id);
            $product->exchange_price  = getExchangePrice($country_details->currency, $product->price);
            $product->exchange_amount = getExchangePrice($country_details->currency, $product->amount);

            /*
            |--------------------------------------------------------------------------
            | WIN PRICE
            |--------------------------------------------------------------------------
            */
            $product->win_price = Bid::where('product_sku', $product->product_sku)
                ->where('is_crow', 1)
                ->orderByDesc('amount')
                ->value('amount');

            /*
            |--------------------------------------------------------------------------
            | HIGHEST BIDDER COUNTRY
            |--------------------------------------------------------------------------
            */
            $highestBid = Bid::where('product_sku', $product->product_sku)
                ->orderByDesc('amount')
                ->first();

            $product->highest_bidders_country_details = null;
            if ($highestBid) {
                $countryId = User::where('id', $highestBid->user_id)->value('country_id');
                $product->highest_bidders_country_details = getCountrydetailsById($countryId);
            }

            $product->bids_count = Bid::where('product_sku', $product->product_sku)->count();

            /*
            |--------------------------------------------------------------------------
            | BID STATUS INDICATOR
            | true  = user is WINNING
            | false = user is LOSING
            | null  = user never bid
            |--------------------------------------------------------------------------
            */
            $product->is_outbid = null;

            $userHasBid = Bid::where('product_sku', $product->product_sku)
                ->where('user_id', $user->id)
                ->exists();

            if ($userHasBid) {

                // Auction running
                if ($now->between($product->live_date, $product->finish_date)) {
                    $product->is_outbid = ($highestBid && $highestBid->user_id == $user->id);
                }

                // Auction ended
                else if ($now->greaterThanOrEqualTo($product->finish_date)) {
                    $crowBid = Bid::where('product_sku', $product->product_sku)
                        ->where('is_crow', 1)
                        ->first();

                    if ($crowBid) {
                        $product->is_outbid = ($crowBid->user_id == $user->id);
                    }
                }
            }

            return $product;
        });

        /*
        |--------------------------------------------------------------------------
        | FINAL RESPONSE
        |--------------------------------------------------------------------------
        */
        return response()->json([
            'status'  => 'success',
            'message' => 'Highest bids list retrieved successfully',
            'data'    => $bids
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }


    public function geProductWiseHighestBidsList(Request $request){
        $bids = Bid::with('user:id,first_name,last_name')
            ->where('product_sku', $request->product_sku)
            ->orderBy('created_at', 'desc')
            ->get();

        return response()->json([
            'success' => true,
            'data'    => $bids,
        ]);
    }

    public function addToCart(Request $request){
        $user = $request->user();
        $product_sku = $request->product_sku;

        $existingCartItem = CartItem::where('user_id', $user->id)
            ->where('product_sku', $product_sku)
            ->first();

        if ($existingCartItem) {
            return response()->json([
                'status' => false,
                'message' => 'Product already exists in your cart.',
            ], 400);
        }

        CartItem::create([
            'user_id' => $user->id,
            'product_sku' => $product_sku,
            'quantity' => $request->product_quantity,
        ]);

        return response()->json([
            'status' => true,
            'message' => 'Product added to cart successfully.',
        ]);
    }

    public function cartItemsList(Request $request){
        $user = $request->user();
        $userCountryId = $user->country_id;

        $cartItems = $user->cartItems()
            ->with([
                'product' => function ($query) use ($userCountryId) {
                    $query->select('id', 'sku', 'name', 'name_ar', 'price', 'product_quantity', 'cover_image', 'cover_image_thumb_73X68', 'condition')
                            // ->whereIn('sku', function ($subQuery) use ($userCountryId) {
                            //     $subQuery->select('product_sku')
                            //         ->from('shipping_details')
                            //         ->where('country_id', $userCountryId);
                            // })
                            ->with([
                                'likes:user_id,product_sku',
                                'shippingDetails'
                                // 'shippingDetails' => function ($q) use ($userCountryId) {
                                //     $q->where('country_id', $userCountryId); // Only get relevant shipping details
                                // }
                            ]);
                }
            ])
            ->get()
            ->filter(function ($item) {
                // Filter out cart items where product is null due to shipping restriction
                return !is_null($item->product);
            })
            ->map(function ($item) use ($userCountryId){
                $country_details = getCountrydetailsById($userCountryId);
                $exchange_price = getExchangePrice($country_details->currency, $item->product->price);

                return [
                    'id' => $item->id,
                    'user_id' => $item->user_id,
                    'product_sku' => $item->product_sku,
                    'quantity' => $item->quantity,
                    'sku' => $item->product->sku ?? null,
                    'name' => $item->product->name ?? null,
                    'name_ar' => $item->product->name_ar ?? null,
                    'price' => $item->product->price ?? null,
                    'exchange_price' => $exchange_price ?? 0,
                    'cover_image' => $item->product->cover_image_thumb_73X68 != '' ? $item->product->cover_image_thumb_73X68 :  $item->product->cover_image,
                    'condition' => $item->product->condition ?? null,
                    'likes' => $item->product->likes ?? [],
                    'shipping_details' => $item->product->shippingDetails ?? [],
                ];
            });

        return response()->json([
            'status' => true,
            'message' => 'Cart items fetched successfully.',
            'data' => $cartItems
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }

    public function checkCommonShippingCountries(Request $request){
        $user = $request->user();

        // Get all cart items for the user, eagerly loading the product and its shipping details
        $cartItems = $user->cartItems()->with('product.shippingDetails')->get();

        if ($cartItems->isEmpty()) {
            return response()->json([
                'status' => false,
                'message' => 'Your cart is empty. Please add products to check for common shipping countries.',
            ], 400);
        }

        $allProductsCountryIds = [];

        foreach ($cartItems as $cartItem) {
            if ($cartItem->product && $cartItem->product->shippingDetails) {
                $countryIds = $cartItem->product->shippingDetails->pluck('country_id')->unique()->toArray();
                $allProductsCountryIds[] = $countryIds;
            }
        }

        if (empty($allProductsCountryIds)) {
            return response()->json([
                'status' => false,
                'message' => 'No shipping details found for the products in your cart.',
            ], 200);
        }

        $commonCountryIds = call_user_func_array('array_intersect', $allProductsCountryIds);

        if (!empty($commonCountryIds)) {
            $environment_data = getEnvironmentData();
            $payment_options = $environment_data['PAYMENT_OPTIONS'];
            $cashPaymentEnabled = data_get($payment_options, 'value', 0);

            // Fetch the full shipping details for the common country IDs
            $commonShippingDetails = ShippingDetails::whereIn('country_id', $commonCountryIds)
                                        ->whereIn('product_sku', $cartItems->pluck('product_sku'))
                                        ->get();

            return response()->json([
                'status' => true,
                'message' => 'Common shipping countries and their details found for all products in your cart.',
                'common_shipping_details' => $commonShippingDetails,
                'payment_options' => $cashPaymentEnabled
            ], 200);
        }

        return response()->json([
            'status' => false,
            'message' => 'There are no common shipping countries for all products in your cart.',
        ], 200);
    }

    public function cartItemDelete(Request $request){
        $user = $request->user();

        $cartItem = $user->cartItems()->where('id', $request->cart_id)->first();
    
        $cartItem->delete();
    
        return response()->json([
            'status' => true,
            'message' => 'Cart item deleted successfully.',
        ]);
    }

    public function uploadProductReceipts(Request $request){
        $file = $request->file('product_receipts');
        $product_sku = $request->product_sku;
        $user_id = $request->user_id;
        $order_id = $request->order_id;

        $documents_name = time().'_'.rand().'.'.$file->getClientOriginalExtension();
        $file->move(public_path('uploads/product_receipts'), $documents_name);

        ProductReceipt::create([
            'product_sku' => $product_sku,
            'user_id' => $user_id,
            'order_id' => $order_id,
            'file_path' => 'uploads/product_receipts/' . $documents_name
        ]);

        $product = Product::select('user_id', 'price', 'commission_percentage')->where('sku', $request->product_sku)->first();

        $pending_balance = User::select('current_balance', 'pending_balance')->where('id', $product->user_id)->first();

        $commission_percentage = $product->commission_percentage;
        $price = $product->price;

        $wallet_amount = $price - (($price * $commission_percentage) / 100);

        $current_balance = $pending_balance->current_balance + $wallet_amount;

        $pending_balance_update = $pending_balance->pending_balance - $wallet_amount;

        User::where('id', $product->user_id)->update([
            'current_balance' => $current_balance,
            'pending_balance' => $pending_balance_update
        ]);

        return response()->json([
            'status' => true,
            'documents' => 'uploads/product_receipts/' . $documents_name
        ]);    
    }

    public function deleteProductReceipts(Request $request){
        $product_receipt = ProductReceipt::find($request->id);

        if ($product_receipt->file_path) {
            $filePath = public_path($product_receipt->file_path);
            if (file_exists($filePath)) {
                @unlink($filePath);
            }
        }

        $product_receipt->delete();

        return response()->json([
            'status' => true,
            'message' => 'Product receipt deleted successfully.'
        ]);   
    }

    public function myPurchaseList(Request $request){
        $user = $request->user();

        $payment_data = Orders::with('product:sku,name,name_ar,cover_image,cover_image_thumb_73X68,condition', 'shippingDetails:id,shipping_fee')->select('id', 'user_id', 'product_sku', 'shipping_id', 'price', 'created_at')->where('user_id', $user->id)->paginate(10);

        $payment_data->getCollection()->transform(function ($payment) use ($user){
            $payment->product->cover_image = $payment->product->cover_image_thumb_73X68 != '' ? $payment->product->cover_image_thumb_73X68 : $payment->product->cover_image;
            
            $hasReceipt = ProductReceipt::where('order_id', $payment->id)->first();
            $payment->product_receipts = $hasReceipt->file_path ?? null;

            $country_details = getCountrydetailsById($user->country_id);
            $payment->exchange_price = getExchangePrice($country_details->currency, $payment->price);
            if(isset($payment->shippingDetails) && !empty($payment->shippingDetails)){
                $payment->exchange_shipping_fee = getExchangePrice($country_details->currency, $payment->shippingDetails->shipping_fee);
            } else {
                $payment->exchange_shipping_fee = 0;
            }

            return $payment;
        });

        return response()->json([
            'status' => true,
            'message' => 'My purchase list fetched successfully.',
            'data' => $payment_data
        ], 200, [], JSON_UNESCAPED_UNICODE);    
    }

    public function waitingForShipmentUploadList(Request $request){
        $user = $request->user();

        $orders = Orders::with('product:sku,user_id,name,name_ar,cover_image,cover_image_thumb_73X68,condition', 'shippingDetails:id,shipping_fee', 'payment:id,order_id,payment_status')
        ->select('id', 'user_id', 'product_sku', 'shipping_id', 'price', 'created_at')
            ->whereHas('product', function ($q) use ($user) {
                $q->where('user_id', $user->id);   // product table no user_id
            })
            ->whereDoesntHave('productReceipt', function ($q) {
                $q->whereNotNull('file_path');
            })
            ->whereHas('payment', function ($q) {
                $q->whereIn('payment_status', [
                    'cash_payment',
                    'paid_on_delivery',
                    'capture'
                ]);
            })
            ->paginate(10);

        $orders->getCollection()->transform(function ($product) use ($request, $user){
            $userCountryId = $user->country_id;
            $product->product->cover_image = $product->product->cover_image_thumb_73X68 != '' ? $product->product->cover_image_thumb_73X68 : $product->product->cover_image;

            $country_details = getCountrydetailsById($userCountryId);
            $product->exchange_price = getExchangePrice($country_details->currency, $product->price);
            $product->exchange_shipping_fee = getExchangePrice($country_details->currency, $product->shippingDetails->shipping_fee);

            return $product;
        });

        return response()->json([
            'status' => true,
            'message' => 'Orders waiting for shipment upload fetched successfully.',
            'data' => $orders
        ], 200, [], JSON_UNESCAPED_UNICODE);
    }
}