Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
32.88% covered (danger)
32.88%
193 / 587
7.41% covered (danger)
7.41%
2 / 27
CRAP
0.00% covered (danger)
0.00%
0 / 1
Users
32.88% covered (danger)
32.88%
193 / 587
7.41% covered (danger)
7.41%
2 / 27
5482.06
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
13 / 13
100.00% covered (success)
100.00%
1 / 1
4
 create_users
37.50% covered (danger)
37.50%
18 / 48
0.00% covered (danger)
0.00%
0 / 1
34.41
 get_users
100.00% covered (success)
100.00%
11 / 11
100.00% covered (success)
100.00%
1 / 1
3
 get_users_by_region
78.57% covered (warning)
78.57%
11 / 14
0.00% covered (danger)
0.00%
0 / 1
5.25
 get_user
0.00% covered (danger)
0.00%
0 / 23
0.00% covered (danger)
0.00%
0 / 1
30
 getUserByName
61.11% covered (warning)
61.11%
11 / 18
0.00% covered (danger)
0.00%
0 / 1
3.53
 getAllUserNames
41.67% covered (danger)
41.67%
5 / 12
0.00% covered (danger)
0.00%
0 / 1
2.79
 get_user_by_email
88.00% covered (warning)
88.00%
22 / 25
0.00% covered (danger)
0.00%
0 / 1
7.08
 add_company_user
86.96% covered (warning)
86.96%
20 / 23
0.00% covered (danger)
0.00%
0 / 1
5.06
 delete_company_user
66.67% covered (warning)
66.67%
6 / 9
0.00% covered (danger)
0.00%
0 / 1
2.15
 update_users
18.87% covered (danger)
18.87%
20 / 106
0.00% covered (danger)
0.00%
0 / 1
233.62
 delete_users
57.14% covered (warning)
57.14%
4 / 7
0.00% covered (danger)
0.00%
0 / 1
8.83
 get_roles
40.00% covered (danger)
40.00%
2 / 5
0.00% covered (danger)
0.00%
0 / 1
2.86
 get_all_commercials
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
6
 get_g3w_warning_fields
0.00% covered (danger)
0.00%
0 / 21
0.00% covered (danger)
0.00%
0 / 1
20
 get_created_by
0.00% covered (danger)
0.00%
0 / 21
0.00% covered (danger)
0.00%
0 / 1
20
 get_commercial_with_pendings
75.00% covered (warning)
75.00%
15 / 20
0.00% covered (danger)
0.00%
0 / 1
4.25
 get_responsible_for_work
0.00% covered (danger)
0.00%
0 / 21
0.00% covered (danger)
0.00%
0 / 1
20
 get_job_created_by
0.00% covered (danger)
0.00%
0 / 21
0.00% covered (danger)
0.00%
0 / 1
20
 get_accepted_by
0.00% covered (danger)
0.00%
0 / 21
0.00% covered (danger)
0.00%
0 / 1
20
 get_commercials
80.95% covered (warning)
80.95%
17 / 21
0.00% covered (danger)
0.00%
0 / 1
4.11
 update_users_itv
66.67% covered (warning)
66.67%
6 / 9
0.00% covered (danger)
0.00%
0 / 1
4.59
 list_roles
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
6
 update_role
72.73% covered (warning)
72.73%
8 / 11
0.00% covered (danger)
0.00%
0 / 1
3.18
 delete_role
57.14% covered (warning)
57.14%
4 / 7
0.00% covered (danger)
0.00%
0 / 1
2.31
 inactivate_user
0.00% covered (danger)
0.00%
0 / 66
0.00% covered (danger)
0.00%
0 / 1
210
 reactivate_user
