<?php

namespace App\Http\Controllers;

use App\Models\Company;
use App\Models\Dashboard;
use App\Models\Demande;
use App\Models\Dossier;
use App\Models\Rapport;
use App\Models\Rendezvous;
use App\Models\Seuil;
use App\Models\Ticket;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;
use App\Services\SidebarCache;

class DashboardController extends Controller
{
    /**
     * Base query with user permissions applied
     */
    private function baseQuery()
    {
        return Dossier::user()
            ->where('dossier_status_id', 2)
            ->where('update', 0);
    }

    /**
     * Get base query for garage users (role_id = 6)
     */
    private function garageQuery()
    {
        $user = User::whereId(Auth::user()->id)->with('manygarage')->first();
        return Dossier::whereHas('garage', function ($query) use ($user) {
            $query->whereIn('garage_id', $user->manygarage->pluck('id'));
        })
            ->where('dossier_status_id', 2)
            ->where('update', 0);
    }

    /**
     * Calculate timeleft for a collection of dossiers efficiently
     */
    private function calculateTimeleft($dossiers)
    {
        if ($dossiers->isEmpty()) {
            return $dossiers;
        }

        $companyIds = $dossiers->pluck('company_id')->unique()->filter();
        $dossierIds = $dossiers->pluck('id');
        $stepIds = $dossiers->pluck('step_id')->unique()->filter();

        $companyStepTimes = DB::table('company_step')
            ->whereIn('company_id', $companyIds)
            ->whereIn('step_id', $stepIds)
            ->get()
            ->groupBy(function ($item) {
                return $item->company_id . '-' . $item->step_id;
            });

        $stepDates = DB::table('step_dossier')
            ->whereIn('dossier_id', $dossierIds)
            ->whereIn('step_id', $stepIds)
            ->orderBy('created_at', 'desc')
            ->get()
            ->groupBy(function ($item) {
                return $item->dossier_id . '-' . $item->step_id;
            });

        foreach ($dossiers as $dossier) {
            if (!$dossier->company_id || !$dossier->step_id) {
                $dossier->timeleft = 0;
                continue;
            }

            $key = $dossier->company_id . '-' . $dossier->step_id;
            $stepKey = $dossier->id . '-' . $dossier->step_id;

            $companyStep = $companyStepTimes->get($key)?->first();
            $stepDate = $stepDates->get($stepKey)?->first();

            if ($companyStep && $stepDate) {
                $monthdate = Carbon::parse($stepDate->created_at);
                $dateDiff = Carbon::now()->diffInHours($monthdate);
                $limit = $companyStep->time ?? 0;
                $dossier->timeleft = $limit - $dateDiff;
            } else {
                $dossier->timeleft = 0;
            }
        }

        return $dossiers;
    }

    /**
     * Get zero counts as fallback
     */
    private function getZeroCounts()
    {
        return [
            'ticketcount' => 0,
            'ticketcountterminer' => 0,
            'senddevis' => 0,
            'sendfacture' => 0,
            'sendrapport' => 0,
            'enexp' => 0,
            'aexpcount' => 0,
            'basedoc' => 0,
            'attdevis' => 0,
            'attexpap' => 0,
            'attfacture' => 0,
            'attfactureap' => 0,
            'attdevisav' => 0,
            'deuxdevis' => 0,
            'deuxfacture' => 0,
            'traitdevis' => 0,
            'traitfacture' => 0,
            'count' => 0,
            'count2' => 0,
            'attrapport' => 0,
            'count3' => 0,
            'deuxrapport' => 0,
            'rapportasigner' => 0,
            'docasigner' => 0,
            'docsigner' => 0,
            'controldevis' => 0,
            'avisdevis' => 0,
            'avisfacture' => 0,
            'douteux' => 0,
            'tribunale' => 0,
            'rdv' => 0,
            'rapide' => 0,
            'demandes' => 0,
            'dossiersfacture' => 0,
        ];
    }

    /**
     * Get sidebar counts - cached version
     */
    private function getSidebarCounts()
    {
        $userId = Auth::user()->id;
        $roleId = Auth::user()->role_id;
        $cacheKey = "sidebar_counts_{$userId}_{$roleId}";
        
        return Cache::remember($cacheKey, 120, function () use ($roleId) {
            return $this->computeSidebarCountsOptimized($roleId);
        });
    }

