Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
7.05% covered (danger)
7.05%
150 / 2128
1.33% covered (danger)
1.33%
1 / 75
CRAP
0.00% covered (danger)
0.00%
0 / 1
Notifications
7.05% covered (danger)
7.05%
150 / 2128
1.33% covered (danger)
1.33%
1 / 75
121913.32
0.00% covered (danger)
0.00%
0 / 1
 __construct
0.00% covered (danger)
0.00%
0 / 13
0.00% covered (danger)
0.00%
0 / 1
20
 list_notifications
0.00% covered (danger)
0.00%
0 / 24
0.00% covered (danger)
0.00%
0 / 1
6
 update_notifications
0.00% covered (danger)
0.00%
0 / 17
0.00% covered (danger)
0.00%
0 / 1
12
 send_follow_up_notification
0.00% covered (danger)
0.00%
0 / 138
0.00% covered (danger)
0.00%
0 / 1
272
 send_request
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
12
 send_g3w_warning
0.00% covered (danger)
0.00%
0 / 20
0.00% covered (danger)
0.00%
0 / 1
12
 send_request_notification
0.00% covered (danger)
0.00%
0 / 149
0.00% covered (danger)
0.00%
0 / 1
380
 get_cc_bcc
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 delete_cc_bcc
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 update_cc_bcc
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
20
 create_cc_bcc
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 isEmailValid
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
0.00%
0 / 1
6
 delete_notifications
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
6
 list_notification_logs
0.00% covered (danger)
0.00%
0 / 28
0.00% covered (danger)
0.00%
0 / 1
20
 delete_notification_logs
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 get_cc_g3w
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 delete_cc_g3w
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 update_cc_g3w
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
20
 create_cc_g3w
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 get_cc_bcc_request
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 delete_cc_bcc_request
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 update_cc_bcc_request
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
20
 create_cc_bcc_request
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 send_executive
0.00% covered (danger)
0.00%
0 / 23
0.00% covered (danger)
0.00%
0 / 1
72
 send_executive_report
0.00% covered (danger)
0.00%
0 / 185
0.00% covered (danger)
0.00%
0 / 1
272
 get_executive_reports
0.00% covered (danger)
0.00%
0 / 65
0.00% covered (danger)
0.00%
0 / 1
30
 currency
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 wcrIssuedDateSql
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 send_weekly_commercial_report
0.00% covered (danger)
0.00%
0 / 34
0.00% covered (danger)
0.00%
0 / 1
420
 send_weekly_commercial_report_email
0.00% covered (danger)
0.00%
0 / 72
0.00% covered (danger)
0.00%
0 / 1
210
 get_weekly_regional_performance
0.00% covered (danger)
0.00%
0 / 119
0.00% covered (danger)
0.00%
0 / 1
272
 query_regional_metrics
42.22% covered (danger)
42.22%
19 / 45
0.00% covered (danger)
0.00%
0 / 1
2.77
 query_regional_accepted_metrics
45.95% covered (danger)
45.95%
17 / 37
0.00% covered (danger)
0.00%
0 / 1
2.63
 blank_region_metrics
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
2
 add_region_metrics
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 ytd_diff_pct
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 get_weekly_individual_performance
31.94% covered (danger)
31.94%
23 / 72
0.00% covered (danger)
0.00%
0 / 1
41.52
 blank_commercial_metrics
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
2
 get_weekly_year_series
92.86% covered (success)
92.86%
52 / 56
0.00% covered (danger)
0.00%
0 / 1
16.09
 get_week_notes
66.67% covered (warning)
66.67%
8 / 12
0.00% covered (danger)
0.00%
0 / 1
3.33
 get_weekly_streaks
49.15% covered (danger)
49.15%
29 / 59
0.00% covered (danger)
0.00%
0 / 1
35.22
 wcr_tally_weeks
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
72
 build_weekly_commercial_report_html
0.00% covered (danger)
0.00%
0 / 137
0.00% covered (danger)
0.00%
0 / 1
56
 wcr_section
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
6
 wcr_two_col
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
6
 wcr_delta_class
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
20
 wcr_group_table
0.00% covered (danger)
0.00%
0 / 82
0.00% covered (danger)
0.00%
0 / 1
72
 wcr_obj_strip
0.00% covered (danger)
0.00%
0 / 31
0.00% covered (danger)
0.00%
0 / 1
56
 wcr_regional_table
0.00% covered (danger)
0.00%
0 / 83
0.00% covered (danger)
0.00%
0 / 1
210
 wcr_mix_table
0.00% covered (danger)
0.00%
0 / 40
0.00% covered (danger)
0.00%
0 / 1
110
 wcr_card_shell
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 wcr_top_card
0.00% covered (danger)
0.00%
0 / 26
0.00% covered (danger)
0.00%
0 / 1
20
 wcr_year_charts_section
0.00% covered (danger)
0.00%
0 / 38
0.00% covered (danger)
0.00%
0 / 1
132
 wcr_streak_badge
0.00% covered (danger)
0.00%
0 / 17
0.00% covered (danger)
0.00%
0 / 1
30
 wcr_pills
0.00% covered (danger)
0.00%
0 / 16
0.00% covered (danger)
0.00%
0 / 1
20
 wcr_under_card
0.00% covered (danger)
0.00%
0 / 26
0.00% covered (danger)
0.00%
0 / 1
110
 wcr_region_label
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
2
 get_cc_bcc_executive
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
6
 delete_cc_bcc_executive
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 update_cc_bcc_executive
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
20
 create_cc_bcc_executive
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 update_notification_settings
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
6
 get_notification_settings
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
6
 send_report_logs
0.00% covered (danger)
0.00%
0 / 41
0.00% covered (danger)
0.00%
0 / 1
20
 get_emails_acceptance_notifications
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
6
 create_to_email_acceptance
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 delete_to_email_acceptance
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 update_to_email_acceptance
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
30
 create_cc_email_acceptance
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 delete_cc_email_acceptance
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 update_cc_email_acceptance
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
30
 list_last_follow_up_date
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
20
 update_last_follow_up_date_working_days
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
6
 update_last_follow_up_date
0.00% covered (danger)
0.00%
0 / 36
0.00% covered (danger)
0.00%
0 / 1
30
 delete_last_follow_up_date
