<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Pages;
use App\Models\User;
use App\Models\Customer;
use App\Models\Ticket\Ticket;
use App\Models\Userrating;
use App\Models\Employeerating;
use App\Models\Articles\Article;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use App\Models\EmployeeActivity;
use Auth;
use Carbon\Carbon;

class AdminReportController extends Controller
{
   public function index()
   {

      $this->authorize('Reports Access');

      $users = $users = User::latest('updated_at')
         ->with('roles', 'comments', 'employeeratings')
         ->withCount(['comments as comentticketss' => function ($query) {
            $query->select(DB::raw('COUNT(DISTINCT ticket_id)'));
         }])
         ->withCount('employeeratings')
         ->addSelect([
            'rating' => Employeerating::selectRaw('SUM(CASE rating WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 END) / COUNT(*)')
               ->whereColumn('user_id', 'users.id')
         ])
         ->take(6)
         ->get();
      $data['users'] = $users;


      $post = Pages::all();
      $data['page'] = $post;

      $agentactivec = User::where('status', '1')->count();
      $data['agentactivec'] = $agentactivec;
      $agentinactive = User::where('status', '0')->count();
      $data['agentinactive'] = $agentinactive;

      $customeractive = Customer::where('status', '1')->count();
      $data['customeractive'] = $customeractive;
      $customerinactive = Customer::where('status', '0')->count();
      $data['customerinactive'] = $customerinactive;

      $newticket = Ticket::where('status', 'New')->count();
      $data['newticket'] = $newticket;

      $closedticket = Ticket::where('status', 'Closed')->count();
      $data['closedticket'] = $closedticket;

      $inprogressticket = Ticket::where('status', 'Inprogress')->count();
      $data['inprogressticket'] = $inprogressticket;

      $onholdticket = Ticket::where('status', 'On-Hold')->count();
      $data['onholdticket'] = $onholdticket;

      $reopenticket = Ticket::where('status', 'Re-Open')->count();
      $data['reopenticket'] = $reopenticket;

      $prioritylow = Ticket::where('priority', 'Low')->count();
      $data['prioritylow'] = $prioritylow;

      $priorityhigh = Ticket::where('priority', 'High')->count();
      $data['priorityhigh'] = $priorityhigh;

      $prioritymedium = Ticket::where('priority', 'Medium')->count();
      $data['prioritymedium'] = $prioritymedium;

      $prioritycritical = Ticket::where('priority', 'Critical')->count();
      $data['prioritycritical'] = $prioritycritical;

      $articlepublished = Article::where('status', 'Published')->count();
      $data['articlepublished'] = $articlepublished;

      $articleunpublished = Article::where('status', 'UnPublished')->count();
      $data['articleunpublished'] = $articleunpublished;

      $activity = EmployeeActivity::with('user')->latest('created_at')->take(6)->get();
      $data['activity'] = $activity;

      $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');

      $data['setting'] = response()->json(['date_format' => setting('date_format'), 'time_format' => setting('time_format')]);

      return $data;
   }

   public function ticketreports(Request $request)
   {
      $users = User::with(['roles', 'comments' => function ($query) {
         $query->where('user_id', '!=', null);
      }])
         ->withCount(['employeeratings as rating' => function ($query) {
            $query->selectRaw('(5 * COUNT(CASE WHEN rating = 5 THEN 1 END) +
                                4 * COUNT(CASE WHEN rating = 4 THEN 1 END) +
                                3 * COUNT(CASE WHEN rating = 3 THEN 1 END) +
                                2 * COUNT(CASE WHEN rating = 2 THEN 1 END) +
                                1 * COUNT(CASE WHEN rating = 1 THEN 1 END)) / COUNT(*)');
         }, 'employeeratings as overallcount'])
         ->withCount(['comments as comentticketss' => function ($query) {
            $query->distinct('comments.ticket_id');
         }])
         ->latest();


      $searchTerm = $request['filter'] ?? null;
      $perPage    = $request['per_page'] ?? 15;
      $page       = $request['page'] ?? 1;

      if ($searchTerm) {
         $users->where(function ($query) use ($searchTerm) {
            $columns = Schema::getColumnListing('users');
            foreach ($columns as $column) {
               $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
            }
            $query->orWhereHas('roles', function ($query) use ($searchTerm) {
               $query->where('name', 'LIKE', $searchTerm . '%');
            });
         });
      }
      $items = $users->paginate($perPage);
      $data['queryList'] = $items;

      $post = Pages::all();
      $data['page'] = $post;

      $activity = EmployeeActivity::latest('created_at')->paginate(6);
      $data['activity'] = $activity;
      return $data;
   }

