<?php

namespace App\Console\Commands;

use App\Services\Customer\CustomerQualityService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CustomerQualityAuditCommand extends Command
{
    protected $signature = 'customer-quality:audit {--output= : CSV output path for score and classification comparison}';
    protected $description = 'Read-only customer quality reconciliation, status mapping, phone validation and before/after score audit.';

    public function handle(CustomerQualityService $service): int
    {
        if (! Schema::hasTable('customer_quality_metrics')) {
            $this->error('customer_quality_metrics table is missing.');
            return self::FAILURE;
        }

        $settings = $service->settings();
        $summary = [
            'total_customers' => 0,
            'high_quality' => 0,
            'high_return' => 0,
            'high_cancel' => 0,
            'high_cancel_return_overlap' => 0,
            'manual_blacklist' => 0,
            'internal_test_excluded' => 0,
            'invalid_phone' => 0,
            'delivered_amount' => 0.0,
            'score_changed' => 0,
        ];
        $rows = [];

        DB::table('customer_quality_metrics')->orderBy('id')->chunkById(1000, function ($metrics) use (&$rows, &$summary, $service, $settings) {
            foreach ($metrics as $metric) {
                $delivered = (int) $metric->delivered_orders;
                $returned = (int) $metric->returned_orders;
                $cancelled = (int) $metric->cancelled_orders;
                $amount = (float) $metric->delivered_amount;
                $aov = $delivered > 0 ? round($amount / $delivered, 2) : 0.0;
                $excluded = (bool) ($metric->is_blacklisted || $metric->is_fake || $metric->is_blocked || ($metric->is_internal ?? false));
                $new = $service->classificationBreakdown($delivered, $returned, $cancelled, $amount, $aov, $excluded, $settings);
                $phone = $service->phoneValidation($metric->phone);
                $oldScore = (float) $metric->quality_score;

                $summary['total_customers']++;
                $summary['high_quality'] += (int) $new['is_high_quality'];
                $summary['high_return'] += (int) $new['is_high_return'];
                $summary['high_cancel'] += (int) $new['is_high_cancel'];
                $summary['high_cancel_return_overlap'] += (int) ($new['is_high_return'] && $new['is_high_cancel']);
                $summary['manual_blacklist'] += (int) ((bool) $metric->is_blacklisted);
                $summary['internal_test_excluded'] += (int) ((bool) ($metric->is_internal ?? false));
                $summary['invalid_phone'] += (int) ($phone['normalized'] === null);
                $summary['delivered_amount'] += $amount;
                $summary['score_changed'] += (int) (abs($oldScore - $new['quality_score']) > 0.001);

                $rows[] = [
                    $metric->metric_key,
                    $metric->user_id,
                    $metric->customer_name,
                    $metric->phone,
                    $phone['normalized'],
                    $phone['reason'],
                    $oldScore,
                    $new['quality_score'],
                    $metric->quality_status,
                    $new['is_high_quality'] ? 'yes' : 'no',
                    $new['is_high_return'] ? 'yes' : 'no',
                    $new['is_high_cancel'] ? 'yes' : 'no',
                    implode('|', $new['reasons']),
                ];
            }
        });

        $statusRows = DB::table('orders')->select('order_status', DB::raw('COUNT(*) as total'))->groupBy('order_status')->orderBy('order_status')->get();
        $mappingAudit = $statusRows->map(function ($row) use ($service, $settings) {
            $status = strtolower(trim(str_replace([' ', '-'], '_', (string) $row->order_status)));
            $group = collect(['delivered', 'returned', 'cancelled'])->first(fn ($candidate) => in_array($status, $service->statusesFor($candidate, $settings), true));
            return ['status' => $row->order_status, 'count' => (int) $row->total, 'mapped_group' => $group ?: 'non_final_or_unmapped'];
        })->all();

        $path = (string) ($this->option('output') ?: storage_path('app/customer-quality-audit-' . now()->format('Ymd-His') . '.csv'));
        $directory = dirname($path);
        if (! is_dir($directory)) {
            mkdir($directory, 0755, true);
        }
        $handle = fopen($path, 'wb');
        fputcsv($handle, ['metric_key', 'customer_id', 'name', 'raw_phone', 'normalized_phone', 'invalid_reason', 'old_score', 'new_score', 'old_status', 'new_high_quality', 'new_high_return', 'new_high_cancel', 'classification_reasons']);
        foreach ($rows as $row) {
            fputcsv($handle, $row);
        }
        fclose($handle);

        $summary['delivered_amount'] = round($summary['delivered_amount'], 2);
        $this->line(json_encode(['summary' => $summary, 'status_mapping_audit' => $mappingAudit, 'comparison_csv' => $path], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
        return self::SUCCESS;
    }
}