    /**
     * OPTIMIZED SIDEBAR COUNTS - Using Eloquent (safer than raw SQL)
     */
    private function computeSidebarCountsOptimized($roleId)
    {
        $userId = Auth::id();
        $counts = [];
        $baseQuery = $this->baseQuery();

        // ========================================
        // SIMPLE COUNTS
        // ========================================
        
        $counts['ticketcount'] = Ticket::where('user_id', $userId)
            ->where('status_rendezvous_id', 2)
            ->count();
        
        $counts['ticketcountterminer'] = Ticket::where('creator_id', $userId)
            ->where('status_rendezvous_id', 3)
            ->count();
        
        $counts['demandes'] = Demande::where('status_rendezvous_id', 2)->count();
        $counts['rdv'] = Rendezvous::where('status_rendezvous_id', 2)->count();
        
        $counts['dossiersfacture'] = Dossier::whereNull('facture')
            ->where('dossier_status_id', 2)
            ->where('update', 1)
            ->count();

        // ========================================
        // DOSSIER COUNTS
        // ========================================
        
        $counts['tribunale'] = (clone $baseQuery)->where('type_expertise_id', 9)->count();
        $counts['douteux'] = (clone $baseQuery)->where('sinistre_douteux', 1)->count();

        if ($roleId == 2) {
            $counts['rapide'] = (clone $baseQuery)
                ->where('type_expertise_id', 16)
                ->whereIn('step_id', [4, 9])
                ->count();
        } else {
            $counts['rapide'] = (clone $baseQuery)->where('type_expertise_id', 16)->count();
        }

        // ========================================
        // DEVIS COUNTS
        // ========================================
        $counts['deuxdevis'] = (clone $baseQuery)->whereHas('onedevis', function($q) {
            $q->where('devis_status_id', 1);
        })->count();
        
        $counts['senddevis'] = (clone $baseQuery)->whereHas('onedevis', function($q) {
            $q->where('devis_status_id', 2);
        })->count();
        
        $counts['avisdevis'] = (clone $baseQuery)->whereHas('onedevis', function($q) {
            $q->where('devis_status_id', 3);
        })->count();
        
        $counts['attdevisav'] = (clone $baseQuery)->whereHas('onedevis', function($q) {
            $q->where('devis_status_id', 9);
        })->count();

        // Status 4, 5, 8 with seuil for role 2
        if ($roleId == 2) {
            $seuils = Seuil::where('user_id', $userId)->get()->keyBy('company_id');
            $companyIds = $seuils->keys()->toArray();
            
            if (!empty($companyIds)) {
                $counts['traitdevis'] = (clone $baseQuery)
                    ->whereIn('company_id', $companyIds)
                    ->whereHas('onedevis', function($q) { $q->where('devis_status_id', 4); })
                    ->count();
                
                $counts['count'] = (clone $baseQuery)
                    ->whereIn('company_id', $companyIds)
                    ->whereHas('onedevis', function($q) { $q->where('devis_status_id', 5); })
                    ->count();
                
                $counts['controldevis'] = (clone $baseQuery)
                    ->whereIn('company_id', $companyIds)
                    ->whereHas('onedevis', function($q) { $q->where('devis_status_id', 8); })
                    ->count();
            } else {
                $counts['traitdevis'] = 0;
                $counts['count'] = 0;
                $counts['controldevis'] = 0;
            }
        } else {
            $counts['traitdevis'] = (clone $baseQuery)->whereHas('onedevis', function($q) {
                $q->where('devis_status_id', 4);
            })->count();
            
            $counts['count'] = (clone $baseQuery)->whereHas('onedevis', function($q) {
                $q->where('devis_status_id', 5);
            })->count();
            
            if ($roleId == 4) {
                $counts['controldevis'] = (clone $baseQuery)->whereHas('onedevis', function($q) {
                    $q->where('devis_status_id', 8);
                })->count();
            } else {
                $counts['controldevis'] = 0;
            }
        }

        // ========================================
        // FACTURE COUNTS
        // ========================================
        $counts['deuxfacture'] = (clone $baseQuery)->whereHas('onefacture', function($q) {
            $q->where('devis_status_id', 1);
        })->count();
        
        $counts['avisfacture'] = (clone $baseQuery)->whereHas('onefacture', function($q) {
            $q->where('devis_status_id', 3);
        })->count();
        
        $counts['attfactureap'] = (clone $baseQuery)->whereHas('onefacture', function($q) {
            $q->where('devis_status_id', 9);
        })->count();
        
        $counts['sendfacture'] = (clone $baseQuery)
            ->whereDoesntHave('rapport')
            ->whereHas('facture', function($q) { $q->where('devis_status_id', 2); })
            ->count();

        if ($roleId == 2) {
            $seuils = Seuil::where('user_id', $userId)->get()->keyBy('company_id');
            $companyIds = $seuils->keys()->toArray();
            
            if (!empty($companyIds)) {
                $counts['traitfacture'] = (clone $baseQuery)
                    ->whereIn('company_id', $companyIds)
                    ->whereHas('onefacture', function($q) { $q->where('devis_status_id', 4); })
                    ->count();
                
                $counts['count2'] = (clone $baseQuery)
                    ->whereIn('company_id', $companyIds)
                    ->whereHas('onefacture', function($q) { $q->where('devis_status_id', 5); })
                    ->count();
            } else {
                $counts['traitfacture'] = 0;
                $counts['count2'] = 0;
            }
        } else {
            $counts['traitfacture'] = (clone $baseQuery)->whereHas('onefacture', function($q) {
                $q->where('devis_status_id', 4);
            })->count();
            
            $counts['count2'] = (clone $baseQuery)->whereHas('onefacture', function($q) {
                $q->where('devis_status_id', 5);
            })->count();
        }

        // ========================================
        // RAPPORT COUNTS
        // ========================================
        $counts['deuxrapport'] = (clone $baseQuery)->whereHas('onerapport', function($q) {
            $q->where('rapport_status_id', 1);
        })->count();
        
        $counts['sendrapport'] = (clone $baseQuery)->whereHas('onerapport', function($q) {
            $q->where('rapport_status_id', 2);
        })->count();
        
        $counts['rapportasigner'] = (clone $baseQuery)->whereHas('onerapport', function($q) {
            $q->where('rapport_status_id', 4);
        })->count();
        
        $counts['count3'] = (clone $baseQuery)->whereHas('onerapport', function($q) {
            $q->where('rapport_status_id', 5);
        })->count();

        // ========================================
        // DOCUMENT COUNTS
        // ========================================
        $counts['docasigner'] = (clone $baseQuery)->whereHas('document', function($q) {
            $q->where('type_document_id', 21)->where('signer', 0)->where('rejected', 0);
        })->count();
        
        $counts['docsigner'] = (clone $baseQuery)
            ->whereDoesntHave('onerapport', function($q) { $q->whereIn('rapport_status_id', [2, 3]); })
            ->whereHas('document', function($q) { $q->where('type_document_id', 21)->where('signer', 1)->where('rejected', 0); })
            ->count();

        // ========================================
        // CARENCE COUNTS
        // ========================================
        $counts['attdevis'] = (clone $baseQuery)->whereDoesntHave('devis')->count();
        
        $counts['aexpcount'] = (clone $baseQuery)
            ->whereDoesntHave('devis')
            ->whereDoesntHave('document', function($q) { $q->where('type_document_id', 5); })
            ->count();
        
        $counts['enexp'] = (clone $baseQuery)->whereHas('meeting', function($q) {
            $q->where('meeting_status_id', 2);
        })->count();
        
        $counts['attexpap'] = (clone $baseQuery)
            ->where('type_expertise_id', '!=', 14)
            ->where('mode_id', '!=', 7)
            ->whereDoesntHave('document', function($q) { $q->where('type_document_id', 6); })
            ->whereHas('onedevis', function($q) { $q->whereIn('devis_status_id', [2, 7]); })
            ->whereDoesntHave('facture')
            ->count();
        
        $counts['attfacture'] = (clone $baseQuery)
            ->where('type_expertise_id', '!=', 14)
            ->where('mode_id', '!=', 7)
            ->whereHas('document', function($q) { $q->where('type_document_id', 6); })
            ->whereHas('onedevis', function($q) { $q->whereIn('devis_status_id', [2, 7]); })
            ->whereDoesntHave('facture')
            ->count();
        
        $counts['attrapport'] = (clone $baseQuery)
            ->whereHas('facture', function($q) { $q->whereIn('devis_status_id', [2, 7]); })
            ->whereHas('devis', function($q) { $q->whereIn('devis_status_id', [2, 7]); })
            ->whereDoesntHave('rapport')
            ->count();
        
        $counts['basedoc'] = (clone $baseQuery)
            ->whereDoesntHave('document', function($q) { $q->whereIn('type_document_id', [1, 2, 3]); })
            ->count();

        return $counts;
    }

