<?php

namespace App\Http\Controllers\Api\Report;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Illuminate\Support\Facades\Log;

class SalesReportController extends Controller
{
    /**
     * FUNGSI INTERNAL: Membangun Query Dasar (Base Query) yang dinamis
     */
    private function buildBaseSalesQuery(Request $request)
    {
        $user = auth()->user();
        
        $startDate = $request->query('start_date', Carbon::today()->startOfMonth()->toDateString());
        $endDate = $request->query('end_date', Carbon::today()->endOfMonth()->toDateString());
        
        $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;
        
        $salesmanId = $request->query('salesman_id');
        $customerId = $request->query('customer_id');
        $productId = $request->query('product_id');
        $cityId = $request->query('city_id');

        $query = DB::table('sales')
            ->join('customers', 'sales.customer_id', '=', 'customers.id')
            ->leftJoin('salesmen', 'customers.salesman_id', '=', 'salesmen.id')
            ->leftJoin('users as sales_user', 'salesmen.user_id', '=', 'sales_user.id')
            ->leftJoin('districts', 'customers.district_id', '=', 'districts.id')
            ->leftJoin('cities', 'districts.city_id', '=', 'cities.id')
            ->whereBetween(DB::raw('DATE(sales.created_at)'), [$startDate, $endDate])
            ->where('sales.payment_status', '!=', 'cancelled');

        if ($branchId && $branchId !== 'all' && $branchId !== '') {
            $query->where('sales.branch_id', $branchId);
        }
        if ($salesmanId && $salesmanId !== 'all') {
            $query->where('customers.salesman_id', $salesmanId);
        }
        if ($customerId && $customerId !== 'all') {
            $query->where('sales.customer_id', $customerId);
        }
        if ($cityId && $cityId !== 'all') {
            $query->where('cities.id', $cityId);
        }
        
        if ($productId && $productId !== 'all') {
            $query->whereExists(function ($subQuery) use ($productId) {
                $subQuery->select(DB::raw(1))
                      ->from('sale_items')
                      ->whereColumn('sale_items.sale_id', 'sales.id')
                      ->where('sale_items.product_id', $productId);
            });
        }

        return $query;
    }

    /**
     * 1. LAPORAN PENJUALAN REKAP
     */
    public function getRekap(Request $request)
    {
        try {
            $query = $this->buildBaseSalesQuery($request);

            $query->select(
                'sales.id',
                'sales.invoice_number',
                'sales.created_at as sale_date',
                'sales.grand_total',
                'sales.payment_status',
                'customers.name as customer_name',
                'customers.customer_code',
                DB::raw('COALESCE(sales_user.name, "Tanpa Salesman") as salesman_name'),
                'cities.name as city_name',
                DB::raw('COALESCE((SELECT SUM(quantity) FROM sale_items WHERE sale_items.sale_id = sales.id), 0) as qty'),
                DB::raw('COALESCE((SELECT SUM(quantity) FROM sale_items WHERE sale_items.sale_id = sales.id), 0) as total_qty')
            );

            $rekapData = $query->orderBy('sales.created_at', 'desc')->get();

            return response()->json([
                'success' => true,
                'summary' => [
                    'total_omzet' => $rekapData->sum('grand_total'),
                    'total_faktur' => $rekapData->count(),
                    'total_item' => $rekapData->sum('qty'),
                    'total_qty' => $rekapData->sum('qty')
                ],
                'data' => $rekapData
            ]);

        } catch (\Exception $e) {
            Log::error("Report Rekap Penjualan Error: " . $e->getMessage());
            return response()->json(['success' => false, 'message' => 'Gagal memuat laporan rekap.'], 500);
        }
    }