   public function employeedetails($id)
   {

      $users = User::with('roles')->find($id);
      $data['users'] = $users;

      $employeerating = Ticket::select('tickets.*')->leftJoin('comments', 'comments.ticket_id', 'tickets.id')
         ->where('comments.user_id', $users->id)->distinct('comments.ticket_id', 'tickets.id')->get();

      $emprating = [];
      foreach ($employeerating as $ticket) {
         $avgrating1 = Userrating::where('ticket_id', $ticket->id)->where('ratingstar', '1')->count();
         $avgrating2 = Userrating::where('ticket_id', $ticket->id)->where('ratingstar', '2')->count();
         $avgrating3 = Userrating::where('ticket_id', $ticket->id)->where('ratingstar', '3')->count();
         $avgrating4 = Userrating::where('ticket_id', $ticket->id)->where('ratingstar', '4')->count();
         $avgrating5 = Userrating::where('ticket_id', $ticket->id)->where('ratingstar', '5')->count();
         $avgr = ((5 * $avgrating5) + (4 * $avgrating4) + (3 * $avgrating3) + (2 * $avgrating2) + (1 * $avgrating1));
         $avggr = ($avgrating1 + $avgrating2 + $avgrating3 + $avgrating4 + $avgrating5);

         if ($avggr == 0) {
            $avggr = 1;
            $avg = $avgr / $avggr;
         } else {
            $avg = $avgr / $avggr;
         }

         $rating = $avg;

         $ticketdata = Userrating::with('customer')->where('ticket_id', $ticket->id)->get();

         $ticket['rating'] = $rating;
         $ticket['ticketdata'] = $ticketdata;

         array_push($emprating, $ticket);
      }
      $activity = EmployeeActivity::latest('created_at')->paginate(6);
      $data['activity'] = $activity;


      $data['employeeratings'] = $emprating;
      return $data;
   }

   public function ratingticketdelete($id)
   {
      $ticketratingdelete = Userrating::where('ticket_id', $id)->first();

      try {
         $employeeratingdelete = Employeerating::where('urating_id', $ticketratingdelete->id)->get();
         foreach ($employeeratingdelete as $employeesrating) {
            $employeesrating->delete();
         }
         $ticketratingdelete->delete();

         return response()->json(['success' => 'Delete successfully']);
      } catch (\Exception $e) {
         return response()->json(['error' => $e->getMessage()]);
      }
   }
   public function employeeActivity(Request $request)
   {
      $this->authorize('Reports Access');

      $employeerating = EmployeeActivity::with('user')->latest('created_at');

      $searchTerm = $request['filter'] ?? null;
      $perPage    = $request['per_page'] ?? 15;
      $date       = $request['date'] ?? null;
      $page       = $request['page'] ?? 1;
      $employeerating->where(function ($query) use ($searchTerm, $date) {
         if ($searchTerm && $searchTerm != "null") {
            $columns = array_merge(Schema::getColumnListing('employee_activities'), Schema::getColumnListing('user'));
            foreach ($columns as $column) {
               $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
            }
         }
      });
      if ($date && $date != "null") {
         $employeerating->whereRaw("DATE(employee_activities.created_at) = ?", [Carbon::parse($date)->toDateString()]);
      }
      $items = $employeerating->paginate($perPage);
      $data['queryList'] = $items;

      $data['setting'] = response()->json(['date_format' => setting('date_format'), 'time_format' => setting('time_format')]);
      $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');
      return $data;
   }
}
