<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Order;
use App\Models\OrderItem;
use App\Models\Product;
use App\Models\Category;
use App\Models\User;
use App\Models\StockAudit;
use App\Models\StockAdjustment;
use App\Services\PaymentHealthReportService;
use App\Services\ProfitService;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Response;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;
use Carbon\Carbon;

class ReportController extends Controller
{
    public function __construct(
        private readonly PaymentHealthReportService $paymentHealthReportService
    ) {
    }

    /**
     * Sales 360: holistic view with date range, by source, by payment, trend, daily breakdown.
     */
    public function sales(Request $request): View
    {
        $this->authorize('reports.sales');
        $period = $request->input('period', 'last_month');
        $source = $request->input('source', 'all');
        $staffId = $request->input('staff_id') ? (int) $request->input('staff_id') : null;

        // Date range: support day/week/month/year/custom and legacy today/yesterday/last_*
        switch ($period) {
            case 'day':
            case 'today':
                $startDate = now()->format('Y-m-d');
                $endDate = now()->format('Y-m-d');
                break;
            case 'yesterday':
                $startDate = now()->subDay()->format('Y-m-d');
                $endDate = now()->subDay()->format('Y-m-d');
                break;
            case 'week':
                $startDate = now()->startOfWeek()->format('Y-m-d');
                $endDate = now()->endOfWeek()->format('Y-m-d');
                break;
            case 'month':
                $startDate = now()->startOfMonth()->format('Y-m-d');
                $endDate = now()->endOfMonth()->format('Y-m-d');
                break;
            case 'year':
                $startDate = now()->startOfYear()->format('Y-m-d');
                $endDate = now()->endOfYear()->format('Y-m-d');
                break;
            case 'last_7_days':
                $startDate = now()->subDays(6)->format('Y-m-d');
                $endDate = now()->format('Y-m-d');
                break;
            case 'last_month':
                $startDate = now()->subDays(29)->format('Y-m-d');
                $endDate = now()->format('Y-m-d');
                break;
            case 'last_year':
                $startDate = now()->subDays(364)->format('Y-m-d');
                $endDate = now()->format('Y-m-d');
                break;
            case 'custom':
                $startDate = $request->input('start_date', now()->subDays(29)->format('Y-m-d'));
                $endDate = $request->input('end_date', now()->format('Y-m-d'));
                break;
            default:
                $startDate = now()->subDays(29)->format('Y-m-d');
                $endDate = now()->format('Y-m-d');
        }

        $startDateTime = Carbon::parse($startDate)->startOfDay();
        $endDateTime = Carbon::parse($endDate)->endOfDay();

        $staff = User::whereIn('user_type', ['admin', 'staff'])
            ->select('id', 'name', 'email')
            ->orderBy('name')
            ->get();

        $baseQuery = Order::paid()
            ->whereNotIn('status', [Order::STATUS_CANCELLED, Order::STATUS_RETURNED])
            ->whereBetween('created_at', [$startDateTime, $endDateTime]);

        if ($source !== 'all') {
            if ($source === 'online') {
                $baseQuery->where('source', 'online');
            } else {
                $baseQuery->whereIn('source', ['pos', 'pos_to_online']);
            }
        }
        if ($source === 'pos' && $staffId) {
            $baseQuery->where('staff_id', $staffId);
        }

        // Daily breakdown for table and chart
        $salesData = (clone $baseQuery)->select(
            DB::raw('DATE(created_at) as date'),
            DB::raw('COUNT(*) as orders_count'),
            DB::raw('SUM(total_payable) as revenue'),
            DB::raw('SUM(discount_total) as discounts')
        )
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        $totalRevenue = $salesData->sum('revenue');
        $totalOrders = $salesData->sum('orders_count');
        $totalDiscounts = $salesData->sum('discounts');
        $avgOrderValue = $totalOrders > 0 ? $totalRevenue / $totalOrders : 0;

        // By source (Online vs POS) for the same filters except source
        $bySourceQuery = Order::paid()
            ->whereNotIn('status', [Order::STATUS_CANCELLED, Order::STATUS_RETURNED])
            ->whereBetween('created_at', [$startDateTime, $endDateTime]);
        if ($staffId) {
            $bySourceQuery->where('staff_id', $staffId);
        }
        $bySource = $bySourceQuery->select('source', DB::raw('SUM(total_payable) as revenue'), DB::raw('COUNT(*) as orders_count'))
            ->groupBy('source')
            ->get()
            ->keyBy('source');
        $bySource = [
            'online' => [
                'revenue' => (float) ($bySource->get('online')->revenue ?? 0),
                'orders' => (int) ($bySource->get('online')->orders_count ?? 0),
            ],
            'pos' => [
                'revenue' => (float) (($bySource->get('pos')->revenue ?? 0) + ($bySource->get('pos_to_online')->revenue ?? 0)),
                'orders' => (int) (($bySource->get('pos')->orders_count ?? 0) + ($bySource->get('pos_to_online')->orders_count ?? 0)),
            ],
        ];

        // By payment method
        $byPaymentQuery = Order::paid()
            ->whereNotIn('status', [Order::STATUS_CANCELLED, Order::STATUS_RETURNED])
            ->whereBetween('created_at', [$startDateTime, $endDateTime]);
        if ($source !== 'all') {
            if ($source === 'online') {
                $byPaymentQuery->where('source', 'online');
            } else {
                $byPaymentQuery->whereIn('source', ['pos', 'pos_to_online']);
            }
        }
        if ($staffId) {
            $byPaymentQuery->where('staff_id', $staffId);
        }
        $paymentRows = $byPaymentQuery->select('payment_method', DB::raw('SUM(total_payable) as revenue'), DB::raw('COUNT(*) as orders_count'))
            ->groupBy('payment_method')
            ->get();
        $byPayment = [];
        foreach ($paymentRows as $row) {
            $label = $row->payment_method === 'cod' ? 'COD' : (in_array($row->payment_method, ['online', 'razorpay'], true) ? 'Online' : ($row->payment_method === 'offline' ? 'Offline' : ucfirst($row->payment_method ?? 'Other')));
            if (!isset($byPayment[$label])) {
                $byPayment[$label] = ['revenue' => 0, 'orders' => 0];
            }
            $byPayment[$label]['revenue'] += (float) $row->revenue;
            $byPayment[$label]['orders'] += (int) $row->orders_count;
        }

        $salesTrendData = $salesData->map(fn ($d) => [
            'date' => $d->date,
            'revenue' => (float) $d->revenue,
            'orders' => (int) $d->orders_count,
        ])->values();

        return view('admin.reports.sales', compact(
            'salesData',
            'salesTrendData',
            'startDate',
            'endDate',
            'totalRevenue',
            'totalOrders',
            'totalDiscounts',
            'avgOrderValue',
            'bySource',
            'byPayment',
            'period',
            'source',
            'staffId',
            'staff'
        ));
    }

