<?php

namespace App\Http\Controllers\Admin;

use App\Models\User;
use App\Models\Product;
use App\Models\Category;
use App\Models\Advertisement;
use App\Models\ContactUs;
use App\Models\Payment;
use App\Models\Orders;
use Carbon\Carbon;

class HomeController
{
    public function index()
    {   
        $now = Carbon::now('UTC');

        $buyer_requested = User::whereIn('kyc_status', [0,1])->whereHas('roles', function ($q) {
            $q->where('title', 'Buyer');
        })->count();

        $buyer_count = User::where('kyc_status', 2)->whereHas('roles', function ($q) {
            $q->where('title', 'Buyer');
        })->count();

        $buyer_rejected_count = User::where('kyc_status', 3)->count();

        $seller_requested = User::where('seller_status', '1')->whereHas('roles', function ($q) {
            $q->where('title', 'Seller');
        })->count();

        $seller_count = User::where('seller_status', 2)->whereHas('roles', function ($q) {
            $q->where('title', 'Seller');
        })->count();

        $seller_rejected_count = User::where('seller_status', 3)->count();

        $category_count = Category::where('status', 1)->count();

        $upcoming_product_count = Product::where('live_date', '>', $now)->where('status', 'Approved')->count();

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

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

        $waiting_for_payment_count = 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();

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

        $sell_product_count = Product::where('product_type', 2)->whereIn('status', ['Approved', 'Save'])->count();

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

        $advertisement_count = Advertisement::count();

        $online_payment_count = Payment::where('payment_status', 'capture')->count();
        $cash_payment_requested_count = Payment::where('payment_status', 'pending')->count();
        $cash_payment_count = Payment::whereIn('payment_status', ['cash_payment', 'paid_on_delivery'])->count();
        $cash_payment_rejected_count = Payment::where('payment_status', 'rejected')->count();

        $contact_us_count = ContactUs::count();

        return view('home', compact('buyer_requested', 'buyer_count', 'buyer_rejected_count', 'seller_requested', 'seller_count', 'seller_rejected_count', 'category_count', 'upcoming_product_count', 'live_auction_product_count', 'archived_auction_product_count', 'waiting_for_payment_count', 'sell_product_count', 'advertisement_count', 'online_payment_count', 'cash_payment_requested_count', 'cash_payment_count', 'cash_payment_rejected_count', 'contact_us_count', 'sold_auction_product_count', 'sell_sold_product_count'));
    }
}