<?php

namespace App\Http\Controllers;

use App\Mail\WeeklyTimesheetReport;
use App\Models\ActivityLog;
use App\Models\CheckMeasure;
use App\Models\Delivery;
use App\Models\Job;
use App\Models\Lead;
use App\Models\MaterialType;
use App\Models\Quote;
use App\Models\QuoteItem;
use App\Models\ScheduledReport;
use App\Models\TimeEntry;
use App\Models\User;
use App\Models\WorksOrder;
use Barryvdh\DomPDF\Facade\Pdf;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Mail;

class ReportController extends Controller
{
    private const GROUP_OPTIONS = ['stage', 'source', 'project_type', 'status'];
    private const STAGES = ['lead','consult','sold','contracts','cm','processing','delivery','installation','completion'];

    public function index(Request $request)
    {
        $from    = $request->from ? Carbon::parse($request->from)->startOfDay()  : Carbon::now()->subMonths(12)->startOfDay();
        $to      = $request->to   ? Carbon::parse($request->to)->endOfDay()      : Carbon::now()->endOfDay();
        $groupBy = in_array($request->group_by, self::GROUP_OPTIONS) ? $request->group_by : 'stage';
        $stageFilter = in_array($request->stage_filter, self::STAGES) ? $request->stage_filter : null;
        $pmFilter = ($request->filled('pm_filter') && (int) $request->pm_filter > 0) ? (int) $request->pm_filter : null;

        $baseQuery = Job::whereBetween('created_at', [$from, $to]);
        if ($stageFilter) $baseQuery->where('stage', $stageFilter);
        if ($pmFilter)    $baseQuery->where('manager_id', $pmFilter);
        $jobQuery = clone $baseQuery;

        $totalRevenue  = (clone $jobQuery)->where('status', 'completed')->sum('job_value');
        $totalJobs     = (clone $jobQuery)->count();
        $completedJobs = (clone $jobQuery)->where('status', 'completed')->count();
        $avgJobValue   = $completedJobs > 0 ? $totalRevenue / $completedJobs : 0;
        $totalLeads    = Lead::whereBetween('created_at', [$from, $to])->count();
        $conversionRate = $totalLeads > 0 ? round(($totalJobs / $totalLeads) * 100, 1) : 0;

        $pipelineQuery = Job::whereBetween('created_at', [$from, $to]);
        if ($pmFilter) $pipelineQuery->where('manager_id', $pmFilter);
        $jobsByStage = [];
        foreach (self::STAGES as $stage) {
            $jobsByStage[$stage] = (clone $pipelineQuery)->where('stage', $stage)->count();
        }

        $groupLabels = ['stage'=>'Pipeline Stage','source'=>'Lead Source','project_type'=>'Project Type','status'=>'Job Status'];
        $groupedData = (clone $jobQuery)
            ->selectRaw("{$groupBy} as grp_key, COUNT(*) as cnt, COALESCE(SUM(job_value),0) as revenue")
            ->groupBy('grp_key')->orderByDesc('cnt')->get();

        $pmStats = User::all()->map(fn($u) => [
            'id'          => $u->id,
            'name'        => $u->name,
            'total'       => (clone $jobQuery)->where('manager_id', $u->id)->count(),
            'completed'   => (clone $jobQuery)->where('manager_id', $u->id)->where('status', 'completed')->count(),
            'in_progress' => (clone $jobQuery)->where('manager_id', $u->id)->where('status', 'active')->count(),
            'revenue'     => (float)(clone $jobQuery)->where('manager_id', $u->id)->where('status', 'completed')->sum('job_value'),
        ])->filter(fn($pm) => $pm['total'] > 0)->values();

        $revenueByMonth = [];
        $monthSpan = (int) $from->diffInMonths($to) + 1;
        $step = max(1, (int) ceil($monthSpan / 12));
        for ($i = 0; $i < $monthSpan; $i += $step) {
            $month = $from->copy()->addMonths($i);
            $revenueByMonth[] = [
                'month'   => $month->format('M Y'),
                'revenue' => (clone $jobQuery)->whereMonth('created_at', $month->month)->whereYear('created_at', $month->year)->sum('job_value'),
                'count'   => (clone $jobQuery)->whereMonth('created_at', $month->month)->whereYear('created_at', $month->year)->count(),
            ];
        }

        $quotesTotal    = Quote::whereBetween('created_at', [$from, $to])->count();
        $quotesSent     = Quote::whereBetween('created_at', [$from, $to])->where('status', '!=', 'draft')->count();
        $quotesAccepted = Quote::whereBetween('created_at', [$from, $to])->whereIn('status', ['accepted','converted'])->count();

        $leadsBySource = Lead::whereBetween('created_at', [$from, $to])
            ->selectRaw('source, COUNT(*) as cnt')->whereNotNull('source')
            ->groupBy('source')->orderByDesc('cnt')->get()->pluck('cnt', 'source')->toArray();

        $scheduledReports = ScheduledReport::with('creator')->latest()->get();
        $projectManagers  = User::whereHas('role', fn($q) => $q->whereIn('name', ['office_admin','project_manager']))->orderBy('name')->get();

        if ($request->export === 'csv') return $this->exportCsv($from, $to, $jobQuery);

        return view('reports.index', compact(
            'jobsByStage', 'totalRevenue', 'totalJobs', 'completedJobs', 'avgJobValue',
            'totalLeads', 'conversionRate', 'pmStats', 'revenueByMonth',
            'groupedData', 'groupBy', 'groupLabels', 'from', 'to',
            'quotesTotal', 'quotesSent', 'quotesAccepted', 'leadsBySource',
            'scheduledReports', 'projectManagers', 'stageFilter', 'pmFilter'
        ));
    }

    // ── Gross Profit Report (original job-level summary) ─────────────────────

