<?php
include 'vendor/autoload.php';
$app = require_once 'bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Console\Kernel::class);
$kernel->bootstrap();

use App\Models\Product;
use Carbon\Carbon;

$start = Carbon::now()->subDays(60)->startOfDay();
$end = Carbon::now()->endOfDay();

echo "Running query sorted by view_count with start: " . $start->toDateTimeString() . " and end: " . $end->toDateTimeString() . "\n";

$productsQuery = Product::with('category');

$productsQuery->select('product.*')
    ->selectSub(function($query) use ($start, $end) {
        $query->from('order')
            ->whereColumn('order.product_id', 'product.id')
            ->whereBetween('order.created_at', [$start, $end])
            ->where(function($q) {
                $q->whereNull('order.cancellation')->orWhere('order.cancellation', 0);
            })
            ->selectRaw('COALESCE(SUM(order.quantity), 0)');
    }, 'total_sold')
    ->selectSub(function($query) use ($start, $end) {
        $query->from('order')
            ->whereColumn('order.product_id', 'product.id')
            ->whereBetween('order.created_at', [$start, $end])
            ->where(function($q) {
                $q->whereNull('order.cancellation')->orWhere('order.cancellation', 0);
            })
            ->selectRaw('COALESCE(SUM(order.total), 0)');
    }, 'revenue')
    ->selectSub(function($query) use ($start, $end) {
        $query->from('product_views')
            ->whereColumn('product_views.product_id', 'product.id')
            ->whereBetween('product_views.created_at', [$start, $end])
            ->selectRaw('COUNT(*)');
    }, 'view_count');

$productsQuery->orderByDesc('view_count');
$products = $productsQuery->paginate(5);

echo "Query success! Total products: " . $products->total() . "\n";
foreach ($products as $p) {
    echo "Product: {$p->product_name} (SKU: {$p->sku}), Category: " . ($p->category->category_name ?? 'N/A') . ", Views: {$p->view_count}, Sold: {$p->total_sold}, Revenue: LKR {$p->revenue}\n";
}