0.00% covered (danger)
0.00%
0 / 18
0.00% covered (danger)
0.00%
0 / 1
20
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Exceptions\AppException;
6use App\Models\TblCompanyUsers;
7use App\Models\TblOngoingJobs;
8use App\Models\TblQuotations;
9use App\Models\TblRoles;
10use App\Models\TblUsers;
11use App\Services\UserCompanies;
12use Carbon\Carbon;
13use Illuminate\Contracts\Routing\ResponseFactory;
14use Illuminate\Http\Request;
15use Illuminate\Http\Response;
16use Illuminate\Support\Facades\App;
17use Illuminate\Support\Facades\Cache;
18use Illuminate\Support\Facades\DB;
19use Illuminate\Support\Facades\Http;
20use Illuminate\Support\Facades\Log;
21use Illuminate\Support\Str;
22
23class Users extends Controller
24{
25    private $locale;
26
27    private $userId;
28
29    private $region;
30
31    private $companyIds;
32
33    private readonly string $companyId;
34
35    public function __construct()
36    {
37        $this->locale = request()->header('Locale-Id');
38        $this->userId = request()->header('User-Id');
39        $this->region = request()->header('Region');
40
41        App::setLocale($this->locale);
42
43        $this->companyIds = [];
44
45        if ($this->region != null && $this->region != '' && $this->region != 'All') {
46            $this->region = urldecode((string) $this->region);
47
48            $query = 'SELECT
49                        b.company_id
50                    FROM
51                        tbl_company_users a
52                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
53                    WHERE
54                        a.user_id = ?
55                        AND b.region = ?';
56
57            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
58
59            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
60        } else {
61            // FIRE-1146: prefer the middleware-resolved attribute; fall back to a fresh fetch only on unattached paths.
62            $this->companyIds = request()->attributes->get('user_company_ids', UserCompanies::forUser((int) $this->userId));
63        }
64
65        $this->companyId = implode(',', $this->companyIds);
66    }
67
68    public function create_users(Request $request): ResponseFactory|Response
69    {
70
71        try {
72            $data = $request->all();
73            $data['role_id'] = 2;
74
75            $sData = [
76                'name' => $data['name'],
77                'email' => $data['email'],
78                'created_by' => $data['created_by'],
79                'role_id' => 2,
80                'sender_email' => $data['sender_email'],
81                'is_commercial' => $data['is_commercial'] ?? null,
82                'sender_enabled' => @$data['sender_enabled'],
83                'G3W_code' => $data['G3W_code'],
84            ];
85
86            $email = TblUsers::where('email', $data['email'])->count();
87
88            if ($email > 0) {
89                return response(['message' => 'KO', 'error' => __('language.email_already_exist')]);
90            }
91
92            $name = TblUsers::where('name', $data['name'])->count();
93
94            if ($name > 0) {
95                return response(['message' => 'KO', 'error' => __('language.name_already_exist')]);
96            }
97
98            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
99            $data['address'] = 'Madrid';
100            $data['city'] = 'Madrid';
101            $data['country'] = 'Spain';
102            $data['nickname'] = $data['name'].'-'.base64_encode($data['name'].date('ymdhis'));
103            $data['from_name'] = $data['name'];
104            $data['from_email'] = $data['sender_email'];
105            $data['reply_to'] = $data['sender_email'];
106            $data['reply_to_name'] = $data['name'];
107            $requestBody = $data;
108
109            $response = $sendgrid->client->verified_senders()->post($requestBody);
110            $x = json_decode((string) $response->body());
111
112            $result = TblUsers::create($sData);
113
114            if ($response->statusCode() >= 400) {
115                $errField = @$x->errors[0]->field;
116                $errMsg = @$x->errors[0]->message;
117                if (! ($errMsg === 'already exists' && $errField === 'from_email')) {
118                    Log::channel('email_log')->warning('SendGrid verified-sender create failed', [
119                        'sender_email' => $data['sender_email'],
120                        'status' => $response->statusCode(),
121                        'error' => "$errField: $errMsg",
122                    ]);
123                }
124            }
125
126            $isVerified = 'no';
127
128            if ($result) {
129                $u = TblUsers::where('id', $result->id)->first();
130
131                if ($u && $u->verified == 1) {
132                    $isVerified = 'yes';
133                }
134            }
135
136            return response(['message' => 'OK', 'data' => $response, 'is_verified' => $isVerified]);
137
138        } catch (\Exception $e) {
139            return response(['message' => 'KO', 'error' => $e->getMessage()]);
140        }
141    }
142
143    public function get_users($companyId = null): ResponseFactory|Response
144    {
145
146        try {
147
148            $column = '';
149
150            if ($companyId != null) {
151                $column = "(SELECT id FROM tbl_company_users WHERE user_id = a.id AND company_id = {$companyId}) user,";
152            }
153
154            $query = "SELECT
155                        a.id,
156                        a.name,
157                        a.email,
158                        DATE_FORMAT(a.created_at, '%b %d, %Y %l:%i%p') 'created_at',
159                        a.created_by,
160                        DATE_FORMAT(a.created_at, '%b %d, %Y %l:%i%p') 'updated_at',
161                        a.updated_by,
162                        a.sender_email,
163                        a.sender_enabled,
164                        a.is_itv,
165                        a.verified,
166                        a.is_commercial,
167                        a.is_active,
168                        a.inactivated_at,
169                        a.inactivated_by,
170                        {$column}
171                        a.default
172                    FROM tbl_users a
173                    ORDER BY a.name ASC";
174
175            $result = DB::select($query);
176
177            return response(['message' => 'OK', 'data' => $result]);
178
179        } catch (\Exception $e) {
180            report(AppException::fromException($e, 'GET_USERS_EXCEPTION'));
181
182            return response(['message' => 'KO', 'error' => $e->getMessage()]);
183        }
184    }
185
186    /**
187     * Users with access to a given region, for the region-scoped G3W user
188     * standardization dropdown on /normalize-g3w. A user has access to a region
189     * when tbl_company_users links them to a company in that region — the same
190     * notion of "access" used elsewhere in the app. Deliberately NOT filtered
191     * by is_commercial: that flag is only set on a subset of users, so gating
192     * on it dropped real mapping targets (e.g. Jordi, is_commercial NULL).
193     *
194     * Region comes from the Region header (normalised Catalunya -> Cataluña,
195     * the accented form stored in tbl_companies). An empty or "All" region
196     * returns every user — the frontend hides the section under "All", so this
197     * is only a defensive fallback.
198     */
199    public function get_users_by_region(): ResponseFactory|Response
200    {
201        try {
202            $region = urldecode((string) request()->header('Region'));
203
204            if ($region === 'Catalunya') {
205                $region = 'Cataluña';
206            }
207
208            if ($region === '' || $region === 'All') {
209                $query = 'SELECT DISTINCT u.id, u.name
210                            FROM tbl_users u
211                            ORDER BY u.name ASC';
212                $result = DB::select($query);
213            } else {
214                $query = 'SELECT DISTINCT u.id, u.name
215                            FROM tbl_users u
216                            INNER JOIN tbl_company_users cu ON cu.user_id = u.id
217                            INNER JOIN tbl_companies c ON c.company_id = cu.company_id
218                            WHERE c.region = :region
219                            ORDER BY u.name ASC';
220                $result = DB::select($query, ['region' => $region]);
221            }
222
223            return response(['message' => 'OK', 'data' => $result]);
224
225        } catch (\Exception $e) {
226            report(AppException::fromException($e, 'GET_USERS_BY_REGION_EXCEPTION'));
227
228            return response(['message' => 'KO', 'error' => $e->getMessage()]);
229        }
230    }
231
232    public function get_user($id): ResponseFactory|Response
233    {
234
235        try {
236
237            $id = addslashes((string) $id);
238
239            $e = TblUsers::where('id', $id)->first();
240
241            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
242
243            $response = $sendgrid->client->verified_senders()->get(null, [
244                'limit' => 1000,
245                'offset' => 0,
246            ]);
247
248            if ($response->statusCode() == 200) {
249                $x = json_decode((string) $response->body())->results;
250
251                foreach ($x as $item) {
252                    if ($item->from_email == $e->sender_email) {
253                        TblUsers::where('sender_email', $item->from_email)->update([
254                            'verified' => $item->verified,
255                            'response_id' => $item->id,
256                        ]);
257                        break;
258                    }
259                }
260            }
261
262            $query = "SELECT
263                        a.id,
264                        a.name,
265                        a.email,
266                        a.created_at,
267                        a.created_by,
268                        a.updated_at,
269                        a.updated_by,
270                        a.default,
271                        a.role_id,
272                        b.name 'role',
273                        a.verified,
274                        a.is_itv,
275                        a.sender_email,
276                        a.is_commercial,
277                        a.sender_enabled,
278                        a.G3W_code,
279                        a.can_download,
280                        a.is_active,
281                        a.inactivated_at,
282                        a.inactivated_by
283                    FROM tbl_users a
284                    LEFT JOIN tbl_roles b
285                        ON a.role_id = b.role_id
286                    WHERE a.id = {$id}";
287
288            $result = DB::select($query);
289
290            return response(['message' => 'OK', 'data' => $result]);
291
292        } catch (\Exception $e) {
293            report(AppException::fromException($e, 'GET_USER_EXCEPTION'));
294
295            return response(['message' => 'KO', 'error' => $e->getMessage()]);
296        }
297    }
298
299    public function getUserByName($name)
300    {
301        try {
302            $formattedName = str_replace('_', ' ', $name);
303            $user = TblUsers::where('name', $formattedName)->first();
304
305            if (! $user) {
306                return response()->json([
307                    'success' => false,
308                    'message' => 'User not found',
309                ], 404);
310            }
311
312            return response()->json([
313                'success' => true,
314                'data' => $user,
315            ], 200);
316        } catch (\Exception $e) {
317            report(AppException::fromException($e, 'GET_USER_BY_NAME_EXCEPTION'));
318
319            return response()->json([
320                'success' => false,
321                'message' => 'An error occurred',
322                'error' => $e->getMessage(),
323            ], 500);
324        }
325    }
326
327    public function getAllUserNames()
328    {
329        try {
330            $users = TblUsers::pluck('name');
331
332            return response()->json([
333                'success' => true,
334                'data' => $users,
335            ]);
336        } catch (\Exception $e) {
337            report(AppException::fromException($e, 'GET_ALL_USER_NAMES_EXCEPTION'));
338
339            return response()->json([
340                'success' => false,
341                'message' => 'Error fetching users',
342                'error' => $e->getMessage(),
343            ], 500);
344        }
345    }
346
347    public function get_user_by_email(Request $request): ResponseFactory|Response
348    {
349
350        try {
351
352            // Cloudflare Turnstile verification (only when token is provided, i.e. from login page)
353            $turnstileToken = $request->input('cf_turnstile_response');
354            if ($turnstileToken) {
355                $turnstileResponse = Http::asForm()->post('https://challenges.cloudflare.com/turnstile/v0/siteverify', [
356                    'secret' => config('services.cloudflare.turnstile_secret'),
357                    'response' => $turnstileToken,
358                    'remoteip' => $request->ip(),
359                ]);
360
361                if (! $turnstileResponse->json('success')) {
362                    return response(['message' => 'KO', 'error' => 'Turnstile verification failed'], 403);
363                }
364            }
365
366            $data = $request->all();
367            $bindings = ['email' => $data['email']];
368            $where = '';
369
370            if (isset($data['company_id'])) {
371                $where = ' AND c.company_id = :company_id ';
372                $bindings['company_id'] = $data['company_id'];
373            }
374
375            $query = "SELECT
376                        a.id,
377                        a.name,
378                        a.email,
379                        a.created_at,
380                        a.created_by,
381                        a.updated_at,
382                        a.updated_by,
383                        c.filename,
384                        a.default,
385                        a.role_id,
386                        d.name role,
387                        b.company_id,
388                        c.logo,
389                        c.name company_name,
390                        c.default_page,
391                        b.can_read,
392                        b.can_write,
393                        a.is_loggedin,
394                        c.is_send,
395                        c.limit_send,
396                        c.is_send_follow_up,
397                        c.is_send_request,
398                        c.is_send_g3w,
399                        c.revenue_per_employee_per_day,
400                        c.minimum_margin,
401                        c.general_costs,
402                        c.hours_per_worker_per_day,
403                        c.cost_of_hour,
404                        c.hours_per_worker_per_day_percentage,
405                        c.convert_to_job_amount_limit,
406                        c.last_follow_up_date,
407                        c.limit_reminder_emails,
408                        c.workflow_budget_size,
409                        c.region,
410                        COALESCE(f.approver_id, e.approver_id) AS approver_id,
411                        a.is_itv,
412                        a.can_download,
413                        a.G3W_code,
414                        a.api_token
415                    FROM tbl_users a
416                    LEFT JOIN tbl_company_users b
417                        ON a.id = b.user_id AND (b.is_selected = 1 OR b.can_read = 1)
418                    LEFT JOIN tbl_companies c
419                        ON b.company_id = c.company_id
420                    LEFT JOIN tbl_roles d
421                        ON a.role_id = d.role_id
422                    LEFT JOIN tbl_approvers e
423                        ON a.id = e.user_id
424                    LEFT JOIN tbl_approvers_v2 f
425                        ON a.id = f.user_id
426                    WHERE a.email = :email
427                    {$where}
428                    ORDER BY b.is_selected DESC
429                    LIMIT 1";
430
431            $result = DB::select($query, $bindings);
432
433            if (isset($data['switch']) && $data['switch'] == 1) {
434                $result[0]->is_loggedin = 1;
435            }
436
437            return response(['message' => 'OK', 'data' => $result]);
438
439        } catch (\Exception $e) {
440            report(AppException::fromException($e, 'GET_USER_BY_EMAIL_EXCEPTION'));
441
442            return response(['message' => 'KO', 'error' => $e->getMessage()]);
443        }
444    }
445
446    public function add_company_user(Request $request): ResponseFactory|Response
447    {
448
449        try {
450
451            $data = $request->all();
452            $id = addslashes((string) $data['user_id']);
453
454            if (isset($data['company_ids'])) {
455
456                $ids = TblCompanyUsers::where('user_id', $id)->pluck('company_id')->toArray();
457                $companyUsers = [];
458
459                for ($i = 0; $i < count($data['company_ids']); $i++) {
460                    if (! in_array($data['company_ids'][$i], $ids)) {
461                        array_push(
462                            $companyUsers,
463                            [
464                                'user_id' => $id,
465                                'company_id' => $data['company_ids'][$i],
466                                'can_read' => 1,
467                                'can_write' => 0,
468                                'created_by' => $data['created_by'],
469                            ]
470                        );
471                    }
472                }
473
474                TblCompanyUsers::insert($companyUsers);
475            }
476
477            // FIRE-1146: the user's company list just changed — drop the cached copy.
478            UserCompanies::forget((int) $id);
479
480            return response(['message' => 'OK']);
481
482        } catch (\Exception $e) {
483            report(AppException::fromException($e, 'ADD_COMPANY_USER_EXCEPTION'));
484
485            return response(['message' => 'KO', 'error' => $e->getMessage()]);
486        }
487
488    }
489
490    public function delete_company_user(Request $request): ResponseFactory|Response
491    {
492
493        try {
494
495            $data = $request->all();
496            $id = addslashes((string) $data['user_id']);
497            $companyId = addslashes((string) $data['company_id']);
498
499            TblCompanyUsers::where('company_id', $companyId)->where('user_id', $id)->delete();
500
501            // FIRE-1146: the user's company list just changed — drop the cached copy.
502            UserCompanies::forget((int) $id);
503
504            return response(['message' => 'OK']);
505
506        } catch (\Exception $e) {
507            report(AppException::fromException($e, 'DELETE_COMPANY_USER_EXCEPTION'));
508
509            return response(['message' => 'KO', 'error' => $e->getMessage()]);
510        }
511
512    }
513
514    public function update_users(Request $request, $id): ResponseFactory|Response
515    {
516
517        try {
518
519            $data = $request->all();
520            $id = addslashes((string) $id);
521
522            if (! isset($data['is_loggedin'])) {
523                $data['is_loggedin'] = 0;
524            } else {
525
526                if ($data['is_loggedin'] == 1) {
527                    // Reuse the user's existing token if it's still valid, so a
528                    // login from a second tab/device on the same account doesn't
529                    // invalidate the first one. Only mint a fresh token when
530                    // there isn't one or it has expired.
531                    $existing = TblUsers::where('id', $id)
532                        ->select('api_token', 'token_expires_at')
533                        ->first();
534
535                    $stillValid = $existing
536                        && $existing->api_token
537                        && $existing->token_expires_at
538                        && Carbon::parse($existing->token_expires_at)->isFuture();
539
540                    if ($stillValid) {
541                        $data['api_token'] = $existing->api_token;
542                        $data['token_expires_at'] = $existing->token_expires_at;
543                    } else {
544                        $data['api_token'] = Str::random(60);
545                        $data['token_expires_at'] = Carbon::now()->addHours(72);
546                    }
547                } else {
548                    $data['api_token'] = null;
549                    $data['token_expires_at'] = null;
550                }
551
552                $result = TblUsers::where('id', $id)->update($data);
553
554                return response(['message' => 'OK', 'data' => $data]);
555            }
556
557            $sData = [
558                'name' => $data['name'],
559                'email' => $data['email'],
560                'role_id' => $data['role_id'],
561                'updated_by' => $data['updated_by'],
562                'sender_email' => $data['sender_email'],
563                'G3W_code' => $data['G3W_code'],
564                'sender_enabled' => @$data['sender_enabled'],
565                'is_commercial' => $data['is_commercial'],
566                'can_download' => $data['can_download'] ?? 1,
567            ];
568
569            if (isset($data['email'])) {
570                $email = TblUsers::where('email', $data['email'])->first();
571
572                if ($email != null && $id != $email->id) {
573                    return response(['message' => 'KO', 'error' => __('language.email_already_exist')]);
574                }
575            }
576
577            $sData['updated_at'] = date('Y-m-d H:i:s');
578
579            $u = TblUsers::where('id', $id)->first();
580
581            if (isset($data['name'])) {
582                TblQuotations::where('commercial', $u->name)->update(
583                    [
584                        'commercial' => $data['name'],
585                    ]
586                );
587
588                TblQuotations::where('created_by', $u->name)->update(
589                    [
590                        'created_by' => $data['name'],
591                    ]
592                );
593
594                TblQuotations::where('updated_by', $u->name)->update(
595                    [
596                        'updated_by' => $data['name'],
597                    ]
598                );
599
600                TblOngoingJobs::where('responsible_for_work', $u->name)->update(
601                    [
602                        'responsible_for_work' => $data['name'],
603                    ]
604                );
605
606                TblOngoingJobs::where('created_by', $u->name)->update(
607                    [
608                        'created_by' => $data['name'],
609                    ]
610                );
611
612                TblOngoingJobs::where('updated_by', $u->name)->update(
613                    [
614                        'updated_by' => $data['name'],
615                    ]
616                );
617            }
618
619            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
620            $data['address'] = 'Madrid';
621            $data['city'] = 'Madrid';
622            $data['country'] = 'Spain';
623            $data['nickname'] = $data['name'].'-'.base64_encode($data['name'].date('ymdhis'));
624            $data['from_name'] = $data['name'];
625            $data['from_email'] = $data['sender_email'];
626            $data['reply_to'] = $data['sender_email'];
627            $data['reply_to_name'] = $data['name'];
628            $requestBody = $data;
629            $error = false;
630            $response = [];
631
632            if ($u->response_id && $u->sender_email == $data['sender_email']) {
633                $response = $sendgrid->client->verified_senders()->_($u->response_id)->patch($requestBody);
634            } else {
635                $requestBody['nickname'] = $data['name'].'-'.base64_encode($data['name'].date('ymdhis'));
636                $response = $sendgrid->client->verified_senders()->post($requestBody);
637            }
638
639            $x = json_decode((string) $response->body());
640
641            if ($response->statusCode() == 200 || is_numeric(@$x->id)) {
642                $sData['response_id'] = $x->id;
643                $result = TblUsers::where('id', $id)->update($sData);
644                Log::channel('email_log')->info('USER EMAIL: '.$data['sender_email'].' - VERIFICATION SENT');
645            } else {
646                $error = true;
647                Log::channel('email_log')->error('REQUEST BODY USER: - '.$response->body());
648            }
649
650            $response = json_decode((string) $response->body());
651
652            if ($error) {
653                if ($response->errors[0]->message == 'already exists' && $response->errors[0]->field == 'from_email') {
654                    TblUsers::where('id', $id)->update($sData);
655
656                    return response(['message' => 'OK', 'data' => $data, 'is_verified' => 'yes']);
657                }
658
659                $errMessage = @$response->errors[0]->field.': '.@$response->errors[0]->message;
660
661                return response(['message' => 'KO', 'error' => $errMessage]);
662            } else {
663                $isVerified = 'no';
664
665                if ($response->verified) {
666                    $isVerified = 'yes';
667                }
668
669                return response(['message' => 'OK', 'data' => $response, 'is_verified' => $isVerified]);
670            }
671
672        } catch (\Exception $e) {
673            report(AppException::fromException($e, 'UPDATE_USER_EXCEPTION'));
674
675            return response(['message' => 'KO', 'error' => $e->getMessage()]);
676        }
677    }
678
679    public function delete_users($id): ResponseFactory|Response
680    {
681        // FIRE-1752: hard delete is disabled. It was the mechanism that
682        // erased the original commercial from tbl_users and rewrote
683        // tbl_quotations.created_by to 'Fire Service Titan', making the
684        // reassignment history unrecoverable in-place. Removing a user is
685        // now done via inactivate_user (is_active = 0). If a caller reaches
686        // this method, refuse with 410 Gone so the incident is visible
687        // instead of silently anonymising rows.
688        return response([
689            'message' => 'gone',
690            'error' => 'delete_users is disabled — use inactivateUser instead (FIRE-1752).',
691        ], 410);
692
693        // The historical implementation follows for git-blame context. It
694        // will never execute because of the early return above.
695        try {
696
697            $id = addslashes((string) $id);
698
699            $user = TblUsers::where('id', $id)->first();
700
701            $count = TblQuotations::where('commercial', $user->name)->where('company_id', '>', 0)->count();
702            $countQ = TblOngoingJobs::where('responsible_for_work', $user->name)->where('company_id', '>', 0)->count();
703
704            if ($count > 0 || $countQ > 0) {
705                $urlOrder = config('app.frontend_url')."orders?commercial={$user->name}&company_id=0";
706                $urlJob = config('app.frontend_url')."ongoing-jobs?responsible_for_work={$user->name}&company_id=0";
707                $urlOrder = "<a href='{$urlOrder}' target='_blank'>{$count}</a>";
708                $urlJob = "<a href='{$urlJob}' target='_blank'>{$countQ}</a>";
709
710                return response(['message' => 'user_cannot_be_deleted', 'error' => __('language.user_cannot_be_deleted'), 'total_job' => $urlJob, 'total_order' => $urlOrder]);
711            }
712
713            $count = TblQuotations::where('created_by', $user->name)->count();
714
715            if ($count > 0) {
716                $query = "UPDATE tbl_quotations SET created_by = 'Fire Service Titan', updated_at = '".date('Y-m-d H:i:s')."' WHERE created_by = '{$user->name}'";
717                DB::select($query);
718            }
719
720            $count = TblOngoingJobs::where('created_by', $user->name)->count();
721
722            if ($count > 0) {
723                $query = "UPDATE tbl_ongoing_jobs SET created_by = 'Fire Service Titan' WHERE created_by = '{$user->name}'";
724                DB::select($query);
725            }
726
727            $result = TblUsers::where('id', $id)->delete();
728            TblCompanyUsers::where('user_id', $id)->delete();
729
730            // FIRE-1146: the user is gone — drop their cached company list.
731            UserCompanies::forget((int) $id);
732
733            return response(['message' => 'OK', 'data' => $result]);
734
735        } catch (\Exception $e) {
736            report(AppException::fromException($e, 'DELETE_USER_EXCEPTION'));
737
738            return response(['message' => 'KO', 'error' => $e->getMessage()]);
739        }
740    }
741
742    public function get_roles(): ResponseFactory|Response
743    {
744
745        try {
746
747            $result = TblRoles::get();
748
749            return response(['message' => 'OK', 'data' => $result]);
750
751        } catch (\Exception $e) {
752            report(AppException::fromException($e, 'GET_ROLES_EXCEPTION'));
753
754            return response(['message' => 'KO', 'error' => $e->getMessage()]);
755        }
756    }
757
758    public function get_all_commercials(Request $request): ResponseFactory|Response
759    {
760
761        try {
762
763            $data = $request->all();
764
765            $companyIds = implode(',', $data['company_ids']);
766
767            $query = "SELECT
768                    b.name
769                FROM
770                    tbl_company_users a
771                    LEFT JOIN tbl_users b ON a.user_id = b.id
772                WHERE
773                    a.company_id IN ({$companyIds})
774                GROUP BY
775                    a.user_id
776                HAVING
777                    COUNT(DISTINCT a.company_id) = (
778                    SELECT
779                        COUNT(DISTINCT company_id)
780                    FROM
781                        tbl_company_users
782                    WHERE
783                        company_id IN ({$companyIds})
784                    )";
785
786            $result = DB::select($query);
787
788            return response(['message' => 'OK', 'data' => $result]);
789
790        } catch (\Exception $e) {
791            report(AppException::fromException($e, 'GET_ALL_COMMERCIALS_EXCEPTION'));
792
793            return response(['message' => 'KO', 'error' => $e->getMessage()]);
794        }
795
796    }
797
798    public function get_g3w_warning_fields($companyId): ResponseFactory|Response
799    {
800
801        try {
802
803            $companyId = addslashes((string) $companyId);
804
805            $where = '';
806
807            if ($companyId != 0) {
808                $where = "WHERE company_id = {$companyId} ";
809            } else {
810                $where = "WHERE company_id IN ({$this->companyId}) ";
811            }
812
813            $query = "SELECT
814                        DISTINCT g3w_warning_fields
815                    FROM
816                        tbl_quotations
817                    {$where}
818                    ORDER BY
819                        g3w_warning_fields ASC";
820
821            $value = Cache::get(base64_encode($query));
822
823            if (! $value) {
824                $result = DB::select($query);
825
826                $g3wWarningFields = array_map(fn ($row) => $row->g3w_warning_fields, $result);
827
828                Cache::put(base64_encode($query), $g3wWarningFields, 600);
829            } else {
830                $g3wWarningFields = $value;
831            }
832
833            return response([
834                'message' => 'OK',
835                'g3wWarningFields' => $g3wWarningFields,
836            ]);
837
838        } catch (\Exception $e) {
839            report(AppException::fromException($e, 'GET_G3W_WARNING_FIELDS_EXCEPTION'));
840
841            return response(['message' => 'KO', 'error' => $e->getMessage()]);
842        }
843    }
844
845    public function get_created_by($companyId): ResponseFactory|Response
846    {
847
848        try {
849
850            $companyId = addslashes((string) $companyId);
851
852            $where = '';
853
854            if ($companyId != 0) {
855                $where = "WHERE company_id = {$companyId} ";
856            } else {
857                $where = "WHERE company_id IN ({$this->companyId}) ";
858            }
859
860            $query = "SELECT
861                        DISTINCT created_by
862                    FROM
863                        tbl_quotations
864                    {$where}
865                    ORDER BY
866                        created_by ASC";
867
868            $value = Cache::get(base64_encode($query));
869
870            if (! $value) {
871                $result = DB::select($query);
872
873                $createdBy = array_map(fn ($row) => $row->created_by, $result);
874
875                Cache::put(base64_encode($query), $createdBy, 600);
876            } else {
877                $createdBy = $value;
878            }
879
880            return response([
881                'message' => 'OK',
882                'createdBy' => $createdBy,
883            ]);
884
885        } catch (\Exception $e) {
886            report(AppException::fromException($e, 'GET_CREATED_BY_EXCEPTION'));
887
888            return response(['message' => 'KO', 'error' => $e->getMessage()]);
889        }
890    }
891
892    public function get_commercial_with_pendings($companyId): ResponseFactory|Response
893    {
894
895        try {
896
897            $companyId = addslashes((string) $companyId);
898
899            $where = '';
900
901            if ($companyId != 0) {
902                $where = "WHERE company_id = {$companyId} ";
903            } else {
904                $where = "WHERE company_id IN ({$this->companyId}) ";
905            }
906
907            $query = "SELECT
908                        b.id userId,
909                        b.name commercial,
910                        a.total_error totalError,
911                        a.total_pending_follow_ups totalPendingFollowUps,
912                        a.total_request_and_visits totalRequestAndVisit,
913                        a.total_g3w_error totalG3WError
914                    FROM
915                        tbl_company_users a
916                    LEFT JOIN tbl_users b
917                        ON a.user_id = b.id
918                    {$where}
919                    ORDER BY
920                        b.name ASC";
921
922            $value = Cache::get(base64_encode($query));
923
924            if (! $value) {
925                $commercial = DB::select($query);
926
927                Cache::put(base64_encode($query), $commercial, 600);
928            } else {
929                $commercial = $value;
930            }
931
932            return response([
933                'message' => 'OK',
934                'commercialWithPendings' => $commercial,
935            ]);
936
937        } catch (\Exception $e) {
938            report(AppException::fromException($e, 'GET_COMMERCIAL_WITH_PENDINGS_EXCEPTION'));
939
940            return response(['message' => 'KO', 'error' => $e->getMessage()]);
941        }
942    }
943
944    public function get_responsible_for_work($companyId): ResponseFactory|Response
945    {
946
947        try {
948
949            $companyId = addslashes((string) $companyId);
950
951            $where = '';
952
953            if ($companyId != 0) {
954                $where = "WHERE company_id = {$companyId} ";
955            } else {
956                $where = "WHERE company_id IN ({$this->companyId}) ";
957            }
958
959            $query = "SELECT
960                        DISTINCT responsible_for_work
961                    FROM
962                        tbl_ongoing_jobs
963                    {$where}
964                    ORDER BY
965                    responsible_for_work ASC";
966
967            $value = Cache::get(base64_encode($query));
968
969            if (! $value) {
970                $result = DB::select($query);
971
972                $responsibleForWork = array_map(fn ($row) => $row->responsible_for_work, $result);
973
974                Cache::put(base64_encode($query), $responsibleForWork, 600);
975            } else {
976                $responsibleForWork = $value;
977            }
978
979            return response([
980                'message' => 'OK',
981                'responsibleForWork' => $responsibleForWork,
982            ]);
983
984        } catch (\Exception $e) {
985            report(AppException::fromException($e, 'GET_RESPONSIBLE_FOR_WORK_EXCEPTION'));
986
987            return response(['message' => 'KO', 'error' => $e->getMessage()]);
988        }
989
990    }
991
992    public function get_job_created_by($companyId): ResponseFactory|Response
993    {
994
995        try {
996
997            $companyId = addslashes((string) $companyId);
998
999            $where = '';
1000
1001            if ($companyId != 0) {
1002                $where = "WHERE company_id = {$companyId} ";
1003            } else {
1004                $where = "WHERE company_id IN ({$this->companyId}) ";
1005            }
1006
1007            $query = "SELECT
1008                        DISTINCT created_by
1009                    FROM
1010                        tbl_ongoing_jobs
1011                    {$where}
1012                    ORDER BY
1013                    created_by ASC";
1014
1015            $value = Cache::get(base64_encode($query));
1016
1017            if (! $value) {
1018                $result = DB::select($query);
1019
1020                $jobCreatedBy = array_map(fn ($row) => $row->created_by, $result);
1021
1022                Cache::put(base64_encode($query), $jobCreatedBy, 600);
1023            } else {
1024                $jobCreatedBy = $value;
1025            }
1026
1027            return response([
1028                'message' => 'OK',
1029                'jobCreatedBy' => $jobCreatedBy,
1030            ]);
1031
1032        } catch (\Exception $e) {
1033            report(AppException::fromException($e, 'GET_JOB_CREATED_BY_EXCEPTION'));
1034
1035            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1036        }
1037    }
1038
1039    public function get_accepted_by($companyId): ResponseFactory|Response
1040    {
1041
1042        try {
1043
1044            $companyId = addslashes((string) $companyId);
1045
1046            $where = '';
1047
1048            if ($companyId != 0) {
1049                $where = "WHERE company_id = {$companyId} ";
1050            } else {
1051                $where = "WHERE company_id IN ({$this->companyId}) ";
1052            }
1053
1054            $query = "SELECT
1055                        DISTINCT accepted_by
1056                    FROM
1057                        tbl_quotations
1058                    {$where}
1059                    ORDER BY
1060                        accepted_by ASC";
1061
1062            $value = Cache::get(base64_encode($query));
1063
1064            if (! $value) {
1065                $result = DB::select($query);
1066
1067                $acceptedBy = array_map(fn ($row) => $row->accepted_by, $result);
1068
1069                Cache::put(base64_encode($query), $acceptedBy, 600);
1070            } else {
1071                $acceptedBy = $value;
1072            }
1073
1074            return response([
1075                'message' => 'OK',
1076                'acceptedBy' => $acceptedBy,
1077            ]);
1078
1079        } catch (\Exception $e) {
1080            report(AppException::fromException($e, 'GET_ACCEPTED_BY_EXCEPTION'));
1081
1082            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1083        }
1084    }
1085
1086    public function get_commercials($companyId): ResponseFactory|Response
1087    {
1088
1089        try {
1090
1091            $companyId = addslashes((string) $companyId);
1092
1093            $where = '';
1094
1095            if ($companyId != 0) {
1096                $where = "WHERE company_id = {$companyId} ";
1097            } else {
1098                $where = "WHERE company_id IN ({$this->companyId}) ";
1099            }
1100
1101            $query = "SELECT
1102                        DISTINCT commercial
1103                    FROM
1104                        tbl_quotations
1105                    {$where}
1106                    ORDER BY
1107                        commercial ASC";
1108
1109            $value = Cache::get(base64_encode($query));
1110
1111            if (! $value) {
1112                $result = DB::select($query);
1113
1114                $commercials = array_map(fn ($row) => $row->commercial, $result);
1115
1116                Cache::put(base64_encode($query), $commercials, 600);
1117            } else {
1118                $commercials = $value;
1119            }
1120
1121            return response([
1122                'message' => 'OK',
1123                'commercials' => $commercials,
1124            ]);
1125
1126        } catch (\Exception $e) {
1127            report(AppException::fromException($e, 'GET_COMMERCIALS_EXCEPTION'));
1128
1129            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1130        }
1131    }
1132
1133    public function update_users_itv(Request $request): ResponseFactory|Response
1134    {
1135
1136        try {
1137
1138            $data = $request->all();
1139
1140            if (isset($data['user_ids'])) {
1141                TblUsers::query()->update(['is_itv' => null]);
1142                for ($i = 0; $i < count($data['user_ids']); $i++) {
1143                    TblUsers::where('id', $data['user_ids'][$i])->update(['is_itv' => 1]);
1144                }
1145            }
1146
1147            return response(['message' => 'OK']);
1148
1149        } catch (\Exception $e) {
1150            report(AppException::fromException($e, 'UPDATE_USERS_ITV_EXCEPTION'));
1151
1152            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1153        }
1154
1155    }
1156
1157    public function list_roles(): ResponseFactory|Response
1158    {
1159
1160        try {
1161
1162            $result = TblRoles::get();
1163
1164            return response(['message' => 'OK', 'data' => $result]);
1165
1166        } catch (\Exception $e) {
1167            report(AppException::fromException($e, 'LIST_ROLES_EXCEPTION'));
1168
1169            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1170        }
1171
1172    }
1173
1174    public function update_role(Request $request): ResponseFactory|Response
1175    {
1176
1177        try {
1178
1179            $data = $request->all();
1180
1181            foreach ($data as $item) {
1182                $id = $item['role_id'];
1183                unset($item['role_id']);
1184
1185                $item['updated_at'] = date('Y-m-d H:i:s');
1186                TblRoles::where('role_id', $id)->update($item);
1187            }
1188
1189            $result = TblRoles::get();
1190
1191            return response(['message' => 'OK', 'data' => $result]);
1192
1193        } catch (\Exception $e) {
1194            report(AppException::fromException($e, 'UPDATE_ROLE_EXCEPTION'));
1195
1196            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1197        }
1198    }
1199
1200    public function delete_role($id): ResponseFactory|Response
1201    {
1202
1203        try {
1204
1205            $id = addslashes((string) $id);
1206
1207            TblRoles::where('role_id', $id)->delete();
1208
1209            $result = TblRoles::get();
1210
1211            return response(['message' => 'OK', 'data' => $result]);
1212
1213        } catch (\Exception $e) {
1214            report(AppException::fromException($e, 'DELETE_ROLE_EXCEPTION'));
1215
1216            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1217        }
1218    }
1219
1220    /**
1221     * FIRE-1539: Inactivate a user + reassign their live-state quotations to
1222     * another commercial. Preserve original commercial on closed-state rows.
1223     * For "special" statuses (2 Enviado, 17 Enviado-IA) preserve the sender in
1224     * `commercial` but set `follow_up_commercial` = new commercial so future
1225     * follow-ups go from the new sales rep.
1226     *
1227     * Payload: { new_commercial_id: <int>, updated_by: <string?> }
1228     *
1229     * Guards:
1230     *  - user exists and is currently active
1231     *  - new commercial is a different, active user of the same company_id
1232     *  - target user is NOT an approver (would break approval flow) → 422
1233     */
1234    public function inactivate_user(Request $request, $userId): ResponseFactory|Response
1235    {
1236        try {
1237            $userId = (int) addslashes((string) $userId);
1238            $data = $request->all();
1239            $newCommercialId = isset($data['new_commercial_id']) ? (int) $data['new_commercial_id'] : 0;
1240            $updatedBy = $data['updated_by'] ?? 'System';
1241
1242            if (! $newCommercialId) {
1243                return response(['message' => 'KO', 'error' => 'new_commercial_id_required'], 422);
1244            }
1245
1246            $userToInactivate = TblUsers::where('id', $userId)->first();
1247            if (! $userToInactivate) {
1248                return response(['message' => 'KO', 'error' => 'user_not_found'], 404);
1249            }
1250            if ((int) ($userToInactivate->is_active ?? 1) === 0) {
1251                return response(['message' => 'KO', 'error' => 'user_already_inactive'], 422);
1252            }
1253
1254            $newCommercial = TblUsers::where('id', $newCommercialId)->first();
1255            if (! $newCommercial) {
1256                return response(['message' => 'KO', 'error' => 'new_commercial_not_found'], 404);
1257            }
1258            if ((int) ($newCommercial->is_active ?? 1) === 0) {
1259                return response(['message' => 'KO', 'error' => 'new_commercial_is_inactive'], 422);
1260            }
1261            if ($newCommercial->id === $userToInactivate->id) {
1262                return response(['message' => 'KO', 'error' => 'new_commercial_must_differ'], 422);
1263            }
1264
1265            $sharedCompany = TblCompanyUsers::where('user_id', $userToInactivate->id)
1266                ->whereIn('company_id', function ($q) use ($newCommercial): void {
1267                    $q->select('company_id')->from('tbl_company_users')->where('user_id', $newCommercial->id);
1268                })->exists();
1269            if (! $sharedCompany) {
1270                return response(['message' => 'KO', 'error' => 'new_commercial_not_in_same_company'], 422);
1271            }
1272
1273            $isApprover = DB::table('tbl_approvers_v2')->where('user_id', $userToInactivate->id)->exists()
1274                || DB::table('tbl_workflow_approvers')->where('user_id', $userToInactivate->id)->exists()
1275                || DB::table('tbl_approvers')->where('user_id', $userToInactivate->id)->exists();
1276            if ($isApprover) {
1277                return response([
1278                    'message' => 'KO',
1279                    'error' => 'user_is_approver',
1280                    'detail' => 'Este usuario es aprobador. Reasigna su rol de aprobador antes de inactivarlo.',
1281                ], 422);
1282            }
1283
1284            // Ticket FIRE-1539 mapping (verify against tbl_budget_status if IDs shift).
1285            $reassignStatuses = [1, 6, 11, 12, 16, 21, 22];
1286            $specialStatuses = [2, 17];
1287
1288            DB::beginTransaction();
1289            try {
1290                $oldName = $userToInactivate->name;
1291                $newName = $newCommercial->name;
1292
1293                $reassigned = TblQuotations::where('commercial', $oldName)
1294                    ->whereIn('budget_status_id', $reassignStatuses)
1295                    ->update(['commercial' => $newName, 'updated_by' => $updatedBy, 'updated_at' => date('Y-m-d H:i:s')]);
1296
1297                $specialRouted = TblQuotations::where('commercial', $oldName)
1298                    ->whereIn('budget_status_id', $specialStatuses)
1299                    ->update(['follow_up_commercial' => $newName, 'updated_by' => $updatedBy, 'updated_at' => date('Y-m-d H:i:s')]);
1300
1301                $userToInactivate->update([
1302                    'is_active' => 0,
1303                    'inactivated_at' => date('Y-m-d H:i:s'),
1304                    'inactivated_by' => $updatedBy,
1305                ]);
1306
1307                DB::commit();
1308
1309                return response([
1310                    'message' => 'OK',
1311                    'data' => [
1312                        'user_id' => $userToInactivate->id,
1313                        'old_commercial' => $oldName,
1314                        'new_commercial' => $newName,
1315                        'quotations_reassigned' => $reassigned,
1316                        'quotations_special_routed' => $specialRouted,
1317                    ],
1318                ]);
1319            } catch (\Throwable $e) {
1320                DB::rollBack();
1321                throw $e;
1322            }
1323        } catch (\Exception $e) {
1324            report(AppException::fromException($e, 'INACTIVATE_USER_EXCEPTION'));
1325
1326            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1327        }
1328    }
1329
1330    /**
1331     * FIRE-1539: Reactivate a previously inactivated user. Prior quotation
1332     * reassignments are NOT reverted — the user resumes empty and any new
1333     * quotations must be assigned explicitly.
1334     */
1335    public function reactivate_user(Request $request, $userId): ResponseFactory|Response
1336    {
1337        try {
1338            $userId = (int) addslashes((string) $userId);
1339            $updatedBy = $request->input('updated_by', 'System');
1340
1341            $user = TblUsers::where('id', $userId)->first();
1342            if (! $user) {
1343                return response(['message' => 'KO', 'error' => 'user_not_found'], 404);
1344            }
1345            if ((int) ($user->is_active ?? 1) === 1) {
1346                return response(['message' => 'KO', 'error' => 'user_already_active'], 422);
1347            }
1348
1349            $user->update([
1350                'is_active' => 1,
1351                'inactivated_at' => null,
1352                'inactivated_by' => null,
1353                'updated_by' => $updatedBy,
1354                'updated_at' => date('Y-m-d H:i:s'),
1355            ]);
1356
1357            return response(['message' => 'OK', 'data' => ['user_id' => $user->id]]);
1358        } catch (\Exception $e) {
1359            report(AppException::fromException($e, 'REACTIVATE_USER_EXCEPTION'));
1360
1361            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1362        }
1363    }
1364}