0.00% covered (danger)
0.00%
0 / 13
0.00% covered (danger)
0.00%
0 / 1
12
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Exceptions\AppException;
6use App\Jobs\Email\SendFollowUpDigest;
7use App\Models\TblCcAcceptanceNotifications;
8use App\Models\TblCcBccExecutiveNotifications;
9use App\Models\TblCcBccNotifications;
10use App\Models\TblCcBccRequestNotifications;
11use App\Models\TblCcG3WEmailReminders;
12use App\Models\TblCompanies;
13use App\Models\TblCompanyUsers;
14use App\Models\TblLastFollowUpDate;
15use App\Models\TblNotificationLogs;
16use App\Models\TblNotifications;
17use App\Models\TblNotificationSettings;
18use App\Models\TblToAcceptanceNotifications;
19use App\Models\TblUsers;
20use App\Services\ExecutiveReportChartService;
21use App\Services\SendgridDispatcher;
22use App\Services\SendgridLogger;
23use App\Services\UserCompanies;
24use Illuminate\Contracts\Routing\ResponseFactory;
25use Illuminate\Http\Request;
26use Illuminate\Http\Response;
27use Illuminate\Support\Facades\App;
28use Illuminate\Support\Facades\Artisan;
29use Illuminate\Support\Facades\DB;
30use Illuminate\Support\Facades\Log;
31use SendGrid\Mail\Mail;
32
33class Notifications extends Controller
34{
35    private $locale;
36
37    private $userId;
38
39    private $region;
40
41    private $companyIds;
42
43    private $companyId;
44
45    public function __construct()
46    {
47        $this->locale = request()->header('Locale-Id');
48        $this->userId = request()->header('User-Id');
49        $this->region = request()->header('Region');
50
51        App::setLocale($this->locale);
52
53        $this->companyIds = [];
54
55        if ($this->region != null && $this->region != '' && $this->region != 'All') {
56            $this->region = urldecode((string) $this->region);
57
58            $query = 'SELECT
59                        b.company_id
60                    FROM
61                        tbl_company_users a
62                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
63                    WHERE
64                        a.user_id = ?
65                        AND b.region = ?';
66
67            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
68
69            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
70        } else {
71            // FIRE-1146: prefer the middleware-resolved attribute; fall back to a fresh fetch only on unattached paths.
72            $this->companyIds = request()->attributes->get('user_company_ids', UserCompanies::forUser((int) $this->userId));
73        }
74
75        $this->companyId = implode(',', $this->companyIds);
76    }
77
78    public function list_notifications(Request $request): ResponseFactory|Response
79    {
80
81        try {
82            // FIRE-1149: paginated rewrite.
83            // Was: full table scan of all of this user's notifications (51k row table),
84            // returned the entire history, counted unread in PHP by iterating every row.
85            // Now: index seek on idx_notifications_user_created for both the unread
86            // count (filtered) and the paginated page.
87            $userId = (int) $request->input('user_id');
88            $perPage = min(100, max(1, (int) $request->input('per_page', 50)));
89            $page = max(1, (int) $request->input('page', 1));
90            $offset = ($page - 1) * $perPage;
91
92            // Unread count — preserves the topbar badge semantics
93            // (topbar.component.ts:212 reads response.total as the bell-icon count).
94            $totalOpenNotification = (int) DB::table('tbl_notifications')
95                ->where('user_id', $userId)
96                ->where('is_open', '>', 0)
97                ->count();
98
99            // Page query — same projection as before (formatted_created_at relative
100            // time string), just with LIMIT / OFFSET and parameterised user_id.
101            $query = "SELECT
102                        notification_id,
103                        user_id,
104                        REPLACE(content, '<br>', '') content,
105                        link,
106                        is_open,
107                        created_by,
108                        created_at,
109                        updated_by,
110                        updated_at,
111                        CASE
112                            WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) < 60 THEN
113                                CASE
114                                    WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) = 1 THEN '1 second ago'
115                                    ELSE CONCAT(TIMESTAMPDIFF(SECOND, created_at, NOW()), ' seconds ago')
116                                END
117                            WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) < 60 THEN
118                                CASE
119                                    WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) = 1 THEN '1 minute ago'
120                                    ELSE CONCAT(TIMESTAMPDIFF(MINUTE, created_at, NOW()), ' minutes ago')
121                                END
122                            WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) < 24 THEN
123                                CASE
124                                    WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) = 1 THEN '1 hour ago'
125                                    ELSE CONCAT(TIMESTAMPDIFF(HOUR, created_at, NOW()), ' hours ago')
126                                END
127                            WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) < 7 THEN
128                                CASE
129                                    WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) = 1 THEN '1 day ago'
130                                    ELSE CONCAT(TIMESTAMPDIFF(DAY, created_at, NOW()), ' days ago')
131                                END
132                            WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) < 4 THEN
133                                CASE
134                                    WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) = 1 THEN '1 week ago'
135                                    ELSE CONCAT(TIMESTAMPDIFF(WEEK, created_at, NOW()), ' weeks ago')
136                                END
137                            WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) < 12 THEN
138                                CASE
139                                    WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) = 1 THEN '1 month ago'
140                                    ELSE CONCAT(TIMESTAMPDIFF(MONTH, created_at, NOW()), ' months ago')
141                                END
142                            ELSE
143                                CASE
144                                    WHEN TIMESTAMPDIFF(YEAR, created_at, NOW()) = 1 THEN '1 year ago'
145                                    ELSE CONCAT(TIMESTAMPDIFF(YEAR, created_at, NOW()), ' years ago')
146                                END
147                        END AS formatted_created_at
148                    FROM
149                        tbl_notifications
150                    WHERE user_id = ?
151                    ORDER BY
152                        created_at DESC
153                    LIMIT ? OFFSET ?";
154
155            $result = DB::select($query, [$userId, $perPage, $offset]);
156
157            return response([
158                'message' => 'OK',
159                'data' => $result,
160                'total' => $totalOpenNotification,
161                '_pagination' => [
162                    'current_page' => $page,
163                    'per_page' => $perPage,
164                    'has_more' => count($result) === $perPage,
165                ],
166            ]);
167
168        } catch (\Exception $e) {
169            report(AppException::fromException($e, 'LIST_NOTIFICATIONS_EXCEPTION'));
170
171            return response(['message' => 'KO', 'error' => $e->getMessage()]);
172        }
173
174    }
175
176    public function update_notifications(Request $request, $notificationId)
177    {
178
179        try {
180
181            $data = $request->all();
182
183            if ($notificationId == 999999999) {
184                TblNotifications::where('user_id', $data['user_id'])->update([
185                    'is_open' => null,
186                ]);
187            } else {
188                TblNotifications::where('notification_id', $notificationId)->update([
189                    'is_open' => null,
190                    'updated_at' => date('Y-m-d H:i:s'),
191                    'updated_by' => $data['updated_by'],
192                ]);
193            }
194
195            $r = new Request([
196                'user_id' => $data['user_id'],
197            ]);
198
199            return $this->list_notifications($r);
200
201        } catch (\Exception $e) {
202            report(AppException::fromException($e, 'UPDATE_NOTIFICATION_EXCEPTION'));
203
204            return response(['message' => 'KO', 'error' => $e->getMessage()]);
205        }
206    }
207
208    public function send_follow_up_notification($userId = null): ResponseFactory|Response
209    {
210
211        try {
212
213            if ($userId != null) {
214                $this->userId = $userId;
215                $this->locale = 'es';
216                App::setLocale($this->locale);
217            }
218
219            $users = TblUsers::get();
220            $totalOrders = 0;
221            $totalCompanies = 0;
222            foreach ($users as $user) {
223                // FIRE-1113: $body, $allTotalFollowUps, $companyCcIds must reset per user.
224                // Pre-fix they were declared above the loop and only appended to (`.=`),
225                // so user N's email contained the concatenated content + running totals
226                // of users 1..N-1 — a leak of every commercial's pipeline to the next.
227                $body = '';
228                $allTotalFollowUps = 0;
229                $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get();
230
231                $body .= __('language.send_follow_up_notification.body_hello');
232                $body = str_replace('{{username}}', $user->name, $body);
233
234                $body .= __('language.send_follow_up_notification.body_message');
235
236                $hasFollowUp = false;
237                $companyCcIds = [];
238                foreach ($userCompanies as $userCompany) {
239
240                    $companyId = $userCompany->company_id;
241                    $company = TblCompanies::where('company_id', $companyId)->first();
242
243                    // tbl_company_users can still point at a deleted company;
244                    // reading its settings crashed the whole digest
245                    // (PHP-LARAVEL-6V/6R: is_send_* on null).
246                    if (! $company) {
247                        continue;
248                    }
249
250                    if (! SendgridDispatcher::isAllowed(
251                        SendgridDispatcher::BUCKET_FOLLOW_UP,
252                        (int) $companyId,
253                    )) {
254                        // FIRE-1930: skip this company's contribution to
255                        // the digest when its region has the follow-up
256                        // bucket off. Users whose companies span multiple
257                        // regions still get the digest, minus the gated
258                        // regions' rows.
259                        continue;
260                    }
261
262                    if ($company->is_send_follow_up > 0) {
263                        $toEmail = $user->email;
264
265                        $query = "SELECT
266                                    id,
267                                    quote_id,
268                                    amount,
269                                    DATE_FORMAT(last_follow_up_date, '%d/%m/%Y') last_follow_up_date,
270                                    commercial 
271                                FROM 
272                                    tbl_quotations 
273                                WHERE 
274                                    last_follow_up_date < NOW() 
275                                    AND email IS NOT NULL 
276                                    AND email <> ''
277                                    AND NOT EXISTS (
278                                    SELECT 
279                                        1 
280                                    FROM 
281                                        tbl_blocked_domains bd 
282                                    WHERE 
283                                        email LIKE CONCAT('%', bd.domain, '%') 
284                                        AND bd.company_id = company_id
285                                    )
286                                    AND last_follow_up_date IS NOT NULL
287                                    AND reason_for_not_following_up_id IS NULL
288                                    AND last_follow_up_date > 0
289                                    AND last_follow_up_date < NOW()
290                                    AND total_sent < 3
291                                    AND for_add = 0
292                                    AND company_id = {$companyId}
293                                    AND commercial IS NOT NULL 
294                                    AND commercial = '{$user->name}'
295                                    AND budget_status_id = 2
296                                ORDER BY last_follow_up_date ASC, CAST(amount AS DOUBLE) DESC";
297
298                        $result = DB::select($query);
299
300                        $totalFolloUps = count($result);
301
302                        if ($totalFolloUps > 0) {
303                            $allTotalFollowUps = $allTotalFollowUps + $totalFolloUps;
304                            array_push($companyCcIds, $companyId);
305
306                            $hasFollowUp = true;
307                            $body .= __('language.send_follow_up_notification.body_company_message');
308                            $body = str_replace('{{total}}', $totalFolloUps, $body);
309                            $body = str_replace('{{company}}', $company->name, $body);
310
311                            $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id={$companyId}";
312                            $body = str_replace('{{click}}', $clickhRef, $body);
313
314                            $ul = '<ul>';
315
316                            $list = '';
317                            $totalAmount = 0;
318                            for ($i = 0; $i < $totalFolloUps; $i++) {
319                                $li = __('language.send_follow_up_notification.body_due');
320
321                                $id = $result[$i]->id;
322                                $date = $result[$i]->last_follow_up_date;
323                                $quoteId = $result[$i]->quote_id;
324                                $totalAmount = $totalAmount + $result[$i]->amount;
325                                $amount = $this->currency($result[$i]->amount, 1);
326
327                                $url = config('app.frontend_url')."orders/{$id}?company_id={$companyId}";
328                                $href = "<a href='{$url}'>{$quoteId}</a>";
329
330                                $li = str_replace('{{quote_id}}', $href, $li);
331                                $li = str_replace('{{amount}}', $amount, $li);
332                                $list .= str_replace('{{date}}', $date, $li);
333                            }
334                            $ul .= $list;
335                            $ul .= '</ul>';
336
337                            $totalAmount = $this->currency($totalAmount, 1);
338                            $body = str_replace('{{amount}}', $totalAmount, $body);
339
340                            $content = $body.$ul;
341
342                            $body .= $ul;
343
344                        } else {
345                            continue;
346                        }
347                    } else {
348                        continue;
349                    }
350                }
351
352                if ($hasFollowUp) {
353
354                    $body = str_replace('{{totalOrders}}', $allTotalFollowUps, $body);
355                    $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body);
356
357                    $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id=0";
358                    $body = str_replace('{{clickAll}}', $clickhRef, $body);
359
360                    $imgpathS = file_get_contents(public_path('sendfollowups.png'));
361                    $img = "<br><img src='cid:sendfollowups' />";
362                    $body .= $img;
363
364                    $imgpath = file_get_contents(public_path('fireservicetitan.png'));
365                    $body .= '<br><p>Fire Service Titan</p>';
366                    $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
367
368                    $html = '<!DOCTYPE html>';
369                    $html .= '<html>';
370                    $html .= '<head>';
371                    $html .= '<meta charset="UTF-8">';
372                    $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
373                    $html .= '</head>';
374                    $html .= '<body>';
375                    $html .= $body;
376                    $html .= '</body>';
377                    $html .= '</html>';
378
379                    $dateNow = date_create(date('Y-m-d'));
380                    $dateNow = date_format($dateNow, 'd/m/Y');
381
382                    $subject = __('language.send_follow_up_notification.subject');
383                    $subject = str_replace('{{dateNow}}', $dateNow, $subject);
384
385                    if ($toEmail != null) {
386                        $userAdmin = TblUsers::where('id', $this->userId)->first();
387                        $sentBy = $userAdmin?->name ?? 'System';
388
389                        $cc = [];
390                        if (config('services.sendgrid.staging')) {
391                            $toEmail = $userAdmin?->email ?? $toEmail;
392                            // In staging we don't CC anyone — redirect every
393                            // send to the admin running the cron.
394                        } else {
395                            $companyCcIds = array_values(array_unique($companyCcIds));
396                            $ccBcc = TblCcBccNotifications::whereIn('company_id', $companyCcIds)->get();
397                            $inCc = [];
398
399                            foreach ($ccBcc as $data) {
400                                if ($data->email != $toEmail && ! in_array($data->email, $inCc)) {
401                                    $cc[] = $data->email;
402                                    $inCc[] = $data->email;
403                                }
404                            }
405                        }
406
407                        // FIRE-1147: synchronous side effects (TblNotifications
408                        // in-app banner + TblNotificationLogs audit row) land
409                        // immediately; the email send is queued so a slow or
410                        // failing SendGrid doesn't bottleneck the cron's
411                        // per-user loop.
412                        TblNotifications::create(
413                            [
414                                'user_id' => $user->id,
415                                'content' => $content,
416                                'is_open' => 1,
417                                'created_by' => 'System',
418                            ]
419                        );
420
421                        TblNotificationLogs::create(
422                            [
423                                'commercial' => $user->name,
424                                'total_follow_ups' => $allTotalFollowUps,
425                                'created_by' => $sentBy,
426                            ]
427                        );
428
429                        SendFollowUpDigest::dispatch(
430                            toEmail: (string) $toEmail,
431                            subject: $subject,
432                            html: $html,
433                            cc: $cc,
434                            userId: $user->id,
435                        )->onQueue('email');
436
437                        $body = '';
438                        $content = '';
439                    }
440                }
441
442                $hasFollowUp = false;
443                $body = '';
444                $content = '';
445                $allTotalFollowUps = 0;
446            }
447
448            return response([
449                'message' => 'OK',
450            ]);
451
452        } catch (\Exception $e) {
453            report(AppException::fromException($e, 'SEND_FOLLOW_UP_NOTIFICATION_EXCEPTION'));
454
455            return response(['message' => 'KO', 'error' => $e->getMessage()]);
456        }
457    }
458
459    public function send_request($companyId): ResponseFactory|Response
460    {
461
462        try {
463
464            $companyId = addslashes((string) $companyId);
465
466            $user = ['commercial'];
467
468            foreach ($user as $col) {
469                // $col is just 'commercial', but send_request_notification takes a $userId which isn't used as such.
470                // Assuming it was intended to only notify all users when called via CLI command.
471                $this->send_request_notification(); // Fixed method call
472            }
473
474            return response([
475                'message' => 'OK',
476            ]);
477
478        } catch (\Exception $e) {
479            report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION'));
480
481            return response(['message' => 'KO', 'error' => $e->getMessage()]);
482        }
483
484    }
485
486    public function send_g3w_warning($companyId): ResponseFactory|Response
487    {
488
489        try {
490
491            $companyId = addslashes((string) $companyId);
492
493            $userAdmin = TblUsers::where('id', $this->userId)->first();
494
495            if (config('services.sendgrid.staging')) {
496                Artisan::call('send:g3w-email-reminders', [
497                    'sent_by' => $userAdmin->name,
498                    'email' => $userAdmin->email,
499                    'company_id' => $companyId,
500                ]);
501            } else {
502                Artisan::call('send:g3w-email-reminders', [
503                    'sent_by' => $userAdmin->name,
504                    'email' => null,
505                    'company_id' => $companyId,
506                ]);
507            }
508
509            $output = Artisan::output();
510
511            return response([
512                'message' => 'OK',
513            ]);
514
515        } catch (\Exception $e) {
516            report(AppException::fromException($e, 'SEND_G3W_WARNING_EXCEPTION'));
517
518            return response(['message' => 'KO', 'error' => $e->getMessage()]);
519        }
520
521    }
522
523    public function send_request_notification($userId = null): ResponseFactory|Response
524    {
525
526        try {
527
528            if ($userId != null) {
529                $this->userId = $userId;
530                $this->locale = 'es';
531                App::setLocale($this->locale);
532            }
533
534            $users = TblUsers::get();
535
536            foreach ($users as $user) {
537                // FIRE-1113: $body, $allTotalRequest, $companyCcIds must reset per user.
538                // Pre-fix they were declared above the loop and only appended to (`.=`),
539                // so user N's email contained the concatenated content + running totals
540                // of users 1..N-1 — a leak of every commercial's pipeline to the next.
541                $body = '';
542                $allTotalRequest = 0;
543                $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get();
544
545                $body .= __('language.send_request_notification.body_hello');
546                $body = str_replace('{{username}}', $user->name, $body);
547                $body .= __('language.send_request_notification.body_intro');
548
549                $hasRequest = false;
550                $companyCcIds = [];
551                foreach ($userCompanies as $userCompany) {
552
553                    $companyId = $userCompany->company_id;
554                    $company = TblCompanies::where('company_id', $companyId)->first();
555
556                    // tbl_company_users can still point at a deleted company;
557                    // reading its settings crashed the whole digest
558                    // (PHP-LARAVEL-6V/6R: is_send_* on null).
559                    if (! $company) {
560                        continue;
561                    }
562
563                    if (! SendgridDispatcher::isAllowed(
564                        SendgridDispatcher::BUCKET_COMMERCIAL,
565                        (int) $companyId,
566                    )) {
567                        // FIRE-1930: skip this company from the request
568                        // digest when its region has bucket 3 off. Same
569                        // semantics as the follow-up digest above.
570                        continue;
571                    }
572
573                    if ($company->is_send_request > 0) {
574                        $toEmail = $user->email;
575
576                        $query = "SELECT
577                                    id,
578                                    quote_id,
579                                    DATE_FORMAT(created_at, '%d/%m/%Y') created_at,
580                                    commercial,
581                                    created_by
582                                FROM
583                                    tbl_quotations
584                                WHERE
585                                    company_id = {$companyId}
586                                    AND budget_status_id IN (6)
587                                    AND commercial = '{$user->name}'";
588
589                        $result = DB::select($query);
590
591                        $totalRequest = count($result);
592
593                        if ($totalRequest > 0) {
594                            $allTotalRequest = $allTotalRequest + $totalRequest;
595                            $hasRequest = true;
596                            array_push($companyCcIds, $companyId);
597
598                            $body .= __('language.send_request_notification.body_message_commercial');
599                            $body = str_replace('{{total}}', $totalRequest, $body);
600                            $body = str_replace('{{company}}', $company->name, $body);
601
602                            $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Solicitud&company_id={$companyId}";
603                            $body = str_replace('{{click}}', $clickhRef, $body);
604
605                            $ul = '<ul>';
606
607                            $list = '';
608                            $totalAmount = 0;
609                            for ($i = 0; $i < $totalRequest; $i++) {
610                                $li = __('language.send_request_notification.body_due');
611
612                                $id = $result[$i]->id;
613                                $date = $result[$i]->created_at;
614                                $quoteId = $result[$i]->quote_id;
615
616                                $url = config('app.frontend_url')."orders/{$id}?company_id={$companyId}";
617                                $href = "<a href='{$url}'>{$quoteId}</a>";
618
619                                $li = str_replace('{{quote_id}}', $href, $li);
620                                $list .= str_replace('{{date}}', $date, $li);
621                            }
622                            $ul .= $list;
623                            $ul .= '</ul>';
624
625                            $totalAmount = $this->currency($totalAmount, 1);
626                            $body = str_replace('{{amount}}', $totalAmount, $body);
627
628                            $content = $body.$ul;
629
630                            $body .= $ul;
631
632                        } else {
633                            continue;
634                        }
635                    } else {
636                        continue;
637                    }
638                }
639
640                if ($hasRequest) {
641
642                    $body = str_replace('{{totalOrders}}', $allTotalRequest, $body);
643                    $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body);
644
645                    $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Solicitud&company_id=0";
646                    $body = str_replace('{{clickAll}}', $clickhRef, $body);
647
648                    $imgpath = file_get_contents(public_path('fireservicetitan.png'));
649
650                    $body .= '<br><p>Fire Service Titan</p>';
651                    $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
652
653                    $html = '<!DOCTYPE html>';
654                    $html .= '<html>';
655                    $html .= '<head>';
656                    $html .= '<meta charset="UTF-8">';
657                    $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
658                    $html .= '</head>';
659                    $html .= '<body>';
660                    $html .= $body;
661                    $html .= '</body>';
662                    $html .= '</html>';
663
664                    $dateNow = date_create(date('Y-m-d'));
665                    $dateNow = date_format($dateNow, 'd/m/Y');
666
667                    $subject = __('language.send_request_notification.subject');
668                    $subject = str_replace('{{dateNow}}', $dateNow, $subject);
669                    $subject = str_replace('{{total}}', $allTotalRequest, $subject);
670
671                    if ($toEmail != null) {
672                        $email = new Mail;
673                        $sentBy = 'System';
674
675                        $email->setFrom('fire@fire.es', 'Fire Service Titan');
676                        $email->setSubject($subject);
677
678                        $userAdmin = TblUsers::where('id', $this->userId)->first();
679                        $sentBy = $userAdmin->name;
680
681                        if (config('services.sendgrid.staging')) {
682                            $toEmail = $userAdmin->email;
683                            $sentBy = $userAdmin->name;
684                            $user->id = $this->userId;
685                        } else {
686                            $companyCcIds = array_values(array_unique($companyCcIds));
687                            $ccBcc = TblCcBccRequestNotifications::whereIn('company_id', $companyCcIds)->get();
688
689                            $inCc = [];
690
691                            if (count($ccBcc) > 0) {
692                                foreach ($ccBcc as $data) {
693                                    if ($data->email != $toEmail && ! in_array($data->email, $inCc)) {
694                                        $email->addCc($data->email);
695                                        array_push($inCc, $data->email);
696                                    }
697                                }
698                            }
699                        }
700
701                        $email->addTo($toEmail);
702                        $email->addContent('text/html', $html);
703
704                        $email->addAttachment(
705                            $imgpath,
706                            'image/png',
707                            'fireservicetitan.png',
708                            'inline',
709                            'fireservicetitan'
710                        );
711
712                        $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
713
714                        try {
715                            $response = $sendgrid->send($email);
716                            SendgridLogger::log($email, $response);
717                        } catch (\Throwable $sendException) {
718                            SendgridLogger::logException($email, $sendException);
719                            throw $sendException;
720                        }
721
722                        if ($response->statusCode() == 202) {
723                            Log::channel('email_log')->info('ID:'.$toEmail.' - REQUEST EMAIL NOTIFICATION SENT');
724
725                            TblNotifications::create(
726                                [
727                                    'user_id' => $user->id,
728                                    'content' => $content,
729                                    'is_open' => 1,
730                                    'created_by' => 'System',
731                                ]
732                            );
733
734                            TblNotificationLogs::create(
735                                [
736                                    'company_id' => $companyId,
737                                    'commercial' => $user->name,
738                                    'total_request' => $allTotalRequest,
739                                    'created_by' => $sentBy,
740                                ]
741                            );
742
743                        } else {
744                            $error = true;
745                            Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
746                        }
747
748                        $body = '';
749                        $content = '';
750                    }
751                }
752
753                $hasRequest = false;
754                $body = '';
755                $content = '';
756                $allTotalRequest = 0;
757            }
758
759            return response([
760                'message' => 'OK',
761            ]);
762
763        } catch (\Exception $e) {
764            report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION'));
765
766            return response(['message' => 'KO', 'error' => $e->getMessage()]);
767        }
768    }
769
770    public function get_cc_bcc($id): ResponseFactory|Response
771    {
772
773        try {
774
775            $companyId = addslashes((string) $id);
776
777            $result = TblCcBccNotifications::where('company_id', $companyId)->get();
778
779            return response(['message' => 'OK', 'data' => $result]);
780
781        } catch (\Exception $e) {
782            report(AppException::fromException($e, 'GET_CC_BCC_EXCEPTION'));
783
784            return response(['message' => 'KO', 'error' => $e->getMessage()]);
785        }
786    }
787
788    public function delete_cc_bcc($id): ResponseFactory|Response
789    {
790
791        try {
792
793            $id = addslashes((string) $id);
794
795            TblCcBccNotifications::where('id', $id)->delete();
796
797            return response(['message' => 'OK']);
798
799        } catch (\Exception $e) {
800            report(AppException::fromException($e, 'DELETE_CC_BCC_EXCEPTION'));
801
802            return response(['message' => 'KO', 'error' => $e->getMessage()]);
803        }
804    }
805
806    public function update_cc_bcc(Request $request, $id): ResponseFactory|Response
807    {
808
809        try {
810
811            $data = $request->all();
812            $id = addslashes((string) $id);
813
814            $x = $this->isEmailValid($data['email']);
815
816            if ($x) {
817                $result = TblCcBccNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
818
819                if ($result > 0) {
820                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
821                } else {
822                    $data['updated_at'] = date('Y-m-d H:i:s');
823                    TblCcBccNotifications::where('id', $id)->update($data);
824                }
825            } else {
826                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
827            }
828
829            return response(['message' => 'OK']);
830
831        } catch (\Exception $e) {
832            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXCEPTION'));
833
834            return response(['message' => 'KO', 'error' => $e->getMessage()]);
835        }
836    }
837
838    public function create_cc_bcc(Request $request): ResponseFactory|Response
839    {
840
841        try {
842
843            $data = $request->all();
844
845            $x = $this->isEmailValid($data['email']);
846
847            if ($x) {
848                $result = TblCcBccNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
849
850                if ($result > 0) {
851                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
852                } else {
853                    TblCcBccNotifications::create($data);
854                }
855            } else {
856                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
857            }
858
859            return response(['message' => 'OK']);
860
861        } catch (\Exception $e) {
862            report(AppException::fromException($e, 'CREATE_CC_BCC_EXCEPTION'));
863
864            return response(['message' => 'KO', 'error' => $e->getMessage()]);
865        }
866    }
867
868    public function isEmailValid($email): bool
869    {
870        // Regular expression pattern for email validation
871        $pattern = '/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/';
872
873        // Check if the email matches the pattern
874        if (preg_match($pattern, (string) $email)) {
875            return true; // Valid email
876        } else {
877            return false; // Invalid email
878        }
879    }
880
881    public function delete_notifications($userId)
882    {
883
884        try {
885
886            $userId = addslashes((string) $userId);
887
888            TblNotifications::where('user_id', $userId)->delete();
889
890            $r = new Request([
891                'user_id' => $userId,
892            ]);
893
894            return $this->list_notifications($r);
895
896        } catch (\Exception $e) {
897            report(AppException::fromException($e, 'DELETE_NOTIFICATIONS_EXCEPTION'));
898
899            return response(['message' => 'KO', 'error' => $e->getMessage()]);
900        }
901
902    }
903
904    public function list_notification_logs(Request $request, $companyId): ResponseFactory|Response
905    {
906
907        try {
908            // FIRE-1149: paginated rewrite. Was a full scan of all 25k+
909            // rows on every page load. Now uses idx_notiflogs_company_created
910            // for an index-only scan of the requested page.
911            $companyId = (int) $companyId;
912            $perPage = min(100, max(1, (int) $request->input('per_page', 50)));
913            $page = max(1, (int) $request->input('page', 1));
914            $offset = ($page - 1) * $perPage;
915
916            if ($companyId !== 0) {
917                $where = ' (company_id = ? OR company_id IS NULL) ';
918                $bindings = [$companyId, $perPage, $offset];
919            } else {
920                // Multi-company case: $this->companyId is a CSV of ints set
921                // in the constructor from ResolveUserCompanies middleware,
922                // safe to interpolate (only used through ResultCache::remember
923                // and friends elsewhere — same trust model).
924                // Defensive: if the user has no companies assigned, fall back
925                // to "company_id IS NULL only" (the IN () form is illegal SQL).
926                $companyIdsList = trim((string) $this->companyId);
927                $where = $companyIdsList !== ''
928                    ? " (company_id IN ({$companyIdsList}) OR company_id IS NULL) "
929                    : ' company_id IS NULL ';
930                $bindings = [$perPage, $offset];
931            }
932
933            $query = "SELECT
934                        id,
935                        commercial,
936                        total_follow_ups,
937                        total_request,
938                        total_executive_report,
939                        total_g3w_warning,
940                        CASE
941                            WHEN total_follow_ups IS NOT NULL THEN total_follow_ups
942                            WHEN total_request IS NOT NULL THEN total_request
943                            WHEN total_executive_report IS NOT NULL THEN total_executive_report
944                            WHEN total_g3w_warning IS NOT NULL THEN total_g3w_warning
945                        END totals,
946                        created_by,
947                        created_at
948                    FROM `tbl_notification_logs`
949                    WHERE {$where}
950                    ORDER BY created_at DESC
951                    LIMIT ? OFFSET ?";
952
953            $result = DB::select($query, $bindings);
954
955            return response([
956                'message' => 'OK',
957                'data' => $result,
958                '_pagination' => [
959                    'current_page' => $page,
960                    'per_page' => $perPage,
961                    'has_more' => count($result) === $perPage,
962                ],
963            ]);
964
965        } catch (\Exception $e) {
966            report(AppException::fromException($e, 'LIST_NOTIFICATION_LOGS_EXCEPTION'));
967
968            return response(['message' => 'KO', 'error' => $e->getMessage()]);
969        }
970
971    }
972
973    public function delete_notification_logs($companyId): ResponseFactory|Response
974    {
975
976        try {
977
978            $companyId = addslashes((string) $companyId);
979
980            TblNotificationLogs::where('company_id', $companyId)->orWhere('company_id', null)->delete();
981
982            return response(['message' => 'OK']);
983
984        } catch (\Exception $e) {
985            report(AppException::fromException($e, 'DELETE_NOTIFICATION_LOGS_EXCEPTION'));
986
987            return response(['message' => 'KO', 'error' => $e->getMessage()]);
988        }
989    }
990
991    public function get_cc_g3w($id): ResponseFactory|Response
992    {
993
994        try {
995
996            $companyId = addslashes((string) $id);
997
998            $result = TblCcG3WEmailReminders::where('company_id', $companyId)->get();
999
1000            return response(['message' => 'OK', 'data' => $result]);
1001
1002        } catch (\Exception $e) {
1003            report(AppException::fromException($e, 'GET_CC_G3W_EXCEPTION'));
1004
1005            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1006        }
1007    }
1008
1009    public function delete_cc_g3w($id): ResponseFactory|Response
1010    {
1011
1012        try {
1013
1014            $id = addslashes((string) $id);
1015
1016            TblCcG3WEmailReminders::where('id', $id)->delete();
1017
1018            return response(['message' => 'OK']);
1019
1020        } catch (\Exception $e) {
1021            report(AppException::fromException($e, 'DELETE_CC_G3W_EXCEPTION'));
1022
1023            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1024        }
1025    }
1026
1027    public function update_cc_g3w(Request $request, $id): ResponseFactory|Response
1028    {
1029
1030        try {
1031
1032            $data = $request->all();
1033            $id = addslashes((string) $id);
1034
1035            $x = $this->isEmailValid($data['email']);
1036
1037            if ($x) {
1038                $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1039
1040                if ($result > 0) {
1041                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1042                } else {
1043                    $data['updated_at'] = date('Y-m-d H:i:s');
1044                    TblCcG3WEmailReminders::where('id', $id)->update($data);
1045                }
1046            } else {
1047                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1048            }
1049
1050            return response(['message' => 'OK']);
1051
1052        } catch (\Exception $e) {
1053            report(AppException::fromException($e, 'UPDATE_CC_G3W_EXCEPTION'));
1054
1055            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1056        }
1057    }
1058
1059    public function create_cc_g3w(Request $request): ResponseFactory|Response
1060    {
1061
1062        try {
1063
1064            $data = $request->all();
1065
1066            $x = $this->isEmailValid($data['email']);
1067
1068            if ($x) {
1069                $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1070
1071                if ($result > 0) {
1072                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1073                } else {
1074                    TblCcG3WEmailReminders::create($data);
1075                }
1076            } else {
1077                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1078            }
1079
1080            return response(['message' => 'OK']);
1081
1082        } catch (\Exception $e) {
1083            report(AppException::fromException($e, 'CREATE_CC_G3W_EXCEPTION'));
1084
1085            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1086        }
1087    }
1088
1089    public function get_cc_bcc_request($id): ResponseFactory|Response
1090    {
1091
1092        try {
1093
1094            $companyId = addslashes((string) $id);
1095
1096            $result = TblCcBccRequestNotifications::where('company_id', $companyId)->get();
1097
1098            return response(['message' => 'OK', 'data' => $result]);
1099
1100        } catch (\Exception $e) {
1101            report(AppException::fromException($e, 'GET_CC_BCC_REQUEST_EXCEPTION'));
1102
1103            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1104        }
1105    }
1106
1107    public function delete_cc_bcc_request($id): ResponseFactory|Response
1108    {
1109
1110        try {
1111
1112            $id = addslashes((string) $id);
1113
1114            TblCcBccRequestNotifications::where('id', $id)->delete();
1115
1116            return response(['message' => 'OK']);
1117
1118        } catch (\Exception $e) {
1119            report(AppException::fromException($e, 'DELETE_CC_BCC_REQUEST_EXCEPTION'));
1120
1121            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1122        }
1123    }
1124
1125    public function update_cc_bcc_request(Request $request, $id): ResponseFactory|Response
1126    {
1127
1128        try {
1129
1130            $data = $request->all();
1131            $id = addslashes((string) $id);
1132
1133            $x = $this->isEmailValid($data['email']);
1134
1135            if ($x) {
1136                $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1137
1138                if ($result > 0) {
1139                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1140                } else {
1141                    $data['updated_at'] = date('Y-m-d H:i:s');
1142                    TblCcBccRequestNotifications::where('id', $id)->update($data);
1143                }
1144            } else {
1145                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1146            }
1147
1148            return response(['message' => 'OK']);
1149
1150        } catch (\Exception $e) {
1151            report(AppException::fromException($e, 'UPDATE_CC_BCC_REQUEST_EXCEPTION'));
1152
1153            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1154        }
1155    }
1156
1157    public function create_cc_bcc_request(Request $request): ResponseFactory|Response
1158    {
1159
1160        try {
1161
1162            $data = $request->all();
1163
1164            $x = $this->isEmailValid($data['email']);
1165
1166            if ($x) {
1167                $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1168
1169                if ($result > 0) {
1170                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1171                } else {
1172                    TblCcBccRequestNotifications::create($data);
1173                }
1174            } else {
1175                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1176            }
1177
1178            return response(['message' => 'OK']);
1179
1180        } catch (\Exception $e) {
1181            report(AppException::fromException($e, 'CREATE_CC_BCC_REQUEST_EXCEPTION'));
1182
1183            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1184        }
1185    }
1186
1187    public function send_executive(Request $request): ResponseFactory|Response
1188    {
1189
1190        try {
1191
1192            $data = $request->all();
1193
1194            $executiveEmails = TblCcBccExecutiveNotifications::get();
1195
1196            if (isset($data['User-ID'])) {
1197                $this->userId = $data['User-ID'];
1198            }
1199
1200            if (isset($data['self']) && $data['self'] == 1) {
1201                $user = TblUsers::where('id', $this->userId)->first();
1202                $this->send_executive_report($user->email, $user->name, $user->id, $data['processed_by']);
1203            } else {
1204                foreach ($executiveEmails as $item) {
1205                    $user = TblUsers::where('email', $item->email)->first();
1206
1207                    if ($user) {
1208                        $this->send_executive_report($item->email, $user->name, $user->id, $data['processed_by']);
1209
1210                        continue;
1211                    }
1212
1213                    // FIRE-1025: don't silently drop recipients whose email
1214                    // isn't in tbl_users. Berta added Duarte on 2026-03-30
1215                    // expecting him to receive the report; the previous
1216                    // `if ($user) { ... }` guard skipped him (and Albert and
1217                    // Pablo) on every Saturday with no log line. Fall back
1218                    // to a sensible display name + user_id = 1 (System) so
1219                    // the email goes out, and surface the missing-user
1220                    // case in the cron log so admins can clean up
1221                    // tbl_cc_bcc_executive_notifications later.
1222                    Log::channel('cron_send_executive_report')->warning(
1223                        "send:executive-report — recipient {$item->email} has no matching tbl_users row; sending anyway with default name and user_id=1 (System)."
1224                    );
1225                    $defaultName = strstr($item->email, '@', true) ?: $item->email;
1226                    $this->send_executive_report($item->email, $defaultName, 1, $data['processed_by']);
1227                }
1228            }
1229
1230            return response([
1231                'message' => 'OK',
1232            ]);
1233
1234        } catch (\Exception $e) {
1235            report(AppException::fromException($e, 'SEND_EXECUTIVE_EXCEPTION'));
1236
1237            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1238        }
1239
1240    }
1241
1242    public function send_executive_report($toEmail, $toName, $toUserId, $sentBy = 'System')
1243    {
1244
1245        try {
1246
1247            $result = TblNotificationSettings::where('id', 1)->first();
1248
1249            $currentYear = date('Y');
1250            $previousYear = date('Y', strtotime('-1 year', strtotime(date('Y-m-d'))));
1251
1252            if ($toEmail != null && $result->is_auto_send_executive == 1) {
1253
1254                if (config('services.sendgrid.staging')) {
1255                    $user = TblUsers::where('id', $this->userId)->first();
1256                    $toEmail = $user->email;
1257                }
1258
1259                $result = (object) $this->get_executive_reports();
1260
1261                $weekNumber = $result->weekNumber;
1262                $monday = $result->monday;
1263                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1264                $totalAmountApproved = $result->totalAmountApproved;
1265                $totalAmount = $result->totalAmount;
1266                $dividendMaintenance = $result->dividendMaintenance;
1267                $dividendCorrectives = $result->dividendCorrectives;
1268                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1269                $approvalRate = $result->approvalRate;
1270
1271                $subject = "FST executive report week $weekNumber (starting on monday - $monday) - Automated";
1272
1273                $body = "<p>Hello {$toName},</p>";
1274
1275                $url = config('app.frontend_url')."analytics?week={$weekNumber}";
1276                $body .= "<p>Find a summary of the commercial activity across <b>Grupo Fire</b> (see the <a href='{$url}'>report on FST here</a>) for week {$weekNumber} (starting on monday - {$monday})</p>";
1277                $body .= "<p><b>{$totalBudgets} budget(s)</b> ({$percentageMoreThanLastWeek}% compared to last week) created for a total of <b>{$totalAmount}</b> ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) - ";
1278                $body .= "this week across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the month <b>(<span style='color: blue;'>{$totalAmountApproved} approved</span>)</b></i></p>";
1279
1280                $query = "SELECT 
1281                            a.company_id, 
1282                            a.name,
1283                            (
1284                            SELECT 
1285                                COUNT(created_at) 
1286                            FROM 
1287                                tbl_quotations 
1288                            WHERE 
1289                                company_id = a.company_id
1290                                AND for_add = 0 
1291                                AND created_at IS NOT NULL 
1292                                AND issue_date IS NOT NULL
1293                                AND YEAR(created_at) = YEAR(issue_date)
1294                            ) total 
1295                        FROM 
1296                            tbl_companies a 
1297                            WHERE is_send_executive = 1
1298                            -- AND company_id IN ({$this->companyId})
1299                        ORDER BY 
1300                            3 DESC
1301                        ";
1302
1303                $companies = DB::select($query);
1304
1305                $li = [];
1306
1307                foreach ($companies as $company) {
1308                    $result = (object) $this->get_executive_reports($company->company_id);
1309                    $result->name = $company->name;
1310                    array_push($li, $result);
1311                }
1312
1313                usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets);
1314
1315                $body .= '<ul>';
1316                foreach ($li as $result) {
1317                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1318                    $totalAmount = $result->totalAmount;
1319                    $dividendMaintenance = $result->dividendMaintenance;
1320                    $dividendCorrectives = $result->dividendCorrectives;
1321                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1322                    $approvalRate = $result->approvalRate;
1323
1324                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1325                }
1326                $body .= '</ul>';
1327
1328                $result = (object) $this->get_executive_reports(null, true, $currentYear);
1329
1330                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1331                $totalAmountApproved = $result->totalAmountApproved;
1332                $totalAmount = $result->totalAmount;
1333                $dividendMaintenance = $result->dividendMaintenance;
1334                $dividendCorrectives = $result->dividendCorrectives;
1335                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1336                $approvalRate = $result->approvalRate;
1337                $totalDaysFromStartOfYear = date('z') + 1;
1338                $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1339                $expected = 0;
1340
1341                $totalGoalForTheYear = 0;
1342                if ($result->totalGoalForTheYear > 0) {
1343                    $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1344                    $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1345                }
1346
1347                $body .= "<p><b>For {$currentYear}</b> as a whole, <b><span style='color: blue;'>{$totalBudgets}</span> budgets (<span style='color: blue;'>{$totalGoalForTheYear}% of the goal for the year accomplished</span> vs {$expected}% expected) for a total of <span style='color: blue;'>{$totalAmount}</span></b> ({$dividendMaintenance}% “Mantenimientos”, {$dividendCorrectives}% “Correctivos”) ";
1348                $body .= "were created across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the year <b>(<span style='color: blue;'>{$totalAmountApproved} approved</span>)</b></i></p>";
1349
1350                $query = "SELECT 
1351                            a.company_id, 
1352                            a.name,
1353                            (
1354                            SELECT 
1355                                COUNT(created_at) 
1356                            FROM 
1357                                tbl_quotations 
1358                            WHERE 
1359                                company_id = a.company_id
1360                                AND for_add != 1
1361                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1362                                AND created_at IS NOT NULL 
1363                                AND YEAR(created_at) = {$currentYear}                                
1364                            ) total 
1365                        FROM 
1366                            tbl_companies a 
1367                            WHERE is_send_executive = 1
1368                            -- AND company_id IN ({$this->companyId})
1369                        ORDER BY 
1370                            3 DESC
1371                        ";
1372
1373                $companies = DB::select($query);
1374
1375                $li = [];
1376
1377                foreach ($companies as $company) {
1378                    $result = (object) $this->get_executive_reports($company->company_id, true, $currentYear);
1379                    $result->name = $company->name;
1380                    array_push($li, $result);
1381                }
1382
1383                usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets);
1384
1385                $body .= '<ul>';
1386                foreach ($li as $result) {
1387
1388                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1389                    $totalAmountApproved = $result->totalAmountApproved;
1390                    $totalAmount = $result->totalAmount;
1391                    $dividendMaintenance = $result->dividendMaintenance;
1392                    $dividendCorrectives = $result->dividendCorrectives;
1393                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1394                    $approvalRate = $result->approvalRate;
1395                    $goalForTheYear = number_format($result->totalGoalForTheYear, 0, ',', '.');
1396                    $totalDaysFromStartOfYear = date('z') + 1;
1397                    $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1398                    $expected = 0;
1399
1400                    $totalGoalForTheYear = 0;
1401                    $style = "style='color: green'";
1402                    if ($result->totalGoalForTheYear > 0) {
1403                        $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1404                        $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1405
1406                        if ($totalGoalForTheYear < $expected) {
1407                            $style = "style='color: red'";
1408                        }
1409                    }
1410
1411                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1412                    $body .= '<ul>';
1413                    $body .= "<li><i><b>{$goalForTheYear} goal</b> for the year. <b {$style}>{$totalGoalForTheYear}%</b> accomplished to date vs <b>{$expected}% expected</b></i></li>";
1414                    $body .= "<li><i>Approval rate of <b>{$approvalRate}%</b> (<span style='color: blue;'>{$totalAmountApproved} approved</span>)</i></li>";
1415                    $body .= '</ul>';
1416                }
1417
1418                $body .= '</ul>';
1419
1420                $result = (object) $this->get_executive_reports(null, true, $previousYear);
1421
1422                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1423                $totalAmount = $result->totalAmount;
1424                $dividendMaintenance = $result->dividendMaintenance;
1425                $dividendCorrectives = $result->dividendCorrectives;
1426                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1427                $approvalRate = $result->approvalRate;
1428
1429                $body .= "<p>For your reference, in <b>{$previousYear}</b>, <b>{$totalBudgets} budgets for a total of {$totalAmount}</b> ({$dividendMaintenance}% “Mantenimientos”, {$dividendCorrectives}% “Correctivos”) ";
1430                $body .= "were created across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the year</i></p>";
1431
1432                $query = "SELECT 
1433                            a.company_id, 
1434                            a.name,
1435                            (
1436                            SELECT 
1437                                COUNT(created_at) 
1438                            FROM 
1439                                tbl_quotations 
1440                            WHERE 
1441                                company_id = a.company_id
1442                                AND for_add != 1
1443                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1444                                AND created_at IS NOT NULL 
1445                                AND YEAR(created_at) = {$currentYear}
1446                            ) total 
1447                        FROM 
1448                            tbl_companies a 
1449                            WHERE is_send_executive = 1
1450                            -- AND company_id IN ({$this->companyId})
1451                        ORDER BY 
1452                            3 DESC
1453                        ";
1454
1455                $companies = DB::select($query);
1456
1457                $li = [];
1458
1459                foreach ($companies as $company) {
1460                    $result = (object) $this->get_executive_reports($company->company_id, true, $previousYear);
1461                    $result->name = $company->name;
1462                    array_push($li, $result);
1463                }
1464
1465                usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets);
1466
1467                $body .= '<ul>';
1468
1469                foreach ($li as $result) {
1470                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1471                    $totalAmount = $result->totalAmount;
1472                    $dividendMaintenance = $result->dividendMaintenance;
1473                    $dividendCorrectives = $result->dividendCorrectives;
1474                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1475                    $approvalRate = $result->approvalRate;
1476
1477                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b> <i> - approval rate of <b>{$approvalRate}%</b></i></li>";
1478                }
1479
1480                $body .= '</ul><br><br>';
1481
1482                $imgpath = file_get_contents(public_path('fireservicetitan.png'));
1483
1484                $content = $body;
1485
1486                $body .= '<br><p>Fire Service Titan</p>';
1487                $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1488
1489                $html = '<!DOCTYPE html>';
1490                $html .= '<html>';
1491                $html .= '<head>';
1492                $html .= '<meta charset="UTF-8">';
1493                $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1494                $html .= '</head>';
1495                $html .= '<body>';
1496                $html .= $body;
1497                $html .= '</body>';
1498                $html .= '</html>';
1499
1500                $email = new Mail;
1501
1502                $email->setFrom('fire@fire.es', 'Fire Service Titan');
1503                $email->setSubject($subject);
1504
1505                $email->addTo($toEmail);
1506                $email->addContent('text/html', $html);
1507
1508                $email->addAttachment(
1509                    $imgpath,
1510                    'image/png',
1511                    'fireservicetitan.png',
1512                    'inline',
1513                    'fireservicetitan'
1514                );
1515
1516                $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1517
1518                try {
1519                    $response = $sendgrid->send($email);
1520                    SendgridLogger::log($email, $response);
1521                } catch (\Throwable $sendException) {
1522                    SendgridLogger::logException($email, $sendException);
1523                    throw $sendException;
1524                }
1525
1526                if ($response->statusCode() == 202) {
1527                    Log::channel('email_log')->info('ID:'.$toEmail.' - EXECUTIVE REPORT EMAIL NOTIFICATION SENT');
1528
1529                    TblNotifications::create(
1530                        [
1531                            'user_id' => $toUserId,
1532                            'content' => $content,
1533                            'is_open' => 1,
1534                            'created_by' => 'System',
1535                        ]
1536                    );
1537
1538                    TblNotificationLogs::create(
1539                        [
1540                            'commercial' => $toName,
1541                            'total_executive_report' => 1,
1542                            'created_by' => $sentBy,
1543                        ]
1544                    );
1545                } else {
1546                    $error = true;
1547                    Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
1548                }
1549
1550                $body = '';
1551
1552                return response([
1553                    'message' => 'OK',
1554                ]);
1555            }
1556
1557        } catch (\Exception $e) {
1558            report(AppException::fromException($e, 'SEND_EXECUTIVE_REPORT_EXCEPTION'));
1559
1560            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1561        }
1562
1563    }
1564
1565    public function get_executive_reports($companyId = null, $wholeYear = false, $year = null): array
1566    {
1567
1568        $where = '';
1569        $whereYear = '';
1570        $whereMonth = "DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1571
1572        if ($companyId != null) {
1573            $where = " AND company_id = {$companyId} ";
1574        }
1575
1576        $now = date('Y-m-d');
1577        $weekNumber = date('W', strtotime($now));
1578        $monday = date('F j, Y', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1579
1580        $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1581        $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days'));
1582
1583        $mondayLastWeekDate = date('Y-m-d', strtotime($mondayDate.' - 1 week'));
1584        $sundayLastWeekDate = date('Y-m-d', strtotime($mondayLastWeekDate.' + 6 days'));
1585
1586        $whereAccMonth = '';
1587        if ($wholeYear == false) {
1588            $whereYear = " AND DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}' ";
1589            $whereMonth = " AND DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1590            $whereAccMonth = " AND DATE_FORMAT(q.acceptance_date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1591        } else {
1592            $whereYear = " AND YEAR(q.created_at) = {$year} ";
1593            $whereMonth = " AND YEAR(q.created_at) = {$year} ";
1594            $whereAccMonth = " AND YEAR(q.acceptance_date) = {$year} ";
1595        }
1596
1597        $query = "SELECT 
1598                    COUNT(q.created_at) totalBudgets, 
1599                    SUM(q.amount) totalAmount, 
1600                    COALESCE(
1601                        SUM(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END)
1602                        , 0
1603                    ) / 
1604                    COUNT(q.created_at) * 100 dividendMaintenance, 
1605                    COALESCE(
1606                        SUM(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END)
1607                        , 0
1608                    ) / 
1609                    COUNT(q.created_at) * 100 dividendCorrectives 
1610                FROM 
1611                    tbl_quotations q 
1612                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id 
1613                WHERE 
1614                    q.for_add != 1
1615                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1616                    AND q.created_at IS NOT NULL
1617                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1618                    -- FIRE-1202: budget_type_id = 7 corresponds to a deleted/never-created
1619                    -- tbl_budget_types row (verified empty on 2026-06-25). Filter is defensive;
1620                    -- repeated in every executive-report aggregate (sibling filters at lines
1621                    -- 1601, 1620, 1638, 1983, 2060, 2088). Not currently load-bearing — kept
1622                    -- so a future re-insert of budget_type_id=7 doesn't silently appear in the
1623                    -- executive totals before someone re-decides the policy.
1624                    AND q.budget_type_id != 7
1625                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1626                    {$whereYear}
1627                    ";
1628
1629        $result = DB::select($query)[0];
1630
1631        $totalBudgets = $result->totalBudgets;
1632        $totalAmount = $this->currency($result->totalAmount, 1, 0);
1633        $totalAmountC = $result->totalAmount;
1634        $dividendMaintenance = number_format($result->dividendMaintenance, 0);
1635        $dividendCorrectives = number_format($result->dividendCorrectives, 0);
1636
1637        $query = "SELECT 
1638                        COUNT(q.created_at) totalBudgetLastWeek 
1639                    FROM tbl_quotations q WHERE 
1640                    DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayLastWeekDate}' 
1641                    AND '{$sundayLastWeekDate}' 
1642                    AND q.for_add != 1
1643                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1644                    AND q.created_at IS NOT NULL 
1645                    AND q.budget_type_id != 7
1646                    AND q.budget_type_id IS NOT NULL 
1647                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1648                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})";
1649
1650        $result = DB::select($query)[0];
1651
1652        $percentageMoreThanLastWeek = 0;
1653
1654        if ($result->totalBudgetLastWeek > 0) {
1655            $percentageMoreThanLastWeek = number_format($totalBudgets / $result->totalBudgetLastWeek * 100, 2);
1656        }
1657
1658        $query = "SELECT 
1659                    SUM(q.amount) totalAmount
1660                FROM tbl_quotations q WHERE 
1661                q.for_add != 1
1662                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1663                AND q.created_at IS NOT NULL 
1664                AND q.budget_type_id != 7
1665                AND q.budget_type_id IS NOT NULL 
1666                AND YEAR(q.created_at) = YEAR(q.issue_date)
1667                AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1668                {$whereMonth}";
1669
1670        $result = DB::select($query)[0];
1671
1672        $totalAmountCC = $result->totalAmount;
1673
1674        $query = "SELECT 
1675                COUNT(CASE WHEN q.created_at IS NOT NULL THEN 1 END) totalBudgets,
1676                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN 1 END) totalAcceptance,
1677                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN q.amount END) totalAmountApproved
1678            FROM tbl_quotations q WHERE 
1679            q.for_add != 1
1680            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1681            AND q.created_at IS NOT NULL 
1682            AND q.budget_type_id != 7
1683            AND q.budget_type_id IS NOT NULL 
1684            AND YEAR(q.created_at) = YEAR(q.issue_date)
1685            AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1686            {$whereAccMonth}";
1687
1688        $result = DB::select($query)[0];
1689
1690        $approvalRate = 0;
1691        $totalAmountApproved = $result->totalAmountApproved;
1692        if ($totalAmountCC > 0) {
1693            $approvalRate = number_format(($totalAmountApproved / $totalAmountCC) * 100, 0);
1694        }
1695
1696        $query = "SELECT SUM(goal_for_the_year) goalForTheYear FROM tbl_companies WHERE is_send_executive = 1 {$where}";
1697        $result = DB::select($query)[0];
1698
1699        return [
1700            'weekNumber' => $weekNumber,
1701            'monday' => $monday,
1702            'totalBudgets' => $totalBudgets,
1703            'totalAmount' => $totalAmount,
1704            'totalAmountApproved' => $this->currency($totalAmountApproved, 1, 0),
1705            'dividendMaintenance' => $dividendMaintenance,
1706            'dividendCorrectives' => $dividendCorrectives,
1707            'percentageMoreThanLastWeek' => $percentageMoreThanLastWeek,
1708            'approvalRate' => $approvalRate,
1709            'totalGoalForTheYear' => $result->goalForTheYear,
1710        ];
1711
1712    }
1713
1714    public function currency($amount, $withEuro = null, $n = 2)
1715    {
1716
1717        if ($withEuro != null) {
1718            $withEuro = ' €';
1719        }
1720
1721        return number_format($amount, $n, ',', '.').$withEuro;
1722    }
1723
1724    // ---------------------------------------------------------------------
1725    // New weekly commercial performance report (redesigned executive email).
1726    //
1727    // Replaces the prose summary built by send_executive / send_executive_report
1728    // with two tabular parts:
1729    //   PART 1 — Regional performance (per region + Total, with a prior-year
1730    //            comparison): weekly #/€ and Preventive/Corrective/Installation
1731    //            splits, plus YTD progress against the per-region annual € target.
1732    //   PART 2 — Individual performance: top-3 by value and by volume, plus the
1733    //            below-weekly-target lists (by € value and by budget volume).
1734    //
1735    // The old send_executive* methods are intentionally left untouched so the
1736    // previous report stays callable. Service-type € is split by
1737    // tbl_budget_types.budget_type_group_id (3 = Preventive/Mantenimiento,
1738    // 5 = Corrective/Correctivos, 4 = Installation/Instalaciones), matching the
1739    // group ids used across the analytics controllers.
1740    // ---------------------------------------------------------------------
1741
1742    // Weekly per-person targets (spec: fixed thresholds).
1743    const WCR_TARGET_VALUE = 50000;   // € — "Sales reps" by value
1744
1745    // FIRE-1202 follow-up (Berta, 2026-06-26): bumped from 50 to 100 — the spec
1746    // originally said 50 by mistake; specialists are now expected to issue at
1747    // least 100 budgets/week.
1748    const WCR_TARGET_VOLUME = 100;    // # budgets — "Sales Specialists" by volume
1749
1750    // tbl_roles.name that splits the two below-target tables: the volume table
1751    // lists only this role, the value table lists every other commercial role.
1752    const WCR_SPECIALIST_ROLE = 'Sales specialist';
1753
1754    // FIRE-1448 Sub-task 4: Luis pushed back on the per-person section
1755    // including non-commercial staff (Oasys/Sat Valles partner-company users,
1756    // ex-employees, Regular-role internal users). The roster is now
1757    // restricted to the two "real commercial" roles Duarte confirmed, plus
1758    // any user flagged tbl_users.is_commercial_adyacente=1 (Iker et al. —
1759    // expected to submit budgets despite a non-commercial primary role).
1760    const WCR_ALLOWED_ROLES = ['Sales specialist', 'Asesor de clientes'];
1761
1762    /**
1763     * How many no-activity weeks in a row count as time away.
1764     *
1765     * Revised to 1 (Duarte, 2026-08-01): a single week with no budgets and no
1766     * trace of the person in Titan is treated as a week away, rather than
1767     * requiring a run of three before it counts.
1768     *
1769     * An away week is simply not an eligible week — it is neither a hit nor a
1770     * miss. Since the figure the report shows is now a tally of weeks met across
1771     * the year (see wcr_walk_streak), away weeks drop out of it either way; this
1772     * threshold governs how the away weeks are counted and reported, not whether
1773     * a run survives.
1774     */
1775    const WCR_STREAK_MAX_AWAY_WEEKS = 1;
1776
1777    /**
1778     * Statuses whose quotes only count as issued once actually sent (Luis
1779     * Reyes, 2026-10-19): Nuevo (1), Listo para enviar (11), Fallo envío -
1780     * correo erróneo (21), Correo erróneo (22). Without a sent_date they were
1781     * counted on their issue_date fallback — ~2,900 quotes / 23.5 M€ in 2026
1782     * that never reached the client.
1783     */
1784    const WCR_NOT_ISSUED_UNTIL_SENT_STATUSES = [1, 11, 21, 22];
1785
1786    /**
1787     * The "issued" date of a quote for every section of the weekly report:
1788     * its last send, falling back to issue_date — except a never-sent quote
1789     * in one of the statuses above, which gets NULL and so fails every week /
1790     * month / YTD / series condition. Accepted figures don't use this.
1791     */
1792    private static function wcrIssuedDateSql(): string
1793    {
1794        $statuses = implode(', ', self::WCR_NOT_ISSUED_UNTIL_SENT_STATUSES);
1795
1796        return "(CASE WHEN q.sent_date IS NULL AND q.budget_status_id IN ({$statuses}) THEN NULL ELSE COALESCE(q.sent_date, q.issue_date) END)";
1797    }
1798
1799    /**
1800     * The four year-by-week charts, in render order. Titles render in the HTML
1801     * above each image (not inside the PNG), numbered 5.1 for emitido and 5.2 for
1802     * aceptado per Duarte's reference.
1803     */
1804    const WCR_CHART_SERIES = [
1805        'emitido_num' => ['title' => '5.1 · EMITIDO por semana — nº de presupuestos', 'kind' => 'num'],
1806        'emitido_eur' => ['title' => '5.1 · EMITIDO por semana — € presupuestados', 'kind' => 'eur'],
1807        'aceptado_num' => ['title' => '5.2 · ACEPTADO por semana — nº de presupuestos', 'kind' => 'num'],
1808        'aceptado_eur' => ['title' => '5.2 · ACEPTADO por semana — € aceptados', 'kind' => 'eur'],
1809    ];
1810
1811    /**
1812     * Per-run cache for the two expensive year-wide aggregations (the weekly
1813     * series and the per-person streaks).
1814     *
1815     * `send_weekly_commercial_report` loops the 9 executive recipients on a
1816     * single controller instance and rebuilds the whole report each time. The
1817     * content is identical for every recipient, so without this the streak
1818     * activity scan alone (~4s over tbl_quotations_log, which has no index on
1819     * created_at) would run nine times and add well over half a minute to the
1820     * Saturday cron.
1821     */
1822    private array $wcrYearCache = [];
1823
1824    /**
1825     * "Ref. 25 €" — full-year 2025 acceptance ratio (%) per region for Tabla 3,
1826     * computed on AMOUNT (€ accepted / € issued), not on budget count — which is
1827     * why the column and its footnote are labelled €.
1828     * Provided manually by Duarte (not yet exposed by the report/CRM). Keyed by
1829     * the canonical tbl_companies.region string. Value 'nuevo' = region opened
1830     * in 2026, so it has no 2025 reference. Region name mapping from Duarte's
1831     * list: Valencia → Comunidad Valenciana, Alicante → Castellón,
1832     * "Andalucía (Almería)" → Andalucía.
1833     */
1834    const WCR_ACCEPTANCE_REF_2025 = [
1835        'Comunidad Valenciana' => 33,
1836        'Cataluña' => 11,
1837        'Madrid' => 21,
1838        'Castilla y León' => 33,
1839        'Castilla-La Mancha' => 36,
1840        'Aragón' => 24,
1841        'Castellón' => 'nuevo',
1842        'Andalucía' => 'nuevo',
1843        'Baleares' => 'nuevo',
1844    ];
1845
1846    public function send_weekly_commercial_report(Request $request): ResponseFactory|Response
1847    {
1848        try {
1849            $data = $request->all();
1850
1851            $executiveEmails = TblCcBccExecutiveNotifications::get();
1852
1853            if (isset($data['User-ID'])) {
1854                $this->userId = $data['User-ID'];
1855            }
1856
1857            // For a "send to self" request the recipient is the authenticated
1858            // user, so take the id from the request header rather than a body
1859            // param. `backend-user-id` is set from the validated token by the
1860            // auth.token middleware (so it can't be spoofed); fall back to a
1861            // literal `User-ID` header, then the body param.
1862            if (isset($data['self']) && $data['self'] == 1) {
1863                $headerUserId = $request->header('backend-user-id') ?? $request->header('User-ID');
1864                if ($headerUserId !== null && $headerUserId !== '') {
1865                    $this->userId = $headerUserId;
1866                }
1867            }
1868
1869            $sentBy = $data['processed_by'] ?? 'System';
1870
1871            // Optional week/year filter: generate the report "as of" a specific
1872            // ISO week instead of the current one. Omit for the normal
1873            // current-week run (cron). e.g. ?week=29&year=2026
1874            $week = isset($data['week']) && $data['week'] !== '' ? (int) $data['week'] : null;
1875            $year = isset($data['year']) && $data['year'] !== '' ? (int) $data['year'] : null;
1876
1877            // Preview mode: render and return the report HTML directly (no email
1878            // dispatched). Use ?preview=1 — handy for inspecting the layout in
1879            // Postman or a browser.
1880            if (isset($data['preview']) && $data['preview'] == 1) {
1881                $toName = 'Preview';
1882                if ($this->userId) {
1883                    $previewUser = TblUsers::where('id', $this->userId)->first();
1884                    $toName = $previewUser->name ?? 'Preview';
1885                }
1886
1887                return $this->send_weekly_commercial_report_email(null, $toName, $this->userId ?: 1, $sentBy, true, $week, $year);
1888            }
1889
1890            if (isset($data['self']) && $data['self'] == 1) {
1891                $user = TblUsers::where('id', $this->userId)->first();
1892                $this->send_weekly_commercial_report_email($user->email, $user->name, $user->id, $sentBy, false, $week, $year);
1893            } else {
1894                foreach ($executiveEmails as $item) {
1895                    $user = TblUsers::where('email', $item->email)->first();
1896
1897                    if ($user) {
1898                        $this->send_weekly_commercial_report_email($item->email, $user->name, $user->id, $sentBy, false, $week, $year);
1899
1900                        continue;
1901                    }
1902
1903                    // Mirror FIRE-1025: don't silently drop recipients whose
1904                    // email isn't in tbl_users — send anyway with a default
1905                    // name and user_id = 1 (System), and log the gap.
1906                    Log::channel('cron_send_executive_report')->warning(
1907                        "send:executive-report (weekly commercial) — recipient {$item->email} has no matching tbl_users row; sending anyway with default name and user_id=1 (System)."
1908                    );
1909                    $defaultName = strstr($item->email, '@', true) ?: $item->email;
1910                    $this->send_weekly_commercial_report_email($item->email, $defaultName, 1, $sentBy, false, $week, $year);
1911                }
1912            }
1913
1914            return response(['message' => 'OK']);
1915
1916        } catch (\Exception $e) {
1917            report(AppException::fromException($e, 'SEND_WEEKLY_COMMERCIAL_REPORT_EXCEPTION'));
1918
1919            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1920        }
1921    }
1922
1923    public function send_weekly_commercial_report_email($toEmail, $toName, $toUserId, $sentBy = 'System', $preview = false, $week = null, $year = null)
1924    {
1925        try {
1926            $settings = TblNotificationSettings::where('id', 1)->first();
1927
1928            // Preview mode (used for testing) skips the auto-send gate and the
1929            // staging redirect, and returns the rendered HTML instead of
1930            // dispatching the email.
1931            if (! $preview && ($toEmail == null || $settings->is_auto_send_executive != 1)) {
1932                return response(['message' => 'OK']);
1933            }
1934
1935            // Staging safety: never email real recipients — redirect to the
1936            // logged-in user (same guard as send_executive_report).
1937            if (! $preview && config('services.sendgrid.staging')) {
1938                $user = TblUsers::where('id', $this->userId)->first();
1939                $toEmail = $user->email;
1940            }
1941
1942            $regional = $this->get_weekly_regional_performance($week, $year);
1943            $individual = $this->get_weekly_individual_performance($regional['mondayDate'], $regional['sundayDate']);
1944
1945            $weekNumber = $regional['weekNumber'];
1946            $subject = "FST weekly commercial report — week {$weekNumber} (week of {$regional['mondayDate']}) - Automated";
1947
1948            // Section 5 — the year week by week. The reporting week is "open"
1949            // whenever its Sunday has not passed yet, which is the normal
1950            // Saturday-dispatch case; a ?week= override of a past week is closed.
1951            $lastWeekOpen = $regional['sundayDate'] >= date('Y-m-d');
1952            $series = $this->get_weekly_year_series(
1953                (int) $regional['currentYear'],
1954                (int) $weekNumber,
1955                (string) $regional['ytdCutoff'],
1956                $lastWeekOpen
1957            );
1958
1959            $chartService = new ExecutiveReportChartService;
1960            $charts = [];
1961            $chartCids = [];
1962            $imgSrc = [];
1963            foreach ($series as $key => $s) {
1964                $png = $chartService->renderSeries($s['points'], (float) $s['average'], $s['kind']);
1965                if ($png === '') {
1966                    // GD missing or nothing to draw — the section falls back to
1967                    // its HTML figure strip rather than showing a broken image.
1968                    continue;
1969                }
1970
1971                $cid = 'wcrchart'.str_replace('_', '', $key);
1972                $charts[$key] = $png;
1973                $chartCids[$key] = $cid;
1974
1975                // Preview renders straight into a browser, where cid: resolves to
1976                // nothing, so inline the bytes there. Email keeps cid: on purpose:
1977                // four base64 data URIs would add ~200KB to the body and push it
1978                // past Gmail's ~102KB clipping threshold, which would truncate the
1979                // report mid-page.
1980                $imgSrc[$key] = $preview
1981                    ? 'data:image/png;base64,'.base64_encode($png)
1982                    : 'cid:'.$cid;
1983            }
1984
1985            // The builder returns the full standalone HTML document (mockup
1986            // design with its own header/styling), so it is sent as-is — no extra
1987            // branded shell or greeting.
1988            $html = $this->build_weekly_commercial_report_html($regional, $individual, $series, $imgSrc);
1989            $content = $html;
1990
1991            if ($preview) {
1992                return response($html)->header('Content-Type', 'text/html; charset=UTF-8');
1993            }
1994
1995            $email = new Mail;
1996            $email->setFrom('fire@fire.es', 'Fire Service Titan');
1997            $email->setSubject($subject);
1998            $email->addTo($toEmail);
1999            $email->addContent('text/html', $html);
2000
2001            // Inline chart images, referenced as cid: by the section-5 markup.
2002            // SendGrid base64-encodes attachment content itself, so raw PNG bytes
2003            // are what it expects here (same as the logo attachments elsewhere).
2004            foreach ($charts as $key => $png) {
2005                $email->addAttachment(
2006                    $png,
2007                    'image/png',
2008                    $chartCids[$key].'.png',
2009                    'inline',
2010                    $chartCids[$key]
2011                );
2012            }
2013
2014            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
2015
2016            try {
2017                $response = $sendgrid->send($email);
2018                SendgridLogger::log($email, $response);
2019            } catch (\Throwable $sendException) {
2020                SendgridLogger::logException($email, $sendException);
2021                throw $sendException;
2022            }
2023
2024            if ($response->statusCode() == 202) {
2025                Log::channel('email_log')->info('ID:'.$toEmail.' - WEEKLY COMMERCIAL REPORT EMAIL NOTIFICATION SENT');
2026
2027                TblNotifications::create([
2028                    'user_id' => $toUserId,
2029                    'content' => $content,
2030                    'is_open' => 1,
2031                    'created_by' => 'System',
2032                ]);
2033
2034                TblNotificationLogs::create([
2035                    'commercial' => $toName,
2036                    'total_executive_report' => 1,
2037                    'created_by' => $sentBy,
2038                ]);
2039            } else {
2040                Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
2041            }
2042
2043            return response(['message' => 'OK']);
2044
2045        } catch (\Exception $e) {
2046            report(AppException::fromException($e, 'SEND_WEEKLY_COMMERCIAL_REPORT_EMAIL_EXCEPTION'));
2047
2048            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2049        }
2050    }
2051
2052    /**
2053     * PART 1 data: per-region weekly + YTD figures for the current year, the
2054     * prior-year YTD comparison, the per-region annual € target, and Totals.
2055     */
2056    public function get_weekly_regional_performance(?int $weekOverride = null, ?int $yearOverride = null): array
2057    {
2058        // Default anchor is today. When a valid ISO week (1..53) is supplied the
2059        // report is generated "as of" the Sunday of that week instead — so the
2060        // weekly columns, the month label, and the YTD cutoff all reflect that
2061        // chosen week rather than the current date.
2062        if ($weekOverride !== null && $weekOverride >= 1 && $weekOverride <= 53) {
2063            $anchorYear = $yearOverride ?? (int) date('Y');
2064            $isoMonday = date('Y-m-d', strtotime($anchorYear.'-W'.sprintf('%02d', $weekOverride).'-1'));
2065            $now = date('Y-m-d', strtotime($isoMonday.' + 6 days')); // Sunday of the target week
2066        } else {
2067            $now = date('Y-m-d');
2068        }
2069        $weekNumber = (int) date('W', strtotime($now));
2070        $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
2071        $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days'));
2072        $currentYear = (int) date('Y', strtotime($now));
2073        $previousYear = $currentYear - 1;
2074        // FIRE-1318 Bug 1: derive $currentMonth from $mondayDate (same source as
2075        // the header's $monthLabel below), NOT from date('Y-m'). Previously a
2076        // report sent on a Sunday of a week that spanned month-end (e.g. week 27
2077        // = Mon 29/06 → Sun 05/07) labelled the column "Junio" (from the week's
2078        // Monday) but the SQL summed July's tiny start-of-month rows because
2079        // date('Y-m') resolved to '2026-07'. Label and data now agree.
2080        $currentMonth = date('Y-m', strtotime($mondayDate));
2081        // Same calendar month one year earlier (e.g. 2025-06 vs 2026-06) so the
2082        // prior-year reference row's "Month" column compares like-for-like. The
2083        // previous-year query is filtered to YEAR = $previousYear, so passing
2084        // $currentMonth ('2026-06') here would never match and always yield 0.
2085        $previousMonth = $previousYear.'-'.date('m', strtotime($mondayDate));
2086        // Same ISO week one year earlier (52 weeks = 364 days keeps the weekday)
2087        // so the prior-year row's weekly columns compare like-for-like instead of
2088        // always reading 0 — the current-year week dates never match last year.
2089        $prevMondayDate = date('Y-m-d', strtotime($mondayDate.' - 364 days'));
2090        $prevSundayDate = date('Y-m-d', strtotime($prevMondayDate.' + 6 days'));
2091
2092        // Display order + label overrides (DB stores "Comunidad Valenciana").
2093        // FIRE-1318 Duarte review (2026-07-10): pin every region that has a
2094        // target so they render in a stable order rather than falling back
2095        // to the "discovered from data" ordering below.
2096        $regionOrder = [
2097            'Madrid' => 'Madrid',
2098            'Cataluña' => 'Cataluña',
2099            'Comunidad Valenciana' => 'Valencia',
2100            'Castellón' => 'Castellón',
2101            'Baleares' => 'Baleares',
2102            'Aragón' => 'Aragón',
2103            'Andalucía' => 'Andalucía',
2104            'Castilla-La Mancha' => 'Castilla-La Mancha',
2105            'Castilla y León' => 'Castilla y León',
2106        ];
2107
2108        // FIRE-1318 Feature 3: YTD cutoffs. Current year caps at today; prior
2109        // year caps at same-day-last-year so the prev-YTD row compares
2110        // like-for-like (was a full-year figure before, which made "YoY YTD"
2111        // meaningless when the report ran mid-year).
2112        $currentYtdCutoff = $now;
2113        $previousYtdCutoff = date('Y-m-d', strtotime($now.' - 1 year'));
2114
2115        $currentRows = $this->query_regional_metrics($currentYear, $mondayDate, $sundayDate, $currentMonth, $currentYtdCutoff);
2116        $previousRows = $this->query_regional_metrics($previousYear, $prevMondayDate, $prevSundayDate, $previousMonth, $previousYtdCutoff);
2117
2118        // FIRE-1202: merge accepted aggregates from a parallel query that filters
2119        // by acceptance_date instead of issue_date. Without this, the email's
2120        // 'Aceptado' row would undercount because budgets issued in $prev_year
2121        // but accepted in $year would be missed.
2122        //
2123        // FIRE-1318 Feature 2: also run the accepted query for the prior year so
2124        // the 2025 row's YTD (% acept.) column has a value (mockup shows it
2125        // populated with the prior-year acceptance ratio).
2126        $currentAccepted = $this->query_regional_accepted_metrics($currentYear, $mondayDate, $sundayDate, $currentYtdCutoff, $currentMonth);
2127        foreach ($currentAccepted as $regionKey => $accRow) {
2128            if (! isset($currentRows[$regionKey])) {
2129                $currentRows[$regionKey] = $this->blank_region_metrics();
2130            }
2131            foreach ($accRow as $k => $v) {
2132                $currentRows[$regionKey][$k] = $v;
2133            }
2134        }
2135
2136        $previousAccepted = $this->query_regional_accepted_metrics($previousYear, $prevMondayDate, $prevSundayDate, $previousYtdCutoff, $previousMonth);
2137        foreach ($previousAccepted as $regionKey => $accRow) {
2138            if (! isset($previousRows[$regionKey])) {
2139                $previousRows[$regionKey] = $this->blank_region_metrics();
2140            }
2141            foreach ($accRow as $k => $v) {
2142                $previousRows[$regionKey][$k] = $v;
2143            }
2144        }
2145
2146        // FIRE-1318 Feature 4: per-region cumulative targets sourced from
2147        // tbl_executive_report_targets (seeded from Berta's Excel). Two lookups
2148        // per region: current-week cumulative → `Obj. YTD`, MAX(week_number) →
2149        // `Obj. anual`. Previously `Obj. YTD = annual_target × (week / 52)`,
2150        // which assumed a linear ramp; the Excel encodes month-weighted
2151        // cadence, so a mid-March week isn't at 3/12 = 25% of annual.
2152        $ytdTargets = DB::table('tbl_executive_report_targets')
2153            ->where('year', $currentYear)
2154            ->where('week_number', $weekNumber)
2155            ->where('kind', 'emitidos')
2156            ->pluck('cumulative_target', 'region');
2157
2158        $annualTargets = DB::table('tbl_executive_report_targets')
2159            ->where('year', $currentYear)
2160            ->where('kind', 'emitidos')
2161            ->select('region', DB::raw('MAX(cumulative_target) as annual'))
2162            ->groupBy('region')
2163            ->pluck('annual', 'region');
2164
2165        // FIRE-1318 Sergio review (2026-07-10): Aceptado targets from the same
2166        // Excel workbook's "Semanal - Aceptados" sheet. Seeded with kind='aceptados';
2167        // rendered in the accepted row's Obj YTD / Obj anual / % Dif cells.
2168        $ytdTargetsAcc = DB::table('tbl_executive_report_targets')
2169            ->where('year', $currentYear)
2170            ->where('week_number', $weekNumber)
2171            ->where('kind', 'aceptados')
2172            ->pluck('cumulative_target', 'region');
2173
2174        $annualTargetsAcc = DB::table('tbl_executive_report_targets')
2175            ->where('year', $currentYear)
2176            ->where('kind', 'aceptados')
2177            ->select('region', DB::raw('MAX(cumulative_target) as annual'))
2178            ->groupBy('region')
2179            ->pluck('annual', 'region');
2180
2181        // Merge any region present in the data but missing from the display map.
2182        foreach (array_keys($currentRows + $previousRows + $annualTargets->all()) as $regionKey) {
2183            if (! isset($regionOrder[$regionKey])) {
2184                $regionOrder[$regionKey] = $regionKey;
2185            }
2186        }
2187
2188        $regions = [];
2189        $totalsCurrent = $this->blank_region_metrics();
2190        $totalsPrevious = $this->blank_region_metrics();
2191        $totalAnnualTarget = 0.0;
2192        $totalYtdTarget = 0.0;
2193        // FIRE-1318 Sergio review: separate accumulators for the accepted-row
2194        // targets so the Total row's Aceptado gets its own Obj. YTD / anual /
2195        // % Dif — independent of the emitidos target totals.
2196        $totalAnnualTargetAcc = 0.0;
2197        $totalYtdTargetAcc = 0.0;
2198
2199        foreach ($regionOrder as $regionKey => $label) {
2200            $cur = $currentRows[$regionKey] ?? $this->blank_region_metrics();
2201            $prev = $previousRows[$regionKey] ?? $this->blank_region_metrics();
2202            $annualTarget = (float) ($annualTargets[$regionKey] ?? 0.0);
2203            $ytdTarget = (float) ($ytdTargets[$regionKey] ?? 0.0);
2204            $annualTargetAcc = (float) ($annualTargetsAcc[$regionKey] ?? 0.0);
2205            $ytdTargetAcc = (float) ($ytdTargetsAcc[$regionKey] ?? 0.0);
2206
2207            // Skip regions with no target AND no activity at all (keeps the
2208            // table to the operating regions).
2209            if ($annualTarget == 0 && $cur['ytd_budgets'] == 0 && $prev['ytd_budgets'] == 0) {
2210                continue;
2211            }
2212
2213            $regions[] = [
2214                'label' => $label,
2215                // Canonical tbl_companies.region key (may differ from the
2216                // display label — e.g. 'Comunidad Valenciana' vs 'Valencia').
2217                // Ref. 25 is keyed by this, not the label.
2218                'region' => $regionKey,
2219                'current' => $cur,
2220                'previous' => $prev,
2221                'annual_target' => $annualTarget,
2222                'ytd_target' => $ytdTarget,
2223                'diff_pct' => $this->ytd_diff_pct($cur['ytd_amount'], $ytdTarget),
2224                'annual_target_acc' => $annualTargetAcc,
2225                'ytd_target_acc' => $ytdTargetAcc,
2226                'diff_pct_acc' => $this->ytd_diff_pct($cur['ytd_accepted_amount'], $ytdTargetAcc),
2227            ];
2228
2229            $totalsCurrent = $this->add_region_metrics($totalsCurrent, $cur);
2230            $totalsPrevious = $this->add_region_metrics($totalsPrevious, $prev);
2231            $totalAnnualTarget += $annualTarget;
2232            $totalYtdTarget += $ytdTarget;
2233            $totalAnnualTargetAcc += $annualTargetAcc;
2234            $totalYtdTargetAcc += $ytdTargetAcc;
2235        }
2236
2237        return [
2238            'weekNumber' => $weekNumber,
2239            'mondayDate' => $mondayDate,
2240            'sundayDate' => $sundayDate,
2241            'currentYear' => $currentYear,
2242            'previousYear' => $previousYear,
2243            // Exposed so the year-by-week charts can window on exactly the same
2244            // cutoff sections 1-4 use. "The figures in the charts agree with the
2245            // figures shown elsewhere for the same week" is an explicit
2246            // acceptance criterion, and re-deriving this would let the two drift.
2247            'ytdCutoff' => $currentYtdCutoff,
2248            'regions' => $regions,
2249            'totals' => [
2250                'current' => $totalsCurrent,
2251                'previous' => $totalsPrevious,
2252                'annual_target' => $totalAnnualTarget,
2253                'ytd_target' => $totalYtdTarget,
2254                'diff_pct' => $this->ytd_diff_pct($totalsCurrent['ytd_amount'], $totalYtdTarget),
2255                'annual_target_acc' => $totalAnnualTargetAcc,
2256                'ytd_target_acc' => $totalYtdTargetAcc,
2257                'diff_pct_acc' => $this->ytd_diff_pct($totalsCurrent['ytd_accepted_amount'], $totalYtdTargetAcc),
2258            ],
2259        ];
2260    }
2261
2262    /**
2263     * Per-region conditional aggregation for one year. Returns a map keyed by
2264     * region with weekly, current-month and YTD figures (+ service splits).
2265     */
2266    private function query_regional_metrics($year, $mondayDate, $sundayDate, $currentMonth, $ytdCutoff): array
2267    {
2268        // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued.
2269        $issued = self::wcrIssuedDateSql();
2270
2271        // FIRE-1202: align with /commercial-productivity (Quotations::list_quotation_analytics_commercial_productivity).
2272        // The page uses `q.issue_date` as its date axis — every "this week", "this
2273        // month", "this year" grouping on the page is by issue_date. The email
2274        // previously used `q.created_at`, so a budget created in Dec but issued
2275        // in Jan landed in different years on the two sides. Switching the
2276        // email's axis to issue_date (and dropping the YEAR(created)=YEAR(issue)
2277        // constraint that hid mixed-year budgets) reconciles the totals.
2278        //
2279        // Accepted aggregates live in `query_regional_accepted_metrics()` because
2280        // the page filters them by `YEAR(q.acceptance_date) = $year`, not by
2281        // issue year. Keeping them in this query would scope them to budgets
2282        // also issued in $year, dropping ~5% of acceptances at year boundaries.
2283        //
2284        // Issued/sent axis is `q.sent_date` (timestamp of the LAST send), matching
2285        // the Productividad page; issue_date stays the ERP budget date.
2286        $week = "DATE_FORMAT({$issued}, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}'";
2287
2288        $query = "SELECT
2289                    c.region region,
2290                    COUNT(CASE WHEN {$week} THEN 1 END) week_budgets,
2291                    COALESCE(SUM(CASE WHEN {$week} THEN q.amount END), 0) week_amount,
2292                    COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 3 THEN q.amount END), 0) week_preventive,
2293                    COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 5 THEN q.amount END), 0) week_corrective,
2294                    COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 4 THEN q.amount END), 0) week_installation,
2295                    -- FIRE-1202 follow-up (Berta, 2026-06-26): groups 6/7/8 (Pasivo /
2296                    -- Otros / Venta) are collapsed into a single Otros column.
2297                    COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) week_otros,
2298                    COALESCE(SUM(CASE WHEN DATE_FORMAT({$issued}, '%Y-%m') = '{$currentMonth}' THEN q.amount END), 0) month_amount,
2299                    -- FIRE-1448 v2: month_budgets parallel to month_amount so the
2300                    -- Grupo block can render both # and € for the month cell.
2301                    COUNT(CASE WHEN DATE_FORMAT({$issued}, '%Y-%m') = '{$currentMonth}' THEN 1 END) month_budgets,
2302                    COUNT(*) ytd_budgets,
2303                    COALESCE(SUM(q.amount), 0) ytd_amount,
2304                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) ytd_preventive,
2305                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) ytd_corrective,
2306                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) ytd_installation,
2307                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) ytd_otros,
2308                    -- Per-type YTD budget counts for Tabla 4 percent-mix-num
2309                    -- (presupuestos del tipo / emitidos YTD).
2310                    COUNT(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END) ytd_preventive_n,
2311                    COUNT(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END) ytd_corrective_n,
2312                    COUNT(CASE WHEN bt.budget_type_group_id = 4 THEN 1 END) ytd_installation_n,
2313                    COUNT(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN 1 END) ytd_otros_n
2314                FROM tbl_quotations q
2315                    JOIN tbl_companies c ON q.company_id = c.company_id
2316                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
2317                WHERE c.is_send_executive = 1
2318                    AND c.region IS NOT NULL
2319                    AND q.for_add = 0
2320                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2321                    AND {$issued} IS NOT NULL
2322                    AND q.budget_type_id IS NOT NULL
2323                    AND q.budget_type_id != 7
2324                    -- FIRE-1202: page (list_quotation_analytics_commercial_productivity)
2325                    -- has `bt.include = 1` and a non-empty `q.commercial` guard plus
2326                    -- the budget_type_id != 7 (Anulado) exclusion above. Mirror all
2327                    -- three so the email's row totals match the page's for the same
2328                    -- ISO week / year.
2329                    AND bt.include = 1
2330                    AND q.commercial IS NOT NULL AND q.commercial != ''
2331                    -- FIRE-1318 Feature 3: strict YTD windowing. Cap at
2332                    -- `$ytdCutoff` (today for current year, same-day-last-year
2333                    -- for prior year) so the prior-year row is a proper
2334                    -- YTD-vs-YTD comparison.
2335                    --
2336                    -- FIRE-1318 Sergio review (2026-07-10): year boundary
2337                    -- must be ISO 8601 (Monday-adjusted), matching the
2338                    -- Productividad Comercial page's outer grouping at
2339                    -- Quotations::list_quotation_analytics_commercial_productivity
2340                    -- (YEAR(DATE_ADD(q.issue_date, INTERVAL -WEEKDAY DAY))).
2341                    -- Prior version used calendar `q.issue_date >= '{year}-01-01'`
2342                    -- which counted the 4-5 Jan 1-4 days that ISO groups into
2343                    -- the previous year; that drift explains the # deltas
2344                    -- Sergio flagged (4521 vs 4501 on Madrid, etc.).
2345                    AND YEAR(DATE_ADD({$issued}, INTERVAL - WEEKDAY({$issued}) DAY)) = {$year}
2346                    AND DATE({$issued}) <= '{$ytdCutoff}'
2347                GROUP BY c.region";
2348
2349        $rows = [];
2350        foreach (DB::select($query) as $r) {
2351            $rows[$r->region] = [
2352                'week_budgets' => (int) $r->week_budgets,
2353                'week_amount' => (float) $r->week_amount,
2354                'week_preventive' => (float) $r->week_preventive,
2355                'week_corrective' => (float) $r->week_corrective,
2356                'week_installation' => (float) $r->week_installation,
2357                'week_otros' => (float) $r->week_otros,
2358                'month_amount' => (float) $r->month_amount,
2359                'month_budgets' => (int) $r->month_budgets,
2360                'ytd_budgets' => (int) $r->ytd_budgets,
2361                'ytd_amount' => (float) $r->ytd_amount,
2362                'ytd_preventive' => (float) $r->ytd_preventive,
2363                'ytd_corrective' => (float) $r->ytd_corrective,
2364                'ytd_installation' => (float) $r->ytd_installation,
2365                'ytd_otros' => (float) $r->ytd_otros,
2366                'ytd_preventive_n' => (int) $r->ytd_preventive_n,
2367                'ytd_corrective_n' => (int) $r->ytd_corrective_n,
2368                'ytd_installation_n' => (int) $r->ytd_installation_n,
2369                'ytd_otros_n' => (int) $r->ytd_otros_n,
2370                // Accepted aggregates filled in by query_regional_accepted_metrics
2371                // (separate query, different WHERE — see method docblock).
2372                'week_accepted_budgets' => 0, 'week_accepted_amount' => 0.0,
2373                'week_accepted_preventive' => 0.0, 'week_accepted_corrective' => 0.0, 'week_accepted_installation' => 0.0, 'week_accepted_otros' => 0.0,
2374                'month_accepted_amount' => 0.0, 'month_accepted_budgets' => 0,
2375                'ytd_accepted_budgets' => 0, 'ytd_accepted_amount' => 0.0,
2376                'ytd_accepted_preventive' => 0.0, 'ytd_accepted_corrective' => 0.0, 'ytd_accepted_installation' => 0.0, 'ytd_accepted_otros' => 0.0,
2377                'ytd_accepted_preventive_n' => 0, 'ytd_accepted_corrective_n' => 0, 'ytd_accepted_installation_n' => 0, 'ytd_accepted_otros_n' => 0,
2378            ];
2379        }
2380
2381        return $rows;
2382    }
2383
2384    /**
2385     * FIRE-1202: per-region accepted aggregates, filtered by `YEAR(q.acceptance_date)
2386     * = $year` instead of `YEAR(q.issue_date)`. The page (commercial-productivity)
2387     * counts a budget as "accepted in 2026" if its acceptance_date is in 2026,
2388     * regardless of when it was issued. Keeping this scope separate from
2389     * `query_regional_metrics` is necessary because the two share no filterable
2390     * WHERE clause (issue-year vs acceptance-year).
2391     *
2392     * Uses `q.acceptance_date` (the G3W canonical column), not `q.accepted_at`
2393     * (the Titan timestamp) — verified against the page's inner SELECT at
2394     * Quotations::list_quotation_analytics_commercial_productivity ~line 11806.
2395     */
2396    private function query_regional_accepted_metrics($year, $mondayDate, $sundayDate, $ytdCutoff, $currentMonth = null): array
2397    {
2398        $acceptedThisWeek = "DATE(q.acceptance_date) BETWEEN '{$mondayDate}' AND '{$sundayDate}'";
2399        // FIRE-1318: accepted-per-month column ("Junio" cell on the aceptado row
2400        // per Berta's mockup). Falls back to the year-month of $mondayDate when
2401        // caller omits it so behaviour matches the label logic elsewhere.
2402        $accMonth = $currentMonth ?? date('Y-m', strtotime($mondayDate));
2403
2404        $query = "SELECT
2405                    c.region region,
2406                    COUNT(CASE WHEN {$acceptedThisWeek} THEN 1 END) week_accepted_budgets,
2407                    COALESCE(SUM(CASE WHEN {$acceptedThisWeek} THEN q.amount END), 0) week_accepted_amount,
2408                    COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id = 3 THEN q.amount END), 0) week_accepted_preventive,
2409                    COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id = 5 THEN q.amount END), 0) week_accepted_corrective,
2410                    COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id = 4 THEN q.amount END), 0) week_accepted_installation,
2411                    COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) week_accepted_otros,
2412                    COALESCE(SUM(CASE WHEN DATE_FORMAT(q.acceptance_date, '%Y-%m') = '{$accMonth}' THEN q.amount END), 0) month_accepted_amount,
2413                    -- FIRE-1448 v2: month_accepted_budgets parallel to month_accepted_amount.
2414                    COUNT(CASE WHEN DATE_FORMAT(q.acceptance_date, '%Y-%m') = '{$accMonth}' THEN 1 END) month_accepted_budgets,
2415                    COUNT(*) ytd_accepted_budgets,
2416                    COALESCE(SUM(q.amount), 0) ytd_accepted_amount,
2417                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) ytd_accepted_preventive,
2418                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) ytd_accepted_corrective,
2419                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) ytd_accepted_installation,
2420                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) ytd_accepted_otros,
2421                    -- Per-type YTD accepted counts for the aceptado row percent-mix-num.
2422                    COUNT(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END) ytd_accepted_preventive_n,
2423                    COUNT(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END) ytd_accepted_corrective_n,
2424                    COUNT(CASE WHEN bt.budget_type_group_id = 4 THEN 1 END) ytd_accepted_installation_n,
2425                    COUNT(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN 1 END) ytd_accepted_otros_n
2426                FROM tbl_quotations q
2427                    JOIN tbl_companies c ON q.company_id = c.company_id
2428                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
2429                WHERE c.is_send_executive = 1
2430                    AND c.region IS NOT NULL
2431                    AND q.for_add = 0
2432                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2433                    AND q.acceptance_date IS NOT NULL
2434                    AND q.budget_type_id IS NOT NULL
2435                    AND q.budget_type_id != 7
2436                    AND bt.include = 1
2437                    AND q.commercial IS NOT NULL AND q.commercial != ''
2438                    -- FIRE-1318 Feature 3 + Sergio review (2026-07-10): ISO 8601
2439                    -- year boundary on acceptance_date, matching the page's
2440                    -- outer grouping. See query_regional_metrics for rationale.
2441                    AND YEAR(DATE_ADD(q.acceptance_date, INTERVAL - WEEKDAY(q.acceptance_date) DAY)) = {$year}
2442                    AND q.acceptance_date <= '{$ytdCutoff}'
2443                GROUP BY c.region";
2444
2445        $rows = [];
2446        foreach (DB::select($query) as $r) {
2447            $rows[$r->region] = [
2448                'week_accepted_budgets' => (int) $r->week_accepted_budgets,
2449                'week_accepted_amount' => (float) $r->week_accepted_amount,
2450                'week_accepted_preventive' => (float) $r->week_accepted_preventive,
2451                'week_accepted_corrective' => (float) $r->week_accepted_corrective,
2452                'week_accepted_installation' => (float) $r->week_accepted_installation,
2453                'week_accepted_otros' => (float) $r->week_accepted_otros,
2454                'month_accepted_amount' => (float) $r->month_accepted_amount,
2455                'month_accepted_budgets' => (int) $r->month_accepted_budgets,
2456                'ytd_accepted_budgets' => (int) $r->ytd_accepted_budgets,
2457                'ytd_accepted_amount' => (float) $r->ytd_accepted_amount,
2458                'ytd_accepted_preventive' => (float) $r->ytd_accepted_preventive,
2459                'ytd_accepted_corrective' => (float) $r->ytd_accepted_corrective,
2460                'ytd_accepted_installation' => (float) $r->ytd_accepted_installation,
2461                'ytd_accepted_otros' => (float) $r->ytd_accepted_otros,
2462                'ytd_accepted_preventive_n' => (int) $r->ytd_accepted_preventive_n,
2463                'ytd_accepted_corrective_n' => (int) $r->ytd_accepted_corrective_n,
2464                'ytd_accepted_installation_n' => (int) $r->ytd_accepted_installation_n,
2465                'ytd_accepted_otros_n' => (int) $r->ytd_accepted_otros_n,
2466            ];
2467        }
2468
2469        return $rows;
2470    }
2471
2472    private function blank_region_metrics(): array
2473    {
2474        return [
2475            'week_budgets' => 0, 'week_amount' => 0.0,
2476            'week_preventive' => 0.0, 'week_corrective' => 0.0, 'week_installation' => 0.0, 'week_otros' => 0.0,
2477            // FIRE-1448 v2: month_budgets is the # parallel to month_amount, so the
2478            // group block can render both € and # aceptados / emitidos per month.
2479            'month_amount' => 0.0, 'month_budgets' => 0,
2480            'ytd_budgets' => 0, 'ytd_amount' => 0.0,
2481            'ytd_preventive' => 0.0, 'ytd_corrective' => 0.0, 'ytd_installation' => 0.0, 'ytd_otros' => 0.0,
2482            'ytd_preventive_n' => 0, 'ytd_corrective_n' => 0, 'ytd_installation_n' => 0, 'ytd_otros_n' => 0,
2483            'week_accepted_budgets' => 0, 'week_accepted_amount' => 0.0,
2484            'week_accepted_preventive' => 0.0, 'week_accepted_corrective' => 0.0, 'week_accepted_installation' => 0.0, 'week_accepted_otros' => 0.0,
2485            'month_accepted_amount' => 0.0, 'month_accepted_budgets' => 0,
2486            'ytd_accepted_budgets' => 0, 'ytd_accepted_amount' => 0.0,
2487            'ytd_accepted_preventive' => 0.0, 'ytd_accepted_corrective' => 0.0, 'ytd_accepted_installation' => 0.0, 'ytd_accepted_otros' => 0.0,
2488            'ytd_accepted_preventive_n' => 0, 'ytd_accepted_corrective_n' => 0, 'ytd_accepted_installation_n' => 0, 'ytd_accepted_otros_n' => 0,
2489        ];
2490    }
2491
2492    private function add_region_metrics(array $acc, array $row): array
2493    {
2494        foreach ($acc as $k => $v) {
2495            $acc[$k] = $v + $row[$k];
2496        }
2497
2498        return $acc;
2499    }
2500
2501    private function ytd_diff_pct($ytdAmount, $ytdTarget)
2502    {
2503        if ($ytdTarget <= 0) {
2504            return null; // no target set → no pace comparison
2505        }
2506
2507        return (($ytdAmount - $ytdTarget) / $ytdTarget) * 100;
2508    }
2509
2510    /**
2511     * PART 2 data: aggregate the current week by commercial (restricted to
2512     * is_commercial users in "send executive" companies). Returns top-3 by
2513     * value and by volume, plus the below-weekly-target lists.
2514     */
2515    public function get_weekly_individual_performance($mondayDate, $sundayDate): array
2516    {
2517        // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued.
2518        $issued = self::wcrIssuedDateSql();
2519
2520        $currentYear = (int) date('Y');
2521
2522        // FIRE-1202 follow-up (Berta, 2026-06-26): roster sourced from tbl_users +
2523        // tbl_company_users instead of tbl_quotations. Every is_commercial=1 user
2524        // assigned to an is_send_executive=1 company now appears, even with zero
2525        // budgets this year. Previously the roster required at least one same-
2526        // year-issued budget, which dropped specialists Berta needed to see
2527        // (Carlos Alonso, Marta Valderrama, Carlos Javier Ortega, Esmeralda
2528        // Robles). The downstream weekly aggregates still key off
2529        // `q.commercial = u.name`, so budget-by-budget attribution is unchanged.
2530        // FIRE-1448 Sub-task 4 (Luis Reyes review, 2026-07-12): the roster
2531        // used to accept any tbl_users.is_commercial=1 row, which pulled in
2532        // partner-company users (Oasys, Sat Valles) and ex-employees whose
2533        // role was `Regular`. Restrict to the two "real commercial" roles
2534        // (WCR_ALLOWED_ROLES) plus the "comercial adyacente" flag for
2535        // Iker-style exceptions Luis wants included.
2536        $allowedRolesSql = "'".implode("', '", array_map(fn ($r) => addslashes($r), self::WCR_ALLOWED_ROLES))."'";
2537        $roster = [];
2538        $rosterQuery = "SELECT u.name commercial, MAX(c.region) region, MAX(rl.name) role
2539                FROM tbl_users u
2540                    JOIN tbl_company_users cu ON cu.user_id = u.id
2541                    JOIN tbl_companies c ON cu.company_id = c.company_id
2542                    LEFT JOIN tbl_roles rl ON u.role_id = rl.role_id
2543                WHERE u.is_commercial = 1
2544                    AND c.is_send_executive = 1
2545                    AND c.region IS NOT NULL
2546                    AND u.name IS NOT NULL AND u.name != ''
2547                    AND (rl.name IN ({$allowedRolesSql}) OR u.is_commercial_adyacente = 1)
2548                GROUP BY u.name";
2549        foreach (DB::select($rosterQuery) as $r) {
2550            $roster[$r->commercial] = $this->blank_commercial_metrics($r->commercial, $r->region, $r->role);
2551        }
2552
2553        // Current-week aggregates per commercial.
2554        $weekQuery = "SELECT
2555                    q.commercial commercial,
2556                    MAX(c.region) region,
2557                    MAX(rl.name) role,
2558                    COUNT(*) n_budgets,
2559                    COALESCE(SUM(q.amount), 0) total_amount,
2560                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) preventive,
2561                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) corrective,
2562                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) installation
2563                FROM tbl_quotations q
2564                    JOIN tbl_companies c ON q.company_id = c.company_id
2565                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
2566                    JOIN tbl_users u ON u.name = q.commercial AND u.is_commercial = 1
2567                    LEFT JOIN tbl_roles rl ON u.role_id = rl.role_id
2568                WHERE c.is_send_executive = 1
2569                    AND q.for_add != 1
2570                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2571                    AND DATE({$issued}) BETWEEN '{$mondayDate}' AND '{$sundayDate}'
2572                    AND q.budget_type_id != 7
2573                    AND q.commercial IS NOT NULL AND q.commercial != ''
2574                GROUP BY q.commercial";
2575        foreach (DB::select($weekQuery) as $r) {
2576            // FIRE-1448 Sub-task 4: only overwrite roster entries the seed
2577            // query already produced. A user filtered out by the role
2578            // allowlist above shouldn't reappear here just because they
2579            // issued a budget this week — that's exactly the case Luis
2580            // called out (Oasys/Sat Valles submitting occasional budgets).
2581            if (! isset($roster[$r->commercial])) {
2582                continue;
2583            }
2584            $roster[$r->commercial] = [
2585                'name' => $r->commercial,
2586                'region' => $r->region,
2587                'role' => $r->role,
2588                'n_budgets' => (int) $r->n_budgets,
2589                'total_amount' => (float) $r->total_amount,
2590                'preventive' => (float) $r->preventive,
2591                'corrective' => (float) $r->corrective,
2592                'installation' => (float) $r->installation,
2593            ];
2594        }
2595
2596        // Per-person weekly EMITIDO € objectives (Section 5 "Performance
2597        // individual"). The FTE sheet stores CUMULATIVE targets per ISO week, so
2598        // the weekly target is cumulative(week) - cumulative(week-1) (or
2599        // cumulative(1) in week 1). Year/week are derived from the Monday of the
2600        // reporting window — the same source the regional method uses — so a
2601        // report spanning a year boundary uses the week's own ISO year rather
2602        // than today's calendar year.
2603        $targetYear = (int) date('Y', strtotime($mondayDate));
2604        $weekNumber = (int) date('W', strtotime($mondayDate));
2605        $prevWeek = $weekNumber - 1;
2606        $weeksToPull = $weekNumber === 1 ? [1] : [$weekNumber, $prevWeek];
2607        // Single query pulls this week + the prior week for every person, keyed
2608        // by name → [week => cumulative], so we never fire N per-person queries.
2609        $cumByName = [];
2610        $targetRows = DB::table('tbl_executive_report_fte_targets')
2611            ->select('commercial_name', 'week_number', 'cumulative_target')
2612            ->where('year', $targetYear)
2613            ->where('kind', 'emitidos')
2614            ->whereIn('week_number', $weeksToPull)
2615            ->get();
2616        foreach ($targetRows as $tr) {
2617            $cumByName[$tr->commercial_name][(int) $tr->week_number] = (float) $tr->cumulative_target;
2618        }
2619        foreach ($roster as $name => &$row) {
2620            // Exact-name match only (MySQL collation is accent-insensitive). No
2621            // row for this person/week → NULL, and the value table falls back to
2622            // WCR_TARGET_VALUE. The sheet has NO per-# target, so the volume
2623            // table stays on the WCR_TARGET_VOLUME constant (see below).
2624            $target = null;
2625            if (isset($cumByName[$name][$weekNumber])) {
2626                $curr = $cumByName[$name][$weekNumber];
2627                $target = $weekNumber === 1
2628                    ? $curr
2629                    : $curr - ($cumByName[$name][$prevWeek] ?? 0.0);
2630            }
2631            $row['target_value'] = $target;
2632        }
2633        unset($row);
2634
2635        // Consecutive weeks hitting the weekly objective. Counted back from the
2636        // last *completed* week — the report goes out before the reporting week
2637        // ends, so including it would break everybody's streak every Saturday.
2638        // Both polarities: the Top-3 tables show weeks met, the bajo-objetivo
2639        // tables show weeks NOT met (they are negative tables).
2640        $streaks = $this->get_weekly_streaks($targetYear, $weekNumber - 1, $roster);
2641        foreach ($roster as $name => &$row) {
2642            $row['weeks_met'] = $streaks[$name]['met'] ?? null;
2643            $row['weeks_missed'] = $streaks[$name]['missed'] ?? null;
2644        }
2645        unset($row);
2646
2647        $all = array_values($roster);
2648
2649        // 2026-07-27: all four Section-5 tables are now split by tbl_roles.name —
2650        // left column = "Asesor de clientes" (judged on € issued), right column =
2651        // "Sales specialist" (judged on # budgets issued). Before this change only
2652        // the two bajo-objetivo tables were role-split; the Top 3 cards ranked
2653        // both roles together, so specialists (many small budgets) and asesores
2654        // (fewer, larger ones) competed on the same scale and the volume podium
2655        // was permanently specialist-only anyway.
2656        //
2657        // The asesor side is "not a Sales specialist" rather than a strict
2658        // 'Asesor de clientes' string match so the is_commercial_adyacente
2659        // exceptions from the roster (Iker et al., whose primary role is neither
2660        // of WCR_ALLOWED_ROLES) keep showing up — they're judged on € like the
2661        // asesores. This is the same predicate the old value table used.
2662        $isSpecialist = fn ($x) => strcasecmp(trim((string) ($x['role'] ?? '')), self::WCR_SPECIALIST_ROLE) === 0;
2663        $advisors = array_values(array_filter($all, fn ($x) => ! $isSpecialist($x)));
2664        $specialists = array_values(array_filter($all, $isSpecialist));
2665
2666        // Top 3 — only people with activity this week, ranked inside their own
2667        // role: asesores by € issued, specialists by # budgets issued.
2668        $topAdvisors = array_values(array_filter($advisors, fn ($x) => $x['n_budgets'] > 0));
2669        usort($topAdvisors, fn ($a, $b) => $b['total_amount'] <=> $a['total_amount']);
2670        $topAdvisors = array_slice($topAdvisors, 0, 3);
2671
2672        $topSpecialists = array_values(array_filter($specialists, fn ($x) => $x['n_budgets'] > 0));
2673        usort($topSpecialists, fn ($a, $b) => $b['n_budgets'] <=> $a['n_budgets']);
2674        $topSpecialists = array_slice($topSpecialists, 0, 3);
2675
2676        // Below weekly objective — across the full roster (people with zero
2677        // budgets this week included, they're the ones furthest below).
2678        //
2679        // Asesores: judge each against their per-person weekly € objective when
2680        // the FTE sheet provides one ($x['target_value']); otherwise fall back to
2681        // the WCR_TARGET_VALUE constant (unchanged).
2682        $belowAdvisors = array_values(array_filter($advisors, fn ($x) => $x['total_amount'] < ($x['target_value'] ?? self::WCR_TARGET_VALUE)));
2683        usort($belowAdvisors, fn ($a, $b) => $a['total_amount'] <=> $b['total_amount']);
2684
2685        // Specialists: left on the WCR_TARGET_VOLUME constant — the objectives
2686        // sheet only provides per-€ FTE targets, not a per-# budget target.
2687        $belowSpecialists = array_values(array_filter($specialists, fn ($x) => $x['n_budgets'] < self::WCR_TARGET_VOLUME));
2688        usort($belowSpecialists, fn ($a, $b) => $a['n_budgets'] <=> $b['n_budgets']);
2689
2690        return [
2691            'topAdvisors' => $topAdvisors,
2692            'topSpecialists' => $topSpecialists,
2693            'belowAdvisors' => $belowAdvisors,
2694            'belowSpecialists' => $belowSpecialists,
2695        ];
2696    }
2697
2698    private function blank_commercial_metrics($name, $region = null, $role = null): array
2699    {
2700        return [
2701            'name' => $name,
2702            'region' => $region,
2703            'role' => $role,
2704            'n_budgets' => 0,
2705            'total_amount' => 0.0,
2706            'preventive' => 0.0,
2707            'corrective' => 0.0,
2708            'installation' => 0.0,
2709            // Consecutive weeks hitting the weekly objective; null = never hit
2710            // one yet, rendered as a dash rather than a zero.
2711            // Weeks meeting / not meeting the weekly objective this year. null =
2712            // no eligible week of that kind yet, rendered as a dash.
2713            'weeks_met' => null,
2714            'weeks_missed' => null,
2715        ];
2716    }
2717
2718    /**
2719     * Year-by-week data for the four trend charts: one point per ISO week of
2720     * $isoYear up to and including $upToWeek.
2721     *
2722     * The WHERE clauses are deliberate copies of `query_regional_metrics` and
2723     * `query_regional_accepted_metrics` — same joins, same exclusions
2724     * (`for_add`, `budget_type_id != 7`, `bt.include = 1`, non-empty
2725     * `commercial`), same ISO-8601 year boundary and same YTD cutoff. A week's
2726     * point therefore equals the sum of that week's per-region figures in
2727     * sections 1-4, which is an explicit acceptance criterion. Change a filter
2728     * there and it must change here too.
2729     *
2730     * Emitido is keyed on `sent_date`, aceptado on `acceptance_date`, matching
2731     * the split the two source methods already make (FIRE-1202).
2732     *
2733     * @param  bool  $lastWeekOpen  true when $upToWeek has not finished yet
2734     */
2735    public function get_weekly_year_series(int $isoYear, int $upToWeek, string $ytdCutoff, bool $lastWeekOpen): array
2736    {
2737        // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued.
2738        $issued = self::wcrIssuedDateSql();
2739
2740        $cacheKey = 'series:'.$isoYear.':'.$upToWeek.':'.$ytdCutoff.':'.($lastWeekOpen ? 1 : 0);
2741        if (isset($this->wcrYearCache[$cacheKey])) {
2742            return $this->wcrYearCache[$cacheKey];
2743        }
2744
2745        // WEEK(d, 3) is ISO-8601 (Monday-based, 1..53) — the same numbering
2746        // PHP's date('W') produces, which is what $weekNumber upstream uses.
2747        $emitidoSql = "SELECT WEEK({$issued}, 3) wk, COUNT(*) n, COALESCE(SUM(q.amount), 0) eur
2748                FROM tbl_quotations q
2749                    JOIN tbl_companies c ON q.company_id = c.company_id
2750                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
2751                WHERE c.is_send_executive = 1
2752                    AND c.region IS NOT NULL
2753                    AND q.for_add = 0
2754                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2755                    AND {$issued} IS NOT NULL
2756                    AND q.budget_type_id IS NOT NULL
2757                    AND q.budget_type_id != 7
2758                    AND bt.include = 1
2759                    AND q.commercial IS NOT NULL AND q.commercial != ''
2760                    AND YEAR(DATE_ADD({$issued}, INTERVAL - WEEKDAY({$issued}) DAY)) = {$isoYear}
2761                    AND DATE({$issued}) <= '{$ytdCutoff}'
2762                GROUP BY wk";
2763
2764        $aceptadoSql = "SELECT WEEK(q.acceptance_date, 3) wk, COUNT(*) n, COALESCE(SUM(q.amount), 0) eur
2765                FROM tbl_quotations q
2766                    JOIN tbl_companies c ON q.company_id = c.company_id
2767                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
2768                WHERE c.is_send_executive = 1
2769                    AND c.region IS NOT NULL
2770                    AND q.for_add = 0
2771                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2772                    AND q.acceptance_date IS NOT NULL
2773                    AND q.budget_type_id IS NOT NULL
2774                    AND q.budget_type_id != 7
2775                    AND bt.include = 1
2776                    AND q.commercial IS NOT NULL AND q.commercial != ''
2777                    AND YEAR(DATE_ADD(q.acceptance_date, INTERVAL - WEEKDAY(q.acceptance_date) DAY)) = {$isoYear}
2778                    AND q.acceptance_date <= '{$ytdCutoff}'
2779                GROUP BY wk";
2780
2781        $byWeek = ['emitido' => [], 'aceptado' => []];
2782        foreach (DB::select($emitidoSql) as $r) {
2783            $byWeek['emitido'][(int) $r->wk] = ['n' => (int) $r->n, 'eur' => (float) $r->eur];
2784        }
2785        foreach (DB::select($aceptadoSql) as $r) {
2786            $byWeek['aceptado'][(int) $r->wk] = ['n' => (int) $r->n, 'eur' => (float) $r->eur];
2787        }
2788
2789        $notes = $this->get_week_notes($isoYear);
2790
2791        // Where the year starts for this report.
2792        //
2793        // The WHERE above attributes a week to the year its MONDAY falls in (the
2794        // FIRE-1318 convention, inherited from the Productividad Comercial page).
2795        // ISO week 1 of 2026 begins Mon 2025-12-29, so under that rule the whole
2796        // week belongs to 2025 and the first week of 2026 is the one starting Mon
2797        // 05/01 — which WEEK(d, 3) numbers as 2.
2798        //
2799        // Iterating from a hardcoded week 1 therefore invented an always-empty
2800        // week that rendered as a plunge to zero and a −100% label. Deriving the
2801        // first week from the same convention keeps the charts consistent with
2802        // sections 1-4 instead of mixing two definitions of "week 1".
2803        $jan1 = strtotime($isoYear.'-01-01');
2804        $mondayOfJan1 = strtotime('-'.((int) date('N', $jan1) - 1).' days', $jan1);
2805        if ((int) date('Y', $mondayOfJan1) < $isoYear) {
2806            $mondayOfJan1 = strtotime('+7 days', $mondayOfJan1);
2807        }
2808        $startWeek = (int) date('W', $mondayOfJan1);
2809
2810        $out = [];
2811        foreach (self::WCR_CHART_SERIES as $key => $meta) {
2812            [$source, $field] = explode('_', $key);
2813            $field = $field === 'eur' ? 'eur' : 'n';
2814
2815            // Weeks with no rows are real zeros, not gaps — a week where nothing
2816            // was issued is information, so the line must dip rather than skip.
2817            $values = [];
2818            for ($w = $startWeek; $w <= $upToWeek; $w++) {
2819                $values[$w] = (float) ($byWeek[$source][$w][$field] ?? 0);
2820            }
2821
2822            // The average is "what normal looks like", so it is taken over
2823            // completed weeks only. Including a half-finished week would drag the
2824            // reference line down and make every other week look better than it is.
2825            $closed = $lastWeekOpen ? array_slice($values, 0, -1, true) : $values;
2826            $average = count($closed) > 0 ? array_sum($closed) / count($closed) : 0.0;
2827
2828            // A note renders as the caption on that week's shaded band, and again
2829            // in full underneath the chart (the band has ~16 characters of room).
2830            $chartNotes = [];
2831
2832            $points = [];
2833            foreach ($values as $w => $v) {
2834                $isOpen = $lastWeekOpen && $w === $upToWeek;
2835
2836                $note = $notes[$w][$key] ?? $notes[$w]['all'] ?? null;
2837                if ($note !== null) {
2838                    $chartNotes[] = ['week' => $w, 'note' => $note];
2839                }
2840
2841                $points[] = [
2842                    'week' => $w,
2843                    'value' => $v,
2844                    // No variance label on the open week. It is only part-way
2845                    // through, so a "−48%" there would read as a collapse when it
2846                    // is just an unfinished week — the ticket calls this out
2847                    // explicitly.
2848                    'pct' => ($isOpen || $average <= 0) ? null : (($v - $average) / $average) * 100,
2849                    'in_progress' => $isOpen,
2850                    'note' => $note,
2851                ];
2852            }
2853
2854            $out[$key] = [
2855                'title' => $meta['title'],
2856                'kind' => $meta['kind'],
2857                'points' => $points,
2858                'average' => $average,
2859                'notes' => $chartNotes,
2860            ];
2861        }
2862
2863        $this->wcrYearCache[$cacheKey] = $out;
2864
2865        return $out;
2866    }
2867
2868    /**
2869     * Active per-week annotations for $year, as [week => [scope => note]].
2870     * Missing table (migration not yet run on this environment) degrades to no
2871     * notes rather than breaking the whole report.
2872     */
2873    private function get_week_notes(int $year): array
2874    {
2875        try {
2876            $rows = DB::table('tbl_executive_report_week_notes')
2877                ->where('year', $year)
2878                ->where('is_active', 1)
2879                ->orderBy('week_number')
2880                ->get();
2881        } catch (\Throwable $e) {
2882            Log::channel('cron_send_executive_report')->warning('Week notes unavailable: '.$e->getMessage());
2883
2884            return [];
2885        }
2886
2887        $notes = [];
2888        foreach ($rows as $r) {
2889            $notes[(int) $r->week_number][(string) $r->chart_scope] = (string) $r->note;
2890        }
2891
2892        return $notes;
2893    }
2894
2895    /**
2896     * How many weeks in a row each commercial has hit their weekly objective,
2897     * counting back from the last completed week.
2898     *
2899     * Measured against the objective that already applies to the role: Sales
2900     * specialists on the number of budgets issued (WCR_TARGET_VOLUME), everyone
2901     * else on the value issued (their FTE-sheet weekly target, falling back to
2902     * WCR_TARGET_VALUE). That is the same pairing the bajo-objetivo tables use,
2903     * so a person's streak and their weekly row cannot disagree about whether
2904     * this week counted.
2905     *
2906     * Weekly figures are keyed on `created_at`, matching
2907     * `get_weekly_individual_performance` rather than the `sent_date` axis of
2908     * sections 1-4 — the streak has to agree with the number printed next to the
2909     * person's name, not with the regional tables.
2910     *
2911     * @param  array  $roster  name => metrics row (needs 'role')
2912     * @return array name => int|null  (null = no streak yet → rendered as a dash)
2913     */
2914    private function get_weekly_streaks(int $isoYear, int $throughWeek, array $roster): array
2915    {
2916        // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued.
2917        $issued = self::wcrIssuedDateSql();
2918
2919        if ($roster === [] || $throughWeek < 1) {
2920            return [];
2921        }
2922
2923        $names = array_keys($roster);
2924        $cacheKey = 'streaks:'.$isoYear.':'.$throughWeek.':'.md5(implode('|', $names));
2925        if (isset($this->wcrYearCache[$cacheKey])) {
2926            return $this->wcrYearCache[$cacheKey];
2927        }
2928
2929        $inNames = "'".implode("', '", array_map(fn ($n) => addslashes((string) $n), $names))."'";
2930
2931        // Per-person weekly issued figures. Filters mirror the current-week
2932        // query in get_weekly_individual_performance exactly (sent_date axis:
2933        // a budget counts in the ISO week of its latest send; for_add != 1,
2934        // budget_type_id != 7).
2935        $weeklySql = "SELECT q.commercial nm, WEEK({$issued}, 3) wk, COUNT(*) n, COALESCE(SUM(q.amount), 0) eur
2936                FROM tbl_quotations q
2937                    JOIN tbl_companies c ON q.company_id = c.company_id
2938                    JOIN tbl_users u ON u.name = q.commercial AND u.is_commercial = 1
2939                WHERE c.is_send_executive = 1
2940                    AND q.for_add != 1
2941                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2942                    AND q.budget_type_id != 7
2943                    AND q.commercial IN ({$inNames})
2944                    AND {$issued} IS NOT NULL
2945                    AND YEAR(DATE_ADD({$issued}, INTERVAL - WEEKDAY({$issued}) DAY)) = {$isoYear}
2946                GROUP BY nm, wk";
2947
2948        $weekly = [];
2949        foreach (DB::select($weeklySql) as $r) {
2950            $weekly[$r->nm][(int) $r->wk] = ['n' => (int) $r->n, 'eur' => (float) $r->eur];
2951        }
2952
2953        // "Any sign of them working in Titan" for the away test. tbl_user_activity
2954        // would be the natural source but only starts 2026-05-04, so it cannot
2955        // speak for the first ~18 weeks of the year; tbl_quotations_log reaches
2956        // back to 2025-10-27 and covers the whole reporting year.
2957        //
2958        // The window is expressed as a plain date range rather than
2959        // YEAR(DATE_ADD(created_at, ...)) so the predicate stays sargable — the
2960        // table has no index on created_at, and wrapping the column in functions
2961        // removes any chance of using one if it is ever added.
2962        $isoStart = date('Y-m-d', strtotime($isoYear.'-W01-1'));
2963        $isoEnd = date('Y-m-d', strtotime(($isoYear + 1).'-W01-1'));
2964
2965        $activitySql = "SELECT l.user nm, WEEK(l.created_at, 3) wk, COUNT(*) n
2966                FROM tbl_quotations_log l
2967                WHERE l.user IN ({$inNames})
2968                    AND l.created_at >= '{$isoStart}'
2969                    AND l.created_at < '{$isoEnd}'
2970                GROUP BY nm, wk";
2971
2972        $activity = [];
2973        foreach (DB::select($activitySql) as $r) {
2974            $activity[$r->nm][(int) $r->wk] = (int) $r->n;
2975        }
2976
2977        // Per-person weekly € objectives. The sheet stores cumulative targets, so
2978        // the weekly figure is cumulative(w) - cumulative(w-1).
2979        $cumulative = [];
2980        $targetRows = DB::table('tbl_executive_report_fte_targets')
2981            ->select('commercial_name', 'week_number', 'cumulative_target')
2982            ->where('year', $isoYear)
2983            ->where('kind', 'emitidos')
2984            ->where('week_number', '<=', $throughWeek)
2985            ->get();
2986        foreach ($targetRows as $tr) {
2987            $cumulative[$tr->commercial_name][(int) $tr->week_number] = (float) $tr->cumulative_target;
2988        }
2989
2990        $isSpecialist = fn ($row) => strcasecmp(trim((string) ($row['role'] ?? '')), self::WCR_SPECIALIST_ROLE) === 0;
2991
2992        $streaks = [];
2993        foreach ($roster as $name => $row) {
2994            $specialist = $isSpecialist($row);
2995
2996            // Reduce each week to the three facts the walk needs, newest first.
2997            $weeks = [];
2998            for ($w = $throughWeek; $w >= 1; $w--) {
2999                $n = $weekly[$name][$w]['n'] ?? 0;
3000                $eur = $weekly[$name][$w]['eur'] ?? 0.0;
3001
3002                if ($specialist) {
3003                    $target = (float) self::WCR_TARGET_VOLUME;
3004                    $metric = (float) $n;
3005                } else {
3006                    $cum = $cumulative[$name][$w] ?? null;
3007                    $target = $cum !== null
3008                        ? $cum - ($cumulative[$name][$w - 1] ?? 0.0)
3009                        : (float) self::WCR_TARGET_VALUE;
3010                    $metric = $eur;
3011                }
3012
3013                $weeks[] = [
3014                    // A week the plan itself sets to zero is not measurable
3015                    // (shutdown weeks are encoded that way), so it neither counts
3016                    // as a hit nor breaks the run.
3017                    'measurable' => $target > 0,
3018                    'hit' => $target > 0 && $metric >= $target,
3019                    'worked' => $n > 0 || ($activity[$name][$w] ?? 0) > 0,
3020                ];
3021            }
3022
3023            $streaks[$name] = self::wcr_tally_weeks($weeks);
3024        }
3025
3026        $this->wcrYearCache[$cacheKey] = $streaks;
3027
3028        return $streaks;
3029    }
3030
3031    /**
3032     * How many weeks the person met their weekly objective, kept free of SQL so
3033     * it can be tested directly.
3034     *
3035     * Revised 2026-08-01 (Duarte): this is a TALLY across the year, not a
3036     * consecutive run. Previously a single miss ended the count, so somebody who
3037     * hit objective in 20 weeks but missed last week showed nothing. The figure
3038     * is now "weeks met so far this year":
3039     *   hit                  → counts
3040     *   missed while working  → does not count, and does NOT stop the tally
3041     *   missed while away     → not an eligible week; ignored
3042     *   not measurable        → ignored (the plan set no objective that week)
3043     *
3044     * Order is therefore irrelevant, and no week can cancel another.
3045     *
3046     * Returns null rather than 0 when no week was ever met, because the report
3047     * shows a dash for that and a zero would read as a measured value.
3048     *
3049     * Returns BOTH polarities, because the two halves of section 6 need opposite
3050     * figures: the Top-3 cards report weeks met, while the bajo-objetivo cards are
3051     * negative tables and must report weeks NOT met. Same eligible-week set for
3052     * both, so they are always consistent with each other.
3053     *
3054     * @param  array  $weeks  list of ['hit'=>bool,'worked'=>bool,'measurable'=>bool]
3055     * @return array{met: ?int, missed: ?int}
3056     */
3057    public static function wcr_tally_weeks(array $weeks): array
3058    {
3059        $met = 0;
3060        $missed = 0;
3061
3062        foreach ($weeks as $week) {
3063            // No objective set for the week → nothing to meet or miss.
3064            if (empty($week['measurable'])) {
3065                continue;
3066            }
3067
3068            // Away weeks are not eligible weeks. They cannot be hits, and with a
3069            // tally they cannot count against anybody either — which is what the
3070            // holiday rule was always trying to achieve.
3071            if (empty($week['hit']) && empty($week['worked'])) {
3072                continue;
3073            }
3074
3075            if (! empty($week['hit'])) {
3076                $met++;
3077            } else {
3078                $missed++;
3079            }
3080        }
3081
3082        return [
3083            'met' => $met > 0 ? $met : null,
3084            'missed' => $missed > 0 ? $missed : null,
3085        ];
3086    }
3087
3088    /**
3089     * FIRE-1448 v2 (Luis Reyes review, 2026-07-12): full layout redesign per
3090     * Berta's HTML mockup. Four regional blocks replace the previous single
3091     * wide table:
3092     *   1 · Grupo — 6 metrics × 3 periods × (2026 / 2025 / Δ)
3093     *   2 · Objetivos — 2 cards with progress bars (Emitido, Aceptado)
3094     *   3 · Regional YTD — two tables (€ and #), no weekly, no by-type
3095     *   4 · Mix por tipo YTD — two tables (Emitido, Aceptado)
3096     * Part 5 (Performance individual) keeps the top 3 + bajo objetivo cards and
3097     * the FIRE-1448 BE-2 roster allowlist; since 2026-07-27 its four tables are
3098     * split by role — left column Asesores de clientes (€), right column Sales
3099     * specialists (#).
3100     */
3101    private function build_weekly_commercial_report_html(array $regional, array $individual, array $series = [], array $imgSrc = []): string
3102    {
3103        // Integer euros with dot-thousands and no space (mockup: "87.400€").
3104        // Kept for Section 5 (Performance individual), which is unchanged.
3105        $money = fn ($v) => number_format((float) $v, 0, ',', '.').'€';
3106        // v3 renders every amount in K€ (thousands). $kNum = bare thousands with
3107        // dot-grouping ("16.440", used inside tables whose header already says
3108        // K€); $kEuro appends the "K€" suffix ("48.281K€", used in headline cells).
3109        // Values <500€ round to 0, per the v3 footnote.
3110        $kNum = fn ($v) => number_format((float) round(((float) $v) / 1000), 0, ',', '.');
3111        $kEuro = fn ($v) => $kNum($v).'K€';
3112        $numFmt = fn ($v) => number_format((int) $v, 0, ',', '.');
3113        $pctFmt = fn ($v, $decimals = 1) => number_format((float) $v, $decimals, ',', '.').'%';
3114        // Signed delta between two comparable series. Handles:
3115        //   prev = 0 & cur > 0    → "nuevo" (neutral color)
3116        //   prev = 0 & cur = 0    → "—"
3117        //   ratio ≥ 500 %         → "×N,N" so 12x growth stays readable
3118        //   otherwise             → "+X,X%" (pos) or "−X,X%" (neg)
3119        $deltaPct = function ($cur, $prev) {
3120            $cur = (float) $cur;
3121            $prev = (float) $prev;
3122            if ($prev <= 0) {
3123                return $cur > 0 ? ['label' => 'nuevo', 'sign' => 'neutral'] : ['label' => '—', 'sign' => 'neutral'];
3124            }
3125            $ratio = (($cur - $prev) / $prev) * 100;
3126            if ($ratio >= 500) {
3127                return ['label' => '×'.number_format(($cur / $prev), 1, ',', '.'), 'sign' => 'pos'];
3128            }
3129            $prefix = $ratio >= 0 ? '+' : '';
3130
3131            return ['label' => $prefix.number_format($ratio, 1, ',', '.').'%', 'sign' => $ratio >= 0 ? 'pos' : 'neg'];
3132        };
3133
3134        $font = "'Segoe UI',system-ui,Arial,sans-serif";
3135        $week = (int) $regional['weekNumber'];
3136        $badge = 'Semana '.$week.' &nbsp;·&nbsp; '
3137            .date('d/m', strtotime($regional['mondayDate'])).' – '.date('d/m/Y', strtotime($regional['sundayDate']));
3138        $monthsEs = [1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril', 5 => 'Mayo', 6 => 'Junio',
3139            7 => 'Julio', 8 => 'Agosto', 9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre'];
3140        $monthLabel = $monthsEs[(int) date('n', strtotime($regional['mondayDate']))] ?? 'Mes';
3141
3142        // Sort regions by 2026 YTD amount desc for blocks 3 and 4 (matches Berta's
3143        // mockup which leads with Cataluña / Madrid / CyL). Total row rendered last.
3144        $sortedRegions = $regional['regions'];
3145        usort($sortedRegions, fn ($a, $b) => ($b['current']['ytd_amount'] ?? 0) <=> ($a['current']['ytd_amount'] ?? 0));
3146
3147        // Build the four v3 regional blocks, bound to the real $regional data.
3148        $block1 = $this->wcr_group_table($regional['totals'], $week, $monthLabel, $kEuro, $pctFmt, $deltaPct, $numFmt);
3149        $block2 = $this->wcr_obj_strip($regional['totals'], $kEuro, $pctFmt);
3150        $block3 = $this->wcr_regional_table($sortedRegions, $regional['totals'], $week, $kEuro, $kNum, $numFmt, $deltaPct);
3151        $block4 = $this->wcr_mix_table($sortedRegions, $regional['totals'], $kNum);
3152
3153        // Individual cards (Performance individual — Part 5). Split by role:
3154        // left column = Asesores de clientes (€), right column = Sales
3155        // specialists (#). Both rows keep that same left/right convention.
3156        $cards = $this->wcr_two_col([
3157            $this->wcr_top_card('Top 3 semana · Asesores de clientes (€)', $individual['topAdvisors'], $money),
3158            $this->wcr_top_card('Top 3 semana · Sales specialists (#)', $individual['topSpecialists'], $money),
3159            $this->wcr_under_card('Asesores de clientes bajo objetivo · valor (&lt;'.(int) (self::WCR_TARGET_VALUE / 1000).'K€)', $individual['belowAdvisors'], 'value', $money),
3160            $this->wcr_under_card('Sales specialists bajo objetivo · volumen (&lt;'.(int) self::WCR_TARGET_VOLUME.' pres.)', $individual['belowSpecialists'], 'volume', $money),
3161        ]);
3162
3163        // v3 design: the report is a single standalone document whose <style>
3164        // block (ported from Berta's v3 mockup) drives sections 1–4 via CSS
3165        // classes. CSS custom properties from the mockup are resolved to literal
3166        // colours here because Outlook's Word engine can't evaluate var(). The
3167        // .wcr-col media query for the (unchanged) Section 5 cards is appended.
3168        $style = ''
3169            .'*{box-sizing:border-box;}'
3170            .'body{font-family:-apple-system,"Segoe UI",Helvetica,Arial,sans-serif;color:#1c1c1e;background:#fff;margin:0;padding:0 0 48px 0;}'
3171            .'.header{background:#d92940;color:#fff;padding:22px 36px;display:flex;justify-content:space-between;align-items:center;}'
3172            .'.header h1{font-size:19px;margin:0 0 3px 0;}'
3173            .'.header .sub{font-size:12.5px;opacity:.85;}'
3174            .'.header .week{background:rgba(0,0,0,.18);padding:7px 14px;border-radius:7px;font-size:13px;font-weight:600;white-space:nowrap;}'
3175            .'.section{padding:26px 36px 6px 36px;}'
3176            .'.section h2{font-size:13px;letter-spacing:.8px;text-transform:uppercase;color:#1c1c1e;margin:0 0 4px 0;}'
3177            .'.section .hint{font-size:12px;color:#8a8a8e;margin:0 0 14px 0;line-height:1.5;}'
3178            .'table{border-collapse:collapse;width:100%;font-size:13px;}'
3179            .'th,td{padding:7px 9px;text-align:right;white-space:nowrap;}'
3180            .'th{font-weight:600;}'
3181            .'td:first-child,th:first-child{text-align:left;}'
3182            .'.group-head th{font-size:11.5px;text-transform:uppercase;letter-spacing:.5px;color:#fff;background:#6b6a67;border-left:3px solid #fff;}'
3183            .'.group-head th:first-child{background:transparent;border:none;}'
3184            .'.col-head th{font-size:11.5px;color:#8a8a8e;border-bottom:2px solid #1c1c1e;background:#faf9f7;}'
3185            .'.col-head th.delta{color:#1c1c1e;}'
3186            .'tbody tr{border-bottom:1px solid #e4e2de;}'
3187            .'tbody tr:nth-child(even){background:#faf9f7;}'
3188            .'.metric{font-weight:600;}'
3189            .'.y25{color:#8a8a8e;}'
3190            .'.pos{color:#1a8a4a;font-weight:700;}'
3191            .'.neg{color:#d9482b;font-weight:700;}'
3192            .'.nd{color:#b5b3af;font-style:italic;font-weight:400;}'
3193            .'.block-sep{border-left:3px solid #d8d6d2;}'
3194            .'.rowgroup td{background:#ecebe8;font-size:11.5px;text-transform:uppercase;letter-spacing:.6px;color:#1c1c1e;font-weight:700;padding:6px 12px;box-shadow:inset 4px 0 0 #6b6a67;}'
3195            .'.obj-strip{display:flex;gap:18px;margin-top:6px;}'
3196            .'.obj-card{flex:1;border:1px solid #e4e2de;border-radius:10px;padding:16px 18px;background:#faf9f7;}'
3197            .'.obj-card .label{font-size:11.5px;text-transform:uppercase;letter-spacing:.6px;color:#8a8a8e;font-weight:700;margin-bottom:8px;}'
3198            .'.obj-card .big{font-size:22px;font-weight:700;}'
3199            .'.obj-card .big span{font-size:13px;color:#8a8a8e;font-weight:500;}'
3200            .'.bar{height:8px;background:#e7e5e1;border-radius:4px;margin:10px 0 6px 0;overflow:hidden;}'
3201            .'.bar .fill{height:100%;border-radius:4px;}'
3202            .'.obj-card .foot{font-size:12px;color:#8a8a8e;display:flex;justify-content:space-between;}'
3203            .'.foot-notes{padding:22px 36px 0 36px;font-size:11.5px;color:#8a8a8e;line-height:1.55;border-top:1px solid #e4e2de;margin-top:30px;}'
3204            .'.regional td.name{font-weight:600;}'
3205            .'.total-row td{font-weight:700;background:#f4f2ee !important;border-top:2px solid #1c1c1e;}'
3206            .'.row-25 td{color:#8a8a8e;font-style:italic;font-weight:400 !important;}'
3207            .'.row-acc td{font-weight:700;background:#f4f2ee !important;border-top:2px solid #1c1c1e;}'
3208            .'@media only screen and (max-width:640px){.wcr-col{display:block !important;width:100% !important;padding:0 0 14px 0 !important;}}';
3209
3210        $html = '<!DOCTYPE html><html lang="es"><head><meta charset="UTF-8">'
3211            .'<meta name="viewport" content="width=device-width, initial-scale=1.0">'
3212            .'<meta http-equiv="X-UA-Compatible" content="IE=edge">'
3213            .'<title>Reporte Semanal de Performance Comercial</title>'
3214            .'<style>'.$style.'</style>'
3215            .'</head><body>';
3216
3217        // Header band (v3 .header).
3218        $html .= '<div class="header">'
3219            .'<div><h1>Reporte semanal · Performance comercial</h1>'
3220            .'<div class="sub">Grupo Fire / IBV Group · TITAN</div></div>'
3221            .'<div class="week">'.$badge.'</div></div>';
3222
3223        // Section 1 — Grupo.
3224        $html .= '<div class="section">'
3225            .'<h2>1 · Grupo — Emitido, aceptado y ratio</h2>'
3226            .'<p class="hint">Comparativas siempre like-for-like: semana vs misma semana 2025, mes vs mismo mes 2025, YTD vs mismo periodo 2025 — nunca vs total del año. Todos los importes en K€.</p>'
3227            .$block1.'</div>';
3228
3229        // Section 2 — Objetivos.
3230        $html .= '<div class="section">'
3231            .'<h2>2 · Objetivos — dónde estamos vs plan</h2>'
3232            .'<p class="hint">Objetivos 2026 por región (modelo vf). Solo YTD y año; el objetivo semanal/mensual añade ruido. El color de la barra indica el <strong>nivel de cumplimiento</strong> del objetivo (rojo &lt;70%, ámbar 70–90%, verde ≥90%), no el tipo de dato.</p>'
3233            .$block2.'</div>';
3234
3235        // Section 3 — Regional.
3236        $html .= '<div class="section">'
3237            .'<h2>3 · Regional — semana + YTD en una sola tabla</h2>'
3238            .'<p class="hint">Productividad de la semana por región + acumulado YTD (€ y #, emitido y aceptado, vs 2025 y vs objetivo). Ordenado por emitido YTD. Todos los importes en K€.</p>'
3239            .$block3.'</div>';
3240
3241        // Section 4 — Mix por tipo.
3242        $html .= '<div class="section">'
3243            .'<h2>4 · Mix por tipo de servicio — semana + YTD</h2>'
3244            .'<p class="hint">Repartición del importe por categoría de servicio. <strong>Las filas por región y los dos totales del bloque "Emitido" son importe EMITIDO</strong> (K€ de la semana y acumulado YTD, con el peso del tipo sobre el total de la región en euros y en número de presupuestos). El bloque inferior (banda «Aceptado») es lo ACEPTADO 2026 YTD. Igual que en la sección 3, emitido y aceptado se distinguen por las bandas de fila, no por color; todas las cifras van en negro. Otros = Venta + Pasivo + Otros (grupos 6, 7, 8).</p>'
3245            .$block4.'</div>';
3246
3247        // Section 5 — Año semana a semana. Sits directly above the individual
3248        // performance section: the group picture first, the individual picture
3249        // after it. Nothing above is removed or reordered — the individual
3250        // section simply becomes 6.
3251        if ($series !== []) {
3252            $html .= '<div class="section">'
3253                .'<h2>5 · El año semana a semana</h2>'
3254                .'<p class="hint">Cada gráfico recorre todas las semanas del año hasta la semana reportada. '
3255                .'La <strong>línea discontinua</strong> es la media del año (solo semanas cerradas), y el porcentaje bajo cada semana '
3256                .'indica cuánto se sitúa por encima (verde) o por debajo (rojo) de esa media. '
3257                .'La <strong>última semana aparece sombreada y marcada «en curso»</strong>: el reporte sale antes de que termine, '
3258                .'así que su cifra es parcial por definición y no debe leerse como una caída. '
3259                .'Las semanas con un círculo ámbar llevan una nota explicativa justo debajo del gráfico.</p>'
3260                .$this->wcr_year_charts_section($series, $imgSrc, $kNum, $numFmt)
3261                .'</div>';
3262        }
3263
3264        // Section 6 — Performance individual. Four tables in two role columns:
3265        // Asesores de clientes on the left, Sales specialists on the right.
3266        $html .= '<div class="section">';
3267        $html .= $this->wcr_section('6 · Performance individual', '👤');
3268        $html .= '<p class="hint">Separado por rol: la columna izquierda son los <strong>Asesores de clientes</strong>, medidos por importe emitido (€); la derecha, los <strong>Sales specialists</strong>, medidos por número de presupuestos (#). Cada rol se ordena y se compara solo contra los de su rol. '
3269            .'La etiqueta de la última columna cuenta semanas del año en curso, y su signo depende de la tabla: en los <strong>Top 3</strong> son las '
3270            .'<strong>semanas cumpliendo el objetivo</strong> (verde) y en las tablas de <strong>bajo objetivo</strong> son las '
3271            .'<strong>semanas sin cumplirlo</strong> (rojo). No tienen que ser consecutivas y una semana no descuenta a otra. '
3272            .'Las semanas de ausencia no cuentan en ninguna de las dos direcciones, y la semana en curso todavía no se cuenta. Un guion (—) significa que aún no hay ninguna.</p>';
3273        $html .= $cards;
3274        $html .= '</div>';
3275
3276        // Foot-notes — the report's own data footnotes (the † / * markers the
3277        // tables reference, plus methodology). The v3 mockup's proposal-only
3278        // notes ("Notas de la propuesta v3", "Cambios vs v2", the v1 fix, the
3279        // pending-model line) are deliberately excluded — they described the
3280        // proposal, not the live report.
3281        $html .= '<div class="foot-notes">'
3282            .'<strong>Notas</strong><br>'
3283            .'· Todos los importes del reporte en K€, redondeados al millar más cercano (valores &lt;500€ → 0).<br>'
3284            .'· Comparativas siempre like-for-like: semana vs misma semana 2025, mes vs mismo mes 2025, YTD vs mismo periodo 2025 (01/01 – hoy) — nunca vs total del año.<br>'
3285            .'· † Ref. 25 € = % de aceptación de referencia del año 2025 completo calculado sobre <strong>importe</strong> (€ aceptados / € emitidos), no sobre número de presupuestos, por región (valores facilitados por Duarte) — más estable que el YTD 25 mes a mes si hay obras grandes presupuestadas o aceptadas. "nuevo" = región abierta en 2026, sin referencia 2025.<br>'
3286            .'· * n/d = dato no expuesto aún por el reporte actual. Para completarlo, el CRM debe servir: (1) aceptados 2025 (€, # y ratio) por semana, mes y YTD — a nivel grupo y región; (2) # de presupuestos del mes en curso. Todo existe en base de datos — es solo query.<br>'
3287            .'· Tabla 4: las filas por región y las dos filas "Total emitido" (2026 / 2025) son importe <strong>emitido</strong>; la última fila, "Total aceptado 2026", es importe <strong>aceptado</strong> (2026 YTD).<br>'
3288            .'· % mix € = peso del tipo en <strong>importe</strong> sobre el total YTD de la región (emitido sobre emitido; en la fila "Total aceptado 2026", aceptado sobre aceptado). % mix # = peso del tipo en <strong>número de presupuestos</strong> (# presupuestos del tipo YTD / # emitidos —o aceptados— YTD). Ambos pueden no sumar 100% por redondeo.<br>'
3289            .'· Emitido vs aceptado se distingue por las bandas de fila etiquetadas (igual que en la sección 3, donde son columnas etiquetadas), no por color: todas las cifras van en negro. El color se reserva para una sola cosa — verde = variación positiva, rojo = variación negativa; gris = año anterior. Única excepción: las barras de la sección 2, donde el color = nivel de cumplimiento del objetivo (rojo &lt;70%, ámbar 70–90%, verde ≥90%).<br>'
3290            .'· % aceptación (€) = € aceptados / € emitidos del periodo; (#) = # aceptados / # emitidos. "Aceptado" se cuenta por fecha de aceptación, independiente del año de emisión (mezcla cohortes). Recomendado en drill-down: ratio por cohorte de emisión.<br>'
3291            .'· Objetivos: modelo 2026 vf por región (emitido y aceptado), acumulado semana a semana; el objetivo YTD es el acumulado hasta la semana en curso y el anual, el total del año.<br>'
3292            .'· Sección 5 (el año semana a semana): los cuatro gráficos usan exactamente los mismos filtros que las secciones 1–4, de modo que el punto de una semana coincide con la cifra de esa misma semana en el resto del reporte. Emitido se imputa por fecha de emisión y aceptado por fecha de aceptación, igual que arriba. La media del año se calcula <strong>solo sobre semanas cerradas</strong>: incluir la semana en curso, que va a medias, hundiría la referencia y haría parecer mejores todas las demás. Por el mismo motivo la semana en curso no muestra porcentaje de variación.<br>'
3293            .'· Semanas contadas (sección 6): número <strong>total</strong> de semanas del año en curso, no necesariamente consecutivas, y una semana nunca resta a otra. En los Top 3 se cuentan las semanas <strong>cumpliendo</strong> el objetivo; en las tablas de bajo objetivo, al ser tablas negativas, se cuentan las semanas <strong>sin cumplirlo</strong>. La semana reportada no se cuenta, porque el reporte sale antes de que termine. Cada persona se mide contra el objetivo de su rol: asesores por importe emitido (objetivo individual de la hoja de FTE cuando existe, '.(int) (self::WCR_TARGET_VALUE / 1000).'K€ si no), specialists por número de presupuestos ('.(int) self::WCR_TARGET_VOLUME.'). Una semana sin presupuestos <strong>y</strong> sin actividad en Titan se considera ausencia y no es una semana evaluable: no cuenta ni a favor ni en contra, de forma que las vacaciones no penalizan. Basta '.(int) self::WCR_STREAK_MAX_AWAY_WEEKS.' semana sin actividad para considerarla ausencia.<br>'
3294            .'· Sección 6 (performance individual): las cuatro tablas se separan por rol de usuario — Asesores de clientes (izquierda, ranking y objetivo en €) y Sales specialists (derecha, ranking y objetivo en # de presupuestos). El objetivo semanal en € es el individual de la hoja de FTE cuando existe, y '.(int) (self::WCR_TARGET_VALUE / 1000).'K€ en caso contrario; el de volumen son '.(int) self::WCR_TARGET_VOLUME.' presupuestos. Los Top 3 solo incluyen a quien ha emitido esta semana; las tablas de bajo objetivo incluyen a todo el roster, también a quien no ha emitido nada.'
3295            .'</div>';
3296
3297        $html .= '</body></html>';
3298
3299        return $html;
3300    }
3301
3302    /**
3303     * Section heading: uppercase label with a light underline (matches the mockup).
3304     * $icon is an email-safe Unicode emoji (SVG can't render in Outlook/Gmail).
3305     */
3306    private function wcr_section(string $title, string $icon = ''): string
3307    {
3308        $iconHtml = $icon !== '' ? '<span style="font-size:14px;">'.$icon.'</span>&nbsp;&nbsp;' : '';
3309
3310        return '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0" style="margin-bottom:12px;"><tr>'
3311            .'<td style="font-family:\'Segoe UI\',system-ui,Arial,sans-serif;font-size:13px;font-weight:600;color:#444441;'
3312            .'text-transform:uppercase;letter-spacing:0.06em;padding-bottom:8px;border-bottom:1px solid #d3d1c7;">'.$iconHtml.$title.'</td>'
3313            .'</tr></table>';
3314    }
3315
3316    /** Lay the individual-performance cards out two-up; .wcr-col stacks them on mobile. */
3317    private function wcr_two_col(array $cards): string
3318    {
3319        $rows = '';
3320        $n = count($cards);
3321        for ($i = 0; $i < $n; $i += 2) {
3322            $left = $cards[$i];
3323            $right = $cards[$i + 1] ?? '';
3324            $rows .= '<tr>'
3325                .'<td class="wcr-col" width="50%" valign="top" style="width:50%;vertical-align:top;padding:0 7px 14px 0;">'.$left.'</td>'
3326                .'<td class="wcr-col" width="50%" valign="top" style="width:50%;vertical-align:top;padding:0 0 14px 7px;">'.$right.'</td>'
3327                .'</tr>';
3328        }
3329
3330        return '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0" style="margin-top:6px;">'.$rows.'</table>';
3331    }
3332
3333    // ---------------------------------------------------------------------
3334    // FIRE-1448 v2 helpers — the four regional blocks (Grupo / Objetivos /
3335    // Regional YTD / Mix por tipo). Each returns a self-contained HTML
3336    // fragment built exclusively out of tables + inline styles so Outlook and
3337    // Gmail render them the same way.
3338    // ---------------------------------------------------------------------
3339
3340    /**
3341     * Map a $deltaPct closure result's sign to the v3 CSS class used on the
3342     * cell: green (pos), red (neg) or muted-italic (neutral / "nuevo" / "—").
3343     */
3344    private function wcr_delta_class(string $sign): string
3345    {
3346        return match ($sign) {
3347            'pos' => 'pos',
3348            'neg' => 'neg',
3349            default => 'nd',
3350        };
3351    }
3352
3353    /**
3354     * Section 1 — Grupo table (v3). Six metrics across three periods (Semana /
3355     * month / YTD), each period with (2026 / 2025 / Δ). Values bind to
3356     * $totals['current'] / $totals['previous']; a cell renders the v3 "n/d*"
3357     * treatment only where the underlying value is genuinely unavailable (prev
3358     * ratio with a zero denominator).
3359     */
3360    private function wcr_group_table(array $totals, int $week, string $monthLabel, $kEuro, $pctFmt, $deltaPct, $numFmt): string
3361    {
3362        $c = $totals['current'];
3363        $p = $totals['previous'];
3364
3365        // One "2026 | 2025 | Δ" triple. $prevValue === null → prev + Δ render n/d*.
3366        $triple = function ($curValue, $prevValue, $formatter) use ($deltaPct) {
3367            $cur = '<td class="block-sep">'.$formatter($curValue).'</td>';
3368            if ($prevValue === null) {
3369                return $cur.'<td class="y25 nd">n/d*</td><td class="nd">—</td>';
3370            }
3371            $d = $deltaPct($curValue, $prevValue);
3372
3373            return $cur
3374                .'<td class="y25">'.$formatter($prevValue).'</td>'
3375                .'<td class="'.$this->wcr_delta_class($d['sign']).'">'.$d['label'].'</td>';
3376        };
3377
3378        // Ratio rows: percentage-point delta instead of a ratio delta.
3379        $pctTriple = function ($curValue, $prevValue) use ($pctFmt) {
3380            if ($curValue === null) {
3381                $cur = '<td class="block-sep nd">n/d*</td>';
3382            } else {
3383                $cur = '<td class="block-sep">'.$pctFmt($curValue).'</td>';
3384            }
3385            if ($curValue === null || $prevValue === null) {
3386                return $cur.'<td class="y25 nd">n/d*</td><td class="nd">—</td>';
3387            }
3388            $delta = $curValue - $prevValue;
3389            $cls = $delta >= 0 ? 'pos' : 'neg';
3390            $prefix = $delta >= 0 ? '+' : '';
3391
3392            return $cur
3393                .'<td class="y25">'.$pctFmt($prevValue).'</td>'
3394                .'<td class="'.$cls.'">'.$prefix.number_format($delta, 1, ',', '.').' pp</td>';
3395        };
3396
3397        // Ratios (guarded against div/0).
3398        $pct = fn ($n, $d) => $d > 0 ? ($n / $d) * 100 : null;
3399        $curPctEurWeek = $pct($c['week_accepted_amount'], $c['week_amount']);
3400        $prevPctEurWeek = $pct($p['week_accepted_amount'], $p['week_amount']);
3401        $curPctEurMonth = $pct($c['month_accepted_amount'], $c['month_amount']);
3402        $prevPctEurMonth = $pct($p['month_accepted_amount'], $p['month_amount']);
3403        $curPctEurYtd = $pct($c['ytd_accepted_amount'], $c['ytd_amount']);
3404        $prevPctEurYtd = $pct($p['ytd_accepted_amount'], $p['ytd_amount']);
3405        $curPctNumWeek = $pct($c['week_accepted_budgets'], $c['week_budgets']);
3406        $prevPctNumWeek = $pct($p['week_accepted_budgets'], $p['week_budgets']);
3407        $curPctNumMonth = $pct($c['month_accepted_budgets'], $c['month_budgets']);
3408        $prevPctNumMonth = $pct($p['month_accepted_budgets'], $p['month_budgets']);
3409        $curPctNumYtd = $pct($c['ytd_accepted_budgets'], $c['ytd_budgets']);
3410        $prevPctNumYtd = $pct($p['ytd_accepted_budgets'], $p['ytd_budgets']);
3411
3412        $thead = '<thead>'
3413            .'<tr class="group-head">'
3414            .'<th></th>'
3415            .'<th colspan="3">Semana '.$week.'</th>'
3416            .'<th colspan="3">'.htmlspecialchars($monthLabel).'</th>'
3417            .'<th colspan="3">YTD (01/01 – hoy)</th>'
3418            .'</tr>'
3419            .'<tr class="col-head">'
3420            .'<th></th>'
3421            .'<th class="block-sep">2026</th><th>2025</th><th class="delta">Δ</th>'
3422            .'<th class="block-sep">2026</th><th>2025</th><th class="delta">Δ</th>'
3423            .'<th class="block-sep">2026</th><th>2025</th><th class="delta">Δ</th>'
3424            .'</tr></thead>';
3425
3426        $body = '<tbody>'
3427            .'<tr class="rowgroup"><td colspan="10">Emitido</td></tr>'
3428            .'<tr><td class="metric">€ emitidos</td>'
3429            .$triple($c['week_amount'], $p['week_amount'], $kEuro)
3430            .$triple($c['month_amount'], $p['month_amount'], $kEuro)
3431            .$triple($c['ytd_amount'], $p['ytd_amount'], $kEuro)
3432            .'</tr>'
3433            .'<tr><td class="metric"># presupuestos</td>'
3434            .$triple($c['week_budgets'], $p['week_budgets'], $numFmt)
3435            .$triple($c['month_budgets'], $p['month_budgets'], $numFmt)
3436            .$triple($c['ytd_budgets'], $p['ytd_budgets'], $numFmt)
3437            .'</tr>'
3438            .'<tr class="rowgroup"><td colspan="10">Aceptado</td></tr>'
3439            .'<tr><td class="metric">€ aceptados</td>'
3440            .$triple($c['week_accepted_amount'], $p['week_accepted_amount'], $kEuro)
3441            .$triple($c['month_accepted_amount'], $p['month_accepted_amount'], $kEuro)
3442            .$triple($c['ytd_accepted_amount'], $p['ytd_accepted_amount'], $kEuro)
3443            .'</tr>'
3444            .'<tr><td class="metric"># aceptados</td>'
3445            .$triple($c['week_accepted_budgets'], $p['week_accepted_budgets'], $numFmt)
3446            .$triple($c['month_accepted_budgets'], $p['month_accepted_budgets'], $numFmt)
3447            .$triple($c['ytd_accepted_budgets'], $p['ytd_accepted_budgets'], $numFmt)
3448            .'</tr>'
3449            .'<tr class="rowgroup"><td colspan="10">Ratio de aceptación</td></tr>'
3450            .'<tr><td class="metric">% aceptación (€)</td>'
3451            .$pctTriple($curPctEurWeek, $prevPctEurWeek)
3452            .$pctTriple($curPctEurMonth, $prevPctEurMonth)
3453            .$pctTriple($curPctEurYtd, $prevPctEurYtd)
3454            .'</tr>'
3455            .'<tr><td class="metric">% aceptación (#)</td>'
3456            .$pctTriple($curPctNumWeek, $prevPctNumWeek)
3457            .$pctTriple($curPctNumMonth, $prevPctNumMonth)
3458            .$pctTriple($curPctNumYtd, $prevPctNumYtd)
3459            .'</tr>'
3460            .'</tbody>';
3461
3462        return '<table>'.$thead.$body.'</table>';
3463    }
3464
3465    /**
3466     * Section 2 — Objetivos strip (v3). Two .obj-card cards (Emitido / Aceptado)
3467     * with a YTD-vs-Obj-YTD headline, a .bar/.fill progress bar and the annual
3468     * target as the footer. Bar colour = compliance level (v3 rule: red <70%,
3469     * amber 70–90%, green ≥90%). Bound to:
3470     *   Emitido : current.ytd_amount / ytd_target / annual_target
3471     *   Aceptado: current.ytd_accepted_amount / ytd_target_acc / annual_target_acc
3472     */
3473    private function wcr_obj_strip(array $totals, $kEuro, $pctFmt): string
3474    {
3475        $ytdEm = (float) ($totals['current']['ytd_amount'] ?? 0);
3476        $ytdTargetEm = (float) ($totals['ytd_target'] ?? 0);
3477        $annualEm = (float) ($totals['annual_target'] ?? 0);
3478        $ytdAc = (float) ($totals['current']['ytd_accepted_amount'] ?? 0);
3479        $ytdTargetAc = (float) ($totals['ytd_target_acc'] ?? 0);
3480        $annualAc = (float) ($totals['annual_target_acc'] ?? 0);
3481
3482        $card = function (string $label, float $ytdVal, float $ytdTarget, float $annualTarget) use ($kEuro) {
3483            if ($ytdTarget > 0) {
3484                $pctVal = ($ytdVal / $ytdTarget) * 100;
3485                $fillPct = max(0, min(100, (int) round($pctVal)));
3486                // v3 compliance thresholds: red <70%, amber 70–90%, green ≥90%.
3487                [$barColor, $footColor] = $pctVal >= 90
3488                    ? ['#1a8a4a', '#1a8a4a']
3489                    : ($pctVal >= 70 ? ['#d9a02b', '#b3831f'] : ['#d9482b', '#d9482b']);
3490                $diffPct = (($ytdVal - $ytdTarget) / $ytdTarget) * 100;
3491                $footLabel = $fillPct.'% del obj. YTD ('.($diffPct >= 0 ? '+' : '').number_format($diffPct, 1, ',', '.').'%)';
3492                $bigSub = 'de '.$kEuro($ytdTarget).' obj. YTD';
3493                $fill = '<div class="bar"><div class="fill" style="width:'.$fillPct.'%;background:'.$barColor.';"></div></div>';
3494                $foot = '<div class="foot"><span style="color:'.$footColor.';font-weight:700;">'.$footLabel.'</span>'
3495                    .'<span>'.($annualTarget > 0 ? 'Obj. anual: '.$kEuro($annualTarget) : 'Obj. anual: <span class="nd">n/d*</span>').'</span></div>';
3496            } else {
3497                $bigSub = 'obj. YTD <span class="nd">n/d*</span>';
3498                $fill = '<div class="bar"></div>';
3499                $foot = '<div class="foot"><span class="nd">n/d*</span>'
3500                    .'<span>'.($annualTarget > 0 ? 'Obj. anual: '.$kEuro($annualTarget) : 'Obj. anual: <span class="nd">n/d*</span>').'</span></div>';
3501            }
3502
3503            return '<div class="obj-card">'
3504                .'<div class="label">'.$label.'</div>'
3505                .'<div class="big">'.$kEuro($ytdVal).' <span>'.$bigSub.'</span></div>'
3506                .$fill.$foot
3507                .'</div>';
3508        };
3509
3510        return '<div class="obj-strip">'
3511            .$card('Emitido vs objetivo', $ytdEm, $ytdTargetEm, $annualEm)
3512            .$card('Aceptado vs objetivo', $ytdAc, $ytdTargetAc, $annualAc)
3513            .'</div>';
3514    }
3515
3516    /**
3517     * Section 3 — Regional (v3). One merged table: weekly productivity + YTD
3518     * (emitido & aceptado, € and #, vs 2025 and vs objetivo) + acceptance ratio.
3519     * Rows ordered by emitido YTD (via $sortedRegions), Total last. Bindings per
3520     * region/total row:
3521     *   Semana         : week_budgets, week_amount, Δ vs prev week, week_accepted_amount
3522     *   Emitido YTD    : ytd_amount, prev ytd_amount, Δ, ytd_budgets, prev ytd_budgets,
3523     *                    Δ, vs Obj. (ytd_amount vs ytd_target)
3524     *   Aceptado YTD   : ytd_accepted_amount, prev ytd_accepted_amount, ytd_accepted_budgets,
3525     *                    vs Obj. (ytd_accepted_amount vs ytd_target_acc)
3526     *   % aceptación   : € (acc/emit), # (acc#/emit#), Ref. 25 €† (full-year 2025
3527     *                    by amount, per region — not produced → n/d*; Total uses
3528     *                    prior YTD € ratio)
3529     */
3530    private function wcr_regional_table(array $sortedRegions, array $totals, int $week, $kEuro, $kNum, $numFmt, $deltaPct): string
3531    {
3532        $deltaTd = function ($cur, $prev) use ($deltaPct) {
3533            $d = $deltaPct($cur, $prev);
3534
3535            return '<td class="'.$this->wcr_delta_class($d['sign']).'">'.$d['label'].'</td>';
3536        };
3537        $objTd = function ($cur, $target) use ($deltaPct) {
3538            if ($target <= 0) {
3539                return '<td class="nd">n/d*</td>';
3540            }
3541            $d = $deltaPct($cur, $target);
3542
3543            return '<td class="'.$this->wcr_delta_class($d['sign']).'">'.$d['label'].'</td>';
3544        };
3545
3546        $renderRow = function (array $row, bool $isTotal) use ($kEuro, $kNum, $numFmt, $deltaTd, $objTd) {
3547            $c = $row['current'];
3548            $p = $row['previous'];
3549            $curYtd = (float) $c['ytd_amount'];
3550            $prevYtd = (float) $p['ytd_amount'];
3551            $ytdTarget = (float) ($row['ytd_target'] ?? 0);
3552            $curAcc = (float) $c['ytd_accepted_amount'];
3553            $prevAcc = (float) $p['ytd_accepted_amount'];
3554            $ytdTargetAcc = (float) ($row['ytd_target_acc'] ?? 0);
3555            $curNum = (int) $c['ytd_budgets'];
3556            $prevNum = (int) $p['ytd_budgets'];
3557            $curAccNum = (int) $c['ytd_accepted_budgets'];
3558
3559            $pctE = $curYtd > 0 ? ($curAcc / $curYtd) * 100 : null;
3560            $pctN = $curNum > 0 ? ($curAccNum / $curNum) * 100 : null;
3561            // Ref. 25 = full-year 2025 acceptance ratio by region. Per-region
3562            // values are provided manually by Duarte (WCR_ACCEPTANCE_REF_2025);
3563            // 'nuevo' marks regions opened in 2026 (no 2025 reference). The
3564            // Total row still surfaces the prior-YTD € acceptance ratio the
3565            // group data produces.
3566            if ($isTotal) {
3567                $ref25 = $prevYtd > 0
3568                    ? '<td class="y25">'.number_format(($prevAcc / $prevYtd) * 100, 1, ',', '.').'%</td>'
3569                    : '<td class="nd">n/d*</td>';
3570            } else {
3571                $ref = self::WCR_ACCEPTANCE_REF_2025[$row['region'] ?? $row['label'] ?? ''] ?? null;
3572                if ($ref === null) {
3573                    $ref25 = '<td class="nd">n/d*</td>';
3574                } elseif ($ref === 'nuevo') {
3575                    $ref25 = '<td class="y25">nuevo</td>';
3576                } else {
3577                    $ref25 = '<td class="y25">'.number_format((float) $ref, 0, ',', '.').'%</td>';
3578                }
3579            }
3580
3581            $tr = $isTotal ? '<tr class="total-row">' : '<tr>';
3582            $label = $isTotal ? 'Total' : (string) $row['label'];
3583
3584            return $tr
3585                .'<td class="name">'.htmlspecialchars($label).'</td>'
3586                // Semana
3587                .'<td class="block-sep">'.$numFmt($c['week_budgets']).'</td>'
3588                .'<td>'.$kEuro($c['week_amount']).'</td>'
3589                .$deltaTd($c['week_amount'], $p['week_amount'])
3590                .'<td>'.$kEuro($c['week_accepted_amount']).'</td>'
3591                // Emitido YTD
3592                .'<td class="block-sep">'.$kNum($curYtd).'</td>'
3593                .'<td class="y25">'.$kNum($prevYtd).'</td>'
3594                .$deltaTd($curYtd, $prevYtd)
3595                .'<td>'.$numFmt($curNum).'</td>'
3596                .'<td class="y25">'.$numFmt($prevNum).'</td>'
3597                .$deltaTd($curNum, $prevNum)
3598                .$objTd($curYtd, $ytdTarget)
3599                // Aceptado YTD
3600                .'<td class="block-sep">'.$kNum($curAcc).'</td>'
3601                .($prevAcc > 0 ? '<td class="y25">'.$kNum($prevAcc).'</td>' : '<td class="y25 nd">n/d*</td>')
3602                .'<td>'.$numFmt($curAccNum).'</td>'
3603                .$objTd($curAcc, $ytdTargetAcc)
3604                // % aceptación YTD
3605                .'<td class="block-sep">'.($pctE !== null ? number_format($pctE, 0, ',', '.').'%' : '<span class="nd">n/d*</span>').'</td>'
3606                .'<td>'.($pctN !== null ? number_format($pctN, 1, ',', '.').'%' : '<span class="nd">n/d*</span>').'</td>'
3607                .$ref25
3608                .'</tr>';
3609        };
3610
3611        $thead = '<thead>'
3612            .'<tr class="group-head">'
3613            .'<th></th>'
3614            .'<th colspan="4">Semana '.$week.'</th>'
3615            .'<th colspan="7">Emitido YTD</th>'
3616            .'<th colspan="4">Aceptado YTD</th>'
3617            .'<th colspan="3">% aceptación YTD</th>'
3618            .'</tr>'
3619            .'<tr class="col-head">'
3620            .'<th>Región</th>'
3621            .'<th class="block-sep">#</th><th>K€ emit.</th><th class="delta">vs s.'.$week.'-25</th><th>K€ acept.</th>'
3622            .'<th class="block-sep">K€</th><th>2025</th><th class="delta">vs 25</th><th>#</th><th>2025</th><th class="delta">vs 25</th><th class="delta">vs Obj.</th>'
3623            .'<th class="block-sep">K€</th><th>2025</th><th>#</th><th class="delta">vs Obj.</th>'
3624            .'<th class="block-sep">€</th><th>#</th><th>Ref. 25 €†</th>'
3625            .'</tr></thead>';
3626
3627        $body = '<tbody>';
3628        foreach ($sortedRegions as $r) {
3629            $body .= $renderRow($r, false);
3630        }
3631        $totalRowData = [
3632            'label' => 'Total',
3633            'current' => $totals['current'],
3634            'previous' => $totals['previous'],
3635            'ytd_target' => $totals['ytd_target'] ?? 0,
3636            'ytd_target_acc' => $totals['ytd_target_acc'] ?? 0,
3637        ];
3638        $body .= $renderRow($totalRowData, true).'</tbody>';
3639
3640        return '<table class="regional">'.$thead.$body.'</table>';
3641    }
3642
3643    /**
3644     * Section 4 — Mix por tipo (v3). A single compact table: one emitido row per
3645     * region + "Total emitido 2026" + "Total emitido 2025" + a "Total aceptado
3646     * 2026" row. Four type groups (Preventivo / Correctivo / Instalación /
3647     * Otros; Otros = groups 6/7/8), each with Sem. K€ / YTD K€ / % mix € / % mix #.
3648     * % mix € = ytd_TYPE_amount / row total YTD €. % mix # = ytd_TYPE_count /
3649     * row total YTD budget count (# presupuestos del tipo / # emitidos —o
3650     * aceptados— YTD).
3651     */
3652    private function wcr_mix_table(array $sortedRegions, array $totals, $kNum): string
3653    {
3654        // label => field suffix (shared by emitido/aceptado via the prefixes below)
3655        $types = [
3656            'Preventivo' => 'preventive',
3657            'Correctivo' => 'corrective',
3658            'Instalación' => 'installation',
3659            'Otros' => 'otros',
3660        ];
3661
3662        $mixRow = function (string $label, string $trClass, array $data, string $weekPrefix, string $ytdPrefix, string $totalField, string $countTotalField, bool $mixMuted) use ($kNum, $types) {
3663            $rowTotal = (float) ($data[$totalField] ?? 0);
3664            $rowCountTotal = (int) ($data[$countTotalField] ?? 0);
3665            $cells = '<td class="name">'.htmlspecialchars($label).'</td>';
3666            foreach ($types as $suffix) {
3667                $weekVal = (float) ($data[$weekPrefix.$suffix] ?? 0);
3668                $ytdVal = (float) ($data[$ytdPrefix.$suffix] ?? 0);
3669                $ytdCount = (int) ($data[$ytdPrefix.$suffix.'_n'] ?? 0);
3670                $mix = $rowTotal > 0 ? number_format(($ytdVal / $rowTotal) * 100, 0, ',', '.').'%' : '0%';
3671                // % mix # = # presupuestos YTD del tipo / # emitidos (o aceptados) YTD.
3672                $mixNum = $rowCountTotal > 0 ? number_format(($ytdCount / $rowCountTotal) * 100, 0, ',', '.').'%' : '0%';
3673                $mixCell = $mixMuted ? '<td class="y25">'.$mix.'</td>' : '<td>'.$mix.'</td>';
3674                $mixNumCell = $mixMuted ? '<td class="y25">'.$mixNum.'</td>' : '<td>'.$mixNum.'</td>';
3675                $cells .= '<td class="block-sep">'.$kNum($weekVal).'</td>'
3676                    .'<td>'.$kNum($ytdVal).'</td>'
3677                    .$mixCell
3678                    .$mixNumCell;
3679            }
3680            $tr = $trClass !== '' ? '<tr class="'.$trClass.'">' : '<tr>';
3681
3682            return $tr.$cells.'</tr>';
3683        };
3684
3685        $thead = '<thead><tr class="group-head"><th></th>';
3686        foreach ($types as $name => $_) {
3687            $thead .= '<th colspan="4">'.$name.'</th>';
3688        }
3689        $thead .= '</tr><tr class="col-head"><th>Región</th>';
3690        foreach ($types as $_) {
3691            $thead .= '<th class="block-sep">Sem. K€</th><th>YTD K€</th><th>% mix €</th><th>% mix #</th>';
3692        }
3693        $thead .= '</tr></thead>';
3694
3695        $body = '<tbody>';
3696        $body .= '<tr class="rowgroup"><td colspan="17">Emitido — por región y total</td></tr>';
3697        foreach ($sortedRegions as $r) {
3698            $body .= $mixRow((string) $r['label'], '', $r['current'], 'week_', 'ytd_', 'ytd_amount', 'ytd_budgets', true);
3699        }
3700        $body .= $mixRow('Total emitido 2026', 'total-row', $totals['current'], 'week_', 'ytd_', 'ytd_amount', 'ytd_budgets', true);
3701        $body .= $mixRow('Total emitido 2025', 'row-25', $totals['previous'], 'week_', 'ytd_', 'ytd_amount', 'ytd_budgets', false);
3702        $body .= '<tr class="rowgroup"><td colspan="17">Aceptado</td></tr>';
3703        $body .= $mixRow('Total aceptado 2026', 'row-acc', $totals['current'], 'week_accepted_', 'ytd_accepted_', 'ytd_accepted_amount', 'ytd_accepted_budgets', false);
3704        $body .= '</tbody>';
3705
3706        return '<table class="regional">'.$thead.$body.'</table>';
3707    }
3708
3709    /**
3710     * Card shell (mockup .card): a bordered, rounded block with an uppercase title and
3711     * optional sub-label, built as a table so it survives Outlook. $danger tints the
3712     * title red.
3713     */
3714    private function wcr_card_shell(string $title, bool $danger, string $bodyRows, string $sublabel = '', string $icon = ''): string
3715    {
3716        $font = "'Segoe UI',system-ui,Arial,sans-serif";
3717        $titleColor = $danger ? '#993C1D' : '#888780';
3718        $iconHtml = $icon !== '' ? '<span style="font-size:12px;">'.$icon.'</span>&nbsp;&nbsp;' : '';
3719        $sub = $sublabel !== ''
3720            ? '<div style="font-family:'.$font.';font-size:11px;color:#888780;margin:-4px 0 8px;">'.$sublabel.'</div>'
3721            : '';
3722
3723        return '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0" style="border:1px solid #d3d1c7;border-radius:10px;background-color:#ffffff;">'
3724            .'<tr><td style="padding:14px 16px;">'
3725            .'<div style="font-family:'.$font.';font-size:10px;font-weight:600;color:'.$titleColor.';text-transform:uppercase;letter-spacing:0.06em;margin-bottom:10px;">'.$iconHtml.$title.'</div>'
3726            .$sub
3727            .'<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0">'.$bodyRows.'</table>'
3728            .'</td></tr></table>';
3729    }
3730
3731    /**
3732     * Top-3 card: ranked rows with name, region, total €, # budgets and a
3733     * Preventive/Corrective/Installation € pill breakdown (largest first). Each row is
3734     * a nested table (rank + name block on the left, numbers on the right).
3735     */
3736    private function wcr_top_card(string $title, array $rows, $money): string
3737    {
3738        $font = "'Segoe UI',system-ui,Arial,sans-serif";
3739
3740        if (empty($rows)) {
3741            $body = '<tr><td style="font-family:'.$font.';font-size:12px;color:#888780;font-style:italic;padding:8px 0;">Sin datos esta semana</td></tr>';
3742
3743            return $this->wcr_card_shell($title, false, $body, '', '🏆');
3744        }
3745
3746        $rankColors = ['#BA7517', '#5f5e5a', '#7B3F20']; // gold / silver / bronze
3747        $body = '';
3748        $i = 0;
3749        foreach ($rows as $r) {
3750            $rankColor = $rankColors[$i] ?? '#888780';
3751            $region = htmlspecialchars($this->wcr_region_label($r['region'] ?? ''));
3752            // One shared table per card (cells below) keeps the rank / name / numbers
3753            // columns aligned across every row.
3754            $cell = 'font-family:'.$font.';vertical-align:top;padding:9px 0;'.($i > 0 ? 'border-top:1px solid #e8e6df;' : '');
3755            $body .= '<tr>'
3756                .'<td width="24" style="'.$cell.'font-size:17px;font-weight:600;color:'.$rankColor.';text-align:center;">'.($i + 1).'</td>'
3757                .'<td style="'.$cell.'padding-left:8px;">'
3758                .'<div style="font-size:13px;font-weight:600;color:#1a1a18;">'.htmlspecialchars((string) $r['name']).'</div>'
3759                .'<div style="font-size:11px;color:#888780;margin-top:1px;">'.$region.'</div></td>'
3760                .'<td align="right" style="'.$cell.'text-align:right;white-space:nowrap;padding-left:8px;">'
3761                .'<div style="font-size:14px;font-weight:600;color:#1a1a18;">'.$money($r['total_amount']).'</div>'
3762                .'<div style="font-size:11px;color:#888780;margin-top:1px;">'.number_format($r['n_budgets'], 0, ',', '.').' presupuestos</div>'
3763                .$this->wcr_pills($r, $money)
3764                .'</td>'
3765                // Racha — last column, per Duarte's review.
3766                .'<td align="right" width="58" style="'.$cell.'text-align:right;white-space:nowrap;padding-left:8px;">'
3767                .$this->wcr_streak_badge($r['weeks_met'] ?? null, 'met').'</td></tr>';
3768            $i++;
3769        }
3770
3771        return $this->wcr_card_shell($title, false, $body, '', '🏆');
3772    }
3773
3774    /**
3775     * The year-by-week charts section: four trend images, each followed by a
3776     * plain-HTML figure strip and any week notes.
3777     *
3778     * The strip is not decoration. It is the accessible/degraded path — a client
3779     * with remote images disabled shows nothing for the PNG, and without the
3780     * strip the whole section would silently vanish. It also gives the exact
3781     * per-week numbers the image can only approximate at 17px per column.
3782     *
3783     * @param  array  $series  output of get_weekly_year_series()
3784     * @param  array  $imgSrc  series key => value for the <img src> attribute
3785     */
3786    private function wcr_year_charts_section(array $series, array $imgSrc, $kNum, $numFmt): string
3787    {
3788        $font = "'Segoe UI',system-ui,Arial,sans-serif";
3789        $html = '';
3790
3791        foreach ($series as $key => $s) {
3792            $isEur = $s['kind'] === 'eur';
3793            $fmt = fn ($v) => $isEur ? $kNum($v).'K' : $numFmt($v);
3794
3795            $html .= '<div style="margin:0 0 30px 0;">';
3796
3797            // ---- title, outside the image --------------------------------
3798            // Kept in HTML rather than baked into the PNG so it stays selectable,
3799            // scales with the reader's font size, and survives blocked images.
3800            $html .= '<div style="font-family:'.$font.';font-size:15px;font-weight:700;color:#1a1a18;margin:0 0 10px 0;">'
3801                .htmlspecialchars($s['title']).'</div>';
3802
3803            // ---- the chart image -----------------------------------------
3804            if (! empty($imgSrc[$key])) {
3805                $alt = htmlspecialchars($s['title']);
3806                $html .= '<img src="'.$imgSrc[$key].'" alt="'.$alt.'" width="1160" '
3807                    .'style="display:block;width:100%;max-width:1160px;height:auto;" />';
3808            }
3809
3810            // ---- per-week figure strip -----------------------------------
3811            $chips = '';
3812            foreach ($s['points'] as $p) {
3813                $val = $fmt($p['value']);
3814
3815                if (! empty($p['in_progress'])) {
3816                    $chips .= '<span style="display:inline-block;font-family:'.$font.';font-size:10.5px;color:#6b6a67;'
3817                        .'background-color:#f2f0ec;border-radius:3px;padding:1px 5px;margin:0 4px 4px 0;white-space:nowrap;">'
3818                        .'<strong>'.$p['week'].'</strong> '.$val.' · en curso</span>';
3819
3820                    continue;
3821                }
3822
3823                $pctTxt = '';
3824                if ($p['pct'] !== null) {
3825                    $pct = (float) $p['pct'];
3826                    $col = $pct >= 0 ? '#1a8a4a' : '#d9482b';
3827                    $pctTxt = ' <span style="color:'.$col.';font-weight:600;">'
3828                        .($pct >= 0 ? '+' : '−').number_format(abs($pct), 0, ',', '.').'%</span>';
3829                }
3830
3831                $chips .= '<span style="display:inline-block;font-family:'.$font.';font-size:10.5px;color:#6b6a67;'
3832                    .'margin:0 8px 4px 0;white-space:nowrap;"><strong>'.$p['week'].'</strong> '.$val.$pctTxt.'</span>';
3833            }
3834
3835            $html .= '<div style="margin-top:8px;line-height:1.7;">'.$chips.'</div>';
3836
3837            // ---- week notes ----------------------------------------------
3838            if (! empty($s['notes'])) {
3839                $noteHtml = '';
3840                foreach ($s['notes'] as $nt) {
3841                    $noteHtml .= '<div style="font-family:'.$font.';font-size:11px;color:#a53a2a;margin-top:3px;">'
3842                        .'<strong>S'.$nt['week'].'</strong> — '.htmlspecialchars($nt['note']).'</div>';
3843                }
3844                $html .= '<div style="margin-top:6px;padding:8px 10px;background-color:#fdf1ee;border-left:3px solid #a53a2a;border-radius:0 4px 4px 0;">'
3845                    .$noteHtml.'</div>';
3846            }
3847
3848            $html .= '</div>';
3849        }
3850
3851        return $html;
3852    }
3853
3854    /**
3855     * Weeks-count badge — the last column of each performance table.
3856     *
3857     * Counts weeks across the year (not a consecutive run), so the label is
3858     * deliberately "N sem." and never "racha".
3859     *
3860     * Polarity matters: the Top-3 cards are positive tables and report weeks MET
3861     * (green), while the bajo-objetivo cards are negative tables and must report
3862     * weeks NOT met (red). Showing "cumpliendo objetivo" on a below-objective
3863     * table read as a contradiction.
3864     *
3865     * `null` prints a dash — no eligible week of that kind yet, which is not the
3866     * same as zero.
3867     *
3868     * @param  string  $polarity  'met' or 'missed'
3869     */
3870    private function wcr_streak_badge($weeks, string $polarity = 'met'): string
3871    {
3872        $font = "'Segoe UI',system-ui,Arial,sans-serif";
3873        $missed = $polarity === 'missed';
3874
3875        if ($weeks === null || (int) $weeks < 1) {
3876            $emptyTitle = $missed
3877                ? 'Ninguna semana por debajo de objetivo todavía'
3878                : 'Ninguna semana cumpliendo objetivo todavía';
3879
3880            return '<span title="'.$emptyTitle.'" style="font-family:'.$font.';font-size:12px;color:#a8a6a0;">—</span>';
3881        }
3882
3883        $weeks = (int) $weeks;
3884
3885        if ($missed) {
3886            // No icon on the negative side — the card is already a danger card and
3887            // the red pill carries the meaning on its own.
3888            $title = $weeks.' semanas sin cumplir el objetivo en lo que va de año';
3889            [$bg, $fg, $label] = ['#FAECE7', '#993C1D', (string) $weeks];
3890        } else {
3891            $title = $weeks.' semanas cumpliendo el objetivo en lo que va de año';
3892            [$bg, $fg, $label] = ['#E1F5EE', '#0F6E56', '&#128293; '.$weeks];
3893        }
3894
3895        return '<span title="'.$title.'" '
3896            .'style="display:inline-block;font-family:'.$font.';font-size:10px;font-weight:600;'
3897            .'padding:2px 6px;border-radius:3px;background-color:'.$bg.';color:'.$fg.';white-space:nowrap;">'
3898            .$label.'</span>';
3899    }
3900
3901    private function wcr_pills(array $r, $money): string
3902    {
3903        $parts = [
3904            ['k' => 'installation', 'l' => 'Inst.', 'bg' => '#E6F1FB', 'c' => '#185FA5'],
3905            ['k' => 'corrective', 'l' => 'Cor.', 'bg' => '#EEEDFE', 'c' => '#534AB7'],
3906            ['k' => 'preventive', 'l' => 'Prev.', 'bg' => '#E1F5EE', 'c' => '#0F6E56'],
3907        ];
3908        usort($parts, fn ($a, $b) => ($r[$b['k']] ?? 0) <=> ($r[$a['k']] ?? 0));
3909
3910        $out = '';
3911        foreach ($parts as $p) {
3912            $v = (float) ($r[$p['k']] ?? 0);
3913            if ($v <= 0) {
3914                continue;
3915            }
3916            $out .= '<span style="display:inline-block;font-family:\'Segoe UI\',system-ui,Arial,sans-serif;font-size:10px;font-weight:600;'
3917                .'padding:2px 5px;margin:4px 0 0 4px;border-radius:3px;background-color:'.$p['bg'].';color:'.$p['c'].';">'.$p['l'].' '.$money($v).'</span>';
3918        }
3919
3920        if ($out === '') {
3921            return '';
3922        }
3923
3924        return '<div style="margin-top:2px;">'.$out.'</div>';
3925    }
3926
3927    /**
3928     * Below-target card (mockup .card.danger): name, €, # budgets and a red badge with
3929     * the % gap vs the weekly objective (value <30K€ or volume <50 budgets). Each row
3930     * is a nested table.
3931     */
3932    private function wcr_under_card(string $title, array $rows, string $mode, $money): string
3933    {
3934        $font = "'Segoe UI',system-ui,Arial,sans-serif";
3935        $target = $mode === 'value' ? self::WCR_TARGET_VALUE : self::WCR_TARGET_VOLUME;
3936
3937        if (empty($rows)) {
3938            $body = '<tr><td style="font-family:'.$font.';font-size:12px;color:#888780;font-style:italic;padding:8px 0;">Sin datos</td></tr>';
3939
3940            return $this->wcr_card_shell($title, true, $body, 'Presupuestos creados en la semana', '⚠️');
3941        }
3942
3943        $body = '';
3944        $i = 0;
3945        foreach ($rows as $r) {
3946            $metric = $mode === 'value' ? (float) $r['total_amount'] : (float) $r['n_budgets'];
3947            // Use the person's per-week € objective when present (value mode
3948            // only); the constant $target stays the fallback and covers volume
3949            // mode, which has no per-# target in the FTE sheet.
3950            $rowTarget = ($mode === 'value' && isset($r['target_value']) && $r['target_value'] !== null)
3951                ? (float) $r['target_value']
3952                : $target;
3953            $gap = $rowTarget > 0 ? (($metric - $rowTarget) / $rowTarget) * 100 : 0;
3954            // Fixed widths on the value / count / badge columns keep them aligned in a
3955            // straight vertical line across every row.
3956            $cell = 'font-family:'.$font.';vertical-align:middle;padding:8px 0;'.($i > 0 ? 'border-top:1px solid #e8e6df;' : '');
3957            $body .= '<tr>'
3958                .'<td style="'.$cell.'font-size:13px;font-weight:600;color:#1a1a18;">'.htmlspecialchars((string) $r['name']).'</td>'
3959                .'<td align="right" width="80" style="'.$cell.'font-size:13px;color:#1a1a18;white-space:nowrap;text-align:right;padding-left:8px;">'.$money($r['total_amount']).'</td>'
3960                .'<td align="right" width="62" style="'.$cell.'font-size:12px;color:#888780;white-space:nowrap;text-align:right;padding-left:8px;">'.number_format($r['n_budgets'], 0, ',', '.').' pres.</td>'
3961                .'<td align="right" width="48" style="'.$cell.'white-space:nowrap;text-align:right;padding-left:8px;"><span style="display:inline-block;font-family:'.$font.';font-size:10px;font-weight:600;padding:2px 7px;border-radius:4px;background-color:#FAECE7;color:#993C1D;">'.number_format($gap, 0, ',', '.').'%</span></td>'
3962                // Racha — last column, per Duarte's review.
3963                .'<td align="right" width="58" style="'.$cell.'white-space:nowrap;text-align:right;padding-left:8px;">'
3964                // Negative table: weeks NOT meeting objective.
3965                .$this->wcr_streak_badge($r['weeks_missed'] ?? null, 'missed').'</td>'
3966                .'</tr>';
3967            $i++;
3968        }
3969
3970        return $this->wcr_card_shell($title, true, $body, 'Presupuestos creados en la semana', '⚠️');
3971    }
3972
3973    private function wcr_region_label(?string $region): string
3974    {
3975        $region = (string) $region;
3976        $map = ['Comunidad Valenciana' => 'Valencia'];
3977
3978        return $map[$region] ?? $region;
3979    }
3980
3981    public function get_cc_bcc_executive(): ResponseFactory|Response
3982    {
3983
3984        try {
3985
3986            $result = TblCcBccExecutiveNotifications::get();
3987
3988            return response(['message' => 'OK', 'data' => $result]);
3989
3990        } catch (\Exception $e) {
3991            report(AppException::fromException($e, 'GET_CC_BCC_EXECUTIVE_EXCEPTION'));
3992
3993            return response(['message' => 'KO', 'error' => $e->getMessage()]);
3994        }
3995    }
3996
3997    public function delete_cc_bcc_executive($id): ResponseFactory|Response
3998    {
3999
4000        try {
4001
4002            $id = addslashes((string) $id);
4003
4004            TblCcBccExecutiveNotifications::where('id', $id)->delete();
4005
4006            return response(['message' => 'OK']);
4007
4008        } catch (\Exception $e) {
4009            report(AppException::fromException($e, 'DELETE_CC_BCC_EXECUTIVE_EXCEPTION'));
4010
4011            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4012        }
4013    }
4014
4015    public function update_cc_bcc_executive(Request $request, $id): ResponseFactory|Response
4016    {
4017
4018        try {
4019
4020            $data = $request->all();
4021            $id = addslashes((string) $id);
4022
4023            $x = $this->isEmailValid($data['email']);
4024
4025            if ($x) {
4026                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
4027
4028                if ($result > 0) {
4029                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
4030                } else {
4031                    $data['updated_at'] = date('Y-m-d H:i:s');
4032                    TblCcBccExecutiveNotifications::where('id', $id)->update($data);
4033                }
4034            } else {
4035                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
4036            }
4037
4038            return response(['message' => 'OK']);
4039
4040        } catch (\Exception $e) {
4041            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXECUTIVE_EXCEPTION'));
4042
4043            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4044        }
4045    }
4046
4047    public function create_cc_bcc_executive(Request $request): ResponseFactory|Response
4048    {
4049
4050        try {
4051
4052            $data = $request->all();
4053
4054            $x = $this->isEmailValid($data['email']);
4055
4056            if ($x) {
4057                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
4058
4059                if ($result > 0) {
4060                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
4061                } else {
4062                    TblCcBccExecutiveNotifications::create($data);
4063                }
4064            } else {
4065                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
4066            }
4067
4068            return response(['message' => 'OK']);
4069
4070        } catch (\Exception $e) {
4071            report(AppException::fromException($e, 'CREATE_CC_BCC_EXECUTIVE_EXCEPTION'));
4072
4073            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4074        }
4075    }
4076
4077    public function update_notification_settings(Request $request): ResponseFactory|Response
4078    {
4079
4080        try {
4081
4082            $data = $request->all();
4083
4084            $data['updated_at'] = date('Y-m-d H:i:s');
4085            TblNotificationSettings::where('id', 1)->update($data);
4086
4087            return response(['message' => 'OK']);
4088
4089        } catch (\Exception $e) {
4090            report(AppException::fromException($e, 'UPDATE_NOTIFICATION_SETTINGS_EXCEPTION'));
4091
4092            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4093        }
4094
4095    }
4096
4097    public function get_notification_settings(): ResponseFactory|Response
4098    {
4099
4100        try {
4101
4102            $result = TblNotificationSettings::where('id', 1)->first();
4103
4104            return response(['message' => 'OK', 'data' => $result]);
4105
4106        } catch (\Exception $e) {
4107            report(AppException::fromException($e, 'GET_NOTIFICATION_SETTINGS_EXCEPTION'));
4108
4109            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4110        }
4111    }
4112
4113    public function send_report_logs(Request $request): ResponseFactory|Response
4114    {
4115
4116        try {
4117
4118            $data = $request->all();
4119
4120            $toEmail = config('services.sendgrid.to_email');
4121
4122            $imgpath = file_get_contents(public_path('fireservicetitan.png'));
4123
4124            $body = $data['logs'];
4125
4126            $body .= '<br><br><p>Fire Service Titan</p>';
4127            $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
4128
4129            $html = '<!DOCTYPE html>';
4130            $html .= '<html>';
4131            $html .= '<head>';
4132            $html .= '<meta charset="UTF-8">';
4133            $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
4134            $html .= '</head>';
4135            $html .= '<body>';
4136            $html .= $body;
4137            $html .= '</body>';
4138            $html .= '</html>';
4139
4140            $email = new Mail;
4141            $subject = 'FST Error report '.date('Y-m-d H:i:s');
4142
4143            $email->setFrom('fire@fire.es', 'Fire Service Titan');
4144            $email->setSubject($subject);
4145
4146            $email->addTo($toEmail);
4147            $email->addContent('text/html', $html);
4148
4149            $email->addAttachment(
4150                $imgpath,
4151                'image/png',
4152                'fireservicetitan.png',
4153                'inline',
4154                'fireservicetitan'
4155            );
4156
4157            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
4158
4159            try {
4160                $response = $sendgrid->send($email);
4161                SendgridLogger::log($email, $response);
4162            } catch (\Throwable $sendException) {
4163                SendgridLogger::logException($email, $sendException);
4164                throw $sendException;
4165            }
4166
4167            if ($response->statusCode() == 202) {
4168                Log::channel('email_log')->info('ID:'.$toEmail.' - REPORT LOG EMAIL SENT');
4169            }
4170
4171            return response(['message' => 'OK']);
4172
4173        } catch (\Exception $e) {
4174            report(AppException::fromException($e, 'SEND_REPORT_LOGS_EXCEPTION'));
4175
4176            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4177        }
4178    }
4179
4180    public function get_emails_acceptance_notifications($companyId): ResponseFactory|Response
4181    {
4182
4183        try {
4184
4185            $companyId = addslashes((string) $companyId);
4186
4187            $cc = TblCcAcceptanceNotifications::where('company_id', $companyId)->get();
4188            $to = TblToAcceptanceNotifications::where('company_id', $companyId)->get();
4189
4190            return response(['message' => 'OK', 'cc' => $cc, 'to' => $to]);
4191
4192        } catch (\Exception $e) {
4193            report(AppException::fromException($e, 'GET_EMAILS_ACCEPTANCE_NOTIFICATIONS_EXCEPTION'));
4194
4195            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4196        }
4197    }
4198
4199    public function create_to_email_acceptance(Request $request): ResponseFactory|Response
4200    {
4201
4202        try {
4203
4204            $data = $request->all();
4205
4206            $x = $this->isEmailValid($data['email']);
4207
4208            if ($x) {
4209                $result = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
4210
4211                if ($result > 0) {
4212                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
4213                } else {
4214                    TblToAcceptanceNotifications::create($data);
4215                }
4216            } else {
4217                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
4218            }
4219
4220            return response(['message' => 'OK']);
4221
4222        } catch (\Exception $e) {
4223            report(AppException::fromException($e, 'CREATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
4224
4225            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4226        }
4227    }
4228
4229    public function delete_to_email_acceptance($id): ResponseFactory|Response
4230    {
4231
4232        try {
4233
4234            $id = addslashes((string) $id);
4235
4236            TblToAcceptanceNotifications::where('id', $id)->delete();
4237
4238            return response(['message' => 'OK']);
4239
4240        } catch (\Exception $e) {
4241            report(AppException::fromException($e, 'DELETE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
4242
4243            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4244        }
4245    }
4246
4247    public function update_to_email_acceptance(Request $request, $id): ResponseFactory|Response
4248    {
4249
4250        try {
4251
4252            $data = $request->all();
4253            $id = addslashes((string) $id);
4254
4255            $x = $this->isEmailValid($data['email']);
4256
4257            if ($x) {
4258                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
4259                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
4260
4261                if ($to > 0 || $cc > 0) {
4262                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
4263                } else {
4264                    $data['updated_at'] = date('Y-m-d H:i:s');
4265                    TblToAcceptanceNotifications::where('id', $id)->update($data);
4266                }
4267            } else {
4268                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
4269            }
4270
4271            return response(['message' => 'OK']);
4272
4273        } catch (\Exception $e) {
4274            report(AppException::fromException($e, 'UPDATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
4275
4276            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4277        }
4278    }
4279
4280    public function create_cc_email_acceptance(Request $request): ResponseFactory|Response
4281    {
4282
4283        try {
4284
4285            $data = $request->all();
4286
4287            $x = $this->isEmailValid($data['email']);
4288
4289            if ($x) {
4290                $result = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
4291
4292                if ($result > 0) {
4293                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
4294                } else {
4295                    TblCcAcceptanceNotifications::create($data);
4296                }
4297            } else {
4298                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
4299            }
4300
4301            return response(['message' => 'OK']);
4302
4303        } catch (\Exception $e) {
4304            report(AppException::fromException($e, 'CREATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
4305
4306            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4307        }
4308    }
4309
4310    public function delete_cc_email_acceptance($id): ResponseFactory|Response
4311    {
4312
4313        try {
4314
4315            $id = addslashes((string) $id);
4316
4317            TblCcAcceptanceNotifications::where('id', $id)->delete();
4318
4319            return response(['message' => 'OK']);
4320
4321        } catch (\Exception $e) {
4322            report(AppException::fromException($e, 'DELETE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
4323
4324            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4325        }
4326    }
4327
4328    public function update_cc_email_acceptance(Request $request, $id): ResponseFactory|Response
4329    {
4330
4331        try {
4332
4333            $data = $request->all();
4334            $id = addslashes((string) $id);
4335
4336            $x = $this->isEmailValid($data['email']);
4337
4338            if ($x) {
4339                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
4340                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
4341
4342                if ($cc > 0 || $to > 0) {
4343                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
4344                } else {
4345                    $data['updated_at'] = date('Y-m-d H:i:s');
4346                    TblCcAcceptanceNotifications::where('id', $id)->update($data);
4347                }
4348            } else {
4349                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
4350            }
4351
4352            return response(['message' => 'OK']);
4353
4354        } catch (\Exception $e) {
4355            report(AppException::fromException($e, 'UPDATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
4356
4357            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4358        }
4359    }
4360
4361    public function list_last_follow_up_date($companyId): ResponseFactory|Response
4362    {
4363
4364        try {
4365
4366            $companyId = addslashes((string) $companyId);
4367
4368            $result = TblLastFollowUpDate::where('company_id', $companyId)->orderBy('row_id', 'ASC')->get();
4369
4370            $data = [];
4371
4372            for ($i = 0; $i < count($result); $i++) {
4373                $data[$result[$i]->row_id]['row_id'] = $result[$i]->row_id;
4374                $data[$result[$i]->row_id]['last_follow_up_date'] = $result[$i]->last_follow_up_date;
4375                if ($result[$i]->budget_type_id != null) {
4376                    $data[$result[$i]->row_id]['budget_types'][] = $result[$i]->budget_type_id;
4377                }
4378            }
4379
4380            return response(['message' => 'OK', 'data' => array_values($data)]);
4381
4382        } catch (\Exception $e) {
4383            report(AppException::fromException($e, 'LIST_LAST_FOLLOW_UP_DATE_EXCEPTION'));
4384
4385            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4386        }
4387
4388    }
4389
4390    public function update_last_follow_up_date_working_days(Request $request)
4391    {
4392
4393        try {
4394
4395            $data = $request->all();
4396            $companyId = addslashes((string) $data['company_id']);
4397            $rowId = addslashes((string) $data['row_id']);
4398
4399            unset($data['company_id']);
4400            unset($data['row_id']);
4401
4402            $data['updated_at'] = date('Y-m-d H:i:s');
4403
4404            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update($data);
4405
4406            return $this->list_last_follow_up_date($companyId);
4407
4408        } catch (\Exception $e) {
4409            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_WORKING_DAYS_EXCEPTION'));
4410
4411            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4412        }
4413
4414    }
4415
4416    public function update_last_follow_up_date(Request $request)
4417    {
4418
4419        try {
4420
4421            $data = $request->all();
4422            $companyId = addslashes((string) $data['company_id']);
4423            $rowId = addslashes((string) $data['row_id']);
4424            unset($data['company_id']);
4425            unset($data['row_id']);
4426
4427            $previousData = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->first();
4428
4429            $lastBudgetTypeId = end($data['budget_type_id']);
4430            $lastFollowUpDate = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $lastBudgetTypeId)->first();
4431
4432            TblLastFollowUpDate::where('company_id', $companyId)->whereIn('budget_type_id', $data['budget_type_id'])->delete();
4433            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->delete();
4434
4435            foreach ($data['budget_type_id'] as $budgetTypeId) {
4436                TblLastFollowUpDate::create(
4437                    [
4438                        'company_id' => $companyId,
4439                        'row_id' => $rowId,
4440                        'budget_type_id' => $budgetTypeId,
4441                        'last_follow_up_date' => $previousData->last_follow_up_date,
4442                        'updated_by' => $data['updated_by'],
4443                        'updated_at' => date('Y-m-d H:i:s'),
4444                    ]
4445                );
4446            }
4447
4448            if ($lastFollowUpDate != null) {
4449                if ($lastFollowUpDate->budget_type_id != null) {
4450                    TblLastFollowUpDate::create(
4451                        [
4452                            'company_id' => $companyId,
4453                            'row_id' => $lastFollowUpDate->row_id,
4454                            'budget_type_id' => null,
4455                            'last_follow_up_date' => $lastFollowUpDate->last_follow_up_date,
4456                            'created_by' => $data['updated_by'],
4457                        ]
4458                    );
4459                }
4460            }
4461
4462            return $this->list_last_follow_up_date($companyId);
4463
4464        } catch (\Exception $e) {
4465            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
4466
4467            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4468        }
4469
4470    }
4471
4472    public function delete_last_follow_up_date(Request $request)
4473    {
4474
4475        try {
4476
4477            $data = $request->all();
4478            $companyId = addslashes((string) $data['company_id']);
4479            $rowId = addslashes((string) $data['row_id']);
4480            unset($data['company_id']);
4481            unset($data['row_id']);
4482
4483            $count = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->count();
4484
4485            if ($count > 1) {
4486                TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $data['budget_type_id'])->delete();
4487            } else {
4488                TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update(['budget_type_id' => null]);
4489            }
4490
4491            return $this->list_last_follow_up_date($companyId);
4492
4493        } catch (\Exception $e) {
4494            report(AppException::fromException($e, 'DELETE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
4495
4496            return response(['message' => 'KO', 'error' => $e->getMessage()]);
4497        }
4498
4499    }
4500}