<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Store;
use Illuminate\Support\Facades\DB;
use App\StoreUsers;
use App\SortieStock;
use App\Article;
use Illuminate\Support\Carbon;

class StoresController extends Controller {

    public function index() {
        $stores = Store::all();
        return view('mattine.stores.index', compact('stores'));
    }

    public function store(Request $request) {

        $this->validate($request, [
            'name' => 'required',
            'address' => 'required',
            'tel' => 'required'
        ]);

        $options = [
            'address' => $request['address'],
            'tel' => $request['tel'],
        ];

        $store = Store::create([
                    'name' => $request->name,
                    'token' => Carbon::now()->format('YmdHis') . str_random(50),
                    'options' => json_encode($options)
        ]);

        /* StoreUsers::create([
          'store_id' => $store->id,
          'user_id' => auth()->id
          ]); */

        return redirect()->route('stores.index')->with('success', 'Store bien ajouter');
    }

    public function single(Store $store) {
        $sales_today = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                            ->where(DB::raw('date(date_)'), DB::raw("date(UTC_TIMESTAMP)"))
                            ->sum('montant');
        $sales_yesterday = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                             ->where(DB::raw('date(date_)'), DB::raw(" date(UTC_TIMESTAMP-INTERVAL 1 day)"))
                            ->sum('montant');
        $percent_day = $sales_yesterday > 0 ? (($sales_today - $sales_yesterday) / $sales_yesterday) * 100 : 0;
        

        $sales_week = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                            ->where(DB::raw('yearweek(date_)'), DB::raw("yearweek(UTC_TIMESTAMP)"))
                            ->sum('montant');
        $sales_lweek = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                            ->where(DB::raw('yearweek(date_)'), DB::raw("yearweek(UTC_TIMESTAMP - INTERVAL 7 DAY)"))
                            ->sum('montant');
        $percent_week = $sales_lweek > 0 ? (($sales_week - $sales_lweek) / $sales_lweek) * 100 : 0;
        
        
        $sales_month = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                            ->where([[DB::raw('MONTH(date_)'),DB::raw("MONTH(UTC_TIMESTAMP)")],
                                [DB::raw('YEAR(date_)'), DB::raw("YEAR(UTC_TIMESTAMP)")]])
                            ->sum('montant');
        $sales_lmonth = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                            ->where([[DB::raw('YEAR(date_)'), DB::raw("YEAR(UTC_TIMESTAMP - INTERVAL 1 MONTH)")],
                                [DB::raw('MONTH(date_)'), DB::raw("MONTH(UTC_TIMESTAMP - INTERVAL 1 MONTH)")]])
                            ->sum('montant');
        $percent_month = $sales_lmonth > 0 ? (($sales_month - $sales_lmonth) / $sales_lmonth) * 100 : 0;


        $sales_year = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                            ->where(DB::raw('YEAR(date_)'), DB::raw('YEAR(CURDATE())'))
                            ->sum('montant');
        $sales_lyear = DB::table('sortiestocks')
                            ->where('store_id', $store->id)
                            ->where(DB::raw('YEAR(date_)'), DB::raw('YEAR(CURDATE())-1'))
                            ->sum('montant');
        $percent_year = $sales_lyear > 0 ? (($sales_year - $sales_lyear) / $sales_lyear) * 100 : 0;


        $last_sortie = SortieStock::where('store_id', $store->id)->orderBy('id', 'desc')->first();

        $records = $last_sortie != null ? collect(json_decode($last_sortie->details)) : [];
        
        foreach ($records as $item) {
            $article = Article::where("matricule", $item->Matricule)->first();
            $item->article_id = $article->nom;
            $item->TVA = $article->image_file; //Image of product
            $item->ID_Depot = $article->category()->first()['designation']; //category name
            $item->ID_BR = $last_sortie->client()->first()['name']; //client name
            $item->Remise = $last_sortie->type; //Type de sortie   
        }

        $cats = DB::table('articles')
                ->join('categories', 'categories.id', '=', 'articles.categorie_id')
                ->select(DB::raw("categories.designation,count(articles.id) as 'numbersarticles'"))
                ->where('articles.store_id', $store->id)
                ->groupBy('categories.designation')
                ->get();
                
        $revenueByYear = DB::table('sortiestocks')
                ->select(DB::raw('sum(montant) as somme, Year(date_) as annee'))
                ->where('sortiestocks.store_id', $store->id)
                ->groupBy(DB::raw('Year(date_)'))
                ->get();
                
        $revenueByMonths = DB::table('sortiestocks')
                ->select(DB::raw('sum(montant) as somme,Monthname(date_) AS month_name'))
                ->where('store_id', $store->id)
                ->where(DB::raw('YEAR(date_)'),DB::raw('YEAR(CURDATE())'))
                ->groupBy(DB::raw('month_name'))
                ->get();
        //dd($revenueByMonths);
        $revenueCurrentMonth = DB::table('sortiestocks')
                ->select(DB::raw("sum(montant) as somme, date_"))
                ->where('sortiestocks.store_id', $store->id)
                ->where(DB::raw('MONTH(date_)'), '=', DB::raw('MONTH(ADDDATE(CURDATE(), INTERVAL 1 HOUR))'))
                ->where(DB::raw('YEAR(date_)'), '=', DB::raw('YEAR(ADDDATE(CURDATE(), INTERVAL 1 HOUR))'))
                ->groupBy(DB::raw("date_"))
                ->get();
        //dd($revenueCurrentMonth);


        return view('mattine.index', compact(
            'store'
            ,'records'
            ,'sales_today'
            ,'sales_yesterday'
            ,'percent_day'
            ,'sales_week'
            ,'sales_lweek'
            ,'percent_week'
            ,'sales_month'
            ,'sales_lmonth'
            ,'percent_month'
            ,'sales_year'
            ,'sales_lyear'
            ,'percent_year'
            ,'cats'
            ,'revenueByYear'
            ,'revenueByMonths'
            ,'revenueCurrentMonth'
        ));
    }

    public function showEdit(Store $store) {
        return view('mattine.stores.edit', compact('store'));
    }

    public function update(Store $store, Request $request) {

        $this->validate($request, [
            'name' => 'required',
            'address' => 'required',
            'tel' => 'required'
        ]);

        $options = [
            'address' => $request['address'],
            'tel' => $request['tel'],
        ];

        $store->update([
            'name' => $request->name,
            'options' => json_encode($options)
        ]);

        return redirect()->route('stores.options', $store)->with('success', 'Store bien modifier');
    }

    public function delete(Store $store) {
        $store->delete();
        return redirect()->route('stores.index');
    }

}