<?php

namespace App\Http\Controllers\Api\Admin;

use App\Http\Controllers\Controller;
use App\Models\Customer;
use App\Models\Order;
use App\Models\Product;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ReportController extends Controller
{
    /**
     * Sales report.
     */
    public function sales(Request $request)
    {
        $request->validate([
            'from' => 'nullable|date',
            'to' => 'nullable|date|after_or_equal:from',
            'group_by' => 'nullable|in:day,week,month,year',
        ]);

        $from = $request->get('from', now()->subDays(30)->toDateString());
        $to = $request->get('to', now()->toDateString());
        $groupBy = $request->get('group_by', 'day');

        $dateFormat = match ($groupBy) {
            'day' => '%Y-%m-%d',
            'week' => '%Y-%u',
            'month' => '%Y-%m',
            'year' => '%Y',
        };

        $sales = Order::where('status', '!=', 'cancelled')
            ->whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->select(
                DB::raw("DATE_FORMAT(created_at, '{$dateFormat}') as period"),
                DB::raw('COUNT(*) as orders_count'),
                DB::raw('SUM(subtotal) as subtotal'),
                DB::raw('SUM(discount) as discount'),
                DB::raw('SUM(delivery_fee) as delivery_fee'),
                DB::raw('SUM(total) as total')
            )
            ->groupBy('period')
            ->orderBy('period', 'asc')
            ->get();

        $summary = [
            'total_sales' => $sales->sum('total'),
            'total_orders' => $sales->sum('orders_count'),
            'total_discount' => $sales->sum('discount'),
            'avg_order_value' => $sales->sum('orders_count') > 0
                ? round($sales->sum('total') / $sales->sum('orders_count'), 3)
                : 0,
        ];

        return response()->json([
            'data' => $sales,
            'summary' => $summary,
        ]);
    }

    /**
     * Products report.
     */
    public function products(Request $request)
    {
        $request->validate([
            'from' => 'nullable|date',
            'to' => 'nullable|date|after_or_equal:from',
            'limit' => 'nullable|integer|min:1|max:100',
        ]);

        $from = $request->get('from', now()->subDays(30)->toDateString());
        $to = $request->get('to', now()->toDateString());
        $limit = $request->get('limit', 20);

        $products = DB::table('order_items')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->where('orders.status', '!=', 'cancelled')
            ->whereBetween('orders.created_at', [$from, $to . ' 23:59:59'])
            ->select(
                'products.id',
                'products.name_ar',
                'products.name_en',
                'products.sku',
                DB::raw('SUM(order_items.quantity) as total_quantity'),
                DB::raw('SUM(order_items.total) as total_revenue'),
                DB::raw('COUNT(DISTINCT orders.id) as orders_count')
            )
            ->groupBy('products.id', 'products.name_ar', 'products.name_en', 'products.sku')
            ->orderBy('total_revenue', 'desc')
            ->limit($limit)
            ->get();

        return response()->json(['data' => $products]);
    }

    /**
     * Inventory report.
     */
    public function inventory(Request $request)
    {
        $query = DB::table('product_weights')
            ->join('products', 'product_weights.product_id', '=', 'products.id')
            ->join('weight_units', 'product_weights.weight_unit_id', '=', 'weight_units.id')
            ->select(
                'products.id as product_id',
                'products.name_ar',
                'products.name_en',
                'products.sku as product_sku',
                'product_weights.id as weight_id',
                'product_weights.weight_value',
                'weight_units.abbreviation_ar as unit',
                'product_weights.stock',
                'product_weights.price',
                'product_weights.sku as weight_sku'
            );

        // Filter low stock
        if ($request->filled('low_stock')) {
            $threshold = $request->get('low_stock', 10);
            $query->where('product_weights.stock', '<=', $threshold);
        }

        // Filter out of stock
        if ($request->boolean('out_of_stock')) {
            $query->where('product_weights.stock', '<=', 0);
        }

        $inventory = $query->orderBy('product_weights.stock', 'asc')
            ->paginate($request->get('per_page', 50));

        // Summary
        $totalProducts = Product::count();
        $outOfStock = DB::table('product_weights')->where('stock', '<=', 0)->count();
        $lowStock = DB::table('product_weights')->where('stock', '>', 0)->where('stock', '<=', 10)->count();
        $totalStockValue = DB::table('product_weights')
            ->selectRaw('SUM(stock * price) as value')
            ->value('value');

        return response()->json([
            'data' => $inventory,
            'summary' => [
                'total_products' => $totalProducts,
                'out_of_stock' => $outOfStock,
                'low_stock' => $lowStock,
                'total_stock_value' => round($totalStockValue ?? 0, 3),
            ],
        ]);
    }

    /**
     * Customers report.
     */
    public function customers(Request $request)
    {
        $request->validate([
            'from' => 'nullable|date',
            'to' => 'nullable|date|after_or_equal:from',
        ]);

        $from = $request->get('from', now()->subDays(30)->toDateString());
        $to = $request->get('to', now()->toDateString());

        $newCustomers = Customer::whereBetween('created_at', [$from, $to . ' 23:59:59'])->count();
        $totalCustomers = Customer::count();
        $returningCustomers = Customer::where('total_orders', '>', 1)->count();

        $topCustomers = Customer::where('total_spent', '>', 0)
            ->orderBy('total_spent', 'desc')
            ->limit(20)
            ->get(['id', 'name', 'phone', 'total_orders', 'total_spent', 'last_order_at']);

        // Customer growth over time
        $customerGrowth = Customer::whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->select(
                DB::raw('DATE(created_at) as date'),
                DB::raw('COUNT(*) as count')
            )
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();

        return response()->json([
            'data' => [
                'new_customers' => $newCustomers,
                'total_customers' => $totalCustomers,
                'returning_customers' => $returningCustomers,
                'top_customers' => $topCustomers,
                'customer_growth' => $customerGrowth,
            ],
        ]);
    }

    /**
     * Revenue report.
     */
    public function revenue(Request $request)
    {
        $request->validate([
            'from' => 'nullable|date',
            'to' => 'nullable|date|after_or_equal:from',
        ]);

        $from = $request->get('from', now()->subDays(30)->toDateString());
        $to = $request->get('to', now()->toDateString());

        $revenue = Order::where('status', '!=', 'cancelled')
            ->whereBetween('created_at', [$from, $to . ' 23:59:59']);

        $totalRevenue = (clone $revenue)->sum('total');
        $totalCost = DB::table('order_items')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->join('product_weights', 'order_items.product_weight_id', '=', 'product_weights.id')
            ->where('orders.status', '!=', 'cancelled')
            ->whereBetween('orders.created_at', [$from, $to . ' 23:59:59'])
            ->selectRaw('SUM(COALESCE(product_weights.cost_price, 0) * order_items.quantity) as total_cost')
            ->value('total_cost');

        $grossProfit = $totalRevenue - ($totalCost ?? 0);

        // Revenue by category
        $revenueByCategory = DB::table('order_items')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->join('categories', 'products.category_id', '=', 'categories.id')
            ->where('orders.status', '!=', 'cancelled')
            ->whereBetween('orders.created_at', [$from, $to . ' 23:59:59'])
            ->select(
                'categories.name_ar',
                'categories.name_en',
                DB::raw('SUM(order_items.total) as revenue')
            )
            ->groupBy('categories.name_ar', 'categories.name_en')
            ->orderBy('revenue', 'desc')
            ->get();

        // Revenue by payment method
        $revenueByPayment = Order::where('status', '!=', 'cancelled')
            ->whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->whereNotNull('payment_method_id')
            ->join('payment_methods', 'orders.payment_method_id', '=', 'payment_methods.id')
            ->select(
                'payment_methods.name_ar',
                'payment_methods.name_en',
                DB::raw('SUM(orders.total) as revenue'),
                DB::raw('COUNT(*) as orders_count')
            )
            ->groupBy('payment_methods.name_ar', 'payment_methods.name_en')
            ->get();

        return response()->json([
            'data' => [
                'total_revenue' => round($totalRevenue, 3),
                'total_cost' => round($totalCost ?? 0, 3),
                'gross_profit' => round($grossProfit, 3),
                'profit_margin' => $totalRevenue > 0
                    ? round(($grossProfit / $totalRevenue) * 100, 2)
                    : 0,
                'revenue_by_category' => $revenueByCategory,
                'revenue_by_payment' => $revenueByPayment,
            ],
        ]);
    }

    /**
     * Export report data.
     */
    public function export(Request $request)
    {
        $request->validate([
            'type' => 'required|in:sales,products,inventory,customers',
            'from' => 'nullable|date',
            'to' => 'nullable|date|after_or_equal:from',
            'format' => 'nullable|in:json,csv',
        ]);

        $from = $request->get('from', now()->subDays(30)->toDateString());
        $to = $request->get('to', now()->toDateString());

        $data = match ($request->type) {
            'sales' => $this->exportSales($from, $to),
            'products' => $this->exportProducts($from, $to),
            'inventory' => $this->exportInventory(),
            'customers' => $this->exportCustomers($from, $to),
        };

        if ($request->get('format') === 'csv') {
            return $this->toCsv($data, $request->type);
        }

        return response()->json(['data' => $data]);
    }

    private function exportSales(string $from, string $to)
    {
        return Order::with(['customer:id,name,phone'])
            ->where('status', '!=', 'cancelled')
            ->whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->orderBy('created_at', 'desc')
            ->get()
            ->map(fn ($o) => [
                'order_number' => $o->order_number,
                'customer' => $o->customer->name ?? '',
                'phone' => $o->customer->phone ?? '',
                'total' => $o->total,
                'status' => $o->status,
                'date' => $o->created_at->format('Y-m-d'),
            ]);
    }

    private function exportProducts(string $from, string $to)
    {
        return DB::table('order_items')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->where('orders.status', '!=', 'cancelled')
            ->whereBetween('orders.created_at', [$from, $to . ' 23:59:59'])
            ->select(
                'order_items.product_name_ar',
                'order_items.product_name_en',
                DB::raw('SUM(order_items.quantity) as quantity'),
                DB::raw('SUM(order_items.total) as revenue')
            )
            ->groupBy('order_items.product_name_ar', 'order_items.product_name_en')
            ->orderBy('revenue', 'desc')
            ->get();
    }

    private function exportInventory()
    {
        return DB::table('product_weights')
            ->join('products', 'product_weights.product_id', '=', 'products.id')
            ->join('weight_units', 'product_weights.weight_unit_id', '=', 'weight_units.id')
            ->select(
                'products.name_ar',
                'products.name_en',
                'products.sku',
                'product_weights.weight_value',
                'weight_units.abbreviation_ar as unit',
                'product_weights.stock',
                'product_weights.price'
            )
            ->orderBy('product_weights.stock', 'asc')
            ->get();
    }

    private function exportCustomers(string $from, string $to)
    {
        return Customer::whereBetween('created_at', [$from, $to . ' 23:59:59'])
            ->orderBy('total_spent', 'desc')
            ->get(['name', 'phone', 'email', 'city', 'total_orders', 'total_spent', 'created_at']);
    }

    private function toCsv($data, string $filename)
    {
        if ($data->isEmpty()) {
            return response()->json(['message' => 'لا توجد بيانات للتصدير'], 404);
        }

        $csv = implode(',', array_keys((array) $data->first())) . "\n";
        foreach ($data as $row) {
            $csv .= implode(',', array_map(function ($val) {
                return '"' . str_replace('"', '""', $val ?? '') . '"';
            }, (array) $row)) . "\n";
        }

        return response($csv, 200, [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename={$filename}.csv",
        ]);
    }
}
