Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
72.04% covered (warning)
72.04%
237 / 329
25.00% covered (danger)
25.00%
5 / 20
CRAP
0.00% covered (danger)
0.00%
0 / 1
AnalyticsRedesign
72.04% covered (warning)
72.04%
237 / 329
25.00% covered (danger)
25.00%
5 / 20
282.12
0.00% covered (danger)
0.00%
0 / 1
 __construct
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
20
 normalizeRegion
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
1
 scopeCompanyIds
88.89% covered (warning)
88.89%
8 / 9
0.00% covered (danger)
0.00%
0 / 1
5.03
 companyIdsForRegion
0.00% covered (danger)
0.00%
0 / 14
0.00% covered (danger)
0.00%
0 / 1
30
 companyInSql
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
2
 standardFilters
27.27% covered (danger)
27.27%
12 / 44
0.00% covered (danger)
0.00%
0 / 1
142.63
 excludeReplacedQuotesSql
50.00% covered (danger)
50.00%
3 / 6
0.00% covered (danger)
0.00%
0 / 1
4.12
 excludeSystemCommercialsSql
86.67% covered (warning)
86.67%
13 / 15
0.00% covered (danger)
0.00%
0 / 1
4.04
 yearWindow
69.23% covered (warning)
69.23%
9 / 13
0.00% covered (danger)
0.00%
0 / 1
8.43
 windowRange
100.00% covered (success)
100.00%
7 / 7
100.00% covered (success)
100.00%
1 / 1
3
 ok
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 ko
0.00% covered (danger)
0.00%
0 / 2
0.00% covered (danger)
0.00%
0 / 1
2
 list_productivity_region
83.33% covered (warning)
83.33%
10 / 12
0.00% covered (danger)
0.00%
0 / 1
2.02
 list_productivity_commercial
84.62% covered (warning)
84.62%
11 / 13
0.00% covered (danger)
0.00%
0 / 1
2.01
 list_mix_group
84.62% covered (warning)
84.62%
11 / 13
0.00% covered (danger)
0.00%
0 / 1
3.03
 mixForWindow
85.71% covered (warning)
85.71%
42 / 49
0.00% covered (danger)
0.00%
0 / 1
11.35
 mixDelta
100.00% covered (success)
100.00%
15 / 15
100.00% covered (success)
100.00%
1 / 1
3
 list_effectiveness
92.00% covered (success)
92.00%
23 / 25
0.00% covered (danger)
0.00%
0 / 1
7.03
 list_backlog
92.59% covered (success)
92.59%
25 / 27
0.00% covered (danger)
0.00%
0 / 1
6.01
 analytics_filters
