<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\TicketDraft;
use Illuminate\Http\Request;
use App\Models\Ticket\Ticket;
use Auth;
use App\Models\User;
use App\Models\usersettings;
use App\Models\Groupsusers;
use App\Models\Groups;
use DB;
use Artisan;
use Illuminate\Support\Facades\File;
use Spatie\Permission\Models\Role;
use App\Models\Ticket\Category;
use Illuminate\Support\Facades\Schema;

class AdminDashboardController extends Controller
{

    public function index()
    {
        if (Auth::user()->dashboard == 'Admin') {
            return $this->adminDashboard();
        }
        if (Auth::user()->dashboard == 'Employee' || Auth::user()->dashboard == null) {
            return $this->Dashboard();
        }
    }

    //Super Admin Dashboard
    public function adminDashboard()
    {

        $data['user'] = Auth::user()->dashboard;
        // Ticket Counting
        $totaltickets = Ticket::count();
        $data['totaltickets'] = $totaltickets;

        $totalactivetickets = Ticket::whereIn('status', ['Re-Open', 'Inprogress', 'On-Hold'])->count();
        $data['totalactivetickets'] = $totalactivetickets;

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

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

        $selfassigncount = Ticket::where('selfassignuser_id', Auth::id())->whereNotIn('status', ['Closed', 'Suspend'])->count();
        $data['selfassigncount'] = $selfassigncount;

        $myassignedticket = Ticket::leftJoin('ticketassignchildren', 'ticketassignchildren.ticket_id', 'tickets.id')->where('toassignuser_id', Auth::id())->whereNotIn('status', ['Closed', 'Suspend'])->get();
        $myassignedticketcount = 0;
        foreach ($myassignedticket as $recent) {
            if ($recent->toassignuser_id != null) {
                $myassignedticketcount += 1;
            }
        }
        $data['myassignedticketcount'] = $myassignedticketcount;


        $myclosedticketcount = Ticket::where('closedby_user', Auth::id())->count();
        $data['myclosedticketcount'] = $myclosedticketcount;

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

        $alltickets = Ticket::whereIn('status', ['New'])->latest('updated_at')->get();
        $data['alltickets'] = $alltickets;

        $data['gtickets'] = Ticket::whereIn('status', ['New'])->latest('updated_at')->get();

        $suspendticketcount = Ticket::where('status', 'Suspend')->where('lastreply_mail', Auth::id())->count();
        $data['suspendticketcount'] = $suspendticketcount;

        return $data;
    }

    // Employee Dashboard
    public function Dashboard()
    {

        $data['user'] = Auth::user()->dashboard;
        $groups =  Groups::all();

        $group_id = '';
        foreach ($groups as $group) {
            $group_id .= $group->id . ',';
        }


        $groupexists = Groupsusers::whereIn('groups_id', explode(',', substr($group_id, 0, -1)))->where('users_id', Auth::id())->exists();

        $gtickets = Ticket::query();

        if ($groupexists) {
            $totalactivetickets = Ticket::select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['Inprogress', 'Re-Open', 'On-Hold'])
                ->where('status', '!=', 'Closed')
                ->whereNotNull('groups_users.users_id')
                ->whereNull('tickets.myassignuser_id')
                ->whereNull('tickets.selfassignuser_id')
                ->where('groups_users.users_id', Auth::id())
                ->groupBy('tickets.id')
                ->get()
                ->count();


            $data['totalactivetickets'] = $totalactivetickets;

            $totalactiverecent = Ticket::select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['Inprogress', 'Re-Open', 'On-Hold'])
                ->where('replystatus', 'Replied')
                ->whereNotNull('groups_users.users_id')
                ->whereNull('tickets.myassignuser_id')
                ->whereNull('tickets.selfassignuser_id')
                ->where('groups_users.users_id', Auth::id())
                ->groupBy('tickets.id')
                ->get()
                ->count();
            $data['totalactiverecent'] = $totalactiverecent;

