<?php
namespace App\Http\Controllers;

use App\Models\Activity;
use App\Models\AuditLog;
use App\Models\Campaign;
use App\Models\Deal;
use App\Models\DealApprovalHistory;
use App\Models\Lead;
use App\Models\OperationalDocument;
use App\Models\Project;
use App\Models\ScheduledReport;
use App\Models\TradeDocumentApprovalHistory;
use App\Models\User;
use App\Models\Account;
use App\Models\DealStage;
use App\Models\Product;
use App\Models\FollowUp;
use App\Models\TradeDocument;
use App\Services\ReportExportService;
use Carbon\Carbon;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Collection;
use Illuminate\View\View;

class ReportingController extends Controller
{
    public function index(Request $request): View {
        $this->useDefaultDateRange($request,'from','to');
        $request->validate([
            'from'=>['nullable','date'],'to'=>['nullable','date','after_or_equal:from'],
            'employee_id'=>['nullable','integer'],'team'=>['nullable','string','max:100'],'owner_id'=>['nullable','integer'],'source_id'=>['nullable','integer'],'stage_id'=>['nullable','integer'],'product_id'=>['nullable','integer'],'account_id'=>['nullable','integer'],'expected_close_from'=>['nullable','date'],'expected_close_to'=>['nullable','date','after_or_equal:expected_close_from'],
        ]);
        $data=$this->data($request->user()->business_id,$request->only(['from','to','employee_id','team','owner_id','source_id','stage_id','product_id','account_id','expected_close_from','expected_close_to']),250);
        foreach ([
            'campaigns' => 'campaigns_page',
            'approvalTurnaround' => 'approvals_page',
            'activityFeed' => 'activity_page',
            'projects' => 'projects_page',
            'timesheets' => 'timesheets_page',
            'operations' => 'operations_page',
        ] as $key => $pageName) {
            $data[$key] = $this->paginateCollection($data[$key], 20, $pageName);
        }
        $users=User::where('business_id',$request->user()->business_id)
            ->orderBy('name')->limit(250)->get();
        $teams=$users->pluck('team_name')->filter()->unique()->sort()->values();
        $schedules=ScheduledReport::where('business_id',$request->user()->business_id)->latest()->limit(100)->get();
        $salesFilters = ['sources'=>Campaign::where('business_id',$request->user()->business_id)->orderBy('campaign_name')->get(['id','campaign_name']),'stages'=>DealStage::orderBy('id')->get(['id','name']),'products'=>Product::where('business_id',$request->user()->business_id)->orderBy('product_name')->get(['id','product_name']),'accounts'=>Account::where('business_id',$request->user()->business_id)->orderBy('account_name')->get(['id','account_name'])];
        return view('reports.crm',$data+compact('users','teams','schedules','salesFilters'));
    }

