<?php

    public function list(Request $request){

        //validate the request
        $this->validate($request, [
            'company_guid' => 'required|exists:companies,guid',
            'per_page' => 'integer|between:5,100',//the user cant ask for more than 100 rows in 1 query
            'sort_order' => 'in:asc,desc'
        ]);

        //check if the token is valid
        if (! $user = JWTAuth::parseToken()->authenticate()) {
            return response()->json(['msg' => 'User not found'], 200);//404
        }

        //set variables from the request
        $guid           = $request->input('company_guid');
        $page           = $request->input('page');
        $search_query   = $request->input('search_query');
        $per_page       = $request->input('per_page');
        $sort_by        = $request->input('sort_by');
        $sort_order     = $request->input('sort_order');

        //check if page is set
        if($page == null || !is_numeric($page)){
            $page = 1;
        }

        // check if table sort order is exsist, if not set default value "asc"
        if($sort_order == null){
            $sort_order = "asc";
        }

        // check if sort by is set, if not set dafault value "name"
        if($sort_by == null){
            $sort_by = "name";
        }

        //check if the column name is exsist in the table
        if(!Schema::hasColumn('clients', $sort_by)){
            $response = [
                'error' => "$sort_by column is not exsist"
            ];
            return response()->json($response, 200);//201
        }

        //check if the page number is real
        $itemCount = Company::where('guid',$guid)
            ->first()
            ->items()
            ->count();

        $total_amount    = $itemCount;
        $max_pages       = ceil($total_amount/$per_page);
        if($page > $max_pages){
            $response = [
                'error' => "page $page is invalid, the pages range is $page - $max_pages"
            ];
            return response()->json($response, 200);//201
        }

        // check if per_page is set
        if($per_page == null || !is_numeric($per_page)){
            $per_page = 5;
        }

        $company_id = Company::where('guid',$guid)->first()->pluck('id');

        $itemQuery = Item::with('currency')->whereIn('company_id', $company_id);

        if($search_query != ""){
            $itemQuery = $itemQuery->where(function($query) use ($search_query) {
                $query->where('name','LIKE','%'.$search_query.'%')
                    ->orWhere('description','LIKE','%'.$search_query.'%')
                    ->orWhere('price','LIKE','%'.$search_query.'%');
            });

            $search_amount = $itemQuery->count();
        }

        $itemQuery = $itemQuery->skip($per_page*($page-1))->take($per_page);

        $itemQuery = $itemQuery->orderBy($sort_by, $sort_order);

        $response = [
            'total_amount'  => $total_amount,
            'rows'          => $itemQuery->get()
        ];

        if(isset($search_amount)){
            $response['search_amount'] = $search_amount;
        }

        return response()->json($response, 200);//201
    }