<?php

namespace App\Http\Controllers;

use App\Exports\ClientContractsExport;
use App\Http\Requests\ExportClientContractsRequest;
use App\Models\Client;
use App\Services\ReferenceDataCache;
use DB;
use Illuminate\Http\Request;
use Illuminate\Support\Str;
use Inertia\Inertia;
use Maatwebsite\Excel\Facades\Excel;

class ClientController extends Controller
{
    public function __construct(protected ReferenceDataCache $referenceCache) {}

    public function index(Client $client, Request $request)
    {
        $search = $request->input('search');

        $query = $client::query()
            ->select('clients.*')
            ->when($search, function ($que) use ($search) {
                $que->join('person', 'person.id', '=', 'clients.person_id')
                    ->where('person.full_name', 'ilike', '%'.$search.'%')
                    ->groupBy('clients.id');
            })
            // ->where('clients.active', 1)
            // Use LEFT JOINs for aggregated data to avoid subqueries
            ->leftJoin('client_cases', 'client_cases.client_id', '=', 'clients.id')
            ->leftJoin('contracts', function ($join) {
                $join->on('contracts.client_case_id', '=', 'client_cases.id')
                    ->whereNull('contracts.deleted_at');
            })
            ->leftJoin('contract_segment', function ($join) {
                $join->on('contract_segment.contract_id', '=', 'contracts.id')
                    ->where('contract_segment.active', true);
            })
            ->leftJoin('accounts', 'accounts.contract_id', '=', 'contracts.id')
            ->groupBy('clients.id')
            ->selectRaw('COUNT(DISTINCT CASE WHEN contract_segment.id IS NOT NULL THEN client_cases.id END) as cases_with_active_contracts_count')
            ->selectRaw('COALESCE(SUM(CASE WHEN contract_segment.id IS NOT NULL THEN accounts.balance_amount END), 0) as active_contracts_balance_sum')
            ->with('person')
            ->orderByDesc('clients.created_at');

        return Inertia::render('Client/Index', [
            'clients' => $query
                ->paginate($request->integer('per_page', default: 100))
                ->withQueryString(),
            'filters' => $request->only(['search']),
        ]);
    }

    public function show(Client $client, Request $request)
    {

        $data = $client::query()
            ->with(['person' => fn ($que) => $que->with(['addresses', 'phones', 'bankAccounts', 'emails', 'client'])])
            ->findOrFail($client->id);

        $types = [
            'address_types' => $this->referenceCache->getAddressTypes(),
            'phone_types' => $this->referenceCache->getPhoneTypes(),
        ];

        return Inertia::render('Client/Show', [
            'client' => $data,
            'auto_mail_decisions' => \App\Models\Decision::query()->where('auto_mail', true)->orderBy('name')->get(['id', 'name']),
            'client_cases' => $data->clientCases()
                ->select('client_cases.*')
                ->when($request->input('search'), function ($que, $search) {
                    $que->join('person', 'person.id', '=', 'client_cases.person_id')
                        ->where('person.full_name', 'ilike', '%'.$search.'%')
                        ->groupBy('client_cases.id');
                })
                ->leftJoin('contracts', function ($join) {
                    $join->on('contracts.client_case_id', '=', 'client_cases.id')
                        ->whereNull('contracts.deleted_at');
                })
                ->leftJoin('contract_segment', function ($join) {
                    $join->on('contract_segment.contract_id', '=', 'contracts.id')
                        ->where('contract_segment.active', true);
                })
                ->leftJoin('accounts', 'accounts.contract_id', '=', 'contracts.id')
                ->groupBy('client_cases.id')
                ->selectRaw('COUNT(DISTINCT CASE WHEN contract_segment.id IS NOT NULL THEN contracts.id END) as active_contracts_count')
                ->selectRaw('COALESCE(SUM(CASE WHEN contract_segment.id IS NOT NULL THEN accounts.balance_amount END), 0) as active_contracts_balance_sum')
                ->with(['person', 'client.person'])
                ->where('client_cases.active', 1)
                ->orderByDesc('client_cases.created_at')
                ->paginate($request->integer('per_page', 15))
                ->withQueryString(),
            'types' => $types,
            'filters' => $request->only(['search']),
        ]);
    }