    public function grossProfit(Request $request)
    {
        $from        = $request->from ? Carbon::parse($request->from)->startOfDay()  : Carbon::now()->subMonths(12)->startOfDay();
        $to          = $request->to   ? Carbon::parse($request->to)->endOfDay()      : Carbon::now()->endOfDay();
        $pmFilter    = ($request->filled('pm_filter') && (int) $request->pm_filter > 0) ? (int) $request->pm_filter : null;
        $stageFilter = in_array($request->stage_filter, self::STAGES) ? $request->stage_filter : null;
        $sortBy      = in_array($request->sort_by, ['job_number','revenue','material_cost','labour_cost','gross_profit','gross_margin']) ? $request->sort_by : 'gross_profit';
        $sortDir      = $request->sort_dir === 'asc' ? 'asc' : 'desc';
        $jobNumber    = $request->filled('job_number') ? trim($request->job_number) : null;

        // Roles that need invoice approval before counting in GP
        $approvalRoles = ['trades'];
        // Roles that count directly (no invoice approval needed)
        $directRoles = ['processor', 'factory_employee', 'lead_installer'];

        $jobsQuery = Job::with([
                'contact','consultant','manager','productionOrders','jobInvoices',
                'timeEntries'=>fn($q)=>$q->where('status','completed')->with(['user.role'])
            ])->whereBetween('created_at', [$from, $to]);
        if ($pmFilter)    $jobsQuery->where('manager_id', $pmFilter);
        if ($stageFilter) $jobsQuery->where('stage', $stageFilter);
        if ($jobNumber)   $jobsQuery->where('job_number', 'like', '%' . $jobNumber . '%');
        $jobs = $jobsQuery->get();

        $rows = $jobs->map(function (Job $job) use ($approvalRoles, $directRoles) {
            // Revenue: job value minus external invoice amounts (incl GST)
            $invoiceDeductions = $job->jobInvoices->sum(fn($inv) => (float)($inv->invoice_amount ?? 0));
            $revenue      = max(0, (float)($job->job_value ?? 0) - $invoiceDeductions);
            $materialCost = $job->productionOrders->sum(fn($po) => (float)($po->cost ?? 0));

            // Labour cost: trades role needs approved invoice; processors/factory/installers count directly
            $labourCost = $job->timeEntries->sum(function($e) use ($approvalRoles, $directRoles) {
                $roleName = $e->user?->role?->name ?? '';
                $rate = (float)($e->user?->hourly_rate ?? 0);
                $hrs  = $e->duration_minutes / 60;
                if (in_array($roleName, $directRoles)) {
                    // Direct: no invoice approval needed
                    return round($hrs * $rate, 2);
                }
                if (in_array($roleName, $approvalRoles)) {
                    // Trades: only count if invoice approved or paid
                    if ($e->invoiced_in && $e->invoice && in_array($e->invoice->status, ['approved','paid'])) {
                        return round($hrs * $rate, 2);
                    }
                    return 0;
                }
                // Other roles with invoice approval
                if ($e->invoiced_in && $e->invoice && in_array($e->invoice->status, ['approved','paid'])) {
                    return round($hrs * $rate, 2);
                }
                return 0;
            });

            $totalCost    = $materialCost + $labourCost;
            $grossProfit  = $revenue - $totalCost;
            $grossMargin  = $revenue > 0 ? round(($grossProfit/$revenue)*100,1) : 0;
            return [
                'id'=>$job->id,'job_number'=>$job->job_number,'client'=>$job->contact?->full_name??'—',
                'pm'=>$job->manager?->name??'—','stage'=>$job->stage,'status'=>$job->status,
                'created_at'=>$job->created_at->format('d M Y'),
                'revenue'=>$revenue,'material_cost'=>$materialCost,'labour_cost'=>$labourCost,
                'total_cost'=>$totalCost,'gross_profit'=>$grossProfit,'gross_margin'=>$grossMargin,
            ];
        });

        $rows = $sortDir==='asc' ? $rows->sortBy($sortBy)->values() : $rows->sortByDesc($sortBy)->values();

        if ($request->export === 'csv') {
            $headers = ['Content-Type'=>'text/csv','Content-Disposition'=>'attachment; filename="gross-profit-'.now()->format('Y-m-d').'.csv"'];
            $callback = function() use ($rows) {
                $fh = fopen('php://output','w');
                fputcsv($fh,['Job #','Client','PM','Stage','Status','Created','Revenue ($)','Materials ($)','Labour ($)','Total Cost ($)','Gross Profit ($)','Gross Margin (%)']);
                foreach ($rows as $r) {
                    fputcsv($fh,[$r['job_number'],$r['client'],$r['pm'],$r['stage'],$r['status'],$r['created_at'],
                        number_format($r['revenue'],2,'.',''),number_format($r['material_cost'],2,'.',''),
                        number_format($r['labour_cost'],2,'.',''),number_format($r['total_cost'],2,'.',''),
                        number_format($r['gross_profit'],2,'.',''),number_format($r['gross_margin'],1,'.','')]);
                }
                fclose($fh);
            };
            return response()->stream($callback, 200, $headers);
        }

        $totalRevenue=$rows->sum('revenue'); $totalMaterials=$rows->sum('material_cost');
        $totalLabour=$rows->sum('labour_cost'); $totalCosts=$rows->sum('total_cost');
        $totalProfit=$rows->sum('gross_profit'); $avgMargin=$rows->filter(fn($r)=>$r['revenue']>0)->avg('gross_margin')??0;
        $profitableJobs=$rows->filter(fn($r)=>$r['gross_profit']>0)->count();
        $unprofitableJobs=$rows->filter(fn($r)=>$r['gross_profit']<0)->count();
        $marginBuckets=['loss'=>$rows->filter(fn($r)=>$r['gross_margin']<0)->count(),'low'=>$rows->filter(fn($r)=>$r['gross_margin']>=0&&$r['gross_margin']<15)->count(),'ok'=>$rows->filter(fn($r)=>$r['gross_margin']>=15&&$r['gross_margin']<30)->count(),'healthy'=>$rows->filter(fn($r)=>$r['gross_margin']>=30)->count()];
        $projectManagers = User::whereHas('role',fn($q)=>$q->whereIn('name',['office_admin','project_manager']))->orderBy('name')->get();

        return view('reports.gross_profit', compact('jobNumber', 
            'rows','from','to','pmFilter','stageFilter','sortBy','sortDir',
            'totalRevenue','totalMaterials','totalLabour','totalCosts',
            'totalProfit','avgMargin','profitableJobs','unprofitableJobs','marginBuckets','projectManagers'
        ));
    }

    // ── Gross Profit Report — per Quote, by material type ────────────────────