    /**
     * 2. LAPORAN PENJUALAN DETAIL
     */
    public function getDetail(Request $request)
    {
        try {
            $query = $this->buildBaseSalesQuery($request);
            $productId = $request->query('product_id');

            $query->join('sale_items', 'sales.id', '=', 'sale_items.sale_id')
                  ->leftJoin('products', 'sale_items.product_id', '=', 'products.id')
                  ->leftJoin('sales_order_items', function($join) {
                      $join->on('sales.sales_order_id', '=', 'sales_order_items.sales_order_id')
                           ->on('sale_items.product_id', '=', 'sales_order_items.product_id');
                  });

            if ($productId && $productId !== 'all') {
                $query->where('sale_items.product_id', $productId);
            }

            $query->select(
                'sales.invoice_number',
                'sales.created_at as sale_date',
                'sales.payment_status',
                'sales.payment_method', 
                'customers.customer_code', 
                'customers.name as customer_name',
                'cities.name as city_name', 
                DB::raw('COALESCE(sales_user.name, "Tanpa Salesman") as salesman_name'),
                'products.sku', 
                'sale_items.product_name',
                'sale_items.quantity as qty',
                'sale_items.unit as unit_name',
                'sale_items.unit_price as price',
                DB::raw('COALESCE(sales_order_items.discount_nominal, 0) as discount_amount'),
                'sale_items.total_price'
            );

            $detailData = $query->orderBy('sales.created_at', 'desc')
                                ->orderBy('sale_items.id', 'asc')
                                ->get();

            return response()->json([
                'success' => true,
                'summary' => [
                    'total_item_terjual' => $detailData->sum('qty'),
                    'total_omzet' => $detailData->sum('total_price')
                ],
                'data' => $detailData
            ]);

        } catch (\Exception $e) {
            Log::error("Report Detail Penjualan Error: " . $e->getMessage());
            return response()->json(['success' => false, 'message' => 'Gagal memuat laporan detail'], 500);
        }
    }

    /**
     * 3. MASTER FILTER
     */
    public function getFilterOptions()
    {
        try {
            $user = auth()->user();
            $branchId = $user->role === 'owner' ? null : $user->branch_id;

            $cities = DB::table('cities')->select('id', 'name')->orderBy('name', 'asc')->get();
            $categories = DB::table('categories')->select('id', 'name')->orderBy('name', 'asc')->get();
            
            // 1. Ambil semua variant_group_id yang aktif digunakan (contoh: 'VAR-30')
            $usedVariantGroups = DB::table('products')
                ->whereNotNull('variant_group_id')
                ->where('variant_group_id', '!=', '')
                ->pluck('variant_group_id')
                ->unique()
                ->toArray();
                
            // 2. Ekstrak ID Induk dari format 'VAR-{ID}'
            $parentIdsToExclude = [];
            foreach ($usedVariantGroups as $vgId) {
                // Memisahkan angka ID dari prefix 'VAR-'
                $extractedId = str_replace('VAR-', '', $vgId);
                if (is_numeric($extractedId)) {
                    $parentIdsToExclude[] = (int)$extractedId;
                }
            }
            
            // 3. Tarik data produk, KECUALIKAN ID yang terdeteksi sebagai Induk
            $productsQuery = DB::table('products')
                ->select('id', 'sku', 'name', 'variant_name')
                ->where('is_active', 1)
                ->whereNotIn('product_type', ['raw_material', 'consumable']); // PERBAIKAN: Menggunakan whereNotIn

            if (!empty($parentIdsToExclude)) {
                $productsQuery->whereNotIn('id', $parentIdsToExclude);
            }
            
            $products = $productsQuery->orderBy('name', 'asc')->get()->map(function ($item) {
                // Gabungkan nama dengan variant_name agar di UI lebih informatif
                if (!empty($item->variant_name)) {
                    $item->name = $item->name . ' - ' . $item->variant_name;
                }
                return $item;
            });
            
            $salesmenQuery = DB::table('salesmen')
                ->join('users', 'salesmen.user_id', '=', 'users.id')
                ->select('salesmen.id', 'users.name');
            
            $customersQuery = DB::table('customers')->select('id', 'name', 'customer_code')->where('is_active', 1);

            if ($branchId) {
                $salesmenQuery->where('users.branch_id', $branchId);
                $customersQuery->where('branch_id', $branchId);
            }

            return response()->json([
                'success' => true,
                'data' => [
                    'cities' => $cities,
                    'salesmen' => $salesmenQuery->orderBy('users.name', 'asc')->get(),
                    'customers' => $customersQuery->orderBy('name', 'asc')->get(),
                    'products' => $products,
                    'categories' => $categories
                ]
            ]);
        } catch (\Exception $e) {
            \Illuminate\Support\Facades\Log::error("Filter Options Error: " . $e->getMessage());
            return response()->json([
                'success' => false, 
                'message' => 'Gagal memuat opsi filter. Cek log server.'
            ], 500);
        }
    }