    private function paginateCollection(Collection $items, int $perPage, string $pageName): LengthAwarePaginator
    {
        $page = LengthAwarePaginator::resolveCurrentPage($pageName);

        return (new LengthAwarePaginator(
            $items->forPage($page, $perPage)->values(),
            $items->count(),
            $perPage,
            $page,
            ['path' => request()->url(), 'pageName' => $pageName]
        ))->withQueryString();
    }
    public function legacy(Request $request,string $section): View {
        $request->merge(['section'=>$section]);return $this->index($request);
    }
    public function data(int $businessId,array $filters=[],?int $detailLimit=null): array {
        $from=!empty($filters['from'])?Carbon::parse($filters['from'])->startOfDay():null;$to=!empty($filters['to'])?Carbon::parse($filters['to'])->endOfDay():null;
        $filterUsers=!empty($filters['team'])||!empty($filters['employee_id']);$userIds=User::where('business_id',$businessId)->when($filters['team']??null,fn($q,$v)=>$q->where('team_name',$v))->when($filters['employee_id']??null,fn($q,$v)=>$q->whereKey($v))->pluck('id');
        $date=fn($q,$col='created_at')=>$q->when($from,fn($x)=>$x->where($col,'>=',$from))->when($to,fn($x)=>$x->where($col,'<=',$to));
        $leads=$date(Lead::where('business_id',$businessId)->when($filterUsers,fn($q)=>$q->whereIn('contact_owner_id',$userIds)),'leads.created_at');
        $dealOwner = $filters['owner_id'] ?? $filters['employee_id'] ?? null;
        $deals=$date(Deal::where('business_id',$businessId)->when($filterUsers && !$dealOwner,fn($q)=>$q->whereIn('owner_id',$userIds))->when($dealOwner,fn($q,$v)=>$q->where('owner_id',$v))->when($filters['source_id']??null,fn($q,$v)=>$q->where('campaign_source_id',$v))->when($filters['stage_id']??null,fn($q,$v)=>$q->where('stage_id',$v))->when($filters['account_id']??null,fn($q,$v)=>$q->where('account_id',$v))->when($filters['product_id']??null,fn($q,$v)=>$q->whereHas('dealProductLine',fn($lines)=>$lines->where('product_id',$v)))->when($filters['expected_close_from']??null,fn($q,$v)=>$q->whereDate('closing_date','>=',$v))->when($filters['expected_close_to']??null,fn($q,$v)=>$q->whereDate('closing_date','<=',$v)),'deals.created_at');
        $activities=$date(Activity::where('business_id',$businessId)->when($filterUsers,fn($q)=>$q->whereIn('owner_id',$userIds)),'activities.created_at');
        $summary=['leads'=>(clone$leads)->count(),'qualified_leads'=>(clone$leads)->whereIn('stage_id',fn($q)=>$q->select('id')->from('lead_stages')->where('name','Qualified'))->count(),'deals'=>(clone$deals)->count(),'pipeline_value'=>(clone$deals)->sum('estimate_amount'),'forecast_value'=>(clone$deals)->sum('forecast_value'),'completed_activities'=>(clone$activities)->where('lifecycle_status','completed')->count()];
        $summary['lead_conversion_rate']=$summary['leads']?round($summary['qualified_leads']/$summary['leads']*100,1):0;
        $teamPipeline=$date(Deal::where('deals.business_id',$businessId)->when($filterUsers,fn($q)=>$q->whereIn('owner_id',$userIds)),'deals.created_at')
            ->leftJoin('users','users.id','=','deals.owner_id')->selectRaw("COALESCE(users.name,'Unassigned') employee, COALESCE(users.team_name,'No Team') team, COUNT(deals.id) deals, COALESCE(SUM(deals.estimate_amount),0) pipeline, COALESCE(SUM(deals.forecast_value),0) forecast")->groupBy('users.name','users.team_name')->orderByDesc('pipeline')->get();
        $leadStages=(clone$leads)->leftJoin('lead_stages','lead_stages.id','=','leads.stage_id')->selectRaw("COALESCE(lead_stages.name,'Unspecified') stage,COUNT(leads.id) total")->groupBy('lead_stages.name')->get();
        $dealStages=(clone$deals)->leftJoin('deal_stages','deal_stages.id','=','deals.stage_id')->selectRaw("COALESCE(deal_stages.name,'Unspecified') stage,COUNT(deals.id) total,COALESCE(SUM(deals.estimate_amount),0) value")->groupBy('deal_stages.name')->get();
        $salesDeals=(clone $deals)->with(['assignedUser:id,name','dealProductLine.product'])->get();
        $weightedPipeline=$salesDeals->sum(fn($deal)=>(float)$deal->estimate_amount*(float)($deal->close_probability??0)/100);
        $won=$salesDeals->where('stage_id',9); $lost=$salesDeals->where('stage_id',10); $decided=$won->count()+$lost->count();
        $overdueFollowUps=FollowUp::where('business_id',$businessId)->where('related_type',Deal::class)->whereIn('related_id',$salesDeals->pluck('id'))->whereNotIn('status',['completed','cancelled'])->where('follow_up_at','<',now())->orderBy('follow_up_at')->get();
        $allQuotations=TradeDocument::where('business_id',$businessId)->where('type','quotation')->whereIn('deal_id',$salesDeals->pluck('id'))->get();
        $quotations=$allQuotations->whereNotIn('status',['accepted','cancelled'])->sortBy('issue_date')->values();
        $convertedQuotes=OperationalDocument::where('business_id',$businessId)->where('type','sales-orders')->whereIn('trade_document_id',$allQuotations->pluck('id'))->count();
        $salesDashboard=['weighted_pipeline'=>$weightedPipeline,'won'=>$won->count(),'lost'=>$lost->count(),'win_rate'=>$decided?round($won->count()/$decided*100,1):0,'quote_to_order_rate'=>$allQuotations->count()?round($convertedQuotes/$allQuotations->count()*100,1):0,'overdue_followups'=>$overdueFollowUps,'quotation_aging'=>$quotations->map(fn($quote)=>(object)['quote'=>$quote,'age'=>now()->diffInDays($quote->issue_date ?? $quote->created_at),'deal'=>$salesDeals->firstWhere('id',$quote->deal_id)]),'deals'=>$salesDeals];
        $campaigns=$date(Campaign::where('business_id',$businessId)->when($filterUsers,fn($q)=>$q->whereIn('owner_id',$userIds)),'campaigns.created_at')->withCount(['targets as targets_count','targets as responses_count'=>fn($q)=>$q->whereNotNull('responded_at'),'targets as conversions_count'=>fn($q)=>$q->where('converted',true)])->when($detailLimit,fn($q)=>$q->latest()->limit($detailLimit))->get()->map(fn($c)=>(object)['campaign'=>$c->campaign_name,'targets'=>$c->targets_count,'responses'=>$c->responses_count,'conversions'=>$c->conversions_count,'conversion_rate'=>$c->targets_count?round($c->conversions_count/$c->targets_count*100,1):0,'revenue'=>(float)$c->actual_revenue]);
        $approvalTurnaround=$this->approvalRows($businessId,$from,$to);
        if($detailLimit)$approvalTurnaround=$approvalTurnaround->take($detailLimit);
        $activityFeed=$date(AuditLog::where('business_id',$businessId),'performed_at')->when($filters['employee_id']??null,fn($q,$v)=>$q->where('user_id',$v))->when($filters['team']??null,fn($q,$v)=>$q->where('team_name',$v))->latest('performed_at')->limit(100)->get();
        $projects=$date(Project::with(['owner','account'])->where('business_id',$businessId)->when($filterUsers,fn($q)=>$q->whereIn('owner_id',$userIds)),'projects.created_at')->when($detailLimit,fn($q)=>$q->latest()->limit($detailLimit))->get();
        $timesheets=(clone $activities)->with(['attendees','owner:id,name,team_name'])->when($detailLimit,fn($q)=>$q->latest()->limit($detailLimit))->get()->map(fn($a)=>(object)['employee'=>$a->owner?->name ?: 'Unassigned','team'=>$a->owner?->team_name,'activity'=>$a->title,'date'=>$a->start_date,'duration'=>$a->duration,'status'=>$a->lifecycle_status]);
        $operationRows=$date(OperationalDocument::where('business_id',$businessId)->when($filterUsers,fn($q)=>$q->whereIn('owner_id',$userIds)),'operational_documents.created_at')->get(['type','status','details']);
        $operations=$operationRows->groupBy(fn($document)=>$document->type.'|'.$document->status)->map(function($records){$first=$records->first();return (object)['type'=>$first->type,'status'=>$first->status,'total'=>$records->count(),'value'=>$records->sum(fn($document)=>(float)(($document->details['total_amount']??$document->details['grand_total']??$document->details['contract_value']??0)))];})->sortBy(fn($record)=>$record->type.'|'.$record->status)->values();
        $summary += [
            'approved_sales_orders'=>(clone $operations)->where('type','sales-orders')->whereIn('status',['approved','partially_delivered','completed'])->sum('total'),
            'active_contracts'=>(clone $operations)->where('type','contracts')->where('status','active')->sum('total'),
            'closed_deliveries'=>(clone $operations)->where('type','deliveries')->whereIn('status',['customer_sign_off','closed'])->sum('total'),
            'paid_invoices'=>(clone $operations)->where('type','invoices')->whereIn('status',['paid','closed'])->sum('total'),
        ];
        $summary['forecast_value']=$weightedPipeline;
        return compact('summary','teamPipeline','leadStages','dealStages','campaigns','approvalTurnaround','activityFeed','projects','timesheets','operations','filters','salesDashboard');
    }
    public function export(Request $request,ReportExportService $export){
        $type=$request->input('report','pipeline');$format=$request->input('format','xlsx');[$headers,$rows]=$this->scheduledRows($this->data($request->user()->business_id,$request->only(['from','to','employee_id','team','owner_id','source_id','stage_id','product_id','account_id','expected_close_from','expected_close_to'])),$type);
        $name=$type.'-report-'.now()->format('Ymd-His');
        if($format==='pdf')return response($export->pdf(ucwords(str_replace('_',' ',$type)).' Report',$headers,$rows),200,['Content-Type'=>'application/pdf','Content-Disposition'=>"attachment; filename=\"{$name}.pdf\""]);
        return response($export->xlsx($type,$headers,$rows),200,['Content-Type'=>'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet','Content-Disposition'=>"attachment; filename=\"{$name}.xlsx\""]);
    }
    public function schedule(Request $request): RedirectResponse {
        $data=$request->validate(['name'=>'required|max:255','report_type'=>'required|in:pipeline,leads,projects,sales,timesheets,campaigns,approvals,activity_feed,operations','format'=>'required|in:pdf,xlsx','recipients_text'=>'required|string','frequency'=>'required|in:daily,weekly,monthly','next_run_at'=>'required|date','employee_id'=>'nullable|integer','team'=>'nullable|string','from'=>'nullable|date','to'=>'nullable|date','owner_id'=>'nullable|integer','source_id'=>'nullable|integer','stage_id'=>'nullable|integer','product_id'=>'nullable|integer','account_id'=>'nullable|integer','expected_close_from'=>'nullable|date','expected_close_to'=>'nullable|date']);
        $recipients=collect(preg_split('/[,;\\s]+/',$data['recipients_text']))->filter()->values()->all();foreach($recipients as $email)abort_unless(filter_var($email,FILTER_VALIDATE_EMAIL),422,'Invalid report recipient: '.$email);
        ScheduledReport::create(['business_id'=>$request->user()->business_id,'created_by'=>$request->user()->id,'name'=>$data['name'],'report_type'=>$data['report_type'],'format'=>$data['format'],'recipients'=>$recipients,'frequency'=>$data['frequency'],'next_run_at'=>$data['next_run_at'],'filters'=>collect($data)->only(['employee_id','team','from','to','owner_id','source_id','stage_id','product_id','account_id','expected_close_from','expected_close_to'])->filter()->all()]);
        return back()->with('success','Scheduled report delivery created.');
    }
    public function scheduledRows(array $data,string $type): array {
        if($type==='leads')return [['Stage','Total'],$data['leadStages']->map(fn($r)=>(array)$r)->all()];
        if($type==='sales')return [['Stage','Deals','Value'],$data['dealStages']->map(fn($r)=>(array)$r)->all()];
        if($type==='operations')return [['Module','Status','Records','Value'],$data['operations']->map(fn($r)=>[$r->type,$r->status,$r->total,$r->value])->all()];
        return $this->exportRows($data,$type);
    }
    private function approvalRows(int $businessId,$from,$to): Collection {
        $rows=collect();$deal=DealApprovalHistory::where('business_id',$businessId)->when($from,fn($q)=>$q->where('created_at','>=',$from))->when($to,fn($q)=>$q->where('created_at','<=',$to))->orderBy('deal_id')->orderBy('created_at')->get()->groupBy('deal_id');
        foreach($deal as $id=>$items)foreach($items->whereIn('action',['submitted','resubmitted']) as $request){$decision=$items->first(fn($h)=>$h->created_at->gt($request->created_at)&&in_array($h->action,['approved','rejected']));$rows->push((object)['module'=>'Deal','record'=>'#'.$id,'requested_at'=>$request->created_at,'decided_at'=>$decision?->created_at,'decision'=>$decision?->action??'pending','hours'=>$decision?round($request->created_at->diffInMinutes($decision->created_at)/60,1):null]);}
        $docItems=TradeDocumentApprovalHistory::whereHas('document',fn($q)=>$q->where('business_id',$businessId))->when($from,fn($q)=>$q->where('created_at','>=',$from))->when($to,fn($q)=>$q->where('created_at','<=',$to))->orderBy('trade_document_id')->orderBy('created_at')->get()->groupBy('trade_document_id');
        foreach($docItems as $id=>$items)foreach($items->where('action','pending_approval') as $request){$decision=$items->first(fn($h)=>$h->created_at->gt($request->created_at)&&in_array($h->action,['approved','rejected']));$rows->push((object)['module'=>'Document','record'=>'#'.$id,'requested_at'=>$request->created_at,'decided_at'=>$decision?->created_at,'decision'=>$decision?->action??'pending','hours'=>$decision?round($request->created_at->diffInMinutes($decision->created_at)/60,1):null]);}return $rows;
    }
    private function exportRows(array $d,string $type):array{return match($type){'operations'=>[['Module','Status','Records','Value'],$d['operations']->map(fn($r)=>[$r->type,$r->status,$r->total,$r->value])->all()],'campaigns'=>[['Campaign','Targets','Responses','Conversions','Rate %','Revenue'],$d['campaigns']->map(fn($r)=>(array)$r)->all()],'approvals'=>[['Module','Record','Requested','Decided','Decision','Hours'],$d['approvalTurnaround']->map(fn($r)=>(array)$r)->all()],'activity_feed'=>[['Employee','Team','Action','Module','Record','Date'],$d['activityFeed']->map(fn($r)=>[$r->employee_name,$r->team_name,$r->action,$r->entity_type,$r->entity_id,$r->performed_at])->all()],'projects'=>[['Project','Owner','Account','Status','Progress','Due'],$d['projects']->map(fn($r)=>[$r->name,$r->owner?->name,$r->account?->account_name,$r->status,$r->progress,$r->due_date])->all()],'timesheets'=>[['Employee','Team','Activity','Date','Duration','Status'],$d['timesheets']->map(fn($r)=>(array)$r)->all()],default=>[['Employee','Team','Deals','Pipeline','Forecast'],$d['teamPipeline']->map(fn($r)=>(array)$r)->all()]};}
}
