<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Symfony\Component\Console\Input\InputOption;
use App\Modules\Bookings\Task;
use App\Console\Commands\AbstractCommand;
use Carbon\Carbon;

class StatsExportCommand extends AbstractCommand
{
    /**
     * The console command name.
     *
     * @var string
     */
    protected $name = 'stats:export';

    /**
     * A friendly name of the command that will be used when loggin messages.
     *
     * @var string
     */
    protected $command_title = 'Stats Export';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Exports stats to a CSV file.';

    /**
     * Execute the console command.
     *
     * @return void
     */
    public function fire()
    {
        if ($this->option('mode') == 'filtered') {
            return $this->filteredStats();
        }

        if ($this->option('mode') == 'repairscount') {
            return $this->repairCountStats();
        }

        if ($this->option('mode') == 'repairslabour') {
            return $this->repairLabourStats();
        }

        return $this->simpleStats();
    }

    /**
     * Get the stats filtered by week and technician.
     *
     * @return void
     */
    public function filteredStats()
    {
        $repairs = Task::with('user')->where('type', 'repair')->where('created_at', '>', '2015-01-01 00:00:00')->where('completed', 1)->whereNotNull('assigned_to')->get();
        $data = [];

        foreach ($repairs as $repair) {
            // Get the timestamp when the job was first picked up
            $change = $repair->assigned_changes()->first();
            $startTime = $change ? $change->created_at : $repair->created_at;

            // Get the completion time
            $lastChange = $repair->status_changes()->whereIn('new_status', [10, 11])->orderBy('created_at', 'desc')->first();
            $endTime = $lastChange ? $lastChange->created_at : null;

            if (! $endTime) {
                continue;
            } // skip this one

            $difference = $endTime->diffInHours($startTime);
            @$data[$repair->user->name][$startTime->format('Y-W')][] = $difference;
        }

        // Calculate the average per week
        foreach ($data as $technician => $weeks) {
            foreach ($weeks as $week => $hours) {
                $data[$technician][$week] = round(array_sum($hours) / count($hours));
            }
        }

        // List all the technicians
        $technicians = array_keys($data);
        echo ','.implode(',', $technicians)."\n";

        $startDate = Carbon::createFromDate(2015, 1, 1, 'Europe/London');
        $endDate = Carbon::now();

        while ($startDate->lt($endDate)) {
            $date = $startDate->format('Y-W');
            echo $date.',';

            foreach ($data as $weeks) {
                if (isset($weeks[$date])) {
                    echo $weeks[$date];
                }

                echo ',';
            }

            echo "\n";
            $startDate->addWeek();
        }
    }

    /**
     * Get the simple stats that are exported to a CSV.
     *
     * @return void
     */
    public function simpleStats()
    {
        $repairs = Task::with('user')->where('type', 'repair')->where('created_at', '>', '2015-01-01 00:00:00')->where('completed', 1)->whereNotNull('assigned_to')->get();
        $data = [];

        foreach ($repairs as $repair) {
            // Get the timestamp when the job was first picked up
            $change = $repair->assigned_changes()->first();
            $startTime = $change ? $change->created_at : $repair->created_at;

            // Get the completion time
            $lastChange = $repair->status_changes()->whereIn('new_status', [10, 11])->orderBy('created_at', 'desc')->first();
            $endTime = $lastChange ? $lastChange->created_at : null;

            if (! $endTime) {
                continue;
            } // skip this one

            $difference = $endTime->diffInHours($startTime);

            echo sprintf("%d,%s,%s,%s,%s\n", $repair->id, $repair->user->name, $startTime, $endTime, $difference);
        }
    }

    /**
     * This function retrieves the number of repairs completed per technician each week
     * in a CSV format.
     *
     * @return void
     */
    public function repairCountStats()
    {
        // Modernized query with parameter binding for security and performance
        $repairs = Task::with('user')
                        ->selectRaw('COUNT(id) as cnt, assigned_to, DATE_FORMAT(`completed_at`, ?) as week', ['%Y-%u'])
                        ->where('type', 'repair')
                        ->where('completed_at', '>', '2015-01-01 00:00:00')
                        ->where('completed', 1)
                        ->whereNotNull('assigned_to')
                        ->groupByRaw('assigned_to, DATE_FORMAT(`completed_at`, ?)', ['%Y-%u'])
                        ->orderBy('week')
                        ->get();

        $data = [];

        foreach ($repairs as $repair) {
            @$data[$repair->user->name][$repair->week] = $repair->cnt;
        }

        // List all the technicians
        $technicians = array_keys($data);
        echo ','.implode(',', $technicians)."\n";

        $startDate = Carbon::createFromDate(2015, 1, 1, 'Europe/London');
        $endDate = Carbon::now();

        while ($startDate->lt($endDate)) {
            $date = $startDate->format('Y-W');
            echo $date.',';

            foreach ($data as $weeks) {
                if (isset($weeks[$date])) {
                    echo $weeks[$date];
                }

                echo ',';
            }

            echo "\n";
            $startDate->addWeek();
        }
    }

    /**
     * This function retrieves the labour sum of repairs completed per technician each week
     * in a CSV format.
     *
     * @return void
     */
    public function repairLabourStats()
    {
        // Modernized query with parameter binding for security and performance
        $repairs = Task::with('user')
                        ->selectRaw('SUM(labour) as cnt, assigned_to, DATE_FORMAT(`completed_at`, ?) as week', ['%Y-%u'])
                        ->where('type', 'repair')
                        ->where('completed_at', '>', '2015-01-01 00:00:00')
                        ->where('completed', 1)
                        ->whereNotNull('assigned_to')
                        ->groupByRaw('assigned_to, DATE_FORMAT(`completed_at`, ?)', ['%Y-%u'])
                        ->orderBy('week')
                        ->get();

        $data = [];

        foreach ($repairs as $repair) {
            @$data[$repair->user->name][$repair->week] = $repair->cnt;
        }

        // List all the technicians
        $technicians = array_keys($data);
        echo ','.implode(',', $technicians)."\n";

        $startDate = Carbon::createFromDate(2015, 1, 1, 'Europe/London');
        $endDate = Carbon::now();

        while ($startDate->lt($endDate)) {
            $date = $startDate->format('Y-W');
            echo $date.',';

            foreach ($data as $weeks) {
                if (isset($weeks[$date])) {
                    echo $weeks[$date];
                }

                echo ',';
            }

            echo "\n";
            $startDate->addWeek();
        }
    }

    /**
     * Get the console command arguments.
     *
     * @return array
     */
    protected function getArguments()
    {
        return [

        ];
    }

    /**
     * Get the console command options.
     *
     * @return array
     */
    protected function getOptions()
    {
        return [
            ['mode', 'm',InputOption::VALUE_OPTIONAL, 'The stat to output', 'simple'],
        ];
    }
}
