<?php

namespace App\Console\Commands;

use App\Models\Activity;
use App\Models\CrmCase;
use App\Models\FollowUp;
use App\Models\NotificationAlert;
use App\Models\Todo;
use App\Models\ScheduledReport;
use App\Http\Controllers\ReportingController;
use App\Services\ReportExportService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Mail;
use App\Models\OperationalDocument;
use App\Models\TradeDocument;
use App\Models\User;
use App\Services\CrmNotificationService;

class ProcessCrmAutomation extends Command
{
    protected $signature = 'crm:process-automation';
    protected $description = 'Send scheduled CRM emails and create due reminder alerts';

    public function handle(): int
    {
        $sent = 0;
        Activity::withoutGlobalScopes()->where('activity_type_id', 9)
            ->whereNotNull('scheduled_at')->where('scheduled_at', '<=', now())
            ->whereIn('email_status', ['draft', 'failed'])->whereNull('sent_at')
            ->chunkById(50, function ($activities) use (&$sent) {
                foreach ($activities as $activity) {
                    try {
                        $body = nl2br(e($activity->email_body)).
                            '<img src="'.route('email-tracking.open', $activity->tracking_token).'" width="1" height="1" alt="">';
                        Mail::html($body, function ($message) use ($activity) {
                            $message->to($activity->to_recipients ?: [$activity->email_to])
                                ->from($activity->from_email)->subject($activity->subject);
                            if ($activity->cc_recipients) $message->cc($activity->cc_recipients);
                            if ($activity->bcc_recipients) $message->bcc($activity->bcc_recipients);
                        });
                        $activity->update(['email_status' => 'sent', 'sent_at' => now()]);
                        $sent++;
                    } catch (\Throwable $exception) {
                        $activity->update(['email_status' => 'failed']);
                        report($exception);
                    }
                }
            });

        $this->createReminders(Activity::withoutGlobalScopes()->whereNotNull('remind_at')
            ->where('remind_at', '<=', now())->whereNotIn('lifecycle_status', ['completed', 'cancelled'])
            ->get(), 'Activity', fn ($record) => $record->title);
        $this->createReminders(FollowUp::withoutGlobalScopes()->whereNotNull('remind_at')
            ->where('remind_at', '<=', now())->where('status', 'pending')->get(), 'Follow-up', fn ($record) => $record->title);
        $this->createReminders(Todo::withoutGlobalScopes()->whereNotNull('remind_at')
            ->where('remind_at', '<=', now())->whereNotIn('status', ['completed', 'cancelled'])->get(), 'To-do', fn ($record) => $record->title);
        app(\App\Services\ActivityWatcherService::class)->deliverDigests();
        $this->createContractRenewals();
        $this->processOperationalReminders();
        $this->processSlaEscalations();
        $this->generateScheduledInvoices();

        ScheduledReport::withoutGlobalScopes()->where('status','active')->where('next_run_at','<=',now())->chunkById(20,function($reports){
            foreach($reports as $report){
                try{
                    $controller=app(ReportingController::class);$exporter=app(ReportExportService::class);
                    [$headers,$rows]=$controller->scheduledRows($controller->data($report->business_id,$report->filters??[]),$report->report_type);
                    $content=$report->format==='pdf'?$exporter->pdf($report->name,$headers,$rows):$exporter->xlsx($report->name,$headers,$rows);
                    $ext=$report->format==='pdf'?'pdf':'xlsx';$mime=$ext==='pdf'?'application/pdf':'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
                    Mail::raw('Your scheduled CRM report is attached.',fn($mail)=>$mail->to($report->recipients)->subject($report->name)->attachData($content,$report->name.'.'.$ext,['mime'=>$mime]));
                    $next=match($report->frequency){'daily'=>$report->next_run_at->addDay(),'monthly'=>$report->next_run_at->addMonth(),default=>$report->next_run_at->addWeek()};
                    $report->update(['last_sent_at'=>now(),'next_run_at'=>$next,'last_error'=>null]);
                }catch(\Throwable $e){$report->update(['last_error'=>$e->getMessage(),'next_run_at'=>now()->addHour()]);report($e);}
            }
        });

        $this->info("Processed CRM automation; {$sent} scheduled emails sent.");
        return self::SUCCESS;
    }