    public function grossProfitByQuote(Request $request)
    {
        $quoteId = $request->quote_id;
        $quotes  = Quote::with(['contact','job'])->latest()->get();

        $quote = $quoteId ? Quote::with(['contact','job','sections.items.materialType'])->find($quoteId) : null;

        $typeRows = [];
        $totalWsale  = 0;
        $totalRetail = 0;
        $grossProfit = 0;

        if ($quote) {
            // Group items by material type
            $allItems = $quote->sections->flatMap->items;
            $grouped  = $allItems->groupBy('material_type_id');
            $materialTypes = MaterialType::orderBy('sort_order')->orderBy('name')->get()->keyBy('id');

            foreach ($grouped as $typeId => $items) {
                $typeName    = $typeId ? ($materialTypes[$typeId]->name ?? 'Uncategorised') : 'Uncategorised';
                $typeWsale   = $items->sum(fn($i) => (float)($i->total_wsale ?? ($i->qty * $i->wsale_price)));
                $typeRetail  = $items->sum(fn($i) => (float)($i->total_retail ?? ($i->qty * $i->retail_price)));
                $typeProfit  = $typeRetail - $typeWsale;
                $typeRows[]  = [
                    'type_name'   => $typeName,
                    'items'       => $items,
                    'total_wsale' => $typeWsale,
                    'total_retail'=> $typeRetail,
                    'gross_profit'=> $typeProfit,
                    'margin_pct'  => $typeRetail > 0 ? round(($typeProfit/$typeRetail)*100,1) : 0,
                ];
                $totalWsale  += $typeWsale;
                $totalRetail += $typeRetail;
                $grossProfit += $typeProfit;
            }
        }

        $marginPct = $totalRetail > 0 ? round(($grossProfit / $totalRetail) * 100, 1) : 0;

        return view('reports.gross_profit_by_quote', compact(
            'quotes','quote','typeRows','totalWsale','totalRetail','grossProfit','marginPct'
        ));
    }

    // ── Client Summary Report ─────────────────────────────────────────────────

    public function clientSummary(Request $request)
    {
        $quoteId = $request->quote_id;
        $quotes  = Quote::with(['contact','job'])->latest()->get();
        $quote   = $quoteId ? Quote::with(['contact','job','sections.items.materialType'])->find($quoteId) : null;

        $summaryRows  = [];
        $totalRetail  = 0;
        $totalWsale   = 0;

        if ($quote) {
            $allItems     = $quote->sections->flatMap->items;
            $grouped      = $allItems->groupBy('material_type_id');
            $materialTypes = MaterialType::orderBy('sort_order')->orderBy('name')->get()->keyBy('id');

            foreach ($grouped as $typeId => $items) {
                $typeName   = $typeId ? ($materialTypes[$typeId]->name ?? 'Uncategorised') : 'Uncategorised';
                $typeRetail = $items->sum(fn($i) => (float)($i->total_retail ?? ($i->qty * $i->retail_price)));
                $typeWsale  = $items->sum(fn($i) => (float)($i->total_wsale  ?? ($i->qty * $i->wsale_price)));
                $summaryRows[] = ['type_name'=>$typeName,'total_retail'=>$typeRetail,'total_wsale'=>$typeWsale];
                $totalRetail += $typeRetail;
                $totalWsale  += $typeWsale;
            }
        }

        $gst          = $totalRetail * 0.10;
        $totalInclGst = $totalRetail + $gst;
        $grossProfit  = $totalRetail - $totalWsale;
        $marginPct    = $totalRetail > 0 ? round(($grossProfit / $totalRetail) * 100, 1) : 0;

        return view('reports.client_summary', compact(
            'quotes','quote','summaryRows','totalRetail','totalWsale',
            'gst','totalInclGst','grossProfit','marginPct'
        ));
    }

    // ── Weekly Timesheet Report ───────────────────────────────────────────────

    public function timesheetWeekly(Request $request)
    {
        $weekStart = $request->filled('week_start')
            ? Carbon::parse($request->week_start)->startOfWeek(Carbon::MONDAY)->startOfDay()
            : Carbon::now()->startOfWeek(Carbon::MONDAY)->startOfDay();
        $weekEnd = $weekStart->copy()->endOfWeek(Carbon::SUNDAY)->endOfDay();

        $hourlyStaff = User::with('role')->whereNotNull('hourly_rate')->where('hourly_rate','>',0)->where('is_active',true)->orderBy('name')->get();
        $staffSummaries=[]; $totalHours=0; $totalEarnings=0;
        foreach ($hourlyStaff as $user) {
            $entries  = TimeEntry::where('user_id',$user->id)->where('status','completed')->whereBetween('started_at',[$weekStart,$weekEnd])->get();
            $hours    = round($entries->sum('duration_minutes')/60,2);
            $earnings = round($hours*(float)$user->hourly_rate,2);
            $staffSummaries[]=['name'=>$user->name,'role'=>$user->role?->display_name??'Staff','email'=>$user->email,'rate'=>(float)$user->hourly_rate,'entries'=>$entries->count(),'hours'=>$hours,'earnings'=>$earnings,'invoiced_hours'=>round($entries->whereNotNull('invoiced_in')->sum('duration_minutes')/60,2)];
            $totalHours+=$hours; $totalEarnings+=$earnings;
        }

        $dailyBreakdown=[];
        for ($i=0;$i<7;$i++) {
            $day=$weekStart->copy()->addDays($i);
            $mins=TimeEntry::where('status','completed')->whereDate('started_at',$day->toDateString())->sum('duration_minutes');
            $dailyBreakdown[]=['label'=>$day->format('D'),'date'=>$day->toDateString(),'hours'=>round($mins/60,2)];
        }

        $reportHistory=ActivityLog::where('action','Weekly Report Sent')->orWhere('action','Weekly Report Emailed')->latest()->take(5)->get();

        return view('reports.timesheet_weekly', compact('staffSummaries','totalHours','totalEarnings','weekStart','weekEnd','dailyBreakdown','reportHistory'));
    }

