<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Contracts\View\Factory;
use Illuminate\Support\Facades\DB;
use App\Models\User;
use App\Models\Order;
use App\Models\BirthCode;
use App\Models\Category;
use App\Models\Product;
use Illuminate\View\View;

class DashboardController extends Controller
{

    /**
     * Show the stats of the website.
     * @return Factory|View
     */

    public function index() :View
    {
                
            $members = User::where('role','user')->count();
            $sellers = User::where('role','seller')->count();
            $companies = User::where('role','company')->count();
            
            $product_paid = Order::query()
                        ->join('order_products as op', 'op.order_id', 'orders.id')
                        ->groupBy('product_id')->count();
                        
                        
            $income = Order::query()
                        ->join('order_payments as op', 'op.order_id', 'orders.id')
                        ->sum('total');
                        
            $category_views = Category::sum('views');
            $product_views = Product::sum('views');
            $buyers = Order::where('user_status','user')
                    ->select('user_id', DB::raw('count(*) as count'))
                    ->with(['user'])
                    ->orderByDESC('count')
                    ->groupBy('user_id')
                    ->take(10)->get();
                    
                    
            $products_paid = Order::query()
                        ->join('order_products as op', 'op.order_id', 'orders.id')
                        ->with(['product'])
                        ->select('product_id', DB::raw('sum(count) as count'))
                        ->orderByDESC('count')
                        ->groupBy('product_id')
                        ->take(10)->get();
                        
                        
            $address_paid = Order::query()
                        ->join('order_addresses as oa', 'oa.order_id', 'orders.id')
                        ->join('addresses as ad', 'address_id', 'ad.id')
                        ->with(['address'])
                        ->select('city_id', DB::raw('count(*) as count'))
                        ->orderByDESC('count')
                        ->groupBy('city_id')
                        ->take(10)->get();
                        
            $males = Order::query()
                        ->join('users', 'users.id', 'orders.user_id')
                        ->where('gender','male')
                        ->with(['user'])
                        ->select('user_id', DB::raw('count(*) as count'))
                        ->orderByDESC('count')
                        ->groupBy('user_id')
                        ->take(10)->get();
            $females = Order::query()
                        ->join('users', 'users.id', 'orders.user_id')
                        ->where('gender','female')
                        ->with(['user'])
                        ->select('user_id', DB::raw('count(*) as count'))
                        ->orderByDESC('count')
                        ->groupBy('user_id')
                        ->take(10)->get();
            $cobon_today = BirthCode::whereDate('date',date('Y-m-d'))->first();

            $lastWeek = date("Y-m-d", strtotime("-1 week"));
            $nextWeek = date("Y-m-d", strtotime("1 day")); // to include today in the statistics

            $weekVisitorCounts = DB::table('website_visitors')
                ->orderBy('created_at', 'asc')
                ->whereBetween('created_at', [$lastWeek, $nextWeek])
                ->count();

            $allVisitorCounts = DB::table('website_visitors')->count();
            $dayVisitCounts = DB::table('website_visitors')->whereRaw('DATE(created_at) = CURRENT_DATE')->count();
            $yearVisitCounts = DB::table('website_visitors')->whereRaw("YEAR(created_at) = YEAR(CURRENT_DATE)")->count();

            for ($i=1; $i < 13; $i++) {
                $month= date('Y').'-'.($i<10 ? '0'.$i : $i);
                $monthVisitCounts[$i] = DB::table('website_visitors')->whereRaw("DATE_FORMAT(created_at, '%Y-%m') = '". $month ."'")->count();
            }
            return view('admin.index', compact(
                'monthVisitCounts','allVisitorCounts', 'yearVisitCounts', 'weekVisitorCounts','dayVisitCounts','members','sellers','companies',
                'product_paid','income','category_views','product_views','buyers','products_paid','address_paid','males','females','cobon_today'
            ));

    }
}