    /**
     * 10. LAPORAN REKAP SALES ORDER
     **/
    public function getSalesOrderReport(Request $request)
    {
        try {
            $user = auth()->user();
            $startDate = $request->query('start_date', Carbon::today()->startOfMonth()->toDateString());
            $endDate = $request->query('end_date', Carbon::today()->endOfMonth()->toDateString());
            $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;
            $status = $request->query('status', 'all');

            $query = DB::table('sales_orders')
                ->join('customers', 'sales_orders.customer_id', '=', 'customers.id')
                ->leftJoin('branches', 'sales_orders.fulfillment_branch_id', '=', 'branches.id')
                ->select(
                    'sales_orders.*',
                    'customers.name as customer_name',
                    'branches.name as branch_name'
                )
                ->whereBetween('sales_orders.created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59']);

            if (!empty($branchId) && $branchId !== 'all') {
                $query->where('sales_orders.fulfillment_branch_id', $branchId);
            }

            if ($status !== 'all') {
                $query->where('sales_orders.status', $status);
            }

            $data = $query->orderBy('sales_orders.created_at', 'desc')->get();

            return response()->json([
                'success' => true,
                'data' => $data,
                'summary' => [
                    'total_count' => $data->count(),
                    'total_amount' => $data->sum('grand_total')
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function getDeliveryOrderReport(Request $request)
    {
        try {
            $user = auth()->user();
            $startDate = $request->query('start_date');
            $endDate = $request->query('end_date');
            $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;
            $driverId = $request->query('driver_id', 'all');

            $query = DB::table('sales')
                ->join('customers', 'sales.customer_id', '=', 'customers.id')
                ->leftJoin('users as driver', 'sales.driver_id', '=', 'driver.id')
                ->leftJoin('branches', 'sales.branch_id', '=', 'branches.id')
                ->select(
                    'sales.*',
                    'customers.name as customer_name',
                    'driver.name as driver_name',
                    'branches.name as branch_name'
                );

            if ($startDate && $endDate) {
                $query->where(function($q) use ($startDate, $endDate) {
                    $q->whereBetween(DB::raw('COALESCE(sales.delivery_date, DATE(sales.created_at))'), [$startDate, $endDate]);
                });
            }

            if (!empty($branchId) && $branchId !== 'all') {
                $query->where('sales.branch_id', $branchId);
            }

            if ($driverId !== 'all') {
                $query->where('sales.driver_id', $driverId);
            }

            $data = $query->orderBy('sales.created_at', 'desc')->get();

            return response()->json([
                'success' => true,
                'data' => $data,
                'summary' => [
                    'total_sj' => $data->count(),
                    'total_nilai' => $data->sum('grand_total'),
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * 12. LAPORAN GAGAL KIRIM (FAILED DELIVERY)
     */
    public function getFailedDeliveryReport(Request $request)
    {
        try {
            $user = auth()->user();
            $startDate = $request->query('start_date');
            $endDate = $request->query('end_date');
            $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;

            $query = DB::table('sales')
                ->join('customers', 'sales.customer_id', '=', 'customers.id')
                ->leftJoin('users as driver', 'sales.driver_id', '=', 'driver.id')
                ->leftJoin('branches', 'sales.branch_id', '=', 'branches.id')
                ->select(
                    'sales.id',
                    'sales.invoice_number',
                    'sales.delivery_date',
                    'sales.created_at',
                    'sales.grand_total',
                    'sales.failed_reason as fail_reason', 
                    'customers.name as customer_name',
                    'driver.name as driver_name',
                    'branches.name as branch_name'
                )
                ->where('sales.delivery_status', 'failed'); 

            if ($startDate && $endDate) {
                $query->whereBetween(DB::raw('COALESCE(sales.delivery_date, DATE(sales.created_at))'), [$startDate, $endDate]);
            }

            if (!empty($branchId) && $branchId !== 'all') {
                $query->where('sales.branch_id', $branchId);
            }

            $data = $query->orderBy('sales.created_at', 'desc')->get();

            return response()->json([
                'success' => true,
                'data' => $data,
                'summary' => [
                    'total_failed' => $data->count(),
                    'total_nominal' => $data->sum('grand_total')
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * 7. LAPORAN ANALISA UMUR PIUTANG (AR AGING) - REKAP
     */
    public function getReceivableAging(Request $request)
    {
        try {
            $user = auth()->user();
            $asOfDate = $request->query('as_of_date', Carbon::today()->toDateString());
            $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;
            $cityId = $request->query('city_id');

            $query = DB::table('sales')
                ->join('customers', 'sales.customer_id', '=', 'customers.id')
                ->leftJoin('salesmen', 'customers.salesman_id', '=', 'salesmen.id')
                ->leftJoin('users as u_sales', 'salesmen.user_id', '=', 'u_sales.id')
                ->leftJoin('districts', 'customers.district_id', '=', 'districts.id')
                ->leftJoin('cities', 'districts.city_id', '=', 'cities.id')
                ->select(
                    'customers.name as customer_name',
                    'cities.name as city_name',
                    DB::raw('COALESCE(u_sales.name, "-") as salesman_name'),
                    'sales.grand_total',
                    DB::raw('(SELECT COALESCE(SUM(amount), 0) FROM debt_payments WHERE sale_id = sales.id AND payment_status = "completed") as total_paid'),
                    DB::raw("DATEDIFF('$asOfDate', sales.due_date) as days_overdue")
                )
                ->where('sales.payment_method', 'kredit')
                ->where('sales.payment_status', '!=', 'paid')
                ->where('sales.payment_status', '!=', 'cancelled')
                ->whereDate('sales.created_at', '<=', $asOfDate);

            if ($branchId && $branchId !== 'all') $query->where('sales.branch_id', $branchId);
            if ($cityId && $cityId !== 'all') $query->where('cities.id', $cityId);

            $rawSales = $query->get();

            $agingData = $rawSales->groupBy('customer_name')->map(function ($items, $customer) {
                $totalBalance = 0; $notDue = 0; $aging1_30 = 0; $aging31_60 = 0; $aging61_90 = 0; $agingOver90 = 0;
                
                $firstItem = $items->first();

                foreach ($items as $item) {
                    $remainingDebt = $item->grand_total - $item->total_paid;
                    if ($remainingDebt <= 0) continue;

                    $totalBalance += $remainingDebt;
                    $diff = $item->days_overdue;

                    if ($diff <= 0) { $notDue += $remainingDebt; }
                    elseif ($diff <= 30) { $aging1_30 += $remainingDebt; }
                    elseif ($diff <= 60) { $aging31_60 += $remainingDebt; }
                    elseif ($diff <= 90) { $aging61_90 += $remainingDebt; }
                    else { $agingOver90 += $remainingDebt; }
                }

                return [
                    'customer_name' => $customer,
                    'city_name' => $firstItem->city_name,
                    'salesman_name' => $firstItem->salesman_name,
                    'total_balance' => $totalBalance,
                    'not_due' => $notDue,
                    'aging_1_30' => $aging1_30,
                    'aging_31_60' => $aging31_60,
                    'aging_61_90' => $aging61_90,
                    'aging_over_90' => $agingOver90,
                ];
            })->filter(fn($i) => $i['total_balance'] > 0)->values();

            return response()->json([
                'success' => true,
                'data' => $agingData,
                'summary' => ['total_piutang' => $agingData->sum('total_balance')]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * 8. LAPORAN ANALISA UMUR PIUTANG (DETAIL PER FAKTUR)
     */
    public function getReceivableAgingDetail(Request $request)
    {
        try {
            $user = auth()->user();
            $asOfDate = $request->query('as_of_date', Carbon::today()->toDateString());
            $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;
            $cityId = $request->query('city_id');

            $query = DB::table('sales')
                ->join('customers', 'sales.customer_id', '=', 'customers.id')
                ->leftJoin('salesmen', 'customers.salesman_id', '=', 'salesmen.id')
                ->leftJoin('users as u_sales', 'salesmen.user_id', '=', 'u_sales.id')
                ->leftJoin('districts', 'customers.district_id', '=', 'districts.id')
                ->leftJoin('cities', 'districts.city_id', '=', 'cities.id')
                ->select(
                    'customers.name as customer_name',
                    DB::raw('COALESCE(u_sales.name, "-") as salesman_name'),
                    'sales.invoice_number',
                    'sales.created_at as sale_date',
                    'sales.due_date',
                    DB::raw('IF(sales.due_days > 0, sales.due_days, customers.due_days) as customer_termin'),
                    'sales.grand_total',
                    DB::raw('(SELECT COALESCE(SUM(amount), 0) FROM debt_payments WHERE sale_id = sales.id AND payment_status = "completed") as total_paid'),
                    DB::raw("DATEDIFF('$asOfDate', sales.due_date) as days_overdue")
                )
                ->where('sales.payment_method', 'kredit')
                ->where('sales.payment_status', '!=', 'paid')
                ->where('sales.payment_status', '!=', 'cancelled')
                ->whereDate('sales.created_at', '<=', $asOfDate);

            if ($branchId && $branchId !== 'all') $query->where('sales.branch_id', $branchId);
            if ($cityId && $cityId !== 'all') $query->where('cities.id', $cityId);

            $data = $query->orderBy('customers.name', 'asc')->orderBy('sales.created_at', 'asc')->get();

            $result = $data->map(function($item) {
                $remainingDebt = $item->grand_total - $item->total_paid;
                $diff = $item->days_overdue;

                return [
                    'customer_name' => $item->customer_name,
                    'invoice_number' => $item->invoice_number,
                    'sale_date' => Carbon::parse($item->sale_date)->format('d/m/Y'),
                    'due_date' => $item->due_date ? Carbon::parse($item->due_date)->format('d/m/Y') : '-',
                    'termin' => ($item->customer_termin ?? 0) . " Hari",
                    'salesman_name' => $item->salesman_name,
                    'total_balance' => $remainingDebt,
                    'umur_piutang_hari' => $diff, 
                    'not_due' => $diff <= 0 ? $remainingDebt : 0,
                    'aging_1_30' => ($diff > 0 && $diff <= 30) ? $remainingDebt : 0,
                    'aging_31_60' => ($diff > 30 && $diff <= 60) ? $remainingDebt : 0,
                    'aging_61_90' => ($diff > 60 && $diff <= 90) ? $remainingDebt : 0,
                    'aging_over_90' => ($diff > 90) ? $remainingDebt : 0,
                ];
            })->filter(fn($item) => $item['total_balance'] > 0)->values();

            return response()->json([
                'success' => true,
                'data' => $result,
                'summary' => ['total_piutang' => $result->sum('total_balance')]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * 9. GET DETAIL FAKTUR UNTUK MODAL PREVIEW
     */
    public function getInvoiceDetail(Request $request)
    {
        try {
            $user = auth()->user();
            $invoiceNumber = $request->query('invoice_number');
            
            if (!$invoiceNumber) {
                return response()->json(['success' => false, 'message' => 'Nomor invoice diperlukan']);
            }

            $query = DB::table('sales')->where('invoice_number', $invoiceNumber);
            
            // PERBAIKAN ENTERPRISE: Proteksi IDOR Mengintip Faktur Cabang Lain
            if ($user && $user->role !== 'owner') {
                $query->where('branch_id', $user->branch_id);
            }

            $sale = $query->first();
            
            if (!$sale) {
                return response()->json(['success' => false, 'message' => 'Faktur tidak ditemukan atau bukan milik cabang Anda.']);
            }

            $items = DB::table('sale_items')->where('sale_id', $sale->id)->get();
            $sale->items = $items;

            return response()->json([
                'success' => true,
                'data' => $sale
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * LAPORAN WARNING JATUH TEMPO (EARLY WARNING SYSTEM)
     */
    public function getDueWarningReport(Request $request)
    {
        try {
            $user = auth()->user();
            $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;
            
            $today = date('Y-m-d');
            $warningDate = date('Y-m-d', strtotime('+7 days'));

            $query = DB::table('sales')
                ->join('customers', 'sales.customer_id', '=', 'customers.id')
                ->leftJoin('branches', 'sales.branch_id', '=', 'branches.id')
                ->select(
                    'sales.id',
                    'sales.invoice_number as so_number', 
                    'sales.due_date',
                    'sales.grand_total',
                    DB::raw('(SELECT COALESCE(SUM(amount), 0) FROM debt_payments WHERE sale_id = sales.id AND payment_status = "completed") as total_paid'),
                    'sales.payment_status',
                    'customers.name as customer_name',
                    'customers.phone as customer_phone',
                    'branches.name as branch_name',
                    DB::raw("DATEDIFF(sales.due_date, '$today') as days_left")
                )
                ->where('sales.payment_method', 'kredit')
                ->where('sales.payment_status', '!=', 'paid')
                ->where('sales.payment_status', '!=', 'cancelled')
                ->where('sales.due_date', '<=', $warningDate);

            if (!empty($branchId) && $branchId !== 'all') {
                $query->where('sales.branch_id', $branchId);
            }

            $data = $query->orderBy('sales.due_date', 'asc')->get();

            return response()->json([
                'success' => true,
                'data' => $data,
                'summary' => [
                    'total_outstanding_count' => $data->count(),
                    'total_outstanding_amount' => $data->sum(function($item) {
                        return $item->grand_total - $item->total_paid;
                    })
                ]
            ]);
        } catch (\Exception $e) {
            Log::error("Warning Jatuh Tempo Error: " . $e->getMessage());
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * 13. LAPORAN RETUR PENJUALAN
     */
    public function getSalesReturnReport(Request $request)
    {
        try {
            $user = auth()->user();
            $startDate = $request->query('start_date');
            $endDate = $request->query('end_date');
            $branchId = ($user->role === 'owner') ? $request->query('branch_id') : $user->branch_id;

            $query = DB::table('sales_returns')
                ->join('customers', 'sales_returns.customer_id', '=', 'customers.id')
                ->leftJoin('branches', 'sales_returns.branch_id', '=', 'branches.id')
                ->select(
                    'sales_returns.id',
                    'sales_returns.return_number',
                    'sales_returns.created_at as return_date',
                    'sales_returns.status',
                    'sales_returns.total_refund_value as total_return_value', 
                    'customers.name as customer_name',
                    'branches.name as branch_name'
                );

            if ($startDate && $endDate) {
                $query->whereBetween('sales_returns.created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59']);
            }

            if (!empty($branchId) && $branchId !== 'all') {
                $query->where('sales_returns.branch_id', $branchId);
            }

            $data = $query->orderBy('sales_returns.created_at', 'desc')->get();

            return response()->json([
                'success' => true,
                'data' => $data,
                'summary' => [
                    'total_count' => $data->count(),
                    'total_amount' => $data->sum('total_return_value')
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false, 
                'message' => 'Kesalahan Database: ' . $e->getMessage()
            ], 500);
        }
    }

    public function getParetoPelanggan(Request $request)
    {
        $request->validate([
            'date_start' => 'required|date',
            'date_end' => 'required|date',
            'metric' => 'required|in:qty,value',
            'filter_type' => 'required|in:all,category,product',
            'ids' => 'nullable|array',
            'comparison' => 'required|in:last_month,last_year',
            'limit' => 'required|in:25,50,100,all',
            'salesman_id' => 'nullable' 
        ]);

        $user = auth()->user();

        $start = $request->date_start . ' 00:00:00';
        $end = $request->date_end . ' 23:59:59';
        
        $compStart = \Carbon\Carbon::parse($start)->{$request->comparison === 'last_month' ? 'subMonth' : 'subYear'}()->toDateTimeString();
        $compEnd = \Carbon\Carbon::parse($end)->{$request->comparison === 'last_month' ? 'subMonth' : 'subYear'}()->toDateTimeString();

        $query = DB::table('sales as s')
            ->join('customers as c', 's.customer_id', '=', 'c.id')
            ->leftJoin('salesmen as sm', 'c.salesman_id', '=', 'sm.id')
            ->leftJoin('users as u_sales', 'sm.user_id', '=', 'u_sales.id')
            ->leftJoin('districts as d', 'c.district_id', '=', 'd.id')
            ->leftJoin('cities as ct', 'd.city_id', '=', 'ct.id')
            ->join('sale_items as si', 's.id', '=', 'si.sale_id')
            ->leftJoin('products as p', 'si.product_id', '=', 'p.id')
            ->leftJoin('sales_returns as sr', 's.sales_invoice_id', '=', 'sr.sales_invoice_id')
            ->leftJoin('sales_return_items as sri', function($join) {
                $join->on('sr.id', '=', 'sri.sales_return_id')
                     ->on('si.product_id', '=', 'sri.product_id');
            })
            ->where('s.delivery_status', '!=', 'cancelled');

        // PERBAIKAN ENTERPRISE: Isolasi Data Pareto Lintas Cabang (Anti-Spionase Internal)
        if ($user && $user->role !== 'owner') {
            $query->where('s.branch_id', $user->branch_id);
        }

        // FILTER PRODUK / KATEGORI
        if ($request->filter_type === 'category' && !empty($request->ids)) {
            $query->whereIn('p.category_id', $request->ids);
        } elseif ($request->filter_type === 'product' && !empty($request->ids)) {
            $query->whereIn('si.product_id', $request->ids);
        }

        // FILTER SALESMAN
        if (!empty($request->salesman_id) && $request->salesman_id !== 'all') {
            $query->where('c.salesman_id', $request->salesman_id);
        }

        $metricCol = ($request->metric === 'qty') ? 'si.quantity' : 'si.total_price';
        $retMetricCol = ($request->metric === 'qty') ? 'sri.qty_received' : '(sri.qty_received * sri.price_at_invoice)';

        $query->select(
            'c.id as customer_id',
            'c.name as customer_name',
            'c.address', 
            'ct.name as city_name',
            DB::raw('COALESCE(u_sales.name, "-") as salesman_name'),
            DB::raw("SUM(CASE WHEN s.created_at BETWEEN '$start' AND '$end' THEN ($metricCol - COALESCE($retMetricCol, 0)) ELSE 0 END) as current_val"),
            DB::raw("SUM(CASE WHEN s.created_at BETWEEN '$compStart' AND '$compEnd' THEN ($metricCol - COALESCE($retMetricCol, 0)) ELSE 0 END) as prev_val")
        )
        ->groupBy('c.id', 'c.name', 'c.address', 'ct.name', 'u_sales.name')
        ->orderBy('current_val', 'DESC');

        if ($request->limit !== 'all') {
            $query->limit((int)$request->limit);
        }

        return response()->json(['success' => true, 'data' => $query->get()]);
    }
}