95.35% covered (success)
95.35%
41 / 43
0.00% covered (danger)
0.00%
0 / 1
5
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Exceptions\AppException;
6use App\Services\ResultCache;
7use App\Services\UserCompanies;
8use Illuminate\Contracts\Routing\ResponseFactory;
9use Illuminate\Http\Request;
10use Illuminate\Http\Response as HttpResponse;
11use Illuminate\Support\Facades\App;
12use Illuminate\Support\Facades\DB;
13use Illuminate\Support\Facades\Schema;
14
15/**
16 * Analytics redesign (2026-07 proposal): the 5-table / 5-filter rewrite of the
17 * quotations analytics page. Lives in its own controller so the legacy
18 * endpoints in Quotations.php keep working during an incremental rollout.
19 *
20 * Endpoints (all POST, same auth group as the legacy analytics routes):
21 *   1. list_productivity_region     — Table 1 "Productividad por región"
22 *   2. list_productivity_commercial — Table 2 "Productividad por comercial"
23 *   3. list_mix_group               — Table 3 "Mix por grupo" (period compare)
24 *   4. list_effectiveness           — Table 4 "Efectividad / Velocidad"
25 *   5. list_backlog                 — Table 5 "Backlog de solicitudes" (NEW)
26 *   +  analytics_filters            — populate all filter dropdowns in one call
27 *
28 * Cross-cutting rules (match the weekly report):
29 *   - issued  → always by sent_date (last send; issue_date = ERP budget date)
30 *   - accepted → always by acceptance_date
31 *   - every table returns BOTH # (count) and € (amount); the frontend "# / €"
32 *     toggle just picks which to show — no re-query needed.
33 *   - tables 3/4/5 accept an optional base/current period window (PBI-style).
34 *
35 * All mappings (regions, budget-type groups, system accounts, AI origin,
36 * backlog buckets) come from config/analytics.php — see the PHASE-0 notes there.
37 */
38class AnalyticsRedesign extends Controller
39{
40    private $locale;
41
42    private $userId;
43
44    private $region;
45
46    /** @var array<int,int> */
47    private array $companyIds;
48
49    public function __construct()
50    {
51        $this->locale = request()->header('Locale-Id');
52        $this->userId = request()->header('User-Id');
53        $this->region = request()->header('Region');
54
55        App::setLocale($this->locale);
56
57        $this->companyIds = [];
58
59        if ($this->region !== null && $this->region !== '' && $this->region !== 'All') {
60            $this->region = urldecode((string) $this->region);
61
62            $query = 'SELECT b.company_id
63                      FROM tbl_company_users a
64                      LEFT JOIN tbl_companies b ON a.company_id = b.company_id
65                      WHERE a.user_id = ? AND b.region = ?';
66
67            $rows = DB::select($query, [intval($this->userId), $this->region]);
68            $this->companyIds = collect($rows)->pluck('company_id')->toArray();
69        } else {
70            $this->companyIds = request()->attributes->get(
71                'user_company_ids',
72                UserCompanies::forUser((int) $this->userId)
73            );
74        }
75    }
76
77    /* =====================================================================
78     |  Shared helpers
79     * ===================================================================== */
80
81    /** Accent/space/case-insensitive normalisation for region name matching. */
82    private function normalizeRegion(string $value): string
83    {
84        $value = mb_strtolower(trim($value));
85        $from = ['á', 'à', 'ä', 'â', 'é', 'è', 'ë', 'ê', 'í', 'ì', 'ï', 'î', 'ó', 'ò', 'ö', 'ô', 'ú', 'ù', 'ü', 'û', 'ñ'];
86        $to = ['a', 'a', 'a', 'a', 'e', 'e', 'e', 'e', 'i', 'i', 'i', 'i', 'o', 'o', 'o', 'o', 'u', 'u', 'u', 'u', 'n'];
87
88        return str_replace($from, $to, $value);
89    }
90
91    /**
92     * Effective company-id list for a request, honouring an optional
93     * company_id param and an optional region code filter. Always intersected
94     * with the caller's accessible companies (never widens scope).
95     *
96     * @return array<int,int>
97     */
98    private function scopeCompanyIds(array $data): array
99    {
100        $ids = $this->companyIds;
101
102        $companyId = isset($data['company_id']) ? (int) $data['company_id'] : 0;
103        if ($companyId !== 0) {
104            $ids = array_values(array_filter($ids, fn ($id) => (int) $id === $companyId));
105            if (empty($ids)) {
106                $ids = [$companyId]; // caller explicitly asked; scope query still guards
107            }
108        }
109
110        if (! empty($data['region'])) {
111            $ids = array_values(array_intersect($ids, $this->companyIdsForRegion((string) $data['region'], $ids)));
112        }
113
114        return array_map('intval', $ids);
115    }
116
117    /**
118     * Company ids (within $within) whose tbl_companies.region maps to $regionCode.
119     *
120     * @param  array<int,int>  $within
121     * @return array<int,int>
122     */
123    private function companyIdsForRegion(string $regionCode, array $within): array
124    {
125        if (empty($within)) {
126            return [];
127        }
128
129        $config = config("analytics.regions.{$regionCode}");
130        if (! $config) {
131            return $within; // unknown code → don't filter (fail open, logged upstream)
132        }
133
134        $aliases = array_map(fn ($a) => $this->normalizeRegion((string) $a), $config['aliases'] ?? []);
135        $aliases[] = $this->normalizeRegion((string) ($config['label'] ?? ''));
136
137        $inIds = implode(',', array_map('intval', $within));
138        $rows = DB::select("SELECT company_id, region FROM tbl_companies WHERE company_id IN ({$inIds})");
139
140        $matched = [];
141        foreach ($rows as $r) {
142            if (in_array($this->normalizeRegion((string) $r->region), $aliases, true)) {
143                $matched[] = (int) $r->company_id;
144            }
145        }
146
147        return $matched;
148    }
149
150    /** Comma-safe company-id SQL fragment for a scoped id list. */
151    private function companyInSql(array $ids): string
152    {
153        $ids = array_filter(array_map('intval', $ids));
154
155        return empty($ids) ? '0' : implode(',', $ids);
156    }
157
158    /** tbl_budget_status 20 = "Rechazado-duplicado" - where a replaced quote lands. */
159    private const STATUS_REJECTED_DUPLICATE = 20;
160
161    /**
162     * Standard filter WHERE fragment shared by all tables. Assumes the query
163     * aliases quotations as `q` and joins bt (budget_types), bs (budget_status),
164     * ct (customer_types), s (sources).
165     */
166    private function standardFilters(array $data): string
167    {
168        $where = '';
169
170        if (! empty($data['source'])) {
171            $where .= " AND s.name = '".addslashes((string) $data['source'])."'";
172        }
173        if (! empty($data['commercial'])) {
174            $where .= " AND q.commercial = '".addslashes((string) $data['commercial'])."'";
175        }
176        if (! empty($data['budget_type'])) {
177            $where .= ' AND bt.budget_type_id = '.(int) $data['budget_type'];
178        }
179        if (! empty($data['budget_type_group'])) {
180            // FIRE-XXXX (2026-09-08): FE sends the group KEY (e.g. "instalacion"),
181            // not the DB id. Resolve via config/analytics.budget_type_groups to
182            // the list of budget_type_group_id values, then IN(...) filter.
183            // Previous code cast the string to int → 0, which never matched, so
184            // all five analytics tables returned "Sin datos" whenever this
185            // filter was set. Reported by Duarte 2026-09-07.
186            $groupKey = (string) $data['budget_type_group'];
187            $groups = config('analytics.budget_type_groups', []);
188            $selected = collect($groups)->firstWhere('key', $groupKey);
189
190            if ($selected && ! empty($selected['ids'])) {
191                $ids = array_map('intval', (array) $selected['ids']);
192                $otherKey = config('analytics.other_group_key', 'otros');
193
194                if ($groupKey === $otherKey) {
195                    // "Otros" is a catch-all: the enumerated ids AND anything
196                    // else not bucketed elsewhere (so new groups added to
197                    // tbl_budget_type_groups don't silently disappear).
198                    $enumerated = collect($groups)
199                        ->where('key', '!=', $otherKey)
200                        ->pluck('ids')
201                        ->flatten()
202                        ->map(fn ($v) => (int) $v)
203                        ->all();
204                    $inList = implode(',', $ids);
205                    $notInList = $enumerated ? implode(',', $enumerated) : '0';
206                    $where .= " AND (bt.budget_type_group_id IN ($inList)"
207                        ." OR bt.budget_type_group_id NOT IN ($notInList))";
208                } else {
209                    $where .= ' AND bt.budget_type_group_id IN ('.implode(',', $ids).')';
210                }
211            }
212        }
213        if (! empty($data['budget_status'])) {
214            $where .= ' AND bs.budget_status_id = '.(int) $data['budget_status'];
215        }
216        if (! empty($data['client_type'])) {
217            $where .= ' AND ct.customer_type_id = '.(int) $data['client_type'];
218        }
219
220        // Advanced: amount range (absorbs the legacy "order size" table).
221        if (isset($data['min_amount']) && $data['min_amount'] !== '') {
222            $where .= ' AND q.amount >= '.(float) $data['min_amount'];
223        }
224        if (isset($data['max_amount']) && $data['max_amount'] !== '') {
225            $where .= ' AND q.amount < '.(float) $data['max_amount'];
226        }
227
228        // Origin toggle: Humano / IA.
229        if (! empty($data['origin'])) {
230            $aiValues = collect(config('analytics.ai_created_by_values', ['IA']))
231                ->map(fn ($v) => "'".addslashes(mb_strtolower((string) $v))."'")
232                ->implode(',');
233            if (mb_strtolower((string) $data['origin']) === 'ia') {
234                $where .= " AND LOWER(q.created_by) IN ({$aiValues})";
235            } elseif (mb_strtolower((string) $data['origin']) === 'human') {
236                $where .= " AND (q.created_by IS NULL OR LOWER(q.created_by) NOT IN ({$aiValues}))";
237            }
238        }
239
240        $where .= $this->excludeReplacedQuotesSql();
241
242        return $where;
243    }
244
245    /**
246     * Drop quotes that were superseded via "Replace quotation".
247     *
248     * Both halves are required: status 20 alone is also set by the AI
249     * duplicate detector on quotes nobody replaced, and a replaced_quotes row
250     * alone would hide a quote whose status was later moved back by hand.
251     * Excluding only the intersection keeps this to the sustituto case the
252     * reports care about - a renegotiated job counted once, not twice.
253     *
254     * Table existence is checked because this also runs where the migration
255     * has not been applied yet; without the guard every analytics table would
256     * fail with a missing-table error instead of showing data.
257     */
258    private function excludeReplacedQuotesSql(): string
259    {
260        try {
261            if (! Schema::hasTable('replaced_quotes')) {
262                return '';
263            }
264        } catch (\Throwable $e) {
265            return '';
266        }
267
268        return ' AND NOT (q.budget_status_id = '.self::STATUS_REJECTED_DUPLICATE
269            .' AND EXISTS (SELECT 1 FROM replaced_quotes rq WHERE rq.old_quotation_id = q.id)) ';
270    }
271
272    /** Exclude bot/admin/API commercials (denylist + is_system_account flag). */
273    private function excludeSystemCommercialsSql(): string
274    {
275        $names = collect(config('analytics.system_account_names', []))
276            ->map(fn ($n) => "'".addslashes(trim(mb_strtolower((string) $n)))."'")
277            ->unique()
278            ->implode(',');
279
280        $sql = " AND q.commercial IS NOT NULL AND TRIM(q.commercial) <> '' ";
281        if ($names !== '') {
282            $sql .= " AND TRIM(LOWER(q.commercial)) NOT IN ({$names}) ";
283        }
284
285        // The is_system_account flag is the authoritative exclusion, but only
286        // apply it when the migration has actually run — otherwise the whole
287        // query would fail with "Unknown column" and the table shows no data.
288        // Guard the subquery against NULL/'' names too: a NULL inside a
289        // NOT IN (...) list makes the predicate reject EVERY row (SQL trap).
290        $hasFlag = false;
291        try {
292            $hasFlag = Schema::hasColumn('tbl_users', 'is_system_account');
293        } catch (\Throwable $e) {
294            $hasFlag = false;
295        }
296
297        if ($hasFlag) {
298            $sql .= " AND q.commercial NOT IN (
299                        SELECT name FROM tbl_users
300                        WHERE is_system_account = 1 AND name IS NOT NULL AND name <> ''
301                      ) ";
302        }
303
304        return $sql;
305    }
306
307    /**
308     * Year / month / week filter on a given date column. Returns [whereSql].
309     * Falls back to the default (current + prior year) when nothing is set.
310     */
311    private function yearWindow(array $data, string $col): string
312    {
313        $where = '';
314
315        if (! empty($data['years']) && is_array($data['years'])) {
316            $years = implode(',', array_map('intval', $data['years']));
317            $where .= " AND YEAR({$col}) IN ({$years})";
318        } elseif (empty($data['current_start']) && empty($data['base_start'])) {
319            // Default: kill the "206"/2021/2022 junk — current + prior year only.
320            $current = (int) date('Y');
321            $prior = $current - 1;
322            $where .= " AND YEAR({$col}) IN ({$current},{$prior})";
323        }
324
325        if (! empty($data['month'])) {
326            $where .= ' AND MONTH('.$col.') = '.(int) $data['month'];
327        }
328        if (! empty($data['week'])) {
329            $where .= ' AND WEEK('.$col.', 1) = '.(int) $data['week'];
330        }
331
332        return $where;
333    }
334
335    /** Date-range fragment for a named window ('current' | 'base'). */
336    private function windowRange(array $data, string $prefix, string $col): ?string
337    {
338        $start = $data["{$prefix}_start"] ?? null;
339        $end = $data["{$prefix}_end"] ?? null;
340        if (empty($start) || empty($end)) {
341            return null;
342        }
343        $start = addslashes((string) $start);
344        $end = addslashes((string) $end);
345
346        return " AND DATE({$col}) BETWEEN '{$start}' AND '{$end}' ";
347    }
348
349    private function ok($data, array $extra = []): ResponseFactory|HttpResponse
350    {
351        return response(array_merge(['message' => 'OK', 'data' => $data], $extra));
352    }
353
354    private function ko(\Throwable $e, string $tag): ResponseFactory|HttpResponse
355    {
356        report(AppException::fromException($e, $tag));
357
358        return response(['message' => 'KO', 'error' => $e->getMessage()]);
359    }
360
361    /* =====================================================================
362     |  Table 1 — Productividad por región
363     * ===================================================================== */
364
365    public function list_productivity_region(Request $request): ResponseFactory|HttpResponse
366    {
367        try {
368            $data = $request->all();
369            $ids = $this->companyInSql($this->scopeCompanyIds($data));
370
371            $where = $this->standardFilters($data);
372            $where .= $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)');
373
374            $query = "SELECT
375                        YEAR(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)) AS 'year',
376                        LPAD(MONTH(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)), 2, 0) AS 'month',
377                        LPAD(WEEK(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY), 1), 2, 0) AS 'week',
378                        COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) AS issued_count,
379                        SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) AS issued_amount,
380                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) AS accepted_count,
381                        SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END) AS accepted_amount,
382                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END)
383                            / NULLIF(COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END), 0) * 100 AS acceptance_rate_count,
384                        SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END)
385                            / NULLIF(SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END), 0) * 100 AS acceptance_rate_amount,
386                        GROUP_CONCAT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.id END) AS groupConcatIds
387                    FROM tbl_quotations q
388                        LEFT JOIN tbl_sources s ON s.source_id = q.source_id
389                        LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
390                        LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
391                        LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
392                    WHERE COALESCE(q.sent_date, q.issue_date) IS NOT NULL
393                        AND q.budget_type_id IS NOT NULL
394                        AND q.for_add = 0
395                        AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
396                        AND q.company_id IN ({$ids})
397                        {$where}
398                    GROUP BY
399                        YEAR(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)),
400                        MONTH(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)),
401                        WEEK(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY), 1) WITH ROLLUP
402                    ORDER BY year DESC, month ASC, week ASC";
403
404            $result = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query));
405
406            return $this->ok($result);
407        } catch (\Throwable $e) {
408            return $this->ko($e, 'ANALYTICS_PRODUCTIVITY_REGION_EXCEPTION');
409        }
410    }
411
412    /* =====================================================================
413     |  Table 2 — Productividad por comercial
414     * ===================================================================== */
415
416    public function list_productivity_commercial(Request $request): ResponseFactory|HttpResponse
417    {
418        try {
419            $data = $request->all();
420            $ids = $this->companyInSql($this->scopeCompanyIds($data));
421
422            $where = $this->standardFilters($data);
423            $where .= $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)');
424            $where .= $this->excludeSystemCommercialsSql();
425
426            $query = "SELECT
427                        q.commercial AS commercial,
428                        COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) AS issued_count,
429                        SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) AS issued_amount,
430                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) AS accepted_count,
431                        SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END) AS accepted_amount,
432                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END)
433                            / NULLIF(COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END), 0) * 100 AS acceptance_rate_count,
434                        AVG(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END) AS avg_ticket
435                    FROM tbl_quotations q
436                        LEFT JOIN tbl_sources s ON s.source_id = q.source_id
437                        LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
438                        LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
439                        LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
440                    WHERE COALESCE(q.sent_date, q.issue_date) IS NOT NULL
441                        AND q.budget_type_id IS NOT NULL
442                        AND q.for_add = 0
443                        AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
444                        AND q.company_id IN ({$ids})
445                        {$where}
446                    GROUP BY q.commercial
447                    ORDER BY issued_count DESC";
448
449            $result = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query));
450
451            return $this->ok($result);
452        } catch (\Throwable $e) {
453            return $this->ko($e, 'ANALYTICS_PRODUCTIVITY_COMMERCIAL_EXCEPTION');
454        }
455    }
456
457    /* =====================================================================
458     |  Table 3 — Mix por grupo  (with optional base/current period compare)
459     * ===================================================================== */
460
461    public function list_mix_group(Request $request): ResponseFactory|HttpResponse
462    {
463        try {
464            $data = $request->all();
465            $ids = $this->companyInSql($this->scopeCompanyIds($data));
466            $groups = config('analytics.budget_type_groups', []);
467
468            $baseFilters = $this->standardFilters($data);
469
470            $current = $this->mixForWindow($data, $ids, $baseFilters, 'current', $groups);
471            $result = ['groups' => $groups, 'current' => $current];
472
473            // Period compare (IMPORTANTE 2) — only when a base window is supplied.
474            if ($this->windowRange($data, 'base', 'COALESCE(q.sent_date, q.issue_date)') !== null) {
475                $base = $this->mixForWindow($data, $ids, $baseFilters, 'base', $groups);
476                $result['base'] = $base;
477                $result['delta'] = $this->mixDelta($current, $base);
478            }
479
480            return $this->ok($result);
481        } catch (\Throwable $e) {
482            return $this->ko($e, 'ANALYTICS_MIX_GROUP_EXCEPTION');
483        }
484    }
485
486    /**
487     * One mix snapshot: per-group issued/accepted in # and €, plus % of total.
488     * Window = the named date range if provided, else the year window.
489     */
490    private function mixForWindow(array $data, string $ids, string $baseFilters, string $prefix, array $groups): array
491    {
492        $range = $this->windowRange($data, $prefix, 'COALESCE(q.sent_date, q.issue_date)');
493        $issuedWindow = $range ?? $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)');
494
495        // Accepted uses acceptance_date over the same calendar window.
496        $acceptedWindow = $range
497            ? str_replace('COALESCE(q.sent_date, q.issue_date)', 'q.acceptance_date', $range)
498            : $this->yearWindow($data, 'q.acceptance_date');
499
500        $query = "SELECT
501                    bt.budget_type_group_id AS group_id,
502                    COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL {$issuedWindow} THEN 1 END) AS issued_count,
503                    SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL {$issuedWindow} THEN q.amount END) AS issued_amount,
504                    COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' {$acceptedWindow} THEN 1 END) AS accepted_count,
505                    SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' {$acceptedWindow} THEN q.amount END) AS accepted_amount
506                FROM tbl_quotations q
507                    LEFT JOIN tbl_sources s ON s.source_id = q.source_id
508                    LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
509                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
510                    LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
511                WHERE q.budget_type_id IS NOT NULL
512                    AND q.for_add = 0
513                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
514                    AND q.company_id IN ({$ids})
515                    {$baseFilters}
516                GROUP BY bt.budget_type_group_id";
517
518        $rows = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query));
519
520        // Fold raw group_id rows into the configured 6 buckets.
521        $byId = [];
522        foreach ($rows as $r) {
523            $byId[(int) $r->group_id] = $r;
524        }
525
526        $otherKey = config('analytics.other_group_key', 'otros');
527        $out = [];
528        $totIssuedC = $totIssuedA = 0;
529        foreach ($groups as $g) {
530            $issuedC = $issuedA = $accC = $accA = 0;
531            foreach ($g['ids'] as $gid) {
532                if (isset($byId[$gid])) {
533                    $issuedC += (int) $byId[$gid]->issued_count;
534                    $issuedA += (float) $byId[$gid]->issued_amount;
535                    $accC += (int) $byId[$gid]->accepted_count;
536                    $accA += (float) $byId[$gid]->accepted_amount;
537                    unset($byId[$gid]);
538                }
539            }
540            $out[$g['key']] = [
541                'key' => $g['key'], 'label' => $g['label'],
542                'issued_count' => $issuedC, 'issued_amount' => $issuedA,
543                'accepted_count' => $accC, 'accepted_amount' => $accA,
544            ];
545            $totIssuedC += $issuedC;
546            $totIssuedA += $issuedA;
547        }
548
549        // Any leftover group ids (not enumerated in config) → the "Otros" bucket.
550        foreach ($byId as $r) {
551            if (isset($out[$otherKey])) {
552                $out[$otherKey]['issued_count'] += (int) $r->issued_count;
553                $out[$otherKey]['issued_amount'] += (float) $r->issued_amount;
554                $out[$otherKey]['accepted_count'] += (int) $r->accepted_count;
555                $out[$otherKey]['accepted_amount'] += (float) $r->accepted_amount;
556                $totIssuedC += (int) $r->issued_count;
557                $totIssuedA += (float) $r->issued_amount;
558            }
559        }
560
561        // % of total issued (# and €).
562        foreach ($out as &$row) {
563            $row['pct_count'] = $totIssuedC > 0 ? round($row['issued_count'] / $totIssuedC * 100, 2) : 0;
564            $row['pct_amount'] = $totIssuedA > 0 ? round($row['issued_amount'] / $totIssuedA * 100, 2) : 0;
565        }
566        unset($row);
567
568        return array_values($out);
569    }
570
571    private function mixDelta(array $current, array $base): array
572    {
573        $baseByKey = [];
574        foreach ($base as $b) {
575            $baseByKey[$b['key']] = $b;
576        }
577        $delta = [];
578        foreach ($current as $c) {
579            $b = $baseByKey[$c['key']] ?? null;
580            $delta[] = [
581                'key' => $c['key'],
582                'label' => $c['label'],
583                'issued_count' => $c['issued_count'] - ($b['issued_count'] ?? 0),
584                'issued_amount' => $c['issued_amount'] - ($b['issued_amount'] ?? 0),
585                'accepted_count' => $c['accepted_count'] - ($b['accepted_count'] ?? 0),
586                'accepted_amount' => $c['accepted_amount'] - ($b['accepted_amount'] ?? 0),
587            ];
588        }
589
590        return $delta;
591    }
592
593    /* =====================================================================
594     |  Table 4 — Efectividad / Velocidad
595     * ===================================================================== */
596
597    public function list_effectiveness(Request $request): ResponseFactory|HttpResponse
598    {
599        try {
600            $data = $request->all();
601            $ids = $this->companyInSql($this->scopeCompanyIds($data));
602            [$t1, $t2] = config('analytics.effectiveness_thresholds', [10, 30]);
603            $t1 = (int) $t1;
604            $t2 = (int) $t2;
605
606            $where = $this->standardFilters($data);
607            $range = $this->windowRange($data, 'current', 'COALESCE(q.sent_date, q.issue_date)');
608            $where .= $range ?? $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)');
609
610            // Response-time metrics keyed by send year (drill by year like today);
611            // the durations below stay on issue_date (ERP budget date).
612            $query = "SELECT
613                        YEAR(COALESCE(q.sent_date, q.issue_date)) AS 'year',
614                        AVG(CASE WHEN q.request_date IS NOT NULL AND q.issue_date > q.request_date
615                                 THEN DATEDIFF(q.issue_date, q.request_date) END) AS avg_days_request_to_issue,
616                        AVG(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date >= q.issue_date
617                                 THEN DATEDIFF(q.acceptance_date, q.issue_date) END) AS avg_days_issue_to_accept,
618                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) AS accepted_total,
619                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado'
620                                   AND DATEDIFF(q.acceptance_date, q.issue_date) < {$t1} THEN 1 END) AS accepted_lt_t1,
621                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado'
622                                   AND DATEDIFF(q.acceptance_date, q.issue_date) >= {$t1}
623                                   AND DATEDIFF(q.acceptance_date, q.issue_date) < {$t2} THEN 1 END) AS accepted_t1_t2,
624                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado'
625                                   AND DATEDIFF(q.acceptance_date, q.issue_date) >= {$t2} THEN 1 END) AS accepted_gt_t2
626                    FROM tbl_quotations q
627                        LEFT JOIN tbl_sources s ON s.source_id = q.source_id
628                        LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
629                        LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
630                        LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
631                    WHERE COALESCE(q.sent_date, q.issue_date) IS NOT NULL
632                        AND q.for_add = 0
633                        AND q.company_id IN ({$ids})
634                        {$where}
635                    GROUP BY YEAR(COALESCE(q.sent_date, q.issue_date)) WITH ROLLUP
636                    ORDER BY year DESC";
637
638            $rows = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query));
639
640            foreach ($rows as $r) {
641                $tot = (int) $r->accepted_total ?: 0;
642                $r->pct_lt_t1 = $tot > 0 ? round($r->accepted_lt_t1 / $tot * 100, 2) : 0;
643                $r->pct_t1_t2 = $tot > 0 ? round($r->accepted_t1_t2 / $tot * 100, 2) : 0;
644                $r->pct_gt_t2 = $tot > 0 ? round($r->accepted_gt_t2 / $tot * 100, 2) : 0;
645            }
646
647            return $this->ok($rows, ['thresholds' => [$t1, $t2]]);
648        } catch (\Throwable $e) {
649            return $this->ko($e, 'ANALYTICS_EFFECTIVENESS_EXCEPTION');
650        }
651    }
652
653    /* =====================================================================
654     |  Table 5 — Backlog de solicitudes (NEW)
655     * ===================================================================== */
656
657    public function list_backlog(Request $request): ResponseFactory|HttpResponse
658    {
659        try {
660            $data = $request->all();
661            $ids = $this->companyInSql($this->scopeCompanyIds($data));
662
663            $buckets = config('analytics.backlog_buckets', []);
664            $pending = collect(config('analytics.backlog_pending_status_ids', [1, 6, 11, 12]))
665                ->map(fn ($v) => (int) $v)->implode(',') ?: '0';
666
667            // Snapshot date: as-of a supplied date, else today.
668            $asOf = ! empty($data['as_of']) ? addslashes((string) $data['as_of']) : date('Y-m-d');
669
670            $where = $this->standardFilters($data);
671
672            // Build one COUNT column per aging bucket.
673            $bucketCols = [];
674            foreach ($buckets as $b) {
675                $min = (int) $b['min'];
676                $cond = "DATEDIFF('{$asOf}', q.request_date) >= {$min}";
677                if ($b['max'] !== null) {
678                    $cond .= ' AND DATEDIFF(\''.$asOf.'\', q.request_date) < '.(int) $b['max'];
679                }
680                $key = addslashes((string) $b['key']);
681                $bucketCols[] = "COUNT(CASE WHEN {$cond} THEN 1 END) AS `bucket_{$key}`";
682            }
683            $bucketSql = implode(",\n                        ", $bucketCols);
684
685            $query = "SELECT
686                        c.region AS region,
687                        COUNT(*) AS pending_total,
688                        {$bucketSql},
689                        ROUND(AVG(DATEDIFF('{$asOf}', q.request_date)) / 7, 1) AS backlog_weeks_avg
690                    FROM tbl_quotations q
691                        LEFT JOIN tbl_companies c ON c.company_id = q.company_id
692                        LEFT JOIN tbl_sources s ON s.source_id = q.source_id
693                        LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
694                        LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
695                        LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
696                    WHERE q.for_add = 0
697                        AND q.request_date IS NOT NULL
698                        AND q.acceptance_date IS NULL
699                        AND q.budget_status_id IN ({$pending})
700                        AND q.company_id IN ({$ids})
701                        {$where}
702                    GROUP BY c.region WITH ROLLUP
703                    ORDER BY pending_total DESC";
704
705            $rows = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query));
706
707            return $this->ok($rows, ['buckets' => $buckets, 'as_of' => $asOf]);
708        } catch (\Throwable $e) {
709            return $this->ko($e, 'ANALYTICS_BACKLOG_EXCEPTION');
710        }
711    }
712
713    /* =====================================================================
714     |  Filter population — one call fills every dropdown on the page
715     * ===================================================================== */
716
717    public function analytics_filters(Request $request): ResponseFactory|HttpResponse
718    {
719        try {
720            $data = $request->all();
721            $ids = $this->companyInSql($this->scopeCompanyIds($data));
722
723            // Regions actually present for these companies, mapped to codes.
724            $regionRows = DB::select("SELECT DISTINCT region FROM tbl_companies WHERE company_id IN ({$ids}) AND region IS NOT NULL AND region <> ''");
725            $configRegions = config('analytics.regions', []);
726            $regions = [];
727            foreach ($regionRows as $rr) {
728                $norm = $this->normalizeRegion((string) $rr->region);
729                foreach ($configRegions as $code => $cfg) {
730                    $aliases = array_map(fn ($a) => $this->normalizeRegion((string) $a), $cfg['aliases'] ?? []);
731                    $aliases[] = $this->normalizeRegion((string) ($cfg['label'] ?? ''));
732                    if (in_array($norm, $aliases, true)) {
733                        $regions[$code] = ['code' => $code, 'label' => $cfg['label']];
734                        break;
735                    }
736                }
737            }
738
739            // Clean commercial list — active humans only.
740            $exclude = $this->excludeSystemCommercialsSql();
741            $commercials = DB::select("SELECT DISTINCT q.commercial
742                FROM tbl_quotations q
743                WHERE q.company_id IN ({$ids}) AND q.for_add = 0 {$exclude}
744                ORDER BY q.commercial ASC");
745
746            // Years present, with default = current + prior year.
747            $years = DB::select("SELECT DISTINCT YEAR(COALESCE(sent_date, issue_date)) AS year
748                FROM tbl_quotations WHERE COALESCE(sent_date, issue_date) IS NOT NULL AND company_id IN ({$ids})
749                    AND YEAR(COALESCE(sent_date, issue_date)) >= 2023
750                ORDER BY year DESC");
751            $currentY = (int) date('Y');
752
753            // Reference lists.
754            // These reference tables are global (no company_id column) — mirror
755            // the legacy Configurations lists, which query them unscoped.
756            $budgetStatus = DB::select("SELECT budget_status_id, name FROM tbl_budget_status WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC");
757            $budgetTypes = DB::select("SELECT budget_type_id, name, budget_type_group_id FROM tbl_budget_types WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC");
758            $customerTypes = DB::select("SELECT customer_type_id, name FROM tbl_customer_types WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC");
759            $sources = DB::select("SELECT source_id, name FROM tbl_sources WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC");
760
761            return response([
762                'message' => 'OK',
763                'regions' => array_values($regions),
764                'commercials' => collect($commercials)->pluck('commercial')->filter()->values(),
765                'years' => collect($years)->pluck('year')->values(),
766                'default_years' => [$currentY, $currentY - 1],
767                'budget_type_groups' => config('analytics.budget_type_groups', []),
768                'budget_types' => $budgetTypes,
769                'budget_status' => $budgetStatus,
770                'client_types' => $customerTypes,
771                'sources' => $sources,
772                'origins' => [
773                    ['key' => 'human', 'label' => 'Humano'],
774                    ['key' => 'ia', 'label' => 'IA'],
775                ],
776            ]);
777        } catch (\Throwable $e) {
778            return $this->ko($e, 'ANALYTICS_FILTERS_EXCEPTION');
779        }
780    }
781}