    public function timesheetWeeklySend(Request $request)
    {
        $weekStart=$request->filled('week_start')?Carbon::parse($request->week_start)->startOfWeek(Carbon::MONDAY)->startOfDay():Carbon::now()->startOfWeek(Carbon::MONDAY)->startOfDay();
        $weekEnd=$weekStart->copy()->endOfWeek(Carbon::SUNDAY)->endOfDay();
        $hourlyStaff=User::with('role')->whereNotNull('hourly_rate')->where('hourly_rate','>',0)->where('is_active',true)->orderBy('name')->get();
        $staffSummaries=[];$totalHours=0;$totalEarnings=0;
        foreach ($hourlyStaff as $user) {
            $entries=TimeEntry::where('user_id',$user->id)->where('status','completed')->whereBetween('started_at',[$weekStart,$weekEnd])->get();
            $hours=round($entries->sum('duration_minutes')/60,2);$earnings=round($hours*(float)$user->hourly_rate,2);
            $staffSummaries[]=['name'=>$user->name,'role'=>$user->role?->display_name??'Staff','email'=>$user->email,'rate'=>(float)$user->hourly_rate,'entries'=>$entries->count(),'hours'=>$hours,'earnings'=>$earnings,'invoiced_hours'=>round($entries->whereNotNull('invoiced_in')->sum('duration_minutes')/60,2)];
            $totalHours+=$hours;$totalEarnings+=$earnings;
        }
        $recipients=User::with('role')->whereHas('role',fn($q)=>$q->whereIn('name',['office_admin','accounts']))->where('is_active',true)->get();
        $mailable=new WeeklyTimesheetReport(staffSummaries:$staffSummaries,totalHours:round($totalHours,2),totalEarnings:round($totalEarnings,2),weekStart:$weekStart,weekEnd:$weekEnd,generatedBy:auth()->user()->name);
        $sent=0;
        foreach ($recipients as $rec) { try { Mail::to($rec->email,$rec->name)->send($mailable);$sent++; } catch(\Exception){} }
        $period=$weekStart->format('d M').'–'.$weekEnd->format('d M Y');
        ActivityLog::create(['user_id'=>auth()->id(),'loggable_type'=>'WeeklyTimesheetReport','loggable_id'=>0,'action'=>'Weekly Report Emailed','description'=>auth()->user()->name." sent weekly timesheet report ({$period}) to {$sent} recipient(s). Total: ".round($totalHours,2)."h / \$".number_format(round($totalEarnings,2))]);
        $msg=$sent>0?"Report emailed to {$sent} admin/accounts recipient(s).":"No admin or accounts recipients found.";
        return redirect()->route('reports.timesheet',['week_start'=>$weekStart->toDateString()])->with($sent>0?'success':'error',$msg);
    }

    public function timesheetWeeklyExport(Request $request)
    {
        $weekStart=$request->filled('week_start')?Carbon::parse($request->week_start)->startOfWeek(Carbon::MONDAY)->startOfDay():Carbon::now()->startOfWeek(Carbon::MONDAY)->startOfDay();
        $weekEnd=$weekStart->copy()->endOfWeek(Carbon::SUNDAY)->endOfDay();
        $hourlyStaff=User::with('role')->whereNotNull('hourly_rate')->where('hourly_rate','>',0)->where('is_active',true)->orderBy('name')->get();
        $headers=['Content-Type'=>'text/csv','Content-Disposition'=>'attachment; filename="timesheet-'.$weekStart->format('Y-m-d').'.csv"'];
        $callback=function() use ($hourlyStaff,$weekStart,$weekEnd) {
            $fh=fopen('php://output','w');
            fputcsv($fh,['Name','Role','Rate ($/hr)','Entries','Hours','Earnings ($)','Invoiced Hours','Uninvoiced Hours']);
            foreach ($hourlyStaff as $user) {
                $entries=TimeEntry::where('user_id',$user->id)->where('status','completed')->whereBetween('started_at',[$weekStart,$weekEnd])->get();
                $hours=round($entries->sum('duration_minutes')/60,2);$earnings=round($hours*(float)$user->hourly_rate,2);$invHours=round($entries->whereNotNull('invoiced_in')->sum('duration_minutes')/60,2);
                fputcsv($fh,[$user->name,$user->role?->display_name??'',number_format($user->hourly_rate,2),$entries->count(),number_format($hours,2),number_format($earnings,2),number_format($invHours,2),number_format(max(0,$hours-$invHours),2)]);
            }
            fclose($fh);
        };
        return response()->stream($callback,200,$headers);
    }

    public function storeScheduled(Request $request)
    {
        $data=$request->validate(['name'=>'required|string|max:255','report_type'=>'required|string|in:pipeline,revenue,leads,quotes,pm_performance,consultant','frequency'=>'required|string|in:daily,weekly,monthly']);
        $data['created_by']=auth()->id();$data['is_active']=true;
        ScheduledReport::create($data);
        return back()->with('success',"Scheduled report '{$data['name']}' created.");
    }

    public function toggleScheduled(ScheduledReport $scheduledReport)
    {
        $scheduledReport->update(['is_active'=>!$scheduledReport->is_active]);
        return back()->with('success',"Report '{$scheduledReport->name}' ".($scheduledReport->is_active?'activated':'paused').'.');
    }

    public function destroyScheduled(ScheduledReport $scheduledReport)
    {
        $name=$scheduledReport->name;$scheduledReport->delete();
        return back()->with('success',"Scheduled report '{$name}' deleted.");
    }

    // ── Report 1: Leads by Showroom ───────────────────────────────────────────

    private function buildLeadsByShowroomData(Request $request): array
    {
        $from = $request->filled('from') ? $request->from : null;
        $to   = $request->filled('to')   ? $request->to   : null;

        $query = Lead::with(['contact', 'showroom', 'brand', 'jobs.designer', 'jobs.consultant']);
        if ($from) $query->whereDate('enquiry_date', '>=', $from);
        if ($to)   $query->whereDate('enquiry_date', '<=', $to);
        $leads   = $query->orderBy('enquiry_date')->get();
        $grouped = $leads->groupBy(fn($l) => $l->showroom?->name ?? 'No Showroom');
        return compact('from', 'to', 'grouped');
    }

    public function leadsByShowroom(Request $request)
    {
        return view('reports.leads_by_showroom', $this->buildLeadsByShowroomData($request));
    }

    public function leadsByShowroomPdf(Request $request)
    {
        $data = $this->buildLeadsByShowroomData($request);
        return Pdf::loadView('reports.pdf.leads_by_showroom', $data)
            ->setPaper('a4', 'landscape')
            ->download('leads-by-showroom-' . now()->format('Y-m-d') . '.pdf');
    }

    // ── Report 2: Leads by Designer ───────────────────────────────────────────

    private function buildLeadsByDesignerData(Request $request): array
    {
        $from = $request->filled('from') ? $request->from : null;
        $to   = $request->filled('to')   ? $request->to   : null;

        $query = Job::with(['contact', 'lead.showroom', 'lead.brand', 'designer', 'consultant'])
                    ->whereNotNull('designer_id');
        if ($from) $query->whereDate('sale_date', '>=', $from);
        if ($to)   $query->whereDate('sale_date', '<=', $to);
        $jobs    = $query->orderBy('sale_date')->get();
        $grouped = $jobs->groupBy(fn($j) => $j->designer?->name ?? 'Unassigned');
        return compact('from', 'to', 'grouped');
    }

