Investidor-V2/app/Http/Controllers/CrmController.php

<?php

namespace App\Http\Controllers;

use App\Models\Contact;
use App\Models\CrmTask;
use App\Models\InterestRequest;
use App\Models\Investor;
use App\Models\Lead;
use App\Models\User;
use App\Services\ConvertInterestToLead;
use App\Services\CountryCatalogue;
use App\Services\DeclaredOriginStatistics;
use App\Services\PortalAudit;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;
use Illuminate\Validation\ValidationException;
use Inertia\Inertia;
use Inertia\Response;

class CrmController extends Controller
{
    public function investors(Request $request): Response
    {
        $filters = $request->validate(['q' => ['nullable', 'string', 'max:150']]);
        $query = Investor::query();
        if ($term = $filters['q'] ?? null) {
            $query->whereLike('legal_name', "%{$term}%");
        }

        return Inertia::render('crm/investors', ['investors' => $query->orderBy('legal_name')->paginate(20)->withQueryString(), 'filters' => $filters]);
    }

    public function investor(Investor $investor): Response
    {
        return Inertia::render('crm/investor', ['investor' => $investor,
            'contacts' => Contact::whereIn('id', DB::table('investor_contacts')->where('investor_id', $investor->id)->select('contact_id'))->get(),
            'leads' => Lead::where('investor_id', $investor->id)->latest()->get(['id', 'title', 'status'])]);
    }

    public function saveInvestor(Request $request, Investor $investor): RedirectResponse
    {
        $data = $request->validate(['legal_name' => ['required', 'string', 'max:200'],
            'investor_type' => ['required', Rule::in(['domestic', 'foreign', 'institutional', 'individual'])],
            'status' => ['required', Rule::in(['prospect', 'active', 'inactive'])]]);
        PortalAudit::save($investor, $data, $request->user()->id);

        return back()->with('success', 'Investidor actualizado.');
    }

    public function saveContact(Request $request, Investor $investor, Contact $contact): RedirectResponse
    {
        abort_unless(DB::table('investor_contacts')->where('investor_id', $investor->id)->where('contact_id', $contact->id)->exists(), 404);
        $data = $request->validate(['full_name' => ['required', 'string', 'max:150'],
            'email' => ['required', 'email:rfc', 'max:150'], 'phone' => ['nullable', 'string', 'max:30']]);
        $data['email'] = strtolower(trim($data['email']));
        DB::transaction(function () use ($contact, $data, $request) {
            DB::select('select pg_advisory_xact_lock(170925, hashtext(?))', [$data['email']]);
            if (Contact::where('id', '!=', $contact->id)->whereRaw('lower(trim(email)) = ?', [$data['email']])->exists()) {
                throw ValidationException::withMessages(['email' => 'Já existe outro contacto com este email. Reveja os registos antes de os fundir.']);
            }
            PortalAudit::save($contact, $data, $request->user()->id);
        });

        return back()->with('success', 'Contacto actualizado.');
    }

    /** @return Collection<int, User> */
    private function owners(): Collection
    {
        return User::whereNotNull('email_verified_at')->whereIn(DB::raw('lower(email)'), config('portal.managers'))
            ->orderBy('name')->get(['id', 'name']);
    }

    public function index(Request $request): Response
    {
        $filters = $request->validate(['q' => ['nullable', 'string', 'max:150'],
            'status' => ['nullable', Rule::in(['new', 'qualified', 'disqualified', 'converted'])],
            'owner_user_id' => ['nullable', 'integer', 'exists:users,id'],
            'origin' => ['nullable', Rule::in(['unknown', ...array_column(app(CountryCatalogue::class)->all(), 'code')])]]);
        $query = Lead::with(['investor:id,legal_name', 'owner:id,name'])
            ->withCount(['tasks as overdue_tasks_count' => fn ($q) => $q->whereIn('status', ['open', 'in_progress'])->where('due_at', '<', now())]);
        if ($term = $filters['q'] ?? null) {
            $query->where(fn ($q) => $q->whereLike('title', "%{$term}%")
                ->orWhereHas('investor', fn ($investor) => $investor->whereLike('legal_name', "%{$term}%")));
        }
        if ($filters['status'] ?? null) {
            $query->where('status', $filters['status']);
        }
        if ($filters['owner_user_id'] ?? null) {
            $query->where('owner_user_id', $filters['owner_user_id']);
        }

        return Inertia::render('crm/leads', ['leads' => $query->latest()->orderBy('id')->paginate(20)->withQueryString(),
            'filters' => $filters, 'owners' => $this->owners(),
            'countries' => app(CountryCatalogue::class)->all(),
            'originStatistics' => app(DeclaredOriginStatistics::class)->forManager($request->user(), $filters['origin'] ?? null)]);
    }

