<?php

namespace App\Http\Controllers\API;

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

class ReportController extends Controller
{
    /**
     * Dashboard consolidated report
     * GET /api/v1/reports/dashboard
     */
    public function dashboard(Request $request)
    {
        $validated = $request->validate([
            'period'     => 'nullable|in:today,yesterday,week,month,year,custom',
            'start_date' => 'nullable|date',
            'end_date'   => 'nullable|date|after_or_equal:start_date',
            'store_id'   => 'nullable|exists:stores,id',
            'seller_id'  => 'nullable|exists:users,id',
        ]);

        $period = $validated['period'] ?? 'month';
        [$startDate, $endDate] = $this->getDateRange($validated);

        // Get user context — non-super_admin is locked to their own store
        $user = $request->user();
        $isSuperAdmin = $user && $user->role === 'super_admin';
        $storeId  = $validated['store_id']  ?? (!$isSuperAdmin && $user ? $user->store_id : null);
        $sellerId = $validated['seller_id'] ?? null;

        // ── Base query helper — applies store + seller filters ────────────────
        $applyFilters = function ($query) use ($storeId, $sellerId) {
            if ($storeId)  $query->where('store_id',  $storeId);
            if ($sellerId) $query->where('seller_id', $sellerId);
            return $query;
        };

        // Base query for orders
        $ordersQuery = $applyFilters(
            Order::query()->whereBetween('created_at', [$startDate, $endDate])
        );

        // Sales metrics
        $totalSales      = (clone $ordersQuery)->where('status', '!=', 'cancelled')->sum('total_amount');
        $totalOrders     = (clone $ordersQuery)->count();
        $completedOrders = (clone $ordersQuery)->where('status', 'completed')->count();
        $pendingOrders   = (clone $ordersQuery)->where('status', 'pending')->count();
        $cancelledOrders = (clone $ordersQuery)->where('status', 'cancelled')->count();

        // Previous period comparison
        $daysDiff      = $startDate->diffInDays($endDate);
        $previousStart = $startDate->copy()->subDays($daysDiff);
        $previousEnd   = $endDate->copy()->subDays($daysDiff);

        $previousQuery = $applyFilters(
            Order::query()->where('status', '!=', 'cancelled')
                ->whereBetween('created_at', [$previousStart, $previousEnd])
        );
        $previousSales = $previousQuery->sum('total_amount');
        $salesGrowth   = $previousSales > 0
            ? (($totalSales - $previousSales) / $previousSales) * 100
            : 0;

        // Top products
        $topProductsQuery = Product::select('products.*')
            ->join('order_items', 'products.id', '=', 'order_items.product_id')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', '!=', 'cancelled');
        if ($storeId)  $topProductsQuery->where('orders.store_id',  $storeId);
        if ($sellerId) $topProductsQuery->where('orders.seller_id', $sellerId);

        $topProducts = $topProductsQuery
            ->selectRaw('SUM(order_items.quantity) as quantity_sold')
            ->selectRaw('SUM(order_items.total_amount) as revenue')
            ->groupBy('products.id')
            ->orderByDesc('revenue')
            ->limit(5)
            ->get();

        // Top customers
        $topCustomersQuery = Customer::select('customers.*')
            ->join('orders', 'customers.id', '=', 'orders.customer_id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', '!=', 'cancelled');
        if ($storeId)  $topCustomersQuery->where('orders.store_id',  $storeId);
        if ($sellerId) $topCustomersQuery->where('orders.seller_id', $sellerId);

        $topCustomers = $topCustomersQuery
            ->selectRaw('SUM(orders.total_amount) as total_spent')
            ->selectRaw('COUNT(orders.id) as orders_count')
            ->groupBy('customers.id')
            ->orderByDesc('total_spent')
            ->limit(5)
            ->get();

        // Sales chart
        $salesChart = $applyFilters(
            Order::query()->where('status', '!=', 'cancelled')
                ->whereBetween('created_at', [$startDate, $endDate])
        )->select(
            DB::raw('DATE(created_at) as date'),
            DB::raw('SUM(total_amount) as total'),
            DB::raw('COUNT(*) as orders')
        )->groupBy('date')->orderBy('date')->get();

        // Payment methods
        $paymentMethods = $applyFilters(
            Order::query()->where('payment_status', 'paid')
                ->whereBetween('created_at', [$startDate, $endDate])
        )->select(
            'payment_method',
            DB::raw('SUM(total_amount) as total'),
            DB::raw('COUNT(*) as count')
        )->groupBy('payment_method')->get();

        // Recent orders
        $recentOrders = $applyFilters(
            Order::query()->with(['customer:id,name,phone', 'store:id,name', 'seller:id,name'])
                ->whereBetween('created_at', [$startDate, $endDate])
        )->latest()->limit(10)->get();

        return response()->json([
            'success' => true,
            'data' => [
                'period' => [
                    'label' => $period,
                    'start_date' => $startDate->toDateString(),
                    'end_date' => $endDate->toDateString(),
                ],
                'sales_metrics' => [
                    'total_sales' => (float) $totalSales,
                    'previous_sales' => (float) $previousSales,
                    'sales_growth' => round($salesGrowth, 2),
                    'total_orders' => $totalOrders,
                    'completed_orders' => $completedOrders,
                    'pending_orders' => $pendingOrders,
                    'cancelled_orders' => $cancelledOrders,
                    'average_order_value' => $totalOrders > 0 ? round($totalSales / $totalOrders, 2) : 0,
                ],
                'top_products' => $topProducts,
                'top_customers' => $topCustomers,
                'sales_chart' => $salesChart,
                'payment_methods' => $paymentMethods,
                'recent_orders' => $recentOrders,
            ],
        ]);
    }