            $recentticketcount = Ticket::whereIn('id', function ($query) {
                $query->select('tickets.id')
                    ->from('tickets')
                    ->leftJoin('groups_categories', 'groups_categories.category_id', '=', 'tickets.category_id')
                    ->leftJoin('groups_users', 'groups_users.groups_id', '=', 'groups_categories.group_id')
                    ->where('tickets.status', 'New')
                    ->where(function ($query) {
                        $query->whereNull('tickets.myassignuser_id')
                            ->whereNull('tickets.selfassignuser_id')
                            ->orWhere(function ($query) {
                                $query->whereNotNull('tickets.selfassignuser_id')
                                    ->where('tickets.selfassignuser_id', Auth::id());
                            })
                            ->orWhere(function ($query) {
                                $query->where('tickets.id', 'exists', function ($subquery) {
                                    $subquery->select('toassignuser_id')
                                        ->from('ticket_assign_multiples')
                                        ->whereRaw('ticket_assign_multiples.ticket_id = tickets.id')
                                        ->whereIn('toassignuser_id', [Auth::id()]);
                                });
                            });
                    })
                    ->whereNotNull('groups_users.users_id')
                    ->where('groups_users.users_id', Auth::id());
            })->groupBy('tickets.id')->get()->count();

            $data['recentticketcount'] = $recentticketcount;

