<?php

/*
    Created by Mohamed.
*/


namespace App\Http\Controllers\API\Dashboard;

use Carbon\Carbon;
use App\Models\Cart;
use App\Models\User;
use App\Models\Order;

use App\Models\Product;
use App\Models\CartItem;
use App\Models\Discount;
use App\Models\OrderItem;
use App\Models\WebsiteVisit;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use App\Http\Resources\OrderResource;
use Illuminate\Support\Facades\Validator;

class OverViewController extends Controller
{
    public function SiteActivity()
    {

        $ordersByStatus = Order::select('status_id')
            ->with('status') // Eager load the status relationship
            ->groupBy('status_id')
            ->get()
            ->mapWithKeys(function ($order) {
                return [
                    $order->status ? $order->status->name : 'Unknown' => Order::where('status_id', $order->status_id)->count()
                ];
            });

        $orders = Order::with(['status', 'user', 'order_items'])
            ->paginate(15); // Paginate with 15 items per page

        $clientsThisMonth = User::whereBetween('created_at', [
            Carbon::now()->startOfMonth(),
            Carbon::now()->endOfMonth()
        ])->count();

        $totalVisits = WebsiteVisit::sum('visit_count'); // Summing visit_count

        $activatedAccounts = User::where('is_active', true)->count();

        return response()->json([
            'orders_by_status' => $ordersByStatus,
            'orders_paginated' => OrderResource::collection($orders),
            'clients_this_month' => $clientsThisMonth,
            'total_visits' => $totalVisits,
            'activated_accounts' => $activatedAccounts,
        ], 200);

    }
    public function Webget()
    {
        $today = Carbon::today('Europe/Athens');
        $startOfMonth = Carbon::now('Europe/Athens')->startOfMonth();

        $visitsToday = WebsiteVisit::whereDate('visit_timestamp', $today)->sum('visit_count');

        $registrationsToday = User::whereDate('created_at', $today)->count();

        $visitsThisMonth = WebsiteVisit::whereBetween('visit_timestamp', [
            $startOfMonth,
            Carbon::now('Europe/Athens')->endOfMonth()
        ])->sum('visit_count');

        $registrationsThisMonth = User::whereBetween('created_at', [
            $startOfMonth,
            Carbon::now('Europe/Athens')->endOfMonth()
        ])->count();

        $trafficByCountry = WebsiteVisit::select('country')
            ->groupBy('country')
            ->get()
            ->mapWithKeys(function ($visit) {
                return [
                    $visit->country ?? 'Unknown' => WebsiteVisit::where('country', $visit->country)->sum('visit_count')
                ];
            });

        $registrationStats = [
            'last_week' => User::where('created_at', '>=', Carbon::now('Europe/Athens')->subWeek())->count(),
            'last_month' => User::where('created_at', '>=', Carbon::now('Europe/Athens')->subMonth())->count(),
            'last_3_months' => User::where('created_at', '>=', Carbon::now('Europe/Athens')->subMonths(3))->count(),
            'last_6_months' => User::where('created_at', '>=', Carbon::now('Europe/Athens')->subMonths(6))->count(),
            'last_9_months' => User::where('created_at', '>=', Carbon::now('Europe/Athens')->subMonths(9))->count(),
            'last_year' => User::where('created_at', '>=', Carbon::now('Europe/Athens')->subYear())->count(),
        ];

        return response()->json([
            'visits_today' => $visitsToday,
            'registrations_today' => $registrationsToday,
            'visits_this_month' => $visitsThisMonth,
            'registrations_this_month' => $registrationsThisMonth,
            'traffic_by_country' => $trafficByCountry,
            'registration_stats' => $registrationStats,
        ], 200);
    }
    /**
     * Summary of statistics
     * @param \Illuminate\Http\Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function statistics(Request $request): JsonResponse
    {
        try {
            // Validate the request
            $validator = Validator::make($request->all(), [
                'filter' => 'required|in:year,month,week',
                'product_filter' => 'required|in:year,six_months,month',
                'year' => 'required|integer|min:2000|max:' . now()->year,
            ], [
                'filter.required' => 'حقل الفلتر مطلوب',
                'filter.in' => 'الفلتر يجب أن يكون year أو month أو week',
                'product_filter.required' => 'حقل فلتر المنتجات مطلوب',
                'product_filter.in' => 'فلتر المنتجات يجب أن يكون year أو six_months أو month',
                'year.required' => 'السنة مطلوبة',
                'year.integer' => 'السنة يجب أن تكون رقمًا صحيحًا',
                'year.min' => 'السنة يجب أن تكون على الأقل 2000',
                'year.max' => 'السنة يجب ألا تتجاوز ' . now()->year,
            ]);

            if ($validator->fails()) {
                return response()->json([
                    'status' => false,
                    'message' => 'فشل التحقق من البيانات',
                    'errors' => $validator->errors(),
                ], 422);
            }

            $filter = $request->filter;
            $productFilter = $request->product_filter;
            $year = $request->year;

            // Define time range for general metrics
            $startDate = Carbon::create($year, 1, 1)->startOfYear();
            $endDate = Carbon::create($year, 12, 31)->endOfYear();

            if ($filter === 'month') {
                $startDate = now()->startOfMonth();
                $endDate = now()->endOfMonth();
            } elseif ($filter === 'week') {
                $startDate = now()->startOfWeek();
                $endDate = now()->endOfWeek();
            }

            // Define time range for product metrics
            $productStartDate = Carbon::create($year, 1, 1)->startOfYear();
            $productEndDate = Carbon::create($year, 12, 31)->endOfYear();

            if ($productFilter === 'six_months') {
                $productStartDate = now()->subMonths(6)->startOfMonth();
                $productEndDate = now()->endOfMonth();
            } elseif ($productFilter === 'month') {
                $productStartDate = now()->startOfMonth();
                $productEndDate = now()->endOfMonth();
            }

            // Get completed order statuses (accepted, delivered, on-process)
            $completedStatusIds = [8, 9, 10];

            // Calculate general metrics
            $ordersQuery = Order::whereIn('status_id', $completedStatusIds)
                ->whereBetween('created_at', [$startDate, $endDate]);

            $sales = $ordersQuery->sum('total_price');
            $orderCount = $ordersQuery->count();
            $aov = $orderCount > 0 ? $sales / $orderCount : 0;

            // $refunds = Refund::whereHas('order', function ($query) use ($startDate, $endDate, $completedStatusIds) {
            //     $query->whereIn('status_id', $completedStatusIds)
            //         ->whereBetween('created_at', [$startDate, $endDate]);
            // })->sum('amount');

            $discounts = Discount::whereHas('order_items.order', function ($query) use ($startDate, $endDate, $completedStatusIds) {
                $query->whereIn('status_id', $completedStatusIds)
                    ->whereBetween('created_at', [$startDate, $endDate]);
            })->whereBetween('start_date', [$startDate, $endDate])
                ->where(function ($query) use ($endDate) {
                    $query->whereNull('end_date')
                        ->orWhere('end_date', '>=', $endDate);
                })->sum('value');

            // Calculate abandonment checkouts (carts for users with no completed orders)
            $abandonmentCheckouts = Cart::whereHas('cartItems')
                ->whereDoesntHave('user.orders', function ($query) use ($startDate, $endDate, $completedStatusIds) {
                    $query->whereIn('status_id', $completedStatusIds)
                        ->whereBetween('created_at', [$startDate, $endDate]);
                })->whereBetween('created_at', [$startDate, $endDate])
                ->count();

            // Calculate product metrics
            $productOrdersQuery = Order::whereIn('status_id', $completedStatusIds)
                ->whereBetween('created_at', [$productStartDate, $productEndDate]);

            $productSales = $productOrdersQuery->with('order_items')->get()
                ->sum(function ($order) {
                    return $order->order_items->sum('quantity');
                });

            $availableProductTypes = Product::distinct('type')
                ->whereExists(function ($query) use ($productStartDate, $productEndDate, $completedStatusIds) {
                    $query->select(\DB::raw(1))
                        ->from('order_items')
                        ->join('orders', 'order_items.order_id', '=', 'orders.id')
                        ->whereIn('orders.status_id', $completedStatusIds)
                        ->whereBetween('orders.created_at', [$productStartDate, $productEndDate])
                        ->whereColumn('order_items.product_id', 'products.id');
                })->count('type');

            $uniqueCustomers = $productOrdersQuery->distinct('user_id')->count('user_id');
            $purchaseFrequency = $uniqueCustomers > 0 ? $productOrdersQuery->count() / $uniqueCustomers : 0;

            $avgProcessingHours = Order::where('status_id', 9) // Delivered orders
                ->whereBetween('created_at', [$productStartDate, $productEndDate])
                ->get()
                ->avg(function ($order) {
                    return Carbon::parse($order->created_at)->diffInHours($order->updated_at);
                }) ?? 0;

            // Total customers and income
            $totalCustomers = User::whereHas('orders', function ($query) use ($completedStatusIds) {
                $query->whereIn('status_id', $completedStatusIds);
            })->count();

            $totalIncome = Order::whereIn('status_id', $completedStatusIds)->sum('total_price');

            // Placeholder percentage change
            $percentageChange = 10.02;

            return response()->json([
                'status' => true,
                'message' => 'تم استرجاع الإحصائيات بنجاح',
                'data' => [
                    'general' => [
                        'sales' => [
                            'value' => number_format($sales, 2),
                            'change' => "+{$percentageChange}%",
                        ],
                        'orders' => [
                            'value' => $orderCount,
                            'change' => "+{$percentageChange}%",
                        ],
                        'avg_order_value' => [
                            'value' => number_format($aov, 2),
                            'change' => "+{$percentageChange}%",
                        ],
                        // 'refunds' => [
                        //     'value' => number_format($refunds, 2),
                        //     'change' => "+{$percentageChange}%",
                        // ],
                        'discounts' => [
                            'value' => number_format($discounts, 2),
                            'change' => "+{$percentageChange}%",
                        ],
                        'abandonment_checkouts' => [
                            'value' => $abandonmentCheckouts,
                            'change' => "+{$percentageChange}%",
                        ],
                    ],
                    'product' => [
                        'product_sales' => [
                            'value' => $productSales,
                            'change' => "+{$percentageChange}%",
                        ],
                        'available_product_types' => [
                            'value' => $availableProductTypes,
                            'change' => "+{$percentageChange}%",
                        ],
                        'purchase_frequency' => [
                            'value' => number_format($purchaseFrequency, 2) . '/day',
                            'change' => "+{$percentageChange}%",
                        ],
                        'avg_processing_hours' => [
                            'value' => number_format($avgProcessingHours, 2),
                            'change' => "+{$percentageChange}%",
                        ],
                    ],
                    'customers' => [
                        'total' => $totalCustomers,
                    ],
                    'income' => [
                        'total' => number_format($totalIncome, 2),
                    ],
                ],
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'status' => false,
                'message' => 'حدث خطأ غير متوقع أثناء استرجاع الإحصائيات',
                'error' => $e->getMessage(),
            ], 500);
        }
    }

    /**
     * Summary of advancedStatistics
     * @param \Illuminate\Http\Request $request
     * @return JsonResponse
     */
    public function advancedStatistics(Request $request): JsonResponse
    {
        try {
            // Validate the request
            $validator = Validator::make($request->all(), [
                'year' => 'integer|min:2000|max:' . now()->year,
            ], [
                'year.integer' => 'السنة يجب أن تكون رقمًا صحيحًا',
                'year.min' => 'السنة يجب أن تكون على الأقل 2000',
                'year.max' => 'السنة يجب ألا تتجاوز ' . now()->year,
            ]);

            if ($validator->fails()) {
                return response()->json([
                    'status' => false,
                    'message' => 'فشل التحقق من البيانات',
                    'errors' => $validator->errors(),
                ], 422);
            }

            $year = $request->input('year', 2019);
            $completedStatusIds = [8, 9, 10];

            $salesPerProduct = OrderItem::select('products.id', 'products.name', \DB::raw('SUM(order_items.price * order_items.quantity) as total_sales'))
                ->join('orders', 'order_items.order_id', '=', 'orders.id')
                ->join('products', 'order_items.product_id', '=', 'products.id')
                ->whereIn('orders.status_id', $completedStatusIds)
                ->groupBy('products.id', 'products.name')
                ->get()
                ->map(function ($item) {
                    // Decode JSON name if stored as JSON
                    $name = is_string($item->name) && json_validate($item->name) ? json_decode($item->name, true) : ['ar' => $item->name, 'en' => $item->name];
                    return [
                        'product_id' => $item->id,
                        'product_name' => [
                            'ar' => $name['ar'] ?? 'غير متوفر',
                            'en' => $name['en'] ?? 'Not Available',
                        ],
                        'total_sales' => number_format($item->total_sales, 2),
                    ];
                })->toArray();

            // Stock summary
            $addedStock = 400.01; // Placeholder (no stock_logs table provided)
            $availableStock = Product::sum('stock');
            $lowStockProducts = Product::where('stock', '<', 10)
                ->select('id', 'name', 'stock', \DB::raw('IF(stock > 0, (stock / SUM(stock) OVER () * 100), 0) as stock_percentage'))
                ->get()
                ->map(function ($product) {
                    // Decode JSON name if stored as JSON
                    $name = is_string($product->name) && json_validate($product->name) ? json_decode($product->name, true) : ['ar' => $product->name, 'en' => $product->name];
                    return [
                        'product_id' => $product->id,
                        'product_name' => [
                            'ar' => $name['ar'] ?? 'غير متوفر',
                            'en' => $name['en'] ?? 'Not Available',
                        ],
                        'stock' => $product->stock,
                        'stock_percentage' => number_format($product->stock_percentage, 2) . '%',
                    ];
                })->toArray();

            // Interaction rate for September 2019
            $startDate = Carbon::create(2019, 9, 1)->startOfMonth();
            $endDate = Carbon::create(2019, 9, 30)->endOfMonth();
            $weeksInSeptember = [
                ['start' => Carbon::create(2019, 9, 1), 'end' => Carbon::create(2019, 9, 7)],
                ['start' => Carbon::create(2019, 9, 8), 'end' => Carbon::create(2019, 9, 14)],
                ['start' => Carbon::create(2019, 9, 15), 'end' => Carbon::create(2019, 9, 21)],
                ['start' => Carbon::create(2019, 9, 22), 'end' => Carbon::create(2019, 9, 30)],
            ];

            $totalUsers = User::count();
            $interactionRates = collect($weeksInSeptember)->map(function ($week, $index) use ($totalUsers, $completedStatusIds) {
                $orderCount = Order::whereIn('status_id', $completedStatusIds)
                    ->whereBetween('created_at', [$week['start'], $week['end']])
                    ->count();
                $cartItemCount = CartItem::whereBetween('created_at', [$week['start'], $week['end']])
                    ->count();
                $interactions = $orderCount + $cartItemCount;
                $rate = $totalUsers > 0 ? ($interactions / $totalUsers * 100) : 0;
                return [
                    'week' => $index + 1,
                    'start_date' => $week['start']->toDateString(),
                    'end_date' => $week['end']->toDateString(),
                    'interaction_rate' => number_format($rate, 2) . '%',
                ];
            })->toArray();

            // Top product sales
            $totalSales = OrderItem::join('orders', 'order_items.order_id', '=', 'orders.id')
                ->whereIn('orders.status_id', $completedStatusIds)
                ->sum(\DB::raw('order_items.price * order_items.quantity'));

            $topProductSales = OrderItem::select('products.id', 'products.name', \DB::raw('SUM(order_items.price * order_items.quantity) as total_sales'))
                ->join('orders', 'order_items.order_id', '=', 'orders.id')
                ->join('products', 'order_items.product_id', '=', 'products.id')
                ->whereIn('orders.status_id', $completedStatusIds)
                ->groupBy('products.id', 'products.name')
                ->orderByDesc('total_sales')
                ->take(5)
                ->get()
                ->map(function ($item) use ($totalSales) {
                    $name = is_string($item->name) && json_validate($item->name) ? json_decode($item->name, true) : ['ar' => $item->name, 'en' => $item->name];
                    $percentage = $totalSales > 0 ? ($item->total_sales / $totalSales * 100) : 0;
                    return [
                        'product_id' => $item->id,
                        'product_name' => [
                            'ar' => $name['ar'] ?? 'غير متوفر',
                            'en' => $name['en'] ?? 'Not Available',
                        ],
                        'sales_percentage' => number_format($percentage, 2) . '%',
                    ];
                })->toArray();

            return response()->json([
                'status' => true,
                'message' => 'تم استرجاع الإحصائيات المتقدمة بنجاح',
                'data' => [
                    'sales_per_product' => $salesPerProduct,
                    'stock_summary' => [
                        'added_stock' => number_format($addedStock, 2),
                        'available_stock' => $availableStock,
                        'low_stock_products' => $lowStockProducts,
                    ],
                    'interaction_rate_september_2019' => $interactionRates,
                    'top_product_sales' => $topProductSales,
                ],
            ], JSON_UNESCAPED_UNICODE); // Ensure proper UTF-8 encoding
        } catch (\Exception $e) {
            return response()->json([
                'status' => false,
                'message' => 'حدث خطأ غير متوقع أثناء استرجاع الإحصائيات المتقدمة',
                'error' => $e->getMessage(),
            ], 500);
        }
    }

