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

$total = \DB::table('products')->count();

$byCategory = \DB::table('products')
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->select('categories.name', \DB::raw('count(*) as total'))
    ->groupBy('categories.name')
    ->orderBy('total', 'desc')
    ->get();

echo "Total products: {$total}\n\nBy category:\n";
foreach ($byCategory as $row) {
    echo "  {$row->name}: {$row->total}\n";
}