    /**
     * Main dashboard page - FAST INITIAL LOAD
     */
    public function front()
    {
        return view('dashboards.front', array_merge($this->getZeroCounts(), [
            'dossiers' => collect(),
        ]));
    }

    /**
     * API endpoint for real-time sidebar counts
     */
public function getSidebarCountsApi()
{
    try {
        $counts = \App\Services\SidebarCache::getAll();
        return response()->json($counts);
    } catch (\Exception $e) {
        \Log::error('Sidebar API error: ' . $e->getMessage());
        // Return zeros on error
        return response()->json([
            'ticketcount' => 0, 'ticketcountterminer' => 0, 'demandes' => 0, 'rdv' => 0,
            'dossiersfacture' => 0, 'tribunale' => 0, 'douteux' => 0, 'rapide' => 0,
            'deuxdevis' => 0, 'senddevis' => 0, 'avisdevis' => 0, 'attdevisav' => 0,
            'traitdevis' => 0, 'count' => 0, 'controldevis' => 0,
            'deuxfacture' => 0, 'avisfacture' => 0, 'attfactureap' => 0,
            'traitfacture' => 0, 'count2' => 0, 'sendfacture' => 0,
            'deuxrapport' => 0, 'sendrapport' => 0, 'rapportasigner' => 0, 'count3' => 0,
            'docasigner' => 0, 'docsigner' => 0,
            'attdevis' => 0, 'aexpcount' => 0, 'enexp' => 0, 'attexpap' => 0,
            'attfacture' => 0, 'attrapport' => 0, 'basedoc' => 0,
        ]);
    }
}
public function recalculateCounters()
{
    \App\Services\SidebarCache::recalculate();
    return response()->json(['success' => true, 'message' => 'Counters recalculated']);
}

