<?php

namespace App\Http\Controllers;

use App\Models\Order;
use Illuminate\Support\Facades\DB;

class LeaderboardController extends Controller
{
    public function index()
    {
        $topServices = Order::select(
        'negara',
        'server',
        'layanan_nama',

        DB::raw("
            SUM(
                CASE
                    WHEN status='SUCCESS' THEN 1
                    ELSE 0
                END
            ) as success_count
        "),

        DB::raw("
            SUM(
                CASE
                    WHEN status='FAILED' THEN 1
                    ELSE 0
                END
            ) as failed_count
        "),

        DB::raw("
            SUM(
                CASE
                    WHEN status IN ('SUCCESS','FAILED') THEN 1
                    ELSE 0
                END
            ) as total_count
        "),

        DB::raw("
            ROUND(
                (
                    SUM(
                        CASE
                            WHEN status='SUCCESS' THEN 1
                            ELSE 0
                        END
                    ) * 100
                ) /
                NULLIF(
                    SUM(
                        CASE
                            WHEN status IN ('SUCCESS','FAILED') THEN 1
                            ELSE 0
                        END
                    ),
                    0
                ),
                2
            ) as success_rate
        ")
    )
    ->whereDate('created_at', today())
    ->groupBy(
        'negara',
        'server',
        'layanan_nama'
    )
    ->havingRaw("
        SUM(
            CASE
                WHEN status IN ('SUCCESS','FAILED')
                THEN 1
                ELSE 0
            END
        ) >= 20
    ")
    ->orderByDesc('success_rate')
    ->orderByDesc('success_count')
    ->limit(10)
    ->get();


        return view('leaderboard.index', compact('topServices'));
    }
}