    /**
     * Summary of marketingStatistics
     * @param \Illuminate\Http\Request $request
     * @return JsonResponse
     */
    public function marketingStatistics(Request $request): JsonResponse
    {
        try {
            // Validate the request
            $validator = Validator::make($request->all(), [
                'date' => 'date_format:Y-m-d',
            ], [
                'date.date_format' => 'التاريخ يجب أن يكون بصيغة Y-m-d',
            ]);

            if ($validator->fails()) {
                return response()->json([
                    'status' => false,
                    'message' => 'فشل التحقق من البيانات',
                    'errors' => $validator->errors(),
                ], 422);
            }

            $date = $request->input('date', now()->toDateString());
            $startDate = Carbon::parse($date)->startOfDay();
            $endDate = Carbon::parse($date)->endOfDay();
            $yesterdayStart = $startDate->copy()->subDay()->startOfDay();
            $yesterdayEnd = $startDate->copy()->subDay()->endOfDay();
            $completedStatusIds = [8, 9, 10]; // Accepted, delivered, on-process

            // Register vs Visits
            $totalVisitsToday = WebsiteVisit::whereBetween('visit_timestamp', [$startDate, $endDate])
                ->sum('visit_count');
            $totalRegistersToday = User::whereBetween('created_at', [$startDate, $endDate])
                ->count();
            $totalUsers = User::count();
            $totalVisitsAverage = WebsiteVisit::sum('visit_count') / max(1, now()->diffInDays(Carbon::create(2000, 1, 1)));

            $totalRegisterPercent = $totalVisitsToday > 0 ? ($totalRegistersToday / $totalVisitsToday * 100) : 0;
            $totalRegistersYesterday = User::whereBetween('created_at', [$yesterdayStart, $yesterdayEnd])
                ->count();
            $registerChange = $totalRegistersYesterday > 0 ? (($totalRegistersToday - $totalRegistersYesterday) / $totalRegistersYesterday * 100) : 0;
            $visitsPercent = $totalVisitsAverage > 0 ? ($totalVisitsToday / $totalVisitsAverage * 100) : 0;

            // Top Countries Marketing
            $socialMediaVisits = WebsiteVisit::whereBetween('visit_timestamp', [$startDate, $endDate])
                ->whereIn('referrer_url', ['%instagram.com%', '%facebook.com%', '%twitter.com%'])
                ->sum('visit_count');
            $socialMediaPercent = $totalVisitsToday > 0 ? ($socialMediaVisits / $totalVisitsToday * 100) : 0;

            $topCountries = WebsiteVisit::select('country', \DB::raw('SUM(visit_count) as total_visits'))
                ->whereBetween('visit_timestamp', [$startDate, $endDate])
                ->groupBy('country')
                ->orderByDesc('total_visits')
                ->take(4)
                ->get()
                ->map(function ($item) {
                    return [
                        'country' => $item->country ?? 'غير معروف',
                        'total_visits' => number_format($item->total_visits, 0),
                    ];
                })->toArray();

            // Best Seller by Category
            $bestSellerCategories = OrderItem::select('categories.id', 'categories.name', \DB::raw('SUM(order_items.price * order_items.quantity) as total_sales'))
                ->join('orders', 'order_items.order_id', '=', 'orders.id')
                ->join('products', 'order_items.product_id', '=', 'products.id')
                ->join('categories', 'products.category_id', '=', 'categories.id')
                ->whereIn('orders.status_id', $completedStatusIds)
                ->whereBetween('orders.created_at', [$startDate, $endDate])
                ->groupBy('categories.id', 'categories.name')
                ->orderByDesc('total_sales')
                ->take(5)
                ->get()
                ->map(function ($item) {
                    $name = is_string($item->name) && json_validate($item->name) ? json_decode($item->name, true) : ['ar' => $item->name, 'en' => $item->name];
                    return [
                        'category_id' => $item->id,
                        'category_name' => [
                            'ar' => $name['ar'] ?? 'غير متوفر',
                            'en' => $name['en'] ?? 'Not Available',
                        ],
                        'total_sales' => number_format($item->total_sales, 2),
                    ];
                })->toArray();

            return response()->json([
                'status' => true,
                'message' => 'تم استرجاع إحصائيات التسويق بنجاح',
                'data' => [
                    'register_vs_visits' => [
                        'today' => $startDate->toDateString(),
                        'total_register' => [
                            'percentage' => number_format($totalRegisterPercent, 2) . '%',
                            'change' => ($registerChange >= 0 ? '+' : '') . number_format($registerChange, 2) . '%',
                        ],
                        'register' => [
                            'percentage' => number_format($totalRegisterPercent, 2) . '%',
                            'change' => ($registerChange >= 0 ? '+' : '') . number_format($registerChange, 2) . '%',
                        ],
                        'visits' => [
                            'percentage' => number_format($visitsPercent, 2) . '%',
                        ],
                    ],
                    'top_countries_marketing' => [
                        'social_media' => [
                            'percentage' => number_format($socialMediaPercent, 2) . '%',
                        ],
                        'countries' => $topCountries,
                    ],
                    'best_seller_by_category' => $bestSellerCategories,
                ],
            ], JSON_UNESCAPED_UNICODE); // Ensure proper UTF-8 encoding
        } catch (\Exception $e) {
            return response()->json([
                'status' => false,
                'message' => 'حدث خطأ غير متوقع أثناء استرجاع إحصائيات التسويق',
                'error' => $e->getMessage(),
            ], 500);
        }
    }
}