    private function createReminders(iterable $records, string $type, callable $title): void
    {
        foreach ($records as $record) {
            $name = "{$type} reminder: {$title($record)} [{$record->id}]";
            $alert = NotificationAlert::firstOrCreate(['business_id' => $record->business_id, 'name' => $name]);
            $ownerId = $record->owner_id ?? $record->assigned_to ?? null;
            $recipients = User::where('business_id', $record->business_id)
                ->when($ownerId, fn ($query) => $query->whereKey($ownerId), fn ($query) => $query->where('is_admin', true))
                ->get();
            $event = match ($type) {
                'Activity' => 'activity_reminder',
                'Follow-up' => 'follow_up_reminder',
                default => 'todo_reminder',
            };
            if ($alert->wasRecentlyCreated) app(CrmNotificationService::class)->send($recipients, $event, $type.' reminder', $title($record).' is due or overdue.', route('notifications.index'), get_class($record), $record->id, 'due');
            if ($alert->wasRecentlyCreated && $record instanceof Activity) app(\App\Services\ActivityWatcherService::class)->notify($record, 'reminder', ($title($record) ?: 'Activity').' is due or overdue.');
            $settingColumn = $record instanceof Activity ? 'activity_hours' : ($record instanceof FollowUp ? 'follow_up_hours' : 'todo_hours');
            $defaultHours = \DB::table('reminder_escalation_settings')->where('business_id', $record->business_id)->value($settingColumn) ?: 24;
            $escalationHours = $record->reminder_escalation_hours ?: $defaultHours;
            if ($record->remind_at && $record->remind_at->lte(now()->subHours($escalationHours))) {
                $escalation=NotificationAlert::firstOrCreate(['business_id'=>$record->business_id,'name'=>"{$type} reminder escalation: {$title($record)} [{$record->id}]"]);
                if ($escalation->wasRecentlyCreated) {
                    $escalationRecipients=User::where('business_id',$record->business_id)->where(fn($q)=>$q->whereKey($ownerId)->orWhere('is_admin',true))->get();
                    app(CrmNotificationService::class)->send($escalationRecipients, $event.'_escalation', $type.' reminder escalated', $title($record)." remains overdue after {$escalationHours} hours.", route('notifications.index'), get_class($record), $record->id, 'overdue');
                }
            }
        }
    }

    private function processOperationalReminders(): void
    {
        foreach (OperationalDocument::withoutGlobalScopes()->whereIn('type',['invoices','contracts','deliveries'])->whereNotIn('status',['paid','closed','cancelled','expired'])->get() as $document) {
            $details=$document->details ?? [];
            $date=$document->type==='invoices'?($details['due_date']??null):($document->type==='contracts'?($details['end_date']??null):($details['planned_delivery_date']??null));
            if (! $date || \Carbon\Carbon::parse($date)->gt(now()->addDays(30))) continue;
            $label=\Carbon\Carbon::parse($date)->isPast()?'overdue':'due soon';
            $alert=NotificationAlert::firstOrCreate(['business_id'=>$document->business_id,'name'=>"{$document->type} {$label}: {$document->reference_number}"]);
            if (! $alert->wasRecentlyCreated) continue;
            $users=User::where('business_id',$document->business_id)->where(fn($q)=>$q->whereKey($document->owner_id)->orWhere('is_admin',true))->get();
            app(CrmNotificationService::class)->send($users,'operational_reminder','Operational reminder',"{$document->reference_number} is {$label}.",route('operational-documents.index',$document->type),'OperationalDocument',$document->id,$document->status);
        }
        foreach (\DB::table('renewal_opportunities')->whereIn('status',['open','engaged'])->whereDate('renewal_date','<=',now()->addDays(30))->get() as $renewal) {
            $alert=NotificationAlert::firstOrCreate(['business_id'=>$renewal->business_id,'name'=>"Renewal due: #{$renewal->id}"]);
            if (! $alert->wasRecentlyCreated) continue;
            $users=User::where('business_id',$renewal->business_id)->where(fn($q)=>$q->whereKey($renewal->owner_id)->orWhere('is_admin',true))->get();
            app(CrmNotificationService::class)->send($users,'renewal_reminder','Renewal due',"Renewal #{$renewal->id} is due on {$renewal->renewal_date}.",route('operations.customer-success'),'RenewalOpportunity',$renewal->id,'open');
        }
    }