    public function topProducts(Request $request): View
    {
        $limit = $request->input('limit', 20);

        $topProducts = OrderItem::select(
            'product_id',
            'name',
            DB::raw('SUM(qty) as total_sold'),
            DB::raw('SUM(line_total) as total_revenue')
        )
            ->groupBy('product_id', 'name')
            ->orderByDesc('total_sold')
            ->limit($limit)
            ->get();

        return view('admin.reports.top-products', compact('topProducts'));
    }

    public function payments(Request $request): View
    {
        $startDate = $request->input('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $paymentMethod = $request->input('payment_method');
        $transactionType = $request->input('transaction_type');
        $orderId = $request->input('order_id');
        $txId = $request->input('tx_id');
        $status = $request->input('status');

        // Calculate KPIs
        $kpis = $this->calculatePaymentKPIs($startDate, $endDate, $paymentMethod, $transactionType, $status);

        return view('admin.reports.payments', compact(
            'startDate',
            'endDate',
            'paymentMethod',
            'transactionType',
            'orderId',
            'txId',
            'status',
            'kpis'
        ));
    }

    /**
     * Lightweight Razorpay payment-health dashboard page.
     */
    public function paymentHealth(Request $request): View
    {
        $this->authorize('reports.payments');

        $days = (int) $request->input('days', 7);
        $days = min(max($days, 1), 30);
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $startDate = Carbon::parse($endDate)->subDays($days - 1)->format('Y-m-d');
        $daily = $this->paymentHealthReportService->summarizeRange($startDate, $endDate);
        $aggregate = $this->paymentHealthReportService->aggregate($daily);

        return view('admin.reports.payment-health', compact('startDate', 'endDate', 'days', 'daily', 'aggregate'));
    }

    /**
     * API endpoint: payment-health summary from payment logs.
     */
    public function paymentHealthApi(Request $request): JsonResponse
    {
        $this->authorize('reports.payments');

        $days = (int) $request->input('days', 7);
        $days = min(max($days, 1), 30);
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $startDate = Carbon::parse($endDate)->subDays($days - 1)->format('Y-m-d');

        $daily = $this->paymentHealthReportService->summarizeRange($startDate, $endDate);
        $aggregate = $this->paymentHealthReportService->aggregate($daily);

        return response()->json([
            'gateway' => 'razorpay',
            'range' => [
                'start_date' => $startDate,
                'end_date' => $endDate,
                'days' => $days,
            ],
            'aggregate' => $aggregate,
            'daily' => $daily,
            'generated_at' => now()->toDateTimeString(),
        ]);
    }

    /**
     * Calculate payment KPIs
     * Optimized to use fewer queries with conditional aggregation
     */
    private function calculatePaymentKPIs(string $startDate, string $endDate, ?string $paymentMethod = null, ?string $transactionType = null, ?string $status = null): array
    {
        $startDateTime = Carbon::parse($startDate)->startOfDay();
        $endDateTime = Carbon::parse($endDate)->endOfDay();

        $baseQuery = Order::whereBetween('created_at', [$startDateTime, $endDateTime]);

        if ($paymentMethod && $paymentMethod !== 'all') {
            $baseQuery->where('payment_method', $paymentMethod);
        }

        if ($transactionType && $transactionType !== 'all') {
            $baseQuery->where('source', $transactionType);
        }

        // Get aggregated data in a single query for better performance
        $aggregated = (clone $baseQuery)
            ->select(
                DB::raw('SUM(CASE WHEN payment_status = "paid" THEN total_payable ELSE 0 END) as total_received'),
                DB::raw('SUM(CASE WHEN payment_status = "failed" THEN 1 ELSE 0 END) as failed'),
                DB::raw('SUM(CASE WHEN payment_status = "pending" THEN 1 ELSE 0 END) as pending'),
                DB::raw('SUM(CASE WHEN source = "pos" AND payment_status = "paid" THEN total_payable ELSE 0 END) as pos_total'),
                DB::raw('SUM(CASE WHEN source = "online" AND payment_status = "paid" THEN total_payable ELSE 0 END) as online_total'),
                DB::raw('SUM(CASE WHEN payment_status = "refunded" THEN total_payable ELSE 0 END) as refunds'),
                DB::raw('SUM(CASE WHEN payment_status IN ("paid", "failed") THEN 1 ELSE 0 END) as total_attempts'),
                DB::raw('SUM(CASE WHEN payment_status = "paid" THEN 1 ELSE 0 END) as success_count')
            )
            ->first();

        $totalReceived = (float) ($aggregated->total_received ?? 0);
        $failed = (int) ($aggregated->failed ?? 0);
        $pending = (int) ($aggregated->pending ?? 0);
        $posTotal = (float) ($aggregated->pos_total ?? 0);
        $onlineTotal = (float) ($aggregated->online_total ?? 0);
        $refunds = (float) ($aggregated->refunds ?? 0);
        $totalAttempts = (int) ($aggregated->total_attempts ?? 0);
        $successCount = (int) ($aggregated->success_count ?? 0);
        $successRate = $totalAttempts > 0 ? round(($successCount / $totalAttempts) * 100, 2) : 0;

        return [
            'total_received' => $totalReceived,
            'failed' => $failed,
            'pending' => $pending,
            'pos_total' => $posTotal,
            'online_total' => $onlineTotal,
            'refunds' => $refunds,
            'success_rate' => $successRate,
        ];
    }

    /**
     * API endpoint for payment summary data
     */
    public function paymentSummaryApi(Request $request): JsonResponse
    {
        $startDate = $request->input('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $paymentMethod = $request->input('payment_method');
        $transactionType = $request->input('transaction_type');
        $status = $request->input('status');

        $startDateTime = Carbon::parse($startDate)->startOfDay();
        $endDateTime = Carbon::parse($endDate)->endOfDay();

        // Calculate KPIs
        $kpis = $this->calculatePaymentKPIs($startDate, $endDate, $paymentMethod, $transactionType, $status);

        // Trend Chart Data (daily payment amounts by status)
        $trendQuery = Order::select(
            DB::raw('DATE(created_at) as date'),
            'payment_status',
            DB::raw('SUM(total_payable) as amount'),
            DB::raw('COUNT(*) as count')
        )
        ->whereBetween('created_at', [$startDateTime, $endDateTime]);

        if ($paymentMethod) {
            $trendQuery->where('payment_method', $paymentMethod);
        }

        if ($transactionType && $transactionType !== 'all') {
            $trendQuery->where('source', $transactionType);
        }

        $trendData = $trendQuery->groupBy('date', 'payment_status')
            ->orderBy('date')
            ->get()
            ->groupBy('date')
            ->map(function ($dayData) {
                return [
                    'date' => $dayData->first()->date,
                    'paid' => $dayData->where('payment_status', 'paid')->sum('amount'),
                    'failed' => $dayData->where('payment_status', 'failed')->sum('amount'),
                    'pending' => $dayData->where('payment_status', 'pending')->sum('amount'),
                    'refunded' => $dayData->where('payment_status', 'refunded')->sum('amount'),
                ];
            })
            ->values();

        // POS vs Online Chart
        $posVsOnlineQuery = Order::select(
            'source',
            DB::raw('SUM(total_payable) as total'),
            DB::raw('COUNT(*) as count')
        )
        ->where('payment_status', 'paid')
        ->whereBetween('created_at', [$startDateTime, $endDateTime]);

        if ($paymentMethod) {
            $posVsOnlineQuery->where('payment_method', $paymentMethod);
        }

        $posVsOnline = $posVsOnlineQuery->groupBy('source')
            ->get()
            ->map(function ($item) {
                return [
                    'source' => $item->source,
                    'total' => (float) $item->total,
                    'count' => $item->count,
                ];
            });

        // Payment Method Pie Chart
        $paymentMethodQuery = Order::select(
            'payment_method',
            DB::raw('SUM(total_payable) as total'),
            DB::raw('COUNT(*) as count')
        )
        ->where('payment_status', 'paid')
        ->whereBetween('created_at', [$startDateTime, $endDateTime])
        ->whereNotNull('payment_method');

        if ($transactionType && $transactionType !== 'all') {
            $paymentMethodQuery->where('source', $transactionType);
        }

        $paymentMethodPie = $paymentMethodQuery->groupBy('payment_method')
            ->get()
            ->map(function ($item) {
                return [
                    'method' => $item->payment_method,
                    'total' => (float) $item->total,
                    'count' => $item->count,
                ];
            });

        return response()->json([
            'kpis' => $kpis,
            'charts' => [
                'trend_data' => $trendData,
                'pos_vs_online' => $posVsOnline,
                'payment_method_pie' => $paymentMethodPie,
            ],
        ]);
    }

    /**
     * API endpoint for transaction list
     */
    public function paymentTransactionsApi(Request $request): JsonResponse
    {
        $startDate = $request->input('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $paymentMethod = $request->input('payment_method');
        $transactionType = $request->input('transaction_type');
        $orderId = $request->input('order_id');
        $txId = $request->input('tx_id');
        $status = $request->input('status');
        $perPage = $request->input('per_page', 15);
        $page = $request->input('page', 1);

        $startDateTime = Carbon::parse($startDate)->startOfDay();
        $endDateTime = Carbon::parse($endDate)->endOfDay();

        $query = Order::with(['user:id,name,mobile', 'staff:id,name'])
            ->whereBetween('created_at', [$startDateTime, $endDateTime]);

        if ($paymentMethod && $paymentMethod !== 'all') {
            $query->where('payment_method', $paymentMethod);
        }

        if ($transactionType && $transactionType !== 'all') {
            $query->where('source', $transactionType);
        }

        if ($status && $status !== 'all') {
            $query->where('payment_status', $status);
        }

        if ($orderId) {
            $query->where('id', $orderId);
        }

        if ($txId) {
            $query->where(function ($q) use ($txId) {
                $q->where('razorpay_payment_id', $txId)
                  ->orWhere('razorpay_order_id', $txId);
            });
        }

        $transactions = $query->orderBy('created_at', 'desc')
            ->paginate($perPage, ['*'], 'page', $page);

        $formattedTransactions = $transactions->map(function ($order) {
            $paymentMethod = $order->payment_method ?? 'N/A';
            $paymentDetails = $paymentMethod;
            
            if ($order->payment_method === 'offline') {
                $paymentDetails = 'Offline' . (!empty($order->payment_screenshot_path) ? ' (Screenshot attached)' : '');
            } elseif ($order->isSplitPayment()) {
                $paymentDetails = 'Split Payment';
                if ($order->cash_amount) {
                    $paymentDetails .= ' (Cash: ₹' . number_format($order->cash_amount, 2);
                }
                if ($order->card_amount) {
                    $paymentDetails .= $order->cash_amount ? ', Card: ₹' : ' (Card: ₹';
                    $paymentDetails .= number_format($order->card_amount, 2) . ')';
                }
            } elseif ($order->cash_amount) {
                $paymentDetails .= ' (Cash: ₹' . number_format($order->cash_amount, 2) . ')';
            } elseif ($order->card_amount) {
                $paymentDetails .= ' (Card: ₹' . number_format($order->card_amount, 2) . ')';
            }
            
            return [
                'id' => $order->id,
                'order_id' => $order->id,
                'tx_id' => $order->razorpay_payment_id ?? $order->razorpay_order_id ?? 'N/A',
                'date' => $order->created_at->format('Y-m-d H:i:s'),
                'customer' => $order->user ? $order->user->name : 'Guest',
                'customer_mobile' => $order->user ? $order->user->mobile : null,
                'amount' => (float) $order->total_payable,
                'payment_method' => $paymentMethod,
                'payment_details' => $paymentDetails,
                'cash_amount' => $order->cash_amount ? (float) $order->cash_amount : null,
                'card_amount' => $order->card_amount ? (float) $order->card_amount : null,
                'is_split_payment' => $order->isSplitPayment(),
                'source' => $order->source,
                'status' => $order->payment_status,
                'staff' => $order->staff ? $order->staff->name : null,
            ];
        });

        return response()->json([
            'data' => $formattedTransactions,
            'pagination' => [
                'current_page' => $transactions->currentPage(),
                'last_page' => $transactions->lastPage(),
                'per_page' => $transactions->perPage(),
                'total' => $transactions->total(),
                'from' => $transactions->firstItem(),
                'to' => $transactions->lastItem(),
            ],
        ]);
    }

    /**
     * Export payment transactions to CSV
     */
    public function exportPaymentCsv(Request $request): StreamedResponse
    {
        $startDate = $request->input('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $paymentMethod = $request->input('payment_method');
        $transactionType = $request->input('transaction_type');
        $orderId = $request->input('order_id');
        $txId = $request->input('tx_id');
        $status = $request->input('status');

        $startDateTime = Carbon::parse($startDate)->startOfDay();
        $endDateTime = Carbon::parse($endDate)->endOfDay();

        $query = Order::with(['user:id,name,mobile', 'staff:id,name'])
            ->whereBetween('created_at', [$startDateTime, $endDateTime]);

        if ($paymentMethod && $paymentMethod !== 'all') {
            $query->where('payment_method', $paymentMethod);
        }

        if ($transactionType && $transactionType !== 'all') {
            $query->where('source', $transactionType);
        }

        if ($status && $status !== 'all') {
            $query->where('payment_status', $status);
        }

        if ($orderId) {
            $query->where('id', $orderId);
        }

        if ($txId) {
            $query->where(function ($q) use ($txId) {
                $q->where('razorpay_payment_id', $txId)
                  ->orWhere('razorpay_order_id', $txId);
            });
        }

        $orders = $query->orderBy('created_at', 'desc')->get();

        $filename = 'payment_report_' . now()->format('Y-m-d_H-i-s') . '.csv';

        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
        ];

        $callback = function() use ($orders) {
            $file = fopen('php://output', 'w');
            fprintf($file, chr(0xEF).chr(0xBB).chr(0xBF));
            
            // CSV Headers
            fputcsv($file, [
                'Order ID',
                'Transaction ID',
                'Date',
                'Customer Name',
                'Customer Mobile',
                'Amount',
                'Payment Method',
                'Cash Amount',
                'Card/UPI Amount',
                'Source',
                'Status',
                'Staff',
            ]);

            // CSV Data
            foreach ($orders as $order) {
                $paymentMethod = $order->payment_method ?? 'N/A';
                if ($order->isSplitPayment()) {
                    $paymentMethod = 'Split Payment';
                }
                
                fputcsv($file, [
                    '#'.$order->id,
                    $order->razorpay_payment_id ?? $order->razorpay_order_id ?? 'N/A',
                    $order->created_at->format('Y-m-d H:i:s'),
                    $order->user ? $order->user->name : 'Guest',
                    $order->user ? $order->user->mobile : '',
                    '₹' . $order->total_payable,
                    $paymentMethod,
                    $order->cash_amount ? '₹' . number_format($order->cash_amount, 2) : '',
                    $order->card_amount ? '₹' . number_format($order->card_amount, 2) : '',
                    $order->source,
                    $order->payment_status,
                    $order->staff ? $order->staff->name : '',
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Profit overview report
     */
    public function profit(Request $request): View
    {
        $profitService = app(ProfitService::class);
        
        $filters = [
            'start_date' => $request->input('start_date', now()->subDays(30)->format('Y-m-d')),
            'end_date' => $request->input('end_date', now()->format('Y-m-d')),
            'product_id' => $request->input('product_id'),
            'category_id' => $request->input('category_id'),
            'order_status' => $request->input('order_status'),
            'payment_status' => $request->input('payment_status', 'paid'),
        ];

        $profitMetrics = $profitService->getProfitReport($filters);
        $profitByProduct = $profitService->getProfitByProduct($filters);
        $topProducts = $profitService->getTopProfitableProducts(10, $filters);
        
        $products = Product::active()->get(['id', 'name']);
        $categories = Category::all(['id', 'name']);

        return view('admin.reports.profit', compact(
            'profitMetrics',
            'profitByProduct',
            'topProducts',
            'products',
            'categories',
            'filters'
        ));
    }

    /**
     * Profit by product report
     */
    public function profitByProduct(Request $request): View
    {
        $profitService = app(ProfitService::class);
        
        $filters = [
            'start_date' => $request->input('start_date', now()->subDays(30)->format('Y-m-d')),
            'end_date' => $request->input('end_date', now()->format('Y-m-d')),
            'category_id' => $request->input('category_id'),
            'order_status' => $request->input('order_status'),
        ];

        $profitByProduct = $profitService->getProfitByProduct($filters);
        $categories = Category::all(['id', 'name']);

        return view('admin.reports.profit-by-product', compact(
            'profitByProduct',
            'categories',
            'filters'
        ));
    }

    /**
     * Profit by order report
     */
    public function profitByOrder(Request $request): View
    {
        $startDate = $request->input('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $orderStatus = $request->input('order_status');

        $query = Order::with(['items' => function ($q) {
            $q->whereNotNull('purchase_price');
        }])
        ->whereBetween('created_at', [$startDate, $endDate])
        ->where('payment_status', 'paid');

        if ($orderStatus) {
            $query->where('status', $orderStatus);
        }

        $orders = $query->get()->map(function ($order) {
            $profitService = app(ProfitService::class);
            $metrics = $profitService->calculateProfitMetrics($order->items);
            
            return [
                'order_id' => $order->id,
                'date' => $order->created_at->format('Y-m-d'),
                'customer' => $order->user->name ?? 'Guest',
                'total_payable' => $order->total_payable,
                'revenue' => $metrics['total_revenue'],
                'cogs' => $metrics['total_cogs'],
                'profit' => $metrics['total_profit'],
                'margin_percent' => $metrics['profit_margin_percent'],
                'status' => $order->status,
            ];
        });

        return view('admin.reports.profit-by-order', compact(
            'orders',
            'startDate',
            'endDate',
            'orderStatus'
        ));
    }

    /**
     * Export profit report to CSV
     */
    public function exportProfitCsv(Request $request): StreamedResponse
    {
        $profitService = app(ProfitService::class);
        
        $filters = [
            'start_date' => $request->input('start_date', now()->subDays(30)->format('Y-m-d')),
            'end_date' => $request->input('end_date', now()->format('Y-m-d')),
            'product_id' => $request->input('product_id'),
            'category_id' => $request->input('category_id'),
            'order_status' => $request->input('order_status'),
            'payment_status' => $request->input('payment_status', 'paid'),
        ];

        $profitByProduct = $profitService->getProfitByProduct($filters);

        $csvData = [];
        $csvData[] = [
            'Product Name',
            'SKU',
            'Units Sold',
            'Total Revenue',
            'Total COGS',
            'Gross Profit',
            'Margin %'
        ];

        foreach ($profitByProduct as $product) {
            $csvData[] = [
                $product['product_name'],
                $product['sku'],
                $product['units_sold'],
               '₹' . $product['total_revenue'],
               '₹' . $product['total_cogs'],
               '₹' . $product['gross_profit'],
                $product['margin_percent'] . '%'
            ];
        }

        $filename = 'profit_report_' . now()->format('Y-m-d_H-i-s') . '.csv';
        
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
        ];

        $callback = function() use ($csvData) {
            $file = fopen('php://output', 'w');
            fprintf($file, chr(0xEF).chr(0xBB).chr(0xBF));
            foreach ($csvData as $row) {
                fputcsv($file, $row);
            }
            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Products missing purchase price report
     */
    public function missingPurchasePrice(Request $request): View
    {
        $products = Product::query()
            ->active()
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->whereNull('parent_id')
                        ->where('product_type', 'single');
                })->orWhereNotNull('parent_id');
            })
            ->missingPurchasePrice()
            ->with('category')
            ->paginate(50);

        $totalProducts = Product::query()
            ->active()
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->whereNull('parent_id')
                        ->where('product_type', 'single');
                })->orWhereNotNull('parent_id');
            })
            ->count();

        $missingCount = Product::query()
            ->active()
            ->where(function ($query) {
                $query->where(function ($q) {
                    $q->whereNull('parent_id')
                        ->where('product_type', 'single');
                })->orWhereNotNull('parent_id');
            })
            ->missingPurchasePrice()
            ->count();

        $coveragePercent = $totalProducts > 0 ? round((($totalProducts - $missingCount) / $totalProducts) * 100, 2) : 0;

        return view('admin.reports.missing-purchase-price', compact(
            'products',
            'totalProducts',
            'missingCount',
            'coveragePercent'
        ));
    }

    /**
     * Salesman performance report (POS only, paid orders, excludes cancelled/returned)
     */
    public function salesmanReport(Request $request): View
    {
        $this->authorize('reports.salesman');
        $data = $this->getSalesmanReportData($request);
        return view('admin.reports.salesman', $data);
    }

    /**
     * Export salesman report to CSV
     */
    public function exportSalesmanCsv(Request $request): StreamedResponse
    {
        $this->authorize('reports.salesman');
        $data = $this->getSalesmanReportData($request);
        $salesmanData = $data['salesmanData'];

        $csvData = [];
        $csvData[] = [
            'Salesman',
            'Email',
            'Orders',
            'Total Sales (₹)',
            'Total Profit (₹)',
            'Profit Margin %',
            'Avg Order Value (₹)',
            'Total Discounts (₹)',
        ];

        foreach ($salesmanData as $row) {
            $csvData[] = [
                $row['staff_name'],
                $row['staff_email'] ?? '',
                $row['total_orders'],
                number_format($row['total_sales'], 2),
                number_format($row['total_profit'], 2),
                number_format($row['profit_margin'] ?? 0, 1),
                number_format($row['avg_order_value'], 2),
                number_format($row['total_discounts'], 2),
            ];
        }

        $filename = 'salesman_report_' . ($data['startDate'] ?? '') . '_to_' . ($data['endDate'] ?? '') . '_' . now()->format('Y-m-d_H-i-s') . '.csv';
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
        ];

        $callback = function () use ($csvData) {
            $file = fopen('php://output', 'w');
            fprintf($file, chr(0xEF) . chr(0xBB) . chr(0xBF));
            foreach ($csvData as $row) {
                fputcsv($file, $row);
            }
            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Build salesman report data (shared by page and CSV export).
     *
     * @return array{salesmanData: \Illuminate\Support\Collection, staff: \Illuminate\Support\Collection, startDate: string, endDate: string, period: string, staffId: int|null, totalSales: float, totalOrders: int|float, totalProfit: float, totalDiscounts: float}
     */
    private function getSalesmanReportData(Request $request): array
    {
        $startDate = $request->input('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $period = $request->input('period', 'custom');
        $staffId = $request->input('staff_id') ? (int) $request->input('staff_id') : null;

        switch ($period) {
            case 'day':
                $startDate = now()->format('Y-m-d');
                $endDate = now()->format('Y-m-d');
                break;
            case 'week':
                $startDate = now()->startOfWeek()->format('Y-m-d');
                $endDate = now()->endOfWeek()->format('Y-m-d');
                break;
            case 'month':
                $startDate = now()->startOfMonth()->format('Y-m-d');
                $endDate = now()->endOfMonth()->format('Y-m-d');
                break;
            case 'year':
                $startDate = now()->startOfYear()->format('Y-m-d');
                $endDate = now()->endOfYear()->format('Y-m-d');
                break;
        }

        $startDateTime = Carbon::parse($startDate)->startOfDay();
        $endDateTime = Carbon::parse($endDate)->endOfDay();

        // Use user_type (same as POS StaffController) so dropdown matches staff/admin list
        $staff = User::whereIn('user_type', ['admin', 'staff'])
            ->select('id', 'name', 'email')
            ->orderBy('name')
            ->get();

        $query = Order::query()
            ->where('source', 'pos')
            ->where('payment_status', Order::PAYMENT_PAID)
            ->whereNotIn('status', [Order::STATUS_CANCELLED, Order::STATUS_RETURNED])
            ->whereBetween('created_at', [$startDateTime, $endDateTime]);

        if ($staffId) {
            $query->where('staff_id', $staffId);
        }

        $salesmanData = $query->select(
            'staff_id',
            DB::raw('COUNT(*) as total_orders'),
            DB::raw('SUM(total_payable) as total_sales'),
            DB::raw('SUM(discount_total) as total_discounts'),
            DB::raw('AVG(total_payable) as avg_order_value')
        )
            ->groupBy('staff_id')
            ->get()
            ->map(function ($item) use ($staff) {
                $staffMember = $item->staff_id ? $staff->find($item->staff_id) : null;
                return [
                    'staff_id' => $item->staff_id,
                    'staff_name' => $staffMember ? $staffMember->name : 'Unassigned / Walk-in',
                    'staff_email' => $staffMember ? $staffMember->email : '',
                    'total_orders' => (int) $item->total_orders,
                    'total_sales' => (float) $item->total_sales,
                    'total_discounts' => (float) $item->total_discounts,
                    'avg_order_value' => (float) $item->avg_order_value,
                ];
            })
            ->sortByDesc('total_sales')
            ->values();

        $profitService = app(ProfitService::class);
        $salesmanData = $salesmanData->map(function ($item) use ($profitService, $startDateTime, $endDateTime) {
            $profitData = $profitService->getProfitReport([
                'start_date' => $startDateTime->format('Y-m-d H:i:s'),
                'end_date' => $endDateTime->format('Y-m-d H:i:s'),
                'payment_status' => Order::PAYMENT_PAID,
                'source' => 'pos',
                'staff_id' => $item['staff_id'],
                'exclude_statuses' => [Order::STATUS_CANCELLED, Order::STATUS_RETURNED],
            ]);

            $item['total_profit'] = $profitData['total_profit'] ?? 0;
            $item['profit_margin'] = $profitData['profit_margin_percent'] ?? 0;
            $item['total_cogs'] = $profitData['total_cogs'] ?? 0;

            return $item;
        });

        return [
            'salesmanData' => $salesmanData,
            'staff' => $staff,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'period' => $period,
            'staffId' => $staffId,
            'totalSales' => $salesmanData->sum('total_sales'),
            'totalOrders' => $salesmanData->sum('total_orders'),
            'totalProfit' => $salesmanData->sum('total_profit'),
            'totalDiscounts' => $salesmanData->sum('total_discounts'),
        ];
    }

    /**
     * GST Report
     */
    public function gst(Request $request): View
    {
        $this->authorize('reports.gst');
        
        $year = $request->input('year');
        $month = $request->input('month');
        $allowedOrderTypes = ['all', 'pos', 'online', 'pos_to_online'];
        $orderType = $request->input('order_type', 'all');
        if (!in_array($orderType, $allowedOrderTypes, true)) {
            $orderType = 'all';
        }
        $orders = null;
        
        // Only fetch orders if year and month are provided
        if ($year && $month) {
            $startDate = Carbon::create($year, $month, 1)->startOfMonth();
            $endDate = Carbon::create($year, $month, 1)->endOfMonth();
            
            $query = Order::with(['user:id,name,mobile,gst_number'])
                ->where('payment_status', Order::PAYMENT_PAID)
                ->whereNotIn('status', [Order::STATUS_CANCELLED, Order::STATUS_RETURNED])
                ->whereBetween('created_at', [$startDate, $endDate]);

            if ($orderType !== 'all') {
                $query->where('source', $orderType);
            }

            $orders = $query->orderBy('created_at', 'desc')
                ->paginate(15)
                ->withQueryString();
        }
        
        // Generate years list (current year and past 5 years)
        $currentYear = now()->year;
        $years = [];
        for ($i = 0; $i <= 5; $i++) {
            $years[] = $currentYear - $i;
        }
        
        // Months list
        $months = [
            1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
            5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
            9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December'
        ];
        
        return view('admin.reports.gst', compact('orders', 'year', 'month', 'years', 'months', 'orderType'));
    }

    /**
     * Export GST Report to CSV
     */
    public function exportGstCsv(Request $request): StreamedResponse
    {
        $this->authorize('reports.gst');
        
        $year = $request->input('year');
        $month = $request->input('month');
        $allowedOrderTypes = ['all', 'pos', 'online', 'pos_to_online'];
        $orderType = $request->input('order_type', 'all');
        if (!in_array($orderType, $allowedOrderTypes, true)) {
            $orderType = 'all';
        }
        
        if (!$year || !$month) {
            abort(400, 'Year and month are required');
        }
        
        $startDate = Carbon::create($year, $month, 1)->startOfMonth();
        $endDate = Carbon::create($year, $month, 1)->endOfMonth();
        
        $query = Order::with(['user:id,name,mobile,gst_number'])
            ->where('payment_status', Order::PAYMENT_PAID)
            ->whereNotIn('status', [Order::STATUS_CANCELLED, Order::STATUS_RETURNED])
            ->whereBetween('created_at', [$startDate, $endDate]);

        if ($orderType !== 'all') {
            $query->where('source', $orderType);
        }

        $orders = $query->orderBy('created_at', 'desc')->get();
        
        $filename = 'gst_report_' . $year . '_' . str_pad($month, 2, '0', STR_PAD_LEFT) . '_' . now()->format('Y-m-d_H-i-s') . '.csv';
        
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
        ];
        
        $callback = function() use ($orders) {
            $file = fopen('php://output', 'w');
            fprintf($file, chr(0xEF).chr(0xBB).chr(0xBF));
            
            // CSV Headers
            fputcsv($file, [
                'Order ID',
                'Order Type',
                'Customer Name',
                'Phone Number',
                'GST Number',
                'Order Value',
                'Tax Value',
                'Pincode',
            ]);
            
            // CSV Data
            foreach ($orders as $order) {
                $addressSnapshot = $order->address_snapshot ?? [];
                $pincode = $addressSnapshot['pin'] ?? '';
                $orderTypeLabel = match ($order->source) {
                    'pos' => 'POS',
                    'online' => 'Online',
                    'pos_to_online' => 'POS to Online',
                    default => ucwords(str_replace('_', ' ', (string) $order->source)),
                };
                
                fputcsv($file, [
                    '#' . $order->id,
                    $orderTypeLabel,
                    $order->user ? $order->user->name : 'N/A',
                    $order->user ? $order->user->mobile : 'N/A',
                    $order->user ? ($order->user->gst_number ?? '') : '',
                    '₹' . number_format($order->total_payable, 2),
                    '₹' . number_format($order->tax_total, 2),
                    $pincode,
                ]);
            }
            
            fclose($file);
        };
        
        return response()->stream($callback, 200, $headers);
    }

    /**
     * Stock Audit Report: list completed audits with total products checked, excess, shortage.
     */
    public function stockAudit(Request $request): View
    {
        $this->authorize('reports.stock-audit');
        $startDate = $request->input('start_date', now()->subMonths(3)->format('Y-m-d'));
        $endDate = $request->input('end_date', now()->format('Y-m-d'));
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $audits = StockAudit::with(['creator', 'items'])
            ->where('status', 'completed')
            ->whereBetween('audit_date', [$start->format('Y-m-d'), $end->format('Y-m-d')])
            ->orderBy('audit_date', 'desc')
            ->get()
            ->map(function ($audit) {
                $items = $audit->items;
                $totalChecked = $items->count();
                $totalExcess = $items->whereNotNull('difference_qty')->filter(fn ($i) => (float) $i->difference_qty > 0)->sum('difference_qty');
                $totalShortage = abs($items->whereNotNull('difference_qty')->filter(fn ($i) => (float) $i->difference_qty < 0)->sum('difference_qty'));
                return (object) [
                    'audit' => $audit,
                    'total_checked' => $totalChecked,
                    'total_excess' => $totalExcess,
                    'total_shortage' => $totalShortage,
                ];
            });

        return view('admin.reports.stock-audit', compact('audits', 'startDate', 'endDate'));
    }

    /**
     * Adjustment History Report: list of stock adjustments with filters.
     */
    public function adjustmentHistory(Request $request): View
    {
        $this->authorize('reports.stock-adjustments');
        $query = StockAdjustment::with(['product', 'variant', 'creator'])->latest();
        if ($request->filled('start_date')) {
            $query->whereDate('created_at', '>=', $request->input('start_date'));
        }
        if ($request->filled('end_date')) {
            $query->whereDate('created_at', '<=', $request->input('end_date'));
        }
        if ($request->filled('reference_type') && in_array($request->reference_type, ['audit', 'manual'])) {
            $query->where('reference_type', $request->reference_type);
        }
        $adjustments = $query->paginate(25)->withQueryString();
        return view('admin.reports.adjustment-history', compact('adjustments'));
    }
}