<?php

namespace App\Http\Controllers;

use App\Mail\WeeklyTimesheetReport;
use App\Models\ActivityLog;
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 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.");
    }

    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);
    }
}