<?php

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

use App\Modules\Bookings\Repair;
use App\Modules\Bookings\Task;
use App\Modules\Gamification\PointsTransaction;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use App\Models\User;
use App\Http\Controllers\BaseController;
// Removed Kashflow import - using Xero integration instead

class RepairsControllerREST extends BaseController
{
    /**
     * Get a summary of income for the given period.
     *
     * @return array
     */
    public function getIncome(Request $request)
    {
        $data = [
            'labour' => Task::query()->where('type', 'repair')->thisMonth($request->input('start_date'), $request->input('end_date'))->completed()->sum('labour'),
            'parts'  => Task::query()->join('parts', 'tasks.id', '=', 'parts.related_id')->where('parts.related_type', 'task')
                            ->completed()
                            ->thisMonth($request->input('start_date'), $request->input('end_date'))
                            ->select(DB::raw('SUM(parts.quantity * parts.price * (parts.vat / 100 + 1)) as total'))
                            ->first()->total,
            'booking_fee' => Task::query()->where('type', 'repair')->thisMonth($request->input('start_date'), $request->input('end_date'))->completed()->whereNotNull('booking_fee_id')->count() * Config::get('settings.booking_fee'),
        ];

        $data['total'] = round(array_sum($data), 2);

        return $data;
    }

    /**
     * Get a summary of completed, open, returned jobs.
     *
     * @return array
     */
    public function getOverview(Request $request)
    {
        return [
            'completed' => Task::query()->where('type', 'repair')->thisMonth($request->input('start_date'), $request->input('end_date'))->completed()->count(),
            'open'      => Task::query()->where('type', 'repair')->open()->count(),
            'supportOpen'      => Task::query()->where('type', 'support')->open()->count(),
            'queue'     => Task::query()->where('type', 'repair')->open()->whereNull('assigned_to')->count(),
            'supportQueue'     => Task::query()->where('type', 'support')->open()->whereNull('assigned_to')->count(),
            'assigned'  => Task::query()->where('type', 'repair')->open()->whereNotNull('assigned_to')->count(),
            'supportAssigned'  => Task::query()->where('type', 'support')->open()->whereNotNull('assigned_to')->count(),
            'returns'   => Task::query()->where('type', 'repair')->thisMonth($request->input('start_date'), $request->input('end_date'))->whereNotNull('return_id')->count(),
        ];
    }

