<?php

namespace App\Http\Controllers;

use Carbon\CarbonInterval;
use Carbon\CarbonPeriod;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Yajny\Models\CategoryCoupon;
use Yajny\Models\Coupon;
use Yajny\Models\Brand;
use Yajny\Member\Models\Click;
use Yajny\Models\Page;
use Illuminate\Http\Request;
use Yajny\EventsYj\Models\AdminNotification;
use Yajny\Member\Models\Sale;
use Yajny\Member\Models\UserBonus;
use Yajny\Member\Models\UserSales;
use Yajny\Models\Store;
use Yajny\Member\Models\UserClaim;
use Yajny\Member\Models\UserPayouts;
use Yajny\Models\ContactUs;
use Yajny\Models\Category;
use Yajny\Models\StoreCashback;
use Yajny\Models\Tag;
use Yajny\Member\Models\Member;

class DashboardController extends Controller
{

    public function __construct()
    {
        parent::__construct();

        $this->data = array(
            'pageTitle' => $this->config['cnf_appname'],
            'pageNote' => 'Welcome to Dashboard',

        );
    }

    public function index(Request $request)
    {
        if(!in_array(auth()->user()->group_id, [1, 2]))
            return view('blank');

//        $earning = Cache::remember('dash_earning', (6 * 60 * 60), function () {
//            return Sale::where('status', '<>', 'declined')->sum('comission');
//        });
//
//        $bonus = Cache::remember('dash_bonus', (6 * 60 * 60), function () {
//            return UserBonus::where('status', 'confirmed')->sum('amount');
//        });
//        $cashback = Cache::remember('dash_cashback', (6 * 60 * 60), function () {
//            return UserSales::where('status', '<>', 'declined')->sum('cashback');
//        });
//
//        $profit = $earning - $bonus - $cashback;
//        $missing_cb = Cache::remember('dash_missing_cb', (6 * 60 * 60), function () {
//            return UserClaim::where('status', 'open')->count();
//        });
//        $payment_request = Cache::remember('dash_payment_request', (6 * 60 * 60), function () {
//            return UserPayouts::where('status', 'created')->count();
//        });
//        $admin_notification = Cache::remember('dash_admin_notification', (6 * 60 * 60), function () {
//            return AdminNotification::where('is_read', '<>', '1')->count();
//        });
//        $contactforms = Cache::remember('dash_contactforms', (6 * 60 * 60), function () {
//            return ContactUs::count();
//        });
//        $topMerchants = Cache::remember('dash_topMerchants', (6 * 60 * 60), function () {
//            return Store::orderBy('clicks', 'DESC')->limit(10)->get();
//        });
//        $recentMerchants = Cache::remember('dash_recentMerchants', (6 * 60 * 60), function () {
//            return Store::orderBy('id', 'DESC')->limit(10)->get();
//        });
//
//
//
//        $topUsers = collect([]);
////        $topUsers = Cache::remember('dash_topUsers', (6 * 60 * 60), function () {
////            return UserSales::with(['user' => function ($q) {
////                return $q->withCount('clicks', 'referred_users');
////            }])->selectRaw('user_id,SUM(cashback) as total_cashback')->groupBy('user_id')->orderByRaw('total_cashback')->get();
////        });
//
//        $latestTransactions = Cache::remember('dash_latestTransactions', (6 * 60 * 60), function () {
//            return Sale::orderBy('id', 'DESC')->with('store')->limit(10)->get();
//        });
//        $stats = Cache::remember('dash_stats', (6 * 60 * 60), function () {
//            return array('store' => Store::count(), 'store_categories' => Category::count(), 'coupon_categories' => CategoryCoupon::count(), 'brands' => Brand::count()
//            , 'tags' => Tag::count(), 'live_coupons' => Coupon::count(), 'active_users' => Member::where('active', 1)->count(), 'total_clicks' => Click::count(), 'total_transactions' => Sale::count(),
//                'pages' => Page::count());
//        });
//
//        $ctrGraph = Cache::remember('dash_ctrGraph', (6 * 60 * 60), function () {
//            return $this->getCtrGraph();
//        });
//        $userGraph = Cache::remember('dash_userGraph', (6 * 60 * 60), function () {
//            return $this->getUserGraph();
//        });
//        $activeUserGraph = Cache::remember('dash_activeUserGraph', (6 * 60 * 60), function () {
//            return $this->getActiveUserGraph();
//        });
//        $clickGraph = Cache::remember('dash_clickGraph', (6 * 60 * 60), function () {
//            return $this->getClickGraph();
//        });
//        $userSignupGraph = Cache::remember('dash_userSignupGraph', (6 * 60 * 60), function () {
//            return $this->userSignupGraph();
//        });
//
//
//        $allStores = Cache::remember('dash_allStores', (6 * 60 * 60), function () {
//            return Store::count();
//        });
//        $allStoreCashback = Cache::remember('dash_allStoreCashback', (6 * 60 * 60), function () {
//            $nbr=0;
//            Store::where('status','<>','trash')->get()->map(function ($item) use (&$nbr)  {
//                $nbr +=  count(app('App\Http\Controllers\StoresController')->getStoreDetails($item->id)['store_cashback']);
//            });
//            return $nbr;
//        });
//        $allStoreCoupons = Cache::remember('dash_allStoreCoupons', (6 * 60 * 60), function () {
//            $nbr=0;
//            Store::where('status','<>','trash')->get()->map(function ($item) use (&$nbr)  {
//                $nbr +=  count(app('App\Http\Controllers\StoresController')->getStoreDetails($item->id)['store_coupons']);
//            });
//            return $nbr;
//        });
//
//        $ActiveCashbackStore = Cache::remember('dash_activeCashbackStore', (6 * 60 * 60), function () {
//
//              return Store::where('status','<>','trash')->get()->map(function ($item)  {
//                  $data=app('App\Http\Controllers\StoresController')->getStoreDetails($item->id)['store_cashback']->first();
//                  if($data){
//                     return array('store' => $item,'data'=> $data );
//                  }
//              });
//        });
//        $DraftCashbackStore = Cache::remember('dash_draftCashbackStore', (6 * 60 * 60), function () {
//
//            return  Store::where('status','<>','trash')->get()->map(function ($item)    {
//                $data=app('App\Http\Controllers\StoresController')->getStoreDetails($item->id)['draft_store_cashback']->first();
//                if($data){
//                    return array('store' => $item,'data'=> $data );
//                }
//            });
//        });
//        $ActiveCouponStore = Cache::remember('dash_activeCouponStore', (6 * 60 * 60), function () {
//
//              return Store::where('status','<>','trash')->get()->map(function ($item)  {
//                  $data=app('App\Http\Controllers\StoresController')->getStoreDetails($item->id)['store_coupons']->first();
//                  if($data){
//                      return array('store' => $item,'data'=> $data );
//                  }
//              });
//        });
//        $DraftCouponStore = Cache::remember('dash_draftCouponStore', (6 * 60 * 60), function () {
//
//            return  Store::where('status','<>','trash')->get()->map(function ($item)   {
//                $data=app('App\Http\Controllers\StoresController')->getStoreDetails($item->id)['draft_store_coupons']->first();
//                if($data){
//                    return array('store' => $item,'data'=> $data );
//                }
//            });
//        });
//
//        return view('dashboard.index', compact('activeUserGraph',
//            'earning','bonus','cashback','profit','missing_cb', 'payment_request',
//            'admin_notification', 'contactforms', 'topMerchants', 'recentMerchants', 'topUsers',
//            'latestTransactions', 'stats', 'ctrGraph', 'userGraph', 'clickGraph', 'userSignupGraph',
//            'allStores','allStoreCoupons','allStoreCashback','ActiveCashbackStore','DraftCashbackStore',
//            'ActiveCouponStore','DraftCouponStore' ));

        return view('dashboard.index');
    }


