<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Http\Requests\StoreProductRequest;
use App\Http\Requests\UpdateProductRequest;
use App\Models\Product;
use App\Models\User;
use App\Models\Category;
use App\Models\Bid;
use App\Models\ProductDocuments;
use App\Models\ShippingDetails;
use App\Models\Notification;
use App\Models\Orders;
use App\Models\ProductReceipt;
use Illuminate\Support\Facades\Gate;
use Illuminate\Http\Request;
use Symfony\Component\HttpFoundation\Response;
use Yajra\DataTables\Facades\DataTables;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\URL;
use DB;

class ProductController extends Controller
{
    public function getAuctionProduct(Request $request){
        abort_if(Gate::denies('product_access'), Response::HTTP_FORBIDDEN, '403 Forbidden');
        
        $now = Carbon::now('UTC');

        if ($request->ajax()) {
            $query = Product::with([
                'user:id,first_name,last_name', 
                'category:id,name'
            ])
            ->select('products.*')
            ->selectSub(function ($q) {
                $q->from('bids')
                    ->selectRaw('COUNT(*)')
                    ->whereColumn('bids.product_sku', 'products.sku');
            }, 'bid_count');

            // Filter by Product Type
            if ($request->has('product_type') && trim($request->product_type) !== '') {
                $query->where('product_type', (int)$request->product_type);
            }

            // Filter by user_id (Seller)
            if ($request->has('user_id') && trim($request->user_id) !== '') {
                $query->where('user_id', $request->user_id);
            }

            // Filter by Category
            if ($request->has('cat_id') && trim($request->cat_id) !== '') {
                $query->where('cat_id', $request->cat_id);
            }

            // Filter by status
            if ($request->has('status') && trim($request->status) !== '') {
                $query->where('status', $request->status);
            }

            // Filter by Condition
            if ($request->has('condition') && trim($request->condition) !== '') {
                $query->where('condition', $request->condition);
            }

            // Filter by IS Admin
            if ($request->has('is_admin') && trim($request->is_admin) !== '') {
                $query->where('is_admin', $request->is_admin);
            }

            // Filter by live_date range
            if ($request->has('live_date') && trim($request->live_date) !== '') {
                $dates = explode(' ', $request->live_date);
                if (count($dates) == 4) {
                    $start_datetime = $dates[0] . ' ' . $dates[1];
                    $end_datetime = $dates[2] . ' ' . $dates[3];

                    $query->where('live_date', '>=', $start_datetime)
                        ->where('live_date', '<=', $end_datetime);
                }
            }

            // Filter by finish_date range
            if ($request->has('finish_date') && trim($request->finish_date) !== '') {
                $dates = explode(' ', $request->finish_date);
                if (count($dates) == 4) {
                    $start_datetime = $dates[0] . ' ' . $dates[1];
                    $end_datetime = $dates[2] . ' ' . $dates[3];

                    $query->where('finish_date', '>=', $start_datetime)
                        ->where('finish_date', '<=', $end_datetime);
                }
            }

            if ($request->tab_status == 'Pending') {
                $query->where('live_date', '>', $now);
            } else if ($request->tab_status == 'Approved') {
                $query->where(function ($q) use ($now) {
                    $q->where('live_date', '<', $now)
                        ->where('finish_date', '>', $now);
                });
            } else if ($request->tab_status == 'Archived') {
                $query->where(function ($q) use ($now) {
                    $q->where('finish_date', '<', $now)
                        ->where('is_sell', '=', 0)
                        ->where('product_type', 1)
                        ->whereDoesntHave('bids'); // ✅ 0 bid products only;
                });

                // Add a subquery for bid count
                // $query->selectSub(function ($sub) {
                //     $sub->from('bids')
                //         ->selectRaw('COUNT(*)')
                //         ->whereColumn('bids.product_sku', 'products.sku');
                // }, 'bid_count');

                // Add a subquery to check if product has payment (exists in orders)
                // $query->selectSub(function ($sub) {
                //     $sub->from('orders')
                //         ->selectRaw('COUNT(*)')
                //         ->whereColumn('orders.product_sku', 'products.sku');
                // }, 'order_count');
            } else if($request->tab_status == 'WaitingForPayment'){
                $query->where('product_type', 1) // only auction type products
                    ->where('is_sell', 0) // not yet marked as sold
                    ->where('finish_date', '<', $now)
                    ->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->tab_status == 'SoldAuction') {
                $query->where(function ($q) {
                    $q->where('is_sell', '=', 1)
                        ->where('product_type', 1);
                });
            } 

            $prodeuct_data = $query->get();
            
            $table = Datatables::of($query);

            $table->addColumn('placeholder', '&nbsp;');
            $table->addColumn('actions', '&nbsp;');

            $table->editColumn('actions', function ($row) {
                $viewGate      = 'product_show';
                $editGate      = $row->is_sell != 1 ? 'product_edit' : '';
                $deleteGate    = 'product_delete';
                $crudRoutePart = 'products';

                return view('partials.datatablesActions', compact(
                    'viewGate',
                    'editGate',
                    'deleteGate',
                    'crudRoutePart',
                    'row',
                ));
            });

            $table->editColumn('id', function ($row) {
                return $row->id ? $row->id : '';
            });

            $table->editColumn('cat_id', function ($row) {
                return $row->category ? $row->category->name : '';
            });

            $table->editColumn('name', function ($row) {
                return $row->name ? $row->name : '';
            });

            $table->addColumn('seller_name', function ($row) {
                return $row->user ? ucwords($row->user->first_name . ' ' . $row->user->last_name) : 'N/A';
            });

            $table->editColumn('sku', function ($row) {
                return $row->sku ? $row->sku : '';
            });

            $table->editColumn('price', function ($row) {
                return $row->price ? 'BHD ' .number_format($row->price, 2) : '';
            });

            $table->editColumn('commission_percentage', function ($row) {
                return $row->commission_percentage ? $row->commission_percentage.' %' : '';
            });

            $table->editColumn('win_price', function ($row) {
                return $row->highest_bid_amount ? 'BHD ' .number_format($row->highest_bid_amount, 2) : '';
            });

            $table->editColumn('admin_commission', function ($row) {
                $admin_commission = ($row->price * $row->commission_percentage) / 100;
                
                return 'BHD ' .number_format($admin_commission, 2);
            });

            $table->editColumn('seller_receiver', function ($row) {
                $seller_commission = 100 - $row->commission_percentage;
                $seller_receiver = ($row->price * $seller_commission) / 100;

                return 'BHD ' .number_format($seller_receiver, 2);
            });
            
            $table->editColumn('condition', function ($row) {
                return $row->condition == 1 ? 'New' : ($row->condition == 2 ? 'Used' : '-');
            });

            $table->addColumn('bid_count', function ($row) {
                return $row->bid_count ?? 0;
            });

            $table->editColumn('live_date', function ($row) {
                return isset($row->live_date) ? toUserTimezone($row->live_date) : '-';
            });

            $table->editColumn('finish_date', function ($row) {
                return isset($row->finish_date) ? toUserTimezone($row->finish_date) : '-';
            });

            $table->editColumn('status', function ($row) use ($request) {
                // Special handling for Archived tab
                if ($request->tab_status == 'Archived') {
                    $bid_count = $row->bid_count ?? 0;
                    $order_count = $row->order_count ?? 0;

                    // Case 1: No Bid
                    if ($bid_count == 0) {
                        return '<span class="label text-warning">No Bid</span>';
                    }

                    // Case 2: Bid exists but No Payment
                    if ($bid_count > 0 && $order_count == 0) {
                        return '<span class="label text-danger">No Payment</span>';
                    }

                    // Case 3: Product Sold (payment done)
                    if ($order_count > 0) {
                        return '<span class="label text-success">Sold</span>';
                    }

                    return '<span class="label text-muted">Archived</span>';
                }

                if ($row->is_sell == 1) {
                    return '<span class="label text-danger">Sold</span>';
                }

                $statusMap = [
                    'Draft' => ['label' => 'Draft', 'class' => 'text-default'],
                    'Pending' => ['label' => 'Pending', 'class' => 'text-info'],
                    'Approved' => ['label' => 'Approved', 'class' => 'text-success'],
                    'Rejected' => ['label' => 'Rejected', 'class' => 'text-danger'],
                    'Save' => ['label' => 'Save', 'class' => 'text-warning'],
                ];

                $status = $statusMap[$row->status] ?? ['label' => '-', 'class' => 'text-muted'];

                return sprintf('<span class="label label-info label-many %s">%s</span>', $status['class'], $status['label']);
            });

            $table->editColumn('is_admin', function ($row) {
                return $row->is_admin == 1 ? 'Yes' : 'No';
            });

            $table->rawColumns(['actions', 'placeholder', 'bid_count', 'status']);

            return $table->make(true);
        }

        $seller_users = User::whereHas('roles', function ($query) {
            $query->where('title', 'Seller');
        })->select('id', 'first_name', 'last_name')
            ->where(['kyc_status' => 2, 'seller_status' => 2])
            ->get();

        $category = Category::select('*')->where('status', 1)->orderBy('id', 'desc')->get();

        $product_upcoming_count = Product::where('live_date', '>', $now)
            ->where('product_type', 1)
            ->count();

        $product_live_count = Product::where('live_date', '<', $now)
            ->where('finish_date', '>', $now)
            ->where('product_type', 1)
            ->count();

        $product_archived_count = Product::where('product_type', 1)
            ->where('is_sell', '=', 0)
            ->where('finish_date', '<=', $now)
            ->whereDoesntHave('bids') // ✅ 0 bid products only;
            ->count();

        $product_sold_auction_count = Product::where('product_type', 1)->where('status', 'Approved')->where('is_sell', 1)->count();

        $waiting_for_payment = Product::where('product_type', 1) // only auction type products
            ->where('is_sell', 0) // not yet marked as sold
            ->where('finish_date', '<', $now)
            ->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
                    });
            })->count();

        return view('admin.products.auction_data_listing', compact('seller_users', 'category', 'product_upcoming_count', 'product_live_count', 'product_archived_count', 'product_sold_auction_count', 'waiting_for_payment'));
    }

    public function getSellProduct(Request $request){
        abort_if(Gate::denies('product_access'), Response::HTTP_FORBIDDEN, '403 Forbidden');
        
        if ($request->ajax()) {
            if ($request->tab_status == 'SoldSell') {
                $query = Orders::with([
                    'user:id,first_name,last_name'
                ])->select('orders.id','orders.user_id','orders.product_sku','orders.shipping_id','orders.price','orders.quantity','orders.payment_id','products.id as product_id',
                'products.name as product_name',
                'products.cat_id',
                'products.sku',
                'products.commission_percentage',
                'products.condition',
                'products.is_admin',
                'products.user_id as seller_id',
                'seller.first_name as seller_first_name',
                'seller.last_name as seller_last_name',
                'category.name as category_name')
                ->leftJoin('products', 'orders.product_sku', '=', 'products.sku')
                ->leftJoin('users as seller', 'products.user_id', '=', 'seller.id')
                ->leftJoin('category', 'products.cat_id', '=', 'category.id')
                ->where('products.product_type', 2 ); // only sell type products

                $table = Datatables::of($query);

                $table->addColumn('placeholder', '&nbsp;');
                $table->addColumn('actions', '&nbsp;');

                $table->editColumn('id', function ($row) {
                    return $row->id;
                });
                
                $table->addColumn('cat_id', fn($row) => $row->category_name ?: 'N/A');

                $table->addColumn('user_name', function ($row) {
                    return $row->user ? ucwords($row->user->first_name.' '.$row->user->last_name) : '-';
                });

                $table->editColumn('sku', function ($row) {
                    return $row->product_sku;
                });
                
                $table->editColumn('product_name', function ($row) {
                    return $row->product ? $row->product->name : '-';
                });
                
                $table->addColumn('seller_name', fn($row) =>
                    $row->seller_first_name || $row->seller_last_name
                    ? ucwords($row->seller_first_name . ' ' . $row->seller_last_name)
                    : '-'
                );

                $table->editColumn('shipping_id', function ($row) {
                    return $row->shipping_id;
                });

                $table->editColumn('price', function ($row) {
                    return $row->price ? 'BHD '.number_format($row->price,2) : '';
                });

                $table->editColumn('product_quantity', function ($row) {
                    return $row->quantity;
                });

                $table->editColumn('commission_percentage', fn($row) =>
                    $row->commission_percentage ? $row->commission_percentage . ' %' : ''
                );

                $table->editColumn('admin_commission', function ($row) {
                    $admin_commission = ($row->price * $row->commission_percentage) / 100;
                    
                    return 'BHD ' .number_format($admin_commission, 2);
                });

                $table->editColumn('seller_receiver', function ($row) {
                    $seller_commission = 100 - $row->commission_percentage;
                    $seller_receiver = ($row->price * $seller_commission) / 100;

                    return 'BHD ' .number_format($seller_receiver, 2);
                });

                $table->editColumn('condition', function ($row) {
                    if ($row->condition == 1) return 'New';
                    if ($row->condition == 2) return 'Used';
                    return '-';
                });

                $table->editColumn('status', fn() =>
                    '<span class="label text-danger">Sold</span>'
                );

                $table->editColumn('is_admin', fn($row) =>
                    $row->is_admin == 1 ? 'Yes' : 'No'
                );
                
                $table->editColumn('actions', function ($row) {
                    $row->id = $row->payment_id;
                    
                    $viewGate      = 'transaction_show';
                    $editGate      = '';
                    $deleteGate    = '';
                    $crudRoutePart = 'transaction';

                    return view('partials.datatablesActions', compact(
                        'viewGate',
                        'editGate',
                        'deleteGate',
                        'crudRoutePart',
                        'row',
                    ));
                });
                
                $table->rawColumns(['actions', 'placeholder', 'status']);

                return $table->make(true);
            } else {
                $query = Product::with([
                    'user:id,first_name,last_name', 
                    'category:id,name'
                ])
                ->select('products.*');

                // Filter by Product Type
                if ($request->has('product_type') && trim($request->product_type) !== '') {
                    $query->where('product_type', (int)$request->product_type);
                }

                // Filter by user_id (Seller)
                if ($request->has('user_id') && trim($request->user_id) !== '') {
                    $query->where('user_id', $request->user_id);
                }

                // Filter by Category
                if ($request->has('cat_id') && trim($request->cat_id) !== '') {
                    $query->where('cat_id', $request->cat_id);
                }

                // Filter by status
                if ($request->has('status') && trim($request->status) !== '') {
                    $query->where('status', $request->status);
                }

                // Filter by Condition
                if ($request->has('condition') && trim($request->condition) !== '') {
                    $query->where('condition', $request->condition);
                }

                // Filter by IS Admin
                if ($request->has('is_admin') && trim($request->is_admin) !== '') {
                    $query->where('is_admin', $request->is_admin);
                }
 
                if ($request->tab_status == 'Sell') {
                    $query->where(function ($q) {
                        $q->where('is_sell', '=', 0)
                            ->where('product_type', 2);
                    });
                }

                $prodeuct_data = $query->get();
                
                $table = Datatables::of($query);

                $table->addColumn('placeholder', '&nbsp;');
                $table->addColumn('actions', '&nbsp;');

                $table->editColumn('actions', function ($row) {
                    $viewGate      = 'product_show';
                    $editGate      = $row->is_sell != 1 ? 'product_edit' : '';
                    $deleteGate    = 'product_delete';
                    $crudRoutePart = 'products';

                    return view('partials.datatablesActions', compact(
                        'viewGate',
                        'editGate',
                        'deleteGate',
                        'crudRoutePart',
                        'row',
                    ));
                });

                $table->editColumn('id', function ($row) {
                    return $row->id ? $row->id : '';
                });

                $table->editColumn('cat_id', function ($row) {
                    return $row->category ? $row->category->name : '';
                });

                $table->editColumn('product_name', function ($row) {
                    return $row->name ? $row->name : '';
                });

                $table->addColumn('seller_name', function ($row) {
                    return $row->user ? ucwords($row->user->first_name . ' ' . $row->user->last_name) : 'N/A';
                });

                $table->editColumn('sku', function ($row) {
                    return $row->sku ? $row->sku : '';
                });

                $table->editColumn('price', function ($row) {
                    return $row->price ? 'BHD ' .number_format($row->price, 2) : '';
                });

                $table->editColumn('commission_percentage', function ($row) {
                    return $row->commission_percentage ? $row->commission_percentage.' %' : '';
                });

                $table->editColumn('admin_commission', function ($row) {
                    $admin_commission = ($row->price * $row->commission_percentage) / 100;
                    
                    return 'BHD ' .number_format($admin_commission, 2);
                });

                $table->editColumn('seller_receiver', function ($row) {
                    $seller_commission = 100 - $row->commission_percentage;
                    $seller_receiver = ($row->price * $seller_commission) / 100;

                    return 'BHD ' .number_format($seller_receiver, 2);
                });

                $table->editColumn('condition', function ($row) {
                    return $row->condition == 1 ? 'New' : ($row->condition == 2 ? 'Used' : '-');
                });

                $table->editColumn('status', function ($row) use ($request) {
                    if ($row->is_sell == 1) {
                        return '<span class="label text-danger">Sold</span>';
                    }

                    $statusMap = [
                        'Draft' => ['label' => 'Draft', 'class' => 'text-default'],
                        'Pending' => ['label' => 'Pending', 'class' => 'text-info'],
                        'Approved' => ['label' => 'Approved', 'class' => 'text-success'],
                        'Rejected' => ['label' => 'Rejected', 'class' => 'text-danger'],
                        'Save' => ['label' => 'Save', 'class' => 'text-warning'],
                    ];

                    $status = $statusMap[$row->status] ?? ['label' => '-', 'class' => 'text-muted'];

                    return sprintf('<span class="label label-info label-many %s">%s</span>', $status['class'], $status['label']);
                });

                $table->editColumn('is_admin', function ($row) {
                    return $row->is_admin == 1 ? 'Yes' : 'No';
                });

                $table->rawColumns(['actions', 'placeholder', 'status']);

                return $table->make(true);
            }
        }

        $seller_users = User::whereHas('roles', function ($query) {
            $query->where('title', 'Seller');
        })->select('id', 'first_name', 'last_name')
            ->where(['kyc_status' => 2, 'seller_status' => 2])
            ->get();

        $category = Category::select('*')->where('status', 1)->orderBy('id', 'desc')->get();

        $product_sell_count = Product::where('is_sell', '=', 0)->where('product_type', 2)->count();

        $product_sold_sell_count = Orders::select('orders.product_sku')
            ->leftJoin('products', 'orders.product_sku', '=', 'products.sku')
            ->where('products.product_type', 2 ) // only sell type products
            ->count();

        return view('admin.products.sell_data_listing', compact('seller_users', 'category', 'product_sell_count', 'product_sold_sell_count'));
    }

    public function index(Request $request)
    {
        abort_if(Gate::denies('product_access'), Response::HTTP_FORBIDDEN, '403 Forbidden');
        
        if ($request->ajax()) {
            if ($request->tab_status == 'SoldSell') {
                $query = Orders::with([
                    'user:id,first_name,last_name'
                ])->select('orders.id','orders.user_id','orders.product_sku','orders.shipping_id','orders.price','orders.quantity','products.id as product_id',
                'products.name as product_name',
                'products.cat_id',
                'products.sku',
                'products.commission_percentage',
                'products.condition',
                'products.is_admin',
                'products.user_id as seller_id',
                'seller.first_name as seller_first_name',
                'seller.last_name as seller_last_name',
                'category.name as category_name')
                ->leftJoin('products', 'orders.product_sku', '=', 'products.sku')
                ->leftJoin('users as seller', 'products.user_id', '=', 'seller.id')
                ->leftJoin('category', 'products.cat_id', '=', 'category.id');

                $table = Datatables::of($query);

                $table->addColumn('placeholder', '&nbsp;');
                $table->addColumn('actions', '&nbsp;');

                $table->editColumn('id', function ($row) {
                    return $row->id;
                });
                
                $table->addColumn('cat_id', fn($row) => $row->category_name ?: 'N/A');

                $table->addColumn('user_name', function ($row) {
                    return $row->user ? $row->user->first_name.' '.$row->user->last_name : '-';
                });

                $table->editColumn('sku', function ($row) {
                    return $row->product_sku;
                });
                
                $table->editColumn('name', function ($row) {
                    return $row->product ? $row->product->name : '-';
                });
                
                $table->addColumn('seller_name', fn($row) =>
                    $row->seller_first_name || $row->seller_last_name
                    ? $row->seller_first_name . ' ' . $row->seller_last_name
                    : '-'
                );

                $table->editColumn('shipping_id', function ($row) {
                    return $row->shipping_id;
                });

                $table->editColumn('price', function ($row) {
                    return $row->price ? 'BHD '.number_format($row->price,2) : '';
                });

                $table->editColumn('product_quantity', function ($row) {
                    return $row->quantity;
                });

                $table->editColumn('commission_percentage', fn($row) =>
                    $row->commission_percentage ? $row->commission_percentage . ' %' : ''
                );

                $table->editColumn('condition', function ($row) {
                    if ($row->condition == 1) return 'New';
                    if ($row->condition == 2) return 'Used';
                    return '-';
                });

                $table->addColumn('bid_count', fn() => 0);

                $table->editColumn('live_date', fn() => '-');
                $table->editColumn('finish_date', fn() => '-');

                $table->editColumn('status', fn() =>
                    '<span class="label text-danger">Sold</span>'
                );

                $table->editColumn('is_admin', fn($row) =>
                    $row->is_admin == 1 ? 'Yes' : 'No'
                );
                
                $table->editColumn('actions', function ($row) {
                    $viewGate      = 'transaction_show';
                    $editGate      = '';
                    $deleteGate    = '';
                    $crudRoutePart = 'transaction';

                    return view('partials.datatablesActions', compact(
                        'viewGate',
                        'editGate',
                        'deleteGate',
                        'crudRoutePart',
                        'row',
                    ));
                });
                
                $table->rawColumns(['actions', 'placeholder', 'bid_count', 'status']);

                return $table->make(true);
            } else {
                // \DB::enableQueryLog();
                $query = Product::with([
                    'user:id,first_name,last_name', 
                    'category:id,name'
                ])
                ->select('products.*')
                ->selectSub(function ($q) {
                    $q->from('bids')
                        ->selectRaw('COUNT(*)')
                        ->whereColumn('bids.product_sku', 'products.sku');
                }, 'bid_count');

                // Filter by Product Type
                if ($request->has('product_type') && trim($request->product_type) !== '') {
                    $query->where('product_type', (int)$request->product_type);
                }

                // Filter by user_id (Seller)
                if ($request->has('user_id') && trim($request->user_id) !== '') {
                    $query->where('user_id', $request->user_id);
                }

                // Filter by Category
                if ($request->has('cat_id') && trim($request->cat_id) !== '') {
                    $query->where('cat_id', $request->cat_id);
                }

                // Filter by status
                if ($request->has('status') && trim($request->status) !== '') {
                    $query->where('status', $request->status);
                }

                // Filter by Condition
                if ($request->has('condition') && trim($request->condition) !== '') {
                    $query->where('condition', $request->condition);
                }

                // Filter by IS Admin
                if ($request->has('is_admin') && trim($request->is_admin) !== '') {
                    $query->where('is_admin', $request->is_admin);
                }

                // Filter by live_date range
                if ($request->has('live_date') && trim($request->live_date) !== '') {
                    $dates = explode(' ', $request->live_date);
                    if (count($dates) == 4) {
                        $start_datetime = $dates[0] . ' ' . $dates[1];
                        $end_datetime = $dates[2] . ' ' . $dates[3];

                        $query->where('live_date', '>=', $start_datetime)
                            ->where('live_date', '<=', $end_datetime);
                    }
                }

                // Filter by finish_date range
                if ($request->has('finish_date') && trim($request->finish_date) !== '') {
                    $dates = explode(' ', $request->finish_date);
                    if (count($dates) == 4) {
                        $start_datetime = $dates[0] . ' ' . $dates[1];
                        $end_datetime = $dates[2] . ' ' . $dates[3];

                        $query->where('finish_date', '>=', $start_datetime)
                            ->where('finish_date', '<=', $end_datetime);
                    }
                }

                $now = Carbon::now($request->currant_timezone);
                if ($request->tab_status == 'Pending') {
                    $query->where('live_date', '>', $now);
                } else if ($request->tab_status == 'Approved') {
                    $query->where(function ($q) use ($now) {
                        $q->where('live_date', '<', $now)
                            ->where('finish_date', '>', $now);
                    });
                } else if ($request->tab_status == 'Archived') {
                    $query->where(function ($q) use ($now) {
                        $q->where('finish_date', '<', $now)
                            ->where('is_sell', '=', 0)
                            ->where('product_type', 1);
                    });

                    // Add a subquery for bid count
                    $query->selectSub(function ($sub) {
                        $sub->from('bids')
                            ->selectRaw('COUNT(*)')
                            ->whereColumn('bids.product_sku', 'products.sku');
                    }, 'bid_count');

                    // Add a subquery to check if product has payment (exists in orders)
                    $query->selectSub(function ($sub) {
                        $sub->from('orders')
                            ->selectRaw('COUNT(*)')
                            ->whereColumn('orders.product_sku', 'products.sku');
                    }, 'order_count');
                } else if($request->tab_status == 'WaitingForPayment'){
                    $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->tab_status == 'SoldAuction') {
                    $query->where(function ($q) {
                        $q->where('is_sell', '=', 1)
                            ->where('product_type', 1);
                    });
                } 
                // else if ($request->tab_status == 'SoldSell') {
                    // $query->where(function ($q) {
                    // $q->where('is_sell', '=', 1)
                    //       ->where('product_type', 2);
                // });
                // }  
                else if ($request->tab_status == 'Sell') {
                    $query->where(function ($q) {
                        $q->where('is_sell', '=', 0)
                            ->where('product_type', 2);
                    });
                }

                $prodeuct_data = $query->get();

                // dd(\DB::getQueryLog());
                
                $table = Datatables::of($query);

                $table->addColumn('placeholder', '&nbsp;');
                $table->addColumn('actions', '&nbsp;');

                $table->editColumn('actions', function ($row) {
                    $viewGate      = 'product_show';
                    $editGate      = $row->is_sell != 1 ? 'product_edit' : '';
                    $deleteGate    = 'product_delete';
                    $crudRoutePart = 'products';

                    return view('partials.datatablesActions', compact(
                        'viewGate',
                        'editGate',
                        'deleteGate',
                        'crudRoutePart',
                        'row',
                    ));
                });

                $table->editColumn('id', function ($row) {
                    return $row->id ? $row->id : '';
                });

                $table->editColumn('cat_id', function ($row) {
                    return $row->category ? $row->category->name : '';
                });

                $table->editColumn('name', function ($row) {
                    return $row->name ? $row->name : '';
                });

                $table->addColumn('seller_name', function ($row) {
                    return $row->user ? $row->user->first_name . ' ' . $row->user->last_name : 'N/A';
                });

                $table->editColumn('sku', function ($row) {
                    return $row->sku ? $row->sku : '';
                });

                $table->editColumn('price', function ($row) {
                    return $row->price ? 'BHD ' .number_format($row->price, 2) : '';
                });

                $table->editColumn('commission_percentage', function ($row) {
                    return $row->commission_percentage ? $row->commission_percentage.' %' : '';
                });

                $table->editColumn('condition', function ($row) {
                    return $row->condition == 1 ? 'New' : ($row->condition == 2 ? 'Used' : '-');
                });

                $table->addColumn('bid_count', function ($row) {
                    return $row->bid_count ?? 0;
                });

                // $table->editColumn('product_type', function ($row) {
                //     return $row->product_type == 1 ? 'For Auction' : 'For Sell';
                // });

                $table->editColumn('live_date', function ($row) {
                    return isset($row->live_date) ? date('d-m-Y H:i', strtotime($row->live_date)) : '-';
                });

                $table->editColumn('finish_date', function ($row) {
                    return isset($row->finish_date) ? date('d-m-Y H:i', strtotime($row->finish_date)) : '-';
                });

                $table->editColumn('status', function ($row) use ($request) {
                    // Special handling for Archived tab
                    if ($request->tab_status == 'Archived') {
                        $bid_count = $row->bid_count ?? 0;
                        $order_count = $row->order_count ?? 0;

                        // Case 1: No Bid
                        if ($bid_count == 0) {
                            return '<span class="label text-warning">No Bid</span>';
                        }

                        // Case 2: Bid exists but No Payment
                        if ($bid_count > 0 && $order_count == 0) {
                            return '<span class="label text-danger">No Payment</span>';
                        }

                        // Case 3: Product Sold (payment done)
                        if ($order_count > 0) {
                            return '<span class="label text-success">Sold</span>';
                        }

                        return '<span class="label text-muted">Archived</span>';
                    }

                    if ($row->is_sell == 1) {
                        return '<span class="label text-danger">Sold</span>';
                    }

                    $statusMap = [
                        'Draft' => ['label' => 'Draft', 'class' => 'text-default'],
                        'Pending' => ['label' => 'Pending', 'class' => 'text-info'],
                        'Approved' => ['label' => 'Approved', 'class' => 'text-success'],
                        'Rejected' => ['label' => 'Rejected', 'class' => 'text-danger'],
                        'Save' => ['label' => 'Save', 'class' => 'text-warning'],
                    ];

                    $status = $statusMap[$row->status] ?? ['label' => '-', 'class' => 'text-muted'];

                    return sprintf('<span class="label label-info label-many %s">%s</span>', $status['class'], $status['label']);
                });

                $table->editColumn('is_admin', function ($row) {
                    return $row->is_admin == 1 ? 'Yes' : 'No';
                });

                $table->rawColumns(['actions', 'placeholder', 'bid_count', 'status']);

                return $table->make(true);
            }
        }

        $seller_users = User::whereHas('roles', function ($query) {
            $query->where('title', 'Seller');
        })->select('id', 'first_name', 'last_name')
            ->where(['kyc_status' => 2, 'seller_status' => 2])
            ->get();

        $category = Category::select('*')->where('status', 1)->orderBy('id', 'desc')->get();

        $timezone = session('user_timezone');
        if(isset($timezone)){
            $now = Carbon::now($timezone);
        } else {
            $now = Carbon::now(config('app.timezone'));
        }

        $product_upcoming_count = Product::where('live_date', '>', $now)
            ->where('product_type', 1)
            // ->whereIn('status', ['Draft', 'Pending'])
            ->count();
        $product_live_count = Product::where('live_date', '<', $now)
            ->where('finish_date', '>', $now)
            ->where('product_type', 1)
            // ->whereIn('status', ['Draft', 'Pending'])
            ->count();
        $product_archived_count = Product::where('product_type', 1)
            ->where('is_sell', '=', 0)
            ->where('finish_date', '<=', $now)
            //->whereIn('status', ['Draft', 'Pending'])
            ->count();
        $product_sold_auction_count = Product::where('product_type', 1)->where('status', 'Approved')->where('is_sell', 1)->count();
        $product_sell_count = Product::where('is_sell', '=', 0)->where('product_type', 2)->count();
        $product_sold_sell_count = Orders::count();
        $waiting_for_payment = Product::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
                    });
            })->count();
       //  $product_sold_sell_count = Product::where('product_type', 2)->where('status', 'Approved')->where('is_sell', 1)->count();

        return view('admin.products.index', compact('seller_users', 'category', 'product_upcoming_count', 'product_live_count', 'product_sell_count', 'product_archived_count', 'product_sold_auction_count', 'product_sold_sell_count', 'waiting_for_payment'));
    }
    
    public function create()
    {
        abort_if(Gate::denies('product_create'), Response::HTTP_FORBIDDEN, '403 Forbidden');

        $users = User::whereHas('roles', function ($query) {
            $query->where('title', 'Seller');
        })->select('id', 'first_name', 'last_name')
            ->where(['kyc_status' => 2, 'seller_status' => 2])
            ->get();

        $category = Category::select('*')->where('status', 1)->orderBy('id', 'desc')->get();
        $country_details = getCountrydetailsById();

        return view('admin.products.create', compact('users', 'category', 'country_details'));
    }

    public function store(StoreProductRequest $request)
    {
        $product_data = $request->all();

        if ($request->hasFile('cover_image')) {
            $imageName = time().'_'.rand().'.'.$request->file('cover_image')->getClientOriginalExtension();
            $request->file('cover_image')->move(public_path('uploads/product_cover_image'), $imageName);
            $product_data['cover_image']= 'uploads/product_cover_image/' . $imageName;
        }
    
        $product = Product::create($product_data);

        if ($request->hasFile('images')) {
            foreach ($request->file('images') as $image) {
                $product_image_name = time().'_'.rand().'.'.$image->getClientOriginalExtension();
                $image->move(public_path('uploads/product_image'), $product_image_name);
                $product->images()->create(['image' => 'uploads/product_image/' . $product_image_name]);
            }
        }

        if ($request->has('country')) {
            foreach ($request->country as $key => $vlu) {
                ShippingDetails::create([
                    'product_sku' => $product->sku,
                    'country_id' => $request->country[$key],
                    'duration_day' => $request->day[$key],
                    'shipping_fee' => $request->fee[$key],
                ]);
            }
        }

        if ($request->hasFile('documents')) {
            foreach ($request->file('documents') as $key => $document) {
                $product_document_name = time().'_'.rand().'.'.$document->getClientOriginalExtension();
                $document->move(public_path('uploads/product_documents'), $product_document_name);
                $product->documents()->create([
                    'name' => $request->document_name[$key],
                    'file_name' => 'uploads/product_documents/' . $product_document_name
                ]);
            }
        }

        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' => $request->user_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' => $request->user_id,
                'title' => "Product Status Change",
                'message' => $message,
            ]);
        }

        if($request->status == 'Approved'){
            $message = "$request->name is approved. You can now proceed to list it in an auction.";
            
            Notification::create([
                'user_id' => $request->user_id,
                'title' => "Product Status Change",
                'message' => $message
            ]);
        }
		
        if($request->status == 'Rejected'){
            $message = "Your product $request->name has been rejected. Please review the feedback and make the necessary changes.";

            Notification::create([
                'user_id' => $request->user_id,
                'title' => "Product Status Change",
                'message' => $message,
            ]);
        }
        
        pushNotification($request->user_id, $message);
        
        return redirect()->route('admin.products.index')->with('success', trans('global.product_store'));
    }

    public function edit(Product $product)
    {
        abort_if(Gate::denies('product_edit'), Response::HTTP_FORBIDDEN, '403 Forbidden');

        $product->load('shippingDetails');

        $users = User::whereHas('roles', function ($query) {
            $query->where('title', 'Seller');
        })->select('id', 'first_name', 'last_name')
            ->where(['kyc_status' => 2, 'seller_status' => 2])
            ->get();

        $category = Category::select('*')->where('status', 1)->orderBy('id', 'desc')->get();
        $country_details = getCountrydetailsById();

        return view('admin.products.edit', compact('product', 'users', 'category', 'country_details'));
    }

    public function update(UpdateProductRequest $request, Product $product)
    {
        $product_data = $request->all();

        if ($request->hasFile('cover_image')) {
            if ($product->cover_image) {
                $oldImagePath = public_path($product->cover_image);
                if (file_exists($oldImagePath)) {
                    unlink($oldImagePath);
                }
            }

            $imageName = time().'_'.rand().'.'.$request->file('cover_image')->getClientOriginalExtension();
            $request->file('cover_image')->move(public_path('uploads/product_cover_image'), $imageName);
            $product_data['cover_image'] = 'uploads/product_cover_image/' . $imageName;
        }
    
        $product->update($product_data);
    
        if ($request->hasFile('images')) {
            foreach ($product->images as $old_image) {
                $ProductOldImagePath = public_path($old_image->image);
                if (file_exists($ProductOldImagePath)) {
                    unlink($ProductOldImagePath);
                }
            }

            foreach ($request->file('images') as $image) {
                $product_image_name = time().'_'.rand() . '.' . $image->getClientOriginalExtension();
                $image->move(public_path('uploads/product_image'), $product_image_name);
                $product->images()->create([
                    'product_id' => $product->id, 
                    'image' => 'uploads/product_image/' . $product_image_name
                ]);
            }
        }

        if ($request->has('existing_document_ids')) {
            foreach ($request->existing_document_ids as $docId) {
                $document = $product->documents()->find($docId);
                if ($document) {
                    // Update name if changed
                    if (isset($request->existing_document_name[$docId])) {
                        $document->name = $request->existing_document_name[$docId];
                        $document->save();
                    }
                }
            }
        }
        
        // Add new uploaded documents
        if ($request->hasFile('documents')) {
            foreach ($request->file('documents') as $key => $file) {
                if ($file) {
                    $filename = time().'_'.rand() . '_' . $file->getClientOriginalName();
                    $file->move(public_path('uploads/product_documents'), $filename);
        
                    $product->documents()->create([
                        'name' => $request->document_name[$key],
                        'file_name' => 'uploads/product_documents/' . $filename
                    ]);
                }
            }
        }

        // if ($request->has('country')) {
        //     ShippingDetails::where('product_sku', $product->sku)->delete();

        //     foreach ($request->country as $key => $vlu) {
        //         ShippingDetails::create([
        //             'product_sku' => $product->sku,
        //             'country_id' => $request->country[$key],
        //             'duration_day' => $request->day[$key],
        //             'shipping_fee' => $request->fee[$key],
        //         ]);
        //     }
        // }

        if ($request->filled('country')) {
            foreach ($request->country as $key => $vlu) {
                ShippingDetails::updateOrCreate(
                    [
                        'product_sku' => $product->sku,
                        'country_id'  => $vlu,
                    ],
                    [
                        'duration_day' => $request->day[$key],
                        'shipping_fee' => $request->fee[$key],
                    ]
                );
            }
        }
		
	    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' => $request->user_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' => $request->user_id,
                'title' => "Product Status Change",
                'message' => $message,
            ]);
        }

        if($request->status == 'Approved'){
            $message = "$request->name is approved. You can now proceed to list it in an auction.";
            
            Notification::create([
                'user_id' => $request->user_id,
                'title' => "Product Status Change",
                'message' => $message,
            ]);
        }
		
	    if($request->status == 'Rejected'){
	        $message = "Your product $request->name has been rejected. Please review the feedback and make the necessary changes.";
		
            Notification::create([
                'user_id' => $request->user_id,
                'title' => "Product Status Change",
                'message' => $message,
            ]);
        }
        
        if($request->status == 'Save'){
	        $message = "Your product $request->name is save approval. We will notify you once it has been reviewed.";
		
            Notification::create([
                'user_id' => $request->user_id,
                'title' => "Product Status Change",
                'message' => $message,
            ]);
        }
        
        pushNotification($request->user_id, $message);

        if($request->product_type == 1){
            return redirect()->route('admin.products.auction_product')->with('success', trans('global.product_update'));
        } else {
            return redirect()->route('admin.products.sell_product')->with('success', trans('global.product_update'));
        }
    }

    public function documentsDestroy($id)
    {
        $doc = ProductDocuments::findOrFail($id);

        // Delete file from storage
        if (file_exists(public_path($doc->file_name))) {
            unlink(public_path($doc->file_name));
        }

        $doc->delete();

        return response()->json(['success' => true]);
    }

    public function show(Product $product, Request $request)
    {
        abort_if(Gate::denies('product_show'), Response::HTTP_FORBIDDEN, '403 Forbidden');
        
        $product->load('user');

        $order_id = $request->query('order_id') ?? null;

        $buyer_details = Orders::with(['user:id,first_name,last_name,phone,country_id,address,new_address', 'shippingDetails'])
            ->where(['id' => $order_id, 'product_sku' => $product->sku])->first();

        $product_receipt = ProductReceipt::select('id', 'file_path')->where(['order_id' => $order_id, 'product_sku' => $product->sku])->first();
        
        return view('admin.products.show', compact('product', 'buyer_details', 'product_receipt'));
    }

    public function destroy(Product $product)
    {
        abort_if(Gate::denies('product_delete'), Response::HTTP_FORBIDDEN, '403 Forbidden');

        if ($product->cover_image) {
            $oldImagePath = public_path($product->cover_image);
            if (file_exists($oldImagePath)) {
                unlink($oldImagePath);
            }
        }

        foreach ($product->images as $old_image) {
            $ProductOldImagePath = public_path($old_image->image);
            if (file_exists($ProductOldImagePath)) {
                unlink($ProductOldImagePath);
            }
        }

        foreach ($product->documents as $document) {
            $ProductOldDocumentsPath = public_path($document->file_name);
            if (file_exists($ProductOldDocumentsPath)) {
                unlink($ProductOldDocumentsPath);
            }
        }

        ShippingDetails::where('product_sku', $product->sku)->delete();

        $product->delete();

        return back()->with('success', trans('global.product_delete'));
    }

    public function getSellerCommission($id)
    {
        $user = User::find($id);

        if (!$user) {
            return response()->json(['seller_commission_percentage' => null], 404);
        }

        return response()->json([
            'seller_commission_percentage' => $user->seller_commission_percentage,
        ]);
    }

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

            $product = Product::select('is_sell', 'finish_date')->where('sku', $productSKU)->first();
            $isSell = $product->is_sell;
            $finish_date = $product->finish_date;

            return DataTables::of($bids)
                ->addColumn('user_name', function ($row) {
                    return $row->user ? ucwords($row->user->first_name . ' ' . $row->user->last_name) : 'N/A';
                })
                ->addColumn('amount', function ($row) use ($productSKU, $isSell, $finish_date) {
                    $amountHtml = '<div class="d-flex align-items-center justify-content-between gap-2">';
                    $amountHtml .= '<span>BHD ' . number_format($row->amount, 2) . '</span>';

                    // Find crow bid (is_crow = 1)
                    $crowBid = Bid::where('product_sku', $productSKU)->where('is_crow', 1)->first();

                    // Check if current row is crow
                    $isCrow = $row->is_crow == 1;

                    if ($isSell == 1 && $isCrow) {
                        $amountHtml .= '<img src="' . asset('img/king1.svg') . '" width="20" height="20">';
                    }

                    // Current date and time
                    $currentDateTime = Carbon::now('UTC');

                    // Initialize expired flag
                    $isCrowExpired = false;

                    // If crow exists, check if expired
                    if ($crowBid && $crowBid->expiry_date) {
                        $expiryDateTime = Carbon::parse($crowBid->expiry_date);
                        $isCrowExpired = $currentDateTime->greaterThan($expiryDateTime);
                    }

                    if($currentDateTime > $finish_date){
                        // Case A: Current row is Crow (is_crow = 1)
                        if ($isCrow) {
                            // If not expired and product not sold, show Follow Up
                            if (!$isCrowExpired && $isSell == 0) {
                                // Show crown icon
                                $amountHtml .= '<img src="' . asset('img/king1.svg') . '" width="20" height="20">';
                                $amountHtml .= '<button type="button" 
                                                    class="btn btn-sm btn-primary followup-btn" 
                                                    data-id="' . $row->id . '">
                                                    Follow Up
                                                </button>';
                                $amountHtml .= '<button type="button" 
                                                    class="btn btn-sm btn-danger expire_now" 
                                                    data-id="' . $row->id . '">
                                                    <i class="fa fa-hourglass-end" aria-hidden="true"></i> Expire Now   
                                                </button>';
                            }

                            // If expired, show Expire button
                            if ($isCrowExpired) {
                                $amountHtml .= '<button type="button" 
                                                    class="btn btn-sm btn-danger" 
                                                    data-id="' . $row->id . '">
                                                    Expire
                                                </button>';
                            }
                        }

                        // Case B: Crow is expired → Show Make Winner for all other records
                        else if ($isCrowExpired && $isSell == 0) {
                            $amountHtml .= '<button type="button" 
                                                class="btn btn-sm btn-info make_winner_btn" 
                                                data-id="' . $row->id . '" 
                                                rel="' . $row->product_sku . '">
                                                Make Winner
                                            </button>';
                        }

                        // Case C: No crow exists → Show Make Winner for all records
                        else if (!$crowBid && $isSell == 0) {
                            $amountHtml .= '<button type="button" 
                                                class="btn btn-sm btn-info make_winner_btn" 
                                                data-id="' . $row->id . '" 
                                                rel="' . $row->product_sku . '">
                                                Make Winner
                                            </button>';
                        }
                    } else {
                        $amountHtml .= '';
                    }

                    $amountHtml .= '</div>';

                    return $amountHtml;
                })
                ->editColumn('created_at', function ($row) {
                    return $row->created_at->format('d-m-Y H:i');
                })
                ->rawColumns(['amount'])
                ->make(true);
        }
    }

    public function getShippingDetails(Request $request, $productSKU)
    {
        if ($request->ajax()) {
            $bids = ShippingDetails::select('id', 'country_id', 'duration_day', 'shipping_fee')
                ->where('product_sku', $productSKU)
                ->orderBy('created_at', 'desc');

            return DataTables::of($bids)
                ->addColumn('country', function ($row) {
                    $country_details = getCountrydetailsById($row->country_id);

                    if(isset($country_details->top_level_domain)){
                        return sprintf('<span class="d-flex"><img src="%s/uploads/flags/%s.png"> %s</span>', env('app_url'), $country_details->top_level_domain, '&nbsp;'.$country_details->country_name);
                    } else {
                        return;
                    }
                })
                ->editColumn('duration_day', function ($row) {
                    return $row->duration_day;
                })
                ->addColumn('shipping_fee', function ($row) {
                    return 'BHD '.number_format($row->shipping_fee, 2);
                })
                ->rawColumns(['country'])
                ->make(true);
        }
    }

    public function old_reminderProductReceiptList(Request $request){
        if ($request->ajax()) {
            $environment_data = getEnvironmentData();
            $cutoffTime = Carbon::now()->subHours($environment_data['PRODUCT_RECEIPT_NOTIFICATION_TIME']);

            $query = Product::with(['user', 'buyer', 'orders', 'receipts']) 
                ->where('is_sell', 1)
                ->where('payment_done', 1)
                ->where('updated_at', '<=', $cutoffTime)
                ->whereDoesntHave('receipts'); // no receipt uploaded

            return DataTables::of($query)
                ->addColumn('sku', fn($row) => $row->sku)
                ->addColumn('product_name', fn($row) => $row->name)
                ->addColumn('price', fn($row) => $row->price)
                ->addColumn('buyer_name', fn($row) => optional($row->buyer)->first_name.' '.optional($row->buyer)->last_name)
                ->addColumn('buyer_mobile_nuber', fn($row) => optional($row->buyer)->phone)
                ->addColumn('seller_name', fn($row) => optional($row->user)->first_name.' '.optional($row->user)->last_name)
                ->addColumn('seller_mobile_nuber', fn($row) => optional($row->user)->phone)

                ->addColumn('status', function ($row) {
                    return $row->receipts->isNotEmpty() ? 'Shipped' : 'Waiting for Ship';
                })

                ->addColumn('actions', function ($row) {
                    return "<a href='#' class='btn btn-sm btn-primary'>View</a>
                    <button class='btn btn-sm btn-info reminder' data-product-id='$row->id' data-seller-id='$row->user_id'>Message</button>";
                })
                ->rawColumns(['actions'])
                ->make(true);
        }

        return view('admin.products.pending_receipts');
    }

    public function reminderProductReceiptList(Request $request){
        $type = $request->get('type', 'seller');

        if ($request->ajax()) {
            $query = Orders::with(
                'product.user:id,first_name,last_name,phone,country_id',
                'product:id,sku,user_id,name,name_ar,product_type',
                'user:id,first_name,last_name,phone,country_id')
                ->select('id', 'user_id', 'product_sku', 'price', 'created_at');

            if ($type === 'seller') {
                // Seller tab: Show products that are sold but NOT shipped yet

                $query->whereDoesntHave('productReceipt', function ($q) {
                    $q->whereNotNull('file_path');
                })
                ->whereHas('payment', function ($q) {
                    $q->whereIn('payment_status', [
                        'cash_payment',
                        'paid_on_delivery',
                        'capture'
                    ]);
                });
            } else if ($type === 'shipped') {
                // Shipped tab: Show products that are sold and shipped

                $query->whereHas('productReceipt')
                    ->whereHas('payment', function ($q) {
                        $q->whereIn('payment_status', [
                            'cash_payment',
                            'paid_on_delivery',
                            'capture'
                        ]);
                    });
            } else {
                // Others: Show products that are sold but payment not done yet

                $query->where(function ($q) {
                    $q->whereDoesntHave('payment')
                    ->orWhereHas('payment', function ($p) {
                        $p->whereNotIn('payment_status', [
                            'cash_payment',
                            'paid_on_delivery',
                            'capture'
                        ]);
                    });
                });
            }

            return DataTables::of($query)
                ->addColumn('sku', fn($row) => $row->product_sku)
                ->addColumn('product_name', function ($row) {
                    return $row->product ? $row->product->name : '-';
                })
                ->addColumn('price', fn($row) => $row->price ? 'BHD ' .number_format($row->price, 2) : '')
                ->addColumn('product_type', function ($row) {
                    if(isset($row->product)){
                        return $row->product->product_type == 1 ? 'Auction' : 'Sell';
                    } else {
                        return '-';
                    }
                })
                ->addColumn('buyer_name', function ($row) {
                    return ucwords(optional($row->user)->first_name . ' ' . optional($row->user)->last_name);
                })
                ->addColumn('buyer_mobile_number', function ($row) {
                    $country_details = getCountrydetailsById($row->user->country_id ?? null);
                    if (isset($country_details->top_level_domain)) {
                        $flag = sprintf(
                            '<img src="%s/uploads/flags/%s.png" width="20" height="14" class="me-1">',
                            env('APP_URL'),
                            $country_details->top_level_domain
                        );

                        $countryInfo = sprintf(
                            '%s (+%s)',
                            ' ',
                            $country_details->phone_code
                        );

                        return sprintf(
                            '<span class="d-flex align-items-center" style="width: max-content;">%s %s %s</span>',
                            $flag.'&nbsp;',
                            e($countryInfo),
                            e($row->user->phone)
                        );
                    } else {
                        return e($row->user->phone);
                    }
                })

                ->addColumn('seller_name', function ($row) {
                    $seller = $row->product?->user;

                    return $seller
                        ? ucwords($seller->first_name . ' ' . $seller->last_name)
                        : '-';
                })
                ->addColumn('seller_mobile_number', function ($row) {
                    $seller = $row->product?->user;

                    if (!$seller) {
                        return '-';
                    }

                    $country = getCountrydetailsById($seller->country_id ?? null);

                    if (!empty($country?->top_level_domain)) {
                        return sprintf(
                            '<span class="d-flex align-items-center" style="width: max-content;">
                                <img src="%s/uploads/flags/%s.png" width="20" height="14" class="me-1">
                                (+%s) %s
                            </span>',
                            env('APP_URL'),
                            $country->top_level_domain,
                            $country->phone_code,
                            e($seller->phone)
                        );
                    }

                    return e($seller->phone);
                })
                ->addColumn('status', function ($row) use ($type) {
                    return '<span class="text-warning">Pending Shipment</span>';
                })
                ->addColumn('action', function ($row) use ($type) {
                    $product_id = $row->product ? $row->product->id : '';
                    if ($type === 'shipped') {
                        return "<a href='".route('admin.products.show', $product_id) . '?order_id=' . $row->id."' class='btn btn-sm btn-primary'>View</a>";
                    } else {
                        return "<a href='".route('admin.products.show', $product_id) . '?order_id=' . $row->id."' class='btn btn-sm btn-primary'>View</a>
                        <button class='btn btn-sm btn-info reminder' data-product-id='$product_id' data-seller-id='$row->user_id'>Message</button>";
                    }
                })
                ->rawColumns(['buyer_mobile_number', 'seller_mobile_number', 'status', 'action'])
                ->make(true);
        }

        return view('admin.products.pending_receipts', compact('type'));
    }

    public function adminSendMessageToSeller(Request $request){
        $product = Product::findOrFail($request->product_id);
        $seller = User::findOrFail($request->seller_id);

        // Dispatch job
        dispatch(new \App\Jobs\AdminSendMessageToSellerEmail($seller, $product, $request->message));

        pushNotification($request->seller_id,$request->message);

        return response()->json(['success' => true]);
    }

    public function userFollowupBidMessage(Request $request){
        $bid = Bid::with('products', 'user')->findOrFail($request->bid_id);
        $product = $bid->products;
        $user = $bid->user;

        // Dispatch job
        dispatch(new \App\Jobs\UserFollowupBidMessageEmail($user, $product, $request->follow_up_message));

        pushNotification($user->id, $request->follow_up_message);

        return response()->json([
            'success' => true,
            'message' => 'Follow up message sent successfully.'
        ]);
    }

    public function makeBidderAsWinner(Request $request){
        $environment_data = getEnvironmentData();  
        $PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS = $environment_data['PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS'];

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

        $now = Carbon::now('UTC')->format('Y-m-d H:i');
        $expiry_date = Carbon::parse($now)->addDays($PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS)->format('Y-m-d H:i');

        // Reset all crow users for this product
        Bid::where('product_sku', $request->product_sku)->update(['is_crow' => 0]);

        $bid = Bid::with('products', 'user')->findOrFail($request->bid_id);
        $product_data = $bid->products;
        $winner = $bid->user;

        // Update bid as winner
        $bid->is_crow = 1;
        $bid->is_notified = 1;
        $bid->notified_date_time = $now;
        $bid->expiry_date = $expiry_date;
        $bid->save();

        $product = Product::findOrFail($product_data->id);
        $product->current_notified_user_id = $winner->id;
        $product->notified_at = $now;
        $product->save();

        if($codEnabled == 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' => $winner->id,
                    'product_sku' => $product_data->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' => $winner->id,
                    'product_sku' => $product_data->sku,
                ]
            );  
        }

        Mail::to($winner->email)->send(new \App\Mail\WinnerMail($product, $winner));
        // Mail::to('ankur.r.mts@gmail.com')->send(new \App\Mail\WinnerMail($product_data, $winner));

        $message = "Congratulations! You’ve won the auction for $product_data->name. Complete your purchase within $PAYMENT_DEADLINE_FOR_AUCTION_WINNER_DAYS hours to secure the item — or it may be offered to the next highest bidder";
        
        Notification::create([
            'user_id' => $winner->id,
            'title' => "Congratulations! You won the auction",
            'message' => $message,
            'link' => $payment_link_1
        ]);

        pushNotification($winner->id, $message);

        return response()->json([
            'success' => true,
            'message' => 'Bidder has been marked as winner successfully.'
        ]);
    }

    public function userBidWinnerExpire(Request $request){
        $bid = Bid::find($request->bid_id);

        // Expire logic
        $bid->is_crow = 0;
        $bid->save();

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