    public function contracts(Client $client, Request $request)
    {
        $data = $client->load(['person' => fn ($q) => $q->with(['addresses', 'phones', 'bankAccounts', 'emails'])]);

        $from = $request->input('from');
        $to = $request->input('to');
        $search = $request->input('search');
        $segmentsParam = $request->input('segments');
        $segmentIds = $segmentsParam ? array_filter(explode(',', $segmentsParam)) : [];

        $contractsQuery = \App\Models\Contract::query()
            ->select(['contracts.id', 'contracts.uuid', 'contracts.reference', 'contracts.start_date', 'contracts.client_case_id'])
            ->join('client_cases', 'client_cases.id', '=', 'contracts.client_case_id')
            ->where('client_cases.client_id', $client->id)
            ->whereNull('contracts.deleted_at')
            ->when($from || $to, function ($q) use ($from, $to) {
                if (! empty($from)) {
                    $q->whereDate('contracts.start_date', '>=', $from);
                }
                if (! empty($to)) {
                    $q->whereDate('contracts.start_date', '<=', $to);
                }
            })
            ->when($search, function ($q) use ($search) {
                $q->leftJoin('person', 'person.id', '=', 'client_cases.person_id')
                    ->where(function ($inner) use ($search) {
                        $inner->where('contracts.reference', 'ilike', '%'.$search.'%')
                            ->orWhere('person.full_name', 'ilike', '%'.$search.'%');
                    });
            })
            ->when($segmentIds, function ($q) use ($segmentIds) {
                $q->whereHas('segments', function ($s) use ($segmentIds) {
                    $s->whereIn('segments.id', $segmentIds)
                        ->where('contract_segment.active', true);
                });
            })
            ->with([
                'clientCase:id,uuid,person_id',
                'clientCase.person:id,full_name',
                'clientCase.person.address',
                'segments' => function ($q) {
                    $q->wherePivot('active', true)->select('segments.id', 'segments.name');
                },
                'account:id,accounts.contract_id,balance_amount',
            ])
            ->orderByDesc('contracts.start_date');

        $segments = \App\Models\Segment::orderBy('name')->get(['id', 'name']);

        $types = [
            'address_types' => $this->referenceCache->getAddressTypes(),
            'phone_types' => $this->referenceCache->getPhoneTypes(),
        ];

        // Support custom pagination parameter names used by DataTableNew2
        $perPage = $request->integer('contracts_per_page', $request->integer('per_page', 15));
        $pageNumber = $request->integer('contracts_page', $request->integer('page', 1));

        return Inertia::render('Client/Contracts', [
            'client' => $data,
            'auto_mail_decisions' => \App\Models\Decision::query()->where('auto_mail', true)->orderBy('name')->get(['id', 'name']),
            'contracts' => $contractsQuery
                ->paginate($perPage, ['*'], 'contracts_page', $pageNumber)
                ->withQueryString(),
            'filters' => $request->only(['from', 'to', 'search', 'segment']),
            'segments' => $segments,
            'types' => $types,
        ]);
    }

    public function exportContracts(ExportClientContractsRequest $request, Client $client)
    {
        $data = $request->validated();
        $columns = array_values(array_unique($data['columns']));

        $from = $data['from'] ?? null;
        $to = $data['to'] ?? null;
        $search = $data['search'] ?? null;
        $segmentsParam = $data['segments'] ?? null;
        $segmentIds = $segmentsParam ? array_filter(explode(',', $segmentsParam)) : [];

        $query = \App\Models\Contract::query()
            ->whereHas('clientCase', function ($q) use ($client) {
                $q->where('client_id', $client->id);
            })
            ->with([
                'clientCase:id,uuid,person_id',
                'clientCase.person:id,full_name',
                'clientCase.person.address',
                'segments' => function ($q) {
                    $q->wherePivot('active', true)->select('segments.id', 'segments.name');
                },
                'account:id,accounts.contract_id,balance_amount',
            ])
            ->select(['id', 'uuid', 'reference', 'start_date', 'client_case_id'])
            ->whereNull('deleted_at')
            ->when($from || $to, function ($q) use ($from, $to) {
                if (! empty($from)) {
                    $q->whereDate('start_date', '>=', $from);
                }
                if (! empty($to)) {
                    $q->whereDate('start_date', '<=', $to);
                }
            })
            ->when($search, function ($q) use ($search) {
                $q->where(function ($inner) use ($search) {
                    $inner->where('reference', 'ilike', '%'.$search.'%')
                        ->orWhereHas('clientCase.person', function ($p) use ($search) {
                            $p->where('full_name', 'ilike', '%'.$search.'%');
                        });
                });
            })
            ->when($segmentIds, function ($q) use ($segmentIds) {
                $q->whereHas('segments', function ($s) use ($segmentIds) {
                    $s->whereIn('segments.id', $segmentIds)
                        ->where('contract_segment.active', true);
                });
            })
            ->orderByDesc('start_date');

        if (($data['scope'] ?? ExportClientContractsRequest::SCOPE_ALL) === ExportClientContractsRequest::SCOPE_CURRENT) {
            $page = max(1, (int) ($data['page'] ?? 1));
            $perPage = max(1, min(200, (int) ($data['per_page'] ?? 15)));
            $query->forPage($page, $perPage);
        }

        $filename = $this->buildExportFilename($client);

        return Excel::download(new ClientContractsExport($query, $columns), $filename);
    }

