<?php
namespace App\Modules\Admin\Repositories;

use Core\Database\DB;
use PDO;

class AdminRepository
{
    public function dashboardStats(): array
    {
        $db = DB::conn();

        $totalUsers = (int) $db->query("SELECT COUNT(*) FROM users")->fetchColumn();

        $currentUsers = (int) $db->query("
        SELECT COUNT(*)
        FROM users
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    ")->fetchColumn();

        $previousUsers = (int) $db->query("
        SELECT COUNT(*)
        FROM users
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
        AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
    ")->fetchColumn();
        $currentRides = (int) $db->query("
    SELECT COUNT(*)
    FROM rides
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
")->fetchColumn();

        $previousRides = (int) $db->query("
    SELECT COUNT(*)
    FROM rides
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
    AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
")->fetchColumn();

        $currentDrivers = (int) $db->query("
    SELECT COUNT(*)
    FROM users
    WHERE role = 'driver'
    AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
")->fetchColumn();

        $previousDrivers = (int) $db->query("
    SELECT COUNT(*)
    FROM users
    WHERE role = 'driver'
    AND created_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
    AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
")->fetchColumn();
        return [
            'users' => $totalUsers,
            'users_growth' => $this->percentageChange($currentUsers, $previousUsers),

            'drivers' => (int) $db->query("
    SELECT COUNT(*)
    FROM users
    WHERE role = 'driver'
")->fetchColumn(),

            'drivers_growth' => $this->percentageChange(
                $currentDrivers,
                $previousDrivers
            ),
            'rides' => (int) $db->query("
    SELECT COUNT(*)
    FROM rides
")->fetchColumn(),
            'verification_queue' => (int) $db->query("
    SELECT COUNT(*)
    FROM users
    WHERE role = 'driver'
    AND verification_status = 'pending'
")->fetchColumn(),
            'rides_growth' => $this->percentageChange(
                $currentRides,
                $previousRides
            ),
        ];
    }
    private function percentageChange(
        int $current,
        int $previous
    ): float {

        if ($previous === 0) {

            return $current > 0
                ? 100.0
                : 0.0;
        }

        return round(
            (($current - $previous) / $previous) * 100,
            1
        );
    }
    public function users(): array
    {
        return DB::conn()
            ->query("SELECT * FROM users ORDER BY id DESC")
            ->fetchAll(PDO::FETCH_ASSOC);
    }

    public function rides(): array
    {
        return DB::conn()
            ->query("
                SELECT rides.*, users.name AS driver_name
                FROM rides
                JOIN users ON users.id = rides.driver_id
                ORDER BY rides.id DESC
            ")
            ->fetchAll(PDO::FETCH_ASSOC);
    }
    public function pendingDriverVerifications(): array
    {
        $stmt = DB::conn()->query("
        SELECT id, name, email, avatar, verification_status, verification_note
        FROM users
        WHERE role = 'driver'
        AND verification_status = 'pending'
        ORDER BY rand()
        LIMIT 3
    ");

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}
