<?php

namespace App\Http\Controllers;

use App\Suggestiontrackings;
use Illuminate\Http\Request;
use DB;

class SuggestiontrackingsController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $suggestions_top_keywords = DB::table('suggestions')
      ->join('suggestion_trackings', 'suggestion_trackings.suggestions_id', '=', 'suggestions.id')
      ->select('suggestion_trackings.keywords',DB::raw('count(*) as total_count'))
      ->groupBy('suggestion_trackings.keywords')
      ->orderBy('total_count', 'desc')
      ->limit(30)
      ->get(); 

       $suggestions_top_doc_urls = DB::table('suggestions')
      ->join('suggestion_trackings', 'suggestion_trackings.suggestions_id', '=', 'suggestions.id')
      ->select('suggestions.destination_url',DB::raw('count(*) as total_count'))
      ->groupBy('suggestions.destination_url')
      ->orderBy('total_count', 'desc')
      ->limit(30)
      ->get(); 
 
        return view('suggestiontrackings.index',compact('suggestions_top_keywords','suggestions_top_doc_urls'));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  \App\Suggestiontrackings  $suggestiontrackings
     * @return \Illuminate\Http\Response
     */
    public function show(Suggestiontrackings $suggestiontrackings)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  \App\Suggestiontrackings  $suggestiontrackings
     * @return \Illuminate\Http\Response
     */
    public function edit(Suggestiontrackings $suggestiontrackings)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \App\Suggestiontrackings  $suggestiontrackings
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, Suggestiontrackings $suggestiontrackings)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  \App\Suggestiontrackings  $suggestiontrackings
     * @return \Illuminate\Http\Response
     */
    public function destroy(Suggestiontrackings $suggestiontrackings)
    {
        //
    }
}