    public function prepare(InterestRequest $interest): Response|RedirectResponse
    {
        $lead = Lead::where('interest_request_id', $interest->id)->first();
        if ($lead) {
            return redirect('/management/crm/leads/'.$lead->id);
        }

        return Inertia::render('crm/convert', ['interest' => $interest->only(['id', 'reference', 'full_name', 'sector_interest', 'opportunity_reference']),
            'journeyInvestorId' => DB::table('investor_journeys')->where('interest_request_id', $interest->id)->whereNull('deleted_at')->value('investor_id'),
            'investors' => Investor::orderBy('legal_name')->get(['id', 'legal_name']), 'owners' => $this->owners()]);
    }

    public function convert(Request $request, InterestRequest $interest, ConvertInterestToLead $converter): RedirectResponse
    {
        $data = $request->validate([
            'investor_id' => ['nullable', 'uuid', Rule::exists('investors', 'id')->whereNull('deleted_at')],
            'legal_name' => ['required_without:investor_id', 'nullable', 'string', 'max:200'],
            'investor_type' => ['required_without:investor_id', 'nullable', Rule::in(['domestic', 'foreign', 'institutional', 'individual'])],
            'owner_user_id' => ['required', 'integer', Rule::in($this->owners()->pluck('id'))],
            'title' => ['required', 'string', 'max:200'], 'next_action' => ['required', 'string', 'max:200'],
            'due_at' => ['required', 'date', 'after:now'],
        ]);
        $lead = $converter->convert($interest, $data, $request->user()->id);

        return redirect('/management/crm/leads/'.$lead->id)->with('success', 'Pedido convertido em lead com responsável e próxima acção.');
    }

    public function show(Lead $lead): Response
    {
        $lead->load(['investor', 'owner:id,name', 'tasks' => fn ($q) => $q->orderBy('due_at')]);
        $contacts = DB::table('investor_contacts')->join('contacts', 'contacts.id', '=', 'investor_contacts.contact_id')
            ->where('investor_id', $lead->investor_id)->whereNull('contacts.deleted_at')->get(['contacts.id', 'full_name', 'email', 'phone']);

        return Inertia::render('crm/lead', ['lead' => $lead, 'contacts' => $contacts, 'owners' => $this->owners()]);
    }

    public function update(Request $request, Lead $lead): RedirectResponse
    {
        $data = $request->validate(['owner_user_id' => ['required', 'integer', Rule::in($this->owners()->pluck('id'))],
            'status' => ['required', Rule::in(['new', 'qualified', 'disqualified'])],
            'qualification_reason' => ['required', 'string', 'min:10', 'max:2000']]);
        DB::transaction(function () use ($lead, $data, $request) {
            $lead = Lead::lockForUpdate()->findOrFail($lead->id);
            if ($lead->status === 'converted') {
                throw ValidationException::withMessages(['status' => 'O lead já foi convertido numa negociação.']);
            }
            if ($data['status'] !== 'disqualified' && ! $lead->tasks()->whereIn('status', ['open', 'in_progress'])->where('due_at', '>', now())->exists()) {
                throw ValidationException::withMessages(['status' => 'Registe uma próxima acção com prazo futuro antes de activar ou qualificar o lead.']);
            }
            PortalAudit::save($lead, $data, $request->user()->id);
        });

        return back()->with('success', 'Lead actualizado.');
    }

    public function task(Request $request, Lead $lead): RedirectResponse
    {
        $data = $request->validate(['title' => ['required', 'string', 'max:200'], 'due_at' => ['required', 'date', 'after:now'],
            'assignee_user_id' => ['required', 'integer', Rule::in($this->owners()->pluck('id'))]]);
        PortalAudit::save(new CrmTask, [...$data, 'lead_id' => $lead->id, 'status' => 'open'], $request->user()->id);

        return back()->with('success', 'Tarefa criada.');
    }

    public function updateTask(Request $request, Lead $lead, CrmTask $task): RedirectResponse
    {
        abort_unless($task->lead_id === $lead->id, 404);
        $data = $request->validate(['status' => ['required', Rule::in(['open', 'in_progress', 'done', 'cancelled'])]]);
        DB::transaction(function () use ($lead, $task, $data, $request) {
            $lead = Lead::lockForUpdate()->findOrFail($lead->id);
            if (in_array($lead->status, ['new', 'qualified'], true) && in_array($data['status'], ['done', 'cancelled'], true)
                && ! $lead->tasks()->where('id', '!=', $task->id)->whereIn('status', ['open', 'in_progress'])->where('due_at', '>', now())->exists()) {
                throw ValidationException::withMessages(['status' => 'Crie a próxima acção antes de encerrar a última tarefa de um lead activo.']);
            }
            PortalAudit::save($task, $data, $request->user()->id);
        });

        return back()->with('success', 'Tarefa actualizada.');
    }
}