<?php

namespace App\Http\Controllers;

use App\Models\Order;
use App\Models\OrderItem;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Carbon\Carbon;

class ReportController extends Controller
{
    /**
     * Omset Penjualan - Sales overview
     */
    public function sales(Request $request)
    {
        $startDate = $request->input('start_date', now()->startOfMonth()->toDateString());
        $endDate = $request->input('end_date', now()->toDateString());
        $perPage = (int) $request->input('per_page', 15);

        $applyFilters = function ($query) use ($request) {
            $this->applyMachineFilterIfAvailable($query, $request);

            if ($request->filled('category_id')) {
                $query->whereHas('items.material', function ($q) use ($request) {
                    $q->where('category_id', $request->category_id);
                });
            }
        };

        // Sinkron dengan AI:
        // Omset default = total penjualan order berdasarkan created_at
        $salesOrdersQuery = Order::with($this->getOrderReportWithRelations(['items']))
            ->notCancelled()
            ->whereBetween('created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59']);

        $applyFilters($salesOrdersQuery);

        $salesOrders = (clone $salesOrdersQuery)->get()->map(function ($order) use ($request) {
            return $this->decorateOrderForReport($order, $request);
        })->values();

        $paidOrders = $salesOrders->filter(function ($order) {
            return (float) ($order->report_remaining_balance ?? 0) <= 0;
        })->values();

        $receivableOrders = $salesOrders->filter(function ($order) {
            return (float) ($order->report_remaining_balance ?? 0) > 0;
        })->values();

        // Omset bersih tidak memasukkan PPN.
        // PPN disimpan sebagai pajak keluaran / titipan dan ditampilkan terpisah.
        $totalRevenue = (float) $salesOrders->sum('report_total_price');
        $paidRevenue = (float) $paidOrders->sum('report_paid_net_amount');
        $receivableRevenue = (float) $receivableOrders->sum('report_remaining_balance');
        $totalPpn = (float) $salesOrders->sum('report_ppn_amount');
        $paidPpn = (float) $paidOrders->sum('report_paid_ppn_amount');
        $totalInvoice = (float) $salesOrders->sum('report_invoice_total');

        $paidCustomers = $this->buildCustomerSummary($paidOrders, 'report_total_price');
        $receivableCustomers = $this->buildCustomerSummary($receivableOrders, 'report_remaining_balance');

        $summary = [
            'total_orders' => $salesOrders->count(),
            'total_revenue' => $totalRevenue,
            'total_ppn' => $totalPpn,
            'paid_ppn' => $paidPpn,
            'total_invoice' => $totalInvoice,

            'paid_orders' => $paidOrders->count(),
            'paid_revenue' => $paidRevenue,

            'receivable_orders' => $receivableOrders->count(),
            'receivable_revenue' => $receivableRevenue,

            'paid_customers' => $paidCustomers->count(),
            'receivable_customers' => $receivableCustomers->count(),

            'total_qty' => (float) $salesOrders->sum('report_total_qty'),
        ];

        $ordersBaseQuery = Order::with($this->getOrderReportWithRelations(['items']));

        if ($salesOrders->isEmpty()) {
            $orders = $ordersBaseQuery
                ->whereRaw('1 = 0')
                ->paginate($perPage)
                ->withQueryString();
        } else {
            $orders = $ordersBaseQuery
                ->whereIn('id', $salesOrders->pluck('id'))
                ->orderByDesc('created_at')
                ->paginate($perPage)
                ->withQueryString();

            $orders->getCollection()->transform(function ($order) use ($request) {
                return $this->decorateOrderForReport($order, $request);
            });
        }

        $dailyData = $salesOrders
            ->groupBy(function ($order) {
                return optional($order->created_at)->format('Y-m-d');
            })
            ->filter(function ($group, $date) {
                return !empty($date);
            })
            ->map(function ($group, $date) {
                return (object) [
                    'date' => $date,
                    'total_orders' => $group->count(),
                    'total_revenue' => (float) $group->sum('report_total_price'),
                    'total_ppn' => (float) $group->sum('report_ppn_amount'),
                    'total_invoice' => (float) $group->sum('report_invoice_total'),
                ];
            })
            ->sortBy('date')
            ->values();