    /** Create a single, traceable renewal opportunity and draft quotation for each expiring active contract. */
    private function createContractRenewals(): void
    {
        OperationalDocument::withoutGlobalScopes()->where('type', 'contracts')->where('status', 'active')
            ->orderBy('id')->each(function (OperationalDocument $contract): void {
                $details = $contract->details ?? [];
                $endDate = filled($details['end_date'] ?? null) ? \Carbon\Carbon::parse($details['end_date'])->startOfDay() : null;
                if (! $endDate || $endDate->gt(now()->addDays(30))) return;

                $renewal = \DB::table('renewal_opportunities')->where('business_id', $contract->business_id)
                    ->where('operational_document_id', $contract->id)->first();
                if (! $renewal) {
                    $renewalId = \DB::table('renewal_opportunities')->insertGetId([
                        'business_id' => $contract->business_id,
                        'account_id' => $contract->account_id,
                        'operational_document_id' => $contract->id,
                        'owner_id' => $contract->owner_id,
                        'renewal_date' => $endDate->toDateString(),
                        'value' => $details['contract_value'] ?? 0,
                        'status' => 'open',
                        'notes' => 'Automatically created from active contract '.$contract->reference_number.'.',
                        'created_at' => now(), 'updated_at' => now(),
                    ]);
                    $renewal = \DB::table('renewal_opportunities')->whereKey($renewalId)->first();
                }

                $quoteReference = 'REN-'.$contract->reference_number.'-'.$endDate->format('Ymd');
                $quote = $contract->deal_id ? TradeDocument::firstOrCreate([
                    'business_id' => $contract->business_id, 'document_number' => $quoteReference,
                ], [
                    'deal_id' => $contract->deal_id, 'created_by' => $contract->owner_id,
                    'type' => 'quotation', 'status' => 'draft', 'currency' => $details['currency'] ?? 'USD',
                    'subtotal' => $renewal->value, 'discount' => 0, 'tax' => 0, 'total' => $renewal->value,
                    'issue_date' => now()->toDateString(), 'due_date' => $endDate->toDateString(),
                    'notes' => 'Renewal quotation generated from contract '.$contract->reference_number.'.',
                ]) : null;
                if ($quote && $quote->wasRecentlyCreated) {
                    $quote->lines()->create(['description' => 'Renewal of '.$contract->reference_number, 'quantity' => 1, 'unit_price' => $renewal->value, 'line_total' => $renewal->value]);
                }

                $followUp = FollowUp::firstOrCreate([
                    'business_id' => $contract->business_id, 'related_type' => OperationalDocument::class,
                    'related_id' => $contract->id, 'title' => 'Renew contract: '.$contract->reference_number,
                ], [
                    'owner_id' => $contract->owner_id, 'follow_up_at' => now()->addDay(), 'remind_at' => now()->addDay(),
                    'priority' => 'high', 'status' => 'pending',
                    'notes' => 'Renewal due '.$endDate->toDateString().($quote ? '; draft quotation '.$quote->document_number.' created.' : '.'),
                ]);
                if ($followUp->wasRecentlyCreated) {
                    $contract->histories()->create(['user_id' => $contract->owner_id, 'action' => 'renewal_workflow_created', 'from_status' => 'active', 'to_status' => 'active', 'comment' => 'Renewal opportunity #'.$renewal->id.($quote ? ' and quotation '.$quote->document_number : '').' created.']);
                    $recipients = User::where('business_id', $contract->business_id)->where(fn ($q) => $q->whereKey($contract->owner_id)->orWhere('is_admin', true))->get();
                    app(CrmNotificationService::class)->send($recipients, 'contract_renewal_created', 'Contract renewal ready', 'Renewal workflow created for '.$contract->reference_number.'.', route('operations.customer-success'), OperationalDocument::class, $contract->id, 'open');
                }
            });
    }

