<?php

namespace App\Modules\Stats\controllers\api\v1;

use Illuminate\Support\Facades\Response;
use App\Models\User;
use Part; // nadeem 23-1-17
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Config;
use Carbon\Carbon;
use App\Modules\Bookings\Task;
use App\Modules\Bookings\Repair;
use App\Modules\Bookings\Support;
use App\Modules\Bookings\Onsite;
use App\Modules\Bookings\TaskFeedback;
use App\Modules\Gamification\PointsTransaction;
use App\Modules\Clients\Client;
use Illuminate\Http\Request;
use App\Http\Controllers\BaseController;

class OverviewControllerREST extends BaseController
{
    /**
     * Returns the summary data for all the active users within the specified period of time.
     *
     * @return array
     */
    public function getUsers(Request $request)
    {
        if($request->input('group'))
        {
            $users = User::getActiveListWithGroup(['id', 'first_name', 'last_name', 'points'], $request->input('group'));
        }
        else
        {
            $users = User::getActiveList(['id', 'first_name', 'last_name', 'points']);

        }
        $task_types = ['repair', 'support', 'onsite'];
      /*  $parts = Part::select(DB::raw('SUM(quantity) as qnt'))          ;


        'overdue' => Task::leftJoin('parts', function ($join) {
                        return $join->on('parts.related_id', '=', 'tasks.id')->where('parts.related_type', '=', 'task');
                    })
                    ->where('completed', 1)->where('paid', 0)->overdue()
                    ->whereIn('tasks.type', ['repair', 'support', 'onsite'])
                    ->select(\DB::raw('SUM(labour) as labour, SUM(parts.quantity * parts.price * (parts.vat / 100 + 1)) as parts'))->first()->toArray(),
*/


        // Get stats for different task types
        $repairs = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))->whereNotNull('assigned_to')->completedstats()->thisMonth($request->input('start_date'), $request->input('end_date'))->where('type', 'repair')->groupBy('assigned_to')->get();
        $support = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))->whereNotNull('assigned_to')->completedstats()->thisMonth($request->input('start_date'), $request->input('end_date'))->where('type', 'support')->groupBy('assigned_to')->get();
        $onsite = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))->whereNotNull('assigned_to')->completedstats()->thisMonth($request->input('start_date'), $request->input('end_date'))->where('type', 'onsite')->groupBy('assigned_to')->get();
        $labour = Task::query()->select(DB::raw('SUM(labour) as labour, assigned_to'))->whereNotNull('assigned_to')->completedstats()->thisMonth($request->input('start_date'), $request->input('end_date'))->whereIn('type', $task_types)->groupBy('assigned_to')->get();
        $booking_fee = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))->whereNotNull('assigned_to')->completedstats()->thisMonth($request->input('start_date'), $request->input('end_date'))->whereNotNull('booking_fee_id')->groupBy('assigned_to')->get();

        // junaid
        $parts = Task::query()->leftJoin('parts', function ($join) {
                          return $join->on('parts.related_id', '=', 'tasks.id')->where('parts.related_type', '=', 'task');
                      })->select(DB::raw('SUM(parts.quantity * parts.price * (parts.vat / 100 + 1)) as parts, assigned_to'))->whereNotNull('assigned_to')->completed()->thisMonth($request->input('start_date'), $request->input('end_date'))->groupBy('assigned_to')->get();

        // nadeem
        $points = Task::query()->leftJoin('points_transactions', function ($join) {
          $join = $join->on('points_transactions.task_id', '=', 'tasks.id')->where('points_transactions.identifier', '=', 'task');
        })->select(DB::raw('SUM(points_transactions.amount) as user_points, tasks.assigned_to'))->whereNotNull('user_id')->completed()->thisMonth($request->input('start_date'), $request->input('end_date'))->where('identifier','task')->groupBy('user_id')->get();

        // A helper function to merge the results from above
        // with all users
        $array_reject = function (&$array, $user_id) {
            foreach ($array as $i => $item) {
                if ($item->assigned_to == $user_id) {
                    unset($array[$i]);

                    return $item;
                }
            }

            return 0;
        };

        foreach ($users as $user) {
            // Repairs
            $count = $array_reject($repairs, $user->id);
            $user->repairs = ($count) ? $count->cnt : 0;

            // Remote support
            $count = $array_reject($support, $user->id);
            $user->remoteSupport = ($count) ? $count->cnt : 0;

            // Onsite
            $count = $array_reject($onsite, $user->id);
            $user->onsite = ($count) ? $count->cnt : 0;

            $user->totalJobs = $user->repairs + $user->remoteSupport + $user->onsite;

            // Labour
            $count = $array_reject($labour, $user->id);
            $user->labour = ($count) ? $count->labour : 0;

            // Booking Fees
            $count = $array_reject($booking_fee, $user->id);
            $user->booking_fee = ($count) ? $count->cnt * Config::get('settings.booking_fee') : 0;

            // Parts
            $count = $array_reject($parts, $user->id);
            $user->parts = ($count) ? $count->parts : 0;

            // Points
            $count = $array_reject($points, $user->id);
            $user->period_points = ($count) ? $count->user_points : 0;
        }
      //  dd($users);
        return $users;

    }

    /**
     * Get a summary of income for the given period.
     *
     * @return array
     */
    public function getIncome(Request $request)
    {
        $data = [
            'repairs'     => Task::query()->where('type', 'repair')->completed()->thisMonth($request->input('start_date'), $request->input('end_date'))->sum('labour'),
            'support'     => Task::query()->where('type', 'support')->completed()->thisMonth($request->input('start_date'), $request->input('end_date'))->sum('labour'),
            'onsite'      => Task::query()->where('type', 'onsite')->completed()->thisMonth($request->input('start_date'), $request->input('end_date'))->sum('labour'),
            'booking_fee' => Task::query()->whereNotNull('booking_fee_id')->completed()->thisMonth($request->input('start_date'), $request->input('end_date'))->count() * Config::get('settings.booking_fee'),
            'contracts'   => Client::query()->where('contract_end_date', '<=', date('Y-m-d', $request->input('end_date', time())))
                                ->sum('contract_price') * abs(Carbon::createFromTimeStamp($request->input('start_date', time()))->diffInDays(Carbon::createFromTimeStamp($request->input('end_date', time()))) / 30),
        ];

        $start_date = date('Y-m-d H:i:s', $request->input('start_date', time()));
        $end_date = date('Y-m-d H:i:s', $request->input('end_date', time()));
        // Fixed wages query - restored original Laravel5 business logic with proper DATE_FORMAT JOIN
        $wages = DB::select("
            SELECT round(sum(pay), 2) as cnt
            FROM (
                SELECT abs(time_to_sec(TIMEDIFF(a.time, b.time)) / 3600) * (SELECT rate_per_hour FROM hr_user_details WHERE user_id = a.user_id) as pay, a.`time`
                FROM hr_time_log as a
                JOIN hr_time_log as b ON DATE_FORMAT(a.time, '%Y-%m-%d') = DATE_FORMAT(b.time, '%Y-%m-%d') AND a.user_id = b.user_id
                WHERE a.type = 'in' AND b.type = 'out' AND a.time >= ? AND b.time <= ? AND b.time < NOW()
                GROUP BY DATE_FORMAT(a.time, '%Y-%m-%d'), a.user_id
            ) as q",
            [$start_date, $end_date]
        );

        $data['total'] = array_sum($data);
        $data['wages'] = $wages[0]->cnt;
        $data['profit'] = round($data['total'] - $data['wages'], 2);

        return $data;
    }

    /**
     * Get the data to be generated in a graph that displays the amount of labour and points earned
     * over a period of time.
     *
     * @return array
     */
    public function getGraph(Request $request)
    {
        // Modern date format selection - more readable and maintainable
        $dateFormat = match ($request->input('view', 'daily')) {
            'weekly' => '%Y-%u',
            'monthly' => '%Y-%m',
            'yearly' => '%Y',
            default => '%Y-%m-%d'
        };

        $start_date = date('Y-m-d H:i:s', $request->input('start_date', time()));
        $end_date = date('Y-m-d H:i:s', $request->input('end_date', time()));

        // Modernized query with cleaner raw SQL
        $labour = Task::query()
                        ->selectRaw("sum(labour) as cnt, DATE_FORMAT(completed_at, ?) as date", [$dateFormat])
                        ->completed()
                        ->thisMonth($request->input('start_date'), $request->input('end_date'))
                        ->whereIn('type', ['repair', 'support', 'onsite'])
                        ->groupByRaw("DATE_FORMAT(completed_at, ?)", [$dateFormat]);

        $points = PointsTransaction::query()
                                    ->selectRaw("sum(amount) as cnt, DATE_FORMAT(created_at, ?) as date", [$dateFormat])
                                    ->createdRange($request->input('start_date'), $request->input('end_date'))
                                    ->groupByRaw("DATE_FORMAT(created_at, ?)", [$dateFormat]);

        // Fixed wages query - restored original Laravel5 business logic with proper DATE_FORMAT JOIN
        $wages = DB::select("
            SELECT round(sum(pay), 2) as cnt, DATE_FORMAT(q.time, ?) as `date`
            FROM (
                SELECT abs(time_to_sec(TIMEDIFF(a.time, b.time)) / 3600) * (SELECT rate_per_hour FROM hr_user_details WHERE user_id = a.user_id) as pay, a.`time`
                FROM hr_time_log as a
                JOIN hr_time_log as b ON DATE_FORMAT(a.time, '%Y-%m-%d') = DATE_FORMAT(b.time, '%Y-%m-%d') AND a.user_id = b.user_id
                WHERE a.type = 'in' AND b.type = 'out' AND a.time >= ? AND b.time <= ? AND b.time < NOW()
                GROUP BY DATE_FORMAT(a.time, '%Y-%m-%d'), a.user_id
            ) as q
            GROUP BY DATE_FORMAT(q.time, ?)
            ORDER BY `date`",
            [$dateFormat, $start_date, $end_date, $dateFormat]
        );

        return [
            'labour' => $labour->get(),
            'points' => $points->get(),
            'wages'  => $wages,
        ];
    }

    /**
     * Returns the data formatted to be used with a piechart. It returns the amount of labour
     * grouped by user_id for the requested period of time.
     *
     * @return array
     */
    public function getPieChart(Request $request)
    {
        $data = Task::query()->select(DB::raw('SUM(tasks.labour) as labour, COUNT(tasks.id) as cnt, tasks.assigned_to'));

        if ($request->input('type') == 'qa') {
            $data->join('tasks as tmp', function ($q) {
                        $q->on('tasks.qa_id', '=', 'tmp.id')->whereNull('tmp.deleted_at');

                    })
                ->where('tmp.type', $request->input('qa_type', 'repair'));
        }

        $data = $data->where('tasks.type', $request->input('type', 'repair'))
            ->thisMonth($request->input('start_date'), $request->input('end_date'))
            ->completed()
            ->whereNotNull('tasks.assigned_to')
            ->groupBy('tasks.assigned_to')
            ->with('user')->get();

        // Calculate booking fees
        if ($request->input('type', 'repair') == 'repair') {
            $booking_fees = Task::query()->select(DB::raw('COUNT(`id`) as cnt, assigned_to'))
                                ->where('type', 'repair')
                                ->completed()
                                ->thisMonth($request->input('start_date'), $request->input('end_date'))
                                ->whereNotNull('assigned_to')
                                ->whereNotNull('booking_fee_id')
                                ->groupBy('assigned_to')
                                ->get();

            // Merge the booking fee amount with the repair labour
            foreach ($data as $i => $value) {
                foreach ($booking_fees as $fee) {
                    if ($fee->assigned_to == $value->assigned_to) {
                        $data[$i]->labour += $fee->cnt * Config::get('settings.booking_fee');
                        break;
                    }
                }
            }
        }

        return $data;
    }

    public function getFeedbackStats(Request $request)
    {
        $data = TaskFeedback::select(DB::raw('AVG(rating) as rating, tasks.type'))
                            ->join('tasks', 'task_id', '=', 'id')
                            ->where('emailed', 1)
                            ->whereNotNull('rating')
                            ->interval($request->input('start_date'), $request->input('end_date'))
                            ->groupBy('tasks.type')
                            ->get();

        // Format the response
        $response = [];
        foreach ($data as $value) {
            $response[$value->type] = round($value->rating, 1);
        }

        return $response;
    }
}