    /**
     * 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 with match expression
        $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 labour query with parameter binding
        $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'))
                        ->where('type', 'repair')
                        ->groupByRaw("DATE_FORMAT(completed_at, ?)", [$dateFormat])
                        ->get();

        // Modernized booking fees query with parameter binding
        $booking_fees = Task::query()
                            ->selectRaw("count(id) as cnt, DATE_FORMAT(completed_at, ?) as date", [$dateFormat])
                            ->where('type', 'repair')
                            ->thisMonth($request->input('start_date'), $request->input('end_date'))
                            ->completed()
                            ->whereNotNull('booking_fee_id')
                            ->groupByRaw("DATE_FORMAT(completed_at, ?)", [$dateFormat])
                            ->get();

        // Modernized points query with parameter binding
        $points = PointsTransaction::query()
                                    ->selectRaw("sum(amount) as cnt, DATE_FORMAT(created_at, ?) as date", [$dateFormat])
                                    ->whereHas('task', function ($q) {
                                        return $q->where('type', 'repair');
                                    })
                                    ->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]
        );

        // Merge labour & booking fee (existing logic preserved)
        foreach ($booking_fees as $i => $bf) {
            $found = false;
            $bf->cnt = $bf->cnt * Config::get('settings.booking_fee');

            foreach ($labour as $j => $value) {
                if ($bf->date == $value->date) {
                    $labour[$j]->cnt += $bf->cnt;
                    $found = true;
                    break;
                }
            }

            if (! $found) {
                $labour->push($bf);
            }
        }

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

    /**
     * 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'], $request->input('group'));

        }
        else
        {
            $users = User::getActiveList(['id', 'first_name', 'last_name']);
        }
        // Get stats for different task types
        $completed = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))
                            ->whereNotNull('assigned_to')
                            ->completed()
                            ->thisMonth($request->input('start_date'), $request->input('end_date'))
                            ->where('type', 'repair')
                            ->groupBy('assigned_to')
                            ->get();

        $open = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))
                            ->whereNotNull('assigned_to')
                            ->open()
                            ->where('type', 'repair')
                            ->groupBy('assigned_to')
                            ->get();

        $openSupport = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))
                            ->whereNotNull('assigned_to')
                            ->open()
                            ->where('type', 'support')
                            ->where('status_id', '<>', 9)
                            ->groupBy('assigned_to')
                            ->get();
        
        $openOnsite = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))
                            ->whereNotNull('assigned_to')
                            ->open()
                            ->where('type', 'onsite')
                            ->where('status_id', '<>', 9)
                            ->groupBy('assigned_to')
                            ->get();

        $openAverageAge = Task::query()->select(DB::raw('avg(TIMESTAMPDIFF(DAY, created_at, now())) as openAverageAge, assigned_to'))
                            ->whereNotNull('assigned_to')
                            ->open()
                            ->whereIn('type', ['support', 'repair', 'onsite'])
                            ->where('status_id', '<>', 9)
                            ->groupBy('assigned_to')
                            ->get();

 
        $parts = Task::query()->join('parts', 'tasks.id', '=', 'parts.related_id')->where('parts.related_type', 'task')
                            ->completed()
                            ->where('type', 'repair')
                            ->thisMonth($request->input('start_date'), $request->input('end_date'))
                            ->select(DB::raw('SUM(parts.quantity * parts.price * (parts.vat / 100 + 1)) as cnt, assigned_to'))
                            ->groupBy('assigned_to')
                            ->get();

        $labour = Task::query()->select(DB::raw('SUM(labour) as labour, assigned_to'))
                            ->whereNotNull('assigned_to')
                            ->completed()
                            ->thisMonth($request->input('start_date'), $request->input('end_date'))
                            ->where('type', 'repair')
                            ->groupBy('assigned_to')
                            ->get();

        $booking_fee = Task::query()->select(DB::raw('COUNT(id) as cnt, assigned_to'))
                            ->whereNotNull('assigned_to')
                            ->completed()
                            ->thisMonth($request->input('start_date'), $request->input('end_date'))
                            ->whereNotNull('booking_fee_id')
                            ->groupBy('assigned_to')
                            ->get();

        $points = PointsTransaction::query()->select(DB::raw('sum(amount) as cnt, user_id as assigned_to'))
                            ->whereHas('task', function ($q) {
                                return $q->where('type', 'repair');
                            })
                            ->createdRange($request->input('start_date'), $request->input('end_date'))
                            ->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) {
            // completed
            $count = $array_reject($completed, $user->id);
            $user->completed = ($count) ? $count->cnt : 0;

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

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

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

            $count = $array_reject($openAverageAge, $user->id);
            $user->openAverageAge = ($count) ? $count->openAverageAge : 0;
            $user->openAverageAge = intval($user->openAverageAge);
            $user->totalJobs = $user->open + $user->openOnsite + $user->openSupport;

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

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

            // 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;
        }

        return $users;
    }

    /**
     * Returns the daily data for repairs booked in and repairs completed.
     *
     * @return Illuminate\Support\Response
     */
    public function getDailySummary(Request $request)
    {
        $data = [];
        // Final modernization - remove all legacy DATE_FORMAT patterns
        $data['booked'] = Task::query()
                            ->selectRaw('COUNT(id) as total, DATE_FORMAT(tasks.created_at, ?) as `date`', ['%Y-%m-%d'])
                            ->where('type', 'repair')
                            ->createdThisMonth($request->input('start_date'), $request->input('end_date'))
                            ->groupByRaw("DATE_FORMAT(tasks.created_at, ?)", ['%Y-%m-%d'])
                            ->get();

        $data['completed'] = Task::query()
                            ->selectRaw('COUNT(id) as total, DATE_FORMAT(tasks.completed_at, ?) as `date`', ['%Y-%m-%d'])
                            ->where('type', 'repair')
                            ->completed()
                            ->thisMonth($request->input('start_date'), $request->input('end_date'))
                            ->groupByRaw("DATE_FORMAT(tasks.completed_at, ?)", ['%Y-%m-%d'])
                            ->get();

        return $data;
    }

    /**
     * Returns the daily data for repairs booked in and repairs completed.
     *
     * @return Illuminate\Support\Response
     */
    public function getDownloadSummary(Request $request)
    {
        ob_start();
        Artisan::call('stats:export', ['--mode' => $request->input('mode')]);
        $stats = ob_get_contents();

        header('Content-Type: application/csv');
        header('Content-Disposition: attachment; filename=example.csv');
        header('Pragma: no-cache');

        return $stats;
    }
}
