<?php

namespace App\Http\Controllers;

use App\Models\Employee;
use App\Models\Pop;
use App\Models\Token;
use App\Models\TokenCode;
use Brian2694\Toastr\Toastr;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\Facades\DataTables;

class TokenDashBoadController extends Controller
{
    public function tokenDashboard()
    {

        // last month start and end date
        $last_month_start_date = now()->subMonth()->startOfMonth()->format('Y-m-d 00:00:00');
        $last_month_end_date = now()->subMonth()->endOfMonth()->format('Y-m-d 23:59:59');

        // this month start and end date
        $this_month_start_date = now()->startOfMonth()->format('Y-m-d 00:00:00');
        $this_month_end_date = now()->endOfMonth()->format('Y-m-d 23:59:59');

        // group by pop and count
        $total_not_close_token_group_by_pop = Token::with('clients.pop')
            ->join('clients', 'tokens.clientId', '=', 'clients.id')
            ->whereNull('tokens.closeUserId')
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');

        $total_create_token_group_by_pop = Token::with('clients.pop')
            ->join('clients', 'tokens.clientId', '=', 'clients.id')
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');

        $total_create_token_last_month_group_by_pop = Token::with('clients.pop')
            ->join('clients', 'tokens.clientId', '=', 'clients.id')
            ->whereBetween('tokens.created_at', [$last_month_start_date, $last_month_end_date])
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');

        $total_create_token_this_month_group_by_pop = Token::with('clients.pop')
            ->join('clients', 'tokens.clientId', '=', 'clients.id')
            ->whereBetween('tokens.created_at', [$this_month_start_date, $this_month_end_date])
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');

        $total_not_close_token_but_assign_token_group_by_pop = Token::with('clients.pop')
            ->join('clients', 'tokens.clientId', '=', 'clients.id')
            ->whereNull('tokens.closeUserId')
            ->whereNotNull('tokens.assign_time')
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');
        $total_not_close_token_and_not_assign_token_group_by_pop = Token::with('clients.pop')
            ->join('clients', 'tokens.clientId', '=', 'clients.id')
            ->whereNull('tokens.closeUserId')
            ->whereNull('tokens.assign_time')
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');

        $total_not_close_token_and_assign_and_null_work_start_time_token_group_by_pop = Token::with('clients.pop')
            ->join('clients', 'tokens.clientId', '=', 'clients.id')
            ->whereNull('tokens.closeUserId')
            ->whereNotNull('tokens.assign_time')
            ->whereNull('tokens.work_start_time')
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');


        $pops = Pop::poplist()->pluck('popname', 'id');


        $token_assign_but_not_solve_till_now_as_per_token_code_solve_time_from_assign_time_group_pop_id = Token::join('clients', 'tokens.clientId', '=', 'clients.id')
            ->leftJoin('token_codes', 'tokens.tokenCode', '=', 'token_codes.id')
            ->where('token_codes.solve_time', '>', 0)
            ->whereNotNull('tokens.assign_time')
            ->whereRaw('TIMESTAMPDIFF(MINUTE, tokens.assign_time, NOW()) >= token_codes.solve_time')
            ->whereNull('tokens.closeUserId')
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');

        $all_the_tokens_thats_are_not_close_but_assign_but_no_notes_added_more_than_one_hour_group_pop_id = Token::join('clients', 'tokens.clientId', '=', 'clients.id')
            ->leftJoin(DB::raw('(
                SELECT token_id, MAX(created_at) as last_note_time
                FROM token_notes
                GROUP BY token_id
            ) as latest_notes'), 'tokens.id', '=', 'latest_notes.token_id')
            ->whereNotNull('tokens.assign_time')
            ->whereNull('tokens.closeUserId')
            ->whereRaw('COALESCE(latest_notes.last_note_time, tokens.assign_time) < ?', [now()->subHour()])
            ->groupBy('clients.pop_id')
            ->select('clients.pop_id', DB::raw('COUNT(*) as total'))
            ->pluck('total', 'pop_id');


        $data = [
            'total_not_close_token_group_by_pop' => $total_not_close_token_group_by_pop,
            'total_create_token_group_by_pop' => $total_create_token_group_by_pop,
            'total_not_close_token_but_assign_token_group_by_pop' => $total_not_close_token_but_assign_token_group_by_pop,
            'total_not_close_token_and_not_assign_token_group_by_pop' => $total_not_close_token_and_not_assign_token_group_by_pop,
            'token_assign_but_not_solve_till_now_as_per_token_code_solve_time_from_assign_time_group_pop_id' => $token_assign_but_not_solve_till_now_as_per_token_code_solve_time_from_assign_time_group_pop_id,
            'all_the_tokens_thats_are_not_close_but_assign_but_no_notes_added_more_than_one_hour_group_pop_id' => $all_the_tokens_thats_are_not_close_but_assign_but_no_notes_added_more_than_one_hour_group_pop_id,
            'total_not_close_token_and_assign_and_null_work_start_time_token_group_by_pop' => $total_not_close_token_and_assign_and_null_work_start_time_token_group_by_pop,
            'pops' => $pops,
            'total_create_token_last_month_group_by_pop' => $total_create_token_last_month_group_by_pop,
            'total_create_token_this_month_group_by_pop' => $total_create_token_this_month_group_by_pop,
        ];

        return view('token.dashboad.dashboad', $data);
    }

    public function tokenDetailReport($pop_id, $type)
    {
        $data = [
            'employees' => Employee::where('status', 'active')->orderBy('name', 'ASC')->get(),
            'pop_id' => $pop_id,
            'type' => $type,
        ];

        return view('token.dashboad.detail-report', $data);
    }

    public function tokenDetailReportData(Request $request, $pop_id, $type)
    {
        //check pop is accessable by user or not
        $popCheck = Pop::list()->pluck('id')->contains($pop_id);
        if (!$popCheck) {
            Toastr::error("You do not have permission to access this page", "Error");
            return redirect()->route('token-dashboard');
        }

        $query = $this->getTokenDetailQuery($pop_id, $type);
        $employees = Employee::where('status', 'active')->orderBy('name', 'ASC')->get();
        $mikrotik_online_users = $request->get('mikrotik_online_users', []);

        return DataTables::of($query)
            ->filter(function ($query) use ($request) {
                $search = $request->get('search')['value'] ?? null;
                if (! empty($search)) {
                    $query->where(function ($q) use ($search) {
                        $q->where('tokens.id', 'like', "%{$search}%")
                            ->orWhere('tokens.token', 'like', "%{$search}%")
                            ->orWhere('tokens.description', 'like', "%{$search}%")
                            ->orWhere('tokens.token_source', 'like', "%{$search}%")
                            ->orWhere('tokens.token_type', 'like', "%{$search}%")
                            ->orWhere('tokens.reportedBy', 'like', "%{$search}%")
                            ->orWhere('tokens.reporterContact', 'like', "%{$search}%");
                    });
                }
            })
            ->addColumn('id', function ($r) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'cell' => 'id'])->render();
            })
            ->addColumn('token_info', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'token_info'])->render();
            })
            ->addColumn('category_code', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'category_code'])->render();
            })
            ->addColumn('description', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'description'])->render();
            })
            ->addColumn('source', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'source'])->render();
            })
            ->addColumn('creator', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'creator'])->render();
            })
            ->addColumn('reseller', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'reseller'])->render();
            })
            ->addColumn('pop', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'pop'])->render();
            })
            ->addColumn('customer_id_username', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'customer_id_username'])->render();
            })
            ->addColumn('customer_info', function ($r) use ($employees, $mikrotik_online_users) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'customer_info', 'mikrotik_online_users' => $mikrotik_online_users])->render();
            })
            ->addColumn('reporter_contact', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'reporter_contact'])->render();
            })
            ->addColumn('assigned', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'assigned'])->render();
            })
            ->addColumn('token_type', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'token_type'])->render();
            })
            ->addColumn('actions', function ($r) use ($employees) {
                return view('token.dashboad.cells.row_cell', ['r' => $r, 'employees' => $employees, 'cell' => 'actions'])->render();
            })
            ->rawColumns(['id', 'token_info', 'category_code', 'description', 'source', 'creator', 'reseller', 'pop', 'customer_id_username', 'customer_info', 'reporter_contact', 'assigned', 'token_type', 'actions'])
            ->orderColumn('id', 'tokens.id $1')
            ->orderColumn('token_info', 'tokens.token $1')
            ->orderColumn('category_code', 'tokens.tokenCategory $1')
            ->orderColumn('description', 'tokens.description $1')
            ->orderColumn('source', 'tokens.token_source $1')
            ->orderColumn('assigned', 'tokens.assign_time $1')
            ->orderColumn('token_type', 'tokens.token_type $1')
            ->make(true);
    }

    protected function getTokenDetailQuery($pop_id, $type)
    {
        $eagerLoad = [
            'category',
            'Code',
            'clients',
            'creator',
            'employees',
            'clients.clientsinfo',
            'clients.pops',
            'clients.pops.reseller',
            'assignUsers.employee',
        ];

        // last month start and end date
        $last_month_start_date = now()->subMonth()->startOfMonth()->format('Y-m-d 00:00:00');
        $last_month_end_date = now()->subMonth()->endOfMonth()->format('Y-m-d 23:59:59');

        // this month start and end date
        $this_month_start_date = now()->startOfMonth()->format('Y-m-d 00:00:00');
        $this_month_end_date = now()->endOfMonth()->format('Y-m-d 23:59:59');

        $baseQuery = function () use ($pop_id, $eagerLoad) {
            return Token::with($eagerLoad)
                ->whereHas('clients', function ($query) use ($pop_id) {
                    $query->where('pop_id', $pop_id);
                });
        };

        switch ($type) {
            case 'totalcreated':
                return $baseQuery()->orderByDesc('id');

            case 'notclose':
                return $baseQuery()->whereNull('closeUserId')->orderByDesc('id');

            case 'assigned':
                return $baseQuery()
                    ->whereNotNull('assign_time')
                    ->whereNull('closeUserId')
                    ->orderBy('assign_time', 'asc');

            case 'unassigned':
                return $baseQuery()
                    ->whereNull('assign_time')
                    ->whereNull('closeUserId')
                    ->orderByDesc('id');

            case 'noupdate':
                return Token::with($eagerLoad)
                    ->join('clients', 'tokens.clientId', '=', 'clients.id')
                    ->leftJoin(DB::raw('(
                        SELECT token_id, MAX(created_at) as last_note_time
                        FROM token_notes
                        GROUP BY token_id
                    ) as latest_notes'), 'tokens.id', '=', 'latest_notes.token_id')
                    ->where('clients.pop_id', $pop_id)
                    ->whereNotNull('tokens.assign_time')
                    ->whereNull('tokens.closeUserId')
                    ->whereRaw('COALESCE(latest_notes.last_note_time, tokens.assign_time) < ?', [now()->subHour()])
                    ->select('tokens.*')
                    ->orderByRaw('COALESCE(latest_notes.last_note_time, tokens.assign_time) ASC');

            case 'timeover':
                return Token::with($eagerLoad)
                    ->join('clients', 'tokens.clientId', '=', 'clients.id')
                    ->leftJoin('token_codes', 'tokens.tokenCode', '=', 'token_codes.id')
                    ->where('clients.pop_id', $pop_id)
                    ->where('token_codes.solve_time', '>', 0)
                    ->whereNotNull('tokens.assign_time')
                    ->whereRaw('TIMESTAMPDIFF(MINUTE, tokens.assign_time, NOW()) >= token_codes.solve_time')
                    ->whereNull('tokens.closeUserId')
                    ->select('tokens.*')
                    ->orderByDesc('tokens.id');

            case 'notstartwork':
                return Token::with($eagerLoad)
                    ->join('clients', 'tokens.clientId', '=', 'clients.id')
                    ->leftJoin('token_codes', 'tokens.tokenCode', '=', 'token_codes.id')
                    ->where('clients.pop_id', $pop_id)
                    ->whereNull('tokens.closeUserId')
                    ->whereNotNull('tokens.assign_time')
                    ->whereNull('tokens.work_start_time')
                    ->select('tokens.*')
                    ->orderByDesc('tokens.id');
            case 'lastmonthtotalcreated':
                return $baseQuery()->whereBetween('tokens.created_at', [$last_month_start_date, $last_month_end_date])->orderByDesc('id');
            case 'thisthismonthtotalcreated':
                return $baseQuery()->whereBetween('tokens.created_at', [$this_month_start_date, $this_month_end_date])->orderByDesc('id');

            default:
                return Token::whereRaw('1 = 0');
        }
    }
}