    public function getCtrGraph()
    {
        $ctrGraph = [];

        $clickSevenDays = DB::table('clicks')->selectRaw('DATE(click_time) cdate, COUNT(*) clicks')->whereRaw(' DATE(click_time) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(click_time)'))->get();
        $transSevenDays = DB::table('sales')->selectRaw('DATE(created_at) sdate, COUNT(*) sales')->whereRaw(' DATE(created_at) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(created_at)'))->get();
        $clickSevenDays = $clickSevenDays->mapWithKeys(function ($item) {
            return [$item->cdate => $item->clicks];
        });
        $transSevenDays = $transSevenDays->mapWithKeys(function ($item) {
            return [$item->sdate => $item->sales];
        });

        $clickGraphPoints = [];
        $saleGraphPoints = [];
        $ctrGraphPoints = [];
        $graphLables = [];
        $total_clicks = 0;
        $total_sales = 0;
        $avg_ctr = 0;
        $ctr_count = 0;
        // dump($clickSevenDays);
        // dump($transSevenDays);
        for ($dayLoop = 6; $dayLoop >= 0; $dayLoop--) {
            $date = date('Y-m-d', strtotime("now - {$dayLoop} days"));
            if (!isset($clickSevenDays[$date])) $clickSevenDays[$date] = 0;
            if (!isset($transSevenDays[$date])) $transSevenDays[$date] = 0;
            if ($clickSevenDays[$date] > 0) {
                $clickSevenDays_div = $clickSevenDays[$date] > 0 ? $clickSevenDays[$date] : 1;
                $ctr = round(($transSevenDays[$date] / $clickSevenDays_div) * 100, 2);
            } else
                $ctr = 0;

            array_push($ctrGraphPoints, ['meta' => 'Ctr', 'value' => $ctr]);

            array_push($graphLables, $date);
            array_push($saleGraphPoints, ['meta' => 'Sale', 'value' => $transSevenDays[$date]]);
            array_push($clickGraphPoints, ['meta' => 'Click', 'value' => $clickSevenDays[$date]]);
            $total_clicks += $clickSevenDays[$date];
            $total_sales += $transSevenDays[$date];
            if ($ctr > 0) $ctr_count++;

            $avg_ctr += $ctr;

        }
        $total_clicks_div = $total_clicks > 0 ? $total_clicks : 1;
        $avg_ctr = round(($total_sales / $total_clicks_div) * 100, 2);

        $ctrGraph['clicks'] = $clickGraphPoints;
        $ctrGraph['sales'] = $saleGraphPoints;
        $ctrGraph['ctr'] = $ctrGraphPoints;
        $ctrGraph['labels'] = $graphLables;
        $ctrGraph['avg_ctr'] = $avg_ctr;
        $ctrGraph['total_clicks'] = $total_clicks;
        $ctrGraph['total_sales'] = $total_sales;

        return $ctrGraph;
    }

    public function getUserGraph()
    {
        $userGraph = [];
        $commissionSevenDays = DB::table('sales')->selectRaw('DATE(created_at) cdate, SUM(comission) as total_commission')->whereRaw(' DATE(created_at) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(created_at)'))->get();
        $cashbackSevenDays = DB::table('user_sales')->selectRaw('DATE(created_at) usdate, SUM(cashback) as total_cashback')->whereRaw(' DATE(created_at) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(created_at)'))->get();
        $bonusSevenDays = DB::table('user_bonus')->selectRaw('DATE(awarded_on) bdate, SUM(amount) as total_amount')->whereRaw(' DATE(awarded_on) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(awarded_on)'))->get();
        $commissionSevenDays = $commissionSevenDays->mapWithKeys(function ($item) {
            return [$item->cdate => $item->total_commission];
        });
        $cashbackSevenDays = $cashbackSevenDays->mapWithKeys(function ($item) {
            return [$item->usdate => $item->total_cashback];
        });
        $bonusSevenDays = $bonusSevenDays->mapWithKeys(function ($item) {
            return [$item->bdate => $item->total_amount];
        });

        $commissionGraphPoints = [];
        $cashbackGraphPoints = [];
        $bonusGraphPoints = [];
        $graphLables = [];
        $total_commission = 0;
        $total_cashback = 0;
        $total_bonus = 0;

        // dump($clickSevenDays);
        // dump($transSevenDays);
        for ($dayLoop = 6; $dayLoop >= 0; $dayLoop--) {
            $date = date('Y-m-d', strtotime("now - {$dayLoop} days"));
            if (!isset($commissionSevenDays[$date])) $commissionSevenDays[$date] = 0;
            if (!isset($cashbackSevenDays[$date])) $cashbackSevenDays[$date] = 0;
            if (!isset($bonusSevenDays[$date])) $bonusSevenDays[$date] = 0;


            array_push($bonusGraphPoints, ['meta' => 'Bonus', 'value' => $bonusSevenDays[$date]]);

            array_push($graphLables, $date);
            array_push($cashbackGraphPoints, ['meta' => 'Cashback', 'value' => $cashbackSevenDays[$date]]);
            array_push($commissionGraphPoints, ['meta' => 'Commission', 'value' => $commissionSevenDays[$date]]);
            $total_commission += $commissionSevenDays[$date];
            $total_cashback += $cashbackSevenDays[$date];
            $total_bonus += $bonusSevenDays[$date];


        }


        $userGraph['commission'] = $commissionGraphPoints;
        $userGraph['cashback'] = $cashbackGraphPoints;
        $userGraph['bonus'] = $bonusGraphPoints;
        $userGraph['labels'] = $graphLables;

        $userGraph['total_commission'] = $total_commission;
        $userGraph['total_cashback'] = $total_cashback;
        $userGraph['total_bonus'] = $total_bonus;

        return $userGraph;
    }

    public function getActiveUserGraph()
    {
        $userGraph = [];
        $fromDate = now()->startOfMonth()->subYear()->toDateString();
        $data = DB::select("SELECT DATE_FORMAT(transaction_time,'%Y-%m') as cdate, COUNT(*) as total_count
            from users u
                     join user_sales us on us.user_id = u.user_id and u.is_merchant = false
            WHERE DATE(transaction_time) BETWEEN '{$fromDate}' AND DATE(NOW())
            group by u.user_id, YEAR(transaction_time), MONTH(transaction_time)");
        $activeUserChartData = [];
        $loyalUserChartData = [];
        foreach ($data as $item) {
            if (!isset($activeUserChartData[$item->cdate])) {
                $activeUserChartData[$item->cdate] = 0;
                $loyalUserChartData[$item->cdate] = 0;
            }
            $activeUserChartData[$item->cdate]++;
            if ($item->total_count >= 3)
                $loyalUserChartData[$item->cdate]++;
        }

        $activeUserGraphPoints = [];
        $loyalUserGraphPoints = [];
        $graphLables = [];

        foreach (CarbonPeriod::between($fromDate, now())->setDateInterval(CarbonInterval::month()) as $date) {
            $date = $date->format('Y-m');
            if (!isset($activeUserChartData[$date])) $activeUserChartData[$date] = 0;
            if (!isset($loyalUserChartData[$date])) $loyalUserChartData[$date] = 0;

            array_push($activeUserGraphPoints, ['meta' => 'Active', 'value' => $activeUserChartData[$date]]);
            array_push($loyalUserGraphPoints, ['meta' => 'Loyal', 'value' => $loyalUserChartData[$date]]);

            array_push($graphLables, $date);
        }

        $userGraph['active'] = $activeUserGraphPoints;
        $userGraph['loyal'] = $loyalUserGraphPoints;
        $userGraph['labels'] = $graphLables;

        return $userGraph;
    }

    public function getClickGraph()
    {
        $clickGraph = [];

        $clicksSevenDays = DB::table('clicks')->selectRaw('DATE(click_time) cdate, COUNT(*) clicks,source_type')->whereRaw(' DATE(click_time) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(click_time),source_type'))->get();
        // dd($clicksSevenDays = DB::table('clicks')->selectRaw('DATE(click_time) cdate, COUNT(*) clicks,source_type')->whereRaw(' DATE(click_time) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(click_time),source_type'))->toSql());
        $storeSevenDays = $clicksSevenDays->where('source_type', 'store')->mapWithKeys(function ($item) {
            return [$item->cdate => $item->clicks];
        });
        $couponSevenDays = $clicksSevenDays->where('source_type', 'coupon')->mapWithKeys(function ($item) {
            return [$item->cdate => $item->clicks];
        });
        $sliderSevenDays = $clicksSevenDays->where('source_type', 'slider')->mapWithKeys(function ($item) {
            return [$item->cdate => $item->clicks];
        });
        $shareSevenDays = $clicksSevenDays->where('source_type', 'share')->mapWithKeys(function ($item) {
            return [$item->cdate => $item->clicks];
        });
        $storecatSevenDays = $clicksSevenDays->where('source_type', 'store-cat')->mapWithKeys(function ($item) {
            return [$item->cdate => $item->clicks];
        });

        $storeGraphPoints = [];
        $couponGraphPoints = [];
        $sliderGraphPoints = [];
        $shareGraphPoints = [];
        $storecatGraphPoints = [];
        $graphLables = [];
        $total_coupon_clicks = 0;
        $total_store_clicks = 0;
        $total_slider_clicks = 0;


        // dump($clickSevenDays);
        // dump($transSevenDays);
        for ($dayLoop = 6; $dayLoop >= 0; $dayLoop--) {
            $date = date('Y-m-d', strtotime("now - {$dayLoop} days"));
            if (!isset($storeSevenDays[$date])) $storeSevenDays[$date] = 0;
            if (!isset($couponSevenDays[$date])) $couponSevenDays[$date] = 0;
            if (!isset($sliderSevenDays[$date])) $sliderSevenDays[$date] = 0;
            if (!isset($shareSevenDays[$date])) $shareSevenDays[$date] = 0;
            if (!isset($storecatSevenDays[$date])) $storecatSevenDays[$date] = 0;

            array_push($storecatGraphPoints, ['meta' => 'StoreCategory', 'value' => $storecatSevenDays[$date]]);
            array_push($graphLables, $date);
            array_push($shareGraphPoints, ['meta' => 'Share', 'value' => $shareSevenDays[$date]]);
            array_push($storeGraphPoints, ['meta' => 'Store', 'value' => $storeSevenDays[$date]]);
            array_push($couponGraphPoints, ['meta' => 'Coupon', 'value' => $couponSevenDays[$date]]);
            array_push($sliderGraphPoints, ['meta' => 'Slider', 'value' => $sliderSevenDays[$date]]);

            $total_coupon_clicks += $couponSevenDays[$date];
            $total_store_clicks += $storeSevenDays[$date];
            $total_slider_clicks += $sliderSevenDays[$date];


        }

        $clickGraph['store'] = $storeGraphPoints;
        $clickGraph['coupon'] = $couponGraphPoints;
        $clickGraph['share'] = $shareGraphPoints;
        $clickGraph['slider'] = $sliderGraphPoints;
        $clickGraph['storecat'] = $storecatGraphPoints;
        $clickGraph['labels'] = $graphLables;

        $clickGraph['total_coupon_clicks'] = $total_coupon_clicks;
        $clickGraph['total_store_clicks'] = $total_store_clicks;
        $clickGraph['total_slider_clicks'] = $total_slider_clicks;
        // dd($clickGraph);
        return $clickGraph;

    }

    function userSignupGraph()
    {
        $userSignupGraph = [];

        $userSevenDays = DB::table('users')->selectRaw('DATE(created_at) udate, COUNT(*) users')->whereRaw(' DATE(created_at) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) ')->groupBy(DB::Raw('DATE(created_at)'))->get();
        $userSevenDays = $userSevenDays->mapWithKeys(function ($item) {
            return [$item->udate => $item->users];
        });
        $userGraphPoints = [];
        $graphLables = [];
        $user_this_week = DB::table('users')->selectRaw('COUNT(*) as ucount')->whereRaw('DATE(created_at) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)')->first();
        $user_last_week = DB::table('users')->selectRaw('COUNT(*) as ucount')->whereRaw('DATE(created_at) BETWEEN   DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) AND  DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)')->first();
        $user_last_month = DB::table('users')->selectRaw('COUNT(*) as ucount')->whereRaw('DATE(created_at) BETWEEN   DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) AND  CURRENT_DATE')->first();

        for ($dayLoop = 6; $dayLoop >= 0; $dayLoop--) {
            $date = date('Y-m-d', strtotime("now - {$dayLoop} days"));
            if (!isset($userSevenDays[$date])) $userSevenDays[$date] = 0;


            array_push($userGraphPoints, ['meta' => 'User', 'value' => $userSevenDays[$date]]);
            array_push($graphLables, $date);
        }


        $userSignupGraph['user'] = $userGraphPoints;

        $userSignupGraph['labels'] = $graphLables;

        $userSignupGraph['user_this_week'] = $user_this_week->ucount;
        $userSignupGraph['user_last_week'] = $user_last_week->ucount;
        $userSignupGraph['user_last_month'] = $user_last_month->ucount;

        return $userSignupGraph;

        /*
        SELECT COUNT(*) as ucount , 'this_week' duration FROM `users` WHERE DATE(created_at) > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
UNION
SELECT COUNT(*) as ucount , 'last_week' duration FROM `users` WHERE DATE(created_at) BETWEEN   DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) AND  DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
UNION
SELECT COUNT(*) as ucount , 'last_30days' duration FROM `users` WHERE DATE(created_at) BETWEEN   DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) AND  CURRENT_DATE
*/
    }

    function generateEmailTemplates()
    {

        $base = base_path() . '/public/etemplate/';

        $EmailHead = base_path() . "/resources/views/EmailHead.blade.php";
        $EmailFoot = base_path() . "/resources/views/EmailFoot.blade.php";

        $index_html = '<h1>Email Templates</h1><br>';

        $EmailHeadHtml = file_get_contents($EmailHead);
        $EmailFootHtml = file_get_contents($EmailFoot);


        $res = DB::table('tb_email_templates')->get();
        foreach ($res as $template) {
            $info = $EmailHeadHtml . $template->body . $EmailFootHtml;
            $index_html .= '<a href="' . url('/etemplate/' . $template->email_key . '.html') . '">' . $template->purpose . '</a><br>';
            $fp = fopen($base . $template->email_key . '.html', "w+");
            fwrite($fp, $info);
            fclose($fp);


        }

        $fp = fopen($base . 'index.html', "w+");
        fwrite($fp, $index_html);
        fclose($fp);

        return redirect('/etemplate/');

    }


}