    private function buildExportFilename(Client $client): string
    {
        $datePrefix = now()->format('dmy');
        $clientName = $this->slugify($client->person?->full_name ?? 'stranka');

        return sprintf('%s_%s-Pogodbe.xlsx', $datePrefix, $clientName);
    }

    private function slugify(?string $value): string
    {
        if (empty($value)) {
            return 'data';
        }

        return Str::slug($value, '-') ?: 'data';
    }

    public function store(Request $request)
    {

        DB::transaction(function () use ($request) {
            $address = $request->input('address');
            $phone = $request->input('phone');
            $person = \App\Models\Person\Person::create([
                'nu' => rand(100000, 200000),
                'first_name' => $request->input('first_name'),
                'last_name' => $request->input('last_name'),
                'full_name' => $request->input('full_name'),
                'gender' => null,
                'birthday' => null,
                'tax_number' => $request->input('tax_number'),
                'social_security_number' => $request->input('social_security_number'),
                'description' => $request->input('description'),
                'group_id' => 1,
                'type_id' => 2,
            ]);

            $person->addresses()->create([
                'address' => $address['address'],
                'country' => $address['country'],
                'type_id' => $address['type_id'],
            ]);

            $person->phones()->create([
                'nu' => $phone['nu'],
                'country_code' => $phone['country_code'],
                'type_id' => $phone['type_id'],
            ]);

            $person->client()->create();
        });

        // \App\Models\Person\PersonAddress::create($address);

        return back()->with('success', 'Client created')->with('flash_method', 'POST');

    }

    public function update(Client $client, Request $request)
    {

        return back()->with('success', 'Client updated')->with('flash_method', 'PUT');
    }

    /**
     * Emergency endpoint: if the linked person record is missing (hard deleted) or soft deleted,
     * create a new minimal Person and re-point all related child records (emails, phones, addresses, bank accounts,
     * client cases) from the old person_id to the new one, then update the client itself.
     */
    public function emergencyCreatePerson(Client $client, Request $request)
    {
        $oldPersonId = $client->person_id;

        // If person exists and is not trashed, abort – nothing to do
        /** @var \App\Models\Person\Person|null $existing */
        $existing = \App\Models\Person\Person::withTrashed()->find($oldPersonId);
        if ($existing && ! $existing->trashed()) {
            return redirect()->back()->with('flash', [
                'type' => 'info',
                'message' => 'Person already exists – emergency creation not needed.',
            ]);
        }

        $data = $request->validate([
            'full_name' => ['nullable', 'string', 'max:255'],
            'first_name' => ['nullable', 'string', 'max:255'],
            'last_name' => ['nullable', 'string', 'max:255'],
            'tax_number' => ['nullable', 'string', 'max:99'],
            'social_security_number' => ['nullable', 'string', 'max:99'],
            'description' => ['nullable', 'string', 'max:500'],
        ]);

        // Provide sensible fallbacks.
        $fullName = $data['full_name'] ?? trim(($data['first_name'] ?? '').' '.($data['last_name'] ?? ''));
        if ($fullName === '') {
            $fullName = 'Unknown Person';
        }

        $newPerson = null;

        \DB::transaction(function () use ($oldPersonId, $client, $fullName, $data, &$newPerson) {
            $newPerson = \App\Models\Person\Person::create([
                'nu' => null, // boot event will generate
                'first_name' => $data['first_name'] ?? null,
                'last_name' => $data['last_name'] ?? null,
                'full_name' => $fullName,
                'gender' => null,
                'birthday' => null,
                'tax_number' => $data['tax_number'] ?? null,
                'social_security_number' => $data['social_security_number'] ?? null,
                'description' => $data['description'] ?? 'Emergency recreated person',
                'group_id' => 1,
                'type_id' => 2,
            ]);

            // Re-point related records referencing the old (missing) person id
            $tables = [
                'emails', 'person_phones', 'person_addresses', 'bank_accounts', 'client_cases',
            ];
            foreach ($tables as $table) {
                \DB::table($table)->where('person_id', $oldPersonId)->update(['person_id' => $newPerson->id]);
            }

            // Finally update the client itself (only this one; avoid touching other potential clients)
            $client->person_id = $newPerson->id;
            $client->save();
        });

        return redirect()->back()->with('flash', [
            'type' => 'success',
            'message' => 'New person created and related records re-linked.',
            'person_uuid' => $newPerson?->uuid,
        ]);
    }
}