    /**
     * AJAX endpoint for DataTables server-side processing
     */
    public function getDossiersData(Request $request)
    {
        $draw = $request->get('draw');
        $start = $request->get('start', 0);
        $length = $request->get('length', 100);
        $search = $request->get('search')['value'] ?? '';
        $orderColumn = $request->get('order')[0]['column'] ?? 0;
        $orderDir = $request->get('order')[0]['dir'] ?? 'desc';
        $filter = $request->get('filter', 'default');

        $query = $this->getFilteredQuery($filter);

        $userId = Auth::id();
        $roleId = Auth::user()->role_id;
        $cacheKey = "filter_count_{$filter}_{$userId}_{$roleId}";
        
        $totalRecords = Cache::remember($cacheKey, 120, function() use ($filter) {
            return $this->getFilteredQuery($filter)->count();
        });

        $filteredRecords = $totalRecords;
        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $q->where('ref', 'like', "%{$search}%")
                    ->orWhere('matricule', 'like', "%{$search}%")
                    ->orWhere('new_matricule', 'like', "%{$search}%")
                    ->orWhere('numero_sinistre', 'like', "%{$search}%");
            });
            $filteredRecords = $query->count();
        }

        $columns = ['ref', 'company_id', 'insured_id', 'matricule', 'numero_sinistre', 
                    'type_expertise_id', 'mode_id', 'id', 'id', 'step_id', 'id', 
                    'dossier_status_id'];
        
        $orderColumnName = $columns[$orderColumn] ?? 'id';
        $query->orderBy($orderColumnName, $orderDir);

        $dossiers = $query
            ->select([
                'id', 'ref', 'matricule', 'new_matricule', 'numero_sinistre',
                'company_id', 'insured_id', 'type_expertise_id', 'mode_id',
                'step_id', 'dossier_status_id', 'update'
            ])
            ->with([
                'company:id,company',
                'insured:id,name',
                'typeexpertise:id,type',
                'mode:id,mode',
                'currentStep:id,step',
                'status:id,status,color',
                'onedevis:id,dossier_id,montant_devis',
                'onefacture:id,dossier_id,montant_facture'
            ])
            ->skip($start)
            ->take($length)
            ->get();

        $dossiers = $this->calculateTimeleft($dossiers);

        $data = $dossiers->map(function ($dossier) {
            return [
                'id' => $dossier->id,
                'ref' => $dossier->ref ?? '.',
                'company' => optional($dossier->company)->company ?? '.',
                'insured_name' => optional($dossier->insured)->name ?? '.',
                'matricule' => $dossier->matricule ?? $dossier->new_matricule ?? '.',
                'numero_sinistre' => $dossier->numero_sinistre ?? '.',
                'type_expertise' => optional($dossier->typeexpertise)->type ?? '.',
                'mode' => optional($dossier->mode)->mode ?? '.',
                'montant_devis' => optional($dossier->onedevis)->montant_devis ?? '.',
                'montant_facture' => optional($dossier->onefacture)->montant_facture ?? '.',
                'step' => optional($dossier->currentStep)->step ?? '.',
                'timeleft' => $dossier->timeleft ?? 0,
                'status' => optional($dossier->status)->status ?? '.',
                'status_color' => optional($dossier->status)->color ?? 'secondary',
                'is_updated' => $dossier->update ?? 0,
            ];
        });

        return response()->json([
            'draw' => intval($draw),
            'recordsTotal' => $totalRecords,
            'recordsFiltered' => $filteredRecords,
            'data' => $data,
        ]);
    }

    /**
     * Get filtered query based on sidebar filter type
     */
    private function getFilteredQuery($filter)
    {
        $query = Auth::user()->role_id == 6 ? $this->garageQuery() : $this->baseQuery();

        switch ($filter) {
            case 'expertiser':
                return $query->whereDoesntHave('devis')
                    ->whereDoesntHave('document', function ($q) {
                        $q->where('type_document_id', 5);
                    });

            case 'enexp':
                return $query->whereHas('meeting', function ($q) {
                    $q->where('meeting_status_id', 2);
                });

            case 'carencedevis':
                return $query->whereDoesntHave('devis');

            case 'carencefacture':
                return $query->where('type_expertise_id', '!=', 14)
                    ->where('mode_id', '!=', 7)
                    ->whereHas('document', function ($q) {
                        $q->where('type_document_id', 6);
                    })
                    ->whereHas('onedevis', function ($q) {
                        $q->where('devis_status_id', 2)->orWhere('devis_status_id', 7);
                    })->whereDoesntHave('facture');

            case 'carencerapport':
                return $query->whereHas('facture', function ($q) {
                    $q->where('devis_status_id', 2)->orWhere('devis_status_id', 7);
                })->whereHas('devis', function ($q) {
                    $q->where('devis_status_id', 2)->orWhere('devis_status_id', 7);
                })->whereDoesntHave('rapport');

            case 'devisretraiter':
                return $this->getDevisRetraiterQuery();

            case 'factureretraiter':
                return $this->getFactureRetraiterQuery();

            case 'traitdevis':
                return $this->getTraitDevisQuery();

            case 'traitfacture':
                return $query->whereHas('facture', function ($q) {
                    $q->where('devis_status_id', 4);
                });

            case 'senddevis':
                return $query->whereHas('onedevis', function ($q) {
                    $q->where('devis_status_id', 2);
                });

            case 'sendfacture':
                return $query->whereDoesntHave('rapport')
                    ->whereHas('facture', function ($q) {
                        $q->where('devis_status_id', 2);
                    });

            case 'avisdevis':
                return $query->whereHas('onedevis', function ($q) {
                    $q->where('devis_status_id', 3);
                });

            case 'avisfacture':
                return $query->whereHas('onefacture', function ($q) {
                    $q->where('devis_status_id', 3);
                });

            case 'deuxdevis':
                return $query->whereHas('onedevis', function ($q) {
                    $q->where('devis_status_id', 1);
                });

            case 'deuxfacture':
                return $query->whereHas('onefacture', function ($q) {
                    $q->where('devis_status_id', 1);
                });

            case 'rapportasigner':
                return $query->whereHas('onerapport', function ($q) {
                    $q->where('rapport_status_id', 4);
                });

            case 'traitrapport':
                return $query->whereHas('onerapport', function ($q) {
                    $q->where('rapport_status_id', 5);
                });

           case 'sendrapport':
    
    return Dossier::where('dossier_status_id', 2)
        ->whereHas('onerapport', function ($q) {
            $q->where('rapport_status_id', 2);
        });

            case 'deuxrapport':
                return $query->whereHas('onerapport', function ($q) {
                    $q->where('rapport_status_id', 1);
                });

            case 'docasigner':
                return $query->whereHas('document', function ($q) {
                    $q->where('type_document_id', 21)->where('signer', 0)->where('rejected', 0);
                });

            case 'docsigner':
                return $query->whereDoesntHave('onerapport', function ($q) {
                    $q->whereIn('rapport_status_id', [2, 3]);
                })->whereHas('document', function ($q) {
                    $q->where('type_document_id', 21)->where('signer', 1)->where('rejected', 0);
                });

            case 'douteux':
                return $query->where('sinistre_douteux', 1);

            case 'tribunale':
                return $query->where('type_expertise_id', 9);

            case 'rapide':
                $q = $query->where('type_expertise_id', 16);
                if (Auth::user()->role_id == 2) {
                    $q->whereIn('step_id', [4, 9]);
                }
                return $q;

            case 'basedoc':
                return $query->whereDoesntHave('document.typedocument', function ($q) {
                    $q->whereIn('type_document_id', [1, 2, 3]);
                });

            case 'facturation':
                return Dossier::whereNull('facture')
                    ->where('dossier_status_id', 2)
                    ->where('update', 1);

            case 'attexpap':
                return $query->where('type_expertise_id', '!=', 14)
                    ->where('mode_id', '!=', 7)
                    ->whereDoesntHave('document', function ($q) {
                        $q->where('type_document_id', 6);
                    })
                    ->whereHas('onedevis', function ($q) {
                        $q->where('devis_status_id', 2)->orWhere('devis_status_id', 7);
                    })->whereDoesntHave('facture');

            case 'attdevisav':
                return $query->whereHas('onedevis', function ($q) {
                    $q->where('devis_status_id', 9);
                });

            case 'attfactureap':
                return $query->whereHas('onefacture', function ($q) {
                    $q->where('devis_status_id', 9);
                });

            case 'controldevis':
                return $this->getControlDevisQuery();

            case 'rapportaretraiter':
                return $query->whereHas('onerapport', function ($q) {
                    $q->where('rapport_status_id', 5);
                });

            case 'areport':
                return $query->whereHas('onemeeting', function ($q) {
                    $q->where('meeting_status_id', 1);
                });

            case 'warning':
                return $query;

            case 'fasigner':
                return Dossier::where('dossier_status_id', 2)
                    ->where('update', 0)
                    ->whereHas('document', function ($q) {
                        $q->where('type_document_id', 16)->where('signer', 0);
                    });

            case 'devisentraitement':
                return $query->whereHas('onedevis', function ($q) {
                    $q->whereIn('devis_status_id', [2, 4, 7]);
                });

            case 'factureentraitement':
                return $query->whereHas('onefacture', function ($q) {
                    $q->whereIn('devis_status_id', [2, 4, 7]);
                });

            default:
                return $query;
        }
    }

    /**
     * Get devis retraiter query with seuil filtering
     */
    private function getDevisRetraiterQuery()
    {
        $query = $this->baseQuery()->whereHas('onedevis', function ($q) {
            $q->where('devis_status_id', 5);
        });

        if (Auth::user()->role_id == 2) {
            $seuils = Seuil::where('user_id', Auth::user()->id)->get()->keyBy('company_id');
            $companyIds = $seuils->keys()->toArray();
            
            if (!empty($companyIds)) {
                $query->whereIn('company_id', $companyIds);
            }
        }

        return $query;
    }

    /**
     * Get facture retraiter query with seuil filtering
     */
    private function getFactureRetraiterQuery()
    {
        $query = $this->baseQuery()->whereHas('onefacture', function ($q) {
            $q->where('devis_status_id', 5);
        });

        if (Auth::user()->role_id == 2) {
            $seuils = Seuil::where('user_id', Auth::user()->id)->get()->keyBy('company_id');
            $companyIds = $seuils->keys()->toArray();
            
            if (!empty($companyIds)) {
                $query->whereIn('company_id', $companyIds);
            }
        }

        return $query;
    }

    /**
     * Get trait devis query
     */
    private function getTraitDevisQuery()
    {
        return $this->baseQuery()->whereHas('devis', function ($q) {
            $q->where('devis_status_id', 4);
        });
    }

    /**
     * Get control devis query
     */
    private function getControlDevisQuery()
    {
        $query = $this->baseQuery()->whereHas('onedevis', function ($q) {
            $q->where('devis_status_id', 8);
        });

        if (Auth::user()->role_id == 2) {
            $seuils = Seuil::where('user_id', Auth::user()->id)->get()->keyBy('company_id');
            $companyIds = $seuils->keys()->toArray();
            
            if (!empty($companyIds)) {
                $query->whereIn('company_id', $companyIds);
            }
        }

        return $query;
    }

    /**
     * Clear sidebar cache
     */
    public function clearSidebarCache()
    {
        $userId = Auth::user()->id;
        $roleId = Auth::user()->role_id;
        Cache::forget("sidebar_counts_{$userId}_{$roleId}");
    }

    /**
     * AJAX endpoint to refresh sidebar counts
     */
    public function refreshSidebarCounts()
    {
        $this->clearSidebarCache();
        return response()->json($this->getSidebarCounts());
    }

    // ============================================
    // Legacy AJAX endpoints
    // ============================================

    public function aexpertiser()
    {
        $this->authorize('attphav', Dashboard::class);
        return $this->getLegacyFilteredResponse('expertiser');
    }

    public function carencedevis()
    {
        $this->authorize('attdevis', Dashboard::class);
        return $this->getLegacyFilteredResponse('carencedevis');
    }

    public function carencefacture()
    {
        $this->authorize('attfacture', Dashboard::class);
        return $this->getLegacyFilteredResponse('carencefacture');
    }

    public function carencerapport()
    {
        $this->authorize('attrapport', Dashboard::class);
        return $this->getLegacyFilteredResponse('carencerapport');
    }

    public function devisaretraiter()
    {
        $this->authorize('retraitdevis', Dashboard::class);
        return $this->getLegacyFilteredResponse('devisretraiter');
    }

    public function facturearetraiter()
    {
        $this->authorize('retraitdevis', Dashboard::class);
        return $this->getLegacyFilteredResponse('factureretraiter');
    }

    public function traitdevis()
    {
        $this->authorize('traitdevis', Dashboard::class);
        return $this->getLegacyFilteredResponse('traitdevis');
    }

    public function traitfacture()
    {
        $this->authorize('traitfacture', Dashboard::class);
        return $this->getLegacyFilteredResponse('traitfacture');
    }

    public function rapportasigner()
    {
        $this->authorize('rappsigner', Dashboard::class);
        return $this->getLegacyFilteredResponse('rapportasigner');
    }

    public function traitrapport()
    {
        $this->authorize('instacerapport', Dashboard::class);
        return $this->getLegacyFilteredResponse('traitrapport');
    }

    public function senddevis()
    {
        $this->authorize('devisacc', Dashboard::class);
        return $this->getLegacyFilteredResponse('senddevis');
    }

    public function sendfacture()
    {
        $this->authorize('factureacc', Dashboard::class);
        return $this->getLegacyFilteredResponse('sendfacture');
    }

    public function sendrapport()
    {
        $this->authorize('rapportenv', Dashboard::class);
        return $this->getLegacyFilteredResponse('sendrapport');
    }

    public function avisdevis()
    {
        $this->authorize('avisdevis', Dashboard::class);
        return $this->getLegacyFilteredResponse('avisdevis');
    }

    public function avisfacture()
    {
        $this->authorize('avisfacture', Dashboard::class);
        return $this->getLegacyFilteredResponse('avisfacture');
    }

    public function deuxdevis()
    {
        $this->authorize('att2emedevis', Dashboard::class);
        return $this->getLegacyFilteredResponse('deuxdevis');
    }

    public function deuxfacture()
    {
        $this->authorize('att2emefacture', Dashboard::class);
        return $this->getLegacyFilteredResponse('deuxfacture');
    }

    public function deuxrapport()
    {
        $this->authorize('att2emerapport', Dashboard::class);
        return $this->getLegacyFilteredResponse('deuxrapport');
    }

    public function douteux()
    {
        $this->authorize('douteux', Dashboard::class);
        return $this->getLegacyFilteredResponse('douteux');
    }

    public function tribunale()
    {
        $this->authorize('tribunale', Dashboard::class);
        return $this->getLegacyFilteredResponse('tribunale');
    }

    public function docasigner()
    {
        $this->authorize('docasigner', Dashboard::class);
        return $this->getLegacyFilteredResponse('docasigner');
    }

    public function docsigner()
    {
        $this->authorize('docasigner', Dashboard::class);
        return $this->getLegacyFilteredResponse('docsigner');
    }

    public function rapide()
    {
        $this->authorize('rapide', Dashboard::class);
        return $this->getLegacyFilteredResponse('rapide');
    }

    public function basedoc()
    {
        $this->authorize('attdocbase', Dashboard::class);
        return $this->getLegacyFilteredResponse('basedoc');
    }

    public function facturation()
    {
        $this->authorize('afacturé', Dashboard::class);
        return $this->getLegacyFilteredResponse('facturation');
    }

    public function devisentraitement()
    {
        $this->authorize('devisattacc', Dashboard::class);
        return $this->getLegacyFilteredResponse('traitdevis');
    }

    public function factureentraitement()
    {
        $this->authorize('factureattacc', Dashboard::class);
        return $this->getLegacyFilteredResponse('traitfacture');
    }

    public function attdevisav()
    {
        $this->authorize('devisattpa', Dashboard::class);
        return $this->getLegacyFilteredResponse('attdevisav');
    }

    public function attfactureap()
    {
        $this->authorize('factureattph', Dashboard::class);
        return $this->getLegacyFilteredResponse('attfactureap');
    }

    public function enexp()
    {
        $this->authorize('enexp', Dashboard::class);
        return $this->getLegacyFilteredResponse('enexp');
    }

    public function attexpap()
    {
        $this->authorize('attphap', Dashboard::class);
        return $this->getLegacyFilteredResponse('attexpap');
    }

    public function controldevis()
    {
        $this->authorize('controldevis', Dashboard::class);
        return $this->getLegacyFilteredResponse('controldevis');
    }

    public function rapportaretraiter()
    {
        $this->authorize('instacerapport', Dashboard::class);
        return $this->getLegacyFilteredResponse('rapportaretraiter');
    }

    public function areport()
    {
        $this->authorize('attphav', Dashboard::class);
        return $this->getLegacyFilteredResponse('areport');
    }

    public function warning()
    {
        $this->authorize('attphav', Dashboard::class);
        return $this->getLegacyFilteredResponse('warning');
    }

    public function fasigner()
    {
        $this->authorize('afacturé', Dashboard::class);
        return $this->getLegacyFilteredResponse('fasigner');
    }

    /**
     * Get legacy filtered response with timeleft calculation
     */
    private function getLegacyFilteredResponse($filter)
    {
        $dossiers = $this->getFilteredQuery($filter)
            ->with(['company', 'mode', 'onefacture', 'onedevis', 'currentStep', 'insured', 'typeexpertise', 'status'])
            ->get();

        $dossiers = $this->calculateTimeleft($dossiers);

        return response()->json($dossiers);
    }
}