<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\DataImport;
use Yajra\Datatables\Datatables;
use App\Exports\DatasExport;
use App\Models\Data;
use App\Models\Service;
use Illuminate\Support\Facades\Auth;

class DataController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
    }

    public function index()
    {
        return view('home');
    }

    public function import()
    {
        return view('import');
    }

    public function dirtyData()
    {
        $data = DB::table('data')
            ->get();

        $operator = DB::select(DB::raw("select distinct(operator) from data"));
        $amount = DB::select(DB::raw("select distinct(amount) from data"));
        $getOperator = "";
        $getAmount = "";
        return view('dirty', compact('data', 'operator', 'amount', 'getOperator', 'getAmount'));
    }

    public function toimport(Request $request)
    {
        if (!$request->all()) {
            return redirect('data-import');
        }

        $checkService = Service::select('service_name')->where('user_id', Auth::id())->groupBy('service_name')->get();

        $checkServiceCount = $checkService->count();


        if ($checkServiceCount < 2 && $checkServiceCount > 0) {
            if ($request->sname == $checkService[0]->service_name) {
                $import = new DataImport($request->sname);
                $data = Excel::import($import, $request->file('importfile'));
                $row = $import->getRowCount();

                $service = new Service;
                $service->service_name = $request->sname;
                $service->user_id = Auth::id();
                $service->save();

                return redirect('data-dirty')->with('row', $row);
            } else {
                return redirect('data-import')->with('alert', 'You need to finish your existing service when it done Delete it!');
            }
        } elseif ($checkServiceCount < 1) {
            $import = new DataImport($request->sname);
            $data = Excel::import($import, $request->file('importfile'));
            $row = $import->getRowCount();

            $service = new Service;
            $service->service_name = $request->sname;
            $service->user_id = Auth::id();
            $service->save();

            return redirect('data-dirty')->with('row', $row);
        } else {
            return redirect('data-import')->with('alert', 'Something went wrong!');
        }
    }

    // public function dataCleaning()
    // {
    //     DB::select(DB::raw("
    //         update data set phone =  right(phone, length(phone)-1)  where  SUBSTRING(phone, 1, 1) = '0'
    //     "));

    //     DB::select(DB::raw("
    //         update data set phone = REGEXP_REPLACE(phone, '[^0-9]+', '');
    //     "));

    //     DB::select(DB::raw("
    //         update data set phone =  SUBSTR(phone,2)   where  SUBSTR(phone, 1, 1) = '0' ;
    //     "));

    //     DB::select(DB::raw("
    //         update data SET phone = SUBSTR(phone,4,LENGTH(phone)) where  SUBSTRING(phone, 1, 3) = '959' ;
    //     "));

    //     DB::select(DB::raw("
    //         update data SET phone = SUBSTR(phone,3,LENGTH(phone))  where  SUBSTRING(phone, 1, 2) = '95' and length(phone)=11 ;
    //     "));

    //     DB::select(DB::raw("
    //         update data SET phone = SUBSTR(phone,2,LENGTH(phone))  where  SUBSTRING(phone, 1, 1) = '9' and length(phone)=10 ;
    //     "));

    //     DB::select(DB::raw("
    //         delete from data where length(phone) > 13
    //     "));

    //     DB::select(DB::raw("
    //         delete from data where length(phone) < 6
    //     "));

    //     DB::select(DB::raw("
    //         update data set date = '2020-01-01' where date = '';
    //     "));
    // }

    // public function getDirtyData()
    // {
    //     $data = DB::table('data')
    //         // ->where('operator', '=', null)
    //         // ->orWhere('operator', '=', "")
    //         // ->whereRaw('length(phone) > 9')
    //         ->get();

    //     return DataTables::of($data)->make(true);
    // }

    public function dataExport(Request $request)
    {
        if (!$request->all()) {
            return redirect('data-dirty');
        }

        $getOperator = $request->operator;
        $getAmount = $request->amount;
        $getAction = $request->action;
        $operator = DB::select(DB::raw("select distinct(operator) from data"));
        $amount = DB::select(DB::raw("select distinct(amount) from data"));

        if ($getOperator && $getAmount) {
            $data = DB::table('data')->where("operator", $getOperator)->where("amount", $getAmount)->get();
        } elseif ($getOperator && !$getAmount) {
            $data = DB::table('data')->where("operator", $getOperator)->get();
        } elseif (!$getOperator && $getAmount) {
            $data = DB::table('data')->where("amount", $getAmount)->get();
        } else {
            $data = DB::table('data')->get();
        }

        if ($getAction == "search") {
            return view('dirty', compact('data', 'operator', 'amount', 'getOperator', 'getAmount'));
        } elseif ($getAction == "all") {
            return redirect('data-dirty');
        } elseif ($getAction == "export") {
            $filename = 'phone_' . date("Y-m-d s") . '.xlsx';
            return Excel::download(new DatasExport($data), '' . $filename . '');
        } else {
            return redirect('removeData');
        }
    }

    public function removeData()
    {
        Service::where('user_id', Auth::id())->delete();

        Data::truncate();

        return redirect('data-dirty');
    }

    // public function getOperator()
    // {

    //     DB::select(DB::raw("
    //         update data set operator = 'telenor' where  SUBSTRING(phone, 1, 2) >= '74' and  SUBSTRING(phone, 1, 2) <= '79'
    //     "));

    //     DB::select(DB::raw("
    //         update data set operator = 'ooredoo' where  SUBSTRING(phone, 1, 2) >= '90' and  SUBSTRING(phone, 1, 2) <= '99'
    //     "));

    //     DB::select(DB::raw("
    //         update data set operator = 'mytel' where  SUBSTRING(phone, 1, 2) >= '60' and  SUBSTRING(phone, 1, 2) <= '69'
    //     "));

    //     DB::select(DB::raw("
    //         update data set operator = 'mec' where  SUBSTRING(phone, 1, 2) in ('93','73')
    //     "));

    //     DB::select(DB::raw("
    //         update data set operator = 'mec' where  SUBSTRING(phone, 1, 2) >= '30' and  SUBSTRING(phone, 1, 2) <= '39'
    //     "));

    //     DB::select(DB::raw("
    //         update data set operator = 'mpt' where operator is null
    //     "));
    // }


}