            $gtickets = $gtickets->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['New'])
                ->whereNotNull('groups_users.users_id')
                ->where('groups_users.users_id', Auth::id())
                ->where(function ($query) {
                    $query->where(function ($query) {
                        $query->whereNull('tickets.myassignuser_id')
                            ->whereNull('tickets.selfassignuser_id');
                    })
                        ->orWhere(function ($query) {
                            $query->whereNotNull('tickets.selfassignuser_id')
                                ->where('tickets.selfassignuser_id', Auth::id());
                        })
                        ->orWhere(function ($query) {
                            $query->where('tickets.id', 'exists', function ($subquery) {
                                $subquery->select('toassignuser_id')
                                    ->from('ticket_assign_multiples')
                                    ->whereRaw('ticket_assign_multiples.ticket_id = tickets.id')
                                    ->whereIn('toassignuser_id', [Auth::id()]);
                            });
                        });
                })->with('ticketnote', 'category', 'cust', 'ticketassignmutliples', 'selfassign')
                ->latest('tickets.updated_at')
                ->groupBy('tickets.id')
                ->get();

                $ticketDrafts = TicketDraft::whereIn('ticket_id', $gtickets->pluck('id'))->get()->keyBy('ticket_id');

                $gtickets = $gtickets->each(function ($ticket) use ($ticketDrafts) {

                    $ticket->ticketdraft = $ticketDrafts->get($ticket->id);

                    // Get the first assigned user if available
                    if ($ticket->ticketassignmutliples->isNotEmpty()) {
                        $assigneddata = $ticket->ticketassignmutliples->first();
                        $ticket->assigneduserdata = User::find($assigneddata->toassignuser_id); // Add assigned user to ticket
                    } else {
                        $ticket->assigneduserdata = null;
                    }

                    return $ticket;
                });
        }
        // If no there in group we get the all tickets
        else {

            $totalactivetickets = Ticket::select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->leftJoin('groups', 'groups.id', 'groups_users.groups_id') // Add a join to the groups table
                ->whereIn('tickets.status', ['Re-Open', 'Inprogress', 'On-Hold'])
                ->whereNull('tickets.myassignuser_id')
                ->whereNull('tickets.selfassignuser_id')
                ->groupBy('tickets.id')
                ->where(function ($query) {
                    $query->whereNull('groups_users.users_id')
                        ->orWhere(function ($query) {
                            $query->whereNotNull('groups_users.users_id')
                                ->where('groups.groupstatus', 0);
                        });
                })
                ->latest('tickets.updated_at')
                ->get()
                ->count();

            $data['totalactivetickets'] = $totalactivetickets;

            $totalactiverecent = Ticket::select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->leftJoin('groups', 'groups.id', 'groups_users.groups_id') // Add a join to the groups table
                ->whereIn('tickets.status', ['Re-Open', 'Inprogress', 'On-Hold'])
                ->where('replystatus', 'Replied')
                ->whereNull('tickets.myassignuser_id')
                ->whereNull('tickets.selfassignuser_id')
                ->groupBy('tickets.id')
                ->where(function ($query) {
                    $query->whereNull('groups_users.users_id')
                        ->orWhere(function ($query) {
                            $query->whereNotNull('groups_users.users_id')
                                ->where('groups.groupstatus', 0);
                        });
                })
                ->get()
                ->count();

            $data['totalactiverecent'] = $totalactiverecent;

            $recentticketcount = Ticket::select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->leftJoin('groups', 'groups.id', 'groups_users.groups_id') // Add a join to the groups table
                ->where('status', 'New')
                ->where(function ($query) {
                    $query->whereNull('tickets.myassignuser_id')
                        ->whereNull('tickets.selfassignuser_id')
                        ->orWhere(function ($query) {
                            $query->whereNotNull('tickets.selfassignuser_id')
                                ->where('tickets.selfassignuser_id', Auth::id());
                        })
                        ->orWhere(function ($query) {
                            $query->where('tickets.id', 'exists', function ($subquery) {
                                $subquery->select('toassignuser_id')
                                    ->from('ticket_assign_multiples')
                                    ->whereRaw('ticket_assign_multiples.ticket_id = tickets.id')
                                    ->whereIn('toassignuser_id', [Auth::id()]);
                            });
                        });
                })
                ->groupBy('tickets.id')
                ->where(function ($query) {
                    $query->whereNull('groups_users.users_id')
                        ->orWhere(function ($query) {
                            $query->whereNotNull('groups_users.users_id')
                                ->where('groups.groupstatus', 0);
                        });
                })
                ->get()
                ->count();

            $data['recentticketcount'] = $recentticketcount;

            $gtickets = $gtickets->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->leftJoin('groups', 'groups.id', 'groups_users.groups_id')
                ->whereIn('tickets.status', ['New'])
                ->where(function ($query) {
                    $query->where(function ($query) {
                        $query->whereNull('tickets.myassignuser_id')
                            ->whereNull('tickets.selfassignuser_id');
                    })
                        ->orWhere(function ($query) {
                            $query->whereNotNull('tickets.selfassignuser_id')
                                ->where('tickets.selfassignuser_id', Auth::id());
                        })
                        ->orWhere(function ($query) {
                            $query->where('tickets.id', 'exists', function ($subquery) {
                                $subquery->select('toassignuser_id')
                                    ->from('ticket_assign_multiples')
                                    ->whereRaw('ticket_assign_multiples.ticket_id = tickets.id')
                                    ->whereIn('toassignuser_id', [Auth::id()]);
                            });
                        });
                })
                ->where(function ($query) {
                    $query->whereNull('groups_users.users_id')
                        ->orWhere(function ($query) {
                            $query->whereNotNull('groups_users.users_id')
                                ->where('groups.groupstatus', 0);
                        });
                })->with('ticketnote', 'category', 'cust', 'ticketassignmutliples', 'selfassign')
                ->latest('tickets.updated_at')
                ->groupBy('tickets.id')
                ->get();

                $ticketDrafts = TicketDraft::whereIn('ticket_id', $gtickets->pluck('id'))->get()->keyBy('ticket_id');

                $gtickets = $gtickets->each(function ($ticket) use ($ticketDrafts) {
     
                    $ticket->ticketdraft = $ticketDrafts->get($ticket->id);

                    // Get the first assigned user if available
                    if ($ticket->ticketassignmutliples->isNotEmpty()) {
                        $assigneddata = $ticket->ticketassignmutliples->first();
                        $ticket->assigneduserdata = User::find($assigneddata->toassignuser_id); // Add assigned user to ticket
                    } else {
                        $ticket->assigneduserdata = null;
                    }

                    return $ticket;
                });
        }


         $data['ticketdata'] = $gtickets;

        $selfassigncount = Ticket::where('selfassignuser_id', Auth::id())->where('status', '!=', 'Closed')->where('status', '!=', 'Suspend')->count();
        $data['selfassigncount'] = $selfassigncount;


        $selfassignrecentreply = Ticket::where('selfassignuser_id', Auth::id())->where('replystatus', 'Replied')->where('status', '!=', 'Closed')->count();
        $data['selfassignrecentreply'] = $selfassignrecentreply;

        $myassignedticketcount = Ticket::leftJoin('ticketassignchildren', 'ticketassignchildren.ticket_id', 'tickets.id')->where('toassignuser_id', Auth::id())->where('status', '!=', 'Closed')->where('status', '!=', 'Suspend')->count();
        $data['myassignedticketcount'] = $myassignedticketcount;

        $myassignedticketrecentreply = Ticket::leftJoin('ticketassignchildren', 'ticketassignchildren.ticket_id', 'tickets.id')->where('toassignuser_id', Auth::id())->where('status', '!=', 'Closed')->where('replystatus', 'Replied')->count();
        $data['myassignedticketrecentreply'] = $myassignedticketrecentreply;


        $myclosedticketcount = Ticket::where('closedby_user', Auth::id())->count();
        $data['myclosedticketcount'] = $myclosedticketcount;

        $suspendticketcount = Ticket::where('status', 'Suspend')->where('lastreply_mail', Auth::id())->count();
        $data['suspendticketcount'] = $suspendticketcount;

        return $data;
    }

    public function dashboardtabledata(Request $request)
    {
        if (Auth::user()->dashboard == 'Admin') {
            return $this->adminDashboardtabledata($request);
        }
        if (Auth::user()->dashboard == 'Employee' || Auth::user()->dashboard == null) {
            return $this->Dashboardtabledatas($request);
        }
    }

    //superadmin dashborad
    public function adminDashboardtabledata($request)
    {
        $ticketquery = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')
            ->whereIn('status', ['New'])
            ->latest('updated_at');

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

        if ($searchTerm) {
            $ticketquery->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $ticketquery->paginate($perPage);


        $data['queryList'] = $items;
        $data['ticketnote'] = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();

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

        $category = Category::get();
        $data['category'] = $category;
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $data['user'] = $user;
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }
        $mailnotify = auth()->user()->unreadNotifications()->where('data->status', 'mail')->get();
        if($mailnotify){
            $mailnotify->transform(function ($groups) {
                if (isset($groups->data['ticket_id'])) {
                    $groups->enc_ticket_id = encrypt($groups->data['ticket_id']);
                }
                return $groups;
            });
        }
        $data['mailnotify'] = $mailnotify;
        
        $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');

        return $data;
    }

    //Employee dashboard
    public function Dashboardtabledatas($request)
    {
        $groups =  Groups::all();

        $group_id = '';
        foreach ($groups as $group) {
            $group_id .= $group->id . ',';
        }


        $groupexists = Groupsusers::whereIn('groups_id', explode(',', substr($group_id, 0, -1)))->where('users_id', Auth::id())->exists();


        // if there in group get group tickets
        if ($groupexists) {

            // All tickets
            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['New'])
                ->whereNotNull('groups_users.users_id')
                ->where('groups_users.users_id', Auth::id())
                ->whereIn('status', ['New'])
                ->latest('tickets.updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;
            // ticket note
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        // If no there in group we get the all tickets
        else {

            $query =  Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['New'])
                ->whereNull('groups_users.users_id')
                ->whereNull('tickets.myassignuser_id')
                ->whereNull('tickets.selfassignuser_id')
                ->latest('tickets.updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $ticketquery->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;

            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }

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

        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $data['user'] = $user;
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }
        $mailnotify = auth()->user()->unreadNotifications()->where('data->status', 'mail')->get();
        
        $mailnotify->transform(function ($groups) {
            if (isset($groups->data['ticket_id'])) {
                $groups->enc_ticket_id = encrypt($groups->data['ticket_id']);
            }
            return $groups;
        });
        $data['mailnotify'] = $mailnotify;

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

        return $data;
    }

    public function activeticket(Request $request)
    {
        if (Auth::user()->dashboard == 'Admin') {
            return $this->adminallactiveticket($request);
        }
        if (Auth::user()->dashboard == 'Employee' || Auth::user()->dashboard == null) {
            return $this->employeeallactiveticket($request);
        }
    }

    public function adminallactiveticket($request)
    {
        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->whereIn('status', ['Re-Open', 'Inprogress', 'On-Hold'])->latest('updated_at');
        
        // ->whereNull('tickets.myassignuser_id')
        // ->whereNull('tickets.selfassignuser_id')
        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }
        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


        $data['queryList'] = $items;

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

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

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

        $allactiveassignedtickets = Ticket::whereIn('status', ['Re-Open', 'Inprogress', 'On-Hold'])->where(function ($r) {
            $r->whereNotNull('myassignuser_id')
                ->orWhereNotNull('selfassignuser_id');
        })->count();
        $data['allactiveassignedtickets'] = $allactiveassignedtickets;

        $allactiveoverduetickets = Ticket::whereIn('status', ['Re-Open', 'Inprogress', 'On-Hold'])->whereNotNull('overduestatus')->count();
        $data['allactiveoverduetickets'] = $allactiveoverduetickets;

        $data['setting'] = response()->json(['date_format' => setting('date_format')]);
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }
        $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');

        return $data;
    }

    public function employeeallactiveticket($request)
    {


        $groups =  Groups::where('groupstatus', '1')->get();


        $group_id = '';
        foreach ($groups as $group) {
            $group_id .= $group->id . ',';
        }

        $groupexists = Groupsusers::whereIn('groups_id', explode(',', substr($group_id, 0, -1)))->where('users_id', Auth::id())->exists();


        // if there in group get group tickets
        if ($groupexists) {
            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['Re-Open', 'Inprogress', 'On-Hold'])
                ->whereNotNull('groups_users.users_id')
                ->where('groups_users.users_id', Auth::id())->groupBy('tickets.id')
                ->where(function ($query) {
                    $query->whereNull('tickets.selfassignuser_id')
                        ->whereNull('tickets.myassignuser_id');
                })
                ->latest('tickets.updated_at');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        // If no there in group we get the all tickets
        else {
            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->leftJoin('groups', 'groups.id', 'groups_users.groups_id') // Add a join to the groups table
                ->whereIn('tickets.status', ['Re-Open', 'Inprogress', 'On-Hold'])
                ->groupBy('tickets.id')
                ->where(function ($query) {
                    $query->whereNull('groups_users.users_id')
                        ->orWhere(function ($query) {
                            $query->whereNotNull('groups_users.users_id')
                                ->where('groups.groupstatus', 0);
                        });
                })
                ->where(function ($query) {
                    $query->whereNull('tickets.selfassignuser_id')
                        ->whereNull('tickets.myassignuser_id');
                })
                ->latest('tickets.updated_at');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;

            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        $data['setting'] = response()->json(['date_format' => setting('date_format')]);
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }
        $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');
        return $data;
    }

    // Superadmin all closed Ticket
    public function closedticket(Request $request)
    {
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->where('status', 'Closed')
            ->latest('updated_at');

        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }

        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


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

    public function assignedTickets(Request $request)
    {
        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->where('myassignuser_id', Auth::id())->whereNull('selfassignuser_id')->latest('updated_at');

        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }
        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


        $data['queryList'] = $items;

        $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
        $data['ticketnote'] = $ticketnote;

        $active = Ticket::whereIn('status', ['New', 'Re-Open', 'Inprogress'])->get();

        $closed = Ticket::where('status', 'Closed')->get();

        $agent = User::count();
        $data['agent'] = $agent;

        $customer = User::count();
        $data['customer'] = $customer;


        $assignedticketsnew = Ticket::where('myassignuser_id', Auth::id())->whereNull('selfassignuser_id')->where('status', 'New')->count();
        $data['assignedticketsnew'] = $assignedticketsnew;

        $assignedticketsinprogress = Ticket::where('myassignuser_id', Auth::id())->whereNull('selfassignuser_id')->where('status', 'Inprogress')->count();
        $data['assignedticketsinprogress'] = $assignedticketsinprogress;

        $assignedticketsonhold = Ticket::where('myassignuser_id', Auth::id())->whereNull('selfassignuser_id')->where('status', 'On-Hold')->count();
        $data['assignedticketsonhold'] = $assignedticketsonhold;

        $assignedticketsreopen = Ticket::where('myassignuser_id', Auth::id())->whereNull('selfassignuser_id')->where('status', 'Re-Open')->count();
        $data['assignedticketsreopen'] = $assignedticketsreopen;

        $assignedticketsoverdue = Ticket::where('myassignuser_id', Auth::id())->whereNull('selfassignuser_id')->where('overduestatus', 'Overdue')->count();
        $data['assignedticketsoverdue'] = $assignedticketsoverdue;

        $assignedticketsclosed = Ticket::where('myassignuser_id', Auth::id())->whereNull('selfassignuser_id')->where('status', 'Closed')->count();
        $data['assignedticketsclosed'] = $assignedticketsclosed;

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

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

        return $data;
    }

    public function myassignedTickets(Request $request)
    {
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $data['authId'] = $user->id;
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $query =  Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', 'ticketassignchildren.toassignuser_id')->whereNull('selfassignuser_id')->leftjoin('ticketassignchildren', 'ticketassignchildren.ticket_id', 'tickets.id')->where('status', '!=', 'Closed')->where('status', '!=', 'Suspend')->where('toassignuser_id', Auth::id())->latest('updated_at');

        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }
        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


        $data['queryList'] = $items;

        $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
        $data['ticketnote'] = $ticketnote;


        $active = Ticket::whereIn('status', ['New', 'Re-Open', 'Inprogress'])->get();

        $closed = Ticket::where('status', 'Closed')->get();

        $agent = User::count();
        $data['agent'] = $agent;

        $customer = User::count();
        $data['customer'] = $customer;

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

        return $data;
    }

    public function onholdticket(Request $request)
    {
        if (Auth::user()->dashboard == 'Admin') {
            return $this->adminonholdticket($request);
        }
        if (Auth::user()->dashboard == 'Employee' || Auth::user()->dashboard == null) {
            return $this->employeeonholdticket($request);
        }
    }

    public function adminonholdticket($request)
    {
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->where('status', 'On-Hold')->latest('updated_at');

        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }
        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


        $data['queryList'] = $items;

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

        return $data;
    }

    public function employeeonholdticket($request)
    {

        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }


        $groupexists = Groupsusers::where('users_id', Auth::id())->exists();

        // if there in group get group tickets
        if ($groupexists) {

            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->where('status', 'On-Hold')
                ->whereNotNull('groups_users.users_id')
                ->where('groups_users.users_id', Auth::id())
                ->latest('updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        // If no there in group we get the all tickets
        else {

            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->where('status', 'On-Hold')
                ->whereNull('groups_users.users_id')
                ->latest('updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }
            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        $data['setting'] = response()->json(['date_format' => setting('date_format')]);
        $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');

        return $data;
    }

    public function overdueticket(Request $request)
    {

        if (Auth::user()->dashboard == 'Admin') {
            return $this->adminoverdueticket($request);
        }
        if (Auth::user()->dashboard == 'Employee' || Auth::user()->dashboard == null) {
            return $this->employeeoverdueticket($request);
        }
    }


    public function adminoverdueticket($request)
    {
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->whereIn('overduestatus', ['Overdue'])
            ->latest('updated_at');

        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }
        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


        $data['queryList'] = $items;
        $data['setting'] = response()->json(['date_format' => setting('date_format')]);
        $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');

        return $data;
    }


    public function employeeoverdueticket($request)
    {

        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $groupexists = Groupsusers::where('users_id', Auth::id())->exists();

        // if there in group get group tickets
        if ($groupexists) {
            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('overduestatus', ['Overdue'])
                ->whereNotNull('groups_users.users_id')
                ->where('groups_users.users_id', Auth::id())
                ->latest('updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        // If no there in group we get the all tickets
        else {

            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('overduestatus', ['Overdue'])
                ->whereNull('groups_users.users_id')
                ->latest('updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);


            $data['queryList'] = $items;
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }

        $tickets = Ticket::whereIn('overduestatus', ['Overdue'])->get();

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

        return $data;
    }


    public function adminallassignedtickets(Request $request)
    {
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')
            ->latest('updated_at')
            ->orWhereNotNull('myassignuser_id')
            ->orWhereNotNull('selfassignuser_id');
        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }

        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);
        $data['queryList'] = $items;

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

    public function recenttickets(Request $request)
    {

        if (Auth::user()->dashboard == 'Admin') {
            return $this->adminrecentticket($request);
        }
        if (Auth::user()->dashboard == 'Employee' || Auth::user()->dashboard == null) {
            return $this->employeerecentticket($request);
        }
    }

    public function adminrecentticket($request)
    {
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->whereIn('status', ['New'])
            ->latest('updated_at');

        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }
        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


        $data['queryList'] = $items;

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


    public function employeerecentticket($request)
    {

        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $groupexists = Groupsusers::where('users_id', Auth::id())->exists();
        // if there in group get group tickets
        if ($groupexists) {
            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['New'])
                ->whereNotNull('groups_users.users_id')
                ->where('groups_users.users_id', Auth::id())
                ->whereIn('status', ['New'])
                ->latest('tickets.updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);

            $data['queryList'] = $items;
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        // If no there in group we get the all tickets
        else {
            $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->select('tickets.*', "groups_categories.group_id", "groups_users.users_id")
                ->leftJoin('groups_categories', 'groups_categories.category_id', 'tickets.category_id')
                ->leftJoin('groups_users', 'groups_users.groups_id', 'groups_categories.group_id')
                ->whereIn('tickets.status', ['New'])
                ->whereNull('groups_users.users_id')
                ->whereIn('status', ['New'])
                ->where('myassignuser_id', null)
                ->where('selfassignuser_id', null)
                ->latest('tickets.updated_at')->groupBy('tickets.id');

            $category      = $request['categories'] ?? null;
            $user  = $request['user'] ?? null;
            $status  = $request['status'] ?? null;
            $searchTerm = $request['filter'] ?? null;
            $perPage    = $request['per_page'] ?? 15;
            $page       = $request['page'] ?? 1;
            // filter by category
            if ($category && $category != 'null') {
                $query->where('category_id', $category);
            }

            // get only tickets that specified user has created
            if ($user && $user != 'null') {
                $query->WhereHas('cust', function ($query) use ($user) {
                    $query->where('userType', $user);
                });
            }
            // get only tickets that specified user has created
            if ($status && $status != 'null') {
                $query->where('status', $status);
            }

            if ($searchTerm) {
                $query->where(function ($query) use ($searchTerm) {
                    $columns = Schema::getColumnListing('tickets');
                    foreach ($columns as $column) {
                        $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                    }
                    $query->orWhereHas('category', function ($query) use ($searchTerm) {
                        $query->where('name', 'LIKE', $searchTerm . '%');
                    })->orWhereHas('cust', function ($query) use ($searchTerm) {
                        $query->where('username', 'LIKE', $searchTerm . '%');
                    })
                        ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                            $query->where(function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                                // Check for 'self' in the search term
                                if (stripos('self', strtolower($searchTerm)) !== false) {
                                    // Also check if the selfassignuser_id is not null
                                    $query->orWhere('selfassignuser_id', '!=', null);
                                }
                            });
                        })
                        ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                            // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                            $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                                $query->where('name', 'LIKE', "%{$searchTerm}%");
                            });
                            if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                                $query->orWhere('ticket_id', '!=', null);
                            }
                        });
                });
            }
            $items = $query->paginate($perPage);

            $data['queryList'] = $items;
            $ticketnote = DB::table('ticketnotes')->pluck('ticketnotes.ticket_id')->toArray();
            $data['ticketnote'] = $ticketnote;
        }
        $data['setting'] = response()->json(['date_format' => setting('date_format')]);
        $data['timezonedata'] = Auth::user()->timezone ?? setting('default_timezone');
        return $data;
    }

    public function markNotification(Request $request)
    {
        auth()->user()
            ->unreadNotifications
            ->when($request->input('id'), function ($query) use ($request) {
                return $query->where('id', $request->input('id'));
            })
            ->markAsRead();

        return response()->noContent();
    }


    public function autorefresh(Request $request, $id)
    {

        $calID = User::with('usetting')->find($id);
        if ($calID->usetting == null) {
            $usersettings = new usersettings();
            $usersettings->users_id = $request->id;
            $usersettings->ticket_refresh = $request->status;
            $usersettings->save();
        } else {
            $calID->usetting->ticket_refresh = $request->status;
            $calID->usetting->save();
        }

        return response()->json(['code' => 200, 'success' => 'Updated successfully'], 200);
    }


    public function summernoteimageupload(Request $request)
    {

        $files = $request->file('image');

        $destinationPath = public_path() . "" . '/uploads/data/'; // upload path
        $profileImage = date('YmdHis') . "." . $files->getClientOriginalExtension();
        $path = $files->move($destinationPath, $profileImage);

        $destinationPath1 = url('/') . '/uploads/data/' . $profileImage;
        return response()->json(['code' => 200, 'data' => $destinationPath1,], 200);
    }


    public function Notificationview($id)
    {

        $notification = auth()->user()->notifications()->where('id', $id)->firstOrFail();
        
        $data['notifications'] = $notification;

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

        return $data;
    }


    public function notifydelete(Request $request)
    {

        $id = $request->id;

        $notificationsdelete = auth()->user()->notifications()->find($id);
        $notificationsdelete->delete();

        return response()->json(['success' => 'Deleted successfully', 200]);
    }

    public function markallnotify()
    {

        auth()->user()->unreadNotifications->markAsRead();

        return response()->noContent();
    }

    public function clearcache()
    {

        Artisan::call('optimize:clear');
        // $log_path = storage_path('logs/laravel.log');
        // $geo_path = storage_path('logs/geoip.log');

        // if (File::exists($log_path)) {
        //     File::delete($log_path);
        // }
        // if (File::exists($geo_path)) {
        //     File::delete($geo_path);
        // }
        $log_path = storage_path('logs/laravel.log');
        $geo_path = storage_path('logs/geoip.log');
        
        if (file_exists($log_path)) {
            file_put_contents($log_path, '', LOCK_EX);
        }
        if (file_exists($geo_path)) {
            file_put_contents($geo_path, '', LOCK_EX);
        }

        return response()->json(['success' => 'Cache Clear Successfull']);
    }

    public function suspendedtickets(Request $request)
    {
        $user = User::with('usetting')->findOrFail(Auth::user()->id);
        $role = Role::where('name', $user->getRoleNames()[0])->get();
        foreach ($role as $roles) {
            $data['permissions'] = $roles->getPermissionNames();
        }

        $query = Ticket::with('cust', 'ticketdraft', 'ticketnote', 'ticketassignmutliples', 'selfassign', 'category', 'users')->where('status', 'Suspend')->latest('updated_at');

        $category      = $request['categories'] ?? null;
        $user  = $request['user'] ?? null;
        $status  = $request['status'] ?? null;
        $searchTerm = $request['filter'] ?? null;
        $perPage    = $request['per_page'] ?? 15;
        $page       = $request['page'] ?? 1;
        // filter by category
        if ($category && $category != 'null') {
            $query->where('category_id', $category);
        }

        // get only tickets that specified user has created
        if ($user && $user != 'null') {
            $query->WhereHas('cust', function ($query) use ($user) {
                $query->where('userType', $user);
            });
        }
        // get only tickets that specified user has created
        if ($status && $status != 'null') {
            $query->where('status', $status);
        }

        if ($searchTerm) {
            $query->where(function ($query) use ($searchTerm) {
                $columns = Schema::getColumnListing('tickets');
                foreach ($columns as $column) {
                    $query->orWhere($column, 'LIKE', "%{$searchTerm}%");
                }
                $query->orWhereHas('category', function ($query) use ($searchTerm) {
                    $query->where('name', 'LIKE', $searchTerm . '%');
                })->orWhereHas('cust', function ($query) use ($searchTerm) {
                    $query->where('username', 'LIKE', $searchTerm . '%');
                })
                    ->orWhereHas('selfassign', function ($query) use ($searchTerm) {
                        $query->where(function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                            // Check for 'self' in the search term
                            if (stripos('self', strtolower($searchTerm)) !== false) {
                                // Also check if the selfassignuser_id is not null
                                $query->orWhere('selfassignuser_id', '!=', null);
                            }
                        });
                    })
                    ->orWhereHas('ticketassignmutliples', function ($query) use ($searchTerm) {
                        // Assuming you defined the 'assignedUser()' relationship in ticketassignchild model
                        $query->whereHas('toassignuser', function ($query) use ($searchTerm) {
                            $query->where('name', 'LIKE', "%{$searchTerm}%");
                        });
                        if (stripos('multi Assign', strtolower($searchTerm)) !== false) {
                            $query->orWhere('ticket_id', '!=', null);
                        }
                    });
            });
        }
        $items = $query->paginate($perPage);


        $data['queryList'] = $items;

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