    public function leadsByDesigner(Request $request)
    {
        return view('reports.leads_by_designer', $this->buildLeadsByDesignerData($request));
    }

    public function leadsByDesignerPdf(Request $request)
    {
        $data = $this->buildLeadsByDesignerData($request);
        return Pdf::loadView('reports.pdf.leads_by_designer', $data)
            ->setPaper('a4', 'landscape')
            ->download('leads-by-designer-' . now()->format('Y-m-d') . '.pdf');
    }

    // ── Report 3: Check Measure by Scheduled Date ─────────────────────────────

    private function buildCheckMeasureData(Request $request): array
    {
        $from = $request->filled('from') ? $request->from : null;
        $to   = $request->filled('to')   ? $request->to   : null;

        $query = CheckMeasure::with(['job.contact', 'job.lead.brand', 'measurer'])
                    ->orderBy('scheduled_date')
                    ->orderBy('arrival_time');
        if ($from) $query->whereDate('scheduled_date', '>=', $from);
        if ($to)   $query->whereDate('scheduled_date', '<=', $to);
        $records = $query->get();
        $byDate  = $records->groupBy(fn($r) => $r->scheduled_date?->format('Y-m-d') ?? 'No Date')
                           ->map(fn($dateGroup) => $dateGroup->groupBy(fn($r) => $r->measurer?->name ?? 'Unassigned'));
        return compact('from', 'to', 'byDate');
    }

    public function checkMeasureReport(Request $request)
    {
        return view('reports.check_measure_report', $this->buildCheckMeasureData($request));
    }

    public function checkMeasurePdf(Request $request)
    {
        $data = $this->buildCheckMeasureData($request);
        return Pdf::loadView('reports.pdf.check_measure', $data)
            ->setPaper('a4', 'landscape')
            ->download('check-measure-' . now()->format('Y-m-d') . '.pdf');
    }

    // ── Report 4: Manufacture Schedule by Date ────────────────────────────────

    private function buildManufactureScheduleData(Request $request): array
    {
        $from          = $request->filled('from') ? $request->from : null;
        $to            = $request->filled('to')   ? $request->to   : null;
        $selectedJobId = $request->filled('job_id') ? (int) $request->job_id : null;

        $query = CheckMeasure::with(['job.contact', 'job.lead.brand', 'measurer'])
                    ->whereNotNull('manufacture_date')
                    ->orderBy('manufacture_date');
        if ($from)          $query->whereDate('manufacture_date', '>=', $from);
        if ($to)            $query->whereDate('manufacture_date', '<=', $to);
        if ($selectedJobId) $query->where('job_id', $selectedJobId);
        $records = $query->get();
        $byDate  = $records->groupBy(fn($r) => $r->manufacture_date?->format('Y-m-d') ?? 'No Date');

        $jobs = Job::with('contact')->orderBy('job_number')->get();
        return compact('from', 'to', 'byDate', 'jobs', 'selectedJobId');
    }

    public function manufactureSchedule(Request $request)
    {
        return view('reports.manufacture_schedule', $this->buildManufactureScheduleData($request));
    }

    public function manufactureSchedulePdf(Request $request)
    {
        $data = $this->buildManufactureScheduleData($request);
        return Pdf::loadView('reports.pdf.manufacture_schedule', $data)
            ->setPaper('a4', 'landscape')
            ->download('manufacture-schedule-' . now()->format('Y-m-d') . '.pdf');
    }

    // ── Report 5: Manufacture Schedule Brand-wise ─────────────────────────────

    private function buildManufactureScheduleBrandData(Request $request): array
    {
        $from = $request->filled('from') ? $request->from : null;
        $to   = $request->filled('to')   ? $request->to   : null;

        $query = CheckMeasure::with(['job.contact', 'job.lead.brand', 'measurer'])
                    ->whereNotNull('manufacture_date')
                    ->orderBy('manufacture_date');
        if ($from) $query->whereDate('manufacture_date', '>=', $from);
        if ($to)   $query->whereDate('manufacture_date', '<=', $to);
        $records = $query->get();
        $byBrand = $records->groupBy(fn($r) => $r->job?->contact?->leads->first()?->brand?->name ?? 'No Brand');
        return compact('from', 'to', 'byBrand');
    }

    public function manufactureScheduleBrand(Request $request)
    {
        return view('reports.manufacture_schedule_brand', $this->buildManufactureScheduleBrandData($request));
    }

    public function manufactureScheduleBrandPdf(Request $request)
    {
        $data = $this->buildManufactureScheduleBrandData($request);
        return Pdf::loadView('reports.pdf.manufacture_schedule_brand', $data)
            ->setPaper('a4', 'landscape')
            ->download('manufacture-schedule-brand-' . now()->format('Y-m-d') . '.pdf');
    }

    // ── Report 6: Works Order Report ──────────────────────────────────────────

    private function buildWorksOrderData(Request $request): array
    {
        $from          = $request->filled('from') ? $request->from : null;
        $to            = $request->filled('to')   ? $request->to   : null;
        $selectedJobId = $request->filled('job_id') ? (int) $request->job_id : null;

        $query = WorksOrder::with(['job.contact', 'items.assignee', 'faults', 'supervisor', 'issuer'])
                    ->orderBy('issued_date');
        if ($from)          $query->whereDate('issued_date', '>=', $from);
        if ($to)            $query->whereDate('issued_date', '<=', $to);
        if ($selectedJobId) $query->where('job_id', $selectedJobId);
        $worksOrders = $query->get();

        $jobs = Job::with('contact')->orderBy('job_number')->get();
        return compact('from', 'to', 'worksOrders', 'jobs', 'selectedJobId');
    }

    public function worksOrderReport(Request $request)
    {
        return view('reports.works_order_report', $this->buildWorksOrderData($request));
    }

    public function worksOrderPdf(Request $request)
    {
        $data = $this->buildWorksOrderData($request);
        return Pdf::loadView('reports.pdf.works_order', $data)
            ->setPaper('a4', 'portrait')
            ->download('works-orders-' . now()->format('Y-m-d') . '.pdf');
    }

    // ─────────────────────────────────────────────────────────────────────────