        return view('pages.reports.sales', [
            'title' => 'Omset Penjualan',
            'orders' => $orders,
            'summary' => $summary,
            'dailyData' => $dailyData,
            'paidCustomers' => $paidCustomers,
            'receivableCustomers' => $receivableCustomers,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'machines' => $this->getMachines(),
            'categories' => \App\Models\MaterialCategory::all(),
        ]);
    }

    /**
     * Rincian Pendapatan - Revenue by payment / product
     */
    public function revenue(Request $request)
    {
        $startDate = $request->input('start_date', now()->startOfMonth()->toDateString());
        $endDate = $request->input('end_date', now()->toDateString());
        $tab = $request->input('tab', 'transaksi');

        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $ordersQuery = Order::with(
            $this->hasMaterialMachinesRelation()
                ? ['items.product', 'items.material.machines', 'designer', 'customer', 'cashier', 'payments.cashier', 'payments.paymentAccount']
                : ['items.product', 'items.material', 'designer', 'customer', 'cashier', 'payments.cashier', 'payments.paymentAccount']
        )
            ->notCancelled()
            ->whereHas('payments', function ($q) use ($start, $end) {
                $q->whereBetween('created_at', [$start, $end]);
            });

        $this->applyMachineFilterIfAvailable($ordersQuery, $request);

        if ($request->filled('category_id')) {
            $ordersQuery->whereHas('items.material', function ($q) use ($request) {
                $q->where('category_id', $request->category_id);
            });
        }

        $orders = $ordersQuery->get()->map(function ($order) use ($request) {
            return $this->decorateOrderForReport($order, $request);
        })->values();

        $byTransaction = $this->buildRevenuePaymentRows($orders, $start, $end);

        $byProduct = $this->buildRevenueByProductFromPayments($orders, $start, $end);

        $summary = [
            'total_revenue' => (float) $byTransaction->sum('amount'),
            'total_qty' => (float) $byTransaction->sum('qty'),
            'total_products' => $byProduct->count(),
            'total_transactions' => $byTransaction->count(),
        ];

        return view('pages.reports.revenue', [
            'title' => 'Rincian Pendapatan',
            'byProduct' => $byProduct,
            'byTransaction' => $byTransaction,
            'summary' => $summary,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'currentTab' => $tab,
            'machines' => $this->getMachines(),
            'categories' => \App\Models\MaterialCategory::all(),
        ]);
    }

    /**
     * Piutang Penjualan - Unpaid and partially paid orders
     */
    public function receivables(Request $request)
    {
        $perPage = (int) $request->input('per_page', 15);
        $type = $request->input('type', 'credit');

        $ordersQuery = Order::with(['designer', 'customer', 'items', 'payments.cashier'])
            ->withReceivables()
            ->notCancelled();

        if ($type === 'credit') {
            $ordersQuery->where('is_credit', true);
        } elseif ($type === 'non_credit') {
            $ordersQuery->where('is_credit', false);
        }

        if ($request->filled('customer')) {
            $ordersQuery->where('customer_name', $request->input('customer'));
        }

        $summaryOrders = (clone $ordersQuery)->get();

        $summary = [
            'total_orders' => $summaryOrders->count(),
            'total_receivables' => $summaryOrders->sum(function ($order) {
                return (float) $order->getRemainingBalance();
            }),
        ];

        $allCustomersQuery = Order::query()
            ->withReceivables()
            ->notCancelled();

        if ($type === 'credit') {
            $allCustomersQuery->where('is_credit', true);
        } elseif ($type === 'non_credit') {
            $allCustomersQuery->where('is_credit', false);
        }

        $allCustomers = $allCustomersQuery
            ->select('customer_name')
            ->distinct()
            ->orderBy('customer_name')
            ->pluck('customer_name');

        $orders = $ordersQuery
            ->latest('created_at')
            ->paginate($perPage)
            ->withQueryString();

        return view('pages.reports.receivables', [
            'title' => 'Piutang Penjualan',
            'orders' => $orders,
            'summary' => $summary,
            'currentType' => $type,
            'allCustomers' => $allCustomers,
        ]);
    }

    /**
     * Process payment for an order (partial or full)
     */
    public function processPayment(Request $request, Order $order)
    {
        if ($order->isPaid()) {
            return back()->with('error', 'Order ini sudah lunas.');
        }

        $remainingBalance = $order->getRemainingBalance();

        $validated = $request->validate([
            'amount' => ['required', 'numeric', 'min:1', 'max:' . $remainingBalance],
            'payment_method' => ['required', 'in:cash,transfer,ewallet'],
            'payment_account_id' => ['nullable', 'exists:payment_accounts,id'],
            'notes' => ['nullable', 'string', 'max:500'],
            'proof' => ['nullable', 'image', 'mimes:jpg,jpeg,png,webp', 'max:2048'],
        ], [
            'amount.max' => 'Jumlah bayar tidak boleh lebih dari sisa piutang (Rp ' . number_format($remainingBalance, 0, ',', '.') . ').',
            'proof.max' => 'Ukuran file bukti maksimal 2MB.',
        ]);

        $proofPath = null;
        if ($request->hasFile('proof')) {
            $proofPath = $request->file('proof')->store('payment-proofs', 'public');
        }

        $order->addPayment(
            $request->user(),
            $validated['amount'],
            $validated['payment_method'],
            $proofPath,
            $validated['notes'] ?? null,
            isset($validated['payment_account_id']) ? (int) $validated['payment_account_id'] : null
        );

        $message = $order->isPaid()
            ? 'Pembayaran berhasil, order sudah lunas!'
            : 'Pembayaran berhasil dicatat. Sisa piutang: Rp ' . number_format($order->getRemainingBalance(), 0, ',', '.');

        return back()->with('success', $message);
    }

    /**
     * Process batch payment for multiple orders
     */
    public function processBatchPayment(Request $request)
    {
        $validated = $request->validate([
            'order_ids' => ['required', 'string'],
            'amount' => ['required', 'numeric', 'min:1'],
            'payment_method' => ['required', 'in:cash,transfer,ewallet'],
            'payment_account_id' => ['nullable', 'exists:payment_accounts,id'],
            'notes' => ['nullable', 'string', 'max:500'],
            'proof' => ['nullable', 'image', 'mimes:jpg,jpeg,png,webp', 'max:2048'],
        ]);

        $orderIds = json_decode($validated['order_ids'], true);
        if (!is_array($orderIds) || empty($orderIds)) {
            return back()->with('error', 'Tidak ada order yang dipilih.');
        }

        $orders = Order::whereIn('id', $orderIds)->get();
        $totalRemaining = $orders->sum(fn ($o) => $o->getRemainingBalance());

        if ($validated['amount'] > $totalRemaining) {
            return back()->with('error', 'Jumlah bayar melebihi total piutang.');
        }

        $proofPath = null;
        if ($request->hasFile('proof')) {
            $proofPath = $request->file('proof')->store('payment-proofs', 'public');
        }

        $remainingPayment = $validated['amount'];
        $paidCount = 0;
        $fullyPaidCount = 0;

        DB::beginTransaction();

        try {
            foreach ($orders as $order) {
                if ($remainingPayment <= 0) {
                    break;
                }

                $orderRemaining = $order->getRemainingBalance();
                if ($orderRemaining <= 0) {
                    continue;
                }

                $payAmount = min($remainingPayment, $orderRemaining);

                $order->addPayment(
                    $request->user(),
                    $payAmount,
                    $validated['payment_method'],
                    $proofPath,
                    $validated['notes'] ?? null,
                    isset($validated['payment_account_id']) ? (int) $validated['payment_account_id'] : null
                );

                $remainingPayment -= $payAmount;
                $paidCount++;

                if ($order->fresh()->isPaid()) {
                    $fullyPaidCount++;
                }
            }

            DB::commit();

            $message = "Pembayaran berhasil untuk {$paidCount} order.";
            if ($fullyPaidCount > 0) {
                $message .= " {$fullyPaidCount} order sudah lunas.";
            }

            return back()->with('success', $message);
        } catch (\Exception $e) {
            DB::rollBack();
            return back()->with('error', 'Gagal memproses pembayaran: ' . $e->getMessage());
        }
    }

    /**
     * Hutang Pembelian - Purchase debts
     */
    public function purchaseDebt(Request $request)
    {
        $perPage = $request->input('per_page', 15);
        $status = $request->input('status', 'all');

        $purchasesQuery = \App\Models\Purchase::with(['material', 'supplierRelation', 'payments'])
            ->where(function ($query) {
                $query->where('payment_status', 'unpaid')
                    ->orWhere('payment_status', 'partial');
            });

        if ($status === 'unpaid') {
            $purchasesQuery->where('payment_status', 'unpaid');
        } elseif ($status === 'partial') {
            $purchasesQuery->where('payment_status', 'partial');
        }

        if ($request->filled('supplier_id')) {
            $purchasesQuery->where('supplier_id', $request->supplier_id);
        }

        $allPurchases = (clone $purchasesQuery)->get();
        $totalDebt = $allPurchases->sum(fn ($p) => $p->remaining);
        $totalAmount = $allPurchases->sum('total_price');
        $totalPaid = $allPurchases->sum('paid_amount');

        $summary = [
            'total_purchases' => $allPurchases->count(),
            'total_amount' => $totalAmount,
            'total_paid' => $totalPaid,
            'total_debt' => $totalDebt,
        ];

        $suppliers = \App\Models\Supplier::active()->orderBy('name')->get();

        $purchases = $purchasesQuery->latest('purchase_date')
            ->paginate($perPage)
            ->withQueryString();

        return view('pages.reports.purchase-debt', [
            'title' => 'Hutang Pembelian',
            'purchases' => $purchases,
            'summary' => $summary,
            'currentStatus' => $status,
            'suppliers' => $suppliers,
        ]);
    }

    /**
     * Log Transaksi - Activity log
     */
    public function transactions(Request $request)
    {
        $startDate = $request->input('start_date', now()->startOfMonth()->toDateString());
        $endDate = $request->input('end_date', now()->toDateString());
        $perPage = $request->input('per_page', 20);

        $ordersQuery = Order::with(
            $this->hasMaterialMachinesRelation()
                ? ['designer', 'cashier', 'operator', 'items.material.machines']
                : ['designer', 'cashier', 'operator', 'items.material']
        )
            ->whereBetween('created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59']);

        $this->applyMachineFilterIfAvailable($ordersQuery, $request);

        if ($request->filled('category_id')) {
            $ordersQuery->whereHas('items.material', function ($q) use ($request) {
                $q->where('category_id', $request->category_id);
            });
        }

        $orders = $ordersQuery->latest()
            ->paginate($perPage)
            ->withQueryString();

        $orders->getCollection()->transform(function ($order) use ($request) {
            return $this->decorateOrderForReport($order, $request);
        });

        $summary = [
            'total_orders' => (clone $ordersQuery)->count(),
            'paid_orders' => (clone $ordersQuery)->paid()->count(),
            'completed_orders' => (clone $ordersQuery)->completed()->count(),
        ];

        return view('pages.reports.transactions', [
            'title' => 'Log Transaksi',
            'orders' => $orders,
            'summary' => $summary,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'machines' => $this->getMachines(),
            'categories' => \App\Models\MaterialCategory::all(),
        ]);
    }

    /**
     * Laporan Produksi
     */
    public function production(Request $request)
    {
        $startDate = $request->input('start_date', now()->startOfMonth()->toDateString());
        $endDate = $request->input('end_date', now()->toDateString());
        $perPage = $request->input('per_page', 15);

        $ordersQuery = Order::with(['designer', 'operator', 'items'])
            ->completed()
            ->whereBetween('finished_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59']);

        $this->applyMachineFilterIfAvailable($ordersQuery, $request);

        if ($request->filled('category_id')) {
            $ordersQuery->whereHas('items.material', function ($q) use ($request) {
                $q->where('category_id', $request->category_id);
            });
        }

        $allOrderIds = (clone $ordersQuery)->pluck('id');
        $totalQty = OrderItem::whereIn('order_id', $allOrderIds)->sum('qty');

        $summary = [
            'total_completed' => (clone $ordersQuery)->count(),
            'total_qty' => $totalQty,
        ];

        $orders = $ordersQuery->latest('finished_at')
            ->paginate($perPage)
            ->withQueryString();

        $byOperatorQuery = Order::completed()
            ->whereBetween('finished_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
            ->whereNotNull('operator_id');

        $this->applyMachineFilterIfAvailable($byOperatorQuery, $request);

        if ($request->filled('category_id')) {
            $byOperatorQuery->whereHas('items.material', function ($q) use ($request) {
                $q->where('category_id', $request->category_id);
            });
        }

        $byOperator = $byOperatorQuery->select('operator_id', DB::raw('COUNT(*) as total_orders'))
            ->groupBy('operator_id')
            ->with('operator')
            ->get();

        foreach ($byOperator as $op) {
            $opQuery = Order::completed()
                ->whereBetween('finished_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
                ->where('operator_id', $op->operator_id);

            $this->applyMachineFilterIfAvailable($opQuery, $request);

            if ($request->filled('category_id')) {
                $opQuery->whereHas('items.material', function ($q) use ($request) {
                    $q->where('category_id', $request->category_id);
                });
            }

            $opOrderIds = $opQuery->pluck('id');
            $op->total_qty = OrderItem::whereIn('order_id', $opOrderIds)->sum('qty');
        }

        return view('pages.reports.production', [
            'title' => 'Laporan Produksi',
            'orders' => $orders,
            'summary' => $summary,
            'byOperator' => $byOperator,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'machines' => $this->getMachines(),
            'categories' => \App\Models\MaterialCategory::all(),
        ]);
    }

    /**
     * Laporan Harian
     */
    public function daily(Request $request)
    {
        $date = $request->input('date', now()->toDateString());
        $perPage = $request->input('per_page', 15);

        $ordersQuery = Order::with(
            $this->hasMaterialMachinesRelation()
                ? ['designer', 'cashier', 'operator', 'items.material.machines']
                : ['designer', 'cashier', 'operator', 'items.material']
        )
            ->whereDate('created_at', $date);

        $this->applyMachineFilterIfAvailable($ordersQuery, $request);

        if ($request->filled('category_id')) {
            $ordersQuery->whereHas('items.material', function ($q) use ($request) {
                $q->where('category_id', $request->category_id);
            });
        }

        $allOrders = (clone $ordersQuery)->get();
        $allOrders->transform(function ($order) use ($request) {
            return $this->decorateOrderForReport($order, $request);
        });

        $summary = [
            'total_orders' => $allOrders->count(),
            'paid_orders' => $allOrders->where('payment_status', 'paid')->count(),
            'unpaid_orders' => $allOrders->where('payment_status', 'unpaid')->count(),
            'completed_orders' => $allOrders->where('production_status', 'selesai')->count(),
            'total_revenue' => $allOrders->where('payment_status', 'paid')->sum('report_total_price'),
        ];

        $orders = $ordersQuery->latest()
            ->paginate($perPage)
            ->withQueryString();

        $orders->getCollection()->transform(function ($order) use ($request) {
            return $this->decorateOrderForReport($order, $request);
        });

        return view('pages.reports.daily', [
            'title' => 'Laporan Harian',
            'orders' => $orders,
            'summary' => $summary,
            'date' => $date,
            'machines' => $this->getMachines(),
            'categories' => \App\Models\MaterialCategory::all(),
        ]);
    }

    /**
     * Laporan Pendapatan Bersih - Net Income
     */
    public function netIncome(Request $request)
    {
        $startDate = $request->input('start_date', now()->startOfMonth()->toDateString());
        $endDate = $request->input('end_date', now()->toDateString());

        $ordersQuery = Order::paid()
            ->whereBetween('paid_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
            ->with($this->hasMaterialMachinesRelation() ? ['items.material.machines'] : ['items.material']);

        $this->applyMachineFilterIfAvailable($ordersQuery, $request);

        if ($request->filled('category_id')) {
            $ordersQuery->whereHas('items.material', function ($q) use ($request) {
                $q->where('category_id', $request->category_id);
            });
        }

        $orders = $ordersQuery->get();

        $expenses = 0;
        if (!$request->filled('machine_id') && !$request->filled('category_id')) {
            $expenses = \App\Models\Expense::whereBetween('expense_date', [$startDate, $endDate])->sum('amount');
        }

        $materials = \App\Models\Material::all();
        $materialCosts = [];

        $allStockIns = \App\Models\StockMovement::stockIn()
            ->where('created_at', '<=', $endDate . ' 23:59:59')
            ->get()
            ->groupBy('material_id');

        foreach ($materials as $material) {
            $stockIns = $allStockIns->get($material->id);

            if ($stockIns) {
                $validStockIns = $stockIns->filter(function ($movement) {
                    return $movement->reference_type !== \App\Models\Order::class;
                });

                $totalQtyIn = $validStockIns->sum('quantity');
                $totalCostIn = $validStockIns->sum(function ($movement) {
                    if ($movement->total_price > 0) {
                        return $movement->total_price;
                    }

                    return $movement->quantity * $movement->unit_price;
                });

                if ($totalQtyIn > 0) {
                    $materialCosts[$material->id] = $totalCostIn / $totalQtyIn;
                } else {
                    $materialCosts[$material->id] = $material->base_price;
                }
            } else {
                $materialCosts[$material->id] = $material->base_price;
            }
        }

        $clickCost = 0;
        $revenue = 0;

        $materialClickCosts = [];
        $materialsWithMachines = $this->hasMaterialMachinesRelation()
            ? \App\Models\Material::with('machines')->get()
            : collect();

        foreach ($materialsWithMachines as $mat) {
            if ($mat->machines->isNotEmpty()) {
                $materialClickCosts[$mat->id] = $mat->machines->avg('cost_per_click');
            }
        }

        $cogs = 0;
        $cogsDetails = [];

        foreach ($orders as $order) {
            $items = $this->getReportItemsForOrder($order, $request);

            foreach ($items as $item) {
                $itemRevenue = $this->calculateReportItemSubtotal($item);
                $revenue += $itemRevenue;

                if ($item->material_id && isset($materialCosts[$item->material_id])) {
                    $resolvedSizeValue = $this->resolveReportItemSizeValue($item);
                    $usageQty = $this->normalizeSizeUnit($item->size_unit) === 'm2'
                        ? $resolvedSizeValue * (float) ($item->qty ?? 0)
                        : (float) ($item->qty ?? 0);

                    $cost = $usageQty * $materialCosts[$item->material_id];
                    $cogs += $cost;

                    if (!isset($cogsDetails[$item->material_id])) {
                        $cogsDetails[$item->material_id] = [
                            'name' => $item->material_name,
                            'total_usage' => 0,
                            'total_cost' => 0,
                            'avg_price' => $materialCosts[$item->material_id],
                        ];
                    }

                    $cogsDetails[$item->material_id]['total_usage'] += $usageQty;
                    $cogsDetails[$item->material_id]['total_cost'] += $cost;
                }

                if ($item->material_id && isset($materialClickCosts[$item->material_id])) {
                    $clicks = (float) ($item->qty ?? 0) * (float) ($item->sisi ?? 1);
                    $clickCost += $clicks * $materialClickCosts[$item->material_id];
                }
            }
        }

        $netIncome = $revenue - $cogs - $clickCost - $expenses;

        $summary = [
            'revenue' => $revenue,
            'cogs' => $cogs,
            'click_cost' => $clickCost,
            'expenses' => $expenses,
            'net_income' => $netIncome,
        ];

        return view('pages.reports.net-income', [
            'title' => 'Laporan Pendapatan Bersih',
            'summary' => $summary,
            'cogsDetails' => $cogsDetails,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'machines' => $this->getMachines(),
            'categories' => \App\Models\MaterialCategory::all(),
        ]);
    }

    public function exportAiSalesPdf(Request $request)
    {
        $validated = $request->validate([
            'question' => ['nullable', 'string', 'max:500'],
            'description' => ['nullable', 'string', 'max:5000'],
            'data_json' => ['required', 'string'],
            'exec_json' => ['nullable', 'string'],
        ]);

        $rows = json_decode($validated['data_json'], true);
        if (!is_array($rows)) {
            $rows = [];
        }

        $exec = [];
        if (!empty($validated['exec_json'])) {
            $execDecoded = json_decode($validated['exec_json'], true);
            if (is_array($execDecoded)) {
                $exec = $execDecoded;
            }
        }

        $payload = [
            'title' => 'AI Sales Report',
            'question' => $validated['question'] ?? 'AI Penjualan',
            'description' => $validated['description'] ?? '',
            'rows' => $rows,
            'exec' => $exec,
            'generatedAt' => now()->format('d M Y H:i'),
        ];

        $pdf = \Barryvdh\DomPDF\Facade\Pdf::loadView('pdf.ai-sales-report', $payload)
            ->setPaper('a4', 'portrait');

        return $pdf->download('AI_Sales_Report.pdf');
    }

    private function hasMaterialMachinesRelation(): bool
    {
        return method_exists(\App\Models\Material::class, 'machines')
            && Schema::hasTable('machines');
    }

    private function getMachines(): Collection
    {
        if (!class_exists(\App\Models\Machine::class)) {
            return collect();
        }

        if (!Schema::hasTable('machines')) {
            return collect();
        }

        return \App\Models\Machine::query()->orderBy('name')->get();
    }

    private function getOrderReportWithRelations(array $extra = []): array
    {
        $relations = array_merge(['designer', 'cashier', 'operator', 'customer', 'payments', 'items.material'], $extra);

        if ($this->hasMaterialMachinesRelation()) {
            $relations[] = 'items.material.machines';
        }

        return array_values(array_unique($relations));
    }

    private function applyMachineFilterIfAvailable($query, Request $request): void
    {
        if (!$request->filled('machine_id')) {
            return;
        }

        if (!$this->hasMaterialMachinesRelation()) {
            return;
        }

        $query->whereHas('items.material.machines', function ($q) use ($request) {
            $q->where('machines.id', $request->machine_id);
        });
    }

    private function normalizeSizeUnit(?string $unit): string
    {
        $unit = strtolower(trim((string) $unit));

        return match ($unit) {
            'm²', 'm2' => 'm2',
            'm', 'meter' => 'meter',
            'pcs', 'pc', 'piece', 'lembar' => 'lembar',
            default => $unit !== '' ? $unit : 'lembar',
        };
    }

    private function parseM2FromDescription(?string $description): float
    {
        $description = strtolower(trim((string) $description));

        if ($description === '') {
            return 1;
        }

        $description = str_replace(['×', '*'], 'x', $description);

        if (!preg_match('/^\s*(\d+(?:\.\d+)?)\s*x\s*(\d+(?:\.\d+)?)\s*$/', $description, $matches)) {
            return 1;
        }

        $a = (float) $matches[1];
        $b = (float) $matches[2];

        if ($a >= 10 || $b >= 10) {
            $a = $a / 100;
            $b = $b / 100;
        }

        return round($a * $b, 4);
    }

    private function isItemCancelled($item): bool
    {
        if (method_exists($item, 'isCancelled')) {
            return $item->isCancelled();
        }

        return (bool) ($item->is_cancelled ?? false);
    }

    private function itemMatchesReportFilter($item, ?Request $request = null): bool
    {
        if (!$request) {
            return true;
        }

        $material = $item->relationLoaded('material')
            ? $item->material
            : ($item->material_id ? $item->material()->first() : null);

        if ($request->filled('category_id')) {
            if (!$material || (int) $material->category_id !== (int) $request->category_id) {
                return false;
            }
        }

        if ($request->filled('machine_id')) {
            if (!$this->hasMaterialMachinesRelation()) {
                return true;
            }

            if (!$material) {
                return false;
            }

            $hasMachine = $material->relationLoaded('machines')
                ? $material->machines->contains(fn ($machine) => (int) $machine->id === (int) $request->machine_id)
                : $material->machines()->where('machines.id', $request->machine_id)->exists();

            if (!$hasMachine) {
                return false;
            }
        }

        return true;
    }

    private function getReportItemsForOrder(Order $order, ?Request $request = null): Collection
    {
        $items = $order->relationLoaded('items')
            ? $order->items
            : $order->items()->with($this->hasMaterialMachinesRelation() ? ['material.machines'] : ['material'])->get();

        return $items
            ->filter(function ($item) use ($request) {
                return !$this->isItemCancelled($item) && $this->itemMatchesReportFilter($item, $request);
            })
            ->values();
    }

    private function resolveReportItemSizeValue($item): float
    {
        $unit = $this->normalizeSizeUnit($item->size_unit ?? 'lembar');
        $sizeValue = (float) ($item->size_value ?? 0);

        if ($unit === 'm2') {
            if ($sizeValue <= 0) {
                $sizeValue = $this->parseM2FromDescription($item->size_description ?? '');
            }
        } else {
            if ($sizeValue <= 0) {
                $sizeValue = 1;
            }
        }

        return $sizeValue > 0 ? $sizeValue : 1;
    }

    /**
     * Standar nominal laporan.
     * - Minimum order: Rp 10.000
     * - Pembulatan ke atas: Rp 1.000
     *
     * Catatan: fungsi ini hanya dipakai untuk angka laporan agar sinkron
     * dengan total order/item setelah fitur minimum order + pembulatan.
     */
    private function getReportMinimumOrderAmount(): float
    {
        return 10000;
    }

    private function getReportRoundingStep(): float
    {
        return 1000;
    }

    private function applyReportMinimumAndRounding(float $amount): float
    {
        $amount = max($amount, 0);

        if ($amount <= 0) {
            return 0;
        }

        $minimum = $this->getReportMinimumOrderAmount();
        $roundingStep = $this->getReportRoundingStep();

        if ($minimum > 0 && $amount < $minimum) {
            $amount = $minimum;
        }

        if ($roundingStep > 0) {
            $amount = ceil($amount / $roundingStep) * $roundingStep;
        }

        return round($amount, 2);
    }

    private function calculateReportItemSubtotal($item): float
    {
        $unit = $this->normalizeSizeUnit($item->size_unit ?? 'lembar');
        $qty = max((float) ($item->qty ?? 0), 0);
        $basePrice = max((float) ($item->base_price ?? 0), 0);
        $manualPrice = max((float) ($item->manual_price ?? 0), 0);
        $storedSubtotal = max((float) ($item->subtotal ?? 0), 0);
        $sizeValue = $this->resolveReportItemSizeValue($item);

        if ($manualPrice > 0) {
            return $this->applyReportMinimumAndRounding($manualPrice);
        }

        if ($unit === 'm2') {
            return $this->applyReportMinimumAndRounding($basePrice * $sizeValue * $qty);
        }

        if ($unit === 'meter') {
            $amount = $storedSubtotal > 0
                ? $storedSubtotal
                : ($basePrice * $qty);

            return $this->applyReportMinimumAndRounding($amount);
        }

        $amount = $storedSubtotal > 0
            ? $storedSubtotal
            : ($basePrice * $qty);

        return $this->applyReportMinimumAndRounding($amount);
    }

    private function orderHasPpnColumns(): bool
    {
        static $hasColumns = null;

        if ($hasColumns !== null) {
            return $hasColumns;
        }

        try {
            $hasColumns = Schema::hasTable('orders')
                && Schema::hasColumn('orders', 'use_ppn')
                && Schema::hasColumn('orders', 'subtotal_before_ppn')
                && Schema::hasColumn('orders', 'ppn_amount')
                && Schema::hasColumn('orders', 'total_price');
        } catch (\Throwable $e) {
            $hasColumns = false;
        }

        return $hasColumns;
    }

    private function getStoredOrderNetTotal(Order $order): float
    {
        $totalPrice = (float) ($order->getRawOriginal('total_price') ?? $order->total_price ?? 0);

        if (!$this->orderHasPpnColumns()) {
            return $this->applyReportMinimumAndRounding($totalPrice);
        }

        $usePpn = (bool) ($order->getRawOriginal('use_ppn') ?? $order->use_ppn ?? false);
        $subtotalBeforePpn = (float) ($order->getRawOriginal('subtotal_before_ppn') ?? $order->subtotal_before_ppn ?? 0);
        $ppnAmount = (float) ($order->getRawOriginal('ppn_amount') ?? $order->ppn_amount ?? 0);

        if ($usePpn && $subtotalBeforePpn > 0) {
            return $this->applyReportMinimumAndRounding($subtotalBeforePpn);
        }

        if ($usePpn && $ppnAmount > 0 && $totalPrice >= $ppnAmount) {
            return $this->applyReportMinimumAndRounding($totalPrice - $ppnAmount);
        }

        return $this->applyReportMinimumAndRounding($totalPrice);
    }

    private function getReportInvoiceTotal(Order $order): float
    {
        $netTotal = $this->calculateReportOrderTotal($order);
        $ppnAmount = $this->getReportPpnAmount($order, $netTotal);

        return round($netTotal + $ppnAmount, 2);
    }

    private function getReportPpnAmount(Order $order, ?float $netTotal = null): float
    {
        if (!$this->orderHasPpnColumns()) {
            return 0;
        }

        $usePpn = (bool) ($order->getRawOriginal('use_ppn') ?? $order->use_ppn ?? false);

        if (!$usePpn) {
            return 0;
        }

        $rate = (float) ($order->getRawOriginal('ppn_rate') ?? $order->ppn_rate ?? 0);
        $netTotal = $netTotal ?? $this->calculateReportOrderTotal($order);

        if ($rate > 0 && $netTotal > 0) {
            return round($netTotal * ($rate / 100), 2);
        }

        $ppnAmount = (float) ($order->getRawOriginal('ppn_amount') ?? $order->ppn_amount ?? 0);

        return $ppnAmount > 0 ? round($ppnAmount, 2) : 0;
    }

    private function allocateGrossPaymentToNet(Order $order, float $grossAmount, ?float $netTotal = null, ?float $invoiceTotal = null): float
    {
        $netTotal = $netTotal ?? $this->calculateReportOrderTotal($order);
        $invoiceTotal = $invoiceTotal ?? $this->getReportInvoiceTotal($order);

        if ($grossAmount <= 0 || $invoiceTotal <= 0) {
            return 0;
        }

        if ($netTotal <= 0) {
            return 0;
        }

        return round(min($netTotal, $grossAmount * ($netTotal / $invoiceTotal)), 2);
    }

    private function allocateGrossPaymentToPpn(Order $order, float $grossAmount, ?float $ppnAmount = null, ?float $invoiceTotal = null): float
    {
        $ppnAmount = $ppnAmount ?? $this->getReportPpnAmount($order);
        $invoiceTotal = $invoiceTotal ?? $this->getReportInvoiceTotal($order);

        if ($grossAmount <= 0 || $invoiceTotal <= 0 || $ppnAmount <= 0) {
            return 0;
        }

        return round(min($ppnAmount, $grossAmount * ($ppnAmount / $invoiceTotal)), 2);
    }

    private function calculateReportOrderTotal(Order $order, ?Request $request = null): float
    {
        $items = $this->getReportItemsForOrder($order, $request);

        if ($items->isEmpty()) {
            return $this->getStoredOrderNetTotal($order);
        }

        // Jika laporan sedang difilter per mesin/kategori, omset mengikuti subtotal item yang lolos filter.
        // Nilai item adalah nilai penjualan bersih, bukan total setelah PPN.
        return round($items->sum(function ($item) {
            return $this->calculateReportItemSubtotal($item);
        }), 2);
    }

    private function getReportOrderQty(Order $order, ?Request $request = null): float
    {
        $items = $this->getReportItemsForOrder($order, $request);

        if ($items->isEmpty()) {
            return 0;
        }

        return (float) $items->sum(function ($item) {
            return (float) ($item->qty ?? 0);
        });
    }

    private function getReportedPaidAmount(Order $order): float
    {
        if ($order->relationLoaded('payments')) {
            return (float) $order->payments->sum('amount');
        }

        return (float) ($order->paid_amount ?? 0);
    }

    private function getDynamicRemainingBalance(Order $order, ?Request $request = null): float
    {
        $netTotal = $this->calculateReportOrderTotal($order, $request);
        $invoiceTotal = $this->getReportInvoiceTotal($order);
        $grossPaid = $this->getReportedPaidAmount($order);
        $netPaid = $this->allocateGrossPaymentToNet($order, $grossPaid, $netTotal, $invoiceTotal);
        $remaining = $netTotal - $netPaid;

        return $remaining > 0 ? round($remaining, 2) : 0;
    }

    private function getExactOrderRemainingBalance(Order $order): float
    {
        $invoiceTotal = $this->getReportInvoiceTotal($order);
        $totalPaid = $this->getReportedPaidAmount($order);

        return max(round($invoiceTotal - $totalPaid, 2), 0);
    }

    private function decorateOrderForReport(Order $order, ?Request $request = null): Order
    {
        $reportTotal = $this->calculateReportOrderTotal($order, $request); // omset bersih tanpa PPN
        $reportQty = $this->getReportOrderQty($order, $request);
        $invoiceTotal = $this->getReportInvoiceTotal($order); // total tagihan customer termasuk PPN
        $ppnAmount = $this->getReportPpnAmount($order, $reportTotal);
        $grossPaid = $this->getReportedPaidAmount($order);
        $paidNetAmount = $this->allocateGrossPaymentToNet($order, $grossPaid, $reportTotal, $invoiceTotal);
        $paidPpnAmount = $this->allocateGrossPaymentToPpn($order, $grossPaid, $ppnAmount, $invoiceTotal);
        $remainingBalance = $this->getDynamicRemainingBalance($order, $request);

        // Untuk tampilan laporan lama, total_price sengaja dioverride menjadi omset bersih.
        // Nilai total tagihan asli tetap tersedia di report_invoice_total.
        $order->setAttribute('total_price', $reportTotal);
        $order->setAttribute('report_total_price', $reportTotal);
        $order->setAttribute('report_invoice_total', $invoiceTotal);
        $order->setAttribute('report_ppn_amount', $ppnAmount);
        $order->setAttribute('report_paid_gross_amount', $grossPaid);
        $order->setAttribute('report_paid_net_amount', $paidNetAmount);
        $order->setAttribute('report_paid_ppn_amount', $paidPpnAmount);
        $order->setAttribute('report_total_qty', $reportQty);
        $order->setAttribute('remaining_balance', $remainingBalance);
        $order->setAttribute('report_remaining_balance', $remainingBalance);

        return $order;
    }

    private function buildCustomerSummary(Collection $orders, string $amountField = 'report_total_price', bool $useExactRemaining = false): Collection
    {
        return $orders
            ->groupBy(function ($order) {
                return $order->customer_name ?: '-';
            })
            ->map(function ($group, $customerName) use ($amountField, $useExactRemaining) {
                return (object) [
                    'customer_name' => $customerName,
                    'total_orders' => $group->count(),
                    'total_amount' => (float) $group->sum(function ($order) use ($amountField, $useExactRemaining) {
                        if ($useExactRemaining) {
                            return $this->getExactOrderRemainingBalance($order);
                        }

                        return (float) ($order->{$amountField} ?? 0);
                    }),
                ];
            })
            ->sortByDesc('total_amount')
            ->values();
    }

    private function buildRevenuePaymentRows(Collection $orders, Carbon $start, Carbon $end): Collection
    {
        return $orders
            ->flatMap(function ($order) use ($start, $end) {
                $payments = collect($order->payments ?? [])
                    ->filter(function ($payment) use ($start, $end) {
                        $paidAt = $payment->created_at ? Carbon::parse($payment->created_at) : null;
                        return $paidAt && $paidAt->between($start, $end);
                    })
                    ->sortBy('created_at')
                    ->values();

                $runningPaid = 0;
                $orderTotal = (float) ($order->report_invoice_total ?? $this->getReportInvoiceTotal($order));
                $orderNetTotal = (float) ($order->report_total_price ?? $this->calculateReportOrderTotal($order));
                $orderPpnAmount = (float) ($order->report_ppn_amount ?? $this->getReportPpnAmount($order, $orderNetTotal));

                return $payments->map(function ($payment, $index) use ($order, $payments, &$runningPaid, $orderTotal, $orderNetTotal, $orderPpnAmount) {
                    $grossAmount = (float) ($payment->amount ?? 0);
                    $amount = $this->allocateGrossPaymentToNet($order, $grossAmount, $orderNetTotal, $orderTotal);
                    $ppnAmount = $this->allocateGrossPaymentToPpn($order, $grossAmount, $orderPpnAmount, $orderTotal);
                    $runningPaid += $grossAmount;

                    $statusLabel = 'Lunas';
                    $statusBadge = 'success';

                    if ($payments->count() > 1) {
                        if ($runningPaid < $orderTotal) {
                            $statusLabel = 'Cicilan';
                            $statusBadge = 'warning';
                        } elseif ($runningPaid >= $orderTotal && $index > 0) {
                            $statusLabel = 'Pelunasan';
                            $statusBadge = 'brand';
                        }
                    } elseif ($amount < $orderTotal) {
                        $statusLabel = 'Cicilan';
                        $statusBadge = 'warning';
                    }

                    return (object) [
                        'order_id' => $order->id,
                        'payment_id' => $payment->id,
                        'payment_date' => $payment->created_at,
                        'invoice_number' => $order->invoice_number,
                        'customer_name' => $order->customer_name,
                        'payment_method' => $payment->payment_method ?? null,
                        'payment_method_label' => method_exists($payment, 'getPaymentMethodLabel')
                            ? $payment->getPaymentMethodLabel()
                            : ucfirst((string) ($payment->payment_method ?? '-')),
                        'payment_account_label' => $payment->paymentAccount
                            ? (method_exists($payment->paymentAccount, 'getShortLabel')
                                ? $payment->paymentAccount->getShortLabel()
                                : ($payment->paymentAccount->name ?? '-'))
                            : '-',
                        'cashier_name' => $payment->cashier->name ?? ($order->cashier->name ?? '-'),
                        'items_label' => collect($order->items)->pluck('product_name')->filter()->unique()->implode(', '),
                        'qty' => (float) (collect($order->items)->sum('qty') ?? 0),
                        // amount = pendapatan bersih tanpa PPN.
                        // gross_amount = nominal uang masuk termasuk PPN.
                        'amount' => $amount,
                        'gross_amount' => $grossAmount,
                        'ppn_amount' => $ppnAmount,
                        'status_label' => $statusLabel,
                        'status_badge' => $statusBadge,
                        'notes' => $payment->notes ?? null,
                    ];
                });
            })
            ->sortByDesc(function ($row) {
                return $row->payment_date ? Carbon::parse($row->payment_date)->timestamp : 0;
            })
            ->values();
    }

    private function buildRevenueByProductFromPayments(Collection $orders, Carbon $start, Carbon $end): Collection
    {
        return $orders
            ->flatMap(function ($order) use ($start, $end) {
                $items = collect($order->items ?? []);
                $orderTotal = (float) ($order->report_total_price ?? $this->calculateReportOrderTotal($order));
                $orderInvoiceTotal = (float) ($order->report_invoice_total ?? $this->getReportInvoiceTotal($order));

                if ($orderTotal <= 0 || $orderInvoiceTotal <= 0 || $items->isEmpty()) {
                    return collect();
                }

                $paymentsInPeriod = collect($order->payments ?? [])
                    ->filter(function ($payment) use ($start, $end) {
                        $paidAt = $payment->created_at ? Carbon::parse($payment->created_at) : null;
                        return $paidAt && $paidAt->between($start, $end);
                    });

                $paidInPeriodGross = (float) $paymentsInPeriod->sum('amount');
                $paidInPeriod = $this->allocateGrossPaymentToNet($order, $paidInPeriodGross, $orderTotal, $orderInvoiceTotal);

                if ($paidInPeriod <= 0) {
                    return collect();
                }

                $ratio = min(1, $paidInPeriod / $orderTotal);

                return $items->map(function ($item) use ($order, $ratio) {
                    $itemSubtotal = $this->calculateReportItemSubtotal($item);

                    return [
                        'order_id' => $order->id,
                        'product_id' => $item->product_id,
                        'name' => $item->product_name ?: (optional($item->product)->name ?? '-'),
                        'qty' => (float) ($item->qty ?? 0),
                        'total_revenue' => round($itemSubtotal * $ratio, 2),
                    ];
                });
            })
            ->groupBy(function ($row) {
                return ($row['product_id'] ?? 'null') . '|' . ($row['name'] ?? '-');
            })
            ->map(function ($rows) {
                $first = $rows->first();

                return (object) [
                    'id' => $first['product_id'],
                    'name' => $first['name'],
                    'total_qty' => (float) $rows->sum('qty'),
                    'total_revenue' => (float) $rows->sum('total_revenue'),
                    'total_orders' => $rows->pluck('order_id')->unique()->count(),
                ];
            })
            ->sortByDesc('total_revenue')
            ->values();
    }
}