<?php

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

use Illuminate\Support\Facades\Response;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;
use App\Modules\Bookings\Task;
use App\Modules\Bookings\Repair;
use App\Modules\Bookings\Support;
use App\Modules\Bookings\Onsite;
use App\Modules\Bookings\Project;
use App\Modules\Bookings\Message;
use App\Modules\Clients\Client;
use App\Modules\Clients\Customer;
use App\Modules\Clients\Site;
use App\Http\Controllers\BaseController;
use Illuminate\Http\Request;

class HelpersControllerREST extends BaseController
{
    /**
     * Returns the overview stats.
     *
     * @return \Illuminate\Http\Response
     */
    public function getOverview()
    {
        // Cache the overview data for 5 minutes since it doesn't change frequently
        $data = Cache::remember('clients_overview', 300, function () {
            // Optimized query - only look at completed tasks from last 12 months
            // Use database aggregation instead of loading all data into PHP
            $average = DB::table('tasks')
                ->selectRaw('AVG(client_labour) as avg_labour')
                ->fromSub(
                    DB::table('tasks')
                        ->select('client_id', 'client_type')
                        ->selectRaw('SUM(labour) as client_labour')
                        ->whereNull('deleted_at')
                        ->where('completed', 1)
                        ->where('completed_at', '>=', now()->subYear()) // Only last 12 months
                        ->whereNotNull('client_id')
                        ->where('labour', '>', 0) // Only tasks with actual labour
                        ->groupBy('client_id', 'client_type'),
                    'client_totals'
                )
                ->value('avg_labour') ?? 0;

            return [
                'count' => [
                    'clients'       => Client::count(),
                    'customers'     => Customer::count(),
                    'new_customers' => Client::where('created_at', '>=', date('Y-m-01 00:00:00'))->count() + Customer::where('created_at', '>=', date('Y-m-01 00:00:00'))->count(),
                    'average'       => round($average, 2),
                ],
            ];
        });

        return Response::json($data);
    }

    /**
     * Returns the client summary data.
     *
     * @return \Illuminate\Http\Response
     */
    public function getClientSummary($id)
    {
        $last_task = Task::where('client_id', $id)->where('client_type', 'client')->orderBy('id', 'desc')->first();

        $data = [
            'count' => [
                'repairs' => intval(Repair::repairs()->where('client_id', $id)->where('client_type', 'client')->count()),
                'support' => intval(Support::support()->where('client_id', $id)->where('client_type', 'client')->count()),
                'onsite'  => intval(Onsite::onsite()->where('client_id', $id)->where('client_type', 'client')->count()),
            ],
            'stats' => [
                'spent'       => Task::where('client_id', $id)->where('client_type', 'client')->sum('labour'),
                'last_task'   => ($last_task) ? $last_task->toArray() : [],
                'payment_due' => Task::where('client_id', $id)->where('client_type', 'client')->completed()->where('paid', 0)->sum('labour'),
            ],
        ];

        return Response::json($data);
    }

    /**
     * Returns the customer summary data.
     *
     * @return \Illuminate\Http\Response
     */
    public function getCustomerSummary($id)
    {
        $last_task = Task::where('client_id', $id)->where('client_type', 'customer')->orderBy('id', 'desc')->first();
        $data = [
            'count' => [
                'repairs' => intval(Repair::repairs()->where('client_id', $id)->where('client_type', 'customer')->count()),
                'support' => intval(Support::support()->where('client_id', $id)->where('client_type', 'customer')->count()),
                'onsite'  => intval(Onsite::onsite()->where('client_id', $id)->where('client_type', 'customer')->count()),
            ],
            'stats' => [
                'spent'       => Task::where('client_id', $id)->where('client_type', 'customer')->sum('labour'),
                'last_task'   => ($last_task) ? $last_task->toArray() : [],
                'payment_due' => Task::where('client_id', $id)->where('client_type', 'customer')->completed()->where('paid', 0)->sum('labour'),
            ],
        ];

        return Response::json($data);
    }

    public function postMergeAccounts(Request $request)
    {
        $client = $request->input('client');
        DB::beginTransaction();

        try {
            if (isset($client['first_name'])) {
                $client = Customer::findOrFail($client['id']);
                $update = [
                    'client_id'    => $client->id,
                    'client_type'  => 'customer',
                    'site_id'      => null,
                    'site_user_id' => null,
                ];
            } else {
                $client = Client::findOrFail($client['id']);

                $site = $client->sites()->first();
                if (! $site) {
                    throw new Exception('Site not found', 1);
                }
                $user = $site->users()->first();
                if (! $user) {
                    throw new Exception('Site user not found', 1);
                }

                $update = [
                    'client_id'    => $client->id,
                    'client_type'  => 'client',
                    'site_id'      => $site->id,
                    'site_user_id' => $user->id,
                ];
            }

            foreach ($request->input('data') as $v) {
                $client_type = (isset($v['first_name'])) ? 'customer' : 'client';
                Task::where('client_id', $v['id'])->where('client_type', $client_type)->update($update);
                Project::where('client_id', $v['id'])->where('client_type', $client_type)->update($update);
                Message::where('client_id', $v['id'])->where('client_type', $client_type)->update($update);

                // Now we delete the client/customer
                if ($client_type == 'client') {
                    Client::findOrFail($v['id'])->delete();
                } else {
                    Customer::findOrFail($v['id'])->delete();
                }
            }

            DB::commit();
        } catch (Exception $e) {
            DB::rollback();

            return Response::json(['error' => $e->getMessage()], 400);
        }

        return $client;
    }
}