    /**
     * Sales report
     * GET /api/v1/reports/sales
     */
    public function sales(Request $request)
    {
        $validated = $request->validate([
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'store_id' => 'nullable|exists:stores,id',
            'period' => 'nullable|in:today,yesterday,week,month,year,custom',
        ]);

        // Determine date range
        [$startDate, $endDate] = $this->getDateRange($validated);

        $query = Order::query()
            ->where('orders.status', '!=', 'cancelled')
            ->whereBetween('orders.created_at', [$startDate, $endDate]);

        // Filter by store
        if (isset($validated['store_id'])) {
            $query->where('orders.store_id', $validated['store_id']);
        }

        // Filter by user's store
        $user = $request->user();
        if ($user && $user->role !== 'super_admin' && $user->store_id) {
            $query->where('orders.store_id', $user->store_id);
        }

        // Calculations
        $totalSales = (clone $query)->sum('orders.total_amount');
        $totalOrders = (clone $query)->count();
        $averageOrderValue = $totalOrders > 0 ? $totalSales / $totalOrders : 0;

        // Sales by day
        $salesByDay = (clone $query)
            ->select(
                DB::raw('DATE(orders.created_at) as date'),
                DB::raw('SUM(orders.total_amount) as total'),
                DB::raw('COUNT(*) as orders')
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        // Sales by payment method
        $salesByPaymentMethod = (clone $query)
            ->select(
                'orders.payment_method',
                DB::raw('SUM(orders.total_amount) as total'),
                DB::raw('COUNT(*) as count')
            )
            ->groupBy('orders.payment_method')
            ->get();

        // Sales by store
        $salesByStore = (clone $query)
            ->join('stores', 'orders.store_id', '=', 'stores.id')
            ->select(
                'stores.id',
                'stores.name',
                DB::raw('SUM(orders.total_amount) as total'),
                DB::raw('COUNT(orders.id) as orders')
            )
            ->groupBy('stores.id', 'stores.name')
            ->orderByDesc('total')
            ->get();

        return response()->json([
            'success' => true,
            'data' => [
                'period' => [
                    'start_date' => $startDate->toDateString(),
                    'end_date' => $endDate->toDateString(),
                ],
                'summary' => [
                    'total_sales' => (float) $totalSales,
                    'total_orders' => $totalOrders,
                    'average_order_value' => (float) $averageOrderValue,
                ],
                'sales_by_day' => $salesByDay,
                'sales_by_payment_method' => $salesByPaymentMethod,
                'sales_by_store' => $salesByStore,
            ],
        ]);
    }

    /**
     * Products report
     * GET /api/v1/reports/products
     */
    public function products(Request $request)
    {
        $validated = $request->validate([
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'store_id' => 'nullable|exists:stores,id',
            'period' => 'nullable|in:today,yesterday,week,month,year,custom',
            'limit' => 'nullable|integer|min:1|max:100',
        ]);

        [$startDate, $endDate] = $this->getDateRange($validated);
        $limit = $validated['limit'] ?? 20;

        // Best selling products
        $bestSelling = Product::select('products.*')
            ->join('order_items', 'products.id', '=', 'order_items.product_id')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', '!=', 'cancelled')
            ->selectRaw('SUM(order_items.quantity) as quantity_sold')
            ->selectRaw('SUM(order_items.total_amount) as revenue')
            ->groupBy('products.id')
            ->orderByDesc('revenue')
            ->limit($limit)
            ->get();

        // Products by category
        $byCategory = Product::select('categories.name as category')
            ->join('categories', 'products.category_id', '=', 'categories.id')
            ->join('order_items', 'products.id', '=', 'order_items.product_id')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', '!=', 'cancelled')
            ->selectRaw('SUM(order_items.quantity) as quantity_sold')
            ->selectRaw('SUM(order_items.total_amount) as revenue')
            ->groupBy('categories.id', 'categories.name')
            ->orderByDesc('revenue')
            ->get();

        // Low performing products
        $lowPerforming = Product::select('products.*')
            ->leftJoin('order_items', 'products.id', '=', 'order_items.product_id')
            ->leftJoin('orders', function ($join) use ($startDate, $endDate) {
                $join->on('order_items.order_id', '=', 'orders.id')
                     ->whereBetween('orders.created_at', [$startDate, $endDate])
                     ->where('orders.status', '!=', 'cancelled');
            })
            ->selectRaw('COALESCE(SUM(order_items.quantity), 0) as quantity_sold')
            ->selectRaw('COALESCE(SUM(order_items.total_amount), 0) as revenue')
            ->groupBy('products.id')
            ->having('quantity_sold', '<', 5)
            ->orderBy('quantity_sold')
            ->limit($limit)
            ->get();

        return response()->json([
            'success' => true,
            'data' => [
                'period' => [
                    'start_date' => $startDate->toDateString(),
                    'end_date' => $endDate->toDateString(),
                ],
                'best_selling' => $bestSelling,
                'by_category' => $byCategory,
                'low_performing' => $lowPerforming,
            ],
        ]);
    }

    /**
     * Customers report
     * GET /api/v1/reports/customers
     */
    public function customers(Request $request)
    {
        $validated = $request->validate([
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'period' => 'nullable|in:today,yesterday,week,month,year,custom',
            'limit' => 'nullable|integer|min:1|max:100',
        ]);

        [$startDate, $endDate] = $this->getDateRange($validated);
        $limit = $validated['limit'] ?? 20;

        // Top customers
        $topCustomers = Customer::select('customers.*')
            ->join('orders', 'customers.id', '=', 'orders.customer_id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', '!=', 'cancelled')
            ->selectRaw('SUM(orders.total_amount) as total_spent')
            ->selectRaw('COUNT(orders.id) as orders_count')
            ->selectRaw('AVG(orders.total_amount) as avg_order_value')
            ->groupBy('customers.id')
            ->orderByDesc('total_spent')
            ->limit($limit)
            ->get();

        // New customers
        $newCustomers = Customer::whereBetween('created_at', [$startDate, $endDate])
            ->count();

        // Customer retention (customers who made repeat purchases)
        $repeatCustomers = Customer::select('customers.id')
            ->join('orders', 'customers.id', '=', 'orders.customer_id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->groupBy('customers.id')
            ->havingRaw('COUNT(orders.id) > 1')
            ->count();

        // Customer lifetime value
        $lifetimeValue = Customer::select('customers.*')
            ->join('orders', 'customers.id', '=', 'orders.customer_id')
            ->where('orders.status', '!=', 'cancelled')
            ->selectRaw('SUM(orders.total_amount) as lifetime_value')
            ->selectRaw('COUNT(orders.id) as total_orders')
            ->groupBy('customers.id')
            ->orderByDesc('lifetime_value')
            ->limit($limit)
            ->get();

        return response()->json([
            'success' => true,
            'data' => [
                'period' => [
                    'start_date' => $startDate->toDateString(),
                    'end_date' => $endDate->toDateString(),
                ],
                'summary' => [
                    'new_customers' => $newCustomers,
                    'repeat_customers' => $repeatCustomers,
                ],
                'top_customers' => $topCustomers,
                'lifetime_value' => $lifetimeValue,
            ],
        ]);
    }

    /**
     * Inventory report
     * GET /api/v1/reports/inventory
     */
    public function inventory(Request $request)
    {
        $validated = $request->validate([
            'store_id' => 'nullable|exists:stores,id',
        ]);

        $query = DB::table('stock')
            ->join('products', 'stock.product_id', '=', 'products.id')
            ->join('stores', 'stock.store_id', '=', 'stores.id')
            ->select(
                'stores.name as store',
                'products.name as product',
                'products.sku',
                'stock.quantity',
                'stock.min_stock_level',
                'products.price',
                DB::raw('(stock.quantity * products.price) as stock_value')
            );

        if (isset($validated['store_id'])) {
            $query->where('stock.store_id', $validated['store_id']);
        }

        $inventory = $query->orderBy('stores.name')
            ->orderBy('products.name')
            ->get();

        // Summary
        $totalValue = $inventory->sum('stock_value');
        $lowStockCount = $inventory->filter(function ($item) {
            return $item->quantity <= $item->min_stock_level;
        })->count();
        $outOfStockCount = $inventory->filter(function ($item) {
            return $item->quantity == 0;
        })->count();

        return response()->json([
            'success' => true,
            'data' => [
                'summary' => [
                    'total_value' => (float) $totalValue,
                    'total_items' => $inventory->count(),
                    'low_stock_count' => $lowStockCount,
                    'out_of_stock_count' => $outOfStockCount,
                ],
                'inventory' => $inventory,
            ],
        ]);
    }

    /**
     * Performance report (by user/seller)
     * GET /api/v1/reports/performance
     */
    public function performance(Request $request)
    {
        $validated = $request->validate([
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'store_id' => 'nullable|exists:stores,id',
            'period' => 'nullable|in:today,yesterday,week,month,year,custom',
        ]);

        [$startDate, $endDate] = $this->getDateRange($validated);

        $query = User::select('users.*')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', '!=', 'cancelled');

        if (isset($validated['store_id'])) {
            $query->where('users.store_id', $validated['store_id']);
        }

        $performance = $query
            ->selectRaw('SUM(orders.total_amount) as total_sales')
            ->selectRaw('COUNT(orders.id) as total_orders')
            ->selectRaw('AVG(orders.total_amount) as avg_order_value')
            ->groupBy('users.id')
            ->orderByDesc('total_sales')
            ->get();

        return response()->json([
            'success' => true,
            'data' => [
                'period' => [
                    'start_date' => $startDate->toDateString(),
                    'end_date' => $endDate->toDateString(),
                ],
                'performance' => $performance,
            ],
        ]);
    }

    /**
     * Financial summary
     * GET /api/v1/reports/financial
     */
    public function financial(Request $request)
    {
        $validated = $request->validate([
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'store_id' => 'nullable|exists:stores,id',
            'period' => 'nullable|in:today,yesterday,week,month,year,custom',
        ]);

        [$startDate, $endDate] = $this->getDateRange($validated);

        $query = Order::query()
            ->whereBetween('created_at', [$startDate, $endDate]);

        if (isset($validated['store_id'])) {
            $query->where('store_id', $validated['store_id']);
        }

        // Revenue
        $totalRevenue = (clone $query)->where('status', '!=', 'cancelled')->sum('total_amount');
        $paidRevenue = (clone $query)->where('payment_status', 'paid')->sum('total_amount');
        $pendingRevenue = (clone $query)->where('payment_status', 'pending')->sum('total_amount');

        // By payment method
        $byPaymentMethod = (clone $query)
            ->where('payment_status', 'paid')
            ->select(
                'payment_method',
                DB::raw('SUM(total_amount) as total'),
                DB::raw('COUNT(*) as count')
            )
            ->groupBy('payment_method')
            ->get();

        // Discounts
        $totalDiscounts = (clone $query)->where('status', '!=', 'cancelled')->sum('discount_amount');
        $totalTax = (clone $query)->where('status', '!=', 'cancelled')->sum('tax_amount');

        return response()->json([
            'success' => true,
            'data' => [
                'period' => [
                    'start_date' => $startDate->toDateString(),
                    'end_date' => $endDate->toDateString(),
                ],
                'revenue' => [
                    'total' => (float) $totalRevenue,
                    'paid' => (float) $paidRevenue,
                    'pending' => (float) $pendingRevenue,
                ],
                'by_payment_method' => $byPaymentMethod,
                'total_discounts' => (float) $totalDiscounts,
                'total_tax' => (float) $totalTax,
            ],
        ]);
    }

    /**
     * Stores performance
     * GET /api/v1/reports/stores
     */
    public function stores(Request $request)
    {
        $validated = $request->validate([
            'start_date' => 'nullable|date',
            'end_date'   => 'nullable|date|after_or_equal:start_date',
            'period'     => 'nullable|in:today,yesterday,week,month,year,custom',
        ]);

        [$startDate, $endDate] = $this->getDateRange($validated);

        $user = $request->user();
        $isSuperAdmin = $user && $user->role === 'super_admin';

        $query = Store::select('stores.*')
            ->leftJoin('orders', function ($join) use ($startDate, $endDate) {
                $join->on('stores.id', '=', 'orders.store_id')
                     ->whereBetween('orders.created_at', [$startDate, $endDate])
                     ->where('orders.status', '!=', 'cancelled');
            })
            ->selectRaw('COALESCE(SUM(orders.total_amount), 0) as revenue')
            ->selectRaw('COALESCE(COUNT(DISTINCT orders.id), 0) as total_orders')
            ->selectRaw('COALESCE(AVG(orders.total_amount), 0) as avg_order_value');

        if (!$isSuperAdmin && $user?->store_id) {
            $query->where('stores.id', $user->store_id);
        }

        $stores = $query->groupBy('stores.id')->orderByDesc('revenue')->get();

        // Daily breakdown per store
        $dailyQuery = Order::query()
            ->join('stores', 'orders.store_id', '=', 'stores.id')
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', '!=', 'cancelled')
            ->select('stores.name as store', DB::raw('DATE(orders.created_at) as date'), DB::raw('SUM(orders.total_amount) as total'))
            ->groupBy('stores.id', 'stores.name', 'date')
            ->orderBy('date');

        if (!$isSuperAdmin && $user?->store_id) {
            $dailyQuery->where('orders.store_id', $user->store_id);
        }

        $daily = $dailyQuery->get();

        return response()->json([
            'success' => true,
            'data' => [
                'period' => ['start_date' => $startDate->toDateString(), 'end_date' => $endDate->toDateString()],
                'stores' => $stores,
                'daily'  => $daily,
            ],
        ]);
    }

    /**
     * Sellers report
     * GET /api/v1/reports/sellers
     */
    public function sellers(Request $request)
    {
        $validated = $request->validate([
            'start_date' => 'nullable|date',
            'end_date'   => 'nullable|date|after_or_equal:start_date',
            'store_id'   => 'nullable|exists:stores,id',
            'period'     => 'nullable|in:today,yesterday,week,month,year,custom',
        ]);

        [$startDate, $endDate] = $this->getDateRange($validated);

        $user = $request->user();
        $isSuperAdmin = $user && $user->role === 'super_admin';
        $storeId = $validated['store_id'] ?? (!$isSuperAdmin ? $user?->store_id : null);

        $query = User::select('users.id', 'users.name', 'users.email', 'users.role', 'users.store_id')
            ->leftJoin('orders', function ($join) use ($startDate, $endDate) {
                $join->on('users.id', '=', 'orders.seller_id')
                     ->whereBetween('orders.created_at', [$startDate, $endDate])
                     ->where('orders.status', '!=', 'cancelled');
            })
            ->selectRaw('COALESCE(SUM(orders.total_amount), 0) as total_sales')
            ->selectRaw('COALESCE(COUNT(DISTINCT orders.id), 0) as total_orders')
            ->selectRaw('COALESCE(AVG(orders.total_amount), 0) as avg_order_value')
            ->whereIn('users.role', ['admin', 'manager', 'cashier', 'seller', 'super_admin']);

        if ($storeId) {
            $query->where('users.store_id', $storeId);
        }

        $sellers = $query->groupBy('users.id', 'users.name', 'users.email', 'users.role', 'users.store_id')
            ->orderByDesc('total_sales')
            ->get();

        return response()->json([
            'success' => true,
            'data' => [
                'period'  => ['start_date' => $startDate->toDateString(), 'end_date' => $endDate->toDateString()],
                'sellers' => $sellers,
            ],
        ]);
    }

    /**
     * Helper method to get date range
     */
    private function getDateRange(array $validated): array
    {
        $period = $validated['period'] ?? 'month';

        switch ($period) {
            case 'today':
                $startDate = Carbon::today();
                $endDate = Carbon::today()->endOfDay();
                break;
            case 'yesterday':
                $startDate = Carbon::yesterday();
                $endDate = Carbon::yesterday()->endOfDay();
                break;
            case 'week':
                $startDate = Carbon::now()->startOfWeek();
                $endDate = Carbon::now()->endOfWeek();
                break;
            case 'month':
                $startDate = Carbon::now()->startOfMonth();
                $endDate = Carbon::now()->endOfMonth();
                break;
            case 'year':
                $startDate = Carbon::now()->startOfYear();
                $endDate = Carbon::now()->endOfYear();
                break;
            case 'custom':
                $startDate = isset($validated['start_date']) 
                    ? Carbon::parse($validated['start_date']) 
                    : Carbon::now()->startOfMonth();
                $endDate = isset($validated['end_date']) 
                    ? Carbon::parse($validated['end_date'])->endOfDay() 
                    : Carbon::now()->endOfDay();
                break;
            default:
                $startDate = Carbon::now()->startOfMonth();
                $endDate = Carbon::now()->endOfMonth();
        }

        return [$startDate, $endDate];
    }
}