    private function processSlaEscalations(): void
    {
        $policies = \DB::table('sla_policies')->where('active', true)->get()->groupBy('business_id');
        foreach (CrmCase::withoutGlobalScopes()->get() as $case) {
            $policy = ($policies[$case->business_id] ?? collect())->firstWhere('priority', $case->priority);
            if (! $policy) continue;
            $due = $case->created_at->copy()->addHours($policy->resolution_hours);
            $status = in_array($case->status, ['resolved', 'closed'], true) ? 'resolved' : ($due->isPast() ? 'breached' : 'open');
            $existing = \DB::table('sla_events')->where('case_id', $case->id)->where('sla_policy_id', $policy->id)->first();
            \DB::table('sla_events')->updateOrInsert(
                ['case_id' => $case->id, 'sla_policy_id' => $policy->id],
                ['business_id' => $case->business_id, 'due_at' => $due, 'status' => $status,
                    'breached_at' => $status === 'breached' ? ($existing?->breached_at ?: now()) : null,
                    'created_at' => $existing?->created_at ?: now(), 'updated_at' => now()]
            );
            if ($status === 'breached' && $existing?->status !== 'breached') {
                NotificationAlert::firstOrCreate(['business_id' => $case->business_id, 'name' => 'SLA breach: '.$case->case_number]);
                $users = User::where('business_id', $case->business_id)->where(fn ($q) => $q->whereKey($case->assigned_to)->orWhere('is_admin', true))->get();
                app(CrmNotificationService::class)->send($users, 'sla_breach', 'SLA breach',
                    $case->case_number.' is past its '.$policy->resolution_hours.' hour SLA.', route('cases.show', $case), 'CrmCase', $case->id, 'breached');
            }
        }
    }

    private function generateScheduledInvoices(): void
    {
        foreach (\DB::table('invoice_schedules')->where('active',true)->whereDate('next_invoice_date','<=',now())->get() as $schedule) {
            $reference='INV-SCH-'.$schedule->id.'-'.now()->format('Ymd');
            if (OperationalDocument::where('business_id',$schedule->business_id)->where('reference_number',$reference)->exists()) continue;
            $details=['invoice_date'=>now()->toDateString(),'due_date'=>now()->addDays(30)->toDateString(),'invoice_type'=>'recurring','currency'=>$schedule->currency,'payment_terms'=>$schedule->payment_terms,'products_services'=>$schedule->description,'subtotal'=>$schedule->amount,'total_amount'=>$schedule->amount,'payment_status'=>'pending'];
            $op=OperationalDocument::create(['business_id'=>$schedule->business_id,'type'=>'invoices','reference_number'=>$reference,'account_id'=>$schedule->account_id,'deal_id'=>$schedule->deal_id,'owner_id'=>$schedule->owner_id,'status'=>'draft','details'=>$details]);
            if ($schedule->deal_id) {$invoice=TradeDocument::create(['business_id'=>$schedule->business_id,'deal_id'=>$schedule->deal_id,'created_by'=>$schedule->owner_id,'type'=>'invoice','document_number'=>$reference,'status'=>'draft','currency'=>$schedule->currency,'subtotal'=>$schedule->amount,'discount'=>0,'tax'=>0,'total'=>$schedule->amount,'issue_date'=>now()->toDateString(),'due_date'=>now()->addDays(30)->toDateString(),'notes'=>$schedule->description]);$invoice->lines()->create(['description'=>$schedule->description,'quantity'=>1,'unit_price'=>$schedule->amount,'line_total'=>$schedule->amount]);$op->update(['trade_document_id'=>$invoice->id]);}
            $next=match($schedule->frequency){'monthly'=>now()->addMonth(),'quarterly'=>now()->addMonths(3),'yearly'=>now()->addYear(),default=>null};\DB::table('invoice_schedules')->where('id',$schedule->id)->update($next?['next_invoice_date'=>$next->toDateString(),'updated_at'=>now()]:['active'=>false,'updated_at'=>now()]);
        }
    }
}