    private function exportCsv(Carbon $from, Carbon $to, $baseQuery)
    {
        $jobs=(clone $baseQuery)->with(['contact','consultant','manager'])->get();
        $headers=['Content-Type'=>'text/csv','Content-Disposition'=>'attachment; filename="jobs-report-'.now()->format('Y-m-d').'.csv"'];
        $callback=function() use ($jobs) {
            $fh=fopen('php://output','w');
            fputcsv($fh,['Job #','Client','Consultant','Manager','Stage','Status','Project Type','Job Value ($)','Created','Site Suburb']);
            foreach ($jobs as $job) { fputcsv($fh,[$job->job_number,$job->contact?->full_name??'',$job->consultant?->name??'',$job->manager?->name??'',$job->stage,$job->status,$job->project_type??'',number_format($job->job_value??0,2,'.',''),$job->created_at->format('Y-m-d'),$job->site_suburb??'']); }
            fclose($fh);
        };
        return response()->stream($callback,200,$headers);
    }

     public function salesByBrand(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = \App\Models\Job::with([
            'lead.brand',
            'lead.showroom',
            'contact',
            'consultant',
            'designer',
        ])->whereNotNull('sale_date')->orderBy('sale_date');

        if ($from) $query->whereDate('sale_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('sale_date', '<=', Carbon::parse($to)->endOfDay());

        $jobs    = $query->get();
        $byBrand = $jobs->groupBy(fn($job) => $job->contact?->leads->first()?->brand?->name ?? 'Unassigned')
                        ->sortKeys();

        return view('reports.sales_by_brand', compact('byBrand', 'from', 'to'));
    }

    public function salesByBrandPdf(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = \App\Models\Job::with([
            'lead.brand',
            'lead.showroom',
            'contact',
            'consultant',
            'designer',
        ])->whereNotNull('sale_date')->orderBy('sale_date');

        if ($from) $query->whereDate('sale_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('sale_date', '<=', Carbon::parse($to)->endOfDay());

        $jobs    = $query->get();
        $byBrand = $jobs->groupBy(fn($job) => $job->contact?->leads->first()?->brand?->name ?? 'Unassigned')
                        ->sortKeys();

        $pdf = Pdf::loadView('reports.pdf.sales_by_brand', compact('byBrand', 'from', 'to'))
            ->setPaper('a4', 'landscape');

        return $pdf->download('sales-by-brand-' . now()->format('Y-m-d') . '.pdf');
    }

     public function completedKitchens(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = \App\Models\Completion::with([
            'job.lead.brand',
            'job.contact',
            'job.designer',
            'job.manager',
            'job.deliveries',
            'completedBy',
        ])->orderBy('completion_date');

        if ($from) $query->whereDate('completion_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('completion_date', '<=', Carbon::parse($to)->endOfDay());

        $completions = $query->get();

        return view('reports.completed_kitchens', compact('completions', 'from', 'to'));
    }

    public function completedKitchensPdf(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = \App\Models\Completion::with([
            'job.lead.brand',
            'job.contact',
            'job.designer',
            'job.manager',
            'job.deliveries',
            'completedBy',
        ])->orderBy('completion_date');

        if ($from) $query->whereDate('completion_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('completion_date', '<=', Carbon::parse($to)->endOfDay());

        $completions = $query->get();

        $pdf = Pdf::loadView('reports.pdf.completed_kitchens', compact('completions', 'from', 'to'))
            ->setPaper('a4', 'landscape');

        return $pdf->download('completed-kitchens-' . now()->format('Y-m-d') . '.pdf');
    }

    public function jobProcessed(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = CheckMeasure::with([
            'job.lead.brand',
            'job.contact',
            'job.designer',
            'measurer',
        ])
        ->whereHas('job', fn($q) => $q->whereIn('stage', ['processing', 'delivery', 'installation', 'completion']))
        ->orderBy('scheduled_date');

        if ($from) $query->whereDate('scheduled_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('scheduled_date', '<=', Carbon::parse($to)->endOfDay());

        $checkMeasures = $query->get();

        return view('reports.job_processed', compact('checkMeasures', 'from', 'to'));
    }

    public function jobProcessedPdf(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = CheckMeasure::with([
            'job.lead.brand',
            'job.contact',
            'job.designer',
            'measurer',
        ])
        ->whereHas('job', fn($q) => $q->whereIn('stage', ['processing', 'delivery', 'installation', 'completion']))
        ->orderBy('scheduled_date');

        if ($from) $query->whereDate('scheduled_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('scheduled_date', '<=', Carbon::parse($to)->endOfDay());

        $checkMeasures = $query->get();

        $pdf = Pdf::loadView('reports.pdf.job_processed', compact('checkMeasures', 'from', 'to'))
            ->setPaper('a4', 'landscape');

        return $pdf->download('jobs-processed-' . now()->format('Y-m-d') . '.pdf');
    }

    // ── Jobs Not Processed ─────────────────────────────────────────────────
    // Jobs that have a check measure but are still in stage 'cm'
    // (check measure done but not yet submitted/moved to processing)

    public function jobNotProcessed(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = CheckMeasure::with([
            'job.lead.brand',
            'job.contact',
            'job.designer',
            'measurer',
        ])
        ->whereHas('job', fn($q) => $q->where('stage', 'cm'))
        ->orderBy('scheduled_date');

        if ($from) $query->whereDate('scheduled_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('scheduled_date', '<=', Carbon::parse($to)->endOfDay());

        $checkMeasures = $query->get();

        return view('reports.job_not_processed', compact('checkMeasures', 'from', 'to'));
    }

    public function jobNotProcessedPdf(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = CheckMeasure::with([
            'job.lead.brand',
            'job.contact',
            'job.designer',
            'measurer',
        ])
        ->whereHas('job', fn($q) => $q->where('stage', 'cm'))
        ->orderBy('scheduled_date');

        if ($from) $query->whereDate('scheduled_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('scheduled_date', '<=', Carbon::parse($to)->endOfDay());

        $checkMeasures = $query->get();

        $pdf = Pdf::loadView('reports.pdf.job_not_processed', compact('checkMeasures', 'from', 'to'))
            ->setPaper('a4', 'landscape');

        return $pdf->download('jobs-not-processed-' . now()->format('Y-m-d') . '.pdf');
    }

     public function deliverySchedule(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = Delivery::with([
            'job.lead.brand',
            'job.contact',
            'job.contract',
            'job.checkMeasure',
        ])
        ->orderBy('scheduled_date')
        ->orderBy('id');

        if ($from) $query->whereDate('scheduled_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('scheduled_date', '<=', Carbon::parse($to)->endOfDay());

        $grouped = $query->get()->groupBy(fn($d) => $d->scheduled_date->format('Y-m-d'));

        return view('reports.delivery_schedule', compact('grouped', 'from', 'to'));
    }

    public function deliverySchedulePdf(Request $request)
    {
        $from = $request->from ?: null;
        $to   = $request->to   ?: null;

        $query = Delivery::with([
            'job.lead.brand',
            'job.contact',
            'job.contract',
            'job.checkMeasure',
        ])
        ->orderBy('scheduled_date')
        ->orderBy('id');

        if ($from) $query->whereDate('scheduled_date', '>=', Carbon::parse($from)->startOfDay());
        if ($to)   $query->whereDate('scheduled_date', '<=', Carbon::parse($to)->endOfDay());

        $grouped = $query->get()->groupBy(fn($d) => $d->scheduled_date->format('Y-m-d'));

        $pdf = Pdf::loadView('reports.pdf.delivery_schedule', compact('grouped', 'from', 'to'))
            ->setPaper('a4', 'landscape');

        return $pdf->download('delivery-schedule-' . now()->format('Y-m-d') . '.pdf');
    }


    /**
     * Employee Task Progress Report — job-wise task completion with percentage.
     */
    public function employeeTaskReport(Request $request)
    {
        $employeeId  = $request->employee_id;
        $jobNumber   = $request->job_number;
        $taskStatus  = $request->task_status;

        $query = \App\Models\JobEmployeeTaskAssignment::with([
            'user', 'taskType', 'job.contact',
        ])->whereHas('user', fn($q) => $q->whereHas('role', fn($r) => $r->where('name', 'factory_employee')));

        if ($employeeId)  $query->where('user_id', $employeeId);
        if ($taskStatus)  $query->where('task_status', $taskStatus);
        if ($jobNumber)   $query->whereHas('job', fn($q) => $q->where('job_number', 'like', '%'.$jobNumber.'%'));

        $assignments = $query->orderBy('user_id')->orderBy('job_id')->get();

        // Attach logged hours per assignment
        $reportRows = $assignments->map(function ($a) {
            $loggedMins = \App\Models\TimeEntry::where('user_id', $a->user_id)
                ->where('job_id', $a->job_id)
                ->where('task_type_id', $a->task_type_id)
                ->whereIn('status', ['paused', 'completed'])
                ->sum('duration_minutes');
            $loggedHours = round($loggedMins / 60, 2);
            $pct = ($a->allocated_hours > 0)
                ? min(100, round(($loggedHours / $a->allocated_hours) * 100, 1))
                : null;
            return [
                'employee_name'   => $a->user?->name ?? 'Unknown',
                'job_id'          => $a->job_id,
                'job_number'      => $a->job?->job_number ?? '—',
                'task_name'       => $a->taskType?->name ?? '—',
                'task_status'     => $a->task_status ?? 'pending',
                'allocated_hours' => $a->allocated_hours,
                'logged_hours'    => $loggedHours,
                'pct'            => $pct,
            ];
        })->groupBy('employee_name')->map(fn($rows) => $rows->groupBy('job_number'));

        $employees        = \App\Models\User::whereHas('role', fn($q) => $q->where('name', 'factory_employee'))->orderBy('name')->get();
        $totalAssignments = $assignments->count();
        $completedCount   = $assignments->where('task_status', 'completed')->count();
        $inProgressCount  = $assignments->whereIn('task_status', ['in_progress', 'paused'])->count();
        $overallPct       = $totalAssignments > 0 ? round(($completedCount / $totalAssignments) * 100) : 0;

        // CSV export
        if ($request->export === 'csv') {
            $headers  = ['Content-Type' => 'text/csv', 'Content-Disposition' => 'attachment; filename="employee-task-report-' . now()->format('Y-m-d') . '.csv"'];
            $callback = function () use ($reportRows) {
                $fh = fopen('php://output', 'w');
                fputcsv($fh, ['Employee','Job','Task','Status','Allocated (h)','Logged (h)','% Done']);
                foreach ($reportRows as $empName => $jobGroups) {
                    foreach ($jobGroups as $jobNum => $rows) {
                        foreach ($rows as $r) {
                            fputcsv($fh, [$r['employee_name'],$r['job_number'],$r['task_name'],$r['task_status'],$r['allocated_hours']??'—',$r['logged_hours'],$r['pct']??'—']);
                        }
                    }
                }
                fclose($fh);
            };
            return response()->stream($callback, 200, $headers);
        }

        return view('reports.employee_task_report', compact(
            'reportRows', 'employees', 'totalAssignments', 'completedCount', 'inProgressCount', 'overallPct'
        ));
    }

    // ── Admin Timesheet Report ─────────────────────────────────────────────────

    /**
     * Comprehensive admin timesheet report:
     * Employee-wise, job-wise, date-wise time log, hourly rate, and total earnings.
     * Also includes job-wise stage/task rows for the CSV export.
     */
    public function adminTimesheetReport(Request $request)
    {
        $from = Carbon::parse($request->filled('from') ? $request->from : now()->startOfMonth());
        $to   = Carbon::parse($request->filled('to')   ? $request->to   : now()->endOfMonth())->endOfDay();

        // Build time-entries query
        $query = \App\Models\TimeEntry::with(['user.role', 'taskType', 'job.contact'])
            ->whereIn('status', ['paused', 'completed', 'running'])
            ->whereBetween('started_at', [$from, $to])
            ->orderByDesc('started_at');

        if ($request->filled('employee_id')) {
            $query->where('user_id', $request->employee_id);
        }
        if ($request->filled('job_number')) {
            $query->whereHas('job', fn($q) => $q->where('job_number', 'like', '%' . $request->job_number . '%'));
        }

        // CSV export for full time log
        if ($request->export === 'csv') {
            $entries = $query->get();
            $headers  = [
                'Content-Type'        => 'text/csv',
                'Content-Disposition' => 'attachment; filename="admin-timesheet-' . now()->format('Y-m-d') . '.csv"',
            ];
            $callback = function () use ($entries) {
                $fh = fopen('php://output', 'w');
                fputcsv($fh, ['Date', 'Employee', 'Role', 'Job #', 'Client', 'Stage', 'Task', 'Start Time', 'Stop Time', 'Hours', 'Hourly Rate ($)', 'Amount ($)', 'Status']);
                foreach ($entries as $e) {
                    $hrs    = round($e->duration_minutes / 60, 2);
                    $rate   = (float)($e->user?->hourly_rate ?? 0);
                    $amount = round($hrs * $rate, 2);
                    fputcsv($fh, [
                        $e->started_at?->format('Y-m-d'),
                        $e->user?->name ?? '',
                        $e->user?->role?->display_name ?? '',
                        $e->job?->job_number ?? '',
                        $e->job?->contact?->full_name ?? '',
                        $e->job?->stageLabel() ?? '',
                        $e->taskType?->name ?? '',
                        $e->started_at?->format('H:i'),
                        $e->stopped_at?->format('H:i') ?? '',
                        $hrs,
                        number_format($rate, 2, '.', ''),
                        number_format($amount, 2, '.', ''),
                        $e->status,
                    ]);
                }
                fclose($fh);
            };
            return response()->stream($callback, 200, $headers);
        }

        // Job-stage CSV export (task assignments)
        if ($request->export === 'job_excel') {
            $assignmentQuery = \App\Models\JobEmployeeTaskAssignment::with(['user', 'taskType', 'job.contact'])
                ->whereHas('user', fn($q) => $q->whereHas('role', fn($r) => $r->where('name', 'factory_employee')));
            if ($request->filled('employee_id')) {
                $assignmentQuery->where('user_id', $request->employee_id);
            }
            if ($request->filled('job_number')) {
                $assignmentQuery->whereHas('job', fn($q) => $q->where('job_number', 'like', '%' . $request->job_number . '%'));
            }
            $assignments = $assignmentQuery->get();

            $headers  = [
                'Content-Type'        => 'text/csv',
                'Content-Disposition' => 'attachment; filename="job-stage-report-' . now()->format('Y-m-d') . '.csv"',
            ];
            $callback = function () use ($assignments, $from, $to) {
                $fh = fopen('php://output', 'w');
                fputcsv($fh, ['Job #', 'Client', 'Stage', 'Employee', 'Task', 'Task Status', 'Hours Logged', 'Hourly Rate ($)', 'Total Price ($)']);
                foreach ($assignments as $a) {
                    $loggedMins = \App\Models\TimeEntry::where('user_id', $a->user_id)
                        ->where('job_id', $a->job_id)
                        ->where('task_type_id', $a->task_type_id)
                        ->whereIn('status', ['paused', 'completed'])
                        ->whereBetween('started_at', [$from, $to])
                        ->sum('duration_minutes');
                    $hrs   = round($loggedMins / 60, 2);
                    $rate  = (float)($a->user?->hourly_rate ?? 0);
                    $price = round($hrs * $rate, 2);
                    fputcsv($fh, [
                        $a->job?->job_number ?? '',
                        $a->job?->contact?->full_name ?? '',
                        $a->job?->stageLabel() ?? '',
                        $a->user?->name ?? '',
                        $a->taskType?->name ?? '',
                        $a->task_status ?? '',
                        $hrs,
                        number_format($rate, 2, '.', ''),
                        number_format($price, 2, '.', ''),
                    ]);
                }
                fclose($fh);
            };
            return response()->stream($callback, 200, $headers);
        }

        // Paginated entries for main view
        $entries = $query->paginate(50)->withQueryString();

        // Employee summary
        $allEntries = (clone $query)->get();
        $employeeSummary = $allEntries->groupBy('user_id')->map(function ($group) {
            $first = $group->first();
            $hrs   = round($group->sum('duration_minutes') / 60, 2);
            $rate  = (float)($first->user?->hourly_rate ?? 0);
            return [
                'name'     => $first->user?->name ?? 'Unknown',
                'role'     => $first->user?->role?->display_name ?? '—',
                'rate'     => $rate,
                'entries'  => $group->count(),
                'hours'    => $hrs,
                'earnings' => round($hrs * $rate, 2),
            ];
        })->sortByDesc('earnings')->values();

        $totalHours    = round($allEntries->sum('duration_minutes') / 60, 2);
        $totalEarnings = $employeeSummary->sum('earnings');
        $totalEntries  = $allEntries->count();
        $jobsCoveredCount = $allEntries->pluck('job_id')->filter()->unique()->count();

        // Job-stage rows (task assignments with logged hours for that period)
        $assignmentQuery = \App\Models\JobEmployeeTaskAssignment::with(['user', 'taskType', 'job.contact'])
            ->whereHas('user', fn($q) => $q->whereHas('role', fn($r) => $r->where('name', 'factory_employee')));
        if ($request->filled('employee_id')) {
            $assignmentQuery->where('user_id', $request->employee_id);
        }
        if ($request->filled('job_number')) {
            $assignmentQuery->whereHas('job', fn($q) => $q->where('job_number', 'like', '%' . $request->job_number . '%'));
        }
        $assignments  = $assignmentQuery->orderBy('job_id')->orderBy('user_id')->get();
        $jobStageRows = $assignments->map(function ($a) use ($from, $to) {
            $loggedMins = \App\Models\TimeEntry::where('user_id', $a->user_id)
                ->where('job_id', $a->job_id)
                ->where('task_type_id', $a->task_type_id)
                ->whereIn('status', ['paused', 'completed'])
                ->whereBetween('started_at', [$from, $to])
                ->sum('duration_minutes');
            $hrs   = round($loggedMins / 60, 2);
            $rate  = (float)($a->user?->hourly_rate ?? 0);
            $price = round($hrs * $rate, 2);
            return [
                'job_number'  => $a->job?->job_number ?? '—',
                'client'      => $a->job?->contact?->full_name ?? '—',
                'stage_label' => $a->job?->stageLabel() ?? '—',
                'stage_color' => $a->job?->stageColor() ?? 'gray',
                'employee'    => $a->user?->name ?? 'Unknown',
                'task'        => $a->taskType?->name ?? '—',
                'task_status' => $a->task_status ?? 'pending',
                'hours'       => $hrs,
                'rate'        => $rate,
                'total_price' => $price,
            ];
        })->filter(fn($r) => $r['hours'] > 0 || $r['task_status'] === 'completed')->values()->toArray();

        // Employee list for filter dropdown
        $employees = \App\Models\User::whereHas('role', fn($q) => $q->whereIn('name', ['factory_employee', 'processor', 'trades']))
            ->where('is_active', true)->orderBy('name')->get();

        return view('reports.admin_timesheet_report', compact(
            'entries', 'employeeSummary', 'employees',
            'totalHours', 'totalEarnings', 'totalEntries', 'jobsCoveredCount',
            'jobStageRows', 'from', 'to'
        ));
    }
}